In this post, we will discuss a new feature – the MySQL 8.0.17 clone plugin. Here I will demonstrate how easy it is to use to create the “classic” replication, building the standby replica from scratch.
The clone plugin permits cloning data locally or from a remote MySQL server instance. The cloned data is a physical snapshot of data stored in InnoDB
, and this means, for example, that the data can be used to create a standby replica.
Let’s go to the hands-on and see how it works.
Installation & validation process of the MySQL 8.0.17 clone plugin
Installation is very easy and it works in the same as installing other plugins. Below is the command line to install the clone plugin:
1 2 |
master [localhost:45008] {msandbox} ((none)) > INSTALL PLUGIN clone SONAME 'mysql_clone.so'; Query OK, 0 rows affected (0.00 sec) |
And how to check if the clone plugin is active:
1 2 3 4 5 6 7 8 |
master [localhost:45008] {msandbox} ((none)) > SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'clone'; +-------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +-------------+---------------+ | clone | ACTIVE | +-------------+---------------+ 1 row in set (0.00 sec) |
Note that these steps need to be executed on the Donor (aka master) and on the Recipient (aka slave if the clone is being used to create a replica).
After executing the installation, the plugin will be loaded automatically across restarts, so you don’t need to worry about this anymore.
Next, we will create the user with the necessary privilege on the Donor, so we can connect to the instance remotely to clone it.
1 2 3 4 5 |
master [localhost:45008] {msandbox} ((none)) > create user clone_user@'%' identified by 'sekret'; Query OK, 0 rows affected (0.01 sec) master [localhost:45008] {msandbox} ((none)) > GRANT BACKUP_ADMIN ON *.* TO 'clone_user'@'%'; Query OK, 0 rows affected (0.00 sec) |
As a security measure, I recommend replacing the % for the IP/hostname or network mask of the Recipient so the connections will be accepted only by the future replica server. Now, on the Recipient server, the clone user requires the CLONE_ADMIN
privilege for replacing recipient data, blocking DDL during the cloning operation and automatically restarting the server.
1 2 3 4 5 |
slave1 [localhost:45009] {msandbox} ((none)) > create user clone_user@'localhost' identified by 'sekret'; Query OK, 0 rows affected (0.01 sec) slave1 [localhost:45009] {msandbox} ((none)) > GRANT CLONE_ADMIN ON *.* TO 'clone_user'@'localhost'; Query OK, 0 rows affected (0.00 sec) |
Next, with the plugin installed and validated, and users created on both Donor and Recipient servers, let’s proceed to the cloning process.
Cloning process
As mentioned, the cloning process can be executed locally or remotely. Also, it supports replication, which means that the cloning operation extracts and transfers replication coordinates from the donor and applies them on the recipient. It can be used for GTID or non-GTID replication.
So, to begin the cloning process, first, let’s make sure that there’s a valid donor. This is controlled by clone_valid_donor_list parameter. As it is a dynamic parameter, you can change it while the server is running. Using the show variables command will show if the parameter has a valid donor:
1 2 3 4 5 6 7 |
slave1 [localhost:45009] {msandbox} ((none)) > SHOW VARIABLES LIKE 'clone_valid_donor_list'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | clone_valid_donor_list | | +------------------------+-------+ 1 row in set (0.01 sec) |
In our case, we need to set it. So let’s change it:
1 2 |
slave1 [localhost:45009] {msandbox} ((none)) > set global clone_valid_donor_list = '127.0.0.1:45008'; Query OK, 0 rows affected (0.00 sec) |
The next step is not mandatory, but using the default log_error_verbosity the error log does not display much information about the cloning progress. So, for this example, I will adjust the verbosity to a higher level (on the Donor and the Recipient):
1 2 |
mysql > set global log_error_verbosity=3; Query OK, 0 rows affected (0.00 sec) |
Now, let’s start the cloning process on the Recipient:
1 2 |
slave1 [localhost:45009] {msandbox} ((none)) > CLONE INSTANCE FROM clone_user@127.0.0.1:45008 identified by 'sekret'; Query OK, 0 rows affected (38.58 sec) |
It is possible to observe the cloning progress in the error log of both servers. Below is the output of the Donor:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
2019-07-31T12:48:48.558231Z 47 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: Acquired backup lock.' 2019-07-31T12:48:48.558307Z 47 [Note] [MY-013457] [InnoDB] Clone Begin Master Task by clone_user@localhost 2019-07-31T12:48:48.876138Z 47 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: COM_INIT: Storage Initialize.' 2019-07-31T12:48:48.876184Z 47 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: COM_RES_COMPLETE.' 2019-07-31T12:48:53.996976Z 48 [Note] [MY-013458] [InnoDB] Clone set state change ACK: 1 2019-07-31T12:48:53.997046Z 48 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: COM_ACK: Storage Ack.' 2019-07-31T12:48:53.997148Z 48 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: COM_RES_COMPLETE.' 2019-07-31T12:48:54.096766Z 47 [Note] [MY-013458] [InnoDB] Clone Master received state change ACK 2019-07-31T12:48:54.096847Z 47 [Note] [MY-013458] [InnoDB] Clone State Change : Number of tasks = 1 2019-07-31T12:48:54.096873Z 47 [Note] [MY-013458] [InnoDB] Clone State BEGIN FILE COPY ... 2019-07-31T12:49:33.939968Z 47 [Note] [MY-013457] [InnoDB] Clone End Master Task ID: 0 Passed, code: 0: 2019-07-31T12:49:33.940016Z 47 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: COM_EXIT: Storage End.' 2019-07-31T12:49:33.940115Z 47 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: COM_RES_COMPLETE.' 2019-07-31T12:49:33.940150Z 47 [Note] [MY-013273] [Clone] Plugin Clone reported: 'Server: Exiting clone protocol.' |
And the Recipient:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
2019 |