MySQL Binlog Data Recovery After Accidental Bulk Deletion: Case Study and Best Practices

Incident Origin

Our system provides an Excel import feature that can load data in a specific format into the database. The client had many similarly‑named files and selected the wrong Excel file, which happened to be parsable by the system. Over 60,000 useless rows were imported, occupying only database space but causing the client great distress because he is a compulsive‑type user.

The client wanted the erroneous rows removed, but the management platform lacked a bulk‑delete function, forcing the development team to delete rows one by one. The client therefore asked the R&D team to delete the data directly from the database.

Deletion Process

Although directly manipulating a production database is a violation of policy, the request had to be fulfilled. In a test environment a user table was created and populated with test data to simulate the production table.

The developer ran the following SELECT to locate the erroneous rows:

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

Instead of modifying this query to a DELETE, the developer wrote a new DELETE statement but omitted the deptid=100 condition:

delete from t_user where age>18;

This mistake removed more than 100,000 rows, breaking many core business functions that depend on the table.

The incident was reported immediately, the service was stopped, customers were notified, and a data‑recovery effort began.

Data Recovery

No backups existed, so the team relied on MySQL binary logs (binlog). The binlog records all write operations (INSERT, DELETE, UPDATE, etc.) when enabled.

First, they verified that log_bin was ON:

SHOW VARIABLES LIKE 'LOG_BIN%';

They located the binlog files in /var/lib/mysql , identified the fourth binlog file (bin‑log.000004) as the likely source, and used mysqlbinlog with time filters to extract the relevant statements:

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 pseudo‑SQL was saved to tmp.log , cleaned up with bulk‑replace operations in a text editor, and transformed into executable SQL statements.

After testing the cleaned SQL on a staging database, it was executed on the production database, restoring the mistakenly deleted rows.

Finally, the originally requested 60,000 erroneous rows were deleted properly, completing the client’s request.

Key Recommendations

1. Developers should not have direct access to production databases. Access should be limited to DBAs or operations staff, with temporary accounts granted only when absolutely necessary.

2. Use read‑only accounts for production queries. This reduces the risk of accidental data modification when using GUI tools.

3. Disable autocommit and require peer review. Before executing DML on production, turn off autocommit, have another engineer review the statements, and only commit after confirmation.

4. Always backup before modifying data. Create a backup table (e.g., CREATE TABLE t_user_bak LIKE t_user; ) and copy data into it before any destructive operation.

5. Implement regular automated backups. Configure MySQL to write binlogs, schedule periodic logical or physical backups, and store copies off‑site.

Binlog Log

Binlog is a binary file that records all write operations. It can be enabled via the MySQL configuration file ( /etc/my.cnf ) with parameters such as:

# Enable binlog and set storage directory
log_bin = /var/lib/mysql/bin-log
# Index file location
log_bin_index = /var/lib/mysql/mysql-bin.index
# Auto‑expire logs after 30 days
expire_logs_days = 30
# Log format (STATEMENT, ROW, MIXED)
binlog_format = row

Three formats exist:

ROW Format

Records each row change, providing precise recovery but generating large logs for bulk operations.

STATEMENT Format

Records the original SQL statements, resulting in smaller logs but potential inconsistencies during replication.

MIXED Format

Combines both; MySQL chooses the appropriate format per statement.

mysqlbinlog Command

The mysqlbinlog utility converts binary logs to readable text and supports filtering options such as --start-datetime , --stop-datetime , --start-position , and --stop-position . Example usages:

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