Recover Deleted MySQL Data with Binlog and GTID Techniques

Overview

Binlog (binary log) records the SQL statements executed on a MySQL server. When data is mistakenly deleted, the binlog can be used to reconstruct the lost rows. Two restoration methods are covered: traditional binary‑file recovery and GTID‑based recovery.

Traditional Binary Log Recovery

1. Modify configuration file

vi /etc/my.cnf
server-id=1
log-bin=binlog

# Restart MySQL service systemctl restart mysqld 2. Operate database

mysql> create database mydb charset utf8mb4;
mysql> use mydb;
mysql> create table test(id int) engine=innodb charset=utf8mb4;
mysql> insert into test values(1),(2),(3),(4);
mysql> commit;
mysql> update test set id=10 where id=4;
mysql> commit;
mysql> select * from test;
mysql> drop database mydb;

3. View binary log information

mysql> show master status\G;
# Output shows File: binlog.000001, Position: 219 …
mysql> show binlog events in 'binlog.000001';

4. Export relevant binary log segment

mysqlbinlog --start-position=219 --stop-position=1868 /var/lib/mysql/binlog.000001 > /tmp/binlog.sql

5. Restore data

# Temporarily disable binary logging
mysql> set sql_log_bin=0;
# Apply the exported statements
mysql> source /tmp/binlog.sql;
# Re‑enable binary logging
mysql> set sql_log_bin=1;

6. Verify restoration

mysql> show databases;
mysql> use mydb;
mysql> select * from test;

GTID‑Based Binary Log Recovery

1. Modify configuration file

vi /etc/my.cnf
server-id=1
log-bin=binlog
gtid_mode=ON
enforce_gtid_consistency=true
log_slave_updates=1

# Restart MySQL service systemctl restart mysqld 2. Operate database

mysql> create database mydb1;
mysql> use mydb1;
mysql> create table t1(id int) engine=innodb charset=utf8mb4;
mysql> insert into t1 values(1),(2),(3),(11),(12);
mysql> commit;
mysql> drop database mydb1;

3. View binary log information

mysql> show master status\G;
# Example output: File: binlog.000003, Position: 1944
mysql> show binlog events in 'binlog.000003';

4. Export GTID‑filtered binary log

# The 8th transaction deletes the database; keep transactions 1‑7
mysqlbinlog --skip-gtids --include-gtids='51d3db57-bf69-11ea-976c-000c2911a022:1-7' /var/lib/mysql/binlog.000003 > /tmp/gtid.sql

Parameters:

--include-gtids: include specified GTIDs

--exclude-gtids: exclude specified GTIDs

--skip-gtids: skip GTID handling

5. Restore data

# Disable binary logging
mysql> set sql_log_bin=0;
# Apply the GTID‑filtered statements
mysql> source /tmp/gtid.sql;
# Re‑enable binary logging
mysql> set sql_log_bin=1;

6. Verify restoration

mysql> show databases;
mysql> use mydb1;
mysql> select * from t1;

Both methods should result in the original rows being present after the restoration process.