percona-toolkit简介
percona-toolkit是一组高级命令行工具的集合,用来执行各种通过手工执行非常复杂和麻烦的mysql和系统任务,这些任务包括:
检查master和slave数据的一致性
有效地对记录进行归档
查找重复的索引
对服务器信息进行汇总
分析来自日志和tcpdump的查询
当系统出问题的时候收集重要的系统信息
percona-toolkit工具包安装
1.相关依赖包安装
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-Time* perl-DBD-MySQL perl-Digest-MD5 -y
2.安装percona-toolkit
wget https://www.percona.com/downloads/percona-toolkit/2.2.20/deb/percona-toolkit_2.2.20-1.tar.gz
tar zxf percona-toolkit_2.2.20-1.tar.gz
cd percona-toolkit-2.2.20/
perl Makefile.PL
make && make install
安装成功!
下面我使用percona-toolkit中的pt-online-schema-change来给一个表增加字段
pt-online-schema-change用处:
ALTER tables without locking them.修改过程中不会造成读写阻塞,用于给大表添加字段。
实现原理:
如果表有外键,除非使用 –alter-foreign-keys-method 指定特定的值,否则工具不予执行。
1 创建一个和你要执行 alter 操作的表一样的空表结构。
2 执行表结构修改,然后从原表中的数据到copy到 表结构修改后的表,
3 在原表上创建触发器将 copy 数据的过程中,在原表的更新操作 更新到新表.
注意:如果表中已经定义了触发器这个工具就不能工作了。
4 copy 完成以后,用rename table 新表代替原表,默认删除原表。
pt-online-schema-change –alter “ADD COLUMN num INT” D=test,t=info –user=test–socket=/tmp/mysql.sock –password=122541 –execute
报错如下:
No slaves found. See –recursion-method if host b0d81312ea9e 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
Altering test
.info
…
Creating new table…
Created new table sls._lisa_new OK.
Altering new table…
Altered test
.info_new
OK.
2016-12-24T08:54:54 Dropping new table…
2016-12-24T08:54:54 Dropped new table OK.
test
.info
was not altered.
The new table test
._info_new
does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
错误原因:没有给info表创建主键
解决办法:
mysql> alter table info add id int not null primary key auto_increment;
//id列必须是info表中不存在的列。存在会报错。
解决问题后再次添加字段:
pt-online-schema-change –alter “ADD COLUMN num INT” D=test,t=info –user=test –socket=/tmp/mysql.sock –password=122541 –execute
成功了!!!
No slaves found. See –recursion-method if host b0d81312ea9e 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
Altering test
.info
…
Creating new table…
Created new table sls._lisa_new OK.
Altering new table…
Altered test
._info_new
OK.
2016-12-24T09:09:15 Creating triggers…
2016-12-24T09:09:15 Created triggers OK.
2016-12-24T09:09:15 Copying approximately 12 rows…
2016-12-24T09:09:15 Copied rows OK.
2016-12-24T09:09:15 Analyzing new table…
2016-12-24T09:09:15 Swapping tables…
2016-12-24T09:09:15 Swapped original and new tables OK.
2016-12-24T09:09:15 Dropping old table…
2016-12-24T09:09:15 Dropped old table test
._info_old
OK.
2016-12-24T09:09:15 Dropping triggers…
2016-12-24T09:09:15 Dropped triggers OK.
Successfully altered test
.info
.