Recover a Dropped MySQL Database Using Full Backup and Binlog Incrementals

Scenario

A MySQL instance performs a nightly full backup at 00:00. At 10:00 the same day a user accidentally executes DROP DATABASE. The only data generated after the backup are the binary log (binlog) events. The goal is to restore the database to the state just before the drop using the full backup file and the subsequent binlog files.

Recovery Method Overview

The backup created with mysqldump --master-data embeds a CHANGE MASTER TO statement that records the binlog file name and position at the moment of the dump. By locating this position we can extract the portion of the binlog that contains only the changes made after the backup, edit out any destructive statements (e.g., DROP), and replay the resulting SQL on top of the restored full backup.

Step‑by‑Step Procedure

1. Sample Data (optional)

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);

2. Create Full Backup with Binlog Position

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

Parameters: -B: dump the specified database. -F: flush logs before dumping (ensures a new binlog file starts). -R: include stored procedures, functions, triggers, and events. -x: lock all tables to obtain a consistent snapshot. --master-data=2: prepend a CHANGE MASTER TO statement with the current binlog file name and position (the statement is commented out when =2).

3. Perform Writes After Backup and Accidentally Drop the Database

INSERT INTO student VALUES (4,'xiaoming',20);
INSERT INTO student VALUES (5,'xiaohong',20);
-- Accidental drop
DROP DATABASE test;

All rows inserted after the backup exist only in the binary log.

4. Locate the Binlog Position Recorded in the Backup

# cd /server/backup/
# gzip -d test_$(date +%F).sql.gz   # decompress
# grep CHANGE test_$(date +%F).sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;

The backup ends at position 107 of mysql-bin.000003. Events before this offset are already included in the dump.

5. Export Incremental Binlog and Remove DROP Statements

# cp /data/3306/mysql-bin.000003 /server/backup/   # move out of the data directory
# mysqlbinlog -d test mysql-bin.000003 > /server/backup/003bin.sql
# Edit /server/backup/003bin.sql and delete any lines containing DROP statements

Moving the binlog file away prevents MySQL from writing new events to it while the full backup is being restored, which would otherwise corrupt the incremental recovery.

6. Restore the Full Backup

# gunzip -c /server/backup/test_$(date +%F).sql.gz | mysql -uroot -p
# mysql -uroot -p -e "SELECT * FROM test.student;"
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 20 |
|  2 | lisi     | 21 |
|  3 | wangwu   | 22 |
+----+----------+-----+

The database is now restored to the state captured at midnight.

7. Apply Incremental Changes

# mysql -uroot -p test < /server/backup/003bin.sql
# mysql -uroot -p -e "SELECT * FROM test.student;"
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 20 |
|  2 | lisi     | 20 |
|  3 | wangwu   | 20 |
|  4 | xiaoming | 20 |
|  5 | xiaohong | 20 |
+----+----------+-----+

All rows inserted after the full backup are recovered, and the database is functional again.

Key Considerations

Both the full dump and every binlog file generated after the dump must be retained.

Stop all external writes before starting the restore to avoid new events mixing with the incremental replay.

Apply the full backup first, then replay the incremental SQL in chronological order.

If the binlog contains harmful statements (e.g., DROP, TRUNCATE), edit them out or filter by timestamp/position before execution.

Process Diagram