Recover Accidentally Deleted MySQL Data: Binlog, Backups, and Third‑Party Tools

Overview

Accidental data loss in MySQL (e.g., a DELETE without WHERE) can be mitigated by three main recovery approaches: binlog replay, backup restoration, and specialized third‑party tools.

Recovery Methods Overview

Binlog recovery : Replay binary logs (ROW format) to reconstruct deleted rows.

Backup recovery : Restore from logical dumps ( mysqldump) or physical snapshots ( xtrabackup).

Third‑party tools : Directly extract data from InnoDB files (e.g., undrop‑for‑innodb, Percona data‑recovery).

1. Binlog Recovery

Principle

When binlog_format=ROW, MySQL records the exact before‑and‑after values for each row change. By extracting the DELETE events and converting them to INSERT statements, the lost rows can be restored.

Pre‑condition Checks

-- Verify binlog is enabled
SHOW VARIABLES LIKE 'log_bin';   -- must be ON

-- Verify ROW format
SHOW VARIABLES LIKE 'binlog_format';   -- must be ROW

-- Check retention policy
SHOW VARIABLES LIKE 'expire_logs_days';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

-- List current binlog files
SHOW BINARY LOGS;

-- Identify the binlog containing the erroneous DELETE
SHOW MASTER STATUS;

Recovery Steps

Locate the error

mysqlbinlog --base64-output=decode-rows -vv /var/lib/mysql/binlog.000123 \
  | grep -A 20 "DELETE FROM.*users"
# Or filter by time range
mysqlbinlog --start-datetime="2024-01-15 14:00:00" \
  --stop-datetime="2024-01-15 15:00:00" \
  --base64-output=decode-rows -vv /var/lib/mysql/binlog.000123 > /tmp/binlog.txt

Parse the binlog

mysqlbinlog --base64-output=decode-rows -vv \
  --start-position=4 --stop-position=999999 \
  /var/lib/mysql/binlog.000123 > /tmp/binlog_full.txt

grep -A 50 "### DELETE FROM \`mydb\`\.\`users\`" /tmp/binlog_full.txt

Generate rollback SQL Python script binlog2sql.py converts the parsed DELETE rows into INSERT statements:

#!/usr/bin/env python3
import re, sys

def parse_binlog_delete(content, table_name):
    pattern = rf"### DELETE FROM `\w+`\.`{table_name}`
((?:###.*
)+)"
    matches = re.findall(pattern, content)
    inserts = []
    for match in matches:
        values = []
        for line in match.strip().split('
'):
            if line.startswith('###   @'):
                values.append(line.split('=', 1)[1])
        if values:
            inserts.append(f"INSERT INTO {table_name} VALUES ({', '.join(values)});")
    return inserts

if __name__ == '__main__':
    with open(sys.argv[1]) as f:
        sqls = parse_binlog_delete(f.read(), 'users')
    for s in sqls:
        print(s)

python3 binlog2sql.py /tmp/binlog_full.txt > /tmp/recovery.sql
# Verify
head -20 /tmp/recovery.sql
# Apply
mysql -u root -p mydb < /tmp/recovery.sql

Using the binlog2sql Utility

# Install via pip
pip install binlog2sql
# Or clone the latest source
git clone https://github.com/danfengcao/binlog2sql.git
cd binlog2sql
pip install -r requirements.txt

# Generate flashback SQL (DELETE → INSERT)
python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'password' -d mydb -t users \
  --start-datetime="2024-01-15 14:00:00" --stop-datetime="2024-01-15 15:00:00" \
  --flashback > /tmp/rollback.sql

Limitations

Binlog must be enabled and retained; purged logs cannot be used.

ROW format is required; STATEMENT format lacks row‑level data.

Large binlogs can be slow to scan.

DROP TABLE events cannot be recreated from binlog alone.

2. Backup Recovery

Logical Backup (mysqldump)

# Locate latest dump
ls -la /backup/mysql/
# Restore to a temporary database
mysql -u root -p -e "CREATE DATABASE mydb_recovery"
gunzip -c /backup/mysql/mydb_20240115.sql.gz | mysql -u root -p mydb_recovery
# Extract the needed table
mysqldump -u root -p mydb_recovery users > /tmp/users_backup.sql
# Load into production
mysql -u root -p mydb < /tmp/users_backup.sql
# Optionally replay binlog for changes after the dump
mysqlbinlog --start-datetime="2024-01-15 03:00:00" \
  --stop-datetime="2024-01-15 14:00:00" \
  --database=mydb /var/lib/mysql/binlog.* | mysql -u root -p mydb

Physical Backup (xtrabackup)

# Prepare (merge incremental if needed)
xtrabackup --prepare --target-dir=/backup/full
# Stop MySQL
systemctl stop mysqld
# Backup current data directory
mv /var/lib/mysql /var/lib/mysql.bak
# Restore the backup
xtrabackup --copy-back --target-dir=/backup/full
# Fix permissions
chown -R mysql:mysql /var/lib/mysql
# Start MySQL
systemctl start mysqld
# Apply binlog after restore
cat /backup/full/xtrabackup_binlog_info   # e.g., binlog.000123 456789
mysqlbinlog --start-position=456789 /var/lib/mysql/binlog.000123 | mysql -u root -p

Partial Table Recovery

# Extract a single table from a logical dump
sed -n '/^-- Table structure for table `users`/,/^-- Table structure for table/p' backup.sql > users.sql
# Or use mydumper/myloader for per‑table files
mydumper -u root -p password -B mydb -o /backup/mydumper/
myloader -u root -p password -B mydb -d /backup/mydumper/ -T users

Point‑in‑Time Recovery (PITR)

# Restore the latest full backup
mysql -u root -p < /backup/full_backup.sql
# Replay binlog up to the desired moment
mysqlbinlog --start-position=154 \
  --stop-datetime="2024-01-15 13:59:00" \
  /var/lib/mysql/binlog.000100 /var/lib/mysql/binlog.000101 | mysql -u root -p

3. Third‑Party Tools

undrop‑for‑innodb

# Build the tool
git clone https://github.com/twindb/undrop-for-innodb.git
cd undrop-for-innodb
make

# Recovery (stop MySQL first!)
systemctl stop mysqld
cp /var/lib/mysql/mydb/users.ibd /tmp/recovery/
# Obtain CREATE TABLE from .frm or information_schema
./c_parser -6f /tmp/recovery/users.ibd -t users.sql > /tmp/recovery/users_data.tsv
# Convert TSV to INSERTs and load into a new table

Percona Data Recovery Tool

Official repository: https://github.com/percona/percona-data-recovery-tool-for-innodb

4. Preventive Measures

Backup Strategy

# /etc/cron.d/mysql_backup
# Daily full backup at 03:00
0 3 * * * root /usr/local/bin/mysql_backup.sh full
# Hourly incremental backup
0 * * * * root /usr/local/bin/mysql_backup.sh incremental

#!/bin/bash
# mysql_backup.sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
case "$1" in
  full)
    mysqldump -u root -p'password' --all-databases --single-transaction \
      --master-data=2 --flush-logs | gzip > ${BACKUP_DIR}/full_${DATE}.sql.gz
    ;;
  incremental)
    LAST_FULL=$(ls -1d ${BACKUP_DIR}/full_* | tail -1)
    xtrabackup --backup --target-dir=${BACKUP_DIR}/inc_${DATE} \
      --incremental-basedir=${LAST_FULL}
    ;;
esac
# Cleanup old backups
find ${BACKUP_DIR} -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete
find ${BACKUP_DIR} -type d -name "full_*" -mtime +${RETENTION_DAYS} -exec rm -rf {} \;

Binlog Configuration

[mysqld]
log-bin = /var/lib/mysql/binlog
server-id = 1
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7   # MySQL 8.0: binlog_expire_logs_seconds = 604800
max_binlog_size = 500M
sync_binlog = 1

Permission Control

# Do not use root in production
CREATE USER 'app'@'%' IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE ON mydb.* TO 'app'@'%';
# Allow DELETE only on specific tables
GRANT DELETE ON mydb.temp_table TO 'app'@'%';
# DBA account with full privileges (audit required)
CREATE USER 'dba'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%';

SQL Auditing (optional)

# Enable general log temporarily (performance impact)
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
# Record connection info via init_connect
SET GLOBAL init_connect = 'INSERT INTO mysql.accesslog VALUES(connection_id(), current_user(), now())';

Delayed Replica

# On the replica
CHANGE MASTER TO MASTER_DELAY = 3600;  # 1‑hour delay

DELETE Best Practices

# Bad example (never)
DELETE FROM users;

# Good practice 1: SELECT first
SELECT * FROM users WHERE id = 123;
DELETE FROM users WHERE id = 123;

# Good practice 2: LIMIT for batch deletes
DELETE FROM users WHERE status = 'inactive' LIMIT 1000;

# Good practice 3: Use transactions
BEGIN;
DELETE FROM users WHERE id = 123;
SELECT ROW_COUNT();
COMMIT;  # or ROLLBACK on error

# Good practice 4: Rename then drop
RENAME TABLE users TO users_to_delete;
-- Verify for a period, then
DROP TABLE users_to_delete;

5. Real‑World Cases

Case 1 – UPDATE without WHERE (5 M rows)

# 1. Stop writes
# 2. Verify binlog availability
mysql -e "SHOW BINARY LOGS" | tail -5
# 3. Locate the UPDATE position
mysqlbinlog --base64-output=decode-rows -vv /var/lib/mysql/binlog.000156 \
  | grep -B5 "UPDATE.*orders.*SET.*status.*cancelled" | head -20
# 4. Generate rollback SQL
python binlog2sql.py -h127.0.0.1 -uroot -p -d orderdb -t orders \
  --start-position=12345 --stop-position=67890 --flashback > /tmp/rollback.sql
# 5. Verify and apply
head -100 /tmp/rollback.sql
mysql -u root -p orderdb < /tmp/rollback.sql
# 6. Validate
mysql -e "SELECT status, COUNT(*) FROM orderdb.orders GROUP BY status"

Time taken: ~2 hours.

Case 2 – DROP DATABASE with partial binlog

# 1. Restore yesterday's dump
mysql -u root -p < /backup/analytics_20240114.sql
# 2. Find DROP position
mysqlbinlog /var/lib/mysql/binlog.000155 | grep -n "DROP DATABASE"
# Assume line 156789
# 3. Replay binlog up to just before DROP
mysqlbinlog --stop-position=156788 /var/lib/mysql/binlog.000155 | mysql -u root -p analytics

Result: Data recovered up to 1 second before DROP; ~18 hours of data lost.

Case 3 – Binlog purge failure

# Disk pressure led to:
PURGE BINARY LOGS BEFORE '2024-01-10';
# Needed data existed only in purged logs; latest backup also pre‑dated purge.
# Undrop‑for‑innodb failed; commercial recovery quoted high cost with no guarantee.

Result: Data permanently lost.

Lessons Learned

Never rely solely on binlog; maintain regular backups.

Keep binlog in ROW format and back it up separately.

Critical operations should require approval and audit.

6. Core Takeaways

Backups are the safety net – without them recovery is unlikely.

Enable binlog with ROW format for precise point‑in‑time restores.

Apply least‑privilege principles to limit accidental DELETE s.

Perform large‑scale deletions in batches or within transactions.

Use a delayed replica to provide a time buffer for accidental writes.

Conduct post‑incident reviews to improve processes.

7. Emergency Response Flow

Detect incident → Stop writes immediately → Assess impact →
├─ Small impact → Direct binlog recovery
└─ Large impact → Notify stakeholders → Draft recovery plan →
    Prepare rollback environment → Execute recovery & verify →
    Restore service → Post‑mortem

References

MySQL Official Documentation – Point‑in‑Time Recovery: https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html

binlog2sql project: https://github.com/danfengcao/binlog2sql

Percona XtraBackup Documentation: https://docs.percona.com/percona-xtrabackup/