Pt-online-schema-change

#!/bin/bash# run the script needs to change 2 places: The first is the basic information, the second is--alter# basic information (need to change) user= ' xxx ' password= ' xxx '   host= ' xxx ' port= xxxxd= ' xxx ' t= ' xxx ' #--alter (need change) #--------------Example----------------# 1. Add Field #  pt-online-schema-change --user= $user  --password= $password  --host= $host  --port= $port    --alter  "Add column content text"  d= $D, t= $t  --no-check-replication-filters  --alter-foreign-keys-method=auto --recursion-method=none --print --execute# #  2. Delete field # pt-online-schema-change --user= $user  --password= $password  --host= $host  --port = $port   --alter  "drop column content "  d= $D, t= $t  -- No-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet  --execute# # 3. Modify field # pt-online-schema-change --user= $user  --password= $password  --host= $host  --port= $port   --alter  " Modify column age tinyint not null default 0 " D= $D, t= $t  -- No-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet  --execute## 4. Field renaming # pt-online-schema-change --user= $user  --password= $password  - -host= $host  --port= $port   --alter  "Change column age address varchar ( " d= $D, t= $t  --no-check-alter --no-check-replication-filters --alter-foreign-keys-method =auto --recursion-method=none --quiet --execut## 5. Increasing the index # pt-online-schema-change  --user= $user  --password= $password  --host= $host  --port= $port   --alter  "ADD  index idx_address (address) " d= $D, t= $t  --no-check-alter -- No-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print  --execute## 6. Deleting an index # pt-online-schema-change --user= $user  --password= $password  --host= $host  --port= $port   --alter   "Drop index idx_address"  d= $D, t= $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 the configuration file # 3. --alter#      structure Change statement, do not need alter table keyword. You can specify multiple changes, separated by commas. In the following scenario, it is important to note that:#     cannot rename the table with rename. The        #     column cannot be renamed by deleting it and then adding it, and the data is not copied to the new column. #     If you join a column that is not empty and has no default value, the tool fails. That is, it will not set a default value for you and must display the specified. #     you need to specify a name _constraint_name when you delete a foreign key (Drop foreign key constrain_name), Rather than the original constraint_name. #     such as: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#     requires special handling of tables with foreign key constraints to ensure that they can be applied to new tables. When renaming a table, The foreign key relationship is taken to the renamed table. #     The tool has two methods, you can find the child table automatically, and modify the constraint relationship. #    auto:  Select one of the two processing methods for Rebuild_constraints and Drop_swap. #    #    rebuild_constraints: Use the  alter table statement to first delete the foreign KEY constraint, And then Add. If the child table is large, it can cause long-time blocking. #    drop_swap:  executes foreign_key_checks=0, prohibits foreign key constraints, deletes the original table, and then renames the new table. This is fast and does not clog, but is risky:#                  The table does not exist for a short period of time when the original table is deleted and the new table is renamed, and the program returns an error. #                 If there is an error renaming the table, Can not be rolled back. Because the original table has been deleted. #    none:  is similar to "Drop_swap", but it does not delete the original table, and the foreign key relationship goes above the cousin with the rename. # 5. --[no]check-alter#     default Yes, syntax parsing. With--dry-run  and  --print  run together to check for problems (Change column,drop primary key). ## 6. --max-lag#     default 1s. After each chunk copy is complete, the latency of all replication Slave is reviewed. If the delay is greater than this value, the replication data is paused until all the lag from is less than this value, using Seconds_behind_master. #     If there are any values from the lag beyond this option, the tool will sleep--check-interval the specified time, and then check again. If it is stopped, it will wait forever until it starts synchronizing, and the delay is less than the value. #     If you specify--check-slave-lag, the tool checks only the latency of that server, not all servers. # 7. --check-slave-lag#     specifies a DSN connection address from the library, and if the value set from the--max-lag parameter is exceeded from the library, the operation is paused # #  8. --check-interval#     default is 1. --max-lag Check the sleep time. ## 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 this class. If such a filter is found, the tool will error and exit. #     because if the update exists on the master of the table and does not exist on the 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 replication data has finished renaming, delete the original table. If there is an error, the original table is preserved. # ##------------------------------------------------- 

Change the relevant parameters according to the case and parameter description when using.

This article is from the "Hanchengway" blog, make sure to keep this source http://hanchengway.blog.51cto.com/10974268/1787635

Pt-online-schema-change