【数据库系列教程】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节点`的`MySQL`中`root用户`的密码

查看`临时`密码

[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节点`的`MySQL`中`root用户`的密码

查看`临时`密码

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

如果觉得文章对你有用，请随意赞赏

linux linux基础服务 mysql mysql基本命令

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

https://www.wsjj.top/archives/76

作者

网笙久久

发布于

2023-04-09

更新于

2023-07-19

许可协议

CC BY 4.0

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

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

一、主从复制

1.工作原理

核心关注点:

实现流程：

2.作用

3.常见主从复制架构

4.主从复制工作方式

二、案列：一主一从复制

1.准备2台虚拟机

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

3.在Master服务器配置

A.安装MySQL 5.7

B.修改配置文件

GTID的介绍

启动MySQL

C.修改Master节点的MySQL中root用户的密码

查看临时密码

修改密码

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

4.在Slave服务器配置

A.安装MySQL 5.7

B.修改配置文件

启动MySQL服务

C.修改Slave节点的MySQL中root用户的密码

查看临时密码

修改密码

D.创建Slave

1.前往主节点查看Master端的二进制日志(上面的不报错，跳过此步骤)

2.回到从节点创建Slave(上面的不报错，跳过此步骤)

E.启动Slave并查看状态

F.检查Master服务器GTID服务

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

H.查看Slave状态

5.测试主从

A.在Master主服务器创建库

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

6.配置只读属性

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

B.重启MySQL服务

三、主从切换

1.适用场景

2.模拟旧Master主节点挂掉

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

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

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

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

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

四、双主复制(重要)

1.配置

2.创建用户

3.互相创建slave服务即可

在Master主服务器创建Slave

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

1.关于多源复制的介绍

2.环境准备

3.配置2台主Master服务器

A.修改配置文件

Master1

Master2

B.创建Slave用户

Master1

Master2

4.配置Slave从节点

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

B.重启Slave从节点的服务

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

5.测试

A.启动所有隧道

B.查看所有隧道状态

6.解决to_master01隧道报错

A.先停止Salve

B.查找GTID点

C.跳过报错GTID点

D.启动Slave

E.检查报错是否解决

作者

发布于

更新于

3.在`Master服务器`配置

C.修改`Master节点`的`MySQL`中`root用户`的密码

查看`临时`密码

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

4.在`Slave服务器`配置

C.修改`Slave节点`的`MySQL`中`root用户`的密码

查看`临时`密码

1.前往主节点查看`Master`端的二进制日志(上面的不报错，跳过此步骤)

2.回到从节点创建`Slave`(上面的不报错，跳过此步骤)

E.启动`Slave`并查看状态

F.检查`Master服务器`GTID服务

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

H.查看`Slave`状态

A.在`Master主服务器`创建库

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

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

2.模拟`旧Master主节点`挂掉

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

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

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

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

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

3.互相创建`slave服务`即可

在`Master主服务器`创建Slave

3.配置2台`主Master服务器`

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

B.重启`Slave从节点`的服务

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

6.解决`to_master01`隧道报错

D.启动`Slave`