3 ways to speed backups of MySQL

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 :

GitHub - mydumper/mydumper: Official MyDumper Project

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:

Percona XtraBackup for MySQL

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.