使用Clone技术搭建MySQL8.0主从同步

环境描述：

Mater: 192.168.100.202 port:3307 doner 捐赠者

Slave: 192.168.100.203 port:3307 recipient 接受者

第一步：两台机器MySQL分别进行初始化安装

详细步骤见【Linux系统同时安装MySQL5.7和MySQL8.0】

第二步：安装同步插件

2.1doner节点192.168.100.202相关操作

–创建用户

create user ‘donor_user’@‘192.168.100.203’ identified by ‘password’;
Query OK, 0 rows affected (0.00 sec)

grant backup_admin on . to ‘donor_user’@‘192.168.100.203’;
Query OK, 0 rows affected (0.01 sec)

–安装clone插件

install plugin clone soname ‘mysql_clone.so’;
Query OK, 0 rows affected (0.01 sec)

2.2recipient节点192.168.100.203相关操作

–创建用户（也可以不创建用户，用root直接操作）

mysql> create user ‘recipient_user’@‘192.168.100.203’ identified by ‘password’;
mysql> grant clone_admin on . to ‘recipient_user’@‘192.168.100.203’;

–安装clone插件
install plugin clone soname ‘mysql_clone.so’;

–设置参数clone_valid_donor_list

set global clone_valid_donor_list=‘192.168.100.202:3307’;
Query OK, 0 rows affected (0.00 sec)

–换成recipient_user’@‘192.168.100.203’ 用户登陆，执行clone语句

[root@node2 bin]# mysql80 -urecipient_user -ppassword -P3307 -h192.168.100.203

mysql> clone instance from ‘donor_user’@‘192.168.100.202’:3307 identified by ‘password’;
Query OK, 0 rows affected (1.29 sec)

mysql> Restarting mysqld;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect…

查看数据目录出现如下文件： #clone #ib_16384_0.dblwr #ib_16384_1.dblwr #innodb_temp

cd /usr/local/mysql80/data/
[root@node2 data]# ll
total 188856
-rw-r-----. 1 mysql mysql 56 Aug 26 23:56 auto.cnf
-rw-------. 1 mysql mysql 1676 Aug 26 23:56 ca-key.pem
-rw-r–r--. 1 mysql mysql 1112 Aug 26 23:56 ca.pem
-rw-r–r--. 1 mysql mysql 1112 Aug 26 23:56 client-cert.pem
-rw-------. 1 mysql mysql 1676 Aug 26 23:56 client-key.pem
drwxr-x—. 2 mysql mysql 72 Sep 11 20:09 #clone
-rw-r-----. 1 mysql mysql 5839 Sep 11 20:13 error.log
-rw-r-----. 1 mysql mysql 196608 Sep 11 20:11 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql 8585216 Aug 26 23:56 #ib_16384_1.dblwr
-rw-r-----. 1 mysql mysql 5942 Sep 11 20:09 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Sep 11 20:09 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Sep 11 20:11 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Sep 11 20:09 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep 11 20:09 ibtmp1
drwxr-x—. 2 mysql mysql 187 Sep 11 20:09 #innodb_temp
drwxr-x—. 2 mysql mysql 143 Aug 26 23:56 mysql
-rw-r-----. 1 mysql mysql 5 Sep 11 20:09 mysqld.pid
-rw-r-----. 1 mysql mysql 25165824 Sep 11 20:09 mysql.ibd
drwxr-x—. 2 mysql mysql 64 Sep 11 20:09 performance_schema
-rw-------. 1 mysql mysql 1680 Aug 26 23:56 private_key.pem
-rw-r–r--. 1 mysql mysql 452 Aug 26 23:56 public_key.pem
-rw-r–r--. 1 mysql mysql 1112 Aug 26 23:56 server-cert.pem
-rw-------. 1 mysql mysql 1676 Aug 26 23:56 server-key.pem
drwxr-x—. 2 mysql mysql 28 Sep 11 20:09 sys
-rw-r-----. 1 mysql mysql 16777216 Sep 11 20:11 undo_001
-rw-r-----. 1 mysql mysql 16777216 Sep 11 20:11 undo_002

等待clone主库192.168.100.202数据…

通过查询两张表来监控一下克隆的进度和结果状态：

–查看clone进度和状态

mysql> SELECT * FROM performance_schema.clone_progress;
±-----±----------±----------±---------------------------±---------------------------±--------±---------±---------±---------±-----------±--------------+
| ID | STAGE | STATE | BEGIN_TIME | END_TIME | THREADS | ESTIMATE | DATA | NETWORK | DATA_SPEED | NETWORK_SPEED |
±-----±----------±----------±---------------------------±---------------------------±--------±---------±---------±---------±-----------±--------------+
| 1 | DROP DATA | Completed | 2024-09-11 20:09:13.550624 | 2024-09-11 20:09:13.748953 | 1 | 0 | 0 | 0 | 0 | 0 |
| 1 | FILE COPY | Completed | 2024-09-11 20:09:13.749120 | 2024-09-11 20:09:14.178993 | 1 | 71423798 | 71423798 | 71435636 | 0 | 0 |
| 1 | PAGE COPY | Completed | 2024-09-11 20:09:14.179243 | 2024-09-11 20:09:14.190915 | 1 | 0 | 0 | 99 | 0 | 0 |
| 1 | REDO COPY | Completed | 2024-09-11 20:09:14.191140 | 2024-09-11 20:09:14.202456 | 1 | 4096 | 4096 | 4493 | 0 | 0 |
| 1 | FILE SYNC | Completed | 2024-09-11 20:09:14.202713 | 2024-09-11 20:09:14.677099 | 1 | 0 | 0 | 0 | 0 | 0 |
| 1 | RESTART | Completed | 2024-09-11 20:09:14.677099 | 2024-09-11 20:09:18.076112 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | RECOVERY | Completed | 2024-09-11 20:09:18.076112 | 2024-09-11 20:09:18.882049 | 0 | 0 | 0 | 0 | 0 | 0 |
±-----±----------±----------±---------------------------±---------------------------±--------±---------±---------±---------±-----------±--------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM performance_schema.clone_status\G
*************************** 1. row ***************************
ID: 1
PID: 0
STATE: Completed
BEGIN_TIME: 2024-09-11 20:09:13.392
END_TIME: 2024-09-11 20:09:18.882
SOURCE: 192.168.100.202:3307
DESTINATION: LOCAL INSTANCE
ERROR_NO: 0
ERROR_MESSAGE:
BINLOG_FILE: mysql-bin.000004
BINLOG_POSITION: 757
GTID_EXECUTED: f12da88e-60a3-11ef-a1d1-005056296686:1-5
1 row in set (0.00 sec)

第三步: 搭建主从复制

–在主库202上建立复制账号：

create user ‘repl’@’%’ identified with mysql_native_password by ‘repl’;

mysql> GRANT REPLICATION SLAVE ON . TO ‘repl’@’%’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

–在从库203上进行复制步骤的完成：

change master to master_host=‘192.168.100.202’,master_port=3307,master_user=‘repl’,master_password=‘repl’,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

start slave;
Query OK, 0 rows affected (0.00 sec)

show slave status\G

至此远程从库通过clone插件的方式搭建成功了，非常简单也非常快速，不需要mysqldump也不需要xtrabackup，在线搭建成功，非常快。

使用clone技术搭建主从需要注意如下几点

MySQL版本大于等于8.0.17且不支持跨版本
两台机器具有相同的操作系统OS
两台MySQL实例具体相同的innodb_page_size和innodb_data_file_path(ibdata文件名)
同一时刻仅仅允许有一个克隆任务存在recipient
需要设置变量 clone_valid_donor_list、max_allowed_packet 大于2M
doner的undo表空间文件名称不能重复
不会克隆my.cnf文件、不会克隆binlog
仅仅支持innodb引擎