MySQL Data Recovery: Backup, Binlog, Point‑in‑Time Recovery and Tools

1. Preface

Data recovery requires proper backups and a row‑based binlog; without them, deleted tables or data may be unrecoverable. If binlog is not enabled or not in ROW format, flashback is impossible.

2. Direct Recovery

2.1 mysqldump full recovery

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

2.2 xtrabackup full recovery

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

2.3 Point‑in‑time recovery

Identify the binlog position from the backup, then extract logs up to the desired timestamp.

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

# Extract logs up to target 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 the full backup and then the incremental log:

# Full restore
mysql -S /tmp/mysql.sock < backup.sql

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

3. Recover a Single Table

3.1 From mysqldump

# Extract data for a specific 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 structure and data
mysql -u
-p mytest < mytest_table_create.sql
mysql -u
-p mytest < mytest_table_insert.sql

3.2 From xtrabackup

For MyISAM tables, copy the .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 the tablespace and verify.

4. Skip Erroneous SQL

4.1 Using backup files

Locate the binlog position of the unwanted DROP TABLE statement, split the binlog before and after that position, and restore the two parts separately.

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

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

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

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

# Restore
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

4.2 Using GTID

Set GTID_NEXT to the GTID of the unwanted transaction, execute a dummy transaction, then resume replication.

SET SESSION GTID_NEXT='[GTID]';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;

4.3 Using delayed slave

Stop the slave, sync up to the statement before the error with START SLAVE UNTIL , skip one event with SET GLOBAL sql_slave_skip_counter=1 , then start the slave again.

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. Open‑Source 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
-P
-u
-p'
' -d
-t
\
  --start-file='
' --start-datetime='
' \
  --stop-datetime='
' > ./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=
--binlogFileNames=
\
  --start-position=
--stop-position=
# Apply generated statements
mysqlbinlog -vv binlog_output_base.flashback | mysql -u
-p

These methods and tools enable reliable MySQL data recovery, point‑in‑time restoration, and safe skipping of harmful SQL operations.