pt-online-schema-change 工具使用薦

mrcto發表於2013-11-03

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

MySQL修改大表工具pt-online-schema-change原理
2019-10-24
MySql
一個 MySQL 線上 DDL 工具 — pt-online-schema-change
2022-02-17
MySql
使用JSF框架推薦使用工具
2005-08-11
JS框架
MySQL online ddl 工具之pt-online-schema-change
2016-01-08
MySql
【MySQL】online ddl 工具之pt-online-schema-change
2015-09-17
MySql
pt-osc(pt-online-schema-change)工具實現原理剖析
2017-08-31
線上改表工具oak-online-alter-table和pt-online-schema-change的使用限制總結
2018-03-23
經歷熊貓：瑞星專殺工具使用感受薦
2007-01-26
MySQL 5.7使用pt-online-schema-change對大表加索引
2021-02-01
MySql索引
MySQL 5.7使用pt-online-schema-change對大表加欄位
2019-10-23
MySql
pt-online-schema-change 使用異常處理及注意事項
2017-02-28
推薦一個工具
2019-02-16
實用工具推薦
2017-12-21
MySQL 5.6使用pt-online-schema-change線上修改大表欄位長度
2020-09-21
MySql
推薦兩款 GTD 工具
2019-03-04
js線上加密工具推薦
2020-11-23
JS加密
[?]前端開發工具推薦
2022-04-01
前端
良心工具吐血推薦指南
2018-06-21
工具軟體推薦--Unlocker
2008-03-06
我為什麼不推薦使用BeanUtils屬性轉換工具
2020-07-16
Bean
01.使用微軟官方工具製作啟動U盤--非常推薦
2019-09-23
微軟
Docker-視覺化管理工具總結-推薦使用Portainer
2022-03-06
Docker視覺化AI
推薦移動UI/UX設計師和PM使用的原型工具
2014-03-18
UIUX原型
使用Mac開發Android/iOS/OS X圖示生成工具推薦
2015-10-05
MacAndroidiOS
pt-online-schema-change工作過程介紹
2018-03-20
RSS生成工具/服務推薦
2022-01-29
推薦一個清單工具
2018-08-14
Windows生產力工具推薦
2020-11-30
Windows
推薦幾個好用的工具
2023-04-19
sqlite視覺化工具推薦
2017-07-05
SQLite視覺化
推薦一發梯子工具kone
2016-12-20
推薦幾款實用工具
2018-01-12
CSS除錯工具推薦—IETester
2009-03-19
CSS除錯
Linux 開發工具推薦
2011-12-05
Linux
推薦給中小團隊使用的免費專案管理工具
2018-12-26
專案管理
Python用什麼工具好?Python工具推薦!
2020-12-16
Python
【工具篇】最流行的Web漏洞掃描工具推薦！
2022-08-15
Web
MySQL:pt-online-schema-change原理及注意點（未完）
2020-06-21
MySql

pt-online-schema-change 工具使用薦

相關文章