Shulou(Shulou.com)06/01 Report--

This article mainly introduces what the new feature Clone Plugin of MySQL 8 is, which can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Clone Plugin is a major feature introduced in MySQL 8.0.17, so why implement this feature? Personal feeling, mainly for Group Replication services. In Group Replication, a new node is added, and the completion of differential data is realized by distributed recovery (Distributed Recovery).

Prior to MySQL 8.0.17, only one recovery method, Binlog, was supported. However, if the Binlog needed by the new node has been Purge, at this time, we can only synchronize all the data with the help of backup tool (XtraBackup,mydumper,mysqldump), and then synchronize the incremental data through distributed recovery.

In this way, although it can also achieve the purpose of adding nodes, it still needs the help of external tools, which requires a certain amount of work and threshold. You know, its competitor, PXC, default integrates XtraBackup for State Snapshot Transfer (similar to full synchronization), while MongoDB goes a step further and natively implements Initial Sync to synchronize all data. In terms of ease of use, MySQL is not as good as its competitors in terms of adding nodes to the cluster. There is still a lot of room for improvement in customer experience.

Fortunately, MySQL officials also faced up to this gap and finally implemented Clone Plugin in MySQL 8.0.17. Of course, it is not difficult for officials to implement this feature, after all, there is an off-the-shelf physical backup tool (MySQL Enterprise Backup) to learn from.

This article will be carried out from the following aspects:

Installation of Clone Plugin how to use Clone Plugin to see the progress of cloning operations, how to build a slave library based on clone data, implementation details of Clone Plugin, limitations of Clone Plugin and XtraBackup comparison of Clone Plugin and XtraBackup parameter parsing of Clone Plugin, installation of Clone Plugin

Clone Plugin supports the following two installation methods:

(1) profile assignment

[mysqld] plugin-load-add=mysql_clone.soclone=FORCE_PLUS_PERMANENT copy code

The clone here, strictly speaking, is not the parameter name, but the plug-in name, which can be added or not, and FORCE_PLUS_PERMANENT controls the behavior of the plug-in.

There are four values:

ON** (* * enable plug-ins) OFF (disable plug-ins) FORCE (force enable. If the plug-in initialization fails, MySQL will not start) FORCE_PLUS_PERMANENT (on the basis of FORCE, it is not allowed to uninstall the plug-in through the UNINSTALL PLUGIN command).

(2) dynamic loading

[mysqld] plugin-load-add=mysql_clone.soclone=FORCE_PLUS_PERMANENT copy code

Check whether the plug-in is installed successfully

Mysql > show plugins;... | clone | ACTIVE | CLONE | mysql_clone.so | GPL |. Copy the code

The clone status displayed as "ACTIVE" indicates that the plug-in has loaded successfully.

II. The use of Clone Plugin

Clone Plugin supports two cloning methods: local cloning and remote cloning.

1. Local cloning

Local cloning is initiated locally in the instance, and its syntax is as follows:

CLONE LOCAL DATA DIRECTORY [=] 'clone_dir'; copy code

Where clone_dir is the clone directory.

Let's take a look at a specific Demo.

Create a cloned user

Mysql > create user' clone_user'@'%' identified by 'clone_pass';mysql > grant backup_admin on *. * to' clone_user'@'%'; copy the code

Create a clone directory

# mkdir / data/mysql# chown-R mysql.mysql / data/mysql copy Code

Create a local clone

# mysql-uclone_user-pclone_passmysql > clone local data directory='/data/mysql/3307'; copy code

Where "/ data/mysql/3307" is a clone directory, which needs to meet the following requirements:

The clone directory must be an absolute path.'/ data/mysql 'must exist and MySQL has writeable access to it. 3307 cannot exist.

View the contents of the clone directory

# ll / data/mysql/3307total 172996drwxr ibdata1-rw-r--2 mysql mysql 89 May 24 22:37 # clone-rw-r- 1 mysql mysql 3646 May 24 22:37 ib_buffer_pool-rw-r- 1 mysql mysql 12582912 May 24 22:37 ibdata1-rw-r- 1 mysql mysql 50331648 May 24 22:37 ib_logfile0-rw-r- 1 mysql mysql 50331648 May 24 22:37 ib_logfile1drwxr-x--- 2 Mysql mysql 6 May 24 22:37 mysql-rw-r- 1 mysql mysql 25165824 May 24 22:37 mysql.ibddrwxr-x--- 2 mysql mysql 20 May 24 22:37 slowtechdrwxr-x--- 2 mysql mysql 28 May 24 22:37 sys-rw-r- 1 mysql mysql 10485760 May 24 22:37 undo_001-rw-r- 1 mysql mysql 11534336 May 24 22:37 undo_002 copy Code

Compared with Xtrabackup, it can be started directly without Prepare.

# / usr/local/mysql/bin/mysqld-- no-defaults-- datadir=/data/mysql/3307-- user mysql-- port 3307 & copy code 2, remote cloning

Remote cloning involves two instances, of which the instance to be cloned is Donor and the instance that accepts cloned data is Recipient. The clone command needs to be initiated on Recipient with the following syntax:

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

Where host,port is the IP and port of (Donor) of the instance to be cloned, and user,password is the cloned user and password on Donor, which requires backup_admin permission, such as clone_user created above.

DATA DIRECTORY specifies the backup directory. If not, it will be cloned to the data directory of Recipient by default.

REQUIRE [NO] SSL, whether to enable SSL communication.

Next, take a look at a specific Demo.

First, create a cloned user on the Donor instance and load the Clone Plugin.

Mysql > create user' donor_user'@'%' identified by 'donor_pass';mysql > grant backup_admin on *. * to' donor_user'@'%';mysql > install plugin clone soname 'mysql_clone.so'; copy the code

Backup_admin is a required permission for clone operations.

Next, create a cloned user on the Recipient instance and load the Clone Plugin.

Mysql > create user' recipient_user'@'%' identified by 'recipient_pass';mysql > grant clone_admin on *. * to' recipient_user'@'%';mysql > install plugin clone soname 'mysql_clone.so'; copy the code

The clone_admin here implicitly contains backup_admin (blocking DDL) and shutdown (restart the instance) permissions.

Set up the Donor whitelist. Recipient can only clone instances in the whitelist.

Mysql > set global clone_valid_donor_list = '192.168.244.10 virtual 3306; copy the code

SYSTEM_VARIABLES_ADMIN permission is required to set this parameter.

Initiate a clone command on Recipient

# mysql-urecipient_user-precipient_passmysql > clone instance from 'donor_user'@'192.168.244.10':3306 identified by' donor_pass';Query OK, 0 rows affected (36.97 sec) copy code

The remote clone does the following in turn:

* * (1) * acquire backup lock. * * backup lock and DDL are mutually exclusive. Note that not only backup locks on Recipient,Donor are also acquired.

* * (2) * DROP user tablespace. * * Note that DROP is only user data, not data catalog, and does not include system tablespaces such as mysql,ibdata.

* * (3) copy data from the Donor instance. * * for user tablespaces, it will be copied directly. If it is a system tablespace, it will be renamed to xxx.#clone and will not directly replace the original file.

Ll / data/mysql/3306/data/...-rw-r- 1 mysql mysql 3646 May 25 07:20 ib_buffer_pool-rw-r- 1 mysql mysql 3646 May 27 07:31 ib_buffer_pool.#clone-rw-r- 1 mysql mysql 12582912 May 27 07:31 ibdata1-rw-r- 1 mysql mysql 12582912 May 27 07:31 ibdata1.#clone-rw-r- 1 mysql mysql 50331648 May 27 07:32 ib_logfile0-rw-r- 1 mysql mysql 50331648 May 27 07:31 ib_logfile0.#clone...-rw-r- 1 mysql mysql 25165824 May 27 07:31 mysql.ibd-rw-r- 1 mysql mysql 25165824 May 27 07:31 mysql.ibd.#clone... Copy the code

* * (4) restart the instance. * * during startup, the original system tablespace file is replaced with xxx.#clone.

3. How to check the progress of the clone operation

Checking the progress of the clone operation is mainly based on the performance_schema.clone_status and performance_schema.clone_progress tables.

First take a look at the performance_schema.clone_ status table.

Mysql > select * from performance_schema.clone_status\ gateway * 1\. Row * * ID: 1 PID: 0 STATE: Completed BEGIN_TIME: 2020-05-27 07 PID 31v 24.220 END_TIME: 2020-05-27 07 JV 33mer 08.185 SOURCE: 192.168.244.10 PID 3306 DESTINATION: LOCAL INSTANCE ERROR_NO: 0 ERROR_MESSAGE : BINLOG_FILE: mysql-bin.000009BINLOG_POSITION: 665197555 GTID_EXECUTED: 59cd4f8f-8fa1-11ea-a0fe-000c29f66609:1-5601 row in set (0.06 sec) copy code

As the name implies, this table records the current state of the clone operation.

Among them

* * PID:**Processlist ID. For the Id in show processlist, if you want to terminate the current clone operation, execute the kill processlist_id command.

* * status of STATE:** clone operation, Not Started (clone has not yet started), In Progress (cloning), Completed (clone successful), Failed (clone failure). If it is a Failed status, ERROR_NO,ERROR_MESSAGE will give a specific error code and error message.

* * BEGIN_TIME,END_TIME:** clone operation starts and ends.

* * address of SOURCE:**Donor instance.

* * DESTINATION:** clone directory. "LOCAL INSTANCE" represents the data directory of the current instance.

* * GTID_EXECUTED,BINLOG_FILE (BINLOG_POSITION): * * the collection of GTID and consistent location points that have been executed by the master database when the clone operation ends. This information can be used to build a slave database.

Next take a look at the performance_schema.clone_ 's table.

Mysql > select * from performance_schema.clone_progress +- -+ | ID | STAGE | STATE | BEGIN_TIME | END_TIME | THREADS | ESTIMATE | DATA | NETWORK | DATA_SPEED | NETWORK_SPEED | +- -+- -+-+ | 1 | DROP DATA | Completed | 2020-05-27 07Freight 31purl 28.581661 | 2020-052707Vers35.855706 | 1 | 0 | 0 | 0 | 1 | FILE COPY | Completed | 2020-0527 07FILE COPY 31purse 35.855952 | 2020-05- 2707VOL31GRV 58.270881 | 2 | 482463294 | 482463294 | 482497011 | 0 | 1 | PAGE COPY | Completed | 201005-2707VRV 58.271250 | 202005-2707VRS 58.719085 | 2 | 10977280 | 10977280 | 11014997 | 0 | 1 | REDO COPY | Completed | 2020-05-2707RU 31RV 58.720128 | : 31RESTART 58.930804 | 2 | 465408 | 465408 | 465903 | 0 | 0 | 1 | FILE SYNC | Completed | 2020-05-27 07 Vera 31R 58.931094 | 2 | 0 | 0 | 0 | 0 | 1 | RESTART | Completed | 2020-05-27 07 | : 32 01.063325 | 2020-05-27 07JV 32JV 59.844119 | 0 | 0 | 0 | 0 | 1 | RECOVERY | Completed | 2020-05-2707JV 32VO 59.844119 | 2020-05-270733v 08.185367 | 0 | 0 | 0 | 0 | 0 | +-- -+ 7 rows in set (0.00 sec) copy code

This table records the progress information of the clone operation.

* * A clone operation of STAGE:** can be subdivided into 7 stages such as DROP DATA,FILE COPY,PAGE COPY,REDO COPY,FILE SYNC,RESTART,RECOVERY. The next phase will not begin until the current phase is over.

* * status of the current phase of STATE:**. There are three states: Not Started,In Progress,Completed.

* * the start and end times of the current phase of BEGIN_TIME,END_TIME:**.

* * the number of concurrent threads used in the current phase of THREADS:**.

* * the amount of data estimated by ESTIMATE:**.

* * the amount of data that DATA:** has copied.

* * the amount of data that NETWORK:** transmits over the network. If it is a local clone, the value of this column is 0.

* * the current data copy rate of DATA_SPEED,NETWORK_SPEED:** and the rate of network transfer.

Note that it is the current value.

Fourth, how to build a slave database based on cloned data

Earlier, we introduced the performance_schema.clone_ status table, which records the consistent location point information for the Donor instance. We can use this information to build a slave database.

Mysql > select * from performance_schema.clone_status\ gateway * 1\. Row *. BINLOG_FILE: mysql-bin.000009BINLOG_POSITION: 665197555 GTID_EXECUTED: 59cd4f8f-8fa1-11ea-a0fe-000c29f66609:1-5601 row in set (0.06 sec) copy code

Here, two scenarios are distinguished, GTID replication and location-based replication.

1. GTID copy mysql > CHANGE MASTER TO MASTER_HOST = 'master_host_name', MASTER_PORT = master_port_num,... MASTER_AUTO_POSITION = 1smith MySQL > START SLAVE; copy the code

It is important to note that no additional set global gtid_purged operations are required. The gtid_purged has been initialized by cloning the instance started by the data.

Mysql > show global variables like 'gtid_purged' +-+-- + | Variable_name | Value | +-+- -- + | gtid_purged | 59cd4f8f-8fa1-11ea-a0fe-000c29f66609:1-560 | +-+-+ 1 row in set (0.00 sec) copy code 2. Location-based replication

Here, it is also necessary to distinguish between the two scenarios.

Scenario 1, the last recipient will be used as a slave library for Donor.

Mysql > SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status; mysql > CHANGE MASTER TO MASTER_HOST = 'master_host_name', MASTER_PORT = master_port_num,... MASTER_LOG_FILE = 'master_log_name', MASTER_LOG_POS = master_log_pos;mysql > START SLAVE; copy the code

Among them

The IP and port of the master_host_name,master_port_num:Donor instance.

BINLOG_FILE, BINLOG_POSITION in master_log_name,master_log_pos:performance_schema.clone_status.

Scenario 2 Donor donor itself is a slave library, and the Recipient is to be used as a slave library for the master library.

Mysql > SELECT MASTER_LOG_NAME, MASTER_LOG_POS FROM mysql.slave_relay_log_info;mysql > CHANGE MASTER TO MASTER_HOST = 'master_host_name', MASTER_PORT = master_port_num,... MASTER_LOG_FILE = 'master_log_name', MASTER_LOG_POS = master_log_pos;mysql > START SLAVE; copy the code

Among them

The IP and port of the master_host_name,master_port_num:Donor master library.

Master_log_name,Master_log_pos in master_log_name,master_log_pos:mysql.slave_relay_log_info (corresponding to Relay_Master_Log_File,Exec_Master_Log_Pos in SHOW SLAVE STATUS, respectively).

When building the slave library, it is recommended to set-- skip-slave-start. This parameter defaults to OFF. After the instance is started, the START SLAVE operation will be performed automatically.

If Donor is a slave library, Recipient will automatically establish replication based on the information in mysql.slave_master_info,mysql.slave_relay_log_info, which in many cases may not be our expected behavior.

Fifth, the implementation details of Clone Plugin

The clone operation can be subdivided into the following five phases.

[INIT]-- > [FILE COPY]-- > [PAGE COPY]-- > [REDO COPY]-> [Done] copy the code

* * 1. INIT:** initializes a cloned object.

* * 2. FILE COPY:** copies all data files. Before copying, a LSN is recorded as "CLONE START LSN", which is actually the LSN of the current CHECKPOINT, and the "Page Tracking" feature is enabled.

"Page Tracking" tracks pages that have been modified since "CLONE START LSN", specifically, records the Tablespace ID and page ID of the page. After the data file is copied, the LSN of the current CHECKPOINT is marked as "CLONE FILE END LSN".

* * 3. PAGE COPY:** copies pages between "CLONE START LSN" and "CLONE FILE END LSN". These pages are sorted before copying-based on Tablespace ID and page ID, to avoid random reads and writes during the copying process. In the meantime, turn on the "Redo Archiving" feature.

"Redo Archiving" starts an archiving thread in the background to copy the contents of the Redo file into the archive file as Chunk. Generally speaking, the copy speed of the archive thread is faster than the generation of Redo logs. "even if slower, when writing a new Redo log, it waits for the archive thread to complete the copy and does not overwrite the Redo log that has not been copied." When all modified pages are copied, the consistency location information of the instance is obtained, and the LSN is marked as "CLONE LSN".

4. REDO COPY: copy the Redo log between "CLONE FILE END LSN" and "CLONE LSN" in the archive file.

* * 5. Done:** calls snapshot_end () to destroy the cloned object.

VI. Restrictions on Clone Plugin

1. During cloning, the DDL command is not allowed. Similarly, DDL blocks the execution of clone commands

2. Clone Plugin will not copy the configuration parameters of Donor.

3. Clone Plugin will not copy the binary log files of Donor.

4. Clone Plugin will only copy the data of the InnoDB table, and for tables of other storage engines, only the table structure will be copied.

5. If a table in a Donor instance specifies an absolute path through DATA DIRECTORY, it will prompt you that the file already exists during local cloning. When making a remote clone, the absolute path must exist and have writeable permissions.

6. It is not allowed to connect Donor instances through MySQL Router.

7. When performing a CLONE INSTANCE operation, the specified Donor port cannot be an X Protocol port.

In addition, when making a remote clone, the following checks are performed:

MySQL versions (including minor versions) must be consistent and support Clone Plugin. ERROR 3864 (HY000): Clone Donor MySQL version: 8.0.20 is different from Recipient MySQL version 8.0.19. The operating system and the number of bits (32-bit, 64-bit) of the replication code host must be the same. Both can be obtained according to the version_compile_os,version_compile_machine parameter. Recipient must have enough disk space to store cloned data. Character set (character_set_server), check set (collation_server), character_set_filesystem must be consistent. Innodb_page_size must be consistent. The number and size of ibdata in innodb_data_file_path are checked. Currently, the implementation of Clone Plugin (8.0.20), whether Donor or Recipient, can only perform one clone operation at a time. Multiple clone operations are supported for concurrent execution in the future. ERROR 3634 (HY000): Too many concurrent clone operations. Maximum allowed-1. Copying the code Recipient requires a restart, so it must be managed through mysqld_safe, systemd, and so on. If the instance is started through mysqld, it needs to be started manually after the instance is closed. ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process). The Plugin that copies the ACTIVE state of the code must be consistent. 7. Comparison between Clone Plugin and XtraBackup

1. In implementation, both have FILE COPY and REDO COPY phases, but Clone Plugin has one more PAGE COPY than XtraBackup. The advantage is that Clone Plugin recovers faster than XtraBackup.

2. XtraBackup does not have Redo Archiving feature, so it is possible that uncopied Redo logs are overwritten.

3. Establish replication under GTID without additional set global gtid_purged operation.

8. Clone Plugin parameter parsing whether clone_autotune_concurrency automatically adjusts the number of concurrent threads in the cloning process. The default is ON. In this case, the maximum number of threads is controlled by the clone_max_concurrency parameter. If set to OFF, the number of concurrent threads will be fixed, consistent with the clone_max_concurrency parameter. The default value for this parameter is 16. When clone_buffer_size is cloned locally, the size of the transit buffer is 4m by default. The larger the buffer, the faster the backup and, accordingly, the greater the pressure on the disk IO. The clone_ddl_timeout clone operation requires the acquisition of a backup lock (Backup Lock). If DDL is executing while the CLONE command is being executed, the CLONE command will be blocked, waiting for the backup lock (Waiting for backup lock) to be acquired. The maximum length of wait is determined by the clone_ddl_timeout parameter, which defaults to 300 (in seconds). If the lock has not been acquired within this time, the CLONE command fails with the prompt "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction".

It is important to note that if a CLONE command is executed when DDL is executed, DDL will also be blocked because the backup lock cannot be obtained, except that the wait time for the DDL operation is determined by the lock_wait_timeout parameter, which defaults to 31536000s, or 365 days.

Clone_enable_compression remote cloning, whether compression is enabled when transferring data. Enabling compression saves network bandwidth, but correspondingly increases CPU consumption. The maximum allowable big data copy rate (in MiB/s) when clone_max_data_bandwidth is cloned remotely. The default is 0 and there is no limit. Note that the copy rate is limited to a single thread, and if there are multiple threads copying in parallel, the actual maximum copy rate = the number of clone_max_data_bandwidth* threads. The maximum network transfer rate (in MiB/s) that can be allowed when clone_max_network_bandwidth is cloned remotely. The default is 0 and there is no limit. If there is a bottleneck in the network bandwidth, you can use this parameter to limit the speed. Clone_valid_donor_list sets the Donor whitelist and can only clone the instances specified in the whitelist. Related to clone_ssl_ca,clone_ssl_cert,clone_ssl_key SSL. Thank you for reading this article carefully. I hope the editor will share what the new MySQL 8 feature Clone Plugin is helpful to you. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.