Pt-online-schema-change principle Analysis

It is said that the Pt-online-schema-change in the Pt-toolkit toolset can modify the table structure on-line without locking the table, so what is the specific principle of this tool, please see below to explain:

Restrictions on the use of 1.pt-online-schema-change tools:

1) If the modified table has a foreign key, the tool will not execute unless a specific value is specified using--alter-foreign-keys-method

2), the modified table must have a primary key, otherwise error: cannot chunk the original table ' Houyi '. ' GA ': There is no good index and the table is oversized. At./pt-online-schema-change Line 5353.

3), modified table can not have for after delete|insert|update three triggers, or modify table structure operation failed

2. Create an execution environment

Install here don't say, go directly here to download the installation:

Source: https://www.percona.com/downloads/percona-toolkit/2.2.17/tarball/percona-toolkit-2.2.17.tar.gz

rpm:https://www.percona.com/downloads/percona-toolkit/2.2.17/rpm/percona-toolkit-2.2.17-1.noarch.rpm

To create the test data:

mysql> CREATE DATABASE Xiaoboluo;

Query OK, 0 rows affected (0.04 sec)

Mysql> CREATE TABLE Teset_ptosc (id int unsigned NOT NULL PRIMARY key auto_increment,test varchar (100));

Query OK, 0 rows affected (0.04 sec)

mysql> INSERT into Teset_ptosc (test) VALUES (' Test1 '), (' Test2 '), (' test3 ');

Query OK, 3 rows affected (0.16 sec)

Records:3 duplicates:0 warnings:0

mysql> set global general_log=1;

Query OK, 0 rows affected (0.02 sec)

3. Execute the pt-online-schema-change command to add a field test2:

Shell > Pt-online-schema-change--alter ' Add column test2 varchar (+) ' p= ' password ', u=root,d=xiaoboluo,t=teset_ Ptosc--no-check-replication-filters--execute

The execution output is as follows:

Found 1 Slaves:

Localhost.localdomain

Would check slave lag on:

Localhost.localdomain

Operation, tries, wait:

Copy_rows, 10, 0.25

Create_triggers, 10, 1

Drop_triggers, 10, 1

Swap_tables, 10, 1

Update_foreign_keys, 10, 1

Altering ' xiaoboluo '. ' Teset_ptosc ' ...

Creating New Table ...

Created new Table Xiaoboluo._teset_ptosc_new OK.

Altering new Table ...

Altered ' Xiaoboluo '. ' _teset_ptosc_new ' OK.

2016-03-14T00:55:56 Creating triggers ...

2016-03-14T00:55:56 Created triggers OK.

2016-03-14t00:55:56 Copying approximately 1 rows ...

2016-03-14t00:55:56 Copied rows OK.

2016-03-14T00:55:56 Swapping tables ...

2016-03-14T00:55:56 swapped original and new tables OK.

2016-03-14t00:55:56 dropping old table ...

2016-03-14t00:55:56 Dropped old table ' Xiaoboluo '. ' _teset_ptosc_old ' OK.

2016-03-14T00:55:56 dropping triggers ...

2016-03-14T00:55:56 Dropped triggers OK.

Successfully altered ' Xiaoboluo '. ' Teset_ptosc '.

From the execution output above, you can probably see the execution process:

Create a new table, alter the new table, then create a trigger, then copy the data, then swap the table, then delete the old table, then delete the trigger, and finally return the prompt for the successful alter, so what is the actual operation in the database, the front open general_log, Now go to view the contents of the following General_log file:

4. In conjunction with the output from the General_log file, the approximate process of Pt-online-schema-change is as follows:

1), first use the account password to connect to MySQL, get the state information of the specified table, check whether there is a trigger, check whether the table has a primary key.

2), then follow the table definition of the modified table, create a new temporary table named ' _tb_new ', perform an alter add field to the table, and verify that the execution succeeds.

3), and then create three triggers for the source table, respectively, as follows:

Create trigger Db_tb_del after delete in DB.TB for each row, delete ignore from db._tb_new where Db._tb_new.id <=> OL D.id #删掉新表中db. _tb_new.id <=> old.id data, otherwise ignore operation

Create trigger Db_tb_del after update on DB.TB for each row replace into db._tb_new (ID,...) VALUES (new.id,...) #源表执行update的时候, write the corresponding data to the new table in the form of replace into

Create trigger Db_tb_del after insert in DB.TB for each row replace into db._tb_new (ID,...) VALUES (new.id,...) #源表执行iinsert操作的时候, write the corresponding data to the new table in the form of replace into

4), after the trigger is created, the insert low_priority ignore into db._tb_new (ID,..) SELECT ID,... the From TB lock in Share mode statement copies the source table data to the new table.

5), after the copy is complete, execute the statement: Rename table Db.tb to db._tb_old,db._tb_new to DB.TB simultaneously modifies the source table to _tb_old format, and modifies the new table _tb_new to the atomic modification of the source table name.

6), then, if there is no option to delete the old table, then the old table is deleted and three triggers are deleted. This completes the modification of the online table structure. The entire process locks the table only at the time of the Rename table, and other times it does not lock the table.

5. The following is the relevant part of the General_log output source:

642 Query SHOW TABLES from ' xiaoboluo ' like ' Teset\_ptosc '

642 Query SHOW TRIGGERS from ' xiaoboluo ' like ' Teset\_ptosc '

642 Query/*!40101 SET @OLD_SQL_MODE: = @ @SQL_MODE, @ @SQL_MODE: = ", @OLD_QUOTE: = @ @SQL_QUOTE_SHOW_CREATE, @ @SQL_QUOT E_show_create: = 1 */

642 Query use ' Xiaoboluo '

642 Query SHOW CREATE TABLE ' Xiaoboluo '. ' Teset_ptosc '

642 Query/*!40101 SET @ @SQL_MODE: = @OLD_SQL_MODE, @ @SQL_QUOTE_SHOW_CREATE: = @OLD_QUOTE */

642 Query EXPLAIN SELECT * from ' xiaoboluo '. ' Teset_ptosc ' WHERE 1=1

642 Query SELECT TABLE_SCHEMA, table_name from information_schema.key_column_usage WHERE referenced_table_schema= ' Xiao Boluo ' and referenced_table_name= ' Teset_ptosc '

642 Query SHOW VARIABLES like ' wsrep_on '

642 Query/*!40101 SET @OLD_SQL_MODE: = @ @SQL_MODE, @ @SQL_MODE: = ", @OLD_QUOTE: = @ @SQL_QUOTE_SHOW_CREATE, @ @SQL_QUOT E_show_create: = 1 */

642 Query use ' Xiaoboluo '

642 Query SHOW CREATE TABLE ' Xiaoboluo '. ' Teset_ptosc '

642 Query/*!40101 SET @ @SQL_MODE: = @OLD_SQL_MODE, @ @SQL_QUOTE_SHOW_CREATE: = @OLD_QUOTE */

642 Query CREATE TABLE ' Xiaoboluo '. ' _teset_ptosc_new ' (

' id ' int (ten) unsigned not NULL auto_increment,

' Test ' varchar (+) DEFAULT NULL,

PRIMARY KEY (' id ')

) Engine=innodb DEFAULT Charset=utf8

642 Query ALTER TABLE ' Xiaoboluo '. ' _teset_ptosc_new ' Add column test2 varchar (100)

642 Query/*!40101 SET @OLD_SQL_MODE: = @ @SQL_MODE, @ @SQL_MODE: = ", @OLD_QUOTE: = @ @SQL_QUOTE_SHOW_CREATE, @ @SQL_QUOT E_show_create: = 1 */

642 Query use ' Xiaoboluo '

642 Query SHOW CREATE TABLE ' Xiaoboluo '. ' _teset_ptosc_new '

642 Query/*!40101 SET @ @SQL_MODE: = @OLD_SQL_MODE, @ @SQL_QUOTE_SHOW_CREATE: = @OLD_QUOTE */

642 Query CREATE TRIGGER ' Pt_osc_xiaoboluo_teset_ptosc_del ' after DELETE on ' Xiaoboluo '. ' Teset_ptosc ' for each ROW DELE TE IGNORE from ' Xiaoboluo '. ' _teset_ptosc_new ' WHERE ' xiaoboluo '. ' _teset_ptosc_new '. ' IDs ' <=> old ' id '

642 Query CREATE TRIGGER ' pt_osc_xiaoboluo_teset_ptosc_upd ' after UPDATE on ' Xiaoboluo '. ' Teset_ptosc ' for each ROW REPL ACE into ' xiaoboluo '. ' _teset_ptosc_new ' (' id ', ' test ') VALUES (new ' id ', new ' test ')

642 Query CREATE TRIGGER ' Pt_osc_xiaoboluo_teset_ptosc_ins ' after INSERT on ' Xiaoboluo '. ' Teset_ptosc ' for each ROW REPL ACE into ' xiaoboluo '. ' _teset_ptosc_new ' (' id ', ' test ') VALUES (new ' id ', new ' test ')

642 Query EXPLAIN SELECT * from ' xiaoboluo '. ' Teset_ptosc ' WHERE 1=1

642 Query EXPLAIN SELECT ' id ', ' test ' from ' Xiaoboluo '. ' Teset_ptosc ' LOCK in SHARE MODE/*explain Pt-online-schema-chan GE 45383 Copy table*/

642 Query INSERT low_priority IGNORE into ' xiaoboluo '. ' _teset_ptosc_new ' (' id ', ' test ') SELECT ' id ', ' test ' from ' XIAOB Oluo '. ' Teset_ptosc ' LOCK in SHARE MODE/*pt-online-schema-change 45383 copy table*/

642 Query SHOW WARNINGS

642 Query SHOW GLOBAL STATUS like ' threads_running '

642 Query RENAME TABLE ' Xiaoboluo '. ' Teset_ptosc ' to ' xiaoboluo '. ' _teset_ptosc_old ', ' Xiaoboluo '. ' _teset_ptosc_new ' to ' Xiaoboluo '. ' Teset_ptosc '

642 Query DROP TABLE IF EXISTS ' Xiaoboluo '. ' _teset_ptosc_old '

642 Query DROP TRIGGER IF EXISTS ' Xiaoboluo '. ' Pt_osc_xiaoboluo_teset_ptosc_del '

642 Query DROP TRIGGER IF EXISTS ' Xiaoboluo '. ' Pt_osc_xiaoboluo_teset_ptosc_upd '

642 Query DROP TRIGGER IF EXISTS ' Xiaoboluo '. ' Pt_osc_xiaoboluo_teset_ptosc_ins '

642 Query SHOW TABLES from ' xiaoboluo ' like ' \_teset\_ptosc\_new '

Pt-online-schema-change principle Analysis