mysql8.0.39采用克隆方式快速搭建主从同步

mysql8.0.39采用克隆方式快速搭建主从同步

备注：基于物理文件拷贝，数据量越大，越能体现出这种优势。8.0.17以上都可以使用

 

一、环境

192.168.0.101  主库
192.168.0.102  从库
Server version: 8.0.39

 

二、查看是否已经安装克隆插件

# 如果没有同步账号，可以新建一个
drop user `repl`@`192.168.0.101`;
drop user `repl`@`192.168.0.102`;

CREATE USER `repl`@`192.168.0.101` IDENTIFIED  by 'Rsdkyt#2024!';
GRANT REPLICATION SLAVE, REPLICATION CLIENT  on *.* TO `repl`@`192.168.0.101`;

CREATE USER `repl`@`192.168.0.102` IDENTIFIED  by 'Rsdkyt#2024!';
GRANT REPLICATION SLAVE, REPLICATION CLIENT  on *.* TO `repl`@`192.168.0.102`;
mysql> select user,host,plugin from mysql.user;
+------------------+---------------+-----------------------+
| user             | host          | plugin                |
+------------------+---------------+-----------------------+
| root             | %             | mysql_native_password |
| u1               | %             | mysql_native_password |
| clone_user       | 192.168.0.101 | mysql_native_password |
| repl             | 192.168.0.101 | mysql_native_password |
| clone_user       | 192.168.0.102 | mysql_native_password |
| repl             | 192.168.0.102 | mysql_native_password |
| mysql.infoschema | localhost     | caching_sha2_password |
| mysql.session    | localhost     | caching_sha2_password |
| mysql.sys        | localhost     | caching_sha2_password |
| root             | localhost     | mysql_native_password |
+------------------+---------------+-----------------------+
10 rows in set (0.00 sec)

# 主从都需要查看
mysql> show PLUGINS;

# 安装克隆插件
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.05 sec)

mysql> 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)

三、在主节点创建专用账号

# 主节点执行
drop user 'clone_user'@'192.168.0.101';
drop user 'clone_user'@'192.168.0.102';

set sql_log_bin=0;
create user 'clone_user'@'192.168.0.101' identified by 'Rsdkyt#2024!';
GRANT CLONE_ADMIN,BACKUP_ADMIN  on *.* to 'clone_user'@'192.168.0.101';

create user 'clone_user'@'192.168.0.102' identified by 'Rsdkyt#2024!';
GRANT CLONE_ADMIN,BACKUP_ADMIN  on *.* to 'clone_user'@'192.168.0.102';
set sql_log_bin=1;

四、添加数据贡献者以及克隆操作

# 从库同步异常了，先按如下操作
stop replica;
reset replica all;
show replica status\G;

# 这个在从库中执行
# 添加克隆提供方的ip列表(即主库ip和端口)与端口至clone_valid_donor_list
mysql> SET GLOBAL clone_valid_donor_list = '192.168.0.101:3306';
# 远程克隆覆盖自身：（默认方式，提供方数据直接克隆到接收方，克隆后接收方实例重启）
mysql> CLONE INSTANCE FROM 'clone_user'@'192.168.0.101':3306 IDENTIFIED BY 'Rsdkyt#2024!';
# 数据量大的话，这个窗口会等待，可以打开另外一个窗口查看克隆进度
# 具体的克隆流程可以查看performance_schema.clone_progress:
mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
+-----------+-----------+----------------------------+
| STAGE     | STATE     | END_TIME                   |
+-----------+-----------+----------------------------+
| DROP DATA | Completed | 2024-08-27 10:46:22.984287 |
| FILE COPY | Completed | 2024-08-27 10:46:28.295979 |
| PAGE COPY | Completed | 2024-08-27 10:46:28.313991 |
| REDO COPY | Completed | 2024-08-27 10:46:28.330992 |
| FILE SYNC | Completed | 2024-08-27 10:46:28.373628 |
| RESTART   | Completed | 2024-08-27 10:46:32.088214 |
| RECOVERY  | Completed | 2024-08-27 10:46:33.725467 |
+-----------+-----------+----------------------------+
7 rows in set (0.00 sec)

mysql> 
# 这个显示数据已经拷贝完成

五、克隆方式部署主从复制环境

# 在从库中执行操作
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.0.101',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='Rsdkyt#2024!',
  SOURCE_PORT=3306,
  SOURCE_AUTO_POSITION=1;

start replica;
show replica status\G;

完毕！