Detailed explanation of global parameter persistence in MySQL 8 new features

Preface

Since the release of the first official version of MySQL 8.0.11 in 2018, the MySQL version has been updated to 8.0.26. Compared with the stable version 5.7, the performance improvement of 8.0 is unquestionable!

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

This article mainly discusses the new features of MySQL 8.0: global parameter persistence

Global parameter persistence

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

Corresponding Worklog [WL#8688]: dev.mysql.com/worklog/tas…

To enable this feature, use the special syntax SET PERSIST to set any dynamically modifiable global variables!

SET PERSIST

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

SET PERSIST_ONLY

The statement does not modify the value of the variable in memory, but only writes the modified value to 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-auto.cnf file in json format in the data directory. The format is as follows. When my.cnf and mysqld-auto.cnf exist at the same time, the latter has a 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 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 parameter to its initial default value, just 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 persistent parameters, just 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 delete the mysqld-auto.cnf file and restart MySQL!

Final Thoughts

Main code:

Commit f2bc0f89b7f94cc8fe963d08157413a01d14d994

Main entry function (8.0.0):

Most interface functions are defined in the sql/persisted_variable.cc file:
Load the contents of mysqld-auto.cnf at startup: Persisted_variables_cache::load_persist_file(); Parse the validity of json and store it in memory to set the configuration read from the file: Persisted_variables_cache::set_persist_options

When the SET PERSIST command is run, Persisted_variables_cache::set_variable is called to update the value stored in memory and write it to the mysqld-auto.cnf file: Persisted_variables_cache::flush_to_file

Summarize

This is the end of this article about the new feature of MySQL 8: global parameter persistence. For more information about MySQL 8 global parameter persistence, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

Reference Documents:

• mysqlserverteam.com/mysql-8-0-p…
• dev.mysql.com/worklog/tas…
• bugs.mysql.com/bug.php?id=…