Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to understand global parameter persistence in MySQL 8.0". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Since the release of the first version of MySQL 8.0.11 in 2018, the MySQL version has been updated and iterated to 8.0.26, and there is no doubt about the performance improvement of 8.0 compared to the stable version 5.7!

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

This article mainly discusses the new feature of MySQL version 8.0: global parameter persistence. [related recommendation: mysql video tutorial]

Global parameter persistence

MySQL version 8.0 supports online modification of global parameters and persistence. By adding 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 a specific syntax SET PERSIST to set any global variable that can be dynamically modified!

SET PERSIST

Statement can modify the value of a variable in memory and write the modified value to mysqld-auto.cnf in the data record.

SET PERSIST_ONLY

Statement does not modify the value of a variable in memory, but writes the modified value to the mysqld-auto.cnf in the data record.

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)

A file containing mysqld-auto.cnf in json format is generated in the data directory, as shown below after formatting, and the latter has a higher priority when my.cnf and mysqld-auto.cnf exist at the same time.

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

Note: even if there is no change in the value of the configuration modified by SET PERSIST, it will be written to the mysqld-auto.cnf file. But you can restore the initial default value by setting it to DEFAULT!

If you want to restore the max_connections parameter to the initial default, simply 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, simply 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, after deleting the mysqld-auto.cnf file, restart MySQL!

Write at the end

Main code: Commit f2bc0f89b7f94cc8fe963d08157413a01d14d994

Main entry function (8.0.0):

Most of the interface functions are defined in the sql/persisted_variable.cc file: load mysqld-auto.cnf at startup: Persisted_variables_cache::load_persist_file () Parse the validity through json and store it in memory to set up the configuration read in the file: when Persisted_variables_cache::set_persist_options runs the SET PERSIST command, call Persisted_variables_cache::set_variable to update the values stored in memory to write to the mysqld-auto.cnf file: Persisted_variables_cache::flush_to_file "how to understand global parameter persistence in MySQL 8.0" is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.