MySQL 大表丝滑变更 (online schema change)

标签：table1 pt -- 大表丝 MySQL ADD online 变更

近接到业务需求，要对 1张6千万数据量和 1张2千万数据量的，MySQL大表进行增加字段，增加索引变更，为不锁表影响业务，调研后决定使用 pt-online-schema-change 工具操作，现详细记录如下。

什么是大表

一张MySQL表的数据量如果在千万级或以上，则可以认定为大表。如果在百万级，但是字段数量非常多，也可以认为是大表。

什么是大表变更

主要指的是对大表，表结构的修改，常见的业务场景诸如，给表新增字段，新增索引等，参考SQL如下：

-- 增加字段
ALTER TABLE table1 ADD field1 varchar(64);

-- 增加索引 方式1
CREATE INDEX table1_index1 ON table1 (field1);

-- 增加索引 方式2
ALTER TABLE table1 ADD INDEX table1_index1 (field1);

-- 即增加字段、又增加索引，合并书写
ALTER TABLE table1
ADD field1 varchar(64),
ADD field2 varchar(64),
ADD field3 varchar(64),
ADD INDEX table1_index1 (field1),
ADD INDEX table1_index2 (field2),
ADD INDEX table1_index3 (field3);

注意最后一种合并写法，使用工具进行大表表更，都会要求传入变更的具体SQL，如果不合并而是分多次执行，则严重加大执行时间。
即便不使用工具直接执行SQL，也应该合并SQL一次性执行，以减少锁表时间。

大表变更存在什么问题

会锁表，整个SQL执行期间，该表无法读，也无法写，基本就是不可用的状态。至于具体锁表的时间，则跟数据量，字段数，执行变更语句的复杂性，都相关。

第三方工具1 pt-osc (pt-online-schema-change)

这个工具是 Percona 出品的，很早就有，成熟，之前在新浪工作，用的就是这个，也是本文的主角。

pt-osc 的工作原理如下：

创建新表：首先，pt-osc 会创建一个与原表结构相同的新空表。
修改表结构：在新表上执行所需的结构变更，如添加新列或索引。显然此时将非常快，新的空表即无数据，更无业务使用。
同步数据：通过在原表上创建触发器（DELETE、UPDATE、INSERT），将对原表的所有写操作（增删改）同步到新表中。同时，pt-osc 会以块的形式从原表中复制数据到新表。
交换表：数据复制完成后，pt-osc 会将原表重命名为一个临时名称（例如，_origin_old），然后将新表重命名为原表的名称（例如，_origin），从而完成表结构变更。
清理：最后，删除原表的触发器和临时表（如果需要）。

全过程为一个原子事物操作，执行过程若有问题，不会对原表进行任何修改。

概念解释 - 触发器

后面会用到这个概念，触发器比较复杂，也不建议业务使用，这里只简单解释一下。
MySQL 触发器（Trigger）是一种特殊的存储过程，它会在特定的数据库表上执行某些操作时自动触发。这些操作包括 INSERT、UPDATE、DELETE 等。触发器的主要目的是在数据变更之前或之后自动执行一些业务逻辑，以确保数据的完整性、执行复杂的业务规则或者进行数据的级联更新。
触发器可以是 BEFORE 触发器，它在指定的操作执行之前触发，或者 AFTER 触发器，它在操作执行之后触发。此外，还可以有 INSTEAD OF 触发器，它在执行操作时取代了原始的操作。
如何解决大表变更锁表的问题

pt-osc 安装

二进制安装

在官网 https://www.percona.com/downloads 下载最新二进制版本即可
https://downloads.percona.com/downloads/percona-toolkit/3.5.7/binary/tarball/percona-toolkit-3.5.7_x86_64.tar.gz
解压后，找到并执行 ./pt-online-schema-change --help 可查看帮助，该文件实际是一个perl脚本
直接使用二进制，可能会提示缺少某些依赖，按照提示安装即可，类似
yum install perl-DBI perl-DBD-MySQL -y

YUM 安装

参考 https://docs.percona.com/percona-toolkit/installation.html
pt-osc 使用
要执行的命令很长，尤其是里边还包含修改语句的SQL，建议放在脚本里，看仔细后，再执行，参考脚本如下

#!/bin/bash
binname=/usr/local/percona-toolkit-3.5.7/bin/pt-online-schema-change

action='--dry-run'
# action='--execute'

str="
$binname \
    --alter=\" 
        ADD field1 varchar(64),
        ADD field2 varchar(64),
        ADD field3 varchar(64),
        ADD INDEX table1_index1 (field1),
        ADD INDEX table1_index2 (field2),
        ADD INDEX table1_index3 (field3)
    \" \
    --print \
    --critical-load='Threads_running=200' \
    $action \
    D=mydb,t=mytable,P=3306,S='/var/lib/mysql/mysql3306.sock',u=root,p='test_pwd_xxx'
"

echo $str
# eval $str

脚本使用细节，解释如下:

alter
这里边是放入要修改的SQL，根据前边的讲述，对同一个表的多个修改，显然应该合并执行。注意，原始SQL里的 ALTER TABLE table1，是省略掉的，否则会报错
echo and eval
echo 显然是把拼凑的命令字符串给打出来，人工逐单词详细确认是否有问题
eval 接命令字符串，就是直接执行命令了，这都是shell的使用技巧
dry-run and --execute
这两个参数只能选择一个，实际使用肯定是先确认拼凑的字符串没有问题，然后执行 --dry-run 模拟执行，不会实际修改原始表，会创建临时表，修改临时表，表结构，创建触发器等等一些内部原理都暴露出来
--execute 当一切都准备完成，使用 --execute 实际修改生产表，建议放到 tmux 等后台执行，命令执行根据情况持续数分钟，数小时都是可能的
critical-load
这个参数是调整对server负载压力的，我在实际执行的时候，报过下面这个错误
(in cleanup) 2024-03-08T11:04:21 Error copying rows from db1.table1 to db1.table1: Threads_running=53 exceeds its critical threshold 50
这个报错就可以调整该参数，提高限制，来解决。更多细节见官方文档，这么一句话
The tool pauses or aborts its operation if it detects too much load on the server. See --max-load and --critical-load for details.
DSN (DATA SOURCE NAME)
最后这一大坨
D=mydb,t=mytable,P=3306,S='/var/lib/mysql/mysql3306.sock',u=root,p='test_pwd_xxx'
是用来连接上具体的MySQL的，显然你需要指定主库的地址
除了DSN这种连接MySQL的方式，官方还支持
--user
--password
这种参数的形式，我的建议是 不要用 ，我混用过两种方式，有奇怪的报错，而且是
--dry-run 不报连接错误， --execute 报连接错误
按照官方文档的说明，使用--xxx这种参数形式连接MySQL，pt-osc工具内部会自动转换成DSN连接方式，既然如此，我们干脆只使用DSN方式

第三方工具2 gh-ost（GitHub Open Source Tool for Online Schema Table）

gh-ost 的工作原理简要介绍如下：

创建镜像表：在主库上创建一个与原表结构相同的镜像表（通常以原表名后缀 _gho 命名）。
心跳表：同时创建一个心跳表（通常以原表名后缀 _ghc 命名），用于记录 Online DDL 的进度和时间。
数据同步：gh-ost 伪装成从库连接到主库或从库，读取 binlog 日志，并将变更应用到主库上的镜像表。同时，它从原表中拷贝数据到镜像表。
增量数据应用：gh-ost 会根据 binlog 信息完成增量数据的变更，确保镜像表与原表的数据保持一致。
原子性切换：在数据同步完成后，gh-ost 会执行一个原子性的切换操作，将原表重命名为一个临时名称（例如 _tablename_del），然后将镜像表重命名为原表的名称，完成表结构变更。
清理：最后，gh-ost 会删除心跳表和临时表（如果需要）。

如果说 pt-osc 核心是利用触发器，gh-ost 核心则是利用 binlog

MySQL 自带的能力

MySQL 5.7 以后，尤其是 MySQL 8.x，自带一些能力可以不锁表进行大表变更

非技术手段

MySQL大表变更锁表是服务自带的问题，有时可能并一定要用技术手段解决，诸如：

说服、告知业务，变更将锁表，业务短暂不可用。
业务低峰，如凌晨、清晨，进行变更。
想办法减少数据量，再进行变更。
甚至于更彻底，让业务短暂，暂停使用数据库，再进行大表变更。

最后

MySQL 大表变更是一个高危、重大变更，本文仅仅作为入门指引。确要选用pt-osc执行大表变更，建议详细阅读官方文档后再操作。

标签：table1,pt,--,大表丝,MySQL,ADD,online,变更
From： https://www.cnblogs.com/jietang64/p/18084788