MySQL的变量分类和配置语法

2021-12-20 19:28:08

系统变量类别

按照作用范围

会话变量
只对某个连接到数据库的会话有效
全局变量
对所有连接到数据库的会话都有效

按照设置方式

从命令行设置
只能在启动数据库时，在命令行中使用类似--variable-name=variables_value的形式来进行设置
从配置文件设置
可以将变量保存到配置文件中，每次数据库启动都会自动从配置文件中读取相应的配置

按照是否可以动态修改

支持动态修改
可以在数据库运行时通过SET语句直接修改变量的值,修改后新值立即生效。
在MySQL5.7中，如果修改后的值没有添加到配置文件中，重启后失效。
在MySQL8.0后，可以通过SET PERSIST语句，保持重启后变量值依旧有效
不支持动态修改
不能在数据库运行时进行修改，只能修改配置文件，重启数据库才会生效。
在MySQL8.0后，可以通过SET PERSIST_ONLY语句在数据库运行时进行修改，这个语句只将修改后的信息保存起来，不会影响正在运行的数据库，在数据库重启后生效。

变量语法

查看变量

# 查看所有会话级别变量
show variables;
# 查看所有全局级别变量
show global variables;

# 查看指定名称的变量 可以使用通配符
# 会话级别
show variables like ‘%name%‘
# 全局变量
show global variables like ‘%name%‘

# 查看指定名称的变量
# 默认显示会话级别变量 ，如果变量只是全局变量，显示全局变量值
select @@variable_name;
# 显示会话级别变量，如果变量只是全局变量，会提示报错信息
select @@session.variable_name;
# 显示全局变量值
select @@global.variable_name;

动态更改变量

# 更改全局变量
# 只对更改后的连接生效，对之前已经连接的会话不会起作用
set global variable_name = value
set @@variable_name = value

# 更改会话变量
# 只对当前连接生效，如果退出重连，会恢复成全局变量的值 
set variable_name = value
set @@session.variable_name = value
set @@variable_name = value

系统变量的持久化 (8.0+)

在MySQL5.7及以前版本中，虽然可以通过SET GLOBAL 或SET SESSION命令动态修改系统变量，但是修改后的变量不能持久化保存，数据库重启后就会失效。要想持久的保存变量，还需要登录服务器修改配置文件。
MySQL8.0提供了持久化保存系统变量的功能，通过SET PERSIST命令，可以在修改变量的同时将变量保存到数据目录的mysqld-auto.cnf文件中。数据库重启后会读取mysqld.auto.cnf文件中的变量信息。这样不登录服务器也可以进行数据库变量的修改。

启用系统变量持久化保存功能

启用功能不需要额外进行配置，但是要数据库正常读取持久化的配置文件，需要设置persisted_globals_load=ON

持久化系统变量的保存方式

文件位置

数据目录的mysql-auto.cnf中

文件格式

mysqld-auto.cnf使用JSON格式
示例：

{
  "Version": 1,
  "mysql_server": {
    "max_connections": {
      "Value": "152",
      "Metadata": {
        "Timestamp": 1.519921356e+15,
        "User": "root",
        "Host": "localhost"
      }
    },
    "transaction_isolation": {
      "Value": "READ-COMMITTED",
      "Metadata": {
        "Timestamp": 1.519921553e+15,
        "User": "root",
        "Host": "localhost"
      }
    },
    "mysql_server_static_options": {
      "innodb_api_enable_mdl": {
        "Value": "0",
        "Metadata": {
          "Timestamp": 1.519921573e+15,
          "User": "root",
          "Host": "localhost"
        }
      },
      "log_slave_updates": {
        "Value": "1",
        "Metadata": {
          "Timestamp": 1.519921582e+15,
          "User": "root",
          "Host": "localhost"
        }
      }
    }
  }
}

文件读取

如果禁用了persisted_globals_load变量，则数据库忽略mysqld-audo.cnf文件
数据库在所有其他配置文件之后读取mysqld-auto.cnf文件
使用SET PERSIST_ONLY设置的只读变量都保存在mysql_server_static_options部分，这部分变量都附加到命令行中，和其他命令行变量一起进行处理

文件修改

mysqld-auto.cnf文件的操作应仅使用SET PERSIST和RESET PERSIST语句执行，而不是手动修改。
手动修改可能导致数据库启动时出现解析所错，导致数据库无法启动，如果出现此问题，使用以下方式处理

禁用persisted_globals_load或使用--no-defaults选项的情况下启动数据库
或者删除mysqld-auto.cnf文件。删除文件会导致所有已保存的持久化系统变量丢失。

持久化系统变量语法

# 修改变量的同时持久化保存变量
SET PERSIST system_var_name=value;
# 不修改变量，只将变量保存到mysqld-auto.cnf文件中
SET PERSIST_ONLY system_var_name=value;

# 删除所有变量
RESET PERSIST;

# 删除指定变量
RESET PERSIST system_var_name;
# 如果mysqld-auto.cnf中不存在指定的变量，会发生错误

# 删除指定变量（如果变量不存在，不报错）
RESET PERSIST IF EXISTS system_var_name;

查看持久化系统变量信息

# 查看mysqld-auto.cnf文件内容
select * from performance_schema.persisted_variables

# 查看最近设置系统变量的用户和时间
select * from performance_schema.variables_info
# SET PERSIST会立即更新VARIABLE_SOURCE为DYNAMIC以及SET_TIME,SET_USER,SET_HOST （等同与SET GLOBAL）
# SET PERSIST ONLY不一会改变variables_info表。
# 数据库重启后，VARIABLES_SOURCE更新为PERSISTED，VARIABLES_PAHT更新为datadir/mysqld-auto.cnf