Recovering Accidentally Deleted MySQL Data Using Binlog

Accidentally deleting data in a MySQL table can be disastrous. This article explains how to recover the lost rows by leveraging the binary log (binlog).

1. Ensure binlog is enabled

Check the status with:

SHOW VARIABLES LIKE 'log_bin';

If the result is OFF, enable it in my.cnf (or my.ini ) by adding:

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

Restart MySQL after the change.

2. Verify binlog format

Row‑based logging is required to capture the actual row values. Query the format:

SHOW VARIABLES LIKE 'binlog_format';

Set it to ROW if it is not:

[mysqld]
binlog_format=ROW

Restart MySQL again.

3. Locate the relevant binlog files

Current file:

SHOW MASTER STATUS\G

All files:

SHOW MASTER LOGS;

Binlog directory can be obtained with:

SHOW VARIABLES LIKE 'log_bin_basename';

4. Extract the statements that inserted the deleted rows

Use mysqlbinlog with a 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

Identify the INSERT statements (or the row events in ROW mode) and note their positions.

Example of a row‑based event:

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

5. Replay the recovered statements

Execute the extracted SQL file:

mysql -uroot -p < binlog.sql

or inside the client:

source binlog.sql;

6. Alternative: generate reverse statements

When the binlog contains a DELETE row event, you can reconstruct the missing row:

INSERT INTO person VALUES (1,'first');

Then replay as in step 5.

7. Helpful open‑source tools

binlog2sql (by Meituan) parses binlog and can output forward or flashback SQL:

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

Add --flashback to get reverse statements.

Another tool is MyFlash (https://github.com/Meituan-Dianping/MyFlash).

In summary, recovering deleted MySQL data is possible as long as binlog was enabled and set to ROW mode. The workflow consists of enabling binlog, locating the correct file, extracting the relevant events with mysqlbinlog , and replaying or reconstructing the missing rows.