MySQL ONLINE DDL 和PT-ONLINE-SCHEMA-CHANGE對比

yepkeepmoving發表於2017-11-19
原文網址 : http://blog.itpub.net/27067062/viewspace-2147452/
MySql
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`.

點選(此處)摺疊或開啟

  1. 171119 17:53:00 66 Connect dbuser@BX-128-28 on dbtest
  2.                    66 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
  3.                    66 Query SET SESSION innodb_lock_wait_timeout=1
  4.                    66 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
  5.                    66 Query SET SESSION lock_wait_timeout=60
  6.                    66 Query SHOW VARIABLES LIKE 'wait\_timeout'
  7.                    66 Query SET SESSION wait_timeout=10000
  8.                    66 Query SELECT @@SQL_MODE
  9.                    66 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/
  10.                    66 Query SELECT @@server_id /*!50038 , @@hostname*/
  11.                    67 Connect dbuser@BX-128-28 on dbtest
  12.                    67 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
  13.                    67 Query SET SESSION innodb_lock_wait_timeout=1
  14.                    67 Query SHOW VARIABLES LIKE 'lock\_wait_timeout'
  15.                    67 Query SET SESSION lock_wait_timeout=60
  16.                    67 Query SHOW VARIABLES LIKE 'wait\_timeout'
  17.                    67 Query SET SESSION wait_timeout=10000
  18.                    67 Query SELECT @@SQL_MODE
  19.                    67 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/
  20.                    67 Query SELECT @@server_id /*!50038 , @@hostname*/
  21.                    66 Query SHOW VARIABLES LIKE 'wsrep_on'
  22.                    66 Query SHOW VARIABLES LIKE 'version%'
  23.                    66 Query SHOW ENGINES
  24.                    66 Query SHOW VARIABLES LIKE 'innodb_version'
  25.                    66 Query SHOW VARIABLES LIKE 'innodb_stats_persistent'
  26.                    66 Query SELECT CONCAT(@@hostname, @@port)
  27.                    66 Query SHOW TABLES FROM `dbtest` LIKE 't1'
  28.                    66 Query SHOW TRIGGERS FROM `dbtest` LIKE 't1'
  29.                    66 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
  30.                    66 Query USE `dbtest`
  31.                    66 Query SHOW CREATE TABLE `dbtest`.`t1`
  32.                    66 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
  33.                    66 Query EXPLAIN SELECT * FROM `dbtest`.`t1` WHERE 1=1
  34.                    66 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='dbtest' AND referenced_table_name='t1'
  35.                    66 Query SHOW VARIABLES LIKE 'wsrep_on'
  36.                    66 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
  37.                    66 Query USE `dbtest`
  38.                    66 Query SHOW CREATE TABLE `dbtest`.`t1`
  39.                    66 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
  40.                    66 Query CREATE TABLE `dbtest`.`_t1_new` (
  41.   `id` int(11) NOT NULL AUTO_INCREMENT,
  42.   `name` varchar(30) DEFAULT NULL,
  43.   PRIMARY KEY (`id`)
  44. ) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8
  45.                    66 Query ALTER TABLE `dbtest`.`_t1_new` ADD COLUMN phone varchar(15)
  46.                    66 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
  47.                    66 Query USE `dbtest`
  48.                    66 Query SHOW CREATE TABLE `dbtest`.`_t1_new`
  49.                    66 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
  50.                    66 Query EXPLAIN SELECT * FROM `dbtest`.`t1` WHERE 1=1
  51.                    66 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lower boundary*/
  52.                    66 Query SHOW TABLES FROM `dbtest` LIKE '\_t1\_new'
  53.                    66 Query DROP TABLE IF EXISTS `dbtest`.`_t1_new`
  54.                    67 Quit
  55.                    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

點選(此處)摺疊或開啟

  1. Operation, tries, wait:
  2.   analyze_table, 10, 1
  3.   copy_rows, 10, 0.25
  4.   create_triggers, 10, 1
  5.   drop_triggers, 10, 1
  6.   swap_tables, 10, 1
  7.   update_foreign_keys, 10, 1
  8. Starting a dry run. `dbtest`.`t1` will not be altered. Specify --execute instead of --dry-run to alter the table.
  9. Creating new table...
  10. CREATE TABLE `dbtest`.`_t1_new` (
  11.   `id` int(11) NOT NULL AUTO_INCREMENT,
  12.   `name` varchar(30) DEFAULT NULL,
  13.   PRIMARY KEY (`id`)
  14. ) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8
  15. Created new table dbtest._t1_new OK.
  16. Altering new table...
  17. ALTER TABLE `dbtest`.`_t1_new` ADD COLUMN phone varchar(15)
  18. Altered `dbtest`.`_t1_new` OK.
  19. Not creating triggers because this is a dry run.
  20. 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`
  21. 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`)
  22. 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`)
  23. Not copying rows because this is a dry run.
  24. 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*/
  25. SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
  26. Not swapping tables because this is a dry run.
  27. Not dropping old table because this is a dry run.
  28. Not dropping triggers because this is a dry run.
  29. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;
  30. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;
  31. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;
  32. 2017-11-19T17:53:00 Dropping new table...
  33. DROP TABLE IF EXISTS `dbtest`.`_t1_new`;
  34. 2017-11-19T17:53:00 Dropped new table OK.
  35. 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

點選(此處)摺疊或開啟

  1. No slaves found. See --recursion-method if host BX-128-28 has slaves.
  2. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
  3. Operation, tries, wait:
  4.   analyze_table, 10, 1
  5.   copy_rows, 10, 0.25
  6.   create_triggers, 10, 1
  7.   drop_triggers, 10, 1
  8.   swap_tables, 10, 1
  9.   update_foreign_keys, 10, 1
  10. Altering `dbtest`.`t1`...
  11. Creating new table...
  12. CREATE TABLE `dbtest`.`_t1_new` (
  13.   `id` int(11) NOT NULL AUTO_INCREMENT,
  14.   `name` varchar(30) DEFAULT NULL,
  15.   PRIMARY KEY (`id`)
  16. ) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8
  17. Created new table dbtest._t1_new OK.
  18. Altering new table...
  19. ALTER TABLE `dbtest`.`_t1_new` ADD COLUMN phone varchar(15)
  20. Altered `dbtest`.`_t1_new` OK.
  21. 2017-11-19T18:05:26 Creating triggers...
  22. 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`
  23. 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`)
  24. 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`)
  25. 2017-11-19T18:05:26 Created triggers OK.
  26. 2017-11-19T18:05:26 Copying approximately 1593410 rows...
  27. 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*/
  28. SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
  29. 2017-11-19T18:05:45 Copied rows OK.
  30. 2017-11-19T18:05:45 Analyzing new table...
  31. 2017-11-19T18:05:45 Swapping tables...
  32. RENAME TABLE `dbtest`.`t1` TO `dbtest`.`_t1_old`, `dbtest`.`_t1_new` TO `dbtest`.`t1`
  33. 2017-11-19T18:05:45 Swapped original and new tables OK.
  34. 2017-11-19T18:05:45 Dropping old table...
  35. DROP TABLE IF EXISTS `dbtest`.`_t1_old`
  36. 2017-11-19T18:05:45 Dropped old table `dbtest`.`_t1_old` OK.
  37. 2017-11-19T18:05:45 Dropping triggers...
  38. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;
  39. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;
  40. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;
  41. 2017-11-19T18:05:45 Dropped triggers OK.
  42. 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

點選(此處)摺疊或開啟

  1. No slaves found. See --recursion-method if host BX-128-28 has slaves.
  2. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
  3. Operation, tries, wait:
  4.   analyze_table, 10, 1
  5.   copy_rows, 10, 0.25
  6.   create_triggers, 10, 1
  7.   drop_triggers, 10, 1
  8.   swap_tables, 10, 1
  9.   update_foreign_keys, 10, 1
  10. Altering `dbtest`.`t1`...
  11. Creating new table...
  12. CREATE TABLE `dbtest`.`_t1_new` (
  13.   `id` int(11) NOT NULL AUTO_INCREMENT,
  14.   `name` varchar(30) DEFAULT NULL,
  15.   `phone` varchar(15) DEFAULT NULL,
  16.   PRIMARY KEY (`id`)
  17. ) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8
  18. Created new table dbtest._t1_new OK.
  19. Altering new table...
  20. ALTER TABLE `dbtest`.`_t1_new` DROP COLUMN phone
  21. Altered `dbtest`.`_t1_new` OK.
  22. 2017-11-19T22:56:33 Creating triggers...
  23. 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`
  24. 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`)
  25. 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`)
  26. 2017-11-19T22:56:33 Created triggers OK.
  27. 2017-11-19T22:56:33 Copying approximately 1597892 rows...
  28. 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*/
  29. SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
  30. 2017-11-19T22:56:52 Copied rows OK.
  31. 2017-11-19T22:56:52 Analyzing new table...
  32. 2017-11-19T22:56:52 Swapping tables...
  33. RENAME TABLE `dbtest`.`t1` TO `dbtest`.`_t1_old`, `dbtest`.`_t1_new` TO `dbtest`.`t1`
  34. 2017-11-19T22:56:52 Swapped original and new tables OK.
  35. 2017-11-19T22:56:52 Dropping old table...
  36. DROP TABLE IF EXISTS `dbtest`.`_t1_old`
  37. 2017-11-19T22:56:52 Dropped old table `dbtest`.`_t1_old` OK.
  38. 2017-11-19T22:56:52 Dropping triggers...
  39. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;
  40. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;
  41. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;
  42. 2017-11-19T22:56:52 Dropped triggers OK.
  43. 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

點選(此處)摺疊或開啟

  1. No slaves found. See --recursion-method if host BX-128-28 has slaves.
  2. Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
  3. Operation, tries, wait:
  4.   analyze_table, 10, 1
  5.   copy_rows, 10, 0.25
  6.   create_triggers, 10, 1
  7.   drop_triggers, 10, 1
  8.   swap_tables, 10, 1
  9.   update_foreign_keys, 10, 1
  10. Altering `dbtest`.`t1`...
  11. Creating new table...
  12. CREATE TABLE `dbtest`.`_t1_new` (
  13.   `id` int(11) NOT NULL AUTO_INCREMENT,
  14.   `name` varchar(30) DEFAULT NULL,
  15.   PRIMARY KEY (`id`)
  16. ) ENGINE=InnoDB AUTO_INCREMENT=1809061 DEFAULT CHARSET=utf8
  17. Created new table dbtest._t1_new OK.
  18. Altering new table...
  19. ALTER TABLE `dbtest`.`_t1_new` add key(name)
  20. Altered `dbtest`.`_t1_new` OK.
  21. 2017-11-19T23:00:40 Creating triggers...
  22. 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`
  23. 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`)
  24. 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`)
  25. 2017-11-19T23:00:40 Created triggers OK.
  26. 2017-11-19T23:00:40 Copying approximately 1559718 rows...
  27. 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*/
  28. SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`t1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
  29. 2017-11-19T23:01:09 Copied rows OK.
  30. 2017-11-19T23:01:09 Analyzing new table...
  31. 2017-11-19T23:01:09 Swapping tables...
  32. RENAME TABLE `dbtest`.`t1` TO `dbtest`.`_t1_old`, `dbtest`.`_t1_new` TO `dbtest`.`t1`
  33. 2017-11-19T23:01:09 Swapped original and new tables OK.
  34. 2017-11-19T23:01:09 Dropping old table...
  35. DROP TABLE IF EXISTS `dbtest`.`_t1_old`
  36. 2017-11-19T23:01:09 Dropped old table `dbtest`.`_t1_old` OK.
  37. 2017-11-19T23:01:09 Dropping triggers...
  38. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_del`;
  39. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_upd`;
  40. DROP TRIGGER IF EXISTS `dbtest`.`pt_osc_dbtest_t1_ins`;
  41. 2017-11-19T23:01:09 Dropped triggers OK.
  42. 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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27067062/viewspace-2147452/，如需轉載，請註明出處，否則將追究法律責任。

相關文章