MySQL5.6在线DDL不锁表(在线添加字段)

2023-12-23 00:08
文章标签 ddl 在线 mysql5.6 不锁表

本文主要是介绍MySQL5.6在线DDL不锁表(在线添加字段),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

http://www.cnblogs.com/MYSQLZOUQI/p/4537774.html


解答你也看一下
MySQL5.6在线DDL不锁表,现在我有一张1亿的表,需要增加一个字段,假如我让你去增加这个字段,你应该注意什么,具体怎么操作?

操作如下:
1.注意磁盘空间(临时表目录 参数 tmpdir ,因为需要创建临时表使用 algorithm=default,inplace,copy copy是用临时表的方法

lock=default,none,shared,exclusive)
2.当前内存剩余量
3.当前有没有大的事务在执行
4.innodb_online_alter_log_max_size参数
5.然后在从上添加,再在主上添加(不记录binlog),处理完成后再开启

如果直接先在主上操作,那么会导致主从延迟很大(在量比较大的情况下)。因为主从复制,从库的SQL线程是单线程,它接收到主库的BINLOG,要一条一条执行,一条SQL卡住了,后面的SQL就会排队等待,这时同步就延迟了

 

附注:

在之前的版本,InnoDB引擎是通过以下步骤来进行DDL的:
1 按照原始表(original_table)的表结构和DDL语句,新建一个不可见的临时表(tmp_table)----> create table tmp_table like original_table;
2 在原表上加write lock,阻塞所有更新操作(insert、delete、update等,一行行的把原表数据拷贝到临时表里,且更新索引)
3 执行insert into tmp_table select * from original_table
4 rename original_table和tmp_table,最后drop original_table
5 释放 write lock。

如果采用第三方工具推荐使用pt-online-schema-change

 

表上不能有任何的外键和触发器,否则不能在线DDL

OSC为facebook的php脚本,用来mysql 在线DDL

触发器将在线DDL期间的DML操作记录到缓存日志rowlog中,到copy的最后redo这些DML日志到表上,实现数据一致

 

 

pt-osc工作原理:
如果表有外键,除非使用 --alter-foreign-keys-method 指定特定的值,否则工具不予执行。
1 判断各种参数,是否符合操作条件
2 创建一个和原表一样的空表结构(tmp_table)
3 执行表结构修改,然后从原表中copy数据到上面创建的新表中
4 在原表上创建3个触发器(名称格式为pt_osc_库名_表名_操作类型),当copy 数据的过程中,把原表的更新操作更新到新表 (注意:如果表中已经定义了触发器这个工具就不能工作了)
CREATE TRIGGER `pt_osc_dba_t_del` AFTER DELETE ON `dba`.`t` FOR EACH ROW DELETE IGNORE FROM `dba`.`_t_new` WHERE `dba`.`_t_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_dba_t_upd` AFTER UPDATE ON `dba`.`t` FOR EACH ROW REPLACE INTO `dba`.`_t_new` (`id`, `a`, `b`, `c1`) VALUES (NEW.`id`, NEW.`a`, NEW.`b`, NEW.`c1`)
CREATE TRIGGER `pt_osc_dba_t_ins` AFTER INSERT ON `dba`.`t` FOR EACH ROW REPLACE INTO `dba`.`_t_new` (`id`, `a`, `b`, `c1`) VALUES (NEW.`id`, NEW.`a`, NEW.`b`, NEW.`c1`)
5 copy 完成以后,用rename table 新表代替原表,默认删除原表 (可以指定参数不删除原表操作)
6 删除触发器
这里要注意的是。。如果是小表。。直接在主上添加了
深圳@邱治军 mysql 中文网 9:46:41

大表要结合业务和时间点考虑是否在主上还是先在从上添加

 

这个工具只能用在percona DB吗
深圳@邱治军 mysql中文网群2015/5/29 10:14:02
没啊
mysql percona mariadb都支持
只要是mysql版本
这工具要维护表的时候最适用了
定期整理mysql innodb 表碎片
你刚才说在线加字段,需要有前提条件
前提条件是什么
深圳@邱治军 mysql中文网群  10:29:32
没啥
添加主键要注意一下
普通字段没啥
主从加字段方法 邱治军
在从库上添加,再在主库上添加
1、在从库设置stop slave; set  sql_log_bin=0;
2、在从库添加字段,不要用pt-osc,从库不能创建触发器,直接加字段就行alter table add column
3、在从库设置start slave; set  sql_log_bin=1;
4、用mha做在线主从切换
5、在主库设置set  sql_log_bin=0;stop slave;
6、主库加字段  ,不要用pt-osc,从库不能创建触发器,直接加字段就行alter table add column
7、在主库设置start slave; set  sql_log_bin=1;

这篇关于MySQL5.6在线DDL不锁表(在线添加字段)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/525985

相关文章

MySQL使用binlog2sql工具实现在线恢复数据功能

《MySQL使用binlog2sql工具实现在线恢复数据功能》binlog2sql是大众点评开源的一款用于解析MySQLbinlog的工具,根据不同选项,可以得到原始SQL、回滚SQL等,下面我们就来... 目录背景目标步骤准备工作恢复数据结果验证结论背景生产数据库执行 SQL 脚本,一般会经过正规的审批

水位雨量在线监测系统概述及应用介绍

在当今社会,随着科技的飞速发展,各种智能监测系统已成为保障公共安全、促进资源管理和环境保护的重要工具。其中,水位雨量在线监测系统作为自然灾害预警、水资源管理及水利工程运行的关键技术,其重要性不言而喻。 一、水位雨量在线监测系统的基本原理 水位雨量在线监测系统主要由数据采集单元、数据传输网络、数据处理中心及用户终端四大部分构成,形成了一个完整的闭环系统。 数据采集单元:这是系统的“眼睛”,

电力系统中的A类在线监测装置—APView400

随着电力系统的日益复杂和人们对电能质量要求的提高,电能质量在线监测装置在电力系统中得到广泛应用。目前,市场上的在线监测装置主要分为A类和B类两种类型,A类和B类在线监测装置主要区别在于应用场景、技术参数、通讯协议和扩展性。选择时应根据实际需求和应用场景综合考虑,并定期维护和校准。电能质量在线监测装置是用于实时监测电力系统中的电能质量参数的设备。 APView400电能质量A类在线监测装置以其多核

JavaFX应用更新检测功能(在线自动更新方案)

JavaFX开发的桌面应用属于C端,一般来说需要版本检测和自动更新功能,这里记录一下一种版本检测和自动更新的方法。 1. 整体方案 JavaFX.应用版本检测、自动更新主要涉及一下步骤: 读取本地应用版本拉取远程版本并比较两个版本如果需要升级,那么拉取更新历史弹出升级控制窗口用户选择升级时,拉取升级包解压,重启应用用户选择忽略时,本地版本标志为忽略版本用户选择取消时,隐藏升级控制窗口 2.

Go Playground 在线编程环境

For all examples in this and the next chapter, we will use Go Playground. Go Playground represents a web service that can run programs written in Go. It can be opened in a web browser using the follow

(javaweb)mysql---DDL

一.数据模型,数据库操作 1.二维表:有行有列 2. 3.客户端连接数据库,发送sql语句给DBMS(数据库管理系统),DBMS创建--以文件夹显示 二.表结构操作--创建 database和schema含义一样。 这样就显示出了之前的内容。

12C 新特性,MOVE DATAFILE 在线移动 包括system, 附带改名 NID ,cdb_data_files视图坏了

ALTER DATABASE MOVE DATAFILE  可以改名 可以move file,全部一个命令。 resue 可以重用,keep好像不生效!!! system照移动不误-------- SQL> select file_name, status, online_status from dba_data_files where tablespace_name='SYSTEM'

css选择器和xpath选择器在线转换器

具体前往:Css Selector(选择器)转Xpath在线工具

C/C++ 网络聊天室在线聊天系统(整理重传)

知识点: TCP网络通信 服务端的流程: 1.创建socket套接字 2.给这个socket绑定一个端口号 3.给这个socket开启监听属性 4.等待客户端连接 5.开始通讯 6.关闭连接 解释: socket:类似于接口的东西,只有通过这个才能跟对应的电脑通信。 每一台电脑都有一个IP地址,一台电脑上有多个应用,每个应用都会有一个端口号。 socket一般分为两种类型,一种是通讯,一种是监听

【最新华为OD机试E卷-支持在线评测】机器人活动区域(100分)多语言题解-(Python/C/JavaScript/Java/Cpp)

🍭 大家好这里是春秋招笔试突围 ,一枚热爱算法的程序员 ✨ 本系列打算持续跟新华为OD-E/D卷的三语言AC题解 💻 ACM金牌🏅️团队| 多次AK大厂笔试 | 编程一对一辅导 👏 感谢大家的订阅➕ 和 喜欢💗 🍿 最新华为OD机试D卷目录,全、新、准,题目覆盖率达 95% 以上,支持题目在线评测,专栏文章质量平均 94 分 最新华为OD机试目录: https://blog.