For ages MySQL was lacking an ability to provision new instances with data, at least for the community builds. Well, technically there always was mysqldump but let’s forget about using it on any kind of scale. It’s too slow to be used in most of the real world scenarios. Finally, Oracle added a ‘clone’ plugin – available in both MySQL 5.7 and MySQL 8.0. Let’s take a look at it and see how we can use it for a quite standard use case like MySQL replication. No InnoDB Cluster nor MySQL Replica Set yet, just plain, old asynchronous replication.
As for the data, we are going to use SysBench and we are going to generate the data using following command:
root@vagrant:~# sysbench /root/sysbench/src/lua/oltp_read_write.lua --threads=4 --mysql-host=192.168.10.111 --mysql-user=sbtest --mysql-password=sbtest --mysql-port=6033 --tables=32 --table-size=1000000 --db-ps-mode=disable prepare
The connection is executed through one of ProxySQL instances, using Virtual IP provided by Keepalived. ProxySQL is configured to perform the read/write split, sending writes to the source node and reads to replica. Once the data is populated, we will be using sysbench to generate load on the cluster:
root@vagrant:~# while true ;do sysbench /root/sysbench/src/lua/oltp_read_write.lua --threads=4 --events=0 --time=$(shuf -i 60-300 -n 1) --mysql-host=192.168.10.111 --mysql-user=sbtest --mysql-password=sbtest --mysql-port=6033 --tables=32 --report-interval=1 --skip-trx=on --table-size=1000000 --db-ps-mode=disable --rate=$(shuf -i 1-20 -n 1) run ; done
Initial setup
There are a couple of prerequisites that ‘clone’ plugin requires to operate. First, we have to install the plugin itself. The whole process is described in detail on MySQL documentation website but here are the steps that we performed.
First, we have to set up the plugin in my.cnf. We did that by adding following lines to [mysqld] section of my.cnf:
plugin-load-add=mysql_clone.so clone=FORCE_PLUS_PERMANENT
Then we performed the rolling restart of our cluster. It is also possible to enable the plugin on the fly using:
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Finally, we verified that the plugin is active:
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
-> FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE PLUGIN_NAME = 'clone';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| clone | ACTIVE |
+-------------+---------------+
1 row in set (0.00 sec)
All is good. We can then proceed to create required users and complete the configuration. The ‘clone’ plugin can be used for local and remote cloning. Obviously, we are interested in the latter. Using it for backups is a great thing but we want to see how it is going to work for provisioning of remote servers.
Clone plugin requires a user to connect to the remote server. Such user should have ‘BACKUP_ADMIN’ privilege. Ideally, you will create such user for every host in the cluster but we, for the sake of simplicity, will just create one user for all hosts:
MySQL localhost SQL > CREATE USER 'donor'@'%' IDENTIFIED BY 'somepass'; Query OK, 0 rows affected (0.0118 sec) MySQL localhost SQL > GRANT BACKUP_ADMIN ON *.* to 'donor'@'%'; Query OK, 0 rows affected (0.0105 sec)
The same thing is for the user required on the recipient side:
MySQL localhost SQL > CREATE USER 'receipient'@'%' IDENTIFIED BY 'somepass'; Query OK, 0 rows affected (0.0588 sec) MySQL localhost SQL > GRANT CLONE_ADMIN ON *.* TO 'receipient'@'%'; Query OK, 0 rows affected (0.0041 sec)
Of course, from the security standpoint it is not ideal, we should minimize the access granted and limit the hosts such users can connect from.
As the next step we should populate the ‘clone_valid_donor_list’ variable on all instances:
MySQL localhost SQL > SET GLOBAL clone_valid_donor_list='192.168.10.157:3306,192.168.10.158:3306,192.168.10.159:3306';
Query OK, 0 rows affected (0.0044 sec)
We have also verified that the ‘innodb_page_size’ and ‘innodb_data_file_path’ are the same on all nodes, and we have checked that ‘max_allowed_packet’ is set to more than 2M. We have also checked that the undo files have unique names. All was good and we could proceed to test the ‘clone’ plugin.
Testing the clone plugin
Let’s start with a common scenario. We want to add a replica to the cluster. In our case we will stop one of them, wipe out the replication configuration and then re-add it using the clone plugin.
mysql> STOP REPLICA; Query OK, 0 rows affected (0.01 sec) mysql> RESET REPLICA ALL; Query OK, 0 rows affected (0.04 sec) mysql> RESET MASTER; Query OK, 0 rows affected (0.05 sec) mysql> DROP SCHEMA sbtest; Query OK, 32 rows affected (0.25 sec)
Let’s verify that everything is clean:
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: binlog.000001
Position: 156
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> SHOW REPLICA STATUS\G
Empty set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE '%gtid%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-------+
8 rows in set (0.00 sec)
mysql> SHOW TABLES FROM sbtest;
ERROR 1049 (42000): Unknown database 'sbtest'
As you can see, we have no replication configuration nor any mention about executed or pruned GTID’s. Let’s proceed with cloning the data.
MySQL localhost:3306 ssl SQL > CLONE INSTANCE FROM 'donor'@192.168.10.157:3306 IDENTIFIED BY 'somepass'; ERROR: 3869 (HY000): Clone system configuration: 192.168.10.157:3306 is not found in clone_valid_donor_list: MySQL localhost:3306 ssl SQL > SHOW GLOBAL VARIABLES LIKE 'clone_valid_donor_list'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | clone_valid_donor_list | | +------------------------+-------+ 1 row in set (0.0064 sec)
Ok, it seems like at some point the donor list has been cleared (after a restart maybe?) or we forgot to set it up. Let’s recreate it.
MySQL localhost:3306 ssl SQL > SET GLOBAL clone_valid_donor_list='192.168.10.157:3306,192.168.10.158:3306,192.168.10.159:3306';
Query OK, 0 rows affected (0.0003 sec)
Now we can run the clone command again:
MySQL localhost:3306 ssl SQL > CLONE INSTANCE FROM 'donor'@192.168.10.157:3306 IDENTIFIED BY 'somepass';
Query OK, 0 rows affected (58.3180 sec)
Ok, let’s see where we are at?
MySQL localhost:3306 ssl SQL > SHOW GLOBAL VARIABLES LIKE '%gtid%'; ERROR: 2013 (HY000): Lost connection to MySQL server during query The global session got disconnected.. Attempting to reconnect to 'mysql://root@localhost:3306'.. The global session was successfully reconnected. MySQL localhost:3306 ssl SQL > SHOW GLOBAL VARIABLES LIKE '%gtid%'; +----------------------------------+------------------------------------------------+ | Variable_name | Value | +----------------------------------+------------------------------------------------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | e8e57a44-4ded-11ec-ab00-0800277360cf:1-4025941 | | gtid_executed_compression_period | 0 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | e8e57a44-4ded-11ec-ab00-0800277360cf:1-4025941 | | session_track_gtids | OFF | +----------------------------------+------------------------------------------------+ 8 rows in set (0.0030 sec)
It looks like we are in good shape. The first error is expected: clone process requires MySQL to be restarted so reconnect is normal. Now we can set up the replication:
MySQL localhost:3306 ssl SQL > CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.10.157', SOURCE_USER='rpl_user', SOURCE_PASSWORD='3Lr2JM@Un.I}1cBwuUGji91hW,H7i84i', SOURCE_AUTO_POSITION=1; Query OK, 0 rows affected, 1 warning (0.0306 sec) MySQL localhost:3306 ssl SQL > START REPLICA; Query OK, 0 rows affected (0.0049 sec)
Let’s see how it goes:
MySQL localhost:3306 ssl SQL > SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.10.157
Source_User: rpl_user
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000012
Read_Source_Log_Pos: 892387029
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 9616743
Relay_Source_Log_File: binlog.000012
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 886019368
Relay_Log_Space: 15984607
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 78
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 3001
Source_UUID: e8e57a44-4ded-11ec-ab00-0800277360cf
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Waiting for dependent transaction to commit
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: e8e57a44-4ded-11ec-ab00-0800277360cf:4050605-4078176
Executed_Gtid_Set: e8e57a44-4ded-11ec-ab00-0800277360cf:1-4067229
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.0290 sec)
All is looking good, replication is proceeding and we can clearly see the GTID’s being replicated and executed.
As you can see, using a clone plugin makes provisioning of new replicas quite an easy and convenient process compared to what we had to use in the past. As it is fully executable from the MySQL CLI (be it MySQL Shell or standard client), it is easier to implement cloning than xtrabackup, which provides pretty much the same functionality. In the future blog posts we will take a look at the SSL setup for clone plugin to see how we can further improve the security of the process by applying encryption of data in transfer.
