原 【DB宝35】使用MySQL 8.0 克隆(clone)插件快速添加MGR节点
Tags： 原创LinuxMySQLDocker主从复制MGR
👉 本文共约1800个字，系统预计阅读时间或需7分钟。
一、MySQL 8.0.17的克隆clone简介
MySQL 8.0.17的克隆插件允许在本地或从远程 MySQL 实例在线克隆数据，从此搭建从库可以不再需要备份工具（PXB或mysqldump）来实现了。克隆数据是存储在 InnoDB 其中的数据的物理快照，其中包括库、表、表空间和数据字典元数据。克隆的数据包含一个功能齐全的数据目录，允许使用克隆插件进行 MySQL 服务器配置。
克隆插件支持两种克隆方式：
- 本地克隆：本地克隆操作将启动克隆操作的 MySQL 服务器实例中的数据克隆到同服务器或同节点上的一个目录里。
- 远程克隆：默认情况下，远程克隆操作会删除接受者(recipient)数据目录中的数据，并将其替换为捐赠者(donor)的克隆数据。（可选）您也可以将数据克隆到接受者的其他目录，以避免删除现有数据。
远程克隆操作和本地克隆操作克隆的数据没有区别，数据是相同的。克隆插件支持复制。除克隆数据外，克隆操作还从捐赠者中提取并传输复制位置信息，并将其应用于接受者，从而可以使用克隆插件来配置组复制或主从复制。使用克隆插件进行配置比复制大量事务要快得多，效率更高。
MySQL 8.0 clone插件提供从一个实例克隆数据的功能，克隆功能提供了更有效的方式来快速创建MySQL实例，搭建主从复制和组复制。本文介绍使用 MySQL 8.0 clone 插件快速添加组复制（MGR）节点的方法。
官网地址：https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html
二、MGR现有环境
已有MGR集群，多主模式：
1 2 3 4 5 6 7 8 9 10 | MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 | 3306 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 | 3306 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 | 3306 | ONLINE | PRIMARY | 8.0.20 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) |
docker环境：
1 2 3 4 5 | [root@docker35 ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 9dd3d6b221b0 mysql:8.0.20 "docker-entrypoint.s…" 3 months ago Up 10 minutes 33060/tcp, 0.0.0.0:33067->3306/tcp mysql8020mgr33067 189b5a889665 mysql:8.0.20 "docker-entrypoint.s…" 3 months ago Up 10 minutes 33060/tcp, 0.0.0.0:33066->3306/tcp mysql8020mgr33066 6ce08dd5dc84 mysql:8.0.20 "docker-entrypoint.s…" 3 months ago Up 10 minutes 33060/tcp, 0.0.0.0:33065->3306/tcp mysql8020mgr33065 |
通过clone插件新加节点： 172.72.0.18。
三、使用clone技术添加MGR节点
3.1 初始化新节点
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | mkdir -p /usr/local/mysql/lhrmgr18/conf.d mkdir -p /usr/local/mysql/lhrmgr18/data docker run -d --name mysql8020mgr33068 \ -h lhrmgr18 -p 33068:3306 --net=mysql-network --ip 172.72.0.18 \ -v /usr/local/mysql/lhrmgr18/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr18/data:/var/lib/mysql/ \ -e MYSQL_ROOT_PASSWORD=lhr \ -e TZ=Asia/Shanghai \ mysql:8.0.20 cat > /usr/local/mysql/lhrmgr18/conf.d/my.cnf <<"EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv='' server-id = 802033068 log-bin = binlog_format=row binlog_checksum=NONE log-slave-updates=1 skip-name-resolve auto-increment-increment=2 auto-increment-offset=1 gtid-mode=ON enforce-gtid-consistency=on default_authentication_plugin=mysql_native_password max_allowed_packet = 500M log_slave_updates=on master_info_repository=TABLE relay_log_info_repository=TABLE relay_log=lhrmgr18-relay-bin-ip18 transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=OFF loose-group_replication_local_address= "172.72.0.18:33064" loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063,172.72.0.18:33064" loose-group_replication_bootstrap_group=OFF loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17,172.72.0.18" report_host=172.72.0.18 report_port=3306 EOF docker restart mysql8020mgr33068 docker ps mysql -uroot -plhr -h192.168.1.35 -P33065 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.1.35 -P33066 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.1.35 -P33067 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.1.35 -P33068 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.1.35 -P33065 mysql -uroot -plhr -h192.168.1.35 -P33066 mysql -uroot -plhr -h192.168.1.35 -P33067 mysql -uroot -plhr -h192.168.1.35 -P33068 docker logs -f --tail 10 mysql8020mgr33065 docker logs -f --tail 10 mysql8020mgr33066 docker logs -f --tail 10 mysql8020mgr33067 docker logs -f --tail 10 mysql8020mgr33068 |
3.2 新节点安装clone插件和组复制插件
1 2 3 4 5 6 7 | mysql -uroot -plhr -h192.168.1.35 -P33068 -- 安装MGR插件 INSTALL PLUGIN group_replication SONAME 'group_replication.so'; -- 安装clone插件(注意，需要在源MGR3个节点都安装) INSTALL PLUGIN clone SONAME 'mysql_clone.so'; |
3.3 新节点执行克隆任务
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | -- 设置克隆源，将clone_valid_donor_list设置为MGR节点 SET GLOBAL clone_valid_donor_list = '172.72.0.15:3306'; -- 开始克隆 CLONE INSTANCE FROM 'root'@'172.72.0.15':3306 IDENTIFIED BY 'lhr'; -- 由于是docker环境，所以需要重启容器 -- ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process). docker restart mysql8020mgr33068 -- 查看克隆进度和状态 MySQL [(none)]> SELECT * FROM performance_schema.clone_status \G *************************** 1. row *************************** ID: 1 PID: 0 STATE: Completed BEGIN_TIME: 2020-11-13 15:03:07.076 END_TIME: 2020-11-13 15:04:31.224 SOURCE: 172.72.0.15:3306 DESTINATION: LOCAL INSTANCE ERROR_NO: 0 ERROR_MESSAGE: BINLOG_FILE: lhrmgr15-bin.000005 BINLOG_POSITION: 1235 GTID_EXECUTED: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-25 1 row in set (0.01 sec) MySQL [(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-11-13 15:03:07 | 2020-11-13 15:03:08 | 320.98 ms | 0MB | 100% | | FILE COPY | Completed | 2020-11-13 15:03:08 | 2020-11-13 15:03:10 | 2.12 s | 64MB | 100% | | PAGE COPY | Completed | 2020-11-13 15:03:10 | 2020-11-13 15:03:10 | 160.27 ms | 0MB | 100% | | REDO COPY | Completed | 2020-11-13 15:03:10 | 2020-11-13 15:03:10 | 100.76 ms | 0MB | 100% | | FILE SYNC | Completed | 2020-11-13 15:03:10 | 2020-11-13 15:03:13 | 2.83 s | 0MB | 100% | | RESTART | Completed | 2020-11-13 15:03:13 | 2020-11-13 15:04:30 | 1.29 m | 0MB | 100% | | RECOVERY | Completed | 2020-11-13 15:04:30 | 2020-11-13 15:04:31 | 1.19 s | 0MB | 100% | +-----------+-----------+---------------------+---------------------+------------+------------------+---------+ 7 rows in set (0.01 sec) |
3.4 在原3节点执行修改参数
1 2 3 4 | set global group_replication_group_seeds='172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063,172.72.0.18:33064'; stop group_replication; set global group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17,172.72.0.18"; start group_replication; |
3.5 新节点启动MGR
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | -- 由于已有MGR集群是多主模式，需要先设置成多主模式 set global group_replication_single_primary_mode=OFF; set global group_replication_enforce_update_everywhere_checks=ON; -- 加入组复制 START GROUP_REPLICATION; -- 查看组复制成员及状态 SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 276804ba-257c-11eb-b8ea-0242ac480012 | 172.72.0.18 | 3306 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 | 3306 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 | 3306 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 | 3306 | ONLINE | PRIMARY | 8.0.20 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 4 rows in set (0.00 sec) -- 新节点查询数据库 MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | lhrdb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
