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]
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!
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!