Optimizing XtraBackup Parameters for Faster MySQL Physical Backups

Overview – XtraBackup is essential for physical MySQL backups, but large databases can still experience slow backup speeds without proper tuning. The article first outlines the backup process, describing how innobackupex launches xtrabackup, which runs two threads (one for copying ibd files and one for redo logs), followed by FTWRL, non‑InnoDB file copying, and final cleanup.

The backup steps are:

innobackupex starts xtrabackup; xtrabackup creates two threads – one for ibd files and one for redo logs (redo thread starts first).

After ibd copying, xtrabackup notifies innobackupex and waits while the redo thread continues.

innobackupex receives the notification, executes FLUSH TABLES WITH READ LOCK , records a consistent point, and copies non‑InnoDB files (frm, MYD, MYI, etc.).

When non‑InnoDB files are done, innobackupex signals xtrabackup and waits.

xtrabackup stops the redo copy thread, notifies innobackupex that redo copying is complete.

innobackupex unlocks tables with UNLOCK TABLES .

Both processes finish cleanup, release resources, and write backup metadata.

Parameter Introduction – Two options can accelerate the backup:

--parallel : Controls how many threads copy ibd files concurrently. The default is single‑threaded; increasing the thread count speeds up copying when many ibd files exist, but has no effect if multiple tables share the same ibd file.

--compress-threads : Sets the number of threads that compress InnoDB data files. Specifying this option enables compression without needing the separate --compress flag.

Test – The backup command used for testing:

[root@TEST/data]# innobackupex --host=127.0.0.1 --user=root --password=123456 --port=3306 --parallel=4 --compress-threads=4 --stream=xbstream --tmpdir=/backup/tmp --no-version-check /backup > /backup/20210125/20210125`date +%H%M`

The test environment used an 8‑core/8 GB machine with a 100 GB database. Results:

Hardware

Data Size

Parameters

Time Used

8C/8G

100G

none

20 minutes

8C/8G

100G

--compress-threads=4

15 minutes

8C/8G

100G

--parallel=4

12 minutes

8C/8G

100G

--parallel=4;--compress-threads=4

8 minutes

The combined use of both --parallel and --compress-threads reduced the backup time by 60% compared with the default configuration.