How to Recover Accidentally Deleted MySQL Data Using Binlog

Background

One day Zhang San tried to delete useless data from a MySQL table but accidentally removed too many rows. This article shows how to recover the lost data using binlog and several ready‑made tools.

Example Table

create table person (
  id   bigint primary key auto_increment comment 'id',
  name varchar(50) comment '名称'
) engine = innodb;

Intended delete: delete from person where id > 500000; Accidental delete:

delete from person;

Solution Overview

The recovery idea is to use the binlog to retrieve the deleted rows and re‑insert them into the database. Binlog records forward operations (INSERT, DELETE, etc.), so two recovery methods are possible.

General Operations

1.1 Confirm binlog is enabled

Check the status: SHOW VARIABLES LIKE 'log_bin'; If the result shows log_bin = ON, binlog is active; otherwise enable it.

1.1.2 Enable binlog

Edit my.cnf or my.ini (usually under /etc/mysql) and add:

[mysqld]
log-bin=mysql-bin
server-id=1

Restart MySQL after changing the configuration. mysql-bin is the binlog file prefix. server-id must be set for binlog to work.

1.2 Binlog format

Only the ROW format can capture the exact values of deleted rows. Check the current format: SHOW VARIABLES LIKE 'binlog_format'; Possible values: ROW, STATEMENT, MIXED. Set to ROW if needed:

[mysqld]
binlog_format=ROW

Restart MySQL after modification.

1.3 Locate binlog files

Find the current binlog file: SHOW MASTER STATUS; List all binlog files: SHOW MASTER LOGS; Get the binlog directory:

SHOW VARIABLES LIKE 'log_bin_basename';

Method 1: Recover by Re‑executing INSERT Statements

2.1 Find the relevant binlog file

Identify the binlog that contains the INSERT statements based on the time of the original operation (e.g., mysql-bin.000213).

2.2 Parse the binlog for the time window

mysqlbinlog --base64-output=decode-rows -v \
  --start-datetime="2024-09-12 11:59:00" \
  --stop-datetime="2024-09-12 12:01:00" \
  mysql-bin.000213 > binlog.sql

In STATEMENT mode, locate the INSERT between BEGIN and COMMIT to get its position (e.g., start 219, end 445).

# at 219
SET TIMESTAMP=1726305266;
BEGIN;
# at 300
INSERT INTO person VALUES (1, 'first');
COMMIT;

2.3 Extract the exact range

mysqlbinlog --start-position=219 --stop-position=445 mysql-bin.000213 > binlog.sql

2.4 Replay the recovered INSERT

mysql -uroot -proot < binlog.sql

or inside the MySQL client:

source binlog.sql;

Method 2: Generate Reverse Operations from DELETE Statements

3.1 Find the binlog file containing the DELETE

Similar to Method 1, locate the binlog (e.g., mysql-bin.000213).

3.2 Parse the binlog

In ROW mode the binlog contains the deleted row values:

# at 1695
DELETE_ROWS ...
### DELETE FROM `tests`.`person`
### WHERE
###   @1=1
###   @2='first'

In STATEMENT mode only the generic delete from person appears, which cannot be reversed.

3.3 Generate the inverse INSERT

insert into person values (1, 'first');

3.4 Replay the INSERT

mysql -uroot -proot < binlog.sql

Common Tools

binlog2sql

Python script from Meituan that pretends to be a slave to fetch binlog and generate forward or reverse SQL.

python binlog2sql.py -h127.0.0.1 -P13306 -uroot -p \
  --start-file=mysql-bin.000002

Use --flashback to output reverse statements.

python binlog2sql.py -h127.0.0.1 -P13306 -uroot -p \
  --start-file=mysql-bin.000002 --flashback

Other tools such as MyFlash are also available.

Conclusion

Data deleted by mistake can be recovered from binlog as long as binlog was enabled, preferably in ROW mode. Open‑source utilities can automate parsing and generation of reverse operations.