Docker MySQL 8.0.45 性能优化配置文档

环境: Docker mysql8 容器 | 服务器: 192.168.65.100

一、硬件环境

二、配置文件架构

宿主机 容器内
──────────────────────────────────────────
/data/mysql8/conf/ ──bind──▶ /etc/mysql/conf.d/
├── max_allowed_packet.cnf (由 my.cnf 中 !includedir 自动加载)
└── performance.cnf

/data/mysql8/data/ ──bind──▶ /var/lib/mysql/

所有配置通过 docker inspect mysql8 确认挂载正确，容器重启/重建均不丢失。

三、参数持久化方案

3.1 为什么 SET GLOBAL 不持久

MySQL 中修改参数有两种方式：

因此所有性能参数必须写入配置文件，而非仅靠 SET GLOBAL。

3.2 Docker 绑定挂载 → 持久化

MySQL 官方镜像的 my.cnf 末尾有指令：

!includedir /etc/mysql/conf.d/

这意味着放在 /etc/mysql/conf.d/ 目录下的任何 .cnf 文件都会被自动加载。

关键：该目录通过 Docker bind mount 映射到宿主机，因此文件在宿主机上创建即可持久化：

宿主机文件 挂载关系 容器内加载路径
────────────────────────────────────────────────────────────
/data/mysql8/conf/ ──bind mount──▶ /etc/mysql/conf.d/
└─ performance.cnf └─ 被 my.cnf 的 !includedir 自动加载

用 docker inspect 验证挂载配置：

docker inspect mysql8 –format '{{json .Mounts}}' | python3 -m json.tool

输出示例：

[
{
"Type": "bind",
"Source": "/data/mysql8/conf",
"Destination": "/etc/mysql/conf.d",
"RW": true
},
{
"Type": "bind",
"Source": "/data/mysql8/data",
"Destination": "/var/lib/mysql",
"RW": true
}
]

✅ Source = 宿主机目录，文件存这里就永远不会丢。

3.3 创建持久化配置（完整操作）

# Step 1: 在宿主机创建配置文件
cat > /data/mysql8/conf/performance.cnf << 'EOF'
[mysqld]
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
# … 其他参数 …
max_allowed_packet = 512M
EOF

# Step 2: 验证容器内可见
docker exec mysql8 cat /etc/mysql/conf.d/performance.cnf

# Step 3: 优雅关闭 + 重启（innodb_log_file_size 变更需额外处理）
docker exec mysql8 mysql -uroot -p -e "SET GLOBAL innodb_fast_shutdown=0;"
docker exec mysql8 rm -f /var/lib/mysql/ib_logfile*
docker restart mysql8

# Step 4: 验证参数已从配置文件加载
docker exec mysql8 mysql -uroot -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
# 预期: 2147483648 (2G)

3.4 持久化验证

重启容器后确认参数来自配置文件而非默认值：

# 重启
docker restart mysql8

# 验证关键参数仍为优化值
docker exec mysql8 mysql -uroot -p -e "
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'max_allowed_packet';
"

如果重启后值与期望不符，说明配置文件未被正确加载，检查：

3.5 当前已持久化的配置文件

/data/mysql8/conf/
├── max_allowed_packet.cnf # [mysqld] max_allowed_packet=512M
└── performance.cnf # [mysqld] 全部 InnoDB/连接/缓存优化参数

两个文件互不冲突，MySQL 会按文件名字母顺序加载（后加载的同名参数覆盖先加载的）。

四、优化前现状分析

MySQL 8.0.45 官方镜像的全部默认参数，运行在 7.7GB 服务器上存在严重浪费：

五、优化参数详解

4.1 InnoDB 核心优化 🔥

innodb_flush_log_at_trx_commit 重要说明

值为 0：每秒写入日志并刷盘（性能最高，可能丢 1 秒数据）
值为 1：每次提交都写入并刷盘（最安全，性能最低） ← 默认
值为 2：每次提交写入，每秒刷盘（平衡方案） ← 当前

当前选择 2 的理由: 这是开发/内部使用环境，对极端数据安全性要求不高，
每秒最多丢失 1 秒事务数据可接受，但写性能提升 5~10 倍。
如需最高安全性可改为 1。

内存分配分析

服务器总内存: 7.7 GB
系统预留: ~1.0 GB
其他服务: ~3.0 GB
MySQL Buffer Pool: 2.0 GB
剩余可用: ~1.7 GB

4.2 连接与线程

wait_timeout 设为 3600s 可自动清理长时间空闲连接，避免连接数耗尽。

4.3 表缓存

当前 126 张表，4000 足够。每张表打开消耗约 4KB，总消耗约 16MB。

4.4 内存临时表与排序

⚠️ sort_buffer_size 和 join_buffer_size 是 per-session（每个连接）的，
不要设太大。当前值在 300 连接上限下最坏情况内存占用：300 × (512K+512K+256K+512K) ≈ 525MB。

4.5 Binlog 日志

binlog_expire_logs_seconds 从 30 天缩短到 7 天，减少磁盘占用。

4.6 监控与诊断

超过 2 秒的查询会被记录，便于后续排查和优化。

4.7 其他

导入 127MB 的 test.sql 时遇到 ERR 2006 MySQL server has gone away，
就是此参数过小导致。512M 足以应对大型 INSERT 语句。

六、完整配置文件

以下为 /data/mysql8/conf/performance.cnf 的实际内容：

[mysqld]

# ===== InnoDB 核心优化 =====
# Buffer Pool: 最重要参数，设为可用内存 60-70%
# 当前服务器 7.7G 总内存，分配 2G 给 MySQL
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2

# Redo Log: 增大减少 checkpoint 频率
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M

# IO 容量: SSD 推荐 1000-2000，HDD 保持 200
innodb_io_capacity = 1000
innodb_io_capacity_max = 3000

# 刷新策略: 1=每次刷盘(最安全), 2=每秒刷盘(平衡)
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# ===== 连接与线程 =====
thread_cache_size = 32
max_connections = 300
wait_timeout = 3600
interactive_timeout = 3600

# ===== 表缓存 =====
table_open_cache = 4000
table_definition_cache = 4000

# ===== 临时表 =====
tmp_table_size = 64M
max_heap_table_size = 64M

# ===== 排序与 JOIN 缓冲 (per-session) =====
sort_buffer_size = 512K
join_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K

# ===== 慢查询日志 =====
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/lib/mysql/slow.log

# ===== Binlog =====
binlog_cache_size = 1M
binlog_expire_logs_seconds = 604800

# ===== 其他 =====
max_allowed_packet = 512M

七、变更 innodb_log_file_size 的特殊步骤

修改 innodb_log_file_size 时，MySQL 不会自动调整已存在的 redo log 文件，
必须手动删除旧文件后重启：

# 1. 优雅关闭 MySQL (完整刷盘)
docker exec mysql8 mysql -uroot -p -e "SET GLOBAL innodb_fast_shutdown=0;"

# 2. 删除旧 redo log 文件
docker exec mysql8 rm -f /var/lib/mysql/ib_logfile*

# 3. 重启容器（会自动创建新大小的 redo log）
docker restart mysql8

⚠️ 跳过步骤 2 会导致 MySQL 启动失败，报错 redo log size mismatch。

八、验证优化效果

# 确认所有参数已生效
docker exec mysql8 mysql -uroot -p -e "
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'innodb_io_capacity';
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'max_allowed_packet';
"

预期输出：

九、优化效果预估

十、后续建议

📄 配置文件路径: /data/mysql8/conf/performance.cnf
🖥️ 目标服务器: 192.168.65.100
🐳 容器名称: mysql8