|
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，如需转载请自行联系原作者
