27个常见的MySQL服务器参数配置

2023-03-08 今日头条 Java灵风

充分理解 MySQL 配置文件中各个变量的意义对我们有针对性的优化 MySQL 数据库性能有非常大的意义。通常我们需要根据不同的数据量级，不同的生产环境情况对MySQ配置文件进行优化

linux下 MySQL配置文件是 my.cnf 存放在 /etc/my.cnf、/etc/mysql/my.cnf,/usr/etc/my.cnf，~/.my.cnf，如果没有设置使用指定目录的my.cnf，MySQl会使用按照优先级使用上面的文件，越在前面的优先级越高。

本教程将带领大家逐条解析最新的 MySQL 8.0 的配置文件，争取搞懂每一条变量。当然，我们理解了变量的意义外，更重要的是在实践中进行微调，以达到优化性能的目的。

1. 通用配置

1.1 数据文件存放目录

mysql> show variables like 'datadir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
复制代码

1.2 进程id存放文件目录

mysql> show variables like 'pid_file';
+---------------+----------------------------------------+
| Variable_name | Value                                  |
+---------------+----------------------------------------+
| pid_file      | /usr/local/mysql/data/mysqld.local.pid |
+---------------+----------------------------------------+
1 row in set (0.01 sec)
复制代码

1.3 端口号


mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)
复制代码

1.4 默认存储引擎

default_storage_engine=InnoDB
复制代码

1.5 是否需要密码校验

skip-grant-tables作用是启动 MySQL 服务器时跳过授权认证阶段，即不需要输入用户名和密码即可登录 MySQL 服务器。启动 MySQL 服务器时使用 skip-grant-tables 选项后，所有连接到 MySQL 服务器的用户都将具有 root 权限

skip-grant-tables
复制代码

2.连接数

2.1 最大连接数

mysql> show variables  like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
+------------------------+-------+
1 rows in set (0.01 sec)
mysql> set global max_connections=256;
Query OK, 0 rows affected (0.00 sec)
复制代码

mysql的最大连接数max_connections，如果数据库的并发连接请求比较大，应该调高该值，调整连接数的时候，应该充分考虑硬件资源等因素，连接数过大，容易造成阻塞

2.2 单个用户最大连接数

mysql> show variables like '%max_user_connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_user_connections | 0     |
+----------------------+-------+
1 row in set (0.01 sec)
复制代码

注：max_user_connections默认值为0，表示不限制。

2.3 最大等待连接数

mysql> show variables like '%back_log%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log      | 151   |
+---------------+-------+
1 row in set (0.00 sec)
复制代码

back_log表示MySQL能够暂存的连接数量，当MySQL在一个很短时间内得到非常多的连接请求时，就会起作用；举个例子，假如MySQL的连接数量达到max_connections时，新的请求会被存储在堆栈中，以等待某一个连接释放资源，如果等待连接的数量超过back_log,则不再接受连接资源。

2.4 交互式最大连接时长

mysql> show variables like '%interactive_timeout%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| interactive_timeout        | 28800 |
+----------------------------+-------+
复制代码

interactive_timeout针对交互式连接，所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。说得直白一点，通过MySQL客户端连接数据库的是交互式连接，如通过mysql -u -p命令连接到MySQL。

interactive_timeout默认是28800，单位秒，即8个小时，有兴趣的小伙伴可以将该参数改成10s，然后重新连接，看10s之后，会不会报错

2.5 非交互式最大连接时长

mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| wait_timeout             | 28800    |
+--------------------------+----------+
复制代码

wait_timeout针对非交互式连接，其默认也是28800，单位秒，即8个小时；像我们常见的JDBC链接，就是非交互式连接。

3.日志

3.1 错误日志文件名称

cat /etc/my.cnf
# 
log-error=/var/log/mysqld.log
复制代码

log-error用于指定错误日志文件名称，用于记录当mysqld启动和停止时，以及服务器在运行中发生任何严重错误时的相关信息

3.2 开启binlog

开启MySQL的binlog 需要在配置文件my.cnf添加如下配置，然后重启服务：

server_id=2
log_bin=mysql-bin
binlog_format=ROW
expire_logs_days=30
复制代码

注意server_id是必填的，其它配置可选填；重启之后，登录MySQL，查看binlog是否开启：

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)
复制代码

log_bin 为ON表示已经开启,OFF表示关闭，MySQl默认是不开启binlog的，感兴趣的同学可先不修改my.cnf文件，先查看一下log_bin的值。

3.3 指定数据库开启binlog

默认情况下，如果我们开启binlog，MySQL默认会记录所有数据库的binlog日志；这样我们MySQL的binlog日志会变得很大，做数据恢复的是也会变得很慢，所以有的时候，我们可以只指定一些核心重要的库才开启binlog。

如下，在MySQL开启binlog的前提下，只对数据库test开启binlog

binlog_do_db=test
复制代码

重启之后，登录MySQL,查看binlog_do_db选项：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      157 | test         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
复制代码

3.4 指定数据库不开启binlog

与binlog_do_db配置相反，binlog_ignore_db表示指定不将更新记录到二进制日志的数据库，既指定哪些数据库不开启binlog；这两个参数为互斥关系，一般只选择其一设置即可。

3.5 查询日志记录

general_log允许您记录服务器执行的所有 SQL 语句。这对于调试和故障排除以及审计目的非常有用。

启用 general_log 后，MySQL 将所有执行的 SQL 语句写入日志文件中，包括 SELECT、INSERT、UPDATE、DELETE 等操作，以及连接和断开连接的事件。

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
复制代码

您可以使用 SET GLOBAL general_log = 'ON'; 命令来启用 general_log，或使用 SET GLOBAL general_log = 'OFF'; 命令来禁用它。您还可以使用 SHOW VARIABLES LIKE 'general_log%'; 命令来查看有关 general_log 的当前设置。但是，启用 general_log 可能会对服务器性能产生负面影响，并且可能会产生大量的日志数据，因此建议仅在需要时启用它，并定期清理日志文件

3.6 慢查询日志记录

slow_query_log 可以将执行时间超过预设阈值的查询定义为蛮SQL，这对于优化查询性能非常有用，因为可以查找和分析耗时较长的查询语句并进行优化。

当 slow_query_log 启用后，MySQL 会将所有执行时间超过预设阈值的查询语句记录到一个日志文件中：

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
复制代码

除了记录慢查询语句外，slow_query_log 还可以记录一些其他信息，例如查询的锁定等待时间、扫描行数、返回行数、执行时间等。

3.7 慢查询的时间

long_query_time表示慢查询的时间，超过这个时间的查询语句才会记录到慢查询文件中，如下，默认值为10 秒。

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
复制代码

4.缓存

4.1 查询缓存

查询缓存在MySQL8已经被去掉，如果小伙伴用的是MySQL7以上的，可不关注此块内容；如下，通过执行命令 show status like '%query_cache%'来查看查询缓存的相关属性

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
复制代码

如上所示，下面再将一些核心属性做简要解释：

query_cache_size：查询缓存的大小，未来版本被删除
query_cache_limit: 超出此大小的查询将不被缓存
query_cache_min_res_unit：缓存块最小大小，默认是4k
query_cache_type：缓存类型，决定缓存什么样的查询 0：表示禁用 1：表示将缓存所有结果，除非sql语句中使用sql_no_cache禁用查询缓存 2：表示只缓存select语句中通过sql_cache指定需要缓存的查询

虽然查询缓存这个模块已经在MySQL8被去掉，但是如果你使用的是MySQL7及以下版本，也可以充分利用该查询缓存的特性，提高MySql查询效率，如一些经常不变的配置表

4.2 排序缓存区大小

sort_buffer_size表示每个需要排序的线程分派该大小的缓冲区：

mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+
复制代码

sort_buffer_size 表示所有存储引擎的默认配置，innodb_sort_buffer_size表示InonoDB存储引擎，默认大小为1M，myisam_sort_buffer_size表示myisam存储引擎，默认大小为8M。

4.3 join 缓冲区大小

当 MySQL 执行一个联接查询时，它需要将两个或多个表中的数据进行匹配。为了提高连接操作的性能，MySQL 会将其中一个表中的数据先读入缓存中，然后在缓存中执行连接操作，而不是每次都从磁盘读取数据默认情况下:

mysql> show variables like '%join_buffer_size%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
复制代码

join_buffer_size 的值为 262144（字节），即 256 KB。如果你的 MySQL 服务器上经常执行大型联接查询，可以考虑增加这个值，以提高查询性能。不过，需要注意的是，过大的缓冲区可能会导致内存使用过高，因此需要根据实际情况进行调整

4.5 thread_cache_size-线程缓存池

MySQL中的thread_cache_size 是一个控制线程缓存的系统变量。该变量指定了 MySQL 服务器在缓存中保留的线程数。线程缓存可以减少创建和销毁线程的开销，从而提高 MySQL 的性能。当客户端连接到 MySQL 服务器时，服务器会检查是否有已经存在的线程可供使用。如果存在，则使用该线程服务客户端请求。否则，服务器将创建一个新线程来处理客户端请求；

mysql> show variables like 'Thread_cache%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+
1 row in set (0.00 sec)
复制代码

thread_cache_size用来缓存空闲的线程，如果该值是0，这意味着线程缓存被禁用，如果设置了一个非零的值，则 MySQL 服务器将尝试在缓存中保留指定数量的线程。如果缓存已满，则服务器将在需要时创建新线程，并在使用后将其放回缓存。下面我们再来看看与其关联的四个状态变量：

mysql> show global status like 'Threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 2     |
| Threads_running   | 2     |
+-------------------+-------+

复制代码

Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程
Threads_connected：代表当前已建立连接的数量
Threads_created ：代表从最近一次服务启动，已创建线程的数量
Threads_running：代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态

适当地设置 thread_cache_size 可以帮助减少服务器创建和销毁线程的开销，从而提高 MySQL 的性能建议将 thread_cache_size 设置为等于服务器最大连接数的 10% - 25%。

5.Innodb存储引擎

5.1 buffer pool 数据缓冲区

当查询需要访问表中的数据时，InnoDB存储引擎会先查看缓冲池中是否有相关的数据。如果缓冲池中已经有数据，InnoDB存储引擎就可以直接从缓冲池中获取数据，而不需要从磁盘中读取数据。这样可以大大提高查询的速度，其默认大小为128M:

mysql> show variables  like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
复制代码

innodb_buffer_pool_size变量的值越大，InnoDB存储引擎可以缓存的数据就越多，查询的速度就会越快。但是，将其设置得过大可能会占用过多的内存资源，导致系统性能下降。通常情况下，建议将innodb_buffer_pool_size设置为系统内存的70%~80%左右

5.2 事务日志刷盘时间点

innodb_flush_log_at_trx_commit用于控制redo log buffer的数据何时写入到磁盘。

mysql> show variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
复制代码

该参数有以下三种可能的值：

0：表示事务提交时不立即将日志写入磁盘，而是将日志缓存在内存中，直到发生检查点或者缓冲池满时才写入磁盘。这种方式可以提高性能，但是在数据库崩溃时可能会丢失最近提交的事务数据。
1：表示事务提交时立即将日志写入磁盘，并等待磁盘写操作完成后才返回客户端。这种方式可以保证最大程度上的数据安全性，但会对性能产生较大影响。
2：表示事务提交时将日志缓存在内存中，但每秒钟将缓冲区中的日志写入磁盘。这种方式可以在一定程度上平衡性能和数据安全性。

5.3 innodb_thread_concurrency 并发线程数

innodb_thread_concurrency定义了在 InnoDB 引擎中可并发运行的线程数量，包括读取线程、写入线程、刷新线程和其他一些管理线程。innodb_thread_concurrency 的原理其实就是通过限制 InnoDB 线程池中可并发执行的线程数量来避免系统资源被过度占用，从而提高系统的性能和稳定性。然而，在实际应用中，由于不同的应用场景和系统配置可能会产生不同的结果，因此需要根据实际情况进行调整。一般来说，在高负载的数据库系统中，适当地调整 innodb_thread_concurrency 的值可以有效地提高系统的并发性能

mysql> show variables like '%innodb_thread_concurrency%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0     |
+---------------------------+-------+
复制代码

默认情况下，innodb_thread_concurrency 的值被设置为 0，表示没有限制。

5.4 redolog文件大小

innodb_log_file_size 参数用于设置 InnoDB 存储引擎单个日志文件大小，以字节为单位；如下，其默认大小为48MB:

mysql> show variables like '%innodb_log_file_size%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
复制代码

如果将 innodb_log_file_size 大小设置过大，可能会导致性能下降或造成磁盘空间的浪费。因此，建议根据实际需求和系统负载进行调整。通常建议将 innodb_log_file_size 设置为 1GB 或更小的值。如果需要更大的日志文件，可以增加日志文件的数量，而不是增加每个文件的大小

5.5 redolog文件个数

innodb_log_files_in_group表示redo log组中包含的 redo log 文件的数量。其默认值是2：

mysql> show variables like '%innodb_log_files_in_group%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+
复制代码

通常情况下，建议将 innodb_log_files_in_group 设置为 2 或 3，这样可以在一个 redo log 文件被写满时，立即切换到下一个 redo log 文件，从而避免出现写满 redo log 文件时的性能问题。但是，修改 innodb_log_files_in_group 参数的值会涉及到重建 redo log 文件，因此在修改该参数之前，请务必备份数据，以防止数据丢失。

5.6 innodb_file_per_table

innodb_file_per_table作用是控制 InnoDB 存储引擎是否将每个表的数据和索引存储到单独的文件中：

mysql> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
复制代码

当 innodb_file_per_table 设置为 ON 时，每个表的数据和索引都将被存储在单独的 .ibd 文件中。这样的好处是可以方便地进行备份和恢复操作，并且可以更加灵活地管理磁盘空间。此外，如果某个表需要进行修复或优化，可以更加方便地针对该表进行操作，而不会影响到其他表。

当 innodb_file_per_table 设置为 OFF 时，所有表的数据和索引都将被存储在共享的 ibdata 文件中。这样的好处是可以减少文件系统的碎片，并且可以更加高效地利用磁盘空间。但是，在这种情况下，如果需要恢复某个表的数据，就需要恢复整个 ibdata 文件，这可能会比较耗时和麻烦。

因此，根据具体的应用场景和需求，可以选择打开或关闭 innodb_file_per_table 配置选项。

原文链接：
https://juejin.cn/post/7207718128939221049