MySQL Data Recovery: Full, Incremental, Point‑in‑Time and Table Restoration with Binlog Skipping Techniques

Before any recovery, ensure you have regular backups and that the binary log (binlog) is enabled with ROW format; without backups, deleted data is unrecoverable, and without ROW binlog you cannot perform flashback operations.

1. Direct full recovery with mysqldump

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

2. Full recovery with Percona XtraBackup

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

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

# Step 3: Copy files back to the data directory
innobackupex --datadir=/var/lib/mysql --copy-back <backup_dir></code>

3. Point‑in‑time recovery using binlog

<code># Create a test table
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
mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -S /tmp/mysql.sock -A > backup.sql

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

# Extract incremental binlog up to a specific time
mysqlbinlog --start-position=39654 --stop-datetime='2019-08-09 11:01:54' /data/mysql_log/mysql_test/mysql-bin.000032 > backup_inc.sql

# Apply full backup then incremental log
mysql -S /tmp/mysql.sock < backup.sql
mysql -S /tmp/mysql.sock < backup_inc.sql

# Verify restored rows
SELECT COUNT(*) FROM mytest.mytest WHERE ctime < '2019-08-09 11:01:54';</code>

4. Restoring a single table

From a mysqldump file:

<code># 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 structure and data
mysql -u<user> -p mytest < mytest_table_create.sql
mysql -u<user> -p mytest < mytest_table_insert.sql</code>

From an xtrabackup backup (MyISAM): copy .frm , .MYD , .MYI files to the data directory and run CHECK TABLE . For InnoDB, recreate the table, discard the tablespace, copy the .ibd file, then import the tablespace.

5. Skipping erroneous SQL statements

When a destructive statement (e.g., DROP TABLE ) cannot be rolled back, you can skip it using one of three approaches:

5.1 Using backup files (no GTID) : locate the binlog position of the backup and the position of the unwanted statement, extract the binlog before and after the statement, and apply them separately.

5.2 Using GTID : find the GTID of the offending transaction and execute a dummy transaction with the same GTID to mark it as applied.

5.3 Using a delayed replica : stop the replica, determine the position just before the bad statement, start the replica with START SLAVE UNTIL MASTER_LOG_FILE='mysql-bin.000039', MASTER_LOG_POS=35134 , then skip the statement with SET GLOBAL sql_slave_skip_counter=1; START SLAVE; .

Example for GTID skipping:

<code>SET SESSION GTID_NEXT='the‑offending‑GTID';
BEGIN; COMMIT;
SET SESSION GTID_NEXT=AUTOMATIC;</code>

6. Flashback tools

binlog2sql (Meituan open‑source) parses binlog to generate rollback SQL:

<code>wget https://github.com/danfengcao/binlog2sql/archive/master.zip -O binlog2sql.zip
unzip binlog2sql.zip
cd binlog2sql-master/
pip install -r requirements.txt
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</code>

MyFlash (Meituan) is a compiled C tool for flashback on MySQL 5.6/5.7 with ROW binlog:

<code># Install dependencies (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=<dbname> --binlogFileNames=<binlog_file> \
    --start-position=<start_pos> --stop-position=<stop_pos>
# Apply generated statements
mysqlbinlog -vv binlog_output_base.flashback | mysql -u<user> -p</code>

These procedures enable reliable recovery of MySQL data after accidental deletions, schema changes, or other destructive operations.