MySQL Data Recovery: Backup, Point‑in‑Time Restore, Table Restoration, and Skipping Erroneous SQL

In daily work, accidental deletions of databases or tables happen due to typos, wrong conditions, or connecting to production instances; the article provides systematic ways to recover data instead of abandoning the job.

1. Preface

Data recovery requires proper backups and binlog enabled in ROW format. Without backups, recovery is impossible unless the file is still open. Without binlog or with non‑ROW format, flashback cannot be performed and only traditional backup‑restore is possible.

2. Direct Recovery

2.1 Restore from mysqldump backup

Use the following command to decompress and import the dump:

gzip -d backup.sql.gz | mysql -u<user> -h<host> -P<port> -p

2.2 Restore from xtrabackup backup

Steps:

# Step 1: Decompress (skip if already uncompressed)
innobackupex --decompress <backup_dir>

# Step 2: Apply logs
innobackupex --apply-log <backup_dir>

# Step 3: Copy files back to the data directory
innobackupex --datadir=<MySQL_data_dir> --copy-back <backup_dir>

2.3 Point‑in‑time recovery

Find the binlog position from the backup, then extract logs up to the desired timestamp:

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

After applying the full dump and the incremental log, the database is restored to the specified moment.

3. Restoring a Single Table

3.1 From mysqldump backup

Extract the table’s schema and data, then import:

# Extract all objects of the database
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 schema
mysql -u<user> -p mytest < mytest_table_create.sql

# Restore data
mysql -u<user> -p mytest < mytest_table_insert.sql

3.2 From xtrabackup backup

For MyISAM tables, copy the *.frm, *.MYD, *.MYI files to the data directory and check the table. For InnoDB tables (with innodb_file_per_table=ON ), create a new instance, recreate the table structure, discard the tablespace, copy the *.ibd file back, import the tablespace, and finally verify the table.

4. Skipping Erroneous SQL

When a destructive statement (e.g., DROP TABLE ) cannot be flashback, you can skip it by restoring from backup and applying binlog up to the statement, then resuming.

4.1 Skip using backup files

Without GTID:

# Find the binlog position of the backup
head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'

# Find the position of the DROP TABLE statement
mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000034 | grep -i -B 3 'drop table `b`'

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

# Restore full backup and then apply the two incremental logs
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

With GTID, set GTID_NEXT to the offending transaction, commit, and then continue replication.

4.2 Skip using delayed replica

Stop the replica, sync it to the position just before the unwanted statement using START SLAVE UNTIL , then set sql_slave_skip_counter=1 and resume.

stop slave;
change master to master_delay=0;
start slave until master_log_file='mysql-bin.000039', master_log_pos=35134;
set global sql_slave_skip_counter=1;
start slave;

5. Flashback

Flashback reverts DML errors by generating inverse statements; it works only when binlog is in ROW format.

5.1 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<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name> \
  --start-file='<binlog_file>' --start-datetime='<start_time>' \
  --stop-datetime='<stop_time>' > ./flashback.sql

5.2 MyFlash

Installation (CentOS):

# Dependencies
yum install gcc* pkg-config glib2 libgnomeui-devel -y

# Download and unzip
wget https://github.com/Meituan-Dianping/MyFlash/archive/master.zip -O MyFlash.zip
unzip MyFlash.zip
cd MyFlash-master

# Compile
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=<dbname> --binlogFileNames=<binlog_file> --start-position=<start_pos> --stop-position=<stop_pos>

Then parse the output with mysqlbinlog -vv binlog_output_base.flashback | mysql -u<user> -p to apply the rollback.