溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些區別”，在日常操作中，相信很多人在MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些區別問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些區別”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
mysql5.6之前執行ddl語句會執行表鎖，只允許查詢不允許更新，執行ddl主要有兩種方式copy方式和inplace方式，inplace方式又稱為(fast index creation)，其中copy方式全稱表鎖，inplace方式只支持二級索引添加和刪除。5.6之后可以利用Online DDL特性完成在線表結構調整，而pt-tools提供的pt-online-schema-change可以在幾乎無表鎖的情況下完成在線表結構調整，這里就針對mysql做下ddl的過程研究。
mysql原生的ddl方式實現形式有三種，分別是copy table、inplace、online ddl的方式，其中5.1-5.5版本實現是通過copy table的形式,5.6-5.7增加了inplace方式和Online ddl方式。
一、mysql 原生ddl實現方式
copy方式
(1).新建臨時表
(2).鎖原表，禁止DML，允許查詢
(3).將原表數據拷貝到臨時表(無排序，一行一行拷貝)
(4).刪除原表，對臨時表進行rename，升級字典鎖，禁止讀寫
(5).完成DDL，釋放鎖
inplace方式
(1).新建索引的數據字典
(2).鎖表，禁止DML，允許查詢
(3).讀取聚集索引，構造新的索引項，排序并插入新索引
(4).等待打開當前表的所有只讀事務提交
(5).創建索引結束
online ddl實現
online方式實質也包含了copy和inplace方式，對于不支持online的ddl操作采用copy方式，比如修改列類型，刪除主鍵等；對于inplace方式，mysql內部以“是否修改記錄格式”為基準也分為兩類，一類需要重建表(修改記錄格式)，比如添加、刪除列、修改列默認值等；另外一類是只需要修改表的元數據，比如添加、刪除索引、修改列名等。Mysql將這兩類方式分別稱為rebuild方式和no-rebuild方式。online ddl主要包括3個階段，prepare階段，ddl執行階段，commit階段，rebuild方式比no-rebuild方式實質多了一個ddl執行階段，prepare階段和commit階段類似。下面將主要介紹ddl執行過程中三個階段的流程。
Prepare階段：
創建新的臨時frm文件
持有EXCLUSIVE-MDL鎖，禁止讀寫
根據alter類型，確定執行方式(copy,online-rebuild,online-norebuild)
更新數據字典的內存對象
分配row_log對象記錄增量
生成新的臨時ibd文件
ddl執行階段：
降級EXCLUSIVE-MDL鎖，允許讀寫
掃描old_table的聚集索引每一條記錄rec
遍歷新表的聚集索引和二級索引，逐一處理
根據rec構造對應的索引項
將構造索引項插入sort_buffer塊
將sort_buffer塊插入新的索引
處理ddl執行過程中產生的增量(僅rebuild類型需要)
commit階段
升級到EXCLUSIVE-MDL鎖，禁止讀寫
重做最后row_log中最后一部分增量
更新innodb的數據字典表
提交事務(刷事務的redo日志)
修改統計信息
rename臨時idb文件，frm文件
變更完成
| Operation | In-Place? | Copies Table? | Allows Concurrent DML? | Allows Concurrent Query? | Notes |
|---|---|---|---|---|---|
| 添加索引 | Yes* | No* | Yes | Yes | 對全文索引的一些限制 |
| 刪除索引 | Yes | No | Yes | Yes | 僅修改表的元數據 |
| OPTIMIZE TABLE | Yes | Yes | Yes | Yes | 從 5.6.17開始使用ALGORITHM=INPLACE，當然如果指定了old_alter_table=1或mysqld啟動帶--skip-new則將還是COPY模式。如果表上有全文索引只支持COPY |
| 對一列設置默認值 | Yes | No | Yes | Yes | 僅修改表的元數據 |
| 對一列修改auto-increment 的值 | Yes | No | Yes | Yes | 僅修改表的元數據 |
| 添加 foreign key constraint | Yes* | No* | Yes | Yes | 為了避免拷貝表，在約束創建時會禁用foreign_key_checks |
| 刪除 foreign key constraint | Yes | No | Yes | Yes | foreign_key_checks 不影響 |
| 改變列名 | Yes* | No* | Yes* | Yes | 為了允許DML并發, 如果保持相同數據類型，僅改變列名 |
| 添加列 | Yes* | Yes* | Yes* | Yes | 盡管允許 ALGORITHM=INPLACE ，但數據大幅重組，所以它仍然是一項昂貴的操作。當添加列是auto-increment，不允許DML并發 |
| 刪除列 | Yes | Yes* | Yes | Yes | 盡管允許 ALGORITHM=INPLACE ，但數據大幅重組，所以它仍然是一項昂貴的操作 |
| 修改列數據類型 | No | Yes* | No | Yes | 修改類型或添加長度，都會拷貝表，而且不允許更新操作 |
| 更改列順序 | Yes | Yes | Yes | Yes | 盡管允許 ALGORITHM=INPLACE ，但數據大幅重組，所以它仍然是一項昂貴的操作 |
|
修改ROW_FORMAT 和KEY_BLOCK_SIZE | Yes | Yes | Yes | Yes | 盡管允許 ALGORITHM=INPLACE ，但數據大幅重組，所以它仍然是一項昂貴的操作 |
|
設置列屬性NULL 或NOT NULL | Yes | Yes | Yes | Yes | 盡管允許 ALGORITHM=INPLACE ，但數據大幅重組，所以它仍然是一項昂貴的操作 |
| 添加主鍵 | Yes* | Yes | Yes | Yes |
盡管允許 ALGORITHM=INPLACE ，但數據大幅重組，所以它仍然是一項昂貴的操作。 如果列定義必須轉化NOT NULL，則不允許INPLACE |
| 刪除并添加主鍵 | Yes | Yes | Yes | Yes | 在同一個 ALTER TABLE 語句刪除就主鍵、添加新主鍵時，才允許inplace；數據大幅重組,所以它仍然是一項昂貴的操作。 |
| 刪除主鍵 | No | Yes | No | Yes | 不允許并發DML，要拷貝表，而且如果沒有在同一 ATLER TABLE 語句里同時添加主鍵則會收到限制 |
| 變更表字符集 | No | Yes | No | Yes | 如果新的字符集編碼不同，重建表 |
mysql 5.7在線修改表結構案例：
語法
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時間內的所有的 插入，更新和刪除操作記錄到一個日志文件， 然后再把這些增量數據應用到相應的表上(等表上的事務完全釋放后)，這個臨時日志文件的上限值由innodb_online_alter_log_max_size指定,每次擴展innodb_sort_buffer_size的大小 該參數如果太小有可能導致DDL失敗
二、pt-online-schema-change
注意事項：
（1）表存在主鍵或唯一建
（2）磁盤容量估計
（3）原表不存在觸發器
（4）原表進行批量DML操作時，會有一定影響，需特別注意鎖等待等參數設置
（5）如果更新的表是被子表外鍵引用的父表，那么需要相應的更新子表的外鍵指向
1、online ddl原理
（1）設置mysql會話參數
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）檢查表結構，是否存在主鍵、其他外鍵參考、觸發器
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）創建新表
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）對舊表創建觸發器（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數據
copy數據一個chunk后會檢查thread_running負載、warning、從庫信息決定是否繼續copy，默認--chunk-time=0.5,根據這個時間copy的記錄動態調整chunk-size，在Copy相關的chunk時，會對原表相關記錄加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）分析新表、統計信息
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）刪除觸發器
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）完成表結構在線修改
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、增加字段
預執行：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.
正式執行：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
到此，關于“MySQL ONLINE DDL和PT-ONLINE-SCHEMA-CHANGE有哪些區別”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
