In today's data-driven world, safeguarding your MySQL databases is crucial. Regular backups and efficient recovery methods are essential for preventing data loss and minimizing downtime.

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,

1. Clone-Plugin
2. Xtrabackup
3. mysqldump
4. mydumper with myloader
5. mysqlpump

Test cases

Hardware Configuration

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
	

Workload Preparation

• 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 mydbops_clone_user@172.23.26.127:3306 identified by 'XXXX';
Query OK, 0 rows affected (7 min 47.39 sec)
	

Restoration:

• Once the clone is complete the plugin will be prepare the data and restart the mysql with in 1 minute.

Logs From Performance Schema:

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 root@172.21.3.114 "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.

Restoration:

• 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
	

Restoration:

• 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
	

Restoration :

• 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
	

Restoration:

• The backup restoration is taken around 41 mins to complete

 
# mysql --user=mydbops --password='XXXX' < /home/vagrant/pump.sql
	

Overall Duration: 78 min

Test Observations

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.

Ready to optimize your MySQL backup strategy and ensure business continuity? Explore Mydbops' comprehensive MySQL services! We offer expert support, secure solutions, and tailored recommendations to meet your unique database needs. Contact us today!

{{cta}}

Featured image by Pietro Mattia on Unsplash