MySQL Disaster Recovery


You’ve accidentally deleted some or all of the rows in a table, or even dropped a schema object altogether, in a production MySQL database. You need to recover the database to the point right before the accidental statements (DML or DDL) were executed, in essence undoing them.

What You’ll Need

  • A recent backup file created using the mysqldump command;
  • The MySQL binary logs;
    • These are usually named like mysql-bin.000123.
    • The binary logs need to cover the transactions that modified the database from when the backup was taken until when the accidental SQL statements were executed.
  • The mysqlbinlog utility. This is usually bundled with MySQL installations.

How to Recover the Database

1) End all connections to the database in preparation for recovery.  As a precaution, create a backup of the broken database, and save a copy of the binary logs from the MySQL data directory.

> mysqldump --triggers --routines --no-autocommit --user=root --password --databases test > backup.sql
> mkdir ~/binlogs
> cp /mysql/mysql-bin.* ~/binlogs

2)  Drop the broken databases and restore the last known good backup.

> mysql -uroot -p -e "drop schema test;"
> cat backup.sql | mysql -uroot -p --show-warnings

3) Determine the binary log start position to use with mysqlbinlog.

a) If your mysqldump backup was executed with the option --master-data=2, find the start position in the line CHANGE MASTER TO.... For example:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=282582857;

In the above line the start position to use is 282582857.

b) Otherwise, create a small sample file of all the transactions that were executed around the time when the mysqldump backup was executed.

> mysqlbinlog -v --start-datetime="2014-02-14 00:00:00" --stop-datetime="2014-02-14 00:00:05" mysql-bin.000001 > start-transactions.txt

Determine the start position by identifying the log position value of the first transaction to be replayed.

# at 282582857

In the above line the start position to use is 282582857.

4) Determine the binary log stop position to use with mysqlbinlog.

Important: the stop position should exclude the accidental SQL statements!

Create a small sample file of all the transactions that were executed right before and including the accidental SQL statements.

> mysqlbinlog -v --start-datetime="2014-02-14 14:35:00" --stop-datetime="2014-02-14 14:40:00" mysql-bin.000001 > end-transactions.txt

Determine the stop position by identifying the end_log_pos value of the last transaction executed right before the transactions to be undone.

# at 282582934
#140214 14:35:50 server id 1  end_log_pos 282583020 CRC32 0x10a3dbd1    Table_map: `test`.`xxx` mapped to number 123
# at 282583020
#140214 14:35:50 server id 1  end_log_pos 282591191 CRC32 0xd03f9bd3    Delete_rows: table id 123
<Many more deleted rows after this.>

From the above line, the stop position is 282582934.

5) Replay the binary logs between the start and stop positions in order to recover the database.

mysqlbinlog --start-position=282582857 --stop-position=282582934 mysql-bin.000001 | mysql -uroot -p

Tip: nightly database backups can be useful because they make the mysqlbinlog replay process, and therefore restoring the database, quicker.


Point-in-Time (Incremental) Recovery Using the Binary Log: