On this page
5.1.7 Server System Variables
The MySQL server maintains many system variables that configure its operation. 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 at runtime using 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.
At runtime, setting a global system variable value requires the SUPER
privilege. Setting a session system variable value normally requires no special privileges and can be done by any user, although there are exceptions. For more information, see Section 5.1.8.1, “System Variable Privileges”
There are several ways to see the names and values of system variables:
To see the values that a server will use 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 will use 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 25.12.13, “Performance Schema System Variable Tables”.
This section provides a description of each system variable. For a system variable summary table, see Section 5.1.4, “Server System Variable Reference”. For more information about manipulation of system variables, see Section 5.1.8, “Using System Variables”.
For additional system variable information, see these sections:
Section 5.1.8, “Using System Variables”, discusses the syntax for setting and displaying system variable values.
Section 5.1.8.2, “Dynamic System Variables”, lists the variables that can be set at runtime.
Information on tuning system variables can be found in Section 5.1.1, “Configuring the Server”.
Section 14.15, “InnoDB Startup Options and System Variables”, lists
InnoDB
system variables.Section 21.3.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 16.1.6, “Replication and Binary Logging Options and Variables”.
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 4.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 will adjust a value upward. For example, if you assign a value of 0 to a variable for which the minimal value is 1024, the server will set the value to 1024.
Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.
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 will be located under /var/mysql/data
. If the value is an absolute path name, its location is as given by the path name.
authentication_windows_log_level
Property Value Command-Line Format --authentication-windows-log-level=#
System Variable authentication_windows_log_level
Scope Global Dynamic No Type Integer Default Value 2
Minimum Value 0
Maximum Value 4
This variable is available only if the
authentication_windows
Windows authentication plugin is enabled and debugging code is enabled. See Section 6.4.1.8, “Windows Pluggable Authentication”.This variable sets the logging level for the Windows authentication plugin. The following table shows the permitted values.
Value Description 0 No logging 1 Log only error messages 2 Log level 1 messages and warning messages 3 Log level 2 messages and information notes 4 Log level 3 messages and debug messages authentication_windows_use_principal_name
Property Value Command-Line Format --authentication-windows-use-principal-name[={OFF|ON}]
System Variable authentication_windows_use_principal_name
Scope Global Dynamic No Type Boolean Default Value ON
This variable is available only if the
authentication_windows
Windows authentication plugin is enabled. See Section 6.4.1.8, “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
and need not be registered anywhere to be used. This UPN is sent by the server at the beginning of authentication handshake.user_id
@computer_name
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 specifytargetName
, and as a result, NTLM authentication is used.If the server fails to obtain its UPN (which will happen primarily in environments that do not support Kerberos authentication), the UPN is not sent by the server and NTLM authentication is used.
-
Property Value Command-Line Format --autocommit[={OFF|ON}]
System Variable autocommit
Scope Global, Session Dynamic Yes Type Boolean Default Value ON
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 orROLLBACK
to cancel it. Ifautocommit
is 0 and you change it to 1, MySQL performs an automaticCOMMIT
of any open transaction. Another way to begin a transaction is to use aSTART TRANSACTION
orBEGIN
statement. See Section 13.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 globalautocommit
value by starting the server with the--autocommit=0
option. To set the variable using an option file, include these lines:[mysqld] autocommit=0
-
Property Value Command-Line Format --automatic-sp-privileges[={OFF|ON}]
System Variable automatic_sp_privileges
Scope Global Dynamic Yes Type Boolean Default Value ON
When this variable has a value of 1 (the default), the server automatically grants the
EXECUTE
andALTER ROUTINE
privileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine. (TheALTER ROUTINE
privilege is required to drop the routine.) The server also automatically drops those privileges from the creator when the routine is dropped. Ifautomatic_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 theDEFINER
in the routine definition.If you start mysqld with
--skip-new
,automatic_sp_privileges
is set toOFF
.See also Section 23.2.2, “Stored Routines and MySQL Privileges”.
-
Property Value Command-Line Format --auto-generate-certs[={OFF|ON}]
System Variable auto_generate_certs
Scope Global Dynamic No Type Boolean Default Value ON
This variable is available if the server was compiled using OpenSSL (see Section 6.3.4, “SSL Library-Dependent Capabilities”). It 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, no SSL options other than--ssl
are specified, and the server-side SSL files are missing from the data directory. These files enable secure client connections using SSL; see Section 6.3.1, “Configuring MySQL to Use Encrypted Connections”.For more information about SSL file autogeneration, including file names and characteristics, see Section 6.3.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”
The
sha256_password_auto_generate_rsa_keys
system variable is related but controls autogeneration of RSA key-pair files needed for secure password exchange using RSA over unencypted connections. -
Property Value Command-Line Format --avoid-temporal-upgrade[={OFF|ON}]
Deprecated Yes System Variable avoid_temporal_upgrade
Scope Global Dynamic Yes Type Boolean Default Value OFF
This variable controls whether
ALTER TABLE
implicitly upgrades temporal columns found to be in pre-5.6.4 format (TIME
,DATETIME
, andTIMESTAMP
columns without support for fractional seconds precision). Upgrading such columns requires a table rebuild, which prevents any use of fast alterations that might otherwise apply to the operation to be performed.This variable is disabled by default. Enabling it causes
ALTER TABLE
not to rebuild temporal columns and thereby be able to take advantage of possible fast alterations.This variable is deprecated and will be removed in a future MySQL release.
-
Property Value Command-Line Format --back-log=#
System Variable back_log
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value 1
Maximum Value 65535
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 documentation for the maximum value for this variable.back_log
cannot be set higher than your operating system limit.The default value is based on the following formula, capped to a limit of 900:
50 + (max_connections / 5)
-
Property Value Command-Line Format --basedir=dir_name
System Variable basedir
Scope Global Dynamic No Type Directory name Default Value configuration-dependent default
The path to the MySQL installation base directory.
-
Property Value Command-Line Format --big-tables[={OFF|ON}]
System Variable big_tables
Scope Global, Session Dynamic Yes Type Boolean Default Value OFF
If enabled, the server stores all temporary tables on disk rather than in memory. This prevents most
The table
errors fortbl_name
is fullSELECT
operations that require a large temporary table, but also slows down queries for which in-memory tables would suffice.The default value for new connections is
OFF
(use in-memory temporary tables). Normally, it should never be necessary to enable this variable because the server is able to handle large result sets automatically by using memory for small temporary tables and switching to disk-based tables as required. -
Property Value Command-Line Format --bind-address=addr
System Variable bind_address
Scope Global Dynamic No Type String Default Value *
The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. To specify an address, set
bind_address=
at server startup, whereaddr
addr
is an IPv4 or IPv6 address or a host name. Ifaddr
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
*
, the server accepts TCP/IP connections on all server host IPv4 interfaces, and, if the server host supports IPv6, on all IPv6 interfaces. Use this address to permit both IPv4 and IPv6 connections on all server interfaces. This value is the default.If the address is
0.0.0.0
, the server accepts TCP/IP connections on all server host IPv4 interfaces.If the address is
::
, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces.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.
If binding to the address fails, the server produces an error and does not start.
If you intend to bind the server to a specific address, be sure that the
mysql.user
system table contains an account with administrative privileges that you can use to connect to that address. Otherwise, you will not be able to shut down the server. For example, if you bind the server to*
, you can connect to it using all existing accounts. But if you bind the server to::1
, it accepts connections only on that address. In that case, first make sure that the'root'@'::1'
account is present in themysql.user
table so you can still connect to the server to shut it down.This variable has no effect for the embedded server (
libmysqld
) and is not visible within the embedded server. -
Property Value Command-Line Format --block-encryption-mode=#
System Variable block_encryption_mode
Scope Global, Session Dynamic Yes Type String Default Value aes-128-ecb
This variable controls the block encryption mode for block-based algorithms such as AES. It affects encryption for
AES_ENCRYPT()
andAES_DECRYPT()
.block_encryption_mode
takes a value inaes-
format, wherekeylen
-mode
keylen
is the key length in bits andmode
is the encryption mode. The value is not case-sensitive. Permittedkeylen
values are 128, 192, and 256. Permitted encryption modes depend on whether MySQL was compiled using OpenSSL or yaSSL:For OpenSSL, permitted
mode
values are:ECB
,CBC
,CFB1
,CFB8
,CFB128
,OFB
For yaSSL, permitted
mode
values are:ECB
,CBC
For example, this statement causes the AES encryption functions to use a key length of 256 bits and the CBC mode:
SET block_encryption_mode = 'aes-256-cbc';
An error occurs for attempts to set
block_encryption_mode
to a value containing an unsupported key length or a mode that the SSL library does not support. -
Property Value Command-Line Format --bulk-insert-buffer-size=#
System Variable bulk_insert_buffer_size
Scope Global, Session Dynamic Yes Type Integer Default Value 8388608
Minimum Value 0
Maximum Value (64-bit platforms) 18446744073709551615
Maximum Value (32-bit platforms) 4294967295
MyISAM
uses a special tree-like cache to make bulk inserts faster forINSERT ... SELECT
,INSERT ... VALUES (...), (...), ...
, andLOAD DATA
when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB. -
Property Value System Variable character_set_client
Scope Global, Session Dynamic Yes Type String Default Value utf8
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. See also Section 10.4, “Connection Character Sets and Collations”.) 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 requests a character set not known to the server. For example, a Japanese-enabled client requests
sjis
when connecting to a server not configured withsjis
support.The client is from a version of MySQL older than MySQL 4.1, and thus does not request a character set.
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.
Some character sets cannot be used as the client character set. Attempting to use them as the
character_set_client
value produces an error. See Impermissible Client Character Sets. -
Property Value System Variable character_set_connection
Scope Global, Session Dynamic Yes Type String Default Value utf8
The character set used for literals specified without a character set introducer and for number-to-string conversion. For information about introducers, see Section 10.3.8, “Character Set Introducers”.
-
Property Value System Variable character_set_database
Scope Global, Session Dynamic Yes Type String Default Value latin1
Footnote This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as
character_set_server
.The global
character_set_database
andcollation_database
system variables are deprecated in MySQL 5.7 and will be removed in a future version of MySQL.Assigning a value to the session
character_set_database
andcollation_database
system variables is deprecated in MySQL 5.7 and assignments produce a warning. The session variables will become read only in a future version of MySQL and assignments will produce an error. It will remain possible to access the session variables to determine the database character set and collation for the default database. -
Property Value Command-Line Format --character-set-filesystem=name
System Variable character_set_filesystem
Scope Global, Session Dynamic Yes Type String Default Value binary
The file system character set. This variable is used to interpret string literals that refer to file names, such as in the
LOAD DATA
andSELECT ... INTO OUTFILE
statements and theLOAD_FILE()
function. Such file names are converted fromcharacter_set_client
tocharacter_set_filesystem
before the file opening attempt occurs. The default value isbinary
, which means that no conversion occurs. For systems on which multibyte file names are permitted, a different value may be more appropriate. For example, if the system represents file names using UTF-8, setcharacter_set_filesystem
to'utf8mb4'
. -
Property Value System Variable character_set_results
Scope Global, Session Dynamic Yes Type String Default Value utf8
The character set used for returning query results to the client. This includes result data such as column values, result metadata such as column names, and error messages.
-
Property Value Command-Line Format --character-set-server=name
System Variable character_set_server
Scope Global, Session Dynamic Yes Type String Default Value latin1
The servers default character set. See Section 10.15, “Character Set Configuration”. If you set this variable, you should also set
collation_server
to specify the collation for the character set. -
Property Value System Variable character_set_system
Scope Global Dynamic No Type String Default Value utf8
The character set used by the server for storing identifiers. The value is always
utf8
. -
Property Value Command-Line Format --character-sets-dir=dir_name
System Variable character_sets_dir
Scope Global Dynamic No Type Directory name The directory where character sets are installed. See Section 10.15, “Character Set Configuration”.
-
Property Value Command-Line Format --check-proxy-users[={OFF|ON}]
System Variable check_proxy_users
Scope Global Dynamic Yes Type Boolean Default Value OFF
Some authentication plugins implement proxy user mapping for themselves (for example, the PAM and Windows authentication plugins). Other authentication plugins do not support proxy users by default. Of these, some can request that the MySQL server itself map proxy users according to granted proxy privileges:
mysql_native_password
,sha256_password
.If the
check_proxy_users
system variable is enabled, the server performs proxy user mapping for any authentication plugins that make such a request. However, it may also be necessary to enable plugin-specific system variables to take advantage of server proxy user mapping support:For the
mysql_native_password
plugin, enablemysql_native_password_proxy_users
.For the
sha256_password
plugin, enablesha256_password_proxy_users
.
For information about user proxying, see Section 6.2.14, “Proxy Users”.
-
Property Value System Variable collation_connection
Scope Global, Session Dynamic Yes Type String The collation of the connection character set.
collation_connection
is important for comparisons of literal strings. For comparisons of strings with column values,collation_connection
does not matter because columns have their own collation, which has a higher collation precedence (see Section 10.8.4, “Collation Coercibility in Expressions”). -
Property Value System Variable collation_database
Scope Global, Session Dynamic Yes Type String Default Value latin1_swedish_ci
Footnote This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. The collation used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as
collation_server
.The global
character_set_database
andcollation_database
system variables are deprecated in MySQL 5.7 and will be removed in a future version of MySQL.Assigning a value to the session
character_set_database
andcollation_database
system variables is deprecated in MySQL 5.7 and assignments produce a warning. The session variables will become read only in a future version of MySQL and assignments will produce an error. It will remain possible to access the session variables to determine the database character set and collation for the default database. -
Property Value Command-Line Format --collation-server=name
System Variable collation_server
Scope Global, Session Dynamic Yes Type String Default Value latin1_swedish_ci
The server's default collation. See Section 10.15, “Character Set Configuration”.
-
Property Value Command-Line Format --completion-type=#
System Variable completion_type
Scope Global, Session Dynamic Yes Type Enumeration Default Value NO_CHAIN
Valid Values NO_CHAIN
CHAIN
RELEASE
0
1
2
The transaction completion type. This variable can take the values shown in the following table. The variable can be assigned using either the name values or corresponding integer values.
Value Description NO_CHAIN
(or 0)COMMIT
andROLLBACK
are unaffected. This is the default value.CHAIN
(or 1)COMMIT
andROLLBACK
are equivalent toCOMMIT AND CHAIN
andROLLBACK AND CHAIN
, respectively. (A new transaction starts immediately with the same isolation level as the just-terminated transaction.)RELEASE
(or 2)COMMIT
andROLLBACK
are equivalent toCOMMIT RELEASE
andROLLBACK RELEASE
, respectively. (The server disconnects after terminating the transaction.)completion_type
affects transactions that begin withSTART TRANSACTION
orBEGIN
and end withCOMMIT
orROLLBACK
. It does not apply to implicit commits resulting from execution of the statements listed in Section 13.3.3, “Statements That Cause an Implicit Commit”. It also does not apply forXA COMMIT
,XA ROLLBACK
, or whenautocommit=1
. -
Property Value Command-Line Format --concurrent-insert[=value]
System Variable concurrent_insert
Scope Global Dynamic Yes Type Enumeration Default Value AUTO
Valid Values NEVER
AUTO
ALWAYS
0
1
2
If
AUTO
(the default), MySQL permitsINSERT
andSELECT
statements to run concurrently forMyISAM
tables that have no free blocks in the middle of the data file.This variable can take the values shown in the following table. The variable can be assigned using either the name values or corresponding integer values.
Value Description NEVER
(or 0)Disables concurrent inserts AUTO
(or 1)(Default) Enables concurrent insert for MyISAM
tables that do not have holesALWAYS
(or 2)Enables concurrent inserts for all MyISAM
tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.If you start mysqld with
--skip-new
,concurrent_insert
is set toNEVER
.See also Section 8.11.3, “Concurrent Inserts”.
-
Property Value Command-Line Format --connect-timeout=#
System Variable connect_timeout
Scope Global Dynamic Yes Type Integer Default Value 10
Minimum Value 2
Maximum Value 31536000
The number of seconds that the mysqld server waits for a connect packet before responding with
Bad handshake
. The default value is 10 seconds.Increasing the
connect_timeout
value might help if clients frequently encounter errors of the formLost connection to MySQL server at '
.XXX
', system error:errno
-
Property Value System Variable core_file
Scope Global Dynamic No Type Boolean Default Value OFF
Whether to write a core file if the server crashes. This variable is set by the
--core-file
option. -
Property Value Command-Line Format --datadir=dir_name
System Variable datadir
Scope Global Dynamic No Type Directory name The path to the MySQL server data directory. Relative paths are resolved with respect to the current directory. If the server will be started automatically (that is, in contexts for which you cannot assume what the current directory will be), it is best to specify the
datadir
value as an absolute path. This variable is unused. It is deprecated and is removed in MySQL 8.0.
This variable is unused. It is deprecated and is removed in MySQL 8.0.
-
Property Value Command-Line Format --debug[=debug_options]
System Variable debug
Scope Global, Session Dynamic Yes Type String Default Value (Windows) d:t:i:O,\mysqld.trace
Default Value (Unix) d:t:i:o,/tmp/mysqld.trace
This variable indicates the current debugging settings. It is available only for servers built with debugging support. The initial value comes from the value of instances of the
--debug
option given at server startup. The global and session values may be set at runtime.Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.8.1, “System Variable Privileges”.
Assigning a value that begins with
+
or-
cause the value to added to or subtracted from the current value:mysql> SET debug = 'T'; mysql> SELECT @@debug; +---------+ | @@debug | +---------+ | T | +---------+ mysql> SET debug = '+P'; mysql> SELECT @@debug; +---------+ | @@debug | +---------+ | P:T | +---------+ mysql> SET debug = '-P'; mysql> SELECT @@debug; +---------+ | @@debug | +---------+ | T | +---------+
For more information, see Section 28.5.3, “The DBUG Package”.
-
Property Value System Variable debug_sync
Scope Session Dynamic Yes Type String This variable is the user interface to the Debug Sync facility. Use of Debug Sync requires that MySQL be configured with the
-DENABLE_DEBUG_SYNC=1
CMake option (see Section 2.9.7, “MySQL Source-Configuration Options”). If Debug Sync is not compiled in, this system variable is not available.The global variable value is read only and indicates whether the facility is enabled. By default, Debug Sync is disabled and the value of
debug_sync
isOFF
. If the server is started with--debug-sync-timeout=
, whereN
N
is a timeout value greater than 0, Debug Sync is enabled and the value ofdebug_sync
isON - current signal
followed by the signal name. Also,N
becomes the default timeout for individual synchronization points.The session value can be read by any user and will have the same value as the global variable. The session value can be set to control synchronization points.
Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.8.1, “System Variable Privileges”.
For a description of the Debug Sync facility and how to use synchronization points, see MySQL Internals: Test Synchronization .
-
Property Value Command-Line Format --default-authentication-plugin=plugin_name
System Variable default_authentication_plugin
Scope Global Dynamic No Type Enumeration Default Value mysql_native_password
Valid Values mysql_native_password
sha256_password
The default authentication plugin. These values are permitted:
mysql_native_password
: Use MySQL native passwords; see Section 6.4.1.1, “Native Pluggable Authentication”.sha256_password
: Use SHA-256 passwords; see Section 6.4.1.5, “SHA-256 Pluggable Authentication”.
NoteIf this variable has a value other than
mysql_native_password
, clients older than MySQL 5.5.7 cannot connect because, of the permitted default authentication plugins, they understand only themysql_native_password
authentication protocol.The
default_authentication_plugin
value affects these aspects of server operation:It determines which authentication plugin the server assigns to new accounts created by
CREATE USER
andGRANT
statements that do not explicitly specify an authentication plugin.The
old_passwords
system variable affects password hashing for accounts that use themysql_native_password
orsha256_password
authentication plugin. If the default authentication plugin is one of those plugins, the server setsold_passwords
at startup to the value required by the plugin password hashing method.For an account created with either of the following statements, the server associates the account with the default authentication plugin and assigns the account the given password, hashed as required by that plugin:
CREATE USER ... IDENTIFIED BY 'cleartext password'; GRANT ... IDENTIFIED BY 'cleartext password';
For an account created with either of the following statements, the server associates the account with the default authentication plugin and assigns the account the given password hash, if the password hash has the format required by the plugin:
CREATE USER ... IDENTIFIED BY PASSWORD 'encrypted password'; GRANT ... IDENTIFIED BY PASSWORD 'encrypted password';
If the password hash is not in the format required by the default authentication plugin, the statement fails.
-
Property Value Command-Line Format --default-password-lifetime=#
System Variable default_password_lifetime
Scope Global Dynamic Yes Type Integer Default Value (≥ 5.7.11) 0
Default Value (≤ 5.7.10) 360
Minimum Value 0
Maximum Value 65535
This variable defines the global automatic password expiration policy. The default
default_password_lifetime
value is 0, which disables automatic password expiration. If the value ofdefault_password_lifetime
is a positive integerN
, it indicates the permitted password lifetime; passwords must be changed everyN
days.The global password expiration policy can be overridden as desired for individual accounts using the password expiration options of the
ALTER USER
statement. See Section 6.2.11, “Password Management”.NotePrior to MySQL 5.7.11, the default
default_password_lifetime
value is 360 (passwords must be changed approximately once per year). For those versions, be aware that, if you make no changes to thedefault_password_lifetime
variable or to individual user accounts, all user passwords will expire after 360 days, and all user accounts will start running in restricted mode when this happens. Clients (which are effectively users) connecting to the server will then get an error indicating that the password must be changed:ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
However, this is easy to miss for clients that automatically connect to the server, such as connections made from scripts. To avoid having such clients suddenly stop working due to a password expiring, make sure to change the password expiration settings for those clients, like this:
ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER
Alternatively, set the
default_password_lifetime
variable to0
, thus disabling automatic password expiration for all users. -
Property Value Command-Line Format --default-storage-engine=name
System Variable default_storage_engine
Scope Global, Session Dynamic Yes Type Enumeration Default Value InnoDB
The default storage engine for tables. See Chapter 15, Alternative Storage Engines. This variable sets the storage engine for permanent tables only. To set the storage engine for
TEMPORARY
tables, set thedefault_tmp_storage_engine
system variable.To see which storage engines are available and enabled, use the
SHOW ENGINES
statement or query theINFORMATION_SCHEMA
ENGINES
table.If you disable the default storage engine at server startup, you must set the default engine for both permanent and
TEMPORARY
tables to a different engine or the server will not start. -
Property Value Command-Line Format --default-tmp-storage-engine=name
System Variable default_tmp_storage_engine
Scope Global, Session Dynamic Yes Type Enumeration Default Value InnoDB
The default storage engine for
TEMPORARY
tables (created withCREATE TEMPORARY TABLE
). To set the storage engine for permanent tables, set thedefault_storage_engine
system variable. Also see the discussion of that variable regarding possible values.If you disable the default storage engine at server startup, you must set the default engine for both permanent and
TEMPORARY
tables to a different engine or the server will not start. -
Property Value Command-Line Format --default-week-format=#
System Variable default_week_format
Scope Global, Session Dynamic Yes Type Integer Default Value 0
Minimum Value 0
Maximum Value 7
The default mode value to use for the
WEEK()
function. See Section 12.6, “Date and Time Functions”. -
Property Value Command-Line Format --delay-key-write[={OFF|ON|ALL}]
System Variable delay_key_write
Scope Global Dynamic Yes Type Enumeration Default Value ON
Valid Values ON
OFF
ALL
This variable specifies how to use delayed key writes. It applies only to
MyISAM
tables. Delayed key writing causes key buffers not to be flushed between writes. See also Section 15.2.1, “MyISAM Startup Options”.This variable can have one of the following values to affect handling of the
DELAY_KEY_WRITE
table option that can be used inCREATE TABLE
statements.Option Description OFF
DELAY_KEY_WRITE
is ignored.ON
MySQL honors any DELAY_KEY_WRITE
option specified inCREATE TABLE
statements. This is the default value.ALL
All new opened tables are treated as if they were created with the DELAY_KEY_WRITE
option enabled.NoteIf you set this variable to
ALL
, you should not useMyISAM
tables from within another program (such as another MySQL server or myisamchk) when the tables are in use. Doing so leads to index corruption.If
DELAY_KEY_WRITE
is enabled for a table, the key buffer is not flushed for the table on every index update, but only when the table is closed. This speeds up writes on keys a lot, but if you use this feature, you should add automatic checking of allMyISAM
tables by starting the server with themyisam_recover_options
system variable set (for example,myisam_recover_options='BACKUP,FORCE'
). See Section 5.1.7, “Server System Variables”, and Section 15.2.1, “MyISAM Startup Options”.If you start mysqld with
--skip-new
,delay_key_write
is set toOFF
.WarningIf you enable external locking with
--external-locking
, there is no protection against index corruption for tables that use delayed key writes. -
Property Value Command-Line Format --delayed-insert-limit=#
Deprecated Yes System Variable delayed_insert_limit
Scope Global Dynamic Yes Type Integer Default Value 100
Minimum Value 1
Maximum Value (64-bit platforms) 18446744073709551615
Maximum Value (32-bit platforms) 4294967295
This system variable is deprecated (because
DELAYED
inserts are not supported), and will be removed in a future release. -
Property Value Command-Line Format --delayed-insert-timeout=#
Deprecated