MySQL8主从复制配置说明及实践

1    主从异步复制

1.1    架构介绍

 
1.    master将改变记录到二进制日志（ binary log)
2.    slave将master的binary log拷贝到它的中继日志（relay log）
3.    slave重做中继日志中的事件，将改变应用到自己的数据库中
在复制过程当中，主库不会去验证 bin-log 有没有成功复制到从库。如果主库提交一个事务并写入 bin-log 中后，由于网络原因，从库没有接收到对应的 bin-log 那从库就不会得到这个事务，也就造成了主从数据的不一致。

1.2 配置说明

1.    在2台服务器分别安装MySQL 8
名称    IP
node01（master）    192.168.0.10
node02（slave）    192.168.0.11
2.    修改Mysq1数据库的配置文件/etc/my.cnf配置server-id，主从必须不同。
主库server-id=101，从库server-id=102。
[mysqld]
#[必须]启用二进制日志，数据间复制必不可少
log-bin=/

usr/local/mysql/mysqllog/binlog

#[必须]服务器唯一ID

server-id=101

3. 重启主库及从库MySQL

4.在主库创建主从复制账户rep

//创建用户

create user rep@'%' identified by '123456';

//授权

GRANT ALL PRIVILEGES ON *.* TO 'rep'@'%'WITH GRANT OPTION;

//刷新

flush privileges;

5. 查看主机的Master的状态，执行完此SQL后不要再主机执行任何操作
show master status;

6. 从库只读配置

set global read_only=ON;

set global super_read_only=ON

7. 从机开启复制

mysql> change master to master_host="192.168.0.10",master_port=3306,master_user="rep",master_password="123456",master_log_file="binlog.000002",master_log_pos=184857;

8. 从机上查看主从复制配置结果

Slave_IO_Running yesSlave_SQL_Running yes代表主从复制搭建成功了。

1.3    主从复制失败的可能原因

2.    MySQL的uuid不是唯一的，查看主从机器的uuid是否唯一。
3.    确认主从机器的server-id不能相同。查看文件：/etc/mysql/my.cnf。
4.    因为从库MySQL重启导致二进制文件位置从库和主库不一致master_log_file=“mysql-bin.000001”,master_log_pos=1397。
5.    用户无权限。
6.    端口未开放。
7.    错误链接达到主库最大配置max_connect_errors,使用 mysql> flush hosts恢复。

1.4    主从复制的使用规则

1.    只能在主机里面执行DML 语句，不能在从机里面执行DML语句（会破坏主从）
2.    在从机里面可以执行查询语句
3.    主机只有一台，但是从机可以有多台

2 主从半同步复制

2.1 架构介绍

1.    主库收到客户端提交的事务。
2.    主库执行事务。
3.    将数据库事件写入到主库的 bin-log 日志中。
4.    当从库来请求 bin-log 日志时，将其发送给从库。
5.    从库的 I/O 线程获取到的 bin-log 日志写入到 relay-log 中。
6.    当 relay-log 写入完成后，从库向主库发送一个写入成功的通知。
7.    主库收到从库发来的成功通知后将事务的结果返回给客户端。
8.    从库的SQL线程对写入成功的 relay-log 进行重放，将数据保存到数据库中。
同步体现在主库返回事务结果给客户端时，须收到从库发送的 relay-log 写入成功的通知才能返回，否则会一直等待从库发来通知，直到等待时间到了（rpl_semi_sync_master_timeout 参数设置的值）。
异步体现在主库只要收到从库发来的成功通知即可返回结果给客户端，不需要考虑从库的 relay-log 是否重放成功。
假如主库在执行第 1、2、3 步的任意一步步骤时主库宕机，事务都不会成功，自然从库也不会收到主库发送的 bin-log，所以主从的数据还是保持一致。

假如执行第 4 步时，主库宕机事务也不会提交成功，如果由于网络原因导致从库未接收到主库的 bin-log 日志，则主库会等待从库一段时间（等待从库写入 relay-log 成功的通知 ACK），然后将主从复制的模式切换到异步模式，再返回成功给客户端。

假如执行第 5 步时，主库宕机由于此时主库还在等待从库，则主库事务自然执行失败。如果从库宕机则主库会等待从库，若一段时间后未收到 ACK，则将主从复制的模式切换到异步模式，再返回成功给客户端。

2.2 配置说明

1. 主库配置
2. 从库配置
3. 检查配置结果

4. 主从库分别输入show status like "%rpl_semi%";结果如下即主从半同步复制正常启动；

   Rpl_semi_sync_master_clients为1

   Rpl_semi_sync_master_status为ON

   Rpl_semi_sync_slave_status为ON

5. 常用配置

3    主从修复

异步复制和同步复制都存在主从同步失败的问题。

3.1 跳过错误事务

stop slave;
--这里的 1 指定的是 你跳过事务的次数，如果有很多事务没有同步需要设置大一点
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;

3.2 重做主从

--主节点 锁表 防止新数据写入
flush tables with read lock;
--查看master偏移量
show master status;
#主机点mysql库导出
mysqldump -uroot -p123456 --all-databases  > mysql.bak.sql
#从节点拷贝主机sql文件
scp -r mysql.bak.sql mc1:/tmp
--停止从库
stop slave;
--关闭只读权限
set global read_only=0;
set global super_read_only=0;
--导入备份数据
source /tmp/mysql.bak.sql
--打开只读权限

set global read_only=1;

set global super_read_only=1;

--查看只读权限是否打开成功

show variables like '%read_only%';

--重新做主从

change master to master_host="192.168.0.10",master_port=3306,master_user="rep",master_password="123456",master_log_file="binlog.000002",master_log_pos=184857;

部分图片及文字来源链接：

MySQL 8 主从配置_mysql8主从配置-CSDN博客