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)

# 主節(jié)點執(zhí)行
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;

# 這個在從庫中執(zhí)行
# 添加克隆提供方的ip列表(即主庫ip和端口)與端口至clone_valid_donor_list
mysql> SET GLOBAL clone_valid_donor_list = '192.168.0.101:3306';
# 遠程克隆覆蓋自身：（默認方式，提供方數(shù)據(jù)直接克隆到接收方，克隆后接收方實例重啟）
mysql> CLONE INSTANCE FROM 'clone_user'@'192.168.0.101':3306 IDENTIFIED BY 'Rsdkyt#2024!';
# 數(shù)據(jù)量大的話，這個窗口會等待，可以打開另外一個窗口查看克隆進度
# 具體的克隆流程可以查看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> 
# 這個顯示數(shù)據(jù)已經(jīng)拷貝完成

# 在從庫中執(zhí)行操作
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;