MySQL Data Recovery: Backup Strategies, Point‑in‑Time Restoration, Table Recovery, and Flashback Techniques

In daily operations, accidental deletions or wrong statements can lead to irreversible data loss if proper backups and binlog settings are not in place. The prerequisite for any recovery is a reliable backup and an enabled binlog with ROW format.

Full Recovery

Full recovery uses a backup file to restore the entire database. For mysqldump backups, the command is:

gzip -d backup.sql.gz | mysql -u
-h
-P
-p

For xtrabackup backups, the steps are:

# Step 1: Decompress (if needed)
innobackupex --decompress
# Step 2: Apply logs
innobackupex --apply-log
# Step 3: Copy files back
innobackupex --datadir=
--copy-back

Point‑in‑Time Recovery

Point‑in‑time recovery relies on binlog to replay changes from the backup moment to a specific timestamp. Example steps:

show create table mytest.mytest \G;
# Insert data continuously
while true; do mysql -S /tmp/mysql.sock -e 'insert into mytest.mytest(ctime)values(now())'; date; sleep 1; done

Take a backup with:

mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -S /tmp/mysql.sock -A > backup.sql

Find the binlog position in the backup:

head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'

Extract the relevant binlog segment:

mysqlbinlog --start-position=39654 --stop-datetime='2019-08-09 11:01:54' /data/mysql_log/mysql_test/mysql-bin.000032 > backup_inc.sql

Apply the full backup and then the incremental binlog:

mysql -S /tmp/mysql.sock < backup.sql
mysql -S /tmp/mysql.sock < backup_inc.sql

Recovering a Single Table

To restore only one table from a mysqldump file:

# Extract the database section
sed -n '/^-- Current Database: `mytest`/,/^-- Current Database:/p' backup.sql > backup_mytest.sql

# Extract CREATE TABLE statement
sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `mytest`/!d;q' backup_mytest.sql > mytest_table_create.sql

# Extract INSERT statements
grep -i 'INSERT INTO `mytest`' backup_mytest.sql > mytest_table_insert.sql

# Restore
mysql -u
-p mytest < mytest_table_create.sql
mysql -u
-p mytest < mytest_table_insert.sql

For xtrabackup you can restore an InnoDB table by discarding the tablespace, copying the .ibd file, and importing it back.

Skipping Erroneous SQL Statements

If a destructive statement such as DROP TABLE b has been executed, you can skip it by extracting binlog segments before and after the statement and replaying them separately:

# Extract before the DROP
mysqlbinlog -vv --start-position=38414 --stop-position=120629 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_1.sql

# Extract after the DROP
mysqlbinlog -vv --start-position=120747 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_2.sql

# Apply full backup and then the two incremental parts
mysql -S /tmp/mysql.sock < backup.sql
mysql -S /tmp/mysql.sock < backup_inc_1.sql
mysql -S /tmp/mysql.sock < backup_inc_2.sql

When GTID is enabled, skipping is simpler: set the session GTID to the offending transaction, commit an empty transaction, and resume replication.

SET SESSION GTID_NEXT='the_GTI D_value';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;

Flashback (Rollback) Tools

Flashback restores the database to a state before a DML operation. It requires binlog in ROW format. Two open‑source tools are highlighted:

binlog2sql

Installation:

wget https://github.com/danfengcao/binlog2sql/archive/master.zip -O binlog2sql.zip
unzip binlog2sql.zip
cd binlog2sql-master/
pip install -r requirements.txt

Generate rollback SQL:

python binlog2sql/binlog2sql.py --flashback \
  -h
-P
-u
-p'
' -d
-t
\
  --start-file='
' \
  --start-datetime='
' \
  --stop-datetime='
' > ./flashback.sql

MyFlash

Installation (CentOS):

yum install gcc* pkg-config glib2 libgnomeui-devel -y
wget https://github.com/Meituan-Dianping/MyFlash/archive/master.zip -O MyFlash.zip
unzip MyFlash.zip
cd MyFlash-master
gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
mv binary /usr/local/MyFlash
ln -s /usr/local/MyFlash/flashback /usr/bin/flashback

Generate rollback statements:

flashback --databaseNames=
--binlogFileNames=
--start-position=
--stop-position=

Parse the output and apply:

mysqlbinlog -vv binlog_output_base.flashback | mysql -u
-p

Conclusion

Proper backup, binlog configuration, and knowledge of recovery tools are essential to avoid permanent data loss in MySQL. By following the procedures above, you can perform full restores, point‑in‑time restores, table‑level restores, skip harmful statements, and execute flashback operations efficiently.