online ddl 工具之pt-online-schema-change
MySQL ddl 的问题现状
在运维mysql数据库时,我们总会对数据表进行ddl 变更,修改添加字段或者索引,对于mysql 而已,ddl 显然是一个令所有MySQL dba 诟病的一个功能,因为在MySQL中在对表进行ddl时,会锁表,当表比较小比如小于1w上时,对前端影响较小,当时遇到千万级别的表 就会影响前端应用对表的写操作。
目前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
详细的教程请参看:
http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html