pt-online-schema-change 工具使用

Green

　　地址：http://www.percona.com/doc/percona-toolkit/2.1/index.html
　　
　　#下载：多种形式下载

wget percona.com/get/percona-toolkit.tar.gz
wget percona.com/get/percona-toolkit.rpm
wget percona.com/get/percona-toolkit.deb
wget percona.com/get/TOOL-Name

　　#解决依赖
　　Python 2.7+版本
　　

　　
　　#安装percona-toolkit：

　　##Install DBI

wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
tar -zxvf DBI-1.625.tar.gz
cd DBI-1.625
perl Makefile.PL
make
make install

##Install DBD::Mysql

wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.023.tar.gz
tar -zxvf DBD-mysql-4.023.tar.gz
cd DBD-mysql-4.023
perl Makefile.PL
make
make install

##Install percona-toolkit
　　

tar -zxvf percona-toolkit-2.2.1.tar.gz
cd percona-toolkit-2.2.1
perl Makefile.PL
make
make install

　　#场景一：修改表结构
　　#修改前的表结构：
　　mysql> show create table pt;

CREATE TABLE `pt` (
 `id` int(11) NOT NULL DEFAULT '0',
 `disktype` enum('sas','shannon','memdisk') NOT NULL,
 `blocksize` enum('4K','64K','256K','1M') NOT NULL,
 `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL,
 `thread` enum('1','2','4','8','16','32') NOT NULL,
 `bandwidth` float(10,2) unsigned NOT NULL,
 `resptime` float(10,2) unsigned NOT NULL DEFAULT '0.00',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　

　　#执行修改

[root@mysql-A luckyy]# pt-online-schema-change --alter "CHANGE resptime responsetime float(10,2) unsigned not NULL default '0'" D=test,t='pt' --
execute --print --statistics --no-check-alter

　　#执行结果：

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 `test`.`pt`...
Renaming columns:
 resptime to responsetime
Creating new table...
CREATE TABLE `test`.`_pt_new` (
 `id` int(11) NOT NULL DEFAULT '0',
 `disktype` enum('sas','shannon','memdisk') NOT NULL,
 `blocksize` enum('4K','64K','256K','1M') NOT NULL,
 `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL,
 `thread` enum('1','2','4','8','16','32') NOT NULL,
 `bandwidth` float(10,2) unsigned NOT NULL,
 `resptime` float(10,2) unsigned NOT NULL DEFAULT '0.00',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Created new table test._pt_new OK.
Altering new table...
ALTER TABLE `test`.`_pt_new` CHANGE resptime responsetime float(10,2) unsigned not NULL default '0'
Altered `test`.`_pt_new` OK.
2013-09-04T07:01:05 Creating triggers...
CREATE TRIGGER `pt_osc_test_pt_del` AFTER DELETE ON `test`.`pt` FOR EACH ROW DELETE IGNORE FROM `test`.`_pt_new` WHERE `test`.`_pt_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_test_pt_upd` AFTER UPDATE ON `test`.`pt` FOR EACH ROW REPLACE INTO `test`.`_pt_new` (`id`, `disktype`, `blocksize`, `testmode`, `thread`, `bandwidth`, `responsetime`) VALUES (NEW.`id`, NEW.`disktype`, NEW.`blocksize`, NEW.`testmode`, NEW.`thread`, NEW.`bandwidth`, NEW.`resptime`)
CREATE TRIGGER `pt_osc_test_pt_ins` AFTER INSERT ON `test`.`pt` FOR EACH ROW REPLACE INTO `test`.`_pt_new` (`id`, `disktype`, `blocksize`, `testmode`, `thread`, `bandwidth`, `responsetime`) VALUES (NEW.`id`, NEW.`disktype`, NEW.`blocksize`, NEW.`testmode`, NEW.`thread`, NEW.`bandwidth`, NEW.`resptime`)
2013-09-04T07:01:05 Created triggers OK.
2013-09-04T07:01:05 Copying approximately 432 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_pt_new` (`id`, `disktype`, `blocksize`, `testmode`, `thread`, `bandwidth`, `responsetime`) SELECT `id`, `disktype`, `blocksize`, `testmode`, `thread`, `bandwidth`, `resptime` FROM `test`.`pt` LOCK IN SHARE MODE /*pt-online-schema-change 25342 copy table*/
2013-09-04T07:01:05 Copied rows OK.
2013-09-04T07:01:05 Swapping tables...
RENAME TABLE `test`.`pt` TO `test`.`_pt_old`, `test`.`_pt_new` TO `test`.`pt`
2013-09-04T07:01:05 Swapped original and new tables OK.
2013-09-04T07:01:05 Dropping old table...
DROP TABLE IF EXISTS `test`.`_pt_old`
2013-09-04T07:01:05 Dropped old table `test`.`_pt_old` OK.
2013-09-04T07:01:05 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_pt_del`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_pt_upd`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_pt_ins`;
2013-09-04T07:01:05 Dropped triggers OK.
# Event  Count
# ================== =====
# INSERT   1
# mysql_warning_1592  1
Successfully altered `test`.`pt`.

　　

　　

　　#修改后的表结构：
　　mysql> show create table pt;

CREATE TABLE `pt` (
 `id` int(11) NOT NULL DEFAULT '0',
 `disktype` enum('sas','shannon','memdisk') NOT NULL,
 `blocksize` enum('4K','64K','256K','1M') NOT NULL,
 `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL,
 `thread` enum('1','2','4','8','16','32') NOT NULL,
 `bandwidth` float(10,2) unsigned NOT NULL,
`responsetime` float(10,2) unsigned NOT NULL DEFAULT '0.00',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　#修改后的数据查询：

mysql> select * from pt limit 10;
+------+----------+-----------+----------+--------+-----------+--------------+
| id | disktype | blocksize | testmode | thread | bandwidth | responsetime |
+------+----------+-----------+----------+--------+-----------+--------------+
| 1056 | shannon | 64K  | seqwr | 1  | 459.61 |   0.16 |
| 1058 | shannon | 64K  | seqwr | 2  | 415.43 |   0.32 |
| 1060 | shannon | 64K  | seqwr | 4  | 417.14 |   1.73 |
| 1062 | shannon | 64K  | seqwr | 8  | 408.06 |   5.86 |
| 1064 | shannon | 64K  | seqwr | 16  | 385.12 |  12.57 |
| 1066 | shannon | 64K  | seqwr | 32  | 381.26 |  25.74 |
| 1068 | shannon | 256K  | seqwr | 1  | 650.01 |   0.43 |
| 1070 | shannon | 256K  | seqwr | 2  | 681.42 |   0.81 |
| 1072 | shannon | 256K  | seqwr | 4  | 732.52 |   3.65 |
| 1074 | shannon | 256K  | seqwr | 8  | 674.30 |  11.79 |
+------+----------+-----------+----------+--------+-----------+--------------+
10 rows in set (0.00 sec)

　　
　　#场景二：增加列

　　
　　#执行修改 增加

[root@mysql-A luckyy]# pt-online-schema-change --alter "add p1 float(10,2) not NULL default '0'" D=test,t='pt' --execute --print --statistics --no-check-alter

　　#修改后的表结构：
　　mysql> show create table pt;

CREATE TABLE `pt` (
 `id` int(11) NOT NULL DEFAULT '0',
 `disktype` enum('sas','shannon','memdisk') NOT NULL,
 `blocksize` enum('4K','64K','256K','1M') NOT NULL,
 `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL,
 `thread` enum('1','2','4','8','16','32') NOT NULL,
 `bandwidth` float(10,2) unsigned NOT NULL,
 `responsetime` float(10,2) unsigned NOT NULL DEFAULT '0.00',
`p1` float(10,2) NOT NULL DEFAULT '0.00',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　#修改后的数据查询：

mysql> select * from pt limit 10;
+------+----------+-----------+----------+--------+-----------+--------------+------+
| id | disktype | blocksize | testmode | thread | bandwidth | responsetime | p1 |
+------+----------+-----------+----------+--------+-----------+--------------+------+
| 1056 | shannon | 64K  | seqwr | 1  | 459.61 |   0.16 | 0.00 |
| 1058 | shannon | 64K  | seqwr | 2  | 415.43 |   0.32 | 0.00 |
| 1060 | shannon | 64K  | seqwr | 4  | 417.14 |   1.73 | 0.00 |
| 1062 | shannon | 64K  | seqwr | 8  | 408.06 |   5.86 | 0.00 |
| 1064 | shannon | 64K  | seqwr | 16  | 385.12 |  12.57 | 0.00 |
| 1066 | shannon | 64K  | seqwr | 32  | 381.26 |  25.74 | 0.00 |
| 1068 | shannon | 256K  | seqwr | 1  | 650.01 |   0.43 | 0.00 |
| 1070 | shannon | 256K  | seqwr | 2  | 681.42 |   0.81 | 0.00 |
| 1072 | shannon | 256K  | seqwr | 4  | 732.52 |   3.65 | 0.00 |
| 1074 | shannon | 256K  | seqwr | 8  | 674.30 |  11.79 | 0.00 |
+------+----------+-----------+----------+--------+-----------+--------------+------+
10 rows in set (0.00 sec)

　　
　　#场景三：删除列

　　#执行修改 删除

[root@mysql-A luckyy]# pt-online-schema-change --alter "drop p1" D=test,t='pt' --execute --print --statistics --no-check-alter

　　#修改后的表结构：
　　mysql> show create table pt;

CREATE TABLE `pt` (
 `id` int(11) NOT NULL DEFAULT '0',
 `disktype` enum('sas','shannon','memdisk') NOT NULL,
 `blocksize` enum('4K','64K','256K','1M') NOT NULL,
 `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL,
 `thread` enum('1','2','4','8','16','32') NOT NULL,
 `bandwidth` float(10,2) unsigned NOT NULL,
 `responsetime` float(10,2) unsigned NOT NULL DEFAULT '0.00',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　

　　工具集地址：http://www.percona.com/doc/percona-toolkit/2.1/index.html
　　工具地址：http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html