5.1.9.3 Persisted System Variables

Inhoudsopgave

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.

Inhoudsopgave 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

Inhoudsopgave 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.

Inhoudsopgave 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.2.2, “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.

Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-persisted-system-variables.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

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

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut