pt-online-schema-change 工具使用-白红宇

pt-online-schema-change 工具使用

阅读量：5935 次

发布时间：2019-06-19

本文共 8110 字，大约阅读时间需要 27 分钟。

地址：

#下载：多种形式下载

wget percona.com/get/percona-toolkit.tar.gzwget percona.com/get/percona-toolkit.rpmwget percona.com/get/percona-toolkit.debwget 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.gztar -zxvf DBI-1.625.tar.gzcd DBI-1.625perl Makefile.PLmakemake install

##Install DBD::Mysql

wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.023.tar.gztar -zxvf DBD-mysql-4.023.tar.gzcd DBD-mysql-4.023perl Makefile.PLmakemake install

##Install percona-toolkit

tar -zxvf percona-toolkit-2.2.1.tar.gzcd percona-toolkit-2.2.1perl Makefile.PLmakemake 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, 1Altering `test`.`pt`...Renaming columns: resptime to responsetimeCreating 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=utf8Created 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  1Successfully 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

工具集地址：

工具地址：

转载于:https://blog.51cto.com/mrcto/1319549

你可能感兴趣的文章

如何确定一个网站是用Wordpress开发的