情况一:从库宕机
如果在从库复制数据过程中,宕机了。很可能造成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.