[MySQL] table online redefinition-pt-online-schema-change_MySQL

[MySQL] online table redefinition-pt-online-schema-change bitsCN.com

MySQL does not support online table redefinition like Oracle itself, but we can use the open-source software percona-toolkit tool pt-online-schema-change for online redefinition.

Official documents: http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#pt-online-schema-change

Pt-online-schema-change is included in percona-toolkit, so we need to download and install it first:

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

Add executable files under./percona-toolkit-2.2.6/bin to $ PATH after appeal steps.

Now, after the installation is complete, you can use this tool for online redefinition. Two parameters are used:

--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.

First, we use -- dry-run to verify whether the modification can be performed:

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.

After confirmation is correct, run -- execute to 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`.

In addition to modifying the table structure, you can also use this method to create indexes on large tables to prevent table locking. BitsCN.com