[MySQL] pt-online-schema-change of the online ddl Tool,

[MySQL] pt-online-schema-change of the online ddl Tool,

Tm tool Database SQL MySQL MySQL DdlStatus quoDuring the O & M of the mySQL database, we always perform ddl changes to the data table, modify and add fields or indexes. For mysql, ddl is a feature that makes all MySQL DBAs criticized, in MySQL, the ddl operation locks the table. If the table is smaller than, the impact on the front-end is small, if tens of millions of tables are encountered at that time, the front-end application's write operations on the tables will be affected. Currently, the InnoDB engine uses the following steps to perform DDL operations: 1. Create an invisible temporary table (tmp_table) according to the table structure and DDL statements of the original table (original_table) 2. Add write lock to the original table to block all update operations (insert, delete, update, etc.) 3. Execute insert into tmp_table select * from original_table4 rename original_table and tmp_table, and finally drop original_table5 to release write lock. We can see that when InnoDB executes DDL, the original table can only be read and cannot be written. Therefore, perconal has released the pt-online-schema-change tool, which is characterized by no read/write blocking during the modification process.Working principle:If the table has a foreign key, the tool will not execute unless you use -- alter-foreign-keys-method to specify a specific value. 1. Create an empty table with the same structure as the table you want to execute the alter operation. 2. Execute the table structure modification and copy the data from the original table to the table structure after modification. 3. Create a trigger on the original table to update the update operation of the original table to the new table during data copy. note: If the trigger has been defined in the table, this tool cannot work. 4. After the copy is complete, use renameTableThe new Table replaces the original table. By default, the original table is deleted.Usage:Pt-online-schema-change [OPTIONS] DSNoptionsDatabaseAnd tables. Here, we need to introduce two parameters: -- dry-run does not create a trigger, does not copy data, and does not replace the original table. Only create and change a new table. -- The execute parameter serves the same purpose as the previous working principle. A trigger is created to ensure that the latest data changes will affect the new table. Note: If this parameter is not added, the tool will exit after performing some checks.Dependency Condition1. The operation table must have a primary key. Otherwise, the following error is reported. [Root @ rac1 bin] #. /pt-online-schema-change-u root-h 10.250.7.50-p yang -- alter = 'add column vid int' -- execute D = houyi, t = ga Cannot connect to D = houyi, h = 127.0.0.1, p = ..., u = rootCannot chunk the original table 'houyi '. 'gal': There is no goodIndexAnd the table is oversized. at./pt-online-schema-change line 5353.Test example:1 Add the field [root @ rac1 bin] #. /pt-online-schema-change-u root-h 10.250.7.50-p yang -- alter = 'add column vid int' -- execute D = houyi, t = ga Cannot connect to D = houyi, h = 127.0.0.1, p = ..., u = rootOperation, tries, wait: copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering 'houyi '. 'ga '... creating new table... created new table houyi. _ ga_new OK. altering new table... altered 'houyi '. '_ ga_new' OK. creating triggers... created triggers OK. copying approximately 746279 rows... copied rows OK. swapping tables... swapped original and new tables OK. dropping old table... dropped old table 'houyi '. '_ ga_old' OK. dropping triggers... dropped triggers OK. successfully altered 'houyi '. 'ga '.2. Add an index[Root @ rac1 bin] #. /pt-online-schema-change-u root-h 10.250.7.50-p yang -- alter = 'add key indx_vid (vid) '-- execute D = houyi, t = ga3. delete a field[Root @ rac1 bin] #. /pt-online-schema-change-u root-h 10.250.7.50-p yang -- alter = 'drop column vid' -- execute D = houyi, t = ga for detailed tutorials, see: http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html