#编程技术 2023-09-12 14:37:00 | 全文 6208 字，阅读约需 13 分钟 | 加载中... 次浏览

👋 相关阅读

• 如何定位Mysql中CPU占用高的查询语句
• Waiting for table metadata lock问题处理
• Mysql8安全清理mysql.slow慢查询日志和general_log文件
• MySQL 正则替换数据：REGEXP_REPLACE 函数
• Mysql 窗口函数学习

---

MySQL 主从复制是一种数据同步技术，将一个 MySQL 数据库从一个服务器（称为主服务器）复制到其他服务器（称为从服务器）。在这个过程中，主服务器 (master) 将写操作记录在二进制日志中，并将日志传输到从服务器 (slave)，从服务器 (slave) 则将其重做，保持与主服务器 (master) 数据的同步。MySQL 支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。MySQL 主从复制是 MySQL 数据库自带的功能，无需借助第三方工具。

MySQL 主从复制过程分成三步：

1. master 将改变记录到二进制日志 (binary log)。
2. slave 将 master 的 binary log 拷贝到它的中继日志 (relay log)。
3. slave 重新执行中继日志中的事件，将执行的事件应用到自己的数据库中。

MySQL主从复制原理图过程如下：

需要注意的是，因为 MySQL 主从复制是异步进行的，所以在数据同步期间可能会存在一些延迟。此外，需要确保主服务器和从服务器的 MySQL 版本号相同，以免出现不兼容的问题。

前期准备工作

1、首先提前准备好两台（或者多台）Linux 服务器，要求主服务器 (master) 与从服务器 (slave) 的 IP 地址、数据库中的 server_uuid 以及虚拟机的 MAC 地址不能相同。

1.1、分别查看主服务器虚拟机与从服务器虚拟机的 MAC 地址是否相同（相同则修改任意一个虚拟机的 MAC 地址即可）

如何修改虚拟机的MAC地址的相关操作可看这里：https://blog.csdn.net/weixin_56817591/article/details/131075423?spm=1001.2014.3001.5501

1.2、分别查看主服务器 (master) 与从服务器 (slave) 的 IP 地址是否相同（相同则修改任意一个 Linux 服务器 IP 地址即可）

ifconfig  #查看本地IP地址

如何修改 Linux 服务器的 IP 地址的相关操作可看这里：https://blog.csdn.net/weixin_56817591/article/details/131075423?spm=1001.2014.3001.5501

1.3、分别查看主服务器 (master) 与从服务器 (slave) MySQL 数据库中 server_uuid 是否相同

登录主服务器管理员 MySQL 数据库

mysql -uroot -p密码

执行 SQL 语句，查看 server_uuid 中的值是否相同

show variables like '%server_uuid%';

如果相同，则删除任意一个 Linux 服务器 MySQL 数据库中的 server_uuid，并重新启动被删除的 MySQL 服务

退出 MySQL 登录后，执行下面语句

rm -rf /var/lib/mysql/auto.cnf   #删除MySQL数据库中的server_uuid，删除后重启MySQL服务会自动生成新的server_uuid
systemctl restart mysqld  #重启MySQL服务

[Linux拓展]：MySQL服务相关操作

#按Ctrl+d可退出MySQL登录
systemctl stop mysqld      #停止MySQL服务
systemctl start mysqld		## 启动MySQL服务
systemctl enable mysqld		 ## 设置MySQL服务开机自启

2、需要保证主服务器 (master) 与从服务器 (slave) 的 Linux 服务器之间可以相互 ping 通

在主服务器 (master) 中 ping 从服务器 (slave) 的 IP 地址 在从服务器 (slave) 中 ping 主服务器 (master) 的 IP 地址

3、需要保证主服务器 (master) 与从服务器 (slave) 的 Linux 服务器 3306 端口开启或直接关闭防火墙

查看开放的端口

firewall-cmd --zone=public --list-ports

查看防火墙状态

systemctl status firewalld

[Linux拓展]：其他关于防火墙的操作

#查看防火墙状态
systemctl status firewalld 或者 firewall-cmd --state
#暂时关闭防火墙
systemctl stop firewalld
#永久关闭防火墙
systemctl disable firewalld
#开启防火墙
systemctl start firewalld
#开放指定端口
firewall-cmd --zone=public --add-port=开放指定的端口号/tcp --permanent
#关闭指定端口
firewall-cmd --zone=public --remove-port=关闭指定端口号/tcp --permanent
#立即生效
firewall-cmd --reload

4、分别检查主服务器 (master) 与从服务器 (slave) 的 MySQL 服务的运行状态,确保 MySQL 服务已经启动

systemctl status mysqld

主库配置（master）

第一步：修改主服务器 MySQL 数据库的配置文件 /etc/my.cnf

打开配置文件 /etc/my.cnf

vi /etc/my.cnf

修改配置文件/etc/my.cnf

#开启二进制日志
log-bin=mysql-bin
#[必须]指定服务器唯一id，默认为1，值范围为1～2^32−1。主库与从库的server-id不能重复
server_id=188
#是否只读,1 代表只读, 0 代表读写
read_only=0
default_authentication_plugin=mysql_native_password
#忽略的数据, 指不需要同步的数据库(按需)
#binlog-ignore-db=mysql
#指定同步的数据库(按需)
#binlog-do-db=db01

第二步：重启主服务器 MySQL 服务

systemctl restart mysqld

第三步: 登录主服务器管理员 MySQL 数据库，执行下面的 SQL 语句创建远程连接的账号，并给账号授予主从复制的权限

登录主服务器管理员 MySQL 数据库

mysql -uroot -p密码

执行SQL语句

#注意设置的密码需要符合大于8位，有大写字母，有特殊符号，不能是连续的
#如果想配置简单密码，可看下面的SQL[拓展]，再来执行这里的SQL语句。这里我已经提前弄好了。

#创建用户并设置密码，%代表该用户可在任意主机连接该MySOL服务
CREATE USER '用户名'@'%' IDENTIFIED WITH mysql_native_password BY '用户密码';
GRANT REPLICATION SLAVE ON *.* TO '用户名'@'%';  #为该用户分配主从复制授权
FLUSH PRIVILEGES;  #重新刷新权限

注意:上面SQL语句的作用是创建一个用户和密码，并且给用户授予 REPLICATION SLAVE 权限。常用于建立复制时所需要用到的用户权限，也就是 slave 必须被 master 授权具有该权限的用户，才能通过该用户复制主库数据。

查看创建的用户是否成功

#查看数据库中的所有用户
select user,host,Grant_priv,Super_priv from mysql.user;
#删除数据库中的指定用户
DROP USER '用户名'@'%';  #注意删除用户时，@后面可以是ip地址、localhost、%

[SQL扩展]：配置 MySQL 简单密码相关操作

## 设置MySQL的密码安全级别与密码长度
set global validate_password.policy=0;		## 设置密码安全级别低
set global validate_password.length=4;	## 设置密码长度最低为4位
flush privileges;    #重新刷新权限

从库配置（slave）

第一步：修改从服务器 MySQL 数据库的配置文件 /etc/my.cnf

打开配置文件/etc/my.cnf

vi /etc/my.cnf

修改配置文件 /etc/my.cnf

#[必须]指定服务器唯一id，默认为1，值范围为1～2^32−1。主库与从库的server-id不能重复
server_id=189
#是否只读,1 代表只读, 0 代表读写
read_only=1
#relay_log文件位置定义，不定义的话默认是在data目录
relay_log = /var/lib/mysql/mysql-realay-bin

第二步:重启从服务器 MySQL 服务

systemctl restart mysqld

第三步: 登录主服务器 MySQL 数据库（master），执行下面 SQL 语句，记录下结果中 File 和 Position 的值

登录主服务器（master）mysql 数据库

mysql -uroot -p密码

执行sql语句

#注意：这里查看的是主服务器MySQL数据库（master）Master的状态，而不是从服务器。
#注意：执行完此SQL语句后不要在主服务器（master）中再执行任何操作，因为每一次查看主服务器MySQL数据库Master状态可能会发生改变。
show master status;

第四步: 登录从服务器（slave）MySQL 数据库，执行下面的 SQL 语句

登录从服务器（slave）mysql 数据库

mysql -uroot -p密码

执行sql语句

#停止slave同步线程（第一次配置可不执行该操作）
stop slave;

## master_host：主库的IP地址
## master_user：在主库中创建的用户名称
## master_password：在主库中创建的用户密码
## master_log_file：第三步中查看的主库Master的File状态码
## master_log_pos：第三步中查看的主库Master的Position状态码

#MySQL8.0.23之前的版本执行这个SQL语句
change master to master_host='192.168.188.188',master_user='xiaoming',master_password='123456',master_log_file='mysql-bin.000014',master_log_pos=157;

#MySQL8.0.23或者之后的版本执行这个SQL语句
CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.188.188', SOURCE_USER='xiaoming',SOURCE_PASSWORD='123456', SOURCE_LOG_FILE='mysql-bin.000014',SOURCE_LOG_POS=157;

#启动slave同步线程
#MySQL8.0.22之前的版本执行这个SQL语句
start slave;
#MySQL8.0.22或者之后的版本执行这个SQL语句
start replica;

第五步:登录从服务器 MySQL 数据库，执行下面 SQL，查看从数据库的状态

show slave status\G;

如果 Slave_IO_Running 与 Slave_SQL_Running 这两个的状态同时为 Yes，则表示成功，否则表示不成功。

相关说明

mysql show slave status 字段说明

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event    #多种不同状态，目前状态表示已经成功连接到master，正等待二进制日志的到达。
                  Master_Host: 10.10.10.1                          #mysql主库的ip地址
                  Master_User: repl                                #master上面的一个用户，用来负责主从复制的用户。
                  Master_Port: 3309                                #master服务器的端口
                Connect_Retry: 60                                  #连接中断后，重新尝试连接的时间间隔。默认值是60秒。
              Master_Log_File: mysql-bin.000003                    #当前I/O线程正在读取的主服务器binlog日志文件的名称。
          Read_Master_Log_Pos: 2224                                #当前I/O线程正在读取的binlog日志的位置。
               Relay_Log_File: relay-log.000008                    #当前slave SQL线程正在读取并执行的中继日志的名称。
                Relay_Log_Pos: 566                                 #当前slave SQL线程正在读取并执行的中继日志的位置。
        Relay_Master_Log_File: mysql-bin.000003                    #SQL线程从中继日志中读取的正在执行的sql语句，对应主库的sql语句记录在主库的哪个binlog日志中。
             Slave_IO_Running: Yes                                 #I/O线程是否被启动并成功地连接到主服务器上。
            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                                   #slave的SQL线程读取日志参数的的错误数量和错误消息，错误数量为0并且消息为空字符串表示没有错误。
                   Last_Error:                                     #如果Last_Error值不是空值，它也会在从属服务器的错误日志中作为消息显示。 
                 Skip_Counter: 0                                   #SQL_SLAVE_SKIP_COUNTER的值，用于设置跳过sql执行步数。
          Exec_Master_Log_Pos: 2224                                #slave SQL线程当前执行的事件，对应在master相应的二进制日志中的位置(Exec_Master_Log_Pos不可能超过Read_Master_Log_Pos)。
              Relay_Log_Space: 767                                 #所有原有的中继日志结合起来的总大小。
              Until_Condition: None                                #如果没有指定UNTIL子句，则没有值。
               Until_Log_File:                                     #用于指示日志文件名。
                Until_Log_Pos: 0                                   #用于指示日志文件名的位置值。
           Master_SSL_Allowed: No                                  #不允许对主服务器进行SSL连接，则值为No，反之为YES。允许SSL连接，但是从属服务器没有让SSL支持被启用，则值为Ignored。
           Master_SSL_CA_File:                                     #被从属服务器使用加密相关的参数。用于连接主服务器。
           Master_SSL_CA_Path:                                     #被从属服务器使用加密相关的参数。用于连接主服务器。
              Master_SSL_Cert:                                     #被从属服务器使用加密相关的参数。用于连接主服务器。
            Master_SSL_Cipher:                                     #被从属服务器使用加密相关的参数。用于连接主服务器。
               Master_SSL_Key:                                     #被从属服务器使用加密相关的参数。用于连接主服务器。
        Seconds_Behind_Master: 0                                   #主从复制延时，0表示主从同步无延时。
Master_SSL_Verify_Server_Cert: No                                  #被从属服务器使用加密相关的参数。用于连接主服务器。
                Last_IO_Errno: 0                                   #最后一次I/O线程的错误号。
                Last_IO_Error:                                     #最后一次I/O线程的错误消息。
               Last_SQL_Errno: 0                                   #最后一次SQL线程的错误号。
               Last_SQL_Error:                                     #最后一次SQL线程的错误消息。
  Replicate_Ignore_Server_Ids:                                     #主从复制，从库忽略的主库服务器Id号。就是不以这些服务器Id为主库。
             Master_Server_Id: 132135                                    #表示主库服务器id号。
                  Master_UUID: 5f0b7791-a499-11e6-901c-44a84227448b      #表示主库服务器的UUID号。
             Master_Info_File: mysql.slave_master_info                   #表示从库中保存主库服务器相关的目录位置。
                    SQL_Delay: 0                                         #一个非负整数，表示秒数，Slave滞后多少秒于master。
          SQL_Remaining_Delay: NULL                                      #Slave_SQL_Running_State等待，表示有多少秒左右的延迟，在其他时候，这个字段是NULL。
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates   #线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。
           Master_Retry_Count: 86400                               #连接主库失败最多的重试次数。   
                  Master_Bind:                                     #slave从库在多网络接口的情况下使用，以确定用哪一个slave网络接口连接到master。
      Last_IO_Error_Timestamp:                                     #最后一次I/O线程错误时的时间戳。
     Last_SQL_Error_Timestamp:                                     #最后一次SQL线程错误时的时间戳。
               Master_SSL_Crl:                                     #被从属服务器使用加密相关的参数。用于连接主服务器。
           Master_SSL_Crlpath:                                     #被从属服务器使用加密相关的参数。用于连接主服务器。
           Retrieved_Gtid_Set: 89gb7791-a499-11e6-901c-44a84227448b:2-11      #获取到的GTID<IO线程>               
            Executed_Gtid_Set: 89gb7791-a499-11e6-901c-44a84227448b:1-11   #执行过的GTID<SQL线程>
                Auto_Position: 1                                   #记录在GTID模式下是否开启了自动事务校验。
         Replicate_Rewrite_DB:                                     #同步的时候需要更改的db名称。
                 Channel_Name:                                     #复制通道的名称，可以有多个。
           Master_TLS_Version:                                     #确定MySQL服务器允许进行加密连接的TLS协议。

slave I/O线程的状态，有以下几种：

1) waiting for master update
这是connecting to master状态之前的状态

2) connecting to master
I/O线程正尝试连接到master

3) checking master version
在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。

4) registering slave on master
在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。 

5) requesting binlog dump
在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。在这个状态下，I/O线程向master发送请求，请求binlog，位置从指定的binglog 名字和binglog的position位置开始。

6) waiting to reconnect after a failed binlog dump request
如果因为连接断开，导致binglog的请求失败，I/O线程会进入睡眠状态。然后定期尝试重连。尝试重连的时间间隔，可以使用命令"change master to master_connect_trt=X;"改变。

7) reconnecting after a failed binglog dump request
I/O进程正在尝试连接master

8) waiting for master to send event
说明，已经成功连接到master，正等待二进制日志时间的到达。如果master 空闲，这个状态会持续很长时间。如果等待的时间超过了slave_net_timeout(单位是秒)的值，会出现连接超时。在这种状态下，I/O线程会人为连接失败，并开始尝试重连

9) queueing master event to the relay log
此时，I/O线程已经读取了一个event，并复制到了relay log 中。这样SQL 线程可以执行此event

10) waiting to reconnect after a failed master event read
读取时出现的错误(因为连接断开)。在尝试重连之前，I/O线程进入sleep状态，sleep的时间是master_connect_try的值(默认是60秒)

11) reconnecting after a failed master event read
I/O线程正尝试重连master。如果连接建立，状态会变成"waiting for master to send event"

12) waiting for the slave sql thread to free enough relay log space
这是因为设置了relay_log_space_limit，并且relay log的大小已经整张到了最大值。I/O线程正在等待SQL线程通过删除一些relay log，来释放relay log的空间。

13) waiting for slave mutex on exit
I/O线程停止时会出现的状态，出现的时间非常短。

遇到的问题：

Slave_IO_Running: NO

找到从库 my.cnf 配置错误日志，查询原因。

查询到的错误为：the master has GTID_MODE = ON and this server has GTID_MODE = OFF. Error_code: MY-013117

主库为 ON 从库 OFF，两者必须相同才行。这里将从库设置为ON.

解决：

set global gtid_mode=OFF_PERMISSIVE;
set global gtid_mode=ON_PERMISSIVE;
set global enforce_gtid_consistency=on;
set global gtid_mode=ON;

Slave_SQL_Running: No

查看 Last_Error 或者 Last_SQL_Error，看看报错语句是什么，如果报错语句不影响数据，可以手动跳过这条错误语句

手动调过的操作方法如下：

查看 Executed_Gtid_Set 中的 Gtid 值，如果有多个，看最后一个即可，如下图所示，5ad71e36-5bbe-11eb-ba64-4cd98f5b5d69:50541783-50541793 即为要找的 Gtid


首先停止从库的同步
stop slave;
将上边找到的 Gtid 复制过来，冒号后边的值改为上边-后边的值+1
set gtid_next='5ad71e36-5bbe-11eb-ba64-4cd98f5b5d69:50541794';
启动从库同步
start slave;
查看是否正常
show slave status\G;
将 gtid_next 恢复为自动
set gtid_next='automatic';

报错 ERROR 1837 (HY000): When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is ‘7f805cdd-c0a2-11ea-8fc6-000c294d81a5:10’.

是因为没有设置 set gtid_next='automatic';

VIA

MySQL(8)主从复制详细过程_mysql8 主从复制_简243的博客-CSDN博客 https://blog.csdn.net/weixin_56817591/article/details/131029316

深入探讨MySQL主从复制只读机制（mysql中主从复制只读）-数据库运维技术服务 https://www.dbs724.com/224494.html

主从复制Slave_IO_Running: NO Slave_SQL_Running: NO ，Slave failed to initialize relay log info struct解决办法_jerry-89的博客-CSDN博客 https://blog.csdn.net/eagle89/article/details/131570373

MySQL主从复制_mysql 主从复制 read_only_小樊同志的博客-CSDN博客 https://blog.csdn.net/fancy106/article/details/118310770

mysql主从复制_跟健哥做运维的博客-CSDN博客 https://blog.csdn.net/m0_73695023/article/details/130628080

MYSQL –延时同步-恢复数据_mysql主从数据延迟恢复-CSDN博客 https://blog.csdn.net/Jerry00713/article/details/107675800

mysql show slave status字段说明_51CTO博客的技术博客_51CTO博客 https://blog.51cto.com/u_13482808/7931543

---

×