Shulou(Shulou.com)06/01 Report--

The DDL operations of MySQL large fields, such as adding and subtracting fields, indexing, modifying field properties, etc., are very time-consuming and labor-consuming before 5.1, especially the impact on MySQL services. With the advent of Plugin Innodb, online indexing has improved a lot since 5. 1, but it will also affect (time is shortened), mainly due to the emergence of MDL locks. However, 5.6 can avoid the above situation, but most of the versions currently in use are pre-5.6.

How it works:

Imitate the alter of MySQL, but the difference is that the table is not locked when the alter operation changes the table structure, that is, the write and read operations are not blocked when the alter is executed, and the client can continue to both and modify the data. Note that a backup must be made when executing this tool, and it is best to read the official documents in detail before doing so.

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 to specify a specific value, the tool does not execute

2. Create a new table with a modified data table for importing data from the source table to the new table.

3. Create a trigger to record the operations that continue to modify the source data table after copying the data, and to perform these operations after the end of the data copy to ensure that the data will not be lost. If the trigger is already defined in the table, the tool will not work.

4. Copy the data from the source data table to the new 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 the old table, the new table rename is the source table name, and the old table is deleted.

7. Delete trigger.

# introduction to key parameters. Some parameters can be used according to the actual situation and in line with your own scenario.

-- user:

-u, the user name of the connection

-- password:

-p, the password for the connection

-- database:

-D, connected database

-- port

-P, the port to connect to the database

-- host:

-h, the host address of the connection

-- socket:

-S, connected socket file

-- ask-pass

Implicitly enter the password to connect to the MySQL

-- charset

Specify the modified character set

-- defaults-file

-F, read the configuration file

-- alter:

The structure change statement does not require the alter table keyword. You can specify multiple changes, separated by commas. You should pay attention to the following scenarios:

You cannot use RENAME to rename a table.

Columns cannot be renamed by deleting and then adding, and the data is not 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 you delete 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"

-- alter-foreign-keys-method

How do I reference a foreign key to a new table? Tables with foreign key constraints need to be specially handled 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 there are risks:

1, within a short time of deleting the original table and renaming the new table, the table does not exist and the program returns an error.

2, 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.

[no] check-alter

Default yes, syntax parsing. Work with-- dry-run and-- print to check for problems (change column,drop primary key).

-- max-lag

The default is 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 for latency on that server, not on all servers.

-- check-slave-lag

Specify a DSN connection address for the slave library, and if the slave library exceeds the value set by the-- max-lag parameter, the operation is paused.

-- recursion-method

The default is show processlist, and the method found from can also be host, but you need to specify report_host on the slave and find it through show slave hosts. You can specify none not to check Slave.

METHOD USES

= =

Processlist SHOW PROCESSLIST

Hosts SHOW SLAVE HOSTS

Dsn=DSN DSNs from a table

None Do not find slaves

Specify none to indicate that you don't care about the delay from.

-- check-interval

The default is 1. Sleep time checked by max-lag.

[no] check-plan

Default yes. Check the security of the query execution plan.

[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 the updated table Master exists, but not on Slave, it will cause the replication to fail. Use the-no-check-replication-filters option to disable the check.

[no] swap-tables

Default yes. Exchange the original table with the new table, unless you disable-- [no] drop-old-table.

[no] drop-triggers

The default yes deletes the trigger on the original table. -- no-drop-triggers will force it on-- no-drop-old-table: if you don't delete the trigger, you will force the original table not to be deleted.

-- new-table-name

Copy the name of the new table created, default% T_new.

[no] drop-new-table

Default yes. Delete the new table if replication of the organization table fails.

[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.

-- max-load

The default is Threads_running=25. After each chunk is copied, the contents of the SHOW GLOBAL STATUS are checked to see if the metric exceeds the specified threshold. If it exceeds, pause it first. You can specify multiple conditions separated by commas, each in the format of status metric = MAX_VALUE or status metric: MAX_VALUE. If you do not specify MAX_VALUE, the tool will only make it 120% of the current value.

-- critical-load

The default is Threads_running=50. The usage is basically similar to-- max-load, if you do not specify MAX_VALUE, then the tool will only this is 200% of the current value. If the specified value is exceeded, the tool exits directly instead of pausing.

-- default-engine

By default, the new table is the same storage engine as the original table, so if the original table uses InnoDB, then the new table will use InnoDB. In some cases where replication is involved, it is likely that the storage engine of the master and slave is different. Using this option defaults to the default storage engine.

-- set-vars

Set the MySQL variable, multiple separated by commas. The default setting for this tool is: wait_timeout=10000 innodb_lock_wait_timeout=1 lock_wait_timeout=60

-- chunk-size-limit

When the block that needs to be copied is much larger than the set chunk-size size, it is not copied. The default value is 4.0, and the block size of a table without a primary key or unique index is uncertain.

-- chunk-time

During the time chunk-time is executed, dynamically resize chunk-size to accommodate changes in server performance. Setting this parameter to 0, or specifying chunk-size, can disable dynamic adjustment.

-- chunk-size

Specify the size of the block. The default is 1000 lines. You can add the suffix k ~ M ~ G. Try to match the size of this block to-- chunk-time. If you specify this option explicitly, each block will specify the size of the number of rows.

[no] check-plan

Default yes. For safety, check the execution plan of the query. By default, the tool will EXPLAIN before executing the query to get a small amount of data, if it is a bad EXPLAIN, then it will get a large amount of data, the tool will execute EXPALIN many times, and if the EXPLAIN results are different, then the query will be considered insecure.

-- statistics

Print out the number of internal events and you can see the number of replicated data inserts.

-- dry-run

Create and modify new tables, but do not create triggers, copy data, and replace the original table. It is not really executed, you can see the generated execution statement and understand its execution steps and details. -- dry-run and-- execute must specify one, and they are mutually exclusive. It works best with-- print.

-- execute

If you are sure to modify the table, specify this parameter. Really carry out. -- dry-run and-- execute must specify one, and they are mutually exclusive.

-- print

Print SQL statements to standard output. Specifying this option allows you to see the statements executed by the tool, which works best with-- dry-run.

-- progress

Print a progress report when copying data, which consists of two parts: the first part is the percentage and the second part is the time.

-- quiet

-Q, do not output the information standard.

Practice process: (here we need to pay attention to the spaces between fields, there can be only one space, if there is more, there will be problems.)

# # check whether there is a problem. For more information, please see the explanation of dry-run.

[root@mysql-server01 dbbackup] # pt-online-schema-change-u dlan-h 172.16.10.60-p root123-P 3306-- alter= "add column route_data_storage_mode1 tinyint (3) UNSIGNED DEFAULT 0 after travel_route" detail detail-charset=utf8-no-version-check-no-drop-old-table-statistics-critical-load= "Threads_running:200"-max-load= "Threads_running=25"-print-dry-run

Execution effect:

# # add a time in front of the execution process to count the final execution time

[root@mysql-server01 dbbackup] # time pt-online-schema-change-u dlan-h 172.16.10.60-p root123-P 3306-- alter= "add column route_data_storage_mode1 tinyint (3) UNSIGNED DEFAULT 0 after travel_route" detail detail-charset=utf8-no-version-check-no-drop-old-table-statistics-critical-load= "Threads_running:200"-max-load= "Threads_running=25"-print-execute

Execution effect:

# # when using PT-OSC:

1. It is not recommended to abort abruptly in the middle of execution, which will result in data exception because triggers will not be deleted.

2. Only single transactions can be executed because of the triggers used

3. If you start a transaction, it will lead to a delay in execution

4. It is very slow when the execution reaches 99%. You can check where the data is loaded through show full processlist. The id value is found in the details.

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.