MySQL的一致性如何检测及实现数据同步

本文主要给大家简单讲讲Mysql的一致性如何检测及实现数据同步，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望mysql的一致性如何检测及实现数据同步这篇文章可以给大家带来一些实际帮助。

一. 部署percona tookit

1. 下载安装包
   ~]# wget https://www.percona.com/downloads/percona-toolkit/3.0.5/binary/redhat/7/x86_64/percona-toolkit-3.0.5-1.el7.x86_64.rpm

2. 安装
   ~]# yum install percona-toolkit-3.0.5-1.el7.x86_64.rpm

二. 一致性检测工具pt-table-checksum

1. 一致性检测原理

pt-table-checksum是percona-toolkit系列工具中的一个，用于检测主从数据库中的一致性。一次只工作在一张表上，会将主库上的表切割成一个一个的chunk，这种切割要依赖于表上的index。所以在检测时不需要大量的内存和前期工作，而且还可以在数据尖峰是通过指数衰减算法，快速选择适合的chunk大小，减轻云服务器压力。将表切割成一个一个的chunk接下来会对chunk进行checksum，并记录下来。并对比从库上的checksum是否一致，从而判断数据是否一致。并且在检测过程中会自动判断master负载，以及slave延迟，一旦超过阈值就会停止下来。对于线上的环境影响不大。而且他还可以随时停止，只需在重启时加入--resume就会从上次的检测重新开始。接下来介绍其详细过程。

1) 表结构的检查

表结构的检查也称之为单行数据checksum值的计算，并获取每一列的数据类型，把所有数据类型都转化为字符串，然后用concat_ws()函数进行连接，由此计算出该行的checksum值。checksum默认采用crc32计算。

2) 数据块checksum的计算

pt-table-sync会智能分析表上的索引，然后把表的数据split成若干个chunk，计算的时候以chunk为单位。可以理解为把chunk内所有行的数据拼接起来，再计算crc32的值，即得到该chunk的checksum值。所以它把checksum结果存储到统计表，然后把执行过的sql语句记录到binlog中，任务就算完成。然后从云服务器会读取到binlog的SQL语句依次执行，并将checksum保存在表中。

2. 校验

1）授权

Create database pt CHARACTER SET utf8;
GRANT UPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATioN SLAVE ON . TO 'checksums'@'192.168.239.135' identified by 'check_pass';
GRANT ALL ON pt.* TO 'checksums'@'192.168.%';
在这里我们创建了一个数据库，用于存储一致性检测生成的数据。主从同步工具pt-table-sync根据此数据库中的数据查找有不一致的数据，并同步。其中135为主，136为从。

2）校验（Master云服务器运行）

pt-table-checksum --nocheck-binlog-fORMat --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases newtable -u'checksums' -p'checksums' -h292.168.239.135
#-h -u -p -P -S -d 连接信息
#--nocheck-replication-filters 检测中忽略mysql 配置参数binlog_ignore_db等。
#--nocheck-binlog-format 不检测日志格式，默认是使用statement 格式，如果binlog的日志与默认不同将会检测失败。所以我们会关闭binlog格式的检测
#--replicate 指定checksum 存储的db和表， 如test.checksum
#--chunk-size， --chunk-size-limit 用于指定检测块的大小。 可控性更强，Number of rows to select for each checksum query。默认是1000。对于--chunk-size-limit来说，他可以避免当主云服务器为空，而从服务数据很大时造成的从延时过大。
#--lock-wait-timeout innodb 锁的超时设定， 默认为1
#--max-load ： Examine SHOW GLOBAL STATUS after every chunk, and pause if any status variables are higher than their thresholds
#--replicate-check-only 只输出数据不一致的信息。
#--resume: pt-table-checksum停止后，使用此参数可以接着停止的地方开始。

注意：
pt-table-checksum前提假设主从的表和表结构是一致的，如果不一致pt-table-checksum会失败

三. 数据同步工具pt-table-sync

pt-table-sync是MySQL数据同步工具，并不仅仅是同步主从数据，任意主机上的表都可以同步。
pt-table-checksum只是校验，所以它把checksum结果存储到统计表，然后把执行过的sql语句记录到binlog中，任务就算完成。
pt-table-sync则不同，工作流程如下：

• a) 连接到主库：pt工具连接到主库，然后自动发现主库的所有从库。默认采用show full processlist来查找从库，但是这只有在主从实例端口相同的情况下才有效
• b) 在主库上对每一个chunk，在校验时加上for update锁。一旦获得锁，就记录下当前主库的show master status值。在从库上执行select master_pos_wait()函数，等待从库sql线程执行到show master status得到的位置。以此保证，主从上关于这个chunk的内容均不再改变。
• c) 对这个chunk执行checksum，然后与主库的checksum进行比较
• d) 如果checksum相同，说明主从数据一致，就继续下一个chunk
• e) 如果checksum不同，说明该chunk有不一致。深入chunk内部，逐行计算checksum并比较
• f) 如果发现某行不一致，则标记下来。继续检测剩余行，直到这个chunk结束
• g) 对找到的主从不一致的行，采用replace into(如果数据不存在则插入,存在则更新,避免了主键约束)语句，在主库执行一遍以生成该行全量的binlog,并同步到从库，这会以主库数据为基准来修复从库;对于主库有的行而从库没有的行，采用replace在主库上插入(必须不能是insert);于从库有而主库没有的行,通过在主库执行delete来删除(pt-table-sync强烈建议所有的数据修复都只在主库进行,而不建议直接修改从库数据;但是也有特例，后面会讲到)。
• h) 直到修复该chunk所有不一致的行。继续检查和修复下一个chunk
• i) 直到这个从库上所有的表修复结束。开始修复下一个从库

四. 实验示例

1. 实验环境

• 主机IP : 192.168.239.135 192.168.239.136
• 主机系统 :  Centos7.2
• MySQL版本 :  5.5.56
  两台主机已经配置好主从，其中135为主，136为从
  

2. 主从数据一致性检查

• 1）授权（master主机上）
  mysql> CREATE DATABASE pt;      #创建数据库pt用于存放checksum的值
  mysql> GRANT UPDATE,INSERT,SELECT,PROCESS,SUPER,REPLICATION SLAVE ON . 'checksum'@'192.168.%' IDENTIFIED BY "check_pass";    #创建checksum用户用于执行检测，以及分配检测时要用的权限。
  mysql> GRANT ALL ON pt.* TO checksum@'192.168.%';       #checksum用户要将checksum的值写入pt数据库中所以需要分配权限给checksum用户。
  权限解释：

  select     //查看所有库的表,原理可加 explain选项查看
  process    //show processlist
  super      //set binlog_format='statement'
  replication slave   //show slave hosts

  注意 : 在master上执行一致性检测时，master会通过show processlist查看slave主机，并通过连接master的账号和密码连接slave，所以master上一致性检测的账号在slave上一定要有。

• 2）在master上执行一致性检测
  首先我们需要人为创造不一致，在slave中删除一条记录
  
  执行一致性检测（主从上都可执行）
  ~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --databases=hellodb -u 'checksum' -p 'check_pass' -h 192.168.239.135

                     TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
  03-14T16:25:21               0      1                  8              1       0           0.011 hellodb.classes
  03-14T16:25:21               0      0                 14             1       0           0.017 hellodb.coc
  03-14T16:25:21               0      0                 7               1       0           0.032 hellodb.courses
  03-14T16:25:21               0      0                15              1       0          0.015 hellodb.scores
  03-14T16:25:21               0      0                25              1       0          0.016 hellodb.students
  03-14T16:25:21               0      0                4                1       0          0.018 hellodb.teachers
  03-14T16:25:21               0      0                0                1       0          0.016 hellodb.toc

  显示数据解释
  TS : 完成检测表时的时间，不显示年份。
  ERRORS : 在checksum时发生的错误和警告的次数
  DIFFS : 主从之间chunk不同的个数，如果不为0，表明主从数据有不一致的。
  ROWS : 检测表时一个chunk有多少行。如果使用了-where选项，一个表中的chunk可能不同
  CHUNKS : 表被切割成了多少个chunk
  SKIPPED : 由于某种原因跳过检测chunk的数量
  TIME : checksum此表所花的时间。
  TABLE : 被checksum的表明
  由上可知classes表中有数据不一致。

3. 主从同步

主从实现同步，往往都是借助pt-table-checksum产生的checksum表来说实现数据同步。

1）手动同步

~]# pt-table-sync --print --sync-to-master h=192.168.239.136,u=checksum,p=check_pass --databases=hellodb --replicate=pt.checksums
h=192.168.239.136,u=checksum,p=check_pass 指明需要同步的slave主机，以及登录的用户名和密码
--databases=hellodb：指明同步的数据
--replicate=pt.checksums：同步时使用的checksum数据库。
--sync-to-master：会通过show slave status去自动找主云服务器同步数据，如果没有此参数，我们需要通过h p u同时指明master和slave，即两组h p u。
--print：主从不同的数据仅打印出来，并不在从上执行。
此命令在主从上都可执行。输出信息如下：

我们只需在从云服务器上执行REPLACE INTO hellodb.classes(classid, class, numofstu) VALUES ('1', 'Shaolin Pai', '10')这条sql语句即可

2）自动同步

~]# pt-table-sync --execute --sync-to-master h=192.168.239.136,u=checksum,p=check_pass --databases=hellodb --replicate=pt.checksums
--execute：自动修复主从不同的数据
自动同步出现如下错误：

pt-table-sync在实现同时时并不会直接在slave上进行操作，都是在master上执行命令，进而影响slave，这种修改数据的方式更加安全。所以master需要在slave上有对应的权限。
上图显示master在slave上没有delete权限，查看slave分配的权限，如下图可知确实没有delete权限，只需在master上将checksum用户添加delete权限即可

在master上修改checksum的权限，由于主从同步，slave也会修改对应用户权限

mysql GRANT UPDATE,INSERT,SELECT,DELETE,PROCESS,SUPER,REPLICATION SLAVE ON . TO 'checksum'@'192.168.%';

执行数据同步，再次执行checksum检测，可以看到没有不同，而且查看slave中classes数据发现删除的数据又出现了

MySQL的一致性如何检测及实现数据同步就先给大家讲到这里，对于其它相关问题大家想要了解的可以持续关注我们的数据库。我们的板块内容每天都会捕捉一些行业新闻及专业知识分享给大家的。