mysql5.6,5.7 主从不同步解决办法

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql5.6,5.7 主从不同步解决办法

技术小阿哥 2017-11-27 14:16:00 浏览833
展开阅读全文
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
MySQL Binlog 【ROW】和【STATEMENT】选择
对比Row和Statement:R比S产生的日志量大5.5倍,网卡流量高4~5倍,cpu稍微忙了10个百分点。在复制过程中,从均没有延迟。因为SQL过滤条件WHERE 后面的字段利用好索引,ROW和STATEMENT模式下效果一样。要是没有利用好索引,则:
STATEMENT下:在主上执行(3~5s)一条,从上也是需要这个时间,并且出现延迟。(Seconds_Behind_Master)。本来就单线程的,导致从的可用性更差。
ROW下:在主上执行(3~5s)一条,正常情况下每张表都有主键,所以按照ROW的记录的SQL格式,不会出现对这类sql的延迟。除非极端情况下更新一张没有主键甚至没有任何索引的表。
 
对比发现:在执行此类sql的时候,在STATEMENT下面,(利用好索引)主和从的各个开销都很小,网络流量都不大。而在ROW下面:因为日志产生量就很大,导致在复制期间网卡流量就很大:12M。网卡流量:【1:10000】,日志大小:【1:2000000】,CPU空闲:【80:20】。这个只限于这个例子,看范围大小和表字段的大小。总之在网络和磁盘开销上面比较,他们差距了好几个数量级。
小结2:
    对于更新单条的sql语句,在STATEMENT和ROW下
1,CPU消耗差距不大,都需要执行这么sql。消耗 R=S
2,磁盘写和网络传输上,因为ROW记录的格式的原因。消耗 R>S
3,SQL效率来看,合理利用索引的更新,效率差距不大,不合理利用索引的更新,效率 R>S
4,日志文件大小上,因为都需要记录这么多SQL,但是由于R和S的记录格式不一样,大小 R>S
    对于执行一个大范围的sql语句,在STATEMENT和ROW下
1,CPU上,主上只要执行一条SQL,而从上需要执行N条,消耗 R>S
2,磁盘写和网络传输上,因为ROW记录的格式的原因。消耗R>S,看范围条件,大的话,差距巨大。
3,日志文件大小上,主记录一条,从记录N条,并且还由于R和S的记录格式不一样,R>S,差距巨大。
从上面的分析得出,STATEMENT要比ROW划算。要是使用STATEMENT没有任何问题的话,就推荐使用STATEMENT/MIXED格式记录二进制日志
 
http://www.cnblogs.com/zhoujinyi/archive/2013/01/15/2836131.html?utm_source=tuicool&utm_medium=referral
 
mysql5.7 主从不同步GTID_NEXT
5.6的解决方案
http://suifu.blog.51cto.com/9167728/1845457
end_log_pos 有了它,根据pos值,直接就能找到,找到delete那条数据,反做(变成insert)
 
原因
我在从库上操作了create语句,然后主从不同步了,所以解决办法就是跳过已经执行的sql
 
Last_SQL_Errno: 1050
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction 'b30dcce8-3395-11e6-902b-0050569d58f6:38435158' at master log mysql-bin.001313, end_log_pos 19583512. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
 
解决 
1.master
mysqlbinlog mysql-bin.001313 (end_log_pos 19583512 对应#170314 19:28:02 server id 1  end_log_pos 19583512 CRC32 0xed572feb  Quer)
 
mysqlbinlog mysql-bin.001313|grep -C 10 "end_log_pos 19583512"
 
说明这行有问题
SET @@SESSION.GTID_NEXT= 'b30dcce8-3395-11e6-902b-0050569d58f6:38435158'/*!*/;
# at 19581673
#170314 19:28:02 server id 1  end_log_pos 19583512 CRC32 0xed572feb   Query   thread_id=23586111  exec_time=0 error_code=0
 
2.slave
stop slave;  
set @@session.gtid_next='b30dcce8-3395-11e6-902b-0050569d58f6:38435158';
begin;
commit;
set @@session.gtid_next=automatic;
start slave;
 
3.show slave status\G;
因为拖到早上解决,所以看下落后多少数据
------Master_Log_File: mysql-bin.001319    1
--Read_Master_Log_Pos: 2532411
Relay_Log_Pos: 27411621
------Relay_Master_Log_File: mysql-bin.001313   1(明显不同)
--Exec_Master_Log_Pos: 27411408
Retrieved_Gtid_Set: b30dcce8-3395-11e6-902b-0050569d58f6:38411883-38616860
Executed_Gtid_Set: 9b59f303-3433-11e6-8a48-0050569d2d94:1-146,
b30dcce8-3395-11e6-902b-0050569d58f6:1270-38443937
 
4.所以有必要利用pt工具强制同步
 
4.1
pt-table-checksum h='masterip',u='xx',p='xx',P=3306 --nocheck-replication-filters --replicate=test.checksums --no-check-binlog-format --ignore-databases=mysql --chunk-size-limit=5
 
4.2
pt-table-sync  --execute --replicate test.checksums  --sync-to-master h='slaveip',P=3306,u='xx',p='xx'
 
5.show slave status变化
 
[MySQL FAQ]系列 — MySQL复制中slave延迟监控
Read_Master_Log_Pos: 445167889
Exec_Master_Log_Pos: 445167889
Seconds_Behind_Master: 0
 
好了,最后我们说下如何正确判断SLAVE的延迟情况:
1、首先看 Relay_Master_Log_File 和 Master_Log_File 是否有差异;
2、如果Relay_Master_Log_File 和 Master_Log_File 是一样的话,
再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异,
对比SQL线程比IO线程慢了多少个binlog事件;
3、如果Relay_Master_Log_File 和 Master_Log_File 不一样,那说明延迟可能较大,
需要从MASTER上取得binlog status,判断当前的binlog和MASTER上的差距;
在第三方监控节点上,对MASTER和SLAVE同时发起SHOW BINARY LOGS和SHOW SLAVE STATUS\G
的请求,最后判断二者binlog的差异,以及 Exec_Master_Log_Pos 和 Read_Master_Log_Pos 
的差异。
 
UserParameter=mysql.slave.Relay_Log_Pos
UserParameter=mysql.slave.Exec_Master_Log_Pos
UserParameter=mysql.slave.Read_Master_Log_Pos
 
监控项目
Read_Master_Log_Pos: 5374182
Relay_Log_Pos: 27113212
Exec_Master_Log_Pos: 27112999
 
Relay_Master_Log_File 和 Master_Log_File(需要增加)
1
2
3
4
5
mysql5.7 主从不同步 gtid_purged
set global gtid_purged='xxxx'
MySQL5.6 GTID新特性实践
MySQL5.7杀手级新特性:GTID原理与实战
GTID跳过SQL错误的脚本

参考 http://blog.itpub.net/29510932/viewspace-1736132/

GTID_PURGE() 当同步发生大量的错误时,使用flush table with read lock锁住主库,记录GTID的事务编号(最后那个,

例如后面示例里面的142787),然后数据同步到从库,在参数中加上UUID(空格)起始事务编号(空格)中止事务编号
原理:purge掉master log中,同步数据的SCN之前的事务,从同步时间点以后开始读取binlog; 这样做的好处是不用去master操作,清理binlog(手抖清理了其他东西就不好了~) 


5.6

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v --start-position="594374863" \

binlog.000283 > /XXX/binlog.sql

mysqlbinlog --base64-output=DECODE-ROWS -v --start-datetime="2017-05-02 20:00:00" --stop-datetime="2017-05-03 00:00:00" mysql-bin.001600> /home/back/test.sql

从以上输出中,我们可以知道,从夯住的那个点开始,binlog 记录的信息就出现了异常,可以推测在主库有大操作。另外,针对出现问题库,查看主库和从库的表数量,发现从库的表数量多于主库,有几个临时表出现。可以推测的,主库有删表的操作,从库同步夯住,导致同步异常,主库删表的操作还没来得及同步到从库。

经过和研发沟通,确认了两点。第一,确实有大操作,程序有大量的批量插入,而且是用的 LOAD DATA LOCAL INFILE;第二,主库确实有删表的操作,这几张表都是临时表


slave优化点

slave_parallel_worker


Master_Log_File: mysql-bin.003842

Read_Master_Log_Pos: 15198736

Relay_Master_Log_File: mysql-bin.003842

Exec_Master_Log_Pos: 15198736   

Relay_Log_Space: 15199242


3.2017.12 数据迁移,做多源复制的时候,出现了问题


1236 error

解决办法

去2个主上,show GTID_PURGED

从好像是把两个主PURGED结合,然后SET @@GLOBAL.GTID_PURGED = 'b30dcce8-3395-11e6-902b-0050569d58f6:1-129112350';

再开启主从。




本文转自 liqius 51CTO博客,原文链接:http://blog.51cto.com/szgb17/1906790,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
技术小阿哥
+ 关注