mysql 8.0 设置 持久的全局变量

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 8000  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.06 sec)

mysql>  set persist max_connections=500;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 500   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.00 sec)

[root@anedbtest01 ~]# cat  /u01/data/ane20000/mysqld-auto.cnf|/tmp/jq
{
  "Version": 1,
  "mysql_server": {
    "max_connections": {
      "Value": "500",
      "Metadata": {
        "Timestamp": 1558056501802792,
        "User": "root",
        "Host": "localhost"
      }
    }
  }
}

[root@anedbtest01 mysql]# cat /u01/mysql8015/etc/my_20000.cnf |grep max_connections 
max_connections = 8000

查看配置文件，仍然是之前的配置max_connections = 8000

mysql> restart
mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 500   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.01 sec)

set persist更改运行时变量值，将变量设置写入mysqld-auto.cnf数据目录中指定的选项文件 。数据库启动时，会首先读取其它配置文件my.cnf，最后才读取mysqld-auto.cnf文件。

不建议手动修改该文件，其有可能导致数据库在启动过程中因解析错误而失败。如果出现这种情况，可手动删除mysqld-auto.cnf文件或将persisted_globals_load变量设置为off来避免该文件的加载

对于已经持久化了变量，可通过reset persist命令取消持久
注意，其只是清空mysqld-auto.cnf和performance_schema.persisted_variables中的内容，对于已经修改了的变量的值，不会产生任何影响。

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

[root@anedbtest01 mysql]# cat  /u01/data/ane20000/mysqld-auto.cnf|/tmp/jq
{
  "Version": 1,
  "mysql_server": {}
}

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 500   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.00 sec)

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

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 8000  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.01 sec)

如果要改read_only 变量

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

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

mysql> set persist_only innodb_log_file_size=2073741824;
Query OK, 0 rows affected (0.00 sec)

[root@anedbtest01 mysql]#  cat  /u01/data/ane20000/mysqld-auto.cnf|/tmp/jq
{
  "Version": 1,
  "mysql_server": {
    "mysql_server_static_options": {
      "innodb_log_file_size": {
        "Value": "2073741824",
        "Metadata": {
          "Timestamp": 1558061748935792,
          "User": "root",
          "Host": "localhost"
        }
      }
    }
  }
}

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

但是对于read only 的参数，修改参数后需要重启才能生效
修改read only的变量需要额外的特权:
SYSTEM_VARIABLES_ADMIN
PERSIST_RO_VARIABLES_ADMIN

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

mysql> show variables like 'innodb_log_file_size';
+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| innodb_log_file_size | 2073034752 |
+----------------------+------------+

mysql> select * from performance_schema.variables_info where variable_source like 'PERSISTED'\G
*************************** 1. row ***************************
  VARIABLE_NAME: innodb_log_file_size
VARIABLE_SOURCE: PERSISTED
  VARIABLE_PATH: /u01/data/ane20000/mysqld-auto.cnf
      MIN_VALUE: 4194304
      MAX_VALUE: 18446744073709551615
       SET_TIME: 2019-05-17 11:02:34.412763
       SET_USER: root
       SET_HOST: localhost
1 row in set (0.01 sec)

1 row in set (0.01 sec)