How to Recover Accidentally Deleted MySQL Data Using Binlog

Incident Cause

The system allowed Excel data import. The client selected the wrong file containing over 60,000 irrelevant rows, which the system successfully parsed and stored, occupying database space but not affecting operation.

The client, a compulsive perfectionist, demanded removal of these rows, but the platform lacked a bulk‑delete feature, forcing a manual, labor‑intensive process.

Deletion Process

Developers were asked to delete the rows directly in the production database, which is a policy violation but was done to satisfy the client.

Because production data and schema are confidential, the focus is on recovery strategy rather than the actual data; therefore I created a test user table in a test environment and imported sample data to simulate production.

After confirming the 60,000 rows were erroneous, a SELECT query was used to locate them:

select * from t_user where age>18 and deptid=100;

Instead of modifying this query, a new DELETE statement was written without the deptid=100 condition: delete from t_user where age>18; This mistake removed more than 100,000 rows, breaking many core business functions.

Data Recovery

No backups existed, so the team relied on MySQL binary logs (binlog) to reconstruct the lost data.

Binlog records all write operations (INSERT, DELETE, UPDATE, CREATE, ALTER, DROP) when enabled.

First, verify binlog is enabled: SHOW VARIABLES LIKE 'LOG_BIN%'; The output shows log_bin is ON, and the binlog files are stored in /var/lib/mysql with names like bin-log.000001.

Navigate to the binlog directory: cd /var/lib/mysql Identify the relevant binlog (the fourth file) and extract logs around the deletion time (10:40). Using mysqlbinlog with time filters:

mysqlbinlog -v --start-datetime='2021-06-10 10:35:00' --stop-datetime='2021-06-10 10:45:00' bin-log.000004 | grep t_user

The extracted logs contain pseudo‑SQL statements. Save them to a temporary file:

mysqlbinlog -v --start-datetime='2021-06-10 10:35:00' --stop-datetime='2021-06-10 10:45:00' bin-log.000004 > tmp.log

After opening tmp.log, replace the pseudo‑SQL placeholders (e.g., @1, @2) with actual column values using a text editor’s bulk‑replace feature.

In the pseudo‑SQL shown, @1 denotes the first column, @2 the second, and so on.

Validate the resulting SQL on a test database, then execute it on the production database to restore the deleted rows.

Key Recommendations

1. Developers should not have direct access to production databases. Production databases should be managed by DBAs or operations staff, with temporary read‑only accounts granted only when necessary.

2. Use read‑only accounts when accessing production. This reduces the risk of accidental data modification.

3. Disable autocommit and require peer review. Before executing any DML on production, turn off automatic commit and have another colleague verify the statements.

-- Disable autocommit
set @@autocommit=0;

-- Verify rows to delete
select * from t_user where age>18 and deptid=100;

-- Delete (if correct)
delete from t_user where age>18 and deptid=100;

-- If something is wrong, rollback
rollback;

-- After confirmation, commit
commit;

4. Always back up data before modification. Create a backup table and copy data:

create table t_user_bak like t_user;
INSERT into t_user_bak select * from t_user;
select * from t_user_bak;

5. Configure regular automated backups. Enable binlog, set retention policies, and store backups off‑site to protect against hardware failures.

Binlog Overview

Binlog (Binary Log) records all write operations. It is essential for replication and point‑in‑time recovery.

To enable binlog, edit /etc/my.cnf:

# Enable binlog and set storage directory
log_bin = /var/lib/mysql/bin-log
# Index file
log_bin_index = /var/lib/mysql/mysql-bin.index
# Retention
expire_logs_days = 30
# Log format (row, statement, mixed)
binlog_format = row

ROW Format

Records detailed row changes, which is useful for precise recovery but can generate large files.

STATEMENT Format

Stores the original SQL statements; files are smaller but may cause inconsistencies during replication.

MIXED Format

Combines both approaches, letting MySQL choose the optimal format per statement.

mysqlbinlog Command

The mysqlbinlog utility converts binary logs to readable text and supports filtering options. mysqlbinlog [options] log-files Common options include: -d: filter by database name -o: skip first N lines --start-datetime / --stop-datetime: time range --start-position / --stop-position: position range -v / -vv: verbose output with comments

Examples:

mysqlbinlog -d=fusion bin-log.000001

mysqlbinlog --start-datetime='2021-06-09 19:30:00' --stop-datetime='2021-06-09 19:50:00' bin-log.000001

mysqlbinlog --start-position 4300 --stop-position 10345 bin-log.000001 | mysql -uroot -p123456 fusion