Master MySQL Data Recovery: Full Restores, Point‑In‑Time, and Flashback Techniques

1. Introduction

Data loss caused by accidental drops or wrong conditions can be mitigated by maintaining backups and enabling binlog in ROW format. Without backups, deleted tables are unrecoverable; if binlog is disabled or not in ROW format, flashback is impossible.

2. Direct Restore

Full restores use backup files.

2.1 Restore from mysqldump

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

2.2 Restore from xtrabackup

<code># Step 1: Decompress (if needed)
innobackupex --decompress <backup_dir>
# Step 2: Apply logs
innobackupex --apply-log <backup_dir>
# Step 3: Copy files to data directory
innobackupex --datadir=<MySQL_data_dir> --copy-back <backup_dir></code>

2.3 Point‑in‑time recovery

Requires binlog. Find the log position from the backup, then extract logs up to the desired timestamp.

<code># Create 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
# Backup
mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -S /tmp/mysql.sock -A > backup.sql
# Find binlog position
head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
# Extract 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 full and incremental restores
mysql -S /tmp/mysql.sock < backup.sql
mysql -S /tmp/mysql.sock < backup_inc.sql</code>

3. Restoring a Single Table

3.1 From mysqldump

<code># Extract table data
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</code>

3.2 From xtrabackup

For MyISAM tables, copy the .frm, .MYD, .MYI files to the data directory and check the table. For InnoDB tables, ensure

innodb_file_per_table=on

, discard the tablespace, copy the .ibd file, import tablespace, and finally dump and import the data.

<code># MyISAM example
cp t_myisam.frm t_myisam.MYD t_myisam.MYI <datadir>/mytest/
mysql -e 'CHECK TABLE t_myisam';
# InnoDB example steps
# 1. Create identical table on new instance
# 2. ALTER TABLE t_innodb DISCARD TABLESPACE;
# 3. Copy t_innodb.ibd to data directory
# 4. ALTER TABLE t_innodb IMPORT TABLESPACE;
# 5. FLUSH TABLE t_innodb; CHECK TABLE t_innodb;</code>

4. Skipping Erroneous SQL

When a DROP TABLE cannot be rolled back, you can skip it using backup files, GTID, or delayed replica.

4.1 Using backup files (no GTID)

<code># Find backup log position
head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
# Find position of DROP TABLE
mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000034 | grep -i -B 3 'drop table `b`'
# Extract logs before and after the DROP
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 then apply increments
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</code>

4.2 Using GTID

<code># Skip by executing empty transaction with same GTID
SET SESSION GTID_NEXT='the_GTI D_value';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;</code>

4.3 Using delayed replica

<code># Stop slave, sync to position before DROP, skip one event, 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;</code>

5. Flashback Tools

5.1 binlog2sql

Open‑source tool to parse binlog and generate rollback statements.

<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_name> \
  --start-file='<binlog_file>' --start-datetime='<start_time>' --stop-datetime='<stop_time>' > ./flashback.sql</code>

5.2 MyFlash

Meituan‑Dianping tool for DML rollback (MySQL 5.6/5.7, ROW binlog).

<code># 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>
# Parse and apply
mysqlbinlog -vv binlog_output_base.flashback | mysql -u<user> -p</code>