MySQL 8.0 OCP (1Z0-908) 考点精析-安装与配置考点1：设置系统变量

【免责声明】文章仅供学习交流，观点代表个人，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)

【MySQL】控制MySQL优化器行为方法之optimizer_switch系统变量
MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点2：系统变量的确认
【MySQL】MySQL系统变量（system variables）列表（mysqld --verbose --help的结果例）
【MySQL】MySQL系统变量（system variables）列表（SHOW VARIABLES 的结果例）

MySQL 8.0 之设置系统变量

MySQL中的系统变量是影响MySQL服务器行为的变量，从影响范围来说，可以分为全局变量和会话变量。
系统变量可以在服务器启动时使用命令行选项或配置文件进行设置，其中大部分可以在运行时使用SET语句进行动态修改，能够在不停止和重新启动服务器的情况下修改服务器的动作行为。
另外，还有些变量是只读的，它们的值由系统环境、MySQL在系统上的安装方式或用于编译MySQL的选项决定。

系统变量的确认

系统变量的确认方法可以参考如下文章。

MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点2：系统变量的确认

设置系统变量的方法

MySQL中的系统变量可以通过多种方式进行设置：

（1）通过启动选项设置。在启动MySQL服务器时，可以通过命令行参数或配置文件来设置系统变量，例如：

mysqld --max_connections=1000
1

（2）在服务器运行时设置系统变量。可以使用SET语句来设置系统变量，例如：

SET GLOBAL max_connections=1000;
1

（3）通过配置文件设置系统变量。可以在MySQL配置文件中设置系统变量，例如：

[mysqld]
max_connections=1000
1
2

SET命令设置系统变量

MySQL提供SET命令用于设置各种类型的系统变量。

SET命令语法

SET命令语法如下：

SET variable = expr [, variable = expr] ...

variable: {
    user_var_name
  | param_name
  | local_var_name
  | {GLOBAL | @@GLOBAL.} system_var_name
  | {PERSIST | @@PERSIST.} system_var_name
  | {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name
  | [SESSION | @@SESSION. | @@] system_var_name
}
1
2
3
4
5
6
7
8
9
10
11

参考：
13.7.6.1 SET Syntax for Variable Assignment
https://dev.mysql.com/doc/refman/8.0/en/set-variable.html

动态系统变量（Dynamic System Variables）

动态系统变量是可以在MySQL运行时进行设置的系统变量。
这些变量的值可以通过SET语句进行动态修改，而无需停止和重新启动服务器。

变量的作用范围可以分为如：

 - 全局级别（GLOBAL）：影响整个数据库库实例 	
 - 会话级别（SESSION或者LOCAL  ）：仅影响所在的会话连接
1
2

另外也有些系统变量同时具有全局和会话级别的作用范围。

全局级别变量的设置方法

可以通过SET设置全局变量：

SET GLOBAL 系统变量名 = 值;
或者
SET @@GLOBAL.系统变量名 = 值;
1
2
3

会话级别变量的设置方法

可以通过SET设置会话变量：

SET SESSION 系统变量名 = 值;
或者
SET @@SESSION.系统变量名 = 值;
或者
SET LOCAL  系统变量名 = 值;
或者
SET @@LOCAL.系统变量名 = 值;
或者
SET  系统变量名 = 值;
1
2
3
4
5
6
7
8
9

系统变量的设置例

我们通过如下例子看看如何设置系统变量。

设置会话级别变量：

mysql> show variables like 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set, 1 warning (0.00 sec)

mysql>  show global variables like  'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set, 1 warning (0.00 sec)

mysql> set join_buffer_size=256;
Query OK, 0 rows affected (0.00 sec)

mysql>  show global variables like  'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set, 1 warning (0.00 sec)

mysql> show variables like 'join_buffer_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| join_buffer_size | 256   |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

设置全局级别变量：

mysql> set global  join_buffer_size= 4294967168;
Query OK, 0 rows affected (0.00 sec)

mysql>  show variables like 'join_buffer_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| join_buffer_size | 256   |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>  show global variables like  'join_buffer_size';
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| join_buffer_size | 4294967168 |
+------------------+------------+
1 row in set, 1 warning (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

其他设置例：

SET SESSION sql_mode = 'TRADITIONAL';
SET LOCAL sql_mode = 'TRADITIONAL';
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET @@LOCAL.sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';
SET sql_mode = 'TRADITIONAL';
1
2
3
4
5
6

参考：
5.1.9.2 Dynamic System Variables
https://dev.mysql.com/doc/refman/8.0/en/dynamic-system-variables.html

系统变量的持久化（Persisted System Variables）

全局变量的修改(SET GLOBAL)只在当前MySQL服务器运行期间有效，重启MySQL服务器后会恢复为默认值。
在MySQL 8.0之前的版本，可以通过修改配置文件(my.cnf)来实现系统变量的持久化，但是要使设置生效需要重启MySQL服务器。
在MySQL 8.0以后得版本，可以通过SET命令选项(SET PERSIST、SET PERSIST_ONLY)将全局变量的修改可以持久化到磁盘上( 名为mysqld-auto.cnf的文件中，该文件位于数据目录中)，即使重启MySQL服务器后也能保持修改后的值。

持久化全局系统变量

可以通过SET PERSIST命令持久化全局系统变量。

SET PERSIST 系统变量名 = 值;
或者
SET @@PERSIST.系统变量名 = 值;
1
2
3

通过SET PERSIST 命令将全局系统变量持久化到mysqld-auto.cnf文件中，同时也会修改全局变量的运行时值。

例：

mysql>  show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 200   |
+-----------------+-------+
1 row in set (0.01 sec)

mysql>  show session variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 200   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>  SET PERSIST max_connections = 152;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 152   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>  show session variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 152   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

查看mysqld-auto.cnf文件，我们可以看到多出了一行值。

ubuntu@mysql-vm:~$ sudo cat /var/lib/mysql/mysqld-auto.cnf
{"Version": 2, "mysql_dynamic_parse_early_variables": {"max_connections": {"Value": "152", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1693542542319277}}}}
ubuntu@mysql-vm:~$
1
2
3

重启MySQL，可以看到修改的系统变量在重启后依然有效。

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ERROR:
Can't connect to the server

mysql> exit
Bye
ubuntu@mysql-vm:~$ sudo systemctl status mysql
○ mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Fri 2023-09-01 12:34:01 CST; 58s ago
    Process: 11820 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Main PID: 11820 (code=exited, status=0/SUCCESS)
     Status: "Server shutdown complete"
        CPU: 5min 24.991s

Aug 20 06:44:41 mysql-vm systemd[1]: Starting MySQL Community Server...
Aug 20 06:44:42 mysql-vm systemd[1]: Started MySQL Community Server.
Sep 01 12:34:01 mysql-vm systemd[1]: mysql.service: Deactivated successfully.
Sep 01 12:34:01 mysql-vm systemd[1]: mysql.service: Consumed 5min 24.991s CPU time.
ubuntu@mysql-vm:~$ sudo systemctl start mysql
ubuntu@mysql-vm:~$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2023-09-01 12:35:07 CST; 8s ago
    Process: 22360 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 22368 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 1101)
     Memory: 395.8M
        CPU: 1.122s
     CGroup: /system.slice/mysql.service
             └─22368 /usr/sbin/mysqld

Sep 01 12:35:06 mysql-vm systemd[1]: Starting MySQL Community Server...
Sep 01 12:35:07 mysql-vm systemd[1]: Started MySQL Community Server.
ubuntu@mysql-vm:~$ mysql -u root -prootroot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.34-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show session variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 152   |
+-----------------+-------+
1 row in set (0.01 sec)

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 152   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

持久化全局系统变量（仅修改mysqld-auto.cnf文件）

通过SET PERSIST_ONLY命令可以将全局系统变量持久化到mysqld-auto.cnf文件中，而不设置全局变量的运行时值。

SET PERSIST_ONLY 系统变量名 = 值;
或者
SET @@PERSIST_ONLY.系统变量名 = 值;
1
2
3

例：

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 152   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>  SET PERSIST_ONLY  max_connections = 151;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 152   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show session variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 152   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> exit
Bye
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

我们进行了变量修改，但是当前运行值没有发生改变。
查看mysqld-auto.cnf文件，我们可以看到值发生了改变。

ubuntu@mysql-vm:~$ sudo cat /var/lib/mysql/mysqld-auto.cnf
{"Version": 2, "mysql_dynamic_parse_early_variables": {"max_connections": {"Value": "151", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1693543409889131}}}}
ubuntu@mysql-vm:~$

1
2
3
4

重启MySQL，可以看到修改的系统变量在重启后依然有效。

ubuntu@mysql-vm:~$ sudo systemctl stop mysql
ubuntu@mysql-vm:~$ sudo systemctl status mysql
○ mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Fri 2023-09-01 12:45:56 CST; 9s ago
    Process: 22360 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
    Process: 22368 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Main PID: 22368 (code=exited, status=0/SUCCESS)
     Status: "Server shutdown complete"
        CPU: 4.860s

Sep 01 12:35:06 mysql-vm systemd[1]: Starting MySQL Community Server...
Sep 01 12:35:07 mysql-vm systemd[1]: Started MySQL Community Server.
Sep 01 12:45:55 mysql-vm systemd[1]: Stopping MySQL Community Server...
Sep 01 12:45:56 mysql-vm systemd[1]: mysql.service: Deactivated successfully.
Sep 01 12:45:56 mysql-vm systemd[1]: Stopped MySQL Community Server.
Sep 01 12:45:56 mysql-vm systemd[1]: mysql.service: Consumed 4.860s CPU time.
ubuntu@mysql-vm:~$ sudo systemctl start mysql
ubuntu@mysql-vm:~$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2023-09-01 12:46:19 CST; 2s ago
    Process: 22441 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 22449 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 1101)
     Memory: 363.8M
        CPU: 980ms
     CGroup: /system.slice/mysql.service
             └─22449 /usr/sbin/mysqld

Sep 01 12:46:18 mysql-vm systemd[1]: Starting MySQL Community Server...
Sep 01 12:46:19 mysql-vm systemd[1]: Started MySQL Community Server.
ubuntu@mysql-vm:~$ mysql -u root -prootroot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.34-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  show session variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

重启后，设置的值生效。

参考：
5.1.9.3 Persisted System Variables
https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html

例题

例题1

Choose the best answer.
Which statement is true about the my.ini file on a Windows platform while MySQL server is running?

A) MySQL server does not use the my.ini option file for server configuration options.
B) The option file is read by the MySQL server service only at start up.
C) Editing the file will immediately change the running server configuration.
D) Using SET PERSIST will update the my.ini file.
1
2
3
4
5
6
7

例题解析：
Answer:B

MySQL服务器的系统变量相关的文件主要有如下文件。

在Windows操作系统中：
	my.ini ：MySQL服务器的主要配置文件。它包含了MySQL服务器的各种配置选项，包括系统变量的设置，数据库启动的时候会读这个文件。
	mysqld-auto.cnf ：自动生成的文件，用于持久化系统变量的设置。SET PERSIST 和SET PERSIST_ONLY会更新这个文件。

在Linux操作系统中：
	my.cnf ：MySQL服务器的主要配置文件，类似于Windows中的my.ini文件。
	mysqld-auto.cnf：与Windows中的mysqld-auto.cnf文件相同，也是一个自动生成的文件，用于持久化系统变量的设置。SET PERSIST 和SET PERSIST_ONLY会更新这个文件。
	
1
2
3
4
5
6
7
8
9
10

参考

https://www.percona.com/blog/using-mysql-8-persisted-system-variables/
https://dev.mysql.com/doc/refman/8.0/en/set-variable.html

https://segmentfault.com/q/1010000039941468?utm_source=sf-similar-question
https://wenku.baidu.com/view/8e6b63fca2c7aa00b52acfc789eb172ded639919.html?fr=sogou&wkts=1694952489836