Percona toolkit-数据库运维的利器

简介

percona-toolkit是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的mysql和系统任务，能极大的提高DBA的工作效率

利用pt-table-checksum作主从一致性校验，其基本原理是，首先设置主从复制格式为statement，在主机上执行生成某块数据校验码的语句，通过主从复制，同样的语句在从库机器机上执行，比较主从机器的校验码结果从而知道数据是否一致。

安装

###rpm包方式需要翻墙（建议使用二进制包解压使用）
wget https://www.percona.com/redir/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

wget https://www.percona.com/downloads/percona-toolkit/3.0.11/binary/tarball/percona-toolkit-3.0.11_x86_64.tar.gz

tar -zxvf percona-toolkit-3.0.11_x86_64.tar.gz -C /usr/local/

vim /etc/profile
###percona-toolkit
export PATH="$PATH:/usr/local/percona-toolkit-3.0.11/bin"

source /etc/profile

###检验是否安装成功
root># pt-query-digest --help
root># pt-table-checksum --help

https://www.cnblogs.com/zishengY/p/6852280.html

如果命令提示可以正常显示，则说明pt工具已经正常安装和使用了。

---

1、用户权限

mysql> select * from mysql.user where user='root'\G;

mysql> update mysql.user set Grant_priv='Y' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 4  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2、在主库上创建percona库，并且创建一个slave表

mysql> create database percona;

mysql> use percona;

mysql> CREATE TABLE `slave` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> GRANT REPLICATION SLAVE,PROCESS,SUPER, SELECT ON *.* TO 'checksum_user'@'%' IDENTIFIED BY 'checksum_password';

mysql> GRANT ALL PRIVILEGES ON percona.* TO 'checksum_user'@'%';

3、insert记录到slave表，该记录表明要检测那个slave

mysql> insert into slave(`id`,`dsn`) values(1,'h=192.168.56.132,P=3306');

4、执行检测

###首先在主库上创建测试库和插入测试数据
mysql> create database aud2 default character set utf8;

mysql> use aud2;

mysql> CREATE TABLE `autodeploy` (
           `id` int(11) NOT NULL AUTO_INCREMENT,
           `name` varchar(255) NOT NULL,
           `desc` varchar(255) NOT NULL,
           PRIMARY KEY (`id`)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> insert into autodeploy(`id`,`name`,`desc`) values(1,'autodepoy1','test1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into autodeploy(`id`,`name`,`desc`) values(2,'autodepoy2','test2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into autodeploy(`id`,`name`,`desc`) values(3,'autodepoy3','test3');
Query OK, 1 row affected (0.00 sec)

###执行检测
root># ./pt-table-checksum --nocheck-replication-filter --no-check-binlog-format --databases="aud2" --create-replicate-table --replicate=percona.checksums --recursion-method=dsn=D=percona,t=slave --host=192.168.56.131 --port=3306 --user=checksum_user --password=checksum_password

Can't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at ./pt-table-checksum line 7807.
BEGIN failed--compilation aborted at ./pt-table-checksum line 7807.


报错解决：
yum -y  install perl-Time-HiRes

###初始检测OK
server02<2018-08-16 17:46:30> ~/percona-toolkit-2.2.20/bin
root># ./pt-table-checksum --nocheck-replication-filter --no-check-binlog-format --databases="aud2" --create-replicate-table --replicate=percona.checksums --recursion-method=dsn=D=percona,t=slave --host=192.168.56.131 --port=3306 --user=checksum_user --password=checksum_password
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
08-16T17:46:36      0      0        2       1       0   0.028 aud2.autodeploy


###修改从库数据（让主从库数据产生差异，然后做主从数据一致性校验）
mysql> UPDATE autodeploy SET name='Start C++' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE autodeploy SET name='Start Python' WHERE id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from autodeploy;
+----+-------------+-----------+
| id | name        | desc      |
+----+-------------+-----------+
|  1 | Start C++      | test1  |
|  2 | Start Python   | test2  |
+----+-------------+-----------+
2 rows in set (0.00 sec)

root># pt-table-checksum --nocheck-replication-filter --no-check-binlog-format --databases="aud2" --create-replicate-table --replicate=percona.checksums --recursion-method=dsn=D=percona,t=slave --host=192.168.56.131 --port=3306 --user=checksum_user --password=checksum_password
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
08-17T11:07:27      0      1        3          0       1       0   0.010 aud2.autodeploy

##发现数据aud2的autodeploy表数据有差异

5、同步数据(注意需要在从库上执行)

root># ./pt-table-sync --sync-to-master h=192.168.56.132,P=3306,u=root,p=123456 --databases=aud2 --tables=autodeploy --charset=utf8 --print --execute    ----注意这里写从库的IP

REPLACE INTO `aud2`.`autodeploy`(`id`, `name`, `desc`, `disk_type`) VALUES ('2', 'aud20103013013231301', '姗..', '2') /*percona-toolkit src_db:aud2 src_tbl:autodeploy src_dsn:A=utf8,P=3306,h=192.168.56.131,p=...,u=root dst_db:aud2 dst_tbl:autodeploy dst_dsn:A=utf8,P=3306,h=192.168.56.132,p=...,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:3454 user:root host:server03*/;

6、在线改表结构

pt-online-schema-change  --charset=utf8 --nocheck-replication-filter --max-lag 1 --critical-load Threads_running=10000 --execute --alter " add disk_type tinyint(4) NOT NULL DEFAULT '2' COMMENT '1.U盘;2.磁盘;3.SSD;4.其他';" D=aud2,t=autodeploy,u=root,p='123456',P=3306,h=192.168.56.132