Mysql5.6.40 主从复制故障_mysql 5.6.40-log

往期历史

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

Mysql5.6.40 主从复制故障

SQL线程故障

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

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

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

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

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

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

mysql> set global read_only=1
1

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

主从复制延迟过高

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

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

mysql> sync_binlog = 1
1

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

mysql> sync_binlog = 0
1

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

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

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

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

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

模拟延时从库

配置从库延时：

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

1. 主库执行：

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;
1
2
3
4
5
6

2. 从库执行：

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

1
2
3

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                          |
+-------------------------------+-----+-------------+-----------+-------------+-                                                                                                                              ---------------------------------------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

[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;#移除从库配置，改为主库使用

1
2
3
4
5
6
7
8
9