【数据库系列教程】MySQL主从复制

一、主从复制

1.工作原理

Master服务器将写操作保存到二进制日志,通过网络将事件发送给slave服务器;
slave服务器产生I/O thread线程接收二进制日志事件,并将该事件写入到本地的中继日志relay log,同时产生SQL thread线程从中继日志中读取操作执行操作,确保数据同步。

slave1

核心关注点:

  • 数据同步
  • 复制延迟时间

实现流程:

  • 配置server_id
  • Master服务器启用二进制日志
  • Master服务器要存在允许从服务器远程 连接的用户

2.作用

  • 避免数据库单点故障
  • 便于冷备份
  • 读写分离
  • 实现方式:
    • 开发代码
    • 数据库中间件
      • mysql-proxy
      • mycat

3.常见主从复制架构

  • 一主一从
  • 一主多从
    • 实现读操作的负载均衡
  • 双主复制

4.主从复制工作方式

  • 异步
    • 默认
  • 同步
  • 半同步
    • 借助插件google公司semi

二、案列:一主一从复制

1.准备2台虚拟机

10.10.10.128 Master服务器
10.10.10.130 Slave服务器

2.关闭防火墙和SElinux、时间同步

3.在Master服务器配置

A.安装MySQL 5.7

点我跳转MySQL安装教程

B.修改配置文件

tips:如果没有vim命令请使用vi或者安装yum install -y vim

[root@master ~]# vim /etc/my.cnf

[mysqld]
server_id=10	#指定serverID
log_bin=master	#开启二进制日志文件
gtid_mode=on	#开启事务ID
enforce_gtid_consistency=true	#强制GTID的一致性
仅演示添加地方,配置文件并不完整,请勿删改其他
GTID的介绍
  • MySQL 5.6版本和之后版本支持此功能
  • GTID全称Global Transaction Identified
  • 中文:全局事务ID
  • GTID构成:
    • server_uuid + 事务ID
  • 记录在二进制日志文件内,每一条记录的命令都会有一个单独的GTID
启动MySQL
[root@master ~]# systemctl enable --now mysqld

C.修改Master节点MySQLroot用户的密码

查看临时密码
[root@master ~]# cat /var/log/mysqld.log | grep -i password
2023-04-07T11:00:18.621832Z 1 [Note] A temporary password is generated for root@localhost: kdt&12i_Bqgh
2023-04-07T11:03:24.803276Z 4 [Note] Access denied for user 'root'@'localhost' (using password: YES)
[root@master ~]# mysql -uroot -p
Enter password: kdt&12i_Bqgh	#这里填写临时密码,每个人的都不一样!
修改密码
mysql> set password for 'root'@'localhost' = PASSWORD("WWW.1.com");		#指定密码为WWW.1.com
Query OK, 0 rows affected, 1 warning (0.00 sec)		#修改密码成功

D.在Master节点创建用于主从复制的用户

mysql> grant replication slave on *.* to 'repluser'@'10.10.10.130' identified by 'WWW.1.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)
  • replication slave授予指定权限
  • IP指定为slave服务器
  • 密码:WWW.1.com

4.在Slave服务器配置

A.安装MySQL 5.7

点我跳转MySQL安装教程

B.修改配置文件

[root@slave ~]# vim /etc/my.cnf

[mysqld]
server_id=13	#必须和主节点Master不一样
log_bin=master	#开启二进制日志(可选,方便后续主从切换)
gtid_mode=on	#开启事务ID(可选,方便后续主从切换)
enforce_gtid_consistency=true	#强制GTID的一致性(可选,方便后续主从切换)
仅演示添加地方,配置文件并不完整,请勿删改其他
启动MySQL服务
[root@slave ~]# systemctl enable --now mysqld

C.修改Slave节点MySQLroot用户的密码

查看临时密码
[root@slave ~]# cat /var/log/mysqld.log | grep -i password
2023-04-09T05:58:01.597896Z 1 [Note] A temporary password is generated for root@localhost: 3<Oi9<(L0d/+
[root@slave ~]# mysql -uroot -p
Enter password: 3<Oi9<(L0d/+	#这里填写临时密码
修改密码
mysql> set password for 'root'@'localhost' = PASSWORD("WWW.1.com");		#指定密码为WWW.1.com
Query OK, 0 rows affected, 1 warning (0.00 sec)		#修改密码成功

D.创建Slave

mysql> change master to
    -> master_host="10.10.10.128",	#Mater服务器IP
    -> master_user="repluser",	#我们刚刚在Master服务器创建的用户
    -> master_password="WWW.1.com",	#指定的密码
    -> master_auto_position=1;	#自动配置,注意Master服务器必须开启GTID服务
Query OK, 0 rows affected, 2 warnings (0.00 sec)

如果您的MySQL版本低于5.6,或者您使用的是低版本的MariaDB,不支持GTID功能的,请尝试使用下面的操作!

1.前往==主节点==查看Master端的二进制日志(上面的不报错,跳过此步骤)
MariaDB [(none)]> show master status\G;
*************************** 1. row ***************************
            File: master.000002	#记住这里的二进制日志文件,每个人的不一样
        Position: 10404018	#记住这里的position,每个人的不一样
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)
2.回到==从节点==创建Slave(上面的不报错,跳过此步骤)
mysql> change master to
    -> master_host="10.10.10.128",	#Mater服务器IP
    -> master_user="repluser",	#我们刚刚在Master服务器创建的用户
    -> master_password="WWW.1.com",	#指定的密码
    -> master_log_file="master.000002",	#必须和Master端查看的一致,指定二进制日志文件
    -> master_log_pos=10404018;	#必须和Master端看到的pos一致
Query OK, 0 rows affected, 2 warnings (0.00 sec)

E.启动Slave并查看状态

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.10.10.128
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No	#看到IO线程未启动
            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: 0
              Relay_Log_Space: 154
              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: 1593
                Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = OFF and this server has GTID_MODE = ON.	#这里报错提示Master服务器未启动GTID服务
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/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: 230409 14:05:52
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 7c541e74-d69b-11ed-bc23-000c29d2b7fd:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

F.检查Master服务器GTID服务

[root@master ~]# systemctl restart mysqld	#我这里因为刚才修改配置文件后,未重启服务,导致GTID服务未启动

G.回到Slave服务器重启Slave服务

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

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

H.查看Slave状态

mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.128
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: master.000001
             Slave_IO_Running: Yes	#可以看到IO线程成功启动
            Slave_SQL_Running: Yes	#SQL线程没有问题
              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: 154
              Relay_Log_Space: 568
              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: 10
                  Master_UUID: 62379680-d533-11ed-bd42-000c298e07d7
             Master_Info_File: /var/lib/mysql/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: 
            Executed_Gtid_Set: 7c541e74-d69b-11ed-bc23-000c29d2b7fd:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

5.测试主从

A.在Master主服务器创建库

mysql> create database A;
Query OK, 1 row affected (0.00 sec)

B.在Slave从服务器查看是否同步

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| A                  |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)	#可以看到成功同步过来了!

6.配置只读属性

因为主从关系,我们不能在Slave从服务器进行写改操作,这样做会破坏主从线程(包括IO和SQL)的正常运行。

A.在Slave从服务器配置只读属性

[root@slave ~]# vim /etc/my.cnf

[mysqld]
server_id=13
read_only=1	#启用只读属性
log_bin=master
gtid_mode=on
enforce_gtid_consistency=true
仅演示添加地方,配置文件并不完整,请勿删改其他

B.重启MySQL服务

[root@slave ~]# systemctl restart mysqld

三、主从切换

1.适用场景

Master主节点挂掉以后,可以使用Slave从节点,把Slave从节点变成新的Master主节点旧的Master主节点变成新的Slave从节点

2.模拟旧Master主节点挂掉

[root@master ~]# systemctl stop mysqld

3.删除旧Slave从节点的配置

mysql> stop slave; 	#先停止服务

mysql> reset slave all; 	#删除旧的连接关系

4.修改旧Slave从节点改配置文件

[mysqld]
server_id=13
log_bin=master	#开启二进制日志
gtid_mode=on	#开启事务ID
enforce_gtid_consistency=true	#强制GTID的一致性
仅演示添加地方,配置文件并不完整,请勿删改其他

5.在旧Slave从节点创建用户

mysql> grant replication slave on *.* to 'repluser'@'10.10.10.128' identified by 'WWW.1.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Tips:创建一个授权旧Master主节点(新Slave从节点)登录的用户,IP地址为旧Master主节点

6.恢复新Slave从节点(旧Master主节点)服务

[root@master ~]# systemctl start mysqld

7.在新Slave从节点(旧Master主节点)添加Slave关系

mysql> change master to
    -> master_host="10.10.10.130",	#新Master主节点(旧Slave从节点)的IP
    -> master_user="repluser",
    -> master_password="WWW.1.com",
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.130
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000004
          Read_Master_Log_Pos: 194
               Relay_Log_File: master-relay-bin.000005
                Relay_Log_Pos: 401
        Relay_Master_Log_File: master.000004
             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: 194
              Relay_Log_Space: 856
              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: 13
                  Master_UUID: 7c541e74-d69b-11ed-bc23-000c29d2b7fd
             Master_Info_File: /var/lib/mysql/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: 7c541e74-d69b-11ed-bc23-000c29d2b7fd:1
            Executed_Gtid_Set: 62379680-d533-11ed-bd42-000c298e07d7:1-2,
7c541e74-d69b-11ed-bc23-000c29d2b7fd:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

四、双主复制(重要)

  • 2台服务器互为主从

1.配置

  • 2台服务器都要开启二进制日志GTID,需要关闭只读属性

2.创建用户

  • 2台服务器都要拥有slave用户用于互相登录对方数据库,都需要授权replication slave权限,授权登录IP对方IP
  • 如果基于单主单从服务器修改,需要在Master主服务器上创建用户,利用Slave线程自动同步Slave从服务器上。
mysql> grant replication slave on *.* to 'repluser'@'10.10.10.128' identified by 'WWW.1.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Tips:这条数据会自动同步到上一个实验的Slave从服务器上,这就相当于我们的Slave从服务器已经授权了我们Master主服务器远程登录。

3.互相创建slave服务即可

  • 双方IO线程SQL线程都为yes状态就是成功
  • 效果:任意一台数据库上,创建任意数据库或者增删改数据对方服务器都能成功同步操作

Master主服务器创建Slave

mysql> change master to
    -> master_host="10.10.10.130",	#Slave从服务器的IP
    -> master_user="repluser",
    -> master_password="WWW.1.com",
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.130
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000004
          Read_Master_Log_Pos: 194
               Relay_Log_File: master-relay-bin.000005
                Relay_Log_Pos: 401
        Relay_Master_Log_File: master.000004
             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: 194
              Relay_Log_Space: 856
              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: 13
                  Master_UUID: 7c541e74-d69b-11ed-bc23-000c29d2b7fd
             Master_Info_File: /var/lib/mysql/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: 7c541e74-d69b-11ed-bc23-000c29d2b7fd:1
            Executed_Gtid_Set: 62379680-d533-11ed-bd42-000c298e07d7:1-2,
7c541e74-d69b-11ed-bc23-000c29d2b7fd:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

五、多主单从(多源复制)

1.关于多源复制的介绍

  • MySQL 5.7版本开始支持
  • 支持多个主服务器同一个从服务器上复制数据
  • 通过channel隧道来区分不同的主服务器
  • master.info, relay-log.info文件中存储的信息要记录到

slave2

Tips:多主单从情况下,从服务器会存储所有主服务器的数据,主服务器之间的数据,不会互相影响

2.环境准备

  • 三台虚拟机
    • 10.10.10.128 Master1主节点
    • 10.10.10.129 Slave从节点
    • 10.10.10.130 Master2主节点

3.配置2台主Master服务器

如果刚才已经配置了互主互从关系,可以使用stop slave;停止服务,然后使用reset slave all;清除连接关系,并且执行flush logs;刷新一下二进制日志

A.修改配置文件

Master1
[root@master1 ~]# vim /etc/my.cnf

[mysqld]
server_id=10	#这里的ID是唯一的
log_bin=master	#开启二进制日志
gtid_mode=on	#开启GTID
enforce_gtid_consistency=true
仅演示添加地方,配置文件并不完整,请勿删改其他
Master2
[root@master2 ~]# vim /etc/my.cnf

[mysqld]
server_id=13	#ID是唯一的
log_bin=master	#开启二进制日志
gtid_mode=on	#开启GTID
enforce_gtid_consistency=true
仅演示添加地方,配置文件并不完整,请勿删改其他

B.创建Slave用户

Master1
mysql> grant replication slave on *.* to 'repluser'@'10.10.10.129' identified by 'WWW.1.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)

IP地址为Slave从节点

Master2
mysql> grant replication slave on *.* to 'repluser'@'10.10.10.129' identified by 'WWW.1.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)

IP地址为Slave从节点

4.配置Slave从节点

A.修改Slave从节点配置文件

[root@slave ~]# vim /etc/my.cnf

[mysqld]
server_id=11	#ID唯一
log_bin=master	#开启二进制日志(可选)
gtid_mode=on	#开启GTID
enforce_gtid_consistency=true
master_info_repository=TABLE	#必须开启(将连接记录存到表中)
relay_log_info_repository=TABLE	#必须开启(将连接记录存到表中)
仅演示添加地方,配置文件并不完整,请勿删改其他

B.重启Slave从节点的服务

[root@salve ~]# systemctl restart mysqld

C.在Slave从服务器上添加2个Master主服务器

mysql> change master to
    -> master_host="10.10.10.128",	#Master1的IP
    -> master_user="repluser",
    -> master_password="WWW.1.com",
    -> master_auto_position=1 for channel "to_master01";	#这里创建第一个隧道名为to_master01,这句话必须写在最后!
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> change master to
    -> master_host="10.10.10.130",	#Master2的IP
    -> master_user="repluser",
    -> master_password="WWW.1.com",
    -> master_auto_position=1 for channel "to_master02";	#这里创建第二个隧道名为to_master02,这句话必须写在最后!
Query OK, 0 rows affected, 2 warnings (0.00 sec)

5.测试

A.启动所有隧道

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

B.查看所有隧道状态

mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.128
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000002
          Read_Master_Log_Pos: 736
               Relay_Log_File: node1-relay-bin-to_master01.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: master.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No	#发现SQL进程未启动
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table mysql.plugin; Duplicate entry 'validate_password' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master.000001, end_log_pos 421
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 1886
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table mysql.plugin; Duplicate entry 'validate_password' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master.000001, end_log_pos 421	#报错主键冲突
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 75cecbf1-d6b3-11ed-a170-000c298e07d7
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 230409 16:55:12
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 75cecbf1-d6b3-11ed-a170-000c298e07d7:1-3
            Executed_Gtid_Set: 0b8ae544-d534-11ed-8391-000c290f55d2:1-2,
6473a5bb-d6b3-11ed-862c-000c29d2b7fd:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: to_master01
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.130
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000002
          Read_Master_Log_Pos: 696
               Relay_Log_File: node1-relay-bin-to_master02.000002
                Relay_Log_Pos: 903
        Relay_Master_Log_File: master.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: 696
              Relay_Log_Space: 1122
              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: 13
                  Master_UUID: 6473a5bb-d6b3-11ed-862c-000c29d2b7fd
             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: 6473a5bb-d6b3-11ed-862c-000c29d2b7fd:1-2
            Executed_Gtid_Set: 0b8ae544-d534-11ed-8391-000c290f55d2:1-2,
6473a5bb-d6b3-11ed-862c-000c29d2b7fd:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: to_master02
           Master_TLS_Version: 
2 rows in set (0.00 sec)

6.解决to_master01隧道报错

报错原因:主键冲突。
解决方法:找到冲突的GTID,然后跳过即可

A.先停止Salve

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

B.查找GTID点

mysql> select * from performance_schema.replication_applier_status_by_worker where LAST_ERROR_NUMBER=1062\G;

*************************** 1. row ***************************
         CHANNEL_NAME: to_master01
            WORKER_ID: 0
            THREAD_ID: NULL
        SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: 75cecbf1-d6b3-11ed-a170-000c298e07d7:1	#得到我们GTID跳报错地址
    LAST_ERROR_NUMBER: 1062
   LAST_ERROR_MESSAGE: Could not execute Write_rows event on table mysql.plugin; Duplicate entry 'validate_password' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master.000001, end_log_pos 421
 LAST_ERROR_TIMESTAMP: 2023-04-09 17:30:43
1 row in set (0.00 sec)

LAST_ERROR_NUMBER=1062,1062为上一句执行show slave status\G;Last_SQL_Errno: 1062的值(每个人都不一样)

C.跳过报错GTID点

mysql> set @@session.gtid_next='75cecbf1-d6b3-11ed-a170-000c298e07d7:1';
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
mysql> commit;
mysql> set @@session.gtid_next=automatic; 

D.启动Slave

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

E.检查报错是否解决

mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.128
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000002
          Read_Master_Log_Pos: 889
               Relay_Log_File: node1-relay-bin-to_master01.000005
                Relay_Log_Pos: 445
        Relay_Master_Log_File: master.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: 889
              Relay_Log_Space: 955
              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: 10
                  Master_UUID: 75cecbf1-d6b3-11ed-a170-000c298e07d7
             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: 75cecbf1-d6b3-11ed-a170-000c298e07d7:1-4
            Executed_Gtid_Set: 6473a5bb-d6b3-11ed-862c-000c29d2b7fd:1-3,
75cecbf1-d6b3-11ed-a170-000c298e07d7:1-4,
e3eb4ef0-d6b7-11ed-b7ad-000c290f55d2:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: to_master01
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.130
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000002
          Read_Master_Log_Pos: 846
               Relay_Log_File: node1-relay-bin-to_master02.000004
                Relay_Log_Pos: 445
        Relay_Master_Log_File: master.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: 846
              Relay_Log_Space: 955
              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: 13
                  Master_UUID: 6473a5bb-d6b3-11ed-862c-000c29d2b7fd
             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: 6473a5bb-d6b3-11ed-862c-000c29d2b7fd:1-3
            Executed_Gtid_Set: 6473a5bb-d6b3-11ed-862c-000c29d2b7fd:1-3,
75cecbf1-d6b3-11ed-a170-000c298e07d7:1-4,
e3eb4ef0-d6b7-11ed-b7ad-000c290f55d2:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: to_master02
           Master_TLS_Version: 
2 rows in set (0.00 sec)