An in-depth discussion of global parameter persistence in MySQL 8.0

This article will take you through the new features in MySQL 8.0: global parameter persistence. I hope it will be helpful to you!

Since the first version of MySQL 8.0.11 was released in 2018, the MySQL version has been updated and iterated to 8.0.26, compared with the stable version 5.7, the performance improvement of 8.0 is beyond doubt!

As more and more enterprises begin to use MySQL 8.0 version, it is a challenge and an opportunity for DBAs!

This article mainly discusses the new features of MySQL 8.0 version: Global parameter persistence. [Related recommendations: mysql video tutorial]

Global parameter persistence

MySQL 8.0 version supports online modification of global parameters and persistence, by adding By using the PERSIST keyword, you can persist the modified parameters to a new configuration file (mysqld-auto.cnf). When you restart MySQL, you can get the latest configuration parameters from this configuration file!

Corresponding Worklog [WL#8688]: https://dev.mysql.com/worklog/task/?id=8688

Enable this feature and use the specific syntax SET PERSIST to set any global variable that can be dynamically modified!

• SET PERSIST

statement can modify the value of the variable in the memory and write the modified value into the data mysqld-auto.cnf in the directory.

• SET PERSIST_ONLY

statement will not modify the value of the variable in the memory, but will only write the modified value into mysqld-auto.cnf in the data directory.

Take the max_connections parameter as an example:

mysql> select * from performance_schema.persisted_variables;
Empty set (0.00 sec)

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

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

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

The system will generate a mysqld containing the json format in the data directory -auto.cnf file, formatted as follows, when my.cnf and mysqld-auto.cnf exist at the same time, the latter has higher priority.

{
    "Version": 1, 
    "mysql_server": {
        "max_connections": {
            "Value": "300", 
            "Metadata": {
                "Timestamp": 1632575065787609, 
                "User": "root", 
                "Host": "localhost"
            }
        }
    }
}

Note: Even if you modify the configuration value through SET PERSIST and there is no change, it will also be written to the mysqld-auto.cnf file. But you can restore the initial default value by setting it to the DEFAULT value!

If you want to restore the max_connections parameters to their initial default values, you only need to execute:

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

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

If you want to remove all global persistence parameters, you only need to execute :

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

mysql> select * from performance_schema.persisted_variables;
Empty set (0.00 sec)

Of course, you can also restart MySQL after deleting the mysqld-auto.cnf file!

Write at the end

Main code: Commit f2bc0f89b7f94cc8fe963d08157413a01d14d994

Main entry function (8.0.0):

接口函数大多定义在sql/persisted_variable.cc文件中：
启动时载入mysqld-auto.cnf的内容: Persisted_variables_cache::load_persist_file()； 通过json解析合法性，并存入内存
将文件中读取的配置进行设置： Persisted_variables_cache::set_persist_options
 
运行SET PERSIST命令时，调用Persisted_variables_cache::set_variable 更新内存中存储的值
写入mysqld-auto.cnf文件中： Persisted_variables_cache::flush_to_file

For more programming-related knowledge, please visit: Programming Video! !

The above is the detailed content of An in-depth discussion of global parameter persistence in MySQL 8.0. For more information, please follow other related articles on the PHP Chinese website!