首页 > 问答 > 关键词  > mysql最新资讯  > 正文

mysql怎么处理大表在不停机的情况下增加字段

2020-06-29 09:21 · 稿源:数据库干货铺

MySQL中给一张千万甚至更大量级的表添加字段一直是比较头疼的问题,遇到此情况通常该如果处理?本文通过常见的三种场景进行案例说明。

1、 环境准备

数据库版本:5.7.25-28(Percona 分支)

服务器配置:3 台centos7 虚拟机,配置均为2CPU 2G内存

数据库架构: 1 主 2 从的MHA架构(为了方便主从切换场景的演示,如开启GTID,则两节点即可),关于MHA搭建可参考此文MySQL高可用之MHA集群部署

准备测试表: 创建一张2kw记录的表,快速创建的方法可以快速创建连续数

本次对存储过程稍作修改,多添加几个字段,存储过程如下:

DELIMITER$$CREATEPROCEDURE`sp_createNum`(cntINT)BEGIN
DECLAREiINTDEFAULT1;DROPTABLEifexiststb_add_columns;CREATETABLEifnotexiststb_add_columns(idintprimarykey,col1int,col2varchar(32));INSERTINTOtb_add_columns(id,col1,col2)SELECTiasid,i%7ascol1,md5(i)ascol2;
WHILEi<cntDOBEGIN
INSERTINTOtb_add_columns(id,col1,col2)SELECTid+iasid,(id+i)%7ascol1,md5(id+i)ascol2FROMtb_add_columnsWHEREid<=cnt-i;SETi=i*2;END;ENDWHILE;END$$
DELIMITER;

调用存储过程,完成测试表及测试数据的创建。

mysql>callsp_createNum(20000000);

2. 直接添加字段

使用场景: 在系统不繁忙或者该表访问不多的情况下,如符合ONLINE DDL的情况下,可以直接添加。

模拟场景:创建一个测试脚本,每10s访问该表随机一条记录,然后给该表添加字段

访问脚本如下

#!/bin/bash
#gjcforiin{1..1000000000}#访问次数1000000000,按需调整即可do
id=$RANDOM#生成随机数
mysql-uroot-p'123456'--socket=/data/mysql3306/tmp/mysql.sock-e"selecta.*,now()fromtestdb.tb_add_columnsawhereid="$id#访问数据sleep10#暂停10sdone

运行脚本

shtest.sh

给表添加字段

mysql>altertabletestdb.tb_add_columnsaddcol3int;

此时,访问正常。

附ONLINE DDL的场景如下,建议DBA们必须弄清楚

(图片转载于https://blog.csdn.net/finalkof1983/article/details/88355314)

(图片转载于https://blog.csdn.net/finalkof1983/article/details/88355314)

3. 使用工具在线添加

虽然Online DDL添加字段时,表依旧可以读写,但是生产环境使用场景中对大表操作使用最多的还是使用工具pt-osc或gh-ost添加。

本文主要介绍 pt-osc(pt-online-schema-change) 来添加字段,该命令是Percona Toolkit工具中的使用频率最高的一种

关于Percona Toolkit的安装及主要使用可以参考五分钟学会Percona Toolkit 安装及使用

添加字段

root@mha1~]#pt-online-schema-change--alter"ADDCOLUMNcol4int"h=localhost,P=3306,p=123456,u=root,D=testdb,t=tb_add_columns,S=/data/mysql3306/tmp/mysql.sock--charset=utf8mb4--execute

主要过程如下:

1>CannotconnecttoA=utf8mb4,P=3306,S=/data/mysql3306/tmp/mysql.sock,h=192.168.28.132,p=...,u=root1>CannotconnecttoA=utf8mb4,P=3306,S=/data/mysql3306/tmp/mysql.sock,h=192.168.28.131,p=...,u=root
Noslavesfound.See--recursion-methodifhostmha1hasslaves.#因为使用的是socket方式连接数据库且未配置root远程连接账号,所以会有此提示
#Asoftwareupdateisavailable:
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,1Altering`testdb`.`tb_add_columns`...
Creatingnewtable...#创建中间表,表名为"_原表名_new"
Creatednewtabletestdb._tb_add_columns_newOK.
Alteringnewtable...#修改表,也就是在新表上添加字段,因新表无数据,因此很快加完
Altered`testdb`.`_tb_add_columns_new`OK.
2020-06-20T12:23:43Creatingtriggers...#创建触发器,用于在原表拷贝到新表的过程中原表有数据的变动(新增、修改、删除)时,也会自动同步至新表中2020-06-20T12:23:43CreatedtriggersOK.2020-06-20T12:23:43Copyingapproximately19920500rows...#拷贝数据,数据库量是统计信息里的,不准确
Copying`testdb`.`tb_add_columns`:11%03:50remain#分批拷贝数据(根据表的size切分每批拷贝多少数据),拷贝过程中可以用showprocesslist看到对应的sql
Copying`testdb`.`tb_add_columns`:22%03:22remain
Copying`testdb`.`tb_add_columns`:32%03:10remain
Copying`testdb`.`tb_add_columns`:42%02:45remain
Copying`testdb`.`tb_add_columns`:51%02:21remain
Copying`testdb`.`tb_add_columns`:62%01:48remain
Copying`testdb`.`tb_add_columns`:72%01:21remain
Copying`testdb`.`tb_add_columns`:81%00:53remain
Copying`testdb`.`tb_add_columns`:91%00:24remain2020-06-20T12:28:40CopiedrowsOK.#拷贝数据完成2020-06-20T12:28:40Analyzingnewtable...#优化新表2020-06-20T12:28:40Swappingtables...#交换表名,将原表改为"_原表名_old",然后把新表表名改为原表名2020-06-20T12:28:41SwappedoriginalandnewtablesOK.
2020-06-20T12:28:41Droppingoldtable...#删除旧表(也可以添加参数不删除旧表)2020-06-20T12:28:41Droppedoldtable`testdb`.`_tb_add_columns_old`OK.2020-06-20T12:28:41Droppingtriggers...#删除触发器2020-06-20T12:28:41DroppedtriggersOK.
Successfullyaltered`testdb`.`tb_add_columns`.#完成

修改过程中,读写均不受影响,大家可以写个程序包含读写的

注: 无论是直接添加字段还是用pt-osc添加字段,首先都得拿到该表的元数据锁,然后才能添加(包括pt-osc在创建触发器和最后交换表名时都涉及),因此,如果一张表是热表,读写特别频繁或者添加时被其他会话占用,则无法添加。

例如: 锁住一条记录

用pt-osc添加字段,会发现一直卡在创建触发器那一步

此时查看对应的SQL正在等待获取元数据锁

换成直接添加也一样,例如

当达到锁等待后将会报错放弃添加字段

mysql>altertabletestdb.tb_add_columnsaddcol5int;
ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction

对于此情况,需等待系统不繁忙情况下添加,或者使用后续的在从库创建再进行主从切换

4 先在从库修改,再进行主从切换

使用场景: 如果遇到上例中一张表数据量大且是热表(读写特别频繁),则可以考虑先在从库添加,再进行主从切换,切换后再将其他几个节点上添加字段。

先在从库添加(本文在备选节点添加)

mysql>altertabletestdb.tb_add_columnsaddcol5int;
QueryOK,0rowsaffected(1min1.91sec)
Records:0Duplicates:0Warnings:0

进行主从切换

使用MHA脚本进行在线切换

masterha_master_switch--conf=/etc/masterha/app1.conf--master_state=alive--orig_master_is_new_slave--new_master_host=192.168.28.131--new_master_port=3306

切换完成后再对其他节点添加字段

/*原主库上添加192.168.28.128*/mysql>altertabletestdb.tb_add_columnsaddcol5int;
QueryOK,0rowsaffected(1min8.36sec)
Records:0Duplicates:0Warnings:0/*另一个从库上添加192.168.28.132*/mysql>altertabletestdb.tb_add_columnsaddcol5int;
QueryOK,0rowsaffected(1min8.64sec)
Records:0Duplicates:0Warnings:0

这样就完成了字段添加。

5. 小结

生产环境MySQL添加或修改字段主要通过如下三种方式进行,实际使用中还有很多注意事项,大家要多多总结。

  • 直接添加

如果该表读写不频繁,数据量较小(通常1G以内或百万以内),直接添加即可(可以了解一下online ddl的知识)

  • 使用pt_osc添加

如果表较大 但是读写不是太大,且想尽量不影响原表的读写,可以用percona tools进行添加,相当于新建一张添加了字段的新表,再降原表的数据复制到新表中,复制历史数据期间的数据也会同步至新表,最后删除原表,将新表重命名为原表表名,实现字段添加

  • 先在从库添加 再进行主从切换

如果一张表数据量大且是热表(读写特别频繁),则可以考虑先在从库添加,再进行主从切换,切换后再将其他几个节点上添加字段

本文转自微信公众号:数据库干货铺

举报

  • 相关推荐
  • 大家在看
  • 《开源数据库生态发展研究报告》发布 GreatSQL为MySQL5.7最佳替代方案!

    随着数字化转型深入推进和数据量的爆炸式增长,行业应用对数据库的需求变化推动数据库技术加速创新。数据库作为数字经济基础底座,连接上层应用和底层基础资源,在数字经济时代展现出巨大的价值和潜能。四、国内开源数据库产业展望最后,针对我国MySQL技术路线开源数据库产业发展,报告进行以下展望:1、开源数据库发展应符合开源生态建设及产业引领要求,积极参与完善开源产业治理;2、加强相关方对开源协议认知,合法合规利用开源协议;3、利用国内MySQL现有技术生态,结合产业需求,加强独立演进开源分支的能力;4、大力推进开源数据库技术规范化、智能化发展。

  • 谷歌增加生成人工智能赔偿 但部分情况例外

    谷歌最近宣布已经在其生成式人工智能服务的服务条款中,增加了明确的赔偿语言,为付费用户提供了广泛的法律保护。这一举措获得了许多积极的报道。用户需要谨慎阅读条款细则,不能掉以轻心。

  • 美的换新品牌日火热开启,多重惊喜福利享不停

    在国家“双碳”战略指引下,绿色、低碳、智能化家电已成为家电行业创新发展和消费升级的主流趋势。 10 月 7 日,一年一度的美的换新品牌日在全国火热开启。即日起- 11 月 30 日,美的推出“绿电行动·跨品换新一站购”活动,通过以旧换新+亿级补贴的形式,带来多重惊喜钜惠福利,助力广大消费者焕新绿色美好生活,共同守护绿色家园。美的“绿电行动”,倡导绿色低碳�

  • 快手解锁看亚运新玩法:全民领金币,好礼兑不停

    亚运适逢国庆,盛会又遇盛典。自9月23日杭州第19届亚运会开幕以来,中国体育健儿在赛场上劈金斩银、奋勇争先,赛场外观众的热情也让这届亚运会成为了一场“全民参与”的体育盛事。作为此次杭州第19届亚运会持权转播商以及官方短视频分享平台,快手通过全场次的赛事点播、多档自制节目、丰富的互动玩法等,为观众带来更多视角、更加立体化的观赛体验。为了全方位�

  • 引起头痛的“幕后黑手”?这种意想不到的情况,你或许也经历过!

    你有没有在夜晚关灯后,决定刷多一条短视频就睡觉,却不料再次看向窗外已是天明?你是否曾“哭着”敲键盘也仍然坚持加班到深夜……在这样一次又一次的“无法抵抗”下,视疲劳便如影随形,对我们的眼睛造成诸多损害。理大将继续以其雄厚根基,与各界策略伙伴合作,力求造福香港、国家乃至全球。

  • 苹果iPhone 16系列将增加更大尺寸屏幕,增加机身重量

    自iPhone12ProMax发布以来,iPhone超大杯手机的屏幕尺寸一直保持在6.69英寸。明年发布的iPhone16系列手机将带来屏幕的变化,不仅仅是iPhone16Pro的屏幕尺寸有所增加,iPhone16ProMax也将配备更大尺寸的屏幕。iPhone16Pro和iPhone16ProMax将采用钛合金金属材料制成机身。

  • AI Score My Site:AI驱动的SEO工具

    AIScoreMySite是一款创新的工具,旨在帮助用户了解他们的网站在AI驱动的搜索引擎上的排名和可发现性,并提供相应的改进建议。在今天的数字化时代,网站的排名对于吸引流量和潜在客户至关重要。随着AI在搜索领域的广泛应用,利用SiteSpeakAI提升网站SEO将变得尤为关键。

  • 随着无人机交通量增加,研究者利用AI帮助避免碰撞

    随着无人机的商业化应用不断增加,未来几年400英尺以下低空区域的无人机交通量预计会大幅增长。到2027年美国将拥有近100万架商业无人机系统,用于快递送货、交通监控以及应急救援等任务。随着商业无人机的快速发展,利用AI和模拟为其交通管理提供决策支持,实现无人机系统的高效安全运作,是当前研究的重要方向。

  • iPhone 15预售情况曝光 郭明錤:这款卖得最好

    凤凰网科技讯北京时间9月18日消息,苹果公司的新一代iPhone15系列手机已在上周五开启预订。天风国际证券公司知名分析师郭明錤公布了首个周末的预售观察,称iPhone15ProMax需求强劲,华为的归来对iPhone产生了影响。这种影响不会表现在iPhone15的中国市场预购中,因为会预购iPhone的人本来就是苹果的核心用户或粉丝,他们不是华为手机目前的目标客户。

  • 过度疲劳的6种现上热搜:身体慢性疲劳自测来了 你中了几条

    #过度疲劳的6种表现#话题登上微博热搜。若未引起重视,未及时休息调整,那么小问题”可能会引发大问题”,严重威胁我们的身心健康甚至生命安全。

今日大家都在搜的词: