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
mysqlsystem 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 variablesshell>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=s2018-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=s2018-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 --helpDisplay a short help message and exit. Use both the
--verboseand--helpoptions to see the full message.-
Command-Line Format --admin-ssl[={OFF|ON}]Introduced 8.0.21 Type Boolean Default Value ONThe
--admin-ssloption is like the--ssloption, 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-ssloption specifies that the server permits but does not require encrypted connections on the administrative interface. This option is enabled by default.--admin-sslcan be specified in negated form as--skip-admin-sslor 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_andxxxadmin_ssl_system variables.xxxThe
--admin-ssloption 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 TLSat runtime. For example, you can use--admin-ssl=OFFto start the administrative interface with encrypted connections disabled, then reconfigure TLS and executeALTER INSTANCE RELOAD TLS FOR CHANNEL mysql_adminto 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_certandadmin_ssl_keysystem 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 OFFThis option controls whether user-defined functions that have only an
xxxsymbol 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 --ansiUse standard (ANSI) SQL syntax instead of MySQL syntax. For more precise control over the server SQL mode, use the
--sql-modeoption instead. See Section 1.7, “MySQL Standards Compliance”, and Section 5.1.11, “Server SQL Modes”. --basedir=,dir_name-bdir_nameCommand-Line Format --basedir=dir_nameSystem Variable basedirScope Global Dynamic No SET_VARHint AppliesNo Type Directory name Default Value parent of mysqld installation directoryThe path to the MySQL installation directory. This option sets the
basedirsystem 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
basedirvalue. This in turn enables the server to use thatbasedirwhen searching for server-related information such as thesharedirectory containing error messages.--character-set-client-handshakeCommand-Line Format --character-set-client-handshake[={OFF|ON}]Type Boolean Default Value ONDo 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-rdir_nameCommand-Line Format --chroot=dir_nameType 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 DATAandSELECT ... INTO OUTFILE.-
Command-Line Format --consolePlatform 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.
--consoletakes precedence over--log-errorif both are given. -
Command-Line Format --core-file[={OFF|ON}]Type Boolean Default Value OFFWrite 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.pidpidrepresents the process ID of the server process. On macOS, a core file namedcore.is written to thepid/coresdirectory. 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-sizeoption 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--useroption. 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_filevariable 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,-DCommand-Line Format --daemonize[={OFF|ON}]Type Boolean Default Value OFFThis 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”.
--daemonizeis mutually exclusive with--initializeand--initialize-insecure.If the server is started using the
--daemonizeoption 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.-Dis a synonym for--daemonize.--datadir=,dir_name-hdir_nameCommand-Line Format --datadir=dir_nameSystem Variable datadirScope Global Dynamic No SET_VARHint AppliesNo Type Directory name The path to the MySQL server data directory. This option sets the
datadirsystem variable. See the description of that variable.--debug[=,debug_options]-# [debug_options]Command-Line Format --debug[=debug_options]System Variable debugScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value (Windows) d:t:i:O,\mysqld.traceDefault Value (Unix) d:t:i:o,/tmp/mysqld.traceIf MySQL is configured with the
-DWITH_DEBUG=1CMake option, you can use this option to get a trace file of what mysqld is doing. A typicaldebug_optionsstring isd:t:o,. The default isfile_named:t:i:o,/tmp/mysqld.traceon Unix andd:t:i:O,\mysqld.traceon Windows.Using
-DWITH_DEBUG=1to 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=+Psets 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=1CMake 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=nameType String Set the default server time zone. This option sets the global
time_zonesystem 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_zonesystem variable. --defaults-extra-file=file_nameRead 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_nameis 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_nameis 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-fileand--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=_otheroption 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_listCommand-Line Format --early-plugin-load=plugin_listType String Default Value empty stringThis option tells the server which plugins to load before loading mandatory built-in plugins and before storage engine initialization. If multiple
--early-plugin-loadoptions are given, only the last one is used.The option value is a semicolon-separated list of
name=plugin_libraryandplugin_libraryvalues. Eachnameis the name of a plugin to load, andplugin_libraryis 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_dirsystem variable.For example, if plugins named
myplug1andmyplug2have library filesmyplug1.soandmyplug2.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-loadis used again.If the server is started using
--initializeor--initialize-insecure, plugins specified by--early-plugin-loadare not loaded.If the server is run with
--help, plugins specified by--early-plugin-loadare loaded but not initialized. This behavior ensures that plugin options are displayed in the help message.The default
--early-plugin-loadvalue is empty. To load thekeyring_fileplugin, you must use an explicit--early-plugin-loadoption with a nonempty value.The
InnoDBtablespace encryption feature relies on thekeyring_fileplugin for encryption key management, and thekeyring_fileplugin must be loaded prior to storage engine initialization to facilitateInnoDBrecovery for encrypted tables. Administrators who want thekeyring_fileplugin loaded at startup should use the appropriate nonempty option value (for example,keyring_file.soon Unix and Unix-like systems andkeyring_file.dllon Windows).For information about
InnoDBtablespace 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 OFFEnable external locking (system locking), which is disabled by default. If you use this option on a system on which
lockddoes 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
MyISAMtable 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 flushScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFFlush (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
--flushis specified, the value offlush_timedoes not matter and changes toflush_timehave no effect on flush behavior. -
Command-Line Format --gdb[={OFF|ON}]Type Boolean Default Value OFFInstall an interrupt handler for
SIGINT(needed to stop mysqld with^Cto 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
RESTARTstatement: 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--gdbsuppresses forking. For a server started with this option,RESTARTsimply exits and does not restart.In non-debug settings,
--no-monitormay be used to suppress forking the monitor process. --initialize,-ICommand-Line Format --initialize[={OFF|ON}]Type Boolean Default Value OFFThis option is used to initialize a MySQL installation by creating the data directory and populating the tables in the
mysqlsystem 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_filesystem variable. For more information, see the description of that variable. In addition, thedisabled_storage_enginessystem variable has no effect.The
--ndbclusteroption is ignored when used together with--initialize.--initializeis mutually exclusive with--daemonize.-Iis a synonym for--initialize.-
Command-Line Format --initialize-insecure[={OFF|ON}]Type Boolean Default Value OFFThis option is used to initialize a MySQL installation by creating the data directory and populating the tables in the
mysqlsystem schema. This option implies--initialize. For more information, see the description of that option, and Section 2.10.1, “Initializing the Data Directory”.--initialize-insecureis mutually exclusive with--daemonize. --innodb-xxxSet an option for the
InnoDBstorage engine. TheInnoDBoptions 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
MySQLif noservice_namevalue 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-fileand--installoptions,--installmust 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
MySQLif noservice_namevalue 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-fileand--install-manualoptions,--install-manualmust be first.--language=lang_name, -Llang_nameCommand-Line Format --language=nameDeprecated Yes; use lc-messages-dirinsteadSystem Variable languageScope Global Dynamic No SET_VARHint AppliesNo Type Directory name Default Value /usr/local/mysql/share/mysql/english/The language to use for error messages.
lang_namecan 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-dirand--lc-messagesshould be used rather than--language, which is deprecated (and handled as a synonym for--lc-messages-dir). You should expect the--languageoption to be removed in a future MySQL release.-
Command-Line Format --large-pages[={OFF|ON}]System Variable large_pagesScope Global Dynamic No SET_VARHint AppliesNo Platform Specific Linux Type Boolean Default Value OFFSome 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-pagesoption.--large-pagesis disabled by default. -
Command-Line Format --lc-messages=nameSystem Variable lc_messagesScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value en_USThe 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-dirto produce the location for the error message file. See Section 10.12, “Setting the Error Message Language”. -
Command-Line Format --lc-messages-dir=dir_nameSystem Variable lc_messages_dirScope Global Dynamic No SET_VARHint AppliesNo Type Directory name The directory where error messages are located. The server uses the value together with the value of
--lc-messagesto 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-serviceoption following the service name causes the server to run using theLocalServiceWindows account that has limited system privileges. If both--defaults-fileand--local-serviceare 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_errorScope Global Dynamic No SET_VARHint 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
host_name.err--pid-fileoption is specified. In that case, the file name is the PID file base name with a suffix of.errin the data directory.If the option names a file, the default destination is that file (with an
.errsuffix 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,
--consoletakes precedence over--log-errorif 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
MyISAMchanges to this file (used only when debuggingMyISAM). -
Command-Line Format --log-raw[={OFF|ON}]System Variable (≥ 8.0.19) log_rawScope (≥ 8.0.19) Global Dynamic (≥ 8.0.19) Yes SET_VARHint Applies (≥ 8.0.19)No Type Boolean Default Value OFFPasswords 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-rawoption. 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-rawoption 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 OFFLog less information to the slow query log, if it has been activated.
-
Command-Line Format --log-tc=file_nameType File name Default Value tc.logThe 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 sizeMinimum Value 6 * page sizeMaximum Value (64-bit platforms) 18446744073709551615Maximum Value (32-bit platforms) 4294967295The 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 OFFLock 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.
--memlockworks 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.hfile, like this:shell>
grep mlockall /usr/include/sys/mman.hIf
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
rootby changing thelimits.conffile. 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 1024Minimum Value 1024Maximum Value 16384The block size to be used for
MyISAMindex pages. Do not read any option files. If program startup fails due to reading unknown options from an option file,
--no-defaultscan 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 OFFNoteThis option is deprecated as of MySQL 8.0.16. It is superseded by the
--upgradeoption, 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-upgradeis 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
RESTARTstatement: Forking enables one process to act as a monitor to the other, which acts as the server. For a server started with this option,RESTARTsimply exits and does not restart.--no-monitoris not available prior to MySQL 8.0.12. The--gdboption can be used as a workaround. -
Command-Line Format --old-style-user-limits[={OFF|ON}]Type Boolean Default Value OFFEnable 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
usertable.) See Section 6.2.20, “Setting Account Resource Limits”. --performance-schema-xxxConfigure a Performance Schema option. For details, see Section 27.14, “Performance Schema Command Options”.
-
Command-Line Format --plugin-load=plugin_listSystem Variable plugin_loadScope Global Dynamic No SET_VARHint AppliesNo Type String This option tells the server to load the named plugins at startup. If multiple
--plugin-loadoptions are given, only the last one is used. Additional plugins to load may be specified using--plugin-load-addoptions.The option value is a semicolon-separated list of
name=plugin_libraryandplugin_libraryvalues. Eachnameis the name of a plugin to load, andplugin_libraryis 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_dirsystem variable.For example, if plugins named
myplug1andmyplug2have library filesmyplug1.soandmyplug2.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-loadis used again. This is in contrast toINSTALL PLUGIN, which adds an entry to themysql.pluginstable 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.pluginssystem table. If the server is started with the--skip-grant-tablesoption, plugins registered in themysql.pluginstable are not loaded and are unavailable.--plugin-loadenables plugins to be loaded even when--skip-grant-tablesis given.--plugin-loadalso 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_listSystem Variable plugin_load_addScope Global Dynamic No SET_VARHint AppliesNo Type String This option complements the
--plugin-loadoption.--plugin-load-addadds 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-addcan be used to avoid specifying a large set of plugins as a single long unwieldy--plugin-loadargument.--plugin-load-addcan be given in the absence of--plugin-load, but any instance of--plugin-load-addthat appears before--plugin-load. has no effect because--plugin-loadresets 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
--pluginprefix. Thus, the--innodb-file-per-tableoption forInnoDBcan be specified as--plugin-innodb-file-per-table.For boolean options that can be enabled or disabled, the
--skipprefix and other alternative formats are supported as well (see Section 4.2.2.4, “Program Option Modifiers”). For example,--skip-plugin-innodb-file-per-tabledisablesinnodb-file-per-table.The rationale for the
--pluginprefix 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--pluginprefix for plugin options is recommended to avoid any question of ambiguity.--port=,port_num-Pport_numCommand-Line Format --port=port_numSystem Variable portScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 3306Minimum Value 0Maximum Value 65535The 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
rootoperating system user. Setting this option to 0 causes the default value to be used.-
Command-Line Format --port-open-timeout=#Type Integer Default Value 0On 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-fileor--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
MySQLif noservice_namevalue 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 OFFIf this option is enabled, a user cannot create new MySQL users by using the
GRANTstatement unless the user has theINSERTprivilege for themysql.usersystem 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
GRANTstatement to give privileges to other users. -
Command-Line Format --skip-grant-tables[={OFF|ON}]Type Boolean Default Value OFFThis option affects the server startup sequence:
--skip-grant-tablescauses the server not to read the grant tables in themysqlsystem 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-tablesdisables authentication checks, the server also disables remote connections in that case by enablingskip_networking.To cause a server started with
--skip-grant-tablesto load the grant tables at runtime, perform a privilege-flushing operation, which can be done in these ways:Issue a MySQL
FLUSH PRIVILEGESstatement 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-tablesdisables failed-login tracking and temporary account locking because those capabilities depend on the grant tables. See Section 6.2.15, “Password Management”.--skip-grant-tablescauses the server not to load certain other objects registered in the data dictionary or themysqlsystem schema:Scheduled events installed using
CREATE EVENTand registered in theeventsdata dictionary table.Plugins installed using
INSTALL PLUGINand registered in themysql.pluginsystem table.To cause plugins to be loaded even when using
--skip-grant-tables, use the--plugin-loador--plugin-load-addoption.User-defined functions (UDFs) installed using
CREATE FUNCTIONand registered in themysql.funcsystem table.
--skip-grant-tablesdoes not suppress loading during startup of components.--skip-grant-tablescauses thedisabled_storage_enginessystem variable to have no effect.
-
Command-Line Format --skip-host-cacheDisable 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-cacheis similar to setting thehost_cache_sizesystem variable to 0, buthost_cache_sizeis 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-cachedoes 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_sizeis 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
InnoDBstorage engine. In this case, because the default storage engine isInnoDB, the server does not start unless you also use--default-storage-engineand--default-tmp-storage-engineto set the default to some other engine for both permanent andTEMPORARYtables.The
InnoDBstorage engine cannot be disabled, and the--skip-innodboption 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-newThis 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 TABLEto be mapped toALTER TABLEfor storage engines for whichOPTIMIZE TABLEis not supported. -
Command-Line Format --skip-show-databaseSystem Variable skip_show_databaseScope Global Dynamic No SET_VARHint AppliesNo This option sets the
skip_show_databasesystem variable that controls who is permitted to use theSHOW DATABASESstatement. See Section 5.1.8, “Server System Variables”. -
Command-Line Format --skip-stack-traceDo 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 15000This 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 socketScope Global Dynamic No SET_VARHint AppliesNo Type String Default Value (Other) /tmp/mysql.sockDefault Value (Windows) MySQLOn 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=nameSystem Variable sql_modeScope Global, Session Dynamic Yes SET_VARHint 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_SUBSTITUTIONValid Values ALLOW_INVALID_DATESANSI_QUOTESERROR_FOR_DIVISION_BY_ZEROHIGH_NOT_PRECEDENCEIGNORE_SPACENO_AUTO_VALUE_ON_ZERONO_BACKSLASH_ESCAPESNO_DIR_IN_CREATENO_ENGINE_SUBSTITUTIONNO_UNSIGNED_SUBTRACTIONNO_ZERO_DATENO_ZERO_IN_DATEONLY_FULL_GROUP_BYPAD_CHAR_TO_FULL_LENGTHPIPES_AS_CONCATREAL_AS_FLOATSTRICT_ALL_TABLESSTRICT_TRANS_TABLESTIME_TRUNCATE_FRACTIONALSet 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-sslType Boolean Default Value ONThe
--ssloption specifies that the server permits but does not require encrypted connections. This option is enabled by default.--sslcan be specified in negated form as--skip-sslor 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_andxxxssl_system variables.xxxThe
--ssloption 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 TLSat runtime. For example, you can use--ssl=OFFto start the server with encrypted connections disabled, then reconfigure TLS and executeALTER INSTANCE RELOAD TLSto 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_certandssl_keysystem variables on the server side and the--ssl-ca(or--ssl-capath) option on the client side. -
Command-Line Format --standalonePlatform 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 OFFStandard 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-pagesor--skip-super-large-pagesoption. --symbolic-links,--skip-symbolic-linksCommand-Line Format --symbolic-links[={OFF|ON}]Deprecated Yes Type Boolean Default Value OFFEnable or disable symbolic link support. On Unix, enabling symbolic links means that you can link a
MyISAMindex file or data file to another directory with theINDEX DIRECTORYorDATA DIRECTORYoption of theCREATE TABLEstatement. 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-linksoption 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_symlinksystem 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 OFFSYSDATE()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 TIMESTAMPin Section 5.1.8, “Server System Variables”. --tc-heuristic-recover={COMMIT|ROLLBACK}Command-Line Format --tc-heuristic-recover=nameType Enumeration Default Value OFFValid Values OFFCOMMITROLLBACKThe decision to use in a manual heuristic recovery.
If a
--tc-heuristic-recoveroption 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
ROLLBACKoption 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=nameSystem Variable transaction_isolationScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value REPEATABLE-READValid Values READ-UNCOMMITTEDREAD-COMMITTEDREPEATABLE-READSERIALIZABLESets the default transaction isolation level. The
levelvalue 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 TRANSACTIONstatement or by setting thetransaction_isolationsystem variable. -
Command-Line Format --transaction-read-only[={OFF|ON}]System Variable transaction_read_onlyScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFSets 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 TRANSACTIONstatement or set thetransaction_read_onlysystem variable. See Section 13.3.7, “SET TRANSACTION Statement”. --tmpdir=,dir_name-tdir_nameCommand-Line Format --tmpdir=dir_nameSystem Variable tmpdirScope Global Dynamic No SET_VARHint AppliesNo Type Directory name The path of the directory to use for creating temporary files. It might be useful if your default
/tmpdirectory 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.--tmpdircan 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_tmpdirsystem variable. For a replica, the temporary files used to replicateLOAD DATAstatements 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=valueIntroduced 8.0.16 Type Enumeration Default Value AUTOValid Values AUTONONEMINIMALFORCEThis 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
mysqlschema. 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
mysqlschema (the remaining non-data dictionary tables).The
sysschema.User schemas.
For details about upgrade steps 1 and 2, see Section 2.11.3, “What the MySQL Upgrade Process Upgrades”.
These
--upgradeoption values are permitted:AUTOThe server performs an automatic upgrade of anything it finds to be out of date (steps 1 and 2). This is the default action if
--upgradeis not specified explicitly.NONEThe 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.
MINIMALThe 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.FORCEThe 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.FORCEis 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? AUTOIf necessary If necessary NONENo No MINIMALIf necessary No FORCEIf necessary Yes --user={,user_name|user_id}-u {user_name|user_id}Command-Line Format --user=nameType String Run the mysqld server as the user having the name
user_nameor 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=rootoption to amy.cnffile (thus causing the server to run asroot), mysqld uses only the first--useroption specified and produces a warning if there are multiple--useroptions. Options in/etc/my.cnfand$MYSQL_HOME/my.cnfare processed before command-line options, so it is recommended that you put a--useroption in/etc/my.cnfand specify a value other thanroot. The option in/etc/my.cnfis found before any other--useroptions, which ensures that the server runs as a user other thanroot, and that a warning results if any other--useroption is found.-
Command-Line Format --validate-config[={OFF|ON}]Introduced 8.0.16 Type Boolean Default Value OFFValidate 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_verbosityvalue, 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
--helpoption for detailed help.--version,-VDisplay 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 VARIABLESstatement 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
InnoDBsystem 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_loginScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFWhether to enable automatic activation of all granted roles when users log in to the server:
If
activate_all_roles_on_loginis 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_loginis 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_rolessystem variable value.activate_all_roles_on_loginapplies 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=addrIntroduced 8.0.14 System Variable admin_addressScope Global Dynamic No SET_VARHint 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_addressvalue. If this variable is not specified at startup, the server maintains no administrative interface. The server also has abind_addresssystem variable for configuring regular (nonadministrative) client TCP/IP connections. See Section 5.1.12.1, “Connection Interfaces”.If
admin_addressis 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.1or--host=::ffff:127.0.0.1.If the address is a “regular” IPv4 or IPv6 address (such as
127.0.0.1or::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.nsAn address with no
/suffix uses the host system global namespace. The global namespace is therefore the default.nsAn address with a
/suffix uses the namespace namednsns.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_addresssystem variable is similar to thebind_addresssystem variable that binds the server to an address for ordinary client connections, but with these differences:bind_addresspermits multiple addresses.admin_addresspermits a single address.bind_addresspermits wildcard addresses.admin_addressdoes not.
-
Command-Line Format --admin-port=port_numIntroduced 8.0.14 System Variable admin_portScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 33062Minimum Value 0Maximum Value 65535The 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_porthas no effect ifadmin_addressis not specified because in that case the server maintains no administrative network interface. -
Command-Line Format --admin-ssl-ca=file_nameIntroduced 8.0.21 System Variable admin_ssl_caScope Global Dynamic Yes SET_VARHint AppliesNo Type File name Default Value NULLThe
admin_ssl_casystem 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_nameIntroduced 8.0.21 System Variable admin_ssl_capathScope Global Dynamic Yes SET_VARHint AppliesNo Type Directory name Default Value NULLThe
admin_ssl_capathsystem 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_nameIntroduced 8.0.21 System Variable admin_ssl_certScope Global Dynamic Yes SET_VARHint AppliesNo Type File name Default Value NULLThe
admin_ssl_certsystem 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=nameIntroduced 8.0.21 System Variable admin_ssl_cipherScope Global Dynamic Yes SET_VARHint AppliesNo Type String Default Value NULLThe
admin_ssl_ciphersystem 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_nameIntroduced 8.0.21 System Variable admin_ssl_crlScope Global Dynamic Yes SET_VARHint AppliesNo Type File name Default Value NULLThe
admin_ssl_crlsystem 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_nameIntroduced 8.0.21 System Variable admin_ssl_crlpathScope Global Dynamic Yes SET_VARHint AppliesNo Type Directory name Default Value NULLThe
admin_ssl_crlpathsystem 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_nameIntroduced 8.0.21 System Variable admin_ssl_keyScope Global Dynamic Yes SET_VARHint AppliesNo Type File name Default Value NULLThe
admin_ssl_keysystem 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_listIntroduced 8.0.21 System Variable admin_tls_ciphersuitesScope Global Dynamic Yes SET_VARHint AppliesNo Type String Default Value NULLThe
admin_tls_ciphersuitessystem 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_listIntroduced 8.0.21 System Variable admin_tls_versionScope Global Dynamic Yes SET_VARHint 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_versionsystem 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_levelCommand-Line Format --authentication-windows-log-level=#System Variable authentication_windows_log_levelScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 2Minimum Value 0Maximum Value 4This variable is available only if the
authentication_windowsWindows 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_nameCommand-Line Format --authentication-windows-use-principal-name[={OFF|ON}]System Variable authentication_windows_use_principal_nameScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value ONThis variable is available only if the
authentication_windowsWindows 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 formuser_id@computer_nameThis 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
0x00byte 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 autocommitScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONThe autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you must use
COMMITto accept a transaction orROLLBACKto cancel it. Ifautocommitis 0 and you change it to 1, MySQL performs an automaticCOMMITof any open transaction. Another way to begin a transaction is to use aSTART TRANSACTIONorBEGINstatement. See Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”.By default, client connections begin with
autocommitset to 1. To cause clients to begin with a default of 0, set the globalautocommitvalue by starting the server with the--autocommit=0option. 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_privilegesScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONWhen this variable has a value of 1 (the default), the server automatically grants the
EXECUTEandALTER ROUTINEprivileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine. (TheALTER ROUTINEprivilege is required to drop the routine.) The server also automatically drops those privileges from the creator when the routine is dropped. Ifautomatic_sp_privilegesis 0, the server does not automatically add or drop these privileges.The creator of a routine is the account used to execute the
CREATEstatement for it. This might not be the same as the account named as theDEFINERin the routine definition.If you start mysqld with
--skip-new,automatic_sp_privilegesis 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_certsScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value ONThis 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_certssystem variable is enabled, no SSL options other than--sslare 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_keysandcaching_sha2_password_auto_generate_rsa_keyssystem 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_upgradeScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFThis variable controls whether
ALTER TABLEimplicitly upgrades temporal columns found to be in pre-5.6.4 format (TIME,DATETIME, andTIMESTAMPcolumns 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 TABLEnot 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_logScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value -1(signifies autosizing; do not assign this literal value)Minimum Value 1Maximum Value 65535The 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_logvalue 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_logcannot 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_nameSystem Variable basedirScope Global Dynamic No SET_VARHint AppliesNo Type Directory name Default Value parent of mysqld installation directoryThe path to the MySQL installation base directory.
-
Command-Line Format --big-tables[={OFF|ON}]System Variable big_tablesScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFIf enabled, the server stores all temporary tables on disk rather than in memory. This prevents most
The tableerrors fortbl_nameis fullSELECToperations 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 theTempTablestorage engine (the default), and the maximum amount of memory that can be occupied by theTempTablestorage engine is exceeded, theTempTablestorage engine starts storing data to temporary files on disk. When in-memory temporary tables are managed by theMEMORYstorage 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=addrSystem Variable bind_addressScope Global Dynamic No SET_VARHint 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_addresssystem variable at server startup. The server also has anadmin_addresssystem variable that enables administrative connections on a dedicated interface. See Section 5.1.12.1, “Connection Interfaces”.If
bind_addressis specified, its value must satisfy these requirements:Prior to MySQL 8.0.13,
bind_addressaccepts 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_addressaccepts 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.1or--host=::ffff:127.0.0.1.If the address is a “regular” IPv4 or IPv6 address (such as
127.0.0.1or::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.nsAn address with no
/suffix uses the host system global namespace. The global namespace is therefore the default.nsAn address with a
/suffix uses the namespace namednsns.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.20The server listens only on the
198.51.100.20IPv4 address.bind_address=198.51.100.20,2001:db8:0:f101::1The server listens on the
198.51.100.20IPv4 address and the2001:db8:0:f101::1IPv6 address.bind_address=198.51.100.20,*This produces an error because wildcard addresses are not permitted when
bind_addressnames a list of multiple values.bind_address=198.51.100.20/red,2001:db8:0:f101::1/blue,192.0.2.50The server listens on the
198.51.100.20IPv4 address in therednamespace, the2001:db8:0:f101::1IPv6 address in thebluenamespace, and the192.0.2.50IPv4 address in the global namespace.
When
bind_addressnames 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_addressnames 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_limitsystem variable.If you intend to bind the server to a specific address, be sure that the
mysql.usersystem 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.usertable so you can still connect to the server to shut it down. -
Command-Line Format --block-encryption-mode=#System Variable block_encryption_modeScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value aes-128-ecbThis variable controls the block encryption mode for block-based algorithms such as AES. It affects encryption for
AES_ENCRYPT()andAES_DECRYPT().block_encryption_modetakes a value inaes-format, wherekeylen-modekeylenis the key length in bits andmodeis the encryption mode. The value is not case-sensitive. Permittedkeylenvalues are 128, 192, and 256. Permittedmodevalues 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_modeto 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_sizeScope Global, Session Dynamic Yes SET_VARHint AppliesYes Type Integer Default Value 8388608Minimum Value 0Maximum Value (64-bit platforms) 18446744073709551615Maximum Value (32-bit platforms) 4294967295MyISAMuses a special tree-like cache to make bulk inserts faster forINSERT ... SELECT,INSERT ... VALUES (...), (...), ..., andLOAD DATAwhen 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_roundsCommand-Line Format --caching-sha2-password-digest-rounds=#Introduced 8.0.24 System Variable caching_sha2_password_digest_roundsScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 5000Minimum Value 5000Maximum Value 4095000The number of hash rounds for the
caching_sha2_passwordauthentication plugin.caching_sha2_password_auto_generate_rsa_keysCommand-Line Format --caching-sha2-password-auto-generate-rsa-keys[={OFF|ON}]System Variable caching_sha2_password_auto_generate_rsa_keysScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value ONThe 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_keysorcaching_sha2_password_auto_generate_rsa_keyssystem 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_passwordorcaching_sha2_passwordplugin; 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_certssystem variable is related but controls autogeneration of SSL certificate and key files needed for secure connections using SSL.caching_sha2_password_private_key_pathCommand-Line Format --caching-sha2-password-private-key-path=file_nameSystem Variable caching_sha2_password_private_key_pathScope Global Dynamic No SET_VARHint AppliesNo Type File name Default Value private_key.pemThis variable specifies the path name of the RSA private key file for the
caching_sha2_passwordauthentication 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_pathCommand-Line Format --caching-sha2-password-public-key-path=file_nameSystem Variable caching_sha2_password_public_key_pathScope Global Dynamic No SET_VARHint AppliesNo Type File name Default Value public_key.pemThis variable specifies the path name of the RSA public key file for the
caching_sha2_passwordauthentication 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_clientScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value utf8mb4The 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-setoption 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
sjiswhen connecting to a server not configured withsjissupport.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-handshakeoption, 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_clientvalue produces an error. See Impermissible Client Character Sets. -
System Variable character_set_connectionScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value utf8mb4The 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_databaseScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value utf8mb4Footnote 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_databaseandcollation_databasesystem variables are deprecated; expect them to be removed in a future version of MySQL.Assigning a value to the session
character_set_databaseandcollation_databasesystem 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=nameSystem Variable character_set_filesystemScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value binaryThe file system character set. This variable is used to interpret string literals that refer to file names, such as in the
LOAD DATAandSELECT ... INTO OUTFILEstatements and theLOAD_FILE()function. Such file names are converted fromcharacter_set_clienttocharacter_set_filesystembefore 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_filesystemto'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_resultsScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value utf8mb4The 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=nameSystem Variable character_set_serverScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value utf8mb4The servers default character set. See Section 10.15, “Character Set Configuration”. If you set this variable, you should also set
collation_serverto specify the collation for the character set. -
System Variable character_set_systemScope Global Dynamic No SET_VARHint AppliesNo Type String Default Value utf8The character set used by the server for storing identifiers. The value is always
utf8. -
Command-Line Format --character-sets-dir=dir_nameSystem Variable character_sets_dirScope Global Dynamic No SET_VARHint 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_usersScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFSome 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_userssystem 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_passwordplugin, enablemysql_native_password_proxy_users.For the
sha256_passwordplugin, enablesha256_password_proxy_users.
For information about user proxying, see Section 6.2.18, “Proxy Users”.
-
System Variable collation_connectionScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String The collation of the connection character set.
collation_connectionis important for comparisons of literal strings. For comparisons of strings with column values,collation_connectiondoes 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_databaseScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value utf8mb4_0900_ai_ciFootnote 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_databaseandcollation_databasesystem variables are deprecated; expect them to be removed in a future version of MySQL.Assigning a value to the session
character_set_databaseandcollation_databasesystem 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=nameSystem Variable collation_serverScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value utf8mb4_0900_ai_ciThe server's default collation. See Section 10.15, “Character Set Configuration”.
-
Command-Line Format --completion-type=#System Variable completion_typeScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value NO_CHAINValid Values NO_CHAINCHAINRELEASE012The 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)COMMITandROLLBACKare unaffected. This is the default value.CHAIN(or 1)COMMITandROLLBACKare equivalent toCOMMIT AND CHAINandROLLBACK AND CHAIN, respectively. (A new transaction starts immediately with the same isolation level as the just-terminated transaction.)RELEASE(or 2)COMMITandROLLBACKare equivalent toCOMMIT RELEASEandROLLBACK RELEASE, respectively. (The server disconnects after terminating the transaction.)completion_typeaffects transactions that begin withSTART TRANSACTIONorBEGINand end withCOMMITorROLLBACK. 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_insertScope Global Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value AUTOValid Values NEVERAUTOALWAYS012If
AUTO(the default), MySQL permitsINSERTandSELECTstatements to run concurrently forMyISAMtables 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 MyISAMtables that do not have holesALWAYS(or 2)Enables concurrent inserts for all MyISAMtables, 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_insertis set toNEVER.See also Section 8.11.3, “Concurrent Inserts”.
-
Command-Line Format --connect-timeout=#System Variable connect_timeoutScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 10Minimum Value 2Maximum Value 31536000The 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_timeoutvalue might help if clients frequently encounter errors of the formLost connection to MySQL server at '.XXX', system error:errno -
System Variable core_fileScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value OFFWhether to write a core file if the server unexpectedly exits. This variable is set by the
--core-fileoption. -
Command-Line Format --create-admin-listener-thread[={OFF|ON}]Introduced 8.0.14 System Variable create_admin_listener_threadScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value OFFWhether 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_threadhas no effect ifadmin_addressis 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_depthScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 1000Minimum Value 0Maximum Value 4294967295The 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_nameSystem Variable datadirScope Global Dynamic No SET_VARHint 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
datadirvalue as an absolute path. -
Command-Line Format --debug[=debug_options]System Variable debugScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value (Windows) d:t:i:O,\mysqld.traceDefault Value (Unix) d:t:i:o,/tmp/mysqld.traceThis 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
--debugoption 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_syncScope Session Dynamic Yes SET_VARHint 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=1CMake 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_syncisOFF. If the server is started with--debug-sync-timeout=, whereNNis a timeout value greater than 0, Debug Sync is enabled and the value ofdebug_syncisON - current signalfollowed by the signal name. Also,Nbecomes 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_nameSystem Variable default_authentication_pluginScope Global Dynamic No SET_VARHint AppliesNo Type Enumeration Default Value caching_sha2_passwordValid Values mysql_native_passwordsha256_passwordcaching_sha2_passwordThe 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_passwordis 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_pluginvalue affects these aspects of server operation:It determines which authentication plugin the server assigns to new accounts created by
CREATE USERandGRANTstatements 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_utf8mb4Scope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Enumeration Valid Values utf8mb4_0900_ai_ciutf8mb4_general_ciFor internal use by replication. This system variable is set to the default collation for the
utf8mb4character 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 forutf8mb4in MySQL 5.7 isutf8mb4_general_ci, bututf8mb4_0900_ai_ciin 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
utf8mb4collation is used in the following statements:CREATE TABLEandALTER TABLEhaving aCHARACTER SET utf8mb4clause without aCOLLATIONclause, either for the table character set or for a column character set.CREATE DATABASEandALTER DATABASEhaving aCHARACTER SET utf8mb4clause without aCOLLATIONclause.Any statement containing a string literal of the form
_utf8mb4'without asome text'COLLATEclause.
-
Command-Line Format --default-password-lifetime=#System Variable default_password_lifetimeScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 65535This variable defines the global automatic password expiration policy. The default
default_password_lifetimevalue is 0, which disables automatic password expiration. If the value ofdefault_password_lifetimeis a positive integerN, it indicates the permitted password lifetime; passwords must be changed everyNdays.The global password expiration policy can be overridden as desired for individual accounts using the password expiration option of the
CREATE USERandALTER USERstatements. See Section 6.2.15, “Password Management”. -
Command-Line Format --default-storage-engine=nameSystem Variable default_storage_engineScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value InnoDBThe 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
TEMPORARYtables, set thedefault_tmp_storage_enginesystem variable.To see which storage engines are available and enabled, use the
SHOW ENGINESstatement or query theINFORMATION_SCHEMAENGINEStable.If you disable the default storage engine at server startup, you must set the default engine for both permanent and
TEMPORARYtables 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_encryptionScope Global, Session Dynamic Yes SET_VARHint AppliesYes Type Boolean Default Value OFFDefines the default encryption setting applied to schemas and general tablespaces when they are created without specifying an
ENCRYPTIONclause.The
default_table_encryptionvariable is only applicable to user-created schemas and general tablespaces. It does not govern encryption of themysqlsystem tablespace.Setting the runtime value of
default_table_encryptionrequires theSYSTEM_VARIABLES_ADMINandTABLE_ENCRYPTION_ADMINprivileges, or the deprecatedSUPERprivilege.default_table_encryptionsupportsSET PERSISTandSET PERSIST_ONLYsyntax. 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=nameSystem Variable default_tmp_storage_engineScope Global, Session Dynamic Yes SET_VARHint AppliesYes Type Enumeration Default Value InnoDBThe default storage engine for
TEMPORARYtables (created withCREATE TEMPORARY TABLE). To set the storage engine for permanent tables, set thedefault_storage_enginesystem 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
TEMPORARYtables to a different engine, or else the server does not start. -
Command-Line Format --default-week-format=#System Variable default_week_formatScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 7The 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_writeScope Global Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value ONValid Values ONOFFALLThis variable specifies how to use delayed key writes. It applies only to
MyISAMtables. 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_WRITEtable option that can be used inCREATE TABLEstatements.Option Description OFFDELAY_KEY_WRITEis ignored.ONMySQL honors any DELAY_KEY_WRITEoption specified inCREATE TABLEstatements. This is the default value.ALLAll new opened tables are treated as if they were created with the DELAY_KEY_WRITEoption enabled.NoteIf you set this variable to
ALL, you should not useMyISAMtables 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_WRITEis 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 allMyISAMtables by starting the server with themyisam_recover_optionssystem 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_writeis 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_limitScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 100Minimum Value 1Maximum Value (64-bit platforms) 18446744073709551615Maximum Value (32-bit platforms) 4294967295This system variable is deprecated (because
DELAYEDinserts 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_timeoutScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 300This system variable is deprecated (because
DELAYEDinserts 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_sizeScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 1000Minimum Value 1Maximum Value (64-bit platforms) 18446744073709551615Maximum Value (32-bit platforms) 4294967295This system variable is deprecated (because
DELAYEDinserts 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_enginesScope Global Dynamic No SET_VARHint AppliesNo Type String Default Value empty stringThis variable indicates which storage engines cannot be used to create tables or tablespaces. For example, to prevent new
MyISAMorFEDERATEDtables from being created, start the server with these lines in the server option file:[mysqld] disabled_storage_engines="MyISAM,FEDERATED"
By default,
disabled_storage_enginesis 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 TABLEorCREATE TABLESPACE, and cannot be used withALTER TABLE ... ENGINEorALTER TABLESPACE ... ENGINEto change the storage engine of existing tables or tablespaces. Attempts to do so result in anER_DISABLED_STORAGE_ENGINEerror.disabled_storage_enginesdoes 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_engineIt is permitted to set the
default_storage_engineordefault_tmp_storage_enginesystem 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_enginesis disabled and has no effect if the server is started with any of these options:--initialize,--initialize-insecure,--skip-grant-tables.NoteSetting
disabled_storage_enginesmight cause an issue with mysql_upgrade. For details, see Section 4.4.5, “mysql_upgrade — Check and Upgrade MySQL Tables”. disconnect_on_expired_passwordCommand-Line Format --disconnect-on-expired-password[={OFF|ON}]System Variable disconnect_on_expired_passwordScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value ONThis 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_passwordis 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