MySQL8.0参数文件

MySQL8.0参数文件

MySQL 8一共有六百多个系统参数，但对系统性能影响大的参数只有几十个。
参数说明参见：https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html

1.1全局级和会话级参数MySQL的系统参数根据作用范围可以分为全局级和会话级，全局级的系统参数对实例的所有会话起作用，会话级的系统参数只对当前会话起作用，在这两个级别修改系统参数的例子如下：
mysql> set session binlog_rows_query_log_events=on;

mysql> set global binlog_rows_query_log_events=on;

root@db 15:21: [(none)]> set session binlog_rows_query_log_events=on;
Query OK, 0 rows affected (0.00 sec)

root@db 15:24: [(none)]> set global binlog_rows_query_log_events=on;
Query OK, 0 rows affected (0.00 sec)

其中session可以省略。

1.2修改全局系统参数的生效时间修改全局系统参数只对修改后连接到MySQL的会话生效，在修改之前已经建立的会话还保持原来的参数值，例如下面的命令修改一个全局系统参数：
root@db 15:27: [(none)]> set global sort_buffer_size=16*1024*1024;
Query OK, 0 rows affected (0.00 sec)

root@db 15:27: [(none)]> select @@global.sort_buffer_size,@@session.sort_buffer_size;
+---------------------------+----------------------------+
| @@global.sort_buffer_size | @@session.sort_buffer_size |
+---------------------------+----------------------------+
| 16777216 | 262144 |
+---------------------------+----------------------------+
1 row in set (0.00 sec)

发现全局级的参数值已经改过来了，而会话级的参数值没有变。

一些系统参数只能是全局级的
有一些系统参数只能是全局级的，在会话级修改这类参数会出错，例如：
root@db 15:27: [(none)]> set log_error_verbosity=2;
ERROR 1229 (HY000): Variable 'log_error_verbosity' is a GLOBAL variable and should be set with SET GLOBAL

参见参数说明（https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html）中的Var Scope字段。

查询其他会话的参数
如果要查询其他会话的参数可以在performance_schema.variables_by_thread视图中查询，例如下面的SQL语句查询所有会话的事务隔离级别如下：

root@db 15:28: [(none)]> select * from performance_schema.variables_by_thread where variable_name='transaction_isolation';
+-----------+-----------------------+-----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+-----------------------+-----------------+
| 390 | transaction_isolation | REPEATABLE-READ |
+-----------+-----------------------+-----------------+
1 row in set (0.00 sec)

1.3静态参数和动态参数MySQL的系统参数还可以分为静态参数和动态参数，动态参数可以MySQL运行中进行修改，静态参数在MySQL启动后无法修改，例如：

root@db 15:30: [(none)]> set auto_generate_certs=on;
ERROR 1238 (HY000): Variable 'auto_generate_certs' is a read only variable

系统参数设置为default
如果把会话级系统参数设置为default，对应的是全局级系统参数值。下面两个设置会话级参数的语句效果是一样的：
mysql> SET @@SESSION.max_join_size = DEFAULT;
mysql> SET @@SESSION.max_join_size = @@GLOBAL.max_join_size;
如果把全局级系统参数设置default，将把系统参数恢复为MySQL内置的默认值，而不是像很多人认为的是参数文件里面的设置值。

参数文件my.cnf中设置max_join_size值为1073741824
root@db 15:42: [(none)]> show variables like '%max_join_size%';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| max_join_size | 1073741824 |
+---------------+------------+
1 row in set (0.00 sec)

把会话级系统参数设置为default
root@db 15:42: [(none)]> SET @@SESSION.max_join_size = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

查询max_join_size值为MySQL内置的默认值
root@db 15:47: [(none)]> show variables like '%max_join_size%';
+---------------+----------------------+
| Variable_name | Value |
+---------------+----------------------+
| max_join_size | 18446744073709551615 |
+---------------+----------------------+
1 row in set (0.00 sec)

1.4持久化参数设置在系统参数设置时，一个容易犯的错误是在MySQL运行时修改了参数值，但没有同时修改参数文件里面的配置，当MySQL重新启动后，参数文件里的旧值生效，之前的修改丢掉了。在MySQL 8里， MySQL推出了让参数持久化的命令，可以让在联机时修改的系统参数在重新启动后仍然生效，例如： mysql> set persist max_connections = 1000;
或者：
mysql> set @@persist.max_connections = 1000;

root@db 15:52: [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 512 |
+-----------------+-------+
1 row in set (0.01 sec)

root@db 15:52: [(none)]> set persist max_connections = 1000;
Query OK, 0 rows affected (0.01 sec)

root@db 15:56: [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1000 |
+-----------------+-------+
1 row in set (0.00 sec)

重启MySQL，查询max_connections为1000

root@db 15:57: [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1000 |
+-----------------+-------+
1 row in set (0.01 sec)

如果想让系统参数在本次MySQL运行时不生效，只是在下次启动时生效，可以使用下面的命令：
mysql> set persist_only back_log = 100;
或者：
mysql> set @@persist_only.back_log = 100;

root@db 15:58: [(none)]> show variables like 'back_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log | 1024 |
+---------------+-------+
1 row in set (0.00 sec)

root@db 15:58: [(none)]> set persist_only back_log = 100;
Query OK, 0 rows affected (0.01 sec)

root@db 15:59: [(none)]> show variables like 'back_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log | 1024 |
+---------------+-------+
1 row in set (0.00 sec)

重启MySQL，查询back_log参数值为100
root@db 15:59: [(none)]> show variables like 'back_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log | 100 |
+---------------+-------+
1 row in set (0.01 sec)

1.5持久化的系统参数以JSON格式保存持久化的系统参数以JSON格式保存在数据目录的mysqld-auto.cnf文件中，例如：

[root@node1 ~]# cat /data/mysql/data/mysqld-auto.cnf
{"Version": 2, "mysql_static_variables": {"back_log": {"Value": "100", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1708329549120636}}}, "mysql_dynamic_parse_early_variables": {"max_connections": {"Value": "1000", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1708329384112700}}}}

可以通过reset persist命令来清除mysqld-auto.cnf文件中的所有配置，也可以通过reset persist 接参数名的方式来清除某个指定的配置参数。

执行reset persist命令
root@db 16:08: [(none)]> reset persist;
Query OK, 0 rows affected (0.00 sec)

mysqld-auto.cnf文件被清除
[root@node1 ~]# cat /data/mysql/data/mysqld-auto.cnf
{"Version": 2}

1.6参数值的来源现在系统参数可以查询视图performance_schema.variables_info找到相关信息，例如：
select variable_name, variable_source as source, variable_path, set_time, set_user as user, set_host
from performance_schema.variables_info where variable_name='max_connections' or variable_name='socket'\G

root@db 15:21: [(none)]> select variable_name, variable_source as source, variable_path, set_time, set_user as user, set_host
-> from performance_schema.variables_info where variable_name='max_connections' or variable_name='socket'\G
*************************** 1. row ***************************
variable_name: max_connections
source: EXPLICIT
variable_path: /etc/my.cnf
set_time: NULL
user: NULL
set_host: NULL
*************************** 2. row ***************************
variable_name: socket
source: COMMAND_LINE
variable_path:
set_time: NULL
user: NULL
set_host: NULL
2 rows in set (0.01 sec)

查询参数文件
root@db 16:11: [(none)]> select variable_path,variable_source,count(*) from performance_schema.variables_info where length(variable_path)!=0 group by variable_path,variable_source;
+----------------------------------+-----------------+----------+
| variable_path | variable_source | count(*) |
+----------------------------------+-----------------+----------+
| /data/mysql/data/mysqld-auto.cnf | PERSISTED | 2 |
| /etc/my.cnf | GLOBAL | 27 |
+----------------------------------+-----------------+----------+
2 rows in set (0.00 sec)

以上为学习笔记