主从故障原因及分析

2021-02-16 | 129 浏览

主从故障原因及分析

1 线程相关监控

1.主库:
show full processlist;
每个从库都会有一行dump相关的信息
HOSTS: 
db01:47176
State:
Master has sent all binlog to slave; waiting for more updates
如果现实非以上信息,说明主从之间的关系出现了问题
从库:
show slave status \G;
db01 [(none)]>show slave status \G
*************************** 1. row ***************************
主库有关的信息(master.info):重点关注
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
2.从库relaylog的信息(已经执行过的relaylog的位置点):重点关注
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
从库两个线程的基本状态:(重点关注)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明:在正常的主从复制关系中,都是YES

3.过滤复制有关的参数
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
最后一次错误的代号及具体的报错信息(了解)
Last_Errno: 0
Last_Error: 
是否自动跳过复制错误(谨慎使用)
Skip_Counter: 0
4.从库和主库延时的秒数:
Seconds_Behind_Master: 0
分别记录的IO和SQL报错的具体信息(重点关注)
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
延时从库的状态
SQL_Delay: 0
SQL_Remaining_Delay: NULL
5.GTID复制有关的状态
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0

2.主从复制故障分析

2.1 IO
1.连接主库
(1) 用户密码 IP port

Last_IO_Error: error reconnecting to master '[email protected]:3307' - retry-time: 10  retries: 7
[root@db01 ~]# mysql -urepl -p123333 -h 10.0.0.51 -P 3307
ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)

原因:

密码错误 
用户错误 
skip_name_resolve

ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (113)
地址错误
端口

处理方法:

stop  slave  
reset slave all 
change master to 
start slave

(2) 主库连接数上线,或者是主库太繁忙

show slave  staus \G 
Last_IO_Errno: 1040
Last_IO_Error: error reconnecting to master '[email protected]:3307' - retry-time: 10  retries: 7

处理思路:

拿复制用户,手工连接一下
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3307 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections

处理方法:

db01 [(none)]>set global max_connections=300;

(3) 防火墙,网络不通

2.2请求二进制日志

主库缺失日志
从库方面,二进制日志位置点不对

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.'

注意: 在主从复制环境中,严令禁止主库中reset master; 可以选择expire 进行定期清理主库二进制日志

解决方案:重新构建主从

3.SQL 线程故障

(1) 读写relay-log.info

(2) relay-log损坏,断节,找不到
(3) 接收到的SQL无法执行

0.SQL_MODE影响

1.要创建的数据库对象,已经存在

2.要删除或修改的对象不存在

3.DML语句不符合表定义及约束时.
归根揭底的原因都是由于从库发生了写入操作.
Last_SQL_Error: Error 'Can't create database 'db'; database exists' on query. Default database: 'db'. Query: 'create database db'
以下是有风险的操作:

处理方法(以从库为核心的处理方案)：
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.可以设置从库只读.
db01 [(none)]>show variables like '%read_only%';

2.加中间件:

4.主从延时监控及原因
主库做了一个变更,从库很久才追上
(1) 主库写binlog不及时 *******
控制binlog从内存写入磁盘的控制开关
每次事务提交都立即刷新binlog到磁盘(双一标准中的其一)
sync_binlog=1
每次事务提交不立即写入磁盘,靠操作系统判断什么时候写入
sync_binlog=0
(2) dump线程多导致的，系统资源压力大
从库越多,压力越大
减少频繁小事务,减少超大事务
group commit  
(3) IO线程阻塞

大事务拆成小事务
事务量大(主库压力大)

(1) group commit可以缓解

(2) 业务的分离和分布式(Mycat,InnoDB Cluster)

(4) SQL线程慢(Classic replication) *****

1.从库默认只有一个SQL线程,从库中的事务都是一个一个串行来执行的
2.主库是可以并行很多个事务
①并发事务数很多
②大事务
都会造成从库延时
5.多线程复制(多sql线程)
有局限性,针对不同库的事务进行并发,在有些情况下可以解决
对于大事务问题,只能在主库方面,将大事务拆成小事务
show slave status\G
(1)
Seconds_Behind_Master: 
(2)
Relay_Log_File: db01-relay-bin.000006
Relay_Log_Pos: 485
db01 [(none)]>show relaylog events in 'db01-relay-bin.000006';
6.小结
传统复制主从延时原因

1 、主库日志写入不及时

2 、dump繁忙 (串行传送日志)

3 、IO线程阻塞(主库大事务,频繁事务,串行接收日志)

4 、SQL线程 (串行的执行事务)

如何监控主从延时:
show slave status \
(1) Seconds_Behind_Master
(2) 对比 从库中:master.info 和relay.info  , show relaylog events in 'db01-relay-bin.000006';
监控延时的日志量
(3)pt-heartbeat

数据库