MySQL 8 new features persistent global variable modification

In versions prior to 8, modifications to global variables affected only their memory values and were not persisted into the configuration file. The database restarts and reverts to the pre-modified value. Starting with 8, you can persist modifications of global variables to the configuration file by using the set persist command.

Try to give a case

Mysql>Show variables like '%max_connections%';+------------------------+-------+|Variable_name|Value|+------------------------+-------+|Max_connections| 151  ||Mysqlx_max_connections|  -  |+------------------------+-------+2Rowsinch Set(0.00sec) MySQL> SetPersist Max_connections= $; Query OK,0Rows Affected (0.00sec) MySQL>Show variables like '%max_connections%';+------------------------+-------+|Variable_name|Value|+------------------------+-------+|Max_connections|  $  ||Mysqlx_max_connections|  -  |+------------------------+-------+2Rowsinch Set(0.00Sec

Changes to global variables are saved in two places, 1. Data directory under the Mysqld-auto.cnf file, note that the configuration file is not--defaults-file specified at startup.

[[email protected] ~] / var /lib/mysql/mysqld-1 , "Mysql_server": {"max_connections": {"Value": "$", "Metadata": {"Timestamp1525509217566258 ,"User":" Root "," Host ":" LocalHost "}}}}

Persistent information is saved in JSON format, where metadata records the user and time information for this modification. When the database starts, other configuration files are read first, and then the mysqld-auto.cnf file is read. It is not recommended to manually modify the file, which may cause the database to fail during startup due to parsing errors. If this is the case, 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 *  fromPerformance_schema.persisted_variables;+-----------------+----------------+|Variable_name|Variable_value|+-----------------+----------------+|Max_connections|  $            |+-----------------+----------------+1Rowinch Set(0.00Sec

The persistence of global variables in addition to the set PERSIST, there is a set persist_only, compared with the former, it only persisted global variables, and does not modify its memory value. At the same time, in terms of permissions, the former only needs system_variables_admin, the latter also requires Persist_ro_variables_admin permissions. For a variable that has persisted, it can be cleared by the reset persist command, noting that it simply empties the contents of MYSQLD-AUTO.CNF and Performance_schema.persisted_variables. There is no effect on the value of the variable that has been modified. In addition, you can persist global variables to default values in the following ways. Note that it is the default value, not the value before the modification.

MySQL>set persist max_connections=default;

This command, like set global Max_connections=default, sets the value of the variable to the default value, except that it persists the default value to the configuration file.

MySQL 8 new features persistent global variable modification