[MySQL] 表線上重定義

MySQL不像Oracle本身就支援表的線上重定義，但我們可以通過開源軟體percona-toolkit中的工具pt-online-schema-change進行線上重定義。

官方文檔：http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#pt-online-schema-change

pt-online-schema-change包含在percona-toolkit中，所以我們得先下載安裝：

wget percona.com/get/percona-toolkit.tar.gztar -zxvf percona-toolkit-2.2.6.tar.gz

通過上訴步驟之後，把./percona-toolkit-2.2.6/bin下的可執行檔加入到$PATH中即可。

好了，安裝完成之後，就可以利用該工具進行線上重定義了。主要用到兩個參數：

--dry-runCreate and alter the new table, but do not create triggers, copy data, or replace the original table.--executeIndicate that you have read the documentation and want to alter the table. You must specify this option to alter the table. If you do not, then the tool will only perform some safety checks and exit. This helps ensure that you have read the documentation and understand how to use this tool. If you have not read the documentation, then do not specify this option.

首先，我們用--dry-run驗證是否可以執行修改：

pt-online-schema-change --alter "modify treatment_afterday INT(4) NULL" D=portal,t=comment_expert -uroot -p*** --dry-runOperation, tries, wait:  copy_rows, 10, 0.25  create_triggers, 10, 1  drop_triggers, 10, 1  swap_tables, 10, 1  update_foreign_keys, 10, 1Starting a dry run.  `portal`.`comment_expert` will not be altered.  Specify --execute instead of --dry-run to alter the table.Creating new table...Created new table portal._comment_expert_new OK.Altering new table...Altered `portal`.`_comment_expert_new` OK.Not creating triggers because this is a dry run.Not copying rows because this is a dry run.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.2014-01-14T13:59:08 Dropping new table...2014-01-14T13:59:08 Dropped new table OK.

確認無誤之後，再用--execute真正執行：

pt-online-schema-change --alter "modify treatment_afterday INT(4) NULL" D=portal,t=comment_expert -uroot -pzhujie1986 --executeFound 1 slaves:  lx203Will check slave lag on:  lx203Operation, tries, wait:  copy_rows, 10, 0.25  create_triggers, 10, 1  drop_triggers, 10, 1  swap_tables, 10, 1  update_foreign_keys, 10, 1Altering `portal`.`comment_expert`...Creating new table...Created new table portal._comment_expert_new OK.Altering new table...Altered `portal`.`_comment_expert_new` OK.2014-01-14T13:59:20 Creating triggers...2014-01-14T13:59:20 Created triggers OK.2014-01-14T13:59:20 Copying approximately 1165430 rows...Copying `portal`.`comment_expert`:  19% 02:03 remainCopying `portal`.`comment_expert`:  38% 01:37 remainCopying `portal`.`comment_expert`:  55% 01:12 remainCopying `portal`.`comment_expert`:  73% 00:44 remainCopying `portal`.`comment_expert`:  90% 00:15 remain2014-01-14T14:02:08 Copied rows OK.2014-01-14T14:02:08 Swapping tables...2014-01-14T14:02:08 Swapped original and new tables OK.2014-01-14T14:02:08 Dropping old table...2014-01-14T14:02:10 Dropped old table `portal`.`_comment_expert_old` OK.2014-01-14T14:02:10 Dropping triggers...2014-01-14T14:02:10 Dropped triggers OK.Successfully altered `portal`.`comment_expert`.

除了修改表結構之外，在大表上建索引也可以利用這種方法防止鎖表。