MySQL 运维

MySQL 安装与基础运维
配置文件（my.cnf）与关键参数
用户与权限管理
存储引擎与表结构
性能优化
InnoDB 关键机制
日常监控指标
备份与恢复
高可用（HA）与复制
数据一致性校验
故障排查
日常运维最佳实践
MySQL 安全指南

1. MySQL 安装与基础运维

1.1 安装方式

二进制安装（官方推荐）
RPM/DEB 包安装
Docker / Kubernetes 部署
源码编译（不推荐）

1.2 常用目录

路径	描述
/etc/my.cnf	配置文件
/var/lib/mysql	数据目录
/var/log/mysql	日志目录
/usr/bin/mysql	客户端
/usr/sbin/mysqld	服务端

1.3 常见命令

systemctl start mysql
systemctl stop mysql
mysql -u root -p
mysqld --verbose --help

2. 配置文件（my.cnf）与关键参数

2.1 配置结构

[mysqld]
[mysql]
[mysqldump]
[client]

2.2 InnoDB 常用参数

参数	推荐值	说明
innodb_buffer_pool_size	内存 60%~70%	缓存数据页（核心）
innodb_log_file_size	1GB~4GB	写日志文件大小
innodb_flush_log_at_trx_commit	1	最强一致性
innodb_file_per_table	ON	独立表空间
innodb_flush_method	O_DIRECT	避免双缓存

2.3 连接参数

参数	说明
max_connections	最大连接数
wait_timeout	空闲连接超时
max_connect_errors	最大错误连接数

2.4 慢查询

slow_query_log = 1
long_query_time = 1

使用 pt-query-digest 分析慢日志。

3. 用户与权限管理

CREATE USER 'app'@'10.%' IDENTIFIED BY 'xxx';
GRANT SELECT, INSERT, UPDATE ON db.* TO 'app'@'10.%';
FLUSH PRIVILEGES;

运维不推荐使用 %，应使用最小权限原则。
使用多账号分离：读写分离、备份账号、监控账号等。

4. 存储与表结构管理

4.1 InnoDB 特征

聚簇索引
行级锁
支持事务
采用 MVCC

4.2 表结构设计建议

使用 INT UNSIGNED 而不是 BIGINT（节省存储）
避免 TEXT，使用 VARCHAR
时间字段使用 TIMESTAMP 或 DATETIME(3)
索引命名：idx_xxx_column

5. 性能优化（Performance Tuning）

主要从 SQL、索引、配置、架构、存储 5 大方向优化。

5.1 SQL 优化

慢 SQL 常见原因：

全表扫描
索引未命中
隐式类型转换
OR/IN/LIKE ‘%xx%’
排序/分组导致 filesort

常用工具：

EXPLAIN
SHOW PROFILE
performance_schema
pt-query-digest

6. InnoDB 核心机制（必须掌握）

6.1 MVCC（多版本并发控制）

依赖：

Undo 日志
Read View
隐藏列 trx_id / roll_pointer

6.2 Redo Log

保证 崩溃恢复（crash-safe）。

6.3 Undo Log

实现 MVCC、回滚。

6.4 Doublewrite Buffer

防止部分页面写入损坏。

6.5 Buffer Pool

数据库性能核心所在。

7. 监控指标体系（运维必备）

7.1 连接相关

Threads_connected
Threads_running
Connections

7.2 事务与锁

innodb_row_lock_time_avg
innodb_row_lock_current_waits
innodb_deadlocks

7.3 Buffer Pool

innodb_buffer_pool_reads
innodb_buffer_pool_read_requests
Buffer Pool 命中率

7.4 QPS/TPS

SHOW GLOBAL STATUS LIKE 'Com%';

7.5 磁盘 IOPS、延迟

8. 备份与恢复（Backup & Restore）

8.1 备份策略

每日全量备份（mysqldump / xtrabackup）
每小时 binlog 增量备份
异地备份（对象存储）
定期恢复演练

8.2 工具

工具	特点
mysqldump	逻辑备份，慢
xtrabackup（推荐）	物理备份，热备
mysqlbinlog	增量恢复

9. 高可用（HA）方案

从强一致性角度排序：

方案	一致性	可用性	推荐度
MySQL Group Replication	★★★★★	★★★★★	推荐
MySQL InnoDB Cluster	★★★★★	★★★★★	推荐
Orchestrator + 主从复制	★★	★★★★★	高性能场景
MHA	★★	★★★★	老方案

10. 数据一致性校验

生产运维重要环节。

工具：

pt-table-checksum
pt-table-sync
手动 checksum：CHECKSUM TABLE

11. 常见故障排查

11.1 无法连接

账号密码错误
防火墙 / 安全组
max_connections 达到上限
表损坏

11.2 性能下降

索引失效
大事务
表锁/死锁
Buffer Pool 不够
IO 饱和

11.3 主从延迟

大事务
网络抖动
从库执行慢 SQL

12. 日常运维最佳实践

设置定期备份 + 恢复演练
主从复制必须启用 GTID
生产避免使用 DELETE 大表，改用分批清理
限制大事务（单事务 < 10s）
通过 ProxySQL 做读写分离
所有 SQL 上生产前必须走 explain 评审
配置慢日志并定期分析
系统资源（CPU、IO、内存）监控必须可视化

13. 安全指南

不允许 root 远程登录
每个应用独立的账号 + 最小权限原则
启用 SSL（8.0 默认）
定期 rotate 密码
删除 test 数据库