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

This article will explain in detail how to modify persistent global variables in MySQL8. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Mysql > show variables like'% max_connections%' +-+-+ | Variable_name | Value | +-+-+ | max_connections | 151 | | mysqlx_max_connections | 100 | +-+-- + rows In set (0.00 sec) mysql > set persist max_connections=200 Query OK, 0 rows affected (0.00 sec) mysql > show variables like'% max_connections%' +-+-+ | Variable_name | Value | +-+-+ | max_connections | 200 | | mysqlx_max_connections | 100 | +-+-+ rows in set (0.00 sec)

Changes to global variables are saved in two places

1. Mysqld-auto.cnf file under data directory

Note that it is not the configuration file specified by defaults-file at startup.

[root@slowtech ~] # cat / var/lib/mysql/mysqld-auto.cnf {"Version": 1, "mysql_server": {"max_connections": {"Value": "200"," Metadata ": {" Timestamp ": 1525509217566258," User ":" root "," Host ":" localhost "}

The persistence information is saved in json format, where Metadata records the user and time information of this modification.

When the database starts, the other configuration files are read first, and then the mysqld-auto.cnf file is read last. Manual modification of this file is not recommended, which may cause the database to fail due to parsing errors during startup. If this happens, you can manually delete the mysqld-auto.cnf file or set the persisted_globals_load variable to off to avoid loading the file.

2. Performance_schema.persisted_variables

Mysql > select * from performance_schema.persisted_variables +-+-+ | VARIABLE_NAME | VARIABLE_VALUE | +-+-+ | max_connections | 200 | +-+-+ row in set (0.00 sec)

Global variable persistence in addition to SET PERSIST, there is also SET PERSIST_ONLY, compared with the former, it only persists the global variable without changing its memory value. At the same time, in terms of permissions, the former only needs SYSTEM_VARIABLES_ADMIN, while the latter also needs PERSIST_RO_VARIABLES_ADMIN permissions.

Variables that have been persisted can be cleared by the RESET PERSIST command. Note that they just empty the contents of mysqld-auto.cnf and performance_schema.persisted_variables and have no effect on the values of the variables that have been modified.

In addition, global variables can be persisted to default values in the following ways. Note that it is the default value, not the pre-modified value.

Mysql > set persist max_connections=default; on how to modify persistent global variables in MySQL8 to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.