pt-online-schema-change原理解析

都说pt-toolkit工具集中的pt-online-schema-change可以在线不锁表修改表结构，那么这个工具具体是什么原理呢，请见下面娓娓道来：

 

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

 

从上面的执行输出中就可以大概看到执行过程：

创建一个新表，然后alter新表，然后创建触发器，然后copy数据，然后交换表，然后删除old表，然后删除触发器，最后返回成功alter的提示，那么，具体在数据库中是如何操作的呢，前面打开了general_log，现在去查看下general_log文件中的内容：

 

 

4.结合general_log文件中的输出，pt-online-schema-change的大致过程如下：

1）、首先使用帐号密码连接到mysql后，获取指定表的状态信息，检查是否有触发器，检查表是否有主键。

2）、接着按照修改表的表定义，新建一个名为'_tb_new'不可见的临时表，对这个表执行alter添加字段，并校验是否执行成功。

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 #删掉新表中db._tb_new.id <=> OLD.id的数据，否则忽略操作

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的方式写入新表

create trigger db_tb_del after insert on db.tb for each row replace into db._tb_new(id,...) values(new.id,...)  #源表执行iinsert操作的时候，把对应的数据replace into的方式写入新表

4）、触发器创建好之后会执行insert low_priority ignore into db._tb_new(id,..) select id,... from tb lock in share mode语句复制源表数据到新表。

5）、复制完成之后执行语句：rename table db.tb to db._tb_old,db._tb_new to db.tb同时把源表修改为_tb_old格式，把新表_tb_new修改为源表名字的原子修改。

6）、接着，如果没有加不删除old表的选项，那么就会删除Old表，然后删除三个触发器。到这里就完成了在线表结构的修改 。整个过程只在rename表的时间会锁一下表，其他时候不锁表。

 

 

5.下面是general_log输出原文的相关部分：

                     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_QUOTE_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='xiaoboluo' 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_QUOTE_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(10) unsigned NOT NULL AUTO_INCREMENT,

  `test` varchar(100) 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_QUOTE_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 DELETE IGNORE FROM `xiaoboluo`.`_teset_ptosc_new` WHERE `xiaoboluo`.`_teset_ptosc_new`.`id` <=> OLD.`id`

                     642 Query    CREATE TRIGGER `pt_osc_xiaoboluo_teset_ptosc_upd` AFTER UPDATE ON `xiaoboluo`.`teset_ptosc` FOR EACH ROW REPLACE 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 REPLACE 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-change 45383 copy table*/

                     642 Query    INSERT LOW_PRIORITY IGNORE INTO `xiaoboluo`.`_teset_ptosc_new` (`id`, `test`) SELECT `id`, `test` FROM `xiaoboluo`.`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'