pt-online-schema-change emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. This means that the original table is not locked, and clients may continue to read and change data in it.
pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table.
The data copy process is performed in small chunks of data, which are varied to attempt to make them execute in a specific amount of time (see --chunk-time). This process is very similar to how other tools, such as pt-tablechecksum, work. Any modifications to data in the original tables during the copy will be reflected in the new table, because the tool creates triggers on the original table to update the corresponding rows in the new table. The use of triggers means that the tool will not work if any triggers are already defined on the table.
When the tool finishes copying data into the new table, it uses an atomic RENAME TABLE operation to simultaneously rename the original and new tables. After this is complete, the tool drops the original table.
亚洲必赢app,Foreign keys complicate the tool’s operation and introduce additional risk. The technique of atomically renaming the original and new tables does not work when foreign keys refer to the table. The tool must update foreign keys to refer to the new table after the schema change is complete. The tool supports two methods for accomplishing this. You can read more about this in the documentation for --alter-foreign-keys-method.
Foreign keys also cause some side effects. The final table will have the same foreign keys and indexes as the original table (unless you specify differently in your ALTER statement), but the names of the objects may be changed slightly to avoid object name collisions in MySQL and InnoDB.
For safety, the tool does not modify the table unless you specify the --execute option, which is not enabled by default. The tool supports a variety of other measures to prevent unwanted load or other problems, including automatically detecting replicas, connecting to them, and using the following safety checks:
• In most cases the tool will refuse to operate unless a PRIMARY KEY or UNIQUE INDEX is 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 are delayed in replication. See --max-lag for 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_change的贯彻原理,change原理剖析。wait_timeout=60 so that it is more likely to be the victim of any lock contention, and less likely to disrupt other transactions. These values can be changed by specifying --set-vars.
• The tool refuses to alter the table if foreign key constraints reference it, unless you specify --alter-foreign-keys-method.
• The tool cannot alter MyISAM tables on “Percona XtraDB Cluster” nodes.

pt-online-schema-change原理解析 博客相关需求阅读 - zengkefu - 天涯论坛


上面结合官方文书档案和general log来深入分析其促成原理。工具的使用限制:

1)、如果修改表有外键,除非使用 --alter-foreign-keys-method 指定特定的值,否则工具不予执行

2)、被修改表必须要有主键,否则报错: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)、被修改表上不能有针对after delete|insert|update三个触发器,否则修改表结构操作失败






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)


shell > pt-online-schema-change --alter 'add column test2 varchar(100)' p='password',u=root,D=xiaoboluo,t=teset_ptosc --no-check-replication-filters --execute


Found 1 slaves:


Will check slave lag on:


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






mysql> show create table t2G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  PRIMARY KEY (`id`)
1 row in set (0.19 sec)





    若是转发,请评释博文来源:   ,版权归 网易 苏家小萝卜 全体。望各位接济!






mysql> select count(*) from t2;
| count(*) |
|  1005763 |
1 row in set (0.31 sec)



create trigger db_tb_del after delete on db.tb for each row delete ignore from db._tb_new where <=> #借使新表中尚无的多少,在源表中试行删除就忽略那一个操作



create trigger db_tb_del after update on db.tb for each row replace into db._tb_new(id,...) values(,...) #源表实施update的时候,把相应的数额replace into的不二等秘书诀写入新表

    PE景逸SUVCONA提供了好些个管制维护MySQL的小工具,集成在 PE途睿欧CONA Toolkit工具中,有慢查询剖判、主从差别比较、主从差距修复及在线表结构修改等工具,个人感觉相当好用的。本文简介pt-online-schema-change 工具。


关键词: 56net亚洲必嬴 MySQL pt-tools