mysql主从数据一致性 mysql主从一致性校验

1. 场景需求

      2020年春，由我司开发的考试系统项目，经过不懈的运营努力，用户群体每日以指数倍激增，现考虑到数据库的安全可靠和访问性能问题，决定在业务中集成部署Mysql主从复制以实现读写分离等功能；巧的是，在想要进行主从复制操作前，我们的主要业务数据库已经工作了一段时间，现在要添加一台新的从数据库进行主从复制，通过一位发量稀少同事的一番操作，两台主机已经成功部署好了主从复制，但是我们该如何检测主从服务器的数据是否一致，如果不一致怎么同步数据？

2. 参考答案

      我们可以通过采用pt-table-checksum工具来检查主从的一致性，以下是具体流程。

1. 工具安装

[root@MySQL-01 ~]# wget http://www.percona.com/get/percona-toolkit.tar.gz

1. 安装所需依赖

[root@MySQL-01 ~]# yum install perl perl-devel perl-Time-HiRes perl-DBI perl-DBD-MySQL perl-Compress-Raw-Bzip2 perl-Compress-Raw-Zlib perl-Digest perl-Digest-MD5 perl-IO-Compress perl-IO-Socket-IP perl-IO-Socket-SSL perl-Mozilla-CA perl-Net-Daemon perl-Net-LibIDN perl-Net-SSLeay ...

1. 安装工具

[root@MySQL-01 ~]# tar zxf percona-toolkit-2.2.13.tar.gz [root@MySQL-01 ~]# cd percona-toolkit-2.2.13 [root@MySQL-01 ~]# perl Makefile.PL [root@MySQL-01 ~]# make && make install

1. 在进行主从校验之前，我们首先需要对主从库进行授权，来看看如何来做吧

主库授权

root@node1 12:28: [pt_check]> GRANT CREATE,INSERT,SELECT,DELETE,UPDATE,LOCK TABLES,PROCESS,SUPER,REPLICATION SLAVE ON *.* TO 'root'@'47.97.218.145' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) root@node1 12:29: [pt_check]> flush privileges; Query OK, 0 rows affected (0.00 sec) root@node1 12:29: [pt_check]> select Host,User from mysql.user; +----------------+---------------+ | Host | User | +---------------------+--------------------+ | localhost | root | | localhost | mysql.session | | localhost | mysql.sys | | 172.16.156.% | rep | | % | java | | 192.168.1.101 | ptuser | +----------------------+---------------------+

从库授权

MariaDB [(none)]> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'root'@'9.106.84.122' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec)

授权到主从两个数据库之后呢，我们就可以开开心心的，进行主从数据一致性的校验。且看下面

pt-table-checksum是通过在主（master）上通过执行校验的查询对复制的一致性进行检查，对比主从的校验值，从而产生结果。

DSN指向的是主的地址，该工具的退出状态不为零，如果发现有任何差别，或者如果出现任何警告或错误，可以查看官方资料。

现在有一主库，数据如下：

mysql> select * from yayun.t1; +----+-------+ | id | name | +----+-------+ | 1 | yayun| | 2 | atlas | | 3 | mysql | +----+-------+ 3 rows in set (0.00 sec) # 主库有三条数据

       而从库的数据于主库是不一致的，我们来欣赏下从库不一致的数据：

mysql> select * from yayun.t1; +----+----------+ | id | name | +----+----------+ | 1 | yayun | | 2 | atlas | | 3 | mysql | | 4 | dengyy | | 5 | love sql | +----+----------+ 5 rows in set (0.00 sec) # 从库有五条数据，多了俩条不一样的：4、5

      接下来，我们通过工具进行检测：

[root@MySQL-01 ~]# pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --replicate=pt.checksums --databases test -u'root' -p'123456' -h127.0.0.1 -P3306

      命令会展示如下的结果

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 04-13T15:59:31 0 1 3 1 0 0.080 yayun.t1

      其中如上表所示的结果中，各字段解释如下

TS	完成检查的时间
ERRORS	检查时候发生错误和警告的数量
DIFFS	0表示一致，1表示不一致
ROWS	表的行数
CHUNKS	被划分到表中的块的数目
SKIPPED	由于错误或警告或过大，则跳过块的数目
TIME	执行的时间
TABLE	被检查的表名

       清晰的看到，当主从之间数据不一致时，进行一致性检查看到的结果DIFF为1。此时代表一致性是错误的。

      上面命令的各参数的意思在这里也做好事做到底来解释一下：

--nocheck-replication-filters：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。

--no-check-binlog-format：       不检查复制的binlog模式，要是binlog模式是ROW，则会报错。

--replicate-check-only :              只显示不同步的信息。

--replicate=：                     把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。

--databases=：                          指定需要被检查的数据库，多个则用逗号隔开。

--tables=：                                指定需要被检查的表，多个用逗号隔开

h=127.0.0.1：                      Master的地址

u=root：                            用户名

p=123456：                               密码

P=3306：                                   端口

       通过DIFFS是1就可以看出主从的表数据不一致。怎么不一致呢？ 通过指定—replicate=yayun.checksums 参数，就说明把检查信息都写到了checksums表中。

       进入SLAVE相应的库中查看checksums表的信息：

mysql> select * from checksums\G *************************** 1. row *************************** db: yayun tbl: t1 chunk: 1 chunk_time: 0.010735 chunk_index: NULL lower_boundary: NULL upper_boundary: NULL this_crc: babf1dc0 #slave this_cnt: 5 #slave master_crc: 8727436a #master master_cnt: 3 #master # 可以发现表t1中从库比主库多2条记录 ts: 2014-04-13 16:05:16 1 row in set (0.00 sec)

       通过上面找到了这些不一致的数据表，如何同步数据呢？即如何修复MySQL主从不一致的数据，让他们保持一致性呢？利用另外一个工具pt-table-sync。

       这个命令的使用很简单，他的语法是这个：pt-table-sync [OPTIONS] DSN [DSN]。pt-table-sync，高效的同步MySQL表之间的数据，他可以做单向和双向同步的表数据；他可以同步单个表，也可以同步整个库。

       它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。

      接着上面的复制情况，主和从的t1表数据不一致，需要修复，首先执行：

[root@MySQL-01 ~]# pt-table-sync --replicate=pt.checksums h=47.97.218.145,u=root,p=123456 h=39.106.84.122,u=root,p=123456 --print DELETE FROM `yayun`.`t1` WHERE `id`='4' LIMIT 1 /*percona-toolkit src_db:yayun src_tbl:t1 src_dsn:h=127.0.0.1,p=...,u=root dst_db:yayun dst_tbl:t1 dst_dsn:h=192.168.0.20,p=...,u=root lock:1 transaction:1 changing_src:yayun.checksums replicate:yayun.checksums bidirectional:0 pid:2190 user:root host:MySQL-01*/; DELETE FROM `yayun`.`t1` WHERE `id`='5' LIMIT 1 /*percona-toolkit src_db:yayun src_tbl:t1 src_dsn:h=127.0.0.1,p=...,u=root dst_db:yayun dst_tbl:t1 dst_dsn:h=192.168.0.20,p=...,u=root lock:1 transaction:1 changing_src:yayun.checksums replicate:yayun.checksums bidirectional:0 pid:2190 user:root host:MySQL-01*/;

      参数的意义：

--replicate=  ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。 --databases=  : 指定执行同步的数据库，多个用逗号隔开。 --tables=     ：指定执行同步的表，多个用逗号隔开。 --sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。 h=127.0.0.1   ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。 u=root             ：帐号。 p=123456    ：密码。 --print       ：打印，但不执行命令。 --execute     ：执行命令。

      命令介绍完了，一起解释下执行的效果：通过（--print）打印出来了修复数据的sql语句，可以手动的去从行执行，让他们数据保持一致性。那能否直接执行？当然可以，通过(--execute)：

[root@MySQL-01 ~]# pt-table-sync --replicate=pt.checksums h=47.97.218.145,u=root,p=123456 h=39.106.84.122,u=root,p=123456 --execute

      没发现任何异常，然后检查主从数据的一致性：

[root@MySQL-01 ~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases test -u'root' -p'123456' -h47.97.218.145 -P3306 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 04-13T16:27:28 0 0 3 1 0 0.097 yayun.t1

      再来看看对应的数据库吧：

主库

mysql> select * from t1; +----+-------+ | id | name | +----+-------+ | 1 | yayun | | 2 | atlas | | 3 | mysql | +----+-------+ 3 rows in set (0.00 sec) mysql>

从库会丢失掉之前多余的两条数据

mysql> select * from t1; +----+-------+ | id | name | +----+-------+ | 1 | yayun | | 2 | atlas | | 3 | mysql | +----+-------+ 3 rows in set (0.00 sec)

      OK，数据已经保持一致了。

       不过来自1902a班的张磊建议还是用—print 打印出来的好，这样就可以知道那些数据有问题，可以人为的干预下。不然直接执行了，出现问题之后更不好处理。总之还是在处理之前做好数据的备份工作。

      注意：要是表中没有唯一索引或则主键则会报错。

Can't make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.

       最后，这些工具很给力，工作中常常在使用。注意使用该工具需要授权，一般SELECT, PROCESS, SUPER, REPLICATION SLAVE等权限就已经足够了。