实施步骤

一、如何监控

[root@db01 data]# mysql -S /tmp/mysql3308.sock -e "show slave status \G"

show slave status 命令常见返回参数状态：

Slave_IO_Running: Yes  # IO线程工作状态： YES、NO、Connecting

Slave_SQL_Running: Yes # SQL线程工作状态：YES、NO

Last_IO_Errno: 0 # IO故障代码：2003,1045,1040,1593,1236...

Last_IO_Error: # IO线程报错详细信息

Last_SQL_Errno: 0 # SQL故障代码：1008，1007，1032，1062..

Last_SQL_Error: # IO线程报错详细信息

二、IO 线程故障

1、连接主库失败分析

1）user、password、IP、Port等连接信息有误

用户密码错误

MySQL主从故障分析及处理_主从


2）主库无法连接：网络、宕机、防护墙、最大连接数上限

主库宕机

MySQL主从故障分析及处理_主从复制_02

3）连接数上限

MySQL主从故障分析及处理_故障处理_03


解决方法：修改登录信息、修改主库连接设置参数。

set global max_cnotallow=1000;

2、主从server_id、server_uuid相同

MySQL主从故障分析及处理_故障处理_04

解决方法：修改从库server_id

set global server_id=1051;

3、请求主库日志失败，主库日志损坏、丢失

解决方法：主从重构

mysql> stop slave;
# 将从库复制信息清空
mysql> reset slave all;
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_USER='repl', MASTER_PASSWORD='密码', MASTER_PORT=3306, master_auto_position = 1; 
mysql> start slave; show slave status\G;

三、SQL 线程故障

SQL线程主要工作是回放relaylog中的日志事件，SQL线程故障原因分析：

1、realy-log 损坏

解决方法（一）
备份主库数据（mysqldump、xbk、clone plugin），恢复至从库，重构主从关系

解决方法（二）

找到SQL回放的realy log位置点，找到主库相应位置点，基于位置点重构主从关系

MySQL主从故障分析及处理_故障分析_05

mysql> stop slave;

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.51',MASTER_USER='repl',MASTER_PASSWORD='123',MASTER_PORT=3307,MASTER_LOG_FILE='mysql-bin.000011',MASTER_LOG_POS=193,MASTER_CONNECT_RETRY=10;

mysql> start slave;

2、执行SQL出问题

1）主从节点配置不一样： 平台、版本、参数、SQL_MODE
解决方法：调整成一致

2）修改的对象不存在（库、表、用户）
从库被写入了，导致数据不一致

3）创建的对象已存在（库、表、用户、约束冲突）
从库被写入了，导致数据不一致

针对从库被写入，主从数据不一致的解决方法如下：
解决方法（一）：部分场景可以使用，只要保证数据以主库为准即可使用

mysql> stop slave;

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

mysql> start slave;

解决方法（二）：配置文件中配置跳过此类型报错，不推荐使用

[root@orch3 ~]#vim /etc/my.cnf
slave-skip-errors = 1032,1062,1007

常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突

解决方法（三）： GTID 处理错误方式：

查看监控信息:show slave status\G;报错如下：

MySQL主从故障分析及处理_主从_06


MySQL主从故障分析及处理_主从故障_07


MySQL主从故障分析及处理_故障处理_08


从库操作：注入一个空事务

mysql> stop slave;

mysql> set gtid_next='befef0d5-3560-11ec-88ce-000c29f624c7:5';

mysql> begin;commit;

mysql> set gtid_next='AUTOMATIC';

mysql> start slave;

总结： SQL线程故障规避方法
1、从库设置只读（read_only、super_read_only），可配合使用读写分离中间件（mycat、ProxySQL）。
2、不使用双主结构；PXC、MGR替代。
3、可使用pt相关工具校验主从数据，并同步。


情况一：从库宕机

如果在从库复制数据过程中，宕机了。很可能造成relay log 损坏。无法继续进行复制。启动复制时报错：

relay log read failure

遇到这种情况，有如下几个解决版本：

开启了 crash-safe replication

MySQL 5.6以后，可以在从库配置以下两个参数,可以自动恢复这个问题

relay_log_info_repository=table
relay_log_recovery=1

开启了GTID

如果开启了GTID，直接在从库

stop slave;
reset slave;
start slave;

没有开启GTID

首先使用show slave status;查看已经复制到的位置

show slave status;
    Relay_Master_Log_File: mysql-bin.000008
    Exec_Master_Log_Pos: 75

然后再CHANGE MASTER TO

stop slave;
change master to 
    master_log_file=relay_master_log_file,
    master_log_pos=exec_master_log_pos,
    ....;

情况二：复制中断

1062错误

从库上出现写入数据，出现1062错误

复现错误

  • master
use zst;
create table zst_1(
    id int not null,
    uname varchar(32),
    primary key (id)
);
insert into zst_1(id, uname) values(1, 'wubx),(2, 'mysql');
  • salve
set sql_log_bin=0;
insert into zst_1(id, uname) values(3, 'python');
  • master
---从库操作完之后，再执行下面语句---
insert into zst_1(id, uname) values(3, 'java');
  • show slave status
Could not execute Write_rows event on table zst.zst_1; 
Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; 
handler error HA_ERR_FOUND_DUPP_KEY；
the event's master log mysql-bin.000001, end_log_pos 1390

解决办法

思路：

  • 无序列表在日志中记录了出现问题这条记录所在的binlog：
binlog 文件（event's master  log mysql-bin.000012）
记录的结束位置（end_log_pos 2072）
  • slave status 信息中可以找到这条记录的开始位置：
    再结合 show slave status 信息中的记录的已经执行到的位置（Exec_Master_Log_Pos: 1975）。
  • 使用mysqlbinlog 工具 从 binlog 中抓取这条记录
mysqlbinlog -v --base64-output=decode-rows --start-positon=1765 
--stop-position=2072 mysql-bin.000012
  • 在从库上生成这条记录（只需要插入非空字段就可以）
    插入记录时，只插入非空字段就可以。因为row格式的binlog 中， 会记录这条记录的全部字段值。从库在根据主库binlog复制这条记录时，会根据binlog中的记录，恢复全部字段的数据
set sql_log_bin=0;
insert into zst_1(id) values (3);
set sql_log_bin=1;
  • 从库启动sql_thread 线程
start slave sql_thread;

删除记录出现1302错误

以上是以update更新记录作为例子的，如果主库删除了一条从库不存在的记录时。解决方法有一下几种：
1、和update 采用一样的方式，人工补齐数据，再重新开始复制
2、在从库跳过这个事务，又分为两种情况：
如果没有开启GTID：

stop slave sql_thread;
set global skip_sql_slave_conter=1;
start slave sql_thread;

如果开启了GTID，需要在从库模拟空事务

stop slave sql_thread;
set gtid_next='master_server_uuid:gtid';   #出问题的这个事务的GTID
begin;commit;     #人为的空事务，因为set gtid_next 中明确指定了下一个gtid，所以虽然这个事务是在从库上产生的，
                    #但是更新的却是 gtid_next参数中指定的那个GITD（也就是主库的GTID）
select @@gtid_next   # 查看下一个GTID
set gtid_next='automatic';     #将gtid改回 为  自动
start slave sql_thread;

1236错误

从库想要获取的GTID，已经不在主库的binlog中了

复现错误

  • salve
stop slave;
  • master
use zst;
create table zst_1(
    id int not null,
    uname varchar(32),
    primary key (id)
);
insert into zst_1(id, uname) values(1, 'wubx);
flush logs;
insert into zst_1(id, uname) values (2, 'mysql');
flush logs;
purge master logs to 'mysql-bin.000002';
  • slave
start slave

这时候，slave会向master获取mysql-bin.000001 和 mysql-bin.000002中的GTID 数据。但是因为主库上已经没有了这两个binlog。从库就会报错 1236

解决办法

1、重建从库，从新从主库复制数据
2、忽略掉主库已经不存在的GTID， 从库只应用主库中还存在的binlog中的事务（不建议这么做，如果从库可以下线的话，建议采用从建从库的方式）
两种方式
1、如果相差的事务不是很多

#需要暂停从库的服务
set gtid_next='xxxx:n';
begin;commit;
set gtid_next='xxxxxx:n+1';
begin;commit;
#然后在使用 pt-table-checksum 和 pt-table-sync工具对数据进行校验.
#花费时间较多

2、如果相差的事务稍微多一点

#需要暂停从库的服务
stop slave;
reset master;
set global gtid_purged='xxxxxxxx:1-28';   #此值可以查询主库的 gtid_purge 参数获取
start slave
#然后在使用 pt-table-checksum 和 pt-table-sync工具对数据进行校验。
#花费时间较多**

情况三：复制延迟

mysql >show slave status;
    Seconds_Behing_Master: 193 # 从库复制相较于主库的落后的时间

排查思路

1、查看sql_thread 在执行什么；
在show slave stauts返回结果中，
Relay_Masster_Log_File 和 Exec_Master_Log_Pos 的结果表示从库最后应用的事务的位置。比如Relay_Masster_Log_File 为 mysql-bin.000013, Exec_Master_Log_Pos 为 3834
在主库上查看这个位置之后的事务，就是从库当前在执行的事务和sql。
使用如下命令解析主库binlog：

mysqlbinlog -v \
    --base64-output=decode-rows \
    --start-position=3834 mysql-bin.000013 > 13.sql

或者在主库执行

show binlog events in 'mysql-bin.000013' from 3834 limit 10;

可能的情况

主库上执行了大事务，并且 这张表上没有索引。
解决办法：

停掉从库
在从库上为这张表建索引
重启打开复制
找时间为主库上的表建索引

另外，还可以检查主库是否开启了binlog group commit 特性, 从库是否开启了writeset 特性
如果还是很慢，可以临时修改从库的以下参数，让从库跑的更快一点

#########当从库总是落后主库时，可以考虑配置以下两个参数
innodb_flush_log_trx_commit=2
sync_binlog=0

其它解决办法：

以下方法存在一定风险， 建议根据实际情况评估之后，在决定是否采用

如果对业务非常了解，也知道了造成当前延迟的具体语句。
也可以先停掉复制，然后在从库上跳过慢的GTID，然后手动执行被跳过的事务。最后再从新开始复制

另外一个办法。因为binlog format 格式为 row时，批量删除和更新操作会按照表中数据，
一条一条的记录在binlog中（
    例如，主库中执行的 delete from tb1,记录在binlog中就会变成
    delete from tb1 where @1=2 @2='abc2';
    delete from tb1 where @1=3 @2='bcd';
）。
也就是主库上的批量操作，在从库应用时会变成一条一条执行。针对这种情况，
在主库批量操作前，可以临时修改format的格式为statement格式。

如何避免复制延迟

主库是开启binlog group commit 特性, 从库是开启了writeset 特性
如果还是很慢，可以临时修改从库的以下参数，让从库跑的更快一点

#########当从库总是落后主库时，可以考虑配置以下两个参数
innodb_flush_log_trx_commit=0
sync_binlog=0

备注：等追上之后改回双1.


1.获取binlog错误

1.1排查方法

执行SHOW SLAVE STATUS\中的Last_SQL_Error查看报错信息。其中：

Slave_IO_Running: No
Slave_SQL_Running: No
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

1.2可能原因

binlog 已经被删除了，或者起始位置设置错误。
复制用户、密码错误，主从库之间网络错误。

1.3解决方法

回复主库的最新备份，并且在已知的日志文件位置，搭建主从关系：

mysql>stop slave;
mysql>reset slave;
mysql>change master to master_user=‘root’,master_password=‘1234’,master_host=‘118.31.127.00’,master_log_file=‘binlog.000010’,master_log_pos=10;

如果启动了gtid，则使用：

mysql>stop slave;
mysql>reset slave;
mysql>change master to master_user=‘root’,master_password=‘1234’,master_host=‘118.31.127.00’ ,MASTER_AUTO_POSITION = 1;

2.修改用户名，密码，主机地址正确后，重新开启主从：

mysql>start slave;

回放relay log错误:

2.1排查方法:

执行SHOW SLAVE STATUS\中的Last_SQL_Error查看报错信息。

Slave_IO_Running: Yes
Slave_SQL_Running: NO

2.2可能原因:

回放位置记录错误
从库数据被?人为修改过导致的
常见错误ERROR 1062、1008、1034、1146：

ERROR-- 1062
MySQL error code 1062 (ER_DUP_ENTRY): Duplicate entry ‘%-.192s’ for key %d
ERROR-- 1008
MySQL error code 1008 (ER_DB_DROP_EXISTS): Can’t drop database ‘%-.192s’; database doesn’t exist
ERROR-- 1032
MySQL error code 1032 (ER_KEY_NOT_FOUND): Can’t find record in ‘%-.192s’
ERROR-- 1594
MySQL error code 1594 (ER_SLAVE_RELAY_LOG_READ_FAILURE): Relay log read failure: %s

2.3解决方法

传统模式下，手动清除或补充缺少的数据，跳过这个报错：

mysql> stop slave;
mysql> set sql_log_bin=0
mysql>//补充这条数据或者删除这条数据
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

GTID模式下，手动清除或补充缺少的数据，跳过这个报错：

mysql> stop slave;
mysql> set sql_log_bin=0
mysql>//补充这条数据或者删除这条数据
mysql> set GTID_NEXT=‘0f869100-71d1-11e7-be5e-000c29f2e72e:14’;
//这个值在show slave status中查看”对应的GTID”:”GTID的:后的以后的位置值”
mysql> begin;
mysql> commit;
mysql> set GTID_NEXT=‘AUTOMATIC’;
mysql> start slave;

如果依然有报错，可以使用mysqlbinlog工具分析主库对应的binlog，找到问题根源：

mysqlbinlog —no-defaults —base64-output=decode-rows -vv mysql-bin.000033> mysql-bin.000033
cat mysql-bin.000033 |grep binlog_position -A 5 -B 20

3.延迟

3.1排查方法

执行SHOW SLAVE STATUS\中的Seconds_Behind_Master、Master_Log_File、Relay_Master_Log_File 中：

Slave_IO_State: Waiting for master to send event
Master_Host: 10.168.99.10
Master_User: repl
Master_Port: 3308
Master_Log_File: mysql-bin.000017
Relay_Master_Log_File: mysql-bin.000017
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Last_Error:
Seconds_Behind_Master: 0

3.2可能原因

1.存在带宽低，binlog传输速率慢。

2.大事务造成的SQL执行速度慢。

3.无主键造成的SQL执行效率低 。

3.3解决方法

1.增加带宽，提高网络通信效率。

2.保证每个表都存在主键，提高SQL执行效率，有主键组织的表可以有效提高SQL的执行效率，提高从库的SQL执行速率。

3.提高SQL的质量，提高从库的SQL执行速率。