pt-osc原理、限制、及与原生online-ddl比较

ps-osc https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

 

一、MySQL ddl 的问题现状
在运维mysql数据库时，我们总会对数据表进行ddl 变更，修改添加字段或者索引，对于mysql 而已，ddl 显然是一个令所有MySQL dba 诟病的一个功能，因为在MySQL中在对表进行ddl时，会

锁表，当表比较小比如小于1w上时，对前端影响较小，当时遇到千万级别的表 就会影响前端应用对表的写操作。
目前InnoDB引擎是通过以下步骤来进行DDL的：
1 按照原始表（original_table）的表结构和DDL语句，新建一个不可见的临时表（tmp_table）
2 在原表上加write lock，阻塞所有更新操作（insert、delete、update等）
3 执行insert into tmp_table select * from original_table
4 rename original_table和tmp_table，最后drop original_table
5 释放 write lock。
我们可以看见在InnoDB执行DDL的时候，原表是只能读不能写的。

二、MySQL 5.6原生ddl原理和用法
  从mysql 5.6开始支持在线ddl操作，其原理：
在执行创建或者删除操作同时，将DML操作日志写入到一个缓存中，待完成后再将重做应用到表上，以此达到数据的一致性。
这个缓存的大小由参数innodb_online_alter_log_max_size控制，默认大小为128MB,支持动态修改。
实现方式分两种：copy和inplace，copy方式会锁表，如修改字段和对主键操作是采用copy方式。

onlineDDL语法：
 alter table
  ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  ADD   [COLUMN] col_name  column_definition [FIRST|AFTER col_name]
  CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
  MODIFY [COLUMN] col_name column_definition
 [FIRST | AFTER col_name],
ALGORITHM [=] {DEFAULT|INPLACE|COPY}  LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE};
       
简单的说就是原来的语句上，加个ALGORITHM=xxx LOCK=XXXX
ALGORITHM指定了onlineDDL时候是使用COPY,还是INPLACE，
（1）COPY表示执行DDL的时候会创建临时表。
（2）INPLACE表示不需要创建临时表。（inplace英文单词是原地的意思）
（3）DEFAULT表示根据参数old_alter_table来判断是通过INPLACE还是COPY的算法，old_alter_table参数默认为OFF，表示采用INPLACE的方式。

LOCK部分为索引创建或删除时对表添加锁的情况，默认是default，可选择的如下：
（1）NONE，目标表不添加任何锁，可以进行读写操作，不阻塞任何操作。如果手工指定NONE，但是onlineDDL不支持SHARE模式，返回一个错误信息，告诉你用SHARE摸索。
（2）SHARE，对操作表加一个S锁。不阻塞读操作。写操作会阻塞，将会发生等待MDL锁，如果手工指定SHARE，但是onlineDDL不支持SHARE模式，将返回一个错误信息。
（3）EXCLUSIVE，执行索引创建或删除时，对目标表加上一个X锁。读写事务均不能进行。会阻塞所有的线程。这和COPY方式类似，但是不需要像COPY方式那样创建一张临时表。
（4）DEFAULT，该模式首先会判断当前操作是否可以使用NONE模式，若不能，则判断是否可以使用SHARE模式，最后判断是否可以使用EXCLUSIVE模式。也就是说DEFAULT会通过判断事务的最大

并发性来判断执行DDL的模式。

 

三、pt-osc工作原理解析 
 perconal 推出一个工具 pt-online-schema-change ，其特点是修改过程中不会造成读写阻塞。  
对一个20G的大表用pt工具增加字段来解读工作原理，如下：
[apps@mvxl0782 bin]$ ./pt-online-schema-change --host=10.24.65.31 -u root -p safe2016 --alter='add column last_updated_date datetime default current_timestamp on update current_timestamp' --print --execute D=lots,t=t_o_tr

 

Found 1 slaves:
  mvxl0783
Will check slave lag on:
  mvxl0783
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 `lots`.`t_o_tr`...
Creating new table...   ----------建立一个新的空表
CREATE TABLE `lots`.`_t_o_tr_new` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `CUSTOMER_ORDER_NO` varchar(50) NOT NULL COMMENT '?????',
  `ORDER_NO` varchar(50) NOT NULL COMMENT '?????',
  `EX_CODE` varchar(20) DEFAULT NULL COMMENT '????',
  `EX_NAME` varchar(50) DEFAULT NULL COMMENT '????',
  `EX_NO` varchar(100) DEFAULT NULL COMMENT '????',
-----------中间省略----------------------------------
) ENGINE=InnoDB AUTO_INCREMENT=60182992 DEFAULT CHARSET=utf8 COMMENT='?????'
Created new table lots._t_o_tr_new OK.
Waiting forever for new table `lots`.`_t_o_tr_new` to replicate to mvxl0783...
Altering new table...   --------给空表增加字段
ALTER TABLE `lots`.`_t_o_tr_new` add column last_updated_date datetime default current_timestamp on update current_timestamp
Altered `lots`.`_t_o_tr_new` OK.
2016-07-24T21:37:33 Creating triggers... -----在原表上增加delete/update/insert的after trigger
CREATE TRIGGER `pt_osc_lots_t_o_tr_del` AFTER DELETE ON `lots`.`t_o_tr` FOR EACH ROW DELETE IGNORE FROM `lots`.`_t_o_tr_new` WHERE `lots`.`_t_o_tr_new`.`id` <=>

OLD.`id`
CREATE TRIGGER `pt_osc_lots_t_o_tr_upd` AFTER UPDATE ON `lots`.`t_o_tr` FOR EACH ROW REPLACE INTO ..................
CREATE TRIGGER `pt_osc_lots_t_o_tr_ins` AFTER INSERT ON `lots`.`t_o_tr` FOR EACH ROW REPLACE INTO ..................
2016-07-24T21:37:33 Created triggers OK.
2016-07-24T21:37:33 Copying approximately 31077422 rows...  --- copy数据到新表
INSERT LOW_PRIORITY IGNORE INTO `lots`.`_t_o_tr_new` (`id`, ...................)) LOCK IN SHARE MODE /*pt-online-schema-change 18438 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `lots`.`t_o_tr` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Copying `lots`.`t_o_tr`:   2% 17:48 remain
Copying `lots`.`t_o_tr`:   5% 18:22 remain
Copying `lots`.`t_o_tr`:   7% 19:08 remain
Copying `lots`.`t_o_tr`:   9% 19:39 remain
Copying `lots`.`t_o_tr`:  11% 19:46 remain
Copying `lots`.`t_o_tr`:  13% 19:11 remain
Copying `lots`.`t_o_tr`:  15% 18:32 remain
Copying `lots`.`t_o_tr`:  18% 17:47 remain
Copying `lots`.`t_o_tr`:  20% 17:35 remain
Copying `lots`.`t_o_tr`:  22% 17:08 remain
Copying `lots`.`t_o_tr`:  24% 16:41 remain
Copying `lots`.`t_o_tr`:  26% 16:23 remain
Copying `lots`.`t_o_tr`:  29% 15:48 remain
Copying `lots`.`t_o_tr`:  31% 15:13 remain
Copying `lots`.`t_o_tr`:  33% 14:40 remain
Copying `lots`.`t_o_tr`:  36% 14:03 remain
Copying `lots`.`t_o_tr`:  38% 13:31 remain
Copying `lots`.`t_o_tr`:  40% 13:02 remain
Copying `lots`.`t_o_tr`:  43% 12:33 remain
Copying `lots`.`t_o_tr`:  45% 12:04 remain
Copying `lots`.`t_o_tr`:  47% 11:31 remain
Copying `lots`.`t_o_tr`:  50% 10:57 remain
Copying `lots`.`t_o_tr`:  52% 10:26 remain
Copying `lots`.`t_o_tr`:  54% 09:53 remain
Copying `lots`.`t_o_tr`:  57% 09:24 remain
Copying `lots`.`t_o_tr`:  59% 08:52 remain
Copying `lots`.`t_o_tr`:  61% 08:24 remain
Copying `lots`.`t_o_tr`:  63% 07:57 remain
Copying `lots`.`t_o_tr`:  66% 07:25 remain
Copying `lots`.`t_o_tr`:  68% 06:51 remain
Copying `lots`.`t_o_tr`:  70% 06:22 remain
Copying `lots`.`t_o_tr`:  73% 05:50 remain
Copying `lots`.`t_o_tr`:  75% 05:13 remain
Copying `lots`.`t_o_tr`:  78% 04:44 remain
Copying `lots`.`t_o_tr`:  80% 04:16 remain
Copying `lots`.`t_o_tr`:  82% 03:45 remain
Copying `lots`.`t_o_tr`:  84% 03:16 remain
Copying `lots`.`t_o_tr`:  87% 02:46 remain
Copying `lots`.`t_o_tr`:  89% 02:17 remain
Copying `lots`.`t_o_tr`:  91% 01:47 remain
Copying `lots`.`t_o_tr`:  94% 01:14 remain
Copying `lots`.`t_o_tr`:  96% 00:40 remain
Copying `lots`.`t_o_tr`:  99% 00:03 remain
2016-07-24T22:12:45 Copied rows OK.
2016-07-24T22:12:45 Swapping tables...  ---将原表改名，并将新表改成原表名
RENAME TABLE `lots`.`t_o_tr` TO `lots`.`_t_o_tr_old`, `lots`.`_t_o_tr_new` TO `lots`.`t_o_tr`
2016-07-24T22:12:46 Swapped original and new tables OK.
2016-07-24T22:12:46 Dropping old table... ---删除原表
DROP TABLE IF EXISTS `lots`.`_t_o_tr_old`
2016-07-24T22:12:47 Dropped old table `lots`.`_t_o_tr_old` OK.
2016-07-24T22:12:47 Dropping triggers... ---删除trigger
DROP TRIGGER IF EXISTS `lots`.`pt_osc_lots_t_o_tr_del`;
DROP TRIGGER IF EXISTS `lots`.`pt_osc_lots_t_o_tr_upd`;
DROP TRIGGER IF EXISTS `lots`.`pt_osc_lots_t_o_tr_ins`;
2016-07-24T22:12:47 Dropped triggers OK.
Successfully altered `lots`.`t_o_tr`.

 

从上面可看到pt-osc原理如下：

1.建立一个与需要操作的表相同表结构的空表
2.给空表执行表结构修改
3.在原表上增加delete/update/insert的after trigger
4.copy数据到新表
5.将原表改名，并将新表改成原表名
6.删除原表
7.删除trigger

 

pt-osc限制条件：
1.表要有主键，否则会报错;
2.表不能有trigger;
3.如果表有外键，除非使用 --alter-foreign-keys-method 指定特定的值，否则工具不予执行。

 

使用pt-osc修改主键时注意:
原表上有个复合主键，现在要添加一个自增id作为主键，如何进行?
会涉及到以下修改动作：
1.删除复合主键定义
2.添加新的自增主键3.原复合主键字段，修改成唯一索引
需要将删除原主键、增加新主键和增加原主键为唯一键同时操作：
--alter "DROP PRIMARY KEY,add column pk int auto_increment primary key,add unique key uk_id_k(id,k)"
---------------------

原文：https://blog.csdn.net/zengxuewen2045/article/details/52017247

 

 

 

 

 

 

 

 

 

pt-osc原理、限制、及与原生online-ddl比较

 

 

1. pt-osc工作过程

1. 创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)
2. 在新表执行alter table 语句（速度应该很快）
3. 在原表中创建触发器3个触发器分别对应insert,update,delete操作
4. 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表
5. Rename 原表到old表中，在把临时表Rename为原表
6. 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理
7. 默认最后将旧原表删除

2. 常用选项说明

只介绍部分常用的选项

• --host=xxx --user=xxx --password=xxx
  连接实例信息，缩写-h xxx -u xxx -p xxx，密码可以使用参数--ask-pass 手动输入。
• --alter
  结构变更语句，不需要 ALTER TABLE关键字。与原始ddl一样可以指定多个更改，用逗号分隔。
  • 绝大部分情况下表上需要有主键或唯一索引，因为工具在运行当中为了保证新表也是最新的，需要旧表上创建 DELETE和UPDATE 触发器，同步到新表的时候有主键会更快。个别情况是，当alter操作就是在c1列上建立主键时，DELETE触发器将基于c1列。
  • 子句不支持 rename 去给表重命名。
  • alter命令原表就不支持给索引重命名，需要先drop再add，在pt-osc也一样。(mysql 5.7 支持 RENAME INDEX old_index_name TO new_index_name)
    但给字段重命名，千万不要drop-add，整列数据会丢失，使用change col1 col1_new type constraint（保持类型和约束一致，否则相当于修改 column type，不能online）
  • 子句如果是add column并且定义了not null，那么必须指定default值，否则会失败。
  • 如果要删除外键（名 fk_foo），使用工具的时候外键名要加下划线，比如--alter "DROP FOREIGN KEY _fk_foo"

  • D=db_name,t=table_name
    指定要ddl的数据库名和表名

  • --max-load
    默认为Threads_running=25。每个chunk拷贝完后，会检查 SHOW GLOBAL STATUS 的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式： status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会这只其为当前值的120%。
    因为拷贝行有可能会给部分行上锁，Threads_running 是判断当前数据库负载的绝佳指标。

  • --max-lag
    默认1s。每个chunk拷贝完成后，会查看所有复制Slave的延迟情况（Seconds_Behind_Master）。要是延迟大于该值，则暂停复制数据，直到所有从的滞后小于这个值。--check-interval配合使用，指定出现从库滞后超过 max-lag，则该工具将睡眠多长时间，默认1s，再检查。如--max-lag=5 --check-interval=2。
    熟悉percona-toolkit的人都知道--recursion-method可以用来指定从库dsn记录。另外，如果从库被停止，将会永远等待，直到从开始同步，并且延迟小于该值。

  • --chunk-time
    默认0.5s，即拷贝数据行的时候，为了尽量保证0.5s内拷完一个chunk，动态调整chunk-size的大小，以适应服务器性能的变化。
    也可以通过另外一个选项--chunk-size禁止动态调整，即每次固定拷贝 1k 行，如果指定则默认1000行，且比 chunk-time 优先生效

  • --set-vars
    使用pt-osc进行ddl要开一个session去操作，set-vars可以在执行alter之前设定这些变量，比如默认会设置--set-vars "wait_timeout=10000,innodb_lock_wait_timeout=1,lock_wait_timeout=60"。
    因为使用pt-osc之后ddl的速度会变慢，所以预计2.5h只能还不能改完，记得加大wait_timeout。

  • --dry-run
    创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节，和--print配合最佳。。

  • --execute
    确定修改表，则指定该参数。真正执行alter。–dry-run与–execute必须指定一个，二者相互排斥

3. 使用疑惑（限制）

3.1 原表上不能有触发器存在

这个很容易理解，pt-osc会在原表上创建3个触发器，而一个表上不能同时有2个相同类型的触发器，为简单通用起见，只能一棍子打死。
所以如果要让它支持有触发器存在的表也是可以实现的，思路就是：先找到原表触发器定义；重写原表触发器；最后阶段将原表触发器定义应用到新表。

3.2 通过触发器写数据到临时新表，会不会出现数据不一致或异常

这其实是我的一个顾虑，因为如果update t1，触发update t2，但这条数据还没copy到t2，不就有异常了吗？后台通过打开general_log，看到它创建的触发器：

1 2 3 4 5 6 7 8 9 10 11 12

6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETE ON `confluence`.`sbtest3` FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new` WHERE `confluence`.`_sbtest3_new`.`id` <=> OLD.`id` 6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_upd` AFTER UPDATE ON `confluence`.`sbtest3` FOR EACH ROW REPLACE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) 6165 Query CREATE TRIGGER `pt_osc_confluence_sbtest3_ins` AFTER INSERT ON `confluence`.`sbtest3` FOR EACH ROW REPLACE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) 并且copy操作是： 6165 Query INSERT LOW_PRIORITY IGNORE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `confluence`.`sbtest3` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4692805')) AND ((`id` <= '4718680')) LOCK IN SHARE MODE /*pt-online-schema-change 46459 copy nibble*/

 

在原表上update，新临时表上是replace into整行数据，所以达到有则更新，无则插入。同时配合后面的 insert ignore，保证这条数据不会因为重复而失败。

3.3 为什么外键那么特殊

假设 t1 是要修改的表，t2 有外键依赖于 t1，_t1_new 是 alter t1 产生的新临时表。
这里的外键不是看t1上是否存在外键，而是作为子表的 t2。主要问题在 rename t1 时，t1“不存在”导致t2的外键认为参考失败，不允许rename。
pt-osc提供--alter-foreign-keys-method选项来决定怎么处理这种情况：

• rebuild_constraints，优先采用这种方式
  • 它先通过 alter table t2 drop fk1,add _fk1 重建外键参考，指向新表
  • 再 rename t1 t1_old, _t1_new t1 ，交换表名，不影响客户端
  • 删除旧表 t1_old
    但如果字表t2太大，以致alter操作可能耗时过长，有可能会强制选择 drop_swap。
    涉及的主要方法在 pt-online-schema-change 文件的 determine_alter_fk_method, rebuild_constraints, swap_tables三个函数中。
• drop_swap，
  • 禁用t2表外键约束检查 FOREIGN_KEY_CHECKS=0
  • 然后 drop t1 原表
  • 再 rename _t1_new t1
    这种方式速度更快，也不会阻塞请求。但有风险，第一，drop表的瞬间到rename过程，原表t1是不存在的，遇到请求会报错；第二，如果因为bug或某种原因，旧表已删，新表rename失败，那就太晚了，但这种情况很少见。
    我们的开发规范决定，即使表间存在外键参考关系，也不通过表定义强制约束。

3.4 在使用之前需要对磁盘容量进行评估

使用OSC会使增加一倍的空间，包括索引
而且在 Row Based Replication 下，还会写一份binlog。不要想当然使用--set-vars去设置 sql_log_bin=0，因为在这个session级别，alter语句也要在从库上执行，除非你对从库另有打算。

4. 使用 pt-osc原生 5.6 online ddl相比，如何选择

• online ddl在必须copy table时成本较高，不宜采用
• pt-osc工具在存在触发器时，不适用
• 修改索引、外键、列名时，优先采用online ddl，并指定 ALGORITHM=INPLACE
• 其它情况使用pt-osc，虽然存在copy data
• pt-osc比online ddl要慢一倍左右，因为它是根据负载调整的
• 无论哪种方式都选择的业务低峰期执行
• 特殊情况需要利用主从特性，先alter从库，主备切换，再改原主库

5.使用pt-osc修改主键时注意

 

使用 pt-online-schema-change 做在线ddl最添加普通索引、列，修改列类型、添加默认值等使用比较常规，但涉及到要修改的是主键时就有点棘手。在我修改线上实例过程中，有这样的需求，不妨先思考一下怎么做才好：

1	原表上有个复合主键，现在要添加一个自增id作为主键，如何进行

 

 

会涉及到以下修改动作：

1. 删除复合主键定义
2. 添加新的自增主键
3. 原复合主键字段，修改成唯一索引

如果你够聪明，应该会把这三个操作放在同一个 alter table 命令执行。percona手册里有两个地方对修改主键进行了特殊注解：

–alter
A notable exception is when a PRIMARY KEY or UNIQUE INDEX is being created from existing columns as part of the ALTER clause; in that case it will use these column(s) for the DELETE trigger.

–[no]check-alter

• DROP PRIMARY KEY
  If –alter contain DROP PRIMARY KEY (case- and space-insensitive), a warning is printed and the tool exits unless –dry-run is specified. Altering the primary key can be dangerous, but the tool can handle it. The tool’s triggers, particularly the DELETE trigger, are most affected by altering the primary key because the tool prefers to use the primary key for its triggers. You should first run the tool with –dry-run and –print and verify that the triggers are correct.

由上一篇文章 pt-online-schema-change使用说明、限制与比较 可知，pt-osc会在原表t1上创建 AFTER DELETE/UPDATE/INSERT 三个触发器，修改主键影响最大的就是 DELETE 触发器：新表t2上的主键字段在旧表t1上不存在，无法根据主键条件触发删除新表t2数据。but the tool can handle it，原因是pt-osc把触发器改成了下面的形式：

1 2 3 4

CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETE ON `confluence`.`sbtest3` FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new` WHERE `confluence`.`_sbtest3_new`.`id` <=> OLD.`id` AND `confluence`.`_sbtest3_new`.`k` <=> OLD.`k` 注：sbtest3表上以(id,k)作为复合主键

 

但是如果id或k列上没有索引，这个删除的代价非常高，所以一定要同时添加复合（唯一）索引 (id,k) .

而对于INSERT,UPDATE的触发器，依然是 REPLACE INTO语法，因为它采用的是先插入，如果违反主键或唯一约束，则根据主键或意义约束删除这条数据，再执行插入。（但是注意你不能依赖于新表的主键递增，因为如果原表有update，新表就会先插入这一条，导致id与原表记录所在顺序不一样）

所以如果使用pt-osc去修改删除主键，务必同时添加原主键为 UNIQUE KEY，否则很有可能导致性能问题：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

$ pt-online-schema-change --user=ecuser --password=ecuser --host=10.0.201.34 \ --alter "DROP PRIMARY KEY,add column pk int auto_increment primary key,add unique key uk_id_k(id,k)" \ D=confluence,t=sbtest3 --print --dry-run --alter contains 'DROP PRIMARY KEY'. Dropping and altering the primary key can be dangerous, especially if the original table does not have other unique indexes. ==>注意 dry-run的输出 ALTER TABLE `confluence`.`_sbtest3_new` DROP PRIMARY KEY,add column pk int auto_increment primary key,add unique key uk_id_k(id,k) Altered `confluence`.`_sbtest3_new` OK. Using original table index PRIMARY for the DELETE trigger instead of new table index PRIMARY because ==> 使用原表主键值判断 the new table index uses column pk which does not exist in the original table. CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETE ON `confluence`.`sbtest3` FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new` WHERE `confluence`.`_sbtest3_new`.`id` <=> OLD.`id` AND `confluence`.`_sbtest3_new`.`k` <=> OLD.`k`

 

 

5.示例

• 添加新列
  完整输出过程

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

  [root@ssd-34 sysbench]# pt-online-schema-change --user=user --password=password --host=10.0.201.34 --alter "ADD COLUMN f_id int default 0" D=confluence,t=sbtest3 --print --execute No slaves found. See --recursion-method if host ssd-34 has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `confluence`.`sbtest3`... Creating new table... ==> 创建新表 CREATE TABLE `confluence`.`_sbtest3_new` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8_bin NOT NULL DEFAULT '', `pad` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_3` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin MAX_ROWS=1000000 Created new table confluence._sbtest3_new OK. Altering new table... ==> 使用ddl修改新表结构 ALTER TABLE `confluence`.`_sbtest3_new` ADD COLUMN f_id int default 0 Altered `confluence`.`_sbtest3_new` OK. 2016-05-24T20:54:23 Creating triggers... ==> 在旧表上创建3个触发器 CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETE ON `confluence`.`sbtest3` FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new` WHERE `confluence`.`_sbtest3_new`.`id` <=> OLD.`id` CREATE TRIGGER `pt_osc_confluence_sbtest3_upd` AFTER UPDATE ON `confluence`.`sbtest3` FOR EACH ROW REPLACE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) CREATE TRIGGER `pt_osc_confluence_sbtest3_ins` AFTER INSERT ON `confluence`.`sbtest3` FOR EACH ROW REPLACE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) 2016-05-24T20:54:23 Created triggers OK. 2016-05-24T20:54:23 Copying approximately 4485573 rows... ==> 分块拷贝数据到新表 INSERT LOW_PRIORITY IGNORE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `confluence`.`sbtest3` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 44155 copy nibble*/ SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `confluence`.`sbtest3` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/ Copying `confluence`.`sbtest3`: 36% 00:52 remain Copying `confluence`.`sbtest3`: 69% 00:26 remain 2016-05-24T20:56:01 Copied rows OK. 2016-05-24T20:56:01 Analyzing new table... 2016-05-24T20:56:01 Swapping tables... ==> 交换新旧表 RENAME TABLE `confluence`.`sbtest3` TO `confluence`.`_sbtest3_old`, `confluence`.`_sbtest3_new` TO `confluence`.`sbtest3` 2016-05-24T20:56:01 Swapped original and new tables OK. 2016-05-24T20:56:01 Dropping old table... ==> 删除旧表 DROP TABLE IF EXISTS `confluence`.`_sbtest3_old` 2016-05-24T20:56:02 Dropped old table `confluence`.`_sbtest3_old` OK. 2016-05-24T20:56:02 Dropping triggers... DROP TRIGGER IF EXISTS `confluence`.`pt_osc_confluence_sbtest3_del`; DROP TRIGGER IF EXISTS `confluence`.`pt_osc_confluence_sbtest3_upd`; DROP TRIGGER IF EXISTS `confluence`.`pt_osc_confluence_sbtest3_ins`; 2016-05-24T20:56:02 Dropped triggers OK. Successfully altered `confluence`.`sbtest3`.

• 修改列类型

  1 2 3 4 5 6 7

  pt-online-schema-change h=10.0.201.34,P=3306,u=jacky,p=xxx,D=confluence,t=sbtest3 \ --alter "CHANGE pad f_pad varchar(60) NOT NULL DEFAULT '' " \ --print --dry-run pt-online-schema-change -ujacky -p xxx -h "10.0.201.34" D=confluence,t=sbtest3 \ --alter "CHANGE pad f_pad varchar(60) NOT NULL DEFAULT '' " \ --execute

• 添加删除索引
  放后台执行

  1 2 3

  pt-online-schema-change --user=user --ask-pass --host=10.0.201.34 \ --alter "DROP KEY cid, ADD KEY idx_corpid_userid(f_corp_id,f_user_id) " \ D=confluence,t=sbtest3 --print --execute

• 修改主键

在我的环境里有不少表设计之初没有自增id，而是采用复合主键，pt-osc 对删除、添加主键会特殊处理，详见 这里 。

6. 错误处理

1. 存在trigger

1 2 3

[zx@mysql-5 ~]$ pt-online-schema-change -u user -p password -h 10.0.200.195 \ --alter="MODIFY COLUMN f_receiver varchar(128) NOT NULL DEFAULT '' AFTER f_user_id" --dry-run D=db_name,t=table_name The table `db_name`.`table_name` has triggers. This tool needs to create its own triggers, so the table cannot already have triggers.

 

表上存在触发器，不适用。

2. no-version-check

1 2 3

$ pt-online-schema-change -uuser -ppassword --alter "add key id_provice(f_provice)" \ D=db_name,t=tb_name -h rdsxxxxxx.mysql.rds.aliyuncs.com Can't use an undefined value as an ARRAY reference at /usr/bin/pt-online-schema-change line 7335.

 

这个错误在阿里云RDS上执行时出现的，我以为是我哪里语法写错了，但拿到原生5.6的版本上就没问题了，加上--no-version-check选项就好了，见 https://help.aliyun.com/knowledge_detail/13098164.html ，没深究，应该是pt去验证mysql server版本的时候从rds拿到的信息不对，导致格式出错。

参考

• refman pt-online-schema-change
• RDS MySQL 如何使用 Percona Toolkit
• percona-toolkit 之 【pt-online-schema-change】说明
• Avoiding MySQL ALTER table downtime
• MySQL Online DDL和NoSQL Schemaless Design