background

It's amazing, 5.7.17 and 8.0.17, two successive 17 minor versions are eye-catching. The former adds Group Replication, while the latter adds Clone Plugin. Today we're going to test this new feature in action.

Introduction to Clone Plug-ins

Cloning plug-ins allow data to be cloned locally or remotely from MySQL instances. Cloned data is a physical snapshot of data stored in InnoDB, including database, table, table space and data dictionary metadata. Cloned data contains a fully functional data catalog that allows MySQL server configuration using clone plug-ins.

Cloning plug-ins support two cloning modes

• Local cloning
• Remote Cloning

Local cloning

Local cloning clones data from MySQL server instances that initiate cloning operations into a directory on the same server or node

Remote Cloning

By default, remote cloning removes data from the recipient data directory and replaces it with donor cloning data. (Optional) You can also clone data into other directories of the recipient to avoid deleting existing data.

The data cloned by remote cloning operation is the same as that cloned by local cloning operation.

Clone plug-ins support replication. In addition to cloning data, cloning operations also extract and transmit replication location information from donors and apply it to recipients, so that cloning plug-ins can be used to configure group replication or master-slave replication. Configuration using cloned plug-ins is much faster and more efficient than replicating a large number of transactions.

Actual part

I. Local Cloning

Install Clone Plug-ins

Pre-startup

[mysqld]
plugin-load-add=mysql_clone.so

Or in operation

INSTALL PLUGIN clone SONAME 'mysql_clone.so';

Check if the plug-in is enabled

mysql> 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.01 sec)

Setting Forced Start Failure Parameters

[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

Privileges required for cloning

You need to have backup lock privileges. Backup locks are one of the new features of MySQL 8.0, lighter than the 5.7 version of flush table with read lock.

mysql> CREATE USER clone_user@'%' IDENTIFIED by 'password'; 
mysql> GRANT BACKUP_ADMIN ON *.* TO 'clone_user';  # BACKUP_ADMIN is the backup lock privilege only available in MySQL 8.0

Executing local cloning

mysql -uclone_user -ppassword -S /tmp/mysql3008.sock
mysql> CLONE LOCAL DATA DIRECTORY = '/fander/clone_dir';

The steps for local cloning are as follows

• DROP DATA
• FILE COPY
• PAGE COPY
• REDO COPY
• FILE SYNC

The observation methods are as follows.

mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
+-----------+-------------+----------------------------+
| STAGE     | STATE       | END_TIME                   |
+-----------+-------------+----------------------------+
| DROP DATA | Completed   | 2019-07-25 21:00:18.858471 |
| FILE COPY | Completed   | 2019-07-25 21:00:19.071174 |
| PAGE COPY | Completed   | 2019-07-25 21:00:19.075325 |
| REDO COPY | Completed   | 2019-07-25 21:00:19.076661 |
| FILE SYNC | Completed   | 2019-07-25 21:00:19.168961 |
| RESTART   | Not Started | NULL                       |
| RECOVERY  | Not Started | NULL                       |
+-----------+-------------+----------------------------+
7 rows in set (0.00 sec)

Of course, there is another way to observe it.

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

mysql> CLONE LOCAL DATA DIRECTORY = '/fander/clone6_dir';
Query OK, 0 rows affected (0.24 sec)

[root@192-168-199-101 data]# tailf mysql-error.err
2019-07-25T22:22:58.261861+08:00 8 [Note] [MY-013457] [InnoDB] Clone Begin Master Task by root@localhost
2019-07-25T22:22:58.262422+08:00 8 [Note] [MY-013457] [InnoDB] Clone Apply Master Loop Back
2019-07-25T22:22:58.262523+08:00 8 [Note] [MY-013457] [InnoDB] Clone Apply Begin Master Task
...
2019-07-25T22:22:58.471108+08:00 8 [Note] [MY-013458] [InnoDB] Clone Apply State FLUSH DATA: 
2019-07-25T22:22:58.472178+08:00 8 [Note] [MY-013458] [InnoDB] Clone Apply State FLUSH REDO: 
2019-07-25T22:22:58.506488+08:00 8 [Note] [MY-013458] [InnoDB] Clone Apply State DONE
2019-07-25T22:22:58.506676+08:00 8 [Note] [MY-013457] [InnoDB] Clone Apply End Master Task ID: 0 Passed, code: 0: 
2019-07-25T22:22:58.506707+08:00 8 [Note] [MY-013457] [InnoDB] Clone End Master Task ID: 0 Passed, code: 0: 

Test, give an example of cloning

/opt/mysql8.0/bin/mysqld --datadir=/fander/clone_dir --port=3333 --socket=/tmp/mysql3333.sock --user=mysql3008user --lower-case-table-names=1 --mysqlx=OFF

#Explanation, because I didn't use my.cnf, I added more parameters.
#-- datadir specifies the data directory to start
#Port specifies the MySQL listening port to start
#Socket specifies the socket path
#The directory permission of user `donors'is mysql3008user:mysql3008user, and the user is mysql3008user. I haven't changed it.
#lower-case-table-names=1 with `donors'`
#If mysqlx=OFF is not closed, the default mysqlx port meets the 33060 duplicate conflict of `donors'.

#Login Check
mysql -uroot -proot -S /tmp/mysql3333.sock
mysql> show master status\G   # It can be seen that GTID is a subset of `donors', so it is very simple to show that `recipients''direct and `donors'' builder-slave replication is very simple.

2. Remote Cloning

Prerequisites and limitations of remote cloning

• Both donors and recipients need to install cloning plug-ins

• Donors and recipients need accounts with at least BACKUP_ADMIN/CLONE_ADMIN privileges, respectively.

  It implies that the recipient must first start a database instance (empty or data instances are acceptable because they will be deleted)

• Cloning target directory must have write permission
• DDL is not allowed during cloning operations and concurrent DML is allowed.
• With the same version number, you cannot clone between MySQL 5.7 and MySQL 8.0 and require version >= 8.0.17
• The same architecture for the same platform, such as linux to windows and x64 to x32, is not supported.
• Enough disk space
• It is possible to clone general table spaces, but it must have directory permissions. It is not supported to clone general spaces created by absolute paths. Cloned tablespace files with the same path as source tablespace files will cause conflicts
• Remote cloning does not support the use of mysqlx ports in CLONE INSTANCE FROM
• Cloning plug-ins do not support cloning MySQL servers to configure my.cnf, etc.
• Cloning plug-ins do not support cloning binary logs.
• Cloning plug-ins clone only stored data InnoDB. No other storage engine data is cloned. Tables stored in MyISAM and CSV in any schema including sys mode are cloned as empty tables.
• Connecting to a donor instance via MySQL router is not supported.
• Some parameters must be consistent, such as innodb_page_size, innodb_data_file_path, --lower_case_table_names
• If you clone encrypted or page compressed data, donors and recipients must have the same file system block size
• If you want to clone encrypted data, you need a secure connection
• clone_valid_donor_list must contain the host address of the donor MySQL server instance in the recipient's settings.
• No other cloning operations must be running. Only one cloning operation is allowed at a time. To determine whether the clone operation is running, query the clone_status table.
• By default, the recipient MySQL instance is automatically restarted after cloning the data. To restart automatically, a monitoring process must be provided on the receiver to detect whether the server has been shut down. Otherwise, after cloning the data, the cloning operation will stop and the following error will occur, and the recipient MySQL server instance will be closed:

ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).

Long-range Clone Warfare

Assuming that the preconditions are satisfied, the steps are as follows

Like local cloning, remote cloning requires plug-in installation and user authorization. The authorization of donors and recipients varies slightly.

1. Ensure that both donors and recipients have cloning plug-ins installed

INSTALL PLUGIN clone SONAME 'mysql_clone.so';

1. User Account Authorization

Donor authorization

mysql> CREATE USER clone_user@'192.168.199.101' IDENTIFIED by 'password1'; 
mysql> GRANT BACKUP_ADMIN ON *.* TO 'clone_user'@'192.168.199.101';  # BACKUP_ADMIN is the backup lock privilege only available in MySQL 8.0

Acceptor authorization

mysql> CREATE USER clone_user@'192.168.199.102' IDENTIFIED by 'password2'; 
mysql> GRANT CLONE_ADMIN ON *.* TO 'clone_user'@'192.168.199.102';  

1. Recipients set up a list of donors

mysql -uclone_user -ppassword2 -h192.168.199.102 -P3008
mysql> SET GLOBAL clone_valid_donor_list = '192.168.199.101:3008';

1. Recipients began pulling data from cloned donors

CLONE INSTANCE FROM clone_user@'192.168.199.101':3008
       IDENTIFIED BY 'password1';

The steps for remote cloning are as follows

mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
+-----------+-----------+----------------------------+
| STAGE     | STATE     | END_TIME                   |
+-----------+-----------+----------------------------+
| DROP DATA | Completed | 2019-07-25 21:56:01.725783 |
| FILE COPY | Completed | 2019-07-25 21:56:02.228686 |
| PAGE COPY | Completed | 2019-07-25 21:56:02.331409 |
| REDO COPY | Completed | 2019-07-25 21:56:02.432468 |
| FILE SYNC | Completed | 2019-07-25 21:56:02.576936 |
| RESTART   | Completed | 2019-07-25 21:56:06.564090 |
| RECOVERY  | Completed | 2019-07-25 21:56:06.892049 |
+-----------+-----------+----------------------------+
7 rows in set (0.01 sec)

The recipient will report an error if it is not started by mysqld_safe, but it does not affect cloning. You need to start mysqld manually.

ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).

Summary after Practice: Comparison between Cloning and xtrabackup

• Cloning and xtrabackup are both physical hot standby, and the principle of backup recovery is similar.
• Cloning needs to start an instance and authorize it when restoring an instance, whereas xtrabackup does not.
• xtrabackup needs application log after backup. Cloning is similar to backup-and-application-log provided by mysqlbackup, which is combined to do.
• The permission of xtrabackup backup file is equal to the permission of the person who executes the command. When restoring the instance, it needs manual chown to return to the instance permission. After cloning and backup, the permission is identical with the original data permission, and no manual chown is needed to facilitate recovery.
• When restoring xtrabackup, reset master needs to be executed in mysql; then set global gtid_purged="UUID:NUMBER", the specific value of UUID:NUMBER is the content of xtrabackup_info file in backup file; cloning does not need this operation step, and by default cloning can establish replication.
• xtrabackup has backup my.cnf, but cloning does not.
• When the backup of xtrabackup is completed, scp is usually copied to another machine to recover. It takes port 22 and MySQL's listening port is cloned. So when the directory permissions are correct, you don't even need the permissions to log on to the Linux server at all. As follows:

[root@192-168-199-103 ~]# mysql -uroot -ppassword2 -h192.168.199.102 -P3008 -e "SET GLOBAL clone_valid_donor_list = '192.168.199.101:3008';"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@192-168-199-103 ~]# mysql -uroot -ppassword2 -h192.168.199.102 -P3008 -e "CLONE INSTANCE FROM root@'192.168.199.101':3008 IDENTIFIED BY 'password1';"
mysql: [Warning] Using a password on the command line interface can be insecure.

3. Establishment of master-slave replication by cloning

The cloned recipient instance can establish master-slave replication with the donor. Of course, it is also possible to establish group replication. For the reasons of length, it is interesting to read section 18.4.3.1 of the official document "Cloning Distributed Recovery" without examples. https://dev.mysql.com/doc/refman/8.0/en/group-replication-cloning.html

When replicating traditionally, view the postion location with the following commands

mysql> SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;
+------------------+-----------------+
| BINLOG_FILE      | BINLOG_POSITION |
+------------------+-----------------+
| mysql-bin.000014 |            2179 |
+------------------+-----------------+
1 row in set (0.01 sec)

When replicating GTID, view the location of GTID by following commands

mysql> SELECT @@GLOBAL.GTID_EXECUTED;
+-------------------------------------------+
| @@GLOBAL.GTID_EXECUTED                    |
+-------------------------------------------+
| 990fa9a4-7aca-11e9-89fa-000c29abbade:1-11 |
+-------------------------------------------+
1 row in set (0.00 sec)

Assuming that we have the following authorization, we can create a replica.

create user repl@'%' identified WITH 'mysql_native_password' by 'password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';

Establishing master-slave replication relationship of GTID

(on the recipient)

CHANGE MASTER TO
  MASTER_HOST='192.168.199.101',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_PORT=3008,
  MASTER_AUTO_POSITION=1;

Monitoring cloning operations

Check cloning progress

mysql> SELECT STATE FROM performance_schema.clone_status;
+-----------+
| STATE     |
+-----------+
| Completed |
+-----------+
1 row in set (0.00 sec)

SELECT STATE, ERROR_NO, ERROR_MESSAGE FROM performance_schema.clone_status;

Check for cloning errors

mysql> SELECT STATE, ERROR_NO, ERROR_MESSAGE FROM performance_schema.clone_status;
+-----------+----------+---------------+
| STATE     | ERROR_NO | ERROR_MESSAGE |
+-----------+----------+---------------+
| Completed |        0 |               |
+-----------+----------+---------------+
1 row in set (0.00 sec)

Check the number of clones

show global status like 'Com_clone';  # ` Donors `every time + 1, `recipients'0

Clones can be kill ed at any time

Use
SELECT * FROM performance_schema.clone_status\G
 or
show processlist
 Look at the thread ID, then kill ID

summary

Cloning is very interesting. It is easy to operate and can be used to quickly build, restore master-slave replication or group replication. It can partly replace the open source hot standby software xtrabackup. It is expected that in the future, the government will make him better and richer.