MySQL 主备数据校验

2016-02-19 Friday     mysql , database

由于各种原因,MySQL 主从架构可能会出现数据不一致的情况出现,为此需要对主备复制的数据进行校验。

在此,简单介绍 Percona-Toolkits 提供的数据校验方式。

简介

由于各种原因,MySQL 主从架构可能会出现数据不一致的情况出现,大致归结为如下几类:

  • 主备方式,且在备库写数据;
  • 执行了 non-deterministic query,如用户自定义函数、rand() 等;
  • 回滚掺杂事务表和非事务表的事务;
  • binlog 或者 relaylog 数据损坏。

在主库执行基于 statement 的 SQL 语句生成主库数据块的 checksum,把相同的 SQL 语句传递到从库,并在从库上计算相同数据块的 checksum,最后,比较主从库上相同数据块的 checksum 值,由此判断主从数据是否一致。

这种校验是分表进行的,在每个表内部又是分块进行的,而且提供了非常多的限流选项,因此对线上服务的冲击较小。

环境搭建

在 CentOS 中可以通过如下方式安装依赖 RPM 包。

# rpm -ivh perl-DBD-MySQL perl-TermReadKey
# rpm -ivh percona-toolkit-x.x.x.el7.x86_64.rpm

三个包分别为 MySQL 驱动、交互密码输入、主备数据校验工具。如果是双主配置,只需要在一个节点部署即可。如果要测试,可以通过 PTDEBUG=1 pt-table-checksum > /tmp/checksum.log 2>&1 测试。

可以通过 pt-table-checksum --help 查看帮助信息,或者 perldoc /usr/local/bin/pt-table-checksum 查看详细帮助。

新建校验结果保存表

分别在主备新建数据校验表。

CREATE TABLE `zabbix`.`checksums` (
  `db` char(64) NOT NULL,
  `tbl` char(64) NOT NULL,
  `chunk` int(11) NOT NULL,
  `chunk_time` float DEFAULT NULL,
  `chunk_index` varchar(200) DEFAULT NULL,
  `lower_boundary` text,
  `upper_boundary` text,
  `this_crc` char(40) NOT NULL,
  `this_cnt` int(11) NOT NULL,
  `master_crc` char(40) DEFAULT NULL,
  `master_cnt` int(11) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`db`,`tbl`,`chunk`),
  KEY `ts_db_tbl` (`ts`,`db`,`tbl`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

主机用户创建

首先需要创建如下用户。

脚本部署主机

CREATE USER 'checker' IDENTIFIED BY 'password';
GRANT SUPER, PROCESS, SHOW DATABASES ON *.* TO 'checker'@'localhost';
GRANT DELETE, INSERT, UPDATE ON zabbix.checksums TO 'checker'@'localhost';

权限配置解析:

  • SUPER: SET @@binlog_format:=”STATEMENT” 设置会话级的 binlog 格式;
  • PROCESS: SHOW GRANTS FOR 查看当前用户的权限信息;
  • SHOW DATABASES: 查看是否存在数据库;
  • DELETE,INSERT,UPDATE: 用于清理、REPLACE INTO、UPDATE checksums 的历史数据;

另外一台主机

CREATE USER 'checker' IDENTIFIED BY 'password';
GRANT PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'checker'@'IP-MASTER';
GRANT SELECT ON zabbix.* TO 'checker'@'IP-MASTER';

权限配置解析:

  • SUPER, REPLICATION CLIENT: SHOW SLAVE STATUS 查看是否已经同步完成;
  • SELECT: EXPLAIN 评估备库数据量;

数据校验命令

$ pt-table-checksum --nocheck-replication-filters --check-slave-tables --chunk-size-limit=1.5 \
  --check-interval=3 --max-lag=1s --nocheck-binlog-format \
  --empty-replicate-table --progress=time,30 \
  --nocreate-replicate-table --replicate=zabbix.checksums \
  --replicate-check --check-plan --replicate-database=zabbix \
  --float-precision=2 --chunk-time=0.5 \
  --user=checker --password=password --port=3306 --host=localhost \
  --databases=somedb --tables=table1,table2,table3

数据不一致检查

可以直接通过如下命令查看数据不一致的表,需要在备库执行。

SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
  FROM checksums
  WHERE (
    master_cnt <> this_cnt
    OR master_crc <> this_crc
    OR ISNULL(master_crc) <> ISNULL(this_crc))
  GROUP BY db, tbl;

执行流程

该工具每次只检测一个表,对于表中的数据会分割成 chunk,一般是 1000 条左右数据(默认会动态调整);这个系统的调用流程可以阐述如下:

设置超时时间

为了减小对线上事务的影响,在主库建立链接后,会先设置会话变量 innodb_lock_wait_timeout=1wait_timeout=10000,也可以通过 --set-vars wait_timeout=500 参数覆盖默认值。

检查 sql_mode

通过 SELECT @@SQL_MODE 命令,确认该变量中不包含 ONLY_FULL_GROUP_BY,因为对于一些正常的函数,该模式仍可能会出错,因此需要通过 SET SQL_MODE 命令重新设置。

设置 binlog_mode

查看当前 SELECT @@binlog_mode 模式,如果不是 STATEMENT,默认会报错直接退出;当然,可以通过 --[no]check-binlog-format 参数不检查该参数。

然后,仍会设置会话变量 /*!50108 SET @@binlog_format := 'STATEMENT'*/

设置隔离级别

设置会话的隔离级别为 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ,主要因为在将 binog_format 设置为 STATEMENT 后,且隔离级别为 RR,那么会报如下的错误 Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT',当然,这正是我们需要的,所以直接忽略该报错信息。

查找备库

通过 get_slaves() 函数查找备库,当然可以在启动时设置不同的备库查找方式,详见 find_slave_hosts() 函数。

拼接 SQL

接下来就是拼接主库执行的 SQL 语句,主要的格式如下:

REPLACE INTO checksum_table(db, tbl, chunk, ...)
  SELECT ..., COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(
    CAST(CRC32(CONCAT_WS('#',...)) AS UNSIGNED)), 10, 16)), 0) AS crc
FROM tables FORCE INDEX(`PRIMARY`)
WHERE ((col >= ?)) AND ((col <= ?));

简单介绍下拼接上述 SQL 时的注意内容:

  • 拼接各个列时,会根据不同的数据类型进行处理,例如以 TIMESTAMP 类型使用 UNIX_TIMESTAMP() 函数;可能为 NULL 时,使用 CONCAT(ISNULL()) 等等;
  • 每次执行查询前会通过 EXPLAIN 生成执行计划,主要关注评估数据量、索引使用情况,执行时会强制使用索引;
  • 通过 WHERE 进行分区,也就是 chunk 块,默认会使执行结果尽量接近 --chunk-time 参数指定的时间,所以每次 chunk 的大小会动态调整。

等待备库执行完成

在执行完上述的 SQL 后,需要等待备库执行完成上述的 SQL 语句,也即是计算备库的 CRC 值。

更新主库校验值

校验完成后在主库更新校验值,SQL 如下,该语句会将主库校验值同步到备库:

UPDATE checksum_table SET chunk_time = ?, master_crc = ?, master_cnt = ?
  WHERE db = ? AND tbl = ? AND chunk = ?;

等待同步完成

接下来就是等待备库执行完成上述的 SQL 语句,也即将主库的校验值同步到备库。在按照 chunk 执行完成之后,接下来只需要去备库的 checksums 表中找 master_cnt <> this_cnt or OR master_crc <> this_crc 的记录即可。

校验函数

简单介绍下上述校验命令中的函数。

COALESCE()

类似于 Oracle 中的 NVL() 函数,语义略有区别,会返回参数中第一个非 NULL 表达式的值,从左到右:

----- 如下函数返回 1
mysql> SELECT COALESCE(NULL, NULL, 1);

通常使用场景为,如果某个字段默认是 NULL,如果不想返回 NULL,而是 0 或其它值,可以使用该函数:

mysql> SELECT COALESCE(field_name, 0) AS value FROM table;

BIT_XOR()

用于 Binary String 的检测,会字段的值逐位进行检测,如果 bit 相同则返回 0,否则返回 1;比较时会按照行逐行进行检测,示例如下:

mysql> CREATE TABLE foobar (id INT);
mysql> INSERT INTO foobar VALUES(100), (100), (120);
mysql> SELECT BIT_XOR(id) FROM foobar;

首先是第一行与第二行比较,结果是 0;然后结果 0 与第三行比较,结果是 120。

CAST()/CONVERT()

CAST() 和 CONVERT() 用来进行类型转换,只是语法不同,可用类型包括了 BINARY、CHAR(N)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED 等:

CAST(value AS type);
CONVERT(value, type);

CONV()

语法为 CONV(N,from_base,to_base),该函数用于数字的基数转换,最小基数值是2,最大值为36,如果任一参数为NULL,则该函数返回NULL。

----- 将数字5从基数16转为基数2
SELECT CONV(5,16,2);

配置选项

接下来,看下执行命令时的常用参数。

安全选项

  • --[no]check-replication-filters (可选)

是否检查复制过滤规则,默认会检查,如果检查到有设置过滤规则会直接退出,主要防治检查的表在主库存在,而备库不存在导致 SQL 异常,主备复制出错;

  • --[no]check-slave-tables (设置)

默认检查,查看是否所有从库都有被检查的表和列,防止由于改程序导致主备复制异常;每次同时会通过 EXPLAIN 检查备库的执行计划,评估行数;

  • --chunk-size-limit=2.0 (设置,尽量确保有主键或者唯一索引)

每次执行校验 SQL 前,会通过 EXPLAIN 检查本次执行SQL的数据量,对于没有唯一索引的表,通过 EXPLAIN 查看的行数差异会比较大,该参数就用于设置最大可以超过 chunk-size 几倍;如果超过 chunk-size*chunk-size-limit,那么就不会对本次的 chunk 做校验。

如果是 1.0,就以 chunk-size 为准;是 0.0 则表示不对 chunk 大小做校验。

限速选项

  • --check-interval=1 (可选)

需要睡眠多久,再检查一次主从延迟是否超过 max-lag,默认是 1 秒;

  • --max-load=Threads_running=25 (建议设置)

设置最大负载,当超过这个值时就等待,防止由于工具导致过载,默认是 Threads_running=25 等价于 Threads_running:25,可以逗号分隔设置多个值;如果不指定值则会在开始检查变量对应的值,并将上限设置为 120%;

每次执行完一个 chunk 之后,会通过 SHOW GLOBAL STATUS 查看当前服务器的状态,如果超过了上述值,就会等待;如果校验 SQL 导致锁等待,则会导致队列增加,进而 Threads_running 增加,通过该参数可以减小系统负载。

  • --check-slave-lag=”h=slave1” (可选)

是否只检查这个从库的复制延迟,如果有些场景通过 pt-slave-delay 特意设置了延迟复制,那么可以通过该参数指定需要关注哪些复制延迟。

  • --max-lag=1s (设置)

检查备库的最大延迟,也就是 Seconds_Behind_Master 参数,当超过这个就等待,而且如果备库复制线程异常,同样会等待;默认会检查所有库,也可以通过 check-slave-lag 指定需要检查哪些库。

过滤选项

可以指定只检查哪些数据库、表、列、存储引擎等,也可以使用 Perl 正则表达式;除此之外,也可以指定忽略哪些。

  • --ignore-databases
  • --ignore-databases-regex
  • --ignore-tables
  • --ignore-tables-regex
  • --ignore-columns
  • --ignore-engines
  • --databases
  • --databases-regex
  • --tables
  • --tables-regex
  • --columns
  • --engines

主库登陆选项

  • --host
  • --user
  • --password
  • --port
  • --ask-pass
  • --socket

其它参数:

  • --[no]empty-replicate-table

默认yes,会在每次执行校验前清理要校验表对应的记录,注意该操作不会TRUNCATE TABLE,如果需要,则要手动执行;

  • --progress=time,30

输出当前的进展,有三种输出方式,包括了两个参数值,默认是 time,30,还可以设置为 percentage,time,iterations;

  • --explain

打印要执行的命令,但是不执行 (dry run);

  • --[no]create-replicate-table

检查 replicate 参数指定的表,如果不存在是否创建;

  • --replicate=percona.checksums

将校验结果写入到那个表中,默认是 percona.checksums,可以通过该参数指定;

  • --binary-index (默认不开启测试)

默认通过 replicate 指定的表的 lower_boundary、upper_boundary 字段格式为 text,有些情况下可能会存在不兼容,通过该参数将定义上述字段为 blob 格式;

  • --[no]replicate-check

默认 yes,也就是每次校验完一个表之后,会等待各个备库同步完成,从而进行数据校验,如果关闭则需要通过 replicate-check-only 参数再检查一遍;

  • --replicate-check-only

不执行数据校验的 SQL,只查询保存的结果;

  • --run-time=NUM

要执行多长时间,每个循环会检测是否达到该参数设置值,可以指定单位,默认是秒,可以指定参数 s=seconds,m=minutes,h=hours,d=days;如果使用 --resume 那么之前的时间也会计算在内;

  • --recurse=NUM

在查找复制库时指定递归层次,可以查看 recurse_to_slaves() 函数;

  • --recursion-method=processlist

递归查找时的方法,包括了 processlist(SHOW PROCESSLIST)、hosts(SHOW SLAVE HOSTS)、none,详细查看 recurse_to_slaves() 函数;默认在启动时会检查所有的级联备库,可以通过该参数确认递归方法,通过 recurse 设置检查级联数;

  • --[no]check-binlog-format

检查各个节点的格式是否相同,一般需要保证 binlog 格式为 STATEMENT 。

  • --[no]check-plan

每次在执行正式查询前,默认会通过 EXPLAIN 查看执行计划,然后判断执行计划是否有问题,有可能会跳过本 chunk;

  • --replicate-check-retries

当出现校验异常时,会尝试多少次;

  • --replicate-database

默认会在执行过程中通过 USE 切换数据库,如果设置了 binlog_ignore_dbreplicate_ignore_db 类型的参数,那么可能会导致错误,通过该参数可以防止切换数据库;

  • --chunk-index

优先选择使用的索引,如果索引不存在则自动选择合适的索引,然后在执行 SQL 时会通过 FORCE INDEX 指定索引,当然如果指定错的话可能会有问题,建议只针对单表设置;

  • --chunk-index-columns

只使用索引最左匹配的几个列,通常可以在 MySQL 优化器有问题时进行设置;

  • --chunk-size

每次校验查询时的查询数据行数,可以使用 k,M,G 后缀,不建议直接使用,最好设置 chunk-time;默认会在每次查询时动态调整尽量接近 chunk-time 值,不指定时采用默认值作为起始值,后续的查询则动态调整,如果指定则严格按照该参数进行查询;

  • --chunk-time

如上,每次查询会动态调整 checksum 查询的记录数,使执行时间尽量接近该值,单位是秒;

  • --config

指定配置文件,如果指定则需要设置为命令行的第一项;

  • --defaults-file

指定 MySQL 配置文件,需要使用绝对路径;

  • --float-precision

用于浮点数四舍五入,会通过 ROUND() 函数处理,防止在不同平台上由于精度不同导致校验失败,例如为2时对于 1.008 和 1.009 会四舍五入为 1.01;

  • --function

指定数据校验的函数,如果采用 CRC32 可能会有 hash 冲突,而 SHA1+MD5 对 CPU 消耗比较大,也可以使用 FNV1A_64()、MURMUR_HASH() 自定义函数进行处理;

  • --pid

指定 PID 文件,一般只允许一个实例部署;

  • --plugin

指定 Perl 插件;

  • --quiet

使用一次时只打印 Errors、Warnings、数据不一致;使用两次则只输出错误信息;

  • --resume

继续上次中断;

  • --retries

发生非致命问题时(锁等待超时、查询被 kill 等),每个 chunk 的重试执行次数;

  • --separator

执行 CONCAT_WS() 函数时指定的分隔符,默认是 #;

  • --set-vars

设置会话变量,默认会设置 wait_timeout=10000innodb_lock_wait_timeout=1 两个参数;

  • --version

查看当前版本;

  • --[no]version-check

自动检查版本,用于更新,除了检查 Percona-toolkit 版本之外,还会检查 Perl 以及 MySQL 的版本,并打印已知问题;

  • --chunk-size=1000

每次 checksum 校验时查询的记录数,如果指定了该参数,则将 chunk-time 设置为 0;

  • --chunk-time=0.5

每次动态调整 checksum 记录数,使执行时间尽量接近该值,单位是秒;

  • --slave-skip-tolerance

如果设置主库一个 chunk 校验完,而备库可能超过限制大小,那么就会跳过备库的查询;

  • --trim

主要是针对 VARCHAR 类型的数据列,主要用于 4.1 和 >=5.0 的数据对比,5.0 之后会保存 VARCHAR 类型开始与结尾的空格,而 4.1 则会自动去除;

  • --where

用于过滤部分数据,与 mysqldump 的 -w 选项类似;

FAQs

1. binlog在row模式下是否支持?

工具在执行时会将会话变量binlog格式设置为statement;如果级联开启了–log-slave-updates选项,而binlog_format=ROW,那么此时仍有可能在会导致下一层的级联校验失败。

2. 如何做到流量控制?

提供了多个参数进行流量控制,每次执行完一个chunk之后,会检查参数是否满足限流要求,如果超过限制则睡眠等待,直到满足要求。详见参数配置中的 “限速选项” 。

参考

DSN (DATA SOURCE NAME) SPECIFICATIONS



如果喜欢这里的文章,而且又不差钱的话,欢迎打赏个早餐 ^_^


About This Blog

Recent Posts

Categories

Related Links

  • RTEMS
    RTEMS
  • GNU
  • Linux Kernel
  • Arduino

Search


This Site was built by Jin Yang, generated with Jekyll, and hosted on GitHub Pages
©2013-2018 – Jin Yang