充分理解 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 的配置文件，争取搞懂每一条变量。当然，我们理解了变量的意义外，更重要的是在实践中进行微调，以达到优化性能的目的。
mysql> show variables like 'datadir';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /usr/local/mysql/data/ |
+---------------+------------------------+
复制代码
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)
复制代码
mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.00 sec)
复制代码
default_storage_engine=InnoDB
复制代码
skip-grant-tables作用是启动 MySQL 服务器时跳过授权认证阶段，即不需要输入用户名和密码即可登录 MySQL 服务器。启动 MySQL 服务器时使用 skip-grant-tables 选项后，所有连接到 MySQL 服务器的用户都将具有 root 权限
skip-grant-tables
复制代码
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，如果数据库的并发连接请求比较大，应该调高该值，调整连接数的时候，应该充分考虑硬件资源等因素，连接数过大，容易造成阻塞
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，表示不限制。
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,则不再接受连接资源。
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之后，会不会报错
mysql> show variables like '%wAIt_timeout%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| wait_timeout | 28800 |
+--------------------------+----------+
复制代码
wait_timeout针对非交互式连接，其默认也是28800，单位秒，即8个小时；像我们常见的JDBC链接，就是非交互式连接。
cat /etc/my.cnf
#
log-error=/var/log/mysqld.log
复制代码
log-error用于指定错误日志文件名称，用于记录当mysqld启动和停止时，以及服务器在运行中发生任何严重错误时的相关信息
开启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的值。
默认情况下，如果我们开启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)
复制代码
与binlog_do_db配置相反，binlog_ignore_db表示指定不将更新记录到二进制日志的数据库，既指定哪些数据库不开启binlog；这两个参数为互斥关系，一般只选择其一设置即可。
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 可能会对服务器性能产生负面影响，并且可能会产生大量的日志数据，因此建议仅在需要时启用它，并定期清理日志文件
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 还可以记录一些其他信息，例如查询的锁定等待时间、扫描行数、返回行数、执行时间等。
long_query_time表示慢查询的时间，超过这个时间的查询语句才会记录到慢查询文件中，如下，默认值为10 秒。
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
复制代码
查询缓存在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 |
+------------------------------+---------+
复制代码
如上所示，下面再将一些核心属性做简要解释：
虽然查询缓存这个模块已经在MySQL8被去掉，但是如果你使用的是MySQL7及以下版本，也可以充分利用该查询缓存的特性，提高MySql查询效率，如一些经常不变的配置表
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。
当 MySQL 执行一个联接查询时，它需要将两个或多个表中的数据进行匹配。为了提高连接操作的性能，MySQL 会将其中一个表中的数据先读入缓存中，然后在缓存中执行连接操作，而不是每次都从磁盘读取数据默认情况下:
mysql> show variables like '%join_buffer_size%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
复制代码
join_buffer_size 的值为 262144（字节），即 256 KB。如果你的 MySQL 服务器上经常执行大型联接查询，可以考虑增加这个值，以提高查询性能。不过，需要注意的是，过大的缓冲区可能会导致内存使用过高，因此需要根据实际情况进行调整
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 |
+-------------------+-------+
复制代码
适当地设置 thread_cache_size 可以帮助减少服务器创建和销毁线程的开销，从而提高 MySQL 的性能建议将 thread_cache_size 设置为等于服务器最大连接数的 10% - 25%。
当查询需要访问表中的数据时，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%左右
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 |
+--------------------------------+-------+
复制代码
该参数有以下三种可能的值：
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，表示没有限制。
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 或更小的值。如果需要更大的日志文件，可以增加日志文件的数量，而不是增加每个文件的大小
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 文件，因此在修改该参数之前，请务必备份数据，以防止数据丢失。
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
京公网安备 11010802035086号