DB-유

Persisting Configuration with SET PERSIST in MySQL 8.0

· mysql, configuration, administration

I have lost tuning work to a reboot more than once. Before 8.0, a permanent change meant two steps: SET GLOBAL for the running instance and an edit to my.cnf for the next restart. Forget the second and your carefully tuned setting vanishes months later. Get the file wrong and the server won't start. And on systems where the DBA has no shell access to /etc/my.cnf, it needed a ticket.

MySQL 8.0 adds SET PERSIST, which writes the change to a JSON file in the data directory that the server reads at startup. You need 8.0.11 or later, and an account with SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN (or SUPER).

Persisting a dynamic variable

SET PERSIST max_connections = 500;
SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 1;

Each statement does two things: it applies the value immediately, exactly like SET GLOBAL, and appends it to mysqld-auto.cnf in the data directory. Have a look at the file:

sudo cat /var/lib/mysql/mysqld-auto.cnf

{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "500" , "Metadata" : { "Timestamp" : 1542540023123456 , "User" : "root" , "Host" : "localhost" } } , "slow_query_log" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1542540031234567 , "User" : "root" , "Host" : "localhost" } } } }

Don't edit this file by hand. The server manages it, and a syntax error makes the server fail to start.

Read-only variables

Some variables, such as innodb_buffer_pool_size on small systems or innodb_log_file_size, cannot be changed at runtime. SET PERSIST_ONLY writes them to mysqld-auto.cnf without touching the running instance:

SET PERSIST_ONLY innodb_log_file_size = 1073741824;
SET PERSIST_ONLY innodb_flush_method = 'O_DIRECT';

The values take effect at the next restart. innodb_buffer_pool_size is dynamic in 8.0 and works with plain SET PERSIST, but the resize happens in chunks and can take a moment on a large pool.

Precedence

At startup the server reads options in this order, later sources overriding earlier ones:

  1. /etc/my.cnf, /etc/mysql/my.cnf and the other standard option files.
  2. mysqld-auto.cnf in the data directory.
  3. Command-line arguments.

So a persisted value beats my.cnf. Convenient, but it confuses the next person who edits my.cnf, changes max_connections, restarts, and sees no effect. I now check for persisted values before touching the file:

SELECT * FROM performance_schema.persisted_variables;

And to see which source each running value came from:

SELECT VARIABLE_NAME, VARIABLE_SOURCE, VARIABLE_PATH, SET_TIME, SET_USER
FROM performance_schema.variables_info
WHERE VARIABLE_SOURCE NOT IN ('COMPILED');

VARIABLE_SOURCE shows PERSISTED, GLOBAL, EXPLICIT (option file), COMMAND_LINE or DYNAMIC, and VARIABLE_PATH shows which file it came from.

Undoing a persisted setting

RESET PERSIST max_connections;       -- remove one
RESET PERSIST IF EXISTS long_query_time;
RESET PERSIST;                       -- remove all

RESET PERSIST only removes the entry from mysqld-auto.cnf; the running value stays until you SET GLOBAL it back or restart. To go back to the compiled default immediately as well:

SET PERSIST max_connections = DEFAULT;

which persists the default value explicitly. Use RESET PERSIST afterwards if you do not want that line in the file either.

Turning it off

If your team keeps all configuration in version-controlled files, you may not want this at all. Two options control it:

[mysqld]
persisted_globals_load = OFF

makes the server ignore mysqld-auto.cnf at startup. And to prevent SET PERSIST from being used at all, revoke SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN from every account that does not need them; with the dynamic privilege model in 8.0 you can grant an operator SYSTEM_VARIABLES_ADMIN alone so they can tune the running instance but not persist anything static.

Restart and check

Restart the server and confirm the values survived:

sudo systemctl restart mysqld
mysql -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('max_connections','slow_query_log','innodb_log_file_size')"

Then check the error log for [Warning] lines mentioning mysqld-auto.cnf; a warning there means an entry could not be applied.

Errors

  • ERROR 1238 (HY000): Variable 'innodb_log_file_size' is a read only variable: you used SET PERSIST on a static variable. Use SET PERSIST_ONLY.
  • ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation: grant SYSTEM_VARIABLES_ADMIN (and PERSIST_RO_VARIABLES_ADMIN for PERSIST_ONLY) to the account.
  • Variable 'datadir' cannot be persisted: a small set of variables that define where the server finds its files (datadir, basedir, plugin_dir, port, socket, log_error) cannot be persisted by design. Those still live in my.cnf.
  • Server fails to start with [ERROR] Failed to parse mysqld-auto.cnf: the file was edited or truncated. Move it aside (mv mysqld-auto.cnf mysqld-auto.cnf.bad), start the server, and re-persist the settings.
  • A setting in my.cnf "does not work": check performance_schema.variables_info; a persisted value is overriding it.

A quiet feature, but a good one: a change made at 02:00 during an incident is permanent without a second step. Just look at persisted_variables before you go hunting in my.cnf.