pt-online-schema-change缩减数据库表空间

pt-online-schema-change, to run ALTER TABLE statements on large tables without downtime
场景：使用innodb的单独表空间，在大量插入数据后，空间变的很大，但删除数据后，数据库表空间不释放，试图使用optimize table  tablename，对innodb不起作用，可以使用mysql自带的alter table  tablename engine=innodb;来重组数据从而释放空间，但这种方式会造成表被锁，影响用户的使用!使用Percona-Toolkit中的pt-online-schema-change能够将表空间释放，而且不锁表，不影响用户的使用
安装使用过程如下：

yum install perl-DBI
yum -y install perl-DBD-MySQL
否则就会有类似的报错：

yum -y install perl-Time-HiRes
否则就会报错如下：
Can’t locate Time/HiRes.pm in @INC (@INC contains: /etc/csf /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 (eval 30)
line 2.
BEGIN failed–compilation aborted at (eval 30) line 2.
Using configuration defaults
##更正附件中应该为perl-DBD-MySQL
yum -y install perl-devel   ##先安装上面的依赖包
tar zxvf percona-toolkit-2.2.4.tar.gz
cd percona-toolkit-2.2.4
perl Makefile.PL            ##如果有报错，安装相应包即可
make
make install     即可将工具安装在/usr/local/bin目录下

[root@web2 bin]#  /usr/local/bin/pt-online-schema-change    --password=kuu****1601  --alter='ENGINE=InnoDB' D=loldbrtmp3,t=rtmprecentgamesnew --execute
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `loldbrtmp3`.`rtmprecentgamesnew`...
Creating new table...
Created new table loldbrtmp3._rtmprecentgamesnew_new OK.
Altering new table...
Altered `loldbrtmp3`.`_rtmprecentgamesnew_new` OK.
2013-08-14T10:54:38 Creating triggers...
2013-08-14T10:54:38 Created triggers OK.
2013-08-14T10:54:38 Copying approximately 3229945 rows...
Copying `loldbrtmp3`.`rtmprecentgamesnew`:   2% 21:18 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:   4% 20:47 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:   7% 20:17 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:   9% 19:43 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  11% 19:15 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  14% 18:46 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  16% 18:14 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  18% 17:41 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  21% 17:16 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  23% 16:46 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  25% 16:15 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  28% 15:47 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  30% 15:17 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  32% 14:45 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  35% 14:14 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  37% 13:42 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  39% 13:11 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  42% 12:39 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  44% 12:08 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  47% 11:37 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  49% 11:05 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  51% 10:33 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  54% 10:02 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  56% 09:31 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  58% 08:59 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  61% 08:28 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  63% 07:56 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  66% 07:25 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  68% 06:53 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  70% 06:22 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  73% 05:51 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  75% 05:19 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  78% 04:44 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  80% 04:13 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  82% 03:43 remain
Copying `loldbrtmp3`.`rtmprecentgamesnew`:  85% 03:08 remain
2013-08-14T11:13:14 Copied rows OK.
2013-08-14T11:13:14 Swapping tables...
2013-08-14T11:13:15 Swapped original and new tables OK.
2013-08-14T11:13:15 Dropping old table...
2013-08-14T11:13:15 Dropped old table `loldbrtmp3`.`_rtmprecentgamesnew_old` OK.
2013-08-14T11:13:15 Dropping triggers...
2013-08-14T11:13:15 Dropped triggers OK.
Successfully altered `loldbrtmp3`.`rtmprecentgamesnew`.

--alter 变更后的存储引挚
D 数据库
t 表   ###这个是另外一个执行过程！

执行前后的对比，见附件!

执行类似于上面的命令后，执行过程后，见附件

显然loldbrtmp3 库大小由46G变成36G，为什么优化中间db会变大，是因为会生成中间表和trigger！但执行完成后，会将中间表和trigger删除
优化前后表记录大小不变：

执行命令前后的记录是对比！显然记录数是一样的！
在执行/usr/local/bin/pt-online-schema-change    --password=kuu****1601  --alter='ENGINE=InnoDB' D=loldbrtmp3,t=rtmprecen命令的过程中，我手动向该表中插入一条记录，是可以插入的 ！
但如果该命令正在在执行时，如果mysql导入sql就会提示报错！是关于trigger的问题。
可以使用：
关于mysql trigger：
use loldbrtmp1;
show triggers;
DROP TRIGGER [schema_name.]trigger_name；  ##删除trigger的方法
 



mysql> show triggers \G
*************************** 1. row ***************************
             Trigger: recent_game_after_insert
               Event: INSERT
               Table: rtmprecentgamesnew
           Statement: BEGIN
  CALL `set_acct_analyze_rencent_game`(NEW.`acctId`, NEW.`championId`, NEW.`subType`, NEW.`MatchResults`);
END
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.00 sec)

>drop trigger  recent_game_after_insert;  ##删除trigger



对于有约束关系的表，按照上面的命令执行时会有报错，例如：
you did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool's documentation carefully
[root@db scripts]# /usr/local/bin/pt-online-schema-change    --password=*****  --alter='ENGINE=InnoDB' --alter-foreign-keys-method="auto" D=dota2_db,t=matches  --execute         
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `dota2_db`.`slots` (approx. 252714702 rows)
Will automatically choose the method to update foreign keys.
Altering `dota2_db`.`matches`...
Creating new table...
Created new table dota2_db._matches_new OK.
Altering new table...
Altered `dota2_db`.`_matches_new` OK.
2013-10-09T01:13:51 Creating triggers...
2013-10-09T01:13:51 Created triggers OK.
2013-10-09T01:13:51 Copying approximately 22050254 rows...
Copying `dota2_db`.`matches`:  18% 02:09 remain
Copying `dota2_db`.`matches`:  37% 01:41 remain
Copying `dota2_db`.`matches`:  54% 01:14 remain
Copying `dota2_db`.`matches`:  71% 00:47 remain
Copying `dota2_db`.`matches`:  88% 00:19 remain
2013-10-09T01:16:42 Copied rows OK.
2013-10-09T01:16:42 Max rows for the rebuild_constraints method: 266818
Determining the method to update foreign keys...
2013-10-09T01:16:42   `dota2_db`.`slots`: too many rows: 252721297; must use drop_swap
2013-10-09T01:16:42 Drop-swapping tables...
2013-10-09T01:16:46 Dropped and swapped tables OK.
2013-10-09T01:16:46 Dropping triggers...
2013-10-09T01:16:46 Dropped triggers OK.
Successfully altered `dota2_db`.`matches`.
或者修改表结构或添加索引，用这个都不会锁表的！
The table `db6`.`tnew` has triggers.  This tool needs to create its own triggers, so the table cannot already have triggers.
显然db6 已经有存储过程了，需要把该存储过程先删除，然后再执行语句，执行完毕后，再次添加存储过程即可
/usr/local/bin/pt-online-schema-change    --password=k***1  --alter='ADD item6 INT NULL COMMENT  "new goods" AFTER  ITEMS' D=db10,t=tnew --execute

在使用过程中还遇到一个问题：
Error copying rows from `d**a2_db`.`slots` to `d***a2_db`.`_slots_new`: 2014-05-26T05:57:24 DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails
解决方法：
/usr/local/bin/pt-online-schema-change    --password=****  --alter='ENGINE=InnoDB'   --set-vars="foreign_key_checks=0" D=d***a2_db,t=slots --execute