ITPub博客

首页 > 数据库 > MySQL > MYSQL在线大表DDL操作

MYSQL在线大表DDL操作

原创 MySQL 作者:Jane_Hoo2015 时间:2015-12-04 09:00:11 0 删除 编辑

作为DBA,我们也常常会碰到这样的需求:需要在不影响线上业务的情况下给表添加一个字段或索引。如果是一张只有几百或几千条记录的小表,这样的需求是非常容易解决的。但如果所管理的表数据量已经上亿、而且应用与数据库交互非常频繁,不允许停机窗口的出现,这样的需求又该如何满足?
大多数的alter table操作都会涉及lock-->copy to new table-->rename-->unlock的过程,锁表时间会很长,而且alter table 的process不可被kill,一旦执行就不可回退。
在MySQL5.5和之前版本,在运行的生产环境对大表(超过数百万纪录)执行Alter操作是一件很困难的事情。因为将重建表和锁表,影响用户者的使用。
从MySQL5.6开始,Online DDL特性被引进。他增强了很多种类的Alter Table操作避免拷贝表和锁表,在运行Alter操作的同时允许运行select,insert,update,delete语句。因此在最新版本,我们可以通过使用ALGORITHM和LOCK选项抑制文件拷贝和加锁。
但是即使在MySQL5.6,仍然有一些Alter操作(增加/删除列,增加/删除主键,改变数据类型等)需要重建表。

虽然mysql5.6对atler table操作有了很大改进,但仍有很多限制,并不建议直接在线上使用。如果线上有DDL的需求我们建议使用以下两种方式:
1、主从架构轮询修改
2、使用在线修改工具online-schema-change
对于第一种方式,使用的前提是,你的数据库架构是一个集群,如果不是,也就无所谓的轮询修改。修改的原理就是利用主从服务,在应用无感知的情况下,得到停机窗口,进行修改。
今天我们着重讲第二种方式,利用第三方工具实现在线大表的DDL操作。这种方式在alter操作更改表结构的时候不用锁定表,也就是说执行alter的时候不会阻塞写和读取操作。

工作原理:
创建一个和你要执行alter操作的表一样的空表结构,执行表结构修改,然后从原表中copy原始数据到表结构修改后的表,当数据copy完成以后就会将原表移走,用新表代替原表,默认动作是将原表drop掉。在copy数据的过程中,任何在原表的更新操作都会更新到新表,因为这个工具在会在原表上创建触发器,触发器会将在原表上更新的内容更新到新表。如果表中已经定义了触发器这个工具就不能工作了。

注意点:
1、操作的表必须有主键或唯一索引否则报错。
2、如果表有外键,除非使用 --alter-foreign-keys-method 指定特定的值,否则工具不予执行。
3、当业务量较大时,修改操作会等待没有数据修改后,执行最后的rename操作。因此,在修改表结构时,应该尽量选择在业务相对空闲时,至少修改表上的数据操作较低时,执行较为妥当。
4、由于可能存在一定的风险,在操作之前,建议对数据表进行备份,可以使得操作更安全、可靠。
5、当是主从环境,不在乎从的延迟,则需要加--recursion-method=none参数。当需要尽可能的对服务产生小的影响,则需要加上--max-load参数。

环境搭建:
安装依赖环境
##Install DBI
wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
tar -zxvf DBI-1.625.tar.gz
cd DBI-1.625
perl Makefile.PL
make
make install

##Install DBD::Mysql
wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.023.tar.gz
tar -zxvf DBD-mysql-4.023.tar.gz
cd DBD-mysql-4.023
perl Makefile.PL
make
make install

安装percona-toolkit:
##Install percona-toolkit
wget percona.com/get/percona-toolkit.tar.gz
tar -zxvf percona-toolkit-2.2.16.tar.gz
cd percona-toolkit-2.2.16
perl Makefile.PL
make
make install

常用操作:
添加字段
[root@rac1 bin]#  ./pt-online-schema-change -uroot  -pxxx --alter='add column col1_test int' --execute D=test,t=t_xxx_compensate
修改字段
[root@rac1 bin]# ./pt-online-schema-change  -uroot  -pxxx  --alter='MODIFY COLUMN col1_test TINYINT NOT NULL DEFAULT 0 ' --execute D=test,t=t_xxx_compensate              
改字段名
[root@rac1 bin]# ./pt-online-schema-change  -uroot  -pxxx  --alter='CHANGE COLUMN col1_test address varchar(30)' --execute D=test,t=t_xxx_compensate  
删除字段        
[root@rac1 bin]# ./pt-online-schema-change  -uroot  -pxxx  --alter='drop  column  address ' --execute D=test,t=t_xxx_compensate  
添加索引
[root@rac1 bin]# ./pt-online-schema-change -uroot  -pxxx --alter='add key indx_test(col1_test) ' --execute D=test,t=t_xxx_compensate
删除索引
[root@rac1 bin]# ./pt-online-schema-change -uroot  -pxxx --alter='DROP INDEX indx_test' --execute D=test,t=t_xxx_compensate

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30536723/viewspace-1853117/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2015-10-21

  • 博文量
    2
  • 访问量
    5724