MySQL interview question · Most asked
How do you back up a MySQL database, and how does point-in-time recovery work?
Summary
Use physical backups (Percona XtraBackup, MySQL Enterprise Backup, snapshots) for large databases and logical backups (mysqldump, MySQL Shell dump utilities) for smaller ones or migrations. Point-in-time recovery restores the last full backup and then replays the binary logs up to just before the incident.
Longer Answer
mysqldump --single-transactiontakes a consistent logical dump of InnoDB tables without long locks, but restoring large dumps is slow (indexes rebuild).- MySQL Shell's
util.dumpInstance()/loadDump()are parallel and much faster. - Physical hot backups copy data files plus redo, restoring quickly.
- Keep binary logs (and their retention) long enough to cover the gap between backups.
A backup is only proven by a restore: test restores regularly and measure how long they take against your recovery time objective (RTO).
Key Points
- Physical for big, logical for small/migrations.
--single-transactionfor consistency.- PITR = full backup + binlog replay.
- Test restores and measure RTO.
Explanation
What interviewers look for
Mention of tested restores and RPO/RTO. A plan that has never been restored is not a plan.
More most-asked MySQL questions
- What is a clustered index in InnoDB, and why does primary key choice matter so much?
- Why is OFFSET pagination slow on large tables, and what is keyset pagination?
- How do you read EXPLAIN output in MySQL?
- Why might MySQL not use an index you created?
- What are the transaction isolation levels, and which does InnoDB use by default?
- How does a composite index work, and what is the leftmost-prefix rule?
- What causes deadlocks in MySQL, and how do you handle them?
- What are gap locks and next-key locks, and why do they surprise developers?