Mysql5.6.40 主从复制故障_5.6.40-log-优快云博客

本文介绍了Centos7环境下MySQL5.6.40主从复制遇到的问题，如SQL线程故障、主从延迟过高及主库误操作后的恢复方法。建议设置从库为只读以防止主从故障，并提供了配置延时从库以应对误操作，包括停止SQL线程、分析relay-log和恢复数据的步骤。此外，还探讨了如何通过设置sync_binlog和slave-skip-errors来减少延迟和处理错误。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Centos7 Mysql5.6.40 主从复制故障

往期历史

1.docker安装MySQL源码编译
2.Centos7 Mysql5.6.40 主从服务搭建

Mysql5.6.40 主从复制故障

SQL线程故障

读写relay-log.info失败
relay-log损坏，断节，找不到
接收到的SQL无法执行
1.1 要创建的数据库对象，已经存在（创建语句，库或表）
1.2 要删除或修改的对象不存在（库或表或数据行不存在）
1.3 DML语句不符合表定义及约束时

归根揭底的原因都是由于从库发生了写入操作（开发工程师容易在从库操作导致主从故障）

以下是有风险的操作：
stop slave;
#将同步指针向下移动一个，如果多次不同步，可以重复操作。

mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;

/etc/my.cnf
#跳过错误行
slave-skip-errors = 1032,1062,1007

但是，以上操作有时是有风险的，最为安全的做法就是从新构建主从。把握一个原则，一切以主库为主。
一个防患于未的解决方案：将从库设定为只读库。
#临时生效

mysql> set global read_only=1

永久设置：
/etc/my.cnf
read_only=1
以上权限只对普通用户生效
管理员read_only
innodb_read_only=1

主从复制延迟过高

主库做了一个变更，从库很久才追上

主库写binlog不及时
控制binlog从内存写入磁盘的控制开关
每次事务提交立即刷新binlog到磁盘（双一标准之一）

mysql> sync_binlog = 1

每次事务提交不立即写入到磁盘，靠操作系统判断什么时候写入

mysql> sync_binlog = 0

从库IO线程阻塞导致延迟
大事务-》拆成小事务
SQL线程慢（classic replication）
1. 默认只有一个sql线程，从库中的事务都是一个一个来执行的
2. 如果主库的并发事务数很多，大事务都会造成从库延时
3. 5.6版本多线程复制（多sql线程），有局限性，针对不同库的事务进行并发，有些情况下可以解决大事务问题，只能在主库方面，将大事务才拆成小事务

主库误操作，怎么使用延时从库（逻辑损坏）

停止主库业务
立即停止从库sql线程

mysql> stop slave sql_thread; #停止从库sql线程

手工模拟sql线程工作，并截止到误操作之前
读取relay-log.info, 获取到上次执行到的位置，作为继续执行relay-log的起点分析relay-log内容，获取到误操作的位置点。截取这段日志，恢复到从库。
切换为主库，开启业务
恢复原主库为从库使用

模拟延时从库

配置从库延时：

mysql> stop slave;  #停止从库
mysql> CHANGE MASTER TO MASTER_DELAY = 300;#配置5分钟延迟sql线程操作
mysql> start slave;#开启主从
mysql> show slave status \G   #查看配置是否生效
SQL_Delay: 300

主库执行：

mysql> create database delay charset utf8;
mysql> use delay;
mysql> create table t1(id int);
mysql> insert into t1 value(1),(2),(3);
mysql> commit;
mysql> drop database delay;

从库执行：

mysql> stop slave sql_thread; #停止从库sql线程
mysql> show slave status \G #查看sql线程最后执行位置

在这里插入图片描述

mysql> show relaylog events in '43812cb2f48a-relay-bin.000002'; #查看事务执行位置详细情况
+-------------------------------+-----+-------------+-----------+-------------+-                                                                                                                              ---------------------------------------------+
| Log_name                      | Pos | Event_type  | Server_id | End_log_pos |                                                                                                                               Info                                         |
+-------------------------------+-----+-------------+-----------+-------------+-                                                                                                                              ---------------------------------------------+
| 43812cb2f48a-relay-bin.000002 |   4 | Format_desc |         8 |         120 |                                                                                                                               Server ver: 5.6.40-log, Binlog ver: 4        |
| 43812cb2f48a-relay-bin.000002 | 120 | Rotate      |         7 |           0 |                                                                                                                               mysql-bin.000007;pos=120                     |
| 43812cb2f48a-relay-bin.000002 | 167 | Format_desc |         7 |           0 |                                                                                                                               Server ver: 5.6.40-log, Binlog ver: 4        |
| 43812cb2f48a-relay-bin.000002 | 283 | Query       |         7 |         230 |                                                                                                                               create database delay charset utf8           |
| 43812cb2f48a-relay-bin.000002 | 393 | Query       |         7 |         329 |                                                                                                                               use `delay`; create table t1(id int)         |
| 43812cb2f48a-relay-bin.000002 | 492 | Query       |         7 |         410 |                                                                                                                               BEGIN                                        |
| 43812cb2f48a-relay-bin.000002 | 573 | Query       |         7 |         517 |                                                                                                                               use `delay`; insert into t1 value(1),(2),(3) |
| 43812cb2f48a-relay-bin.000002 | 680 | Xid         |         7 |         548 |                                                                                                                               COMMIT /* xid=40 */                          |
| 43812cb2f48a-relay-bin.000002 | 711 | Query       |         7 |         643 |                                                                                                                               drop database delay                          |
+-------------------------------+-----+-------------+-----------+-------------+-                                                                                                                              ---------------------------------------------+

[root@43812cb2f48a /]# cd /data/3308/data
#截取binlog日志误操作的位置点。截取这段日志，恢复到从库。
[root@43812cb2f48a data]# mysqlbinlog --start-position=283 --stop-position=711 /                                                                                                                              data/3308/data/43812cb2f48a-relay-bin.000002>/tmp/relay.sql
[root@43812cb2f48a data]mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;#关闭二进制日志
mysql>source /tmp/relay.sql #恢复数据
mysql> stop slave; #停止主从
mysql> reset slave all;#移除从库配置，改为主库使用