In my previous blog, I have explained how the MySQL clone plugin works internally. In this blog, I am going to do a comparison of Backup and Recovery speed of MySQL clone plugin with other available mysql open source backup tools.
Below tools are used for speed comparison of Backup and Recovery,
Two standalone servers are picked up with identical configuration.
Server 1 * IP: 172.23.26.127 * CPU: 2 Cores * RAM: 4 GB * DISK: 200 GB SSD Server 2 * IP: 172.21.3.114 * CPU: 2 Cores * RAM: 4 GB * DISK: 200 GB SSD
- On Server 1 (172.23.26.127), We have loaded approx 122.40 GB data.
- Now, We want to restore the data from Server 1 (172.23.26.127) to Server 2 (172.21.3.114).
- MySQL Setup
- MySQL Version: 8.0.17
- InnoDB Buffer Pool Size: 1 GB
- InnoDB Log File Size: 16 MB
- Binary Logging: On
- Before starting every test, MySQL server is rebooted.
- Sysbench is setup to generate active writes across 10 tables.
# sysbench oltp_insert.lua --table-size=2000000 --num-threads=2 --rand-type=uniform --db-driver=mysql --mysql-db=sysbench --tables=10 --mysql-user=test --mysql-password=****** prepare Initializing worker threads... Creating table 'sbtest1'... Creating table 'sbtest2'... Inserting 2000000 records into 'sbtest1' Inserting 2000000 records into 'sbtest2' Creating a secondary index on 'sbtest1'... Creating a secondary index on 'sbtest2'...
Case 1: (MySQL Clone Plugin)
- MySQL Clone Plugin is the first in the queue for testing.
- To read more on Clone Plugin Setup, Check out my Previous Blog
- Cloning can be initiated by firing a simple SQL as below.
mysql> clone instance from firstname.lastname@example.org:3306 identified by 'XXXX'; Query OK, 0 rows affected (7 min 47.39 sec)
- Once the clone is complete the plugin will be prepare the data and restart the mysql with in 1 minute.
Logs From Performance Schema:
|Stage||Status||Start time||End time|
|DROP DATA||Completed||2019-10-24 14:16:19||2019-10-24 14:16:19|
|FILE COPY||Completed||2019-10-24 14:16:19||2019-10-24 14:23:56|
|PAGE COPY||Completed||2019-10-24 14:23:56||2019-10-24 14:23:57|
|REDO COPY||Completed||2019-10-24 14:23:57||2019-10-24 14:23:57|
|FILE SYNC||Completed||2019-10-24 14:23:57||2019-10-24 14:24:06|
|RESTART||Completed||2019-10-24 14:24:06||2019-10-24 14:24:11|
|RECOVERY||Completed||2019-10-24 14:24:11||2019-10-24 14:24:12|
Overall Duration: 7 min 47 sec
Case 2: (Xtrabackup)
- Next one on the queue is Xtrabackup 8.0. Streaming backup has been initiated from Server 1 (172.23.26.127) to Server 2 (172.21.3.114).
# xtrabackup --user=XXX --password='XXXX' --backup --no-timestamp --no-lock --stream=xbstream | ssh email@example.com "xbstream -x -C /var/backup"
start_time = 2019-10-24 07:53:02 end_time = 2019-10-24 08:10:08
- It’s took around 16 min to complete.
- Once the backup is complete the preparation and restoration taking around 8 min to complete.
xtrabackup --prepare --target-dir /var/lib/mysql . . . FTS optimize thread exiting. Starting shutdown... Log background threads are being closed... Shutdown completed; log sequence number 31714228528 191024 08:14:10 completed OK!
Overall Duration: 24 min
Case 3: (mysqldump)
- Now it’s turn for mysqldump and backup took around 43 mins to complete.
# mysqldump -u mydbops -h XXXXX -p'XXXXX' -P3306 --single-transaction --routines --events --triggers --master-data=2 --all-databases > /backup/fullbackup.sql
- The backup restoration is taken around 52 min’s.
Overall Duration: 95 min
Case 4: (mydumper)
- The mydumper took 39 min to complete. mydumper is setup to run on 2 threads as it’s dual core machine.
# mydumper --host=xxxx --user=mydbops --password='XXXXX' --triggers --events --routines -v 3 --outputdir=/backup/mydumper_backup
- The backup restoration is taken around 46 mins to complete.
# myloader --user=mydbops --password='XXXXX' --host=xxxx --directory=/backup/mydumper_backup --queries-per-transaction=5000 --threads=2 --verbose=3 -e 2> /backup/restore_sep_26.log
Overall Duration: 85 min
Case 5: (mysqlpump)
- The mysqlpump is completed with in 37 mins to complete.
# mysqlpump --user=mydbops --password='XXXXX' --host=xxxx --default-parallelism=2 > pump.sql
- The backup restoration is taken around 41 mins to complete
# mysql --user=mydbops --password='XXXX' < /home/vagrant/pump.sql
Overall Duration: 78 min
Physical backups are faster as expected, Surprising to see clone plugin beats Xtrabackup. If you’re using MySQL 8.0, Clone plugin is worth a try, If you’re running older versions of MySQL, You can happily choose Xtrabackup.
MySQL Cloning and Xtrabackup Differences:
- Both the Cloning and xtrabackup are physical backups (copying of data files), It can be used to perform hot backup and recovery (can be used on live databases) and the principle of backup recovery is also similar.
- The permission of xtrabackup backup file is equal to the permission of the person who executes the command. When restoring the instance, it needs manual chown to return to the instance permission.
- After cloning and backup, the permission is identical with the original data permission, and no manual chown is needed to facilitate recovery.
- When restoring xtrabackup, reset master needs to be executed in mysql, then set global gtid_purged=”UUID:NUMBER”.
- The specific value of UUID:NUMBER is the content of xtrabackup_info file in backup file ,But cloning does not need this operation step, and by default cloning can establish replication.
- When the backup of xtrabackup is completed, scp is usually copied to another machine to recover. It takes port 22 and MySQL’s listening port is cloned.
- So when the directory permissions are correct, you don’t even need the permissions to log on to the Linux server at all.
- Do remember Clone is supported only from MySQL 8.0.17 and xtrabackup is a full fledge backup with more robust options.