When somebody wants to migrate from a single MySQL instance to a full HA solution using MySQL InnoDB Cluster, the best solution to reduce the downtime is to use asynchronous replication and switch database only once at a certain point in time when everything is ready. This is almost what I explained already in this post.
The most difficult part was related to the provisioning of the existing data to the new cluster members. A backup (physical or logical) was required. It should have been restored on every nodes and we had to be sure to not mess up with the GTIDs.
This is not more the case since MySQL 8.0.17 ! Now we can use the CLONE plugin to start the cluster provisioning too.
The current situation
We have a single instance of MySQL 8.0.17 to serve the application. If that server crashes, we don’t have database service available anymore.
That’s the reason why we want to setup a MySQL InnoDB Cluster.
Install a new server
We install an empty MySQL server 8.0.17 on a new machine: mysql1.
When the server is installed, we start it and as the finality is to use MySQL InnoDB Cluster, we enable directly GTIDs on both servers. Then we install the CLONE Plugin on it. We install the CLONE Plugin on the production server too (single).
We add single:3306 in the clone_valid_donor_list and we initiate a remote clone from mysql1 to single.
MySQL InnoDB Cluster Creation
Now that we have a new server with a snapshot of the production’s data, we can create an InnoDB Cluster out of it.
When the cluster is created with a single member, we can add the other 2 new servers. They will also get their initial snapshot from mysql1 using CLONE.
Retrieve last data from production
The fully HA cluster is ready and running with 3 members. We need to recover all the data that was written in production while we were creating this awesome cluster.
Therefor, we will use Asynchronous Replication from single to the Primary Member in the Cluster (mysql1). We wait for the cluster to catch up before we continue.
MySQL Router
Finally, we can bootstrap the MySQL Router. Ideally on the app server. When the router is installed (bootstrap), we can start it and stop the application. This is the only downtime, very short during the full migration. After having stopped the application, we can restart it and this time the application needs to connect to the MySQL Router.
All in video
You can now watch all the steps on the video below):
Conclusion
It’s now very easy to migrate from an architecture to a new one using the CLONE Plugin. Also this plugin really simplify the cluster creation when new nodes need to be added.
Hi Fred,
I’ve an issue upgrading mysql-community from 8.0.16 to 8.0.17, required to use clone plugin.
After the upgrade to 8.0.17, and after clone plugin installation, the wizard does not display clone option, do you have an idea about why this happens?
The message during the addInstance is:
Please select a recovery method [I]ncremental recovery/[A]bort (default Incremental recovery)
Thanks
Ciao Matteo,
Are all your members in 8.0.17 or only the upgraded one ? You need to have the donor and the joiner in 8.0.17.
If this is the case, please verify the privileges of your user too.
Cheers,
Matteo,
I also wanted to add that if you have done all this, you also need to enable the clone usage in your cluster. Check with cluster.options() the value of ‘disableClone’ it shoud be set to false: cluster.setOption(“disableClone”, false)
Cheers,
Hi,
all nodes are 8.0.17, I’ve solved setting this option with mysqlshell:
cluster.setOption(“disableClone”, false)
Thank you
Hay lefred – quality tutorial you have made!
I was wondering – do you have to cleanup somehow after switching the application to use the cluster instead of the single instance? I am refering to the CHANGE MASTER TO and START SLAVE that were issued on the mysql1 cluster instance.
Or you can simply shut down and destroy the source/single instance?
Thanks!
Hi Georgi,
No need, however, this is an old video. We have improved all those architectures, please take a look at: https://speakerdeck.com/lefred/the-evolution-of-a-mysql-database-from-a-single-instance-to-ha-with-disaster-recovery
Best regards.