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

The clone plug-in allows you to clone data locally or from a remote MySQL server instance. It can also be seen as a backup method, so I put it into backup arrangement. Clone data is a physical snapshot of the data stored in InnoDB, including schema, table, tablespace, and data dictionary metadata. The cloned data contains a full-featured data directory that allows MySQL server configuration using the clone plug-in.

Local clone operation

The local clone operation clones the data from the MySQL server instance that initiated the clone operation to a directory on the same server or on the same node (note this configuration secure_file_priv). This feature reminds me of tukodb's hotbackup, which I wrote in my previous article.

Remote cloning

By default, the remote clone operation deletes the data in the recipient (recipient) data directory and replaces it with the donor (donor) clone data. (optional) you can also clone the data to another directory of the recipient to avoid deleting existing data.

There is no difference between the data cloned by the remote clone operation and the local clone operation, the data is the same.

The clone plug-in supports replication. In addition to cloning data, the clone operation also extracts and transfers replication location information from the donor and applies it to the recipient, so that you can use the clone plug-in to configure group replication or master-slave replication. Configuring with a clone plug-in is much faster and more efficient than copying a large number of transactions.

This feature reminds me of postgresql's pg_basebackup, which will be written in future articles.

1. Install MySQL on two machines respectively

192.168.56.16 es3192.168.56.15 es2 [root@es2 ~] # yum-y install mysql-community-* [root@es3 ~] # yum-y localinstall mysql-community-*

2. Modify the configuration file

[root@es2 ~] # grep-Ev "^ $| ^ [#;]" / etc/ my.cnf [mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidgtid-mode = ONenforce-gtid-consistency = ONlog-slave-updates = ONserver-id=1 [root@es2 ~] # [root@es3 ~] # grep-Ev "^ $| ^ [# ] "/ etc/ my.cnf [mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidgtid-mode = ONenforce-gtid-consistency = ONlog-slave-updates = ONserver-id=2 [root@es3 ~] #

3. Install the clone plug-in and create cloned and replicated users

Mysql > INSTALL PLUGIN clone SONAME 'mysql_clone.so';mysql > CREATE USER clone_user@'%' identified by' iwSeuFagt0&31';mysql > GRANT BACKUP_ADMIN ON *. * TO 'clone_user';mysql > CREATE USER repl_user@'%' identified by' iwSeuFagt0&31';mysql > grant replication slave on *. * to repl_user;mysql > flush privileges

4. Check secure_file_priv and start the first local clone, and note whether the clone_dir1 in / var/lib/mysql-files/clone_dir1 exists.

Mysql > show variables like 'secure_file_priv' +-+-- + | Variable_name | Value | +-+-- + | secure_file_priv | / var/lib/mysql-files/ | +-- -+-+ 1 row in set (0.00 sec) mysql > CLONE LOCAL DATA DIRECTORY ='/ var/lib/mysql-files/clone_dir1'

5. Perform multiple cloning tests by creating different identities

Mysql > create database after_clone1;mysql > CLONE LOCAL DATA DIRECTORY ='/ var/lib/mysql-files/clone_dir2';mysql > create database after_clone2

6. Switch the clone data directory and check the relevant identities

Mysql > show variables like 'datadir' +-+-+ | Variable_name | Value | +-+- -+ | datadir | / var/lib/mysql-files/clone_dir1/ | +-+ 1 row in set (0.01sec) mysql > SELECT BINLOG_FILE BINLOG_POSITION FROM performance_schema.clone_status +-+-+ | BINLOG_FILE | BINLOG_POSITION | +-+-+ | binlog.000001 | 1741 | +- -- + 1 row in set (0.00 sec) mysql > SELECT @ @ GLOBAL.GTID_EXECUTED +-- + | @ @ GLOBAL.GTID_EXECUTED | +-- + | dbda28c9-c970-11e9-b268-0800275c8ec3:1-6 | +-- -- + 1 row in set (0.00 sec) mysql > exitmysql > show variables like 'datadir' +-+-+ | Variable_name | Value | +-+- -+ | datadir | / var/lib/mysql-files/clone_dir2/ | +-+ 1 row in set (0.01sec) mysql > SELECT BINLOG_FILE BINLOG_POSITION FROM performance_schema.clone_status +-+-+ | BINLOG_FILE | BINLOG_POSITION | +-+-+ | binlog.000001 | 1950 | +- -- + 1 row in set (0.00 sec) mysql > SELECT @ @ GLOBAL.GTID_EXECUTED +-- + | @ @ GLOBAL.GTID_EXECUTED | +-- + | dbda28c9-c970-11e9-b268-0800275c8ec3:1-7 | +-- -- + 1 row in set (0.00 sec) mysql > exitmysql > show variables like 'datadir' +-+-+ | Variable_name | Value | +-+-+ | datadir | / var/lib/mysql/ | +-- -+ 1 row in set (0.01sec) mysql > SELECT BINLOG_FILE BINLOG_POSITION FROM performance_schema.clone_status Empty set (0.00 sec) mysql > SELECT @ @ GLOBAL.GTID_EXECUTED +-- + | @ @ GLOBAL.GTID_EXECUTED | +-- + | dbda28c9-c970-11e9-b268-0800275c8ec3:1-8 | +-- -- + 1 row in set (0.00 sec) mysql > exit

Remote cloning

7. Remote clone data receiver environment is ready to create receiver user and authorization. CLONE_ADMIN permissions = BACKUP_ADMIN permissions + SHUTDOWN permissions. SHUTDOWN is limited to allowing users shutdown and restart mysqld. Authorization is different because the recipient needs restart mysqld, and a special note is that the local clone user is using BACKUP_ADMIN. Here we use the local clone user created above as the donor user.

Mysql > INSTALL PLUGIN clone SONAME 'mysql_clone.so';Query OK, 0 rows affected (0.09 sec) mysql > CREATE USER clone_user@'%' identified by' iwSeuFagt0&31';Query OK, 0 rows affected (0.32 sec) mysql > GRANT CLONE_ADMIN on *. * to clone_user;Query OK, 0 rows affected (0.01 sec) mysql > flush privileges

8. Try remote cloning and pay attention to the clone_valid_donor_list setting. Of course, if you do not set it, it will remind you.

Mysql > set global clone_valid_donor_list = '192.168.56.15 clone instance from clone_user@'192.168.56.15':3306 3306 iwSeuFagt0&31'; mysql > MySQL > identified by' iwSeuFagt0&31'; mysql > show databases +-+ | Database | +-+ | after_clone1 | | after_clone2 | | information_schema | | mysql | | performance_schema | | sys | +-+ mysql > SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status +-+-+ | BINLOG_FILE | BINLOG_POSITION | +-+-+ | binlog.000002 | 195 | +-- -+ 1 row in set (0.01sec) mysql > SELECT @ @ GLOBAL.GTID_EXECUTED +-- + | @ @ GLOBAL.GTID_EXECUTED | +-- + | dbda28c9-c970-11e9-b268-0800275c8ec3:1-8 | +-- -- + 1 row in set (0.00 sec)

9. Try to copy from master and slave, and pay attention to modify the password verification plug-in of the master library.

Main library

Alter USER repl_user@'%' identified WITH mysql_native_password by 'iwSeuFagt0&31'

Slave library

Mysql > CHANGE MASTER TO MASTER_HOST = '192.168.56.15', mysql > MASTER_PORT = 3306 master master autopilot policy = 1 Mastermind MySQL > START SLAVE USER = 'repl_user' PASSWORD =' iwSeuFagt0&31' Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.15 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000003 Read_Master_Log_Pos: 195 Relay_Log_File: es3-relay-bin.000004 Relay_Log_Pos: 403 Relay_Master_Log_File: binlog.000003 Slave_IO_Running: Yes Slave_SQL_Running : Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos : 195 Relay_Log_Space: 1271 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master _ UUID: dbda28c9-c970-11e9-b268-0800275c8ec3 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log Waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: dbda28c9-c970-11e9 Last_SQL_Error_Timestamp b268-0800275c8ec3:9-10 Executed_Gtid_Set: dbda28c9-c970-11e9 Murray b268- 0800275c8ec3:1-10 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) mysql > show databases +-+ | Database | +-+ | after_clone1 | | after_clone2 | | information_schema | | mysql | | performance_schema | | sys | +-+ 6 rows in set (0.00 sec) mysql > exit

10. Synchronous testing

10.1. Main library operation

[root@es2 ~] # mysql-palleiwSeuFagt0room31' mysql > show databases +-+ | Database | +-+ | after_clone1 | | after_clone2 | | information_schema | | mysql | | performance_schema | | sys | +-+ 6 rows in set (0.00 sec) mysql > Use after_clone2Database changedmysql > create table t_clone (id int not null auto_increment primary key Name varchar (255)) Query OK, 0 rows affected (0.36 sec) mysql > insert into t_clone (name) values ('c'), ('l'), ('o'), ('n'), ('e'); Query OK, 5 rows affected (0.29 sec) Records: 5 Duplicates: 0 Warnings: 0mysql > select * from after_clone2.t_clone +-+ | id | name | +-+-+ | 1 | c | 2 | l | 3 | o | 4 | n | 5 | e | +-- +-+ 5 rows in set (0.00 sec) mysql > drop database after_clone1;Query OK, 0 rows affected (0.31 sec) mysql > exit

10.2. Observe from the library

[root@es3 ~] # mysql-palleiwSeuFagt031destroy MySQL > show databases +-+ | Database | +-+ | after_clone2 | | information_schema | | mysql | | performance_schema | | sys | +-+ 5 rows in set (0.00 sec) mysql > select * from after_clone2.t_clone +-mysql > exit.

11. Prerequisite for remote cloning

To perform a remote clone operation, both the MySQL server instance of the data provider and the data receiver are active to perform the remote clone operation requires the MySQL user on the data provider and the data receiver: on the data provider, the clone user needs the privilege of BACKUP_ADMIN to access and transfer data from the donor, as well as the privilege to block DDL during the clone operation On the data recipient, the cloned user needs to have the right to replace recipient data with CLONE_ADMIN, block DDL during the clone operation, and automatically restart the server, including implicit BACKUP_ADMIN and SHUTDOWN privileges. The data provider and data receiver must have the same MySQL server version. The clone plug-in is supported in MYSQL 8.0.17 and later. The data provider and the data receiver must run on the same operating system and platform. For example, if the donor instance runs on the Linux 64-bit platform, the recipient instance must also run on that platform. The data provider and data receiver must have the same MySQL server character set and collation clone plug-in to clone only the stored data InnoDB. Do not clone other storage engine data. Tables that are MyISAM and CSV stored in any schema that includes the sys schema are cloned as empty tables. Clone plug-in does not support cloning MySQL server configuration my.cnf; clone plug-in does not support cloning binary logs; connecting to donor instances through MySQL router is not supported. If you clone encrypted or page compressed data, the donor and recipient must have the same file system block size; cloning encrypted data requires a secure connection. By default, the data recipient MySQL server instance is automatically restarted (stopped and started) after the data is cloned. To restart automatically, a monitoring process must be provided on the recipient to detect whether the server has been shut down. Otherwise, after the data is cloned, the clone operation stops with the following error and closes the data recipient MySQL server instance: ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).

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.