新特性解读 | MySQL 8.0 轻松改配置，云上友好

于由陈俊聪发布

背景

MySQL 5.7 及之前版本下修改配置，如果能动态修改的，可以用 set global 语法，不能动态修改的，只能修改 /etc/my.cnf 配置文件，之后重启生效。如果需要持久化动态修改的参数，也只能同时修改 /etc/my.cnf 配置文件。

这个对云上环境不友好，毕竟云数据库上，大家无法直接远程底层虚拟机修改配置文件。针对如何持久化参数配置，在 MySQL 8.0，有一个新特性，可以实现轻松修改配置文件，那就是 set persist 和 set persist_only 语法。前者用于修改并持久化动态参数，后者用于持久化静态参数。

修改并持久化动态参数一例
  1. mysql> show variables like '%innodb_buffer_pool_size%';
  2. +-------------------------+-----------+
  3. | Variable_name           | Value     |
  4. +-------------------------+-----------+
  5. | innodb_buffer_pool_size | 134217728 |
  6. +-------------------------+-----------+
  7. 1 row in set (0.01 sec)

  9. mysql> set persist innodb_buffer_pool_size=134217728*2;
  10. Query OK, 0 rows affected (0.00 sec)

  12. mysql> show variables like '%innodb_buffer_pool_size%';
  13. +-------------------------+-----------+
  14. | Variable_name           | Value     |
  15. +-------------------------+-----------+
  16. | innodb_buffer_pool_size | 268435456 |
  17. +-------------------------+-----------+
  18. 1 row in set (0.00 sec)
在数据目录里，会生成一个持久化参数的 json 格式文件，内容如下：
  1. [root@pxc1 data]# cat mysqld-auto.cnf |jq
  2. {
  3.   "Version": 1,
  4.   "mysql_server": {
  5.     "innodb_buffer_pool_size": {
  6.       "Value": "268435456",
  7.       "Metadata": {
  8.         "Timestamp": 1570678719890919,
  9.         "User": "root",
  10.         "Host": "localhost"
  11.       }
  12.     }
  13. }
点评：居然有时间戳，还有谁修改了参数！
持久化静态参数一例
  1. # 我们先模拟没有权限
  2. mysql> revoke SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN on *.* from 'root'@'localhost';
  3. Query OK, 0 rows affected, 1 warning (0.01 sec)

  5. mysql> show variables like 'innodb_log_file_size';
  6. +----------------------+----------+
  7. | Variable_name        | Value    |
  8. +----------------------+----------+
  9. | innodb_log_file_size | 50331648 |
  10. +----------------------+----------+
  11. 1 row in set (0.01 sec)

  13. mysql> set persist innodb_log_file_size=50331648*2;
  14. ERROR 1238 (HY000): Variable 'innodb_log_file_size' is a read only variable

  16. # 因缺少SYSTEM_VARIABLES_ADMIN 和 PERSIST_RO_VARIABLES_ADMIN 权限而报错
  17. mysql> set persist_only innodb_log_file_size=50331648*2;
  18. ERROR 3630 (42000): Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation

  20. # 增加授权
  21. mysql> grant SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN on *.* to 'root'@'localhost';
  22. Query OK, 0 rows affected, 1 warning (0.01 sec)

  24. # 修改成功
  25. mysql> set persist_only innodb_log_file_size=50331648*2;
  26. Query OK, 0 rows affected (0.00 sec)

  28. # 重启，MySQL8.0支持在数据库里重启操作，云友好！
  29. mysql> restart;
  30. Query OK, 0 rows affected (0.00 sec)
mysql> restart；
ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).

注意：要求 mysqld 运行在 mysqld_safe 下。

清空 persist 持久化参数

  1. mysql> RESET PERSIST;
  2. Query OK, 0 rows affected (0.00 sec)

人手删除此文件或人手清空此文件内容，也可以达到相同效果。当然了，云数据库上只能用 RESET PERSIST 了。

相关 performance_schema 表

1. 查看数据库启动后载入的 PERSIST 相关参数

在重启数据库后，我们可以通过表 performance_schema.variables_info 查看我们从哪些 mysqld-auto.cnf 载入了哪些参数。

  1. mysql> select * from performance_schema.variables_info where variable_source like 'PERSISTED'\G
  2. *************************** 1. row ***************************
  3.   VARIABLE_NAME: innodb_buffer_pool_size
  4. VARIABLE_SOURCE: PERSISTED
  5.   VARIABLE_PATH: /data/mysql/mysql3308/data/mysqld-auto.cnf
  6.       MIN_VALUE: 5242880
  7.       MAX_VALUE: 9223372036854775807
  8.        SET_TIME: 2019-10-10 11:38:39.890919
  9.        SET_USER: root
  10.        SET_HOST: localhost
  11. *************************** 2. row ***************************
  12.   VARIABLE_NAME: innodb_log_file_size
  13. VARIABLE_SOURCE: PERSISTED
  14.   VARIABLE_PATH: /data/mysql/mysql3308/data/mysqld-auto.cnf
  15.       MIN_VALUE: 4194304
  16.       MAX_VALUE: 18446744073709551615
  17.        SET_TIME: 2019-10-10 11:21:12.623177
  18.        SET_USER: root
  19.        SET_HOST: localhost
  20. 2 rows in set (0.02 sec)

2. 查看变量都来自于哪里

  1. mysql> SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE
  2.     -> FROM performance_schema.variables_info t1
  3.     -> JOIN performance_schema.global_variables t2
  4.     -> ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
  5.     -> WHERE t1.VARIABLE_SOURCE != 'COMPILED';
  6. +--------------------------------------+-------------------------------------+-----------------+
  7. | VARIABLE_NAME                        | VARIABLE_VALUE                      | VARIABLE_SOURCE |
  8. +--------------------------------------+-------------------------------------+-----------------+
  9. | auto_increment_increment             | 1                                   | EXPLICIT        |
  10. | auto_increment_offset                | 1                                   | EXPLICIT        |
  11. ...
  12. | binlog_format                        | ROW                                 | DYNAMIC         |
  13. | binlog_rows_query_log_events         | ON                                  | EXPLICIT        |
  14. | foreign_key_checks                   | ON                                  | DYNAMIC         |
  15. | gtid_executed_compression_period     | 1000                                | EXPLICIT        |
  16. | innodb_buffer_pool_size              | 268435456                           | PERSISTED       |
  17. | innodb_change_buffer_max_size        | 25                                  | EXPLICIT        |
  18. | innodb_log_file_size                 | 100663296                           | PERSISTED       |
  19. | innodb_log_files_in_group            | 2                                   | EXPLICIT        |
  20. | pid_file                             | /data/mysql/mysql3308/data/pxc1.pid | COMMAND_LINE    |
  21. | port                                 | 3308                                | EXPLICIT        |
  22. +--------------------------------------+-------------------------------------+-----------------+
  23. 73 rows in set (0.01 sec)
EXPLICIT 来自于参数配置文件 my.cnf

COMMAND_LINE 来自于启动 mysqld 时额外的 command_line

PERSISTED 来自于数据目录下 mysqld-auto.cnf

DYNAMIC 来自于 SESSION 变量（包括 GLOBAL 变量的继承）

3. 查看动态修改的 session 变量（包括 GLOBAL 变量的继承）
  1. mysql> set session binlog_format='Mixed';
  2. Query OK, 0 rows affected (0.00 sec)

  4. mysql> set global read_only=1;
  5. Query OK, 0 rows affected (0.00 sec)

  7. mysql> SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE FROM performance_schema.variables_info t1 JOIN performance_schema.session_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME WHERE t1.VARIABLE_SOURCE = 'DYNAMIC';
  8. +--------------------+----------------+-----------------+
  9. | VARIABLE_NAME      | VARIABLE_VALUE | VARIABLE_SOURCE |
  10. +--------------------+----------------+-----------------+
  11. | binlog_format      | MIXED          | DYNAMIC         |
  12. | foreign_key_checks | ON             | DYNAMIC         |
  13. | read_only          | ON             | DYNAMIC         |
  14. +--------------------+----------------+-----------------+
  15. 2 rows in set (0.01 sec)

吐槽

MySQL 8.0 对于 all 这个权限没之前直观了。

  1. mysql> create user fander@'%' identified by 'password';
  2. Query OK, 0 rows affected (0.03 sec)

  4. mysql> grant all on *.* to fander@'%';
  5. Query OK, 0 rows affected (0.02 sec)

  7. mysql> show grants for fander@'%';
  8. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  9. | Grants for fander@%                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
  10. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `fander`@`%`                                                                                       |
  12. | GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `fander`@`%` |
  13. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  14. 2 rows in set (0.01 sec)

潜台词：我还得数一数权限是否足够。

作为对比，以下是 5.7 的显示方式：

  1. mysql> create user fander@'%' identified by 'password';
  2. Query OK, 0 rows affected (0.00 sec)

  4. mysql> grant all on *.* to fander@'%';
  5. Query OK, 0 rows affected (0.01 sec)

  7. mysql> show grants for fander@'%';
  8. +---------------------------------------------+
  9. | Grants for fander@%                         |
  10. +---------------------------------------------+
  11. | GRANT ALL PRIVILEGES ON *.* TO 'fander'@'%' |
  12. +---------------------------------------------+
  13. 1 row in set (0.00 sec)

参考链接：

https://lefred.be/content/mysql-8-0-changing-configuration-easily-and-cloud-friendly/ https://lefred.be/content/what-configuration-settings-did-i-change-on-my-mysql-server/

社区近期动态

No.1

10.26 DBLE 用户见面会 北京站

爱可生开源社区将在 2019 年 10 月 26 日迎来在北京的首场 DBLE 用户见面会，以线下互动分享的会议形式跟大家见面。

时间：10月26日 9:00 – 12:00 AM

地点：HomeCafe 上地店（北京市海淀区上地二街一号龙泉湖酒店对面）


重要提醒：

1. 同日下午还有 dbaplus 社群举办的沙龙：聚焦数据中台、数据架构与优化。

2. 爱可生开源社区会在每年10.24日开源一款高质量产品。本次在 dbaplus 沙龙会议上，爱可生的资深研发工程师闫阿龙，将为大家带来《金融分布式事务实践及txle概述》，并在现场开源。

No.2

「3306π」成都站 Meetup

知数堂将在 2019 年 10 月 26 日在成都举办线下会议，本次会议中邀请了五位数据库领域的资深研发/DBA进行主题分享。

时间：2019年10月26日 13:00-18:00

地点：成都市高新区天府三街198号腾讯成都大厦A座多功能厅

No.3

Mycat 问题免费诊断

诊断范围支持：

Mycat 的故障诊断、源码分析、性能优化

服务支持渠道：

  1. 技术交流群，进群后可提问

    QQ群（669663113）

  2. 社区通道，邮件&电话

    osc@actionsky.com

  3. 现场拜访，线下实地，1天免费拜访

关注“爱可生开源社区”公众号，回复关键字“Mycat”，获取活动详情。

No.4

社区技术内容征稿

征稿内容：

  1. 格式：.md/.doc/.txt

  2. 主题：MySQL、分布式中间件DBLE、数据传输组件DTLE相关技术内容

  3. 要求：原创且未发布过

  4. 奖励：作者署名；200元京东E卡+社区周边

投稿方式：

  1. 邮箱：osc@actionsky.com

  2. 格式：[投稿]姓名+文章标题

  3. 以附件形式发送，正文需注明姓名、手机号、微信号，以便小编及时联系

分类： MySQL 新特性技术文章
标签：

相关文章

MySQL 新特性

新特性解读 | MySQL 8.0 支持对单个数据库设置只读！

MySQL 8.0.22 支持对单个数据库设置只读，当一个实例中只需要 阅读更多…

MySQL 新特性

技术译文 | MySQL 8.0 引入 innodb_flush_method 等新参数的系统调用分析

本文我们将讨论如何在操作系统级别验证 innodb_flush_met 阅读更多…

技术文章

故障分析 | MySQL 无法修改主键？原来是因为这个参数

同事咨询了一个问题，TDSQL（for MySQL）中的某张表主键需要 阅读更多…