使用clone plugin快速创建MySQL副本

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5823755.html

MySQL 8.0.17引入了CLONE SQL语句，即克隆插件(clone plugin)，允许克隆数据本地保存，或者复制到远程MySQL服务器实例进行克隆数据。克隆数据是一个储存在innodb引擎上数据的物理快照，这些数据包括schema、表、表空间、数据字典元数据。其中复制源实例称为Donor，接受目标实例称为Recipient。


step1：配置捐赠节点(Donor)
安装支持新特性的插件
(root@localhost:)[(none)]> INSTALL PLUGIN CLONE SONAME "mysql_clone.so"; 
Query OK, 0 rows affected (0.02 sec)
创建用户并授予相关权限
(root@localhost:)[(none)]> 
(root@localhost:)[(none)]> CREATE USER dbayang IDENTIFIED BY "dbayang";
Query OK, 0 rows affected (0.02 sec)
(root@localhost:)[(none)]> 
(root@localhost:)[(none)]> GRANT CLONE_ADMIN ON *.* to dbayang;
Query OK, 0 rows affected (0.01 sec)
(root@localhost:)[(none)]> 
(root@localhost:)[(none)]> GRANT BACKUP_ADMIN ON *.* to dbayang;
Query OK, 0 rows affected (0.01 sec)

(root@localhost:)[(none)]> GRANT SELECT ON performance_schema.* TO dbayang;
Query OK, 0 rows affected (0.01 sec)
(root@localhost:)[(none)]> GRANT EXECUTE ON *.* to dbayang;
Query OK, 0 rows affected (0.00 sec)

step2：配置接收节点(Recipient)
安装支持特性的插件
(root@localhost:)[(none)]> INSTALL PLUGIN CLONE SONAME "mysql_clone.so"; 
Query OK, 0 rows affected (0.01 sec)
(root@localhost:)[(none)]> CREATE USER dbayang IDENTIFIED BY "dbayang";
Query OK, 0 rows affected (0.03 sec)
(root@localhost:)[(none)]> GRANT BACKUP_ADMIN ON *.* to dbayang;
Query OK, 0 rows affected (0.01 sec)
(root@localhost:)[(none)]> 
(root@localhost:)[(none)]> GRANT SELECT ON performance_schema.* TO dbayang;
Query OK, 0 rows affected (0.00 sec)
(root@localhost:)[(none)]> 
(root@localhost:)[(none)]> GRANT EXECUTE ON *.* to dbayang;
Query OK, 0 rows affected (0.01 sec)
配置donor节点地址列表：

(root@localhost:)[(none)]> SET GLOBAL clone_valid_donor_list = "127.0.0.1:3306";
Query OK, 0 rows affected (0.01 sec)

(root@localhost:)[(none)]> show variables like 'clone_valid_donor_list%';
+------------------------+----------------+
| Variable_name          | Value          |
+------------------------+----------------+
| clone_valid_donor_list | 127.0.0.1:3306 |
+------------------------+----------------+
1 row in set (0.01 sec)

step3创建数据克隆副本
使用clone 语句进行复制donor节点实例:
语法：
CLONE INSTANCE FROM USER@HOST:PORT
IDENTIFIED BY 'password'
[DATA DIRECTORY [=] 'clone_dir']
[REQUIRE [NO] SSL];

(root@localhost:)[(none)]> CLONE INSTANCE
    ->      FROM dbayang@127.0.0.1:3306
    ->      IDENTIFIED BY "dbayang";
Query OK, 0 rows affected (26.93 sec)

clone操作完成数据库自动重启。
使用以下语句检查clone进度等信息。
(root@localhost:)[(none)]> select STATE, CAST(BEGIN_TIME AS DATETIME) as "START TIME",
    -> CASE WHEN END_TIME IS NULL THEN
    -> LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ')
    -> ELSE
    ->   LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ')
    -> END as DURATION
    -> from performance_schema.clone_status;
+-----------+---------------------+------------+
| STATE     | START TIME     | DURATION   |
+-----------+---------------------+------------+
| Completed | 2019-07-25 14:15:29 |  32.01 s |
+-----------+---------------------+------------+
1 row in set (0.02 sec)


(root@localhost:)[(none)]> select STAGE, STATE, CAST(BEGIN_TIME AS TIME) as "START TIME",
    ->   CASE WHEN END_TIME IS NULL THEN
    ->   LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ')
    ->   ELSE
    ->   LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ')
    ->   END 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(CAST(ROUND(DATA*100/ESTIMATE, 0) AS BINARY), "%"), 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 | DURATION   | Estima |    Done(%) |
+-----------+-----------+------------+------------+------------------+---------+
| DROP DATA | Completed | 14:15:29   |   340.77 ms |      0 MB |  100% |
| FILE COPY | Completed | 14:15:29   |    20.89 s |  2,623 MB |  100% |
| PAGE COPY | Completed | 14:15:50   |  401.78 ms |      0 MB |  100% |
| REDO COPY | Completed | 14:15:50   |  300.52 ms |      0 MB |  100% |
| FILE SYNC | Completed | 14:15:51   |    4.99 s |      0 MB |   100% |
| RESTART  | Completed | 14:15:56   |    4.05 s |       0 MB |   100% |
| RECOVERY | Completed | 14:16:00    |     1.03 s |      0 MB |   100% |
+-----------+-----------+------------+------------+------------------+---------+
7 rows in set (0.00 sec)
使用下面SQL语句查看任务总体信息。
(root@localhost:)[(none)]> select STATE, ERROR_NO, BINLOG_FILE, BINLOG_POSITION, GTID_EXECUTED,
    -> CAST(BEGIN_TIME AS DATETIME) as "START TIME",
    -> CAST(END_TIME AS DATETIME) as "FINISH TIME",
    -> sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME)))
    -> as DURATION
    -> from performance_schema.clone_status \G
*************************** 1. row ***************************
          STATE: Completed
       ERROR_NO: 0
    BINLOG_FILE: mysql-bin.000020
BINLOG_POSITION: 4119280
  GTID_EXECUTED: 
     START TIME: 2019-07-25 14:15:29
    FINISH TIME: 2019-07-25 14:16:01
       DURATION: 32.01 s
1 row in set (0.00 sec)


--The end