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
mysql2is a single fresh installed instance without any data.
The plan is to create a replica very quickly and using only a SQL connection.
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.
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,
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
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_keyenabled, 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;
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:
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)
Can this setup be used in between two InnoDB cluster? I want to filter some tables which will be replicated to DR InnoDB cluster.
Any idea about how to achieve this?
I am running mysql 8.0.19 and unable to install clone plugin:
mysql> set sql_require_primary_key=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> install plugin clone soname ‘mysql_clone.so’;
ERROR 1123 (HY000): Can’t initialize function ‘clone’; Plugin initialization function failed.
2020-04-06T14:03:36.440231Z 52 [ERROR] [MY-013272] [Clone] Plugin Clone reported: ‘Client: PFS table creation failed.’
2020-04-06T14:03:36.440258Z 52 [ERROR] [MY-010202] [Server] Plugin ‘clone’ init function returned error.
It seems the tables in PFS needed for Clone are already created. Could you verify this ? Is this a fresh install of MySQL ?