MySQL命令行工具：percona-toolkit安装使用初探

导读：percona-toolkit 源自 Maatkit 和 Aspersa 工具，这两个工具是管理 mysql 的最有名的工具，现在 Maatkit 工具已经不维护了，请大家还是使用 percona-toolkit 吧！这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类，作为一个优秀的 DBA，里面有的工具非常有用，如果能掌握并加以灵活应用，将能极大的提高工作效率。

一、 工具简介

percona-toolkit 是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的 mysql 任务和系统任务，这些任务包括：

• 检查 master 和 slave 数据的一致性
• 有效地对记录进行归档
• 查找重复的索引
• 对服务器信息进行汇总
• 分析来自日志和 tcpdump 的查询
• 当系统出问题的时候收集重要的系统信息

二、工具安装

①、下载安装包

Percona-toolkit 安装包：

最新 rpm 包：http://percona.com/get/percona-toolkit.rpm

最新编译包：http://percona.com/get/percona-toolkit.tar.gz （本文略）

Perl-TermReadKey 安装包：

访问：http://pkgs.repoforge.org/perl-TermReadKey/ 下载最新 rpm 安装包

②、安装记录

rpm -ivh perl-TermReadKey-2.30-3.el6.rfx.x86_64.rpm
rpm -ivh percona-toolkit-2.2.16-1.noarch.rpm

若发现缺失组件，则使用 yum 在线安装即可：

[[email protected]:~]# rpm -ivh percona-toolkit-2.2.16-1.noarch.rpm 
warning: percona-toolkit-2.2.16-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
        perl(IO::Socket::SSL) is needed by percona-toolkit-2.2.16-1.noarch

 yum install -y perl-IO-Socket-SSL  

成功安装后，系统会多出如下 pt 命令：

[[email protected]:~]# pt-
pt-align                  pt-find                   pt-mext                   pt-slave-delay            pt-table-usage
pt-archiver               pt-fingerprint            pt-mysql-summary          pt-slave-find             pt-upgrade
pt-config-diff            pt-fk-error-logger        pt-online-schema-change   pt-slave-restart          pt-variable-advisor
pt-deadlock-logger        pt-heartbeat              pt-pmp                    pt-stalk                  pt-visual-explain
pt-diskstats              pt-index-usage            pt-query-digest           pt-summary                
pt-duplicate-key-checker  pt-ioprofile              pt-show-grants            pt-table-checksum         
pt-fifo-split             pt-kill                   pt-sift                   pt-table-sync

三、使用初探

pt 有很多实用功能，但我就是奔着 alter 不锁表去的，所以率先测试 pt-online-schema-change 这个字段属性修改工具。

• pt-online-schema-change

①、功能介绍

如果没有这个工具，直接使用 alter 修改字段，过程如下：

• 按照原始表（original_table）的表结构和 DDL 语句，新建一张不可见的临时表；
• 在原表上加 write lock，此时对原表的所有 U D I DDL 都是阻塞的；
• 执行 insert into tmp_table select * from oldtable；
• rename oldtable 和 tmp_table，再 drop oldtable；
• 释放 write lock。

为了避免锁表，该换 pt-online-schema-change 出马了！

pt-online-schema-change 的 工作过程解析如下：

• 如果存在外键，根据 alter-foreign-keys-method 参数的值，检测外键相关的表，做相应设置的处理。
• 创建一个和你要执行 alter 操作的表一样的新的空表结构(是 alter 之前的结构)。
• 在新表执行 alter table 语句，
  在原表中创建触发器（3 个）三个触发器分别对应 insert,update,delete 操作
  从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表。
• Rename 原表到 old 表中，在把临时表 Rename 为原表，默认最后将原表删除，将原表上所创建的触发器删除。

因此，这个工具有如下限制：

• 表必须是单一列的主键或者单一唯一键。
• 若表存在外键，则需要指定参数 --alter-foreign-keys-method=auto|rebuild_constraints|drop_swap|none，而且外键不能有错误
• 表上不能有触发器，否则修改失败。如果有触发器，需要先删除触发器再执行 PT，最后再增加触发器。

②、demo

pt-online-schema-change --user=pttest --password=pttest --host=192.168.1.100  --alter "ADD COLUMN CustomerId_pt INT DEFAULT 0  NOT NULL AFTER Int04;" D=config15_test,t=t_device --execute --alter-foreign-keys-method=auto

③、测试记录

在测试数据库上随便导入了一份现网的数据，居然就让我碰到了触发器和外键，人品不错！

测试数据，本机无密码，所以直接执行：

#新增一个字段，使用 execute 模式
pt-online-schema-change --alter "ADD COLUMN pt_test1 INT" D=pttest,t=idc_exit --execute

结果如下报错：

The table `pttest`.`idc_exit` has triggers.  This tool needs to create its own triggers, so the table cannot already have triggers.

对象表存在触发器！于是，将其中某个表的触发器全部删除，继续执行，又发现如下错误：

You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool's documentation carefully.

对象表存在外键！

于是加入 –alter-foreign-keys-method=drop_swap 参数成功执行：

[[email protected]:~]# pt-online-schema-change --alter "ADD COLUMN pt_test2 INT" D=pttest,t=netdeviceport --execute --alter-foreign-keys-method=drop_swap 
Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable [for Statement "SET SESSION innodb_lock_wait_timeout=1"].  The current value for innodb_lock_wait_timeout is 50.  If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=50 to avoid this warning, else manually set the variable and restart MySQL.

Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable [for Statement "SET SESSION innodb_lock_wait_timeout=1"].  The current value for innodb_lock_wait_timeout is 50.  If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=50 to avoid this warning, else manually set the variable and restart MySQL.

No slaves found.  See --recursion-method if host SWEBMYVMM002293 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `pttest`.`dwdm_idc_exit_circuit_map` (approx. 642 rows)
  `pttest`.`dwdm_idc_exit_wave_division_special_line_map` (approx. 330 rows)
  `pttest`.`net_device_link_map` (approx. 8745 rows)
Will use the drop_swap method to update foreign keys.
Altering `pttest`.`netdeviceport`...
Creating new table...
Created new table pttest._netdeviceport_new OK.
Altering new table...
Altered `pttest`.`_netdeviceport_new` OK.
2016-02-23T11:25:27 Creating triggers...
2016-02-23T11:25:27 Created triggers OK.
2016-02-23T11:25:27 Copying approximately 3076859 rows...
Copying `pttest`.`netdeviceport`:  20% 01:55 remain
Copying `pttest`.`netdeviceport`:  40% 01:29 remain
Copying `pttest`.`netdeviceport`:  58% 01:03 remain
Copying `pttest`.`netdeviceport`:  73% 00:42 remain
Copying `pttest`.`netdeviceport`:  86% 00:23 remain
Copying `pttest`.`netdeviceport`:  94% 00:10 remain
2016-02-23T11:28:46 Copied rows OK.
2016-02-23T11:28:46 Drop-swapping tables...
2016-02-23T11:28:46 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2016-02-23T11:28:46 Dropping triggers...
2016-02-23T11:28:46 Dropped triggers OK.
Successfully altered `pttest`.`netdeviceport`.

新增字段成功：

四、教程分享

除此之外，percona-toolkit 还有很多实用功能，比如：

pt-config-diff ： 比较本地与远程 MySQL 配置文件差异

pt-heartbeat ： MySQL 主从复制延迟监控

pt-slave-find ： 查找和打印所有从服务器复制层级关系

pt-table-checksum ： 主从复制一致性检查

等等...

本文篇幅有限，且奔着不锁表在线修改表字段功能去的，更多功能的探索这里附上 percona-toolkit 中文使用教程：