pt-online-schema-change 试用

pt-online-schema-change使用

#install percona-toolkit-2.1.1-1
#need rpm packages below,
#could search them at: http://rpm.pbone.net/
#another web site: http://rpmfind.net/linux/RPM/index.html

mysql55-libs-5.5.12-1.ius.el5.x86_64.rpm
percona-toolkit-2.1.1-1.noarch.rpm
perl-TermReadKey-2.30-3.el6.rfx.x86_64.rpm
MySQL-python-1.2.3-3.fc15.x86_64.rpm
perl-DBD-MySQL-4.014-1.el6.rfx.x86_64.rpm
perl-Test-Simple-0.98-1.el6.rfx.noarch.rpm
MySQL-shared-compat-5.6.5_m8-1.el6.x86_64.rpm
perl-DBI-1.620-1.el6.rfx.x86_64.rpm
perl-Time-HiRes-1.9724-1.el6.rfx.x86_64.rpm


#create new tables with new schema,and import table rows.
mysql> insert into t1(id,k,c,pad,test) select id,k,c,pad,'this is a test' from sbtest;
Query OK, 5000000 rows affected (3 min 32.50 sec)

#use pt-online-schema-change
[root@***** DBD-mysql-4.020]# time pt-online-schema-change --alter "ADD COLUMN c2 INT default 100" D=sbtest,t=sbtest --host=localhost --password=ondemand --user=root --execute
Altering `sbtest`.`sbtest`...
Creating new table...
Created new table sbtest._sbtest_new OK.
Altering new table...
Altered `sbtest`.`_sbtest_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 5000071 rows...
Copying `sbtest`.`sbtest`: 20% 01:54 remain
Copying `sbtest`.`sbtest`: 37% 01:38 remain
Copying `sbtest`.`sbtest`: 46% 01:44 remain
Copying `sbtest`.`sbtest`: 56% 01:33 remain
Copying `sbtest`.`sbtest`: 64% 01:21 remain
Copying `sbtest`.`sbtest`: 73% 01:05 remain
Copying `sbtest`.`sbtest`: 82% 00:46 remain
Copying `sbtest`.`sbtest`: 90% 00:25 remain
Copying `sbtest`.`sbtest`: 94% 00:14 remain
Copying `sbtest`.`sbtest`: 97% 00:08 remain
Copying `sbtest`.`sbtest`: 99% 00:00 remain
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `sbtest`.`_sbtest_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `sbtest`.`sbtest`.

real 5m49.554s
user 0m0.741s
sys 0m0.100s

#bug list:
https://bugs.launchpad.net/percona-toolkit/+bugs?field.tag=pt-online-schema-change#show_id=true&show_tag=false&show_reporter=false&show_importance=true&show_assignee=false&show_date_last_updated=false&show_datecreated=false&show_targetname=true&show_heat=true&show_milestone_name=false&show_status=true&batch_key=%5B%22-importance%22%2Cnull%2Ctrue%2C0%5D

#detail:
http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

在修改表结构过程中，可以对原表进行各种操作。注意bugs列表里的问题。

官方的这句话，值得关注：

pt-online-schema-change modifies data and structures. You should be careful with it, and test it before using it in production. You should also ensure that you have recoverable backups before using this tool.

 

安装完所有rpm包之后，可能出现链接数据错误
#运行报错
/usr/bin/perl: symbol lookup error: /usr/local/lib64/perl5/auto/DBD/mysql/mysql.so: undefined symbol: mysql_init
#解决办法，重新安装DBD-mysql
mkdir /tmp/mysql-static
cp /usr/lib64/mysql/*.a /tmp/mysql-static
perl Makefile.PL --libs="-L/tmp/mysql-static -lmysqlclient"
make
make install
rm -rf /tmp/mysql-static