Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.1Mb
PDF (A4) - 38.1Mb
PDF (RPM) - 33.0Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 132.7Kb
Man Pages (Zip) - 189.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Using System Variables

5.1.8 Using System Variables

The MySQL server maintains many system variables that indicate how it is configured. Section 5.1.7, “Server System Variables”, describes the meaning of these variables. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running by means of the SET statement, which enables you to modify operation of the server without having to stop and restart it. You can also use system variable values in expressions.

System variables implemented by a server component are exposed when the component is installed and have names that begin with a component-specific prefix. For example, the log_filter_dragnet error log filter component implements a system variable named log_error_filter_rules, the full name of which is dragnet.log_error_filter_rules. To refer to this variable, use the full name.

There are two scopes in which system variables exist. Global variables affect the overall operation of the server. Session variables affect its operation for individual client connections. A given system variable can have both a global and a session value. Global and session system variables are related as follows:

  • When the server starts, it initializes each global variable to its default value. These defaults can be changed by options specified on the command line or in an option file. (See Section 4.2.4, “Specifying Program Options”.)

  • The server also maintains a set of session variables for each client that connects. The client's session variables are initialized at connect time using the current values of the corresponding global variables. For example, a client's SQL mode is controlled by the session sql_mode value, which is initialized when the client connects to the value of the global sql_mode value.

    For some system variables, the session value is not initialized from the corresponding global value; if so, that is indicated in the variable description.

System variable values can be set globally at server startup by using options on the command line or in an option file. When you use a startup option to set a variable that takes a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243; that is, units of kilobytes, megabytes, or gigabytes, respectively. Thus, the following command starts the server with an InnoDB log file size of 16 megabytes and a maximum packet size of one gigabyte:

mysqld --innodb_log_file_size=16M --max_allowed_packet=1G

Within an option file, those variables are set like this:


The lettercase of suffix letters does not matter; 16M and 16m are equivalent, as are 1G and 1g.

To restrict the maximum value to which a system variable can be set at runtime with the SET statement, specify this maximum by using an option of the form --maximum-var_name=value at server startup. For example, to prevent the value of innodb_log_file_size from being increased to more than 32MB at runtime, use the option --maximum-innodb_log_file_size=32M.

Many system variables are dynamic and can be changed at runtime by using the SET statement. For a list, see Section, “Dynamic System Variables”. To change a system variable with SET, refer to it by name, optionally preceded by a modifier:

  • To indicate that a variable is a global variable, precede its name by the GLOBAL keyword or the @@global. qualifier:

    SET GLOBAL max_connections = 1000;
    SET @@global.max_connections = 1000;

    The SYSTEM_VARIABLES_ADMIN or SUPER privilege is required to set global variables.

  • Another way to set a global variable is to precede its name by the PERSIST keyword or the @@persist. qualifier:

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

    This SET syntax enables you to make configuration changes at runtime that also persist across server restarts. Like SET GLOBAL, SET PERSIST changes the runtime variable value, but also writes the variable setting to an option file named mysqld-auto.cnf in the data directory (replacing any existing variable setting if there is one). At startup, the server processes this file after all other option files. The SYSTEM_VARIABLES_ADMIN or SUPER privilege is required to persist global variables.

    For a list of system variables that cannot be persisted, see Section, “Nonpersistent System Variables”


    Management of the mysqld-auto.cnf file should be left to the server and not performed 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:

    • 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, but, as noted previously, removing this file results in a loss of all persisted settings.

    A plugin variable can be persisted if the plugin is installed when SET PERSIST 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
  • The PERSIST_ONLY keyword or @@persist_only. qualifier is similar to PERSIST:

    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 runtime global system variable value, making it suitable for configuring read-only system variables that can be set only at server startup. The PERSIST_RO_VARIABLES_ADMIN privilege is required to use PERSIST_ONLY.

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

      "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"

    Only read only variables persisted using PERSIST_ONLY are written to the "mysql_server_static_options" section. All variables present inside the "mysql_server_static_options" section are appended to the command line when the server is started. All remaining persisted variables are set by executing a SET GLOBAL statement.

  • To indicate that a variable is a session variable, precede its name by the SESSION keyword or either the @@session. or @@ qualifier:

    SET @@session.sql_mode = 'TRADITIONAL';
    SET @@sql_mode = 'TRADITIONAL';

    Setting a session variable normally requires no special privilege, although there are exceptions that require the SYSTEM_VARIABLES_ADMIN or SUPER privilege (such as sql_log_bin). A client can change its own session variables, but not those of any other client.

    Session-only system variables cannot be persisted. They cannot be set at server startup, so there is no reason to list them in mysqld-auto.cnf.

  • LOCAL and @@local. are synonyms for SESSION and @@session..

  • If no modifier is present, SET changes the session variable.

  • An error occurs under these circumstances:

    • Use of SET GLOBAL (or @@global.), SET PERSIST (or @@persist.), or SET PERSIST_ONLY (or @@persist_only.), when setting a variable that has only a session value:

      mysql> SET GLOBAL sql_log_bin = ON;
      ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION
      variable and can't be used with SET GLOBAL
    • Omission of GLOBAL (or @@global.), PERSIST (or @@persist.), or PERSIST_ONLY (or @@persist_only.) when setting a variable that has only a global value:

      mysql> SET max_connections = 1000;
      ERROR 1229 (HY000): Variable 'max_connections' is a
      GLOBAL variable and should be set with SET GLOBAL
    • Use of SET PERSIST (or @@persist.), or SET PERSIST_ONLY (or @@persist_only.), when setting a variable that cannot be persisted:

      mysql> SET PERSIST port = 3307;
      ERROR 1238 (HY000): Variable 'port' is a read only variable
      mysql> SET PERSIST_ONLY port = 3307;
      ERROR 1238 (HY000): Variable 'port' is a non persistent read only variable
    • Use of SET SESSION (or @@SESSION.) when setting a variable that has only a global value:

      mysql> SET SESSION max_connections = 1000;
      ERROR 1229 (HY000): Variable 'max_connections' is a
      GLOBAL variable and should be set with SET GLOBAL

The preceding modifiers apply only to system variables. An error occurs for attempts to apply them to user-defined variables, stored procedure or function parameters, or stored program local variables.

A SET statement can contain multiple variable assignments, separated by commas. This statement assigns values to a user-defined variable and a system variable:

SET @x = 1, SESSION sql_mode = '';

If you set multiple system variables, the most recent GLOBAL, PERSIST, PERSIST_ONLY, or SESSION modifier in the statement is used for following assignments that have no modifier specified.

Examples of multiple-variable assignment:

SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
SET @@global.sort_buffer_size = 1000000, @@local.sort_buffer_size = 1000000;
SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;

If any variable assignment in a SET statement fails, the entire statement fails and no variables are changed, nor is the mysqld-auto.cnf file changed.

If you change a session system variable, the value remains in effect within your session until you change the variable to a different value or the session ends. The change has no effect on other sessions.

If you change a global system variable, the value is remembered and used for new sessions until you change the variable to a different value or the server exits. The change is visible to any client that accesses the global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any current client sessions (not even the session within which the SET GLOBAL statement occurred).

To make a global system variable setting permanent so that it applies across server restarts, modify it with SET PERSIST or PERSIST_ONLY to record it in the mysqld-auto.cnf file. It is also possible to use SET GLOBAL and manually modify a my.cnf file, but that is more cumbersome, and an error in a manually entered setting might not be discovered until much later. SET PERSIST or PERSIST_ONLY is more convenient and avoids the possibility of malformed settings.

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, “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, “Performance Schema variables_info Table”.

To set a GLOBAL value to the compiled-in MySQL default value or a SESSION variable to the current corresponding GLOBAL value, set the variable to the value DEFAULT. For example, the following two statements are identical in setting the session value of max_join_size to the current global value:

SET @@session.max_join_size=DEFAULT;
SET @@session.max_join_size=@@global.max_join_size;

Not all system variables can be set to DEFAULT. In such cases, assigning DEFAULT results in an error.

With SET PERSIST (or @@persist.), setting a global variable to 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.

An error occurs for attempts to assign DEFAULT to user-defined variables, stored procedure or function parameters, or stored program local variables.

To refer to the value of a system variable in expressions, use one of the @@-modifiers (except @@persist., which is not permitted in expressions). For example, you can retrieve values in a SELECT statement like this:

SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;

For a reference to a system variable in an expression as @@var_name (rather than with @@global. or @@session.), MySQL returns the session value if it exists and the global value otherwise. This differs from SET @@var_name = expr, which always refers to the session value.

Suffixes for specifying a value multiplier can be used when setting a variable at server startup, but not to set the value with SET at runtime. On the other hand, with SET you can assign a variable's value using an expression, which is not true when you set a variable at server startup. For example, the first of the following lines is legal at server startup, but the second is not:

shell> mysql --max_allowed_packet=16M
shell> mysql --max_allowed_packet=16*1024*1024

Conversely, the second of the following lines is legal at runtime, but the first is not:

mysql> SET GLOBAL max_allowed_packet=16M;
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;

Some system variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0. However, to set such a variable on the command line or in an option file, you must set it to 1 or 0; setting it to ON or OFF will not work. For example, on the command line, --delay_key_write=1 works but --delay_key_write=ON does not.

To display system variable names and values, use the SHOW VARIABLES statement:

| Variable_name                   | Value                             |
| auto_increment_increment        | 1                                 |
| auto_increment_offset           | 1                                 |
| automatic_sp_privileges         | ON                                |
| back_log                        | 151                               |
| basedir                         | /home/mysql/                      |
| binlog_cache_size               | 32768                             |
| bulk_insert_buffer_size         | 8388608                           |
| character_set_client            | utf8                              |
| character_set_connection        | utf8                              |
| character_set_database          | utf8mb4                           |
| character_set_filesystem        | binary                            |
| character_set_results           | utf8                              |
| character_set_server            | utf8mb4                           |
| character_set_system            | utf8                              |
| character_sets_dir              | /home/mysql/share/mysql/charsets/ |
| collation_connection            | utf8_general_ci                   |
| collation_database              | utf8mb4_0900_ai_ci                |
| collation_server                | utf8mb4_0900_ai_ci                |
| innodb_autoextend_increment     | 8                                 |
| innodb_buffer_pool_size         | 8388608                           |
| innodb_commit_concurrency       | 0                                 |
| innodb_concurrency_tickets      | 500                               |
| innodb_data_file_path           | ibdata1:10M:autoextend            |
| innodb_data_home_dir            |                                   |
| version                         | 8.0.1-dmr-log                     |
| version_comment                 | Source distribution               |
| version_compile_machine         | i686                              |
| version_compile_os              | suse-linux                        |
| wait_timeout                    | 28800                             |

With a LIKE clause, the statement displays only those variables that match the pattern. To obtain a specific variable name, use a LIKE clause as shown:

SHOW VARIABLES LIKE 'max_join_size';

To get a list of variables whose name match a pattern, use the % wildcard character in a LIKE clause:


Wildcard characters can be used in any position within the pattern to be matched. Strictly speaking, because _ is a wildcard that matches any single character, you should escape it as \_ to match it literally. In practice, this is rarely necessary.

For SHOW VARIABLES, if you specify neither GLOBAL nor SESSION, MySQL returns SESSION values.

The reason for requiring the GLOBAL keyword when setting GLOBAL-only variables but not when retrieving them is to prevent problems in the future:

  • Were we to remove a SESSION variable that has the same name as a GLOBAL variable, a client with the SYSTEM_VARIABLES_ADMIN or SUPER privilege might accidentally change the GLOBAL variable rather than just the SESSION variable for its own connection.

  • Were we to add a SESSION variable with the same name as a GLOBAL variable, a client that intends to change the GLOBAL variable might find only its own SESSION variable changed.

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Rajesh K on June 30, 2011
There is a slight difference in behavior of some session variables like character_set_client. It doesn't take the value from global variable

Source: which says

Updated the description for character_set_client:

The character set for statements that arrive from the client. The
session value of this variable is set using the character set
requested by the client when the client connects to the server. (Many
clients support a --default-character-set option to enable this
character set to be specified explicitly.) The global
value of the variable is used to set the session value in cases when
the client-requested value is unknown or not available, or the server
is configured to ignore client requests:

* The client is from a version of MySQL older than MySQL 4.1, and thus
does not request a character set.

* The client requests a character set not known to the server. For
example, a Japanese-enabled client requests sjis when connecting to a
server not configured with sjis support.

* mysqld was started with the --skip-character-set-client-handshake
option, which causes it to ignore client character set configuration.
This reproduces MySQL 4.0 behavior and is useful should you wish to
upgrade the server without upgrading all the clients.

Sign Up Login You must be logged in to post a comment.