mysql pt-online-schema-change工具的使用方法

OSC是DBA比较熟悉的工具之一,也是PT的TOOLKIT里面最重量级的工具.
在mysql-5.6支持OLDDL以后,大部分人可能觉着这个工具已经没有意义了,其实在一些特殊环境下,这个工具还是很有用的.
这篇文章除了介绍普通青年如何使用OSC，还会介绍一种文艺青年使用OSC的方法,那就是用来实现master到slave的数据差异恢复。

+++++++++++++++++++++++++++普通青年分割线+++++++++++++++++++++++++++++++++++++++
此段内容转自：http://www.dataguru.cn/article-3460-1.html
目前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的时候，原表是只能读不能写的。为此 perconal 推出一个工具 pt-online-schema-change ，其特点是修改过程中不会造成读写阻塞。
工作原理：
如果表有外键，除非使用 –alter-foreign-keys-method 指定特定的值，否则工具不予执行。
1 创建一个和你要执行 alter 操作的表一样的空表结构。
2 执行表结构修改，然后从原表中的数据到copy到 表结构修改后的表，
3 在原表上创建触发器将 copy 数据的过程中，在原表的更新操作 更新到新表.
注意:如果表中已经定义了触发器这个工具就不能工作了。
4 copy 完成以后，用rename table 新表代替原表，默认删除原表。

用法介绍：
pt-online-schema-change [OPTIONS] DSN
options 可以自行查看 help，DNS 为你要操作的数据库和表。这里有两个参数需要介绍一下：
–dry-run
这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。
–execute
这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。注意：如果不加这个参数，这个工具会在执行一些检查后退出。

依赖条件
1操作的表必须有主键否则 报如下错误。

[root@rac1 bin]#  ./pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='add column vid int ' --execute D=houyi,t=ga
Cannot connect to D=houyi,h=127.0.0.1,p=...,u=root
Cannot chunk the original table `houyi`.`ga`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5353.
测试例子：
1 添加字段

[root@rac1 bin]#  ./pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='add column vid int ' --execute D=houyi,t=ga
Cannot connect to D=houyi,h=127.0.0.1,p=...,u=root
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 `houyi`.`ga`...
Creating new table...
Created new table houyi._ga_new OK.
Altering new table...
Altered `houyi`.`_ga_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 746279 rows...
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `houyi`.`_ga_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `houyi`.`ga`.
2 添加索引

[root@rac1 bin]# ./pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='add key indx_vid(vid) ' --execute D=houyi,t=ga
3 删除字段

[root@rac1 bin]# ./pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='drop  column vid ' --execute D=houyi,t=ga
++++++++++++++++++++++++++++文艺青年分割线+++++++++++++++++++++++++++
所谓的文艺用法,就是通过OSC实现slave和master数据差异时候的恢复.有人说,这个是pt-table-sync该干的事情.但是在表数据差异较大的时候,使用OSC可能效率更好,而且更加简单可靠.
OSC如何实现master到slave的数据差异恢复的?
由于OSC的原理是新建表和使用触发器.然后把原表的数据insert into select from的方式导入新表.如果这个时候,我们把binlog改成row格式.那么insert into记录的肯定是源表的数据了.触发器在row格式的时候,也是在日志中记录的源表数据.也就是说,通过OSC可以逻辑的,无阻塞的把源表的数据同步到所有slave.

pt-online-schema-change –set-vars ‘binlog_format=ROW’ –alter ‘engine=INNODB’ D=test,t=tt1,h=localhost –execute
如果你本来就工作在row格式下,那么–set-vars ‘binlog_format=ROW’就可以不设置了.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
详细原理解剖,转自http://hi.baidu.com/ytjwt/item/7ea9c637c29079f6e6bb7a0d
pt-online-schema-change在线更改表结构的实现核心有如下几个过程：
（注：在跟改过程中涉及到三个表：原表、tmp_table即作为原表导数据的临时表，old_table在最后rename 原表的结果表）
1、CREATE TABLE `$db`.`$tmp_tbl` LIKE`$db`.`$tbl`” 新建tmp_table，表结构同原表
2、在tmp_table上更改表结构为需要的表结构
3、在原表上建立三个触发器，如下：
（1）CREATETRIGGER mk_osc_del AFTER DELETE ON $table ” “FOR EACH ROW ”
“DELETE IGNORE FROM $new_table “”WHERE$new_table.$chunk_column = OLD.$chunk_column”;
（2）CREATETRIGGER mk_osc_ins AFTER INSERT ON $table ” “FOR EACH ROW ”
“REPLACEINTO $new_table ($columns) ” “VALUES($new_values)”;
（3）CREATETRIGGER mk_osc_upd AFTER UPDATE ON $table ” “FOR EACH ROW ”
“REPLACE INTO $new_table ($columns) “”VALUES ($new_values)”;

我们可以看到这三个触发器分别对应于INSERT、UPDATE、DELETE三种操作：
（1）mk_osc_del，DELETE操作，我们注意到DELETEIGNORE，当新有数据时，我们才进行操作，也就是说，当在后续导入过程中，如果删除的这个数据还未导入到新表，那么我们可以不在新表执行操作，因为在以后的导入过程中，原表中改行数据已经被删除，已经没有数据，那么他也就不会导入到新表中；
（2）mk_osc_ins，INSERT操作，所有的INSERT INTO全部转换为REPLACEINTO，为了确保数据的一致性，当有新数据插入到原表时，如果触发器还未把原表数据未同步到新表，这条数据已经被导入到新表了，那么我们就可以利用replaceinto进行覆盖，这样数据也是一致的
（3）mk_osc_upd UPDATE操作，所有的UPDATE也转换为REPLACEINTO，因为当跟新的数据的行还未同步到新表时，新表是不存在这条记录的，那么我们就只能插入该条数据，如果已经同步到新表了，那么也可以进行覆盖插入，所有数据与原表也是一致的；
我们也能看出上述的精髓也就这这几条replaceinto操作，正是因为这几条replaceinto才能保证数据的一致性
4、拷贝原表数据到临时表中，在脚本中使用如下语句
INSERT IGNORE INTO $to_table ($columns) ” “SELECT $columns FROM $from_table “”WHERE ($chunks->[$chunkno])”，我们能看到他是通过一些查询（基本为主键、唯一键值）分批把数据导入到新的表中，在导入前，我们能通过参数–chunk-size对每次导入行数进行控制，已减少对原表的锁定时间，并且在导入时，我们能通过—sleep参数控制，在每个chunk导入后与下一次chunk导入开始前sleep一会，sleep时间越长，对于磁盘IO的冲击就越小
5、Rename 原表到old表中，在把临时表Rename为原表，
“RENAME TABLE `$db`.`$tmp_tbl`TO `$db`.`$tbl`”; 在rename过程，其实我们还是会导致写入读取堵塞的，所以从严格意思上说，我们的OSC也不是对线上环境没有一点影响，但由于rename操作只是一个修改名字的过程，也只会修改一些表的信息，基本是瞬间结束，故对线上影响不太大
6、清理以上过程中的不再使用的数据，如OLD表。