Change table structure online Pt-onling-schema-change

Pt-online-schema-change is a tool for Perona company, which solves the problem of not locking tables when changing tables, and does not affect the business, which is the principle:

• If there is a foreign key, the foreign key related table is detected according to the value of the Alter-foreign-keys-method parameter, and the corresponding setting is processed.

• Creates a new table with a modified table structure that guides the data from the source data table to the new data table.

• Creates a trigger that, after copying data begins, records data that continues to be modified on the source data table to perform these operations at the end of the data copy to ensure that the data is not lost.

• Copy the data and copy the data from the source data table to the new table.

• Modifies the corresponding child table of the foreign key, modifying the associated child table of the foreign key according to the modified data.

• Change the source data table to the old table, rename the new table as the source table name, and delete the old table.

Test Result: support that form of modification.

1. Add a field to a table without a primary key and the tool will error.

2. You can create a success by adding a primary key to a table that does not have a primary key.

3. There are replicate_do_table=book.t1 from the library, adding fields to the master, error, and not copying the temporary tables generated on the main library to the slave library. An error will be made when renaming the temporary table to the original table name.

4. For tables that have foreign keys, add fields to the parent table and you can create a success.

5. For tables with foreign keys, add fields to the child table and you can create a success.

6. field contains not NULL is not empty, will error, need to increase the default value can be changed successfully.

Change table structure online Pt-onling-schema-change