46. MySQL 配置与日常运维

46.1 配置文件

MySQL 启动时读取配置文件（option file），常见位置：

配置文件路径

系统	路径
Linux	/etc/my.cnf → /etc/mysql/my.cnf → /usr/etc/my.cnf → ~/.my.cnf
Windows	C:\Windows\my.ini → C:\Windows\my.cnf → C:\my.ini → C:\my.cnf → %PROGRAMDATA%\MySQL\MySQL Server X.X\my.ini

后加载的覆盖先加载的。推荐用 /etc/my.cnf（Linux）或 %PROGRAMDATA% 下的 my.ini（Windows）。

配置文件结构

[mysqld]    # 服务端配置
port = 3306
datadir = /var/lib/mysql
character_set_server = utf8mb4

[client]    # 客户端配置（mysql 命令行、mysqldump 等）
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysql]     # 仅 mysql 命令行客户端
default-character-set = utf8mb4

46.2 核心参数

内存相关

[mysqld]
# InnoDB 缓冲池，最重要的内存参数，建议设为物理内存的 50%-75%
innodb_buffer_pool_size = 4G

# 每个连接的缓冲区（按需分配，非一次性）
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M

# 内存临时表上限
tmp_table_size = 64M
max_heap_table_size = 64M

连接相关

[mysqld]
# 最大连接数，默认 151，高并发场景需要调大
max_connections = 1000

# 连接空闲超时（秒），默认 8 小时
wait_timeout = 1800
interactive_timeout = 1800

# 最大错误连接次数，超过临时封禁主机
max_connect_errors = 10000

# 允许的最大数据包大小
max_allowed_packet = 128M

InnoDB 相关

[mysqld]
# 每个表独立表空间文件（推荐 ON）
innodb_file_per_table = 1

#  redo log 大小
innodb_log_file_size = 1G

# 刷盘策略，O_DIRECT 避免双缓冲
innodb_flush_method = O_DIRECT

# 每次事务提交写 redo log（最安全）
innodb_flush_log_at_trx_commit = 1

innodb_flush_log_at_trx_commit	行为	安全性	性能
1	每次提交写磁盘	最高	最低
0	每秒写一次	低（宕机丢 1 秒）	高
2	每次提交写 OS 缓存，每秒刷盘	中	中

46.3 日志管理

MySQL 有多种日志，各司其职：

日志	作用	是否默认开启
错误日志（Error Log）	启动/运行/停止时的错误信息	是
慢查询日志（Slow Query Log）	记录执行超阈值的 SQL	否
二进制日志（Binary Log / binlog）	记录数据变更，用于复制和恢复	8.0+ 默认开启
通用查询日志（General Query Log）	记录所有 SQL 语句	否（性能影响大，慎用）
中继日志（Relay Log）	主从复制时从库的待执行日志	主从时才有

错误日志

-- 查看错误日志位置
SHOW VARIABLES LIKE 'log_error';

启动失败、崩溃、死锁等关键信息都在这里，排查问题时首先看。

慢查询日志

-- 开启
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

分析工具：mysqldumpslow 或 pt-query-digest。

二进制日志（binlog）

binlog 记录所有数据变更（INSERT/UPDATE/DELETE/DDL），用于主从复制和时间点恢复。

-- 查看 binlog 状态
SHOW VARIABLES LIKE 'log_bin';

-- 查看所有 binlog 文件
SHOW BINARY LOGS;

-- 查看当前写入的 binlog 文件
SHOW MASTER STATUS;

-- 查看 binlog 内容
SHOW BINLOG EVENTS IN 'binlog.000001' LIMIT 10;

[mysqld]
# binlog 格式：ROW（推荐，安全）、STATEMENT、MIXED
binlog_format = ROW

# binlog 过期天数，默认 30 天
expire_logs_days = 30

# 或按秒（8.0+）
binlog_expire_logs_seconds = 2592000

46.4 服务管理

Linux（systemd）

systemctl start mysqld      # 启动
systemctl stop mysqld       # 停止
systemctl restart mysqld    # 重启
systemctl status mysqld     # 查看状态
systemctl enable mysqld     # 开机自启
systemctl disable mysqld    # 取消自启

Windows

net start mysql     # 启动
net stop mysql      # 停止

或 services.msc 图形界面操作。

查看运行状态

-- 查看运行状态（连接数、查询量、流量等）
SHOW GLOBAL STATUS;

-- 查看特定状态
SHOW GLOBAL STATUS LIKE 'Threads_connected';  -- 当前连接数
SHOW GLOBAL STATUS LIKE 'Uptime';             -- 运行时长（秒）
SHOW GLOBAL STATUS LIKE 'Slow_queries';       -- 慢查询总数

-- 查看变量
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'version';

46.5 版本升级注意

升级前

1. 备份：全量备份 + 确认可恢复。
2. 阅读 Release Notes：看是否有不兼容变更（breaking changes）。
3. 测试环境验证：先升级测试库，跑一遍业务 SQL。

升级方式

• 就地升级（In-place）：替换二进制文件后启动，MySQL 自动升级数据字典。8.0+ 支持原子 DDL，升级更安全。
• 逻辑升级（Logical）：mysqldump 导出 → 装新版本 → 导入。适合跨大版本。

升级后

-- 检查表是否兼容
mysqlcheck -u root -p --all-databases --check-upgrade

-- 查看错误日志有无告警

46.6 常见问题排查

现象	排查方向
连不上 MySQL	服务是否启动、防火墙/安全组端口、max_connections 是否打满、用户 host 是否匹配
查询慢	是否走索引（EXPLAIN）、是否有锁等待（SHOW ENGINE INNODB STATUS）、慢查询日志
CPU 高	大量慢查询、全表扫描、并发连接过多
磁盘满	binlog 未清理、临时文件、undo log 膨胀、数据增长
主从延迟	从库 IO/SQL 线程状态、大事务、单线程复制瓶颈

常用排查命令

-- 查看当前连接与状态
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;  -- 显示完整 SQL

-- 查看锁等待
SELECT * FROM sys.innodb_lock_waits;

-- 查看 InnoDB 状态（含死锁信息）
SHOW ENGINE INNODB STATUS;

-- 查看最近一次死锁
SHOW ENGINE INNODB STATUS\G  -- 找 LATEST DETECTED DEADLOCK 段

46.7 日常运维清单

作为没有专职 DBA 的团队，建议定期做这些事：

• 每日：检查错误日志、确认备份是否成功、慢查询日志是否有新出现的慢 SQL
• 每周：检查磁盘空间使用、连接数趋势、表空洞情况
• 每月：审查用户权限、binlog 清理策略、索引使用情况
• 每季度：版本补丁评估、故障演练（恢复备份）