图片 17

广大的谬误及解决方案,MySQL复制最好实施

图片 1

数据不一致的原因

我们平台中上线了大量的MySQL业务,但是每个MySQL业务中主从数据库之间的一致性,我们基本不可知,而且由于性能原因的考虑,我们99%左右的MySQL中的两个影响数据一致性的参数innodb_flush_log_at_trx_commit设置为1,并且sync_binlog设置为0。这样,一旦出现机器重启时,MySQL数据库的主从节点之间就会存在数据不一致性的情况。

图片 2

一、错误日志解析:

一致性检测原理

数据不一致性出现以后,一方面会影响业务,另外一方面DBA经常会收到告警:数据库同步错误。为了解决这个问题,我们要实现平台级的数据库一致性对比功能,在平台中定期对必要的业务进行定期的一致性检测和数据修复就非常有必要性了。

所以最近研究了一下MySQL的一致性对比和修复工具:pt-table-checksum和pt-table-sync。下面分别对pt-table-checksum和pt-table-sync的原理做一下简单介绍。

图片来自网络

(1) 【ERROR】1452:无法在外键的表插入参考主键没有的数据

pt-table-checksum的原理:

  1. 校验表的结构如下:

CREATE TABLE checksums (
db char(64) NOT NULL,
tbl char(64) NOT NULL,
chunk int NOT NULL,
chunk_time float NULL,
chunk_index varchar(200) NULL,
lower_boundary text NULL,
upper_boundary text NULL,
this_crc char(40) NOT NULL,
this_cnt int NOT NULL,
master_crc char(40) NULL,
master_cnt int NULL,
ts timestamp NOT NULL,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;

  1. 把数据进行分块,先计算第一个块的数据的校验和,并写入到校验表中的this_crc和this_cnt字段中,这条检核和的SQL语句会传递到从库,从库会收到后计算自己的SQL语句;
  2. 然后读取主库的中校验表的this_crc和this_cnt字段数据,生成SQL语句更新checksums表中的master_crc和master_cnt字段,从库收到SQL语句以后,会把从库中的checksums表的master_crc和master_cnt字段进行更新,这样在从库中就得到了主库的校验和以及自己的数据校验和;
  3. 轮询整张表,重复2,3步把所有的块计算完成;
  4. 通过下面这条语句就可以得出主库和从库的各个块是否一致select * from
    checksums where master_cnt <>this_cnt OR master_crc
    <> this_crc OR ISNULL(master_crc) <>ISNULL(this_crc)

文/Bruce.Liu1

 

pt-table-sync的原理:

  1. 对每一个从库,每一个表,循环进行如下校验和修复过程。
  2. 对每一个chunk,在校验时加上for
    update锁。一旦获得锁,就记录下当前主库的show master status值。
  3. 在从库上执行select
    master_pos_wait()函数,等待从库sql线程执行到show master
    status得到的位置。以此保证,主从上关于这个chunk的内容均不再改变。
  4. 对这个chunk执行checksum,然后与主库的checksum进行比较。
  5. 如果checksum相同,说明主从数据一致,就继续下一个chunk。
  6. 如果checksum不同,说明该chunk有不一致。深入chunk内部,逐行计算checksum并比较(单行的checksum的比较过程与chunk的比较过程一样,单行实际是chunk的size为1的特例)。
  7. 如果发现某行不一致,则标记下来。继续检测剩余行,直到这个chunk结束。
  8. 对找到的主从不一致的行,采用replace
    into语句,在主库执行一遍以生成该行全量的binlog,并同步到从库,这会以主库数据为基准来修复从库;对于主库有的行而从库没有的行,采用replace在主库上插入(必须不能是insert);对于从库有而主库没有的行,通过在主库执行delete来删除(pt-table-sync强烈建议所有的数据修复都只在主库进行,而不建议直接修改从库数据;但是也有特例)。
  9. 直到修复该chunk所有不一致的行。继续检查和修复下一个chunk。
  10. 直到这个从库上所有的表修复结束。开始修复下一个从库。

文章大纲

  1. 复制概述
    1.1. 复制基本概念
    1.2. 常见复制结构
    1.3. 主从复制原理
    1.4. 主从复制模式
  2. 复制最佳实践
    2.1. 双主复制部署
    2.2. 分发、级联复制部署
    2.3. 分发、级联结构调整
    2.4. 复制常见错误处理
  3. 复制过滤应用
    3.1. 复制参数介绍
    3.2. 复制过滤最佳实践
    3.3. 复制状态、延迟监控
  4. 半同步复制
    4.1. 半同步复制原理
    4.2. 半同步复制最佳实践

 

平台一致性对比方案

有了这两个一致性对比工具,就很容易在平台中搭建一致性对比和数据修复了,我们在选择一个业务进行对比,通过指令下发就能够对业务进行一致性对比,并从平台中查询对比的进行和最终对比的结果,依据对比结果,决定是否进行数据修复。

1.复制概述

MySQL内建的复制功能是构建大型,高性能应用程序的基础。将MySQL的数据分布到多个系统上去,这种分布的机制,是通过将MySQL的某一台主机的数据复制到其它主机(slaves)上,并重新执行一遍来实现的。复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。

1452:无法在外键的表插入或更新参考主键没有的数据。由于item_discovery.itemid字段(外键)参考了items.itemid字段(主键),当要在item_discovery表插数据时,如果items表的主键没有对应的数据,则无法插入,报1452错误。此时可以检查参考的表的主键是否有主库对应的数据,如果有,则插入参考的表相应的数据,再开启复制恢复SQL线程。

问题出现了

测试的过程中,我们发现一旦表比较大的情况下,并且不一致的情况比较严重的情况下,会造成对比非常缓慢,最坏的情况20G的数据对比和修复需要花费6个小时,而且整个对比过程有可能会影响线上正在运行的业务。为了减少这种大表对比和修复的影响,我们必须要针对大表提出我们自己的对比和修复方案来。

1.1.复制基本概念

请注意当你进行复制时,所有对复制中的表的更新必须在主服务器上进行。否则,你必须要小心,以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

图片 3

图片来自原创

复制的基本概念

  • 复制是单向的,只能从Master复制到Slave上
  • 一组复制结构中可以有多个Slave,但是Master通常情况下都只推荐1个
  • Mster端写入数据,生成event记到binary log中,Dump
    thread进程读取并推送event至Slave,最后slave顺序应用

复制解决的问题

  • 利用从库减轻主库的读取压力(读写分离)
  • 利用从库做Master故障的接管(高可用)
  • 利用从库做备份等维护工作减少对业务的影响(运维管理)
  • 利用从库可以无缝迁移、升级(滚动升级)
  • 利用从库可以数据分析、统计(BI等数据订阅)
  • 利用从库可以做异地灾备(数据冗余灾备)

 

解决思路

1.2.常见复制结构

图片 4

图片来自原创

(2) 【ERROR】1032:删除或更新数据,从库找不到记录

1.首先需要解决的是要找出数据库中的大表

我们可以通过指令show table status from DBNAME
获取数据库中的表的信息,可以获取到表的行数信息,就可以知道那些表是大表了。但是这条指令有可能会产生副作用,因为这条sql语句会访问information_schema下的表,访问这些表的时候,如果是innodb,这些innodb可能会自动做更新索引统计的操作,这可能会造成大量磁盘操作和缓存数据的换出,为了减少副作用,我们可以在执行show
table status from DBNAME之前,先执行set global
innodb_stats_on_metadata=0,来防止innodb的“顺手”操作。

1.3.主从复制原理

图片 5

图片来自原创

主从复制原理

  • master记录二进制日志。在每个事务更新数据完成之前,master在二日志记录这些改变。MySQL将事务串行的写入二进制日志,即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后,master通知存储引擎提交事务。
  • slave开始一个工作线程:IO_thread。IO_thread在master上打开一个普通的连接,然后启动binlog
    dump process。Binlog dump
    process负责从master的二进制日志中读取事件并推送给IO_thread,如果已经跟上master,它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志(Relay_log)。
  • SQL_thread从中继日志读取事件,并重放其中的事件而达到更新slave数据,使其与master中的数据一致。只要该线程与I/O线程保持一致,中继日志通常会位于OS的缓存中,所以中继日志的开销很小。

请思考:
1.这个复制结构是同步还是异步的?
2.复制结构中哪个环节容易出现性能瓶颈?

图片 6

图片来自原创

SQL Thread应用流程(ROW格式)

  • 检查当前event涉及的表是否有主键,有则基于主键更新数据
  • 检查当前event涉及的表是否有二级索引,有则基于二级索引找到主键,基于主键更新数据
  • 当前event涉及的表中没有主键以及二级索引,此次SQL_thread的应用的行为是全表扫描

 

2.大表对比和修复方案

大表的数据量大,所以我们想到的方案就是大数据计算平台:hadoop和spark平台。所以我们的对比过程图如下所示:

导出主从数据库表中的数据 —> 上传到Hadoop —>
用大数据平台对比出不同的key值 —>
不同的key值采用pt-table-sync类似的原理一行行再次对比,过滤掉相同key
—-> 剩下不同的key进行修复

通过以上手段流程就能完美的把大量的数据计算从MySQL迁移到大数据平台,剩下的少量不一致的key值,再到MySQL上比对,从而避免了对业务数据库的影响。大数据库对比有两种选择MapReduce以及Spark,相对来说Spark会比MapReduce简单很多,所以我们选择Spark,

下面简单介绍一下大概的对比方案的实施步骤:

1.4.主从复制模式

MySQL在5.6之前都是传统的复制方式,唯一不同的是记录的binary
log格式有所不同,但5.6之后出现了全新的复制模式即:GTID模式,下面我们抽丝剥茧的一一道来。(配置一个主从,对binary
log格式,逐一解释)

复制模式中传统可以基于任何的日志格式进行复制,但全新的GTID模式下只能基于row格式进行复制,见下图:(what
is log format? 其实就是event写入binary log的形式而已
)

  • 传统模式
    a) statement格式(SBR)
    b) row格式(RBR)
    c) mixed格式(MIX)
  • GTID模式
    a) row格式

binary log format

  • statement格式(SBR)
    相关参数:
    binlog_format='statement'
    优点:
    a) 写入binlog的文件内容很少
    b) 日志是包含用户执行的SQL,方便统计和审计
    c) 出现较早binlog兼容性较好
    d) binlog方便阅读,方便故障修复
    缺点:
    a) 存在安全隐患,会造成数据不一致,如mysql函数 uuid() ; user() ;
    sysdate() 用户自定义函数… …

  • row格式(RBR)
    相关参数:
    binlog_format='row'
    优点:
    a) 相比statement更加安全的复制格式
    b) 在某些场景下复制速度更快(SQL复杂、表有主键)
    c) 可以复制mysql特殊函数
    d) 更少的锁
    缺点:
    a) binary log比较大(row格式记录结果集)
    b) 单sql变更表中的行数过多,会形成大量binlog
    c) 无法从binlog中看到用户执行的sql
    d) DDL语句记录的也是statement格式记录
    限制:
    a) 如果MySQL设置日志格式是row,那它隔离级别只能是:READ-COMMITTED

  • mixed格式(MIX)
    相关参数:
    binlog_format='mixed'
    特性:
    a) 混合使用row和statement格式: 对于DDL记录会statement格式;
    对于table里的行操作记录为默认为statement格式,当特殊条件触发时才会转换成row格式;
    b) 如果使用Innodb表,事务级别使用了 READ COMMITTED 或 READ
    UNCOMMITED日志级别只能强制使用row格式.
    c) 但在使用row格式中DDL语句还是会记录成statement格式
    切换条件:
    mixed模式,以下几种情况会自动将binlog的模式由SBR模式切换成RBR模式
    a) 当DML语句更新一个NDB表时
    b) 当函数包含uuid()时
    c) 2个及以上包含auto_increment字段的表被更新时
    d) 任何insert delayed语句时
    e) 当使用用户自定义函数时(User Define Function – udf)
    f ) 视图中必须要求使用RBR时,例如创建视图使用了uuid()函数时

传统复制模式

  • 相关参数
    log_bin=on
    binlog_format=Any

  • 说明
    默认只要配置上两个参数,在复制结构中就是传统复制,并且传统复制兼容所有的日志格式

  • 最佳实践
    演示传统复制和解析不同的binlog格式
    a) 部署传统复制
    b) 解析statement、rows、mixed日志格式及输出信息解读

GTID复制模式

在传统的复制模式下,主从切换后,就需要找到对应的bin log
file以及position,基于这些信息指向新的主库,这对于不是很有经验的运维或者DBA来说,往往会出错最终造成主库同步错误。GTID的出现极大的解决了该问题的发生。

  • 相关参数
    log_bin=on
    binlog_format='row'
    gtid_mode=on
    enforce-gtid-consistency=on
    log-slave-updates=1

  • gtid概述
    1.mysql-5.6.2支持,mysql5.6.10后完善.
    2.一个事务对应一个唯一ID
    3.一个gtid在一个服务器上只会执行一次
    4.gtid是用来代替传统classic的复制方法

  • gtid的组成
    server_uuid: sequence number,如:404fba33-a7d3-11e5-93e2-a0369f7c3c3c:1-536246774

图片 7

图片来自官方文档

每一个序号表示一个事务,当然事务可以包含一个或多个dml
server_uuid来自datadir目录的auto.cnf文件,官方不建议修改,但可以启动MySQL实例后删除,启动后MySQL实例,会自动生成(Master的DB,就不要给自己找刺激了)

  • gtid的优点
    1.在分发复制的场景中,当master宕机后,slave之间发生延迟时,slave之间可能会有数据丢失
    2.在级联复制的场景中,分发slave宕机时,后续的slave无缝的change到任意一个节点中,而不丢数据

  • gtid的限制
    1.不支持非事务引擎(从库报错, stop slave; start slave ; 忽略)
    2.不支持create table … select语句(主库直接报错)
    3.不支持一个sql同时更新一个事务引擎和非事务引擎的表
    4.在一个复制组中,必须要求统一开启gtid或是关闭gtid
    5.开启gtid需要重启
    6.开启gtid后,就不在使用原来传统的复制方式
    7.对于create temporary table和drop temporary table语句不支持
    8.不支持sql_slave_skip_counter

  • 最佳实践
    演示GTID两种复制的部署方法:
    a) 全新GTID环境主从复制部署
    b) 已运行GTID环境主从复制部署

 

1 导出文件

采用select * into
outfile从mysql中导出要对比的表,假设我们从master中导出了一张表,导出文件为:master.txt,内容为:

1,”abc”,”dddddd”
2,”ef”,”adddd”

从slave中导出一张表,导出文件为:slave.txt,内容为:

1,”abc”,”tttt”
2,”ef”,”fffff”

2.复制最佳实践

图片 8

图片来自原创

双主的缺陷

  • 双主场景下必须是单边写(两边同时修改数据时会造成数据不一致)
  • 即使是两个业务使用双主那也要将两个业务同时访问同一个数据库(避免复制冲突时无法修复问题)
  • 据传说,古时候曾经有人建议调整每个实例auto_increment_offsetauto_increment_increment参数避免写入冲突问题,但会造成序列大量浪费。

双主的实现

  • 首先实现一个简单的主从复制结构就行
  • 如何避免master1上已执行的event,在master2上同reply后同样写入binlog,此时master1的dump
    thread还会将该binlog event推送至IO
    Thtread.解决该问题的方式是:Server id
  • 因master2上所有数据的变更都是有master1产生的,所以只要master2上不写入数据,随便get偏移量即可
  • 最后在master2上show master status找到Binlog File以及Binlog Positionchange即可

问题:
1.GTID环境下能否解决双边写入时,乱数据问题?
2.环形复制结构中,master会重复执行自己已执行的event吗?
3.为什么master1可以直接change
master2上的任何Binlog File以及Binlog Position

双主最佳实践

  • 必要参数

log_bin
relay-log
binlog-format
server_id
log-slave-updates
gtid_mode #5.7后复制必须开启才参数
enforce-gtid-consistency

  • master1
    1.创建复制账号
    2.备份数据库,将备份传输至master2主机上

mysql> grant replication slave,replication client on *.* to 'repl'192.168.5.@'%' identified by 'mycat';
mysql> flush privileges;

# mysqldump -S /data1/db16000/my16000.sock -u root -p --single-transaction --master-data=2 -A > /tmp/full_16000_dump.sql
# scp /tmp/full_16000_dump.sql 192.168.5.192:/tmp
  • master2
    1.恢复数据库,找到position
    2.部署slave,检查复制状态
    3.查看当前binlog position

# mysql16000  < /tmp/full_16000_dump.sql
#解析full_16000_dump.sql备份文件的master1 binlog file、position
# head -50 /tmp/full_16000_dump.sql | grep CHANGE

mysql> change master to master_host='192.168.5.191',master_port=16000,master_user='repl',master_password='mycat',master_log_file='519116000-bin.000004',master_log_position=356;
mysql> start slave;
mysql> show slave status \G

mysql> show master status \G
  • master1
    mater1当master2的slave,实现双主复制

mysql> change master to master_host='192.168.5.192',master_port=16000,master_user='repl',master_password='mycat',master_log_file='519216000-bin.000003',master_log_pos=504;
mysql> start slave;
mysql> show slave status \G;

 1032:删除或更新从库的数据,从库找不到记录。此时,主库的数据是比从库新的,可以采取从库添加相同的数据在开启复制恢复SQL线程。

2 上传文件到hdfs中

hadoop fs -copyFromLocal master.txt /
hadoop fs -copyFromLocal slave.txt /

2.2.分发、级联复制部署

分发复制结构

图片 9

图片来自原创

实现原理

  • master创建复制账号、备份数据库并将备份集传输至其他从库
  • slave1、slave2恢复数据库,根据恢复时的binlog file、position或者gtid恢复数据库
  • slave1、slave2 change到主库,完成部署

级联复制结构

图片 10

图片来自原创

实现原理

  • master创建复制账号、备份数据库并将备份集传输至其他从库
  • slave1、slave2恢复数据库
  • 当slave之间恢复完数据库后,slave1获取当前binlog file、position信息
  • slave2
    基于slave1的binlog file、position信息change到slave1上,成为slave1的slave
  • slave1
    基于备份中master的binlog file、position信息change到master1上,完成级联复制

 

3 采用下面的spark脚本计算不同的key值

val rddMaster = sc.textFile(“/master.txt”).map(x =>(x.split(“,”)(0),
x)) // 假设第一个字段是主键
val rddSlave = sc.textFile(“/slave.txt”).map(x =>(x.split(“,”)(0),
x))
val difference = rddMaster.cogroup(rddSlave).filter(x=> x._2._1 !=
x._2._2 )
val differenceKey = difference.keys()
differenceKey.collect

通过三面的三个步骤我们就能够得出不同的key值有哪些了。

上面的代码非常简单,只是提供一个思路,对于具体的情况肯定会复杂一些,如有多个主键的情况,依据主键数据中包含了分割符号等等情况,这里就不对这些情况进行细说了。

2.3.分发、级联结构调整

(3) 【ERROR】1062:从库插入数据,发生唯一性冲突

2.3.1.复制结构调整场景一

图片 11

图片来自原创

图片 12

图片来自原创

实现思路

  • 让所有slave停在同一个position,slave1 获取自己的Binlog File Position
  • slave2基于slave1的position,change到slave1上,变成slave1的slave
  • 此时,slave2是slave1的slave,但是master和slave1的复制是中断的
  • 修复之前和master停在同一个position的错误,恢复和master的复制关系

问题:两个slave drop table的时间点不一致,master在drop
table的时候就说明slave之间停的不是同一个position

最佳实践

  • master1
    主库创建一张标志表

mysql> create table t_slave_stop (id int);
  • slave1
    删除标志表

drop table t_slave_stop;
  • slave2
    为了表示slave之间不是同一个时间drop
    table的,最好master写入一下数据,在删除标志表

drop table t_slave_stop;
  • master1
    master drop table子句触发slave之间同在同一个position上

mysql> drop table t_slave_stop;
  • slave1

mysql> show master status \G;
  • salve2
    基于slave数据静止的binary log fileposition,change过去

mysql> stop slave;
mysql> change master to master_host='10.209.5.192',master_port=16000,master_log_file='519216000-bin.000003',master_log_pos=3638;
mysql> start slave;
mysql> show slave status \G
  • slave1
    修复和master的复制关系,并且slave2复制slave1的数据,所以slave2不用关心

mysql> stop slave;
mysql> create table t_slave_stop (id int);
mysql> start slave;
mysql> show slave status \G

 

2.3.2.复制结构调整场景二

图片 13

图片来自原创

实现思路

  • slave1停止和master的复制关系,slave2如果和slave1有延迟,就等待延迟的数据追上
  • slave1和slave2是数据是镜像关系,所以slave2就参照slave1中show slave status信息change到master上(Relay_Master_Log_FileExec_Master_Log_Pos)
  • slave1启动和master的复制关系,完成结构调整

最佳实践

  • slave1
    停止slave复制,等待slave2复制延迟的数据追平

mysql> stop slave;
mysql> show slave status \G;
  • slave2
    基于slave1show slave status \G中Sql
    Thread回放的binlog filebinlog positionchange过去

mysql> stop slave;
mysql> change master to master_host='10.209.5.191',master_port=16000,master_user='repl',master_password='mycat',master_log_file='519116000-bin.000005',master_log_pos=2961;
mysql> start slave;
mysql> show slave status \G;
  • slave1
    恢复和master的复制关系

mysql> start slave;
mysql> show slave status \G;

 

2.4.复制常见错误处理

 1062:从库插入数据,发生唯一性冲突。此时从库已经有相同主键的数据,如果再插入相同主键值的数据则会报错。可以查看主库的改行数据与从库的要插入数据是否一致,如一致则跳过错误,恢复SQL线程,如不一致,则以主库为准,将从库的该行记录删除,再开启复制。

2.4.1.配置类的错误处理

 

2.4.1.1.IO_Thread访问不到主库

Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'replica@192.168.5.191:16001' - retry-time: 60 retries: 1

  • 最常见的复制问题,排查主库的ip、port、username、password、权限是否正确;主库是否开启了防火墙等基础链接情况

如果当前高可用架构为Master-Master,则以下均在从库的操作都必须set
sql_log_bin=0,避免从库执行的语句同步到主库(恢复时以主库的数据为准)。

2.4.1.2.主从字段类型不一致

Last_SQL_Errno: 1677
Last_SQL_Error: Column 1 of table 'test.t' cannot be converted from type 'int' to type 'bigint(20)'

  • 将主从数据库字段改成一致
  • 从库支持有损数据类型转换,可通过参数slave_type_conversions配置,该参数三个选项:
    a)
    ALL_LOSSY:仅支持有损转换,什么叫有损?比如一个值本来是bigint存储为9999999999999,现在转换为int类型势必会要截断从而导致数据不一致。
    b) ALL_NON_LOSSY:仅支持无损转换,只能在无损的情况下才能进行转换
    c) ALL_LOSSY,ALL_NON_LOSSY:有损/无算转换都支持
    注意: 前面说的这几中情况都只在binlog_format=ROW的情况下才有效。

二、怎么解决问题:

2.4.1.3.MyISAM表损坏

Last_SQL_Errno: 1194
Last_SQL_Error: Error 'Table 'traincenter' is marked as crashed and should be repaired' on query. Default database: 'basketballman'. Query: 'update traincenter set points='4',pointstime='1361912066' where uid = '1847482697' limit 1'

  • myisam表traincenter损坏,直接repair table即可。
    innodb相对MyIASM表数据安全的优势:
    1.innodb有double write机制,损坏或者half write的页可以用它恢复
    2.innodb是事务引擎,都有操作都是事务的,而myisam是非事务的,存在写一半但是操作终止情况。

1.临时解决方案(业务运行期间不适宜使用数据对比和修复工具)

2.4.1.4.从库找不到主库的bin-log

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

  • 主库上的binlog文件已经不存在,或者index
    file文件被修改导致;常见故障场景:错误的原因在于由于复制中断时间很长,报警出来一直没人处理,这个中断时间超过master上binlog超期时间,等恢复复制时需要的binlog已经由于其超期而被删掉,没办法只好重建这个实例了。

 

2.4.1.5.server-id冲突

Last_IO_Errno: 1593
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).

  • 主从配置的server-id一样,而在主从复制环境中server-id一样的binlog
    events都会被过滤掉。具体server-id的含义可以了解一下复制原理。这个一般是因为拷贝配置文件时忘记修改server-id导致。

【ERROR】1452:

2.4.1.6.MyIASM表”事务”丢失

Last_Errno: 1053
Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'insert into ...

  • 由于myisam不支持事务所以可能存在一个查询完成一部分然后失败的情况。解决方法一般也就是提示信息给出的跳过一个binlog
    event。不过确认跳过之前最好还是查询一下master上是否真的存在相应的记录,因为错误信息同时还会给出它认为在master上执行一部分然后终止的查询语句。

 

2.4.1.7.日志格式不统一

Last_SQL_Errno: 1666
Last_SQL_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'

  • 一个ABC结构的复制,B、C中设定的binlog_format=statement,A中的是MIXED,所以当B尝试重做A过来的relay
    log,然后记录binlog(传给C)时发现relay
    log的binlog_format与自己设定的binlog_format不一致。

 

2.4.1.8.slave_load_tmpdir空间不足

Last_Errno: 28
Last_Error: Error in Append_block event: write to '/tmp/SQL_LOAD-32343798-72213798-1.data' failed

  • 主库执行load data infile,同步到从库后load data
    infile存放的文件默认是放在/tmp(由参数slave_load_tmpdir控制),而/tmp空间不够因此报错。因此只要将从库上slave_load_tmpdir设置到一个磁盘空间足够大的分区就行

 

2.4.2.数据冲突错误处理
  • 样本数据

mysql> CREATE TABLE `t_policy` (
         `id` int(11) NOT NULL AUTO_INCREMENT,
         `policy_name` VARCHAR(30) NOT NULL,
         `dimension` VARCHAR(255) NOT NULL,
         `active` VARCHAR(1) NOT NULL,
         `create_time` timestamp  DEFAULT NULL,
         `update_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
         PRIMARY KEY (`id`),
         KEY `IX_policy_name` (`policy_name`)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> insert into t_policy (policy_name,dimension,active,update_time) values ('AAA','1080x920',1,now()),('B','1080x920',2,now()),('B','1024x690',2,now()),('C','1024x768',3,now());
mysql> insert into t_policy (policy_name,dimension,active,update_time) values ('AAAA','4K',1,now()),('AAA','2K',1,now()),('AAA','1K',1,now()),('AAAAA','蓝光',1,now());

普通主从复制环境

2.4.2.1.从库找记录不存在

Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction '8117cf0f-96b9-11e7-b884-a0369f790658:31' at master log 519116000-bin.000005, end_log_pos 9324. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

  • 删除或更新从库的数据,从库找不到记录。此时,主库的数据是比从库新的,可以采取从库添加相同的数据在开启复制恢复SQL线程。

  • 模拟1032错误

1.从库删除一条记录

mysql> delete from t_policy where id = 8;

2.主库对删除的一条记录进行修改

mysql> update t_policy set dimension='蓝光v2'  where id = 8;

3.从库检查复制状态(复制已经断开)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.5.191
                  Master_User: repl
                  Master_Port: 16000
                Connect_Retry: 60
              Master_Log_File: 519116000-bin.000005
          Read_Master_Log_Pos: 9355
               Relay_Log_File: 519216000-relay-bin.000005
                Relay_Log_Pos: 6426
        Relay_Master_Log_File: 519116000-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1032
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction '8117cf0f-96b9-11e7-b884-a0369f790658:31' at master log 519116000-bin.000005, end_log_pos 9324. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 9023
              Relay_Log_Space: 7179
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1032
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction '8117cf0f-96b9-11e7-b884-a0369f790658:31' at master log 519116000-bin.000005, end_log_pos 9324. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 519116000
                  Master_UUID: 8117cf0f-96b9-11e7-b884-a0369f790658
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 170913 15:13:24
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 8117cf0f-96b9-11e7-b884-a0369f790658:2-31
            Executed_Gtid_Set: 8117cf0f-96b9-11e7-b884-a0369f790658:1-30,
95ff34d2-96c0-11e7-850c-a0369f790bac:1-147
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

4.基于SQL thread停止的position解析主库的binlog

# mysqlbinlog -vv --base64-output=decode-rows 519116000-bin.000005 --start-position=9023 > /tmp/binlog

......  省略  ......
#170913 15:13:24 server id 519116000  end_log_pos 9324 CRC32 0x1a0f64c2         Update_rows: table id 221 flags: STMT_END_F
### UPDATE `mycat`.`t_policy`
### WHERE
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2='AAAAA' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
###   @3='languang' /* VARSTRING(765) meta=765 nullable=0 is_null=0 */
###   @4='1' /* VARSTRING(3) meta=3 nullable=0 is_null=0 */
###   @5=1505286407 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
###   @6=1505286487 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
### SET
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2='AAAAA' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
###   @3='蓝光v2' /* VARSTRING(765) meta=765 nullable=0 is_null=0 */
###   @4='1' /* VARSTRING(3) meta=3 nullable=0 is_null=0 */
###   @5=1505286407 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
###   @6=1505286804 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
# at 9324
......  省略  ......

5.生成数据启动从库的复制

mysql> insert into t_policy values (8,'AAAAA','languang','1',from_unixtime(1505286407),from_unixtime(1505286487));
mysql> start slave;
mysql> show slave status \G

 

2.4.2.2.从库主键冲突

Last_Errno: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction '8117cf0f-96b9-11e7-b884-a0369f790658:32' at master log 519116000-bin.000005, end_log_pos 9619. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

  • 从库插入数据,发生唯一性冲突。此时从库已经有相同主键的数据,如果再插入相同主键值的数据则会报错。可以查看主库的改行数据与从库的要插入数据是否一致,如一致则跳过错误,恢复SQL线程,如不一致,则以主库为准,将从库的该行记录删除,再开启复制。

  • 模拟1062错误

1.从库写入一条数据

mysql> insert into t_policy values (9,'slave','768x1024',0,now(),now());

2.主库在写入一条数据

mysql> insert into t_policy (policy_name,dimension,active,update_time) values ('A+','2K',1,now());

3.从库查看复制状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.209.5.191
                  Master_User: repl
                  Master_Port: 16000
                Connect_Retry: 60
              Master_Log_File: 519116000-bin.000005
          Read_Master_Log_Pos: 9650
               Relay_Log_File: 519216000-relay-bin.000005
                Relay_Log_Pos: 6758
        Relay_Master_Log_File: 519116000-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction '8117cf0f-96b9-11e7-b884-a0369f790658:32' at master log 519116000-bin.000005, end_log_pos 9619. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 9355
              Relay_Log_Space: 7474
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction '8117cf0f-96b9-11e7-b884-a0369f790658:32' at master log 519116000-bin.000005, end_log_pos 9619. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 519116000
                  Master_UUID: 8117cf0f-96b9-11e7-b884-a0369f790658
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 170913 15:47:40
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 8117cf0f-96b9-11e7-b884-a0369f790658:2-32
            Executed_Gtid_Set: 8117cf0f-96b9-11e7-b884-a0369f790658:1-31,
95ff34d2-96c0-11e7-850c-a0369f790bac:1-149
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

4.基于SQL thread停止的position解析主库的binlog

# mysqlbinlog -vv --base64-output=decode-rows 519116000-bin.000005 --start-position=9355 > /tmp/binlog

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 9355
#170913 15:47:40 server id 519116000  end_log_pos 9420 CRC32 0x8dac50fb         GTID    last_committed=34       sequence_number=35
SET @@SESSION.GTID_NEXT= '8117cf0f-96b9-11e7-b884-a0369f790658:32'/*!*/;
# at 9420
#170913 15:47:40 server id 519116000  end_log_pos 9501 CRC32 0x0cf2c142         Query   thread_id=317   exec_time=0     error_code=0
SET TIMESTAMP=1505288860/*!*/;
SET @@session.pseudo_thread_id=317/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 9501
#170913 15:47:40 server id 519116000  end_log_pos 9566 CRC32 0x73be9b73         Table_map: `mycat`.`t_policy` mapped to number 221
# at 9566
#170913 15:47:40 server id 519116000  end_log_pos 9619 CRC32 0x454a0acf         Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO `mycat`.`t_policy`
### SET
###   @1=9 /* INT meta=0 nullable=0 is_null=0 */
###   @2='A+' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
###   @3='2K' /* VARSTRING(765) meta=765 nullable=0 is_null=0 */
###   @4='1' /* VARSTRING(3) meta=3 nullable=0 is_null=0 */
###   @5=NULL /* TIMESTAMP(0) meta=0 nullable=1 is_null=1 */
###   @6=1505288860 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
# at 9619
......  省略  ......

5.从库解决数据一致性问题,有两种处理方法:
a) 修改成基础数据,为stop position的事务提供基础变更环境
b) 修改成stop position的事务之后的数据,这样做需要跳过该事务
为了演示传统复制跳过错误,我们就通过跳过错误的方式处理问题

5.1.修复主从数据

mysql> delete from t_policy where id = 9;
mysql> insert into t_policy values (9,'A+','2K','1',NULL,from_unixtime(1505288860));

5.2.跳过复制错误
方法:解析binlog
position,查看复制错误的当前事务的GTID序号,跳过即可

实现原理:在从库针对报错GTID的序号注入一个空事务,达到其跳过错误的目的

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> set gtid_next='8117cf0f-96b9-11e7-b884-a0369f790658:32';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set gtid_next='automatic';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;

从库:

2.4.3.处理复制错误高阶方法

 

2.4.3.1.传统跳过复制错误
  • MySQL5.7之前没有强制开启GTID模式时,传统复制模式下跳过错误通过以下方法:

mysql> stop slave;
mysql> set sql_slave_skip_counter=1; #表示基于当前的binlog position之后允许跳过一个错误
mysql> start slave;

 

2.4.3.2.批量跳过复制错误
  • 试想一下在实际工作中,一旦发生复制错误,几乎这种复制错误就会很多,绝不会只有单单的一个,在这种场景中我们如何快速的修复复制问题呢?MySQL提供了一个可以跳过复制错误代码的参数:slave_skip_errors
    a) 默认表示不跳过任何错误
    b) all表示跳过所有复制的错误
    c) 1032,1062表示跳过这两类的复制错误

注意:该参数是只读参数,所以需要将参数配置到数据库中重启数据库后生效!
友情提示:数据追平后,记得把该参数关闭 !!!

主库:

3.复制过滤应用

图片 14

图片来自原创

查看主库在出错的相应位置的执行语句,可通过SQL得出当时insert或者update的对应的主键值。

3.1.复制过滤应用

 

3.1.复制参数介绍

master

  • log-bin binlog
    文件名前缀,可以是全路径,不能动态修改

  • sync_binlog
    sync_binlog=N,N个SQL之后,刷新binlog信息写入到disk。
    参数等于0时,每一次事务后,MySQL会将binlog信息写入到文件系统缓存,由fdatasync()定期刷新到disk中
    参数等于1时,每一次事务后,MySQL数据库调用OS
    fsync()函数将binlog信息写入disk。
    参数大于1时,第N次事务后,MySQL调用fdatasync()函数将binlog信息写入disk。
    fdatasyncfsync的区别

  • server-id
    唯一区别ID,同一个集群内不可重复即可,可动态修改

  • log-bin-index
    索引文件前缀名,和log-bin一样,可以是全路径

  • binlog_format
    日志格式:statement、row、mixed可动态修改,建议row

  • binlog_cache_size
    写入buffer的大小,(binlog_cache_disk如果存在说明binlog_cache_size小了)可动态修改

  • max_binlog_size
    限制单个binlog大小,默认1G,可动态修改,建议binlog切换时间为30分钟的大小为宜,否则数据恢复可能会很慢

  • expire_logs_days
    N天后自动删除binlog,可动态修改

  • log_bin_trust_function_creators
    开启binlog时,是否允许创建存储过程等.

  • gtid-mode=on
    是否开启GTID,如果是ON,则必须是log-bin,log-slave-updates,enforce-gtid-consistency也必须启动

  • enforce-gtid-consistency
    gtid-mode=ON,该参数必须开启,用于控制事务安全的记录到binlog中

  • gtid-next
    用来指示一下要执行的事务

  • gtid-purged
    用于指定复制中忽略的事务

  • gtid-executed
    那些事务中是已经执行过的了

slave

  • 必要参数:

  • server-id
    唯一区别ID,同一个集群内不可重复即可,可动态修改

  • log_slave_updates
    slave会把复制的binlog也到binarylog中记录,在级联复制中,用于记录元数据都被哪些数据库应用过.

  • relay_log_recovery
    slave异常重启时,未应用完的relaylog会被删掉,当配置该参数后,会检查自身丢失的数据,在master重新获取binlog,进行恢复

  • skip-slave-start
    从库重启后,不自动启动slave

  • slave-transaction-retries
    innodb死锁或者锁超时导致复制线程执行失败,重试的次数,建议为1020或者保持默认

  • read-only
    只读打开,super权限的用户无效

  • slave_net_timeout
    slave和master网络超时的时间,默认一小时,必须要改成10秒左右,

  • slave_skip_errors
    复制中,忽略某些错误(临时解决的办法,并不是常态)

  • slave-parallel-type
    并行复制模式,默认库级别DATABASE,5.7后调整成事务级别LOGICAL_CLOCK

  • slave-parallel-workers
    并行复制并行度

  • master_info_repository
    从库读取到

  • relay_log_info_repository

  • 可选参数:

  • log-slow-slave-statments
    默认复制慢语句不会记录到慢日志中,启用该功能,会把复制超过long_query_time的语句记录到慢日志中

  • max_relay_log_size
    设置relay_log的最大大小,建议不要设置

  • relag-log-info-file
    relay-log的索引文件

  • relay-log-purge
    默认开启,在relaylog使用完毕后,尽可能的清理掉(Mha环境中尽量不要删除,目的是master挂机后,数据补不全的情况下,从relay-log中补全)

  • relay_log_space_limit
    限制relaylog占用磁盘的总大小(relaylog写满后,同步会中断,一般SSD设备空间较小经常出现)

  • replicate-same-server-id
    对于相同的server-id的binlogevent默认不执行

  • slave_load_tmpdir
    对于复制的loaddatainfile语句,slave指定创建临时文件的目录,默认就是tmpdir

 

3.2.复制过滤最佳实践

让从库只复制某个库或者某个表;让主库只记录某个库的变更日志(主库的颗粒度只能是库级别的);一般也用于拆库。

复制颗粒度
可以在主库上配置:只记录某个库的日志,支持反向(do ignore)
可以在从库上配置:只复制某个库或者是某个表的数据,支持反向(do ignore)

建议: 在从库上配置过滤,不要在主库上配置

查询item_discovery的外键约束c_item_discovery_1参考的表items对应主键值的数据行。

3.2.1.复制过滤参数

master

  • binlog-do-db
    配置主库复制的数据库

  • binlog-ignore-db
    配置主库忽略的数据库

  • 注意:这两个选项要结合binlog_format工作
    statment 只作用于当前默认库
    row 是能全局应用

思考下列问题:
示例一

binlog-do-db=db1
use db2;
update db1.tab1 set col2=2 where id = 1;

SBR/RBR会不会记录到日志中?
SBR:因为SBR下只作用于当前默认库,(use db2;) 所以不会记录复制的
RBR:因为RBR下是全局的,update中包含了db1.tab1 的数据,所以会记录

示例二

binlog-ignore-db=db1
use db2;
update db1.tab1 set col2=2 where id = 1;

SBR/RBR会不会记录到日志中?
SBR:因为SBR下只会忽略当前库是db1的,当前库是db2,所以是会记录复制内容的
RBR:RBR 在忽略的时候能够看到 update 更新是db1.tab1的数据 所以会忽略掉

slave

  • replicate-do-db
    配置slave只复制某个db

  • replicate-ignore-db
    配置slave只忽略某个db

  • replicate-do-table
    配置slave只复制某个table

  • replicate-ignore-table
    配置slave只复制某个table

  • replicate-wild-do-table
    支持通配符,配置slave只复制table (replicate-wild-do-table=db1.t*)

  • replicate-wild-ignore-table
    支持通配符,配置slave只忽略table
    (replicate-wild-ignore-table=db1.s*)

  • repicate-rewrite-db
    如果master和slave有同名的A库了,那么可以采用rewrite规则,用master的A库复制到slave的B库(语法:master
    replicate-rewrite-db=a->b)

 

3.3.复制状态、延迟监控

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event  #io/thread连接Master时产生的等待事件
                  Master_Host: 192.168.1.1  #master主机
                  Master_User: replica  #复制账号
                  Master_Port: 3306  #master端口
                Connect_Retry: 60  #重试连接主库次数
              Master_Log_File: 113306-bin.032047  #IO thread读取Master的bin_log日志位置
          Read_Master_Log_Pos: 481887487  #IO thread读取Master的bin_log日志偏移量
               Relay_Log_File: 123306-relay-bin.052152  #SQL thread读取Raley_log中master的日志位置
                Relay_Log_Pos: 481651027  #SQL thread读取和执行的中继日志文件的名称
        Relay_Master_Log_File: 113306-bin.032047  #SQL thread读取和执行对应master的日志位置。
             Slave_IO_Running: Yes  #IO thread状态
            Slave_SQL_Running: Yes  #SQL thread状态
              Replicate_Do_DB:   #
          Replicate_Ignore_DB:   #
           Replicate_Do_Table:   # 忽略复制
       Replicate_Ignore_Table:   #
      Replicate_Wild_Do_Table:   #
  Replicate_Wild_Ignore_Table:   #
                   Last_Errno: 0  # 复制错误代码
                   Last_Error:   #复制错误描述信息
                 Skip_Counter: 0  #最近被使用SQL_SLAVE_SKIP_COUNTER的值
          Exec_Master_Log_Pos: 481650851  #SQL thread读取和执行对应master的日志偏移量
              Relay_Log_Space: 481886976  #原有的中继日志结合起来的总大小
              Until_Condition: None  #start slave语句的until子句中指定的值
               Until_Log_File:   #until子句终止时对应的binlog文件位置
                Until_Log_Pos: 0  #until子句终止时对应的binlog偏移量
           Master_SSL_Allowed: No  #是否允许SSL连接,下面是加密连接时的参数
           Master_SSL_CA_File:   #
           Master_SSL_CA_Path:   #
              Master_SSL_Cert:   #
            Master_SSL_Cipher:   #
               Master_SSL_Key:   #
        Seconds_Behind_Master: 0  #主从复制延迟的值,单位是秒。该值准确但不精确。
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0  #IO thread错误代码
                Last_IO_Error:    #
               Last_SQL_Errno: 0  #SQL thread错误代码
               Last_SQL_Error:    #
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 113306
                  Master_UUID: a0f36bb1-fdbb-11e5-8413-a0369f7c3bb4
             Master_Info_File: mysql.slave_master_info  #在slave持久化master info的介质
                    SQL_Delay: 0  #延迟复制,单位是秒
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp:   IO thread错误时间戳
     Last_SQL_Error_Timestamp:  SQL thread错误时间戳
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: a0f36bb1-fdbb-11e5-8413-a0369f7c3bb4:11766950523-17011135306  #接收master的GTID
            Executed_Gtid_Set: 0921d44c-5d33-11e5-ad43-ecf4bbdee514:1-5946125770,  #已经执行过的GTID
a0f36bb1-fdbb-11e5-8413-a0369f7c3bb4:1-17011135306,
a6d9cf92-83bc-11e6-ade4-a0369f7c3f80:1-24
                Auto_Position: 1  #GTID模式复制

 

4. 半同步复制

从库:

4.1. 半同步复制概括及原理

MySQL默认的复制是属于异步同步。在mysql5.5之前,mysql复制是异步操作,主库和从库的数据之间存在一定的延迟,这样存在一个隐患,当主库上写入一个事物并提交成功,而从库尚未得到主库推送的binlog日志时,主库宕机了,例如主库可能因为磁盘损坏,内存故障等造成主库上该事务binlog丢失,此时,从库就可能损失这个事务,从而造成主从不一致;为了解决这个问题,mysql5.5引入了半同步复制机制,在mysql5.5之前的异步复制时,主库执行完commit提交操作后,在主库写入binlog日志后即成功返回客户端,无需等待binlog日志传送给从库。

在items表插入主库查询出来的数据。

4.1.1.MySQL复制架构
  • 同步复制:
    Master提交事务,直到事务在所有的Slave都已提交,此时才会返回客户端,事务执行完毕。缺点:完成一个事务可能会有很大的延迟。

  • 异步复制:
    当Slave准备好才会向Master请求binlog。缺点:不能保证一些事件都能够被所有的Slave所接收。

  • 半同步复制:
    半同步复制工作的机制处于同步和异步之间,Master的事务提交阻塞,只要一个Slave已收到该事务的事件且已记录。它不会等待所有的Slave都告知已收到,且它只是接收,并不用等其完全执行且提交。

 

4.1.2.MySQL半同步原理

半同步复制时,为了保证主库上的每一个binlog事务都能够被可靠的复制到从库上,主库在每次事物成功提交时,并不及时反馈给前段应用用户,而是等待其中一个从库也接收到binlog事务并成功写入中继日志后,主库才返回commit操作成功给客户端。半同步复制保证了事务成功提交后,至少有两份日志记录,一份在主库的binlog,另一份在至少一个从库的中继日志relaylog上,从而更近一步保证了数据的完整性

 

4.1.2.1.官方半同步原理

图片 15

图片来自原创

  • MySQL SQL Parase
    权限检查、语法语义检测,并通知并等待存储引擎进入preare,在Binary
    log中进行标记
  • Storage Involve
    存储引擎和MySQL
    Server层发送ACK确认包后,就开始数据变更并产生redo、undo
  • Write Binary Log
    写入Binary log
  • Storage Commit
    存储引擎提交,表示数据已经变更完毕
  • Waititng Slave dump
    如果多个Slave,等待任何一个Slave接收到Event,并收到Slave确认消息
  • Return To Client
    返回结果集给用户

半同步缺陷
假设场景中客户端事务在存储引擎层提交后,在得到从库确认的过程中,主库宕机了,此时,可能的情况有两种:

  • 事务还没发送到从库上
    此时,客户端会收到事务提交失败的信息,客户端会重新提交该事务到新的主上,当宕机的主库重新启动后,以从库的身份重新加入到该主从结构中,会发现,该事务在从库中被提交了两次,一次是之前作为主的时候,一次是被新主同步过来的。

  • 事务已经发送到从库上
    此时,从库已经收到并应用了该事务,但是客户端仍然会收到事务提交失败的信息,重新提交该事务到新的主上。

基于GTID复制环境

4.1.2.2.社区增强版同步

社区增强半同步最早出现在社区中,后在官方5.7、MariaDB10.1被正式Release。

图片 16

图片来自原创

与普通主从复制环境处理方式相同。

4.2. 半同步复制最佳实践

 

作业

  • 问:复制结构中的slave Binlog File可以比master的序号大吗?
    答:

  • 问:请简述复制原理,从库有延迟需要处理吗?一般延迟的原因?
    答:

  • 问:复制延迟1000,如何确定master和slave之间同步的位置?
    答:

扫描下方二维码关注本人微信号!欢迎大家交流学习!

图片 17

Bruce.Liu

#
###SQL thread读取Raley_log中Master的日志偏移量######

【ERROR】1032:

 

 

 

发生1032可能是delete或者update时从库没有对应数据行,可以分两种情况处理:

 

(1)如果是Could not execute Delete_rows,则可以直接跳过错误

 

普通主从复制环境

 

从库:

 

 

基于GTID复制环境

 

从库:

找出复制出错时的executed_Gtid_Set,若出现多个,则选择跟Master_uuid相同的那一条。

 

 

(2)如果是Could not execute
Update_rows,则需要在二进制日志找出出错位置的SQL,再找出该表在主库的对应的数据行,然后直接在从库插入这条数据,开启SQL线程恢复。

 

普通主从复制环境

 

从库:

 

 

主库:

查看主库在出错的相应位置的执行语句,可通过SQL得出当时update的对应的主键值。

 

 

查询item_discovery的对应主键值的数据行。

 

 

从库:

在items表插入主库查询出来的数据。

 

 

基于GTID复制环境

与普通主从复制环境处理方式相同。

 

【ERROR】1062:

 

 

 

普通主从复制环境

 

从库:

 

 

主库:

查看主库在出错的相应位置的执行语句,可通过SQL得出当时insert的对应的主键值。

 

 

查询trends_uint表对应主键值的数据行。

 

 

从库:

在trends_uint表删除主库查询出来的数据。

 

 

基于GTID复制环境

与普通主从复制环境处理方式相同。

 

2.彻底解决方案

 

使用pt-table-checksum和pt-table-sync彻底修复数据不一致。

 

注意:使用pt工具包首先要安装pt工具包和安装perl模块。

 

(1)   从库停止复制

 

 

 

(2) 在主库创建校验信息表

 

 

 

(3) 在主库用pt-table-checksum校验主从数据一致性

 

在从库执行以下语句,查看Last_Error,发现数据不一致的表:

 

 

 

然后返回操作系统执行以下命令:

 

 

 

该命令可以查看该表是否发生数据不一致情况,若有,则使用pt-table-sync修复。

 

(4)
在主库用pt-table-sync打印出修复不一致数据的SQL(如果有外键约束,修复数据应先从外键参考的字段所属表开始修复),后将修复语句在从库执行。

 

三、优化建议

在复制由于1045、1032、1062的原因中断后,应使用三.1的临时解决方案,恢复复制后再在业务低谷使用pt-check-sum检查数据一致性。

 

检查完后可以在从库执行这条语句查看有无数据不一致表:

 

 

 

针对核心表,可以定制自动数据校验脚本,每周进行数据校验,但必须要在业务低谷进行校验哦!

 ————————————————————

推荐阅读:

【干货】Linux监控
sar命令解析

【干货】Linux
Shell常用经典脚本收藏

教你分分钟搞定Python之Flask框架

网页加载性能调优