Recovering Accidentally Deleted MySQL Tables Using Binlog and Backups

Scenario

The client accidentally deleted a table and wants to restore the data to its state before the deletion.

Prerequisite

The recovery method assumes the database has daily backups and binlog logging enabled. The example uses the test database and the student table.

1. Enable binlog and backup data

1.1 Check if binlog is enabled

Run show variables like 'log_bin'; or view the MySQL configuration.

1.2 Enable binlog if not already

Edit /etc/my.cnf (Linux) and add:

# edit /etc/my.cnf
vi /etc/my.cnf
# i to start editing
# add after #log bin
server_id=2
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30
log_bin_basename= /path/to/log/binlog/port/mysql-bin
log_bin_index=/path/to/log/binlog/port/mysql-bin.index
# esc to exit, shift+: to save

Then restart MySQL:

systemctl restart mysqld

Verify binlog is active with show variables like 'log_bin';

2. View current table data

Use standard SELECT queries or tools to confirm the table’s contents before proceeding.

2. Backup data

Common mysqldump commands:

mysqldump -h10.186.63.4 -P4149 -u u1 -p1234567890q@ --all-databases > /test1.sql

mysqldump -h10.186.63.4 -P4149 -u u1 -p1234567890q@ database > test2.sql

mysqldump -h10.186.63.4 -P4149 -u u1 -p1234567890q@ database student > test3.sql

mysqldump -h10.186.63.4 -P4149 -u u1 -p1234567890q@ database --ignore-table=db.tb --ignore-table=db.tb2 > /test4.sql

3. Insert data then drop the database (to generate binlog)

Insert sample rows:

mysql> insert into student values('201215130','张三','男',21,'IS');
Query OK, 1 row affected (0.03 sec)
mysql> insert into student values('201215131','李四','女',20,'MA');
Query OK, 1 row affected (0.02 sec)

Drop the database (simulating accidental deletion):

mysql> drop database test;
Query OK, 1 row affected (0.10 sec)

Do not perform any further operations after dropping the database.

4. Recover data

4.1 View current binlog file

mysql> show master status\G;
File: mysql-bin.000021
Position: 68403303
...

4.2 Copy the binlog file to a safe location

cp /test/data/mysql/log/binlog/4149/mysql-bin.000021 /root

4.3 Convert binlog to SQL

Use mysqlbinlog to extract statements for the target database:

/data/mysql/base/5.7.25/bin/mysqlbinlog -d test mysql-bin.000021 > 0021bin.sql

Edit 0021bin.sql and remove the accidental DROP DATABASE command.

4.4 Restore the backup file

/data/mysql/base/5.7.25/bin/mysql -h10.186.63.4 -P4149 -u u1 -p1234567890q@ < test.sql

Verify the restoration with show databases; , use test; , show tables; , and select * from student;

4.5 Apply the edited binlog SQL to recover the deleted rows

Comment out the GTID purge line if present:

/*SET @@GLOBAL.GTID_PURGED=XXXX*/;

Import the cleaned SQL:

/data/mysql/base/5.7.25/bin/mysql -h10.186.63.4 -P4149 -u u1 -p1234567890q@ test < 0021bin.sql

Finally, query the table to confirm the data has been restored:

mysql> select * from test.student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 201215121 | 李勇      | 男   | 20   | CS    |
| ...       | ...       | ...  | ...  | ...   |
+-----------+-----------+------+------+-------+
10 rows in set (0.00 sec)