检测MySQL主从复制一致性并修复
MySQL主从复制时可能由于各种原因导致数据不能及时同步，对用对造成影响，所以我们需要对一致性做检测，并在出现不一致的时候及时修复。
造成主从数据不一致的情况有：
- 网络延时
- 磁盘IO过高
- max_allowed_packet不一致
- key自增键开始的键值跟自增步长设置不一致
- MySQL异常宕机，如果未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出现binlog或者relaylog文件出现损坏，导致主从不一致
- 版本不一致
针对这些情况，我们选择使用percona-toolkit工具进行检测并修复。percona-toolkit是一组高级命令行工具的集合，使用perl编写，用来执行各种通过手工执行非常复杂和麻烦的MySQL和系统任务，我们使用最多的三个组件有：
- pt-table-checksum 负责监测mysql主从数据一致性
- pt-table-sync 负责当主从数据不一致时修复数据，让它们保存数据的一致性
- pt-heartbeat 负责监控mysql主从同步延迟
安装
1 2 3 | 1、wget https://www.percona.com/downloads/percona-toolkit/2.2.7/RPM/percona-toolkit-2.2.7-1.noarch.rpm 2、rpm -ivh percona-toolkit-2.2.7-1.noarch.rpm 3、yum y install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI |
|---|
准备
在使用pt命令之前，需要创建一个用户，并给这个用户授予权限，让这个用户可以在主或从机器上能连接主或者从。
1 2 3 4 | GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON *.* TO 'pt'@'MASTER_IP' identified by '123456'; GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON *.* TO 'pt'@'SLAVE_IP' identified by '123456'; flush privileges; 在两台机器上分别执行这三条语句。 |
|---|
检测数据一致性
使用的是pt-table-checksum这个工具，原理是：
在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库执行，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。
特点：
- 检测过程根据唯一索引将表按row切分为块（chunk），以单位为计算，可以避免锁表。检测时会自动判断复制延迟、 master的负载， 超过阀值后会自动将检测暂停，减小对线上服务的影响。
- pt-table-checksum 默认情况下可以应对绝大部分场景，官方说，即使上千个库、上万亿的行，它依然可以很好的工作，这源自于设计很简单，一次检查一个表，不需要太多的内存和多余的操作；必要时，pt-table-checksum 会根据服务器负载动态改变 chunk 大小，减少从库的延迟。
- 为了保证主数据库服务的安全，该工具实现了许多保护措施：1）自动设置 innodb_lock_wait_timeout 为1s，避免锁等待时间过长；2）默认当数据库有25个以上的并发查询时，pt-table-checksum会暂停。可以设置 –max-load 选项来设置这个阀值； 3）当用 Ctrl+C 停止任务后，工具会正常的完成当前 chunk 检测，下次使用 –resume 选项启动可以恢复继续下一个 chunk
使用介绍
1 | pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=novel.checksums --create-replicate-table --databases=novel h=172.17.24.140,u=pt,p=chenfei@2018,P=3306 |
|---|
1 2 3 4 5 6 7 8 9 10 11 | --nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。 --no-check-binlog-format : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。 --replicate-check-only :只显示不同步的信息。 --replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。 --create-replicate-table： 生成checksums表（第一次执行时一定要加上这个参数，否则需要手动添加） --databases= ：指定需要被检查的数据库，多个则用逗号隔开。 --tables= ：指定需要被检查的表，多个用逗号隔开 h= ：Master的地址 u= ：用户名 p=：密码 P= ：端口 |
|---|
如果执行上面的命令，未加–create-replicate-table时，需要手动建表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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 2 | TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 04-28T17:44:48 0 0 0 1 0 0.015 novel.wallet_recharge_log_19 |
|---|
1 2 3 4 5 6 7 8 | TS ：完成检查的时间。 ERRORS ：检查时候发生错误和警告的数量。 DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。 ROWS ：表的行数。 CHUNKS ：被划分到表中的块的数目。 SKIPPED ：由于错误或警告或过大，则跳过块的数目。 TIME ：执行的时间。 TABLE ：被检查的表名。 |
|---|
一致性修复
使用pt-table-sync工具来进行不一致数据修复，使用方法：
1 2 | pt-table-sync --replicate=novel.checksums h=MASTER_IP,u=root,p=123456 h=SLAVE_IP,u=root,p=123456 --print #不会执行，会将输出打印出来 pt-table-sync --replicate=novel.checksums h=MASTER_IP,u=root,p=123456 h=SLAVE_IP,u=root,p=123456 --execute #执行 |
|---|
这个命令需要和pt-table-checksum一块使用，因为只有通过前一个工具检测出哪些表数据不一致时才能通过该工具进行同步修复。
主从复制延迟检测
使用pt-heartbeat来检测，原理：
- 在主上创建一张heartbeat表，按照一定的时间频率更新该表的字段（把时间更新进去）。监控操作运行后，heartbeat表能促使主从同步！
- 连接到从库上检查复制的时间记录，和从库的当前系统时间进行比较，得出时间的差异。
使用方法
1 | pt-heartbeat -D novel --create-table --table=heartbeat --monitor --user=pt --host=MASTER_IP --password=123456 --master-server-id=1 --log=/tmp/master-slave.txt --daemonize |
|---|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | 注意：需要指定的参数至少有 --stop，--update，--monitor，--check。 其中--update，--monitor和--check是互斥的，--daemonize和--check也是互斥。 --ask-pass 隐式输入MySQL密码 --charset 字符集设置 --check 检查从的延迟，检查一次就退出，除非指定了--recurse会递归的检查所有的从服务器。 --check-read-only 如果从服务器开启了只读模式，该工具会跳过任何插入。 --create-table 在主上创建心跳监控的表，如果该表不存在，可以自己手动建立，建议存储引擎改成memory。通过更新该表知道主从延迟的差距。 CREATE TABLE heartbeat ( ts varchar(26) NOT NULL, server_id int unsigned NOT NULL PRIMARY KEY, file varchar(255) DEFAULT NULL, position bigint unsigned DEFAULT NULL, relay_master_log_file varchar(255) DEFAULT NULL, exec_master_log_pos bigint unsigned DEFAULT NULL ); heratbeat 表一直在更改ts和position,而ts是我们检查复制延迟的关键。 --daemonize 执行时，放入到后台执行 --user=-u， 连接数据库的帐号 --database=-D， 连接数据库的名称 --host=-h， 连接的数据库地址 --password=-p， 连接数据库的密码 --port=-P， 连接数据库的端口 --socket=-S， 连接数据库的套接字文件 --file 【--file=output.txt】 打印--monitor最新的记录到指定的文件，很好的防止满屏幕都是数据的烦恼。 --frames 【--frames=1m,2m,3m】 在--monitor里输出的[]里的记录段，默认是1m,5m,15m。可以指定1个，如：--frames=1s，多个用逗号隔开。可用单位有秒（s）、分钟（m）、小时（h）、天（d）。 --interval 检查、更新的间隔时间。默认是见是1s。最小的单位是0.01s，最大精度为小数点后两位，因此0.015将调整至0.02。 --log 开启daemonized模式的所有日志将会被打印到制定的文件中。 --monitor 持续监控从的延迟情况。通过--interval指定的间隔时间，打印出从的延迟信息，通过--file则可以把这些信息打印到指定的文件。 --master-server-id 指定主的server_id，若没有指定则该工具会连到主上查找其server_id。 --print-master-server-id 在--monitor和--check 模式下，指定该参数则打印出主的server_id。 --recurse 多级复制的检查深度。模式M-S-S...不是最后的一个从都需要开启log_slave_updates，这样才能检查到。 --recursion-method 指定复制检查的方式,默认为processlist,hosts。 --update 更新主上的心跳表。 --replace 使用--replace代替--update模式更新心跳表里的时间字段，这样的好处是不用管表里是否有行。 --stop 停止运行该工具（--daemonize），在/tmp/目录下创建一个“pt-heartbeat-sentinel” 文件。后面想重新开启则需要把该临时文件删除，才能开启（--daemonize）。 --table 指定心跳表名，默认heartbeat。 |
|---|
停止用pt-heartbeat –stop即可。
参考脚本：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | #!/bin/bash pt_table=$(which pt-table-checksum) pt_sync=$(which pt-table-sync) dbs="库名" master="master_ip" slave="slave_ip" users="pt" pass="密码" port="端口" DATE=$(date +%F) mail="邮件" cwd=`pwd` table_wallet=$(mysql -upt -h172.17.24.140 -pchenfei@2018 -e "use novel;show tables" | grep wallet_recharge_log) if test ! -d $cwd/log;then mkdir $cwd/log fi for table in $table_wallet do num=$($pt_table --nocheck-replication-filters \ --no-check-binlog-format \ --replicate=${dbs}.checksums \ --databases=$dbs \ --tables=$table \ h=$master,u=$users,p=$pass,P=$port | awk '{print $3}'|sed -n '2p') if [ $num -eq 1 ];then $pt_sync --replicate=${dbs}.checksums \ h=$master,u=$users,p=$pass \ h=$slave,u=$users,p=$pass --print >> $cwd/log/pt_check.log $pt_sync --replicate=${dbs}.checksums \ h=$master,u=$users,p=$pass \ h=$slave,u=$users,p=$pass --execute if [ $? -ne 0 ];then python send.py $mail "warnning" "$DATE: Data Inconsistency";fi else echo -e "$DATE $table data is ok" >> $cwd/log/pt_check.log fi done ##send.py是使用python写的邮件发送程序，使用的是加密端口465 |
|---|