本文首发：MySQL 配置文件 my.cnf / my.ini 逐行详解 – 卡拉云

MySQL 配置文件的意义

充沛了解 MySQL 配置文件中各个变量的意义对咱们有针对性的优化 MySQL 数据库性能有十分大的意义。咱们须要依据不同的数据量级，不同的生产环境状况对 MySQL 配置文件进行优化。

Windows 和 Linux 下的 MySQL 配置文件的名字和寄存地位都是不同的，WIndows 下 MySQL 配置文件是 my.ini 寄存在 MySQL 装置目录的根目录下；Linux 下 MySQL 配置文件是 my.cnf 寄存在 /etc/my.cnf、/etc/mysql/my.cnf。咱们也能够通过 find 命令进行查找。

另外要留神的是，通过 rpm 命令装置的 MySQL 是没有 /etc/my.cnf 文件的，如果须要配置 MySQL，能够在 /etc/my.cnf 新建配置文件，而后把本文的配置信息复制到文件中即可。

本教程将率领大家逐条解析最新的 MySQL 8.0 的配置文件，争取搞懂每一条变量。当然，咱们了解了变量的意义外，更重要的是针对本人的数据库外部环境，在实践中进行微调，以达到优化性能的目标。

提醒：你能够应用 Ctrl+F 疾速定位。MySQL 配置文件详解

• [client]
• [mysqld_safe]
• [mysqld]
• Query Cache MySQL 谬误日志设置 慢查问记录 全局查问日志
• 隶属线程变量
• 平安变量
• MyISAM 变量
• MEMORY 变量
• InnoDB 变量 WSREP 配置

MySQL 配置文件详解

文件地位: Windows、Linux、Mac 有轻微区别，Windows 配置文件是 .ini，Mac/linux 是 .cnf

[Windows]
MySQL\MySQL Server 5.7\my.ini

[Linux / Mac]
/etc/my.cnf
/etc/mysql/my.cnf 

当然咱们也能够应用命令来查看 MySQL 默认配置文件地位

mysql --help|grep 'cnf'

[client]

客户端设置。以后为客户端默认参数

port = 3306

默认连贯端口为 3306

socket = /tmp/mysql.sock

本地连接的 socket 套接字

default_character_set = utf8

设置字符集，通常应用 uft8 即可

[mysqld_safe]

mysqld_safe 是服务器端工具，用于启动 mysqld，也是 mysqld 的守护过程。当 mysql 被 kill 时，mysqld_safe 负责重启启动它。

open_files_limit = 8192

此为 MySQL 关上的文件描述符限度，它是 MySQL 中的一个全局变量且不可动静批改。它管制着 mysqld 过程能应用的最大文件描述符数量。默认最小值为 1024

须要留神的是这个变量的值并不一定是你在这里设置的值，mysqld 会在零碎容许的状况下尽量取最大值。

当 open_files_limit 没有被配置时，比拟 max_connections*5 和 ulimit -n 的值，取最大值

当 open_file_limit 被配置时，比拟 open_files_limit 和 max_connections*5 的值，取最大值

user = mysql

用户名

log-error  = error.log

谬误 log 记录文件

[mysqld]

服务端根本配置

port = 3306

mysqld 服务端监听端口

socket = /tmp/mysql.sock

MySQL 客户端程序和服务器之间的本地通信指定一个套接字文件

max_allowed_packet  = 16M

容许最大接管数据包的大小，避免服务器发送过大的数据包。

当收回长查问或 mysqld 返回较大后果时，mysqld 才会分配内存，所以增大这个值危险不大，默认 16M，也能够依据需要改大，但太大会有溢出危险。取较小值是一种安全措施，防止偶尔呈现但大数据包导致内存溢出。

default_storage_engine = InnoDB 

创立数据表时，默认应用的存储引擎。这个变量还能够通过 –default-table-type 进行设置

max_connections  = 512

最大连接数，以后服务器容许多少并发连贯。默认为 100，个别设置为小于 1000 即可。太高会导致内存占用过多，MySQL 服务器会卡死。作为参考，小型站设置 100 – 300

max_user_connections = 50

用户最大的连接数，默认值为 50 个别应用默认即可。

thread_cache_size = 64

线程缓存，用于缓存闲暇的线程。这个数示意可从新应用保留在缓存中的线程数，当对方断开连接时，如果缓存还有空间，那么客户端的线程就会被放到缓存中，以便进步零碎性能。咱们可依据物理内存来对这个值进行设置，对应规定 1G 为 8；2G 为 16；3G 为 32；4G 为 64 等。

Query Cache

query_cache_type = 1 

设置为 0 时，则禁用查问缓存（只管仍调配 query_cache_size 个字节的缓冲区）。

设置为 1 时，除非指定 SQL_NO_CACHE，否则所有SELECT 查问都将被缓存。

设置为 2 时，则仅缓存带有 SQL CACHE 子句的查问。

请留神，如果在禁用查问缓存的状况下启动服务器，则无奈在运行时启用服务器。

query_cache_size = 64M

缓存 select 语句和后果集大小的参数。

查问缓存会存储一个 select 查问的文本与被传送到客户端的相应后果。

如果之后接管到一个雷同的查问，服务器会从查问缓存中检索后果，而不是再次剖析和执行这个同样的查问。

如果你的环境中写操作很少，读操作频繁，那么关上 query_cache_type=1，会对性能有显著晋升。如果写操作频繁，则应该敞开它（query_cache_type=0）。

Session variables

sort_buffer_size = 2M

MySQL 执行排序时，应用的缓存大小。增大这个缓存，进步 group by，order by 的执行速度。

tmp_table_size = 32M

HEAP 长期数据表的最大长度，超过这个长度的长期数据表 MySQL 可依据需要主动将基于内存的 HEAP 长期表改为基于硬盘的 MyISAM 表。咱们可通过调整 tmp_table_size 的参数达到进步连贯查问速度的成果。

read_buffer_size  = 128k

MySQL 读入缓存的大小。如果对表对程序申请比拟频繁对话，可通过减少该变量值以进步性能。

read_rnd_buffer_size = 256k

用于表的随机读取，读取时每个线程调配的缓存区大小。默认为 256k，个别在 128 – 256k 之间。在做 order by 排序操作时，会用到 read_rnd_buffer_size 空间来暂做缓冲空间。

join_buffer_size  = 128k 

程序中常常会呈现一些两表或多表 Join（联表查问）的操作。为了缩小参加 Join 连表的读取次数以进步性能，须要用到 Join Buffer 来帮助 Join 实现操作。当 Join Buffer 太小时，MySQL 不会将它写入磁盘文件。和 sort_buffer_size 一样，此参数的内存调配也是每个连贯独享。

table_definition_cache = 400

限度不应用文件描述符存储在缓存中的表定义的数量。

table_open_cache   = 400                                

限度为所有线程在内存中关上的表数量。

扩大浏览：《在 MySQL 中 DATETIME 和 TIMESTAMP 工夫类型的区别及应用场景》

MySQL 谬误日志设置

log_error = error.log
log_warnings = 2

• log_warnings 为 0，示意不记录告警信息。
• log_warnings 为 1，示意告警信息写入谬误日志。
• log_warnings 大于 1，示意各类告警信息，例如无关网络故障的信息和从新连贯信息写入谬误日志。

扩大浏览：《MySQL「Every derived table must have its own alias」谬误 ERROR 1248 修复办法》

MySQL 慢查问记录

slow_query_log_file = slow.log
slow_query_log  = 0
log_queries_not_using_indexes  = 1
long_query_time = 0.5
min_examined_row_limit = 100

slow_query_log：全局开启慢查问性能。

slow_query_log_file：指定慢查问日志存储文件的地址和文件名。

log_queries_not_using_indexes：无论是否超时，未被索引的记录也会记录下来。

long_query_time：慢查问阈值（秒），SQL 执行超过这个阈值将被记录在日志中。

min_examined_row_limit：慢查问仅记录扫描行数大于此参数的 SQL。

对于 MySQL 慢查问日志更多扩大内容，请看《如何应用慢查问日志对 MySQL 进行性能优化 – Profiling、mysqldumpslow 实例详解》

MySQL 全局查问日志

general_log_file = general.log
general_log = 0

这一段比拟好了解，寄存文件名，是否开启日志记录

Binary logging and Replication

server_id  = 42
log_bin  = mysql-bin
binlog_cache_size  = 1M

管制二进制日志缓存大小，减少其值可改善解决大事务的零碎的性能。在具备大量数据库连贯的环境中应限度该值。

binlog_stmt_cache_size = 1M

如果二进制日志处于活动状态，则此变量确定在每次事务中保留二进制日志更改记录的缓存的每个连贯的字节大小。独自的变量 binlog_stmt_cache_size 设置了语句缓存的下限。

该binlog_cache_disk_use 和 binlog_cache_use 服务器状态变量将显示这个变量是否须要减少。

max_binlog_size = 128M

如果二进制日志在写入后超出此大小，则服务器会通过敞开它并关上新的二进制日志来旋转它。

单个事务将始终存储在同一二进制日志中，因而服务器将期待未实现的事务在轮换之前实现。

如果将 max_relay_log_size 设置为 0，此图也实用于中继日志的大小。

sync_binlog = 0 

管制 binlog 写磁盘频率

expire_logs_days  = 5

主动二进制日志文件删除的天数。默认值为 0，table 示“不主动删除”。在启动时和革除二进制日志时，可能会删除它们。

binlog_format  = ROW

此变量设置二进制日志记录格局，并且能够是 STATEMENT，ROW 或 MIXED 三选一

binlog_row_image = MINIMAL

对于 MySQL 基于行的复制，此变量确定如何将行图像写入二进制日志。

隶属线程变量

log_slave_updates  = 1

如果设置为 0（默认值），则复制期间从主服务器接管到的从服务器上的更新不会记录在从服务器的二进制日志中。如果设置为 1，则为。须要启用从站的二进制日志能力失效。

read_only = 0
skip_slave_start = 0

平安变量

local_infile  = 0 

此变量管制 LOAD DATA 语句的服务器端 LOCAL 性能。依据 local_infile 设置，服务器会回绝或容许 Client 端启用 LOCAL 的 Client 端加载本地数据。

#secure_auth  = 1
#sql_mode = TRADITIONAL,ANSI,ONLY_FULL_GROUP_BY
#skip_name_resolve = 0

查看 Client 端连贯时是否解析主机名。如果此变量是 0，则 mysqld 在查看 Client 端连贯时解析主机名。

如果是 1，则 mysqld 仅应用 IPNumbers；在这种状况下，受权 table 中的所有 Host 列值都必须是 IP 地址

MyISAM 变量

key_buffer_size  = 8M 

MyISAM table 的索引块被缓冲并由所有线程共享。

key_buffer_size 是用于索引块的缓冲区的大小。密钥缓冲区也称为密钥缓存。

myisam_recover  = BACKUP,FORCE

设置 MyISAM 存储引擎恢复模式。变量值是 OFF，DEFAULT，BACKUP，FORCE 或 QUICK 的值的任意组合。

如果指定多个值，请用逗号分隔。在服务器启动时指定没有值的变量与指定 DEFAULT 雷同，指定显式值 ”” 会禁用复原(与 OFF 的值雷同)。

如果启用了复原，则每次 mysqld 关上 MyISAMtable 时，它都会查看该 table 是否标记为已解体或未正确敞开。

(只有在禁用内部锁定的状况下运行，最初一个选项才起作用.)在这种状况下，mysqld在 table 上运行查看。如果 table 已损坏，mysqld尝试修复它。

MEMORY 变量

max_heap_table_size  = 64M

此变量设置容许用户创立的 MEMORY table 增长的最大大小。

变量的值用于计算 MEMORYtableMAX_ROWS 的值。除非应用诸如 CREATE TABLE 之类的语句从新创立该 table 或应用 ALTER TABLE 或TRUNCATE TABLE对其进行更改，否则设置此变量对任何现有的 MEMORYtable 均有效。

服务器重新启动还会将现有 MEMORYtable 的最大大小设置为全局 max_heap_table_size 值。

InnoDB 变量

innodb_buffer_pool_size        = 128M

管制缓存表和索引数据的 InnoDB 缓冲池的内存大小

innodb_file_per_table          = 1 

此为独立表空间模式，每个数据库的每个表都会生成一个数据空间。当删除或截断一个数据库表时，你也能够回收未应用的空间。这样配置的另一个益处是你能够将某些数据库表放在一个独自的存储设备。这能够大大晋升你磁盘的 I / O 负载。

独立表空间长处：每个表都有自已独立的表空间。每个表的数据和索引都会存在自已的表空间中。能够实现单表在不同的数据库中挪动。空间能够回收（除 drop table 操作处，表空不能自已回收）

毛病：单表减少过大，如超过 100G

论断：共享表空间在 Insert 操作上少有劣势。其它都没独立表空间体现好。当启用独立表空间时，请正当调整：innodb_open_files

#innodb_buffer_pool_instances   = n
#innodb_write_io_threads        = 8
#innodb_read_io_threads         = 8

Innodb 应用后盾线程解决数据页上的读写 I/O(输入输出)申请, 依据你的 CPU 核数来更改, 默认是 4 #注: 这两个参数不反对动静扭转, 须要把该参数退出到 my.cnf 里，批改完后重启 MySQL 服务, 允许值的范畴从 1 -64

#innodb_io_capacity = 1000

innodb_io_capacity变量定义 InnoDB 可用的总体 I/O 容量。应该将其设置为大概零碎每秒能够执行的 I/O 操作数 (IOPS)。设置 innodb_io_capacity 时，InnoDB依据设置的值预计可用于后台任务的 I/O 带宽。

您能够将 innodb_io_capacity 设置为 100 或更大的值。默认值为200。通常，大概 100 的值实用于 Consumer 级别的存储设备，例如最高 7200 RPM 的硬盘驱动器。

innodb_flush_log_at_trx_commit = 2

这个选项决定着什么时候把日志信息写入日志文件以及什么时候把这些文件物理地写 (术语称为”同步”) 到硬盘上。

当设为 0 ,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作（执行是由 mysql 的 master thread 线程来执行的。

当设为 1 时，每次提交事务的时候，都会将 log buffer 刷写到日志。

当设为 2 , 每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要留神的是，并不能保障 100% 每秒肯定都会刷到磁盘，这要取决于过程的调度。

innodb_log_buffer_size         = 8M 

此参数确定些日志文件所用的内存大小，以 M 为单位。缓冲区更大能进步性能，但意外的故障将会失落数据。事务日志所应用的缓存区。InnoDB在写事务日志的时候为了进步性能，先将信息写入 Innodb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）时，再将日志写到文件（或者同步到磁盘）中。能够通过 innodb_log_buffer_size 参数设置其能够应用的最大内存空间。默认是 8MB，个别为 16～64MB 即可。

innodb_log_file_size           = 256M

事务日志文件写操作缓存区的最大长度。更大的设置能够进步性能，但也会减少复原故障数据库所需的工夫 Galera specific MySQL parameter default_storage_engine = InnoDB 服务器启动时必须启用默认存储引擎，否则服务器将无奈启动。默认设置是 MyISAM。这项设置还能够通过 –default-table-type 选项来设置。

#innodb_flush_log_at_trx_commit = 0

当值为 1 时，默认状况下，日志缓冲区被写入 InnoDB 重做日志文件，并在每次事务处理后刷新到磁盘。要完全符合ACID。当值为 0 时，提交时不做任何事件；而是将日志缓冲区每秒写入一次并刷新到 InnoDB 重做日志中。这样能够提供更好的性能，然而服务器解体能够革除事务的最初一秒。当值为 2 时，每次提交后，日志缓冲区都会写入 InnoDB 重做日志，但刷新每秒产生一次。性能稍好一些，然而操作系统或断电可能导致最初一秒的事务失落。

#innodb_autoinc_lock_mode = 2

为 InnoDB 表生成 AUTO_INCREMENT 值时应用的锁定模式。有效值为:

0 是传统锁定模式。1 是间断锁定模式。2 是交织锁定模式。

#binlog_format  = row

#query_cache_type = 0

• 设置为 0 时，则禁用查问缓存（只管仍调配 query_cache_size 个字节的缓冲区）。
• 设置为 1 时, 除非指定SQL_NO_CACHE，否则所有 SELECT 查问都将被缓存。
• 设置为 2 时，则仅缓存带有 SQL CACHE 子句的查问。请留神，如果在禁用查问缓存的状况下启动服务器，则无奈在运行时启用服务器。

扩大浏览：《如何在 MySQL / MariaDB 中查找和删除重复记录？– 4 种 MySQL 数据去重法》

WSREP 配置

#wsrep_provider = /usr/lib/libgalera_smm.so 

wsrep 库的地位

通常不同版本的 Linux 地位

• Debian 和 Ubuntu 在 /usr/lib/libgalera_smm.so
• Red Hat / CentOS 在 /usr/lib64/libgalera_smm.so

#wsrep_cluster_name = "kalacloud.com Galera Cluster"

集群的名称。节点无奈连贯到名称不同的集群，因而在同一集群中的所有节点上都必须雷同。

#wsrep_cluster_address  = "gcomm://" 
#wsrep_cluster_address = "gcomm://192.168.0.2,192.168.0.3"

启动时要连贯的群集节点的地址

#wsrep_node_name  = "Node A" 

此节点的名称。此名称能够在 wsrep_sst_donor 中用作首选供体。请留神，集群中的多个节点能够具备雷同的名称。

#wsrep_node_address = 192.168.0.1

以 ip address[:port] 格局指定节点的网络地址。

wsrep_node_incoming_address    = 10.0.0.1

这是节点用来侦听客户端连贯的地址。如果未指定地址或将其设置为 AUTO（默认），则 mysqld 应用 --bind-address 或--wsrep-node-address，或尝试以雷同程序从可用网络接口列表中获取一个地址。

#wsrep_causal_reads = 0 

设置为 1 时（默认为 0），则在整个集群中强制执行读取提交的特色。

如果主机比从机更快地利用事件，则两者可能会短暂地不同步。

在将此变量设置为 1 的状况下，隶属将期待事件利用，而后再解决其余查问。

#wsrep_sst_method  = mysqldump

用于进行状态快照传输（SST）的办法。可选办法有 rsync，mysqldump，xtrabackup，xtrabackup-v2，mariabackup

#wsrep_sst_auth                 = sst:sst     

用于复制的用户名和明码。如果 wsrep_sst_method 设置为 rsync，则不应用，而对于其余办法，它的格局应为 <user>:<password>。当应用 SHOW VARIABLES 查问值时，内容在日志中被屏蔽。

#wsrep_sst_receive_address      = 192.168.0.1 

这是集群中其余节点（供体）连贯到的地址，用于发送状态转移更新。如果未指定地址或将其设置为 AUTO（默认），则 mysqld 应用 --wsrep_node_address 的值作为接管地址。然而，如果未设置 --wsrep_node_address，则它将应用--bind-address 中的地址，或尝试以雷同程序从可用网络接口列表中获取一个地址。

[mysql]
no_auto_rehash

敞开主动补全 SQL 命令性能

max_allowed_packet = 16M

数据包或生成的 / 两头的字符串的最大大小（以字节为单位）。

数据包音讯缓冲区应用 net_buffer_length 中的值进行初始化，但能够增长到 max_allowed_packet 个字节。设置为最大 BLOB 的最大值（1024 的倍数）。

如果更改此值，则也应该在客户端更改它。

prompt = '\u@\h [\d]>'

此为批改 mysql 提示符内容的变量。咱们自定义提示符信息。通过配置能够显示登入的主机地址，登陆用户名，以后工夫，以后数据库等信息。

[mysqldump]

max_allowed_packet = 16M

限度承受的数据包大小，这里的值为 MySQL 服务器端和客户端在一次传送数据包的过程当中数据包的大小

扩大浏览：《最好用的 10 款 MySQL GUI 管理工具横向测评》

卡拉云 – 新一代低代码开发工具

MySQL 配置文件对于优化数据库性能有着极大的意义，咱们不仅要搞懂每一行代码的意义，更要结合实际状况，在实践中边改边测，最终达到性能最大化的指标。

如果你想在你的 MySQL 数据库上构建利用工具，能够试试卡拉云，卡拉云是新一代低代码开发工具，免装置部署，可一键接入包含 MySQL 在内的常见数据库及 API。不仅能够实现 Workbench 所有性能，还可依据本人的工作流，定制开发。无需繁琐的前端开发，只须要简略拖拽，即可疾速搭建企业外部工具。数月的开发工作量，应用卡拉云后可缩减至数天，。

卡拉云可一键接入常见的数据库及 API

卡拉云可依据公司工作流需要，轻松搭建数据看板，并且可分享给组内的小伙伴共享数据

下图为应用卡拉云在 5 分钟内搭建的「优惠券发放核销」后盾，仅须要简略拖拽即可疾速生成前端组件，只有会写 SQL，便可搭建一套趁手的数据库工具。欢送试用卡拉云。

无关 MySQL 教程，可持续拓展学习：

• 如何近程连贯 MySQL 数据库，阿里云腾讯云外网连贯教程
• 如何在 MySQL / MariaDB 中导入导出数据，导入导出数据库文件、Excel、CSV
• 如何在两台服务器之间迁徙 MySQL 数据库 阿里云腾讯云迁徙案例
• MySQL 中如何实现 BLOB 数据类型的存取，BLOB 有哪些利用场景？
• MySQL 触发器的创立、应用、查看、删除教程及利用场景实战案例