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

This article mainly introduces how to use pt-online-schema-change in mysql, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Pt-online-schema-change

Restriction condition

1 Foreign key limit, used in conjunction with-alter-foreign-keys-method parameter

2 there must be a primary key

3 there cannot be three after triggers on the table.

Alter-foreign-keys-method description

Two ways (T1 and T2 have foreign keys dependent on T1)

Rebuild_constraints (priority)

It first rebuilds the foreign key through alter table T2 drop fk1 and add _ fk1 to point to the new table

Then exchange the table name with rename T1 and then change the table name without affecting the client

Delete old table t1_old

If the T2 table is so large that the alter operation takes too long, pt-osc may force the choice of another way, drop_swap.

Drop_swap

Disable T2 table foreign key constraint check, set FOREIGN_KEY_CHECKS = 0

Then drop T1 original table rename _ t1_new T1

This approach is fast and does not block requests, but it is risky. The instant process from drop table to rename table. T1 table does not exist. An error will be reported when a request is encountered.

Comparison between pt-osc and online ddl

1 ddl is not suitable for use because of its high cost when it is necessary to copy table.

2 pt-osc cannot be used when there is a trigger

3 modify index,foreign-key,column-name to give priority to ddl, and specify ALGORITHM=INPLACE

Example

1 add a new column

Pt-online-schema-change-- user=user-- password=xxx-- host=ip-- port=33066-- alter "add column col1 VARCHAR (64) NULL COMMENT 'order No.'" DempsyLignedl-- execute-- charset=utf8-- nocheck-replication-filters-- max-load= "Threads_running=20"

2 modify column type

Pt-online-schema-change-- user=user-- password=xxx-- host=ip-- port=3306-- alter "convert to character set utf8mb4 collate utf8mb4_bin" Dumbledb 1 authoring T1-- execute-- nocheck-replication-filters-- charset=utf8-- max-load= "Threads_running=20"

3 add delete index

Pt-online-schema-change-- user=user-- password=xxx-- host=ip-- port=3306-- alter "DROP KEY cid,add key idx_game_id (game_id)" Dempt1 execute-- charset=utf8-- nocheck-replication-filters-- max-load= "Threads_running=20"

4 modify the primary key (assuming that the original primary key is a compound primary key)

The following modification actions are involved

4.1 Delete compound primary key

4.2 add a new self-increasing primary key

4.3 the original compound primary key field, modified to a unique index

Ps: the delete trigger has the greatest impact on modifying the primary key. The primary key field on the new table T2 does not exist on the old table T1, and the new table T2 data cannot be deleted according to the primary key condition. If there is no index or primary key on the relevant columns on the table, then the cost of deletion is very high, so the compound index must be added when deleting the primary key.

-alter "drop primary key,add column id auto_crement primary key,add unique key uk_pk_k (pk,k)"

5 rebuild the table

Pt-online-schema-change-- user=user-- password=xxx-- host=ip-- port=3306-- alter "engine=innodb" DemptDB 1 authoring T1-- execute-- nocheck-replication-filters-- charset=utf8-- max-load= "Threads_running=20"-- max-lag=2s-- chunk-time=1s

Parameter description

Execute: this parameter is used to perform alter operations. If you do not add it, you will only do some security checks and exit. Be sure to add this parameter after you know how to use the tool and have the appropriate backup. When using this parameter, SUPER and REPLICATION SLAVE permissions are required in addition to the permissions required for the object table.

Nocheck-replication-filters:

Max-lag: interrupts the copy of the data until all replication delays are less than this value, default is 1s. After each chunk copy is completed, OSC will go to show salve status to determine all replication through Seconds_Behind_Master. When the replication delay of any related slave is higher than this value, OSC will stop the data copy at the time specified in the-check-interval parameter, and then restart the check until the delay falls below this value.

Charset:

Max-load: default threads_running = 25. After each chunk (what chunk is) is copied, the show global status is checked to see if the metric exceeds the threshold of the specified number of t threads. If so, the data copy is suspended.

Critical-load: default threads_running = 50. After each chunk (what chunk is) is copied, it will check the show global status and check whether the indicator exceeds the threshold of the specified number of t threads, if it exceeds the termination of the pt process.

Chunk-time: the default is 0.5 seconds. The tool calculates the number of rows of data that can be processed within the specified time (i.e. chunks) according to the busy degree of the current system (compared with chunk-size).

Chunk-size: specify the size of the chunk

Charset: specify the character set of the connection, and remember to add it for remote operations, otherwise garbled codes may occur.-- charset=utf8 or Dencrypted db1thecontrol thumbnemainAggutf8

Ps:

Threads_running represents the number of threads in a non-sleeping state.

Reasons for threads_running growth:

1 client connection explosion

2 system performance bottleneck, cpu io memory swap

3 abnormal sql

Thank you for reading this article carefully. I hope the article "how to use pt-online-schema-change in mysql" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.