MySQL :: MySQL 8.0 Reference Manual :: 7.1.9.3 Persisted System Variables (original) (raw)

7.1.9.3 Persisted System Variables

The MySQL server maintains system variables that configure its operation. A system variable can have a global value that affects server operation as a whole, a session value that affects the current session, or both. Many system variables are dynamic and can be changed at runtime using theSET statement to affect operation of the current server instance.SET can also be used to persist certain global system variables to themysqld-auto.cnf file in the data directory, to affect server operation for subsequent startups.RESET PERSIST removes persisted settings from mysqld-auto.cnf.

The following discussion describes aspects of persisting system variables:

Overview of Persisted System Variables

The capability of persisting global system variables at runtime enables server configuration that persists across server startups. Although many system variables can be set at startup from a my.cnf option file, or at runtime using theSET statement, those methods of configuring the server either require login access to the server host, or do not provide the capability of persistently configuring the server at runtime or remotely:

To augment administrative capabilities for server configuration beyond what is achievable by editing option files or usingSET GLOBAL, MySQL provides variants ofSET syntax that persist system variable settings to a file namedmysqld-auto.cnf file in the data directory. Examples:

SET PERSIST max_connections = 1000;
SET @@PERSIST.max_connections = 1000;

SET PERSIST_ONLY back_log = 100;
SET @@PERSIST_ONLY.back_log = 100;

MySQL also provides a RESET PERSIST statement for removing persisted system variables from mysqld-auto.cnf.

Server configuration performed by persisting system variables has these characteristics:

Syntax for Persisting System Variables

TheseSET syntax options are available for persisting system variables:

SET PERSIST max_connections = 1000;  
SET @@PERSIST.max_connections = 1000;

Like SET GLOBAL,SET PERSIST sets the global variable runtime value, but also writes the variable setting to themysqld-auto.cnf file (replacing any existing variable setting if there is one).

SET PERSIST_ONLY back_log = 1000;  
SET @@PERSIST_ONLY.back_log = 1000;

Like PERSIST,PERSIST_ONLY writes the variable setting to mysqld-auto.cnf. However, unlike PERSIST,PERSIST_ONLY does not modify the global variable runtime value. This makesPERSIST_ONLY suitable for configuring read-only system variables that can be set only at server startup.

For more information aboutSET, see Section 15.7.6.1, “SET Syntax for Variable Assignment”.

These RESET PERSIST syntax options are available for removing persisted system variables:

RESET PERSIST;  
RESET PERSIST system_var_name;

This includes plugin system variables, even if the plugin is not currently installed. If the variable is not present in the file, an error occurs.

RESET PERSIST IF EXISTS system_var_name;

For more information about RESET PERSIST, see Section 15.7.8.7, “RESET PERSIST Statement”.

UsingSET to persist a global system variable to a value ofDEFAULT or to its literal default value assigns the variable its default value and adds a setting for the variable to mysqld-auto.cnf. To remove the variable from the file, useRESET PERSIST.

Some system variables cannot be persisted. SeeSection 7.1.9.4, “Nonpersistible and Persist-Restricted System Variables”.

A system variable implemented by a plugin can be persisted if the plugin is installed when theSET statement is executed. Assignment of the persisted plugin variable takes effect for subsequent server restarts if the plugin is still installed. If the plugin is no longer installed, the plugin variable does not exist when the server reads the mysqld-auto.cnf file. In this case, the server writes a warning to the error log and continues:

currently unknown variable 'var_name'
was read from the persisted config file
Format and Server Handling of the mysqld-auto.cnf File

The mysqld-auto.cnf file uses aJSON format like this (reformatted slightly for readability):

{
  "Version": 1,
  "mysql_server": {
    "max_connections": {
      "Value": "152",
      "Metadata": {
        "Timestamp": 1519921341372531,
        "User": "root",
        "Host": "localhost"
      }
    },
    "transaction_isolation": {
      "Value": "READ-COMMITTED",
      "Metadata": {
        "Timestamp": 1519921553880520,
        "User": "root",
        "Host": "localhost"
      }
    },
    "mysql_server_static_options": {
      "innodb_api_enable_mdl": {
        "Value": "0",
        "Metadata": {
          "Timestamp": 1519922873467872,
          "User": "root",
          "Host": "localhost"
        }
      },
      "log_slave_updates": {
        "Value": "1",
        "Metadata": {
          "Timestamp": 1519925628441588,
          "User": "root",
          "Host": "localhost"
        }
      }
    }
  }
}

At startup, the server processes themysqld-auto.cnf file after all other option files (see Section 6.2.2.2, “Using Option Files”). The server handles the file contents as follows:

Management of the mysqld-auto.cnf file should be left to the server. Manipulation of the file should be performed only usingSET and RESET PERSIST statements, not manually:

RESET PERSIST;
Persisting Sensitive System Variables

From MySQL 8.0.29, MySQL Server has the capability to securely store persisted system variable values containing sensitive data such as private keys or passwords, and restrict viewing of the values. No MySQL Server system variables are currently marked as sensitive, but the new capability allows system variables containing sensitive data to be persisted securely in the future. After upgrading to MySQL 8.0.29, the format of the mysqld-auto.cnf option file remains the same until the first time aSET PERSIST orSET PERSIST ONLY statement is issued, and at that point it is changed to a new format, even if the system variable involved is not sensitive. In the new format, the option file cannot be read by older releases of MySQL Server.

Note

A keyring component must be enabled on the MySQL Server instance to support secure storage for persisted system variable values, rather than a keyring plugin, which do not support the function. See Section 8.4.4, “The MySQL Keyring”.

In the mysqld-auto.cnf option file, the names and values of sensitive system variables are stored in an encrypted format, along with a generated file key to decrypt them. The generated file key is in turn encrypted using a master key (persisted_variables_key) that is stored in a keyring. When the server starts up, the persisted sensitive system variables are decrypted and used. By default, if encrypted values are present in the option file but cannot be successfully decrypted at startup, their default settings are used. The optional most secure setting makes the server halt startup if the encrypted values cannot be decrypted.

The system variablepersist_sensitive_variables_in_plaintext controls whether the server is permitted to store the values of sensitive system variables in an unencrypted format, if keyring component support is not available at the time whenSET PERSIST is used to set the value. It also controls whether or not the server can start if the encrypted values cannot be decrypted.

The privilegeSENSITIVE_VARIABLES_OBSERVER allows a holder to view the values of sensitive system variables in the Performance Schema tablesglobal_variables,session_variables,variables_by_thread, andpersisted_variables, to issue SELECT statements to return their values, and to track changes to them in session trackers for connections. Users without this privilege cannot view or track those system variable values.

If a SET statement is issued for a sensitive system variable, the query is rewritten to replace the value with “<redacted>” before it is logged to the general log and audit log. This takes place even if secure storage through a keyring component is not available on the server instance.