MySQL: Frequently asked Interview Questions and Answers Part 5

What is Access Control List(ACL)?

MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can attempt to perform. There is also support for SSL-encrypted connections between MySQL clients and servers.
MySQL keeps the ACLs (also called grant tables) cached in memory.
When a user tries to authenticate or run a command, MySQL checks the authentication information.

What is the difference between MySQL_connect and MySQL_pconnect?

MySQL_Connect opens a new connection in the database and every time you need to open and close connection.
MySQL_PConnect for the persistent connection so it open one time and use for different running sessions. You do not require open and close again and again.

What is the meaning of ” i_am_a_dummy flag” ?

This is one flag in MySQL and its enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.

Command to change a password for an existing user using Mysqladmin.

Mysqladmin -u root -p password “newpassword”.

Which command you are using to show all indexes of a table?

SHOW INDEX FROM table_name;

How to change storage engine of a table?

ALTER TABLE table_name ENGINE Innodb;

What are the drawbacks to creating a large number of tables in the same database.

MySQL requires to open, close and create files for every single table and even this can be multiple because of multi-threaded so this will degrade the performance.
You should also increase the size of table_cache otherwise it will be full because of many table file operations.

The User is created and Host column contains %, Is it good or bad practice?

This is bad practice, % indicates that user can login from anywhere in the network or internet. This should be best practice only for specific user like admin or root.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of