Shulou(Shulou.com)05/31 Report--

Today, the editor will share with you the relevant knowledge points about how to use MySQL's Clone plug-in, the content is detailed, and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article.

Introduction to cloning plug-in

MySQL 8.0.17 introduced the Clone plug-in, which can be cloned locally or from a remote MySQL server instance. The cloned data is a physical snapshot of the data stored in InnoDB, including schemas, tables, tablespaces, and data dictionary metadata. Cloning operations include local and remote clones.

Local clone operation: remote clone operation:

Install the clone plug-in

To use the Clone plug-in for cloning operations, you must first install and configure the plug-in. The name of the plug-in is mysql_clone.so and there are two ways to install it.

Method 1:

The plug-in for the MySQL database is placed in the directory corresponding to the system variable plugin_dir by default. You can load the plug-in with the-- plugin-load-add option when the MySQL server starts, but this method requires that you specify the corresponding option each time you start the server, and you can configure it in the my.cnf file, that is:

[mysqld] plugin-load-add=mysql_clone.so method 2:

Load the plug-in at runtime, install it using INSTALL PLUGIN, and register the plug-in in the mysql.plugin system table:

Install plugin clone soname 'mysql_clone.so'

After installation, you can view it in the information_ schema.plugins table or through show plugins.

Clone local data

To clone data locally is to clone the MySQL data directory from the same server or node to another directory. The syntax is as follows

CLONE LOCAL DATA DIRECTORY [=]'/ path/to/clone_dir'

To execute the above statement, the corresponding user must have BACKUP_ADMIN permission, and the file or tablespace created by the user must be in the data directory. At the same time, the destination of the clone must specify an absolute path, and the full path of the directory must exist, but clone_dir must not exist.

Demo: clone local data

1) create a user

Mysql > select version (); +-+ | version () | +-+ | 8.0.25 | +-+ 1 row in set (0.00 sec) mysql > create user clone_admin identified by 'Cl0neTest';Query OK, 0 rows affected (0.02 sec) mysql > grant backup_admin on *. * to clone_admin;Query OK, 0 rows affected (0.10 sec)

2) create a directory

[root@node1 ~] # mkdir / mysql/clone/ [root@node1 ~] # chown-R mysql:mysql / mysql/clone/

3) Clone operation

Mysql > clone local data directory='/mysql/clone/clone_data';Query OK, 0 rows affected (17.09sec)

4) View cloned files

[root@node1] # ll / mysql/clone/clone_data/total 6348816drwxr Murray Murray. 2 mysql mysql 89 Nov 28 11:26 # clone-rw-r-. 1 mysql mysql 9231 Nov 28 11:26 ib_buffer_pool-rw-r-. 1 mysql mysql 4294967296 Nov 28 11:26 ibdata1-rw-r-. 1 mysql mysql 1073741824 Nov 28 11:26 ib_logfile0-rw-r-. 1 mysql mysql 1073741824 Nov 28 11:26 ib_logfile1drwxr-x---. 2 mysql mysql 6 Nov 28 11:26 mysql-rw-r-. 1 mysql mysql 25165824 Nov 28 11:26 mysql.ibddrwxr-x---. 2 mysql mysql 4096 Nov 28 11:26 sakiladrwxr-x---. 2 mysql mysql 28 Nov 28 11:26 sys-rw-r-. 1 mysql mysql 16777216 Nov 28 11:26 undo_001-rw-r-. 1 mysql mysql 16777216 Nov 28 11:26 undo_002

5) verify that the database is started using the cloned directory

[root@node1 ~] # service mysql.server stopShutting down MySQL.... SUCCESS! [root@node1 ~] # mysqld_safe-- datadir=/mysql/clone/clone_data/-- lower-case-table-names=1-- user=mysql2021-11-28T03:47:11.012900Z mysqld_safe Logging to'/ mysql/clone/clone_data/node1.com.cn.err'.2021-11-28T03:47:11.036181Z mysqld_safe Starting mysqld daemon with databases from / mysql/clone/clone_data Clone remote data

"Clone the remote MySQL server instance (donor) and transfer it to the MySQL instance (recipient) that performs the clone operation. The syntax for cloning remote data is as follows:"

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

Among them

User is the user name of the donor MySQL server instance

Password is the password of user

Host is the hostname address of the donor MySQL server instance. Currently, IPv4 is supported. IPv6 is not supported, but aliases can be used.

Port is the port number of the donor MySQL server instance

DATA DIRECTORY [=] 'clone_dir' is an optional clause to specify the directory where the clone data is received. Not specifying this option will overwrite the existing data file, and specify this option to transfer the clone data to this directory

REQUIRE [NO] SSL explicitly specifies whether to use encrypted connections

To perform the cloning operation, the clone plug-in must be activated in the donor and recipient MySQL server instances. In the donor server instance, BACKUP_ADMIN permission is required to clone the user. In the recipient server instance, the CLONE_ADMIN permission is required to clone the user, and the CLONE_ADMIN permission includes BACKUP_ADMIN and SHUTDOWN permission.

The following prerequisites must be met to execute a CLONE INSTANCE statement:

Donor and recipient must have the same MySQL server version, and the clone plug-in is supported after version 8.0.17

Donor and recipient must run on the same operating system and platform

To clone data, recipient must have enough disk space

InnoDB needs to create a tablespace outside the data directory, which can be viewed through INFORMATION_SCHEMA.FILES

The clone plug-in must be activated in donor and recipient and can be viewed through SHOW PLUGINS

Donor and recipient must have the same MySQL server character set and collation

Donor and recipient need to have the same innodb_page_size and innodb_data_file_path settings

If you clone encrypted or page-compressed data, donor and recipient must have the same file system block size

If you clone encrypted data, you need a secure connection

The clone_valid_donor_list setting on recipient must include the host address of the donor MySQL server instance

There can be only one clone operation at a time. No other clone operations can be performed during the cloning period. You can view them through clone_status.

The clone plug-in transmits data in the form of 1MB packets and metadata, and the minimum max_allowed_packet required on donor and recipient MySQL server instances is 2MB

The Undo tablespace filename on donor must be unique when the data is cloned into the recipient,undo tablespace to the location specified by innodb_undo_directory on recipient or to the directory specified by the DATA DIRECTORY [=] 'clone_dir' clause

By default, the recipient MySQL server instance automatically restarts after the data has been cloned

Several variables control all aspects of remote cloning operations

Demo: clone remote data

By default, the data is cloned to the data directory on the client side, and overwritten with donor data, and then the MySQL server instance on the client side is automatically restarted.

1) Log in to the donor MySQL server instance, create a user and install the plug-in (ignored if installed)

Mysql > create user' donor_clone_user' identified by 'donor_clone_user';Query OK, 0 rows affected (0.02 sec) mysql > grant backup_admin on *. * to donor_clone_user;Query OK, 0 rows affected (0.01 sec)

2) Log in to the recipient MySQL server instance, create an account and install the plug-in, and set up clone_valid_donor_list

Mysql > create user recipient_clone_user identified by 'recipient_clone_user';Query OK, 0 rows affected (0.04 sec) mysql > grant clone_admin,backup_admin on *. * to recipient_clone_user;Query OK, 0 rows affected (0.01 sec) mysql > install plugin clone soname' mysql_clone.so';Query OK, 0 rows affected (0.01 sec) mysql > set global clone_valid_donor_list='192.168.56.53:3306';Query OK, 0 rows affected (0.00 sec)

3) Log in to the recipient MySQL server instance and use the recipient_clone_user user or root user to perform the clone operation, which will restart automatically after the operation is completed.

Mysql > clone instance from 'donor_clone_user'@'192.168.56.81':3306 identified by' donor_clone_user';Query OK, 0 rows affected (51.08 sec)

Note: cloning the data of donor to the client will overwrite its data file by default, or you can specify a directory to clone it, as follows:

Mysql > clone instance from 'donor_clone_user'@'192.168.56.81':3306 identified by' donor_clone_user' data directory='/mysql/clone/clone_data';Query OK, 0 rows affected (51.17 sec)

Start the MySQL server instance with the new directory:

[root@node2 clone] # mysqld-- lower-case-table-names=1-- datadir=/mysql/clone/clone_data/-- user=mysql & that's all of the article "how to use MySQL's Clone plugin". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

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.