Background
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.
References
Point-in-Time (Incremental) Recovery Using the Binary Log: https://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html