MySQL的配置文件详解及实例代码_Mysql

前言

mysql 的配置文件（通常是 my.cnf 或 my.ini）是控制 mysql 服务器行为的重要文件。不同操作系统的配置文件位置可能不同：

linux/unix：/etc/my.cnf、/etc/mysql/my.cnf、/usr/local/mysql/etc/my.cnf 或 ~/.my.cnf
windows：c:\programdata\mysql\mysql server x.y\my.ini（具体路径取决于安装版本）

一、配置文件结构

配置文件由多个 section（分组） 组成，每个 section 对应不同的组件或客户端工具。常见 section 如下：

1.[mysqld]

mysql 服务器核心配置（必填）。

[mysqld]
# 基础配置
port = 3306              # 监听端口（默认3306）
datadir = /var/lib/mysql # 数据存储目录
socket = /tmp/mysql.sock # unix socket 文件路径
user = mysql             # 运行mysql的系统用户

# 字符集配置
character-set-server = utf8mb4  # 服务器默认字符集
collation-server = utf8mb4_unicode_ci  # 默认排序规则

# 日志配置
log-error = /var/log/mysql/error.log  # 错误日志路径
slow_query_log = 1                    # 开启慢查询日志
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2                   # 慢查询阈值（秒）

# 内存与性能
innodb_buffer_pool_size = 1g          # innodb缓冲池大小（建议设为物理内存的50-80%）
innodb_log_file_size = 256m           # innodb日志文件大小
max_connections = 200                 # 最大并发连接数
thread_cache_size = 10                # 线程缓存数量

# 安全相关
skip_name_resolve = 1                 # 禁用dns反向解析（提升连接速度）
secure_file_priv = /var/lib/mysql-files  # 限制文件导入导出路径

2.[client]

客户端工具（如 mysql 命令行）的默认配置。

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

3.[mysql]

mysql 命令行客户端的配置。

[mysql]
auto-rehash           # 启用自动补全
prompt = "\\u@\\h:\\d> "  # 自定义提示符（用户@主机:数据库）

4.[mysqldump]

备份工具 mysqldump 的配置。

[mysqldump]
quick                  # 逐行导出数据（减少内存占用）
max_allowed_packet = 256m  # 最大数据包大小

二、关键配置项详解

1.内存与性能优化

innodb_buffer_pool_size
innodb 引擎的核心缓存，存储数据和索引。建议设置为物理内存的 50-80%。
innodb_flush_log_at_trx_commit
控制事务日志刷新策略：
- 0：每秒写入日志并刷新到磁盘（性能最佳，可能丢失1秒数据）。
- 1：每次事务提交都写入并刷新（最安全，性能最低）。
- 2：每次提交写入日志，但每秒刷新到磁盘（折中方案）。
key_buffer_size
myisam 表的索引缓存大小（若使用 myisam 表需调整）。

2.连接管理

max_connections
最大并发连接数。需根据应用需求调整，过高可能导致内存耗尽。
wait_timeout 和 interactive_timeout
非交互式和交互式连接的空闲超时时间（默认28800秒=8小时）。

3.二进制日志（主从复制）

server-id = 1                   # 服务器唯一id（主从不能重复）
log_bin = /var/log/mysql/mysql-bin.log  # 开启二进制日志
expire_logs_days = 7            # 日志保留天数
binlog_format = row             # 推荐使用row格式（数据一致性更强）

4.查询缓存（mysql 8.0已废弃）

query_cache_type = 0            # 关闭查询缓存（8.0已移除该功能）

5.其他重要配置

tmp_table_size 和 max_heap_table_size
控制内存临时表的最大大小，超出后会转为磁盘表。

sql_mode
定义 sql 严格模式，例如：

sql_mode = strict_trans_tables,no_engine_substitution

三、配置文件示例

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
port = 3306

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# innodb配置
innodb_buffer_pool_size = 2g
innodb_log_file_size = 512m
innodb_flush_log_at_trx_commit = 2

# 连接管理
max_connections = 300
thread_cache_size = 20
wait_timeout = 600

# 日志
log-error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

# 安全
skip_name_resolve = 1
secure_file_priv = /var/lib/mysql-files

[client]
default-character-set = utf8mb4