pt-online-schema-change工具文艺用法

PHPABC Mysql 529 次浏览 , , 没有评论

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表。

参考资料

http://www.dataguru.cn/article-3460-1.html

http://hi.baidu.com/ytjwt/item/7ea9c637c29079f6e6bb7a0d

http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

http://www.percona.com/blog/2014/09/17/syncing-mysql-slave-table-with-pt-online-schema-change/

http://www.percona.com/blog/2013/06/24/binlog_format-and-pt-online-schema-change/ 推荐阅读

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

Go