1. 主从故障分析及处理

1.1 监控方法

从库：
show slave status \G;

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 

1.2 IO线程

1.2.1 正常状态

Slave_IO_Running: Yes

1.2.2 非正常状态

NO
Connecting

1.2.3 连接主库故障

1.2.3.1 报错信息

Last_IO_Error: error reconnecting to master 'repl@10.0.0.51:3307' - retry-time: 10  retries: 7

1.2.3.2 可能原因

(1)网络、端口、防火墙
(2)用户名、密码、授权(replication slave权限)
(3)主库连接数上限(select @@max_connections;)
(4)版本不统一，5.7使用native加密，8.0使用sha2加密

1.2.3.3 排查方法

手工连接一下主库
(1)出现Access denied for user 'repl'@'db01' (using password: YES)错误信息时：检查用户名密码
(2)出现Can't connect to MySQL server on '10.0.0.1'错误信息时：检查IP地址和端口号
(3)出现Too many connections错误信息时：检查主库连接数上限

1.2.3.4 故障修复

stop slave;
reset slave all;
change master to...;   #执行正确的change master信息
start slave;
show slave status \G;

1.2.4 请求日志、接收日志故障

1.2.4.1 报错信息

日志追踪错误

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000001' at 154, the last event read from '/data/3307/data/mysql-bin.000002' at 154, the last byte read from '/data/3307/data/mysql-bin.000002' at 154.'

server_id错误

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;

1.2.4.2 可能原因

(1)主库的二进制日志不完整：损坏、不连续、主库reset master
(2)从库请求的起点有问题
(3)主从的server_id(server_uuid)相同
(4)relay-log问题

1.2.4.3 主库需要reset master的场景

如果主库因业务需要（如日志文件需要快要用尽）执行reset master，可以按照以下步骤进行：
(1)找业务不繁忙时间，停业务5分钟，不进行数据写入
(2)等待从库重放完所有主库日志
(3)主库执行reset master
(4)从库依次执行stop slave、reset slave all、change master to 到主库的重新生成的binlog和position、start slave

1.2.4.4 故障修复

如果因为在业务繁忙期间做reset master等操作，导致数据库夯住。
需要重新搭建主从，才能够恢复。

1.3 SQL线程

1.3.1 SQL线程主要工作

回放relay-log中的日志，可以理解为执行relay-log SQL

1.3.2 SQL线程故障本质

SQL线程执行不了SQL语句

1.3.3 可能原因

(1)需要创建的对象已经存在了
(2)需要操作的对象不存在
(3)DML语句不符合表定义及约束
以上问题：大几率出现在从库写入或双主结构中
(4)SQL_MODE，参数，版本不同

1.3.4 故障模拟

从库事先建立与主库一样的库，导致同步问题的场景
(1)先在从库create database test;
(2)在主库create database test;
(3)主从同步报错

Last_SQL_Error: Error 'Can't create database 'db'; database exists' on query. Default database: 'db'. Query: 'create database db'

1.3.5 故障处理

1.3.5.1 思路1：以主库为基准

从库删除已存在的数据库，重启线程

mysql> drop database test;
mysql> start slave;

1.3.5.2 思路2：以从库为基准，跳过此次复制错误

以下操作是有风险的，除非能够保证故障数据主从一模一样，否则后续会出现问题。最安全的做法就是重新构建主从。把握一个原则,一切以主库为主。

借助第三方工具Percona-toolkit，检查主从数据的一致性，并修复主从不一致的情况。
方法一：

stop slave; 
set global sql_slave_skip_counter = 1;
#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;

方法二：

/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突

1.3.5.3 思路3：重新构建主从

备份恢复+重新构建

1.3.6 防止从库写入的方法

1.3.6.1 设置从库只读

#普通用户只读
mysql> set global read_only=1;
mysql> select @@read_only;
#管理员只读
mysql> set global super_read_only=1;
mysql> select @@super_read_only;

1.3.6.2 加中间件

通过中间件控制写主库、读从库

2 主从延时问题分析及处理

2.1 什么是主从延时

主库发生了操作，从库过了“很久”才跟上来

2.2 主从延时怎么监控

通过Seconds_Behind_Master:0参数，能够得出有或没有延时情况，该值是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp(简写为ts)进行比较，而得到的这么一个差值。

等于0不代表没有延时：因为当主库I/O负载很大或是网络阻塞，io_thread不能及时复制binlog（没有中断，也在复制），而sql_thread一直都能跟上io_thread的脚步，这时Seconds_Behind_Master的值是0，也就是我们认为的无延时，但是，实际上不是。

评估主从延时更加精确的指标是，延时了多少日志量。评估主库执行的日志量，从库执行的日志量的对比。比对主库binlog位置点和从库relay执行到的位置点。

2.3 如何计算延时日志量

比较主库mysql> show master status;的信息和从库cat /data/3308/data/relay-log.info的信息，看position号的差值能知道落后多少字节。

查看主库的binlog文件和position信息

查看从库执relay-log.info中记录的已回放的位置信息

position数值的差为落后的字节数，本例为0。

2.4 主从复制延时原因

2.4.1 主库原因

2.4.1.1 主库外部

网络、硬件配置、主库业务繁忙、从库太多

主库业务繁忙的解决方式：

1. 拆分业务（分布式）：组件分离（数据库与nginx等模块分开部署）、数据库垂直、水平拆分
2. 大事务的拆分：比如1000w的业务，拆分成20次执行

2.4.1.2 主库内部

1. 二进制日志更新问题：如果主库事务执行完后没有马上更新binlog，就会造成延时
   解决方法：配置sync_binlog=1参数
2. 日志传输方式：5.7之前的版本，没有开启GTID之前，主库可以并发事务，但是dump线程传输binlog日志是串行。所以会导致事务量大或大事务时，出现比较严重的延时。
   解决方法：
   • 5.6+版本，手工开启GTID，事务在主从的全局范围内就有了唯一标识
   • 5.7+版本，无需手工开启，系统会自动生成匿名的GTID
   • 有了GTID之后，就可以实现并发传输binlog
   • 但是，即使有了GTID的优秀特性，依然要尽可能减少大事务和锁的影响

2.4.1.3 判断主库传输binlog不及时

1. 主库show master status，查看主库的binlog文件及位置信息
2. 从库show slave status \G或cat master.info，查看从库已经接收的主库binlog文件及位置信息。
3. 比较文件和位置信息的差值，从而得出主库传输binlog不及时

2.4.2 从库原因

2.4.2.1 从库外部

网络、从库配置低、参数设定。

2.4.2.2 从库内部

IO线程：

• 写relay-log -->IO 性能

SQL线程：

• 问题：回放SQL默认在非GTID模式下是串行的
• 解决方案：
  1. 开启GTID
  2. 串行改并行：
     5.6+版本，GTID是database级别，基于不同库进行SQL线程并发。
     5.7+版本，GTID加入了Logic_clock 逻辑时钟，保证了在同库级别下的事务顺序，可以基于不同事务的并发回放。
  3. 即使有了以上优化机制，我们还是要注意对于大事务的处理问题，锁的问题。