MySQL的瑞士军刀----percona-toolkit

在维护数据库时，有一个非常好的工具推荐给大家，可能会对大家有一定的帮助。

PT工具是由Percona公司开发的一款开源软件，大家可以去官网查看https://www.percona.com/。

首先介绍一下PT工具的安装

PT工具的安装

$ yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL
$ yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
$ yum -y install percona-toolkit
$ pt-query-digest --version
pt-query-digest 3.0.13

　　

安装时报错

$ rpm -qa | grep mysql 

如果少了libs-compat就会报上述错误。然后去MySQL官网找到对应的libs-compat安装即可，不同版本的我不确定可不可以，可以自己尝试。

常用命令

1.检查冗余

# database如果省略，就是检查所有库 -h 省略就是检查本地
$ pt-duplicate-key-checker --socket=/var/lib/mysql/mysql.sock -uUSER -p'PASSWORD' --database=DATABASE_NAME -h IP
​
# ########################################################################
# qptreasurerecorddb.recorduserinout                                      
# ########################################################################
​
# IDX_AUTOFIELD is a duplicate of PRIMARY
# Key definitions:
#   KEY `IDX_AUTOFIELD` (`ID`),
#   PRIMARY KEY (`ID`),
# Column types:
#     `id` int(11) not null auto_increment comment '????'
# To remove this duplicate index, execute:
ALTER TABLE `qptreasurerecorddb`.`recorduserinout` DROP INDEX `IDX_AUTOFIELD`;
​
# ########################################################################
# Summary of indexes                                                      
# ########################################################################
​
# 连接上数据库之后执行上述的语句
mysql> ALTER TABLE `qptreasurerecorddb`.`recorduserinout` DROP INDEX `IDX_AUTOFIELD`;

2.pt-mysql-summary

比较mysql的配置和status信息进行汇总

$ pt-mysql-summary --user=USER --password='PASSWORD' --all-databases

3.pt-online-schema-change

原理：创建一个和你要执行alter操作的表一样的空表结构，执行表结构修改，然后从原表中copy原始数据到表结构修改后的表，当数据完成以后将会将原表移走，用新表替代原表，默认动作是将原表drop掉

PS：在copy数据过程中，任何在原表的更新操作都会更新到新表，这个工具会在原表上创建触发器，触发器会将 在原表上的更新的内容更新到新表。如果表中已经定义了触发器这个工具就不能工作。

$ pt-online-schema-change  --alter="ENGINE=InnoDB" D=database,t=table --execute
$ pt-online-schema-change  --alter="ADD COLUMN domain_id INT" D=database,t=table --execute

报错

Error creating new table: DBD::mysql::db do failed: Column 'sex' has duplicated value '?' in SET [for Statement "CREATE TABLE test._t1_new

字符集问题，可以在执行的时候指定与原表相同字符集--charset=utf8mb4

$ pt-online-schema-change  --alter="ENGINE=Myisam" --charset=utf8mb4  D=test,t=t1  --user=test --password='Qy123123.' --execute

说明：使用pt-osc时，原表不能存在触发器，

• pt-osc会在原表创建3个触发器，而一个表上不能同时有两个相同类型的触发器，所以默认使用pt-osc不允许表上存在触发器

3.1 特殊的外键

假设t1 是要修改的表，t2有外键依赖于t1 ，_t1_new是 alter t1 产生的新的临时表

这里的外键不是看t1上是否存在外键，而是作为子表的t2。 主要问题在renamet1时，t1“不存在”导致t2的外键认为参考失败，不允许rename

• --alter-foreign-keys-method

  • rebuild_constraints

    • 先通过alter table t2 drop fk1,add _fk1重建外键参考，指向新表

    • 再rename t1 t1_old,_t1_new t1交换表名，不影响客户端

    • 删除旧表 t1_old

      如果t2字表太大，以致alter操作时间耗时过长，有可能会强制选择drop_swap

  • drop_swap

    • 禁用t2表外键约束检查FOREIGN_KEY_CHECKS=0

    • 然后drop t1原表

    • 再rename _t1_new t1

      速度快，也不会阻塞请求，但是有风险，

      • drop表的瞬间到rename的过程，原表t1是不存在的，遇到请求就会报错

      • 如果因为某种bug或某种原因，新表rename失败，那就太晚了，但是一般不会有这种情况

 以上只是我目前用到的一些工具，当然瑞士军刀中还有很多好用的工具，有兴趣的朋友可以访问官网查看。

4.PT-SLAVE-FIND

NAME

pt-slave-find 找到并且打印出来slave的信息

example

$ pt-slave-find --host master-host-ip --user=  --password= 

　　

输出信息如下：

Cannot connect to h=172.16.142.114,p=...,u=root
localhost
Version         8.0.16
Server ID       1
Uptime          121+07:34:25 (started 2019-08-28T08:25:31)
Replication     Is a slave, has 1 slaves connected, is not read_only
Filters         
Binary logging  ROW
Slave status    0 seconds behind, running, no errors
Slave mode      STRICT
Auto-increment  increment 1, offset 1
InnoDB version  8.0.16

5.PT-SLAVE-RESTART

NAME

pt-slave-restart 如果有多个slave，可以通过这个重新启动slave。

 

6.PT-TABLE-CHECKSUM

pt-table-checksum 确定mysql主从的数据一致性

$ pt-table-checksum [OPTION] [DSN]

pt-table-checksum通过在主服务器上执行校验和查询来执行联机复制一致性检查

该工具只是找到数据的差异。如果数据不同，则可以使用pt-table-sync解决问题

• --databases:指定特定的库，不指定的话，默认会对所有的库执行

  • --databases-regex：可以使用正则匹配

• --tables：指定特定的表

• --no-check-replication-filters：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。

• --no-check-binlog-format ：不检查复制的binlog模式，要是binlog模式是ROW，则会报错。

• --replicate-check-only：只显示不同步的信息。(注意：要谨慎使用，此参数不会生成新的checksums数据，只会根据checksums表已经有的数据来显示。)

• --replicate=：把checksum的信息写入到指定表中。

 

7.PT-TABLE-SYNC

Example

主库执行

主库 114 从库 115

pt-table-checksum --no-check-replication-filters --no-check-binlog-format --databases=qpextensionrecorddb --replicate=percona.checksums h=172.16.142.114,u=per,p='Qy123123.',P=3306

打印

$ pt-table-sync --replicate=percona.checksums h=172.16.142.114,u=per,p='Qy123123.',P=3306 h=172.16.142.115,u=per,p='Qy123123.',P=3306 --print

执行

$ pt-table-sync --replicate=percona.checksums h=172.16.142.114,u=per,p='Qy123123.',P=3306 h=172.16.142.115,u=per,p='Qy123123.',P=3306 --execute

　　

表一定要存在唯一索引或者主键，不然会报错Can't make changes on the master because no unique index exists at /usr/bin/pt-table-sync line 10858. while doing qpextensionrecorddb.RechargeRecord19 on 172.16.142.115

 

8.PT-ONLINE-SCHEMA-CHANGE

 在线修改表工具

$ pt-online-schema-change --charset=utf8 --no-version-check --user=USER --password=PASSWORD --host=IP  P=3306,D=DATABASE,t=TABLE --alter 
"语句" --execute

优点:

• 降低主从延时的风险
• 可以限速、限资源，避免操作时MySQL负载过高

 

9.PT-UPGRADE

这在升级服务器的时候非常有用，可以先安装并导数据到新的服务器上，然后使用这个工具跑一下sql看看有什么不同，可以找出不同版本之间的差异。

10.PT-CONFIG-DIFF

比较两个配置文件的差异