Originality: Translated by Guan ChanglongAikesheng Open Source Community Yesterday

 

order

In this article, we will discuss a new feature, the MySQL 8.0.17 clone plug-in. In this article, I'll show you how to easily create "classic" replicas (one master, one slave) and build standby replicas from scratch.

Cloning plug-ins allow data to be cloned locally or from remote MySQL server instances. In InnoDB, cloned data is data stored in physical snapshots, which means that, for example, data can be used to create alternate copies.

Let's do it ourselves and see how it works.

 

Installation and Verification of MySQL 8.0.17 clone Plug-in

Installation is very simple and works the same way as installing other plug-ins. The following is the command line to install the clone plug-in:

master [localhost:45008] ((none)) > INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.00 sec)

And how to check whether the clone plug-in is active:

master [localhost:45008] ((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 performed on both Donor (donor) and Recipient (receptor, also known as Slave).

After installation, the plug-in will automatically load after reboot, so you don't have to worry about that anymore.

Next, we will create users with the necessary permissions on Donor so that we can connect remotely to an instance to clone it.

master [localhost:45008] ((none)) > create user clone_user@'%' identified by 'sekret';
Query OK, 0 rows affected (0.01 sec)

master [localhost:45008] ((none)) > GRANT BACKUP_ADMIN ON *.* TO 'clone_user'@'%';
Query OK, 0 rows affected (0.00 sec)

As a security measure, I recommend replacing percent with slave IP, host name or network mask so that only future slave servers can accept connections. Now, from the server, the clone user needs CLONE_ADMIN privileges to replace slave data, blocking DDL and automatically restarting the server during the cloning operation.

slave1 [localhost:45009] ((none)) > create user clone_user@'localhost' identified by 'sekret';
Query OK, 0 rows affected (0.01 sec)

slave1 [localhost:45009] ((none)) > GRANT CLONE_ADMIN ON *.* TO 'clone_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Next, install and validate the plug-in and create users on the master and slave servers.

 

Cloning process

As mentioned above, the cloning process can be performed locally or remotely. In addition, it supports replication, which means that the cloning operation extracts and transfers replication coordinates from donors and applies them to recipients. It can be used for GTID or non-GTID replication.

So, to begin the cloning process, let's first ensure that there is an effective donor (Master). This is controlled by the clone_valid_donor_list parameter. Because it is a dynamic parameter, you can make changes while the server is running. Use the show variables command to display whether the parameter has a valid donor (Master):

slave1 [localhost:45009] ((none)) > SHOW VARIABLES LIKE 'clone_valid_donor_list';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| clone_valid_donor_list | |
+------------------------+-------+
1 row in set (0.01 sec)

 

In the example, we need to set it up:

slave1 [localhost:45009] ((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 with the default log_error_verbosity, the error log does not display a lot of information about cloning progress. So, for this example, I will adjust the level of detail to a higher level (on both donor and recipient machines):

mysql > set global log_error_verbosity=3;
Query OK, 0 rows affected (0.00 sec)

 

Now let's start the cloning process on the receptor (Slave):

slave1 [localhost:45009] ((none)) > CLONE INSTANCE FROM clone_user@127.0.0.1:45008 identified by 'sekret';
Query OK, 0 rows affected (38.58 sec)

 

Cloning progress can be observed in the error logs of both servers. The following is the output of the donor (Master):

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.'

 

Slave:

2019-07-31T12:48:48.521515Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Task Connect.'
2019-07-31T12:48:48.557855Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Master ACK Connect.'
2019-07-31T12:48:48.557923Z 8 [Note] [MY-013457] [InnoDB] Clone Apply Begin Master Version Check
2019-07-31T12:48:48.558474Z 8 [Note] [MY-013457] [InnoDB] Clone Apply Version End Master Task ID: 0 Passed, code: 0:
2019-07-31T12:48:48.558507Z 8 [Note] [MY-013457] [InnoDB] Clone Apply Begin Master Task
2019-07-31T12:48:48.558749Z 8 [Warning] [MY-013460] [InnoDB] Clone removing all user data for provisioning: Started
2019-07-31T12:48:48.558769Z 8 [Note] [MY-011977] [InnoDB] Clone Drop all user data
2019-07-31T12:48:48.863134Z 8 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 371 task: 0
2019-07-31T12:48:53.829493Z 8 [Note] [MY-011977] [InnoDB] Clone Drop User schemas
2019-07-31T12:48:53.829948Z 8 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 5 task: 0
2019-07-31T12:48:53.838939Z 8 [Note] [MY-011977] [InnoDB] Clone Drop User tablespaces
2019-07-31T12:48:53.839800Z 8 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 6 task: 0
2019-07-31T12:48:53.910728Z 8 [Note] [MY-011977] [InnoDB] Clone Drop: finished successfully
...
2019-07-31T12:49:33.836509Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Command COM_EXECUTE.'
2019-07-31T12:49:33.836998Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Master ACK COM_EXIT.'
2019-07-31T12:49:33.839498Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Master ACK Disconnect : abort: false.'
2019-07-31T12:49:33.851403Z 0 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Command COM_EXECUTE.'
2019-07-31T12:49:33.851796Z 0 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Task COM_EXIT.'
2019-07-31T12:49:33.852398Z 0 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Task Disconnect : abort: false.'
2019-07-31T12:49:33.852472Z 0 [Note] [MY-013457] [InnoDB] Clone Apply End Task ID: 1 Passed, code:

Note that when the cloning process is completed, MySQL services on Slave will be restarted. After that, the database is ready to be accessed, and the final step is to set up a copy.

 

Duplication process

Binary log locations (file names, offsets) and GTID coordinates are extracted and transmitted from the Master MySQL server instance.

The following queries can be executed on the cloned MySQL server instance to view the location of the binary log or the GTID of the last transaction of the application:

# Binary log position
slave1 [localhost:45009] ((none)) > SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;
+------------------+-----------------+
| BINLOG_FILE | BINLOG_POSITION |
+------------------+-----------------+
| mysql-bin.000001 | 437242601 |
+------------------+-----------------+
1 row in set (0.01 sec)

# GTID
slave1 [localhost:45009] ((none)) > SELECT @@GLOBAL.GTID_EXECUTED;
+----------------------------------------------+
| @@GLOBAL.GTID_EXECUTED |
+----------------------------------------------+
| 00045008-1111-1111-1111-111111111111:1-32968 |
+----------------------------------------------+
1 row in set (0.00 sec)

With this information, we need to execute the CHANGE MASTER command accordingly:

slave1 [localhost:45009] ((none)) > CHANGE MASTER TO
MASTER_HOST = '127.0.0.1',
MASTER_PORT = 45008,
MASTER_USER='root',
MASTER_PASSWORD='msandbox',
MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

slave1 [localhost:45009] ((none)) > start slave;
Query OK, 0 rows affected (0.00 sec)

limit

Cloning plug-ins have some limitations, which will be described here. In my opinion, the community will face two major constraints.

First, it can only clone tables from InnoDB engines. This means that any tables stored by MyISAM and CSV engines will be cloned as empty tables.

Another limitation is on DDL, including TRUNCATE TABLE, which is not allowed during cloning operations. However, concurrent DML is allowed. If the DDL is running, the cloning operation will wait for the lock:

+----+------------+-----------------+------+----------+-------+----------------------------+------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------------+-----------------+------+----------+-------+----------------------------+------+
| 63 | clone_user | localhost:34402 | NULL | clone | 3 | Waiting for backup lock | NULL |
+----+------------+-----------------+------+----------+-------+----------------------------+------+

Otherwise, if the cloning operation is running, the DDL will wait for the lock:

+----+-----------------+-----------------+------+------------------+-------+---------------------------------------------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+------------------+-------+---------------------------------------------------------------+----------------------------------+
| 52 | msandbox | localhost | test | Query | 5 | Waiting for backup lock | alter table joinit engine=innodb |
| 60 | clone_user | localhost:34280 | NULL | clone | 15 | Receiving from client | NULL |
| 61 | clone_user | localhost:34282 | NULL | clone | 15 | Receiving from client | NULL |
| 62 | clone_user | localhost:34284 | NULL | clone | 6 | Receiving from client | NULL |
+----+-----------------+-----------------+------+------------------+-------+---------------------------------------------------------------+----------------------------------+

 

conclusion

With MySQL 8.0.17 clone plug-in, creating replicas becomes easier. This function can also be used to connect and encrypt data using SSL. When publishing this blog post, the clone plug-in can be used not only to set up asynchronous copies, but also to set up group replication members.