1.pt工具介绍 Percona Toolkit简称pt工具，是Percona公司开发用于管理MySQL的工具，包括表中记录归档和清理、在线DDL、检查主从复制的数据一致性、检查重复索引、定位IO占用高的表文件等功能，利用工具提高日常工作效率。 2. pt工具安装
下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST/
linux下载：
wget https://www.percona.com/downloads/percona-toolkit/3.0.1/binary/redhat/7/x86_64/percona-toolkit-3.0.1-1.el7.x86_64.rpm
yum localinstall percona-toolkit-3.0.1-1.el7.x86_64.rpm
3.常用工具 3.1 pt-archiver
作用： 1.用于将MySQL表中记录归档到另外一个表或者文件中（文件可以用LOAD DATA INFILE进行数据装载，类似历史数据的增量删除）； 2.用于对MySQL表中记录进行清除。
场景： 1.定期按照时间范围，进行归档表； 2.亿级的大表，delete批量删除100w左右数据。
注意: 需要归档表中至少有一个索引,做好是where条件列有索引
准备环境：
利用MySQL官方提供的t100w的表为基表
mysql> source /root/t100w.sql;
为做归档表中创建索引：
mysql> alter table t100w modify id int not null primary key auto_increment;
Query OK, 1000000 rows affected (4.88 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| num | int(11) | YES | | NULL | |
| k1 | char(2) | YES | | NULL | |
| k2 | char(4) | YES | | NULL | |
| dt | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
复制张test1表
mysql> create table test1 like t100w;
mysql> desc test1;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| k1 | char(2) | YES | | NULL | |
| k2 | char(4) | YES | | NULL | |
| dt | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
案例1.归档到数据库
# 重要参数
--limit 100 每次取100行数据用pt-archive处理
--txn-size 100 设置100行为一个事务提交一次，
--where 'id<3000' 设置操作条件
--progress 5000 每处理5000行输出一次处理信息
--statistics 输出执行过程及最后的操作统计。（只要不加上--quiet，默认情况下pt- archive都会输出执行过程的）
--charset=UTF8 指定字符集为UTF8—这个最后加上不然可能出现乱码。
--bulk-delete 批量删除source上的旧数据(例如每次1000行的批量删除操作)
pt-archiver --source h=127.0.0.1,P=3306,u=root,D=test,t=t100w --dest h=127.0.0.1,P=3306,u=root,D=test,t=test1 --where 'id<=10000' --progress 50 --txn-size=1000 --statistics --no-delete --ask-pass --no-check-charset
Enter password: 源密码
Enter password: 目标密码
........
Started at 2021-04-12T17:19:21, ended at 2021-04-12T17:19:24
Source: D=test,P=3306,h=127.0.0.1,p=...,t=t100w,u=root
Dest: D=test,P=3306,h=127.0.0.1,p=...,t=test1,u=root
SELECT 10000
INSERT 10000
DELETE 0
Action Count Time Pct
select 10001 1.4474 48.29
inserting 10000 1.0556 35.22
commit 22 0.0420 1.40
other 0 0.4523 15.09
查询测试：
[root@ansel ~ ]# mysql -uroot -p -e 'select count(*) from test.test1;'
Enter password:
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
案例2.归档到文件
pt-archiver --source h=127.0.0.1,P=3306,u=root,D=test,t=t100w --file='/root/test1_%Y-%m-%d.sql' --where "id <= 10000" --progress=50 --txn-size=1000 --limit=50 --statistics --no-delete --ask-pass --no-check-charset
Started at 2021-04-12T17:31:55, ended at 2021-04-12T17:23:56
Source: D=test,P=3306,h=127.0.0.1,p=...,t=t100w,u=root
SELECT 10000
INSERT 0
DELETE 0
Action Count Time Pct
select 201 0.0477 20.49
commit 11 0.0007 0.31
print_file 10000 -0.0112 -4.82
other 0 0.1958 84.02
案例3.（仅）清理数据
mysql> alter table test1 modify id int not null primary key auto_increment;
pt-archiver --source h=127.0.0.1,D=test,t=test1,u=root,p=bulibuqi... --where 'id<10000' --purge --limit=1 --no-check-charset
之前导入了--where 'id<=10000',刚才删除了--where 'id=10000'
mysql> select * from test1;
+-------+--------+------+------+---------------------+
| id | num | k1 | k2 | dt |
+-------+--------+------+------+---------------------+
| 10000 | 607112 | t0 | rs23 | 2019-08-12 11:41:25 |
+-------+--------+------+------+---------------------+
1 row in set (0.00 sec)
3.2 pt-osc 在线修改表结构，创建、删除索引等，不能加快速度，只能减少业务影响（锁）
pt-osc工作流程：
0、先检查从节点
Found 2 slaves:
db02 -> 192.168.6.51:socket
db03 -> 192.168.6.52:socket
Will check slave lag on:
db02 -> 192.168.6.51:socket
db03 -> 192.168.6.52:socket
1、检查更改表是否有主键或唯一索引，是否有触发器
2、检查修改表的表结构，创建一个临时表，在新表上执行ALTER TABLE语句
Created new table test._t100w_new OK.
Altering new table...
ALTER TABLE `test`.`_t100w_new` add column state int not null default 1
3、在源表上创建三个触发器分别对于INSERT UPDATE DELETE操作
2021-04-12T17:43:56 Creating triggers...
2021-04-12T17:43:56 Created triggers OK.
4、从源表拷贝数据到临时表，在拷贝过程中，对源表的更新操作会写入到新建表中
Copying approximately 997632 rows...
5、将临时表和源表rename（需要元数据修改锁，需要短时间锁表）
RENAME TABLE `test`.`t100w` TO `test`.`_t100w_old`, `test`.`_t100w_new` TO `test`.`t100w`
6、删除源表和触发器，完成表结构的修改。
2021-04-12T17:44:03 Dropped old table `test`.`_t100w_old` OK.
2021-04-12T17:44:03 Dropping triggers...
pt-osc工具限制
1、源表必须有主键或唯一索引，如果没有工具将停止工作
在拷贝创建临时表期间有DDL操作，工具停止工作。2、如果线上的复制环境过滤器操作过于复杂，工具将无法工作
3、如果开启复制延迟检查，但主从延迟时，工具将暂停数据拷贝工作
4、如果开启主服务器负载检查，但主服务器负载较高时，工具将暂停操作
5、当表使用外键时，如果未使用--alter-foreign-keys-method参数，工具将无法执行
6、只支持Innodb存储引擎表，且要求服务器上有该表1倍以上的空闲空间。
pt-osc之alter语句限制
1、不需要包含alter table关键字，可以包含多个修改操作，使用逗号分开，如"drop clolumn c1, add column c2 int"
2、不支持rename语句来对表进行重命名操作
3、不支持对索引进行重命名操作
4、如果删除外键，需要对外键名加下划线，如删除外键fk_uid, 修改语句为"DROP FOREIGN KEY _fk_uid"
pt-osc之命令模板
## --execute表示执行
## --dry-run表示只进行模拟测试
## 表名只能使用参数t来设置，没有长参数
pt-online-schema-change \
--host="127.0.0.1" \
--port=3306 \
--user="root" \
--password="root@xxxx" \
--max-lag=10 \
--check-salve-lag='xxx.xxx.xxx.xxx' \
--recursion-method="hosts" \
--check-interval=2 \
--database="test" \
t="tb001" \
--alter="add column c4 int" \
--execute
例子：
pt-online-schema-change --user=root --password=xxxx --host=127.0.0.1 --alter "add column state int not null default 1" D=test,t=t100w --print --execute
pt-online-schema-change --user=root --password=xxxx --host=127.0.0.1 --alter “add index idx(num)” D=test,t=t100w --print --execute
3.3 pt-table-checksum & pt-table-sync pt-table-checksum是一个基于MySQL数据库主从架构在线数据一致性校验工具。其工作原理在主库上运行， 通过对同步的表在主从段执行checksum， 从而判断数据是否一致。在校验完毕时，该工具将列出与主库存在差异的对象结果。
-- 环境：Master 192.168.1.8， Slave 192.168.1.12，主从已构建
-- 复制过滤器如下：
[root@ansel ~]# mysql -uroot -p -e "show slave status\G"|grep "Replicate
Enter password:
Replicate_Do_DB: sakila,test
Replicate_Ignore_DB: mysql
a、环境准备
--对用于执行checksum的用户授权，注，如果主从复制未开启mysql系统库复制，则从库也同样执行用户创建
master@localhost[test]> create database pt CHARACTER SET utf8;
master@localhost[test]> grant select, process, super, replication slave on *.* to'checksums'@'192.168.1.%' identified by 'xxx';
Query OK, 0 rows affected (0.00 sec)
--主库建表及插入记录
master@localhost[test]> create table t1(id tinyint primary key auto_increment,ename varchar(20));
Query OK, 0 rows affected (0.01 sec)
master@localhost[test]> insert into t1(id,ename) values(1,'ansel'),(3,'dba'),(4,'zhang');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
--从库查询结果
slave@localhost[test]> select * from t1;
+----+-------+
| id | ename |
+----+-------+
| 1 | ansel |
| 3 | dba |
| 4 | zhang |
+----+-------+
3 rows in set (0.00 sec)
--模拟数据不一致,slave端删除记录
slave@localhost[test]> delete from t1 where id!=1;
参数描述
–nocheck-replication-filters
不检查复制过滤器，建议启用。后面可以用–databases来指定需要检查的数据库。
–no-check-binlog-format
不检查复制的binlog模式，要是binlog模式是ROW，则会报错。
–replicate-check-only
只显示不同步的信息。
–replicate=
把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。
–databases=
指定需要被检查的数据库，多个则用逗号隔开。
–tables=
指定需要被检查的表，多个用逗号隔开
h=127.0.0.1 ：Master的地址
u=root ：用户名
p=123456 ：密码
P=3306 ：端口
b、单表校验
-- 执行pt-table-checksum
pt-table-checksum h='192.168.1.8',u='checksums',p='xxx',P=3306 -dtest -tt1 --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksum
TS ERRORS DIFFS ROWS DIDD_ROWS CHUNKS SKIPPED TIME TABLE
07-17T22:36:31 0 1 2 2 1 0 0.025 test.t1
TS ：完成检查的时间。
ERRORS ：检查时候发生错误和警告的数量。
DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，
会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS ：表的行数。
CHUNKS ：被划分到表中的块的数目。
SKIPPED ：由于错误或警告或过大，则跳过块的数目。
TIME ：执行的时间。
TABLE ：被检查的表名。
此时同步需要pt-table-sync校验,然后同步 参数描述
--replicate
指定通过pt-table-checksum得到的表
--databases
指定执行同步的数据库
--tables
指定执行同步的表，多个用逗号隔开
--sync-to-master
指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主
h=127.0.0.1 ：Master的地址
u=root ：用户名
p=123456 ：密码
P=3306 ：端口
打印，但不执行命令
--execute
执行命令
处理方式一：
# pt-table-sync --replicate=pt.checksums --databases test --tables t1 h=192.168.1.8,u=checksum,p=checksum,P=3306 h=192.168.1.12,u=checksum,p=checksum,P=3306 --print
处理方式二：
pt-table-sync --replicate=pt.checksums --databases test --tables t1 h=192.168.1.8,u=checksum,p=checksum,P=3306 h=192.168.1.12,u=checksum,p=checksum,P=3306 --execute
3.4 pt-duplicate-key-checker pt-duplicate-key-checker帮助检测表中重复的索引或者主键。合理的索引会更快查询所需数据，但是过量的索引反而可能会使数据库的性能降低，它可以找到重复的索引并且还会删除重复索引的建立语句，非常实用。
a.准备环境
CREATE TABLE `temp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_test_temp_name` (`name`),
KEY `idx_test_temp_name_new` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
insert into temp values(1,'ansel', '123456', 18);
insert into temp values(2,'zhang', 'qwerty', 23);
insert into temp values(3,'zheng', 'zxcvbn', 34);
CREATE INDEX idx_test_temp_name ON test.temp(name);
CREATE INDEX idx_test_temp_name_new ON test.temp(name);
show indexes from temp;
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| temp | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| temp | 1 | idx_test_temp_name | 1 | name | A | 3 | NULL | NULL | YES | BTREE | | |
| temp | 1 | idx_test_temp_name_new | 1 | name | A | 3 | NULL | NULL | YES | BTREE | | |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
b.运行测试
pt-duplicate-key-checker --database=test h='127.0.0.1' --user=root --password=xxxxxx
# ########################################################################
# test.temp
# ########################################################################
# idx_test_temp_name_new is a duplicate of idx_test_temp_name
# Key definitions:
# KEY `idx_test_temp_name_new` (`name`)
# KEY `idx_test_temp_name` (`name`),
# Column types:
# `name` varchar(20) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`temp` DROP INDEX `idx_test_temp_name_new`;
# ########################################################################
# Summary of indexes
# ########################################################################
# Size Duplicate Indexes 249
# Total Duplicate Indexes 1
# Total Indexes 6
c.执行删除语句：
mysql> ALTER TABLE `test`.`temp` DROP INDEX `idx_test_temp_name_new`;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
d.再次运行测试
pt-duplicate-key-checker --database=test h='127.0.0.1' --user=root --password=xxxxxx
# ########################################################################
# Summary of indexes
# ########################################################################
# Total Indexes 5
更多精彩内容欢迎关注微信公众号