MySQL实验之-持久化变量

简介

MySQL8 版本以前为了保证修改后的变量的值在 MySQL 运行时及 MySQL 重启后生效,需要分别修改内存和配置文件中变量的值

SET global/session 语句设置内存中全局/当前会话的变量的值, 修改 my.cnf 或 my.ini 中的值

此时你会发现, 配置⽂件需要 通过 ps -ef | grep mysql 查看进程启动命令中带⼊的选项 --defaults-file 以确定配置⽂件, 如果启动命令没有带⼊选项 --defaults-file, 那么默认配置⽂件为 /etc/my.cnf你会发现修改配置⽂件时, 需要确定配置⽂件, 以及修改配置⽂件, 这个步骤其实蛮繁琐的, ⽽且如果你⼀不小心写错了变量名或值, 可能会直接导致 MySQL 启动失败, 如果此时你有其他策略依赖 MySQL 的启动/重启,那么这个 MySQL 启动失败可能⼜会导致你策略判断不符合预期, 造成其他不好的后果, 这可能是就是亚马逊飓风初始的那只蝴蝶的翅膀

修改方式

MySQL8 以后, 对于持久化变量的值, 有了其他方式

● SET PERSIST 语句可以修改内存中变量的值, 并且将修改后的值写⼊数据⽬录中的 mysqld-auto.cnf 中

● SET PERSIST_ONLY 语句不会修改内存中变量的值, 只是将修改后的值写⼊数据⽬录中的 mysqld-auto.cnf 中

注意

PERSIST 和 PERSIST_ONLY 设置的都是 Global 级别, 不是 Session 级别

删除 mysqld-auto.cnf ⽂件 或设置 persisted_globals_load 为 off 都不会加载变量持久化的配置

通过语句 SET PERSIST / PERSIST_ONLY 去持久化变量的值到⽂件中, 相较于直接修改配置⽂件⽽⾔, 使⽤语句修改的⽅式更安全, 因为语句会校验变量和值的准确性, 不会产⽣因为修改错误导致的 MySQL 启动失败的问题; 因为 MySQL 启动期间, 最后才读取 mysqld-auto.cnf ⽂件, 也就是说 mysqld-auto.cnf ⽂件中的变量会覆盖配置⽂件中的变量的值, 所以推荐直接使⽤ SET PERSIST/ PERSIST_ONLY 修改变量的值, ⽽不是⼿⼯修改配置⽂件, 除⾮变量只能通过修改配置⽂件的⽅式修改

权限说明

● SET PERSIST 需要 system_variables_admin 权限

● SET PERSIST_ONLY 需要 system_variables_admin 和
persist_ro_variables_admin 权限

权限补充说明

MySQL 安装完成后, root 账号是不存在 system_variables_admin,
persist_ro_variables_admin 权限的

但是 root 账号存在 WITH GRANT OPTION 权限,所以 root 可以给自己/其他账号授予 system_variables_admin,
persist_ro_variables_admin 权限

GRANT system_variables_admin, persist_ro_variables_admin ON . TO user_name@‘%’

全局变量保存位置

1. 数据⽬录下的 mysqld-auto.cnf 持久化信息以 json 格式保存, metadata 记录了这次修改的用户及时间信息. 文件中还区分了变量的类型: mysql_server (动态变量)和
mysql_server_static_options (只读变量); 在数据库启动时, 会首先读取其他配置⽂件, 最后才读取 mysqld-auto.cnf 文件. 不建议⼿动修改 mysqld-auto.cnf ⽂件, 其可能导致数据库在启动过程中因解析错误⽽失败. 如果出现这种情况,可⼿动删除 mysqld-auto.cnf ⽂件或设置 persisted_globals_load 为 off 来避免该⽂件的加载

2. 表
performance_schema.persisted_variables

涉及参数

persisted_globals_load = ON – 8.0.0添加, 默认是 ON, 表示 MySQL 启动时, 读取数据⽬录下的 mysqld-auto.cnf ⽂件中的变量, 设置为 OFF 时, 则不会读取文件中的变量

涉及文件

数据目录下的 mysqld-auto.cnf ⽂件, 数据目录通过 select @@global.datadir; 获取. 打开该文件可以看到是以json保存的文本文件.

实验举例

show variables like "max_connections"; -- 查看 max_connections 的值
set persist max_connections=800; -- persist 不仅修改了内存中的值,还会持久化到 mysqld-auto.cnf ⽂件中
set @@persist.max_connections=800; -- 效果同上
set persist max_connections=default; -- 把 max_connections 设置为默认值
set persist_only max_connections=300; -- persist_only 只会将全局变量持久化到配置文件中,不会修改内存中的值
set @@persist_only.back_log=300; -- 效果同上
set persist_only back_log=1000, persist max_connections=2000; -- 同时设置多个变量的值
select * from performance_schema.persisted_variables; -- 查看已持久化的参数
reset persist back_log; -- 清除指定的已持久化的变量(mysql-auto.cnf 和 performance_schema.persisted_variables)
reset persist if exists back_log; -- 清除指定的变量, 如果变量没在持久化配置文件里, 那么就会告警, 而不是报错 (幂等操作)
reset persist; -- 清除所有的已持久化的变量
set persist net_read_timeout=50; -- persist 只会设置变量的 global 值, 不会设置变量的 session 值
set @@session.net_read_timeout=@@global.net_read_timeout; -- 设置当前 session 的变量值为全局变量的值