MySQL 8.0: Persisting configuration variables

Starting with MySQL 8.0 we are introducing a new feature which will allow configuration variables to be persisted. This means that for a number of configuration settings, you will no longer need to manually edit a configuration file and can instead make modifications directly via an SQL connection.


MySQL has always offered the ability to edit settings via configuration files (for example: /etc/my.cnf), with changes taking effect the next time that the MySQL server restarts. Over time as compute resources have become more powerful, database caches have also become larger. This means that restarting a server has only become less desirable; as the time to come back online at full health increases.

In the MySQL team; we have addressed this problem from a number of angles:

  • In MySQL 5.6 we introduced the ability to Save and restore the buffer pool state, in order to reload caches faster. In MySQL 5.7 this feature is now enabled by default.
  • We have made it possible to change more configuration variables without restarting a MySQL server.

If a configuration variable is able to be changed without restarting the MySQL Server it is said to be DYNAMIC. For example, the setting innodb_buffer_pool_size was made dynamic in MySQL 5.7 and can be changed as follows:

Upon the next restart, the value of the innodb_buffer_pool_size will reset to the previous value as it was specified in the configuration file(s). That is to say that the value does not persist, and that a DBA wishing to change a setting without a restart should change it in two places (in the running server and in a configuration file). Manually editing a configuration file and waiting potentially months between restarts can introduce risk that there was a typo or error in the file which prevents MySQL from starting.

As a further usability issue, for some deployments of MySQL the DBA may not be granted access to the local filesystem to edit configuration files. It is also difficult for automated tools to modify and persist variables in a standard way (e.g. a load balancing script which flips offline_mode, read_only depending on current topology state) that will work for all MySQL users.

New features introduced in 8.0


In Worklog #8688 we introduced the ability to set a global variable and have the change persist through restarts:

With this new feature, a MySQL server can be configured and managed over a SQL connection thus removing manual file operations (on configuration files) to be done by DBAs. This feature addresses the usability issues described above, and allows MySQL to be more easily deployed and configured on cloud platforms.

Any GLOBAL DYNAMIC configuration variable can be modified with the SET PERSIST statement. Under the hood the settings will be persisted to a file named mysqld-auto.cnf which will be created in the data directory. This file will be read during server startup just like any other configuration file, and all variables present in this file will be applied as the highest priority. That means the file mysqld-auto.cnf will be the last file to be applied on server startup (even after command-line options) and takes precedence if a specific setting has been specified in more than one location.

The file mysqld-auto.cnf is created the first time a SET PERSIST statement is executed. Further SET PERSIST statement executions will append the contents to this file. This file is in JSON format and can be parsed using json parser. Here is an example showing the feature working with a server restart:

A persisted variable can be forgotten (aka reset) by explicitly setting it to the value DEFAULT. This will remove it from mysqld-auto.cnf and the previous precedence of configuration files will apply again. As a demonstration:

variables_info Performance Schema Table

There is a potential debugging issue present from having multiple locations to edit a configuration variable (/etc/my.cnf and the mysqld-auto.cnf file). While this is not actually a new problem (it’s always been possible to specify a setting twice in two different configuration files), persistent configuration doesn’t make it any easier.

To address this problem, Worklog #8688 also introduces a Performance Schema table to show the source of where a configuration variable has been set as well as providing the minimum and maximum values. In the below output we can see that variable source for max_connections is PERSISTED which means variable is set from mysqld-auto.cnf file:

Below query reports all the variables which are set from command line:

If any variable is read from global configuration files like C:\my.ini or C:\Windows\my.ini then we can check for the same from variables_info table using the below query:

If a variable is dynamically changed in any session then this is also reflected in this new table. Below example illustrates the same:

A new read only system variable named persisted-globals-load is introduced which will help DBA to enable or disable reading of this new configuration file. By default this variable is set to ON. The below output shows how server behaves when this variable is ON or OFF:

To know all the variables which are set from command line and any of the configuration files run below query:


We look forward to hearing from MySQL users as you begin trying out this new feature. If you encounter any problems with this new feature – or you have ideas for enhancements – please let us know here in the comments, open a bug report / feature request at

Thank you for using MySQL!

4 thoughts on “MySQL 8.0: Persisting configuration variables

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.