MySQL主从(二)--数据校验

最新推荐文章于 2022-12-20 16:24:31 发布

独孤清扬玩DB

最新推荐文章于 2022-12-20 16:24:31 发布

阅读量1.7k

点赞数 1

分类专栏： MySQL数据库文章标签： mysql 数据校验 pt checksum pt table sync

本文链接：https://blog.csdn.net/u010033674/article/details/104463278

版权

MySQL数据库专栏收录该内容

35 篇文章 0 订阅

订阅专栏

文章目录

4、pt-table-checksum校验主从数据
5、pt-table-sync修复数据

4、pt-table-checksum校验主从数据

4.0、参考资料和安装

参考文档：

https://www.percona.com/doc/percona-toolkit/3.0/pt-table-checksum.html
https://www.cnblogs.com/dbabd/p/10653408.html
http://seanlook.com/2015/12/29/mysql_replica_pt-table-checksum/
http://keithlan.github.io/2016/05/25/pt_table_checksum/

安装：

https://www.percona.com/downloads/percona-toolkit/LATEST 地址下载最新的rpm包；

yum install percona-toolkit-*.rpm -y

4.1、pt-table-checksum校验原理

pt-table-checksum是percona-toolkit系列工具中的一个，可以用来检测主、从数据库中数据的一致性。通过开启general_log观察主从库的动作，可以得知：

1.连接主库和从库，查询当前数据库服务器信息，包括参数设置，负载信息等；
2.根据工具选项设置会话级别参数，设置会话级binlog row format为STATEMENT；
3.根据工具选项创建校验结果表(默认为percona.checksums表)，查看当前数据库服务器运行状态；
4.获取校验的数据库和表，逐张顺序进行检查校验；
5.开始分析表，根据表的索引(如有)，将表分成多个chunk，每个chunk包含多行，默认为1000，chunk对应的表行数可以根据数据库性能状态动态调整；
6.根据以上分析生成表的校验语句，并检查表结构；
7.开始进行表的校验分析，为了保证一致性，这个阶段会将当前chuck所包含的行加上行锁，并将校验的结果以replace into方式存入校验结果表；
8.再次查询校验结果表，并更新master_crc、master_cnt的值(主库)，如果是从库则是this_crc、this_cnt；
9.根据数据库运行状态调整下一个chunk所包含行数；
10.继续下一个chunk的校验检查，直到表中所有的chunk校验完成；
11.从库运行完校验检查，汇总这张表的结果；
12.循环完成所有需要校验的表直到完成所有的表的校验操作。

4.2、注意事项和常用参数说明

4.2.1、注意事项

1、校验数据的是后主从库必须保持同步，确保从库的IO和SQL进程是YES状态，否则脚本会发生等待
2、如果--recursion-method=processlist或者hosts时，需要一个即能登录主库，也能登录从库的账号。
3、--host参数只能指定一个host，必须为主库的IP;并且--port --user --databases --password均指的是主库的信息。
4、在检查时会向表加S锁。
5、如果master和slave的binlog日志不是STATEMENT格式，要用--no-check-binlog-format选项。
6、表要有主键索引或唯一键索引。对于一个chunk可以放得下的小表，可以没有主见。大表没有主键会被忽略，有如下告警信息：
 Cannot checksum table mtest.bbb: There is no good index and the table is oversized. at /bin/pt-table-checksum line 6743
7、不支持级联从库的数据校验。

4.2.2、常用参数说明

参数	说明
–nocheck-replication-filters	不检查复制过滤器，建议启用。后面可以用–databases来指定需要检查的数据库。
–no-check-binlog-format	不检查复制的binlog模式，要是binlog模式是ROW，则会报错。
–replicate-check-only	只显示不同步的信息。
–replicate=	把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。
–databases=	指定需要被检查的数据库，多个则用逗号隔开。
–tables=	指定需要被检查的表，多个用逗号隔开
–nocheck-binlog-format	非statement格式时启用
–ignore-databases	选择忽略的库
–recursion-method	找slave信息的方式，默认processlist
–host	Master的地址
–port	Master的端口
–user	Master的用户
–password	Master的密码
–databases	Master的库名

关于–recursion-method=dsn方式的使用说明

METHOD	USES
processlist	SHOW PROCESSLIST
hosts	SHOW SLAVE HOSTS
cluster	SHOW STATUS LIKE ‘wsrep\_incoming\_addresses’
dsn=DSN	DSNs from a table
none	Do not find slaves

来自官方的说明：

The dsn method is special: rather than automatically discovering replicas, this method specifies a table with replica DSNs. 
The tool will only connect to these replicas. 
This method works best when replicas do not use the same MySQL username or password as the master, or when you want to prevent the tool from connecting to certain replicas. 
The dsn method is specified like: --recursion-method dsn=h=host,D=percona,t=dsns. The specified DSN must have D and t parts, or just a database-qualified t part, which specify the DSN table. 
The DSN table must have the following structure:

意思是，dsn是一种特殊而又灵活的方式，把slave的信息存放到表中，然后使用dsn=h=host…这样的方式去连接指定的数据库，去获取表数据。无论是主库、从库还是其它库，只要有权限访问即可。表结构如下：

CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

举例：

--recursion-method dsn=h=1.1.3.9,u=test,p=Test_123,P=3306,D=mydb9,t=dsns

参与校验数据的从库信息，插入到dsn这个字段中,可以多个，按照id排序：

insert into dsns(dsn) values ('h=1.1.3.111,u=root,p=1234.C0m,P=3306');
insert into dsns(dsn) values ('h=1.1.3.9,u=root,p=1234.C0m,P=3306');

检查结果说明

标题	说明
TS	完成检查的时间。
ERRORS	检查时候发生错误和警告的数量。
DIFFS	0表示一致，1表示不一致。当指定–no-replicate-check时，会一直为0，当指定–replicate-check-only会显示不同的信息。
DIFF_ROWS	主库和从库差异的数据行数
ROWS	表的行数。
CHUNKS	被划分到表中的块的数目。
SKIPPED	由于错误或警告或过大，则跳过块的数目。
TIME	执行的时间。
TABLE	被检查的表名

DIFF_ROWS数据特殊说明：

在1主2从架构中的测试中发现，只要主库和任何一个从库的数据不一致，diffs都标记为1；
但是diff_rows计数并不是主库和所有从库数据的差异总和或者选取最小差异的从库，而是主库数据逐个和从库对比，选取最后一个对比的结果。比如：

从库2排在第二位：

+------------+-----------+------+------------+--------------------------------------+
| Server_id  | Host      | Port | Master_id  | Slave_UUID                           |
+------------+-----------+------+------------+--------------------------------------+
| 2020021325 | 1.1.3.111 | 3306 | 2019010155 | e34b371d-4e4f-11ea-b9e6-000c29aa5c5d |
| 2020022219 | 1.1.3.9   | 3306 | 2019010155 | b6031e42-5560-11ea-aa8a-000c2921de41 |
+------------+-----------+------+------------+--------------------------------------+

主库：0条

root@localhost [mtest]> select * from cqy;
Empty set (0.00 sec)

从库1 (server_id 2020021325): 3条

root@localhost [mtest]> show variables like '%server_id%';
+----------------+------------+
| Variable_name  | Value      |
+----------------+------------+
| server_id      | 2020021325 |
| server_id_bits | 32         |
+----------------+------------+

root@localhost [mtest]> select * from cqy;
+------+------+
| name | id   |
+------+------+
| test |    1 |
| test |    2 |
| test |    3 |
+------+------+

从库2 (server_id 2020022219)：1条

mysql> select * from cqy;
+------+------+
| name | id   |
+------+------+
| test |    1 |
+------+------+

校验结果：DIFF_ROWS = 1

            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
02-22T22:31:10      0      0   262144          0       5       0   0.801 mtest.aaa
02-22T22:31:11      0      0        0          0       1       0   0.327 mtest.bbb
02-22T22:31:11      0      1        0          1       1       0   0.328 mtest.cqy
02-22T22:31:11      0      0        0          0       1       0   0.330 mtest.dsns
02-22T22:31:12      0      0        1          0       1       0   0.344 mtest.inttest
02-22T22:31:12      0      0        0          0       1       0   0.367 mtest.medivac
02-22T22:31:12      0      1        2          0       1       0   0.338 mtest.t

如果加上参数–replicate-check-only，只显示有差异的结果，就可以显示出所有从库的差异：

Checking if all tables can be checksummed ...
Starting checksum ...
Differences on onetest
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
mtest.cqy 1 3 1   
mtest.t 1 -2 1   

Differences on rpmtest
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
mtest.cqy 1 1 1

4.3、最佳实践

示例说明：

1.1.3.111 3309 主库
1.1.3.111 3306 从库
1.1.3.9   3306 从库

首先需要创建个用户并授权：

create user root@'1.1.3.111' identified by 'xxxx';
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'root'@'1.1.3.111';

–recursion-method=processlist方式，需要主备库有相同的用户名和密码，并且端口一致：

pt-table-checksum --nocheck-binlog-format --replicate=mtest.testchk --ignore-databases=mysql --recursion-method=processlist --host=1.1.3.111 --port=3309 --user=root --databases=mtest --password=bigbangdata.cn

–recursion-method=hosts方式，需要主备库有相同的用户名和密码，并且主库show slave hosts可以看到slave的ip和端口：

pt-table-checksum --nocheck-binlog-format --replicate=mtest.testchk --ignore-databases=mysql --recursion-method=hosts --host=1.1.3.111 --port=3309 --user=root --databases=mtest --password=bigbangdata.cn

–recursion-method dsn=h=host,D=percona,t=dsns，比较灵活的方式，主备库可以有不通的用户、密码、端口：

1)、存储dsn信息的表

CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

2)、dsn列信息，for example: “h=replica_host,u=repl_user,p=repl_pass”

insert into dsns(dsn) values ('h=1.1.3.111,u=root,p=1234.C0m,P=3306');

可以插入多行，slave库的连接信息。
3)、执行脚本

pt-table-checksum --nocheck-binlog-format --replicate=mtest.testchk --ignore-databases=mysql --recursion-method dsn=h=1.1.3.9,u=test,p=Test_123,P=3306,D=mydb9,t=dsns --host=1.1.3.111 --port=3309 --user=root --databases=mtest --password=bigbangdata.cn

脚本说明：

dsn=h=1.1.3.9,u=test,p=Test_123,P=3306,D=mydb9,t=dsns

获取dsn信息表的连接串，可以和主从库无关。

–replicate-check-only，只显示有差异的结果：

pt-table-checksum --nocheck-binlog-format --replicate=mtest.testchk --ignore-databases=mysql --recursion-method=hosts --host=1.1.3.111 --port=3309 --user=root --databases=mtest --password=bigbangdata.cn --replicate-check-only

4.4、常见问题排查

4.4.1、Diffs cannot be detected because no slaves were found

这是在使用pt-table-checksum工具遇到最多的问题，当看到如下提示信息时代表着校验失败

Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.

错误也给出了提示，要你去看看官方文档–recursion-method的用法，其实看明白了用法，知道了是如何查找从库信息的，问题也就迎刃而解了。

processlist方式查找从库信息：

root@localhost [mtest]> show processlist;

+-----+------+-----------------+-------+------------------+-------+---------------------------------------------------------------+------------------+
| Id  | User | Host            | db    | Command          | Time  | State                                                         | Info             |
+-----+------+-----------------+-------+------------------+-------+---------------------------------------------------------------+------------------+
|  97 | repl | 1.1.3.111:12759 | NULL  | Binlog Dump GTID | 49911 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 115 | repl | 1.1.3.9:35554   | NULL  | Binlog Dump GTID | 45133 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 126 | root | localhost       | mtest | Query            |     0 | starting                                                      | show processlist |
+-----+------+-----------------+-------+------------------+-------+---------------------------------------------------------------+------------------+
3 rows in set (0.04 sec)

当在主库上使用show processlist去发现从库信息时，只能获取到从库ip信息。但命令行参数中只传入了主库的用户名、密码、端口等信息，所以就要求主备库要有一样的用户名、密码、端口，否则就会找不到从库信息。

hosts方式查找从库信息：

root@localhost [mtest]> show slave hosts;
+------------+-----------+------+------------+--------------------------------------+
| Server_id  | Host      | Port | Master_id  | Slave_UUID                           |
+------------+-----------+------+------------+--------------------------------------+
| 2020021325 | 1.1.3.111 | 3306 | 2019010155 | e34b371d-4e4f-11ea-b9e6-000c29aa5c5d |
| 2020022219 |           | 3306 | 2019010155 | b6031e42-5560-11ea-aa8a-000c2921de41 |
+------------+-----------+------+------------+--------------------------------------+
2 rows in set (0.00 sec)

当使用show slave hosts命令时，有可能看不到从库的ip信息，获取不到信息当然就不能连上从库了。这种情况需要在从库的/etc/my.cnf配置文件中加入参数：从库的ip和端口

report_host=1.1.3.9
report_port=3306

修改参数后重启从库再看看：发现从库的信息已经有了

root@localhost [mtest]> show slave hosts;
+------------+-----------+------+------------+--------------------------------------+
| Server_id  | Host      | Port | Master_id  | Slave_UUID                           |
+------------+-----------+------+------------+--------------------------------------+
| 2020021325 | 1.1.3.111 | 3306 | 2019010155 | e34b371d-4e4f-11ea-b9e6-000c29aa5c5d |
| 2020022219 | 1.1.3.9   | 3306 | 2019010155 | b6031e42-5560-11ea-aa8a-000c2921de41 |
+------------+-----------+------+------------+--------------------------------------+
2 rows in set (0.00 sec)

最后，同样的，还需要主从库上有同样的用户和密码。

5、pt-table-sync修复数据

5.0、参考资料和安装

参考文档：

https://www.percona.com/doc/percona-toolkit/3.0/pt-table-sync.html

https://www.hellojava.com/a/75316.html

安装：

https://www.percona.com/downloads/percona-toolkit/LATEST 地址下载最新的rpm包；

yum install percona-toolkit-*.rpm -y

5.1、pt-table-sync原理

pt-table-sync是percona-toolkit系列工具中的一个，可以用来修复数据。通过开启general_log观察主从库的信息可以得知：

1.开启两个会话连接，一个会话负责校验同步，一个会话负责持续检查服务器状态信息；
2.连接DSN主机对应的主库，检查当前服务器负载信息，参数设置信息，关闭自动提交功能；
3.设置二进制日志格式为STATEMENT，设置会话级别隔离级别为REPEATABLE READ；
4.检查当前连接用户的权限，检查操作表是否被外键约束；
5.通过主键或唯一键(如有)对表进行chunk上边界和下边界的确定，以便更好进行chunk分块操作；
6.表被分成多个chunk进行校验和同步修复，chunk大小由选项--chunk-size控制；
7.完成所有的chunk校验和同步修复，退出。

5.2、工具使用总结

5.2.1、注意事项

特别注意

1、在进行校验分析的同时会对操作的表行执行FOR UPDATE语句进行锁定，所以尽量选择在业务低峰期进行操作，同时避免在高并发场景下进行操作数据以免造成阻塞。
2、当使用--sync-to-master or --replicate选项时，复制格式需要statement格式，脚本会自动在session级别set binlog_format=STATEMENT，所以用户必须要有super权限。
3、如果在master-master双主模式下修复一个没有主键或唯一索引的表时，需要添加选项--no-bin-log，目的是为了防止修改后的数据，反向又同步到源端。

能做的事情：

1、同步MySQL表之间的数据，可以做单向和双向同步的表数据。他可以同步单个表，也可以同步整个库。
2、主从库之间数据可以同步；非主从库之间库名、表名、表结构一致，也可以同步。
3、如果表上没有唯一键，则变更只好在从库进行；但前提需指定选项--no-check-slave，以及不能再制定参数--replicate或--sync-to-master，这时候需要指定两个dsn地址，代表源库和目标库，目标库的数据同步为源库的。

不能做的事情：

1、不能同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。
2、指定选项--replicate或--sync-to-master时，若表中没有唯一索引或则主键则会报错，不能进行数据修复。

5.2.2、常用参数说明

基本用法：

pt-table-sync [OPTIONS] DSN [DSN]

常用选项(OPTIONS)

--[no]bin-log
默认值：yes
指定同步操作记录二进制日志，相当于执行SET SQL_LOG_BIN=1。如果指定'--no-bin-log'，则对应执行SET SQL_LOG_BIN=0。

--channel
指定当主从复制环境是多源复制时需要进行同步哪个主库的数据，适用于多源复制中多个主库对应一个从库的情形。

--charset，-A
指定连接字符集。

--[no]check-master
默认值：yes
指定当选项'--sync-to-master'使用时，尝试验证工具连接到的主库是否是真正的主库。

--[no]check-slave
默认值：yes
指定检查目标服务器是否是从库所在服务器。
如果目标服务器是从库，则对其进行变更是不安全的，但某些情况却必须这么做，比如当主库需要同步的表上没有唯一索引时，指定选项'--replace'是不会进行工作的，所以在这种情况下无法对主库进行变更。默认情况下如果需要对从库上进行变更，则工具会有提示，可以指定选项'--no-check-slave'禁止检查。

--chunk-size
默认值：1000
指定表分块的chunk大小，每个chunk对应的表行数，也可以是数据块大小，当指定大小时允许的后缀单位为k、M、G。

--host，-h
指定连接的数据库IP地址。

--port，-P
指定连接的数据库Port端口。

--user，-u
指定连接的数据库用户。

--password，-p
指定连接的数据库用户密码。

--socket，-S
指定使用SOCKET文件连接。

--databases，-d
指定需要进行同步的数据库，如有多个则用','(逗号)隔开。

--tables，-t
指定仅需要进行同步的表，如有多个则用','(逗号)隔开。表名称可以使用数据库名加以限定。

--columns，-c
指定进行比较的表字段，如有多个则用','(逗号)隔开。

--where
通过where语句条件限制表的同步内容。

--dry-run
分析、选择同步的算法，并打印信息和退出。
意味着指定选项'--verbose'可以得出工具分析的结果。分析结果的输出格式与工具实际执行时的输出一致，但是并不会有数据被影响。

--execute
指定工具执行同步操作使表数据达成一致状态，没有此参数则不执行同步操作。
工具使用此选项意味着将同步指定出现数据不一致的表，因此表的数据将被更改，除非指定了选项'--verbose'，否则表数据的更改将以静默的方式进行。

--ignore-databases
指定需要忽略比较的数据库，如有多个则用','(逗号)隔开，系统数据库information_schema和performance_schema默认被忽略。

--ignore-engines
默认值：FEDERATED，MRG_MyISAM
指定需要忽略同步的存储引擎类型的表，如有多个则用','(逗号)隔开。

--ignore-tables
指定需要忽略同步的表，如有多个则用','(逗号)隔开。表名称可以使用数据库名加以限定。

--lock
指定哪个过程中进行锁表(LOCK TABLES)操作，主要有如下取值：
'
VALUE  MEANING
=====  ===========================================================================
0      永远不进行锁表操作；
1      每个同步周期进行锁表操作，例如锁定每次同步chunk对就的表行，这是最细粒度的锁定级别；
2      在表执行操作的时候进行锁表操作；
3      为连接的每个DSN连接的服务器进行锁表操作，指定语句FLUSH TABLES WITH READ LOCK。
'
当指定选项'--replicate'或'--sync-to-master'时，从库的表是不会被锁定的。如果指定了选项'--wait'，则主库对应的表被锁定，工具暂定执行操作直到从库追上主库才继续执行。
如果指定选项'--transaction'，则锁表操作(LOCK TABLES)不会执行，取代的方式是通过事务的开始和提交来进行锁定操作，例外情况是'--lock=3'，如果指定选项'--no-transaction'，则锁表操作(LOCK TABLES)适应所有'--lock'取值情况。

--print
指定打印工具需要执行哪些查询语句来同步表，解决数据不一致，只是打印输出，并不会真正执行。

--recursion-method
默认值：processlist，hosts
指定获取从库的方式。
'
METHOD       USES
===========  =============================================
processlist  SHOW PROCESSLIST   
hosts        SHOW SLAVE HOSTS   
none         Do not find slaves
==========================================================
'
processlist：通过SHOW PROCESSLIST方式找到slave，为默认方式，当SHOW SLAVE HOSTS不可用时。一旦实例运行在非3306端口上时，hosts方式就会变为默认方式；
hosts：通过SHOW SLAVE HOSTS方式找到slave，hosts方式要求从库配置'--report_host'和'--report_port'这两个参数。

--replicate
指定参照该选项中列出的表进行表同步操作。
工具将在该选项指定的表中去查询数据不一致表的信息并进行同步操作，这个选项跟工具pt-table-checksum当中的同名选项是相同意义的。该选项会自动设置选项'--wait=60'并确保在主库进行变更。
如果指定选项'--sync-to-master'，则工具会假设指定的连接是从库，并会寻找主库连接进行表数据同步操作，如果指定的连接不是从库，工具将使用选项'--recursion-method'中查找从库方法进行查找操作，然后再找到需要进行同步操作的表。

--sync-to-master
指定将DSN连接信息确认为从库，并同步信息到主库。
该选项将指定的服务器当作是从库，并且检查从库的主库，连接主库。将主库作为数据同步的源端，从库作为数据同步的目标端进行同步。选项会默认设置选项'--wait=60'和'--lock=1'，并且设置选项'--no-transaction'。

--timeout-ok
指定当选项'--wait'导致工具执行失败时跳过失败继续执行。
如果指定了选项'--wait'，但是从库在指定时间内还是没能追上主库且依然存在主从延迟，则工具将中止操作并退出。指定选项'--timeout-ok'则会继续执行操作。

--verbose，-v
指定打印更详细的操作信息。

DSN选项(DSN)说明：

可以使用DSN方式来连接数据库，DSN选项为key=value方式，在等号的两侧不能有空格出现，并且区分大小写，多个选项之前以’,’(逗号)隔开，主要选项如下：

选项	说明
A	指定字符集
D	指定需要同步数据库
t	指定需要同步的表
h	指定要连接的HOST
P	指定要连接的PORT
S	指定连接所使用的SOCKET文件(Unix systems)
u	指定连接的用户名
p	指定连接的用户名密码

示例：

h=192.168.58.3,P=3306,D=employees,t=employees

5.2.3、–replicate选项说明

pt-table-sync工具使用起来有点复杂，它可以通过很多不同方式起作用，其中选项–replicate的使用对于工具至关重要，以下关于该选项的使用作进一步的说明。

该选项的使用逻辑如下：

一、如果不指定--replicate选项，DSN选项中有涉及到表选项t，则只同步指定的表：
  1.如果DSN只有1个主机信息，并指定选项--sync-to-master：
    1).如果DSN代表的是从库，工具也会连接它的主库并且同步；
    2).如果DSN代表的是主库，工具会报错找不到主库。
  2.如果DSN里大于1个主机信息：
    1).第1个DSN主机是源端数据库(并不区分主库和从库)，按顺序向之后DSN主机同步。如果第一个是DSN主机是从库的话会将从库的数据同步到主库；
    2).如果第1个DSN主机是主库，确保同步的表有唯一索引以便在主库执行REPLACE变更操作，并指定选项--no-check-slave。
二、如果指定--replicate选项，证明已经存在保存数据差异结果的表(可以先使用工具pt-table-checksum进行校验)：
  1.指定--sync-to-master选项：
    1).当这两个选项一起使用时，只允许有1个DSN主机，否则工具会报错退出。DSN代表的是从库。工具会连接它的主库，找出差异数据并进行同步修复。
  2.不指定--sync-to-master选项：
    1).DSN代表的是主库。工具会找出所有从库并连接，找出差异数据并进行同步修复。
三、其他情况，不指定选项--replicate和--sync-to-master：
  1.有多个DSN主机：
    1).通过--databases或者--ignore-databases等过滤选项找出指定数据库所有表数据的差异，以DSN第1个主机为主，并同步差异到DSN其余所有主机。

总结：

1.如果DSN只有1个主机信息，则必须指定选项–sync-to-master或者–replicate其中之一，否则报错；

2.如果有指定选项–replicate和–sync-to-master，或者两者其中之一，参考以上使用逻辑；

3.如果DSN不只1个主机信息，并且都没有指定选项–replicate和–sync-to-master，则以第1个主机为主，同时指定选项–no-check-slave，在其余主机上同步差异数据。

5.3、使用案例

如果表上具有唯一键(主键)时，对于主从复制架构来说，最理想的做法是指定选项–replicate或–sync-to-master将同步需要执行的变更语句放在主库上执行，并将变更的操作通过主从复制传递给从库来执行。

5.3.1、只有1个DSN主机

1).只指定选项–sync-to-master

因为只有1个DSN主机并且指定了选项–sync-to-master，则DSN主机对应为从库的连接串，可以先使用选项–dry-run查看执行信息;使用–dry-run参数会覆盖掉–execute参数，所以下面的语句不会执行同步：

pt-table-sync --execute --sync-to-master --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose --dry-run

去掉–dry-run,可以看到已经对表cqy进行了修复，从库上删除一行记录：

[mysql@onetest ~]$ pt-table-sync --execute --sync-to-master --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose
# Syncing A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 Chunk     15:53:19 15:53:22 0    mtest.aaa
#      0       0      0      0 Chunk     15:53:22 15:53:22 0    mtest.bbb
#      1       0      0      0 Chunk     15:53:22 15:53:22 2    mtest.cqy
[mysql@onetest ~]$

2).只指定选项–replicate

如果是只指定选项–replicate，则DSN主机对应的为主库的连接串，在这之前选项–replicate指定的表有保存之前数据不一致的校验结果，可以先通过工具pt-table-checksum进行校验，否则并不会进行同步变更修复。

先使用pt-table-checksum工具做一次主从校验,发现bbb和cqy这两张表的数据不一致：

[mysql@onetest ~]$ pt-table-checksum --nocheck-binlog-format --replicate=mtest.testchk --ignore-databases=mysql --recursion-method=hosts --host=1.1.3.111 --port=3309 --user=root --databases=mtest --password=1234.C0m
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
02-23T15:59:32      0      0   262144          0       5       0   0.826 mtest.aaa
02-23T15:59:33      0      1        0          1       1       0   0.333 mtest.bbb
02-23T15:59:33      0      1        0          1       1       0   0.332 mtest.cqy
02-23T15:59:33      0      0        0          0       1       0   0.326 mtest.dsns
02-23T15:59:34      0      0        1          0       1       0   0.326 mtest.inttest
02-23T15:59:34      0      0        0          0       1       0   0.330 mtest.medivac
02-23T15:59:34      0      0        2          0       1       0   0.347 mtest.t
02-23T15:59:35      0      0        0          0       1       0   0.323 mtest.t0
02-23T15:59:35      0      0        3          0       1       0   0.329 mtest.t1
02-23T15:59:35      0      0        1          0       1       0   0.345 mtest.t_tab_test
02-23T15:59:36      0      0        1          0       1       0   0.332 mtest.tab0
02-23T15:59:36      0      0        1          0       1       0   0.337 mtest.tab1

执行修复脚本：1.1.3.111 3309为主库

pt-table-sync --execute --replicate=mtest.testchk --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3309 --databases=mtest --tables=aaa,bbb,cqy --verbose

下面信息可以看出，主库下面的两个从库的数据都被修复了：

# Syncing via replication A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      1       0      0      0 Chunk     16:06:32 16:06:32 2    mtest.bbb
#      1       0      0      0 Chunk     16:06:32 16:06:33 2    mtest.cqy
# Syncing via replication A=utf8,P=3306,h=1.1.3.9,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      1       0      0      0 Chunk     16:06:33 16:06:33 2    mtest.bbb
#      1       0      0      0 Chunk     16:06:33 16:06:33 2    mtest.cqy

3).同时指定选项–sync-to-master和–replicate

因为选项–sync-to-master出现，所以DSN主机对应的为从库的连接串;测试前再次制造主从不一致，并使用pt-table-checksum校验下数据,因为如果没有校验数据信息则不执行任何操作：同样的，还是bbb和cqy表不一致，并且两个从库都不一致

[mysql@onetest ~]$ pt-table-checksum --nocheck-binlog-format --replicate=mtest.testchk --ignore-databases=mysql --recursion-method=hosts --host=1.1.3.111 --port=3309 --user=root --databases=mtest --password=1234.C0m
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
02-23T16:19:51      0      0   262144          0       5       0   0.859 mtest.aaa
02-23T16:19:51      0      1        0          1       1       0   0.329 mtest.bbb
02-23T16:19:52      0      1        0          1       1       0   0.325 mtest.cqy
02-23T16:19:52      0      0        0          0       1       0   0.330 mtest.dsns
02-23T16:19:52      0      0        1          0       1       0   0.337 mtest.inttest
02-23T16:19:53      0      0        0          0       1       0   0.325 mtest.medivac
02-23T16:19:53      0      0        2          0       1       0   0.341 mtest.t
02-23T16:19:53      0      0        0          0       1       0   0.330 mtest.t0
02-23T16:19:54      0      0        3          0       1       0   0.325 mtest.t1
02-23T16:19:54      0      0        1          0       1       0   0.339 mtest.t_tab_test
02-23T16:19:54      0      0        1          0       1       0   0.332 mtest.tab0

执行修复脚本：1.1.3.111 3306为从库1

pt-table-sync --execute --sync-to-master --replicate=mtest.testchk --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose

执行脚本后，发现只有指定的从库1数据被修复了，从库2(1.1.3.9 3306)并没有被修复：

[mysql@onetest ~]$ pt-table-sync --execute --sync-to-master --replicate=mtest.testchk --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose
# Syncing via replication A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      1       0      0      0 Chunk     16:22:08 16:22:08 2    mtest.bbb
#      1       0      0      0 Chunk     16:22:08 16:22:08 2    mtest.cqy

小结：–sync-to-master和–replicate一起指定，可以定向的修复某个从库，单独指定–replicate可以修复主库下所有的从库。
4).不指定选项–sync-to-master和–replicate呢？

当DSN只有1个主机的时候，必须指定至少其中一个选项，否则工具报错退出。

[mysql@onetest ~]$ pt-table-sync --execute  --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3309 --databases=mtest --tables=aaa,bbb,cqy --verbose
Usage: pt-table-sync [OPTIONS] DSN [DSN]

Errors in command-line arguments:
  * At least one DSN is required, and at least two are required unless --sync-to-master or --replicate is specified

pt-table-sync synchronizes data efficiently between MySQL tables.  For more
details, please use the --help option, or try 'perldoc /bin/pt-table-sync' for
complete documentation.

5.3.2、指定多个DSN主机

1).只指定选项–sync-to-master

因为有多个DSN主机，必须确保所列出DSN主机均为从库，否则工具报错退出。

pt-table-sync --execute --sync-to-master --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3306 h=1.1.3.9,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose

从下面的结果看出，指定的两个从库数据都被修复了，和指定主库dsn使用–replicate效果一样：

# Syncing A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 Chunk     16:30:26 16:30:29 0    mtest.aaa
#      1       0      0      0 Chunk     16:30:29 16:30:29 2    mtest.bbb
#      1       0      0      0 Chunk     16:30:29 16:30:29 2    mtest.cqy
# Syncing A=utf8,P=3306,h=1.1.3.9,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 Chunk     16:30:29 16:30:32 0    mtest.aaa
#      1       0      0      0 Chunk     16:30:32 16:30:32 2    mtest.bbb
#      1       0      0      0 Chunk     16:30:32 16:30:33 2    mtest.cqy

2).只指定选项–replicate

–replicate参数必须确保第一个dsn主机为主库，其它dsn地址为从库。

pt-table-sync --execute --replicate=mtest.testchk  --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3309 h=1.1.3.9,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose

测试发现，即使指定了一个从库dsn，但还是会把其它从库的数据给修复了，所以无论主库dsn后面还有没有从库的dsn地址，效果都等同于–replicate后面只指定一个主库dsn。

[mysql@onetest ~]$ pt-table-sync --execute --replicate=mtest.testchk  --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3309 h=1.1.3.9,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy --verbose
# Syncing via replication A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      1       0      0      0 Chunk     16:43:20 16:43:20 2    mtest.bbb
#      1       0      0      0 Chunk     16:43:20 16:43:20 2    mtest.cqy
# Syncing via replication A=utf8,P=3306,h=1.1.3.9,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      1       0      0      0 Chunk     16:43:20 16:43:20 2    mtest.bbb
#      1       0      0      0 Chunk     16:43:20 16:43:21 2    mtest.cqy

3).同时指定选项–sync-to-master和–replicate

当同时指定这两个选项时，DSN主机只允许有一个,并且必须为从库dsn地址，否则工具报错退出。

4).不指定选项–sync-to-master和–replicate

因为都不指定这两个选项，所以DSN主机的顺序必须格外注意，最好是先写主库再写从库，或者根据同步的方向来确定。同时如果需要做同步变更修复的表上没有唯一键(主键)，需要指定选项–no-check-slave直接在从库进行变更修复。

不加–no-check-slave参数

pt-table-sync --execute --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3309 h=1.1.3.111,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy,t --verbose

在把主库的数据同步到从库时，因为表上没有主键，得到了如下报错：

# Syncing A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
Can't make changes on A=utf8,P=3306,h=1.1.3.111,p=...,u=root because it's a slave. See the documentation section 'REPLICATION SAFETY' for solutions to this problem. at /bin/pt-table-sync line 10879.  while doing mtest.aaa on 1.1.3.111
#      0       0      0      0 0         16:52:01 16:52:01 1    mtest.aaa
Can't make changes on A=utf8,P=3306,h=1.1.3.111,p=...,u=root because it's a slave. See the documentation section 'REPLICATION SAFETY' for solutions to this problem. at /bin/pt-table-sync line 10879.  while doing mtest.bbb on 1.1.3.111
#      0       0      0      0 0         16:52:01 16:52:01 1    mtest.bbb
Can't make changes on A=utf8,P=3306,h=1.1.3.111,p=...,u=root because it's a slave. See the documentation section 'REPLICATION SAFETY' for solutions to this problem. at /bin/pt-table-sync line 10879.  while doing mtest.cqy on 1.1.3.111
#      0       0      0      0 0         16:52:01 16:52:01 1    mtest.cqy
Can't make changes on A=utf8,P=3306,h=1.1.3.111,p=...,u=root because it's a slave. See the documentation section 'REPLICATION SAFETY' for solutions to this problem. at /bin/pt-table-sync line 10879.  while doing mtest.t on 1.1.3.111
#      0       0      0      0 0         16:52:01 16:52:01 1    mtest.t

加上–no-check-slave参数

pt-table-sync --execute --charset=utf8 --transaction h=1.1.3.111,u=root,p=1234.C0m,P=3309 h=1.1.3.111,u=root,p=1234.C0m,P=3306 --databases=mtest --tables=aaa,bbb,cqy,t --no-check-slave --verbose

从下面的结果看出，已经完成了没有主键或唯一索引表mtest.t的修复：

# Syncing A=utf8,P=3306,h=1.1.3.111,p=...,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 Chunk     16:55:10 16:55:11 0    mtest.aaa
#      0       0      0      0 Chunk     16:55:11 16:55:11 0    mtest.bbb
#      0       0      0      0 Chunk     16:55:11 16:55:11 0    mtest.cqy
#      1       0      1      0 GroupBy   16:55:11 16:55:11 2    mtest.t

总结

1、只使用1个DSN主机信息，连接的主库，加上选项–replicate，需先进行校验，与pt-table-checksum工具配合使用；

2、只使用1个DSN主机信息，连接的从库，加上选项–sync-to-master，无需先进行校验；
3、使用多个DSN主机信息，按照数据同步方向，顺序写好主机信息，根据实际需求加上选项–no-check-slave；

4、推荐使用多个DSN主机信息的方式，同时避免使用选项–replicate和–sync-to-master，无需先进行校验，只需考虑数据同步的方向。

独孤清扬玩DB

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
1
评论
MySQL主从(二)--数据校验

文章目录4、pt-table-checksum校验主从数据4.0、参考资料和安装4.1、pt-table-checksum校验原理4.2、注意事项和常用参数说明4.2.1、注意事项4.2.2、常用参数说明4.3、最佳实践4.4、常见问题排查4.4.1、Diffs cannot be detected because no slaves were found5、pt-table-sync修复数据5....
复制链接

扫一扫