前言

本章，您将学习与 MySQL 配置文件相关的内容。

在有些资料中，也将配置文件称为选项文件。

配置文件位置

通过查阅 /usr/local/mysq8l/bin/mysqld --help --verbose 命令的输出文本可知，您可以在以下的路径位置定义 MySQL 的配置文件：

• /etc/my.cnf – 全局
• /etc/mysql/my.cnf – 全局
• /usr/local/mysql8/etc/my.cnf – 全局
• ~/.my.cnf – 用于特定用户的局部配置文件

简单的配置文件示例：

[client]
port=3306 
socket=/tmp/mysql.sock

[mysqld] 
port=3306 
socket=/tmp/mysql.sock
key-buffer-size=16M
max-allowed-packet=128M

[mysqldump] 
quick

配置文件的语法要求

• "[ ]" 表示要设置选项的程序或组的名称，在名称下面则是各种参数与值。

• "#" 开头，表示这是注释行

• 有一部分参数需要加值，有些则不需要

• 有时候需要为不同版本的 MySQL 定义选项组，例如 [mysqld-5.7]、 [mysqld-8.4]

• 可使用 !include 包含或搜索特定目录中的配置文件，例如:

  • !include /home/mydir/myopt.cnf – 包含特定配置文件
  • !includedir /home/mydir/ – 搜索目录下的配置文件，如果要被 MySQL 搜索到，请将文件名以 .cnf 结尾。

• 参数的下划线（_）与连字符（-）都可以被接受，如 bind-address=* 与 bind_address=* 是等价的

• "=" 两边可以允许有空格

配置文件的常见参数

针对客户端

[client]
port=3306
socket=/tmp/mysql.sock

针对守护进程

[mysqld]
port=3306
socket=/tmp/mysql.sock
basedir=/usr/local/mysql8/
datadir=/usr/local/mysql8/data/
user=mysql
log-error=/usr/local/mysql8/data/mysqld_start.err
bind-address=*
default-storage-engine=INNODB
group-concat-max-len=102400
skip-name-resolve
back-log=151
max-connections=700
wait-timeout=90
key-buffer-size=100M
max-allowed-packet=100M
thread-stack=1024576
tmp-table-size=16777216
sort-buffer-size=262144
read-buffer-size=512K
join-buffer-size=512K
table-open-cache=4000
table-open-cache-instances=16
max-error-count=1024
skip-networking=OFF
binlog-format=ROW
innodb-thread-concurrency=0
innodb-flush-log-at-trx-commit=1
sync-binlog=1
innodb-buffer-pool-size=1G
innodb-max-dirty-pages-pct=90
innodb-io-capacity=200
innodb-log-file-size=48M
innodb-log-buffer-size=16M
innodb-file-per-table=ON
innodb-compression-level=6
server-id=1

• port=3306 – 守护进程占用的端口

• socket=/tmp/mysql.sock – 守护进程使用的套接字

• basedir=/usr/local/mysql8/ – MySQL 的基础目录

• datadir=/usr/local/mysql8/data/ – MySQL 的数据目录

• user=mysql – 使用特定用户来运行进程

• log-error=/usr/local/mysql8/data/mysqld_start.err – 错误日志位置

• bind-address=* – 绑定一个或多个 IP 地址，多个 IP 地址用逗号进行分隔

• default-storage-engine=INNODB – 创建表时默认使用的存储引擎，其他值还有 MyISAM、Memory、CSV、Archive、Blackhole、NDB、Merge、Federated、Example

• group-concat-max-len=102400 – GROUP_CONCAT( ) 函数允许的最大结果长度（字节），默认值为1024，64 位平台最大允许为 18446744073709551615。我们建议你加大该值，例如 102400

• skip-name-resolve – 跳过 MySQL 的 DNS 解析，若开启，则远程连接时需要使用 IP 地址

• back-log=-1 – 重要的性能参数，我们以前在介绍 Redis 的配置文件 时见过。在 MySQL 中，其值范围为 [1,65535]，默认值为 -1 ，表示自动调整

• max-connections=700 – 重要的性能参数，我们以前在介绍 Redis 的配置文件 时见过。在 MySQL 中，其值范围为 [1,100000]，默认值为 151

• wait-timeout=90 – 非交互时的最大等待时间

• key-buffer-size=100M – 针对 MyISAM 存储引擎的索引缓冲区大小（默认为 8388608 字节），可以添加单位后缀，如 K、M、G、T、P、E。

• max-allowed-packet=64M – MySQL 接收单个数据包的大小，默认67108864 字节（64MB）

• thread-stack=1048576 – 每个线程的堆栈大小，默认值 1048576 足以满足正常运行。

• tmp-table-size=16777216 – 针对 Memory 存储引擎的临时表最大大小

• sort-buffer-size=262144 – 执行排序会话的缓冲区大小，适用于全部存储引擎，默认即可。

• read-buffer-size=128K – 每个线程在进行表的顺序扫描（即全表扫描）时使用的缓冲区大小，主要提升 MyISAM 存储引擎的性能，对于其他存储引擎（如 INNODB）的性能提升有限。该值过大会浪费不必的内存，过小则无法提升 I/O 性能

• join-buffer-size=256K – join 表时缓冲区的大小，不宜过大也不宜国小，默认 256K 即可

• table-open-cache=4000 – 所有线程打开表的的数量，增加该值会增加 mysqld 所需的文件描述符

• table-open-cache-instances=16 – 将全局可缓存的表对象分为 16 个区，对于拥有 16 核及以上的系统，设置为 8 或 16 都是合理的。

• max-error-count=1024 – 控制可以存储的错误、警告和注释消息的最大数量

• skip-networking=OFF – 是否禁用 TCP/IP 连接，默认为 OFF，即允许使用套接字进行连接，若为 ON，则表示仅允许本地连接

• binlog-format=ROW – binlog 的日志格式，默认为 ROW，其他的有 MIXED、STATEMENT

• innodb-thread-concurrency=0 – INNODB 存储引擎相关，定义 INNODB 内部允许的最大线程数，0 值解释为无限制并发

• innodb-flush-log-at-trx-commit=1 – INNODB 存储引擎相关，用于控制事务提交时重做日志（redo log）的刷盘策略，影响数据的持久性和性能

  • 0 （延迟刷盘）- 事务提交时不立即刷盘，日志缓冲区会每秒写入到日志文件并同步到磁盘。这可能会丢失 1 秒的事务数据
  • 1 （实时刷盘）- 每次事务提交时都强制将 redo log 写入到磁盘
  • 2 （折中） – 事务提交时 redo log 写入到操作系统缓存，然后每秒同步到磁盘一次，这可能会丢失 1 秒的事务数据

• sync-binlog=1 – 控制 binlog 同步到磁盘的频率

  • 0 – 表示禁用
  • 1 – 表示在提交事务之前启用二进制日志到磁盘的同步，最安全的设置，但由于磁盘写入次数增加，可能会对性能产生负面影响。
  • N – 指的是 0 或 1 以外的值，在收集了 N 个 binlog 提交组之后，将二进制日志同步到磁盘。在电源故障或操作系统崩溃的情况下，服务器可能提交了尚未刷新到二进制日志的事务。由于磁盘写入次数增加，此设置可能会对性能产生负面影响。值越大，性能越高，但数据丢失的风险也会相应增加。

为了在 INNODB 的事务中获得最好的一致性与持久性，请设置 innodb-flush-log-at-trx-commit=1 和 sync-binlog=1

• innodb-buffer-pool-size=1G – INNODB 缓冲池大小。通常设置为当前可用内存的 80% 即可
• innodb-max-dirty-pages-pct=90 – 缓冲池中允许的脏页百分比，默认 90 即可
• innodb-io-capacity=200 – 控制 INNODB 后台每秒可执行的 I/O 操作数（即 IOPS）。对于机械硬盘，我们建议设置为 100；对于 SATA 或低端 SSD ，我们建议设置为 200；对于高端的 NVME SSD，我们建议设置为 1000 或往上。不建议设置高于 20000 的值，除非您确定较低的值不足以满足您的工作负载。
• innodb-redo-log-capacity=100M – 定义 redo log 文件占用的磁盘空间大小。在此版本中，innodb-redo-log-capacity 已经取代了 innodb-log-files-in-group 和 innodb-log-file-size
• innodb-log-buffer-size=64M – 设置 ‌INNODB 重做日志（redo log）的缓冲区大小‌
• innodb-file-per-table=ON – 是否为每张表创建表空间文件（.ibd 文件）
• innodb-compression-level=6 – INNODB 压缩表或索引时使用的 zlib 压缩级别，值越大，表示压缩等级越高。默认 6 即可
• server-id=1 – 复制技术中用来标识机器的 ID 号，该 ID 号是一个介于 1 至 2^32-1 之间的数字。

更多内容参阅以下链接：

• https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html
• https://dev.mysql.com/doc/refman/8.4/en/replication-options-binary-log.html
• https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html
• https://dev.mysql.com/doc/refman/8.4/en/replication-options.html#sysvar_server_id

基本配置

简单配置当前的 MySQL：

Shell > vim /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
server-id = 1
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql8/
datadir = /usr/local/mysql8/data/
user = mysql
log-error = /usr/local/mysql8/data/mysqld_start.err
bind-address = *
default-storage-engine = INNODB
group-concat-max-len = 102400
skip-name-resolve
back-log = -1
max-connections = 700
wait-timeout = 90
max-allowed-packet = 64M
read-buffer-size = 128K
innodb-flush-log-at-trx-commit = 1
sync-binlog = 1
innodb-buffer-pool-size = 1G
innodb-io-capacity = 100

[mysqldump]
quick

Shell > /usr/local/mysql8/bin/mysqld_safe  --user=mysql &

赞赏