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

In this issue, the editor will bring you about the parameters of pt-online-schema-change use. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Pt-online-schema-change usage

How it works:

1. If there is a foreign key, detect the table related to the foreign key according to the value of the alter-foreign-keys-method parameter, and do the corresponding setting processing. Without using-- alter-foreign-keys-method=rebuild_constraints to specify a specific value, the tool does not execute

2. Create a table (table_new) with the same structure as the source table, and execute alter to modify the temporary table structure

3. Create three triggers on the original table, the corresponding trigger for insert,delete,udpate (for copy data, update the original table to the new table in the process)

4. In the process of copying data from the original table to the new table, the write operations in the original table will be updated to the new temporary table.

5. Modify the child table related to the foreign key, and modify the child table associated with the foreign key according to the modified data

6. The name source data table is old, take the new table rename as the source indication, and delete the old table

7. Delete trigger

Execution conditions:

1. The operating table must have a primary key or a unique index, otherwise an error will be reported

2. The table cannot define triggers, otherwise an error will be reported

Usage introduction:

Pt-online-schema-change-- host=ip-- port=3306-- user=username-- password='password' dumbtablelighting name-- alter= "modify order_id bigint (20) COMMENT 'order id';"-- critical-load= "Threads_running=200"-- sleep=1-- charset=utf8mb4-- check-slave-lag= "192.168.1.2192.168.1.3"-- check-interval=1-- execute

-- dry-run creates and modifies a new table, but does not create triggers, copy the table, or replace the original table, mutually exclusive with-- execute

The function of the parameter execute is the same as described earlier in how it works, creating triggers to ensure that the most recently changed data will affect the new table. Note: if this parameter is not added, the tool will exit after performing some checks

-- critical-load will count the changes of the specified state quantity before and after each chunk operation according to show global status. The default is statistical Thread_running. The goal is to protect against excessive load caused by triggers on the original table. This is also to prevent the impact of online DDL on the online. If you exceed the set threshold, the operation will be terminated and the online DDL will be interrupted. An exception prompted, such as reporting a wrong message.

-- run SET NAMES UTF8 after charset=utf8 connects to MySQL

-- check-slave-lag checks master-slave delay

-- check-replication-filters checks whether the filter condition is set in the replication. If so, the program will exit.

-- nocheck-replication-filters does not check whether filtering conditions are set in replication

-- set-vars sets the variable value of mysql

-- sleep sleep for a while after each chunk import and before the start of the next chunk import. The longer the sleep time, the smaller the impact on the disk IO.

-- [no] drop-old-table rename the old table after the new table, and you can no-xxx to keep the old table.

-- [no] drop-new-table deletes the new table if it fails to copy the original table; you can also no-xxx to keep the new table

-- the number of rows of chunk-size chunk. Default is 1000.

-- specify the index column when chunk-index-columns has a composite index

-- critical-load default Threads_running=50; automatically checks the load with SHOW GLOBAL STATUS after each chunk execution, and abandons it if the threshold is exceeded

-- execute executes operation and-- dry-run mutually exclusive

-- force is forced to run, which may break the foreign key constraint

-- when skip-check-slave-lag checks SLAVE, specify that SLAVE skip

-- print will display the commands executed by the tool

-- null-to-not-null modification allows null value to be not null

-- preserve-triggers retains the trigger of the original table and does not delete it

-- max-lag defaults to 1s. If the master-slave delay exceeds this value, the replication pauses the "--check-interval" second time; then it checks until the master-slave delay is less than this value; if "--check-slave-lag" is specified, only the specified slave delay is checked instead of all slave;. If there is any SLAVE stop, the tool will wait forever; the report will be printed every time it stops.

-- A password is required when ask-pass connects

These are the pt-online-schema-change usage parameters that Xiaobian shares with you. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.