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

来到golang学习网的大家，相信都是编程学习爱好者，希望在这里学习数据库相关编程知识。下面本篇文章就来带大家聊聊《新特性解读 | MySQL 8.0 轻松改配置，云上友好》，介绍一下MySQL、配置、数据库、配置文件，希望对大家的知识积累有所帮助，助力实战开发！

作者：陈俊聪

背景

MySQL 5.7 及之前版本下修改配置，如果能动态修改的，可以用 set global 语法，不能动态修改的，只能修改

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)

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

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+
1 row in set (0.00 sec)

在数据目录里，会生成一个持久化参数的 json 格式文件，内容如下：

[root@pxc1 data]# cat mysqld-auto.cnf |jq
{
  "Version": 1,
  "mysql_server": {
    "innodb_buffer_pool_size": {
      "Value": "268435456",
      "Metadata": {
        "Timestamp": 1570678719890919,
        "User": "root",
        "Host": "localhost"
      }
    }
}

点评: 居然有时间戳，还有谁修改了参数！

持久化静态参数一例

# 我们先模拟没有权限
mysql> revoke SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN on *.* from 'root'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
1 row in set (0.01 sec)

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

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

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

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

# 重启，MySQL8.0支持在数据库里重启操作，云友好！
mysql> restart;
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 持久化参数

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

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

mysql> select * from performance_schema.variables_info where variable_source like 'PERSISTED'\G
*************************** 1. row ***************************
  VARIABLE_NAME: innodb_buffer_pool_size
VARIABLE_SOURCE: PERSISTED
  VARIABLE_PATH: /data/mysql/mysql3308/data/mysqld-auto.cnf
      MIN_VALUE: 5242880
      MAX_VALUE: 9223372036854775807
       SET_TIME: 2019-10-10 11:38:39.890919
       SET_USER: root
       SET_HOST: localhost
*************************** 2. row ***************************
  VARIABLE_NAME: innodb_log_file_size
VARIABLE_SOURCE: PERSISTED
  VARIABLE_PATH: /data/mysql/mysql3308/data/mysqld-auto.cnf
      MIN_VALUE: 4194304
      MAX_VALUE: 18446744073709551615
       SET_TIME: 2019-10-10 11:21:12.623177
       SET_USER: root
       SET_HOST: localhost
2 rows in set (0.02 sec)

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

mysql> SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE
    -> FROM performance_schema.variables_info t1
    -> JOIN performance_schema.global_variables t2
    -> ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
    -> WHERE t1.VARIABLE_SOURCE != 'COMPILED';
+--------------------------------------+-------------------------------------+-----------------+
| VARIABLE_NAME                        | VARIABLE_VALUE                      | VARIABLE_SOURCE |
+--------------------------------------+-------------------------------------+-----------------+
| auto_increment_increment             | 1                                   | EXPLICIT        |
| auto_increment_offset                | 1                                   | EXPLICIT        |
...
| binlog_format                        | ROW                                 | DYNAMIC         |
| binlog_rows_query_log_events         | ON                                  | EXPLICIT        |
| foreign_key_checks                   | ON                                  | DYNAMIC         |
| gtid_executed_compression_period     | 1000                                | EXPLICIT        |
| innodb_buffer_pool_size              | 268435456                           | PERSISTED       |
| innodb_change_buffer_max_size        | 25                                  | EXPLICIT        |
| innodb_log_file_size                 | 100663296                           | PERSISTED       |
| innodb_log_files_in_group            | 2                                   | EXPLICIT        |
| pid_file                             | /data/mysql/mysql3308/data/pxc1.pid | COMMAND_LINE    |
| port                                 | 3308                                | EXPLICIT        |
+--------------------------------------+-------------------------------------+-----------------+
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 变量的继承)

mysql> set session binlog_format='Mixed';
Query OK, 0 rows affected (0.00 sec)

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

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';
+--------------------+----------------+-----------------+
| VARIABLE_NAME      | VARIABLE_VALUE | VARIABLE_SOURCE |
+--------------------+----------------+-----------------+
| binlog_format      | MIXED          | DYNAMIC         |
| foreign_key_checks | ON             | DYNAMIC         |
| read_only          | ON             | DYNAMIC         |
+--------------------+----------------+-----------------+
2 rows in set (0.01 sec)

吐槽

MySQL8.0 对于

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

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

mysql> show grants for fander@'%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for fander@%                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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`@`%`                                                                                       |
| 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`@`%` |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

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

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

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

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

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

参考:
https://lefred.be/content/mys...
https://lefred.be/content/wha...

以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于数据库的相关知识，也可关注golang学习网公众号。