走向DBA之主从复制(监控及故障处理)
2019-06-30 本文已影响0人
国王12
52为主库，51，53为从库
查看主库(52)线程
db02 [(none)]>show processlist;
+----+------+-----------------+------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+------------------+------+---------------------------------------------------------------+------------------+
| 3 | repl | 10.0.0.53:19273 | NULL | Binlog Dump GTID | 2225 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 63 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 65 | repl | 10.0.0.51:8447 | NULL | Binlog Dump | 466 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+------+-----------------+------+------------------+------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
#从库Command列出现binlog dump为正常
查看从库状态(拿从库51举例)：
db01 [(none)]>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.52 主库IP
Master_User: repl 复制用户名(连接主库的用户)
Master_Port: 3306 主库的端口
Connect_Retry: 10 断链之后重试次数
Master_Log_File: mysql-bin.000004 已经获取到binlog的文件名
Read_Master_Log_Pos: 827 已经获取到的binlog位置号
Relay_Log_File: db01-relay-bin.000004 从库已经运行过的relaylog的文件名
Relay_Log_Pos: 472 从库已经运行过的relay的位置号
Relay_Master_Log_File: mysql-bin.000004 主库当前最新binlog文件名
Slave_IO_Running: Yes 从库IO线程状态
Slave_SQL_Running: Yes 从库SQL线程状态
省略若干行
#看到Slave_IO_Running,Slave_SQL_Running状态为yes，即代表主从状态正常
一、IO线程故障：
分析：IO线程干了啥：
1.连接主库
2.请求新的binlog
3.写relaylog
4.更新master.info
IO线程故障具体解决：
1.连接主库连接不上 ：
状态：
Slave_IO_Running: Connecting
原因：
网络不通
防火墙
IP不对
port不对 端口
用户，密码不对
链接数上限
skip_name_resolve
处理思路：
手动连接测试，看看是哪一块出的问题，修改change master to 信息即可
举例：
[root@db01 ~] mysql -urepl -p123 -h 10.0.0.51 -P 3308 --->端口问题
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'db01' is not allowed to connect to this MySQL server
解决(51从库操作)：
mysql> stop slave; 停止主从复制
mysql> reset slave all; 关闭主从复制
重新录入连接主库的信息
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=194,
MASTER_CONNECT_RETRY=10;
注意：binlog文件名和position号可在主库使用show master status \G查看
还需要把数据差补上
mysql> start slave; 启动主从
状态：
IO线程NO
Slave_IO_Running: NO
原因：
从库信息
日志名不对
Master_Log_File: mysql-bin.000001 已经获取得到binlog的文件名
Read_Master_Log_Pos: 444 已经获取得到binlog的位置号
把这些号和备份信息的号对比，看看对不对
日志损坏
比如：主库 reset master; 就是主库清空日志了，从001开始了，日志号对不上了从库比主库日志还新呢
解决：
关闭从库的主从状态，重新开启，修改change master
stop slave;
reset slave all ;
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=675,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
注意，既然主库日志从1开始了，那么position号要从154开始同步，因为154之前是默认开头，不用同步
日志不连续
server_id 重复 解决：重亲录入，方法在上边
二、SQL线程故障
分析：SQL线程都干啥了?
读relay-log.info
读delay-log，并执行日志
更新relay-log.info
以上文件损坏，最好是重新构建主从
为什么一条SQL语句执行不成功？
1.主从数据库版本差异较大
2.主从数据库配置参数不一致（如：sql_mode等）
3.想要创建的对象已经存在
4.想要删除或修改的对象不存在
5.主键冲突
6.DML语句不符合表定义约束
从库被操作了
解决：万能方法：
设置从库只读，防止写入。
做成多谢分离结构
三、主从延时原因分析
（被动）从库延时主库的时间:
Seconds_Behind_Master: 0 从库延时主库的时间（秒为单位）
3.1主库方面造成主从延时:
1.语句执行之后，日志写入不及时
sync_binlog=1； 即表示语句已提交，立即刷写磁盘，5.6版本以后
2.主库并发业务较高
硬件升级
分布式架构
3.从库太多
不要太多从库
4.对于传统的复制，(Classic Replication)，主库是有能力并发运行事务的，但是
在Dump_T在传输日志的时候，是以事件为单元传输日志的，所以导致事务的传输工作是串
行方式的，这时在主库TPS很高时，会产生比较大的主从延时
处理：从5.6卡开始加入了GTID，在复制时，可以将原来串行的传输模式变成并行的。
除了GTID的支持，还需要双一来保证
这种工作模式叫做 group commit
在同一时刻并发的事务，可以一起传输。
3.2从库方面造成主从延时
对于传统的复制，(Classic Replication)
SQL线程只有一个，所以只能串行执行relay的事务
解决：多加几个SQL线程
在5.6中出现了多线程SQL(只能针对不同库下才能并发)基于databases级别的多线程SQL
到5.7中加入了MTS，才真正实现了事务级别的并发SQL线程
四、延时从库(手工配置的)
4.1数据损坏的原因：
物理损坏
磁盘坏了，服务器炸了等等
逻辑损坏
drop，内部删除了
对于传统的主从复制，比较擅长处理物理损坏，不擅长逻辑损坏。
4.2设计理念：
对SQL线程进行延时（该传日志传日志，该收日志收日志，晚点执行就行了）
4.3延时多久合适？
一般：3-6小时，主要还是根据当从发现删除操作到运维人原反应解决的时间。
4.4如何设置延时？(从库设置)
mysql>stop slave; 关闭主从
mysql>CHANGE MASTER TO MASTER_DELAY = 300; 设置延时间
mysql>start slave; 启动主从
mysql> show slave status \G 查看状态
SQL_Delay: 300 延时三百秒
SQL_Remaining_Delay: NULL 还有多久执行
4.5如何使用延时从库？
4.5.1故障模拟：
mysql -S /data/3307/mysql.sock
create database delay charset utf8mb4;
use delay;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay;
commit;
4.5.2发现了drop
思路：
1.停止SQL线程，停止主从业务
2.模拟SQL手工恢复relaylog到drop之前的位置点
3.截取relaylog日志（找到起点和终点）
起点：relay-log.info记录了上次SQL线程最后执行的位置点
终点：drop操作前
4.恢复截取日志，验证数据可用性。
4.5.3处理：
1.停从库的SQL线程
mysql> stop slave sql_thread;
2. 查起点和终点 mysql> show slave status \G
Relay_Log_File: mysql52-relay-bin.000002 停线程时文件名
Relay_Log_Pos: 320 postion号名
000002文件320为起点
show relaylog events in 'mysql52-relay-bin.000002'; 找终点（drop前）
4.5.4截取日志
mysqlbinlog --start-position=320 --stop-position=992 /data/3308/data/mysql52-relay-bin.000002>/tmp/relay.sql
起点和终点都找到了，进行备份
4.5.5恢复数据
mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql;
mysql> set sql_lob_bin=1;
4.5.6将从库替换为主库，对外服务
stop slave; 停止主从
reset slave all; 关闭主从
将业务引向这台服务器
4.5.7补偿原主库数据和新主库保持一致并重新构建主从
上课的解决方法：
把从库多出来的那个库删掉
stop slave; 停掉主从
reset slave all; 关闭主从
mysql> show master status; 查看主库的position号和日志文件名
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10; 信息修改够重新建立主从
mysql> start slave; 启动主从
五、过滤复制
应用场景：
把某一项业务或某一个或多个库的数据复制在一台服务器上时，需要过滤复制
从主库设置(了解)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
binlog_do_db 白名单
binlog_ignore_db 黑名单
写到配置文件即可
从从库下手（常用）
是在SQL线程进行回放日志时，进行控制日志，即日志该接收接收，当SQL线程执行时进行过滤，匹配则执行，不匹配则不执行
show slave status \G
Replicate_Do_DB: 库级别的白名单
Replicate_Ignore_DB: 库级别的黑名单
Replicate_Do_Table: 表级别的白名单
Replicate_Ignore_Table: 表级别的黑名单
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
举例：
只需要复制xyz库的数据到从库
解决：
只需要把replicate_do_db=xyz 写到从库的my.cnf
若果想过滤多个库，那么在配置文件写多行即可。如：
replicate_do_db=xyz
replicate_do_db=abc
replicate_do_db=jqx
然后重启即可
查看：
show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: xyz
