一、起源:最频繁也最危险的一类操作
1.1 结构变更的风险来源
结构变更(DDL)在数据库操作中频率并不低:新增字段、加索引、改字段类型、调字符集,都会随业务迭代发生。它与 DML 的差别在于执行代价的量级:
- 元数据锁(MDL)造成的连锁阻塞。DDL 需要获取表的元数据写锁,而所有访问该表的语句都需要元数据读锁。一旦 DDL 进入等待队列,后续到达的查询会被排在其后,阻塞范围从一条语句扩散到该表的全部请求。
- 表重建与数据拷贝。需要重建表的变更会复制全量数据,期间消耗大量磁盘 I/O 与 CPU,并把表空间占用翻倍。
- 磁盘空间膨胀。重建过程产生的临时文件、新表空间、以及逐行变更写入的 binlog,会在短时间内推高磁盘占用,余量不足时直接写满。
- 复制延迟。DDL 在从库重放时同样需要重建表,从库在此期间无法推进重放;逐行拷贝类工具产生的海量 binlog 也会让从库追赶时间显著拉长。
- 业务中断。锁等待堆积到连接数上限后,新建连接失败,业务从"变慢"变为"不可用"。
这五类风险之间存在放大关系:锁等待本身不致命,但锁等待叠加连接池耗尽是可用性事故;磁盘空间不足不致命,但磁盘写满会使 DDL 中断并留下不一致的中间状态。
1.2 日常治理中的隐形成本
日常治理的困难在于成本不即时显现,只在特定时刻集中暴露。
| 治理项 | 隐形成本的形态 | 触发时刻 |
|---|---|---|
| 日志与 binlog | 单个文件按天增长,磁盘余量以月为单位下降 | 磁盘写满时业务写入失败 |
| binlog 保留 | 从库长时间断连,主库为保留其所需日志不断累积文件 | 磁盘告警或复制恢复失败时才发现 |
| 账号权限 | 为临时排障授予的宽权限长期保留,账号数量逐月累积 | 审计或安全事件排查时无法解释权限来源 |
| 字符集 | 早期表使用 utf8mb3,新表使用 utf8mb4,两者共存 | 关联查询报排序规则冲突,或写入四字节字符失败 |
| SQL 模式 | 新旧实例的 sql_mode 不一致,同一语句行为不同 | 迁移或主从切换后数据写入结果不符预期 |
| 备份任务 | 任务返回非零退出码但无人查看,备份文件逐渐不完整 | 需要恢复时才发现可用备份缺失 |
这些项目的共同特征是"当下不做也不出问题",因此容易被排在其他工作之后。治理方式不是一次性清理,而是把这些项目纳入固定的巡检与复核周期。
二、原理
2.1 在线 DDL 的机制
ALGORITHM 与 LOCK 的语义
ALTER TABLE 可以通过 ALGORITHM 与 LOCK 两个子句约束执行方式:
| ALGORITHM | 执行方式 | 对并发访问的影响 |
|---|---|---|
| INSTANT | 只修改数据字典中的元数据,不触碰表数据与索引 | 不阻塞并发读写 |
| INPLACE | 在原表上完成,多数操作不需要复制全量数据(加二级索引只构建索引) | 可配合 LOCK=NONE 允许并发读写 |
| COPY | 创建临时表并逐行复制数据,再用临时表替换原表 | 复制期间不允许并发写入,可允许并发读取 |
| DEFAULT | 由服务器自动选择代价最小的可用算法 | 取决于实际选中的算法 |
| LOCK | 含义 |
|---|---|
| NONE | 允许并发查询与并发 DML |
| SHARED | 允许并发查询,不允许并发 DML |
| EXCLUSIVE | 并发查询与 DML 均不允许 |
| DEFAULT | 按算法允许的最小锁级别 |
算法与锁并非任意组合:请求 NONE 而算法只能做到 SHARED 时,语句直接报错(ER_ALTER_OPERATION_NOT_SUPPORTED)而不降级执行。这一点决定了显式指定 ALGORITHM 与 LOCK 的价值——让不满足在线条件的变更在影响业务之前失败,而不是在后台持锁重建。
INSTANT 的支持范围随小版本变化
INSTANT 的可用性由服务器小版本决定,判定时必须核对实际版本:
- MySQL 5.6 起引入 InnoDB 在线 DDL,支持部分操作的 INPLACE 方式,但没有 INSTANT。
- MySQL 5.7 支持加列、加索引的 INPLACE 方式(加列仍需重建表,但允许并发 DML),同样没有 INSTANT。
- MySQL 8.0.12 起引入 INSTANT,最初支持在表末尾新增列、设置列的默认值等操作。
- MySQL 8.0.29 起支持在任意位置新增列与删除列,但删除仍受列用途限制(例如参与索引、被生成列引用时不可用 INSTANT 删除)。
- 反复使用 INSTANT 新增列会在一行内累积多个行版本,达到 64 个后需要一次 INPLACE 重建来物化(materialize),此后才能继续使用 INSTANT。
INSTANT 的代价并非零:变更后表的行布局与新建表不同,读取时需按行版本解释;部分版本不允许对使用 INSTANT 新增的列立即执行其他 INSTANT 操作。因此 INSTANT 适合迭代过程中的小步变更,不适合作为随意变更的理由。
元数据锁的获取时机与阻塞表现
元数据锁的行为可以按时间顺序还原:
- 一条语句解析时,需要为涉及的表获取元数据读锁;在显式事务中,该读锁持有到事务提交或回滚,而非语句结束。
- DDL 在开始执行前需要为表获取元数据写锁,与所有已持有的元数据读锁互斥。
- 若存在未提交的长事务持有元数据读锁,DDL 只能进入等待队列。
- 元数据锁采用写优先策略:DDL 的写锁请求在排队后,新到达的读锁请求会被阻塞在其后,以避免写操作饥饿。
- 于是出现典型现象:一条未提交的长事务使 DDL 挂起,DDL 的挂起使该表所有新查询被阻塞,被阻塞的查询继续占用连接,连接池逐步耗尽。
这一链条说明,DDL 的安全前提不是"表够小",而是"没有长事务在持有该表的元数据读锁"。观察 MDL 的手段:
-- 查看当前等待中的元数据锁
SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING';
-- 查看锁等待的双方关系(sys 视图,5.7 与 8.0 均可使用)
SELECT * FROM sys.schema_table_lock_waits\G
-- 查看长事务,重点关注未提交且运行时间长的会话
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_rows_locked, LEFT(trx_query, 80)
FROM information_schema.innodb_trx
ORDER BY trx_started;
变更期间对复制的影响
无论采用何种算法,DDL 语句本身都会写入 binlog,且即使 binlog_format=ROW,DDL 仍以语句形式记录。从库重放该 DDL 时会独立执行一次,由此产生三个方面的影响:
- 从库执行 DDL 期间同样需要获取元数据锁,若从库上有长查询在跑,从库的 DDL 会挂起,复制停滞。
- COPY 类变更在从库上同样要重建表,从库的 I/O 与 CPU 压力与主库相当,主从性能差异会使延迟进一步扩大。
- INPLACE 变更期间主库持续产生的 DML 需要被记录到在线变更日志中,由
innodb_online_alter_log_max_size(默认 128 MB)限制上限;高写入场景下可能超过上限,DDL 报DB_ONLINE_LOG_TOO_BIG失败并回滚,需要重新执行。
复制链路的线程模型、位点推进与延迟来源参见 mysql: 主从同步原理。变更完成后的主从切换与延迟处置见 mysql: 主从部署与运维实践。
MySQL 8.0 的原子 DDL 使数据字典变更、存储引擎变更与 binlog 写入成为一个原子操作,避免了 5.7 中 DDL 执行中途崩溃时可能出现的表定义与存储文件不一致。
2.2 大表变更的代价来源
代价可以拆分为五个可测量的部分:
- 表重建与数据拷贝。COPY 算法与
CONVERT TO CHARACTER SET类操作需要把全部行读出、转换、写入新表,代价与行数、行宽、索引数量成正比。8.0.27 起可通过innodb_ddl_threads与innodb_ddl_buffer_size加快重建阶段的排序与写入。 - 磁盘空间。重建期间原表与新表并存,所需额外空间接近表的实际占用;
tmpdir还会承载排序文件。空间不足是 DDL 失败最常见的外部原因。 - I/O 与 buffer pool 争用。重建产生的顺序读与随机写会挤占总量的 I/O 能力,并冲刷 buffer pool 中的热数据,表现为变更期间业务查询命中率下降。
- binlog 生成量。原生 DDL 只产生一条语句级日志,但外部在线改表工具逐行拷贝数据,在 ROW 格式下产生的 binlog 量与原表数据量同级甚至更大;从库需要把这些行事件全部重放。
- 复制延迟与从库追赶。从库既要重放海量行事件,又要在执行 DDL 时暂停重放,延迟会先堆积再逐步消化;延迟期间读写分离架构下的从库读取到过期数据。
- 连接与锁等待。变更期间的锁等待抬高响应时间,连接被慢查询占用,进而推高连接数;这部分影响与锁级别直接相关。
代价的可控性来自选择:能 INSTANT 的不选 INPLACE,能 INPLACE 加 LOCK=NONE 的不选 COPY,必须 COPY 时改用外部工具把一次长操作拆成可控的分批操作。
2.3 外部工具的机制
共同思路
gh-ost 与 pt-online-schema-change 解决的问题相同:把"一次持锁的重建"改写为"影子表逐步追平加一次瞬时切换"。
| 阶段 | 动作 |
|---|---|
| 准备 | 按原表结构创建影子表,在影子表上执行目标 DDL |
| 增量同步 | 把原表上持续发生的增删改同步到影子表,保证数据不丢失 |
| 逐步拷贝 | 按主键(或唯一索引)分批把存量数据从原表复制到影子表 |
| 原子切换 | 用 RENAME TABLE 把影子表改名替换原表,同时清理触发器或中间表 |
切换阶段的 RENAME TABLE 仍需短暂获取元数据写锁,锁定期通常很短,但若此刻存在长事务,切换会等待,等待期间原表的新请求被阻塞——这是两个工具都无法消除的固有风险。
差异
| 维度 | gh-ost | pt-online-schema-change |
|---|---|---|
| 增量同步方式 | 读取 binlog 事件并转换为行操作 | 在原表创建 AFTER INSERT / UPDATE / DELETE 三个触发器 |
| 对原表的侵入 | 不创建触发器,对原表写入路径无额外开销 | 每次写入额外触发一次触发器逻辑,写放大明显 |
| 前置条件 | 要求 binlog_format=ROW(可用 --assume-rbr 声明),需要复制权限 | 表上不能已有触发器;存在外键引用时需指定处理方式 |
| 暂停与限速 | 支持通过文件、连接、状态查询等方式暂停与恢复 | 支持 --pause-file,主要通过负载阈值暂停 |
| 中间表命名 | _表名_gho(影子表)、_表名_ghc(状态表) | _表名_new(新表)、_表名_old(旧表) |
| 对主从的要求 | 默认从从库读取 binlog,需指定从库;与多主、级联结构配合时需额外参数 | 只依赖主库触发器,对复制拓扑无额外要求 |
使用边界与风险
- 两个工具都会产生与原表数据量相当的 binlog,从库延迟与磁盘占用必须提前评估。
- 表没有主键或唯一索引时,分批拷贝失去可靠的切分依据;pt-osc 会拒绝执行,gh-ost 会退化为全表扫描式的迭代。
- 存在外键约束的表处理复杂:pt-osc 需要选择
--alter-foreign-keys-method,gh-ost 对带外键的表支持有限。 - 触发器类工具在写入路径上引入额外开销,高写入表上会同时抬高主库延迟与 binlog 量。
- 工具运行期间的中间表占用额外磁盘空间,中断后若未清理,残留表会持续占用空间并干扰后续操作。
- 工具只能完成结构变更,不能替代变更前的兼容性评估;应用侧的读写兼容仍按扩展、迁移、收缩的顺序推进。
2.4 字符集与排序规则
utf8 与 utf8mb4 的差别
MySQL 中的 utf8 是 utf8mb3 的别名,最多使用 3 字节表示一个字符,无法表示四字节字符(emoji、部分生僻汉字、部分少数民族文字)。utf8mb4 使用最多 4 字节,是完整的 UTF-8 实现。历史遗留问题集中在此:
- 早期版本的默认字符集为 latin1,中期广泛使用 utf8mb3,两种定义的表与新库并存。
- utf8mb3 列在写入四字节字符时报错或被截断(取决于 sql_mode),表现为业务侧"无法保存 emoji"。
- utf8mb3 与 utf8mb4 之间的比较与关联查询会发生隐式转换,转换过程中可能使用索引失效的执行计划。
- MySQL 8.0 起
utf8作为字符集名被标记为废弃,utf8mb3成为正式名称,8.0 的默认字符集改为 utf8mb4。
三层字符集的一致性
字符集的设置分布在三个层面,任一层面不一致都会产生转换:
| 层面 | 相关变量 | 影响 |
|---|---|---|
| 服务端 | character_set_server、collation_server | 未显式指定字符集的建库建表语句所继承的默认值 |
| 连接层 | character_set_client、character_set_connection、character_set_results | 客户端发送语句与实际返回结果的解释方式 |
| 列层 | 表与列的 CHARACTER SET、COLLATE | 数据实际存储与比较所用的字符集与排序规则 |
连接层与服务端不一致时,服务器会做字符集转换,转换可能失败(写入报错)或丢失信息;列层不一致时,比较与关联会出现隐式转换。因此在建库、建表、连接串、驱动配置四处都要显式声明 utf8mb4,不依赖默认值。
排序规则对索引与比较的影响
排序规则决定字符串的比较与排序方式,直接影响查询结果与索引可用性:
utf8mb4_general_ci与utf8mb4_unicode_ci的比较基准不同,前者基于简化规则,后者基于 Unicode 排序算法,对重音字符与特殊字符的处理不一致。- MySQL 8.0 的默认排序规则为
utf8mb4_0900_ai_ci,与 5.7 时代的utf8mb4_general_ci并非同一套规则。 - 两列排序规则不同时,关联查询会报
Illegal mix of collations;能隐式转换的场景下,转换后的列无法使用其索引。 NO PAD与PAD SPACE的差别影响尾部空格的处理:8.0 的utf8mb4_0900_ai_ci为 NO PAD,5.7 使用的 utf8mb4 排序规则为 PAD SPACE,同一份数据在升级后可能命中不同的唯一约束判定。
统一排序规则的成本高于统一字符集,因为它要求所有相关列使用同一套规则,而规则本身又存在版本差异。可行做法是按"新库新表用新规则、存量表在变更时一并调整"推进,避免长期混用。
2.5 SQL 模式的意义
sql_mode 决定服务器对非法值与越界行为的处理方式,是数据质量的最后一道约束。
严格模式与宽松模式
严格模式下,数据超出列定义范围、缺少非空列的值、日期非法时,语句直接失败;宽松模式下这些情况会被静默截断或替换为默认值。后果差异明显:宽松模式中写入 VARCHAR(10) 的 20 字符字符串会被截断为 10 字符且只产生警告,业务侧读到的是被截断的数据,而错误在很久之后才被发现。
| 模式 | 作用 | 取舍 |
|---|---|---|
STRICT_TRANS_TABLES | 事务表上的非法值导致语句失败 | 生产环境建议开启;开启后需确认应用能正确处理写入失败 |
STRICT_ALL_TABLES | 所有存储引擎上的非法值导致语句失败 | 非事务表场景使用;InnoDB 环境用前一项即可 |
ONLY_FULL_GROUP_BY | GROUP BY 之外的列必须被聚合 | 5.7 与 8.0 默认开启;关闭会掩盖查询逻辑错误 |
NO_ZERO_DATE、NO_ZERO_IN_DATE | 拒绝零日期与零值日期部分 | 历史数据中已存在零日期时,开启后读取不受影响但修改会失败 |
ERROR_FOR_DIVISION_BY_ZERO | 除零在写入场景报错 | 与严格模式配合使用 |
NO_ENGINE_SUBSTITUTION | 指定的存储引擎不可用时直接失败 | 避免表被静默创建为其他引擎 |
NO_AUTO_CREATE_USER | 授权时拒绝自动创建用户 | 8.0 已移除,见 3.10 |
宽严取舍的核心矛盾在于:严格模式把问题暴露在写入时刻,宽松模式把问题推迟到数据使用时刻。引入严格模式的代价是应用必须处理写入失败,这需要在业务改造与数据质量之间作出取舍,而不是简单地把模式调宽。
模式不一致带来的迁移风险
sql_mode 是实例级(可用 SET SESSION 覆盖)配置。主库与从库、旧实例与新实例、应用连接串中的设置不一致时,同一语句可能得到不同结果:主库严格模式拒绝的插入,从库宽松模式可能写入;或在迁移后原本能写入的数据报错。因此 sql_mode 应与字符集一并纳入配置基线,纳入 3.8 的巡检项。
2.6 权限模型
账号与主机限制
MySQL 的账号由用户名与主机两部分共同构成,'app'@'10.0.1.%' 与 'app'@'%' 是两个不同的账号,权限分别维护。主机部分决定连接来源的可信范围,是访问控制的第一道边界。
最小权限原则
权限粒度可到库、表、列乃至存储过程。实践中的收敛方向:
- 业务账号只授予实际用到的语句权限(通常为 SELECT、INSERT、UPDATE、DELETE),不授予
ALL PRIVILEGES。 - 权限范围限定到具体库,避免使用
ON *.*。 - 不授予
WITH GRANT OPTION,避免权限在账号之间自行扩散。 - 管理类权限(
SUPER、PROCESS、REPLICATION SLAVE、REPLICATION CLIENT)单独创建账号,与业务账号分离。 - 只读账号除 SELECT 外不应有写权限,且在被用作审计对照时不应包含
SUPER,否则read_only对其无效。
角色与权限复用
MySQL 8.0 引入角色,用于把一组权限打包复用:
CREATE ROLE 'app_rw', 'app_ro';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app_rw';
GRANT SELECT ON app.* TO 'app_ro';
GRANT 'app_rw' TO 'biz_order'@'10.0.1.%';
SET DEFAULT ROLE 'app_rw' TO 'biz_order'@'10.0.1.%';
角色的价值在于权限调整只需改角色定义,不必逐账号重授。角色同样需要纳入复核清单,避免出现"角色已放宽、账号批量继承"的隐性扩权。
审计与留痕的要求
数据库侧的审计需要回答"谁在何时从哪台主机执行了什么语句,结果如何"。可用的手段有三类,代价各不相同:
- 通用查询日志。记录所有到达服务器的语句,信息最全,但开启后写入量极大,会明显增加磁盘 I/O 与空间占用,只适合短时排查。
- 审计插件。企业版 audit_log 插件可记录连接、语句、权限变更并指定输出策略,是合规场景的常规手段;社区版需借助第三方方案或外部代理。
- binlog 解析。binlog 记录了数据变更,配合解析工具可还原出变更语句与来源,但只覆盖写操作,不包含查询类访问。
审计要求的框架、证据链完整性、留存周期与访问控制在主机层面统一约定,具体落地参见 linux: 合规与审计落地;数据库侧的日志留存与轮转见 3.7。
2.7 日志与空间治理
四类日志的产生速率与保留需求
| 日志 | 产生速率的影响因素 | 保留需求 | 治理方式 |
|---|---|---|---|
| 错误日志 | 启动事件、告警、复制错误、崩溃恢复记录 | 保留周期覆盖故障复盘与审计要求 | logrotate 轮转,注意轮转后需通知服务器重开文件 |
| 慢查询日志 | 慢 SQL 数量,log_queries_not_using_indexes 开启后无索引语句全部记录 | 影响写入速率的主要变量 | logrotate 轮转,定期聚合分析后归档 |
| binlog | 写入量与行事件大小,批量操作与在线改表会短时暴增 | 至少覆盖最长一次从库断连时长与备份周期 | 按保留期自动清理,清理前核对从库位点 |
| relay log(从库) | 主库 binlog 量,重放速度不足时持续累积 | 由复制状态决定 | 随重放自动推进;重放停滞时会持续占用磁盘 |
binlog 保留与未消费风险
binlog 的清理方式有两种:按文件清理与按时间清理。
-- 清理到指定文件之前的所有日志(不含该文件)
PURGE BINARY LOGS TO 'binlog.000123';
-- 清理指定时间点之前的日志
PURGE BINARY LOGS BEFORE '2026-09-01 00:00:00';
风险在于清理动作不检测从库位点:若某从库仍停留在被清理的日志区间内,其拉取线程会因日志缺失而中断,报错 1236(Could not find first log file name in binary log index file)。自动清理同样如此,保留期到期即删除,不区分是否有从库仍需要该文件。
从库断连时,主库无法清理该从库尚未拉取的 binlog(在非 GTID 模式下由从库位点决定,在 GTID 模式下由主库的 gtid_purged 与从库已执行集合共同决定),这些文件会在磁盘上持续累积直至复制恢复或从库被移除。位点与 GTID 的差别参见 mysql: 主从同步原理 第 2.5 节与第 2.6 节。
磁盘空间的容量模型
数据库实例所在分区需要容纳的不只是数据文件:
所需空间 ≈ 数据文件(ibd、ibdata1)
+ undo 表空间
+ redo 日志(innodb_log_file_size × innodb_log_files_in_group)
+ binlog(按保留期估算)
+ relay log(从库)
+ tmpdir 中的排序与临时表文件
+ 错误日志与慢日志
+ 本机保留的备份副本
+ 变更期间的重建空间(约等于被重建表的实际占用)
容易遗漏的两项是 tmpdir 与变更期间的额外空间:前者在出现大排序或临时表时突然增长,后者只在 DDL 执行时出现。容量规划应把这两项计入常态余量,而不是等到变更前临时检查。各类表空间文件的构成、膨胀成因与回收方式见 mysql: InnoDB 存储引擎。
三、实现
3.1 变更前的评估清单
变更单中的数据库部分应能回答下列各项,任一项无法回答时先补齐再执行。
| 评估项 | 查证方式 | 判定要点 |
|---|---|---|
| 表规模 | information_schema.tables 取 table_rows、data_length、index_length、data_free | table_rows 在 InnoDB 下为估算值,需结合 SHOW TABLE STATUS 与实际行数抽样核对 |
| 影响面查询 | 从慢日志与 performance_schema.events_statements_summary_by_digest 中筛出涉及该表的语句 | 确认是否有长事务、是否有长查询,是否存在 SELECT * 依赖列顺序 |
| 索引与约束 | SHOW CREATE TABLE、SHOW INDEX FROM | 判断目标变更是否会导致索引隐藏或重建 |
| 可否 INSTANT | 按实例小版本对照 2.1 的支持范围 | 8.0.12 起支持末尾加列,8.0.29 起支持任意位置;不确定时以 ALGORITHM=INSTANT 试探并捕获报错 |
| 磁盘余量 | df -h 与表占用对比 | 需要重建的变更预留接近表占用的额外空间,含 tmpdir 所在分区 |
| 复制拓扑与延迟容忍 | SHOW SLAVE STATUS(8.0.22 起为 SHOW REPLICA STATUS) | 明确从库数量、是否级联、业务能接受的延迟上限 |
| 回滚方案 | 结构、数据、配置三类分别写明 | 删除列、修改列类型、字符集转换的回滚都需恢复数据,不能仅靠逆向 DDL |
| 变更窗口 | 与业务低峰对齐 | 窗口需覆盖执行、观察与回滚的完整时长 |
-- 表规模与碎片情况
SELECT table_schema, table_name, engine, table_rows,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
ROUND(data_free / 1024 / 1024, 2) AS free_mb,
table_collation, create_options
FROM information_schema.tables
WHERE table_schema = 'app'
ORDER BY data_length + index_length DESC
LIMIT 20;
-- 变更对象的当前定义与索引
SHOW CREATE TABLE app.orders\G
-- 活跃会话与长事务(变更前确认无长事务持有 MDL)
SELECT id, user, host, db, command, time, state, LEFT(info, 100) AS info
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;
变更与发布的审批、窗口、冻结期、灰度与复盘流程不在本篇展开,参见 linux: 变更与发布管理 第 2 节与第 3 节。数据类变更的扩展、迁移、收缩顺序同样在该篇第 2.3 节给出。
3.2 在线 DDL 实操要点
按操作类型选择算法
| 变更类型 | 通常可用的算法 | 说明 |
|---|---|---|
| 末尾加列 | INSTANT(8.0.12 起) | 不触碰表数据;指定 ALGORITHM=INSTANT 验证 |
| 任意位置加列、删列 | INSTANT(8.0.29 起) | 删列受列用途限制 |
| 设置或修改列默认值 | INSTANT(8.0.12 起) | 仅元数据变更 |
| 重命名列 | INPLACE(8.0 起支持 RENAME COLUMN) | 不重建表数据 |
| 新增或删除二级索引 | INPLACE,可 LOCK=NONE | 只构建或删除索引结构,不复制表数据;索引的写入代价与冗余判定见 mysql: 索引与执行计划 |
| 修改 VARCHAR 长度 | 跨 256 字节边界时为 COPY,未跨边界为 INPLACE | 需按列的实际字节长度判断 |
| 修改列的数据类型 | COPY | 需要转换每行数据 |
| 转换字符集与排序规则 | COPY | CONVERT TO CHARACTER SET 会重建表 |
| 新增或删除主键 | COPY | 涉及聚簇索引重建 |
| 新增或删除外键 | foreign_key_checks=0 时为 INPLACE,否则 COPY | 生产环境不建议关闭外键检查 |
指定算法与锁的写法
-- 期望在线完成:不满足时报错而非降级持锁执行
ALTER TABLE app.orders
ADD COLUMN remark VARCHAR(200) DEFAULT NULL,
ALGORITHM=INSTANT;
-- 新增索引:明确要求并发读写
ALTER TABLE app.orders
ADD INDEX idx_created_at (created_at),
ALGORITHM=INPLACE, LOCK=NONE;
-- 修改列类型:接受 COPY 时显式声明,并允许并发查询以减少阻塞面
ALTER TABLE app.orders
MODIFY COLUMN amount DECIMAL(18,2) NOT NULL,
ALGORITHM=COPY, LOCK=SHARED;
语句失败时的处理顺序:
- 报
ER_ALTER_OPERATION_NOT_SUPPORTED(错误号 1846)说明请求的算法或锁不被该操作支持,此时不应直接移除ALGORITHM子句重试,而应确认实际会采用的算法与阻塞范围。 - 报
ER_LOCK_WAIT_TIMEOUT或长时间无返回,说明 MDL 获取受阻,应先用 2.1 中的元数据锁查询定位持有者,而不是直接重试。 - 报
DB_ONLINE_LOG_TOO_BIG(错误号 1799)说明变更期间的并发 DML 量超过了innodb_online_alter_log_max_size,需要调大该参数或在写入更低谷的时段重试。
高危 提醒:在线 DDL 是在生产表上直接执行的结构变更,一旦阻塞扩散,影响的是该表的全部请求。示例:在有未提交长事务的表上执行加索引语句,DDL 等待元数据写锁,后续所有对该表的查询被排在 DDL 之后,连接池被逐个占满,服务在几十秒内完全不可用。兜底方式:
- 执行前确认无长事务与长查询,重复 3.1 中的会话与事务查询,确认
trx_started距今时间可接受 - 为 DDL 会话单独设置等待行为,避免无限制等待:
SET SESSION lock_wait_timeout = 30; SET SESSION innodb_lock_wait_timeout = 30; -
变更期间持续观察阻塞情况,出现异常立即终止:
-- 观察线程与锁等待 SHOW STATUS LIKE 'Threads_running'; SHOW STATUS LIKE 'Threads_connected'; SELECT * FROM sys.schema_table_lock_waits\G SELECT * FROM sys.innodb_lock_waits\G -- 观察 INPLACE 类变更的进度 SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE 'stage/innodb/alter%'; -- 终止本会话的 DDL(先定位 id) KILL QUERY <ddl_thread_id>; -
熔断条件提前约定:
Threads_running超过该实例基线的若干倍、连接数接近max_connections、任一从库延迟超过容忍上限、磁盘余量进入告警区间时终止变更 - 终止 COPY 类变更后核对是否残留临时表文件,清理方式见 3.3
- 优先选择业务低峰窗口,且变更时段内不并行安排其他数据库操作
变更期间的观察指标
| 指标 | 采集方式 | 异常表现 |
|---|---|---|
| 运行线程数 | SHOW STATUS LIKE 'Threads_running' | 上升说明存在排队 |
| 连接数与连接失败 | Threads_connected、Aborted_connects、Connection_errors_max_connections | 连接失败出现说明已触及上限 |
| 锁等待 | sys.innodb_lock_waits、sys.schema_table_lock_waits | 出现持续性等待说明阻塞成立 |
| 从库延迟 | Seconds_Behind_Master(8.0.22 起为 Seconds_Behind_Source) | 持续增长且无回落趋势 |
| 磁盘余量 | df -h、du -sh 数据目录 | 重建阶段下降速度明显 |
| 变更进度 | performance_schema.events_stages_current | 进度长时间不推进说明受阻 |
3.3 大表变更工具的使用流程
准备阶段
- 分别准备具备
ALTER、CREATE、DROP、INSERT、UPDATE、DELETE权限的专用账号,主从读取 binlog 时另需REPLICATION SLAVE与REPLICATION CLIENT。 - 用
--dry-run(pt-osc)或先以--test-on-replica思路在从库验证(gh-ost),确认工具对目标表结构的判断与预期一致。 - 评估中间表所需的额外磁盘空间,确认
tmpdir与数据目录所在分区余量。 - 确认目标表具备主键或唯一索引;不具备时先评估是否补充主键。
- 设定限速与熔断参数。
# pt-online-schema-change 预演:只输出将要执行的语句,不做实际改动
pt-online-schema-change \
--alter "ADD COLUMN remark VARCHAR(200) DEFAULT NULL, ADD INDEX idx_created_at (created_at)" \
D=app,t=orders,h=10.0.1.11,P=3306,u=ddl_user,p=<password> \
--chunk-size=2000 --chunk-time=0.5 \
--max-load="Threads_running=40" --critical-load="Threads_running=80" \
--max-lag=3 --check-interval=2 \
--check-replication-filters --no-drop-old-table --dry-run
# gh-ost:以文件方式控制切换时机,配合限速与延迟阈值
gh-ost \
--host=10.0.1.11 --port=3306 --user=ddl_user --password=<password> \
--database=app --table=orders \
--alter="ADD COLUMN remark VARCHAR(200) DEFAULT NULL, ADD INDEX idx_created_at (created_at)" \
--assume-rbr --allow-on-master \
--chunk-size=2000 \
--max-load="Threads_running=40" --critical-load="Threads_running=80" \
--max-lag-millis=3000 --throttle-control-replicas=10.0.1.12 \
--postpone-cut-over-flag-file=/tmp/gh-ost.postpone \
--ok-to-drop-table --execute
关键参数的含义
| 参数 | 工具 | 作用 |
|---|---|---|
--chunk-size | 两者 | 每批拷贝的行数,决定单次操作的事务大小 |
--chunk-time | pt-osc | 动态调整每批大小,使单批耗时接近该值 |
--max-load | 两者 | 超过阈值时暂停拷贝,负载回落后继续 |
--critical-load | 两者 | 超过阈值时直接中止,用于兜底 |
--max-lag / --max-lag-millis | 两者 | 从库延迟超过阈值时暂停拷贝 |
--throttle-control-replicas | gh-ost | 指定用于判断延迟的从库 |
--check-interval | pt-osc | 检查负载与延迟的间隔 |
--pause-file / --postpone-cut-over-flag-file | 两者 | 通过文件控制暂停与切换时机 |
--dry-run | pt-osc | 预演,不执行变更 |
--test-on-replica | gh-ost | 在从库上完整演练,切换后不删除数据 |
--no-drop-old-table | pt-osc | 保留旧表,作为回滚依据 |
--ok-to-drop-table | gh-ost | 允许切换后删除旧表 |
变更期间的监控项
- 拷贝进度与剩余行数,判断是否仍在推进。
Threads_running与从库Seconds_Behind_Master,与--max-load、--max-lag的阈值对照。- 主库 binlog 生成速率与磁盘余量,外部工具会产生大量行事件。
- 中间表体积增长,确认额外空间在预期范围内。
- 工具自身的日志输出,关注反复出现的暂停与恢复记录。
切换时机的判定与失败处置
切换应在拷贝追平且业务压力较低时进行,判定条件是:增量同步的滞后量接近零、从库延迟处于阈值内、主库无长事务持有目标表的元数据锁。
高危 提醒:终止在线改表工具并清理中间表属于破坏性操作,误判会造成数据丢失或表不可用。示例:在工具仍在拷贝阶段直接 DROP 影子表,工具进程随后尝试写入已删除的表而报错退出;或在切换已完成后误删与原表同名的旧表,丢失切换前写入的数据。兜底方式:
- 优先使用工具的优雅停止方式(pt-osc 创建暂停文件、gh-ost 使用
--postpone-cut-over-flag-file或交互式暂停),而不是直接终止进程 - 必须终止进程时,先确认当前阶段(存在于拷贝阶段还是切换阶段):
# pt-osc:观察进程与中间表 ps -ef | grep pt-online-schema-change mysql -e "SHOW TABLES FROM app LIKE '\_orders%';" # gh-ost:观察进程与状态表 ps -ef | grep gh-ost mysql -e "SELECT * FROM app.\`_orders_ghc\`;" -
终止后保留中间表,先比对行数与校验再决定清理,比对方式:
SELECT COUNT(*) FROM app.orders; SELECT COUNT(*) FROM app.`_orders_new`; CHECKSUM TABLE app.orders, app.`_orders_new`; -
清理残留表前确认其确为中间表,命名符合
_表名_new、_表名_old、_表名_gho、_表名_ghc的规则:DROP TABLE IF EXISTS app.`_orders_new`; DROP TABLE IF EXISTS app.`_orders_gho`; DROP TABLE IF EXISTS app.`_orders_ghc`; -
原生
ALTER TABLE被中断后可能残留#sql-开头的临时文件,需在实例无其他 DDL 运行时确认并清理:ls -l /var/lib/mysql/app/ | grep '#sql-' -
保留旧表(
--no-drop-old-table)直到业务验证通过,验证内容包含读写路径与数据一致性
3.4 分批处理与数据修正
分批的原因
单条 UPDATE 或 DELETE 涉及大量行时,会形成大事务,带来的问题是连锁的:undo 日志持续增长、锁持有时间拉长、binlog 中单个事务体积过大、从库在重放该事务时无法并行且中途不可中断。分批把一次大操作拆成多个小事务,使锁的持有时间可控、复制可以持续推进。
按主键范围分批
-- 删除历史数据:每批限定行数,重复执行直至影响行数为 0
DELETE FROM app.operation_log
WHERE created_at < '2025-01-01'
ORDER BY id
LIMIT 2000;
-- 查看上一批实际影响的行数
SELECT ROW_COUNT();
# 循环执行,直到影响行数为 0,批间留出间隔让从库追赶
while true; do
affected=$(mysql -N -e "
DELETE FROM app.operation_log
WHERE created_at < '2025-01-01'
ORDER BY id
LIMIT 2000;
SELECT ROW_COUNT();")
echo "affected=${affected}"
[ "${affected}" -eq 0 ] && break
sleep 0.5
done
按主键区间推进(id > 上批最大 id)比 LIMIT 更稳定,尤其在无索引条件上有并发写入时,LIMIT 的扫描起点可能发生偏移;但主键区间要求分批条件与主键单调性相关,条件本身为时间列时应优先确认该列上有可用索引。
执行约束
- 批大小按单批耗时控制,使每批的执行时间远小于业务可感知的阈值,而不是固定行数。
- 批间加入短暂间隔,给从库重放留出追平时间;间隔时长按从库延迟反馈调整。
- 避免在分批条件上依赖无索引列,否则每批都会退化为全表扫描。
- 分批任务安排在业务低峰执行,并与变更窗口一致地通知值班人员。
- 分批删除配合归档:先归档到历史表或离线存储,再从生产表删除。
# 归档并清理:先输出待处理行预演,再执行删除
pt-archiver \
--source h=10.0.1.11,D=app,t=operation_log,u=arch_user,p=<password> \
--where "created_at < '2025-01-01'" \
--purge --limit 1000 --commit-each --sleep 0.5 \
--max-lag 1 --progress 10000 --dry-run
# 预演结果符合预期后去掉 --dry-run 执行
高危 提醒:批量 DELETE 与批量 UPDATE 一旦条件写错,影响范围是整个表而非一批数据,且删除难以逆向恢复。示例:DELETE FROM operation_log WHERE created_at < '2025-01-01' 中日期条件误写为 >,删除的是近期数据;或漏写索引条件下的全表更新,把业务状态字段统一改写。兜底方式:
-
先用等价的 SELECT 统计影响行数并抽样查看,确认条件范围符合预期:
SELECT COUNT(*) FROM app.operation_log WHERE created_at < '2025-01-01'; SELECT id, created_at, LEFT(content, 50) FROM app.operation_log WHERE created_at < '2025-01-01' ORDER BY id LIMIT 20; -
使用带
LIMIT的批次语句,不使用无限制的单条语句;DELETE语句在带ORDER BY时谨慎核对语法在目标版本上的支持情况 - 变更前确认备份可用且近期验证过恢复流程,备份与恢复方法参见 linux: 备份与容灾
- 更新类操作先记录原值,便于比对与回退:
CREATE TABLE app.bak_status_20260922 AS SELECT id, status FROM app.orders WHERE status = 1; -
先在从库或影子库执行同一语句验证影响范围,或在预发环境复现
- 双人复核 SQL 语句中的库名、表名与条件,执行后核对影响行数与预期是否一致
3.5 字符集与排序规则的统一
新库新表的规范
CREATE DATABASE IF NOT EXISTS app
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;
CREATE TABLE app.orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_no VARCHAR(64) NOT NULL,
remark VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_order_no (order_no)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;
排序规则的选择需要在"新规则的一致性"与"与存量表的兼容性"之间取舍:存量表多为 utf8mb4_general_ci 时,新表沿用该规则可减少关联查询的排序规则冲突;若以统一到 8.0 默认规则为长期目标,则应有配套的存量表调整计划,而不是长期混用。
连接层的一致性
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
# JDBC 连接串
jdbc:mysql://10.0.1.11:3306/app?useUnicode=true&characterEncoding=utf8mb4
服务端配置见 mysql: 服务配置。应用与命令行客户端都应显式声明字符集,不依赖服务端默认值。
检测不一致
-- 服务端与连接层变量
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
-- 非 utf8mb4 的表
SELECT table_schema, table_name, table_collation
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
AND table_collation NOT LIKE 'utf8mb4%';
-- 非 utf8mb4 的字符列
SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE character_set_name IS NOT NULL
AND character_set_name <> 'utf8mb4'
ORDER BY table_schema, table_name, ordinal_position;
历史数据的调整路径与代价
-- 转换整表的字符集(重建表,属于 COPY 类变更)
ALTER TABLE app.orders
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
ALGORITHM=COPY;
-- 只改表默认值,不转换既有数据(不重建表,但既有列定义不变)
ALTER TABLE app.orders
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
需要明确的代价与限制:
CONVERT TO CHARACTER SET会重建表,属于 COPY 类变更,必须按 3.2 与 3.3 的做法评估窗口与工具。- 从 utf8mb3 转为 utf8mb4 后,单个字符最大字节数由 3 变为 4,含索引的 VARCHAR 列所需索引长度随之增加。在
DYNAMIC或COMPRESSED行格式下索引前缀上限为 3072 字节,REDUNDANT或COMPACT下为 767 字节,超过上限的索引需要缩短前缀或改用其他方案。 - 已存在于 utf8mb3 列中的四字节字符无法通过该语句恢复,因为其从未被正确存储;此类数据需从上游重新导入。
- 排序规则变更会改变唯一索引的冲突判定(尾部空格、重音等价字符),转换前需确认无数据在该规则下产生新的唯一性冲突:
SELECT order_no, COUNT(*) FROM app.orders GROUP BY order_no HAVING COUNT(*) > 1;
3.6 账号与权限治理
账号清单与权限复核
-- 账号清单:用户、来源主机、认证插件、是否锁定
SELECT user, host, plugin, account_locked, password_expired
FROM mysql.user
ORDER BY user, host;
-- 查看指定账号的权限
SHOW GRANTS FOR 'biz_order'@'10.0.1.%';
-- 权限分布复核:找出持有全局权限的账号
SELECT user, host, Select_priv, Insert_priv, Update_priv, Delete_priv,
Super_priv, Grant_priv, Repl_slave_priv
FROM mysql.user
WHERE Super_priv = 'Y' OR Grant_priv = 'Y' OR Select_priv = 'Y';
复核的判定要点:
- 逐个账号核对权限与其用途是否匹配,对持有
SUPER、PROCESS、FILE、SHUTDOWN、CREATE USER的账号单独确认必要性。 - 找出长期未使用的账号,结合
mysql.user的password_last_changed与审计记录判断,确认后禁用而非直接删除:ALTER USER 'legacy_user'@'%' ACCOUNT LOCK; -
核对主机范围,将
'%'逐步收敛为具体网段;收敛前需确认应用的实际出口地址。 - 记录每一次权限变更的原因、申请人与回收时间,纳入 3.8 的巡检对比基线。
密码策略与轮换
-- 8.0 启用密码强度校验组件
INSTALL COMPONENT 'file://component_validate_password';
SHOW VARIABLES LIKE 'validate_password%';
-- 设置密码有效期策略
ALTER USER 'biz_order'@'10.0.1.%' PASSWORD EXPIRE INTERVAL 180 DAY;
轮换时使用双密码机制可以让新旧密码在切换期间同时有效,避免应用与数据库改密时序不一致导致的连接失败:
-- 保留当前密码,同时设置新密码,两者均可登录
ALTER USER 'biz_order'@'10.0.1.%' IDENTIFIED BY '<new_password>' RETAIN CURRENT PASSWORD;
-- 应用全部改用新密码后,丢弃旧密码
ALTER USER 'biz_order'@'10.0.1.%' DISCARD OLD PASSWORD;
高危 提醒:修改账号的认证插件或密码会立即影响该账号的后续连接。示例:为兼容旧客户端把账号从 caching_sha2_password 改为 mysql_native_password 时未同时指定密码,authentication_string 被清空,该账号此后无法登录;或在只有一个管理账号的实例上轮换密码失败,实例失去可用的管理入口。兜底方式:
- 操作前确认实例上存在另一个可用的高权限账号,且其密码已知可用
- 在同一实例上保留一个独立的紧急管理账号,仅限本机连接:
CREATE USER 'dba_emergency'@'localhost' IDENTIFIED WITH caching_sha2_password BY '<password>'; GRANT ALL PRIVILEGES ON *.* TO 'dba_emergency'@'localhost' WITH GRANT OPTION; -
修改认证插件时始终同时给出密码,避免哈希被清空:
ALTER USER 'legacy_app'@'10.0.1.%' IDENTIFIED WITH mysql_native_password BY '<password>'; -
修改后立即用新凭据在另一个会话中登录验证,验证通过后再通知应用侧更新配置:
mysql -h 10.0.1.11 -u biz_order -p -e "SELECT USER(), CURRENT_USER();" -
轮换前确认变更单与回滚步骤,回滚即恢复原密码,恢复动作需同样验证
- 保留操作前后的
SHOW GRANTS输出,作为权限变更留痕
审计留痕的开启与留存
-- 临时开启通用查询日志,仅用于短时排查,排查结束后立即关闭
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
SET GLOBAL general_log = ON;
-- 排查完成
SET GLOBAL general_log = OFF;
通用查询日志在写入量大的实例上会显著增加磁盘 I/O,只能短时开启。长期留痕应使用审计插件或依赖 binlog 解析,审计记录的留存周期、访问控制与保护要求遵循 linux: 合规与审计落地 中的日志留存约定。
3.7 日志与空间治理
错误日志与慢日志的轮转
MySQL 支持通过 flush-logs 重新打开日志文件,因此 logrotate 可采用默认的 rename 模式,配合 postrotate 通知服务器:
/var/log/mysql/mysql-error.log
/var/log/mysql/mysql-slow.log {
daily
rotate 30
compress
delaycompress
missingok
notifempty
create 0640 mysql adm
sharedscripts
postrotate
/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf flush-logs > /dev/null 2>&1 || true
endscript
}
要点说明:
mysqladmin flush-logs会同时切换错误日志、慢日志与 binlog 文件,这一点需要在操作记录中说明,避免被误认为只有日志轮转。postrotate失败时|| true会使轮转本身不失败,但日志句柄未重开,出现"新文件为空、旧文件继续写入"的现象,因此每次轮转后应核对新文件是否有内容。delaycompress使最近一份归档不被立即压缩,避免与仍在写入的文件句柄冲突。- logrotate 的调度、状态文件与容量估算方法参见 linux: 日志轮转;MySQL 的日志类型划分参见 mysql: 使用命令 第十一节。
- 慢日志的开关与阈值参数在 mysql: 服务配置 中给出,其中的
log_queries_not_using_indexes会让所有未走索引的语句被记录,写入量大的实例上应评估其日志增长。
手工触发轮转与核对:
# 手动切换日志文件
mysqladmin --defaults-file=/etc/mysql/debian.cnf flush-logs
# 核对轮转后的文件与归档
ls -l --time-style=long-iso /var/log/mysql/
du -sh /var/log/mysql/
# 模拟轮转,检查配置是否正确
logrotate -d /etc/logrotate.d/mysql-server
binlog 的保留与清理
-- 当前状态与文件列表
SHOW MASTER STATUS;
SHOW BINARY LOGS;
-- 从库位点:清理前必须核对每个从库的 Master_Log_File
SHOW SLAVE STATUS\G -- 5.7 与 8.0.22 之前
SHOW REPLICA STATUS\G -- 8.0.22 起
保留期配置:
[mysqld]
# 8.0 使用秒为单位,默认 2592000(30 天)
binlog_expire_logs_seconds = 604800
# 5.7 使用天为单位,默认 0(不自动清理)
# expire_logs_days = 7
高危 提醒:清理 binlog 会破坏从库的日志来源与基于日志的时间点恢复能力,删除范围一旦越过从库位点,复制立即中断且无法通过重新拉取恢复。示例:仅按时间清理 7 天前的日志,而某从库已断连 10 天,清理后该从库报错 1236,只能重建。兜底方式:
-
清理前枚举所有从库(含级联从库、延迟从库、临时复制的下游)并记录其位点,取最小的
Master_Log_File作为清理上界:-- 在每个从库上执行,记录 File 与 Position SHOW SLAVE STATUS\G -
清理使用
PURGE BINARY LOGS TO '目标文件'指定文件,不使用RESET MASTER(后者会清除全部 binlog 并重置编号,风险更高) - 确认清理范围之外的文件已完成备份归档,并保留一份完整备份与其对应的 binlog 起点
- 设置保留期时以"最长可能出现的从库断连时长"与"最近一次全量备份的时间"两者的较大值作为下限
-- 清理到指定文件之前(不含该文件) PURGE BINARY LOGS TO 'binlog.000123'; -
清理后立即检查所有从库的复制状态,确认
Slave_IO_Running与Slave_SQL_Running均为Yes - 对延迟从库与临时复制实例单独核对,不依赖主库的保留期自动清理
容量监控与告警阈值
| 监控对象 | 采集方式 | 阈值参考 |
|---|---|---|
| 数据目录分区使用率 | df -h 或节点监控 | 超过 80% 告警,超过 90% 紧急 |
| binlog 目录占用 | du -sh 或 SHOW BINARY LOGS 汇总 | 超过分区容量的约定比例,或增长率异常 |
| relay log 占用 | 从库数据目录中的 relay-bin* 文件 | 持续增长说明重放停滞 |
tmpdir 占用 | du -sh /tmp 或自定义临时目录 | 出现大排序或临时表时突增 |
| 单表空间 | information_schema.tables | 单表接近单实例容量上限时进入规划 |
| 表碎片率 | data_free 与总占用之比 | 碎片占比高时评估 OPTIMIZE TABLE |
各类日志的容量估算方法与分区余量核对方式,与主机侧日志治理一致,参见 linux: 日志轮转 第 7.8 节;磁盘空间本身的判读与误判规避参见 linux: 巡检与健康检查 第 2.4 节与第 3.5 节。
3.8 监控与巡检
巡检项清单
巡检与告警的分工、频率分层与记录格式参见 linux: 巡检与健康检查 第 1.3 节与第 2.2 节;数据库侧需要补充的项目如下。
| 类别 | 巡检项 | 采集方式 | 判读要点 |
|---|---|---|---|
| 连接 | 当前连接数、运行线程数、拒绝连接数 | Threads_connected、Threads_running、Connection_errors_max_connections | 连接数接近 max_connections 需评估连接池配置 |
| 连接 | 空闲连接与超时 | wait_timeout、Sleep 状态的会话数 | 大量长空闲连接占用连接额度 |
| 复制 | 线程状态 | Slave_IO_Running、Slave_SQL_Running | 任一为 No 立即处置 |
| 复制 | 延迟 | Seconds_Behind_Master、Slave_SQL_Running_State | 延迟趋势与重放状态结合判断 |
| 复制 | GTID 差距 | Retrieved_Gtid_Set 与 Executed_Gtid_Set 的差集 | 差集持续扩大说明重放跟不上 |
| 锁 | 行锁等待 | sys.innodb_lock_waits、performance_schema.data_lock_waits(5.7 为 innodb_lock_waits) | 持续存在等待需定位长事务 |
| 锁 | 元数据锁等待 | sys.schema_table_lock_waits | 出现等待说明有 DDL 受阻 |
| 事务 | 长事务 | information_schema.innodb_trx 的 trx_started | 超过约定时长的事务是 DDL 的主要障碍 |
| SQL | 慢查询 | 慢日志条数与聚合、events_statements_summary_by_digest | 关注新增的语句摘要而非总条数 |
| 空间 | 实例与库容量 | information_schema.tables 汇总 | 与增长趋势对比 |
| 空间 | 表碎片 | data_free | 碎片占比高时安排整理 |
| 日志 | 日志文件增长速率 | 错误日志、慢日志、binlog 的日增量 | 速率突变指向异常写入或配置变更 |
| 配置 | 关键变量基线对比 | character_set_*、collation_*、sql_mode、log_bin、binlog_format、read_only | 与基线不一致需确认来源 |
| 备份 | 备份任务结果 | 任务退出码、备份文件时间戳与大小、备份存储余量 | 仅任务成功不足以确认可恢复 |
| 权限 | 账号与权限变化 | mysql.user、角色定义、SHOW GRANTS 输出快照 | 与上次快照 diff,新增与扩权需确认来源 |
巡检脚本示例
#!/usr/bin/env bash
# MySQL 日常巡检,只读执行
set -uo pipefail
OUT_DIR="/var/log/mysql-inspection"
OUT_FILE="${OUT_DIR}/inspection-$(date '+%Y%m%d-%H%M').log"
mkdir -p "${OUT_DIR}"
mysql -N -B -e "
SELECT CONCAT('connections ',
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_connected'),
' / ',
(SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME='max_connections'));
SELECT CONCAT('threads_running ',
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_running'));
SELECT CONCAT('long_trx ',
(SELECT COUNT(*) FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 300 SECOND));
SELECT CONCAT('lock_waits ',
(SELECT COUNT(*) FROM performance_schema.data_lock_waits));
SELECT CONCAT('repl ',
(SELECT SERVICE_STATE FROM performance_schema.replication_connection_status),
' ',
(SELECT SERVICE_STATE FROM performance_schema.replication_applier_status));
SELECT CONCAT('schema_size_gb ', ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2),
' free_gb ', ROUND(SUM(data_free) / 1024 / 1024 / 1024, 2))
FROM information_schema.tables;
SELECT CONCAT('top_table ', table_schema, '.', table_name, ' ',
ROUND((data_length + index_length) / 1024 / 1024 / 1024, 2), 'G')
FROM information_schema.tables
ORDER BY data_length + index_length DESC LIMIT 5;
SELECT CONCAT('charset_issue ',
(SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
AND table_collation NOT LIKE 'utf8mb4%'));
" 2>/dev/null | tee -a "${OUT_FILE}"
# 复制延迟与磁盘余量
mysql -N -B -e "SHOW REPLICA STATUS\G" 2>/dev/null \
| grep -E 'Running:|Seconds_Behind' | tee -a "${OUT_FILE}"
df -hT | grep -v tmpfs | tee -a "${OUT_FILE}"
脚本中使用了 8.0 的 performance_schema.replication_* 与 data_lock_waits 表;在 5.7 上应改用 SHOW SLAVE STATUS 与 information_schema.innodb_lock_waits。巡检与告警的配合原则是:阈值稳定的项目下沉为监控告警,无法用单一阈值表达的项目(配置基线对比、权限快照比对、备份结果核对)留在巡检中。
结果归档与趋势分析
巡检结果按日期命名归档并保留,关注三类变化:
- 新增异常项:此前正常、本次异常,优先级最高。
- 状态升级:从警告变为严重,说明未处置的事项在恶化。
- 数值同向漂移:状态未变但容量、连接数、慢 SQL 数量单向上行,属于趋势预警。
数据库容量类趋势的最小可分析口径是按周记录库容量、单表 TopN、binlog 日增量与慢查询摘要数量,按月对比斜率;归档目录自身也需纳入轮转,避免巡检记录占满分区。
3.9 容量规划
数据增长估算
-- 各库当前容量
SELECT table_schema,
ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb,
ROUND(SUM(data_free) / 1024 / 1024 / 1024, 2) AS free_gb,
COUNT(*) AS table_count
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_gb DESC;
-- 单表容量与行数估算
SELECT table_name, table_rows,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
ROUND(data_length / NULLIF(table_rows, 0), 2) AS avg_row_bytes
FROM information_schema.tables
WHERE table_schema = 'app'
ORDER BY data_length + index_length DESC
LIMIT 20;
估算时需要注意:table_rows 在 InnoDB 下来自统计信息,误差可能较大,容量判断应以 data_length + index_length 为准,行数用于趋势参考。字节级的平均行长可结合业务写入速率折算增长速率,但增长并非线性,业务活动、日志类表与归档策略都会改变斜率。
单实例容量上限的判断依据
容量上限不是单一数字,而是多个约束中最早触发的那个:
- 内存与数据量的比例。
innodb_buffer_pool_size能覆盖的活跃数据比例下降后,随机读增加,查询延迟上升;当活跃数据远大于缓冲池时,延迟对磁盘性能高度敏感。 - 备份与恢复窗口。备份耗时与数据量成正比,恢复耗时更长;当恢复时长超过业务可接受的中断时长时,实例容量已到上限,与磁盘是否还有空间无关。
- DDL 与维护窗口。单表大到无法在可用窗口内完成重建时,常规结构变更手段失效。
- 单机资源上限。CPU 核数、磁盘 IOPS 与网络带宽限制了实例可承载的并发与吞吐。
- 故障切换代价。从库追平时间、主从切换后的容量承接能力决定实例规模的可用上限。
分库分表的触发条件
以下条件用于判断是否需要拆分,拆分方案本身不在本篇展开:
- 单表容量或行数达到该实例上维护操作无法在可用窗口内完成的量级。
- 单实例数据量使备份与恢复时长超过业务可接受的中断时长,且无法通过物理备份或增量手段压缩。
- 单实例写入吞吐接近磁盘或 CPU 上限,垂直扩容已达到机型上限。
- 业务上存在天然的水平切分维度(租户、地域、时间),且已具备跨片查询的收敛能力。
- 单库连接数长期接近
max_connections,而连接池配置已优化到位。 - 已具备分片后的运维能力:分片路由、跨片事务处理、分片级备份与变更编排。
条件 6 常被忽略:缺少对应运维与研发能力的拆分,会把单实例的容量问题转化为分布式一致性问题。
3.10 版本升级与迁移
5.7 到 8.0 的主要差异与兼容风险
| 差异项 | 5.7 行为 | 8.0 行为 | 兼容风险 |
|---|---|---|---|
| 默认字符集 | latin1 | utf8mb4 | 未显式指定字符集的建表语句在升级后得到不同结果 |
| 默认排序规则 | latin1_swedish_ci 或表级指定 | utf8mb4_0900_ai_ci | 新旧表排序规则不一致时关联查询报排序规则冲突 |
| 默认认证插件 | mysql_native_password | caching_sha2_password | 旧客户端与旧驱动无法连接,需升级驱动或改插件 |
| 数据字典 | 每表 .frm 文件加存储引擎元数据 | 统一的数据字典,无 .frm 文件 | 无法通过文件层面的方式回退到 5.7 |
| DDL 原子性 | 非原子,中途崩溃可能留下不一致 | 原子 DDL | 依赖非原子 DDL 的脚本行为会变化 |
| 保留字 | 无 RANK、GROUPS、SYSTEM 等 | 新增若干保留字 | 表名或列名与保留字冲突时语句报语法错误 |
sql_mode | 含 NO_AUTO_CREATE_USER | 该模式被移除 | 配置中保留该值会导致启动参数校验失败 |
| 复制语句 | SHOW SLAVE STATUS、log_slow_slave_statements | SHOW REPLICA STATUS、log_slow_replica_statements | 监控脚本与配置项名称需要同步替换 |
| 废弃参数 | query_cache_*、innodb_file_format、expire_logs_days | 前两者移除,expire_logs_days 被 binlog_expire_logs_seconds 取代 | 配置文件保留废弃参数会影响启动 |
| undo 表空间 | innodb_undo_tablespaces 可设为 0 | 最少 2 个,且不可单独删除 | 涉及 undo 的管理脚本失效 |
| GTID 开启 | 可一步 SET GLOBAL gtid_mode = ON | 需按 OFF、OFF_PERMISSIVE、ON_PERMISSIVE、ON 逐级切换 | 沿用旧步骤会失败 |
| 事务隔离变量 | tx_isolation | transaction_isolation | 配置与脚本中的旧变量名不再生效 |
升级前的检查清单
# 官方升级检查工具:输出不兼容项与风险提示
mysqlsh -- util check-for-server-upgrade root@10.0.1.11:3306 \
--target-version=8.0.36 --outputFormat=JSON
-- 存储引擎与表数量(表数量过大影响升级耗时)
SELECT engine, COUNT(*) FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
GROUP BY engine;
-- 非 utf8mb4 的库表与列(同 3.5)
SELECT table_schema, table_name, table_collation
FROM information_schema.tables
WHERE table_collation NOT LIKE 'utf8mb4%'
AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys');
-- 与 8.0 保留字冲突的标识符(示例关键词,需与 8.0 关键字清单完整比对)
SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE UPPER(column_name) IN
('RANK','DENSE_RANK','ROW_NUMBER','GROUPS','SYSTEM','WINDOW','LEAD','LAG','OVER','RECURSIVE')
AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys');
-- 外键命名冲突与重复
SELECT constraint_name, COUNT(*) FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY'
GROUP BY constraint_name HAVING COUNT(*) > 1;
-- 当前 sql_mode 与字符集配置
SELECT @@sql_mode, @@character_set_server, @@collation_server, @@binlog_format;
# 数据文件层面的完整性校验
mysqlcheck -u root -p --all-databases --check-upgrade
检查清单要点:
- 驱动与客户端版本是否支持
caching_sha2_password,客户端工具版本是否与 8.0 匹配。 - 配置文件中是否存在被移除的参数,
NO_AUTO_CREATE_USER是否出现在sql_mode中。 - 是否存在与新增保留字冲突的表名、列名与索引名。
- 所有对数据字典有直接依赖的运维脚本(备份、对比、权限导出)是否适配。
- 表数量与总容量是否在升级可承受范围内,升级过程的元数据转换会占用额外时间。
- 从库数量与升级顺序是否已确定。
- 回滚依据是否具备:全量备份完成且恢复验证通过,或已准备好可回退的实例副本。
高危 提醒:从 5.7 升级到 8.0 后的降级几乎不可实施。8.0 首次启动即完成数据字典升级,原有 .frm 文件被取代,5.7 无法读取升级后的数据目录。示例:在未做实例级快照的情况下直接原地升级主库,升级后发现应用兼容问题,此时既无法回退到 5.7,备份也不在当前实例上,只能以备份重建并接受数据损失。兜底方式:
- 升级前完成一次全量备份并验证可恢复,备份与恢复方法参见 linux: 备份与容灾
- 优先采用可回退的路径:先升级从库并观察、再执行主从切换、最后处理原主库;或搭建 8.0 新实例通过复制与切换迁移,保留 5.7 实例作为回退点
- 在非生产环境用生产数据的副本完成一次完整升级演练,记录不兼容项与实际耗时
- 保留升级前的主机或卷快照,明确其保留时间与恢复方式
- 升级后核对关键业务读写路径、复制状态与字符集设置,确认无误后再清理回退资源
升级步骤
以主从架构的原地升级为例,顺序安排如下:
- 完成全量备份与快照,记录当前版本、复制拓扑、GTID 集合与关键参数。
- 在从库上执行升级检查工具,处理检查结果中的阻断项。
- 升级从库:停止复制、关闭实例、替换二进制、执行升级、启动并核对数据字典与复制状态。
- 从库升级完成并追平后,执行受控的主从切换,使升级后的实例承担写流量。
- 观察业务指标与错误日志,确认无异常后升级原主库,并将其重新接入为从库。
- 用 5.7 时代的语句与参数检查配置与脚本,替换
SHOW SLAVE STATUS等已废弃用法。
迁移到新实例
迁移除版本升级外还用于硬件更换与机房调整,方式与校验对照如下:
| 方式 | 适用场景 | 校验手段 |
|---|---|---|
| 逻辑导出导入(mysqldump、mydumper) | 数据量中等、允许较长停机或需跨版本 | 行数比对、CHECKSUM TABLE、抽样比对 |
| 物理备份恢复(xtrabackup 等) | 数据量大、要求最短停机 | 备份校验、恢复后全量校验和比对 |
| 克隆插件或存储层克隆 | 同版本实例间快速复制 | 复制状态与校验和比对 |
| 主从复制加切换 | 要求接近零停机 | 追平后校验和比对,再执行切换 |
迁移完成后必须完成的校验:行数逐表比对、关键表校验和比对、复制状态确认、字符集与排序规则核对、权限与账号迁移核对(mysql.user 与角色定义)、计划任务与外部依赖的连接地址更新。校验工具类方案可使用 pt-table-checksum,其原理是通过分块计算校验和并比对各分片结果,适合迁移后的量化验收。
四、总结
- 结构变更的风险来自元数据锁阻塞、表重建、磁盘膨胀、复制延迟与业务中断五处,彼此之间存在放大关系;日常治理的风险来自日志与 binlog 膨胀、binlog 未消费、权限累积、字符集与 SQL 模式不统一等长期累积项
- 在线 DDL 通过 ALGORITHM 与 LOCK 约束执行方式:INSTANT 只改元数据、INPLACE 多数不复制全量数据、COPY 需要重建表;INSTANT 的支持范围以小版本分界(8.0.12 支持末尾加列,8.0.29 支持任意位置加列与删列),并存在行版本累积上限
- 元数据锁采用写优先策略,DDL 一旦等待元数据写锁,该表的后续查询会被排在其后,阻塞范围从一条语句扩散到全部请求;因此 DDL 的安全前提是没有长事务持有该表的元数据读锁
- 大表变更的代价由表重建、磁盘空间、I/O 争用、binlog 生成量、复制延迟与锁等待六部分构成,可通过选择更轻的算法或改用外部工具把一次长操作拆为可控的分批操作
- gh-ost 与 pt-online-schema-change 的共同思路是影子表、增量同步、逐步拷贝与原子切换;前者基于 binlog 无触发器侵入,后者基于触发器但要求表上无既有触发器,切换阶段的 RENAME 都需要短暂的元数据锁
- 字符集与排序规则需要服务端、连接层、列层三层一致;utf8mb3 不支持四字节字符,8.0 的默认排序规则与 5.7 不同,排序规则差异会造成关联查询冲突与唯一性判定变化
- SQL 模式决定非法值的处理方式,严格模式把问题暴露在写入时刻,宽松模式推迟到数据使用时刻;模式不一致是迁移与主从切换后的数据差异来源,应纳入配置基线
- 权限治理收敛到账号、主机与权限三项,配合角色复用、密码轮换与留痕;管理类账号与业务账号分离,审计手段在通用查询日志、审计插件与 binlog 解析之间按代价取舍
- 日志与空间治理的关键是识别各日志的产生速率与保留需求,binlog 清理必须先核对所有从库位点,容量模型需计入 tmpdir 与变更期间的重建空间
- 巡检覆盖连接、复制、锁与事务、慢查询、空间、日志速率、配置基线与权限快照,与告警分工明确,结果按周期归档并做趋势对比
- 容量上限由内存与数据量比例、备份恢复窗口、DDL 窗口、单机资源与切换代价共同决定;分库分表的触发条件还需包含是否具备分片后的运维与研发能力
- 5.7 升级到 8.0 的风险集中在默认字符集与排序规则、认证插件、保留字、废弃参数与变量改名;升级后无法直接降级,必须依赖备份或保留可回退的实例副本
- 变更前的评估清单、变更期间的观察指标与熔断条件、执行后的验证与回滚预案,共同构成数据库变更的安全边界;高危操作都需要在事前确认备份可用并明确兜底路径