Mysql8主从复制实现过程记录

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

👋 相关阅读


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

图片alt

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

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

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

图片alt

图片alt

需要注意的是,因为 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

图片alt

主库配置(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;

图片alt

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

show slave status\G;

图片alt

如果 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

![图片alt](https://cdn.wangtwothree.com/imgur/QEKGz6a.png ‘‘图片title’’)

首先停止从库的同步
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';

图片alt

图片alt

报错 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

Edit | Last updated on 2024-02-23 10:33:45




×