聊聊 MySQL 8.0 的克隆插件

原创大表哥 2022-04-18

1774

大家好，这次大表哥分享的是 MySQL 8.0 的克隆插件。

小伙伴们在平时的工作中可能会时常遇到如下的工作：

1.主从复制的搭建，快速大家一个slave 节点，无论是传统的主从架构的slave ，还是组复制MGR的 secondary 节点
2.快速的给开发或者测试的同事，复制一个可读写的和生产一样的数据库镜像，来复现生产的问题

DBA 的角度来说，一般根据数据库的大小来采取不一样的数据库搭建方式：

1）一般是小于 20个G（个人经验的判断，非业界标准），可以采用 mysqldump的方式来导出SQL的文本
（这种当时一般是比较慢的，SQL的导出和导入都是语句级别的）
2）一般数据库很大的话，采用一般采用xtrabackup的进行进行一个全备，然后再恢复目标的数据库

那有没有更简单的方式呢，类似于一条命令就能搞定呢？ mysql 8.0.17版本的推出了 clone 的插件。

官方文档上是说：可供2种克隆的方式：本地和远程克隆。

本地克隆：

利用clone 搭建master的slave 节点：

我们现在来试验一下，如何给master节点克隆一个slave 节点：这里我们的版本是 8.0.20-11

1）我们需要在master节点上安装克隆插件：

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.20-11 |
+-----------+
1 row in set (0.00 sec)


mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'clone';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| clone       | ACTIVE        |
+-------------+---------------+
1 row in set (0.00 sec)

2）创建克隆的账户：该账户需要权限 BACKUP_ADMIN

mysql> CREATE USER clone_user@'%' IDENTIFIED by 'admin123';
Query OK, 0 rows affected (0.01 sec)


mysql> GRANT BACKUP_ADMIN ON *.* TO 'clone_user'@'%';
Query OK, 0 rows affected (0.01 sec)


mysql> show grants for 'clone_user'@'%';
+-----------------------------------------------+
| Grants for clone_user@%                       |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `clone_user`@`%`        |
| GRANT BACKUP_ADMIN ON *.* TO `clone_user`@`%` |
+-----------------------------------------------+
2 rows in set (0.00 sec)

3)运行克隆的命令，我们把源数据库克隆到 /data/clone/backup

这里注意数据目录必须不存在：否则会报错
mysql> CLONE LOCAL DATA DIRECTORY = ‘/data/clone/backup’;
ERROR 1007 (HY000): Can’t create database ‘/data/clone/backup’; database exists

mysql> CLONE LOCAL DATA DIRECTORY = '/data/clone/backup';
Query OK, 0 rows affected (6.64 sec)

4)运行clone 命令的后台进程，可以通过performance_schema.clone_progress 监控

mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
+-----------+-------------+----------------------------+
| STAGE     | STATE       | END_TIME                   |
+-----------+-------------+----------------------------+
| DROP DATA | Completed   | 2022-04-16 16:03:07.071601 |
| FILE COPY | Completed   | 2022-04-16 16:03:07.711494 |
| PAGE COPY | Completed   | 2022-04-16 16:03:07.820410 |
| REDO COPY | Completed   | 2022-04-16 16:03:07.822254 |
| FILE SYNC | Completed   | 2022-04-16 16:03:13.706686 |
| RESTART   | Not Started | NULL                       |
| RECOVERY  | Not Started | NULL                       |
+-----------+-------------+----------------------------+
7 rows in set (0.00 sec)

我们可以到看clone的大致的步骤是：DROP DATA -》 FILE COPY=》PAGE COPY=》REDO COPY =》 FILE SYNC

RESTART 和 RECOVERY 需要我们自己指定一下 mysql 的conf 文件，手动起来mysqld 的进程。

这里需要注意，参数文件 my.cnf 是不会克隆的，需要从源端手动拷贝一个copy.

5）源端拷贝参数文件 , 进行必要的参数改动，保证正常的启动。

主要是 port, socket file和文件路径相关的参数需要改动

如果是作为slave 的话，还需要设置为只读： read_only=ON

server_id=13316
port=3316
audit_log_file=/data/clone/audit/audit.log
socket=/tmp/mysql_3316.sock
slow_query_log_file=/data/clone/log/slowLog_3316.log
log_bin=/data/clone/binlogs/3316_binlog
relay_log=/data/clone/binlogs/replay_3316
log-error=/data/clone/log/error_3316.log
pid-file=/data/clone/data/3316_db.pid

当然还需要创建一些其他的文件目录：tmp,log,audit,binlogs

[jason@VM-24-9-centos clone]$ mkdir -p /data/clone/{tmp,log,audit,binlogs}

6）万事俱备，就差我们手动启动数据库了

[jason@VM-24-9-centos log]$ nohup /opt/mysql/8/bin/mysqld_safe --defaults-file=/data/clone/my3316.cnf --user=jason > /dev/null  2>&1 &
[1] 21997

[jason@VM-24-9-centos log]$ netstat -all | grep 3316
unix  2      [ ACC ]     STREAM     LISTENING     152693851 /tmp/mysql_3316.sock

这个时候再次观察 clone 的process 的监控表：发现 RESTART和RECOVERY 也已经完成

mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
+-----------+-----------+----------------------------+
| STAGE     | STATE     | END_TIME                   |
+-----------+-----------+----------------------------+
| DROP DATA | Completed | 2022-04-16 16:03:07.071601 |
| FILE COPY | Completed | 2022-04-16 16:03:07.711494 |
| PAGE COPY | Completed | 2022-04-16 16:03:07.820410 |
| REDO COPY | Completed | 2022-04-16 16:03:07.822254 |
| FILE SYNC | Completed | 2022-04-16 16:03:13.706686 |
| RESTART   | Completed | 2022-04-16 16:40:09.595637 |
| RECOVERY  | Completed | 2022-04-16 16:40:10.480645 |
+-----------+-----------+----------------------------+
7 rows in set (0.01 sec)

7）数据库启动后，我们尝试一下登录从库

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3316 |
+--------+
1 row in set (0.00 sec)


mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|       13316 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

8)主库中创建复制账户

mysql> create user repl@'%' identified with mysql_native_password by 'admin123';
Query OK, 0 rows affected (0.02 sec)


mysql> grant replication slave on *.* to repl@'%';
Query OK, 0 rows affected (0.01 sec)

9)从库中创建复制链路这里我们采用的是GTID的模式

mysql> change master to master_host='82.156.200.136',master_user='repl',master_password='admin123', master_port=3306,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.07 sec)


mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

10）查看slave节点的复制状态

mysql> show slave status\G
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: 82.156.200.136
                 Master_User: repl
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: 3306_binlog.000002
         Read_Master_Log_Pos: 2536
              Relay_Log_File: replay_3316.000002
               Relay_Log_Pos: 1581
       Relay_Master_Log_File: 3306_binlog.000002
            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: 2536
             Relay_Log_Space: 1778
             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: 0
Master_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: 13306
                 Master_UUID: 9915aff5-bbd6-11ec-9110-525400743c33
            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: 9915aff5-bbd6-11ec-9110-525400743c33:8-12
           Executed_Gtid_Set: 9915aff5-bbd6-11ec-9110-525400743c33:1-12,
d7251678-bd60-11ec-bed2-525400743c33:1-3
               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)

至此目前我们，实现了利用克隆搭建本地slave节点。

利用clone 搭建MGR：

为刚才的master 节点，添加2个secondary节点，组成一个3节点的MGR的组的复制集。

实验环境：这里我们采用的伪分布式的方式（通过指定不通的端口）

1）手动创建2个节点的基础路径

[jason@VM-24-9-centos ~]$ sudo mkdir -p /data/{mysql_mgr3307,mysql_mgr3308}
[jason@VM-24-9-centos ~]$ sudo chown -R jason:jason  /data/{mysql_mgr3307,mysql_mgr3308}
[jason@VM-24-9-centos ~]$ mkdir -p /data/{mysql_mgr3307,mysql_mgr3308}/{log,audit,binlogs,tmp}

2）克隆2个节点的数据路径

MGR 端口3307的克隆路径为： /data/mysql_mgr3307/data
MGR 端口3308的克隆路径为： /data/mysql_mgr3308/data

运行克隆命令：这里需要注意克隆命令不允许多个同时进行。需要串行执行。

CLONE LOCAL DATA DIRECTORY = '/data/mysql_mgr3307/data';
CLONE LOCAL DATA DIRECTORY = '/data/mysql_mgr3308/data';

3）编辑参数文件修改必要的选项，添加MGR相关的参数

loose-group_replication_group_name=b965e3bb-be27-11ec-ba80-525400743c33     
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= 82.156.200.136:33061
loose-group_replication_group_seeds= 82.156.200.136:33061,82.156.200.136:33071,82.156.200.136:33081
loose-group_replication_bootstrap_group=off
loose-group_replication_recovery_use_ssl=1
loose-group_replication_consistency=BEFORE_ON_PRIMARY_FAILOVER
loose-group_replication_exit_state_action=READ_ONLY
loose-group_replication_member_expel_timeout=10
loose-group_replication_gtid_assignment_block_size=10000
loose-group_replication_recovery_get_public_key=ON
loose-group_replication_start_on_boot=ON
loose-group_replication_autorejoin_tries=3

4）需要停掉主库，添加相关的参数在cnf 文件,并安装group_replication.so 插件

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

[jason@VM-24-9-centos mysql3306]$ nohup  /opt/mysql/8/bin/mysqld_safe --defaults-file=/data/mysql3306/my3306.cnf  > /dev/null  --user=jason 2>&1 &
[1] 29684



mysql> select * from information_schema.plugins where PLUGIN_NAME like '%replication%'\G
*************************** 1. row ***************************
          PLUGIN_NAME: group_replication
       PLUGIN_VERSION: 1.1
        PLUGIN_STATUS: ACTIVE
          PLUGIN_TYPE: GROUP REPLICATION
  PLUGIN_TYPE_VERSION: 1.4
       PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.10
        PLUGIN_AUTHOR: Oracle Corporation
   PLUGIN_DESCRIPTION: Group Replication (1.1.0)
       PLUGIN_LICENSE: GPL
          LOAD_OPTION: ON
1 row in set (0.00 sec)

5）逐一启动2个新建的从库并安装插件并创建复制组复制链路

[jason@VM-24-9-centos mysql_mgr3307]$ nohup /opt/mysql/8/bin/mysqld_safe --defaults-file=/data/mysql_mgr3307/my3307.cnf > /dev/null --user=jason  2>&1 &
[2] 869

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)


mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (1.01 sec)

mysql> change master to master_user='repl',master_password='admin123' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.05 sec)

同样的步骤，在另一个节点执行：

[jason@VM-24-9-centos ~]$ nohup /opt/mysql/8/bin/mysqld_safe --defaults-file=/data/mysql_mgr3308/my3308.cnf > /dev/null --user=jason  2>&1 &
[3] 3640


mysql> select @@port;
+--------+
| @@port |
+--------+
|   3308 |
+--------+
1 row in set (0.00 sec)


mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (1.03 sec)


mysql> change master to master_user='repl',master_password='admin123' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

6）主库启动MGR的bootstrap 进程

mysql> set global group_replication_bootstrap_group = on;
Query OK, 0 rows affected (0.00 sec)


mysql> start group_replication;
Query OK, 0 rows affected (8.31 sec)


mysql> set global group_replication_bootstrap_group =off;
Query OK, 0 rows affected (0.00 sec)

7）从库2节点启动MGR

mysql> start group_replication;
Query OK, 0 rows affected (3.47 sec)

mysql> start group_replication;
Query OK, 0 rows affected (3.63 sec)

8）检查MGR组复制的状态

mysql> select * from replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 519dc1a6-be2b-11ec-9f85-525400743c33 | 82.156.200.136 |        3307 | ONLINE       | SECONDARY   | 8.0.20         |
| group_replication_applier | 9915aff5-bbd6-11ec-9110-525400743c33 | 82.156.200.136 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 9aa1cbcd-be2c-11ec-90a8-525400743c33 | 82.156.200.136 |        3308 | ONLINE       | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

远程克隆：

我们再来试验一下快速刷新数据库：

目标端需要刷新的实例叫做： Recipient
源端提供数据源的实例叫做： Donor

我们需要执行 clone instance 的命令行在 Recipient的实例上

实验环境：

现在我们要刷新一下测试UAT节点 3316中的数据 (Recipient), 数据源的 Donor 是 3306 这个端口的实例：

1）我们要在doner 的节点创建克隆的账户 clone_user ,并且安装克隆插件

mysql> CREATE USER clone_user@'%' IDENTIFIED by 'admin123';
Query OK, 0 rows affected (0.01 sec)


mysql> GRANT BACKUP_ADMIN ON *.* TO 'clone_user'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> INSTALL PLUGIN CLONE SONAME "mysql_clone.so";

2)接下来，我们再Recipient节点上，安装克隆插件，并且设置一下 clone_valid_donor_list 参数

mysql> INSTALL PLUGIN CLONE SONAME "mysql_clone.so";

mysql> SET GLOBAL clone_valid_donor_list = "82.156.200.136:3316";
Query OK, 0 rows affected (0.00 sec)

3）执行远程克隆命令之前，我们先在donor 实例制造一些数据

mysql> create database clonedb;
Query OK, 1 row affected (0.02 sec)


mysql> use clonedb;
Database changed

mysql> create table clone_replication (id int not null primary key , name varchar(20));
Query OK, 0 rows affected (0.12 sec)



mysql> insert into clone_replication values (1,'am clone');
Query OK, 1 row affected (0.02 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)

4）我们在recipient 上执行一下远程克隆的命令

mysql> CLONE INSTANCE FROM clone_user@82.156.200.136:3316 IDENTIFIED BY "admin123";
Query OK, 0 rows affected (1 min 6.55 sec)

5）我们可以观察clone的process 进度：

mysql> 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   |
+-------------+---------------------+------------+
| In Progress | 2022-04-18 07:52:40 |     1.08 m |
+-------------+---------------------+------------+
1 row in set (0.00 sec)


+-----------+---------------------+------------+
| STATE     | START TIME          | DURATION   |
+-----------+---------------------+------------+
| Completed | 2022-04-18 07:52:40 |     1.20 m |
+-----------+---------------------+------------+
1 row in set (0.02 sec)

具体的每一步是耗时统计：

mysql> select STAGE, STATE, CAST(BEGIN_TIME AS TIME) as "START TIME",
   ->   CAST(END_TIME AS TIME) as "FINISH TIME",
   ->   LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ')
   ->   as DURATION
   ->   from performance_schema.clone_progress;
+-----------+-----------+------------+-------------+------------+
| STAGE     | STATE     | START TIME | FINISH TIME | DURATION   |
+-----------+-----------+------------+-------------+------------+
| DROP DATA | Completed | 07:52:40   | 07:52:40    |  291.48 ms |
| FILE COPY | Completed | 07:52:40   | 07:53:40    |    59.89 s |
| PAGE COPY | Completed | 07:53:40   | 07:53:41    |  345.40 ms |
| REDO COPY | Completed | 07:53:41   | 07:53:41    |  300.73 ms |
| FILE SYNC | Completed | 07:53:41   | 07:53:47    |     5.54 s |
| RESTART   | Completed | 07:53:47   | 07:53:51    |     4.67 s |
| RECOVERY  | Completed | 07:53:51   | 07:53:52    |  891.68 ms |
+-----------+-----------+------------+-------------+------------+
7 rows in set (0.00 sec)

6）clone 完成后 recipient 的节点会自动重启：

mysql> show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 265   |
+---------------+-------+
1 row in set (0.06 sec)

7）最后我来验证一下数据在recipient 的实例节点：

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3326 |
+--------+
1 row in set (0.00 sec)


mysql> select * from clonedb.clone_replication;
+----+----------+
| id | name     |
+----+----------+
|  1 | am clone |
+----+----------+
1 row in set (0.00 sec)

当然了克隆的操作也存在一定的限制：
1)克隆过程中， DDL 在 donor 和 recipient 实例上是不允许的，在mysql >= 8.0.27 才开始支持，可以通过参数 clone-block-ddl 控制开关
2) donor 和 donor 的实例版本要精确到小版本的一致
3）每次只能克隆一个实例，不允许同时运行多个克隆的命令
4）binlogs 不会克隆复制
5）克隆目前支持innodb 的存储引擎。myISAM和CSV的存储引擎在sys数据库下，会克隆成空表。
6）不支持通过mysql router 克隆

具体可以参考：https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-limitations.html

最后我们来总结一下：

clone 插件是基于mysql 版本 >=8.0.17之后的
支持本地和远程2种方式的克隆
对于大数据量的数据库的从库搭建和或者是做数据库的镜像，是一个很好的选择。

最后修改时间：2022-04-19 14:19:53

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者