Master MySQL Data Recovery: From Full Backups to Point‑in‑Time Restoration

1. Prerequisite

Ensure backups exist and binlog is enabled with row format; without backups, deleted tables are unrecoverable, and without binlog you cannot perform flashback.

2. Direct Restore

2.1 mysqldump full restore

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

2.2 xtrabackup full restore

# Step 1: decompress (if needed)
innobackupex --decompress <backup_dir>

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

# Step 3: copy files back
innobackupex --datadir=<MySQL_data_dir> --copy-back <backup_dir>

2.3 Point‑in‑time recovery

Use the binlog to locate logs from the backup position to the desired timestamp, then apply them.

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

# Extract incremental logs
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 backup and the incremental log, the data is restored to the specified point.

3. Restore a Single Table

3.1 From mysqldump

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

# Extract CREATE TABLE
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<user> -p mytest < mytest_table_create.sql
mysql -u<user> -p mytest < mytest_table_insert.sql

3.2 From xtrabackup

For MyISAM tables, copy .frm, .MYD, .MYI files to the data directory and run CHECK TABLE. For InnoDB tables, ensure innodb_file_per_table=on, discard the existing tablespace, copy the .ibd file, then import it with ALTER TABLE … IMPORT TABLESPACE.

4. Skip Erroneous SQL

4.1 Using backup files

Locate the binlog position of the backup and the position of the unwanted DROP TABLE statement, then split the binlog into two parts: before the drop and after the drop, and apply them separately.

# First part (up to drop start)
mysqlbinlog -vv --start-position=38414 --stop-position=120629 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_1.sql

# Second part (after drop end)
mysqlbinlog -vv --start-position=120747 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_2.sql

Apply the full backup, then the two incremental files; the drop statement is omitted.

4.2 Using a delayed slave

Stop the slave, find the position just before the erroneous statement, start the slave with START SLAVE UNTIL MASTER_LOG_POS=…, then skip the statement with SET GLOBAL sql_slave_skip_counter=1 and resume replication.

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;

4.2.2 Skipping with GTID

When GTID is enabled, execute a dummy transaction with the same GTID as the unwanted statement to skip it.

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

5. Flashback Tools

5.1 binlog2sql

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

# Generate flashback SQL
python binlog2sql/binlog2sql.py --flashback -h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table> \
    --start-file='<binlog_file>' --start-datetime='<start_time>' --stop-datetime='<stop_time>' > flashback.sql

5.2 MyFlash

# Install dependencies (CentOS)
yum install gcc* pkg-config glib2 libgnomeui-devel -y

# Download and compile
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=<dbname> --binlogFileNames=<binlog_file> \
    --start-position=<start_pos> --stop-position=<stop_pos>

Both tools require the binlog to be in ROW format and are limited to MySQL 5.6/5.7 for DML flashback.