Table of Contents
- 5.1 The MySQL Server
- 5.1.1 Configuring the Server
- 5.1.2 Server Configuration Defaults
- 5.1.3 Server Configuration Validation
- 5.1.4 Server Option, System Variable, and Status Variable Reference
- 5.1.5 Server System Variable Reference
- 5.1.6 Server Status Variable Reference
- 5.1.7 Server Command Options
- 5.1.8 Server System Variables
- 5.1.9 Using System Variables
- 5.1.10 Server Status Variables
- 5.1.11 Server SQL Modes
- 5.1.12 Connection Management
- 5.1.13 IPv6 Support
- 5.1.14 Network Namespace Support
- 5.1.15 MySQL Server Time Zone Support
- 5.1.16 Resource Groups
- 5.1.17 Server-Side Help Support
- 5.1.18 Server Tracking of Client Session State Changes
- 5.1.19 The Server Shutdown Process
- 5.2 The MySQL Data Directory
- 5.3 The mysql System Schema
- 5.4 MySQL Server Logs
- 5.5 MySQL Components
- 5.6 MySQL Server Plugins
- 5.7 MySQL Server User-Defined Functions
- 5.8 Running Multiple MySQL Instances on One Machine
- 5.9 Debugging MySQL
MySQL Server (mysqld) is the main program that does most of the work in a MySQL installation. This chapter provides an overview of MySQL Server and covers general server administration:
Server configuration
The data directory, particularly the
mysql
system schemaThe server log files
Management of multiple servers on a single machine
For additional information on administrative topics, see also:
- 5.1.1 Configuring the Server
- 5.1.2 Server Configuration Defaults
- 5.1.3 Server Configuration Validation
- 5.1.4 Server Option, System Variable, and Status Variable Reference
- 5.1.5 Server System Variable Reference
- 5.1.6 Server Status Variable Reference
- 5.1.7 Server Command Options
- 5.1.8 Server System Variables
- 5.1.9 Using System Variables
- 5.1.10 Server Status Variables
- 5.1.11 Server SQL Modes
- 5.1.12 Connection Management
- 5.1.13 IPv6 Support
- 5.1.14 Network Namespace Support
- 5.1.15 MySQL Server Time Zone Support
- 5.1.16 Resource Groups
- 5.1.17 Server-Side Help Support
- 5.1.18 Server Tracking of Client Session State Changes
- 5.1.19 The Server Shutdown Process
mysqld is the MySQL server. The following discussion covers these MySQL server configuration topics:
Startup options that the server supports. You can specify these options on the command line, through configuration files, or both.
Server system variables. These variables reflect the current state and values of the startup options, some of which can be modified while the server is running.
Server status variables. These variables contain counters and statistics about runtime operation.
How to set the server SQL mode. This setting modifies certain aspects of SQL syntax and semantics, for example for compatibility with code from other database systems, or to control the error handling for particular situations.
How the server manages client connections.
Configuring and using IPv6 and network namespace support.
Configuring and using time zone support.
Using resource groups.
Server-side help capabilities.
Capabilities provided to enable client session state changes.
The server shutdown process. There are performance and reliability considerations depending on the type of table (transactional or nontransactional) and whether you use replication.
For listings of MySQL server variables and options that have been added, deprecated, or removed in MySQL 8.0, see Section 1.4, “Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.0”.
Not all storage engines are supported by all MySQL server binaries and configurations. To find out how to determine which storage engines your MySQL server installation supports, see Section 13.7.7.16, “SHOW ENGINES Statement”.
The MySQL server, mysqld, has many command options and system variables that can be set at startup to configure its operation. To determine the default command option and system variable values used by the server, execute this command:
shell> mysqld --verbose --help
The command produces a list of all mysqld options and configurable system variables. Its output includes the default option and variable values and looks something like this:
abort-slave-event-count 0 allow-suspicious-udfs FALSE archive ON auto-increment-increment 1 auto-increment-offset 1 autocommit TRUE automatic-sp-privileges TRUE avoid-temporal-upgrade FALSE back-log 80 basedir /home/jon/bin/mysql-8.0/ ... tmpdir /tmp transaction-alloc-block-size 8192 transaction-isolation REPEATABLE-READ transaction-prealloc-size 4096 transaction-read-only FALSE transaction-write-set-extraction OFF updatable-views-with-limit YES validate-user-plugins TRUE verbose TRUE wait-timeout 28800
To see the current system variable values actually used by the server as it runs, connect to it and execute this statement:
mysql> SHOW VARIABLES;
To see some statistical and status indicators for a running server, execute this statement:
mysql> SHOW STATUS;
System variable and status information also is available using the mysqladmin command:
shell>mysqladmin variables
shell>mysqladmin extended-status
For a full description of all command options, system variables, and status variables, see these sections:
More detailed monitoring information is available from the
Performance Schema; see Chapter 27, MySQL Performance Schema. In
addition, the MySQL sys
schema is a set of
objects that provides convenient access to data collected by the
Performance Schema; see Chapter 28, MySQL sys Schema.
If you specify an option on the command line for mysqld or mysqld_safe, it remains in effect only for that invocation of the server. To use the option every time the server runs, put it in an option file. See Section 4.2.2.2, “Using Option Files”.
The MySQL server has many operating parameters, which you can change at server startup using command-line options or configuration files (option files). It is also possible to change many parameters at runtime. For general instructions on setting parameters at startup or runtime, see Section 5.1.7, “Server Command Options”, and Section 5.1.8, “Server System Variables”.
On Windows, MySQL Installer interacts with the user and creates a
file named my.ini
in the base installation
directory as the default option file.
On Windows, the .ini
or
.cnf
option file extension might not be
displayed.
After completing the installation process, you can edit the
default option file at any time to modify the parameters used by
the server. For example, to use a parameter setting in the file
that is commented with a #
character at the
beginning of the line, remove the #
, and modify
the parameter value if necessary. To disable a setting, either add
a #
to the beginning of the line or remove it.
For non-Windows platforms, no default option file is created during either the server installation or the data directory initialization process. Create your option file by following the instructions given in Section 4.2.2.2, “Using Option Files”. Without an option file, the server just starts with its default settings—see Section 5.1.2, “Server Configuration Defaults” on how to check those settings.
For additional information about option file format and syntax, see Section 4.2.2.2, “Using Option Files”.
As of MySQL 8.0.16, MySQL Server supports a
--validate-config
option that
enables the startup configuration to be checked for problems
without running the server in normal operational mode:
mysqld --validate-config
If no errors are found, the server terminates with an exit code of 0. If an error is found, the server displays a diagnostic message and terminates with an exit code of 1. For example:
shell> mysqld --validate-config --no-such-option
2018-11-05T17:50:12.738919Z 0 [ERROR] [MY-000068] [Server] unknown
option '--no-such-option'.
2018-11-05T17:50:12.738962Z 0 [ERROR] [MY-010119] [Server] Aborting
The server terminates as soon as any error is found. For
additional checks to occur, correct the initial problem and run
the server with --validate-config
again.
For the preceding example, where use of
--validate-config
results in
display of an error message, the server exit code is 1. Warning
and information messages may also be displayed, depending on the
log_error_verbosity
value, but do
not produce immediate validation termination or an exit code of 1.
For example, this command produces multiple warnings, both of
which are displayed. But no error occurs, so the exit code is 0:
shell>mysqld --validate-config --log_error_verbosity=2
--read-only=s --transaction_read_only=s
2018-11-05T15:43:18.445863Z 0 [Warning] [MY-000076] [Server] option 'read_only': boolean value 's' was not recognized. Set to OFF. 2018-11-05T15:43:18.445882Z 0 [Warning] [MY-000076] [Server] option 'transaction-read-only': boolean value 's' was not recognized. Set to OFF.
This command produces the same warnings, but also an error, so the error message is displayed along with the warnings and the exit code is 1:
shell>mysqld --validate-config --log_error_verbosity=2
--no-such-option --read-only=s --transaction_read_only=s
2018-11-05T15:43:53.152886Z 0 [Warning] [MY-000076] [Server] option 'read_only': boolean value 's' was not recognized. Set to OFF. 2018-11-05T15:43:53.152913Z 0 [Warning] [MY-000076] [Server] option 'transaction-read-only': boolean value 's' was not recognized. Set to OFF. 2018-11-05T15:43:53.164889Z 0 [ERROR] [MY-000068] [Server] unknown option '--no-such-option'. 2018-11-05T15:43:53.165053Z 0 [ERROR] [MY-010119] [Server] Aborting
The scope of the --validate-config
option is limited to configuration checking that the server can
perform without undergoing its normal startup process. As such,
the configuration check does not initialize storage engines and
other plugins, components, and so forth, and does not validate
options associated with those uninitialized subsystems.
--validate-config
can be used any
time, but is particularly useful after an upgrade, to check
whether any options previously used with the older server are
considered by the upgraded server to be deprecated or obsolete.
For example, the tx_read_only
system variable
was deprecated in MySQL 5.7 and removed in 8.0. Suppose that a
MySQL 5.7 server was run using that system variable in its
my.cnf
file and then upgraded to MySQL 8.0.
Running the upgraded server with
--validate-config
to check the
configuration produces this result:
shell> mysqld --validate-config
2018-11-05T10:40:02.712141Z 0 [ERROR] [MY-000067] [Server] unknown variable
'tx_read_only=ON'.
2018-11-05T10:40:02.712178Z 0 [ERROR] [MY-010119] [Server] Aborting
--validate-config
can be used with
the --defaults-file
option to
validate only the options in a specific file:
shell> mysqld --defaults-file=./my.cnf-test --validate-config
2018-11-05T10:40:02.712141Z 0 [ERROR] [MY-000067] [Server] unknown variable
'tx_read_only=ON'.
2018-11-05T10:40:02.712178Z 0 [ERROR] [MY-010119] [Server] Aborting
Remember that --defaults-file
, if
specified, must be the first option on the command line.
(Executing the preceding example with the option order reversed
produces a message that
--defaults-file
itself is
unknown.)
The following table lists all command-line options, system
variables, and status variables applicable within
mysqld
.
The table lists command-line options (Cmd-line), options valid in configuration files (Option file), server system variables (System Var), and status variables (Status var) in one unified list, with an indication of where each option or variable is valid. If a server option set on the command line or in an option file differs from the name of the corresponding system variable, the variable name is noted immediately below the corresponding option. For system and status variables, the scope of the variable (Var Scope) is Global, Session, or both. Please see the corresponding item descriptions for details on setting and using the options and variables. Where appropriate, direct links to further information about the items are provided.
For a version of this table that is specific to NDB Cluster, see Section 23.3.2.5, “NDB Cluster mysqld Option and Variable Reference”.
Table 5.1 Command-Line Option, System Variable, and Status Variable Summary
Notes:
1. This option is dynamic, but should be set only by server. You should not set this variable manually.
The following table lists all system variables applicable within
mysqld
.
The table lists command-line options (Cmd-line), options valid in configuration files (Option file), server system variables (System Var), and status variables (Status var) in one unified list, with an indication of where each option or variable is valid. If a server option set on the command line or in an option file differs from the name of the corresponding system variable, the variable name is noted immediately below the corresponding option. The scope of the variable (Var Scope) is Global, Session, or both. Please see the corresponding item descriptions for details on setting and using the variables. Where appropriate, direct links to further information about the items are provided.
Table 5.2 System Variable Summary
Notes:
1. This option is dynamic, but should be set only by server. You should not set this variable manually.
The following table lists all status variables applicable within
mysqld
.
The table lists each variable's data type and scope. The last column indicates whether the scope for each variable is Global, Session, or both. Please see the corresponding item descriptions for details on setting and using the variables. Where appropriate, direct links to further information about the items are provided.
Table 5.3 Status Variable Summary
When you start the mysqld server, you can specify program options using any of the methods described in Section 4.2.2, “Specifying Program Options”. The most common methods are to provide options in an option file or on the command line. However, in most cases it is desirable to make sure that the server uses the same options each time it runs. The best way to ensure this is to list them in an option file. See Section 4.2.2.2, “Using Option Files”. That section also describes option file format and syntax.
mysqld reads options from the
[mysqld]
and [server]
groups. mysqld_safe reads options from the
[mysqld]
, [server]
,
[mysqld_safe]
, and
[safe_mysqld]
groups.
mysql.server reads options from the
[mysqld]
and [mysql.server]
groups.
mysqld accepts many command options. For a brief summary, execute this command:
mysqld --help
To see the full list, use this command:
mysqld --verbose --help
Some of the items in the list are actually system variables that
can be set at server startup. These can be displayed at runtime
using the SHOW VARIABLES
statement.
Some items displayed by the preceding mysqld
command do not appear in SHOW
VARIABLES
output; this is because they are options only
and not system variables.
The following list shows some of the most common server options. Additional options are described in other sections:
Options that affect security: See Section 6.1.4, “Security-Related mysqld Options and Variables”.
SSL-related options: See Command Options for Encrypted Connections.
Binary log control options: See Section 5.4.4, “The Binary Log”.
Replication-related options: See Section 17.1.6, “Replication and Binary Logging Options and Variables”.
Options for loading plugins such as pluggable storage engines: See Section 5.6.1, “Installing and Uninstalling Plugins”.
Options specific to particular storage engines: See Section 15.14, “InnoDB Startup Options and System Variables” and Section 16.2.1, “MyISAM Startup Options”.
Some options 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 an option 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 an option 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.
Some options 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 option 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.
You can also set the values of server system variables at server
startup by using variable names as options. To assign a value to a
server system variable, use an option of the form
--
.
For example,
var_name
=value
--sort_buffer_size=384M
sets the
sort_buffer_size
variable to a
value of 384MB.
When you assign a value to a variable, MySQL might automatically correct the value to stay within a given range, or adjust the value to the closest permissible value if only certain values are permitted.
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-
at server startup.
var_name
=value
You can change the values of most system variables at runtime with
the SET
statement. See Section 13.7.6.1, “SET Syntax for Variable Assignment”.
Section 5.1.8, “Server System Variables”, provides a full description for all variables, and additional information for setting them at server startup and runtime. For information on changing system variables, see Section 5.1.1, “Configuring the Server”.
--help
,-?
Command-Line Format --help
Display a short help message and exit. Use both the
--verbose
and--help
options to see the full message.-
Command-Line Format --admin-ssl[={OFF|ON}]
Introduced 8.0.21 Type Boolean Default Value ON
The
--admin-ssl
option is like the--ssl
option, except that it applies to the administrative connection interface rather than the main connection interface. For information about these interfaces, see Section 5.1.12.1, “Connection Interfaces”.The
--admin-ssl
option specifies that the server permits but does not require encrypted connections on the administrative interface. This option is enabled by default.--admin-ssl
can be specified in negated form as--skip-admin-ssl
or a synonym (--admin-ssl=OFF
,--disable-admin-ssl
). In this case, the option specifies that the server does not permit encrypted connections, regardless of the settings of theadmin_tsl_
andxxx
admin_ssl_
system variables.xxx
The
--admin-ssl
option has an effect only at server startup on whether the administrative interface supports encrypted connections. It is ignored and has no effect on the operation ofALTER INSTANCE RELOAD TLS
at runtime. For example, you can use--admin-ssl=OFF
to start the administrative interface with encrypted connections disabled, then reconfigure TLS and executeALTER INSTANCE RELOAD TLS FOR CHANNEL mysql_admin
to enable encrypted connections at runtime.For general information about configuring connection-encryption support, see Section 6.3.1, “Configuring MySQL to Use Encrypted Connections”. That discussion is written for the main connection interface, but the parameter names are similar for the administrative connection interface. Consider setting at least the
admin_ssl_cert
andadmin_ssl_key
system variables on the server side and the--ssl-ca
(or--ssl-capath
) option on the client side. For additional information specifically about the administrative interface, see Administrative Interface Support for Encrypted Connections. -
Command-Line Format --allow-suspicious-udfs[={OFF|ON}]
Type Boolean Default Value OFF
This option controls whether user-defined functions that have only an
xxx
symbol for the main function can be loaded. By default, the option is off and only UDFs that have at least one auxiliary symbol can be loaded; this prevents attempts at loading functions from shared object files other than those containing legitimate UDFs. See UDF Security Precautions. -
Command-Line Format --ansi
Use standard (ANSI) SQL syntax instead of MySQL syntax. For more precise control over the server SQL mode, use the
--sql-mode
option instead. See Section 1.7, “MySQL Standards Compliance”, and Section 5.1.11, “Server SQL Modes”. --basedir=
,dir_name
-b
dir_name
Command-Line Format --basedir=dir_name
System Variable basedir
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Directory name Default Value parent of mysqld installation directory
The path to the MySQL installation directory. This option sets the
basedir
system variable.The server executable determines its own full path name at startup and uses the parent of the directory in which it is located as the default
basedir
value. This in turn enables the server to use thatbasedir
when searching for server-related information such as theshare
directory containing error messages.--character-set-client-handshake
Command-Line Format --character-set-client-handshake[={OFF|ON}]
Type Boolean Default Value ON
Do not ignore character set information sent by the client. To ignore client information and use the default server character set, use
--skip-character-set-client-handshake
; this makes MySQL behave like MySQL 4.0.--chroot=
,dir_name
-r
dir_name
Command-Line Format --chroot=dir_name
Type Directory name Put the mysqld server in a closed environment during startup by using the
chroot()
system call. This is a recommended security measure. Use of this option somewhat limitsLOAD DATA
andSELECT ... INTO OUTFILE
.-
Command-Line Format --console
Platform Specific Windows (Windows only.) Cause the default error log destination to be the console. This affects log sinks that base their own output destination on the default destination. See Section 5.4.2, “The Error Log”. mysqld does not close the console window if this option is used.
--console
takes precedence over--log-error
if both are given. -
Command-Line Format --core-file[={OFF|ON}]
Type Boolean Default Value OFF
Write a core file if mysqld dies. The name and location of the core file is system dependent. On Linux, a core file named
core.
is written to the current working directory of the process, which for mysqld is the data directory.pid
pid
represents the process ID of the server process. On macOS, a core file namedcore.
is written to thepid
/cores
directory. On Solaris, use the coreadm command to specify where to write the core file and how to name it.For some systems, to get a core file you must also specify the
--core-file-size
option to mysqld_safe. See Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”. On some systems, such as Solaris, you do not get a core file if you are also using the--user
option. There might be additional restrictions or limitations. For example, it might be necessary to execute ulimit -c unlimited before starting the server. Consult your system documentation.The
innodb_buffer_pool_in_core_file
variable can be used to reduce the size of core files on operating systems that support it. For more information, see Section 15.8.3.7, “Excluding Buffer Pool Pages from Core Files”. --daemonize
,-D
Command-Line Format --daemonize[={OFF|ON}]
Type Boolean Default Value OFF
This option causes the server to run as a traditional, forking daemon, permitting it to work with operating systems that use systemd for process control. For more information, see Section 2.5.9, “Managing MySQL Server with systemd”.
--daemonize
is mutually exclusive with--initialize
and--initialize-insecure
.If the server is started using the
--daemonize
option and is not connected to a tty device, a default error logging option of--log-error=""
is used in the absence of an explicit logging option, to direct error output to the default log file.-D
is a synonym for--daemonize
.--datadir=
,dir_name
-h
dir_name
Command-Line Format --datadir=dir_name
System Variable datadir
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Directory name The path to the MySQL server data directory. This option sets the
datadir
system variable. See the description of that variable.--debug[=
,debug_options
]-# [
debug_options
]Command-Line Format --debug[=debug_options]
System Variable debug
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value (Windows) d:t:i:O,\mysqld.trace
Default Value (Unix) d:t:i:o,/tmp/mysqld.trace
If MySQL is configured with the
-DWITH_DEBUG=1
CMake option, you can use this option to get a trace file of what mysqld is doing. A typicaldebug_options
string isd:t:o,
. The default isfile_name
d:t:i:o,/tmp/mysqld.trace
on Unix andd:t:i:O,\mysqld.trace
on Windows.Using
-DWITH_DEBUG=1
to configure MySQL with debugging support enables you to use the--debug="d,parser_debug"
option when you start the server. This causes the Bison parser that is used to process SQL statements to dump a parser trace to the server's standard error output. Typically, this output is written to the error log.This option may be given multiple times. Values that begin with
+
or-
are added to or subtracted from the previous value. For example,--debug=T
--debug=+P
sets the value toP:T
.For more information, see Section 5.9.4, “The DBUG Package”.
-
Command-Line Format --debug-sync-timeout[=#]
Type Integer Controls whether the Debug Sync facility for testing and debugging is enabled. 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 option is not available. The option value is a timeout in seconds. The default value is 0, which disables Debug Sync. To enable it, specify a value greater than 0; this value also becomes the default timeout for individual synchronization points. If the option is given without a value, the timeout is set to 300 seconds.For a description of the Debug Sync facility and how to use synchronization points, see MySQL Internals: Test Synchronization.
-
Command-Line Format --default-time-zone=name
Type String Set the default server time zone. This option sets the global
time_zone
system variable. If this option is not given, the default time zone is the same as the system time zone (given by the value of thesystem_time_zone
system variable. --defaults-extra-file=
file_name
Read this option file after the global option file but (on Unix) before the user option file. If the file does not exist or is otherwise inaccessible, an error occurs.
file_name
is interpreted relative to the current directory if given as a relative path name rather than a full path name. This must be the first option on the command line if it is used.For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.
Read only the given option file. If the file does not exist or is otherwise inaccessible, an error occurs.
file_name
is interpreted relative to the current directory if given as a relative path name rather than a full path name.Exception: Even with
--defaults-file
, mysqld readsmysqld-auto.cnf
.NoteThis must be the first option on the command line if it is used, except that if the server is started with the
--defaults-file
and--install
(or--install-manual
) options,--install
(or--install-manual
) must be first.For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.
Read not only the usual option groups, but also groups with the usual names and a suffix of
str
. For example, mysqld normally reads the[mysqld]
group. If the--defaults-group-suffix=_other
option is given, mysqld also reads the[mysqld_other]
group.For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.
--early-plugin-load=
plugin_list
Command-Line Format --early-plugin-load=plugin_list
Type String Default Value empty string
This option tells the server which plugins to load before loading mandatory built-in plugins and before storage engine initialization. If multiple
--early-plugin-load
options are given, only the last one is used.The option value is a semicolon-separated list of
name
=
plugin_library
andplugin_library
values. Eachname
is the name of a plugin to load, andplugin_library
is the name of the library file that contains the plugin code. If a plugin library is named without any preceding plugin name, the server loads all plugins in the library. The server looks for plugin library files in the directory named by theplugin_dir
system variable.For example, if plugins named
myplug1
andmyplug2
have library filesmyplug1.so
andmyplug2.so
, use this option to perform an early plugin load:shell>
mysqld --early-plugin-load="myplug1=myplug1.so;myplug2=myplug2.so"
Quotes are used around the argument value because otherwise a semicolon (
;
) is interpreted as a special character by some command interpreters. (Unix shells treat it as a command terminator, for example.)Each named plugin is loaded early for a single invocation of mysqld only. After a restart, the plugin is not loaded early unless
--early-plugin-load
is used again.If the server is started using
--initialize
or--initialize-insecure
, plugins specified by--early-plugin-load
are not loaded.If the server is run with
--help
, plugins specified by--early-plugin-load
are loaded but not initialized. This behavior ensures that plugin options are displayed in the help message.The default
--early-plugin-load
value is empty. To load thekeyring_file
plugin, you must use an explicit--early-plugin-load
option with a nonempty value.The
InnoDB
tablespace encryption feature relies on thekeyring_file
plugin for encryption key management, and thekeyring_file
plugin must be loaded prior to storage engine initialization to facilitateInnoDB
recovery for encrypted tables. Administrators who want thekeyring_file
plugin loaded at startup should use the appropriate nonempty option value (for example,keyring_file.so
on Unix and Unix-like systems andkeyring_file.dll
on Windows).For information about
InnoDB
tablespace encryption, see Section 15.13, “InnoDB Data-at-Rest Encryption”. For general information about plugin loading, see Section 5.6.1, “Installing and Uninstalling Plugins”.--exit-info[=
,flags
]-T [
flags
]Command-Line Format --exit-info[=flags]
Type Integer This is a bitmask of different flags that you can use for debugging the mysqld server. Do not use this option unless you know exactly what it does!
-
Command-Line Format --external-locking[={OFF|ON}]
Type Boolean Default Value OFF
Enable external locking (system locking), which is disabled by default. If you use this option on a system on which
lockd
does not fully work (such as Linux), it is easy for mysqld to deadlock.To disable external locking explicitly, use
--skip-external-locking
.External locking affects only
MyISAM
table access. For more information, including conditions under which it can and cannot be used, see Section 8.11.5, “External Locking”. -
Command-Line Format --flush[={OFF|ON}]
System Variable flush
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
Flush (synchronize) all changes to disk after each SQL statement. Normally, MySQL does a write of all changes to disk only after each SQL statement and lets the operating system handle the synchronizing to disk. See Section B.3.3.3, “What to Do If MySQL Keeps Crashing”.
NoteIf
--flush
is specified, the value offlush_time
does not matter and changes toflush_time
have no effect on flush behavior. -
Command-Line Format --gdb[={OFF|ON}]
Type Boolean Default Value OFF
Install an interrupt handler for
SIGINT
(needed to stop mysqld with^C
to set breakpoints) and disable stack tracing and core file handling. See Section 5.9.1.4, “Debugging mysqld under gdb”.On Windows, this option also suppresses the forking that is used to implement the
RESTART
statement: Forking enables one process to act as a monitor to the other, which acts as the server. However, forking makes determining the server process to attach to for debugging more difficult, so starting the server with--gdb
suppresses forking. For a server started with this option,RESTART
simply exits and does not restart.In non-debug settings,
--no-monitor
may be used to suppress forking the monitor process. --initialize
,-I
Command-Line Format --initialize[={OFF|ON}]
Type Boolean Default Value OFF
This option is used to initialize a MySQL installation by creating the data directory and populating the tables in the
mysql
system schema. For more information, see Section 2.10.1, “Initializing the Data Directory”.When the server is started with
--initialize
, some functionality is unavailable that limits the statements permitted in any file named by theinit_file
system variable. For more information, see the description of that variable. In addition, thedisabled_storage_engines
system variable has no effect.The
--ndbcluster
option is ignored when used together with--initialize
.--initialize
is mutually exclusive with--daemonize
.-I
is a synonym for--initialize
.-
Command-Line Format --initialize-insecure[={OFF|ON}]
Type Boolean Default Value OFF
This option is used to initialize a MySQL installation by creating the data directory and populating the tables in the
mysql
system schema. This option implies--initialize
. For more information, see the description of that option, and Section 2.10.1, “Initializing the Data Directory”.--initialize-insecure
is mutually exclusive with--daemonize
. --innodb-
xxx
Set an option for the
InnoDB
storage engine. TheInnoDB
options are listed in Section 15.14, “InnoDB Startup Options and System Variables”.-
Command-Line Format --install [service_name]
Platform Specific Windows (Windows only) Install the server as a Windows service that starts automatically during Windows startup. The default service name is
MySQL
if noservice_name
value is given. For more information, see Section 2.3.4.8, “Starting MySQL as a Windows Service”.NoteIf the server is started with the
--defaults-file
and--install
options,--install
must be first. --install-manual [
service_name
]Command-Line Format --install-manual [service_name]
Platform Specific Windows (Windows only) Install the server as a Windows service that must be started manually. It does not start automatically during Windows startup. The default service name is
MySQL
if noservice_name
value is given. For more information, see Section 2.3.4.8, “Starting MySQL as a Windows Service”.NoteIf the server is started with the
--defaults-file
and--install-manual
options,--install-manual
must be first.--language=
lang_name
, -Llang_name
Command-Line Format --language=name
Deprecated Yes; use lc-messages-dir
insteadSystem Variable language
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Directory name Default Value /usr/local/mysql/share/mysql/english/
The language to use for error messages.
lang_name
can be given as the language name or as the full path name to the directory where the language files are installed. See Section 10.12, “Setting the Error Message Language”.--lc-messages-dir
and--lc-messages
should be used rather than--language
, which is deprecated (and handled as a synonym for--lc-messages-dir
). You should expect the--language
option to be removed in a future MySQL release.-
Command-Line Format --large-pages[={OFF|ON}]
System Variable large_pages
Scope Global Dynamic No SET_VAR
Hint AppliesNo Platform Specific Linux Type Boolean Default Value OFF
Some hardware/operating system architectures support memory pages greater than the default (usually 4KB). The actual implementation of this support depends on the underlying hardware and operating system. Applications that perform a lot of memory accesses may obtain performance improvements by using large pages due to reduced Translation Lookaside Buffer (TLB) misses.
MySQL supports the Linux implementation of large page support (which is called HugeTLB in Linux). See Section 8.12.3.2, “Enabling Large Page Support”. For Solaris support of large pages, see the description of the
--super-large-pages
option.--large-pages
is disabled by default. -
Command-Line Format --lc-messages=name
System Variable lc_messages
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value en_US
The locale to use for error messages. The default is
en_US
. The server converts the argument to a language name and combines it with the value of--lc-messages-dir
to produce the location for the error message file. See Section 10.12, “Setting the Error Message Language”. -
Command-Line Format --lc-messages-dir=dir_name
System Variable lc_messages_dir
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Directory name The directory where error messages are located. The server uses the value together with the value of
--lc-messages
to produce the location for the error message file. See Section 10.12, “Setting the Error Message Language”. -
Command-Line Format --local-service
(Windows only) A
--local-service
option following the service name causes the server to run using theLocalService
Windows account that has limited system privileges. If both--defaults-file
and--local-service
are given following the service name, they can be in any order. See Section 2.3.4.8, “Starting MySQL as a Windows Service”. -
Command-Line Format --log-error[=file_name]
System Variable log_error
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type File name Set the default error log destination to the named file. This affects log sinks that base their own output destination on the default destination. See Section 5.4.2, “The Error Log”.
If the option names no file, the default error log destination on Unix and Unix-like systems is a file named
in the data directory. The default destination on Windows is the same, unless thehost_name
.err--pid-file
option is specified. In that case, the file name is the PID file base name with a suffix of.err
in the data directory.If the option names a file, the default destination is that file (with an
.err
suffix added if the name has no suffix), located under the data directory unless an absolute path name is given to specify a different location.If error log output cannot be redirected to the error log file, an error occurs and startup fails.
On Windows,
--console
takes precedence over--log-error
if both are given. In this case, the default error log destination is the console rather than a file. -
Command-Line Format --log-isam[=file_name]
Type File name Log all
MyISAM
changes to this file (used only when debuggingMyISAM
). -
Command-Line Format --log-raw[={OFF|ON}]
System Variable (≥ 8.0.19) log_raw
Scope (≥ 8.0.19) Global Dynamic (≥ 8.0.19) Yes SET_VAR
Hint Applies (≥ 8.0.19)No Type Boolean Default Value OFF
Passwords in certain statements written to the general query log, slow query log, and binary log are rewritten by the server not to occur literally in plain text. Password rewriting can be suppressed for the general query log by starting the server with the
--log-raw
option. This option may be useful for diagnostic purposes, to see the exact text of statements as received by the server, but for security reasons is not recommended for production use.If a query rewrite plugin is installed, the
--log-raw
option affects statement logging as follows:For more information, see Section 6.1.2.3, “Passwords and Logging”.
-
Command-Line Format --log-short-format[={OFF|ON}]
Type Boolean Default Value OFF
Log less information to the slow query log, if it has been activated.
-
Command-Line Format --log-tc=file_name
Type File name Default Value tc.log
The name of the memory-mapped transaction coordinator log file (for XA transactions that affect multiple storage engines when the binary log is disabled). The default name is
tc.log
. The file is created under the data directory if not given as a full path name. This option is unused. -
Command-Line Format --log-tc-size=#
Type Integer Default Value 6 * page size
Minimum Value 6 * page size
Maximum Value (64-bit platforms) 18446744073709551615
Maximum Value (32-bit platforms) 4294967295
The size in bytes of the memory-mapped transaction coordinator log. The default and minimum values are 6 times the page size, and the value must be a multiple of the page size.
-
Command-Line Format --memlock[={OFF|ON}]
Type Boolean Default Value OFF
Lock the mysqld process in memory. This option might help if you have a problem where the operating system is causing mysqld to swap to disk.
--memlock
works on systems that support themlockall()
system call; this includes Solaris, most Linux distributions that use a 2.4 or higher kernel, and perhaps other Unix systems. On Linux systems, you can tell whether or notmlockall()
(and thus this option) is supported by checking to see whether or not it is defined in the systemmman.h
file, like this:shell>
grep mlockall /usr/include/sys/mman.h
If
mlockall()
is supported, you should see in the output of the previous command something like the following:extern int mlockall (int __flags) __THROW;
ImportantUse of this option may require you to run the server as
root
, which, for reasons of security, is normally not a good idea. See Section 6.1.5, “How to Run MySQL as a Normal User”.On Linux and perhaps other systems, you can avoid the need to run the server as
root
by changing thelimits.conf
file. See the notes regarding the memlock limit in Section 8.12.3.2, “Enabling Large Page Support”.You must not use this option on a system that does not support the
mlockall()
system call; if you do so, mysqld is very likely to exit as soon as you try to start it. -
Command-Line Format --myisam-block-size=#
Type Integer Default Value 1024
Minimum Value 1024
Maximum Value 16384
The block size to be used for
MyISAM
index pages. Do not read any option files. If program startup fails due to reading unknown options from an option file,
--no-defaults
can be used to prevent them from being read. This must be the first option on the command line if it is used.For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.
-
Command-Line Format --no-dd-upgrade[={OFF|ON}]
Deprecated 8.0.16 Type Boolean Default Value OFF
NoteThis option is deprecated as of MySQL 8.0.16. It is superseded by the
--upgrade
option, which provides finer control over data dictionary and server upgrade behavior.Prevent automatic upgrade of the data dictionary tables during the MySQL server startup process. This option is typically used when starting the MySQL server following an in-place upgrade of an existing installation to a newer MySQL version, which may include changes to data dictionary table definitions.
When
--no-dd-upgrade
is specified, and the server finds that its expected version of the data dictionary differs from the version stored in the data dictionary itself, startup fails with an error stating that data dictionary upgrade is prohibited;[ERROR] [MY-011091] [Server] Data dictionary upgrade prohibited by the command line option '--no_dd_upgrade'. [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
During a normal startup, the data dictionary version of the server is compared to the version stored in the data dictionary to determine whether data dictionary table definitions should be upgraded. If an upgrade is necessary and supported, the server creates data dictionary tables with updated definitions, copies persisted metadata to the new tables, atomically replaces the old tables with the new ones, and reinitializes the data dictionary. If an upgrade is not necessary, startup continues without updating data dictionary tables.
-
Command-Line Format --no-monitor[={OFF|ON}]
Introduced 8.0.12 Platform Specific Windows Type Boolean Default Value OFF
(Windows only). This option suppresses the forking that is used to implement the
RESTART
statement: Forking enables one process to act as a monitor to the other, which acts as the server. For a server started with this option,RESTART
simply exits and does not restart.--no-monitor
is not available prior to MySQL 8.0.12. The--gdb
option can be used as a workaround. -
Command-Line Format --old-style-user-limits[={OFF|ON}]
Type Boolean Default Value OFF
Enable old-style user limits. (Before MySQL 5.0.3, account resource limits were counted separately for each host from which a user connected rather than per account row in the
user
table.) See Section 6.2.20, “Setting Account Resource Limits”. --performance-schema-xxx
Configure a Performance Schema option. For details, see Section 27.14, “Performance Schema Command Options”.
-
Command-Line Format --plugin-load=plugin_list
System Variable plugin_load
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type String This option tells the server to load the named plugins at startup. If multiple
--plugin-load
options are given, only the last one is used. Additional plugins to load may be specified using--plugin-load-add
options.The option value is a semicolon-separated list of
name
=
plugin_library
andplugin_library
values. Eachname
is the name of a plugin to load, andplugin_library
is the name of the library file that contains the plugin code. If a plugin library is named without any preceding plugin name, the server loads all plugins in the library. The server looks for plugin library files in the directory named by theplugin_dir
system variable.For example, if plugins named
myplug1
andmyplug2
have library filesmyplug1.so
andmyplug2.so
, use this option to perform an early plugin load:shell>
mysqld --plugin-load="myplug1=myplug1.so;myplug2=myplug2.so"
Quotes are used around the argument value here because otherwise semicolon (
;
) is interpreted as a special character by some command interpreters. (Unix shells treat it as a command terminator, for example.)Each named plugin is loaded for a single invocation of mysqld only. After a restart, the plugin is not loaded unless
--plugin-load
is used again. This is in contrast toINSTALL PLUGIN
, which adds an entry to themysql.plugins
table to cause the plugin to be loaded for every normal server startup.During the normal startup sequence, the server determines which plugins to load by reading the
mysql.plugins
system table. If the server is started with the--skip-grant-tables
option, plugins registered in themysql.plugins
table are not loaded and are unavailable.--plugin-load
enables plugins to be loaded even when--skip-grant-tables
is given.--plugin-load
also enables plugins to be loaded at startup that cannot be loaded at runtime.For additional information about plugin loading, see Section 5.6.1, “Installing and Uninstalling Plugins”.
-
Command-Line Format --plugin-load-add=plugin_list
System Variable plugin_load_add
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type String This option complements the
--plugin-load
option.--plugin-load-add
adds a plugin or plugins to the set of plugins to be loaded at startup. The argument format is the same as for--plugin-load
.--plugin-load-add
can be used to avoid specifying a large set of plugins as a single long unwieldy--plugin-load
argument.--plugin-load-add
can be given in the absence of--plugin-load
, but any instance of--plugin-load-add
that appears before--plugin-load
. has no effect because--plugin-load
resets the set of plugins to load. In other words, these options:--plugin-load=x --plugin-load-add=y
are equivalent to this option:
--plugin-load="x;y"
But these options:
--plugin-load-add=y --plugin-load=x
are equivalent to this option:
--plugin-load=x
For additional information about plugin loading, see Section 5.6.1, “Installing and Uninstalling Plugins”.
Specifies an option that pertains to a server plugin. For example, many storage engines can be built as plugins, and for such engines, options for them can be specified with a
--plugin
prefix. Thus, the--innodb-file-per-table
option forInnoDB
can be specified as--plugin-innodb-file-per-table
.For boolean options that can be enabled or disabled, the
--skip
prefix and other alternative formats are supported as well (see Section 4.2.2.4, “Program Option Modifiers”). For example,--skip-plugin-innodb-file-per-table
disablesinnodb-file-per-table
.The rationale for the
--plugin
prefix is that it enables plugin options to be specified unambiguously if there is a name conflict with a built-in server option. For example, were a plugin writer to name a plugin “sql” and implement a “mode” option, the option name might be--sql-mode
, which would conflict with the built-in option of the same name. In such cases, references to the conflicting name are resolved in favor of the built-in option. To avoid the ambiguity, users can specify the plugin option as--plugin-sql-mode
. Use of the--plugin
prefix for plugin options is recommended to avoid any question of ambiguity.--port=
,port_num
-P
port_num
Command-Line Format --port=port_num
System Variable port
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Integer Default Value 3306
Minimum Value 0
Maximum Value 65535
The port number to use when listening for TCP/IP connections. On Unix and Unix-like systems, the port number must be 1024 or higher unless the server is started by the
root
operating system user. Setting this option to 0 causes the default value to be used.-
Command-Line Format --port-open-timeout=#
Type Integer Default Value 0
On some systems, when the server is stopped, the TCP/IP port might not become available immediately. If the server is restarted quickly afterward, its attempt to reopen the port can fail. This option indicates how many seconds the server should wait for the TCP/IP port to become free if it cannot be opened. The default is not to wait.
Print the program name and all options that it gets from option files. Password values are masked. This must be the first option on the command line if it is used, except that it may be used immediately after
--defaults-file
or--defaults-extra-file
.For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.
-
Command-Line Format --remove [service_name]
Platform Specific Windows (Windows only) Remove a MySQL Windows service. The default service name is
MySQL
if noservice_name
value is given. For more information, see Section 2.3.4.8, “Starting MySQL as a Windows Service”. -
Command-Line Format --safe-user-create[={OFF|ON}]
Type Boolean Default Value OFF
If this option is enabled, a user cannot create new MySQL users by using the
GRANT
statement unless the user has theINSERT
privilege for themysql.user
system table or any column in the table. If you want a user to have the ability to create new users that have those privileges that the user has the right to grant, you should grant the user the following privilege:GRANT INSERT(user) ON mysql.user TO '
user_name
'@'host_name
';This ensures that the user cannot change any privilege columns directly, but has to use the
GRANT
statement to give privileges to other users. -
Command-Line Format --skip-grant-tables[={OFF|ON}]
Type Boolean Default Value OFF
This option affects the server startup sequence:
--skip-grant-tables
causes the server not to read the grant tables in themysql
system schema, and thus to start without using the privilege system at all. This gives anyone with access to the server unrestricted access to all databases.Because starting the server with
--skip-grant-tables
disables authentication checks, the server also disables remote connections in that case by enablingskip_networking
.To cause a server started with
--skip-grant-tables
to load the grant tables at runtime, perform a privilege-flushing operation, which can be done in these ways:Issue a MySQL
FLUSH PRIVILEGES
statement after connecting to the server.Execute a mysqladmin flush-privileges or mysqladmin reload command from the command line.
Privilege flushing might also occur implicitly as a result of other actions performed after startup, thus causing the server to start using the grant tables. For example, the server flushes the privileges if it performs an upgrade during the startup sequence.
--skip-grant-tables
disables failed-login tracking and temporary account locking because those capabilities depend on the grant tables. See Section 6.2.15, “Password Management”.--skip-grant-tables
causes the server not to load certain other objects registered in the data dictionary or themysql
system schema:Scheduled events installed using
CREATE EVENT
and registered in theevents
data dictionary table.Plugins installed using
INSTALL PLUGIN
and registered in themysql.plugin
system table.To cause plugins to be loaded even when using
--skip-grant-tables
, use the--plugin-load
or--plugin-load-add
option.User-defined functions (UDFs) installed using
CREATE FUNCTION
and registered in themysql.func
system table.
--skip-grant-tables
does not suppress loading during startup of components.--skip-grant-tables
causes thedisabled_storage_engines
system variable to have no effect.
-
Command-Line Format --skip-host-cache
Disable use of the internal host cache for faster name-to-IP resolution. With the cache disabled, the server performs a DNS lookup every time a client connects.
Use of
--skip-host-cache
is similar to setting thehost_cache_size
system variable to 0, buthost_cache_size
is more flexible because it can also be used to resize, enable, or disable the host cache at runtime, not just at server startup.Starting the server with
--skip-host-cache
does not prevent runtime changes to the value ofhost_cache_size
, but such changes have no effect and the cache is not re-enabled even ifhost_cache_size
is set larger than 0.For more information about how the host cache works, see Section 5.1.12.3, “DNS Lookups and the Host Cache”.
Disable the
InnoDB
storage engine. In this case, because the default storage engine isInnoDB
, the server does not start unless you also use--default-storage-engine
and--default-tmp-storage-engine
to set the default to some other engine for both permanent andTEMPORARY
tables.The
InnoDB
storage engine cannot be disabled, and the--skip-innodb
option is deprecated and has no effect. Its use results in a warning. Expect this option to be removed in a future MySQL release.-
Command-Line Format --skip-new
This option disables (what used to be considered) new, possibly unsafe behaviors. It results in these settings:
delay_key_write=OFF
,concurrent_insert=NEVER
,automatic_sp_privileges=OFF
. It also causesOPTIMIZE TABLE
to be mapped toALTER TABLE
for storage engines for whichOPTIMIZE TABLE
is not supported. -
Command-Line Format --skip-show-database
System Variable skip_show_database
Scope Global Dynamic No SET_VAR
Hint AppliesNo This option sets the
skip_show_database
system variable that controls who is permitted to use theSHOW DATABASES
statement. See Section 5.1.8, “Server System Variables”. -
Command-Line Format --skip-stack-trace
Do not write stack traces. This option is useful when you are running mysqld under a debugger. On some systems, you also must use this option to get a core file. See Section 5.9, “Debugging MySQL”.
-
Command-Line Format --slow-start-timeout=#
Type Integer Default Value 15000
This option controls the Windows service control manager's service start timeout. The value is the maximum number of milliseconds that the service control manager waits before trying to kill the windows service during startup. The default value is 15000 (15 seconds). If the MySQL service takes too long to start, you may need to increase this value. A value of 0 means there is no timeout.
-
Command-Line Format --socket={file_name|pipe_name}
System Variable socket
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type String Default Value (Other) /tmp/mysql.sock
Default Value (Windows) MySQL
On Unix, this option specifies the Unix socket file to use when listening for local connections. The default value is
/tmp/mysql.sock
. If this option is given, the server creates the file in the data directory unless an absolute path name is given to specify a different directory. On Windows, the option specifies the pipe name to use when listening for local connections that use a named pipe. The default value isMySQL
(not case-sensitive). --sql-mode=
value
[,value
[,value
...]]Command-Line Format --sql-mode=name
System Variable sql_mode
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesYes Type Set Default Value ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION
Valid Values ALLOW_INVALID_DATES
ANSI_QUOTES
ERROR_FOR_DIVISION_BY_ZERO
HIGH_NOT_PRECEDENCE
IGNORE_SPACE
NO_AUTO_VALUE_ON_ZERO
NO_BACKSLASH_ESCAPES
NO_DIR_IN_CREATE
NO_ENGINE_SUBSTITUTION
NO_UNSIGNED_SUBTRACTION
NO_ZERO_DATE
NO_ZERO_IN_DATE
ONLY_FULL_GROUP_BY
PAD_CHAR_TO_FULL_LENGTH
PIPES_AS_CONCAT
REAL_AS_FLOAT
STRICT_ALL_TABLES
STRICT_TRANS_TABLES
TIME_TRUNCATE_FRACTIONAL
Set the SQL mode. See Section 5.1.11, “Server SQL Modes”.
NoteMySQL installation programs may configure the SQL mode during the installation process.
If the SQL mode differs from the default or from what you expect, check for a setting in an option file that the server reads at startup.
-
Command-Line Format --ssl[={OFF|ON}]
Disabled by skip-ssl
Type Boolean Default Value ON
The
--ssl
option specifies that the server permits but does not require encrypted connections. This option is enabled by default.--ssl
can be specified in negated form as--skip-ssl
or a synonym (--ssl=OFF
,--disable-ssl
). In this case, the option specifies that the server does not permit encrypted connections, regardless of the settings of thetls_
andxxx
ssl_
system variables.xxx
The
--ssl
option has an effect only at server startup on whether the server supports encrypted connections. It is ignored and has no effect on the operation ofALTER INSTANCE RELOAD TLS
at runtime. For example, you can use--ssl=OFF
to start the server with encrypted connections disabled, then reconfigure TLS and executeALTER INSTANCE RELOAD TLS
to enable encrypted connections at runtime.For more information about configuring whether the server permits clients to connect using SSL and indicating where to find SSL keys and certificates, see Section 6.3.1, “Configuring MySQL to Use Encrypted Connections”, which also describes server capabilities for certificate and key file autogeneration and autodiscovery. Consider setting at least the
ssl_cert
andssl_key
system variables on the server side and the--ssl-ca
(or--ssl-capath
) option on the client side. -
Command-Line Format --standalone
Platform Specific Windows Available on Windows only; instructs the MySQL server not to run as a service.
-
Command-Line Format --super-large-pages[={OFF|ON}]
Platform Specific Solaris Type Boolean Default Value OFF
Standard use of large pages in MySQL attempts to use the largest size supported, up to 4MB. Under Solaris, a “super large pages” feature enables uses of pages up to 256MB. This feature is available for recent SPARC platforms. It can be enabled or disabled by using the
--super-large-pages
or--skip-super-large-pages
option. --symbolic-links
,--skip-symbolic-links
Command-Line Format --symbolic-links[={OFF|ON}]
Deprecated Yes Type Boolean Default Value OFF
Enable or disable symbolic link support. On Unix, enabling symbolic links means that you can link a
MyISAM
index file or data file to another directory with theINDEX DIRECTORY
orDATA DIRECTORY
option of theCREATE TABLE
statement. If you delete or rename the table, the files that its symbolic links point to also are deleted or renamed. See Section 8.12.2.2, “Using Symbolic Links for MyISAM Tables on Unix”.NoteSymbolic link support, along with the
--symbolic-links
option that controls it, is deprecated; you should expect it to be removed in a future version of MySQL. In addition, the option is disabled by default. The relatedhave_symlink
system variable also is deprecated; expect it be removed in a future version of MySQL.This option has no meaning on Windows.
-
Command-Line Format --sysdate-is-now[={OFF|ON}]
Type Boolean Default Value OFF
SYSDATE()
by default returns the time at which it executes, not the time at which the statement in which it occurs begins executing. This differs from the behavior ofNOW()
. This option causesSYSDATE()
to be a synonym forNOW()
. For information about the implications for binary logging and replication, see the description forSYSDATE()
in Section 12.7, “Date and Time Functions” and forSET TIMESTAMP
in Section 5.1.8, “Server System Variables”. --tc-heuristic-recover={COMMIT|ROLLBACK}
Command-Line Format --tc-heuristic-recover=name
Type Enumeration Default Value OFF
Valid Values OFF
COMMIT
ROLLBACK
The decision to use in a manual heuristic recovery.
If a
--tc-heuristic-recover
option is specified, the server exits regardless of whether manual heuristic recovery is successful.On systems with more than one storage engine capable of two-phase commit, the
ROLLBACK
option is not safe and causes recovery to halt with the following error:[ERROR] --tc-heuristic-recover rollback strategy is not safe on systems with more than one 2-phase-commit-capable storage engine. Aborting crash recovery.
-
Command-Line Format --transaction-isolation=name
System Variable transaction_isolation
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type Enumeration Default Value REPEATABLE-READ
Valid Values READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE
Sets the default transaction isolation level. The
level
value can beREAD-UNCOMMITTED
,READ-COMMITTED
,REPEATABLE-READ
, orSERIALIZABLE
. See Section 13.3.7, “SET TRANSACTION Statement”.The default transaction isolation level can also be set at runtime using the
SET TRANSACTION
statement or by setting thetransaction_isolation
system variable. -
Command-Line Format --transaction-read-only[={OFF|ON}]
System Variable transaction_read_only
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
Sets the default transaction access mode. By default, read-only mode is disabled, so the mode is read/write.
To set the default transaction access mode at runtime, use the
SET TRANSACTION
statement or set thetransaction_read_only
system variable. See Section 13.3.7, “SET TRANSACTION Statement”. --tmpdir=
,dir_name
-t
dir_name
Command-Line Format --tmpdir=dir_name
System Variable tmpdir
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Directory name The path of the directory to use for creating temporary files. It might be useful if your default
/tmp
directory resides on a partition that is too small to hold temporary tables. This option accepts several paths that are used in round-robin fashion. Paths should be separated by colon characters (:
) on Unix and semicolon characters (;
) on Windows.--tmpdir
can be a non-permanent location, such as a directory on a memory-based file system or a directory that is cleared when the server host restarts. If the MySQL server is acting as a replica, and you are using a non-permanent location for--tmpdir
, consider setting a different temporary directory for the replica using theslave_load_tmpdir
system variable. For a replica, the temporary files used to replicateLOAD DATA
statements are stored in this directory, so with a permanent location they can survive machine restarts, although replication can now continue after a restart if the temporary files have been removed.For more information about the storage location of temporary files, see Section B.3.3.5, “Where MySQL Stores Temporary Files”.
-
Command-Line Format --upgrade=value
Introduced 8.0.16 Type Enumeration Default Value AUTO
Valid Values AUTO
NONE
MINIMAL
FORCE
This option controls whether and how the server performs an automatic upgrade at startup. Automatic upgrade involves two steps:
Step 1: Data dictionary upgrade.
This step upgrades:
The data dictionary tables in the
mysql
schema. If the actual data dictionary version is lower than the current expected version, the server upgrades the data dictionary. If it cannot, or is prevented from doing so, the server cannot run.The Performance Schema and
INFORMATION_SCHEMA
.
Step 2: Server upgrade.
This step comprises all other upgrade tasks. If the existing installation data has a lower MySQL version than the server expects, it must be upgraded:
The system tables in the
mysql
schema (the remaining non-data dictionary tables).The
sys
schema.User schemas.
For details about upgrade steps 1 and 2, see Section 2.11.3, “What the MySQL Upgrade Process Upgrades”.
These
--upgrade
option values are permitted:AUTO
The server performs an automatic upgrade of anything it finds to be out of date (steps 1 and 2). This is the default action if
--upgrade
is not specified explicitly.NONE
The server performs no automatic upgrade steps during the startup process (skips steps 1 and 2). Because this option value prevents a data dictionary upgrade, the server exits with an error if the data dictionary is found to be out of date:
[ERROR] [MY-013381] [Server] Server shutting down because upgrade is required, yet prohibited by the command line option '--upgrade=NONE'. [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
MINIMAL
The server upgrades the data dictionary, the Performance Schema, and the
INFORMATION_SCHEMA
, if necessary (step 1). Note that following an upgrade with this option, Group Replication cannot be started, because system tables on which the replication internals depend are not updated, and reduced functionality might also be apparent in other areas.FORCE
The server upgrades the data dictionary, the Performance Schema, and the
INFORMATION_SCHEMA
, if necessary (step 1). In addition, the server forces an upgrade of everything else (step 2). Expect server startup to take longer with this option because the server checks all objects in all schemas.FORCE
is useful to force step 2 actions to be performed if the server thinks they are not necessary. For example, you may believe that a system table is missing or has become damaged and want to force a repair.
The following table summarizes the actions taken by the server for each option value.
Option Value Server Performs Step 1? Server Performs Step 2? AUTO
If necessary If necessary NONE
No No MINIMAL
If necessary No FORCE
If necessary Yes --user={
,user_name
|user_id
}-u {
user_name
|user_id
}Command-Line Format --user=name
Type String Run the mysqld server as the user having the name
user_name
or the numeric user IDuser_id
. (“User” in this context refers to a system login account, not a MySQL user listed in the grant tables.)This option is mandatory when starting mysqld as
root
. The server changes its user ID during its startup sequence, causing it to run as that particular user rather than asroot
. See Section 6.1.1, “Security Guidelines”.To avoid a possible security hole where a user adds a
--user=root
option to amy.cnf
file (thus causing the server to run asroot
), mysqld uses only the first--user
option specified and produces a warning if there are multiple--user
options. Options in/etc/my.cnf
and$MYSQL_HOME/my.cnf
are processed before command-line options, so it is recommended that you put a--user
option in/etc/my.cnf
and specify a value other thanroot
. The option in/etc/my.cnf
is found before any other--user
options, which ensures that the server runs as a user other thanroot
, and that a warning results if any other--user
option is found.-
Command-Line Format --validate-config[={OFF|ON}]
Introduced 8.0.16 Type Boolean Default Value OFF
Validate the server startup configuration. If no errors are found, the server terminates with an exit code of 0. If an error is found, the server displays a diagnostic message and terminates with an exit code of 1. Warning and information messages may also be displayed, depending on the
log_error_verbosity
value, but do not produce immediate validation termination or an exit code of 1. For more information, see Section 5.1.3, “Server Configuration Validation”. Use this option with the
--help
option for detailed help.--version
,-V
Display version information and exit.
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.
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 5.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 27.12.14, “Performance Schema System Variable Tables”.
This section provides a description of each system variable. For a system variable summary table, see Section 5.1.5, “Server System Variable Reference”. For more information about manipulation of system variables, see Section 5.1.9, “Using System Variables”.
For additional system variable information, see these sections:
Section 5.1.9, “Using System Variables”, discusses the syntax for setting and displaying system variable values.
Section 5.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 5.1.1, “Configuring the Server”.
Section 15.14, “InnoDB Startup Options and System Variables”, lists
InnoDB
system variables.Section 23.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 17.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 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.
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.
-
Command-Line Format --activate-all-roles-on-login[={OFF|ON}]
System Variable activate_all_roles_on_login
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
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 withSET DEFAULT ROLE
.If
activate_all_roles_on_login
is disabled, the server activates the default roles specified withSET 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, useSET ROLE
. To change the active roles for a stored program, the program body should executeSET ROLE
. -
Command-Line Format --admin-address=addr
Introduced 8.0.14 System Variable admin_address
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type String The IP address on which to listen for TCP/IP connections on the administrative network interface (see Section 5.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 abind_address
system variable for configuring regular (nonadministrative) client TCP/IP connections. See Section 5.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::
).As of MySQL 8.0.22, 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
/
suffix immediately following the address.ns
An address with no
/
suffix uses the host system global namespace. The global namespace is therefore the default.ns
An address with a
/
suffix uses the namespace namedns
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 5.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 thebind_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.
-
Command-Line Format --admin-port=port_num
Introduced 8.0.14 System Variable admin_port
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Integer Default Value 33062
Minimum Value 0
Maximum Value 65535
The TCP/IP port number to use for connections on the administrative network interface (see Section 5.1.12.1, “Connection Interfaces”). Setting this variable to 0 causes the default value to be used.
Setting
admin_port
has no effect ifadmin_address
is not specified because in that case the server maintains no administrative network interface. -
Command-Line Format --admin-ssl-ca=file_name
Introduced 8.0.21 System Variable admin_ssl_ca
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type File name Default Value NULL
The
admin_ssl_ca
system variable is likessl_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. -
Command-Line Format --admin-ssl-capath=dir_name
Introduced 8.0.21 System Variable admin_ssl_capath
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Directory name Default Value NULL
The
admin_ssl_capath
system variable is likessl_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. -
Command-Line Format --admin-ssl-cert=file_name
Introduced 8.0.21 System Variable admin_ssl_cert
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type File name Default Value NULL
The
admin_ssl_cert
system variable is likessl_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. -
Command-Line Format --admin-ssl-cipher=name
Introduced 8.0.21 System Variable admin_ssl_cipher
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value NULL
The
admin_ssl_cipher
system variable is likessl_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. -
Command-Line Format --admin-ssl-crl=file_name
Introduced 8.0.21 System Variable admin_ssl_crl
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type File name Default Value NULL
The
admin_ssl_crl
system variable is likessl_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. -
Command-Line Format --admin-ssl-crlpath=dir_name
Introduced 8.0.21 System Variable admin_ssl_crlpath
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Directory name Default Value NULL
The
admin_ssl_crlpath
system variable is likessl_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. -
Command-Line Format --admin-ssl-key=file_name
Introduced 8.0.21 System Variable admin_ssl_key
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type File name Default Value NULL
The
admin_ssl_key
system variable is likessl_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. -
Command-Line Format --admin-tls-ciphersuites=ciphersuite_list
Introduced 8.0.21 System Variable admin_tls_ciphersuites
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value NULL
The
admin_tls_ciphersuites
system variable is liketls_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. -
Command-Line Format --admin-tls-version=protocol_list
Introduced 8.0.21 System Variable admin_tls_version
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value TLSv1,TLSv1.1,TLSv1.2,TLSv1.3
(OpenSSL 1.1.1 and higher)TLSv1,TLSv1.1,TLSv1.2
(otherwise)The
admin_tls_version
system variable is liketls_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. authentication_windows_log_level
Command-Line Format --authentication-windows-log-level=#
System Variable authentication_windows_log_level
Scope Global Dynamic No SET_VAR
Hint AppliesNo 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.6, “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
Command-Line Format --authentication-windows-use-principal-name[={OFF|ON}]
System Variable authentication_windows_use_principal_name
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Boolean Default Value ON
This variable is available only if the
authentication_windows
Windows authentication plugin is enabled. See Section 6.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
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 happens primarily in environments that do not support Kerberos authentication), the UPN is not sent by the server and NTLM authentication is used.
-
Command-Line Format --autocommit[={OFF|ON}]
System Variable autocommit
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo 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
-
Command-Line Format --automatic-sp-privileges[={OFF|ON}]
System Variable automatic_sp_privileges
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo 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 25.2.2, “Stored Routines and MySQL Privileges”.
-
Command-Line Format --auto-generate-certs[={OFF|ON}]
System Variable auto_generate_certs
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Boolean Default Value ON
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, 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
andcaching_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 unencypted connections. -
Command-Line Format --avoid-temporal-upgrade[={OFF|ON}]
Deprecated Yes System Variable avoid_temporal_upgrade
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo 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; expect it to be removed in a future MySQL release.
-
Command-Line Format --back-log=#
System Variable back_log
Scope Global Dynamic No SET_VAR
Hint AppliesNo 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 the value of
max_connections
, which enables the permitted backlog to adjust to the maximum permitted number of connections. -
Command-Line Format --basedir=dir_name
System Variable basedir
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Directory name Default Value parent of mysqld installation directory
The path to the MySQL installation base directory.
-
Command-Line Format --big-tables[={OFF|ON}]
System Variable big_tables
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo 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. When in-memory internal temporary tables are managed by theTempTable
storage engine (the default), and the maximum amount of memory that can be occupied by theTempTable
storage engine is exceeded, theTempTable
storage engine starts storing data to temporary files on disk. When in-memory temporary tables are managed by theMEMORY
storage engine, in-memory tables are automatically converted to disk-based tables as required. For more information, see Section 8.4.4, “Internal Temporary Table Use in MySQL”. -
Command-Line Format --bind-address=addr
System Variable bind_address
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type String Default Value *
The MySQL server listens on one or more network sockets for TCP/IP connections. Each socket is bound to one address, but it is possible for an address to map onto multiple network interfaces. To specify how the server should listen for TCP/IP connections, set the
bind_address
system variable at server startup. The server also has anadmin_address
system variable that enables administrative connections on a dedicated interface. See Section 5.1.12.1, “Connection Interfaces”.If
bind_address
is specified, its value must satisfy these requirements:Prior to MySQL 8.0.13,
bind_address
accepts a single address value, which may specify a single non-wildcard IP address or host name, or one of the wildcard address formats that permit listening on multiple network interfaces (*
,0.0.0.0
, or::
).As of MySQL 8.0.13,
bind_address
accepts either a single value as just described, or a list of comma-separated values. When the variable names a list of multiple values, each value must specify a single non-wildcard IP address (either IPv4 or IPv6) or a host name. Wildcard address formats (*
,0.0.0.0
, or::
) are not allowed in a list of values.As of MySQL 8.0.22, addresses may include a network namespace specifier.
IP addresses can be specified as IPv4 or IPv6 addresses. For any value that 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 variable specifies a list of multiple values, this value is not permitted.If the address is
0.0.0.0
, the server accepts TCP/IP connections on all server host IPv4 interfaces. If the variable specifies a list of multiple values, this value is not permitted.If the address is
::
, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces. If the variable specifies a list of multiple values, this value is not permitted.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
/
suffix immediately following the address.ns
An address with no
/
suffix uses the host system global namespace. The global namespace is therefore the default.ns
An address with a
/
suffix uses the namespace namedns
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.
If the variable value specifies multiple addresses, it can include addresses in the global namespace, in named namespaces, or a mix.
For additional information about network namespaces, see Section 5.1.14, “Network Namespace Support”.
If binding to any address fails, the server produces an error and does not start.
Examples:
bind_address=*
The server listens on all IPv4 or IPv6 addresses, as specified by the
*
wildcard.bind_address=198.51.100.20
The server listens only on the
198.51.100.20
IPv4 address.bind_address=198.51.100.20,2001:db8:0:f101::1
The server listens on the
198.51.100.20
IPv4 address and the2001:db8:0:f101::1
IPv6 address.bind_address=198.51.100.20,*
This produces an error because wildcard addresses are not permitted when
bind_address
names a list of multiple values.bind_address=198.51.100.20/red,2001:db8:0:f101::1/blue,192.0.2.50
The server listens on the
198.51.100.20
IPv4 address in thered
namespace, the2001:db8:0:f101::1
IPv6 address in theblue
namespace, and the192.0.2.50
IPv4 address in the global namespace.
When
bind_address
names a single value (wildcard or non-wildcard), the server listens on a single socket, which for a wildcard address may be bound to multiple network interfaces. Whenbind_address
names a list of multiple values, the server listens on one socket per value, with each socket bound to a single network interface. The number of sockets is linear with the number of values specified. Depending on operating system connection-acceptance efficiency, long value lists might incur a performance penalty for accepting TCP/IP connections.Because file descriptors are allocated for listening sockets and network namespace files, it may be necessary to increase the
open_files_limit
system variable.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 cannot 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. -
Command-Line Format --block-encryption-mode=#
System Variable block_encryption_mode
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo 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. Permittedmode
values areECB
,CBC
,CFB1
,CFB8
,CFB128
, andOFB
.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. -
Command-Line Format --bulk-insert-buffer-size=#
System Variable bulk_insert_buffer_size
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesYes 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.As of MySQL 8.0.14, 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.9.1, “System Variable Privileges”.
caching_sha2_password_digest_rounds
Command-Line Format --caching-sha2-password-digest-rounds=#
Introduced 8.0.24 System Variable caching_sha2_password_digest_rounds
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Integer Default Value 5000
Minimum Value 5000
Maximum Value 4095000
The number of hash rounds for the
caching_sha2_password
authentication plugin.caching_sha2_password_auto_generate_rsa_keys
Command-Line Format --caching-sha2-password-auto-generate-rsa-keys[={OFF|ON}]
System Variable caching_sha2_password_auto_generate_rsa_keys
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Boolean Default Value ON
The server uses this variable to determine whether to autogenerate RSA private/public key-pair files in the data directory if they do not already exist.
At startup, the server automatically generates RSA private/public key-pair files in the data directory if all of these conditions are true: The
sha256_password_auto_generate_rsa_keys
orcaching_sha2_password_auto_generate_rsa_keys
system variable is enabled; no RSA options are specified; the RSA files are missing from the data directory. These key-pair files enable secure password exchange using RSA over unencrypted connections for accounts authenticated by thesha256_password
orcaching_sha2_password
plugin; see Section 6.4.1.3, “SHA-256 Pluggable Authentication”, and Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.For more information about RSA file autogeneration, including file names and characteristics, see Section 6.3.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”
The
auto_generate_certs
system variable is related but controls autogeneration of SSL certificate and key files needed for secure connections using SSL.caching_sha2_password_private_key_path
Command-Line Format --caching-sha2-password-private-key-path=file_name
System Variable caching_sha2_password_private_key_path
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type File name Default Value private_key.pem
This variable specifies the path name of the RSA private key file for the
caching_sha2_password
authentication plugin. If the file is named as a relative path, it is interpreted relative to the server data directory. The file must be in PEM format.ImportantBecause this file stores a private key, its access mode should be restricted so that only the MySQL server can read it.
For information about
caching_sha2_password
, see Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.caching_sha2_password_public_key_path
Command-Line Format --caching-sha2-password-public-key-path=file_name
System Variable caching_sha2_password_public_key_path
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type File name Default Value public_key.pem
This variable specifies the path name of the RSA public key file for the
caching_sha2_password
authentication plugin. If the file is named as a relative path, it is interpreted relative to the server data directory. The file must be in PEM format.For information about
caching_sha2_password
, including information about how clients request the RSA public key, see Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.-
System Variable character_set_client
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value utf8mb4
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. -
System Variable character_set_connection
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value utf8mb4
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”.
-
System Variable character_set_database
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value utf8mb4
Footnote This option is dynamic, but should be set only by server. You should not set 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
.As of MySQL 8.0.14, 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.9.1, “System Variable Privileges”.
The global
character_set_database
andcollation_database
system variables are deprecated; expect them to be removed in a future version of MySQL.Assigning a value to the session
character_set_database
andcollation_database
system variables is deprecated and assignments produce a warning. Expect the session variables to become read-only (and assignments to them to produce an error) in a future version of MySQL in which it remains possible to access the session variables to determine the database character set and collation for the default database. -
Command-Line Format --character-set-filesystem=name
System Variable character_set_filesystem
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo 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'
.As of MySQL 8.0.14, 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.9.1, “System Variable Privileges”.
-
System Variable character_set_results
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value utf8mb4
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.
-
Command-Line Format --character-set-server=name
System Variable character_set_server
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value utf8mb4
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. -
System Variable character_set_system
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type String Default Value utf8
The character set used by the server for storing identifiers. The value is always
utf8
. -
Command-Line Format --character-sets-dir=dir_name
System Variable character_sets_dir
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Directory name The directory where character sets are installed. See Section 10.15, “Character Set Configuration”.
-
Command-Line Format --check-proxy-users[={OFF|ON}]
System Variable check_proxy_users
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo 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.18, “Proxy Users”.
-
System Variable collation_connection
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo 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”). -
System Variable collation_database
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value utf8mb4_0900_ai_ci
Footnote This option is dynamic, but should be set only by server. You should not set 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
.As of MySQL 8.0.18, setting the session value of this system variable is no longer a restricted operation.
The global
character_set_database
andcollation_database
system variables are deprecated; expect them to be removed in a future version of MySQL.Assigning a value to the session
character_set_database
andcollation_database
system variables is deprecated and assignments produce a warning. Expect the session variables to become read-only (and assignments to produce an error) in a future version of MySQL in which it remains possible to access the session variables to determine the database character set and collation for the default database. -
Command-Line Format --collation-server=name
System Variable collation_server
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type String Default Value utf8mb4_0900_ai_ci
The server's default collation. See Section 10.15, “Character Set Configuration”.
-
Command-Line Format --completion-type=#
System Variable completion_type
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo 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
. -
Command-Line Format --concurrent-insert[=value]
System Variable concurrent_insert
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo 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”.
-
Command-Line Format --connect-timeout=#
System Variable connect_timeout
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo 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
-
System Variable core_file
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
Whether to write a core file if the server unexpectedly exits. This variable is set by the
--core-file
option. -
Command-Line Format --create-admin-listener-thread[={OFF|ON}]
Introduced 8.0.14 System Variable create_admin_listener_thread
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
Whether to use a dedicated listening thread for client connections on the administrative network interface (see Section 5.1.12.1, “Connection Interfaces”). The default is
OFF
; that is, the manager thread for ordinary connections on the main interface also handles connections for the administrative interface.Depending on factors such as platform type and workload, you may find one setting for this variable yields better performance than the other setting.
Setting
create_admin_listener_thread
has no effect ifadmin_address
is not specified because in that case the server maintains no administrative network interface. -
Command-Line Format --cte-max-recursion-depth=#
System Variable cte_max_recursion_depth
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 1000
Minimum Value 0
Maximum Value 4294967295
The common table expression (CTE) maximum recursion depth. The server terminates execution of any CTE that recurses more levels than the value of this variable. For more information, see Limiting Common Table Expression Recursion.
-
Command-Line Format --datadir=dir_name
System Variable datadir
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Directory name The path to the MySQL server data directory. Relative paths are resolved with respect to the current directory. If you expect the server to be started automatically (that is, in contexts for which you cannot know the current directory in advance), it is best to specify the
datadir
value as an absolute path. -
Command-Line Format --debug[=debug_options]
System Variable debug
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo 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.9.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 5.9.4, “The DBUG Package”.
-
System Variable debug_sync
Scope Session Dynamic Yes SET_VAR
Hint AppliesNo 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 has 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.9.1, “System Variable Privileges”.
For a description of the Debug Sync facility and how to use synchronization points, see MySQL Internals: Test Synchronization.
-
Command-Line Format --default-authentication-plugin=plugin_name
System Variable default_authentication_plugin
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Enumeration Default Value caching_sha2_password
Valid Values mysql_native_password
sha256_password
caching_sha2_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.3, “SHA-256 Pluggable Authentication”.caching_sha2_password
: Use SHA-256 passwords; see Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
NoteIn MySQL 8.0,
caching_sha2_password
is the default authentication plugin rather thanmysql_native_password
. For information about the implications of this change for server operation and compatibility of the server with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin.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.For an account created with the following statement, 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
';
-
System Variable default_collation_for_utf8mb4
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type Enumeration Valid Values utf8mb4_0900_ai_ci
utf8mb4_general_ci
For internal use by replication. This system variable is set to the default collation for the
utf8mb4
character set. The value of the variable is replicated from a source to a replica so that the replica can correctly process data originating from a source with a different default collation forutf8mb4
. This variable is primarily intended to support replication from a MySQL 5.7 or older replication source server to a MySQL 8.0 replica server, or group replication with a MySQL 5.7 primary node and one or more MySQL 8.0 secondaries. The default collation forutf8mb4
in MySQL 5.7 isutf8mb4_general_ci
, bututf8mb4_0900_ai_ci
in MySQL 8.0. The variable is not present in releases earlier than MySQL 8.0, so if the replica does not receive a value for the variable, it assumes the source is from an earlier release and sets the value to the previous default collationutf8mb4_general_ci
.As of MySQL 8.0.18, setting the session value of this system variable is no longer a restricted operation.
The default
utf8mb4
collation is used in the following statements:CREATE TABLE
andALTER TABLE
having aCHARACTER SET utf8mb4
clause without aCOLLATION
clause, either for the table character set or for a column character set.CREATE DATABASE
andALTER DATABASE
having aCHARACTER SET utf8mb4
clause without aCOLLATION
clause.Any statement containing a string literal of the form
_utf8mb4'
without asome text
'COLLATE
clause.
-
Command-Line Format --default-password-lifetime=#
System Variable default_password_lifetime
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 0
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 option of the
CREATE USER
andALTER USER
statements. See Section 6.2.15, “Password Management”. -
Command-Line Format --default-storage-engine=name
System Variable default_storage_engine
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type Enumeration Default Value InnoDB
The default storage engine for tables. See Chapter 16, 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 else the server does not start. -
Command-Line Format --default-table-encryption[={OFF|ON}]
Introduced 8.0.16 System Variable default_table_encryption
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesYes Type Boolean Default Value OFF
Defines the default encryption setting applied to schemas and general tablespaces when they are created without specifying an
ENCRYPTION
clause.The
default_table_encryption
variable is only applicable to user-created schemas and general tablespaces. It does not govern encryption of themysql
system tablespace.Setting the runtime value of
default_table_encryption
requires theSYSTEM_VARIABLES_ADMIN
andTABLE_ENCRYPTION_ADMIN
privileges, or the deprecatedSUPER
privilege.default_table_encryption
supportsSET PERSIST
andSET PERSIST_ONLY
syntax. See Section 5.1.9.3, “Persisted System Variables”.For more information, see Defining an Encryption Default for Schemas and General Tablespaces.
-
Command-Line Format --default-tmp-storage-engine=name
System Variable default_tmp_storage_engine
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesYes 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 else the server does not start. -
Command-Line Format --default-week-format=#
System Variable default_week_format
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 0
Minimum Value 0
Maximum Value 7
The default mode value to use for the
WEEK()
function. See Section 12.7, “Date and Time Functions”. -
Command-Line Format --delay-key-write[={OFF|ON|ALL}]
System Variable delay_key_write
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo 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 16.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.8, “Server System Variables”, and Section 16.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. -
Command-Line Format --delayed-insert-limit=#
Deprecated Yes System Variable delayed_insert_limit
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo 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 you should expect it to be removed in a future release. -
Command-Line Format --delayed-insert-timeout=#
Deprecated Yes System Variable delayed_insert_timeout
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 300
This system variable is deprecated (because
DELAYED
inserts are not supported), and you should expect it to be removed in a future release. -
Command-Line Format --delayed-queue-size=#
Deprecated Yes System Variable delayed_queue_size
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 1000
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 you should expect it to be removed in a future release. -
Command-Line Format --disabled-storage-engines=engine[,engine]...
System Variable disabled_storage_engines
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type String Default Value empty string
This variable indicates which storage engines cannot be used to create tables or tablespaces. For example, to prevent new
MyISAM
orFEDERATED
tables from being created, start the server with these lines in the server option file:[mysqld] disabled_storage_engines="MyISAM,FEDERATED"
By default,
disabled_storage_engines
is empty (no engines disabled), but it can be set to a comma-separated list of one or more engines (not case-sensitive). Any engine named in the value cannot be used to create tables or tablespaces withCREATE TABLE
orCREATE TABLESPACE
, and cannot be used withALTER TABLE ... ENGINE
orALTER TABLESPACE ... ENGINE
to change the storage engine of existing tables or tablespaces. Attempts to do so result in anER_DISABLED_STORAGE_ENGINE
error.disabled_storage_engines
does not restrict other DDL statements for existing tables, such asCREATE INDEX
,TRUNCATE TABLE
,ANALYZE TABLE
,DROP TABLE
, orDROP TABLESPACE
. This permits a smooth transition so that existing tables or tablespaces that use a disabled engine can be migrated to a permitted engine by means such asALTER TABLE ... ENGINE
.permitted_engine
It is permitted to set the
default_storage_engine
ordefault_tmp_storage_engine
system variable to a storage engine that is disabled. This could cause applications to behave erratically or fail, although that might be a useful technique in a development environment for identifying applications that use disabled engines, so that they can be modified.disabled_storage_engines
is disabled and has no effect if the server is started with any of these options:--initialize
,--initialize-insecure
,--skip-grant-tables
.NoteSetting
disabled_storage_engines
might cause an issue with mysql_upgrade. For details, see Section 4.4.5, “mysql_upgrade — Check and Upgrade MySQL Tables”. disconnect_on_expired_password
Command-Line Format --disconnect-on-expired-password[={OFF|ON}]
System Variable disconnect_on_expired_password
Scope Global Dynamic No SET_VAR
Hint AppliesNo Type Boolean Default Value ON
This variable controls how the server handles clients with expired passwords:
If the client indicates that it can handle expired passwords, the value of
disconnect_on_expired_password
is irrelevant. The server permits the client to connect but puts it in sandbox mode.If the client does not indicate that it can handle expired passwords, the server handles the client according to the value of
disconnect_on_expired_password