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

下面来看一个mysql pt-online-schema-change工具的使用方法,希望这个例子能帮助到各位同学哦.

OSC是DBA比较熟悉的工具之一,也是PT的TOOLKIT里面最重量级的工具,在mysql-5.6支持OLDDL以后,大部分人可能觉着这个工具已经没有意义了,其实在一些特殊环境下,这个工具还是很有用的.

这篇文章除了介绍普通青年如何使用OSC,还会介绍一种文艺青年使用OSC的方法,那就是用来实现master到slave的数据差异恢复.

目前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,操作的表必须有主键否则报如下错误.

1. [root@rac1bin]#./pt-online-schema-change-uroot-h10.250.7.50-pyang--alter='addcolumnvidint'--executeD=houyi,t=ga
2. CannotconnecttoD=houyi,h=127.0.0.1,p=...,u=root
3. Cannotchunktheoriginaltable`houyi`.`ga`:Thereisnogoodindexandthetableisoversized.at./pt-online-schema-changeline5353.--phpfensi.com

测试例子:

1 添加字段

1. [root@rac1bin]#./pt-online-schema-change-uroot-h10.250.7.50-pyang--alter='addcolumnvidint'--executeD=houyi,t=ga
2. CannotconnecttoD=houyi,h=127.0.0.1,p=...,u=root
3. Operation,tries,wait:
4. copy_rows,10,0.25
5. create_triggers,10,1
6. drop_triggers,10,1
7. swap_tables,10,1
8. update_foreign_keys,10,1
9. Altering`houyi`.`ga`...
10. Creatingnewtable...
11. Creatednewtablehouyi._ga_newOK.
12. Alteringnewtable...
13. Altered`houyi`.`_ga_new`OK.
14. Creatingtriggers...
15. CreatedtriggersOK.
16. Copyingapproximately746279rows...
17. CopiedrowsOK.
18. Swappingtables...
19. SwappedoriginalandnewtablesOK.
20. Droppingoldtable...
21. Droppedoldtable`houyi`.`_ga_old`OK.
22. Droppingtriggers...
23. DroppedtriggersOK.
24. Successfullyaltered`houyi`.`ga`.

2 添加索引

1. [root@rac1bin]#./pt-online-schema-change-uroot-h10.250.7.50-pyang--alter='addkeyindx_vid(vid)'--executeD=houyi,t=ga

3 删除字段

1. [root@rac1bin]#./pt-online-schema-change-uroot-h10.250.7.50-pyang--alter='dropcolumnvid'--executeD=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. （1）CREATETRIGGERmk_osc_delAFTERDELETEON$table”“FOREACHROW”
2. （1）CREATETRIGGERmk_osc_delAFTERDELETEON$table”“FOREACHROW”
3. “DELETEIGNOREFROM$new_table“”WHERE$new_table.$chunk_column=OLD.$chunk_column”;
4. （2）CREATETRIGGERmk_osc_insAFTERINSERTON$table”“FOREACHROW”
5. “REPLACEINTO$new_table($columns)”“VALUES($new_values)”;
6. （3）CREATETRIGGERmk_osc_updAFTERUPDATEON$table”“FOREACHROW”
7. “REPLACEINTO$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表.