New feature of MySQL8: Modification method of persistent global variables

Preface

In versions prior to 8, modifications to global variables only affected their memory values and were not persisted to configuration files. When the database is restarted, it will be restored to the value before modification. From 8 onwards, changes to global variables can be persisted to configuration files with the SET PERSIST command.

Give an example

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 files in the data directory,

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

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

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 the mysqld-auto. cnf file is read last. Manual modification of this file is not recommended, as it may cause the database to fail during startup due to parsing errors. If this happens, you can either 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)

Persistence of global variables in addition to SET PERSIST, there is also SET PERSIST_ONLY, which only persists global variables without modifying their memory values. 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, noting that it only empties mysqld-auto. cnf and performance_schema.persisted_variables Has no effect on the value of a 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 modification.

mysql> set persist max_connections=default;

This order is the same as " set global max_connections=default "Similarly, the value of the variable is set to the default value, except that the former also persists the default value to the configuration file.

Summarize