Understanding MySQL Variable Persistence and the Use of SET PERSIST
This article explains how MySQL variables are stored in memory and configuration files, the challenges of modifying them before MySQL 8, and how the new SET PERSIST and SET PERSIST_ONLY statements simplify persistent variable management with proper permissions and examples.
MySQL variables can reside in memory (runtime) or in configuration files loaded at server start.
Before MySQL 8, changing a variable required updating both the in‑memory value with SET GLOBAL/SESSION and the configuration file manually, which was error‑prone.
MySQL 8 introduced SET PERSIST and SET PERSIST_ONLY to write variable values to mysqld-auto.cnf automatically, persisting them across restarts.
These statements affect only global variables; SET PERSIST_ONLY does not modify the in‑memory value.
Required privileges are SYSTEM_VARIABLES_ADMIN for SET PERSIST and both SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN for SET PERSIST_ONLY .
The persisted values are stored in JSON format in the data directory’s mysqld-auto.cnf and also reflected in performance_schema.persisted_variables . Modifying this file manually is discouraged.
Common commands include SET PERSIST max_connections=500; , SET PERSIST_ONLY max_connections=500; , RESET PERSIST; , and queries to view persisted variables.
Aikesheng Open Source Community
The Aikesheng Open Source Community provides stable, enterprise‑grade MySQL open‑source tools and services, releases a premium open‑source component each year (1024), and continuously operates and maintains them.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.