MySQL online modify table structure Pt-osc

MySQL online modify table structure Pt-osc

It is known that MySQL is more expensive than the DDL operation. Because MySQL blocks any read and write operations while modifying the table.

Basically the business is paralyzed. This is not tolerated if the volume of data is large and may take several hours to complete. Percona developed a series of Tools Percona Toolkit package, which has a tool pt-online-schema-change can perform DDL operations online without blocking read and write operations and affecting business processes. Of course there are other tools such as MySQL5.6 's online DDL and gh-ost This article mainly about Pt-online-schema-change modifying the table structure online.

Principle part

Environment overview

Percona-server-5.7.17-11 percona-toolkit-3.0.3-1.el7.x86_64

Table structure

CREATE TABLE ' test ' (' id ' int (+) not null, ' name ' char () default NULL, PRIMARY KEY (' id ')) engine=myisam default CH Arset=utf8

Action Modify non-primary Key Name field

One. Preparatory work

1. Set current reply parameter session level

   
   

SHOW VARIABLES like ' innodb\_lock_wait_timeout '; Set session Innodb_lock_wait_timeout=1set session lock_wait_timeout=60 SET session wait_timeout=10000innodb_lock_wait _timeout=1 lock_wait_timeout=60 wait_timeout=10000

2. Collect MySQL Information

Show VARIABLES like ' version% ' show enginesshow VARIABLES like ' innodb_version ' show VARIABLES like ' Innodb_stats_persiste NT ' SELECT @ @SERVER_IDSHOW GRANTS for Current_User () show full processlistshow GLOBAL STATUS like ' threads_running ' SHOW GLO BAL STATUS like ' threads_running ' SELECT CONCAT (@ @hostname, @ @port) show TABLES from ' test2 ' like ' test1 ' show TRIGGERS from ' Test2 ' like ' test1 '

Two official beginnings

1. Create a new table that is identical to the old table

CREATE TABLE ' test2 '. ' _test1_new ' (' id ' int () not null, ' name ' char () DEFAULT NULL, PRIMARY KEY (' id ')) engine=myi SAM DEFAULT Charset=utf8

2. Modify the table structure on a new table

ALTER TABLE ' test2 '. ' _test1_new ' modify name char (27)

3. Create a Trigger

CREATE TRIGGER ' Pt_osc_test2_test1_del ' after DELETE on ' test2 '. ' Test1 ' for each ROW DELETE IGNORE from ' test2 '. ' _test1_ne W ' WHERE ' test2 '. ' _test1_new '. ' id ' <=> old ' id '

#删除操作

CREATE TRIGGER ' pt_osc_test2_test1_upd ' after UPDATE on ' test2 '. ' Test1 ' for each ROW BEGIN DELETE IGNORE from ' test2 '. ' _te St1_new ' WHERE! (old. ' id ' <=> NEW ' id ') and ' test2 '. ' _test1_new '. ' id ' <=> old ' id '; REPLACE into ' test2 '. ' _test1_new ' (' id ', ' name ') VALUES (new ' id ', new ' name ')

#更新操作

CREATE TRIGGER ' Pt_osc_test2_test1_ins ' after INSERT on ' test2 '. ' Test1 ' for each ROW REPLACE into ' test2 '. ' _test1_new ' (' I d ', ' name ') VALUES (new ' id ', new ' name ')

#插入操作

4. Inserting into the old table

EXPLAIN SELECT ' id ', ' name ' from ' test2 '. ' Test1 ' LOCK in SHARE MODE

IGNORE into ' test2 '. ' _test1_new ' (' id ', ' name ') SELECT ' id ', ' name ' from ' test2 '. ' Test1 ' LOCK in SHARE MODE/*PT-ONLINE-SC Hema-change 6291 Copy table*/

#有锁操作LOCK in SHARE MODE

The third finishing job

Show Warningsselect @ @SERVER_IDSHOW GRANTS for Current_User () show full processlistshow GLOBAL STATUS like ' Threads_runnin G ' ANALYZE table ' test2 '. ' _test1_new '/* pt-online-schema-change */rename table ' test2 '. ' Test1 ' to ' test2 '. ' _test1_old ', ' Test2 '. ' _test1_new ' to ' test2 '. ' test1 ' DROP TABLE IF EXISTS ' test2 '. ' _test1_old ' ROP TRIGGER IF EXISTS ' test2 '. ' Pt_osc_ Test2_test1_del ' Drop TRIGGER if EXISTS ' test2 '. ' pt_osc_test2_test1_upd ' drop TRIGGER if EXISTS ' test2 '. ' Pt_osc_test2_ Test1_ins ' SHOW TABLES from ' test2 ' like ' \_test1\_new '

Overview

1. View collect MySQL Information

2. Create a new table with the same structure as the original table and change the table structure in the new table.

3. Create 3 triggers in the original table three triggers corresponding to the INSERT update delete operation

4. Writes from the original table copy data to the new table copy are updated to the staging table

5. After copy completes, rename the original table to the old table and then the new table rename the original table and finally deletes the old table and the trigger

   
   

Four operational considerations

• Read the tool ' s documentation

• Review The tool ' s known "BUGS"

• Test the tool on a non-production server

• Backup your production server and verify the backups

Review the tool documentation first, use the test before doing the backup backup. In the implementation of the online modification of the table structure, it is best to choose the business low peak period, do not delete the old table.

Five Pt-osc limits

• In most cases the tool would refuse to operate unless a PRIMARY KEY or UNIQUE INDEX was present in the table. See--alter for details.

• The tool refuses to operate if it detects replication filters. See--[no]check-replication-filters for details.

• The tool pauses the data copy operation if it observes any replicas that is delayed in replication. See--max-lagfor details.

• The tool pauses or aborts its operation if it detects too much load on the server. See--max-load and--critical-load for details.

• The tool sets Innodb_lock_wait_timeout=1 and (for MySQL 5.5 and newer) lock_wait_timeout=60 so it's more likely to B E The victim of any lock contention, and less likely to disrupt other transactions. These values can changed by specifying--set-vars.

• The tool refuses to alter the table if FOREIGN key constraints reference it, unless you specify--alter-foreign-keys-metho D.

• The tool cannot alter MyISAM tables on "Percona XtraDB Cluster" nodes.

Six precautions

1. Take a look at the tool documentation before using the test to back up backup backups.

2. In the implementation of the online modification of the table structure, it is best to choose the business low peak period, do not delete the old table.

3. Must have the primary key, cannot use, must have the primary key, must have the primary key, must have the primary key, must have the primary key.

4.pt-osc If you change the foreign key constraint, you refuse to work unless you specify--alter-foreign-keys-method.

5. You need to specify a character set to prevent garbled characters when operating.

Reference

Https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

This article is from the "Linux" blog, so be sure to keep this source http://tplinux.blog.51cto.com/3917416/1933189

MySQL online modify table structure Pt-osc