MySQL is one of the world’s most widely used open-source databases, powering WordPress sites, web applications and SaaS platforms. These MySQL interview questions and answers cover SQL basics, InnoDB, indexing, transactions, replication, backup, security and performance.
Basics
1. What is MySQL?
MySQL is an open-source relational database management system, now owned by Oracle. It uses SQL to store and query data and comes in a free Community Edition and a commercial Enterprise Edition.
2. Which versions should you know about?
MySQL 8.4 is the current long-term support (LTS) release, and the 9.x series delivers newer “innovation” features. MySQL 8.0 reached end of life in April 2026, so production systems should move to 8.4.
3. What is the default storage engine?
InnoDB. It supports transactions, row-level locking, foreign keys and crash recovery. The older MyISAM engine lacks transactions and is rarely used today.
Keys, indexes and joins
4. Primary key vs unique key vs foreign key?
- A primary key uniquely identifies each row and cannot be NULL. A table has one.
- A unique key prevents duplicate values, but can allow NULL.
- A foreign key links a column to a primary key in another table and enforces referential integrity.
5. How do indexes work in InnoDB?
InnoDB stores table data in the clustered index, ordered by the primary key. Secondary indexes store the indexed columns plus the primary key. Therefore, short primary keys keep secondary indexes small.
6. Explain the join types.
INNER JOIN returns matching rows from both tables. LEFT JOIN returns all rows from the left table plus matches. RIGHT JOIN does the reverse. A CROSS JOIN returns every combination.
7. WHERE vs HAVING?
WHERE filters rows before grouping, while HAVING filters groups after GROUP BY.
8. How do you list tables and their sizes?
SELECT table_name,
ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY size_mb DESC;Transactions
9. What does ACID mean?
Atomicity, consistency, isolation and durability: a transaction completes fully or not at all, keeps data valid, does not interfere with other transactions and survives crashes.
10. Name the isolation levels.
Read Uncommitted, Read Committed, Repeatable Read (InnoDB’s default) and Serializable. Higher levels reduce anomalies but can increase locking.
11. How does MySQL handle deadlocks?
InnoDB detects deadlocks and rolls back one transaction. Applications should catch the error and retry. Keeping transactions short and accessing tables in a consistent order reduces deadlocks.
Replication and backup
12. How does replication work?
The source writes changes to the binary log, and replicas apply them. GTIDs simplify failover. Group Replication and InnoDB Cluster add automatic failover and multi-node consistency.
13. How do you back up MySQL?
Use mysqldump or MySQL Shell dump utilities for logical backups, and tools like Percona XtraBackup or MySQL Enterprise Backup for fast physical backups. Combine them with binary logs for point-in-time recovery, and test restores.
Performance and security
14. How do you tune a slow query?
Enable the slow query log, then use EXPLAIN or EXPLAIN ANALYZE to see the plan. Add or adjust indexes, avoid SELECT *, and rewrite queries that cannot use indexes, such as functions on indexed columns.
15. Which server settings matter most?
innodb_buffer_pool_size is the most important; it is often set to 50 to 75% of RAM on a dedicated server. Also review max_connections and redo log capacity.
16. How do you secure MySQL?
Run mysql_secure_installation, remove anonymous users, use least-privilege accounts per application, keep port 3306 off the internet, enable TLS, and patch regularly. For WordPress sites, also protect wp-config.php, which holds database credentials.
For related databases, see our MariaDB and PostgreSQL interview questions.
Keep preparing
Practise each answer out loud, and run the commands in a lab rather than memorising them. Interviewers often ask follow-up questions, so make sure you understand why each answer is true.
Sources
Preparing for an interview? Browse more questions in our interview preparation hub. For businesses, we also offer security services such as VAPT, server hardening and ISO 27001 readiness.
