Using MySQL 8 Persisted System Variables

MySQL 8 Persisted System VariablesThis blog discusses new features about the persisted system variables and how we can use it to make variable changes persistent. The MySQL server maintains system variables that control its operations. The dynamic variables used prior to the MySQL 8 release are not persistent and are reset upon restart. These variables can be changed at runtime using the SET statement to affect the operation of the current server instance but we have to manually update my.cnf config file to make them persistent. In many cases, updating my.cnf from the server-side is not a convenient option, and leaving the variable just updated dynamically reverts on the subsequent restart without any history.

Persisted system variables are one of the useful features introduced in MySQL 8. The new functionality helps DBAs update the variables dynamically and register them without touching the configuration files from the server-side.

How to Persist the Global System Variables?

Like SET GLOBAL, SET PERSIST is the command that can be used for updating the system variables at runtime and make them persistent across restarts. When we use the PERSIST keyword, the variable changes are updated to the mysqld-auto.cnf option file in the data directory. The mysqld-auto.cnf is a JSON format file created only upon the first execution of the PERSIST or PERSIST_ONLY statement.

Let’s see how this feature works using an example updating the max connections.

The resulting mysqld-auto.cnf looks like this:

How to Persist the Read-Only System Variables?

When there is a need to change variables that are read-only, we need to use the PERSIST_ONLY keyword. This clause updates the change in the mysqld-auto.cnf file but does not apply in the MySQL, persisting upon the next MySQL restart. This makes PERSIST_ONLY suitable for configuring read-only system variables that can be set only at server startup.

How to Clear the Persisted System Variable Settings?

We can use the RESET PERSIST command to remove persisted settings from mysqld-auto.cnf. Be careful when running the command without a specific variable name as it will remove all the settings from the configuration file. In effect, it removes the persisted settings from mysqld-auto.cnf but not from MySQL.

See the below examples.

If you may want to clear a specific variable instead of clearing all settings from the configuration file, the following example shows us how to do this. If we try to remove a variable that does not exist in the mysqld-auto.cnf it results in an error as shown below and we can use the IF EXISTS clause to suppress the error.

Is There a Way to Disable Persistence?

Yes, the persisted_globals_load parameter is used to enable or disable the persisted system variables. When disabled, the server startup sequence ignores the mysqld-auto.cnf file. Manually changing to the mysqld-auto.cnf file may result in a parse error at server startup. In this case, the server reports an error and exits. If this issue occurs, then you have to start the server with the persisted_globals_load system variable disabled or with the –no-defaults option as given in the example below.

What Grants Are Required?

Considering security, the right permissions for the right user is definitely a best practice. The SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN are the required privileges for a user to use the SET PERSIST_ONLY to persist global system variables to the mysqld-auto.cnf.

The user also needs to have the SHUTDOWN privilege to be able to use the RESTART command. It provides a way to restart MySQL from the client session without requiring command-line access on the server host.

How to Monitor the Variables

To list the variables that have been updated using the PERSIST option, we can query the performance_schema.persisted_variables table joining with a couple of other tables as shown below. This is a simple example of how to monitor the variables from the MySQL side and based on your needs, you can modify the query.