Pt-online-schema-change interpretation

"Use" online change chart

"Note the risk" because it involves modifying the table's data and structure, so be careful to test and backup before use, the tool will not change the table by default, unless you add the--execute parameter

"Introduction to Tools"

Pt-osc imitate MySQL internal change table way to change the table, but the whole process of the table is through the copy of the original table, that is, in the process of changing the table is not locked the original table, does not affect the read and write operation of the table.

First, the OSC creates a new table with the same data as the original table and modifies the table structure as required, and then copies the data from the original table to the new table in chunk size, and when the copy is complete, the original table and the new table name are modified automatically and the original table is deleted by default

There are two issues to note in this process:

1. Triggers

Because the entire process is online, in order to change the table in the process of updating the original table updates to the new table, the corresponding trigger will be created, whenever the deletion of the original table and add and change operations, will trigger the corresponding operation of the new table. So there can be no other trigger on the original table, that is, if there is a trigger on the original table, the OSC will strike

2. Foreign key

Foreign keys make the table operation more complex, if the original table has foreign keys, automatic rename of the original table and the operation of the new table will not be smooth, you have to copy the data after the completion of the foreign keys to the new table, the tool has two methods to support this operation, Specific later Parameters section (--alter-foreign-keys-method) Introduction

"Use Example"

Pt-online-schema-change [OPTIONS] DSN adds a column: Pt-online-schema-change--alter "add column C1 INT" d=sakila,t= Actoroptimize table is caused by a large load, you can use the OSC to perform: Pt-online-schema-change--alter "Engine=innodb" D=sakila,t=actor

" Security Assurance Measures "

1. By default, if a replication filter is detected, the change table will be rejected, and the parameter is--[no]check-replication-filters

2. Default if master-slave replication delay is detected, the data copy is automatically stopped and the tuning parameter is--max-lag

3. Default if server load is detected to stop or interrupt operation, the tuning parameters are--max-load and--critical-load

4. The lock wait timeout of 1s is set by default to avoid interfering with other transactions, and the tuning parameter is--lock-wait-timeout

5. By default, if a foreign key violation is detected, the table will be rejected and the adjustment parameter is--alter-foreign-keys-method

6. The tool cannot alter the MYISAM table in the PXC (Percona XtraDB Cluster) cluster

" support for PXC. "

Support pxc5.5.28-23.7 or newer version, but there are two restrictions, if you do not meet any of the conditions will be an error exit:

1. Only support InnoDB table change table

2. Wsrep_osu_method must be set to TOI (total order isolation)

" parameter Details "

1.--alter

Used to specify the action to change the table, you can specify multiple actions at the same time, apply the following:

Pt-online-schema-change--alter "ADD COLUMN C1 INT" D=sakila,t=actor

Attention:

(1) Change the table name is not available rename (do not use OSC to change the table name, direct rename how good)

(2) When you modify a column name, you cannot do so by drop the column and then re-add the column, which results in the inability to copy the data from the original column.

(3) If the Add column does not have a default value and is set to ' NOT NULL ', the tool will refuse to execute because the tool will not help you guess a default value.

(4) Drop foreign key, foreign key name before the need to add ' _ ' instead of just a foreign key name, such as the drop below this foreign key

CONSTRAINT 'fk_foo' FOREIGN KEY (' foo_id ') REFERENCES ' bar ' (' foo_id ')

This must be specified:

_fk_foo"

(5) When MySQL version is 5.0, it is important to note that the tool cannot use lock in SHARE MODE, which can cause a replication error as follows:

Query caused different errors on master and slave. Error on master: ' Deadlock found if trying to get lock; Try restarting transaction ' (1213), Error on slave: ' No Error ' (0). Default database: ' Pt_osc '. Query: ' INSERT into pt_osc.t (ID, c) VALUES (' 730 ', ' new Row ') '

Typically occurs when converting a MyISAM table to a InnoDB table because the MyISAM table does not support transactions and InnoDB table support. 5.1 and newer versions do not have this issue

2.--alter-foreign-keys-method

The foreign key must be continuously linked to the correct table before and after the table, and when the tool rename the original table and replaces the original table with the new table, the foreign key has to be updated correctly to the new table, and the foreign key in the original table is no longer in effect

There are two ways to achieve this, with four specific parameters:

(1) Auto

Automatically decide which method to use, if you can use Rebuild_constraints, if you can not use Drop_swap

(2) Rebuild_constraints

This method uses ALTER TABLE to drop and re-add the foreign key that links the new table. This method is generally used unless the associated child table is too large to take a long time to alter the process. The time spent here is evaluated by comparing the number of rows in the child table with the copy rate of the tool copying the original table data to the new table, which is used if the evaluation finds that the data in the child table can be completed in less than--chunk-time time. In addition, because ALTER TABLE is much faster than the external copy data in MySQL, the copy rate is determined by--chunk-size-limit.

Because of the MySQL limit, the foreign key in the table before and after the name will be different, the new table after the foreign key name will be underlined, as well, will automatically change the foreign key corresponding index name

(3) Drop_swap

This method prohibits foreign key checking (foreign_key_checks=0) and then drop the original table before rename the new table, which is faster and will not be blocked, but the risk is higher and the risk is two:

? There is a difference between dropping the original table and rename the new table, during which time the table does not exist, which results in a query error

? If there is an error rename the new table, then the problem is big, because the original table has been dropped, only hehe

(4) None

This method is similar to Drop_swap without "swap", where all foreign keys in the original table are assigned to a nonexistent table, which causes the following information to be displayed when querying show engine InnoDB status:

Trying to add to index ' idx_fk_staff_id ' tuple:data tuple:2 Fields;0:len 1; Hex 05; ASC  ;; 1:len 4; Hex 80000001; ASC     ;; But the parent table ' Sakila '. ' Staff_old ' or their. ibd file does not currently exist!

Because the original table (Database.tablename) will be rename to Database.tablename_old and then drop off. This method of handling foreign keys allows DBAs to cancel this built-in functionality of the tool when needed

3.--charset

The default character type, for example, if the value is UTF8, set the output character to UTF8 format, pass Mysql_enable_utf8 to Dbd::mysql, and then run the set NAMES UTF8 command after connecting to MySQL

4.--[no]check-alter

Used to change the table safety warning, two types of alarms are currently supported:

(1) Column renames

In previous versions of the tool, changing the column name with the change-column name new_name caused the column's data to be lost, and the bug was patched in later versions, but the code was still not foolproof, so it was best to use--dry-run before the operation- Print to check to make sure the correct rename column

(2) DROP PRIMARY KEY

Unless--dry-run is specified, a warning is issued once the drop PRIMARY key is executed. The trigger of the tool, especially the Delete trigger, is mainly used to execute the trigger with the primary key, so it will be greatly affected. So it's best to use--dry-run and--print to check to make sure trigger can execute correctly.

5.--[no]check-replication-filters

If there is replication filtering in the master-slave replication to exit the error, that is, if the detection of binlog_ignore_db, replicate_do_db and other filtering methods, the error exits

6.--check-slave-lag

If the replication delay is greater than--max-lag to stop the data copy, set this parameter will monitor all the replication process, once found any more than--max-lag time to stop the data copy, if you do not want to monitor all the replication process, You can specify which objects you want to monitor by--recursion-method

7.--recursion-method

Master looks for slave method, the default value is Processlist,hosts, all methods are as follows:

METHOD       uses===========  ==================processlist  show processlisthosts        show SLAVE hostsdsn= DSN      DSNs from a tablenone does not         find slaves

(1) Processlist is the default, because show slave status is not very reliable.

(2) If the server uses a non-3306 port, the hosts method is also good

(3)when using the DSN =dsn method, you need to first create a table in the library, such as building a DNSN table in the Percona library .

The Build Table statement is :

CREATE TABLE ' DSNs ' (' id ' int (one) not null auto_increment, ' parent_id ' int (one) DEFAULT NULL, ' DSN ' varchar (255) Not Null,pri MARY KEY (' id '));

Build and plug in the master from copying information data, such as: INSERT INTO Table DSNs (DSN) values (H=slave_host,u=repl_user,p=repl_password,p=port);

You can then use the DSN method: The command is :--recursion-method Dsn=d=percona,t=dsns.

(4) If you want to monitor only the replication latency of hosts10.10.1.16 and 10.10.1.17, you can specify the contents of the h=10.10.1.16 and h=10.10.1.17 in the table "DSNs" to precisely

8.--chunk-time

The default is 0.5 seconds, and the tool calculates the number of rows of data (that is, chunk) that can be processed at that specified time, depending on how busy the current system is running, which is more flexible

9.--dry-run

Create and alter the new table, but do not create trigger, copy the data, and replace the original table

Ten.--execute

This parameter is used to perform the alter operation and, if not added, only performs some security checks and exits. Make sure you know how to use the tool and have the appropriate backup, and then add the parameter

One.--max-lag (type:time; DEFAULT:1S)

The data copy is interrupted until all replication latencies are less than this value, which defaults to 1S. After each chunk copy is completed, the OSC will go to show salve status to determine all the replication conditions through Seconds_behind_master, and any associated slave replication delay is higher than this value. The OSC stops the time specified by the data copy--check-interval parameter, and then re-initiates the check until the delay drops below that value.

If the--check-slave-lag parameter is specified, the OSC checks only the specified slave latency, not all slave. If you want to specify the monitoring server accurately, you can use the--recursion-method DSN to specify

--max-load (Type:array; default:threads_running=25)

The purpose of this parameter is to prevent the overhead of the OSC execution process from being too large, and if the data copy causes lock waits or excessive load, other queries are blocked and queued, which can cause threads_running growth. The OSC will perform a show global status to detect the system status after each chunk copy is completed, and if the critical value of some system parameters is set, the OSC will stop copying the data until the detection value returns to the threshold value. If this still finds a blockage, it's best to chunk the time value

The show global status is checked after each chunk copy, and if the status parameter is found to be above their threshold, the copy is interrupted, and the parameter is given a comma-delimited list of MySQL status parameters, with the parameter assigned as Variable_name=max_ Value or Variable_name:max_value, if not specified, OSC will set the parameter value to 120% of the current parameter value

That is: If you specify the threads_connected parameter, do not assign a value and the current value of this parameter is 100, then threads_connected will break the data copy if you want to specify precisely, use ' = ' or ': ' to precisely assign the value

"Other parameters"

1.--ask-pass

Prompt for a password when connecting to MySQL

2.--check-interval (type:time; DEFAULT:1)

Sleep time between checks for--max-lag.

3.--chunk-index

Used to specify the index

4.--chunk-index-columns

Used column to specify the federated index

5.--chunk-size

Used to specify the size of the chunk, not recommended, recommended for use with--chunk-time

6.--chunk-size-limit

To limit the size of the chunk

7.--config

Specifies the configuration, if this parameter is to be used, must be placed at the front of the command line

8.--critical-load

Similar to--max-load, the difference is that when an ultra-high load is detected, the OSC process is interrupted directly instead of paused

9.--default-engine

Specifies that the engine, with the parameters, will take the new table to the current default storage engine of MySQL instead of the original table.

Ten.--defaults-file

Specify the configuration file, you must use the absolute path

--[no]drop-new-table.

By default, a new table is dropped after a copy failure, which can be configured with this parameter

--[no]drop-old-table.

By default, the original table is dropped at the end of the change table and can be configured with this parameter

--host,--password,--pid,--port,--socket,--user.

Connect the parameters of the MySQL database

--print.

It is very good to use this parameter to--dry-run the SQL statements in the process of changing the table to stdout and to observe the operation of the table.

--progress.

In the process of copying data, the process report output to stderr, the parameters are specified by two parts, the first part is percentage, time, or iterations, the second part is the first part of the value of the money specified, that is, the number of points, seconds and times, the default is time,30

--quiet.

Do not output information to stdout, error and warning messages will still be output to stderr

--recurse.

Copy level designation, default is infinite level

--retries.

The number of retries when there is a non-fatal error in the data copy process, such as a lock wait timeout or a query being killed. Default is 3

--set-vars.

Use set ..... Executes the value in the specified parameter directly in the MySQL client

--[no]swap-tables.

The default is to replace the original table with a new table after the table is finished, which you can configure with this parameter

"DSN"

Note case sensitive when specified, "=" cannot have spaces around, and multiple values are separated by commas

1. A CharSet

2. D Database

3. F Mysql_read_default_file

4. H Host

5. P password

6. P Port

7. S Mysql_socket

8. T table

9.U User

"Specific execution process resolution"

Now execute a change statement and open the general log to observe

Pt-online-schema-change--alter ' Add column c1 int ' u=username,s=/data/mysql.sock,d=test,t=a--execute

　　

1. The first is a variety of show, a variety of sets, interested in their own to see, mainly on the authority of the check, time-out setting, the current system of the busy degree; then is the check of the table, such as whether there is a trigger exists, and

Explain SELECT * from ' test '. ' A ' where 1=1;select table_schema, table_name from Information_schema.key_column_usage WHERE Referenced_table_schema= ' test ' and referenced_table_name= ' a '; SHOW CREATE table ' test '. ' A ';

Here's a list of things to check out.

2. Start building a new watch now, notice the change of name, a becomes _a_new

CREATE TABLE ' test '. ' _a_new ' (  ' id ' int (one) not NULL auto_increment,  ' name ' char (a) COLLATE Utf8_bin not Null,
    ' type ' char (COLLATE) utf8_bin not null,  ' b ' int (one) DEFAULT NULL,  PRIMARY key (' id '),  key ' Uni_name ' (' NA Me ')) Engine=innodb auto_increment=10000000 DEFAULT Charset=utf8 collate=utf8_bin

and alter it directly on this empty table.

ALTER TABLE ' test '. ' _a_new ' add column C1 int

Then do a check to see if Alter is successful.

SHOW CREATE TABLE ' test '. ' _a_new '

　　　

3. Create a Trigger

New ' name ', new ' type ', new ' B ')

　　

4. By explain to determine the cost of executing the chunk copy, the first chunk is fixed at 1000 rows, and the subsequent chunk determines the size according to its own designation, such as Chunk-time

EXPLAIN SELECT ' id ', ' name ', ' type ', ' B ' from ' Test '. ' A ' force INDEX (' PRIMARY ') WHERE (' id ' >= ' 1 ')) and ((' ID ' <= ' + ')) LOCK in SHARE MODE

Make sure that the insert operation is not affected when the system is running, and that the data in the original table is copied to the new table according to the current chunk size

WHERE (' id ' >= ' 1 ')) and ((' id ' <= ') ') LOCK in SHARE MODE

　

5. The system load is checked immediately after the end of a chunk copy

SHOW GLOBAL STATUS like ' threads_running '

If that's all right, just keep explain,insert, load is too high, pause the copy, wait for the load to drop, and so on until all copies end

6. Whether the new table state is checked after the copy is finished

ANALYZE TABLE ' test '. ' _a_new '

If normal OK to go down, if not OK to delete the new table or do not delete the error exit (according to the parameters specified), the default is to delete

7. Make sure the new table is not a problem, replace the old table with the new one, and note that the name of the old table will be changed at this time.

RENAME TABLE ' test '. ' A ' to ' test '. ' _a_old ', ' Test '. ' _a_new ' to ' test '. ' A '

　　

8. The default is to delete the old table after successful replacement

DROP TABLE IF EXISTS ' test '. ' _a_old '

　

9. Delete the previously built trigger

Drop TRIGGER if EXISTS ' test '. ' Pt_osc_test_a_del ' drop TRIGGER if EXISTS ' test '. ' pt_osc_test_a_upd ' drop TRIGGER IF EXISTS ' Test '. ' Pt_osc_test_a_ins '

　　

10. Finally determine if the new table has been renamed successfully

Show TABLES from ' test ' like ' \_a\_new ' SHOW TABLES from ' test ' like ' a '

Change the table to complete!!!

　

　

Pt-online-schema-change interpretation