使用pt工具校验主从一致性并修复
【原理说明】
pt-table-checksum 是 Percona-Toolkit 的组件之一，用于检测MySQL主、从库的数据是否一致。其原理是在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库执行，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。检测过程根据唯一索引将表按row切分为块（chunk），以为单位计算，可以避免锁表。检测时会自动判断复制延迟、 master的负载， 超过阀值后会自动将检测暂停，减小对线上服务的影响。
pt-table-checksum 默认情况下可以应对绝大部分场景，官方说，即使上千个库、上万亿的行，它依然可以很好的工作，这源自于设计很简单，一次检查一个表，不需要太多的内存和多余的操作；必要时，pt-table-checksum 会根据服务器负载动态改变 chunk 大小，减少从库的延迟。
为了减少对数据库的干预，pt-table-checksum还会自动侦测并连接到从库，当然如果失败，可以指定--recursion-method选项来告诉从库在哪里。它的易用性还体现在，复制若有延迟，在从库 checksum 会暂停直到赶上主库的计算时间点（也通过选项--设定一个可容忍的延迟最大值，超过这个值也认为不一致）。
为了保证主数据库服务的安全，该工具实现了许多保护措施：
2）默认当数据库有25个以上的并发查询时，pt-table-checksum会暂停。可以设置 --max-load 选项来设置这个阀值
3）当用 Ctrl+C 停止任务后，工具会正常的完成当前 chunk 检测，下次使用 --resume 选项启动可以恢复继续下一个 chunk
【工作过程】
1. 连接到主库：pt工具连接到主库，然后自动发现主库的所有从库。默认采用show slave hosts来查找从库，但是这只有在主从实例端口相同的情况下才有效。
2. 查找主库或者从库是否有复制过滤规则：这是为了安全而默认检查的选项。你可以关闭这个检查，但是这可能导致checksum的sql语句要么不会同步到从库，要么到了从库发现从库没有要被checksum的表，这都会导致从库同步卡库。
3. 开始获取表，一个个的计算。
4. 如果是表的第一个chunk，那么chunk-size一般为1000；如果不是表的第一个chunk，那么采用12步中分析出的结果。
5. 检查表结构，进行数据类型转换等，生成checksum的sql语句。
6. 根据表上的索引和数据的分布，选择最合适的split表的方法。
7. 开始checksum表。
8. 默认在chunk一个表之前，先删除上次这个表相关的计算结果。除非–resume。
9. 根据explain的结果，判断chunk的size是否超过了你定义的chunk-size的上限。如果超过了，为了不影响线上性能，这个chunk将被忽略。
10. 把要checksum的行加上for update锁，并计算。
11. 把计算结果存储到master_crc master_count列中。
12. 调整下一个chunk的大小。
13. 等待从库追上主库。如果没有延迟备份的从库在运行，最好检查所有的从库，如果发现延迟最大的从库延迟超过max-lag秒，pt工具在这里将暂停。
14. 如果发现主库的max-load超过某个阈值，pt工具在这里将暂停。
15. 继续下一个chunk，直到这个table被chunk完毕。
16. 等待从库执行完checksum，便于生成汇总的统计结果。每个表汇总并统计一次。
17. 循环每个表，直到结束。
【校验脚本】
点击(此处)折叠或打开
- #!/bin/bash
- #------------------------------
- #func: pt-table-checksum check table
- #Usage:$0 MySQL_Host MySQL_Port
- #------------------------------
- if [[ "$1" = "-h" ]]||[[ "$1" = "-help" ]];then
- echo "$0 MySQL_Host MySQL_Port "
- exit 1
- fi
- if [ $# -ne 2 ];then
- echo "$0 MySQL_Host MySQL_Port "
- exit 1
- fi
-
- now_date=`/bin/date +"%Y-%m-%d_%H-%M-%S"`
- logfile="pt-table-checksum-${now_date}.log"
- user="checksum"
- password=""
- port="$2"
- chunk_size=1500
- chunk_time=0.8
- replicate="pt.checksum"
- check_interval="2s"
- host="$1"
- ignore_databases="information_schema,mysql,performance_schema,pt,sys,test"
- max_lag="0.5s"
- max_load="Threads_running=20,Threads_connected=1500"
- pid="/data/script/mysql/pt.pid"
- recursion_method="dsn=D=pt,t=dsns,h=${host}"
- replicate_check_retries=2
- replicate_database="pt"
- retries=1
- recurse=1
- chunk_size_limit=2.0
- /usr/bin/pt-table-checksum \
- --user=${user} \
- --password=${password} \
- --port=${port} \
- --chunk-size=${chunk_size} \
- --chunk-time=${chunk_time=} \
- --replicate=${replicate} \
- --check-interval=${check_interval} \
- --host=${host} \
- --ignore-databases=${ignore_databases} \
- --max-lag=${max_lag} \
- --max-load ${max_load} \
- --pid=${pid} \
- --no-check-binlog-format \
- --recursion-method ${recursion_method} \
- --replicate-check-retries=${replicate_check_retries} \
- --replicate-database=${replicate_database} \
- --retries=${retries} \
- --trim \
- --recurse=${recurse} \
- --check-replication-filters \
- --empty-replicate-table \
- --replicate-check \
- --chunk-size-limit ${chunk_size_limit} >${logfile} 2>&1
【校验脚本参数说明】
1）replicate="pt.checksum" #指定 checksum 计算结果存到哪个库表里，如果没有指定，默认是 percona.checksum
2）check_interval="2s" #当主从延迟大于max_lag，暂停check-interval秒后再次检查所有从库延迟情况
3）max_load="Threads_running=20,Threads_connected=1500" #达到这些值负载，就暂停校验
4）pid="/data/script/mysql/pt.pid" #设置pid，这样不会重复启用pt进程
5）recursion_method="dsn=D=pt,t=dsns,h=${host}" #此处填写的是主库地址、库名为pt 表名为dsns的记录;
recursion-method一般通过show processlist;自动发现host、port等信息。若信息没有，使用这个参数。
从库不使用与主库相同的MySQL用户名或密码，或者当您想要防止工具连接到某些副本时，此方法效果最佳。指定的DSN必须具有D和t部分，或仅具有数据库限定的t部分，它们指定DSN表。DSN表必须具有以下结构：--recursion-method dsn=h=host,D=percona,t=dsns
6）replicate_check_retries=2 #以防一次校验不准确，校验2次
7）replicate_database="pt" #只操作pt表，以防数据库有设置只复制某些表或ignore某些表，导致使用pt工具引起主从异常
8）retries=1 #如果发生锁或任务中断，尝试校验的次数，默认是2，担心引起锁，就设置为1
9）recurse=1 #仅校验一主多从，不校验主从级联情况
10）chunk_size_limit=2.0 #当表没有唯一索引时，块大小可能不准确。此选项指定不准确度的最大容许限制。该工具使用 来估计块中有多少行。如果估计值超过所需的块大小乘以限制（默认值的两倍），则该工具将跳过该块
【校验环境准备】
1）在主库添加校验用户，权限如下
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksum'@'XXXX' identified by "XXXX";
GRANT all ON `pt`.* TO 'checksum'@'XXXX' ;
#PROCESS用于自动发现从库信息，SUPER权限用于set binlog_format,select权限用于查询主从数据
2）如果使用recursion-method参数，相应库、表结构如下
create database pt;
CREATE TABLE `pt`.`dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
select * from pt.dsns;
+----+-----------+-----------------------+
| id | parent_id | dsn |
+----+-----------+-----------------------+
| 1 | 1 | h=10.9.157.245,P=3309 |
| 2 | 1 | h=10.9.98.143,P=3309 |
+----+-----------+-----------------------+
【如何校验】
1）在从库查看checksum，校验主从不一致。一主多从，正常的从库显示结果为空，不正常的从库显示结果
SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
FROM pt.checksum
WHERE (
master_cnt <> this_cnt
OR master_crc <> this_crc
OR ISNULL(master_crc) <> ISNULL(this_crc))
GROUP BY db, tbl;
2）通过输出的日志查看
输出结果：
Replica lag is 2307 seconds on mysql-5. Waiting.
Checksumming d_ts_profile.t_user_account: 3% 54:48 remain
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
12-18T16:07:48 0 0 313641 9 0 146.417 d_ts_profile.t_user
12-18T16:08:00 0 0 397734 12 0 11.747 d_ts_profile.t_user_detail
12-18T16:08:24 0 0 1668327 20 0 23.941 d_ts_profile.t_user_group
- TS ：完成检查的时间戳。
- ERRORS ：检查时候发生错误和警告的数量。
- DIFFS ：不一致的chunk数量。当指定 --no-replicate-check 即检查完但不立即输出结果时，会一直为0；当指定 --replicate-check-only 即不检查只从checksums表中计算crc32，且只显示不一致的信息（毕竟输出的大部分应该是一致的，容易造成干扰）。
- ROWS ：比对的表行数。
- CHUNKS ：被划分到表中的块的数目。
- SKIPPED ：由于错误或警告或过大，则跳过块的数目。
- TIME ：执行的时间。
- TABLE ：被检查的表名
【修复脚本】
点击(此处)折叠或打开
- user=""
- password=""
- charset="utf8mb4"
- replicate="pt.checksum"
- chunk_size="1500"
- pid="/data/script/mysql/pt-table-sync.pid"
- sync_to_master="h=10.9.129.33,P=3306"
- tables="kuaikan.device_push_info"
-
- /usr/bin/pt-table-sync \
- --user=${user} \
- --password=${password} \
- --pid=${pid} \
- --bin-log \
- --tables="${tables}" \
- --buffer-in-mysql \
- --no-buffer-to-client \
- --charset=${charset} \
- --no-check-child-tables \
- --no-foreign-key-checks \
- --check-master \
- --replicate=${replicate} \
- --sync-to-master ${sync_to_master} \
- --check-slave \
- --check-triggers \
- --chunk-size=${chunk_size} \
- --print \
- --transaction \
- --verbose
【修复脚本参数说明】
1）print只是打印需要执行的命令，确认无误后把参数—print改成—execute
2）sync_to_master此处填写从库的地址，只需要填写从库地址，会自动从从库show slave status获取主库的信息，不需要再写主库的地址，写了从库地址后，会根据从库差异对这个从库进行更改，无论如何都是在master端执行。并不会对其他从库的差异进行修复。
3）no-buffer-to-client如果禁用该选项的话，MySQL会一次性发送所有的rows，针对大表
4）lock参数如果 使用—replicate 或者 –sync-to-master 参数时，slave端 是不会锁表的。锁表的时候使用的是 lock tables ，但是如果使用 --transaction 的话，就是在事务开始到提交这一段，开始锁表
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31324175/viewspace-2139236/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31324175/viewspace-2139236/
295
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
