3 ways to speed backups of MySQL

Fabricio Pedroso Jorge
6 min readJun 10, 2023

--

Hi all,

in this short article, I’d like to show some ways that you can speed-up MySQL backups.

The original tool, called “mysqldump”, although very simple and easy to use, does not work well in the world of parallelism. With the current amount of data stored in databases, taking backups using only a single thread/core is something that does not work well in any production database, which is in the multi-GB, to TBs of data.

  1. MySQL Shell

MySQL Shell — mysqlsh - is an improved CLI, with some new features over the old “mysql” tool. It has an interesting way to perform MySQL logical backups in parallel.

It by default uses the “ZStandard” compression algorithm.

Examples:

-- connecting to a MySQL instance
-- the flag "--js" indicates the shell will start in JavaScript mode
mysqlsh --user <username> --host <host> --js


-- dumping an entire schema
-- "threads" indicate the number of threads used for this job
-- this creates a directory in the specified path with all database structures
util.dumpSchemas(["dev"], "/home/mysql/backups/bkpdev_100623", {threads: 2})

Acquiring global read lock
Global read lock acquired
Initializing - done
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
NOTE: Could not select columns to be used as an index for table `dev`.`tab_people_bkp`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `dev`.`tab_test_replication`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `dev`.`tab_people_bkp_2`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `dev`.`tab_people_archive_070321`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `dev`.`tab_trigger_source`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `dev`.`tab_trigger_target`. Chunking has been disabled for this table, data will be dumped to a single file.
Running data dump using 2 threads.
NOTE: Progress information uses estimated values and may not be accurate.
NOTE: Table statistics not available for `dev`.`tab_json_tests`, chunking operation may be not optimal. Please consider running 'ANALYZE TABLE `dev`.`tab_json_tests`;' first.
NOTE: Table statistics not available for `dev`.`tab_generic`, chunking operation may be not optimal. Please consider running 'ANALYZE TABLE `dev`.`tab_generic`;' first.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
103% (1.36M rows / ~1.31M rows), 97.09K rows/s, 26.03 MB/s uncompressed, 12.87 MB/s compressed
Dump duration: 00:00:17s
Total duration: 00:00:18s
Schemas dumped: 1
Tables dumped: 15
Uncompressed data size: 379.01 MB
Compressed data size: 187.58 MB
Compression ratio: 2.0
Rows written: 1355704
Bytes written: 187.58 MB
Average uncompressed throughput: 21.06 MB/s
Average compressed throughput: 10.42 MB/s


-- dumping a MySQL instance
-- "threads" indicate the number of threads used for this job
util.dumpInstance("/home/mysql/backups/bkp_mysql_100623", {threads:2})

Global read lock acquired
Initializing - done
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
NOTE: Could not select columns to be used as an index for table `dev`.`tab_people_bkp`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `dev`.`tab_test_replication`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `dev`.`tab_people_bkp_2`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `dev`.`tab_people_archive_070321`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `dev`.`tab_trigger_source`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `dev`.`tab_trigger_target`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `kafkaarchive`.`tab_kafka_mysql`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `prod`.`tab_generic`. Chunking has been disabled for this table, data will be dumped to a single file.
NOTE: Could not select columns to be used as an index for table `prod`.`people`. Chunking has been disabled for this table, data will be dumped to a single file.
Running data dump using 2 threads.
NOTE: Progress information uses estimated values and may not be accurate.
NOTE: Table statistics not available for `dev`.`tab_json_tests`, chunking operation may be not optimal. Please consider running 'ANALYZE TABLE `dev`.`tab_json_tests`;' first.
NOTE: Table statistics not available for `dev`.`tab_generic`, chunking operation may be not optimal. Please consider running 'ANALYZE TABLE `dev`.`tab_generic`;' first.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
102% (1.65M rows / ~1.60M rows), 93.32K rows/s, 25.11 MB/s uncompressed, 12.42 MB/s compressed
Dump duration: 00:00:18s
Total duration: 00:00:18s
Schemas dumped: 5
Tables dumped: 25
Uncompressed data size: 445.75 MB
Compressed data size: 220.01 MB
Compression ratio: 2.0
Rows written: 1647275
Bytes written: 220.01 MB
Average uncompressed throughput: 23.96 MB/s
Average compressed throughput: 11.83 MB/s

This backup solution can also be executed from a script, which makes easier to automate and schedule it:

   1) Create a .js file with the command to be executed. Ex:

-- dumps a single database using 2 threads
util.dumpSchemas(["dev"], "/home/mysql/backups/dmp_dev_100623", {threads: 2})

2) Run the "mysqlsh", pointing to the .js file

mysqlsh --user=username --host=hostname < backupdatabase.js

2. MyDumper

It is a 3rd-party tool which also provides several advantages over the original “musqldump”. Some advantages are:

  • Parallelism
  • It is easier to manage — supporting PCRE (Perl scripts) to specify the databases/tables inclusion/exclusion
  • Consistency — data snapshots are maintained across all threads during the backup process

The Github repository for this tool is :

Note that “mydumper” tool is only used to create the dumps. To restore then, use the tool “myloader”

Examples:

--parameters are self-explanatory using this tool
mydumper --host=192.168.244.157 --user=usr_backup --password=$MYSQLPWD --port=3306 --database=dev --threads=2 --verbose=3 --outputdir=/home/mysql/backups/bkpdev_100623

3. Percona XtraBackup

This tool differs from the other 2 presented above as this one is a physical backup tool, copying datafiles and REDO log files

It has some interesting features making more suitable for big production environments:

  • Hot backups
  • Incremental backups
  • Stream compressed backups to another servers
  • Move tables between MySQL servers online
  • Easy creation of MySQL replicas

It is available for free at:

Examples:

-- simple backup
xtrabackup -u <user> -p -H <mysqlhost> -P 3306 --password --backup --target-dir=/var/lib/mysql/backups

-- compressed backup (can be "gzip" or "tar")
-- creating a compressed backup, spliting into multiple GZIP files
xtrabackup --backup --user=<user> --password=<pwd> --stream=tar --compress --target-dir=</path/backupdir/> | gzip - | split -d --bytes=500MB - </path/backupdir/backupfile>.tar.gz

NOTE: XtraBackup version 8 and above does not support TAR as a stream type. Use XBSTREAM instead.

-- backup using the "xbstream" format
-- creating a compressed backup, spliting into multiple XBSTREAM files
xtrabackup --backup --user=<user> --password=<pwd> --stream=xbstream --compress --target-dir=</path/backupdir> | split -d --bytes=500MB - </path/backupdir/backupfile>.xbstream

4. Other tools

There are other tools available as well:

  • Mysqlpump: (Note that it is “mysqlPump” and not “mysqlDump”). Tool that performs logical backups, producing a set of SQL statements in which can then be executed to recreate the original database objects.
  • MySQL Enterprise Backup: This is the enterprise backup solution developed by Oracle. No need to mention that a license is required to be able to use this tool. Client tool for this solution is named “mysqlbackup”. One interesting feature is that it integrates with several cloud storage services, such as OCI (Oracle Cloud) Object Storage, AWS S3, GCP Cloud Storage.

That’s it. I hope this small article may help you in your journey with MySQL backups.

Thanks.

--

--

Fabricio Pedroso Jorge
Fabricio Pedroso Jorge

Written by Fabricio Pedroso Jorge

Database engineer born in Brazil, but living in Ireland since 2018.

No responses yet