MySQL 配置大全 | 性能优化+避坑指南+一键抄作业

本文链接：https://blog.csdn.net/qq_41388113/article/details/146957822

适用场景：高并发业务/新服务器部署/性能瓶颈排查
效果：TPS提升50%~300% | 内存占用降低30% | 拒绝常见报错

🔧 一、核心基础配置

[mysqld]
# 1️⃣ 基础路径与权限
datadir=/var/lib/mysql                # 数据存储目录（SSD优先，需chown -R mysql:mysql）
socket=/var/run/mysqld/mysqld.sock  
user=mysql                            # 专用低权限用户运行
secure-file-priv=/var/lib/mysql-files # 限制文件导入导出路径（需手动创建目录）

# 2️⃣ 网络与连接
port=3306
skip-name-resolve                     # 禁用DNS反向解析（远程连接需IP授权）
skip-host-cache                     
max_connections=1000                  # 最大连接数（根据内存调整）
back_log=600                          # 等待连接队列大小

# 3️⃣ 字符集与兼容性
character-set-server=utf8mb4        
collation-server=utf8mb4_unicode_ci   # 支持Emoji和生僻字
lower_case_table_names=1              # 表名不区分大小写（初始化前必须设置！）
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO

💡 重点提示：

⚠️ lower_case_table_names 修改需重新初始化数据库！
🔐 sql_mode 严格模式可防止脏数据，但需测试旧业务兼容性

⚡ 二、性能狂飙配置（高并发/大数据量）

# 1️⃣ InnoDB引擎优化
innodb_buffer_pool_size=5G            # 缓冲池（物理内存70%~80%，不超过80%）
innodb_flush_log_at_trx_commit=2      # 事务提交策略（1=安全但慢，2=性能优先）
innodb_redo_log_capacity=2G           # 8.0+专用日志容量（替代innodb_log_file_size）
innodb_read_io_threads=8              # 读IO线程（SSD建议16）
innodb_write_io_threads=8           
innodb_flush_method=O_DIRECT          # 直接写盘避免双缓存

# 2️⃣ 查询与临时表
tmp_table_size=64M                  
max_heap_table_size=64M               # 必须与tmp_table_size一致
join_buffer_size=4M                 
sort_buffer_size=4M                 

# 3️⃣ 线程与缓存
thread_cache_size=32                  # 线程池复用（max_connections×10%）
table_open_cache=2000               
key_buffer_size=256M                  # MyISAM缓存（非必须可设为16M）

🚨 避坑指南：

💥 innodb_buffer_pool_size 过大导致OOM？监控命令：
watch -n 5 "free -h | grep -E 'Mem|Swap'"
🔄 死锁频繁？尝试：
SET GLOBAL innodb_lock_wait_timeout=120;

📊 三、日志与监控（快速定位问题）

# 1️⃣ 慢查询日志
slow_query_log=1
long_query_time=2                     # 记录超过2秒的SQL
slow_query_log_file=/var/log/mysql/mysql-slow.log  # 统一日志目录

# 2️⃣ 错误日志与Binlog
log_error=/var/log/mysql/mysql-error.log
log_bin=/var/lib/mysql/mysql-bin    
expire_logs_days=7                  
binlog_format=row                     # 主从复制推荐格式

# 3️⃣ 内存诊断
performance_schema=ON                 # 消耗5%~10%性能

🔍 诊断技巧：

# 慢日志分析（按耗时排序）
mysqldumpslow -s t /var/log/mysql/mysql-slow.log

# 实时连接监控
mysqladmin proc stat | grep Threads

🔒 四、安全与兼容性配置

# 1️⃣ 权限控制
local_infile=OFF                    
secure-auth=ON                      
symbolic-links=0                    

# 2️⃣ 防暴力破解
max_connect_errors=10               
wait_timeout=600                    

# 3️⃣ 存储引擎限制
default-storage-engine=InnoDB       
disabled_storage_engines=MyISAM       # 仅8.0+生效（5.7需UNINSTALL PLUGIN）

# 4️⃣ 认证插件兼容
default_authentication_plugin=mysql_native_password  # 8.0默认caching_sha2_password

⚠️ 血泪教训：

🚫 local_infile=ON + 弱密码 = 数据库被拖库！
💀 未禁用MyISAM导致表损坏？5.7必须执行：

UNINSTALL PLUGIN MyISAM;

🔄 五、完整配置（整合版）

# ========================
# MySQL 5.7/8.0 通用配置
# 路径：/etc/my.cnf
# 最后更新：2025-04-02
# ========================

[mysqld]
# ------------------------
# 核心基础配置
# ------------------------
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
user=mysql
port=3306
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
lower_case_table_names=1
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO

# ------------------------
# 性能优化
# ------------------------
innodb_buffer_pool_size=4G
innodb_flush_log_at_trx_commit=2
innodb_redo_log_capacity=2G          # 8.0+
; innodb_log_file_size=1G            # 5.7专用（8.0注释掉）
innodb_read_io_threads=8
innodb_flush_method=O_DIRECT
tmp_table_size=64M
max_heap_table_size=64M
thread_cache_size=32
table_open_cache=2000

# ------------------------
# 日志与安全
# ------------------------
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
log_error=/var/log/mysql/mysql-error.log
log_bin=/var/lib/mysql/mysql-bin
disabled_storage_engines=MyISAM
local_infile=OFF
max_connect_errors=10

# ------------------------
# 废弃参数
# ------------------------
; query_cache_type=0                # 8.0已移除
; query_cache_size=0                # 8.0已移除

[mysql]
default-character-set=utf8mb4

✅ 配置验证与调优工具

# 语法检查
mysqld --validate-config

# 关键参数验证
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# 内存计算公式（16G示例）
| 参数                | 计算公式             | 示例值  |
|---------------------|--------------------|--------|
| innodb_buffer_pool  | 内存×70%           | 11G     |
| max_connections     | (内存-缓冲池)/8MB  | 640     |

📜 版本兼容速查表

配置项	MySQL 5.7	MySQL 8.0
认证插件默认值	mysql_native_password	caching_sha2_password
禁用MyISAM方式	UNINSTALL PLUGIN	disabled_storage_engines
Redo日志参数	innodb_log_file_size	innodb_redo_log_capacity

优化说明：