Accelerating MySQL Data Recovery with Xtrabackup: Scenarios and Techniques

1 Background

As a DBA, backup and recovery are extremely important; while many focus on improving backup efficiency, the time cost of data restoration is often more critical in real‑world operations because a long recovery time may fail to meet RTO requirements. This article uses the Xtrabackup tool to explore how to accelerate recovery in three scenarios.

Scenario 1: Full backup followed by a database failure, requiring restoration of the full backup plus all binlog data.

Scenario 2: Full backup followed by accidental deletion of a specific database, requiring restoration of that database only.

Scenario 3: Full backup followed by accidental deletion of a specific table, requiring restoration of that table only.

Precondition: you already have a complete Xtrabackup full backup and binlog.

2 Scenario One

The acceleration trick for the full‑backup + binlog recovery flow is to use the SQL Thread for binlog replay, which brings two main benefits:

It can leverage parallel replication for faster speed.

It can use replication filtering to replay only the relevant database or table binlog (single‑database or single‑table recovery).

Assuming you have restored the complete Xtrabackup full backup to a temporary instance, the process of using the SQL Thread to replay binlog is shown below:

2.1 Generate index file

Copy all binlog files to the temporary instance’s relay log directory, rename them, and then generate the index file.

[root@localhost relaylog]$ rename mysql-bin mysql-relay mysql-bin*
[root@localhost relaylog]$ ls ./mysql-relay.0* > mysql-relay.index
[root@localhost relaylog]$ chown -R mysql.mysql mysql-relay.*

2.2 Modify parameters

Modify MySQL parameters (the server_id must differ from the original instance, relay_log_recovery must be set to 0; other parameters can be tuned to improve replay efficiency), then restart the temporary instance.

[root@localhost relaylog]$ vim ../my.cnf.3306
[root@localhost relaylog]$ less ../my.cnf.3306 | grep -Ei "server_id|relay_log_recovery|slave-para|flush_log_at|sync_binlog"
server_id               = 4674
slave-parallel-type    = LOGICAL_CLOCK
slave-parallel-workers = 8
sync_binlog            = 0
innodb_flush_log_at_trx_commit = 0
relay_log_recovery     = 0
[root@localhost relaylog]$ systemctl restart mysql_3306
[root@localhost relaylog]$ ps aux | grep 3306

2.3 Build replication channel and start SQL thread

[root@localhost relaylog]$ cat /data/mybackup/recovery/186-60-42/xtrabackup_binlog_info
mysql-bin.000002 195862214 5af74703-a85e-11ed-a34e-02000aba3c2a:1-205
[root@localhost relaylog]$ mysql -S /data/mysql/3306/data/mysqld.sock -uroot -p
mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1',RELAY_LOG_FILE='mysql-relay.000002',RELAY_LOG_POS=195862214;
mysql> SELECT * FROM MYSQL.SLAVE_RELAY_LOG_INFO\G
mysql> START SLAVE SQL_THREAD;

Key acceleration techniques used:

Use SQL thread to replay binlog with parallel replication.

Set double‑zero parameters (e.g., sync_binlog=0 , innodb_flush_log_at_trx_commit=0 ) for replication acceleration.

3 Scenario Two

When restoring a single database from a full backup, in addition to SQL thread binlog replay, the second acceleration trick is to use transportable tablespace.

Official documentation: InnoDB Table Import

Note: Using transportable tablespace has six pre‑conditions; please refer to the official docs for details.

For large tables, transporting tablespace provides a huge efficiency gain compared with logical restore, and Xtrabackup’s --export option allows generation of the required .cfg files during the prepare phase.

Example: restoring the test database (source 3310, temporary target 3311):

3.1 Prepare table structure

Export the table schema with mysqldump and import it into the target.

# logical export
[root@localhost 3310]$ /data/mysql/3310/base/bin/mysqldump -uroot -p -h127.0.0.1 -P3310 --set-gtid-purged=off --no-data --databases test > ./testdb_schema_bak.sql

# import to target
[root@localhost 3311]$ /data/mysql/3311/base/bin/mysql -uroot -p -h127.0.0.1 -P3311 < /data/mysql/3310/testdb_schema_bak.sql

3.2 Prepare

Run Xtrabackup with --export to generate files needed for tablespace transfer.

# files before export
[root@localhost test]$ ll
-rw-r----- 1 root root 8632 Dec 14 10:45 sbtest1.frm
-rw-r----- 1 root root 30408704 Dec 14 10:45 sbtest1.ibd
... (other tables)

# after --export, .cfg files are generated
[root@localhost 3310]$ xtrabackup --prepare --export --use-memory=1024MB --target-dir=/data/mysql/3310/backup/3310_20231214_full_bak
[root@localhost 3310]$ ll /data/mysql/3310/backup/3310_20231214_full_bak/test/
-rw-r--r-- 1 root root 490 Dec 14 10:47 sbtest1.cfg
-rw-r----- 1 root root 16384 Dec 14 10:47 sbtest1.exp
-rw-r----- 1 root root 30408704 Dec 14 10:45 sbtest1.ibd
... (other tables)

3.3 Prepare SQL

Generate DISCARD TABLESPACE and IMPORT TABLESPACE statements; when tables exist, you can use information_schema.tables to build the statements. Example shell script:

[root@localhost tmp]$ DATABASE='test'
[root@localhost tmp]$ for table in sbtest1 sbtest2 sbtest3 sbtest4 sbtest5
> do
>   echo "ALTER TABLE ${DATABASE}.${table} DISCARD TABLESPACE;" >> discard_ts.sql
>   echo "ALTER TABLE ${DATABASE}.${table} IMPORT TABLESPACE;" >> import_ts.sql
> done
[root@localhost tmp]$ cat discard_ts.sql
ALTER TABLE test.sbtest1 DISCARD TABLESPACE;
ALTER TABLE test.sbtest2 DISCARD TABLESPACE;
... (other tables)
[root@localhost tmp]$ cat import_ts.sql
ALTER TABLE test.sbtest1 IMPORT TABLESPACE;
ALTER TABLE test.sbtest2 IMPORT TABLESPACE;
... (other tables)

3.4 Copy files to target

[root@localhost test]$ cp sbtest*.{cfg,ibd} /data/mysql/3311/tmp/
[root@localhost tmp]$ ll
total 148508
-rw-r--r-- 1 root root 225 Dec 14 14:00 discard_ts.sql
-rw-r--r-- 1 root root 225 Dec 14 14:00 import_ts.sql
-rw-r--r-- 1 root root 490 Dec 14 13:59 sbtest1.cfg
-rw-r----- 1 root root 30408704 Dec 14 13:59 sbtest1.ibd
... (other tables)
[root@localhost tmp]$ chown mysql. ./*

3.5 Recover data

# 1. discard tablespace
[root@localhost tmp]$ /data/mysql/3311/base/bin/mysql -uroot -p -h127.0.0.1 -P3311 < discard_ts.sql
# 2. copy .cfg and .ibd to target test directory
[root@localhost tmp]$ cp -a sbtest*.{cfg,ibd} /data/mysql/3311/data/test/
# 3. import tablespace (check mysql-error.log for errors)
[root@localhost tmp]$ /data/mysql/3311/base/bin/mysql -uroot -p -h127.0.0.1 -P3311 < import_ts.sql

3.6 Data verification

mysql> use test;
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.21 sec)

At this point the table data has been restored. To recover the binlog data, configure SQL‑thread replay with replication filtering, locate the GTID or POS of the DROP operation, and stop the SQL thread before that point.

Parse the binlog/relaylog to obtain the GTID/POS:

# parse binlog for DROP statements
[root@localhost relaylog]$ while read relaylogname; do
  /data/mysql/3311/base/bin/mysqlbinlog --base64-output=decode-rows -vvv $relaylogname | grep -Ei "drop" && echo "RELAYLOG position: $relaylogname";
  done < /data/mysql/3311/relaylog/mysql-relay.index

# example output shows DROP DATABASE `test` at position 20135899
[root@localhost relaylog]$ /data/mysql/3311/base/bin/mysqlbinlog --base64-output=decode-rows -vvv mysql-relay.000006 | less

Configure replication filter:

mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (test);
Query OK, 0 rows affected (0.01 sec)

Start the replication thread until the erroneous transaction:

# using GTID
mysql> START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS = '5af74703-a85e-11ed-a34e-02000aba3c2a:399350';
# or using POS
mysql> START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE = 'mysql-relay.000006', RELAY_LOG_POS = 20135899;

Scenario three follows the same logic as scenario two.

The required table structure can be obtained from a test or performance environment, ensuring consistency.

For MySQL 8.0 and earlier, the .frm file can be parsed with tools like mysqlfrm ; for MySQL 8.0+, the ibd2sdi tool together with third‑party scripts can help.

Summary of acceleration tricks used throughout the article:

Leverage Xtrabackup’s --export to transfer only the needed tablespace, avoiding full‑backup restoration when only a few tables are required.

For large tables, transportable tablespace migration provides a clear performance advantage (subject to its limitations).

In scenario one, use filtered replication to replay only the required database or table, further reducing recovery time.

4 Other Tips

Beyond the two main tricks, there are several additional time‑saving tips throughout the recovery workflow:

Tool and version selection: Xtrabackup 8.0.33‑28 optimizes the prepare phase for noticeable speed gains.

Configure performance parameters based on actual machine resources, e.g., --parallel together with --decompress , --decrypt , and --use-memory to enable parallel decompression, decryption, and memory‑tuned preparation.

Control the prepare phase timing: performing prepare immediately after backup can save a lot of time, and ensuring the temporary recovery machine can quickly obtain backup files also helps.

Hardware considerations such as CPU, disk I/O, and network bandwidth affect overall recovery speed.

Recommended Reading

Fault Analysis | MySQL VARCHAR Length Issue Summary

Fault Analysis | Why MySQL Cannot Modify Primary Key?

Fault Analysis | MySQL Full‑Text Index OOM Example

Technical Share | Detailed MySQL Three‑Table JOIN

Technical Share | MySQL 8 Precise Timestamps

Technical Share | MySQL User Security Hardening Strategies

Technical Share | Difference Between sysdate() and now()

Technical Share | Multi‑Channel Master‑Master MySQL Disaster Recovery Architecture

About SQLE

SQLE is a comprehensive SQL quality management platform that covers SQL review and management from development to production. It supports major open‑source, commercial, and domestic databases, providing workflow automation for developers and operators to improve release efficiency and data quality.

SQLE Access

🔗 Github: https://github.com/actiontech/sqle

📚 Documentation: https://actiontech.github.io/sqle-docs/

💻 Official site: https://opensource.actionsky.com/sqle/

👥 WeChat technical community: add administrator WeChat "ActionOpenSource"

📊 Click the original link to view the feature comparison between community and enterprise editions: https://actiontech.github.io/sqle-docs/docs/support/compare