MySQL 8.0.17+: Cloning is now much easier

If you use replication with MySQL, if you need a backup, if you need a spare copy of a system for testing and for many other reasons you need a way to make a copy of your MySQL system. In the past you could make a copy in various ways:

• using a cold file system copy with MySQL first shutdown
• using MySQL Enterprise Backup, Xtrabackup from Percona or MariaBackup (an Xtrabackup fork). These work against an online running server. However, they required special binaries to run on the source in addition to the traditional mysqld.
• copying the LUN to a new server

There are many ways to do this and they all work and we have been doing this for so long that it feels normal, but clearly a mysqld to mysqld copy using the normal mysql port and protocol just simplifies things considerably. That is what is available now in 8.0.17 and later with the new clone plugin.

So I wanted to see how easy it was to set this up and how it worked. One easy way for on your laptop testing is to use dbdeployer as that allows you to spin up one or more instances quickly and is great for all sorts of testing. I’d almost like to see this used for normal MySQL provisioning and management but that’s a topic for a different blog post.

So when I found out about the new cloning method I wrote a quick script to see what effort was involved in setting up and cloning between servers. Take a look at the script to see what’s needed to set things up, and at the sample output when this happens. Thanks go to Giuseppe Maxia for showing how to easily achieve such a setup with dbdeployer.

Summary: once configured you can clone a server with a single SQL command. That is certainly much simpler than the methods that have been available until now.

So are there any gotchas? I found a few things:

• bug 96281 – Can’t initialize function ‘clone’; Plugin initialization function failed (broken if sql_require_primary_key = 1), appears to be resolved in 8.0.18
• bug 96302 – support CIDR notation for clone-valid-donor-list (though I’m not convinced this setting is actually useful)
• bug 96305 – make the clone port optional and default to 3306 (default mysql port)
• bug 96837 – documentation is a bit confusing
  

None of these are critical. The main concern I see is the backup_lock that gets taken on the source and which prevents certain operations from continuing normally. If this is to be used to copy from a primary master I see that as problematic.

As for how fast this works compared to other methods I’ll leave that for a later post.