为了账号安全,请及时绑定邮箱和手机立即绑定

pt-online-schema-change 工具使用

标签:
MySQL


地址: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

©著作权归作者所有:来自51CTO博客作者mrcto的原创作品,如需转载,请注明出处,否则将追究法律责任

mysqlpt-online-schema-cha在线更新表结构MySQL


点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消