Recovering an Accidentally Dropped MySQL Database with Full Backup and Binlog Incrementals

Scenario Overview

MySQL database is backed up nightly at midnight. At 10 am a user accidentally drops the database.

Main Idea

Use the CHANGE MASTER statement stored in the full backup SQL file together with the incremental binlog files to extract only the needed binlog events, filter out DROP statements, and apply them after restoring the full backup.

Process Diagram

Step‑by‑Step Procedure

Create sample data

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO student VALUES(1,'zhangsan',20);
INSERT INTO student VALUES(2,'lisi',21);
INSERT INTO student VALUES(3,'wangwu',22);

Full backup command

# mysqldump -uroot -p -B -F -R -x --master-data=2 test | gzip > /server/backup/test_$(date +%F).sql.gz

Options: -B specifies the database, -F flushes logs, -R backs up stored procedures, -x locks tables, and --master-data adds a CHANGE MASTER statement with the binlog file and position.

Insert more data and simulate accidental drop

INSERT INTO student VALUES(6,'xiaoming',20);
INSERT INTO student VALUES(6,'xiaohong',20);
DROP DATABASE test;

Identify the binlog position recorded in the backup

# cd /server/backup/
# gzip -d test_2016-08-02.sql.gz
# grep CHANGE test_2016-08-02.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;

This indicates that all binlog events before line 107 of mysql-bin.000003 are already included in the full backup.

Extract incremental binlog and remove DROP statements

# cp /data/3306/mysql-bin.000003 /server/backup/
# mysqlbinlog -d test mysql-bin.000003 > 003bin.sql
# Edit 003bin.sql with a text editor to delete any DROP statements

Move the binlog file out of the data directory before restoring to avoid new writes that would corrupt the incremental recovery.

Restore data

# mysql -uroot -p < test_2016-08-02.sql
# mysql -uroot -p -e "SELECT * FROM test.student;"
# mysql -uroot -p test < 003bin.sql
# mysql -uroot -p -e "SELECT * FROM test.student;"

The first command restores the database to the state captured by the full backup. The second command applies the filtered incremental binlog, bringing the data up to the point just before the accidental drop.

Key Takeaways

Suitable for recovery when errors are caused by manual SQL mistakes or when no master‑slave hot‑standby exists.

Both the full backup and all incremental binlog data are required for a complete restoration.

Stop external writes during recovery to prevent further binlog entries from interfering.

Restore the full backup first, then apply the filtered incremental binlog in order, removing problematic statements if necessary.