MySQL 系统变量查看与设置（System Variables Configuration）

MySQL中有大量的系统变量控制服务器的行为，大部分的系统变量是不需要我们调整的，保持默认即可。但为了获得更高的性能和稳定性，有时需要适当对部分变量进行调整，本文总结了MySQL中系统变量的查看与设置方法。

一、变量的类型

MySQL中根据变量的作用范围可以将其分为两种：

• 全局变量：影响整个数据库
• 会话变量：影响某个会话

大部分变量同时具有全局和会话两种作用范围，其中会话变量会在客户端连接到MySQL时会根据全局变量进行初始化。

而根据变量是否可以在数据库运行时动态修改，也可将变量分为两种：

• 静态变量：数据库运行时不能修改，需要重启生效
• 动态变量：可以数据库运行时修改，不需要重启

二、变量值的查看

系统变量有很多查询方式，以下为4种常用的查询变量值方法：

• 通过show命令查看
• 通过select命令查看
• 通过命令行工具mysqladmin查看
• 通过查询performance_schema视图查看

2.1 使用show命令查看变量

使用show [global | session] variables [like ‘variable_name’];可以查看变量的值。global和session关键字指定查看全局或会话变量，省略时默认查看会话变量。like ‘variable_name’ 子句查看指定变量的值，可以使用通配符%和_，省略like子句则会输出所有变量。

示例：使用show命令查看autocommit（自动提交）变量的值，省略global/session时，默认返回会话变量的值（查看全局变量global不能省略）：

show variables like 'autocommit';  -- 等于show session variables like 'autocommit';
show global variables like 'autocommit';

部分变量的范围只有全局，此时global/session关键字，无论用哪个关键字或省略，返回的都是全局变量值。例如max_connctions作用范围只有全局，下面的3个命令返回的都是全局的值：

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

2.2 使用select命令查看变量

使用select @@[global|session].variable_name; 可以查看指定变量的值，global/session省略时，默认查看会话变量。和show命令不同，select命令不能模糊匹配，只能查看某个具体变量的值。

示例：查看autocommit变量的值：

select @@autocommit;  -- 等于 select @@session.autocommit;
select @@global.autocommit;

2.3 使用命令行工具mysqladmin查看

使用MySQL自带的管理工具mysqladmin variables也可以查看系统变量，但这个命令只能输出全部的变量，通常配合grep过滤或重定向到文件后再查看。

mysqladmin variables > variables.txt 

2.4 通过performance_schema视图查看

MySQL的performance_schema提供了几张视图可以直接用SQL查询变量信息：

• global_variables 查询所有全局变量值
• session_variables 查询所有会话变量值（当前会话）
• variables_by_thread 查询每个会话的变量值（比session_variables多了一个thread_id）
• variables_info 查询变量最近一次的设置信息，包括变量来源，范围，修改人，修改时间等

示例：查看全局变量autocommit的值：

select * from performance_schema.global_variables where variable_name='autocommit';

示例：查看最近一次autocommit变量的设置：

select * from performance_schema.variables_info where variable_name='autocommit';

三、变量值的设置

MySQL变量的设置有3种方式：

• 通过参数文件设置
• 通过命令行启动项设置
• 通过set命令设置

MySQL启动时会根据参数文件初始化变量值，但MySQL的系统变量都有默认值，所以即使没有参数文件数据库也可以启动。这点和Oracle不同，Oracle如果没有初始化参数文件则无法启动。

3.1 通过参数文件设置

MySQL的参数文件是my.cnf，数据库在启动时会读取其[mysqld]和[server]模块下的内容并设置变量值。

可以通过下面的命令查看参数文件搜索路径，通常保留一个参数文件即可，后面的设置会覆盖前面的设置：

mysqld --verbose --help | grep my.cnf

将需要设置的参数放在my.cnf中的[mysqld]或[server]模块下，变量名中的"-“和”_"是通用的。对于布尔类型的变量，1和on代表启用，0和off代表关闭。对于数值类型的变量，可以用数字，表达式或者K, M, G作为单位来设置，例如设置max_allowed_packet为8M，下面的3种设置方式是等价的：

max_allowed_packet=8388608
max_allowed_packet=8*1024*1024
max_allowed_packet=8M

3.2 通过命令行启动项设置

在MySQL启动时用–variables（前面有双横杠）的方式也可以设置系统变量，通过命令行启动参数设置变量比my.cnf文件中的设置优先级更高，但缺点是不能持久化，重启时容易忘记。因此这种设置方式不太建议（把重要的配置放到参数文件中）。

示例：下面通过命令行选项启动数据库，变量名中"_“和”-"是通用的：

mysqld --user=mysql --port=3306 --max_allowed_packet=8M --lower-case-table-names=1 &

3.3 通过set命令设置

MySQL中set命令除了可以设置自定义变量，还可以在运行时修改系统变量的值。修改会话变量对当前会话立刻生效，修改全局变量只会影响后续新建会话，已存在的会话不受影响。

示例：全局变量设置，使用global或@@gloabl.限定全局变量，下面的2种设置方式是等价的：

set global max_connections=500;
set @@global.max_connections=500;

示例：会话变量设置使用session/@@session.，local/@@local.或省略，下面6种设置方式是等价的：

set session sql_mode='traditional';
set @@session.sql_mode='traditional';

set local sql_mode='traditional';
set @@local.sql_mode='traditional';

set sql_mode='traditional';
set @@sql_mode='traditional';

set命令还有个关键字default，可以将变量设置为初始值。会话变量会还原为全局变量的值，全局变量则设置为MySQL自己编译的默认值（不是启动时参数文件初始化的值）：

set autocommit=default;
set global server_id=default;

3.3.1 使用persist/persist_only关键字持久化变更

上面的set命令都是动态修改变量，当会话退出，或者数据库重启，设置效果就消失了。MySQL8.0针对set命令做了持久化功能，通过persist/persist_only关键字可以将变更保存到数据目录下的mysqld-auto.cnf文件中，如此即使数据库重启设置也不会消失。

使用persist/persist_only持久化修改变量有如下优点：

• 不需要修改my.cnf文件，意味着不需要登陆数据库服务器，通过远程会话即可持久化修改变量。
• 自动语法校验，出现语法或变量值错误时，设置不会生效，而通过my.cnf文件，启动报错时才能知道错误。

示例：使用persist关键字修改全局变量同时持久化到mysqld-auto.cnf文件中（当前实例和重启都生效），下面2种方式是等价的：

set persist max_connections=500;
set @@persist.max_connections=500;

示例：使用persist_only关键字持久化到mysqld-auto.cnf文件中，但不修改当前系统全局变量（重启生效），下面2种方式是等价的：

set persist_only  server_id=32;
set @@persist_only.server_id=32;

持久化设置的变量可以通过reset persist [[if exists] variable_name];命令取消，if exists可以在未设置变量时不报错：

reset persist max_connections;  -- 取消指定的变量设置
reset persist;    -- 取消所有persist/persist_only设置的变量

3.3.2 查询persist持久化变更信息

mysqld-auto.cnf中的持久化变量会在参数文件my.cnf之后加载，相当于启动后对立刻数据库进行一次set global，因此可能覆盖my.cnf中的设置。而是否加载mysqld-auto.cnf由变量persisted_globals_load控制，设置为off则启动时会忽略mysqld-auto.cnf文件：

show variables like 'persisted_globals_load';

如果想知道有哪些变量通过set persist/persist_only命令做了持久化变更，可以通过下面两种方式查询：

• 查看数据目录下的mysqld-auto.cnf文件内容
• 通过select语句查询performance_schema.persisted_variables视图

示例：直接查看mysqld-auto.cnf内容，信息是以JSON格式存储的（不建议手动修改文件内容）：

cat mysqld-auto.cnf

示例：通过performance_schema.persisted_variables查询：

select * from performance_schema.persisted_variables;

以上即是MySQL中系统变量的查询和修改方式，在修改变量前要熟悉变量的作用。在调整变量值之前最好通过mysqladmin variables命令将当前的服务器参数备份到文件中，如果调整后出现性能下降及时还原。