MySQL's online table structure is modified because of inefficient and blocking read and write. has been criticized. As for the principle of alter TABLE, see my previous article. MySQL modifies the large table structure online. After reading, found that the problem is still locked, and for the online update of this block is not very good processing, so can only be understood theoretically, applied to the online library or there is a problem. The latest data is not guaranteed.
Today we see the same online-schema-change that Percona launched in the toolkit that Facebook launched. The approximate principle is the same. How to install the document using detailed crossing Web. http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html Note that the documentation is very clear. It is important to note the use of two parameters. The difference between--dry-run and--execute is used. Establish a process for viewing changes using--dry-run and--print first. OK after executing--execute. The reference website is as follows:
--dry-run
-
Create and alter the new table, but does not create triggers, copy data, or replace the original table.
- --execute
-
Indicate that's you There are read the documentation and want to alter the table. You must specify this option to alter the table. If you don't, then the tool would only perform some safety checks and exit. This helps ensure, which has the read the documentation and understand how-to use this tool. If you had not read the documentation and then did not specify this option.
The following is the process of performing--dry-run runs yourself. Because it is in the test library, no new records appear. But the execution process can still be seen. Password and host are blocked.
[email protected]:/home/hzwuzhimin# pt-online-schema-change--user=root--password=***--host= ' * * * * -- lock-wait-time=120 --alter= "add column DD int (one) default 0" d=wzm,t=tmp20121127_user_text_monitor_ config,f=/home/hzwuzhimin/mysql/my.cnf,s=/home/hzwuzhimin/mysql/node/mysqld.sock,p=4333 --dry-run-- Printstarting a dry run. ' Wzm '. ' Tmp20121127_user_text_monitor_config ' won't be altered. specify--execute instead of--dry-run to alter the table. Creating New Table ... CREATE TABLE ' wzm '. ' _tmp20121127_user_text_monitor_config_new ' ( ' id ' bigint () not NULL auto_increment, ' Xml_content ' longtext not null, ' group_name ' varchar (+) not NULL DEFAULT ', ' user_account ' varchar (max) not N ULL DEFAULT ', ' Create_time ' bigint () not null, ' Modify_time ' bigint (a) not null, ' Modify_user_acco UNT ' varchar (+) NOT null default ', ' V ' bigint (a) NOT null default ' 1 ', ' AA ' int (one) ' default ' 0 ', ' BB ' int (one) default ' 0 ', ' cc ' int (one) default ' 0 ', PRIMARY KEY (' id ')) engine=myisam auto_increment=244 Defau LT charset=gbkcreated new Table Wzm._tmp20121127_user_text_monitor_config_new OK. Altering new Table ... ALTER TABLE ' wzm '. ' _tmp20121127_user_text_monitor_config_new ' add column DD int (one) default 0Altered ' Wzm '. ' _ Tmp20121127_user_text_monitor_config_new ' OK. Not creating triggers because the is a dry run. CREATE TRIGGER ' Pt_osc_wzm_tmp20121127_user_text_monitor_config_del ' after DELETE on ' Wzm '. ' Tmp20121127_user_text_ Monitor_config ' For each ROW DELETE IGNORE from ' wzm '. ' _tmp20121127_user_text_monitor_config_new ' WHERE ' wzm '. ' _ Tmp20121127_user_text_monitor_config_new '. ' id ' <=> old ' id ' CREATE TRIGGER ' Pt_osc_wzm_tmp20121127_user_text_ Monitor_config_upd ' after UPDATE in ' Wzm '. ' Tmp20121127_user_text_monitor_config ' for each ROW REPLACE into ' wzm '. ' _ Tmp20121127_user_text_monitor_config_new ' (' id ', ' xml_content ', ' group_name ', ' user_account ', ' create_time ', ' Modify _Time ', ' modify_user_account ', ' V ', ' aa ', ' BB ', ' cc ') VALUES (new ' id ', new ' xml_content ', new ' group_name ', new ' user_ Account ', new ' create_time ', new ' modify_time ', new ' modify_user_account ', new ' V ', new ' AA ', new ' BB ', new ' CC ' CREATE TRIGGER ' Pt_osc_wzm_tmp20121127_user_text_monitor_config_ins ' after INSERT on ' Wzm '. ' Tmp20121127_user_text_ Monitor_config ' for each ROW REPLACE into ' wzm '. ' _tmp20121127_user_text_monitor_config_new ' (' id ', ' xml_content ', ' Group_name ', ' user_account ', ' create_time ', ' modify_time ', ' modify_user_account ', ' V ', ' aa ', ' BB ', ' cc ') VALUES (NEW ' ID ', new ' xml_content ', new ' group_name ', new ' user_account ', new ' create_time ', new ' modify_time ', new ' modify_user_ ' Account ', new ' V ', new ' AA ', new ' BB ', new ' CC ') not copying rows because the is a dry run. insert low_priority Ignor E into ' wzm '. ' _tmp20121127_user_text_monitor_config_new ' (' id ', ' xml_content ', ' group_name ', ' user_account ', ' Create_ ' Time ', ' modify_time ', ' modify_user_account ', ' V ', ' aa ', ' BB ', ' CC 'SELECT ' id ', ' xml_content ', ' group_name ', ' user_account ', ' create_time ', ' modify_time ', ' modify_user_account ', ' V ', ' aa ', ' BB ', ' cc ' From ' wzm '. ' Tmp20121127_user_text_monitor_config ' LOCK in SHARE MODE/*pt-online-schema-change 7498 copy t Able*/not swapping tables Because this is a dry run. Not dropping the old table because this is a dry run. Not dropping triggers because the is a dry run. DROP TRIGGER if EXISTS ' wzm '. ' Pt_osc_wzm_tmp20121127_user_text_monitor_config_del ';D rop TRIGGER if EXISTS ' wzm '. ' Pt_ Osc_wzm_tmp20121127_user_text_monitor_config_upd ';D rop TRIGGER IF EXISTS ' wzm '. ' Pt_osc_wzm_tmp20121127_user_text_ Monitor_config_ins ';D ropping new Table ... DROP table IF EXISTS ' wzm '. ' _tmp20121127_user_text_monitor_config_new ';D ropped new table OK. Dry run complete. ' Wzm '. ' Tmp20121127_user_text_monitor_config ' is not altered.
the procedure after--execute is executed as follows [email protected]:/home/hzwuzhimin# pt-online-schema-change--user=root--password=***--host= ' * * * * * * * -- lock-wait-time=120 --alter= "add column DD int (one) default 0" d=wzm,t=tmp20121127_user_text_monitor_ config,f=/home/hzwuzhimin/mysql/my.cnf,s=/home/hzwuzhimin/mysql/node/mysqld.sock,p=4333--execute-- Printaltering ' Wzm '. ' Tmp20121127_user_text_monitor_config ' ... Creating New Table ... CREATE TABLE ' wzm '. ' _tmp20121127_user_text_monitor_config_new ' ( ' id ' bigint () not NULL auto_increment, ' Xml_content ' longtext not null, ' group_name ' varchar (+) not NULL DEFAULT ', ' user_account ' varchar (max) not N ULL DEFAULT ', ' Create_time ' bigint () not null, ' Modify_time ' bigint (a) not null, ' Modify_user_acco UNT ' varchar (+) NOT null default ', ' V ' bigint (a) NOT null default ' 1 ', ' AA ' int (one) ' default ' 0 ', ' bb ' Int (one) default ' 0 ', ' cc ' int (one) default ' 0 ', PRIMARY KEY (' id ')) Engine=myisam auto_increment=244 DEFAULT charset=gbkcreated new Table Wzm._tmp20121127_user_text_monitor_config_new OK. Altering new Table ... ALTER TABLE ' wzm '. ' _tmp20121127_user_text_monitor_config_new ' add column DD int (one) default 0Altered ' Wzm '. ' _ Tmp20121127_user_text_monitor_config_new ' OK. Creating triggers ... CREATE TRIGGER ' Pt_osc_wzm_tmp20121127_user_text_monitor_config_del ' after DELETE on ' Wzm '. ' Tmp20121127_user_text_ Monitor_config ' For each ROW DELETE IGNORE from ' wzm '. ' _tmp20121127_user_text_monitor_config_new ' WHERE ' wzm '. ' _ Tmp20121127_user_text_monitor_config_new '. ' id ' <=> old ' id ' CREATE TRIGGER ' Pt_osc_wzm_tmp20121127_user_text_ Monitor_config_upd ' after UPDATE in ' Wzm '. ' Tmp20121127_user_text_monitor_config ' for each ROW REPLACE into ' wzm '. ' _ Tmp20121127_user_text_monitor_config_new ' (' id ', ' xml_content ', ' group_name ', ' user_account ', ' create_time ', ' Modify _time ', ' modify_user_account ', ' V ', ' aa ', ' BB ', ' cc ') VALUES (new ' id ', new ' xml_content ', new ' group_name ', new ' user_ Account ', NEW. 'Create_time ', new ' modify_time ', new ' modify_user_account ', new ' V ', new ' AA ', new ' BB ', new ' cc ', CREATE TRIGGER ' Pt_ Osc_wzm_tmp20121127_user_text_monitor_config_ins ' after INSERT in ' Wzm '. ' Tmp20121127_user_text_monitor_config ' for Each ROW replaces into ' wzm '. ' _tmp20121127_user_text_monitor_config_new ' (' id ', ' xml_content ', ' group_name ', ' user_ Account ', ' create_time ', ' modify_time ', ' modify_user_account ', ' V ', ' aa ', ' BB ', ' cc ') VALUES (new ' id ', new ' xml_content ', new ' group_name ', new ' user_account ', new ' create_time ', new ' modify_time ', new ' modify_user_account ', new ' V ', New ' AA ', new ' BB ', new ' CC ') Created triggers OK. Copying approximately 123 rows ... INSERT low_priority IGNORE into ' wzm '. ' _tmp20121127_user_text_monitor_config_new ' (' id ', ' xml_content ', ' group_name ', ' User_account ', ' create_time ', ' modify_time ', ' modify_user_account ', ' V ', ' aa ', ' BB ', ' cc ') SELECT ' id ', ' xml_content ', ' Group_name ', ' user_account ', ' create_time ', ' modify_time ', ' modify_user_account ', ' V ', ' aa ', ' BB ', ' CC ' from ' wzm '. ' Tmp20121127_user_text_monitor_config ' LOCK in SHARE MODE/*pt-online-schema-change 8270 copy Table*/cop IED rows OK. Swapping tables ... RENAME TABLE ' wzm '. ' Tmp20121127_user_text_monitor_config ' to ' wzm '. ' _tmp20121127_user_text_monitor_config_old ', ' Wzm '. ' _tmp20121127_user_text_monitor_config_new ' to ' wzm '. ' Tmp20121127_user_text_monitor_config ' Swapped original and new tables OK. Dropping old table ... DROP TABLE IF EXISTS ' wzm '. ' _tmp20121127_user_text_monitor_config_old ' Dropped old TABLE ' Wzm '. ' _tmp20121127_user_text _monitor_config_old ' OK. Dropping triggers ... DROP TRIGGER if EXISTS ' wzm '. ' Pt_osc_wzm_tmp20121127_user_text_monitor_config_del ';D rop TRIGGER if EXISTS ' wzm '. ' Pt_ Osc_wzm_tmp20121127_user_text_monitor_config_upd ';D rop TRIGGER IF EXISTS ' wzm '. ' Pt_osc_wzm_tmp20121127_user_text_ Monitor_config_ins ';D ropped triggers OK. Successfully altered ' wzm '. ' Tmp20121127_user_text_monitor_config ' . summary: actually through the print out of the log. We can clearly see the whole process of online updates. 1: Create a table structure that is the same as the table structure that needs to be modified. Named Old table name _new;2: According toAlter condition modifies the table result after the new table structure is required; 3: Creates a trigger for modifying the association of tables and new tables, for insert,update,delete operations. Ensure that two table records are synchronized. (Because the online update is not blocking the user to read and write to the modified table); 4: Insert the record into the new table according to the trigger condition until the copy is complete; 5: Rename the table, first the Modified Table tab name is named Tab_old, tab_new the same names as tab; 6: Then delete tab_ Old table. After success, delete the trigger. Execution is complete.
Percona Toolkit Pt-online-schema-change Introduction
