change的贯彻原理,change原理剖析

作者:计算机知识

pt-online-schema-change用于MySQL的在线DDL。

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.pt-online-schema-change工具的使用限制:

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三个触发器,否则修改表结构操作失败


2.创建执行环境

安装这里就不说了,直接到这里下载安装:

源码: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


创建测试数据:

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.执行pt-online-schema-change命令添加一个字段test2:

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:

localhost.localdomain

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

 

 

从上面包车型大巴进行输出中就能够大要看看实行进程:


测试表

创立1个新表,然后alter新表,然后成立触发器,然后copy数据,然后交流表,然后删除old表,然后删除触发器,最终回来成功alter的唤醒


mysql> show create table t2G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1005764 DEFAULT CHARSET=utf8
1 row in set (0.19 sec)

那正是说,具体在数据库中是怎么样操作的吧,前边张开了general_log,今后去查看下general_log文件中的内容:

 

该表中惟有壹列,id,自增主键。

4.结合general_log文件中的输出,pt-online-schema-change的光景进度如下:

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

 

一)、首先应用帐号密码连接到mysql后,获取内定表的情况音信,检查是还是不是有触发器,检查表是或不是有主键。

  

内部,表中已经存在一些数据

2)、接着根据修改表的表定义,新建二个名字为'_tb_new'不可知的有的时候表,对这几个表推行alter加多字段,并校验是还是不是实施成功。


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

3)、然后针对源表创立八个触发器,分别如下:


 

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

 

应用pt-online-schema-change对该表新增添1列

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

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

本文由bwin必赢发布,转载请注明来源

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