MySQL8.0 clone plugin function

MySQL8.0.17 introduced a heavyweight feature: clone plugin. Allow users to clone the current instance locally or remotely. It is suitable for the quick backup of the entire instance and the addition of new members of mgr.

The definition of clone: ​​is the online physical snapshot of the data stored in the schema (database), table (table), tablespaces (table space) and data dictionary metadata (data dictionary metadata) stored in InnoDB.

I have seen other colleagues' introductions several times and talked about clone can replace Percona xtrabackup.
Let’s take a look.

1.1. Loading

Clone is also in the same way as other plugin plug-ins, it exists in the mysql/lib/plugin directory

image.png

Need to configure my.cnf to start the add-on

##配置my.cnf 8.0.20版本试了都不行,是个坑,看来后续版本会修复[mysqld]plugin-load-add=mysql_clone.soclone=FORCE_PLUS_PERMANENT ##启动加载,是可以的mysqld_safe --defaults-file=/etc/my.cnf --plugin-load-add=mysql_clone.so  --user=mysql   &

Remarks: The FORCE_PLUS_PERMANENT protection mechanism here, loads the plug-in at startup and prevents it from being deleted at runtime

Command line loading and reliable:

##加载mysql>  INSTALL PLUGIN clone SONAME 'mysql_clone.so';Query OK, 0 rows affected (0.01 sec)##卸载命令mysql> UNINSTALL  PLUGIN clone;Query OK, 0 rows affected (0.01 sec)##查看mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'clone';
image.png

Remarks: Load the introduction, it's best to check the official website if you take the time. https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-installation.html

1.2. Local clone

image.png


The local clone operation clones data from the MySQL server instance, where the clone operation is initialized to a directory on the same server or node where the MySQL server instance is running.

BACKUP_ADMIN privilege is required to execute the clone local data directory statement.

mysql> CREATE USER 'clone_user'@'%' identified by '123456';Query OK, 0 rows affected (0.01 sec) mysql> GRANT BACKUP_ADMIN ON  *.*  TO  'clone_user'@'%' ;Query OK, 0 rows affected (0.01 sec)

Log in with the backup account to perform the clone operation

##文件夹权限,必须要赋予mysql所有权要不会报错[[email protected] backup]# chown -R mysql:mysql /opt/backup/20200520##backup权限登录只能看到 infomation库mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema |+--------------------+##bak目录不能存在,如存在会提示database exists错误mysql> clone LOCAL DATA DIRECTORY = '/opt/backup/20200520/bak';Query OK, 0 rows affected (0.58 sec)

The process is very simple, check the backup file:

image.png


Remarks:

  • Without the information_schema and db2 folders, db2 is just an empty library on production and no tables.
  • The following files will not be cloned: socket, pid-file, tmpdir, log-error, slow_query_log_file, log-bin, relay-log

1.3. Recovery

#清空datadir目录,把备份出来的clone_dir文件拷贝datadir目录下,还得touch一个 错误日志[[email protected] mysql]# cp -r /opt/backup/20200520/bak /opt/data8.1/mysql[[email protected] mysql]# touch /opt/data8.1/logs/mysql_err.log [[email protected] mysql]# chow -R mysql:mysql[[email protected] mysql]# mysqld_safe --defaults-file=/etc/clone.cnf --user=mysql  &

Clone library login confirmation: The
database is displayed normally, and the master status is also normal (the original master library gtid information is also included).

image.png


Quickly build the slave library: After many attempts, it is normal and stable, indicating that the file consistency of the clone is very good.

mysql> change master to    ->       master_host = '192.168.244.129',     ->       master_port = 3380,    ->       master_user = 'repl',     ->       master_password = '123456',     ->       master_auto_position=1;
image.png


Remarks:
When cloning data, the following directories and files will be created for internal use. Never modify it.

  • #clone: ​​Contains the internal clone files used by the clone operation. Created in the directory where the data is cloned.
  • #ib_archive: Contains internally archived log files, which are archived on the provider during operation.
  • *.#clone files: Temporary data files created on the recipient when the remote clone operation replaces the existing data directory.

1.4. Remote clone

image.png


The remote clone operation involves the local MySQL server instance ("recipient") that starts the clone operation and the remote MySQL server instance ("donor") where the source data is located. When the remote clone operation is started on the recipient, the clone data is transmitted from the sender to the receiver over the network. By default, the remote clone operation deletes the data in the recipient data directory and replaces it with clone data. You can choose to copy the data to another directory on the recipient to avoid deleting existing data.

There is no difference between the data of the local clone operation and the data of the remote clone operation. These two operations clone the same data.

The clone plugin supports replication. In addition to the clone data, the clone operation also extracts and transfers the copy coordinates from the donor and applies them to the recipient, which makes it possible to use the clone plugin to provide group copy members and copy slave nodes. Compared with copying a large number of transactions, using clone plugin for provisioning is much faster and more efficient. Group replication members can also be configured to use the clone plug-in as another recovery method so that the members automatically choose the most effective method for retrieving group data from seed members. The clone plugin supports encrypted and page compressed data.

1.4.1 Syntax

clone INSTANCE FROM 'user'@'host':portIDENTIFIED BY 'password'[DATA DIRECTORY [=] 'clone_dir'][REQUIRE [NO] SSL];

Specify in the syntax: ip, port, user, password, directory.

1.4.2 13 restrictions:

Donor: the node is in the state of preparing or transmitting the full amount of cluster data for the new node, which is the source end
recipient: the node that needs to be replicated is the target end

  • 1. The clone plugin must be active on both the sender (donor) and receiver (recipient) MySQL server instances
  • 2. Permission:
    The user logged in by the Donor at the source needs the BACKUP_ADMIN privilege to access and transfer data from the clone server, and block DDL during operation.
    Recipient users on the target side need clone_ADMIN privileges to replace recipient data, block DDL during the operation, and automatically restart the server. The clone_ADMIN privilege implicitly includes the BACKUP_ADMIN and SHUTDOWN privileges.
  • 3. The same version: the donor and recipient must have the same MySQL server version. MYSQL 8.0.17 and higher versions support clone plugins.
  • 4. The recipient must have enough disk space to store the clone data.
  • 5.SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES; only the identified content will be cloned
  • 6. The same MySQL server character set and collation
  • 7. The innodb_page_size and innodb_data_file_path settings must be the same
  • 8. To clone encrypted or page compressed data, donor and replicat must have the same file system block size.
  • 9.ssl, the secret key mechanism must be the same
  • 10. Set clone_valid_donor_list source library information in recipient
  • 11. Only one clone operation is allowed at a time
  • 12. The clone plug-in transmits data in the form of a 1MB package and metadata. Therefore, for recipient and donor MySQL server instances, the minimum required max_allowed_packet value is 2MB. The max_allowed_packet value is less than 2MB will cause an error.
  • 13. During the Undo tablespace clone period, it must not be changed. Starting from MySQL 8.0.18, if duplicate undo tablespace file names are encountered during the clone operation, an error will be reported.

1.4.3 Preparation

The donor uses the previous library, and the recipient loads the plug-in

##加载组件mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';Query OK, 0 rows affected (0.02 sec)mysql>  SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'clone';+-------------+---------------+| PLUGIN_NAME | PLUGIN_STATUS |+-------------+---------------+| clone       | ACTIVE        |+-------------+---------------+##用户创建BACKUP_ADMIN ,clone_ADMIN权限mysql> CREATE USER 'clone_user'@'%' identified by '123456';Query OK, 0 rows affected (0.01 sec)mysql> GRANT BACKUP_ADMIN ,clone_ADMIN ON  *.*  TO  'clone_user'@'%' ;Query OK, 0 rows affected (0.01 sec)##源端ip信息mysql> SET GLOBAL clone_valid_donor_list = "192.168.244.129:3380";Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'clone_valid_donor_list';+------------------------+----------------------+| Variable_name          | Value                |+------------------------+----------------------+| clone_valid_donor_list | 192.168.244.129:3380 |+------------------------+----------------------+

1.4.4 Start clone

The recipient server logs in with clone_user and executes clone to overwrite local data

##clonemysql> clone INSTANCE FROM 'clone_user'@'192.168.244.129':3380  IDENTIFIED BY '123456';Query OK, 0 rows affected (1.76 sec) mysql> Restarting mysqld...2020-05-22T15:41:51.659583Z mysqld_safe Number of processes running now: 02020-05-22T15:41:51.666882Z mysqld_safe mysqld restarted

Specify the directory:

mysql>clone INSTANCE FROM 'clone_user'@'192.168.244.129':3380  IDENTIFIED BY '123456' DATA DIRECTORY= '/opt/backup/20200520/bak02';

Just perform data clone without overwriting and restarting the database service. You need to manually configure the path to use clone data. The rest is the subsequent recovery operation mode of the local clone.

Because the amount of data is small, it is very fast. If the amount of clone data is large, this command usually takes a long time.

1.4.5 View clone status

There are two ways here, but they are still lacking, especially the error records are basically absent.
1) Two tables are provided under performance_schema: clone_status, clone_progress to monitor the execution status and situation

image.png
image.png

Specific table field description:

clone_status
ID: The unique clone operation identifier in the current MySQL server instance
PID: The process list ID of the session that performs the clone operation
STATE: The current status of the clone operation includes not started, in progress, completed, and failed
(Not Started, In Progress , Completed, and Failed)
BEGIN_TIME: start time
END_TIME: end time
SOURCE: source server information
DESTINATION: target directory
ERROR_NO: failure error number
ERROR_MESSAGE: failure error message
BINLOG_FILE: the name of the binary log file to which the
data is copied BINLOG_POSITION: copy the data The location of the binary log to the
GTID_EXECUTED: GTID value of the last clone transaction

clone_progress
ID: The unique clone operation identifier in the current MySQL server instance
STAGE: Phases include data deletion, file copy, PAGE_COPY, REDO_COPY, FILE_SYNC, restart and recovery
STATE: The current status of the clone phase includes not started, in progress, and already complete
Not started, the in Progress, the completed
BEGIN_TIME: start time
END_TIME: end time
tHREADS: the number of concurrent threads used in stage
eSTIMATE: estimated data amount of the current phase, in bytes
dATA: current amount of data transmitted in a state to Byte as a unit
NETWORK: The amount of network data transferred in the current state, in bytes
DATA_SPEED: The actual speed of the current data transfer, in bytes per second.
This value may be different from the requested maximum data transfer rate defined by clone_max_data_bandwidth
NETWORK_SPEED : The current network transmission speed, in bytes per second

Note: In the clone_progress table, you can see that the clone process is divided into several stages: DROP DATA, FILE COPY, PAGE COPY, REDO COPY, FILE SYNC, RESTART, RECOVERY. This is very useful for understanding the current stage.

2) See the execution status through performance_schema.setup_instruments

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'       WHERE NAME LIKE 'stage/innodb/clone%'; mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'       WHERE NAME LIKE '%stages%'; mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current       WHERE EVENT_NAME LIKE 'stage/innodb/clone%';+--------------------------------+----------------+----------------+| EVENT_NAME                     | WORK_COMPLETED | WORK_ESTIMATED |+--------------------------------+----------------+----------------+| stage/innodb/clone (redo copy) |              1 |              1 |+--------------------------------+----------------+----------------+ mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_history       WHERE EVENT_NAME LIKE 'stage/innodb/clone%';+--------------------------------+----------------+----------------+| EVENT_NAME                     | WORK_COMPLETED | WORK_ESTIMATED |+--------------------------------+----------------+----------------+| stage/innodb/clone (file copy) |            301 |            301 || stage/innodb/clone (page copy) |              0 |              0 || stage/innodb/clone (redo copy) |              1 |              1 |+--------------------------------+----------------+----------------+ mysql> SELECT * FROM performance_schema.setup_instruments       WHERE NAME LIKE WHERE NAME LIKE '%clone%';+----------------------------------------------+---------+| NAME                                         | ENABLED |+----------------------------------------------+---------+| wait/synch/mutex/innodb/clone_snapshot_mutex | NO      || wait/synch/mutex/innodb/clone_sys_mutex      | NO      || wait/synch/mutex/innodb/clone_task_mutex     | NO      || wait/io/file/innodb/innodb_clone_file        | YES     || stage/innodb/clone (file copy)               | YES     || stage/innodb/clone (redo copy)               | YES     || stage/innodb/clone (page copy)               | YES     || statement/abstract/clone                     | YES     || statement/clone/local                        | YES     || statement/clone/client                       | YES     || statement/clone/server                       | YES     || memory/innodb/clone                          | YES     || memory/clone/data                            | YES     |+----------------------------------------------+---------+

Performance mode statement event tracking statement execution:

  • statement/abstract/clone: ​​Track the statement events of any clone operation, and then classify them as local, client, or server operation types.
  • statement/clone/local: track the clone statement event of the local clone operation; it is generated when the clone local statement is executed.
  • statement/clone/client: Track remote clone statement events that occur on the recipient MySQL server instance; it is generated when the clone instance statement is executed on the recipient.
  • statement/clone/server: Track statement events that occur on the remote clone MySQL server instance; it is generated when the clone instance statement is executed on the recipient.

Detailed description: https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-monitoring.html

3) There are
only two lines of information recorded in mysql_error, and there are no other lines. It really needs official improvement.
Because sometimes the performance library will hang live.

image.png

1.4.6 Stop the clone operation

Pass the PID information of the performance_schema.clone_status table, and then use the KILL QUERY processlist_id statement to stop the clone operation

1.5. clone parameters

So far, 11 parameters have been provided for use with clone. The variables are configured on the MySQL server instance that performs the clone operation. It is recommended that the source and target libraries are set, and they are all dynamic parameters.

  • 1.clone-autotune-concurrency:
    default value: Yes, configuration object: recipient,
    description: enable the dynamic generation of threads for remote clone operations. This setting only applies to the receiving MySQL server instance. The clone_max_concurrency variable defines the maximum number of threads that can be spawned
  • 2. clone_max_concurrency:
    default value: 16, range: 1~128, configuration object: recipient,
    description: define the maximum number of concurrent threads for remote clone operations. The default value is 16. More threads can improve clone performance, but also reduce the number of concurrent client connections allowed, which will affect the performance of existing client connections
  • 3.clone_max_data_bandwidth:
    default value: 0, range: 0~1048576, configuration object: recipient,
    description: Define the maximum data transfer rate per second of remote clone operation, the unit is mebibytes (MiB). This variable helps manage the performance impact of clone operations. The limit should be set only when the donor disk I/O bandwidth reaches saturation and affects performance
  • 4.clone_max_network_bandwidth:
    Default value: 0 Range: 0~1048576 Configuration object: recipient
    Description: Specify the maximum approximate network transmission rate per second for remote clone operations (in mebibytes (MiB))
  • 5.clone_valid_donor_list:
    configuration object: recipient
    Description: define the effective host address of the remote clone operation
  • 6.clone-enable-compression:
    Default value: OFF Configuration object: donor, recipient
    Description: Allows to compress network layer data during remote clone operation. Compression saves network bandwidth at the cost of CPU. Enabling compression can increase the data transfer rate
  • 7.clone-ddl-timeout:
    Default value: 0 Range: 0~2592000 Configuration object: donor, recipient
    Description: The time (in seconds) to wait for the backup lock when performing the clone operation. This setting applies to both the sender and receiver MySQL server instances. A value of 0 means that the clone operation does not require a backup lock. In this case, if you try the DDL operation at the same time, the operation will fail with an error
  • 8.clone-buffer-size:
    Default value: 4M Range: 1048576~268435456 Configuration object: donor, recipient
    Description: The default value is 4 megabytes (MiB). Larger buffer sizes may allow I/O device drivers to fetch data in parallel, which can improve clone performance.
  • 9.clone_ssl_ca:
    configuration object: recipient
    description: ca certificate
  • 10.clone-ssl-cert:
    configuration object: recipient
    Description: specify the path of the public key certificate
  • 11.clone_ssl_key:
    configuration object: recipient
    Description: specify the path of the private key file

1.6. Problem

  • Regarding the network load, will there be a problem that the clone on the transmission occupies the network bandwidth? Although there are parameter settings, it takes time to polish. Especially now that some enterprises can reach 1T level of data in a single MySQL database.
  • Whether clone will kill threads, there are still more problems with mysql thread pool.
  • Clone a single database backup, a single table is temporarily unable to support, hope to provide such a function. After all, there is a multi-source copy function
  • The clone function will delete all data on the target MySQL instance. Make a backup before.
  • The log function of clone needs to be improved

1.7. Summary

As far as experience is concerned, I currently believe that the function of xtrabackup cannot be replaced, and it can only be said that each has its own advantages.
Although in the mgr environment, initialization is implemented by clone, it is not perfect in terms of the above-mentioned problem points, function points, large data transfer stability, and log records.

The highlight of the clone implementation here is the use of Page Tracking and Redo Archiving functions, xtrabackup is based on the Redo Archiving function, this part can be understood and it is worth learning.

In the actual environment of 8.0, you should have both xtrabackup & clone. I also hope that xtrabackup will develop new functions based on clone, keep improving, and make progress in the competition.

Reference:
https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html
https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-replication.html
https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-remote.html
https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-monitoring.html