mysql 大表表结构变更的姿势 online ddl | ps-osc | gh-ost

遇到的生产问题：

大表更新耗时长，会锁表（metadatalock）， 导致生产业务中断。 监控中可以看到很多session是  waitting for metadatalock  的状态，读写都阻塞.

夜晚操作可以规避一些问题， 但是晚上也会有业务运行，有接单等系统间的调用，也不能停机很久。

另外， 也没人愿意大晚上值班吧， 

 

ddl 方式选择：

直接执行脚本就算了， 耗时长 ， 还可能锁表

  1. online ddl 
  2.  ps-osc 
  3.  gh-ost   

  

前提：

研究这个问题之前 ， 先确定使用的mysql版本， 本文依据的是mysql5.7版本；

要先了解一下metadatalock。（比较重要）

 

onlin ddl：

说明

重点看官方文档！！！！！！！！！：

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

mysql5.6之后出现了online-ddl， 可以在某些场景下  ，可以不阻塞读写，在线更新， 很香。

默认情况下， ddl语句使用的就是onlineddl  特性， 不需要人为指定算法和锁定方式，比如

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM ： 1 INPLACE 2 COPY
LOCK：
NONE  不锁定
SHARED 允许并发查询但阻止 DML。
DEFAULT 允许尽可能多的并发（并发查询、DML 或两者兼有）。省略 LOCK 子句与指定 LOCK=DEFAULT 相同。
EXCLUSIVE 阻止并发查询和 DML。


如果执行了， 但是该语法不支持 ，会报错终止。 
所以加上  LOCK=NONE的好处是如果预期不锁定表 ， 可以加上后缀，失败了，就知道会阻塞dml，避免出现生产事故。

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

生产上常见的ddl操作：

  • 创建或添加二级索引（btree类型）
  • 重命名索引
  • 删除索引
  • 新建表
  • 添加列（加列一般也不会设置自增）：
  • 删除列
  • 修改列（改长度）
  • 修改列（改类型）
  • 设置列默认值
  • 删除列默认值

  

 

操作 是否支持并发DML  
新建表 不影响  

创建或添加二级索引

 是  

重命名索引

 是  

删除索引

 是  

添加列（不设置自增）

 是  

修改列（增加varchar类型列的长度）（注意两点， varchar列，增加长度）

是

( 仅将 VARCHAR 列大小从 0 增加到 255 字节，或从 256 字节增加到更大的大小。此场景才支持)

注意是字节数。VARCHAR 列的字节长度取决于字符集的字节长度。

utf8 ：一个字符，三个字节

utf8mb4：一个字符，四个字节。

用 length(name)可以查看实际字节数。

所以以常用的utf8mb4字符集为例， 就是0-64， 64-无穷大

utf8 的话， 就是（0-86），[86-无穷大)

 

  VARCHAR 列所需的长度字节数必须保持不变。对于 0 到 255 字节大小的 VARCHAR 列，需要一个长度字节来对值进行编码。对于大小为 256 字节或更大的 VARCHAR 列，需要两个长度字节。因此，in-place ALTER TABLE 仅支持将 VARCHAR 列大小从 0 增加到 255 字节，或从 256 字节增加到更大的大小。就地 ALTER TABLE 不支持将 VARCHAR 列的大小从小于 256 字节增加到等于或大于 256 字节的大小。在这种情况下，所需长度字节数从 1 变为 2，仅表副本 (ALGORITHM=COPY) 支持。

修改列varchar列，除了上面的情况之外的场景，包括调小值

否

  

修改列（改int类型列的长度）

 

 是

  非常快

修改列（其他类型的长度，基本常用的就是int和varchar了）

未实验， 官方未说明

  

修改列（改类型）

 否  

设置列默认值

 是  

删除列默认值

 是  

删除列

 是  

 

实验 

1、增加varchar类型列的长度

字段原长度：  `batch_no` varchar(64) 

字符集： utf8

1、增加到500

2、增加到80

 

 

 

 

 

3、增加到100

 

 

 

 

2、修改列（改其他类型列的长度）

原字段： `rec_ver` int(11) DEFAULT NULL

1、增加到20

由以上知识，可以知道onlineddl的使用规范：

  1. 不阻塞dml的操作，可以上线之前闲时执行， 不会影响生产。
  2. 阻塞dml的操作，必须晚上执行。
  3. 执行ddl时，要遵循以下原则和步骤
    1. 　同一个表的多个变更，必须放在一个语句内执行，
    2. ddl之前加上等待锁的超时时间，。 当确定不阻塞dml时， ddl之后要加上无锁的后缀，保险起见。例如下面的形式 
      set lock_wait_timeout=5;
      ALTER TABLE tb_order_event CHANGE COLUMN batch_no batch_no VARCHAR(85), LOCK=NONE;
    3. 如果获取锁失败， 那么重试。 如果还失败，那么就说明数据库存在未提交的事务，或者事务未关闭。
      1. 　show PROCESSLIST;找到长事务并kill掉，重试.

 

 

gh-ost

说明

cutover说明：

http://www.linzhoukai.com/?p=246

https://www.modb.pro/db/34041

使用说明： https://www.cnblogs.com/zhoujinyi/p/9187421.html

https://zhuanlan.zhihu.com/p/83770402

 


启动

nohup ./gh-ost \
--aliyun-rds \
--max-load=Threads_running=25 \
--critical-load=Threads_running=50 \
--critical-load-interval-millis=5000 \
--chunk-size=1000 \
--user="root" \
--password="123456" \
--host='172.23.96.1' \
--port=3306 \
--database="demo" \
--table="test_ghost" \
--verbose \
--alter="ADD COLUMN iot_el_fence_inout varchar(255) NULL COMMENT '进出电子围栏标识：in 进入 out 出'" \
--assume-rbr \
--cut-over=default \
--cut-over-lock-timeout-seconds=3 \
--dml-batch-size=50 \
--allow-on-master \
--concurrent-rowcount \
--default-retries=10 \
--heartbeat-interval-millis=2000 \
--panic-flag-file=/data/ghosttmp/ghost.panic.flag \
--timestamp-old-table \
--execute >/data/ghosttmp/run.log 2>&1 &

 

以上命令，自动重试10次，每次尝试锁3s。成功后自动cutover

如果加上  --postpone-cut-over-flag-file=/data/ghosttmp/ghost.postpone.flag  ， 就可以手动控制cutover时机。

ddl一个4000w的表， 发现工具本身占用的资源并不多

 

 

后边写详细的文档吧 

另外，ghost不支持触发器 。有触发器， 会执行失败

题外话， mysql的触发器 ，不能改本表的数据， 因为数据库怕死循环了。 会报错

 

 

pt-osc


dba界的瑞士军刀。

没玩过，以下供了解。原理是用触发器 ， 负载比较大， 也不能控制速率，也不能暂停，可能发生死锁。

 

https://blog.csdn.net/lijingkuan/article/details/68951089

https://blog.csdn.net/isoleo/article/details/103818332

 

posted @ 2022-04-24 11:18  rudolf_lin  阅读(97)  评论(0)  编辑  收藏  举报