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

This article mainly introduces the example analysis of pt-online-schema-change in the database, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

#! / bin/bash# to run the script needs to change two places: the first is basic information; the second is-- alter# basic information (need to change) user='xxx'password='xxx' host='xxx'port=xxxxD='xxx't='xxx'#--alter (need to change) #-example-# 1. Add the field # pt-online-schema-change-- user=$user-- password=$password-- host=$host-- port=$port-- alter "ADD COLUMN content text" DeliverDrect alter-foreign-keys-method=auto-- alter-foreign-keys-method=auto-- recursion-method=none-- print-- execute# # 2. Delete the field # pt-online-schema-change-- user=$user-- password=$password-- host=$host-- port=$port-- alter "DROP COLUMN content" DeliverDrect alter-foreign-keys-method=auto-- alter-foreign-keys-method=auto-- recursion-method=none-- quiet-- execute# # 3. Modify the field # pt-online-schema-change-- user=$user-- password=$password-- host=$host-- port=$port-- alter "MODIFY COLUMN age TINYINT NOT NULL DEFAULT 0" DeliverDrect alter-foreign-keys-method=auto-- recursion-method=none-- quiet-- execute## 4. Rename the field # pt-online-schema-change-- user=$user-- password=$password-- host=$host-- port=$port-- alter "CHANGE COLUMN age address varchar (30)"-- no-check-alter-- no-check-replication-filters-- alter-foreign-keys-method=auto-- quiet-- execut## 5. Add the index # pt-online-schema-change-- user=$user-- password=$password-- host=$host-- port=$port-- alter "ADD INDEX idx_address (address)" Domenclature, no-check-alter-- no-check-replication-filters-- alter-foreign-keys-method=auto-- recursion-method=none-- print-- execute## 6. Delete index # pt-online-schema-change-- user=$user-- password=$password-- host=$host-- port=$port-- alter "DROP INDEX idx_address" deleted Defaults-file# _ read _ t-- no-check-alter-- no-check-replication-filters-- alter-foreign-keys-method=auto-- recursion-method=none-- print-- execute##- parameter description-# 1.-- ask-pass# implicitly enter the password to connect to MySQL # 2.-- defaults-file#-F read configuration file # 3.-- alter# structure change statement The alter table keyword is not required. You can specify multiple changes, separated by commas. Note the following scenarios: # you cannot use RENAME to rename a table. # columns cannot be renamed by deleting and then adding, and the data will not be copied to the new column. # if the added column is not empty and there is no default value, the tool will fail. That is, it will not set a default value for you, it must display the specified value. # when deleting a foreign key (drop foreign key constrain_name), you need to specify the name _ constraint_name instead of the original constraint_name. # for example: CONSTRAINT `fk_ Foo` FOREIGN KEY (`foo_ id`) REFERENCES `bar` (`foo_ id`), you need to specify:-- alter "DROP FOREIGN KEY _ fk_foo" # 4.-- alter-foreign-keys-method# needs to specially handle tables with foreign key constraints to ensure that they can be applied to new tables. When you rename a table, the foreign key relationship is brought to the renamed table. The tool has two ways to automatically find child tables and modify constraint relationships. # auto: choose one of the two processing methods: rebuild_constraints or drop_swap. # # rebuild_constraints: use the ALTER TABLE statement to delete the foreign key constraint before adding it. If the child table is large, it can cause a long period of blocking. # drop_swap: execute FOREIGN_KEY_CHECKS=0, disable foreign key constraints, delete the original table, and then rename the new table. This approach is fast and does not cause blocking, but it is risky: # within a short period of time between deleting the original table and renaming the new table, the table does not exist and the program returns an error. # if there is an error in the renamed table, it cannot be rolled back. Because the original table has been deleted. # none: similar to "drop_swap" treatment, but it does not delete the original table, and the foreign key relationship is transferred to the old table with the rename. # 5.-- [no] check-alter# default yes, syntax parsing. Work with-- dry-run and-- print to check for problems (change column,drop primary key). # # 6.-- max-lag# defaults to 1s. "after each chunk copy is completed, the latency of all replication Slave is checked." If the delay is greater than this value, the data replication is paused until the lag of all slaves is less than this value, using Seconds_Behind_Master. # if any slave lag exceeds the value of this option, the tool will sleep-the time specified by check-interval, and then check. If it is stopped, it will wait forever until it starts to synchronize, and the delay is less than this value. # if you specify-- check-slave-lag, the tool only checks the latency of that server, not all servers. # 7.-- check-slave-lag# specifies a DSN connection address of the slave library. If the slave library exceeds the value set by the-- max-lag parameter, the operation # # 8.-- check-interval# defaults to 1. Sleep time checked by max-lag. # # 9.-- [no] check-replication-filters# default yes. If the tool detects any replication-related filtering in the server options, such as specifying binlog_ignore_db and replicate_do_db. If such a filter is found, the tool will report an error and exit. # because if it exists on the updated table Master but not on Slave, it will cause replication to fail. Use the-no-check-replication-filters option to disable the check. # 10.-- [no] drop-old-table# default yes. After the copied data has been renamed, delete the original table. If there is an error, the original table will be retained. #--

When in use, you can change the relevant parameters according to the case and parameter description.

Thank you for reading this article carefully. I hope the article "sample Analysis of pt-online-schema-change in Database" shared by the editor will be helpful to you. At the same time, I also hope that 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.