MySQL在线DDL工具pt-osc

2017-01-05 11:09:17来源:oschina作者:IT--小哥人点击

导读:


上篇文章讲过MySQL原生的Online DDL还是有很多限制的,还是会遇到data meta lock的问题等诸多不便,然后就有了我们今天的话题,通过pt-osc在线执行DDL。


一、pt-osc的工作原理

1、创建一个和源表一样表结构的新表


2、在新表执行DDL语句(空表嘛,所以。。。)


3、在源表创建三个触发器分别对应insert、update、delete操作


4、从源表拷贝数据到新表,拷贝过程中源表通过触发器把新的DML操作更新到新表中


5、rename源表到old表中,把新表rename为源表,默认最后删除源表


二、pt-osc工具的限制

1、源表不能有触发器存在


显然不是不能有任何触发器,只是不能有针对insert、update、delete的触发器存在,因为一个表上不能有两个相同类型的触发器

2、源表必须要有主键


源表没有主键会报错:


Cannot chunk the original table `houyi`.`ga`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5353.

3、源表有外键,必须使用--alter-foreign-keys-method指定特定的值


三、pt-osc与原生MySQL5.6 Online DDL对比

l Online DDL在必须copy table时成本较高,不宜采用


l Pt-osc在存在触发器时,不适用


l 其他情况使用pt-osc


l 选择在业务低峰期进行online ddl


四、pt-osc常用参数

--host=xxx --user=xxx --password=xxx 连接实例信息,缩写-h xxx -u xxx -p xxx,密码可以使用参数--ask-pass手动输入。

--alter 结构变更语句,不需要ALTER TABLE关键字。与原始ddl一样可以指定多个更改,用逗号分隔。

D=db_name,t=table_name 指定要ddl的数据库名和表名

--dry-run 创建和修改新表,但不会创建触发器、复制数据、和替换原表。并不真正执行,可以看到生成的执行语句,了解其执行步骤与细节,和--print配合最佳。。

--execute 确定修改表,则指定该参数。真正执行alter。–dry-run与–execute必须指定一个,二者相互排斥


五、pt-osc使用示例

1、添加新列


[root@bogon ~]# pt-online-schema-change --user=root --password=123456 --host=localhost --alter "add column age int(4) default 0" D=test,t=test01 --print --execute
No slaves found. See --recursion-method if host bogon has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `test`.`test01`...
Creating new table...
CREATE TABLE `test`.`_test01_new` (
`name` varchar(3) DEFAULT NULL,
`id` varchar(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Created new table test._test01_new OK.
Altering new table...
ALTER TABLE `test`.`_test01_new` add column age int(4) default 0
Altered `test`.`_test01_new` OK.
2016-11-30T02:54:32 Creating triggers...
CREATE TRIGGER `pt_osc_test_test01_del` AFTER DELETE ON `test`.`test01` FOR EACH ROW DELETE IGNORE FROM `test`.`_test01_new` WHERE `test`.`_test01_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_test_test01_upd` AFTER UPDATE ON `test`.`test01` FOR EACH ROW REPLACE INTO `test`.`_test01_new` (`name`, `id`) VALUES (NEW.`name`, NEW.`id`)
CREATE TRIGGER `pt_osc_test_test01_ins` AFTER INSERT ON `test`.`test01` FOR EACH ROW REPLACE INTO `test`.`_test01_new` (`name`, `id`) VALUES (NEW.`name`, NEW.`id`)
2016-11-30T02:54:32 Created triggers OK.
2016-11-30T02:54:32 Copying approximately 4 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_test01_new` (`name`, `id`) SELECT `name`, `id` FROM `test`.`test01` LOCK IN SHARE MODE /*pt-online-schema-change 21439 copy table*/
2016-11-30T02:54:32 Copied rows OK.
2016-11-30T02:54:32 Analyzing new table...
2016-11-30T02:54:32 Swapping tables...
RENAME TABLE `test`.`test01` TO `test`.`_test01_old`, `test`.`_test01_new` TO `test`.`test01`
2016-11-30T02:54:32 Swapped original and new tables OK.
2016-11-30T02:54:32 Dropping old table...
DROP TABLE IF EXISTS `test`.`_test01_old`
2016-11-30T02:54:32 Dropped old table `test`.`_test01_old` OK.
2016-11-30T02:54:32 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test01_del`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test01_upd`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test01_ins`;
2016-11-30T02:54:32 Dropped triggers OK.
Successfully altered `test`.`test01`.

2、修改列类型


[root@bogon ~]# pt-online-schema-change h=localhost,P=3306,u=root,p=123456,D=test,t=test01 --alter "change women age int(4) default 0" --print --execute --no-check-alter

3、添加删除索引


[root@bogon ~]# pt-online-schema-change h=localhost,P=3306,u=root,D=test,t=test01 --ask-pass --alter "drop key index_name,add key index_age(age)" --print --execute

4、修改主键


使用pt-osc去修改删除主键,务必同时添加原主键为 UNIQUE KEY,否则很有可能导致性能问题


[root@bogon ~]# pt-online-schema-change h=localhost,u=root,p=123456,D=test,t=test01 --alter "drop primary key,add primary key(age)" --print --execute --no-check-alter

七、报错案例

1、报错语句


The tool should handle this correctly, but you should test it first because if it fails the renamed columns' data will be lost! Specify --no-check-alter to disable this check and perform the --alter.

介个直接看着报错就可以解决了

2、报错语句


The table `db_name`.`table_name` has triggers. This tool needs to create its own triggers, so the table cannot already have triggers.

存在触发器,表不能存在触发器

4、pt-osc产生死锁


当一个事务在做DDL操作,一个事务在做DML操作,有可能会造成死锁

5、pt-osc导致丢表


当在做DDL的时候,还未提交,此时如果实例crash,就会导致表丢失。


参考文档:


https://my.oschina.net/kings0/blog/807871


http://www.cnblogs.com/zengkefu/category/854034.html


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


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


为了方便大家交流,本人开通了微信公众号,和QQ群291519319。喜欢技术的一起来交流吧



最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台