阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL在线大表DDL操作

199次阅读
没有评论

共计 2883 个字符,预计需要花费 8 分钟才能阅读完成。

MySQL 在线大表 DDL 操作的方法:

1、主从架构轮询修改

a、主库会话级别的记录 binglog 的参数关闭

b、500\502 错误异常捕捉

c、检查备库的 second behind master 是否有延迟

d、varchar 有页分裂的情况,尽量减少 varchar 的长度

2、在线工具 online-schema-change

a、超过 1000w 行,速度会变慢(半小时左右)

参考链接:http://www.linuxidc.com/Linux/2016-08/134761.htm

作为 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

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2016-08/134771.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计2883字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中