percona-toolkit的安装和使用

iyufygfd

percona-toolkit简介

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

• 检查master和slave数据的一致性

• 有效地对记录进行归档

• 查找重复的索引

• 对服务器信息进行汇总

• 分析来自日志和tcpdump的查询

• 当系统出问题的时候收集重要的系统信息

  
  

percona-toolkit工具包安装

#wget https://www.percona.com/download ... kit_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  报错如下：

[iyunv@b0d81312ea9e percona-toolkit-2.2.20]# perl Makefile.PL
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1.
BEGIN failed--compilation aborted at Makefile.PL line 1.
该错误解决办法：

#yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker -y
问题解决，下面我继续安装
#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=sls,t=lisa
//给我mysql中的sls数据库的lisa表增加一个num字段   D：指定数据库  t:指定表
报错如下：
Can't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/bin/pt-online-schema-change line 3727.
BEGIN failed--compilation aborted at /usr/local/bin/pt-online-schema-change line 3727.
该错误的意思是：系统没有安装Perl的Time组件
该错误解决办法：
# yum -y install perl-Time*
解决问题后再次添加字段：
# pt-online-schema-change --alter "ADD COLUMN num INT" D=sls,t=lisa
再次报错如下：

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

Exiting without altering `sls`.`lisa` because neither --dry-run nor --execute was specified.  Please read the tool's documentation carefully before using this tool.

解决办法：

# pt-online-schema-change --alter "ADD COLUMN num INT" D=sls,t=lisa --user=sls --socket=/tmp/mysql.sock --password=122541 --execute
报错如下：
You do not have the PROCESS privilege at /usr/local/bin/pt-online-schema-change line 4330.
错误原因：没有给sls用户授权
解决办法
mysql>grant all privileges on *.* to sls@localhost identified by "122541";
mysql> flush privileges;
解决问题后再次添加字段：
# pt-online-schema-change --alter "ADD COLUMN num INT" D=sls,t=lisa --user=sls --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 `sls`.`lisa`...
Creating new table...
Created new table sls._lisa_new OK.
Altering new table...
Altered `sls`.`_lisa_new` OK.
2016-12-24T08:54:54 Dropping new table...
2016-12-24T08:54:54 Dropped new table OK.
`sls`.`lisa` was not altered.
The new table `sls`.`_lisa_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.

错误原因：没有给lisa表创建主键
解决办法：
mysql> alter table lisa add id int not null primary key auto_increment;
//id列必须是lisa表中不存在的列。存在会报错。

解决问题后再次添加字段：

# pt-online-schema-change --alter "ADD COLUMN num INT" D=sls,t=lisa --user=sls --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 `sls`.`lisa`...
Creating new table...
Created new table sls._lisa_new OK.
Altering new table...
Altered `sls`.`_lisa_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 `sls`.`_lisa_old` OK.
2016-12-24T09:09:15 Dropping triggers...
2016-12-24T09:09:15 Dropped triggers OK.
Successfully altered `sls`.`lisa`.
成功了！！！



终于成功了！！我已经爱上我自己！！哈哈哈