Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MySQL modify big table tool pt-online-schema-change principle", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's train of thought slowly in depth, together to study and learn "MySQL modify big table tool pt-online-schema-change principle" bar!

MySQL modifies the usage restrictions of the large table tool pt-online-schema-change:

1) if the modification table has a foreign key, the tool will not execute it unless you specify a specific value using-- alter-foreign-keys-method

2), the modified table must have a primary key, otherwise an error will be reported: Cannot chunk the original table `houyi`.`ga`: There is no good index and the table is oversized. At. / pt-online-schema-change line 5353.

3) there cannot be three triggers for after delete | insert | update on the modified table, otherwise the operation of modifying the table structure fails.

MySQL modifies the principle of the large table tool pt-online-schema-change:

1) first, after connecting to mysql with account password, get the status information of the specified table, check whether there is a trigger, and check whether the table has a primary key.

2) then, according to the table definition of the modified table, create a new temporary table named'_ tb_new' invisible, add alter fields to the table, and verify that the execution is successful.

3), and then create three triggers for the source table, as follows:

Create trigger db_tb_del after delete on db.tb for each row delete ignore from db._tb_new where db._tb_new.id OLD.id # delete the db._tb_new.id OLD.id data in the new table, otherwise ignore the operation

Create trigger db_tb_del after update on db.tb for each row replace into db._tb_new (id,...) Values (new.id,...) # when the source table executes update, the corresponding data replace into is written to the new table

Create trigger db_tb_del after insert on db.tb for each row replace into db._tb_new (id,...) Values (new.id,...) # when the source table performs the insert operation, the corresponding data replace into is written to the new table

4) insert low_priority ignore into db._tb_new (id,..) will be executed after the trigger is created Select id,... The from tb lock in share mode statement copies the source table data to the new table.

5) execute the statement after the copy is completed: rename table db.tb to db._tb_old,db._tb_new to db.tb also modifies the source table to _ tb_old format, and the new table _ tb_new to the atomic modification of the source table name.

6) then, if there is no option to add or not delete the old table, the Old table will be deleted, and then three triggers will be deleted. At this point, the modification of the online table structure is completed. The whole process locks the table only at the time of the rename table, and does not lock the table at other times.

Thank you for your reading, the above is the content of "MySQL modifies the principle of the big table tool pt-online-schema-change". After the study of this article, I believe you have a deeper understanding of the principle of MySQL to modify the large table tool pt-online-schema-change, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.