MySQL参数和状态查询
简介
MySQL的变量分为以下两种：
1）系统变量：配置MySQL服务器的运行环境，可以用show variables查看。
2）状态变量：监控MySQL服务器的运行状态，可以用show status查看。
Mysql参数文件中的参数可以分为2种类型：动态（dynamic）参数和静态参数（staitic）
（1）dynamic ：可以通过set进行实时修改
（2）static，只能在my.cnf里面修改，需要restart生效
动态参数意味着可以在mysql实例运行中进行修改，set global sort_buffer_size=32999999;修改后，别的connection重新进行连接就可以生效了。
生效范围分为：global和session。
静态的说明在整个mysql实例运行期间不得进行修改，就类似一个只读的read only
系统变量
系统变量按其作用域的不同可以分为以下两种：
1）全局（GLOBAL）级：对整个MySQL服务器有效
2）会话（SESSION或LOCAL）级：只影响当前会话
有些变量同时拥有以上两个级别，MySQL将在建立连接时用全局级变量初始化会话级变量，但一旦连接建立之后，全局级变量的改变不会影响到会话级变量。
查看系统变量的值
可以通过show vairables语句查看系统变量的值：
1 2 3 | mysql> show variables like 'log%'; mysql> show variables where Variable_name like 'log%' and value='ON'; |
注意：show variables优先显示会话级变量的值，如果这个值不存在，则显示全局级变量的值，当然你也可以加上GLOBAL或SESSION关键字区别：
1 2 3 | show global variables; show session/local variables; |
在写一些存储过程时，可能需要引用系统变量的值，可以使用如下方法：
1 2 3 | @@GLOBAL.var_name @@SESSION.var_name @@LOCAL.var_name |
如果在变量名前没有级别限定符，将优先显示会话级的值。
最后一种查看变量值的方法是从INFORMATION_SCHEMA数据库里的GLOBAL_VARIABLES和SESSION_VARIABLES表获得（在5.7以上需要设置：set global show_compatibility_56=on;）。
设置和修改系统变量的值
在MySQL服务器启动时，有以下两种方法设置系统变量的值：
1）命令行参数，如：mysqld --max_connections=200
2）选项文件（my.cnf）
在MySQL服务器启动后，如果需要修改系统变量的值，可以通过SET语句：
1 2 3 4 5 6 7 | SET GLOBAL var_name = value; SET @@GLOBAL.var_name = value; SET SESSION var_name = value; SET @@SESSION.var_name = value; |
如果在变量名前没有级别限定符，表示修改会话级变量。
注意：和启动时不一样的是，在运行时设置的变量不允许使用后缀字母'K'、'M'等，但可以用表达式来达到相同的效果，如：
1 | SET GLOBAL read_buffer_size = 2*1024*1024 |
MySQL 8 全局变量的修改持久化 set persist
https://dev.mysql.com/doc/refman/8.0/en/nonpersistible-system-variables.html
https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html
在8之前的版本中，对于全局变量的修改，其只会影响其内存值，而不会持久化到配置文件中。数据库重启，又会恢复成修改前的值。从8开始，可通过SET PERSIST命令将全局变量的修改持久化到配置文件中。
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 | mysql> show variables like '%max_connections%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | max_connections | 151 | | mysqlx_max_connections | 100 | +------------------------+-------+ 2 rows in set (0.03 sec) mysql> set persist max_connections=300; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%max_connections%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | max_connections | 300 | | mysqlx_max_connections | 100 | +------------------------+-------+ 2 rows in set (0.00 sec) cat /mysql/data/mysqld-auto.cnf { "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "300" , "Metadata" : { "Timestamp" : 1583910814250180 , "User" : "root" , "Host" : "localhost" } } } } mysql> select * from performance_schema.persisted_variables; +-----------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-----------------+----------------+ | max_connections | 300 | +-----------------+----------------+ 1 row in set (0.01 sec) |
在数据库启动时，会首先读取其它配置文件，最后才读取mysqld-auto.cnf文件（默认保存在数据文件目录中）。不建议手动修改该文件，因为其内容是json格式的，其有可能导致数据库在启动过程中因解析错误而失败。如果出现这种情况，可手动删除mysqld-auto.cnf文件或将persisted_globals_load变量设置为off来避免该文件的加载。
对于已经持久化了变量，可通过reset persist命令清除掉，注意，其只是清空mysqld-auto.cnf和performance_schema.persisted_variables中的内容，对于已经修改了的变量的值，不会产生任何影响。
通过表查询
这两个表提供和系统状态相关的信息
从 MySQL 5.7.6开始，这两个表开始被废弃，并将在后续的版本移除，只能在Performance_schema数据库中查询
这个特性通过show_compatibility_56 参数控制，同时控制系统变量和状态变量
MySQL 5.6的时候如下系统变量和状态变量可以使用show命令获取
1 2 | SHOW VARIABLES SHOW STATUS |
他们的来源于如下表
1 2 3 4 5 | INFORMATION_SCHEMA.GLOBAL_VARIABLES INFORMATION_SCHEMA.SESSION_VARIABLES INFORMATION_SCHEMA.GLOBAL_STATUS INFORMATION_SCHEMA.SESSION_STATUS |
MySQL 5.7.6开始后改成了从如下表获取
1 2 3 4 5 6 7 8 9 | performance_schema.global_variables performance_schema.session_variables performance_schema.variables_by_thread performance_schema.global_status performance_schema.session_status performance_schema.status_by_thread performance_schema.status_by_account performance_schema.status_by_host performance_schema.status_by_user |
从该版本开始MySQL引入了show_compatibility_56参数来控制show命令从什么地方获取值
当show_compatibility_56为ON时代表启用了MySQL 5.6的兼容性，这时和MySQL5.6一致
如果show_compatibility_56为OFF，则代表禁用兼容性，这时表的信息通过peformance_schema获取，这时直接查询information_schame会报错
该参数从5.7.8开始默认为OFF
我们可以直接使用show命令来查询状态变量，系统变量同理，这时不必关心show_compatibility_56参数的值
1 2 | show global status ; show session status ; |
或者使用performance_schema数据库
1 | select * from performance_schema.global_status where variable_name like '%Max_used_connections%'; |
参考链接
https://dev.mysql.com/doc/refman/5.7/en/status-table.html
https://dev.mysql.com/doc/refman/5.7/en/performance-schema-variable-table-migration.html
