How to Recover Accidentally Deleted MySQL Data Using my2sql

Overview

Accidental DELETE statements can erase critical data. The open‑source my2sql tool can parse MySQL binlog files and generate INSERT statements to roll back deletions. Repository: https://github.com/liuhr/my2sql

Prerequisites

Binlog must be in ROW format with binlog_row_image=FULL.

Only DML statements can be rolled back; DDL cannot.

For MySQL 8.0, the user must use mysql_native_password authentication.

1. Create Test Database, Table and User

create database d_recover;
use d_recover;
CREATE TABLE del_t1 (
  id int NOT NULL AUTO_INCREMENT,
  a int NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8mb4;
insert into del_t1 values (1,1),(2,2);
CREATE USER `u_rollback`@`127.0.0.1` IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'IgdI8G_aUU';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `u_rollback`@`127.0.0.1`;

2. Install my2sql

mkdir -p /data/backup/rollback
cd /data/backup/rollback
wget https://github.com/liuhr/my2sql/blob/master/releases/centOS_release_7.x/my2sql

3. Simulate Accidental Deletion

delete from d_recover.del_t1;

4. Copy Relevant Binlog

mkdir -p /data/backup/rollback
cp /data/mysql/binlog/mysql-bin.000012 /data/backup/rollback/

5. Identify Start and Stop Positions

Use mysqlbinlog to extract the transaction range.

cd /data/backup/rollback
mysqlbinlog mysql-bin.000012 \
  --start-datetime='2023-06-21 01:20:00' \
  --stop-datetime='2023-06-21 01:40:00' \
  --base64-output=decode-rows -v > operation.sql

The extracted binlog shows a start position around 3556 and an end position around 3719 .

6. Generate Rollback SQL

/data/backup/rollback/my2sql \
  -user u_rollback \
  -password 'IgdI8G_aUU' \
  -host 127.0.0.1 \
  -databases d_recover \
  -tables del_t1 \
  -work-type rollback \
  -start-file mysql-bin.000012 \
  -start-pos 3556 \
  -stop-pos 3719 \
  -output-dir recover_01

Inspect the generated file:

cat recover_01/rollback.12.sql

7. Apply the Rollback

mysql -uroot -p < recover_01/rollback.12.sql

Verify the data is restored: select * from d_recover.del_t1; The rows (1,1) and (2,2) should reappear.

Additional Recovery Options

Other approaches include using full backups with binlog, delayed replica recovery, or creating a new replica to roll back to a point before the mistake.