7.1.8 Server System Variables

Skip to Main Content

MySQL 8.4 Reference Manual Including MySQL NDB Cluster 8.4

7.1.8 Server System Variables

The MySQL server maintains many system variables that affect its operation. Most 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 at runtime using the SET statement, which enables you to modify operation of the server without having to stop and restart it. Some variables are read-only, and their values are determined by the system environment, by how MySQL is installed on the system, or possibly by the options used to compile MySQL. Most system variables have a default value, but there are exceptions, including read-only variables. You can also use system variable values in expressions.

Setting a global system variable runtime value normally requires the SYSTEM_VARIABLES_ADMIN privilege (or the deprecated SUPER privilege). Setting a session system runtime variable value normally requires no special privileges and can be done by any user, although there are exceptions. For more information, see Section 7.1.9.1, “System Variable Privileges”

There are several ways to see the names and values of system variables:

• To see the values that a server uses based on its compiled-in defaults and any option files that it reads, use this command:

mysqld --verbose --help - To see the values that a server uses based only on its compiled-in defaults, ignoring the settings in any option files, use this command:

mysqld --no-defaults --verbose --help - To see the current values used by a running server, use the SHOW VARIABLES statement or the Performance Schema system variable tables. See Section 29.12.14, “Performance Schema System Variable Tables”.

This section provides a description of each system variable. For a system variable summary table, see Section 7.1.5, “Server System Variable Reference”. For more information about manipulation of system variables, see Section 7.1.9, “Using System Variables”.

For additional system variable information, see these sections:

• Section 7.1.9, “Using System Variables”, discusses the syntax for setting and displaying system variable values.
• Section 7.1.9.2, “Dynamic System Variables”, lists the variables that can be set at runtime.
• Information on tuning system variables can be found in Section 7.1.1, “Configuring the Server”.
• Section 17.14, “InnoDB Startup Options and System Variables”, lists InnoDB system variables.
• Section 25.4.3.9.2, “NDB Cluster System Variables”, lists system variables which are specific to NDB Cluster.
• For information on server system variables specific to replication, see Section 19.1.6, “Replication and Binary Logging Options and Variables”.

Note

Some of the following variable descriptions refer to “enabling” or “disabling” a variable. These variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0. Boolean variables can be set at startup to the values ON, TRUE, OFF, and FALSE (not case-sensitive), as well as 1 and 0. See Section 6.2.2.4, “Program Option Modifiers”.

Some system variables control the size of buffers or caches. For a given buffer, the server might need to allocate internal data structures. These structures typically are allocated from the total memory allocated to the buffer, and the amount of space required might be platform dependent. This means that when you assign a value to a system variable that controls a buffer size, the amount of space actually available might differ from the value assigned. In some cases, the amount might be less than the value assigned. It is also possible that the server adjusts a value upward. For example, if you assign a value of 0 to a variable for which the minimal value is 1024, the server sets the value to 1024.

Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.

Note

Some system variable descriptions include a block size, in which case a value that is not an integer multiple of the stated block size is rounded down to the next lower multiple of the block size before being stored by the server, that is to FLOOR(value) * block_size.

Example: Suppose that the block size for a given variable is given as 4096, and you set the value of the variable to 100000 (we assume that the variable's maximum value is greater than this number). Since 100000 / 4096 = 24.4140625, the server automatically lowers the value to 98304 (24 * 4096) before storing it.

In some cases, the stated maximum for a variable is the maximum allowed by the MySQL parser, but is not an exact multiple of the block size. In such cases, the effective maximum is the next lower multiple of the block size.

Example: A system variable's maxmum value is shown as 4294967295 (232-1), and its block size is 1024. 4294967295 / 1024 = 4194303.9990234375, so if you set this variable to its stated maximum, the value actually stored is 4194303 * 1024 = 4294966272.

Some system variables take file name values. Unless otherwise specified, the default file location is the data directory if the value is a relative path name. To specify the location explicitly, use an absolute path name. Suppose that the data directory is /var/mysql/data. If a file-valued variable is given as a relative path name, it is located under /var/mysql/data. If the value is an absolute path name, its location is as given by the path name.

• activate_all_roles_on_login

Whether to enable automatic activation of all granted roles when users log in to the server:

• If activate_all_roles_on_login is enabled, the server activates all roles granted to each account at login time. This takes precedence over default roles specified with SET DEFAULT ROLE.
• If activate_all_roles_on_login is disabled, the server activates the default roles specified with SET DEFAULT ROLE, if any, at login time.

Granted roles include those granted explicitly to the user and those named in the mandatory_roles system variable value.

activate_all_roles_on_login applies only at login time, and at the beginning of execution for stored programs and views that execute in definer context. To change the active roles within a session, use SET ROLE. To change the active roles for a stored program, the program body should execute SET ROLE. - admin_address

The IP address on which to listen for TCP/IP connections on the administrative network interface (see Section 7.1.12.1, “Connection Interfaces”). There is no default admin_address value. If this variable is not specified at startup, the server maintains no administrative interface. The server also has a bind_address system variable for configuring regular (nonadministrative) client TCP/IP connections. See Section 7.1.12.1, “Connection Interfaces”.

If admin_address is specified, its value must satisfy these requirements:

• The value must be a single IPv4 address, IPv6 address, or host name.
• The value cannot specify a wildcard address format (*, 0.0.0.0, or ::).
• The value may include a network namespace specifier.

An IP address can be specified as an IPv4 or IPv6 address. If the value is a host name, the server resolves the name to an IP address and binds to that address. If a host name resolves to multiple IP addresses, the server uses the first IPv4 address if there are any, or the first IPv6 address otherwise.

The server treats different types of addresses as follows:

• If the address is an IPv4-mapped address, the server accepts TCP/IP connections for that address, in either IPv4 or IPv6 format. For example, if the server is bound to ::ffff:127.0.0.1, clients can connect using --host=127.0.0.1 or --host=::ffff:127.0.0.1.
• If the address is a “regular” IPv4 or IPv6 address (such as 127.0.0.1 or ::1), the server accepts TCP/IP connections only for that IPv4 or IPv6 address.

These rules apply to specifying a network namespace for an address:

• A network namespace can be specified for an IP address or a host name.
• A network namespace cannot be specified for a wildcard IP address.
• For a given address, the network namespace is optional. If given, it must be specified as a /ns suffix immediately following the address.
• An address with no /ns suffix uses the host system global namespace. The global namespace is therefore the default.
• An address with a /ns suffix uses the namespace named ns.
• The host system must support network namespaces and each named namespace must previously have been set up. Naming a nonexistent namespace produces an error.

For additional information about network namespaces, see Section 7.1.14, “Network Namespace Support”.

If binding to the address fails, the server produces an error and does not start.

The admin_address system variable is similar to the bind_address system variable that binds the server to an address for ordinary client connections, but with these differences:

• bind_address permits multiple addresses. admin_address permits a single address.
• bind_address permits wildcard addresses. admin_address does not.
• admin_port

The TCP/IP port number to use for connections on the administrative network interface (see Section 7.1.12.1, “Connection Interfaces”). Setting this variable to 0 causes the default value to be used.

Setting admin_port has no effect if admin_address is not specified because in that case the server maintains no administrative network interface. - admin_ssl_ca

The admin_ssl_ca system variable is like ssl_ca, except that it applies to the administrative connection interface rather than the main connection interface. For information about configuring encryption support for the administrative interface, see Administrative Interface Support for Encrypted Connections. - admin_ssl_capath

The admin_ssl_capath system variable is like ssl_capath, except that it applies to the administrative connection interface rather than the main connection interface. For information about configuring encryption support for the administrative interface, see Administrative Interface Support for Encrypted Connections. - admin_ssl_cert

The admin_ssl_cert system variable is like ssl_cert, except that it applies to the administrative connection interface rather than the main connection interface. For information about configuring encryption support for the administrative interface, see Administrative Interface Support for Encrypted Connections. - admin_ssl_cipher

The admin_ssl_cipher system variable is like ssl_cipher, except that it applies to the administrative connection interface rather than the main connection interface. For information about configuring encryption support for the administrative interface, see Administrative Interface Support for Encrypted Connections.

The list specified by this variable may include any of the following values:

• ECDHE-ECDSA-AES128-GCM-SHA256
• ECDHE-ECDSA-AES256-GCM-SHA384
• ECDHE-RSA-AES128-GCM-SHA256
• ECDHE-RSA-AES256-GCM-SHA384
• ECDHE-ECDSA-CHACHA20-POLY1305
• ECDHE-RSA-CHACHA20-POLY1305
• ECDHE-ECDSA-AES256-CCM
• ECDHE-ECDSA-AES128-CCM
• DHE-RSA-AES128-GCM-SHA256
• DHE-RSA-AES256-GCM-SHA384
• DHE-RSA-AES256-CCM
• DHE-RSA-AES128-CCM
• DHE-RSA-CHACHA20-POLY1305

Trying to include any values in the cipher list that are not shown here when setting this variable raises an error (ER_BLOCKED_CIPHER). - admin_ssl_crl

The admin_ssl_crl system variable is like ssl_crl, except that it applies to the administrative connection interface rather than the main connection interface. For information about configuring encryption support for the administrative interface, see Administrative Interface Support for Encrypted Connections. - admin_ssl_crlpath

The admin_ssl_crlpath system variable is like ssl_crlpath, except that it applies to the administrative connection interface rather than the main connection interface. For information about configuring encryption support for the administrative interface, see Administrative Interface Support for Encrypted Connections. - admin_ssl_key

The admin_ssl_key system variable is like ssl_key, except that it applies to the administrative connection interface rather than the main connection interface. For information about configuring encryption support for the administrative interface, see Administrative Interface Support for Encrypted Connections. - admin_tls_ciphersuites

The admin_tls_ciphersuites system variable is like tls_ciphersuites, except that it applies to the administrative connection interface rather than the main connection interface. For information about configuring encryption support for the administrative interface, see Administrative Interface Support for Encrypted Connections.

The value is a list of zero or more colon-separated ciphersuite names from among those listed here:

• TLS_AES_128_GCM_SHA256
• TLS_AES_256_GCM_SHA384
• TLS_CHACHA20_POLY1305_SHA256
• TLS_AES_128_CCM_SHA256

Trying to include any values in the cipher list that are not shown here when setting this variable raises an error (ER_BLOCKED_CIPHER). - admin_tls_version

The admin_tls_version system variable is like tls_version, except that it applies to the administrative connection interface rather than the main connection interface. For information about configuring encryption support for the administrative interface, see Administrative Interface Support for Encrypted Connections.

Important

• MySQL 8.4 does not support the TLSv1 and TLSv1.1 connection protocols. See Removal of Support for the TLSv1 and TLSv1.1 Protocols for more information.
• MuySQL 8.4 supports the TLSv1.3 protocol, provided that the MySQL server was compiled using OpenSSL 1.1.1 or newer. The server checks the version of OpenSSL at startup, and if it is older than 1.1.1, TLSv1.3 is removed from the default value for the system variable. In that case, the default is TLSv1.2.
• authentication_policy

This variable is used to administer multifactor authentication (MFA) capabilities. For CREATE USER and ALTER USER statements used to manage MySQL account definitions, it determines what authentication factor or factors may be specified, where “factor” corresponds to an authentication method or plugin associated with an account. authentication_policy determines the following aspects of multifactor authentication:

• The number of authentication factors.
• The plugins (or methods) permitted for each factor.
• The default authentication plugin for authentication specifications that do not name a plugin explicitly.

Because authentication_policy applies only when accounts are created or altered, changes to its value have no effect on existing user accounts.

Note

Although the authentication_policy system variable places certain constraints on the authentication-related clauses of CREATE USER and ALTER USER statements, a user who has the AUTHENTICATION_POLICY_ADMIN privilege is not subject to these constraints. (A warning does occur for statements that otherwise would not be permitted.)

The value of authentication_policy is a list of 1, 2, or 3 comma-separated elements, each corresponding to an authentication factor and each being of one of the forms listed here, with their meanings:

• empty

  The authentication factor is optional; any authentication plugin may be used. - *

  The authentication factor is required; any authentication plugin may be used. - plugin_name

  The authentication factor is required; this factor must be plugin_name. - *:plugin_name

  The authentication factor is required; plugin_name is the default, but another authentication plugin may be used.

In each case, an element may be surrounded by whitespace characters. The entire list must be enclosed in single quotes.

authentication_policy must contain at least one nonempty factor, and any empty factors must come at the end of the list, following any nonempty factors. This means that ',,' is not permitted because this signifies that all factors are optional. Every account must have at least one authentication factor.

The default value of authentication_policy is '*,,'. This means that factor 1 is required in account definitions and can use any authentication plugin (with caching_sha2_password being the default), and that factors 2 and 3 are optional and each can use any authentication plugin.

If authentication_policy does not specify a default plugin for the first factor, the default plugin for this factor is caching_sha2_password, although another plugin may be used.

The following table shows some possible values for authentication_policy and the policy that each establishes for creating or altering accounts.

Table 7.4 Example authentication_policy Values

This variable is available only if the authentication_windows Windows authentication plugin is enabled and debugging code is enabled. See Section 8.4.1.6, “Windows Pluggable Authentication”.

This variable sets the logging level for the Windows authentication plugin. The following table shows the permitted values.

This variable is available only if the authentication_windows Windows authentication plugin is enabled. See Section 8.4.1.6, “Windows Pluggable Authentication”.

A client that authenticates using the InitSecurityContext() function should provide a string identifying the service to which it connects (targetName). MySQL uses the principal name (UPN) of the account under which the server is running. The UPN has the form user_id@computer_name and need not be registered anywhere to be used. This UPN is sent by the server at the beginning of authentication handshake.

This variable controls whether the server sends the UPN in the initial challenge. By default, the variable is enabled. For security reasons, it can be disabled to avoid sending the server's account name to a client as cleartext. If the variable is disabled, the server always sends a 0x00 byte in the first challenge, the client does not specify targetName, and as a result, NTLM authentication is used.

If the server fails to obtain its UPN (which happens primarily in environments that do not support Kerberos authentication), the UPN is not sent by the server and NTLM authentication is used. - autocommit

The autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you must use COMMIT to accept a transaction or ROLLBACK to cancel it. If autocommit is 0 and you change it to 1, MySQL performs an automatic COMMIT of any open transaction. Another way to begin a transaction is to use a START TRANSACTION or BEGIN statement. See Section 15.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”.

By default, client connections begin with autocommit set to 1. To cause clients to begin with a default of 0, set the global autocommit value by starting the server with the --autocommit=0 option. To set the variable using an option file, include these lines:

[mysqld] autocommit=0 - automatic_sp_privileges

When this variable has a value of 1 (the default), the server automatically grants the EXECUTE and ALTER ROUTINE privileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine. (The ALTER ROUTINE privilege is required to drop the routine.) The server also automatically drops those privileges from the creator when the routine is dropped. If automatic_sp_privileges is 0, the server does not automatically add or drop these privileges.

The creator of a routine is the account used to execute the CREATE statement for it. This might not be the same as the account named as the DEFINER in the routine definition.

If you start mysqld with --skip-new, automatic_sp_privileges is set to OFF.

See also Section 27.2.2, “Stored Routines and MySQL Privileges”. - auto_generate_certs

This variable controls whether the server autogenerates SSL key and certificate files in the data directory, if they do not already exist.

At startup, the server automatically generates server-side and client-side SSL certificate and key files in the data directory if the auto_generate_certs system variable is enabled and the server-side SSL files are missing from the data directory. These certificates are always generated in such cases, regardless of the values of any other TLS options. The certificate and key files enable secure client connections using SSL; see Section 8.3.1, “Configuring MySQL to Use Encrypted Connections”.

For more information about SSL file autogeneration, including file names and characteristics, see Section 8.3.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”

The sha256_password_auto_generate_rsa_keys and caching_sha2_password_auto_generate_rsa_keys system variables are related but control autogeneration of RSA key-pair files needed for secure password exchange using RSA over unencrypted connections. - back_log

The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many connection requests in a very short time. It then takes some time (although very little) for the main thread to check the connection and start a new thread. The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect a large number of connections in a short period of time.

In other words, this value is the size of the listen queue for incoming TCP/IP connections. Your operating system has its own limit on the size of this queue. The manual page for the Unix listen() system call should have more details. Check your OS

（本文超出站点单文档体积上限，已截断）