1、主从复制监控
主库监控
#监控主库 Binlog Dump线程# 每个从库都有一行信息mysql -e "show processlist" | grep "Binlog Dump"#监控从库端口, UUID信息mysql -e "show slave hosts"
从库监控
show slave status\G
2、线程相关监控信息
2.1、主库相关监控信息
#主库连接信息、binlog位置信息 (master.info)
Master_Host: 127.0.0.1
Master_User: backup
Master_Port: 3316
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
2.2、从库相关监控信息
#从库中relay-log的回放信息 (relay-log.info)
Relay_Log_File: db01-relay-bin.000006
Relay_Log_Pos: 320
# relay-log 对应的 binlog位置点
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 444
2.3、线程监控信息：主要用来排查主从故障
show slave status\G
#显示信息↓↓↓
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#IO线程错误代码
Last_IO_Errno: 0
#IO线程完整报错
Last_IO_Error:
#SQL线程错误代码
Last_SQL_Errno: 0
#SQL线程完整报错
Last_SQL_Error:
2.4、过滤复制相关信息
#默认情况是全库复制
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
2.5、落后于主库的秒数
Seconds_Behind_Master: 0
2.6、延时从库状态信息
#人为配置的延迟复制信息
SQL_Delay: 0
SQL_Remaining_Delay: NULL
2.7、GTID复制信息
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
3、 日志文件 position相关信息
3.1、IO线程 已经获取到的主库BinLog的位置点 (master.info)
#作用: IO下次请求日志时, 起点位置
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
3.2、SQL线程 回放到的relay-log位置点 (relay-log.info)
Relay_Log_File: db01-relay-bin.000006
Relay_Log_Pos: 320
3.3、SQL线程 回放的realy-log位置点, 对应的主库BinLog的位置点 (relay-log.info)
#作用: 计算主从复制延时日志量
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 600
4、主从复制故障分析
4.1、如何监控
mysql> show slave status\G
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
Last_SQL_Error: : SQL线程报错详细信息
4.2、IO线程故障
4.2.1、连接主库失败 (连接错误)
#连接报错通过不会记录到错误日志
1. 连接信息错误: 网络、防火墙、最大连接数上线、主库宕机
2. 验证信息错误: IP、port、user、password、加密插件(native)
#连接错误排查方法 (登录主从复制用户)
mysql -ubackup -p123 -h 127.0.0.1 -P 3316
(1). 主库宕机
#主库
systemctl stop mysql
#从库
mysql> show slave status\G
Slave_IO_Running: Connecting
Last_IO_Errno: 2003
Last_IO_Error: error reconnecting to master 'backup@127.0.0.1:3316' - retry-time: 10 retries: 1 message: Can't connect to MySQL server on '127.0.0.1' (111)
#还原主从关系
主库
systemctl start mysql
从库
mysql> start slave;
(2). 模拟用户密码错误
Slave_IO_Running: Connecting
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master 'backup@127.0.0.1:3316' - retry-time: 10 retries: 1 message: Access denied for user 'backup'@'localhost' (using password: YES)
(3). 模拟端口号错误
Slave_IO_Running: Connecting
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'backup@127.0.0.1:3306' - retry-time: 10 retries: 2 message: Can't connect to MySQL server on '127.0.0.1' (111)
(4). 最大连接数上限
#主库
#mysql会为 root本地管理员用户预留一个连接
mysql> set global max_connections=0;
max_connections=200
#从库
Slave_IO_Running: Connecting
Last_IO_Errno: 1040
Last_IO_Error: error connecting to master 'backup@127.0.0.1:3316' - retry-time: 10 retries: 1 message: Too many connections
#还原主从关系
主库
mysql> set global max_connections=200;
vim my.cnf
max_connections=200
从库
stop slave;
start slave;
4.3、请求二进制日志错误
报错现象: IO线程为No, 会记录错误日志
主库binlog损坏, 缺失
主从 server_id, server_uuid 相同
(1). 模拟主从server_id相同
1. 从库修改 server_id 与 主库相同
mysql> set global server_id=3316;
# IO线程是长连接: 需要重启才会重新连接
mysql> stop slave;
mysql> start slave;
2. 从库报错信息
Slave_IO_Running: No
Last_IO_Errno: 13117
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work
(or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
3. 解决方案
mysql> set global server_id=3317;
mysql> stop slave;
mysql> start slave;
(2). 模拟主库binlog损坏
1. 主库
mysql> show master status;
mysql-bin.000004 | 156
#重置主库binlog日志
mysql> reset master;
mysql> show master status;
mysql-bin.000001 | 156
2. 从库
Relay_Master_Log_File: mysql-bin.000004
Exec_Master_Log_Pos: 156
Slave_IO_Running: No
Last_IO_Errno: 13114
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.000004' at 156, the last event read from '/data/3316/logs/mysql-bin.000004' at 125, the last byte read from '/data/3316/logs/mysql-bin.000004' at 156.'
3. 解决办法: 数据一致, 停止主从关系, 清空主从同步信息, 重新做主从复制
主从数据处理一致
从库停止所有线程
mysql> stop slave;
将从库复制信息清空: relay-log、relay-log.info、master.info
mysql> reset slave all;
重新做主从
CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_PORT=3316,
MASTER_USER='backup',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=156,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
mysql> show slave status\G
4. 生产中要注意什么 (主从数据不一致时)
重构主从
(1). 备份恢复
(2). change master to ...
(3). start slave;
4.4、SQL线程故障
relay-log 损坏 (机率小)
#处理方法: 重构主从
方法一: 备份恢复: change master to ... , start slave;
#缺点: 简单、恢复时间长
方法二: 通过 slave status\G; 找到SQL线程 当前 回放到什么位置了
#前提: 主库binlog 完整
#缺点: 稍微复杂
Relay_Log_File: db01-relay-bin.000006
Relay_Log_Pos: 320
# relay-log 对应的 binlog位置点
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 444
方式三: 重构主从
change master to mysql-bin.000001 - 444
执行SQL出问题
场景一: 主从节点配置不一样: 平台、版本、配置参数、sql_mode
在生产中修改配置要同步进行, 减少差异
场景二: 修改的对象不存在 (库、表、用户)
原因: 从库被人为操作: 误删除了: 错误代码: 1108
模拟从库误删除
#从库
mysql> drop database jjq;
#主库
mysql> drop database jjq;
#从库
Last_SQL_Errno: 1008
Last_SQL_Error: Error 'Can't drop database 'jjq'; database doesn't exist' on query. Default database: 'jjq'. Query: 'drop database jjq'
#解决方法
方式一: 主从数据恢复一致, 重构主从
方式二: 跳过错误
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
创建的用户已存在 (库、表、用户、约束冲突)
原因: 从库被写入了: 错误代码: 1007
模拟从库被被写入:
#从库
mysql> create database db01 charset utf8mb4;
#主库
mysql> create database db01 charset utf8;
#从库
mysql> show slave status\G;
Slave_SQL_Running: No
Last_Errno: 1007
Last_Error: Error 'Can't create database 'db01'; database exists' on query. Default database: 'db01'. Query: 'create database db01 charset utf8'
解决办法:
方式一: 停止主从同步线程, 手工删除从库 已存在的db01库, 启动主从同步
mysql> stop slave;
mysql> drop database db01;
mysql> start slave;
方式二: 跳过错误 (不推荐), 针对 1007 错误报错, 需要做评估是否跳过 (生产中以主库为准)
mysql> stop slave;
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;
4.5、常见错误代码
- 1007:对象已存在
- 1032:无法执行DML
-
4.5.1、解决办法
部分场景可用
mysql> stop slave;
mysql> drop database db01;
mysql> start slave;不推荐的方法
vim my.cnf
slave-skip-errors = all
slave-skip-errors = 1032,1062,10075、总结: 生产中规避SQL线程错误
给开发人员从库查询权限, 读写分离中间件
- 不适用双主结构, PXC、MGR替代双主结构
- 半同步模式、增强半同步等、替代异步复制 (提高主从一致性问题)
6、主从延时监控及原因
6.1、监控方法
方式一
#不能完全相信此参数 mysql -e "show slave status\G" | grep "Seconds_Behind_Master" Seconds_Behind_Master: 0 # 延迟秒数方式二
```通过pos对比
查看主库的日志量 show master status;
查看从库已经拿到的日志量 mysql -e “show slave status\G” | grep “Master_Log”
已经拿到的主库的binlog日志量
Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 156
查看从库已经执行到的日志量
已经执行的binlog日志量
Relay_Master_Log_File: mysql-bin.000002 Exec_Master_Log_Pos: 156
<a name="0iTTf"></a> #### 方式三pt-heartbeat工具监控
<a name="hqDtf"></a> ## 6.2、导致延时的主要原因 <a name="5pTUG"></a> ### 6.2.1、外部因素- 网络延时 (网卡绑定)
- 主从硬件设备, 配置文件参数不一致
- 从库太多
- 主库压力大
mysql> select @@sync_binlog; 参数说明: 1: 每次事物提交都立即刷新binlog到磁盘 (数据安全) 0: 由操作系统确定, 什么时候刷新到磁盘 (提升性能) 大于1: 提交多少个事物, 刷新到磁盘<a name="zgeq8"></a> ### 6.2.2、主库导致的延迟 <a name="rdZAE"></a> #### binlog 记录不及时
建议: 对主从实时性要求比较高时, 设置 sync_binlog=1
<a name="EqYu1"></a>
#### Binlog Dump 线程串行工作
串行工作模式: 保证执行顺序, 缺点: 对于并发事物没有全局唯一标识 大事物、并发事务高、DDL 并发事物 主从延时解决办法: GTID模式复制, Binlog Dump 在传输日志的时候可以支持并发 5.6 版本加入了GTID复制模式, 需要手工配置, Binlog Dump 在传输时可以并发 5.7 版本GTID做了增强, 没有开启GTID 也自动维护匿名GTID信息 大事务 主从延迟解决办法 将一个大事物, 拆分成多个小事务执行 例如: 更新 100w条数据, 分10次更新, 每次10w条 DDL操作 主从延时解决办法 停止主从同步、主库修改完、在从库手动执行、重新同步
<a name="wp4Dm"></a>
#### 怎么判断是主库导致的延迟
主库: mysql -e “show master status;” mysql-bin.000002 | 156
从库: mysql -e “show slave status\G” | grep “Master_Log” Relay_Master_Log_File: mysql-bin.000002 Exec_Master_Log_Pos: 156
<a name="YJ1Fn"></a>
### 6.2.3、从库导致主从延时的原因
<a name="OrfuO"></a>
#### 排查主从延迟方法
已经执行的主库日志量 (relay-log.info): 判断回放有没有延时 Relay_Master_Log_File: mysql-bin.000004 Exec_Master_Log_Pos: 141847
<a name="8sqqO"></a>
#### IO线程
从库IO比较慢, relay-log慢, 推荐将relay-log放到SSD上
relay_log_purge 线程会自动清理relaylog mysql> show variables like ‘%relay%’;
<a name="8qJUX"></a>
#### SQL 线程
主库可以并行事务，从库SQL线程串行回放。<br />所以：并发事务高、大事务、DDL
<a name="89GLn"></a>
### 6.2.4、解决方法
5.6 版本: 开启GTID后，可以多SQL线程，只能针对不同的库的事务进行并行SQL恢复<br />5.7 版本: 做了增强，基于逻辑时钟的并行回放 (MTS)<br />5.7 的从库并发配置方法:
gtid_mode=ON enforce_gtid_consistency=ON log_slave_updates=ON slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 master_info_repository=TABLE relay_log_info_repository=TABLE
<a name="S92WK"></a>
# 7、降低主从复制延迟
参考博客: [https://www.jianshu.com/p/6f25002db226](https://www.jianshu.com/p/6f25002db226)
解决方法: 从库多线程并行复制
说明: 主库是默认多线程并行工作的, 5.5版本从库默认chuan’xing ```
若有收获，就点个赞吧
0 人点赞
