下面来看一个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@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 
2. Cannot connect to D=houyi,h=127.0.0.1,p=...,u=root 
3. Cannot chunk the original table `houyi`.`ga`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5353. --phpfensi.com 

测试例子:

1 添加字段

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 
2. Cannot connect to D=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. Creating new table... 
11. Created new table houyi._ga_new OK. 
12. Altering new table... 
13. Altered `houyi`.`_ga_new` OK. 
14. Creating triggers... 
15. Created triggers OK. 
16. Copying approximately 746279 rows... 
17. Copied rows OK. 
18. Swapping tables... 
19. Swapped original and new tables OK. 
20. Dropping old table... 
21. Dropped old table `houyi`.`_ga_old` OK. 
22. Dropping triggers... 
23. Dropped triggers OK. 
24. Successfully altered `houyi`.`ga`. 

2 添加索引

1. [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 删除字段

1. [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. （1）CREATETRIGGER mk_osc_del AFTER DELETE ON $table ” “FOR EACH ROW ” 
2. （1）CREATETRIGGER mk_osc_del AFTER DELETE ON $table ” “FOR EACH ROW ” 
3. “DELETE IGNORE FROM $new_table “”WHERE$new_table.$chunk_column = OLD.$chunk_column”; 
4. （2）CREATETRIGGER mk_osc_ins AFTER INSERT ON $table ” “FOR EACH ROW ” 
5. “REPLACEINTO $new_table ($columns) ” “VALUES($new_values)”; 
6. （3）CREATETRIGGER mk_osc_upd AFTER UPDATE ON $table ” “FOR EACH ROW ” 
7. “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表.

Tags: pt-online-schema-change

分享到：