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

发布时间：2022-06-28 14:00 所属栏目：115 来源：互联网

导读：下面来看一个mysql pt-online-schema-change工具的使用方法,希望这个例子能帮助到各位同学哦. OSC是DBA比较熟悉的工具之一,也是PT的TOOLKIT里面最重量级的工具,在mysql-5.6支持OLDDL以后,大部分人可能觉着这个工具已经没有意义了,其实在一些特殊环境下,这个

　　下面来看一个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,操作的表必须有主键否则报如下错误.

　　[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. --phpfensi.com
　　测试例子:

　　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 ”
　　（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表.

（编辑：ASP站长网）