MySQL8.0新特性clone plugin

本文主要是介绍MySQL8.0新特性clone plugin，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

长话短说，这个东西可以快速克隆MySQL实例，相关人士测试，效率远胜xtrabackup，另外已经由公司开始用该功能做数据库备份

数据库版本

(root@localhost) [(none)]> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)
测试实例都是新初始化的干净实例

Ⅰ、clone插件安装

在线安装插件

(root@localhost) [(none)]> install plugin clone soname 'mysql_clone.so';
Query OK, 0 rows affected (0.00 sec)

查看插件状态

(root@localhost) [(none)]> select plugin_name, plugin_status from information_schema.plugins where plugin_name = 'clone';
+-------------+---------------+
| plugin_name | plugin_status |
+-------------+---------------+
| clone       | ACTIVE        |
+-------------+---------------+
1 row in set (0.00 sec)

Ⅱ、本地克隆

创建克隆账号

该账号需要backup_admin权限

(root@localhost) [(none)]> create user clone_user identified by '123456';
Query OK, 0 rows affected (0.01 sec)

(root@localhost) [(none)]> grant backup_admin on *.* to clone_user;
Query OK, 0 rows affected (0.01 sec)

创建clone目录

clone目录属主属组设置为启动mysql服务的用户

[root@master data]# ll
total 4
drwxr-xr-x 6 mysql mysql 4096 Apr 20 16:17 mysql
[root@master data]# pwd
/data
[root@master data]# mkdir clone_dir
[root@master data]# chown -R mysql:mysql clone_dir

创建测试数据

(root@localhost) [(none)]> create database t;
Query OK, 1 row affected (0.01 sec)

(root@localhost) [(none)]> create table t.t(id int);
Query OK, 0 rows affected (0.03 sec)

(root@localhost) [(none)]> insert into t.t values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@localhost) [(none)]> select * from t.t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

用clone账号登陆做本地克隆

(clone_user@localhost) [(none)]> clone local data directory = '/data/clone_dir/mysql';
Query OK, 0 rows affected (0.24 sec)

启动并检查clone数据目录

[root@master data]# /etc/init.d/mysql.server stop
Shutting down MySQL..                                      [  OK  ]
[root@master data]# mv mysql mysql_bak
[root@master data]# mv clone_dir/mysql .
[root@master data]# ll mysql
total 155672
drwxr-x--- 2 mysql mysql     4096 Apr 20 17:00 #clone
-rw-r----- 1 mysql mysql     5360 Apr 20 17:00 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Apr 20 17:00 ibdata1
-rw-r----- 1 mysql mysql 50331648 Apr 20 17:00 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Apr 20 17:00 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Apr 20 17:00 mysql
-rw-r----- 1 mysql mysql 25165824 Apr 20 17:00 mysql.ibd
drwxr-x--- 2 mysql mysql     4096 Apr 20 17:00 sys
drwxr-x--- 2 mysql mysql     4096 Apr 20 17:00 t
-rw-r----- 1 mysql mysql 10485760 Apr 20 17:00 undo_001
-rw-r----- 1 mysql mysql 10485760 Apr 20 17:00 undo_002
[root@master data]# /etc/init.d/mysql.server start
Starting MySQL.Logging to '/data/mysql/error.log'.
.                                                          [  OK  ]
[root@master data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@localhost) [(none)]> select * from t.t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

Ⅲ、远程克隆

远程克隆中，源叫做捐赠者，目标叫做接收者

这里用上面的实例作为捐赠者，下面在接收者上操作

接收者建议保持为空，如果不为空会被清空

在线安装插件

(root@localhost) [(none)]> install plugin clone soname 'mysql_clone.so';
Query OK, 0 rows affected (0.02 sec)

创建克隆账号

这里的克隆账号需要clone_admin权限，这个权限比捐赠者上的克隆账号多了shutdown权限，克隆完后需要重启数据库，所以非mysqld_safe启动则会报错，但不影响克隆，手动重启数据库即可。

(root@localhost) [(none)]> create user clone_user identified by '123456';
Query OK, 0 rows affected (0.01 sec)

(root@localhost) [(none)]> grant clone_admin on *.* to clone_user;
Query OK, 0 rows affected (0.01 sec)

设置捐赠者列表清单

(root@localhost) [(none)]> set global clone_valid_donor_list = '192.168.0.65:3306';
Query OK, 0 rows affected (0.00 sec)

用clone账号登陆做远程克隆

(clone_user@localhost) [(none)]> clone instance from clone_user@'192.168.0.65':3306 identified by '123456';
Query OK, 0 rows affected (1.03 sec)
(clone_user@localhost) [(none)]> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***
说明已经重启了

退出重连查看数据

我这里mysqld_safe自动重启了数据库，不需要手动启动数据库

(root@localhost) [(none)]> select * from t.t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

Ⅳ、捐赠者和接收者创建复制关系

捐赠者上创建复制账号

(root@localhost) [(none)]> create user rpl identified with 'mysql_native_password' by '123';
Query OK, 0 rows affected (0.01 sec)

(root@localhost) [(none)]> grant replication slave on *.* to rpl;
Query OK, 0 rows affected (0.00 sec)

接收者设置主从关系

(root@localhost) [(none)]> change master to master_host = '192.168.0.65',
    -> master_user = 'rpl',
    -> master_password = '123',
    -> master_port = 3306,
    -> master_auto_position = 1;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

启动并检查复制关系

(root@localhost) [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

(root@localhost) [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.65
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 195
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 363
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ...
            

Ⅴ、其他相关小操作

查看克隆过程状态及错误

(root@localhost) [(none)]> 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)

查看克隆步骤

(root@localhost) [(none)]> select 
    -> stage,
    -> state,
    -> cast(begin_time as DATETIME) as "START TIME",
    -> cast(end_time as DATETIME) as "FINISH TIME",
    -> lpad(sys.format_time(power(10,12) * (unix_timestamp(end_time) - unix_timestamp(begin_time))), 10, ' ') as DURATION,
    -> lpad(concat(format(round(estimate/1024/1024,0), 0), "MB"), 16, ' ') as "Estimate",
    -> case when begin_time is NULL then LPAD('%0', 7, ' ')
    -> when estimate > 0 then
    -> lpad(concat(round(data*100/estimate, 0), "%"), 7, ' ')
    -> when end_time is NULL then lpad('0%', 7, ' ')
    -> else lpad('100%', 7, ' ')
    -> end as "Done(%)"
    -> from performance_schema.clone_progress;
+-----------+-------------+---------------------+---------------------+------------+------------------+---------+
| stage     | state       | START TIME          | FINISH TIME         | DURATION   | Estimate         | Done(%) |
+-----------+-------------+---------------------+---------------------+------------+------------------+---------+
| DROP DATA | Completed   | 2020-04-21 18:22:44 | 2020-04-21 18:22:44 |  303.87 ms |              0MB |    100% |
| FILE COPY | In Progress | 2020-04-21 18:22:44 | NULL                | NULL       |          3,600MB |     96% |
| PAGE COPY | Not Started | NULL                | NULL                | NULL       |              0MB |      %0 |
| REDO COPY | Not Started | NULL                | NULL                | NULL       |              0MB |      %0 |
| FILE SYNC | Not Started | NULL                | NULL                | NULL       |              0MB |      %0 |
| RESTART   | Not Started | NULL                | NULL                | NULL       |              0MB |      %0 |
| RECOVERY  | Not Started | NULL                | NULL                | NULL       |              0MB |      %0 |
+-----------+-------------+---------------------+---------------------+------------+------------------+---------+
7 rows in set (0.01 sec)

+-----------+-----------+---------------------+---------------------+------------+------------------+---------+
| stage     | state     | START TIME          | FINISH TIME         | DURATION   | Estimate         | Done(%) |
+-----------+-----------+---------------------+---------------------+------------+------------------+---------+
| DROP DATA | Completed | 2020-04-21 18:22:44 | 2020-04-21 18:22:44 |  303.87 ms |              0MB |    100% |
| FILE COPY | Completed | 2020-04-21 18:22:44 | 2020-04-21 18:23:06 |    22.28 s |          3,600MB |    100% |
| PAGE COPY | Completed | 2020-04-21 18:23:06 | 2020-04-21 18:23:08 |     2.01 s |              0MB |    100% |
| REDO COPY | Completed | 2020-04-21 18:23:08 | 2020-04-21 18:23:09 |  300.20 ms |              0MB |    100% |
| FILE SYNC | Completed | 2020-04-21 18:23:09 | 2020-04-21 18:23:09 |  327.89 ms |              0MB |    100% |
| RESTART   | Completed | 2020-04-21 18:23:09 | 2020-04-21 18:23:13 |     3.90 s |              0MB |    100% |
| RECOVERY  | Completed | 2020-04-21 18:23:13 | 2020-04-21 18:23:13 |  482.81 ms |              0MB |    100% |
+-----------+-----------+---------------------+---------------------+------------+------------------+---------+
7 rows in set (0.01 sec)

查看克隆次数

只能看本地克隆次数，该命令记录clone命令执行次数
远程克隆，在接收者上执行，clone成功后服务重启，这个值会被置零

(root@localhost) [(none)]> show global status like 'Com_clone';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_clone     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

停止克隆

直接kill克隆线程即可

这篇关于MySQL8.0新特性clone plugin的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！