首页手记 pt-online-schema-change 工具使用

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;

+------+----------+-----------+----------+--------+-----------+--------------+

+------+----------+-----------+----------+--------+-----------+--------------+

| 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;

+------+----------+-----------+----------+--------+-----------+--------------+------+

+------+----------+-----------+----------+--------+-----------+--------------+------+

| 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

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

点击查看更多内容

为 TA 点赞

若觉得本文不错，就分享一下吧！

评论

评论

共同学习，写下你的评论

评论加载中...

展开查看更多评论

作者其他优质文章

正在加载中

largeQ

手记
篇

粉丝

92

获赞与收藏

585

关注作者，订阅最新文章

相关文章推荐

pt-online-schema-change 工具使用

pt-online-schema-change和XtraBackup的工作原理

pt-online-schema-change你今天滥用了吗？

DM 源码阅读系列文章（八）Online Schema Change 同步支持

MySQL在线修改大表神器：PT工具

阅读免费教程

MySQL 进阶教程

32个小节 51177 958

MySQL 入门教程

32个小节 128952 1611

后端通用面试教程

41个小节 30273 342

推荐

评论

收藏

共同学习，写下你的评论



感谢您的支持，我会继续努力的～

扫码打赏，你说多少就多少

赞赏金额会直接到老师账户

支付方式

打开微信扫一扫，即可进行扫码打赏哦

今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与放弃机会

点击
抽奖

慕课手记新用户专享福利

恭喜你，你的运气太好了，居然抽中了 100个积分！

恭喜你，抽中了价值元的专栏！

太棒了，直接落到你账户里！

积分商城里的罗技鼠标、机械键盘、
Kindle 阅读器、小米平衡车
Apple iPad （10.2英寸）、大额优惠券
在等着你去兑换了噢

作者：

免费赠送

兑换码：1111222211 复制

优惠券可用于购买实战课、体系课
无门槛使用

先去看看，有什么好东西马上兑换我爱学习，选课去


热搜

最近搜索清空

pt-online-schema-change 工具使用

相关文章推荐

阅读免费教程