MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些區(qū)別

發(fā)布時間：2021-11-15 13:39:49 來源：億速云閱讀：172 作者：iii 欄目：MySQL數(shù)據(jù)庫

這篇文章主要介紹“MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些區(qū)別”，在日常操作中，相信很多人在MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些區(qū)別問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些區(qū)別”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

mysql5.6之前執(zhí)行ddl語句會執(zhí)行表鎖，只允許查詢不允許更新，執(zhí)行ddl主要有兩種方式copy方式和inplace方式，inplace方式又稱為(fast index creation)，其中copy方式全稱表鎖，inplace方式只支持二級索引添加和刪除。5.6之后可以利用Online DDL特性完成在線表結(jié)構(gòu)調(diào)整，而pt-tools提供的pt-online-schema-change可以在幾乎無表鎖的情況下完成在線表結(jié)構(gòu)調(diào)整，這里就針對mysql做下ddl的過程研究。
mysql原生的ddl方式實現(xiàn)形式有三種，分別是copy table、inplace、online ddl的方式，其中5.1-5.5版本實現(xiàn)是通過copy table的形式,5.6-5.7增加了inplace方式和Online ddl方式。
一、mysql 原生ddl實現(xiàn)方式
copy方式
　　(1).新建臨時表
　　(2).鎖原表，禁止DML，允許查詢
　　(3).將原表數(shù)據(jù)拷貝到臨時表(無排序，一行一行拷貝)
　　(4).刪除原表，對臨時表進行rename，升級字典鎖，禁止讀寫
　　(5).完成DDL，釋放鎖
inplace方式
　　(1).新建索引的數(shù)據(jù)字典
　　(2).鎖表，禁止DML，允許查詢
　　(3).讀取聚集索引，構(gòu)造新的索引項，排序并插入新索引
　　(4).等待打開當(dāng)前表的所有只讀事務(wù)提交
　　(5).創(chuàng)建索引結(jié)束
online ddl實現(xiàn)
　　online方式實質(zhì)也包含了copy和inplace方式，對于不支持online的ddl操作采用copy方式，比如修改列類型，刪除主鍵等；對于inplace方式，mysql內(nèi)部以“是否修改記錄格式”為基準(zhǔn)也分為兩類，一類需要重建表(修改記錄格式)，比如添加、刪除列、修改列默認(rèn)值等；另外一類是只需要修改表的元數(shù)據(jù)，比如添加、刪除索引、修改列名等。Mysql將這兩類方式分別稱為rebuild方式和no-rebuild方式。online ddl主要包括3個階段，prepare階段，ddl執(zhí)行階段，commit階段，rebuild方式比no-rebuild方式實質(zhì)多了一個ddl執(zhí)行階段，prepare階段和commit階段類似。下面將主要介紹ddl執(zhí)行過程中三個階段的流程。
　　Prepare階段：
　　創(chuàng)建新的臨時frm文件
　　持有EXCLUSIVE-MDL鎖，禁止讀寫
　　根據(jù)alter類型，確定執(zhí)行方式(copy,online-rebuild,online-norebuild)
　　更新數(shù)據(jù)字典的內(nèi)存對象
　　分配row_log對象記錄增量
　　生成新的臨時ibd文件
　　ddl執(zhí)行階段：
　　降級EXCLUSIVE-MDL鎖，允許讀寫
　　掃描old_table的聚集索引每一條記錄rec
　　遍歷新表的聚集索引和二級索引，逐一處理
　　根據(jù)rec構(gòu)造對應(yīng)的索引項
　　將構(gòu)造索引項插入sort_buffer塊
　　將sort_buffer塊插入新的索引
　　處理ddl執(zhí)行過程中產(chǎn)生的增量(僅rebuild類型需要)
　　commit階段
　　升級到EXCLUSIVE-MDL鎖，禁止讀寫
　　重做最后row_log中最后一部分增量
　　更新innodb的數(shù)據(jù)字典表
　　提交事務(wù)(刷事務(wù)的redo日志)
　　修改統(tǒng)計信息
　　rename臨時idb文件，frm文件
　　變更完成

Operation	In-Place?	Copies Table?	Allows Concurrent DML?	Allows Concurrent Query?	Notes
添加索引	Yes*	No*	Yes	Yes	對全文索引的一些限制
刪除索引	Yes	No	Yes	Yes	僅修改表的元數(shù)據(jù)
OPTIMIZE TABLE	Yes	Yes	Yes	Yes	從 5.6.17開始使用ALGORITHM=INPLACE，當(dāng)然如果指定了old_alter_table=1或mysqld啟動帶--skip-new則將還是COPY模式。如果表上有全文索引只支持COPY
對一列設(shè)置默認(rèn)值	Yes	No	Yes	Yes	僅修改表的元數(shù)據(jù)
對一列修改auto-increment 的值	Yes	No	Yes	Yes	僅修改表的元數(shù)據(jù)
添加 foreign key constraint	Yes*	No*	Yes	Yes	為了避免拷貝表，在約束創(chuàng)建時會禁用foreign_key_checks
刪除 foreign key constraint	Yes	No	Yes	Yes	foreign_key_checks 不影響
改變列名	Yes*	No*	Yes*	Yes	為了允許DML并發(fā), 如果保持相同數(shù)據(jù)類型，僅改變列名
添加列	Yes*	Yes*	Yes*	Yes	盡管允許 ALGORITHM=INPLACE ，但數(shù)據(jù)大幅重組，所以它仍然是一項昂貴的操作。當(dāng)添加列是auto-increment，不允許DML并發(fā)
刪除列	Yes	Yes*	Yes	Yes	盡管允許 ALGORITHM=INPLACE ，但數(shù)據(jù)大幅重組，所以它仍然是一項昂貴的操作
修改列數(shù)據(jù)類型	No	Yes*	No	Yes	修改類型或添加長度，都會拷貝表，而且不允許更新操作
更改列順序	Yes	Yes	Yes	Yes	盡管允許 ALGORITHM=INPLACE ，但數(shù)據(jù)大幅重組，所以它仍然是一項昂貴的操作
修改ROW_FORMAT 和KEY_BLOCK_SIZE	Yes	Yes	Yes	Yes	盡管允許 ALGORITHM=INPLACE ，但數(shù)據(jù)大幅重組，所以它仍然是一項昂貴的操作
設(shè)置列屬性NULL 或NOT NULL	Yes	Yes	Yes	Yes	盡管允許 ALGORITHM=INPLACE ，但數(shù)據(jù)大幅重組，所以它仍然是一項昂貴的操作
添加主鍵	Yes*	Yes	Yes	Yes	盡管允許 ALGORITHM=INPLACE ，但數(shù)據(jù)大幅重組，所以它仍然是一項昂貴的操作。如果列定義必須轉(zhuǎn)化NOT NULL，則不允許INPLACE
刪除并添加主鍵	Yes	Yes	Yes	Yes	在同一個 ALTER TABLE 語句刪除就主鍵、添加新主鍵時，才允許inplace；數(shù)據(jù)大幅重組,所以它仍然是一項昂貴的操作。
刪除主鍵	No	Yes	No	Yes	不允許并發(fā)DML，要拷貝表，而且如果沒有在同一 ATLER TABLE 語句里同時添加主鍵則會收到限制
變更表字符集	No	Yes	No	Yes	如果新的字符集編碼不同，重建表

mysql 5.7在線修改表結(jié)構(gòu)案例：
語法
alter table
| ALGORITHM [=] {DEFAULT|INPLACE|COPY}
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
| MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
online ddl的原理是，mysql把在ddl時間內(nèi)的所有的插入，更新和刪除操作記錄到一個日志文件，然后再把這些增量數(shù)據(jù)應(yīng)用到相應(yīng)的表上(等表上的事務(wù)完全釋放后)，這個臨時日志文件的上限值由innodb_online_alter_log_max_size指定,每次擴展innodb_sort_buffer_size的大小該參數(shù)如果太小有可能導(dǎo)致DDL失敗

二、pt-online-schema-change
注意事項：
    （1）表存在主鍵或唯一建
    （2）磁盤容量估計
    （3）原表不存在觸發(fā)器
    （4）原表進行批量DML操作時，會有一定影響，需特別注意鎖等待等參數(shù)設(shè)置
    （5）如果更新的表是被子表外鍵引用的父表，那么需要相應(yīng)的更新子表的外鍵指向
1、online ddl原理
（1）設(shè)置mysql會話參數(shù)
    SET SESSION innodb_lock_wait_timeout=1
    SET SESSION lock_wait_timeout=60
    SET SESSION wait_timeout=10000
    SET @@SQL_QUOTE_SHOW_CREATE = 1,@@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'
（2）檢查表結(jié)構(gòu)，是否存在主鍵、其他外鍵參考、觸發(fā)器
    SHOW TRIGGERS FROM `dbtest` LIKE 't1'
    SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='dbtest' AND referenced_table_name='t1'
（3）創(chuàng)建新表
   Creating new table...
   CREATE TABLE `dbtest`.`_t1_new` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(30) DEFAULT NULL,
      PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8
   Created new table dbtest._t1_new OK.
（4）對新表進行DDL操作
    Altering new table...
   ALTER TABLE `dbtest`.`_t1_new` ADD COLUMN phone varchar(15)
   Altered `dbtest`.`_t1_new` OK.
（5）對舊表創(chuàng)建觸發(fā)器（insert/update/delete）
    2017-11-19T18:05:26 Creating triggers...
   CREATE TRIGGER `pt_osc_dbtest_t1_del` AFTER DELETE ON `dbtest`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbtest`.`_t1_new` WHERE `dbtest`.`_t1_new`.`id` <=> OLD.`id`
   CREATE TRIGGER `pt_osc_dbtest_t1_upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
   CREATE TRIGGER `pt_osc_dbtest_t1_ins` AFTER INSERT ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
   2017-11-19T18:05:26 Created triggers OK.
（6）copy數(shù)據(jù)
    copy數(shù)據(jù)一個chunk后會檢查thread_running負(fù)載、warning、從庫信息決定是否繼續(xù)copy，默認(rèn)--chunk-time=0.5,根據(jù)這個時間copy的記錄動態(tài)調(diào)整chunk-size，在Copy相關(guān)的chunk時，會對原表相關(guān)記錄加S鎖。
    2017-11-19T18:05:26 Copying approximately 1593410 rows...
   INSERT LOW_PRIORITY IGNORE INTO `dbtest`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 9157 copy nibble*/
   SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
   2017-11-19T18:05:45 Copied rows OK.
（7）分析新表、統(tǒng)計信息
    2017-11-19T18:05:45 Analyzing new table..
（8）新、舊表交換，將舊表t1重命名為_t1_old，將新表_t1_new重命名為t1，并刪除舊表_t1_old
    2017-11-19T18:05:45 Swapping tables...
   RENAME TABLE `dbtest`.`t1` TO `dbtest`.`_t1_old`, `dbtest`.`_t1_new` TO `dbtest`.`t1`
   2017-11-19T18:05:45 Swapped original and new tables OK.
   2017-11-19T18:05:45 Dropping old table...
   DROP TABLE IF EXISTS `dbtest`.`_t1_old`
   2017-11-19T18:05:45 Dropped old table `dbtest`.`_t1_old` OK.
（9）刪除觸發(fā)器
    2017-11-19T18:05:45 Dropping triggers...
   DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;
   DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;
   DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;
   2017-11-19T18:05:45 Dropped triggers OK.
（10）完成表結(jié)構(gòu)在線修改
Successfully altered `dbtest`.`t1`.

點擊(此處)折疊或打開

171119 17:53:00 66 Connect dbuser@BX-128-28 on dbtest
66 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
66 Query SET SESSION innodb_lock_wait_timeout=1
66 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
66 Query SET SESSION lock_wait_timeout=60
66 Query SHOW VARIABLES LIKE 'wait\_timeout'
66 Query SET SESSION wait_timeout=10000
66 Query SELECT @@SQL_MODE
66 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/
66 Query SELECT @@server_id /*!50038 , @@hostname*/
67 Connect dbuser@BX-128-28 on dbtest
67 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
67 Query SET SESSION innodb_lock_wait_timeout=1
67 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
67 Query SET SESSION lock_wait_timeout=60
67 Query SHOW VARIABLES LIKE 'wait\_timeout'
67 Query SET SESSION wait_timeout=10000
67 Query SELECT @@SQL_MODE
67 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/
67 Query SELECT @@server_id /*!50038 , @@hostname*/
66 Query SHOW VARIABLES LIKE 'wsrep_on'
66 Query SHOW VARIABLES LIKE 'version%'
66 Query SHOW ENGINES
66 Query SHOW VARIABLES LIKE 'innodb_version'
66 Query SHOW VARIABLES LIKE 'innodb_stats_persistent'
66 Query SELECT CONCAT(@@hostname, @@port)
66 Query SHOW TABLES FROM `dbtest` LIKE 't1'
66 Query SHOW TRIGGERS FROM `dbtest` LIKE 't1'
66 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
66 Query USE `dbtest`
66 Query SHOW CREATE TABLE `dbtest`.`t1`
66 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
66 Query EXPLAIN SELECT * FROM `dbtest`.`t1` WHERE 1=1
66 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='dbtest' AND referenced_table_name='t1'
66 Query SHOW VARIABLES LIKE 'wsrep_on'
66 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
66 Query USE `dbtest`
66 Query SHOW CREATE TABLE `dbtest`.`t1`
66 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
66 Query CREATE TABLE `dbtest`.`_t1_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8
66 Query ALTER TABLE `dbtest`.`_t1_new` ADD COLUMN phone varchar(15)
66 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
66 Query USE `dbtest`
66 Query SHOW CREATE TABLE `dbtest`.`_t1_new`
66 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
66 Query EXPLAIN SELECT * FROM `dbtest`.`t1` WHERE 1=1
66 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lower boundary*/
66 Query SHOW TABLES FROM `dbtest` LIKE '\_t1\_new'
66 Query DROP TABLE IF EXISTS `dbtest`.`_t1_new`
67 Quit
66 Quit

2、增加字段
預(yù)執(zhí)行：pt-online-schema-change --user=dbuser --password=123456 --host=10.xx --alter "ADD COLUMN phone varchar(15)" D=dbtest,t=t1 --print --dry-run

點擊(此處)折疊或打開

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
Starting a dry run. `dbtest`.`t1` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
CREATE TABLE `dbtest`.`_t1_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8
Created new table dbtest._t1_new OK.
Altering new table...
ALTER TABLE `dbtest`.`_t1_new` ADD COLUMN phone varchar(15)
Altered `dbtest`.`_t1_new` OK.
Not creating triggers because this is a dry run.
CREATE TRIGGER `pt_osc_dbtest_t1_del` AFTER DELETE ON `dbtest`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbtest`.`_t1_new` WHERE `dbtest`.`_t1_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_dbtest_t1_upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
CREATE TRIGGER `pt_osc_dbtest_t1_ins` AFTER INSERT ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
Not copying rows because this is a dry run.
INSERT LOW_PRIORITY IGNORE INTO `dbtest`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 9137 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;
DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;
DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;
2017-11-19T17:53:00 Dropping new table...
DROP TABLE IF EXISTS `dbtest`.`_t1_new`;
2017-11-19T17:53:00 Dropped new table OK.
Dry run complete. `dbtest`.`t1` was not altered.

正式執(zhí)行：pt-online-schema-change --user=dbuser --password=123456 --host=10.xx --alter "ADD COLUMN phone varchar(15)" D=dbtest,t=t1 --print --execute

點擊(此處)折疊或打開

No slaves found. See --recursion-method if host BX-128-28 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 `dbtest`.`t1`...
Creating new table...
CREATE TABLE `dbtest`.`_t1_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8
Created new table dbtest._t1_new OK.
Altering new table...
ALTER TABLE `dbtest`.`_t1_new` ADD COLUMN phone varchar(15)
Altered `dbtest`.`_t1_new` OK.
2017-11-19T18:05:26 Creating triggers...
CREATE TRIGGER `pt_osc_dbtest_t1_del` AFTER DELETE ON `dbtest`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbtest`.`_t1_new` WHERE `dbtest`.`_t1_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_dbtest_t1_upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
CREATE TRIGGER `pt_osc_dbtest_t1_ins` AFTER INSERT ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
2017-11-19T18:05:26 Created triggers OK.
2017-11-19T18:05:26 Copying approximately 1593410 rows...
INSERT LOW_PRIORITY IGNORE INTO `dbtest`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 9157 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
2017-11-19T18:05:45 Copied rows OK.
2017-11-19T18:05:45 Analyzing new table...
2017-11-19T18:05:45 Swapping tables...
RENAME TABLE `dbtest`.`t1` TO `dbtest`.`_t1_old`, `dbtest`.`_t1_new` TO `dbtest`.`t1`
2017-11-19T18:05:45 Swapped original and new tables OK.
2017-11-19T18:05:45 Dropping old table...
DROP TABLE IF EXISTS `dbtest`.`_t1_old`
2017-11-19T18:05:45 Dropped old table `dbtest`.`_t1_old` OK.
2017-11-19T18:05:45 Dropping triggers...
DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;
DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;
DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;
2017-11-19T18:05:45 Dropped triggers OK.
Successfully altered `dbtest`.`t1`.

3、刪除字段
pt-online-schema-change --user=dbuser --password=123456 --host=10.xx --no-check-replication-filters --recursion-method=none --alter "DROP COLUMN phone " D=dbtest,t=t1 --print --execute

點擊(此處)折疊或打開

No slaves found. See --recursion-method if host BX-128-28 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 `dbtest`.`t1`...
Creating new table...
CREATE TABLE `dbtest`.`_t1_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`phone` varchar(15) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8
Created new table dbtest._t1_new OK.
Altering new table...
ALTER TABLE `dbtest`.`_t1_new` DROP COLUMN phone
Altered `dbtest`.`_t1_new` OK.
2017-11-19T22:56:33 Creating triggers...
CREATE TRIGGER `pt_osc_dbtest_t1_del` AFTER DELETE ON `dbtest`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbtest`.`_t1_new` WHERE `dbtest`.`_t1_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_dbtest_t1_upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
CREATE TRIGGER `pt_osc_dbtest_t1_ins` AFTER INSERT ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
2017-11-19T22:56:33 Created triggers OK.
2017-11-19T22:56:33 Copying approximately 1597892 rows...
INSERT LOW_PRIORITY IGNORE INTO `dbtest`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 9444 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
2017-11-19T22:56:52 Copied rows OK.
2017-11-19T22:56:52 Analyzing new table...
2017-11-19T22:56:52 Swapping tables...
RENAME TABLE `dbtest`.`t1` TO `dbtest`.`_t1_old`, `dbtest`.`_t1_new` TO `dbtest`.`t1`
2017-11-19T22:56:52 Swapped original and new tables OK.
2017-11-19T22:56:52 Dropping old table...
DROP TABLE IF EXISTS `dbtest`.`_t1_old`
2017-11-19T22:56:52 Dropped old table `dbtest`.`_t1_old` OK.
2017-11-19T22:56:52 Dropping triggers...
DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;
DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;
DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;
2017-11-19T22:56:52 Dropped triggers OK.
Successfully altered `dbtest`.`t1`.

4、添加索引
pt-online-schema-change --user=dbuser --password=123456 --host=10.xx --no-check-replication-filters --recursion-method=none --alter "add key idx_name(name)" D=dbtest,t=t1 --print --execute

點擊(此處)折疊或打開

No slaves found. See --recursion-method if host BX-128-28 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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 `dbtest`.`t1`...
Creating new table...
CREATE TABLE `dbtest`.`_t1_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8
Created new table dbtest._t1_new OK.
Altering new table...
ALTER TABLE `dbtest`.`_t1_new` add key(name)
Altered `dbtest`.`_t1_new` OK.
2017-11-19T23:00:40 Creating triggers...
CREATE TRIGGER `pt_osc_dbtest_t1_del` AFTER DELETE ON `dbtest`.`t1` FOR EACH ROW DELETE IGNORE FROM `dbtest`.`_t1_new` WHERE `dbtest`.`_t1_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_dbtest_t1_upd` AFTER UPDATE ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
CREATE TRIGGER `pt_osc_dbtest_t1_ins` AFTER INSERT ON `dbtest`.`t1` FOR EACH ROW REPLACE INTO `dbtest`.`_t1_new` (`id`, `name`) VALUES (NEW.`id`, NEW.`name`)
2017-11-19T23:00:40 Created triggers OK.
2017-11-19T23:00:40 Copying approximately 1559718 rows...
INSERT LOW_PRIORITY IGNORE INTO `dbtest`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 9453 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
2017-11-19T23:01:09 Copied rows OK.
2017-11-19T23:01:09 Analyzing new table...
2017-11-19T23:01:09 Swapping tables...
RENAME TABLE `dbtest`.`t1` TO `dbtest`.`_t1_old`, `dbtest`.`_t1_new` TO `dbtest`.`t1`
2017-11-19T23:01:09 Swapped original and new tables OK.
2017-11-19T23:01:09 Dropping old table...
DROP TABLE IF EXISTS `dbtest`.`_t1_old`
2017-11-19T23:01:09 Dropped old table `dbtest`.`_t1_old` OK.
2017-11-19T23:01:09 Dropping triggers...
DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;
DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;
DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;
2017-11-19T23:01:09 Dropped triggers OK.
Successfully altered `dbtest`.`t1`.

5、刪除索引
pt-online-schema-change --user=dbuser --password=123456 --host=10.xx --no-check-replication-filters --recursion-method=none --alter "DROP key idx_name" D=dbtest,t=t1 --print --execute
6、改變字段類型、長度
pt-online-schema-change --user=dbuser --password=123456 --host=10.xx --no-check-replication-filters --recursion-method=none --alter "modify name varchar(10)" D=dbtest,t=t1 --print --execute

到此，關(guān)于“MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些區(qū)別”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>

向AI問一下細(xì)節(jié)

MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些區(qū)別

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽