Chinaunix首页 | 论坛 | 博客
  • 博客访问: 589829
  • 博文数量: 38
  • 博客积分: 587
  • 博客等级: 中士
  • 技术积分: 579
  • 用 户 组: 普通用户
  • 注册时间: 2011-10-17 14:32
文章存档

2013年(15)

2012年(23)

分类: Mysql/postgreSQL

2013-06-04 16:43:55

 
【业务背景】
    刷新服务的一张主表和一张归档表,分别有1.5KW行和4KW行,都要添加几个字段;由于开发急着上线,且业务不能接受业务中断,之前类似大表变更都在凌晨处理。
所以需要对这两个表进行在线变更,目前两个常用工具:Percona的pt-online-change-schema 和 FaceBook的php写的osc工具;后者使用比较复杂,所以选择pt-online-change-schema工具(简称pt-osc)

【线上使用情况】
   新工具使用,进行反复测试,和对线上数据导到线下进行测试,预估操作时间和风险,及数据恢复方案。
已在生产环境中MySQL5.1和MySQL5.5中正常使用,pt-osc版本2.1.5

[root@mysql bak]# pt-online-schema-change  --host=127.0.0.1 --port=3306 --user=root --password=xxxxx  --nodrop-old-table  --charset=utf8   --lock-wait-timeout=51 --max-load Threads_running=100 --alter="add cityid bigint(20) DEFAULT NULL COMMENT '城市id' " D=dbname,t=tbname --execute

Altering `dbname`.`tbname `...
Creating new table...
Created new table 
dbname._tbname_new OK.
Altering new table...
Altered `
dbname`.`_tbname_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 14118609 rows...
Copying `
dbname`.`tbname`:   4% 10:33 remain
Copying `dbname`.`tbname`:   8% 10:25 remain
--------------------------------省略----------------------------------------------------
Copying `
dbname`.`tbname`:  97% 00:22 remain
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `
dbname`.`tbname`.

【原理简述】
为方便说明,把要修改表结构的表名设定为:tbname
1、创建一张结构相同的新表_tbname_new, 并修改其表结构。
2、原表的更新操作同步到新表: 在原表tbname上创建三个trigger:
   1)  pt_osc_库名_tbname_ins trigger保证原表的insert操作同步到新表,CREATE TRIGGER `pt_osc_库名_tbname_ins ` AFTER INSERT ON xxx  FOR EACH ROW REPLACE INTO
    2)  pt_osc_库名_tbname_upd trigger保证原表的update操作同步到新表,CREATE TRIGGER `pt_osc_库名_tbname_upd` AFTER UPDATE ON xxx  FOR EACH ROW REPLACE INTO
    3)  pt_osc_库名_tbname_del 保证原表的delete操作同步到新表,  CREATE  TRIGGER `pt_osc_库名_tbname_del` AFTER DELETE ON xxx  FOR EACH ROW DELETE IGNORE FROM
通过以上三个trigger,在copy数据的过程中,原表的所以更新操作,最终都会同步到新表中。
3、copy原表数据到新表: 使用insert into _tbname_new select * from tbname,一小块一块地从原表copy数据到new table.
4、交换原表和新表, 交换的操作:
RENAME TABLE `tbname` TO `test`.`_tbname_old`, `_tbname_new` TO `tbname`;
如果pt-osc不指定--nodrop-old-table参数,当swap tables完成后,会接着删除_tb_name_old.
DROP TABLE IF EXISTS `_tbname_old`
5、删除三个triggers
DROP TRIGGER IF EXISTS `库名`.`pt_osc_库名_tbname_ins`;
DROP TRIGGER IF EXISTS `库名`.`pt_osc_库名_tbname_del`;
DROP TRIGGER IF EXISTS `库名`.`pt_osc_库名_tbname_upd`;

【使用注意事项】
1、pt-osc在处理存在外键约束的表,有可能出现异常,pt-osc它爸Baron也提过,所以先检查是否有外键(有外键的,尽量不要使用)。
select CONSTRAINT_NAME,UNIQUE_CONSTRAINT_NAME,TABLE_NAME,REFERENCED_TABLE_NAME from REFERENTIAL_CONSTRAINTS where REFERENCED_TABLE_NAME='tb_name' or TABLE_NAME='tb_name';

2、Failed to SET SESSION innodb_lock_wait_timeout=1: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable。
当在built-in innodb的MySQL server执行时,如果不指定--lock-wait-timeout, pt执行会出现错误的;因为built-in innodb的innodb_lock_wait_timeout不能动态调整,pt执行会退出;
处理方法: pt显示指定--lock-wait-timeout参数,此值要大于MySQL server的innodb_lock_wait_timeout的值, 因为线上默认为50,所我这里指定  --lock-wait-timeout=51

3、保留原始表: --nodrop-old-table 这个参数设置后,在表结构修改完成后,最后不会删除原表,表名为“_表名_old", 这样回滚方便。
如果不指定此参数,在FS为ext3时,建议为原表ibd创建hardlink(硬连接),然后再使用truncate工具删除ibd大文件(参考维西的:http://www.taobaodba.com/html/1505_large-tbl-drop.html)。
因为drop table时,在unlink ibd文件这个过程中,都是的持有全局的mutex.

4、注意指定字符集 --charset=utf8  

5、--max-load Threads_running=100 此参数是为防止pt-online-schema-change给MySQL Server太大压力;pt在每完成一次数据插入到新表,都会检查此参数指定的状态值,默认为Threads_running=25 ,如果达到此阀值,pt会进入等待状态,
后续会再次检查,直到下降到指定的阀值下,再接着执行。

6、--alter="" 只把常规的alter table tbanme去掉,保留后面部分;可以一次性修改多个字段, 不能用于alter table rename子句。

【注意】
可能因场景不一样,会导致异常 ,所以在使用,一定要把相关数据在线下进行反复测试,制定好恢复计划。

【参考】

 

阅读(2195) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册