mysql主从一致性检测和修复

最新推荐文章于 2024-01-03 11:05:44 发布

weixin_33753845

最新推荐文章于 2024-01-03 11:05:44 发布

阅读量241

点赞数

文章标签：数据库运维

原文链接：https://my.oschina.net/shyl/blog/719621

版权

为什么80%的码农都做不了架构师？>>>

一、简介
      pt-table-checksum是著名的 percona-toolkit 工具集的工具之一。它通过在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。这种校验是分表进行的，在每个表内部又是分块进行的，而且pt工具本身提供了非常多的限流选项，因此对线上服务的冲击较小。
二、 适用范围
pt-table-checksum是一个在线验证主从数据一致性的工具，主要用于以下场景:
1、数据迁移前后，进行数据一致性检查
2、当主从复制出现问题，待修复完成后，对主从数据进行一致性检查
3、把从库当成主库，进行数据更新，产生了"脏数据"
4、定期进行数据校验
三、环境说明
Master
OS：CentOS 6.4，IP：123.59.7.118，DB Version：5.1.73
Port：3306
Slave
OS：CentOS 5.9，IP：123.59.7.109，DB Version：5.1.51
Port：3307
四、校验过程
1、安装yum依赖包（Master、Slave全部安装）
[root@localhost ~]# yum install perl perl-devel perl-Time-HiRes perl-DBI perl-DBD-MySQL -y
2、安装percona-toolkit工具包（Master、Slave全部安装）
[root@localhost ~]# mkdir /SoftWare
[root@localhost ~]# cd /SoftWare
[root@localhost ~]# wget http://www.percona.com/get/percona-toolkit.tar.gz
[root@localhost ~]# tar -xvzf percona-toolkit-2.2.14.tar.gz
[root@localhost ~]# cd percona-toolkit-2.2.14
[root@localhost ~]# perl Makefile.PL
[root@localhost ~]# make && make install
3、Master建库、建表、用户授权
[root@localhost ~]# mysql
mysql> create database pt character set utf8;
mysql> use pt;
mysql> create table if not exists 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (db, tbl, chunk),
          INDEX ts_db_tbl (ts, db, tbl)
          ) ENGINE=InnoDB;
mysql> GRANT UPDATE,INSERT,DELETE,SELECT,LOCK TABLES,PROCESS,SUPER,REPLICATION SLAVE ON *.* TO 'checksums'@'%' identified by '123456';
mysql> GRANT ALL ON pt.* TO 'checksums'@'%' IDENTIFIED BY '123456';
4、Master指定Slave信息
--test库中创建dsns表
mysql> use test
mysql> CREATE TABLE `dsns` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) );
--指定slave信息（如果有多个slave，就插入多条slave信息）
mysql> INSERT INTO dsns (parent_id,dsn) values(1,'h=123.59.7.109,u=checksums,p=123456,P=3307');
mysql> exit
注：
①如果主从端口不同，执行pt-table-checksum命令时多加 --recursion-method=dsn=h=123.59.7.118,D=test,t=dsns 参数即可。
②如果主从端口相同，且都是3306端口，第4步跳过。
5、Master校验
[root@localhost ~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=50 --databases nobserver_new -u'checksums' -p'123456' -h123.59.7.118 --recursion-method=dsn=h=123.59.7.118,D=test,t=dsns
参数解释：
# --h -u -p -P -S -d 连接信息
# --nocheck-replication-filters 检测中忽略mysql 配置参数binlog_ignore_db等
# --nocheck-binlog-format 不检测日志格式
# --replicate 指定checksum 存储的db和表，如：pt.checksum
# --databases 指定数据库，如：nobserver_new
# --chunk-size， --chunk-size-limit 用于指定检测块的大小，可控性更强
# --ignore-databases/tables/column 跳出指定元素的过滤
# --lock-wait-timeout innodb 锁的超时设定，默认为1
# --max-load 设置最大并发连接数
# --replicate-check-only 只输出数据不一致的信息
# --help 帮助文档
校验结果如下图：

列名解释：
# --TS ：完成检查的时间
# --ERRORS ：检查时候发生错误和警告的数量
# --DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息
# --ROWS ：表的行数
# --CHUNKS ：被划分到表中的块的数目
# --SKIPPED ：由于错误或警告或过大，则跳过块的数目
# --TIME ：执行的时间
# --TABLE ：被检查的表名
注：
①如果主从端口相同都是3306端口，--recursion-method=dsn=h=123.59.7.118,D=test,t=dsns参数省略即可。
6、Slave查看差异信息
[root@localhost ~]# ./login_mysql_3307.sh
mysql> use pt;
mysql> 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;

7、Master打印出消除差异sql信息
[root@localhost ~]# pt-table-sync --print --sync-to-master h=123.59.7.109,P=3307,u=checksums,p='123456' --database=nobserver_new --tables=media_class_jieliang

8、Slave执行消除差异的sql语句
[root@localhost ~]# mysql
mysql> use nobserver_new;
mysql> DELETE FROM `nobserver_new`.`media_class_jieliang` WHERE `seqid`='116' LIMIT 1;
mysql> REPLACE INTO `nobserver_new`.`media_class_jieliang`(`seqid`, `dateline`, `media_class`, `a_times`, `m_times`) VALUES ('107', '20150616', '视频网站', '7', '7');
9、Master再次进行校验检查同步结果
检验方法参考第5步，当DIFFS列全部为0时表示Master、Slave无差异。

转载于:https://my.oschina.net/shyl/blog/719621