Persisting MySQL 8.0 Configuration with SET PERSIST and SET PERSIST_ONLY

Background – In MySQL 5.7 and earlier, only dynamic variables could be changed at runtime with SET GLOBAL; static variables required editing /etc/my.cnf and restarting the server, which is inconvenient for cloud environments.

MySQL 8.0 adds the SET PERSIST (for dynamic variables) and SET PERSIST_ONLY (for static variables) statements, allowing changes to be written automatically to a JSON file ( mysqld-auto.cnf) in the data directory.

Example – Persisting a dynamic variable

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)

mysql> set persist innodb_buffer_pool_size=134217728*2;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+
1 row in set (0.00 sec)

The command creates or updates mysqld-auto.cnf with a JSON entry that includes the new value, a timestamp, the user, and the host.

{
  "Version": 1,
  "mysql_server": {
    "innodb_buffer_pool_size": {
      "Value": "268435456",
      "Metadata": {
        "Timestamp": 1570678719890919,
        "User": "root",
        "Host": "localhost"
      }
    }
  }
}

Example – Persisting a static variable

# Simulate missing privileges
mysql> revoke SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN on *.* from 'root'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> set persist innodb_log_file_size=50331648*2;
ERROR 1238 (HY000): Variable 'innodb_log_file_size' is a read only variable

# Grant the required privileges
mysql> grant SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN on *.* to 'root'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> set persist_only innodb_log_file_size=50331648*2;
Query OK, 0 rows affected (0.00 sec)

After granting the privileges, the static variable is persisted in the same JSON file.

Resetting persisted parameters

mysql> RESET PERSIST;
Query OK, 0 rows affected (0.00 sec)

Alternatively, the JSON file can be edited or deleted manually; on cloud instances only RESET PERSIST is available.

Inspecting persisted settings

mysql> select * from performance_schema.variables_info where variable_source like 'PERSISTED'\G
*************************** 1. row ***************************
  VARIABLE_NAME: innodb_buffer_pool_size
VARIABLE_SOURCE: PERSISTED
VARIABLE_PATH: /data/mysql/mysql3308/data/mysqld-auto.cnf
      MIN_VALUE: 5242880
      MAX_VALUE: 9223372036854775807
       SET_TIME: 2019-10-10 11:38:39.890919
       SET_USER: root
       SET_HOST: localhost

*************************** 2. row ***************************
  VARIABLE_NAME: innodb_log_file_size
VARIABLE_SOURCE: PERSISTED
VARIABLE_PATH: /data/mysql/mysql3308/data/mysqld-auto.cnf
      MIN_VALUE: 4194304
      MAX_VALUE: 18446744073709551615
       SET_TIME: 2019-10-10 11:21:12.623177
       SET_USER: root
       SET_HOST: localhost

The performance_schema.variables_info table also shows variables from other sources: EXPLICIT (from my.cnf), COMMAND_LINE (from the server start command), and DYNAMIC (session variables).

Querying dynamic session variables

mysql> set session binlog_format='Mixed';
Query OK, 0 rows affected (0.00 sec)

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE FROM performance_schema.variables_info t1 JOIN performance_schema.session_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME WHERE t1.VARIABLE_SOURCE = 'DYNAMIC';
+--------------------+----------------+-----------------+
| VARIABLE_NAME      | VARIABLE_VALUE | VARIABLE_SOURCE |
+--------------------+----------------+-----------------+
| binlog_format      | MIXED          | DYNAMIC         |
| foreign_key_checks | ON             | DYNAMIC         |
| read_only          | ON             | DYNAMIC         |
+--------------------+----------------+-----------------+
2 rows in set (0.01 sec)

Notes on privileges – In MySQL 8.0 the old ALL privilege no longer grants the new SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN rights, so they must be granted explicitly.

Comparison with MySQL 5.7 – In 5.7 static variables could only be changed by editing my.cnf and restarting, and the SHOW GRANTS output for a user created with GRANT ALL was much simpler.

Reference links – https://lefred.be/content/mysql-8-0-changing-configuration-easily-and-cloud-friendly/ and https://lefred.be/content/what-configuration-settings-did-i-change-on-my-mysql-server/