Skip to content
中间件
MySQL
备份恢复与性能调优

备份恢复与性能调优

备份的完整谱系：mysqldump/mydumper/XtraBackup/Clone 四条路线、binlog 归档与 PITR 精确到秒的恢复，再加上监控指标解读、参数调优分层与安全加固清单。

为什么有 RAID 和主从，仍然必须备份

先破除三个常见错觉：

  • RAID 不是备份：RAID 防的是磁盘物理损坏。它防不了 DROP TABLE、防不了应用 bug 写脏数据、防不了勒索加密。
  • 主从不是备份：复制是实时同步——你在主库误删一行，毫秒级之内所有从库一起删。而且主从同属一个机房/一个云账号时，火灾、误操作集群销毁、账号欠费停机是"一锅端"。
  • 快照不是完整备份：云盘快照一致性依赖文件系统级 freeze，裸拷数据目录在不一致状态下恢复可能连 InnoDB 都起不来。

备份真正保护的是：人为错误、软件缺陷、逻辑损坏、整库丢失。这四类事故没有一类是 RAID 或复制能兜的。DBA 圈的铁律：没做过恢复演练的备份，等于没有备份。

备份分类坐标轴

选工具之前，先用三根轴把自己的需求定位清楚：

逻辑 vs 物理   —— 导出 SQL 语句/数据行，还是直接拷数据文件？
热 / 温 / 冷   —— 备份时业务能不能正常读写？
全量 / 增量 / binlog 归档 —— 备份的是整个库、变化的部分，还是变化流水？
  • 逻辑备份（mysqldump、mydumper）：产物是 SQL，跨版本跨平台能力强，可单表挑出来恢复，恢复慢（要逐条重放）。
  • 物理备份（XtraBackup）：产物是数据文件拷贝 + redo 补齐，备份快恢复也快，但版本强绑定、不能"只恢复一张表"（8.0 起可通过独立表空间Transportable tablespace 单表搬运，复杂度高）。
  • 快照/Clone：介于两者之间的"整实例拷贝"，快，但不解决"回到过去某时刻"。

一句话决策：GB 级用逻辑，百 GB 以上用物理，全量物理 + binlog 归档 = 能 PITR 的组合。

把三根轴合成一个选型流程：

数据量多大？
 |
 +-- < 10G 或需单表粒度恢复  -------->  mysqldump --single-transaction
 |
 +-- 10G ~ 100G  ------------------>  mydumper 并行导出/并行恢复
 |
 +-- > 100G 或恢复时长敏感  -------->  XtraBackup 物理全量 + 增量链

正交的两问（不影响上面选路，影响的是"还缺什么"）：
- 需要"恢复到任意时刻" -> 无论哪条路，binlog 实时归档都要补上
- 只是搭从库/整库搬迁   -> Clone plugin 一步到位，但它不进备份计划

注意最后一问和前三问不冲突：一台生产库可以同时有"每周 XtraBackup + binlog 归档"的备份计划，和"用 Clone 快速补一台从库"的初始化手册——工具是为场景服务的，不是为站队的。

mysqldump：小库之王，逐参数讲透

mysqldump 导出一堆 INSERT/CREATE 语句，是"逻辑备份 + 热备 + 全量"的合体。

核心参数账本 

mysqldump -uroot -p \
  --single-transaction \      # 关键：InnoDB 一致性快照，不锁表
  --routines --events \       # 默认不导出存储过程和事件，务必显式带上
  --triggers \                # 默认开启，写出是为了显式表达
  --set-gtid-purged=ON \      # GTID 集合写入文件，搭从库/恢复后衔接复制用
  --hex-blob \                # BLOB/二进制转十六进制字面量，防跨字符集乱码
  --default-character-set=utf8mb4 \
  --source-data=2 \           # 8.0.26+ 新名，旧名 --master-data（8.4 标注弃用但仍可用）；位点/GTID 写为注释
  --all-databases > full_$(date +%F).sql

逐个说清为什么：

  • --single-transaction：在所有表导出前开一个 START TRANSACTION WITH CONSISTENT SNAPSHOT，利用 InnoDB 的 MVCC（原理见 06-MVCC）拿到一致性视图——导出期间别人随便写，dump 里都是"快照那一刻"的数据。只对 InnoDB 有效；对 MyISAM 表它不设锁也不等待，导出结果可能是撕裂的（MyISAM 只能靠 --lock-tables 或停写）。
  • --source-data=2：在文件头部以注释形式写入 CHANGE REPLICATION SOURCE TO SOURCE_FILE='binlog.000042', SOURCE_POS=...（配 --single-transaction + GTID 时其实用 --set-gtid-purged=ON 更优）。1 是直接生成可执行语句——恢复脚本一旦误执行会把从库身份改掉，所以几乎所有人都该用 2。旧版本叫 --master-data，含义相同。
  • --lock-tables / -l：温备份路线。给所有表加读锁直到导出结束，MyISAM 必须用它；对 InnoDB 用 --single-transaction 更好。--lock-all-tables 锁全实例（等价 FTWRL），基本只在"就要一个绝对静止点"时用。
  • --set-gtid-purged=ON|AUTO|OFF：决定恢复文件里是否带 SET @@GLOBAL.GTID_PURGED。带从库场景必须 ON；恢复到全新独立实例且不想继承 GTID 账本时用 OFF。
  • --hex-blob：二进制数据在 SQL 里以 0x... 表示，绕开字符集转换对字节的破坏。有 BLOB/VARBINARY 时永远开着。
  • --where / 指定单表：mysqldump db t --where="created_at>='2026-01-01'" 做局部归档。注意单表导出不带外键依赖表——--no-tablespaces、--skip-add-drop-table 等杂项按恢复目标取舍，拿不准就查官方文档别猜。

权限：至少 SELECT + PROCESS + RELOAD（8.0 用 BACKUP_ADMIN 路线的 dump 线程）+ LOCK TABLES/SHOW VIEW + TRIGGER。给备份账号建一个最小权限组合比用 root 体面得多。

恢复流程 

# 1. 建库（dump 里若无 CREATE DATABASE，需先手建）
mysql -uroot -p -e "CREATE DATABASE IF NOT EXISTS shopdb"
# 2. 重放
mysql -uroot -p shopdb < full_2026-10-20.sql
# 3. 检查：表数量、关键行数、gtid_executed、应用层冒烟
mysql -uroot -p -e "SELECT table_name FROM information_schema.tables
  WHERE table_schema='shopdb' ORDER BY 1"

注意：恢复前先确认目标实例的 sql_mode、字符集（03 章）、以及是否开着 super_read_only（从库身份恢复会写不进去）。大 dump 恢复时把 innodb_flush_log_at_trx_commit=2、关掉 sync_binlog 临时提速，恢复完改回来——这是临时实验环境手法，别写进长期配置。

性能现实要认清：mysqldump 单线程读、恢复时单线程逐条 INSERT，每秒几十 MB 都算快。几个 GB 的库用它很省心；上百 GB 它会让你怀疑人生，此时换 mydumper 或 XtraBackup。

mydumper / myloader：并行逻辑备份主力

mydumper 把 mysqldump 的两个瓶颈都拆了：多线程分块导出（每个表可切成多个 chunk 并行）、并行恢复（myloader 按表/块并行灌入），并且对大单表用 --rows 强制切分行块，避免"一张 5 亿行的表卡住整个备份"。

# 导出：8 线程，每 20 万行切一块，InnoDB 一致性快照
mydumper -h 10.0.0.1 -u backup -p 'xxx' -B shopdb \
  --threads=8 --rows=200000 --trx-consistency-only \
  --output-directory=/backup/dump_2026-10-20

# 恢复：并行灌入
myloader -d /backup/dump_2026-10-20 --threads=8 --overwrite-tables

一致性快照机制：mydumper 先全局 FLUSH TABLES WITH CONSISTENT SNAPSHOT + 记录 master 信息，InnoDB 表在事务快照里并行导出——和 mysqldump --single-transaction 同一原理的并行版。--trx-consistency-only 表示只保证 InnoDB 一致性（接受 MyISAM 不保证，通常是正确取舍）。

为什么社区主力是它而不是官方的 mysqlpump：官方曾提供 mysqlpump 试图做并行逻辑导出，但长期存在功能缺口与维护力度问题，其当前弃用/维护状态请以对应版本文档为准——实践中大多数团队的备份工具栈是 mydumper（逻辑并行）+ XtraBackup（物理），不选 mysqlpump。注意 mydumper 的 GTID/字符集细节参数较多（--no-lock、--sync-thread-limit），首次上生产拿测试库对账验证后再固化。

XtraBackup：物理备份三步曲

Percona XtraBackup 的思路：热拷数据文件 + 持续收集期间产生的 redo log，备份完在 prepare 阶段用 redo 把拷"脏"的文件回放到一致点——相当于给整个数据目录做了一次崩溃恢复。

与版本强绑定

XtraBackup 的大版本跟着 MySQL 走（8.0.x 备份 MySQL 8.0）。MySQL 8.4 用 XtraBackup 前必须先在 Percona 官方兼容矩阵确认当前版本声明支持 8.4；不匹配时报错往往很怪（LSN 格式/表空间结构差异），别硬试。

备份 → prepare → 恢复 

# 主库/从库皆可：全量物理备份（从库做物理备备份更合适，主库零感知）
xtrabackup --backup --target-dir=/backup/full \
  --user=backup --password='xxx'

# 关键一步：prepare，回放 redo 使数据文件一致
xtrabackup --prepare --target-dir=/backup/full

# 恢复到空目录（目标 datadir 必须为空，否则拒绝执行）
xtrabackup --copy-back --target-dir=/backup/full \
  --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql

增量备份靠 LSN 链：全量记录检查点 LSN，增量只拷贝 LSN 大于基线的数据页，prepare 时把增量 redo 按序 --apply-log-only 合并进全量：

xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full ...
xtrabackup --prepare --apply-log-only --target-dir=/backup/full \
  --incremental-dir=/backup/inc1
xtrabackup --prepare --target-dir=/backup/full   # 最后一次 prepare 不加 --apply-log-only

注意：--prepare 对全量目录执行不可逆地改写数据文件——最后一次 prepare（不带 --apply-log-only）跑完后，这个 full 目录不能再作为增量基线。链条断一环，整套增量全部作废，必须重做全量。增量备份的演练比全量更重要，因为它的失败模式更隐蔽。

备份账号在 8.0 上需要 BACKUP_ADMIN、PROCESS、REPLICATION CLIENT 等权限（官方文档有完整清单，逐项 GRANT 不要直接给 SUPER——8.4 里 SUPER 已被拆分弃用）。

恢复后想接续复制：物理备份目录里有个 xtrabackup_binlog_info / xtrabackup_checkpoints 文件，记录了备份结束时的 binlog 位点/LSN/（GTID 环境下）gtid_purged——用它生成 CHANGE REPLICATION SOURCE TO，即可用"昨晚的全量物理备份"重建从库，这也是 10 章路径 C 的完整拼图。

Clone plugin：轻量整库克隆（8.0.17+）

不需要保留期、不需要增量时的整实例拷贝——上面 10 章已给命令，这里补边界：

-- 源端账号：GRANT BACKUP_ADMIN
-- 目标端：GRANT CLONE_ADMIN；SET GLOBAL clone_valid_donor_list='10.0.0.1:3306'
CLONE INSTANCE FROM 'cloner'@'10.0.0.1':3306 IDENTIFIED BY 'xxx';
-- 完成后重启实例；本地拷贝单表空间：CLONE LOCAL DATA DIRECTORY '/data/tbs'

适用：搭从库初始化、同大版本机器迁移、开发环境复制生产数据。局限：它不是备份工具——没有保留期、没有异地归档、不能恢复到过去的时间点，且要求网络直连与版本兼容。别把它写进备份计划，把它写进"初始化手册"。

大库跑 Clone 时盯进度，别干等：

SELECT stage, state, scan_elapsed, transfer_rate
  FROM performance_schema.clone_progress;   -- 按 data/statelog 等阶段看传输量
SELECT * FROM performance_schema.clone_status\G  -- 整体完成/失败状态

传输速率异常低通常是 clone 走的网络被限速，或在跨可用区拉数据——安排在业务低峰，几百 G 的实例克隆期间源端 IO 是有真实开销的。

binlog 归档与 PITR：恢复到任意时刻

全量备份给你"每天一个立足点"，binlog 给你"立足点之后的每一秒"。两者拼起来才是 PITR（Point-in-Time Recovery）。

归档 binlog 的两条路线

路线 A：主库保留 + 定期搬运（简单）。binlog_expire_logs_seconds 留够 7-30 天，再每天 rsync/对象存储搬运 --read-from-remote-server 拉的文件：

mysqlbinlog --read-from-remote-server --host=10.0.0.1 --user=archiver \
  --password='xxx' --raw --stop-never binlog.000042 ...

路线 B：从库当归档点——从库 log_replica_updates=ON 时自身 binlog 即完整副本，直接归档从库的，主库保留期可以缩短。

恢复指定时刻

场景：今天 14:32 跑了个坏脚本，要恢复到 14:31。

# 1. 恢复最近一次全量（mysqldump 或 XtraBackup 已 prepare 好并起实例）
# 2. 重放全量之后、事故之前的 binlog
mysqlbinlog --start-datetime='2026-10-20 00:00:00' \
            --stop-datetime='2026-10-20 14:31:00' \
            --database=shopdb \
            binlog.000041 binlog.000042 | mysql -uroot -p

# 也可用位点定位：--start-position/--stop-position（配合 SHOW BINLOG EVENTS 找到事故事件位置）

GTID 环境重放的常见障碍：binlog 里的 GTID 与目标实例 gtid_executed 冲突（全量恢复已经"执行过"这批 GTID）。mysqlbinlog 提供 --skip-gtids 剥离 GTID 语句重放，或在恢复到旁路实例后接管复制身份——两种处理各有流派，务必先在测试库走一遍全流程，本文不假装存在一条无脑通用的命令。

误删 UPDATE 的"闪回"思路

前提条件苛刻但值得知道：binlog_format=ROW + binlog_row_image=FULL（8.0 默认）时，binlog 里保存了行修改前后的完整镜像。把 ROW 事件反向翻译，UPDATE 旧值→新值 变成 UPDATE 新值→旧值，DELETE 变 INSERT，INSERT 变 DELETE——这就是闪回（flashback）。

生态：binlog-utils/2sql（美团）、MyFlash（去哪儿）、mysqlbinlog 工具链派生实现。典型用法：

# 用 2sql 类工具从指定时间段/位置的 binlog 反向生成回滚 SQL
2sql --server=... --begin-time="2026-10-20 14:30:00" --end-time="2026-10-20 14:33:00" \
     --database=shopdb --back-interval=10 --type=UPDATE --binlog-file=binlog.000042

注意：闪回只救"UPDATE/DELETE 错数据"，不救 DROP（表结构都没了，镜像也在被 drop 的对象里）；DELETE 闪回需要原表还在。生成的回滚 SQL 先人工审、先在影子表试——它是机器反演的语句，不是经过验证的修复方案。“演练过才算数"在这里同样是铁律：每季度把一条误删的恢复从头到尾跑一遍，包括闪回路径。

延迟复制：给数据买个"后悔时间窗”

10 章的 CHANGE REPLICATION SOURCE TO SOURCE_DELAY=3600（8.0.23+ 新名，旧 MASTER_DELAY）让从库故意落后 1 小时执行。主库 14:32 误删，15:00 发现：

-- 从延迟从库上：停掉 SQL 线程（IO 线程继续收，数据不缺）
STOP REPLICA SQL_THREAD;
-- 把从库进度推到事故前一刻：按 GTID 跳过错误事务 或 按时间回放
-- 然后把从库提升为新主（见 10 章切换 SOP），原主重灌

延迟从库 = “一台时间机器”。它便宜、零工具依赖，但恢复的粒度是"整库回到一小时前"，且从库本身不再对外提供服务——关键读流量别路由给它。

三套恢复剧本（背下来的 SOP）

剧本 1：单表误删/误改

  1. 立即停相关应用写入，保留现场；先 SHOW BINLOG EVENTS/确认事故 binlog 位置。
  2. 表被 DROP 且有从库（延迟从库最佳）：从从库 mysqldump 单表 导回主库；无延迟从库则用最近全量恢复到临时实例再导回。 MySQL 没有生产可用的原生"回收站"（DROP TABLE 直接删表空间），很多团队的解法是约定删除走重命名：删表前先 RENAME TABLE orders TO orders_deleted_20261020，观察期（如 72 小时）后再真正 DROP。把这条写进删除工单模板，等于给 DROP 加了个可撤销层——它不替代备份，只是把"当天发现"的恢复成本从几小时降到一条 RENAME。
  3. 数据被改错（UPDATE/DELETE）：走闪回工具反演回滚 SQL，审核 → 影子表验证 → 小事务分批回滚。
  4. 事后复盘：该表为什么能整表误删？权限收口（见安全加固节）。

剧本 2：整库损坏/误 DROP DATABASE

  1. 隔离：停写，防止应用继续报错写日志淹掉现场。
  2. 找立足点：最近全量（mysqldump/XtraBackup）。
  3. 旁路恢复：恢复到一台干净实例（或指定时间点 PITR），验证行数与业务关键查询。
  4. 数据导回原实例，或直接把业务切到恢复实例（配合 10 章切换流程）。

剧本 3：主机报废（机器没了）

  1. 有从库：10 章手工切换 SOP 五步提主——这是最快路径（RTO 分钟级）。
  2. 无备可提：用异地/对象存储里的物理备份在新机 copy-back，或 Clone 拉一台同版本空实例（若源也坏了则只剩备份路线），再 PITR 补 binlog 到最新。
  3. 演练要点：剧本 3 只有在"备份文件真的在异地"时成立——这就是下一节 3-2-1 的存在意义。

备份策略清单

  • 3-2-1 原则：3 份副本、2 种介质、1 份异地（对象存储/跨机房）。只在本地磁盘留备份，等于把鸡蛋放在同一场火灾里。
  • 节奏：物理全量每周一次 + 每日增量（XtraBackup 链条）；或每日 mysqldump 全量（小库）；binlog 实时归档保证任意时刻可回。
  • 保留期：全量留 4-8 周，增量链跟随全量滚动，binlog 归档至少覆盖最长全量周期 + 事故发现延迟（误删经常几天后才被发现——延迟复制窗口、binlog 保留都要对齐这个"发现时差"）。
  • 大小与现实预期：几百 GB 的库 dump 会占等量文本空间且恢复数小时；物理备份压缩后小一个量级恢复分钟级（--compress、zstd/qpress，按工具支持确认）。
  • 校验：备份完成做基础检查（文件完整性、备份大小环比突变报警——“今天备份只有昨天的 1%“往往意味着备份脚本悄悄失败了）；更进一步的校验靠恢复演练。
  • 恢复演练：每季度至少一次，抽全量恢复到隔离环境、比对关键表行数、跑业务冒烟。演练本身要有记录（时间、恢复点、耗时、发现的问题），否则等于没练。
  • 备份账号权限最小化 + 备份文件里的敏感数据注意加密/访问控制（mysqldump 是明文）。

监控与关键指标

SHOW GLOBAL STATUS 是计数器累计值，所有"率"都要取两次采样的差值除以时间再解读。

核心计数器速查 

SHOW GLOBAL STATUS WHERE Variable_name IN (
 'Questions','Com_select','com_insert','com_update','com_delete',
 'Uptime','Threads_connected','Threads_running',
 'Opened_tables','Open_tables','Created_tmp_disk_tables','Created_tmp_tables',
 'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',
 'Innodb_row_lock_waits','Innodb_row_lock_time_avg',
 'Rpl_semi_sync_source_status');
  • QPS ≈ Questions 增量/秒（或 Com_select+写类计数相加）。
  • TPS：简单口径用 Com_insert+Com_update+Com_delete 增量；精确口径看 Handler_commit 增量或 binlog 事件数。
  • Threads_running：正在执行（非睡眠）的连接数。持续 > CPU 核数说明并发过载/出现慢堆积，是最值得配报警的一个数。
  • 临时表落盘率 = Created_tmp_disk_tables / Created_tmp_tables，偏高（>25%）指向排序/分组过大、内存临时表阈值太小（tmp_table_size），去 08-EXPLAIN与查询优化 治 SQL，别只调参数。
  • buffer pool 命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests，应 >99%；Innodb_buffer_pool_reads 是物理读次数，增长快说明热数据装不下。
  • Innodb_row_lock_waits / Innodb_row_lock_time_avg：行锁等待次数与平均等待时长，结合 05-事务与锁 的等待事件视图看谁在堵。
  • History list length（SHOW ENGINE INNODB STATUS 的 TRANSACTIONS 段）：长事务未提交导致 undo purge 积压，数字持续增长到几十万级要查有没有大事务/僵尸事务挂着（详见 06-MVCC）。
  • Seconds_Behind_Source：从库延迟（局限见 10 章延迟节）。

SHOW PROCESSLIST 与 sys schema

SHOW FULL PROCESSLIST 看现场：谁在跑、跑多久、状态是什么（Sending data、Waiting for table metadata lock……）。更友好的是 sys schema（8.0 内置）：

SELECT * FROM sys.session;                              -- 非后台线程的实时会话
SELECT * FROM sys.statements_with_full_table_scans
  ORDER BY total_latency DESC LIMIT 10;                -- 谁在全表扫描
SELECT * FROM sys.innodb_lock_waits;                   -- 谁在堵谁
SELECT * FROM sys.statement_analysis LIMIT 10;         -- 累计开销最大的语句摘要

指标体系：mysqld_exporter

单机看板够用一时，集群化监控用 mysqld_exporter 把上述状态变量以 Prometheus 格式暴露，接 Grafana 出趋势图——本库 prometheus/ 手册有 exporter 部署与采集配置，指标选型按本节口径对应即可。给 exporter 建一个只需 PROCESS, REPLICATION CLIENT, SELECT 权限的账号，采集端 .promrc 文件放好 DSN：

# prometheus.yml 片段
scrape_configs:
  - job_name: mysql
    static_configs:
      - targets: ['10.0.0.1:9104', '10.0.0.2:9104']
    # mysqld_exporter 启动需 --config.my-cnf=/etc/.my.cnf 指向只读账号
```报警至少覆盖：实例存活（`mysql_up`）、`Threads_running` 水位、buffer pool 命中率跌破阈值、从库延迟、备份任务完成状态（文件存在 + 大小合理）。

### 错误日志巡检

`mysqld.err` 每天扫一遍：启动/崩溃痕迹、`InnoDB: ...` 告警、证书到期、磁盘写满痕迹。错误日志是最便宜的事前信号，很多"半夜实例起不来"前一天的日志里早有磁盘/inode 报警。

日常巡检可以从这几条 grep 起步，有命中再展开上下文：

```bash
ERR=/var/log/mysqld.log   # 路径以 log_error 配置为准
grep -E '\[ERROR\]|\[Warning\]' "$ERR" | tail -50          # 最近告警面
grep -c 'Aborted connection' "$ERR"                         # 连接异常断开计数（网络/超时线索）
grep -E 'InnoDB: .*assert|Disk full|sort file' "$ERR"      # 崩溃/磁盘/排序溢出硬信号

参数调优分层

调参顺序错了会白忙：先保证 SQL 没问题（08 章），再按 OS → 全局 → 会话 逐层往下，每一层调的是"一类资源"而不是"一个数字”。

OS 层

  • 内存不超卖：buffer pool + 每连接内存 + OS page cache 总和留有余量，宁可小也不允许 MySQL 触发 OOM killer。swap 要么关掉、要么 vm.swappiness=1 级别压低——数据库进程的内存页被换出就是延迟毛刺。
  • 磁盘：XFS/ext4 + noatime；文件系统与块设备的预读按存储厂商建议。InnoDB 推荐 innodb_flush_method=O_DIRECT（绕过 page cache 双重缓存）——8.0/8.4 的默认值与不同平台行为请以当前版本文档实测为准，本手册不替官方文档背书。
  • CPU 频率调节器：performance 模式，别让节能策略在流量高峰期降频。

全局层（实例级）

  • innodb_buffer_pool_size = 物理内存的 50-70%（专用数据库机器）。留出 30-50% 不是玄学：每个连接的 sort/join/tmp 内存、performance_schema 自身、复制回放、OS page cache 都要空间。16G 机器给 10G 是常见配比，给 13G 就是等着出事。
  • redo 容量：8.0.30+ 用单一参数 innodb_redo_log_capacity（取代 innodb_log_file_size×组数）。经验口径：容量 ≥ 单位时间写入量 × checkpoint 年龄希望覆盖的分钟数；太小会频繁激进刷盘（看 Innodb_buffer_pool_pages_flushed 突刺与日志里的 checkpoint 告警）。写入量与刷盘机制的原理账在 07-日志体系。
  • innodb_io_capacity / _max：告诉 InnoDB 你的盘能扛多少后台 IOPS。机械盘 200-400、SATA SSD 2000、NVMe 5000+ 起步，设太低刷盘迟钝、太高正常业务被后台 IO 挤。
  • 刷盘档位矩阵（flush_log_at_trx_commit × sync_binlog，为什么这么选见 07 章）：
场景 trx_commit sync_binlog 含义
金融/订单（不丢） 1 1 每次提交双 fsync，默认
日志/统计类库 2 或 0 100-1000 允许崩溃丢 ≤1 秒 / binlog 攒批
从库（可重建） 2 0 反正能从主库恢复
  • change buffering：它服务于"二级索引页不在 buffer pool 时先缓存变更、后合并"的场景，现代 SSD + 大 buffer pool 下收益持续缩水；社区对默认取向的讨论与新版本的调整方向以官方 release notes 为准，不确定就保持默认，别手动开大。

会话层（每连接内存）

sort_buffer_size、join_buffer_size、read_buffer_size、tmp_table_size/max_heap_table_size、thread_stack 都是每连接、甚至每次操作独立分配的。把它们调大 × max_connections 可能轻松吃光内存。正确姿势：会话级保持较小默认，谁真有需求在应用连接或查询级临时调（SET SESSION sort_buffer_size=...）。

max_connections 的真实参照不是"业务并发数”，而是 连接池总数之和 + 监控/复制/内部连接余量。几百个池 × 几套服务很容易顶到默认 151；与其把它调到 5000 赌内存，不如应用侧把池子收小、上中间件连接复用（ProxySQL 的 connection pooling 正为此生，见 10 章）。

一份 16G 单机示例 my.cnf 

[mysqld]
# ---- 身份与复制基线（00 章讲必配项，这里讲调优组合）----
server_id                = 101
gtid_mode                = ON
enforce_gtid_consistency = ON
binlog_format            = ROW              # 闪回/PITR/半同步的公共前提

# ---- 内存账：16G 机器，buffer pool 留 55%，其余给连接/系统/监控 ----
innodb_buffer_pool_size  = 9G               # 专用 DB 机；混部再减
innodb_buffer_pool_instances = 8            # 大 pool 分片降锁争用
performance_schema       = ON                # 监控的前提，内存预算里记得给它留量

# ---- 日志与刷盘：金融口径双 1；吞吐优先场景按上面矩阵取舍 ----
innodb_redo_log_capacity = 2G               # 与日写入量匹配，指 07 章账本
innodb_flush_log_at_trx_commit = 1
sync_binlog              = 1
binlog_expire_logs_seconds = 604800         # 7 天；覆盖 PITR 窗口

# ---- 磁盘：NVMe/SSD 假设 ----
innodb_io_capacity       = 2000
innodb_io_capacity_max   = 4000
innodb_flush_method      = O_DIRECT         # 版本/平台默认以文档为准

# ---- 连接与超时：配合应用连接池，不设 5000 赌运气 ----
max_connections          = 500
wait_timeout             = 600              # 回收空闲连接
interactive_timeout      = 600

# ---- 会话级内存：小默认，个别大查询用 SET SESSION 临时抬 ----
sort_buffer_size         = 4M
join_buffer_size         = 4M
tmp_table_size           = 64M
max_heap_table_size      = 64M

# ---- 安全：见安全加固节 ----
local_infile             = OFF
require_secure_transport = ON

32G 机器的主要变化：buffer pool 16-20G、redo capacity 4G、max_connections 按池子实际和定；其余结构不变。调参后一周看命中率与锁指标回滚决策，而不是抄完就走。

注意：网上大量中文调优教程仍在讲 query_cache_*、innodb_adaptive_hash_index 调优的"老四件套"——查询缓存在 8.0 已被整体移除（参数都不存在，配了直接起不来），这些教程的其他数字也大概率基于 5.6/5.7。看到 query_cache_size 就先把教程发表年份和基准版本核对一遍，再决定信不信。

安全加固

备份解决"数据没了"，加固解决"数据不该被看到/被改"。

最小权限：从"能连上"到"只能用" 

-- 应用账号：只给一个库的增删改查，别给 DDL/DROP
CREATE USER 'shop_app'@'10.20.%' IDENTIFIED BY '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'shop_app'@'10.20.%';

-- 列级授权：报表账号只能看指定列，敏感列（手机号）不进授权
GRANT SELECT (id, order_no, amount) ON shopdb.orders TO 'report_ro'@'10.20.%';

-- 备份账号：mysqldump/XtraBackup 所需的最小集（按工具对照官方清单）
GRANT SELECT, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT,
      REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO 'backup'@'10.20.%';

权限粒度四级：*.* → db.* → db.table → 列。给账号授权时从细往粗试，不要从粗往细收——粗放的 ALL PRIVILEGES 账号一旦泄露，DROP DATABASE 只需要一条语句的时间。

账号与通道

  • 禁 root 远程：ALTER USER 'root'@'localhost' 保持 host 仅本机；运维登录用个人实名账号 + sudo 式权限，便于审计追责。存量 root@’%’ 先建替代账号再删，别在没人能登录时手滑。
  • TLS：require_secure_transport=ON 拒绝未加密连接；证书用 mysql_config_certificate 自动生成自签即可起步（内网场景），对外服务换受信 CA。注意 8.0 默认认证插件 caching_sha2_password 在非 TLS 连接上需要 RSA key 交换（GET_SOURCE_PUBLIC_KEY 那套，10 章见过），上 TLS 一并消掉这类别扭。
  • local_infile=OFF：LOAD DATA LOCAL 历史上是客户端被恶意服务端利用的通道；不需要就关，需要数据导入用 LOAD_FILE 受限路径或 LOAD DATA 不带 LOCAL。
  • 密码策略：validate_password 组件（8.0 起以 component 形式提供）可约束强度，但各版本默认策略/安装状态不一，8.4 上先查 SELECT * FROM performance_schema.global_variables WHERE variable_name LIKE 'validate_password%'; 确认现状再决定装不装——不写死"默认开启"。
  • 账号生命周期：每季度清理一次无人认领的账号（SELECT user, host, account_locked, password_expired FROM mysql.user 配合 last logon 类审计），测试账号随人走；这是最常被遗忘的加固项。

审计思路：社区无开箱完整 SQL 审计，企业走防火墙/代理层日志 + general log 限时采样（general_log 生产常开是性能灾难，只在排障窗口启用并尽快关闭）；云上直接开数据库审计产品。

日常运维清单

把下面这些变成日历上的例行事件，而不是事故后的动作：

  • 慢日志周报（07 章开关方法）：top 10 耗时 SQL 派单优化；对比上周新增的慢查询——通常是新上线代码。
  • 容量趋势：表大小周环比、磁盘余量、binlog 产出速率（决定 PITR 窗口与备份时长）；information_schema.tables 按月聚合即可。
  • 碎片率判断：DATA_FREE 高的表（information_schema.tables）值得 OPTIMIZE TABLE 回收，但它是重建表级别的大动作（InnoDB 下走 online copy），先评估低峰窗口与空间需求，不要迷信定期跑——表结构稳定、无大批量删除的库常年不需要它。
  • 索引清理：长期 0 使用的索引占空间又拖慢写入，sys.schema_unused_indexes 给候选名单，删前观察一个完整业务周期（大促/月结）再动。
  • 复制健康：两 Running、GTID 差集、Seconds_Behind_Source、半同步状态、延迟从库窗口——10 章字段表的巡检化。
  • 证书与到期：TLS 证书、Kerberos（若有）到期提醒。
  • 版本升级节奏：小版本（安全补丁）跟随厂商建议尽快打；大版本升级前读官方弃用清单（00 章安装基线与 13 章陷阱汇总），先在测试环境跑全量回归。
  • 恢复演练（每季度，见备份策略节）——它同时是对备份、SOP、人员三件事的体检。

实战建议

  • 工具选型按数据量定，不按"听说哪个快"定：< 10G 用 mysqldump 最省心；几十到几百 G 用 XtraBackup 全量 + binlog 归档换 PITR；建从/迁移用 Clone。
  • 任何恢复手段，先在测试环境把"从备份到可用"走通并计时： mysqldump 恢复 50G 可能是几小时起步，真实故障时这个时长就是 RTO，业务必须知道。
  • 备份的价值密度在 binlog：全量谁都会留，“binlog 有没有实时归档到异地"才区分开能 PITR 和只能回到昨晚。
  • 闪回工具、--skip-gtids、增量链修复这些"高级操作”，都先建一张"演练通过清单"，没演练过的路径在事故里不要用。
  • 调参先记账再动手：动 buffer pool 前算一遍内存分配表，动刷盘参数前想清楚丢数据容忍度——07 章的账、06 章的 MVCC 是这些参数背后的物理规律，参数表只是规律的换算。
  • 把"监控报警 + 季度演练 + 权限最小化"三件事各做好一半，胜过把所有时间花在调一个魔法数字上：生产事故里"备份不存在"“root 泄露"“没人会恢复"三类原因，远多于"buffer pool 少给了 2G”。
主从复制与高可用分库分表