MySQL/MariaDB DBA Interview Questions and Answers - Part 3
Q41. How do you monitor and identify slow queries in MySQL?
answer:
You can enable the slow query log in MySQL and set a threshold for query execution time using the long_query_time variable. Slow queries exceeding the threshold will be logged, allowing you to analyze and optimize them.
Q42. How do you enable slow query logging in MySQL?
answer:
To enable slow query logging, you need to set the slow_query_log variable to ON and configure the long_query_time variable to specify the threshold for query execution time.
Q43. How do you enable query caching in MySQL?
answer:
To enable query caching, you need to set the query_cache_type variable to a non-zero value (e.g., ON), and configure the query_cache_size variable to allocate memory for caching query results.
Q44. How do you import data from a CSV file into a MySQL table?
answer:
You can use the LOAD DATA INFILE statement to import data from a CSV file into a MySQL table. The statement allows you to specify the file path, field delimiters, and column mappings.
Q45. What is the purpose of the EXPLAIN statement in MySQL?
answer:
The EXPLAIN statement is used to analyze and optimize the execution plan of a query. It provides information about how MySQL executes a query, the order of table access, the indexes used, and estimated row counts.
Q46. What is the difference between MyISAM and InnoDB?
answer:
MyISAM is a non-transactional storage engine with high read performance and is suitable for read-heavy workloads. InnoDB is a transactional storage engine with support for ACID (Atomicity, Consistency, Isolation, Durability) properties, providing better data integrity and concurrency control.
Q47. How do you perform point-in-time recovery in MySQL?
answer:
To perform point-in-time recovery, you need to restore a backup taken before the desired point in time and then apply binary logs containing changes made after the backup. This process brings the database to a specific state as of the desired point in time.
Q48. What is the purpose of the --skip-lock-tables option in mysqldump?
answer:
The --skip-lock-tables option in mysqldump allows you to perform a logical backup of the database without acquiring read locks on the tables. It enables you to backup a database while it is still being actively used, avoiding interruptions.
Q49. What is the purpose of the REPAIR TABLE statement in MySQL?
answer:
The REPAIR TABLE statement is used to repair corrupted or damaged MyISAM tables. It automatically detects and fixes common table issues, such as incorrect links, missing rows, or index problems.
Q50. What is the purpose of the mysqlbinlog utility?
answer:
The mysqlbinlog utility is used to read and display the contents of binary log files. It allows you to view the logged changes, including SQL statements and events, facilitating analysis, troubleshooting, and replication tasks.
