How to Recover MySQL Data: Full, Point-in-Time, and Table Restoration Techniques

1. Introduction

Data recovery requires regular backups and binlog enabled in ROW format. Without backups, deleted databases or tables are unrecoverable; without binlog, you cannot perform flashback or point‑in‑time recovery.

2. Direct Restore

2.1 Full restore with mysqldump

Decompress and import the backup:

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

2.2 Full restore with xtrabackup

Steps:

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

3. Point‑in‑Time Recovery

Locate the binlog position from the backup, then extract logs up to the desired timestamp and apply them.

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

# Extract incremental binlog
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 logs
mysql -S /tmp/mysql.sock < backup.sql
mysql -S /tmp/mysql.sock < backup_inc.sql

After execution the database is restored to the specified point.

4. Restoring a Single Table

4.1 From mysqldump

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

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

4.2 From xtrabackup

For MyISAM tables, copy .frm, .MYD, and .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 schema, discard the tablespace, copy the .ibd file, import the tablespace, and finally dump and import the data.

5. Skipping Erroneous SQL

5.1 Using backup files (no GTID)

Identify the binlog position of the unwanted DROP TABLE statement, split the binlog before and after that position, restore the full backup, then apply only the incremental logs that exclude the dropped statement.

# Example commands
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
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

5.2 Using GTID

Set the session GTID to the one of the unwanted statement, commit an empty transaction, then reset GTID to automatic.

SET SESSION GTID_NEXT='<GTID>';
BEGIN; COMMIT;
SET SESSION GTID_NEXT=AUTOMATIC;

5.3 Using delayed replica (no GTID)

Stop the replica, sync it to the binlog position just before the bad statement, skip one event, and restart 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;

6. Flashback Tools

6.1 binlog2sql

Open‑source tool from Meituan‑Dianping to generate rollback SQL from binlogs.

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

6.2 MyFlash

Another Meituan‑Dianping tool for rolling back DML statements (supports MySQL 5.6/5.7, row‑format binlog, full image).

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

# Apply generated statements
mysqlbinlog -vv binlog_output_base.flashback | mysql -u<user> -p