Movatterモバイル変換

MySQL 9.4 Reference Manual

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 frommysqld-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 amy.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:

Modifying an option file requires direct access to that file, which requires login access to the MySQL server host. This is not always convenient.
Modifying system variables withSET GLOBAL is a runtime capability that can be done from clients run locally or from remote hosts, but the changes affect only the currently running server instance. The settings are not persistent and do not carry over to subsequent server startups.

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 aRESET PERSIST statement for removing persisted system variables frommysqld-auto.cnf.

Server configuration performed by persisting system variables has these characteristics:

Persisted settings are made at runtime.
Persisted settings are permanent. They apply across server restarts.
Persisted settings can be made from local clients or clients who connect from a remote host. This provides the convenience of remotely configuring multiple MySQL servers from a central client host.
To persist system variables, you need not have login access to the MySQL server host or file system access to option files. Ability to persist settings is controlled using the MySQL privilege system. SeeSection 7.1.9.1, “System Variable Privileges”.
An administrator with sufficient privileges can reconfigure a server by persisting system variables, then cause the server to use the changed settings immediately by executing aRESTART statement.
Persisted settings provide immediate feedback about errors. An error in a manually entered setting might not be discovered until much later.SET statements that persist system variables avoid the possibility of malformed settings because settings with syntax errors do not succeed and do not change server configuration.

Syntax for Persisting System Variables

TheseSET syntax options are available for persisting system variables:

To persist a global system variable to themysqld-auto.cnf option file in the data directory, precede the variable name by thePERSIST keyword or the@@PERSIST. qualifier:
```
SET PERSIST max_connections = 1000;SET @@PERSIST.max_connections = 1000;
```
LikeSET 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).
To persist a global system variable to themysqld-auto.cnf file without setting the global variable runtime value, precede the variable name by thePERSIST_ONLY keyword or the@@PERSIST_ONLY. qualifier:
```
SET PERSIST_ONLY back_log = 1000;SET @@PERSIST_ONLY.back_log = 1000;
```
LikePERSIST,PERSIST_ONLY writes the variable setting tomysqld-auto.cnf. However, unlikePERSIST,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, seeSection 15.7.6.1, “SET Syntax for Variable Assignment”.

TheseRESET PERSIST syntax options are available for removing persisted system variables:

To remove all persisted variables frommysqld-auto.cnf, useRESET PERSIST without naming any system variable:
```
RESET PERSIST;
```
To remove a specific persisted variable frommysqld-auto.cnf, name it in the statement:
```
RESET PERSISTsystem_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.
To remove a specific persisted variable frommysqld-auto.cnf, but produce a warning rather than an error if the variable is not present in the file, add anIF EXISTS clause to the previous syntax:
```
RESET PERSIST IF EXISTSsystem_var_name;
```

For more information aboutRESET PERSIST, seeSection 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 tomysqld-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 themysqld-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

Obtaining Information About Persisted System Variables

The Performance Schemapersisted_variables table provides an SQL interface to themysqld-auto.cnf file, enabling its contents to be inspected at runtime usingSELECT statements. SeeSection 29.12.14.2, “Performance Schema persisted_variables Table”.

The Performance Schemavariables_info table contains information showing when and by which user each system variable was most recently set. SeeSection 29.12.14.3, “Performance Schema variables_info Table”.

RESET PERSIST affects the contents of thepersisted_variables table because the table contents correspond to the contents of themysqld-auto.cnf file. On the other hand, becauseRESET PERSIST does not change variable values, it has no effect on the contents of thevariables_info table until the server is restarted.

Format and Server Handling of the mysqld-auto.cnf File

Themysqld-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_replica_updates": {        "Value": "1",        "Metadata": {          "Timestamp": 1519925628441588,          "User": "root",          "Host": "localhost"        }      }    }  }}

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

If thepersisted_globals_load system variable is disabled, the server ignores themysqld-auto.cnf file.
The"mysql_server_static_options" section contains read-only variables persisted usingSET PERSIST_ONLY. The section may also (despite its name) contain certain dynamic variables that are not read only. All variables present inside this section are appended to the command line and processed with other command-line options.
All remaining persisted variables are set by executing the equivalent of aSET GLOBAL statement later, just before the server starts listening for client connections. These settings therefore do not take effect until late in the startup process, which might be unsuitable for certain system variables. It may be preferable to set such variables inmy.cnf rather than inmysqld-auto.cnf.

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

Removal of the file results in a loss of all persisted settings at the next server startup. (This is permissible if your intent is to reconfigure the server without these settings.) To remove all settings in the file without removing the file itself, use this statement:
```
RESET PERSIST;
```
Manual changes to the file may result in a parse error at server startup. In this case, the server reports an error and exits. If this issue occurs, start the server with thepersisted_globals_load system variable disabled or with the--no-defaults option. Alternatively, remove themysqld-auto.cnf file. However, as noted previously, removing this file results in a loss of all persisted settings.

Persisting Sensitive System Variables

MySQL 9.4 has the capability to store persisted system variable values containing sensitive data such as private keys or passwords securely, and to restrict viewing of the values. No MySQL Server system variables are currently marked as sensitive, but this capability allows system variables containing sensitive data to be persisted securely in the future. Amysqld-auto.cnf option file created by MySQL 9.4 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. SeeSection 8.4.5, “The MySQL Keyring”.

In themysqld-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 default setting,ON, encrypts the values if keyring component support is available, and persists them unencrypted (with a warning) if it is not. The next time any persisted system variable is set, if keyring support is available at that time, the server encrypts the values of any unencrypted sensitive system variables. TheON setting also allows the server to start if encrypted system variable values cannot be decrypted, in which case a warning is issued and the default values for the system variables are used. In that situation, their values cannot be changed until they can be decrypted.
The most secure setting,OFF, means sensitive system variable values cannot be persisted if keyring component support is unavailable. TheOFF setting also means the server does not start if encrypted system variable 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 issueSELECT 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 aSET 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.

PREV HOME UP NEXT