导读:percona-toolkit 源自 Maatkit 和 Aspersa 工具,这两个工具是管理 mysql 的最有名的工具,现在 Maatkit 工具已经不维护了,请大家还是使用 percona-toolkit 吧!这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类,作为一个优秀的 DBA,里面有的工具非常有用,如果能掌握并加以灵活应用,将能极大的提高工作效率。
percona-toolkit 是一组高级命令行工具的集合,用来执行各种通过手工执行非常复杂和麻烦的 mysql 任务和系统任务,这些任务包括:
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 在线安装即可:
[root@localhost:~]# 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 命令:
[root@localhost:~]# 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 这个字段属性修改工具。
①、功能介绍
如果没有这个工具,直接使用 alter 修改字段,过程如下:
为了避免锁表,该换 pt-online-schema-change 出马了!
pt-online-schema-change 的 工作过程解析如下:
因此,这个工具有如下限制:
②、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 参数成功执行:
[root@localhost:~]# 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 中文使用教程: