簡(jiǎn)介：
pt-online-schema-change是Percona-toolkit一員，通過改進(jìn)原生ddl的方式赃额，達(dá)到不鎖表在線修改表結(jié)構(gòu)怕犁。
pt-osc工作過程
- 創(chuàng)建一個(gè)和要執(zhí)行 alter 操作的表一樣的新的空表結(jié)構(gòu)(是alter之前的結(jié)構(gòu))
- 在新表執(zhí)行alter table 語句（速度應(yīng)該很快）
- 在原表中創(chuàng)建觸發(fā)器3個(gè)觸發(fā)器分別對(duì)應(yīng)insert,update,delete操作
- 以一定塊大小從原表拷貝數(shù)據(jù)到臨時(shí)表紫新，拷貝過程中通過原表上的觸發(fā)器在原表進(jìn)行的寫操作都會(huì)更新到新建的臨時(shí)表
- Rename 原表到old表中，在把臨時(shí)表Rename為原表
- 如果有參考該表的外鍵但绕，根據(jù)alter-foreign-keys-method參數(shù)的值，檢測(cè)外鍵相關(guān)的表，做相應(yīng)設(shè)置的處理
- 默認(rèn)最后將舊原表刪除
使用之前捏顺，請(qǐng)參考安裝Percona-toolkit教程參考：
下面是pt-online-scheme-change示例：
在test庫新建表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
我們來上面字段resptime改為responsetime
#自己修改ip和賬號(hào)和密碼
------------------------------------------------------------
~ # pt-online-schema-change --host=127.0.0.1 --user=root --password=123456 --alter "CHANGE resptime responsetime float(10,2) unsigned not NULL default '0'" D=test,t='pt' --execute --print --statistics --no-check-alter
#執(zhí)行整個(gè)流程輸出
------------------------------------------------------------
No slaves found. See --recursion-method if host localhost.localdomain has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
together with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /usr/bin/pt-online-schema-change line 6948.
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
together with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /usr/bin/pt-online-schema-change line 6948.
# A software update is available:
Operation, tries, wait:
analyze_table, 10, 1
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.
2018-01-07T16:36:29 Creating triggers...
2018-01-07T16:36:29 Created triggers OK.
2018-01-07T16:36:29 Copying approximately 1 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 7706 copy table*/
2018-01-07T16:36:29 Copied rows OK.
2018-01-07T16:36:29 Analyzing new table...
2018-01-07T16:36:29 Swapping tables...
RENAME TABLE `test`.`pt` TO `test`.`_pt_old`, `test`.`_pt_new` TO `test`.`pt`
2018-01-07T16:36:29 Swapped original and new tables OK.
2018-01-07T16:36:29 Dropping old table...
DROP TABLE IF EXISTS `test`.`_pt_old`
2018-01-07T16:36:29 Dropped old table `test`.`_pt_old` OK.
2018-01-07T16:36:29 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`
2018-01-07T16:36:29 Dropped triggers OK.
# Event Count
# ====== =====
# INSERT 1
Successfully altered `test`.`pt`.
成功看到：
mysql> show create table pt;
| 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 |
添加一個(gè)字段p1
------------------------------------------------------------
~ # pt-online-schema-change --host=127.0.0.1 --user=root --password=123456 --alter "add p1 float(10,2) not NULL default '0'" D=test,t='pt' --execute --print --statistics --no-check-alter
成功看到：
----------------------------------------------------------+
| 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 |
