Pt-online-schema-change Working principle

./pt-online-schema-change--user=root--password=root456--recursion-method= "Processlist"--chunk-size=50000-- alter= "Modify column emp_no bigint not NULL auto_increment" H=192.168.31.64,p=3306,d=employees,t=tmp_employees-- Execute

Create an intermediate table that is the same as the original table structure, with the name generally beginning with _ and new ending

CREATE TABLE ' employees '. ' _tmp_employees_new ' (

' Emp_no ' bigint (+) not NULL auto_increment,

' Birth_date ' date not NULL,

' first_name ' varchar (not NULL),

' last_name ' varchar (+) not NULL,

' Gender ' enum (' M ', ' F ') is not NULL,

' Hire_date ' date not NULL,

PRIMARY KEY (' Emp_no '),

KEY ' idx_1 ' (' hire_date ')

) Engine=innodb auto_increment=500000 DEFAULT Charset=utf8;

Modify the table structure by condition in the intermediate table

ALTER TABLE ' employees '. ' _tmp_employees_new ' modify column emp_no bigint not NULL auto_increment;

Creates a trigger that is synchronized to a temporarily created table when the original table is found to have a redaction operation during the copy process

TRIGGER ' Pt_osc_employees_tmp_employees_del ' after DELETE in ' employees '. ' Tmp_employees ' for each ROW DELETE IGNORE from ' employees ', ' _tmp_employees_new ' WHERE ' employees '. ' _tmp_employees_new '. ' Emp_no ' <=> old ' Emp_no '

TRIGGER ' pt_osc_employees_tmp_employees_upd ' after UPDATE in ' employees '. ' Tmp_employees ' for each ROW REPLACE into ' employees '. ' _tmp_employees_new ' (' emp_no ', ' birth_date ', ' first_name ', ' last_name ', ' gender ', ' hire_date ' VALUES (new ' emp_no ', new ' birth_date ', new ' first_name ', new ' last_name ', new ' gender ', new ' hire_date ')

TRIGGER ' Pt_osc_employees_tmp_employees_ins ' after INSERT in ' employees '. ' Tmp_employees ' for each ROW REPLACE into ' employees '. ' _tmp_employees_new ' (' emp_no ', ' birth_date ', ' first_name ', ' last_name ', ' gender ', ' hire_date ' VALUES (new ' emp_no ', new ' birth_date ', new ' first_name ', new ' last_name ', new ' gender ', new ' hire_date ')

EXPLAIN SELECT * from ' employees '. ' Tmp_employees ' WHERE 1=1

Copy the data, the default Chunk-size value is 1000, that is, each copy of 1000 data, the copy process needs to add a s lock, then it is not possible to do the modification operation, but this copy is generally very fast, the impact is very small, because the need to lock, So your Chunk-size value does not specify too large, this article is opened general-log, table data total of 300,000, in order to let less log, so specified 50000

INSERT low_priority IGNORE into ' employees '. ' _tmp_employees_new ' (' emp_no ', ' birth_date ', ' first_name ', ' last_name ', ' Gender ', ' hire_date ') SELECT ' emp_no ', ' birth_date ', ' first_name ', ' last_name ', ' gender ', ' hire_date ' from ' employees '. ' Tmp_employees ' Force INDEX (' PRIMARY ') WHERE ((' Emp_no ' >= ' 10001 ')) and ((' Emp_no ' <= ' 60000 ')) LOCK in SHARE MODE/ *pt-online-schema-change 15147 Copy nibble*/

。。。。。。。。。

INSERT low_priority IGNORE into ' employees '. ' _tmp_employees_new ' (' emp_no ', ' birth_date ', ' first_name ', ' last_name ', ' Gender ', ' hire_date ') SELECT ' emp_no ', ' birth_date ', ' first_name ', ' last_name ', ' gender ', ' hire_date ' from ' employees '. ' Tmp_employees ' Force INDEX (' PRIMARY ') WHERE ((' Emp_no ' >= ' 499976 ')) and ((' Emp_no ' <= ' 499999 ')) LOCK in SHARE MODE /*pt-online-schema-change 15147 Copy nibble*/

Make table statistics for the intermediate table

ANALYZE TABLE ' employees '. ' _tmp_employees_new '/* pt-online-schema-change */

Name the original table _tmp_employees_old, and name the intermediate table as the original table

RENAME TABLE ' employees '. ' Tmp_employees ' to ' employees '. ' _tmp_employees_old ', ' employees '. ' _tmp_employees_new ' to ' Employees '. ' Tmp_employees '

Delete old table

DROP TABLE IF EXISTS ' employees '. ' _tmp_employees_old '

Delete the trigger created in step three

EXISTS ' employees ', DROP TABLE IF '. ' _tmp_employees_old '

TRIGGER IF EXISTS ' employees '. ' Pt_osc_employees_tmp_employees_del '

TRIGGER IF EXISTS ' employees '. ' PT_OSC_EMPLOYEES_TMP_EMPLOYEES_UPD '

TRIGGER IF EXISTS ' employees '. ' Pt_osc_employees_tmp_employees_ins '

Pt-online-schema-change Working principle