MySQL 系统变量和 SET 命令

MySQL 的系统变量配置了 MySQL Server 如何运行，SET 语句用于修改系统变量。MySQL SET 命令有多种选项，用于指定如何以及何时修改系统变量。重要的是要了解这些修改如何反映在当前会话（连接）、以后的会话以及数据库服务器重启后。

 

用户可以使用 SET 修改的变量由其权限级别决定。系统变量有不同的作用域：global、session

 

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
}

 

对于系统变量，要知道以下一些内容：

· 有些系统变量是动态的，会立即改变 MySQL Server 的行为

· 有些系统变量不修改 MySQL Server 运行时的行为，需要重启

· 有些系统变量可以修改 MySQL Server 运行时的行为，但是 dba 可以选择在重启后再应用

· 有些系统变量修改 MySQL Server 运行时的行为，但是重启后不会被持久化

· 有些系统变量修改只能应用于新建的连接，对于已有的连接不生效

 

考虑到上述可能性，为了更好地理解各种情况，接下来将通过示例回顾实际工作中的情况。

 

每个系统变量都包含各种属性。与此相关的 2 个属性是：适用范围(global、session、或同时支持global和session)、是否支持动态修改。

 

如果修改了全局系统变量，且该值对会话（连接）有影响，则该值将在会话初始化时使用。 因此，在设置全局值之前的任何现有连接都不会 "继承" 该值，只有新会话才会修改该设置。此外，根据 SET 命令，当重新启动时，对全局值的修改可能会被持久化，也可能不会被持久化。

 

以max_connections 为例：

 

用例1： set global

假设你想将 max_connections从默认值151修改成1000。如果执行以下命令：

MySQL> SET GLOBAL max_connections = 1000;

此前所有已存在的会话的 max_connections 设置是151；所有新建的连接的max_connections是1000；如果 mysql server 发生重启，重启后 max_connections还是151。

 

用例2： set persist

如果要修改新会话的 max_connections，并在重启后将所有会话设置为 1000，则需要将修改持久化。

MySQL> SET PERSIST max_connections = 1000;

 

用例3：set persist_only

还有另一个选择。你可能不想更改当前正在运行的服务器的值，在这种情况下，可以将值保留为 151，但在重新启动时，希望将值改为 1000。在这种情况下，您可以执行

MySQL> SET PERSIST_ONLY max_connections = 1000；

PERSIST 和 PERSIST ONLY 语句的结果都是写入新值，在本例中就是将 max_connections=1000 写入 mysqld-auto.cnf 文件。

 

用例4：reset/set成默认值

如果想设置成默认值，有两个选择：

1.重新设置并持久化，以下将mysqld-auto.cnf中值设置成默认值

MySQL> SET PERSIST max_connections = DEFAULT;

2.移除mysqld-auto.cnf中的设置同时设置成默认值

MySQL> RESET PERSIST max_connections;

 

如果想在会话级别修改 max_connections呢？

对于全局的变量，使用 set session 命令修改会报错。

MySQL> SET SESSION max_connections=1000;
ERROR: 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL

SESSION 变量只在设置它们的当前会话中有效。

 

动态变量和非动态变量的区别

如果变量是动态的，通过set 设置后会立即应用和将变化反应到mysql server；如果和会话相关的，只会应用到新的会话。可以被持久化，也可以不持久化。

如果是非动态的，只可以使用set persist_only进行修改，生效需要重启mysql server。

 

了解自己的变量设置

show variables 命令可以提供全局变量和会话变量的活跃值：

show variables like 'max_connections';
show global variables like 'max_connections';
show session  variables like 'max_connections';

 

此外，还可以通过performance_schema中的表进行查看：

#全局变量
select * from performance_schema.global_variables;

#会话变量
select * from performance_schema.session_variables;

#与线程相关的变量，根据线程id进行区分
select * from performance_schema.variables_by_thread;

#持久的变量，设置在mysqld-auto.cnf中的值
select * from performance_schema.persisted_variables;

#变量信息：变量设置的来源，值的范围，设置时间等
select * from performance_schema.variables_info;