Shulou(Shulou.com)06/03 Report--

This article mainly explains "how to make MySQL8 global parameters persistent". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to make MySQL8 global parameters persistent".

Catalogue

Global parameter persistence

Write at the end

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]: dev.mysql.com/worklog/tas...

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 sql/persisted_variable.cc files:

Load the contents of mysqld-auto.cnf at startup: Persisted_variables_cache::load_persist_file (); parse the validity through json and store it in memory

Set the configuration read in the file: Persisted_variables_cache::set_persist_options

When you run the SET PERSIST command, call Persisted_variables_cache::set_variable to update the value stored in memory

Write to mysqld-auto.cnf file: Persisted_variables_cache::flush_to_file

Thank you for your reading, the above is the content of "how to make MySQL8 global parameters persistent". After the study of this article, I believe you have a deeper understanding of how to make MySQL8 global parameters persistent, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.