Mysql8主从复制实现过程记录
#编程技术 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 主从复制过程分成三步：
- master 将改变记录到二进制日志 (binary log)。
- slave 将 master 的 binary log 拷贝到它的中继日志 (relay log)。
- 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