5.1.9.3 Persisted System Variables

Table des matières

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 the SET statement to affect operation of the current server instance. SET can also be used to persist certain global system variables to the mysqld-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 the SET 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 with SET 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 using SET GLOBAL, MySQL provides variants of SET syntax that persist system variable settings to a file named mysqld-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:

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. See Section 5.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 a RESTART 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.

Table des matières Haut

Syntax for Persisting System Variables

These SET syntax options are available for persisting system variables:

To persist a global system variable to the mysqld-auto.cnf option file in the data directory, precede the variable name by the PERSIST keyword or the @@PERSIST. qualifier:
1. SET PERSIST max_connections = 1000;
2. SET @@PERSIST.max_connections = 1000;
Like SET GLOBAL, SET PERSIST sets the global variable runtime value, but also writes the variable setting to the mysqld-auto.cnf file (replacing any existing variable setting if there is one).
To persist a global system variable to the mysqld-auto.cnf file without setting the global variable runtime value, precede the variable name by the PERSIST_ONLY keyword or the @@PERSIST_ONLY. qualifier:
1. SET PERSIST_ONLY back_log = 1000;
2. 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 makes PERSIST_ONLY suitable for configuring read-only system variables that can be set only at server startup.

For more information about SET, see Section 13.7.5.1, “SET Syntax for Variable Assignment”.

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

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

For more information about RESET PERSIST, see Section 13.7.7.7, “RESET PERSIST Syntax”.

Using SET to persist a global system variable to a value of DEFAULT 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, use RESET PERSIST.

Some system variables cannot be persisted. See Section 5.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 the SET 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 will 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

Table des matières Haut

Obtaining Information About Persisted System Variables

The Performance Schema persisted_variables table provides an SQL interface to the mysqld-auto.cnf file, enabling its contents to be inspected at runtime using SELECT statements. See Section 26.12.13.1, “Performance Schema persisted_variables Table”.

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

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

Table des matières Haut

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

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

{
  "Version": 1,
  "mysql_server": {
    "max_connections": {
      "Value": "152",
      "Metadata": {
        "Timestamp": 1.519921356e+15,
        "User": "root",
        "Host": "localhost"
      }
    },
    "transaction_isolation": {
      "Value": "READ-COMMITTED",
      "Metadata": {
        "Timestamp": 1.519921553e+15,
        "User": "root",
        "Host": "localhost"
      }
    },
    "mysql_server_static_options": {
      "innodb_api_enable_mdl": {
        "Value": "0",
        "Metadata": {
          "Timestamp": 1.519921573e+15,
          "User": "root",
          "Host": "localhost"
        }
      },
      "log_slave_updates": {
        "Value": "1",
        "Metadata": {
          "Timestamp": 1.519921582e+15,
          "User": "root",
          "Host": "localhost"
        }
      }
    }
  }
}

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

If the persisted_globals_load system variable is disabled, the server ignores the mysqld-auto.cnf file.
Only read-only variables persisted using SET PERSIST_ONLY appear in the "mysql_server_static_options" section. 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 a SET 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 in my.cnf rather than in mysqld-auto.cnf.

Management of the mysqld-auto.cnf file should be left to the server. Manipulation of the file should be performed only using SET and RESET 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:
1. 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 the persisted_globals_load system variable disabled or with the --no-defaults option. Alternatively, remove the mysqld-auto.cnf file. However, as noted previously, removing this file results in a loss of all persisted settings.

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-persisted-system-variables.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut