官方文檔
pt-online-schema-change是個可以不鎖表的情況下更改表結(jié)構(gòu)的工具,也可以添加索引,建議在表數(shù)據(jù)量極大的時候使用
安裝
apt install percona-toolkit
準備一個千萬行數(shù)據(jù)的大表
CREATE TABLE person(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '主鍵',
person_id tinyint not null comment '用戶id',
person_name VARCHAR(200) comment '用戶名稱',
gmt_create datetime comment '創(chuàng)建時間',
gmt_modified datetime comment '修改時間'
) comment '人員信息表';
添加一條數(shù)據(jù)
insert into person values(1, 1,'user_1', NOW(), now());
以下 sql 執(zhí)行 20 次 約等于 100 萬條數(shù)據(jù)執(zhí)行 24 次 1600 萬條數(shù)據(jù)
insert into person(id, person_id, person_name, gmt_create, gmt_modified)
select @i:=@i+1,
left(rand()*10,10) as person_id,
concat('user_',@i%2048), //concat在簡書是非法字符你敢信
date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND),
date_add(date_add(gmt_modified,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)
from person;
image.png
pt-online-schema-change 添加一列c1
pt-online-schema-change --host=192.168.6.14 --user=root --password=123456 --alter "ADD COLUMN c1 INT" D=t1,t=person --print --execute
--host 主機
--user 賬號
--password 密碼
--alter 修改表結(jié)構(gòu)語句
D 數(shù)據(jù)庫
t 表
--print 打印工具執(zhí)行過程
--execute 確認開始
因為表數(shù)據(jù)量過大,一時半會執(zhí)行不完,這個時候可以去添加幾條新數(shù)據(jù),刪除幾條數(shù)據(jù),執(zhí)行成功,然后等待pt-online-schema-change執(zhí)行完畢,刷新表發(fā)現(xiàn)成功添加字段,剛剛添加的數(shù)據(jù)也在,刪除的數(shù)據(jù)也不見了
image.png
image.png
流程
- 檢測有沒從庫,如果有從庫,從庫也會執(zhí)行添加字段操作
- 創(chuàng)建一個新表 _person_new
- 執(zhí)行
ALTER TABLE `t1`.`_person_new` ADD COLUMN c1 INT
- 創(chuàng)建三個觸發(fā)器
#刪除操作觸發(fā)器
Event : DELETE
Name : pt_osc_t1_person_del
SQL : CREATE TRIGGER `pt_osc_t1_person_del` AFTER DELETE ON `t1`.`person` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `t1`.`_person_new` WHERE `t1`.`_person_new`.`id` <=> OLD.`id`; END
Suffix: del
Time : AFTER
#更新操作觸發(fā)器
Event : UPDATE
Name : pt_osc_t1_person_upd
SQL : CREATE TRIGGER `pt_osc_t1_person_upd` AFTER UPDATE ON `t1`.`person` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `t1`.`_person_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `t1`.`_person_new`.`id` <=> OLD.`id`; REPLACE INTO `t1`.`_person_new` (`id`, `person_id`, `person_name`, `gmt_create`, `gmt_modified`) VALUES (NEW.`id`, NEW.`person_id`, NEW.`person_name`, NEW.`gmt_create`, NEW.`gmt_modified`); END
Suffix: upd
Time : AFTER
#添加操作觸發(fā)器
Event : INSERT
Name : pt_osc_t1_person_ins
SQL : CREATE TRIGGER `pt_osc_t1_person_ins` AFTER INSERT ON `t1`.`person` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `t1`.`_person_new` (`id`, `person_id`, `person_name`, `gmt_create`, `gmt_modified`) VALUES (NEW.`id`, NEW.`person_id`, NEW.`person_name`, NEW.`gmt_create`, NEW.`gmt_modified`);END
Suffix: ins
Time : AFTER
- 從person表拷貝數(shù)據(jù)到_person_new表
INSERT LOW_PRIORITY IGNORE INTO `t1`.`_person_new` (`id`, `person_id`, `person_name`, `gmt_create`, `gmt_modified`) SELECT `id`, `person_id`, `person_name`, `gmt_create`, `gmt_modified` FROM `t1`.`person` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE
- 改名,刪除舊表
#原子操作 person改名 person_old,person_new改名person
RENAME TABLE `t1`.`person` TO `t1`.`_person_old`, `t1`.`_person_new` TO `t1`.`person`
#刪除表person_old
DROP TABLE IF EXISTS `t1`.`_person_old`
#刪除觸發(fā)器
DROP TRIGGER IF EXISTS `t1`.`pt_osc_t1_person_del`
DROP TRIGGER IF EXISTS `t1`.`pt_osc_t1_person_upd`
DROP TRIGGER IF EXISTS `t1`.`pt_osc_t1_person_ins`
- 結(jié)束
注意事項
- 如果表里面已經(jīng)添加了觸發(fā)器,該工具將不會啟動,可以嘗試用gh-ost工具,一款基于binlog的不鎖表修改結(jié)構(gòu)工具
- 如果表里面有外鍵,除非你指定--alter-foreign-keys-method參數(shù),否則工具將不會啟動
- 如果表里沒有主鍵或者唯一鍵,該工具將不會啟動
- 如果表里檢測到復制過濾器,該工具將不會啟動
- 如果該工具觀察到任何復制延遲的副本擅这，它會暫停數(shù)據(jù)復制操作
- 如果檢測到服務(wù)器負載過大凝化，該工具會暫屯彀Γ或中止其操作膘滨。
