MySQL DBA Interview Questions Basic and Advanced

Hello all, with my 8+ years of experience as MySQL DBA, I’ll share some of the most common beginner level to advance level MySQL DBA Interview Question Answers which I’ve faced in almost every interview, I’ll keep on adding more, hope this will help you:

Read this post to get the overview of MySQL: MySQL Basics

MySQL DBA Interview Questions:

1.Describe MySQL architecture.

Answer: MySQL has got the logical architecture as follows(you can google and get a good understanding of MySQL architecture)

A. Connection Manager
B. Query Optimizer
C. Pluggable Storage Engines.

2. What are the major differences between MySQL 5.* and 5.* and 8.*?

Answer: You need to be up to date as new MySQL version are coming out frequently, read this.

3. What are the different database engines available in MySQL?

Answer: Following:

A. MyISAM
B. INNODB –> Get in depth knowledge if this.
C. Memory
D. Federated
E. CSV

4. What are the major differences between MyISAM and InnoDB?

Answer: Following are the differences between InnoDB and MyISAM

A. MyISAM does not support transactions whereas InnoDB does.
B. InnoDB supports multi versioning concurrency control.
C. MyISAM uses table level locks and InnoDB uses row level locking.

5. Which engine would you prefer for production OLTP environment and why?

Answer:  InnoDB (to be transaction safe)

6. What are the best installation practices for MySQL?

Answer: There are following installation methods available

A. Binary Installation
B. RPM Installation
C. Source Code compilation

7. Which RAID level is best suited for MySQL?

Answer: RAID 10

8. What is the default port of MySQL?

Answer:  3306

9. How many types of logs are there in mysql?

Answer: General Log, Error Log, Binary Log and Slow Query Log

10. How do you find out slow queries in mysql?

Answer: By enabling the slow query log as follows
SET GLOBAL slow_query_log = 1;

11. How do you go through the MySQL slow query log?

Answer: Slow query log might be very huge in size and query could be listed thousand times to summarize the slow query log in a very informative way there is third party tool available ‘pt-query-digest’ which is a part of percona tool kit freely downloadable.

12. How do you check the uptime of a mysql server?

Answer: SHOW GLOBAL STATUS LIKE ‘UPTIME’

13. If the mysql server is performing slow than how to find out the process which is causing problem.

Answer: Best way is to check with: ‘SHOW PROCESSLIST / SHOW ENGINE INNODB STATUS

14. What do you do if the end user complains about the performance of the DB?

Answer: ‘SHOW PROCESSLIST’ will show the processes which taking resources at db server and the do the rest diagnosis.

15. What do you do about the slow queries?

Answer: study the explain plan and create necessary indexes if required.

16. Where do you change the performance parameters of mysql and by default where is the file located on a unix system?

Answer: my.cnf configuration file, this file is available under /etc/my.cnf

17. Which are the important performance parameters for MyISAM and InnoDB?

Answer: For MyISAM

key_cache_size
thread_cache_size
tmp_table_size
max_heap_table_size
read_buffer_size
query_cache

For InnoDB

innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_file_size
innodb_log_buffer_size
tmp_table_size
max_heap_table_size

file_per_table
table_open_cache

18. Should we have Query_cache enabled?

Answer: Yes if same queries are executed again and again, it will store the output in cache that will improve the performance. [Note: It has been removed in MySQL 8.0, why is explained in next question]

19. What are the disadvantages of having a big Query cache size?

Answer: Query cache puts an additional overhead on the database. It require the db to work on invalidating the queries from the query cache.

20. What should be the optimum size of InnoDB buffer cache?

Answer: It should be the 70-80% of the memory available.

21. How do you backup InnoDB tables?

Answer: there are two ways of taking backup

1. mysqldump (with –single-transaction no tables are locked)
2. xtrabackup (part of percona)

3 MEB (MySQL Enterprise Backup)

22. How to take incremental backup in MySQL?

Answer: Using binary logs or use 3rd party backup tools like percona-backup.

23. Can the database be renamed in MySQL?

Answer:  No, it can be recreated with different name.

24. How to check the table fragmentation and resolve if the fragmentation is found?

Answer: Following query will list all the fragmented tables

SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), ‘MB’) DATA, CONCAT(ROUND(data_free  / ( 1024 * 1024 ), 2), ‘MB’)FREE from information_schema.TABLES where TABLE_SCHEMA NOT IN (‘information_schema’,’mysql’) and Data_free < 0;

then run the following command on tables given by the query

alter table < table returned from pervious query > engine=innodb;

25. How to change the root password if the root password is lost?

Answer: Start the Db with –skip-grants-table. change the password and restart the db in  normal mode.

Check this link for proper explanation mysql root password reset.

26. What do you do if the data disk is full?

Answer: If the data disk is full then create a soft link and move the .frm and .idb files to the linked location.

27. How to take consistent backup in mysql?

Answer: Take the backup at slave after stopping the slave.

28. How do you restrict the users in mysql?

Answer:  by grant and revoke commands.

29. What is advantage of having file per table parameter enabled?

Answer:  point # 26 can only be done in file per table is enabled at innodb level.

30. How do you setup replication?

Answer: Read this post set up mysql replication.

31. What is normalization in DBMS?

Answer: Check this for details: Normalization in DBMS

32. How can you access a Database Server which has crashed(ssh not working)? (Asked in Amazon)

Answer.  Using telnet or ping check if the host is up or not first. (I’m not sure of the answer, I wasn’t selected there).

33. Explain how ACID properties are followed in a MySQL transaction.

Answer. Get an idea of how a table or rows are locked in a transaction to follow ACID properties, how dirty reads are not possible, etc.

34. How to add a new slave to a existing Master-slave setup without any downtime?

Answer. First stop slave of existing slave, copy data and start replication from same position as existing slave. Hence we don’t touch the master in this case.

35. If you have experience with clusters? Common issues, setup, installation, adding new node to an existing cluster?

Answer. Get overview of InnoDB cluster, galera cluster, NDB cluster.

I’ll keep on adding more MySQL DBA Interview questions, feel free to comment below for any query or concerns.