How to Recover Accidentally Deleted MySQL Tables: Real Enterprise Cases

1. Enterprise Fault Recovery Case

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

Backup strategy: Daily at 23:00, scheduled mysqldump full backup.

Failure time: At 10:00 a developer mistakenly deleted a core business table.

Recovery steps:

Stop the service to prevent further damage.

Create a temporary database and restore the previous day's full backup.

Extract binlog from 23:00 of the previous day to 10:00 of the failure day and apply to the temporary database.

Test data integrity and availability.

Two options to bring service back:

Replace the production database with the temporary one and switch front‑end connections.

Export the recovered table and import it back into the original production database.

Resume business.

a. Directly use the temporary database to replace the production one, cutting over front‑end applications. b. Export the recovered table separately and import it into the original production environment.

Simulation 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

Simulated Deletion

mysql> drop table proc.proc;

Recovery Procedure

1) Stop service
# /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 full backup to temporary host
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 segments
# 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

5) Apply binlog to temporary database, test, then switch back as described above.

2. Enterprise Incremental Recovery Practice

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

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

Failure scenario: On Wednesday at 14:00 a table was accidentally deleted.

Simulation 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
innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)

# Incremental backups (examples)
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir /backup/full_$(date +%F) /backup/inc_6
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir /backup/inc_6 /backup/inc_7
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir /backup/inc_7 /backup/inc_1
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir /backup/inc_1 /backup/inc_2

Deletion Simulation

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

mysql> drop table test.ts;

Recovery Steps

1. Stop MySQL service.
# /etc/init.d/mysqld stop

2. Prepare new environment and move old data directory.
# mv /application/mysql/data/ /usr/local/src/

3. Apply logs:
   innobackupex --apply-log --redo-only /backup/full_2022-08-19/
   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/

4. Copy back data:
   innobackupex --copy-back /backup/full_2022-08-19/

5. Set ownership and start MySQL.
   chown -R mysql.mysql /application/mysql/data
   /etc/init.d/mysqld start

6. Extract relevant binlog segments between deletion time and recovery point and apply as needed.
   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