MySQL 运维最佳实践

1. 架构与部署最佳实践

1.1 部署架构优先级（推荐）

生产环境优先采用：

场景	推荐架构	特点
强一致性、金融级	MGR（三节点）	自动故障切换、强一致性、无脑运维
高可用但读写分离	Semi-sync 主从 + VIP/Keepalived	成熟、维护简单
高压力 OLTP	Percona XtraDB Cluster（PXC）	可扩展、强同步、提供 ProxySQL
云原生	MySQL Operator / PXC Operator	自动备份 + 自动恢复 + 自动扩缩

1.2 服务器 / 系统层最佳实践

Linux 配置

vm.swappiness = 1
fs.file-max = 1000000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.ip_local_port_range = 10000 65000

硬件建议

内存：数据量至少 2–4 倍
磁盘：NVMe SSD + RAID10
CPU：MySQL 对单核性能极敏感（优先高主频 ≥ 3.0 GHz）

2. 数据库配置（my.cnf）最佳实践

2.1 最重要的 InnoDB 参数

[mysqld]
innodb_buffer_pool_size = 70%~80% 内存
innodb_buffer_pool_instances = CPU 核心 / 2
innodb_log_file_size = 1G~4G
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1

参数解释（融合底层原理）

innodb_buffer_pool_size – 决定 读性能
flush_log_at_trx_commit=1 – 最安全，真正的 ACID
innodb_log_file_size 大 + redo log 更长 = 写放大减少 = TPS 提升
O_DIRECT – 避免 OS Cache 双缓存，减少脏页抖动

2.2 连接池与线程配置

max_connections = 800
thread_cache_size = 256
max_connect_errors = 100000

生产中 99% 的瓶颈不是 max_connections，而是 事务过长导致连接占满。

3. 运维监控与巡检最佳实践

3.1 必须监控的核心指标（KPI）

1）写性能

Innodb_os_log_fsyncs
Log sequence number (LSN) 增速
Redo Log Wait（>0 必须查原因）

2）锁

Innodb_row_lock_waits
Threads_running（>20 就要警惕）

3）IO

innodb_data_reads/writes
fsync time（最重要）

4）复制延迟

MGR：performance_schema.replication_group_member_stats
主从：Seconds_Behind_Master

3.2 每日巡检 Checklist

主从复制正常、无延迟
Redo Log 无等待
TempTable 不超过 5%
没有 full table scan 的慢 SQL
表空间增长合理
备份成功且可恢复
磁盘利用率 < 80%

4. 性能优化最佳实践（融合原理）

4.1 SQL 优化优先级（必遵守）

优化 SQL
加索引
结构优化（反范式）
增加内存（Buffer Pool）
增加节点

👉 绝不要一开始就调 my.cnf。

4.2 索引最佳实践

单表读写 QPS > 1W 必须有覆盖索引
区分度低字段（如性别）不建索引
尽量使用前缀索引减少索引大小

例：

ALTER TABLE users ADD INDEX idx_email(email(20));

4.3 慢 SQL 定位法（最强）

观察 Threads_running

SHOW GLOBAL STATUS LIKE 'Threads_running';

20：数据库已经阻塞
100：系统在雪崩

观察是否存在锁

SELECT * FROM information_schema.innodb_locks;

5. High Availability（高可用）最佳实践

5.1 主从复制最佳实践

binlog_format = ROW
sync_binlog = 1
rpl_semi_sync_master_enabled = ON
rpl_semi_sync_slave_enabled  = ON

关键点：

要保证事务一致性，必须 row 格式 + semi-sync
异步复制一旦主挂了，数据必然丢

5.2 MGR（MySQL Group Replication）最佳实践

部署 3 个节点（odd quorum）
使用 single-primary-mode
必须使用 XA + GTID

防止脑裂：

group_replication_exit_state_action = READ_ONLY

6. 备份与恢复最佳实践（核心）

6.1 必须配置 PITR（Point-In-Time Recovery）

结构：

类型	工具	周期
完全备份	xtrabackup	每日
增量备份	xtrabackup	每小时
Binlog	MySQL	实时

恢复命令：

mysqlbinlog --start-datetime="2025-12-10 10:00:00" \
            --stop-datetime="2025-12-10 10:30:00" \
            binlog.000123 | mysql

7. 常见故障 + 最佳处理方式

7.1 主库压力高（90% 都是这个）

症状

CPU 100%
Threads_running 巨高
复制延迟上升

解决优先级

找慢 SQL
加索引
增加只读实例
拆库拆表
引入 Redis 缓存

👉 重点：不要盲目调参，也不要盲目扩机器。

7.2 磁盘写满

立即：

systemctl stop mysql

否则 MySQL 会自动崩溃并损坏数据。

7.3 undo/redo 爆满

常见原因：长事务（sleeping 也算）。

定位：

SELECT * FROM information_schema.innodb_trx;

8. 安全最佳实践

所有账号必须使用 plugin = caching_sha2_password
禁止 % 全网段访问
root 禁止远程登录
禁止 skip-grant-tables
加密所有数据传输（TLS）

9. 自动化运维最佳实践

必配工具

Prometheus + Grafana（监控）
Ansible（配置管理）
XtraBackup（备份）
Orchestrator（主从切换）
ProxySQL（读写分离）

10. 最佳实践总结（最重要的 10 条）

优先使用 MGR 或 Semi-sync，避免异步复制
所有 SQL 上线必须审核
日常巡检锁、慢 SQL、缓存命中率
SQL 优化永远优先于配置优化
禁止长事务
磁盘必须使用 SSD + RAID10
Buffer Pool 至少占内存 70%
大表不要轻易 ALTER TABLE
必须部署备份 + Binlog（PITR）
每半年做一次“主库切换演练”

按真实企业生产环境标准整理，涵盖配置、备份、高可用、监控、安全、SQL 管控、容量规划等多个方面，实用性极高，可作为长期运维手册。

系统层面最佳实践
配置文件（my.cnf）最佳实践
MySQL 账户与权限最佳实践
数据库结构设计最佳实践
SQL 与索引管理最佳实践
性能优化最佳实践
监控最佳实践
备份与恢复最佳实践
主从复制与高可用最佳实践
数据一致性最佳实践
容量规划最佳实践
安全最佳实践
日常运维规范

1. 系统层面最佳实践（OS）

1.1 文件系统

使用 ext4 或 XFS（生产推荐 XFS）
禁用 atime：noatime
磁盘读写尽量使用 SSD/NVMe（IOPS 直接影响 MySQL 性能）

1.2 系统参数（/etc/sysctl.conf）

vm.swappiness = 1
vm.dirty_ratio = 10
vm.dirty_background_ratio = 5
fs.aio-max-nr = 1048576

1.3 CPU

避免共享 CPU、强隔离环境（KVM/VM 也可以但需要 CPU 直通）
避免开启节能模式（性能下降明显）

2. 配置文件（my.cnf）最佳实践

以下为 16GB 内存参考（可让我要其他内存版本）：

[mysqld]
innodb_buffer_pool_size = 10G
innodb_log_file_size = 1G
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1

max_connections = 400
max_connect_errors = 10000

slow_query_log = 1
long_query_time = 1
log_output = FILE

binlog_format = ROW
binlog_row_image = FULL
gtid_mode = ON
enforce_gtid_consistency = ON

server_id = 1001
log_bin = mysql-bin

关键原则：

Buffer Pool 设置为内存的 60%~70%
Redo Log 越大（1~4GB），写性能越好
binlog 格式必须为 ROW（保证一致性）
必须开启 GTID（复制、运维更安全）

3. 用户与权限最佳实践

最小权限原则

GRANT SELECT, INSERT, UPDATE ON db.* TO 'app'@'10.%.%.%';

不允许 root 远程

CREATE USER 'root'@'%' IDENTIFIED BY 'xxx';
-- ❌ 禁止这样做

账号逻辑分离

读账号
写账号
备份账号
监控账号
运维账号

密码策略

定期 rotate
禁止弱密码

4. 数据库结构设计最佳实践

4.1 字段类型

避免 BIGINT 滥用
VARCHAR(N) 而不要使用 TEXT
枚举类尽量用 TINYINT / SMALLINT
时间用 DATETIME(3)

4.2 索引

每个表最多 3~5 个索引
索引列必须是查询过滤条件
索引命名规则：idx_table_column

4.3 表分区

只适用于 TB 级非常大表
不建议初学者滥用

5. SQL 与索引管理最佳实践

5.1 所有上线 SQL 必须做 EXPLAIN

命中 type 必须为 ref 或以上
避免 ALL（全表扫描）

5.2 严禁以下 SQL 直接上生产

WHERE name LIKE ‘%xxx%’
WHERE id != xxx
大表 FULL JOIN

5.3 定期分析慢 SQL

使用 pt-query-digest：

pt-query-digest /var/log/mysql/slow.log > slow_report.txt

6. 性能优化最佳实践

优化顺序不要反了

SQL > 索引 > 架构 > 配置 > 硬件

千万不要先调 MySQL 配置。

大事务拆分

不要执行超过 1s 的事务，避免：

持锁时间长
产生大量 undo
主从延迟

7. 监控最佳实践

必须监控的指标（核心）：

7.1 连接

Threads_running
Threads_connected

7.2 事务与锁

innodb_row_lock_time_avg
innodb_deadlocks
innodb_trx

7.3 Buffer Pool

命中率（>99%）
innodb_buffer_pool_reads

7.4 延迟与 QPS

QPS / TPS
慢查询数

7.5 IO

磁盘延迟 > 10ms 就危险
IOPS 峰值

监控工具：

Prometheus + mysqld_exporter
Grafana Dashboard
阿里云 RDS 监控模版

8. 备份与恢复最佳实践

8.1 备份策略（企业成熟做法）

每日全量（xtrabackup）
每小时增量（binlog）
异地存储（对象存储）
每周一次恢复演练

8.2 恢复最佳实践

恢复到测试环境
对比 checksum
验证主从一致性

9. 高可用最佳实践（HA）

从一致性和可用性上，从高到低：

方案	一致性	可用性	企业推荐
MySQL InnoDB Cluster（GR）	★★★★★	★★★★★	⭐⭐⭐⭐⭐
MySQL Group Replication	★★★★★	★★★★★	⭐⭐⭐⭐⭐
Orchestrator + 主从	★★	★★★★★	⭐⭐⭐⭐
MHA	★★★	★★★★	⭐⭐⭐

优先使用：

Group Replication（强一致性）
InnoDB Cluster（官方全家桶）

10. 数据一致性最佳实践

企业级一致性方案：

pt-table-checksum（推荐）
pt-table-sync
同步时使用 ROW binlog
避免 “并发更新 + 读写分离” 导致脏读

11. 容量规划最佳实践

11.1 预估增长

每个表容量
每天新增数据量
30/90/365 天增长趋势

11.2 数据分区策略

分库分表（主键 hash）
按日期分表（日志类）
冷热数据拆分

11.3 避免单表超过

2000 万行（普通业务）
1 亿行以上建议分表

12. 安全最佳实践

禁止 root 远程
将 MySQL 放在私网
开启审计日志（仅在需要时）
对 binlog 加密
定期 rotate 密码
禁止弱密码
开启 SSL 连接（8.x 默认支持）

13. 日常运维规范

每周查看慢查询报告
每天检查主从延迟
每天监控磁盘空间
每月做一次备份恢复演练
所有 DDL 必须先在测试环境验证
所有 SQL 上线必须经过 Code Review