A Comparison Between the MySQL Clone Plugin and Xtrabackup

In one of our previous blogs we explained how Clone Plugin, one of new features that showed in MySQL 8.0.17, can be used to rebuild a replication slave. Currently the go-to tool for that, as well as for backups, is Xtrabackup. We thought it is interesting to compare how those tools work and behave.

Comparing Performance

The first thing we decided to test is how both perform when it comes to storing the copy of the data locally. We used AWS and m5d.metal instance with two NVMe SSD and we ran the clone to local copy:

mysql> CLONE LOCAL DATA DIRECTORY='/mnt/clone/';

Query OK, 0 rows affected (2 min 39.77 sec)

Then we tested Xtrabackup and made the local copy:

rm -rf /mnt/backup/ ; time xtrabackup --backup --target-dir=/mnt/backup/ --innodb-file-io-threads=8 --innodb-read-io-threads=8  --innodb-write-io-threads=8 --innodb-io-capacity=20000 --parallel=16

200120 13:12:28 completed OK!

real 2m38.407s

user 0m45.181s

sys 4m18.642s

As you can see, the time required to copy the data was basically the same. In both cases the limitation was the hardware, not the software.

Transferring data to another server will be the most common use case for both tools. It can be a slave you want to provision or rebuild. In the future it may be a backup, Clone Plugin doesn’t have such functionality as of now but we are pretty sure in the future someone will make it possible to use it as a backup tool. Given that hardware is the limitation for local backup in both cases, hardware will also be a limitation for transferring the data across the network. Depending on your setup, it could be either the network, disk I/O or CPU.

In a I/O-intensive operations CPU is the least common bottleneck. This makes it quite common to trade some CPU utilization for reduction in the data set size. You can accomplish that through compression. If it is done on the fly, you still have to read the same amount of data but you send less of it (as it is compressed) over the network. Then, you will have to decompress it and write it down. It is also possible that the files themselves are compressed. In that case you reduce the amount of data read, transferred and written to disk.

Both Clone Plugin and Xtrabackup come with an on-the-fly compression (we would like to thanks Kenny Gryp, who corrected us on this bit). In Clone Plugin you can enable it through clone_enable_compression, which is disabled by default. Xtrabackup can also utilize external tools to compress the data. In case of compressed InnoDB tables, external compression won’t make too much of a difference so both tools should perform in a similar manner in case the network bandwidth is the limiting factor.

Comparing Usability

Performance is just one thing to compare, there are many others like how easy tools are to use. In both cases there are several steps you have to perform. For Clone Plugin it is:

1. Install the plugin on all nodes
2. Create users on both donor and receiver nodes
3. Set up the donor list on the receiver

Those three steps have to be performed once. When they are set, you can use Clone Plugin to copy the data. Based on the init system you may need to start MySQL node after the clone process has completed. This is not required if, like in the case of systemd, MySQL will be automatically restarted.

Xtrabackup requires a couple more steps to get things done.

1. Install the software on all nodes
2. Create user on the donor

Those two steps have to be executed once. For every backup you have to execute following steps:

1. Configure network streaming. Simple and secure way would be to use SSH, something like:

xtrabackup --backup --innodb-file-io-threads=8 --innodb-read-io-threads=8  --innodb-write-io-threads=8 --innodb-io-capacity=20000 --parallel=8 --stream=xbstream --target-dir=/mnt/backup/ | ssh [email protected] "xbstream -x -C /mnt/backup/"

We found, though, for faster harddrives, with single-threaded SSH, CPU becomes a bottleneck. Setting up netcat requires additional step on the receiver to ensure netcat is up, listening and redirecting the traffic to the proper software (xbstream).

2. Stop MySQL on the receiver node

3. Run the Xtrabackup

4. Apply InnoDB logs

5. Copy back the data

6. Start MySQL on the receiver node

As you can see, Xtrabackup requires more steps to be taken.

Security Considerations

Clone Plugin can be configured to use SSL for data transfer even though by default it uses plain text. Cloning of the encrypted tablespaces is possible but there is no option to encrypt, for example, the local clone. User would have to do it separately, after the clone process is completed.

Xtrabackup itself doesn’t provide any security. Security is determined by how you stream the data. If you use SSH for streaming, data in transit will be encrypted. If you decide to use netcat, it will be sent as a plain text. Of course, if the data is encrypted in tablespaces, it is already secured, just like in the case of the Clone Plugin. Xtrabackup can also be used along with on-the-fly encryption to ensure your data is encrypted also at rest.

Plugin Features

Clone Plugin is a new product, still in an infant phase. Its primary task is to provide ways of provisioning nodes in InnoDB Cluster and it does that just fine. For other tasks, like backups or provisioning of replication slaves, it can be used to some extent but it suffers from several limitations. We covered some of them in our previous blog so we won’t repeat it here but the most serious one, when talking about provisioning and backups, is that only InnoDB tables are cloned. If you happen to use any other storage engine, you cannot really use Clone Plugin. On the other hand Xtrabackup will happily backup and transfer most commonly used storage engines: InnoDB, MyISAM (unfortunately, it’s still used in many places) and CSV. Xtrabackup comes also with a set of tools that are intended to help with streaming the data from node to node or even stream backup to S3 buckets.

To sum it up, when it comes to backing up data and provisioning replication slaves, xtrabackup is and most likely will still be the most popular pick. On the other hand, Clone Plugin, most likely, will improve and evolve. We will see what the future holds and how things will look like in a year’s time.

Let us know if you have any thoughts on the Clone Plugin, we are very interested to see what is your opinion on this new tool.