Create an Asynchronous MySQL Replica in 5 minutes

I have already posted some time ago a post related to the same topic (see here).

Today, I want to explain the easiest way to create an asynchronous replica from an existing MySQL instance, that this time has already data !

The Existing Situation and the Plan

Currently we have a MySQL server using 8.0.17 and GTID enabled on mysql1. mysql2is a single fresh installed instance without any data.

The plan is to create a replica very quickly and using only a SQL connection.

Preliminary Checks

First we verify that mysql1 has GTID enabled. If not we will enable them:

mysql> select @@server_id,@@gtid_mode,@@enforce_gtid_consistency;
+-------------+-------------+----------------------------+
| @@server_id | @@gtid_mode | @@enforce_gtid_consistency |
+-------------+-------------+----------------------------+
|           1 | OFF         | OFF                        |
+-------------+-------------+----------------------------+
1 row in set (0.00 sec)

If you can restart the server:

mysql1> SET PERSIST_ONLY gtid_mode=on;
mysql1> SET PERSIST_ONLY enforce_gtid_consistency=true;
mysql1> RESTART;

If you prefer to not restart the server (if you have others replica already attached to the server, you need to also enable GTID on those replicas):

mysql1> SET PERSIST enforce_gtid_consistency=true;
mysql1> SET PERSIST gtid_mode=off_permissive;
mysql1> SET PERSIST gtid_mode=on_permissive;
mysql1> SET PERSIST gtid_mode=on;
mysql1> INSTALL PLUGIN clone SONAME 'mysql_clone.so'

Since MySQL 8.0.17, we have the possibility to use the amazing CLONE Plugin, that’s why we installed it.

Replication User

It’s time now to create a user we will use to replicate:

mysql1> CREATE USER 'repl'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
mysql1> GRANT REPLICATION SLAVE, BACKUP_ADMIN, CLONE_ADMIN ON *.* TO 'repl'@'%';

You may have noticed two new privileges, BACKUP_ADMINand CLONE_ADMIN, these are required to provision our replica without using any external tool.

Provision the Replica

We can now provision the replica and configure it to replicate from mysql1 but we also need to specify which server can be considered as a potential donor by setting the clone_valid_donor_list variable:

mysql2> SET GLOBAL clone_valid_donor_list='mysql1:3306';
mysql2> CLONE INSTANCE FROM repl@mysql1:3306 IDENTIFIED BY 'password';

Please note that if you want to install the CLONE Plugin on server running with sql_require_primary_key enabled, you won’t be able to install the plugin. See bug #96281 and at the end of this post.

The data has been transferred from the existing MySQL Server (mysql1) , the clone process restarted mysqld and now we can configure the server and start replication:

mysql2> SET PERSIST enforce_gtid_consistency=true;
mysql2> SET PERSIST gtid_mode=off_permissive;
mysql2> SET PERSIST gtid_mode=on_permissive;
mysql2> SET PERSIST gtid_mode=on;
mysql2> SET PERSIST server_id=2;

mysql2> CHANGE MASTER TO MASTER_HOST='mysql1',MASTER_PORT=3306, 
        MASTER_USER='repl', MASTER_PASSWORD='password',     
        MASTER_AUTO_POSITION=1, MASTER_SSL=1;

Conclusion

As you can see, asynchronous replication also benefits from the new CLONE plugin and never made so easy to setup replicas from existing servers with data.

More blog posts about the Clone Plugins:

• https://mysqlserverteam.com/clone-create-mysql-instance-replica/
• https://mysqlhighavailability.com/automatic-provisioning-in-group-replication/
• https://mysqlhighavailability.com/a-breakthrough-in-usability-automatic-node-provisioning/
• https://lefred.be/content/mysql-innodb-cluster-from-scratch-even-more-easy-since-8-0-17/

---

Bug #96281

I’m adding the description of this problem in this post, so if you search the Internet for the same error, you might find this solution 😉

So, if you server is running with sql_require_primary_key = ON, when you will try to install the CLONE Plugin, it will fail with the following error:

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; 
Plugin initialization function failed.

In the error log, you can see:

2019-07-23T14:58:42.452398Z 8 [ERROR] [MY-013272] [Clone] Plugin Clone reported: 
                              'Client: PFS table creation failed.'
2019-07-23T14:58:42.465800Z 8 [ERROR] [MY-010202] [Server] 
                              Plugin 'clone' init function returned error.

The solution to fix this problem is simple:

mysql> SET sql_require_primary_key=off;
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.19 sec)