How to Recover Accidentally Deleted MySQL Tables: Real-World Backup & Binlog Strategies

Enterprise Fault Recovery Case

Background: Running website system with MySQL database, 25 GB data, daily increment 10‑15 MB.

Backup strategy: Daily at 23:00 a scheduled task runs mysqldump for a full backup.

Fault time: At 10 AM a developer mistakenly dropped a core business table.

Approach:

1) Stop the service to avoid further damage. 2) Create a temporary database and restore the previous day's full backup. 3) Extract binlog entries from 23:00 of the previous day to 10:00 of the fault day and apply them to the temporary database. 4) Test availability and integrity. 5) Two options before bringing the service back: a) Replace the production database with the temporary one and switch front‑end connections. b) Export the recovered table and import it into the original production environment.

a. Directly use the temporary database to replace the original production database, cutting over the front‑end to the new DB. b. Export the mistakenly deleted table separately and import it back into the original production environment.

Simulated Data

#!/bin/bash

num=1
while true;
 do
  mysql -uroot -p123 -e "insert into proc.proc1 value($num);commit;"
  (( num++ ))
  sleep 1
 done

Backup Command

# mysqldump -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full_$(date +%F).sql.gz

Simulate Table Deletion

mysql> drop table proc.proc;

Recovery Steps

1) Stop service to avoid further damage
# /etc/init.d/mysqld stop

2) Prepare new environment
# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
# /etc/init.d/mysqld start

3) Restore previous day's full backup to a temporary database
# scp /tmp/full_2022-08-19.sql.gz 172.16.1.61:/tmp/
# zcat /tmp/full_2022-08-19.sql.gz | mysql

4) Extract binlog between 23:00 and 10:00
# mysqlbinlog --start-position=7138 --stop-position=42855 /application/mysql/data/mysql-bin.000002 > /tmp/inc1.sql
# mysqlbinlog --start-position=42975 --stop-position=58870 /application/mysql/data/mysql-bin.000002 > /tmp/inc2.sql
# scp /tmp/inc* 172.16.1.61:/tmp/

5) Test and verify

6) Bring service back using either:
   a) Replace production DB with temporary DB and cut over front‑end.
   b) Import the recovered table into the original production DB.

Enterprise Incremental Recovery Practice

Background: Large website with MySQL, 500 GB data, daily updates 100‑200 MB.

Backup strategy: XtraBackup full backup every Saturday 00:00, incremental backups on other days at 00:00.

Fault scenario: On Wednesday afternoon a table was accidentally deleted.

How to recover?

Simulated Data

#!/bin/bash

num=1
while true;
 do
  mysql -uroot -p123 -e "insert into proc.proc1 value($num);commit;"
  (( num++ ))
  sleep 1
 done

Backup Commands

# Full backup on Saturday
innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)

# Incremental backup on Sunday
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir /backup/full_$(date +%F) /backup/inc_6

# Incremental backup on Monday
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir /backup/inc_6 /backup/inc_7

# Incremental backup on Tuesday
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir /backup/inc_7 /backup/inc_1

# Incremental backup on Wednesday
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir /backup/inc_1 /backup/inc_2

Delete Data

mysql> select * from ts;
+----+------+
| id | A    |
+----+------+
|  1 | 300  |
|  2 | 200  |
+----+------+
mysql> drop table test.ts;

Recovery Steps

1. Stop service and database
# /etc/init.d/mysqld stop

2. Prepare new environment

3. Clear data directory
# mv /application/mysql/data/ /usr/local/src/

4. Rebuild data
   a) Apply redo‑only to full backup
# innobackupex --apply-log --redo-only /backup/full_2022-08-19/

   b) Apply incremental redo‑only for each incremental backup in order
# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_6 /backup/full_2022-08-19/
# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_7 /backup/full_2022-08-19/
# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_1 /backup/full_2022-08-19/
# innobackupex --apply-log --incremental-dir=/backup/inc_2 /backup/full_2022-08-19/
# innobackupex --apply-log /backup/full_2022-08-19/

5. Restore data
# innobackupex --copy-back /backup/full_2022-08-19/

6. Set ownership
# chown -R mysql.mysql /application/mysql/data

7. Start database
# /etc/init.d/mysqld start

8. Extract binlog for the deletion period
# mysqlbinlog --start-position=184023 --stop-position=200666 /usr/local/src/data/mysql-bin.000003 > /tmp/inc_1.sql
# mysqlbinlog --start-position=200781 --stop-position=205830 /usr/local/src/data/mysql-bin.000003 > /tmp/inc_2.sql

Reference: https://www.cnblogs.com/wangchengww/p/16603009.html (original author retains copyright).