Clone: Create MySQL instance replica

Cloning MySQL instance data reliably and efficiently is required in many scenarios. This includes MySQL HA solutions where one needs to provision an instance before joining it to a Group Replication cluster or adding it as Slave in classic the replication model.

 Creating a MySQL replica for a replication topology has always been cumbersome. There were too many steps  involved. It involved first taking a backup of the MySQL server, transferring the backup over the network to the new MySQL node that we wanted to add to the replication set,  followed by a restore of the backup on that  node. A MySQL server was then  started manually to complete the process. To use it for HA, and get the cluster up and running one needs to set the backed up GTID correctly too. The number of manual steps involved was not good for usability. The Clone plugin solves this problem and simplifies the replica provisioning. You can use the MySQL client (and SQL commands)  to provision new nodes and observe the cloning progress as it happens. No need to mess around with multiple steps manually and maintain your own infrastructure to provision new MySQL nodes.

MySQL 8.0.17 introduces the CLONE SQL statement to make the current MySQL server a “clone” of another MySQL server running at a different node. We refer to the server instance, where the clone statement is executed, as the “Recipient”. The source server instance from which the data is going to be cloned is referred to as the “Donor”.  The Donor clones  all its data and metadata stored in the InnoDB storage engine at a consistent snapshot to replace the data in Recipient.

After the successful execution of a CLONE SQL statement the Recipient  server is restarted automatically. The restart involves recovery of the snapshot data cloned just like it would if the data was copied across in the old way. Once recovery completes, the Recipient is a clone of  the Donor and is ready for use! It’s as simple as that.

A few important points to note here about the cloning process.

  • MySQL configuration parameters are not cloned and the Recipient retains all its original configuration parameters as before clone. This is done because many configurations could be specific to a node (e. g. PORT) and thus preserving them seems a good choice. On the other hand, some storage configurations do need to match (e. g. innodb_page_size) between the Donor and the Recipient, the CLONE will report an  error if such configuration parameters mismatch.
  • The Clone plugin does not clone the binary logs.  Note that donor binary logs are not required to be cloned for joining the node to HA cluster.
  • Clone plugin supports only the InnoDB storage engine currently. Tables created in other storage engines like MyISAM and CSV will be cloned as empty tables. The design of the Clone infrastructure  allows for cloning any storage engine supported by MySQL. However, only the InnoDB serialize and deserialize methods have been implemented and tested.
  • Clone blocks all concurrent DDLs in Donor.
  • One needs to be careful about the fact that “Recipient” gives up all its data along with any binary logs in order to become clone of the donor instance. The current recipient data needs to backed up, if deemed necessary, before executing CLONE.

Let’s now go through a real example and steps to perform clone. In the following examples we will also look at some interesting details e.g., observability –   how to monitor the progress of a long running Clone command and the privileges required to Clone etc. In the following example we will use the classic shell. With the new shell you can visualize the cloning state as a progress bar.

Step-1: Choose and setup the Donor.
Choose the MySQL server to clone from and connect to it as the system user. Then install clone plugin and provide backup privilege to the user that would transfer data from donor server.

Grant privilege to view performance schema and execute functions. This is for observing clone progress.

Step-2: Choose and setup Recipient.
Choose the server that would become a clone of  Donor. If provisioning a new node, then first initialize a data directory and start the server.

Connect to the server as the system user. Then install clone plugin and provide CLONE_ADMIN privilege to the user that would replace the current instance data with cloned data. We also maintain a configuration which lists a set of valid donors that one can clone from.

Grant privilege to view performance schema and execute functions. This is for observing clone progress.

Step-3: Connect to Recipient and execute CLONE SQL statement.
We mention here the host name where the Donor is running and the MySQL protocol PORT which defaults to 3306. The donor sever must be reachable from Recipient . The Recipient will connect to Donor with the address and credential provided and start cloning.

Clone command might take time to finish depending on the size of the data set and load on Donor. It’s as simple as that!

Step-3A: Wait for clone to finish and check progress

While cloning if data is in progress, create another connection to the Recipient to observe the progress of the running clone operation using performance_schema views.

The data shown here is for cloning a database of around 100GB over LAN with moderate sysbench read-write load on Donor.  The data could vary based on cpu, disk, network used and load on Donor.

Clone is in progress after about 5 minutes of starting the command. Now we check the progress view for more details.

We use the format from sys.format_time to display time in “DURATION” column conveniently.  “m” implies minutes here. See for more details.

Clone is in the  FILE COPY  stage (copying files) and 47% of the total work of about  94G is now complete. Now we wait for some more time and re-check progress.

Most clone stages are complete and clone is now in FILE SYNC  stage (syncing data to disk). After FILE SYNC stage is completed the  clone command will  return and the server will be restarted automatically. During the last two stages  RESTART  and RECOVERY the  server will not be available.  This is because MySQL is doing a full recovery at this stage  from the cloned data . The redo logs are being applied and the new clone has to be synchronized on the snapshot GTID so that it can catch up with upstream server using the bin log. We can check the status only after these stages are over.

Step-4: Verify that clone completed successfully

CLONE SQL needs a server restart after completion. After CLONE SQL returns successfully, the server will shutdown automatically. The client executing CLONE SQL can expect the connection to be lost. Note: If auto-reconnect is enabled, the connection will be reestablished after the server restarts. Otherwise, the connection must be reestablished manually.

Recipient should be running in an environment with a monitoring process to detect a server shutdown and restart. See the generally preferred environment for running the MySQL server.

In the presence of a monitoring process, client would be able to re-connect to the Recipient once it restarts automatically after CLONE.

In absence of monitoring process, clone will return an error and shutdown the server.

This error message doesn’t indicate a clone failure. It means the server needs to be restarted manually and then client needs to reconnect to the server.

Once the client reconnects  to the Recipient, check the performance_schema views to verify that clone completed successfully.

Clone has completed successfully and took about 17 minutes to complete. Verify that all stages completed successfully and the time taken in individual stages.

That’s it! Recipient is now a clone of Donor with GTID set to correct value. It is now ready to join the HA cluster either based on GTID or binary log position. Please refer to MySQL HA documents for more details on configuring a MySQL instance as slave or group replication node.

I will cover more details about clone security aspects, performance, internal details like clone stages and other salient features in future posts.

Thank you for using  MySQL. Happy cloning  🙂

Leave a Reply

Your email address will not be published. Required fields are marked *