MySQL Interview Questions for DBAs

Configure MySQL User Privileges

Create a new MySQL user with read-only access to a specific database.

Expected output: A user that can only perform SELECT queries on the database.

Hint: Use CREATE USER and GRANT statements.

Analyze a Slow Query

Use EXPLAIN to analyze a provided slow query and suggest improvements.

Expected output: A report identifying bottlenecks and recommended indexes.

Hint: Look for full table scans and missing indexes.

You can perform a logical backup using mysqldump, which exports SQL statements, or a physical backup by copying data files. For point-in-time recovery, binary logs are used.

MyISAM is non-transactional and faster for read-heavy workloads, while InnoDB supports transactions, foreign keys, and row-level locking, making it suitable for high-concurrency environments.

You can use the SHOW SLAVE STATUS command on the replica server to check replication health, including lag and error states.

Slow queries often result from missing indexes, inefficient joins, or large data scans. Optimization involves adding indexes, rewriting queries, and analyzing execution plans with EXPLAIN.