Recovery for MySQL DBAs
MySQL recovery is the ability to restore a database to a working, recent state after data loss, built from backups and binary logs. Backups can be logical, made with mysqldump, or physical, made with tools such as MySQL Enterprise Backup or Percona XtraBackup, and point in time recovery replays the binlog.
Stage: Security and Backup · Part of the MySQL DBA roadmap · Last reviewed 19 November 2026
What to learn in Recovery
- Take a consistent logical backup with mysqldump --single-transaction
- Restore a dump into a fresh server and verify row counts
- Enable the binary log and replay it to a chosen point in time
- Compare logical and physical backups by speed, size and restore time
- Test a restore on a schedule instead of trusting untested backups
Recovery interview questions
What does mysqldump --single-transaction do?
It starts a consistent-read transaction before dumping, so InnoDB tables are captured at one point in time without locking them. It does not give the same guarantee for non-transactional tables like MyISAM, which may still need locks.
How do you recover to a point in time after an accidental DELETE?
Restore the last full backup, then replay binary log events with mysqlbinlog from the backup position up to just before the bad statement, using --stop-datetime or --stop-position. This requires binary logging to have been enabled before the incident.
Where Recovery fits in the MySQL DBA roadmap
Recovery belongs to the Security and Backup stage (intermediate level). Protect the data.
Other units in this stage
Back to the full MySQL DBA roadmap