专栏首页爱可生开源社区新特性解读 | 说说 MySQL 8 对于持久化变量的一些修改

新特性解读 | 说说 MySQL 8 对于持久化变量的一些修改

作者:姚嵩

爱可生南区交付服务部经理,爱好音乐,动漫,电影,游戏,人文,美食,旅游,还有其他。

虽然都很菜,但毕竟是爱好

本文来源:原创投稿

*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


MySQL 变量存在的介质及意义:

MySQL 的变量存在于内存、以及配置文件中;

其中内存中的变量是在运行时生效,配置文件中的变量是在 mysqld 程序启动时加载到内存中;

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

SET global/session 语句设置内存中全局/当前会话的变量的值,

通过 Linux 命令⼿⼯修改配置⽂件中变量的值,

此时你会发现,你的配置⽂件需要 通过 ps -ef | grep mysql 查看进程启动命令中带⼊的选项 --defaults-file,以确定配置⽂件,

如果启动命令没有带⼊选项 --defaults-file,那么默认配置⽂件为 /etc/my.cnf ;

你会发现修改配置⽂件时,需要确定配置⽂件,以及修改配置⽂件,这个步骤其实蛮繁琐的,

⽽且如果你⼀不小心写错了变量名或值,可能会直接导致启动 MySQL 失败,

如果此时 你有其他策略依赖 MySQL 的启动/重启,那么这个 MySQL 启动失败可能⼜会导致你策略判断不符合预期,造成其他不好的后果,

这可能是就是亚马逊飓风初始的那只蝴蝶的翅膀。

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

  • 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

常用语句:

  • set persist max_connections=500 ; # persist 不仅修改了内存中的值,还会持久化到配置⽂件中
  • set @@persist.max_connections=500 ; # 效果同上
  • set persist max_connections=default ; # 可以设置为变量为默认值
  • set persist_only back_log=500 ; # persist_only 只会将全局变量持久化到配置文件中,不会修改内存中的值
  • set @@persist_only.back_log=500 ; # 效果同上
  • set persist_only back_log=1000 ,persist max_connections=2000 ; # 同时设置多个变量的值
  • select * from performances_chema.persisted_variables ; # 查看已持久化的参数
  • reset persist back_log ; # 清除指定的已持久化的变量
  • reset persist if exists back_log ; # 清除指定的变量,如果变量没在持久化配置文件里,那么就会告警,而不是报错(幂等操作)
  • reset persist ; # 清除所有的已持久化的变量

涉及参数:

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

涉及文件:

数据目录下的 mysqld-auto.cnf ⽂件,数据目录通过 select @@global.datadir;获取;

举例:

# 查看 max_connections 的值

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 200 |
+-----------------+-------+
1 row in set (0.00 sec)

# persist 不仅修改了内存中的值,还会持久化到 mysqld-auto.cnf ⽂件中

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 |
+-----------------+-------+
1 row in set (0.00 sec)

# 把 max_connections 设置为默认值

mysql> set persist max_connections=default ;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.01 sec)

# persist_only 只会将全局变量持久化到配置文件中,不会修改内存中的值

mysql> set persist_only max_connections=300 ;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)

# 查看已持久化的参数

mysql> select * from performance_schema.persisted_variables ;
+-----------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 300 |
+-----------------+----------------+
1 row in set (0.00 sec)

# 清除所有的已持久化的变量

mysql> reset persist ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from performance_schema.persisted_variables ;
Empty set (0.00 sec)
mysql> set session net_read_timeout=20;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.net_read_timeout,@@session.net_read_timeout ;
+---------------------------+----------------------------+
| @@global.net_read_timeout | @@session.net_read_timeout |
+---------------------------+----------------------------+
| 30 | 20 |
+---------------------------+----------------------------+
1 row in set (0.00 sec)

# persist 只会设置变量的 global 值,不会设置变量的 session 值

mysql> set persist net_read_timeout=50 ;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.net_read_timeout,@@session.net_read_timeout ;
+---------------------------+----------------------------+
| @@global.net_read_timeout | @@session.net_read_timeout |
+---------------------------+----------------------------+
| 50 | 20 |
+---------------------------+----------------------------+
1 row in set (0.00 sec)
mysql> select * from performance_schema.persisted_variables ;
+------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+------------------+----------------+
| net_read_timeout | 50 |
+------------------+----------------+
1 row in set (0.00 sec)

# 你可以设置当前 session 的变量值为全局变量的值

mysql> set @@session.net_read_timeout=@@global.net_read_timeout ;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.net_read_timeout,@@session.net_read_timeout ;
+---------------------------+----------------------------+
| @@global.net_read_timeout | @@session.net_read_timeout |
+---------------------------+----------------------------+
| 50 | 50 |
+---------------------------+----------------------------+
1 row in set (0.00 sec)

本文分享自微信公众号 - 爱可生开源社区(ActiontechOSS),作者:姚嵩

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-06-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 技术分享 | 使用备份恢复实例时存在的坑

    爱可生服务团队成员,负责处理客户在MySQL日常运维中遇到的问题;擅长处理备份相关的问题,对数据库相关技术有浓厚的兴趣,喜欢钻研各种问题。

    爱可生开源社区
  • 技术分享 | MariaDB 10.1.9 迁移到 MySQL 5.7.25

    爱可生 DBA 团队成员,负责项目日常问题处理及公司平台问题排查,对数据库有兴趣,对技术有想法。一入 IT 深似海,从此节操是路人。

    爱可生开源社区
  • 第06期:梳理 MySQL 字符集的相关概念

    用途:用来映射简单的单字节字符,比如大小写英文字母、阿拉伯数字、常用的标点符、运算符、控制字符等。

    爱可生开源社区
  • 站长之家百度关键词数据库的更新对SEO有什么影响

    百度PC端排名关键词数量直接从原来的基础上一天上涨了4-6倍,按照正常的SEO经验来看,这个数据上涨的可谓是非常恐怖了。不过并不是所有的网站都出现了关键词数量暴...

    高级优化师
  • 1467: C语言实验题――两个数最大

    描述:求两个整数中的最大值。 输入:两个整数 输出: 输出最大的值,格式为:max=最大值 样例输入: 1 2 样例输出: max=2 考点:i...

    bboysoul
  • 4步让你驱动Kubernetes【Containers】

    在本系列的第三篇文章中,我介绍了Kubernetes的基础知识:首先学习如何驱动,我强调您应该学会驱动Kubernetes,而不是构建它。我还解释了在Kuber...

    王欣壳
  • PPT多文件合并

    今天要给大家讲的是PPT多文件合并! PPT多文件合并相对于Excel多工作薄合并来讲要简单很多。 主要也是两种方法: PPT内置合并功能 使用OIIO插件 ...

    数据小磨坊
  • 利用协方差,Pearson相关系数和Spearman相关系数确定变量间的关系

    数据集中的变量之间可能存在复杂且未知的关系。重要的是发现和量化数据集的变量相关的程度。这些知识可以帮你更好地准备数据,以满足机器学习算法的预期,例如线性回归,其...

    AiTechYun
  • 运维之道:16 张图片带你 1 小时学会 Ansible

    今天分享一个讲解 Ansible 的 PPT,可以用做入门学习之用。内容非常全面,当然,能不能一小时学会还要看你认不认真咯~

    马哥linux运维
  • Python3 常见错误和异常处理

    即便 Python 程序的语法是正确的,在运行它的时候,也有可能发生错误。运行检测到的错误被称为异常。

    python鱼霸霸

扫码关注云+社区

领取腾讯云代金券