How to Persist Global System Variables in MySQL 8

Before MySQL 8, dynamic system variables were not permanent and would reset after a server restart; administrators had to modify the my.cnf file manually to make changes persistent.

MySQL 8 introduced persisted system variables, allowing DBAs to update variables at runtime and have the changes automatically saved in a JSON file ( mysqld-auto.cnf ) without editing configuration files.

To persist a global variable, use SET PERSIST . For example, mysql> SET PERSIST max_connections = 1000; stores the new value in /var/lib/mysql/mysqld-auto.cnf , which then contains a JSON entry for max_connections with its value, timestamp, user, and host.

Read‑only variables can be persisted using SET PERSIST_ONLY . Attempting to change a read‑only variable with SET PERSIST fails, but mysql> set persist_only innodb_log_file_size=50331648*2; succeeds and records the change in the persisted file.

Persisted settings can be cleared with RESET PERSIST . Running it without arguments removes all entries from mysqld-auto.cnf , while specifying a variable name (e.g., mysql> RESET PERSIST max_connections; ) removes only that entry; the IF EXISTS clause suppresses errors for non‑existent variables.

Persistence can be disabled by setting the persisted_globals_load system variable to 0. After disabling, the server ignores mysqld-auto.cnf on startup, and the server must be started with the --no-defaults option if the file contains invalid entries.

To use these commands, a user needs the SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges, and the SHUTDOWN privilege to restart the server. Example: CREATE USER 'admin_persist'@'localhost' IDENTIFIED BY '*********'; GRANT SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN, SHUTDOWN ON *.* TO 'admin_persist'@'localhost';

Persisted variables can be monitored by querying performance_schema.persisted_variables together with performance_schema.variables_info and performance_schema.global_variables . The sample query lists variable names, current values, persisted values, set time, user, source, and path.