Table of Contents
MySQL Server (mysqld) is the main program that does most of the work in a MySQL installation. This section provides an overview of MySQL Server and covers topics that deal with administering a MySQL installation:
Server configuration
The server log files
Security issues and user-account management
Management of multiple servers on a single machine
mysqld is the MySQL server. The following discussion covers these MySQL server configuration topics:
Startup options that the server supports
Server system variables
Server status variables
How to set the server SQL mode
The server shutdown process
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 12.4.5.17, “SHOW ENGINES Syntax”.
The following table provides a list of all the command line
options, server 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 notification of where each option/variable is valid. If a server option set on the command line or in an option file differs from the name of the corresponding server system or status variable, the variable name is noted immediately below the corresponding option. For status variables, the scope of the variable is shown (Scope) as either global, session, or both. Please see the corresponding sections for details on setting and using the options and variables. Where appropriate, a direct link to further information on the item as available.
Table 5.1. Option/Variable Summary
When you start the mysqld server, you can specify program options using any of the methods described in Section 4.2.3, “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.3.3, “Using Option Files”.
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.
An embedded MySQL server usually reads options from the
[server], [embedded], and
[
groups, where xxxxx_SERVER]xxxxx is the name of the
application into which the server is embedded.
mysqld accepts many command options. For a brief summary, execute mysqld --help. To see the full list, use mysqld --verbose --help.
The following list shows some of the most common server options. Additional options are described in other sections:
Options that affect security: See Section 5.3.4, “Security-Related mysqld Options”.
SSL-related options: See Section 5.5.8.3, “SSL Command Options”.
Binary log control options: See Section 5.2.4, “The Binary Log”.
Replication-related options: See Section 17.1.3, “Replication and Binary Logging Options and Variables”.
Options for loading plugins such as pluggable storage engines: See Section 5.1.3, “Server Options for Loading Plugins”.
Options specific to particular storage engines: See
Section 13.5.1, “MyISAM Startup Options”, and
Section 13.6.4, “InnoDB Startup Options and System Variables”.
You can also set the values of server system variables by using variable names as options, as described at the end of this section.
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 will adjust a value upward. For example, if you assign a value of 0 to an option for which the minimal value is 1024, the server will set the value to 1024.
Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.
Some 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 will be located under
/var/mysql/data. If the value is an absolute
path name, its location is as given by the path name.
--help, -?
| Command-Line Format | -? | ||
--help | |||
| Option-File Format | help | ||
Display a short help message and exit. Use both the
--verbose and
--help options to see the full
message.
| Command-Line Format | --allow-suspicious-udfs | ||
| Option-File Format | allow-suspicious-udfs | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
This option controls whether user-defined functions that have
only an xxx symbol for the main function
can be loaded. By default, the option is off and only UDFs
that have at least one auxiliary symbol can be loaded; this
prevents attempts at loading functions from shared object
files other than those containing legitimate UDFs. See
Section 23.3.2.6, “User-Defined Function Security Precautions”.
| Command-Line Format | --ansi | ||
-a | |||
| Option-File Format | ansi | ||
Use standard (ANSI) SQL syntax instead of MySQL syntax. For
more precise control over the server SQL mode, use the
--sql-mode option instead. See
Section 1.8.3, “Running MySQL in ANSI Mode”, and
Section 5.1.7, “Server SQL Modes”.
| Command-Line Format | --basedir=path | ||
-b | |||
| Option-File Format | basedir | ||
| Option Sets Variable | Yes, basedir | ||
| Variable Name | basedir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The path to the MySQL installation directory. All paths are usually resolved relative to this directory.
| Command-Line Format | --big-tables | ||
| Option-File Format | big-tables | ||
| Option Sets Variable | Yes, big_tables | ||
| Variable Name | big-tables | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
Enable large result sets by saving all temporary sets in files. This option prevents most “table full” errors, but also slows down queries for which in-memory tables would suffice. Since MySQL 3.23.2, the server is able to handle large result sets automatically by using memory for small temporary tables and switching to disk tables where necessary.
| Command-Line Format | --bind-address=name | ||
| Option-File Format | bind-address=name | ||
| Permitted Values | |||
| Type | string | ||
| Default | 0.0.0.0 | ||
| Range | 0.0.0.0-255.255.255.255 | ||
The IP address to bind to. Only one address can be selected. If this option is specified multiple times, the last address given is used.
If no address or 0.0.0.0 is specified, the
server listens on all interfaces.
--binlog-format={ROW|STATEMENT|MIXED}
| Command-Line Format | --binlog-format=format | ||
| Option-File Format | binlog-format=format | ||
| Option Sets Variable | Yes, binlog_format | ||
| Variable Name | binlog_format | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | STATEMENT | ||
| Valid Values | ROW, STATEMENT, MIXED | ||
Specify whether to use row-based, statement-based, or mixed replication. Statement-based is the default in MySQL 5.5. See Section 17.1.2, “Replication Formats”.
Previous to MySQL 5.5, setting the binary logging format
without enabling binary logging prevented the MySQL server
from starting. In MySQL 5.5, the server starts in
such cases, the binlog_format
global system variable is set, and a warning is logged instead
of an error. (Bug#42928)
| Command-Line Format | --bootstrap | ||
| Option-File Format | bootstrap | ||
This option is used by the mysql_install_db script to create the MySQL privilege tables without having to start a full MySQL server.
This option is unavailable if MySQL was configured with the
DISABLE_GRANT_OPTIONS compiler
flag. See Section 2.11.4, “MySQL Source-Configuration Options”.
| Command-Line Format | --character-sets-dir=path | ||
| Option-File Format | character-sets-dir=path | ||
| Option Sets Variable | Yes, character_sets_dir | ||
| Variable Name | character-sets-dir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The directory where character sets are installed. See Section 9.5, “Character Set Configuration”.
--character-set-client-handshake
| Command-Line Format | --character-set-client-handshake | ||
| Option-File Format | character-set-client-handshake | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | TRUE | ||
Do not ignore character set information sent by the client. To
ignore client information and use the default server character
set, use
--skip-character-set-client-handshake;
this makes MySQL behave like MySQL 4.0.
--character-set-filesystem=
charset_name
| Command-Line Format | --character-set-filesystem=name | ||
| Option-File Format | character-set-filesystem | ||
| Option Sets Variable | Yes, character_set_filesystem | ||
| Variable Name | character_set_filesystem | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The file system character set. This option sets the
character_set_filesystem
system variable.
--character-set-server=,
charset_name-C
charset_name
| Command-Line Format | --character-set-server | ||
| Option-File Format | character-set-server | ||
| Option Sets Variable | Yes, character_set_server | ||
| Variable Name | character_set_server | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
Use charset_name as the default
server character set. See
Section 9.5, “Character Set Configuration”. If you use this
option to specify a nondefault character set, you should also
use --collation-server to
specify the collation.
--chroot=,
path-r
path
| Command-Line Format | --chroot=name | ||
-r name | |||
| Option-File Format | chroot | ||
| Permitted Values | |||
| Type | file name | ||
Put the mysqld server in a closed
environment during startup by using the
chroot() system call. This is a recommended
security measure. Note that use of this option somewhat limits
LOAD DATA
INFILE and
SELECT ... INTO
OUTFILE.
--collation-server=
collation_name
| Command-Line Format | --collation-server | ||
| Option-File Format | collation-server | ||
| Option Sets Variable | Yes, collation_server | ||
| Variable Name | collation_server | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
Use collation_name as the default
server collation. See Section 9.5, “Character Set Configuration”.
| Command-Line Format | --console | ||
| Option-File Format | console | ||
| Platform Specific | windows | ||
(Windows only.) Write error log messages to
stderr and stdout even
if --log-error is specified.
mysqld does not close the console window if
this option is used.
| Command-Line Format | --core-file | ||
| Option-File Format | core-file | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Write a core file if mysqld dies. The name
and location of the core file is system dependent. On Linux, a
core file named
core. is
written to the current working directory of the process, which
for mysqld is the data directory.
pidpid represents the process ID of
the server process. On Mac OS X, a core file named
core. is
written to the pid/cores directory. On
Solaris, use the coreadm command to specify
where to write the core file and how to name it.
For some systems, to get a core file you must also specify the
--core-file-size option to
mysqld_safe. See
Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”. On some systems, such as
Solaris, you do not get a core file if you are also using the
--user option. There might be
additional restrictions or limitations. For example, it might
be necessary to execute ulimit -c unlimited
before starting the server. Consult your system documentation.
--datadir=,
path-h
path
| Command-Line Format | --datadir=path | ||
-h | |||
| Option-File Format | datadir | ||
| Option Sets Variable | Yes, datadir | ||
| Variable Name | datadir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The path to the data directory.
--debug[=,
debug_options]-# [
debug_options]
| Command-Line Format | --debug[=debug_options] | ||
| Option-File Format | debug | ||
| Variable Name | debug | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
| Default | 'd:t:o,/tmp/mysqld.trace' | ||
If MySQL is configured with
-DWITH_DEBUG=1, you can use this
option to get a trace file of what mysqld
is doing. A typical debug_options
string is
'd:t:o,.
The default is file_name''d:t:i:o,mysqld.trace'. See
MySQL
Internals: Porting.
Using -DWITH_DEBUG=1 to
configure MySQL with debugging support enables you to use the
--debug="d,parser_debug" option
when you start the server. This causes the Bison parser that
is used to process SQL statements to dump a parser trace to
the server's standard error output. Typically, this output is
written to the error log.
This option may be given multiple times. Values that begin
with + or - are added to
or subtracted from the previous value. For example,
--debug=T
--debug=+P sets the value to
P:T.
| Command-Line Format | --debug-sync-timeout[=#] | ||
| Option-File Format | debug-sync-timeout | ||
| Permitted Values | |||
| Type | numeric | ||
Controls whether the Debug Sync facility for testing and
debugging is enabled. Use of Debug Sync requires that MySQL be
configured with the
-DENABLE_DEBUG_SYNC=1 option
(see Section 2.11.4, “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.
--default-character-set=
charset_name
| Command-Line Format | --default-character-set=name | ||
-C name | |||
| Option-File Format | default-character-set | ||
| Deprecated | 5.0 | ||
| Permitted Values | |||
| Type | string | ||
Use charset_name as the default
character set. This option is deprecated in favor of
--character-set-server. See
Section 9.5, “Character Set Configuration”.
--default-character-set was
removed in MySQL 5.5.3.
--default-collation=
collation_name
| Command-Line Format | --default-collation=name | ||
| Variable Name | default-collation | ||
| Variable Scope | |||
| Dynamic Variable | No | ||
| Deprecated | 4.1.3 | ||
| Permitted Values | |||
| Type | string | ||
Use collation_name as the default
collation. This option is deprecated in favor of
--collation-server. See
Section 9.5, “Character Set Configuration”.
--default-collation was removed
in MySQL 5.5.3.
| Command-Line Format | --default-storage-engine=name | ||
| Option-File Format | default-storage-engine | ||
| Option Sets Variable | Yes, default_storage_engine | ||
| Variable Name | default-storage-engine | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values (<= 5.5.4) | |||
| Type | enumeration | ||
| Default | MyISAM | ||
| Permitted Values (>= 5.5.5) | |||
| Type | enumeration | ||
| Default | InnoDB | ||
Set the default storage engine (table type) for tables. See Chapter 13, Storage Engines.
| Command-Line Format | --default-time-zone=name | ||
| Option-File Format | default-time-zone | ||
| Permitted Values | |||
| Type | string | ||
Set the default server time zone. This option sets the global
time_zone system variable. If
this option is not given, the default time zone is the same as
the system time zone (given by the value of the
system_time_zone system
variable.
--delay-key-write[={OFF|ON|ALL}]
| Command-Line Format | --delay-key-write[=name] | ||
| Option-File Format | delay-key-write | ||
| Option Sets Variable | Yes, delay_key_write | ||
| Variable Name | delay-key-write | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | ON | ||
| Valid Values | ON, OFF, ALL | ||
Specify how to use delayed key writes. Delayed key writing
causes key buffers not to be flushed between writes for
MyISAM tables. OFF
disables delayed key writes. ON enables
delayed key writes for those tables that were created with the
DELAY_KEY_WRITE option.
ALL delays key writes for all
MyISAM tables. See
Section 7.11.2, “Tuning Server Parameters”, and
Section 13.5.1, “MyISAM Startup Options”.
If you set this variable to ALL, you
should not use MyISAM tables from within
another program (such as another MySQL server or
myisamchk) when the tables are in use.
Doing so leads to index corruption.
| Command-Line Format | --des-key-file=file_name | ||
| Option-File Format | des-key-file=file_name | ||
Read the default DES keys from this file. These keys are used
by the DES_ENCRYPT() and
DES_DECRYPT() functions.
| Command-Line Format | --enable-named-pipe | ||
| Option-File Format | enable-named-pipe | ||
| Option Sets Variable | Yes, named_pipe | ||
| Platform Specific | windows | ||
Enable support for named pipes. This option applies only on Windows.
| Version Removed | 5.5.7 | ||
| Command-Line Format | --enable-pstack | ||
| Option-File Format | enable-pstack | ||
| Deprecated | 5.1.54 | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
This option is nonfunctional before MySQL 5.5.7 and removed in 5.5.7.
--engine-condition-pushdown={ON|OFF}
| Version Deprecated | 5.5.3 | ||
| Command-Line Format | --engine-condition-pushdown | ||
| Option-File Format | engine-condition-pushdown | ||
| Option Sets Variable | Yes, engine_condition_pushdown | ||
| Variable Name | engine_condition_pushdown | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Deprecated | 5.5.3, by optimizer_switch | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | ON | ||
Sets the
engine_condition_pushdown
system variable. For more information, see
Section 7.13.3, “Engine Condition Pushdown Optimization”.
| Command-Line Format | --event-scheduler[=value] | ||
| Option-File Format | event-scheduler | ||
| Option Sets Variable | Yes, event_scheduler | ||
| Variable Name | event_scheduler | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | OFF | ||
| Valid Values | ON, OFF, DISABLED | ||
Enable or disable, and start or stop, the event scheduler.
For detailed information, see
The
--event-scheduler Option.
--exit-info[=,
flags]-T [
flags]
| Command-Line Format | --exit-info[=flags] | ||
-T [flags] | |||
| Option-File Format | exit-info | ||
| Permitted Values | |||
| Type | numeric | ||
This is a bit mask 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 | ||
| Option-File Format | external-locking | ||
| Option Sets Variable | Yes, skip_external_locking | ||
| Disabled by | skip-external-locking | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Enable external locking (system locking), which is disabled by
default as of MySQL 4.0. Note that if you use this option on a
system on which lockd does not fully work
(such as Linux), it is easy for mysqld to
deadlock.
For more information about external locking, including conditions under which it can and cannot be used, see Section 7.10.5, “External Locking”.
| Command-Line Format | --flush | ||
| Option-File Format | flush | ||
| Variable Name | flush | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Flush (synchronize) all changes to disk after each SQL statement. Normally, MySQL does a write of all changes to disk only after each SQL statement and lets the operating system handle the synchronizing to disk. See Section C.5.4.2, “What to Do If MySQL Keeps Crashing”.
| Command-Line Format | --gdb | ||
| Option-File Format | gdb | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Install an interrupt handler for SIGINT
(needed to stop mysqld with
^C to set breakpoints) and disable stack
tracing and core file handling. See
MySQL
Internals: Porting.
| Command-Line Format | --general-log | ||
| Option-File Format | general-log | ||
| Option Sets Variable | Yes, general_log | ||
| Variable Name | general_log | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Specify the initial general query log state. With no argument
or an argument of 1, the
--general-log option enables
the log. If omitted or given with an argument of 0, the option
disables the log.
| Command-Line Format | --init-file=file_name | ||
| Option-File Format | init-file=file_name | ||
| Option Sets Variable | Yes, init_file | ||
| Variable Name | init_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
Read SQL statements from this file at startup. Each statement must be on a single line and should not include comments.
This option is unavailable if MySQL was configured with the
DISABLE_GRANT_OPTIONS compiler
flag. See Section 2.11.4, “MySQL Source-Configuration Options”.
--innodb-
xxx
The InnoDB options are listed in
Section 13.6.4, “InnoDB Startup Options and System Variables”.
--language=
lang_name,
-L lang_name
| Version Deprecated | 5.5.0 | ||
| Command-Line Format | --language=name | ||
-L | |||
| Option-File Format | language | ||
| Option Sets Variable | Yes, language | ||
| Variable Name | language | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Deprecated | 5.5.0, by lc-messages-dir | ||
| Permitted Values | |||
| Type | directory name | ||
| Default | /usr/local/mysql/share/mysql/english/ | ||
The language to use for error messages.
lang_name can be given as the
language name or as the full path name to the directory where
the language files are installed. See
Section 9.2, “Setting the Error Message Language”.
As of MySQL 5.5,
--lc-messages-dir and
--lc-messages should be used
rather than --language, which
is deprecated and handled as an alias for
--lc-messages-dir.
| Command-Line Format | --large-pages | ||
| Option-File Format | large-pages | ||
| Option Sets Variable | Yes, large_pages | ||
| Variable Name | large_pages | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Platform Specific | linux | ||
| Permitted Values | |||
| Type (linux) | boolean | ||
| Default | FALSE | ||
Some hardware/operating system architectures support memory pages greater than the default (usually 4KB). The actual implementation of this support depends on the underlying hardware and operating system. Applications that perform a lot of memory accesses may obtain performance improvements by using large pages due to reduced Translation Lookaside Buffer (TLB) misses.
MySQL 5.5 supports the Linux implementation of
large page support (which is called HugeTLB in Linux). See
Section 7.11.4.2, “Enabling Large Page Support”. For Solaris support of
large pages, see the description of the
--super-large-pages option.
--large-pages is disabled by
default.
| Command-Line Format | --lc-messages=name | ||
| Option-File Format | lc-messages | ||
| Option Sets Variable | Yes, lc_messages | ||
| Variable Name | lc-messages | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The locale to use for error messages. The server converts the
argument to a language name and combines it with the value of
the --lc-messages-dir to produce the location
for the error message file. See
Section 9.2, “Setting the Error Message Language”.
| Command-Line Format | --lc-messages-dir=path | ||
| Option-File Format | lc-messages-dir | ||
| Option Sets Variable | Yes, lc_messages_dir | ||
| Variable Name | lc-messages-dir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The directory where error messages are located. The value is
used together with the value of --lc-messages
to produce the location for the error message file. See
Section 9.2, “Setting the Error Message Language”.
--log[=,
file_name]-l [
file_name]
| Command-Line Format | --log[=name] | ||
-l | |||
| Option-File Format | log | ||
| Option Sets Variable | Yes, log | ||
| Variable Name | log | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Deprecated | 5.1.29, by general-log | ||
| Permitted Values | |||
| Type | string | ||
| Default | OFF | ||
This option enables logging to the general query log, which
contains entries that record client connections and SQL
statements received from clients. The log output destination
can be selected with the
--log-output option. If you
omit the file name, MySQL uses
host_name.log
The --log option is deprecated
and will be removed (along with the
log system variable) in MySQL
7.0. Instead, use the
--general_log option to enable
the general query log and the
--general_log_file=
option to set the general query log file name.
file_name
| Command-Line Format | --log-error[=name] | ||
| Option-File Format | log-error | ||
| Option Sets Variable | Yes, log_error | ||
| Variable Name | log_error | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
Log errors and startup messages to this file. See
Section 5.2.2, “The Error Log”. If you omit the file name, MySQL
uses
host_name.err.err.
| Command-Line Format | --log-isam[=name] | ||
| Option-File Format | log-isam | ||
| Permitted Values | |||
| Type | file name | ||
Log all MyISAM changes to this file (used
only when debugging MyISAM).
| Command-Line Format | --log-long-format | ||
-0 | |||
| Option-File Format | log-long-format | ||
| Deprecated | 4.1 | ||
Log extra information to the binary log and slow query log, if
they have been activated. For example, the user name and
timestamp are logged for all queries. This option is
deprecated, as it now represents the default logging behavior.
(See the description for
--log-short-format.) The
--log-queries-not-using-indexes
option is available for the purpose of logging queries that do
not use indexes to the slow query log.
--log-long-format was removed
in MySQL 5.5.3.
| Command-Line Format | --log-output[=name] | ||
| Option-File Format | log-output | ||
| Option Sets Variable | Yes, log_output | ||
| Variable Name | log_output | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | set | ||
| Default | FILE | ||
| Valid Values | TABLE, FILE, NONE | ||
This option determines the destination for general query log
and slow query log output. The option value can be given as
one or more of the words TABLE,
FILE, or NONE. If the
option is given without a value, the default is
FILE. TABLE select
logging to the general_log
and slow_log tables in the
mysql database as a destination.
FILE selects logging to log files as a
destination. NONE disables logging. If
NONE is present in the option value, it
takes precedence over any other words that are present.
TABLE and FILE can both
be given to select to both log output destinations.
This option selects log output destinations, but does not
enable log output. To do that, use the
--general_log and
--slow_query_log options. For
FILE logging, the
--general_log_file and
-slow_query_log_file options determine the
log file location. For more information, see
Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”.
--log-queries-not-using-indexes
| Command-Line Format | --log-queries-not-using-indexes | ||
| Option-File Format | log-queries-not-using-indexes | ||
| Option Sets Variable | Yes, log_queries_not_using_indexes | ||
| Variable Name | log_queries_not_using_indexes | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
If you are using this option with the slow query log enabled, queries that are expected to retrieve all rows are logged. See Section 5.2.5, “The Slow Query Log”. This option does not necessarily mean that no index is used. For example, a query that uses a full index scan uses an index but would be logged because the index would not limit the number of rows.
| Command-Line Format | --log-short-format | ||
| Option-File Format | log-short-format | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Originally intended to log less information to the binary log and slow query log, if they have been activated. However, this option is not operational.
| Command-Line Format | --log-slow-admin-statements | ||
| Option-File Format | log-slow-admin-statements | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Log slow administrative statements such as
OPTIMIZE TABLE,
ANALYZE TABLE, and
ALTER TABLE to the slow query
log.
--log-slow-queries[=
file_name]
| Command-Line Format | --log-slow-queries[=name] | ||
| Option-File Format | log-slow-queries | ||
| Option Sets Variable | Yes, log_slow_queries | ||
| Variable Name | log_slow_queries | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Deprecated | 5.1.29, by slow-query-log | ||
| Permitted Values | |||
| Type | boolean | ||
This option enables logging to the slow query log, which
contains entries for all queries that have taken more than
long_query_time seconds to
execute. See the descriptions of the
--log-long-format and
--log-short-format options for
details. The log output destination can be selected with the
--log-output option. If you
omit the file name, MySQL uses
host_name-slow.log
The --log-slow-queries option
is deprecated and will be removed (along with the
log_slow_queries system
variable) in MySQL 7.0. Instead, use the
--slow_query_log option to
enable the slow query log and the
--slow_query_log_file=
option to set the slow query log file name.
file_name
| Command-Line Format | --log-tc=name | ||
| Option-File Format | log-tc | ||
| Permitted Values | |||
| Type | file name | ||
| Default | tc.log | ||
The name of the memory-mapped transaction coordinator log file
(for XA transactions that affect multiple storage engines when
the binary log is disabled). The default name is
tc.log. The file is created under the
data directory if not given as a full path name. Currently,
this option is unused.
| Command-Line Format | --log-tc-size=# | ||
| Option-File Format | log-tc-size | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 24576 | ||
| Max Value | 4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 24576 | ||
| Max Value | 18446744073709547520 | ||
The size in bytes of the memory-mapped transaction coordinator log. The default size is 24KB.
--log-warnings[=,
level]-W [
level]
| Command-Line Format | --log-warnings[=#] | ||
-W [#] | |||
| Option-File Format | log-warnings | ||
| Option Sets Variable | Yes, log_warnings | ||
| Variable Name | log_warnings | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Disabled by | skip-log-warnings | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 1 | ||
| Range | 0-18446744073709547520 | ||
Print out warnings such as Aborted
connection... to the error log. Enabling this option
is recommended, for example, if you use replication (you get
more information about what is happening, such as messages
about network failures and reconnections). This option is
enabled (1) by default, and the default
level value if omitted is 1. To
disable this option, use
--log-warnings=0. If the value
is greater than 1, aborted connections are written to the
error log, and access-denied errors for new connection
attempts are written. See
Section C.5.2.11, “Communication Errors and Aborted Connections”.
If a slave server was started with
--log-warnings enabled, the
slave prints messages to the error log to provide information
about its status, such as the binary log and relay log
coordinates where it starts its job, when it is switching to
another relay log, when it reconnects after a disconnect, and
so forth. The server logs messages about statements that are
unsafe for statement-based logging only if
--log-warnings is enabled.
| Command-Line Format | --low-priority-updates | ||
| Option-File Format | low-priority-updates | ||
| Option Sets Variable | Yes, low_priority_updates | ||
| Variable Name | low_priority_updates | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Give table-modifying operations
(INSERT,
REPLACE,
DELETE,
UPDATE) lower priority than
selects. This can also be done using {INSERT |
REPLACE | DELETE | UPDATE} LOW_PRIORITY ... to lower
the priority of only one query, or by SET
LOW_PRIORITY_UPDATES=1 to change the priority in one
thread. This affects only storage engines that use only
table-level locking (MyISAM,
MEMORY, MERGE). See
Section 7.10.2, “Table Locking Issues”.
--min-examined-row-limit=
number
| Command-Line Format | --min-examined-row-limit=# | ||
| Option-File Format | min-examined-row-limit | ||
| Variable Name | min_examined_row_limit | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-18446744073709547520 | ||
When this option is set, queries which examine fewer than
number rows are not written to the
slow query log. The default is 0.
| Command-Line Format | --memlock | ||
| Option-File Format | memlock | ||
| Variable Name | locked_in_memory | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Lock the mysqld process in memory. This option might help if you have a problem where the operating system is causing mysqld to swap to disk.
--memlock works on systems that
support the mlockall() system call; this
includes Solaris as well as most Linux distributions that use
a 2.4 or newer kernel. On Linux systems, you can tell whether
or not mlockall() (and thus this option) is
supported by checking to see whether or not it is defined in
the system mman.h file, like this:
shell> grep mlockall /usr/include/sys/mman.h
If mlockall() is supported, you should see
in the output of the previous command something like the
following:
extern int mlockall (int __flags) __THROW;
Using this option requires that you run the server as
root, which, for reasons of security, is
normally not a good idea. See
Section 5.3.6, “How to Run MySQL as a Normal User”.
You must not try to use this option on a system that does
not support the mlockall() system call;
if you do so, mysqld will very likely
crash as soon as you try to start it.
| Command-Line Format | --myisam-block-size=# | ||
| Option-File Format | myisam-block-size | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 1024 | ||
| Range | 1024-16384 | ||
The block size to be used for MyISAM index
pages.
--myisam-recover[=
option[,option]...]]
This option is renamed as of MySQL 5.5.3 to
--myisam-recover-options. See
the description of that option for more information.
--myisam-recover-options[=
option[,option]...]]
| Version Introduced | 5.5.3 | ||
| Command-Line Format | --myisam-recover-options[=name] | ||
| Option-File Format | myisam-recover-options | ||
| Option Sets Variable | Yes, myisam_recover_options | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | OFF | ||
| Valid Values | OFF, DEFAULT, BACKUP, FORCE, QUICK | ||
Set the MyISAM storage engine recovery
mode. The option value is any combination of the values of
DEFAULT, OFF,
BACKUP, FORCE, or
QUICK. If you specify multiple values,
separate them by commas. Specifying the option with no
argument is the same as specifying DEFAULT,
and specifying with an explicit value of ""
disables recovery (same as not giving the option). If recovery
is enabled, each time mysqld opens a
MyISAM table, it checks whether the table
is marked as crashed or was not closed properly. (The last
option works only if you are running with external locking
disabled.) If this is the case, mysqld runs
a check on the table. If the table was corrupted,
mysqld attempts to repair it.
The following options affect how the repair works.
| Option | Description |
|---|---|
DEFAULT | Recovery without backup, forcing, or quick checking. |
OFF | Recovery without backup, forcing, or quick checking. |
BACKUP | If the data file was changed during recovery, save a backup of the
|
FORCE | Run recovery even if we would lose more than one row from the
.MYD file. |
QUICK | Do not check the rows in the table if there are not any delete blocks. |
Before the server automatically repairs a table, it writes a
note about the repair to the error log. If you want to be able
to recover from most problems without user intervention, you
should use the options BACKUP,FORCE. This
forces a repair of a table even if some rows would be deleted,
but it keeps the old data file as a backup so that you can
later examine what happened.
This option was named
--myisam-recover, before MySQL
5.5.3. The old option name still works because it is
recognized as an unambiguous prefix of the new name,
--myisam-recover-options.
(Option prefix recognition occurs as described in
Section 4.2.3, “Specifying Program Options”.)
The option value OFF is available as of
MySQL 5.5.3.
| Command-Line Format | --old-alter-table | ||
| Option-File Format | old-alter-table | ||
| Option Sets Variable | Yes, old_alter_table | ||
| Variable Name | old-alter-table | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
When this option is given, the server does not use the
optimized method of processing an ALTER
TABLE operation. It reverts to using a temporary
table, copying over the data, and then renaming the temporary
table to the original, as used by MySQL 5.0 and earlier. For
more information on the operation of
ALTER TABLE, see
Section 12.1.6, “ALTER TABLE Syntax”.
| Command-Line Format | --old_passwords | ||
| Option-File Format | old-passwords | ||
| Option Sets Variable | Yes, old_passwords | ||
| Variable Name | old_passwords | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Force the server to generate short (pre-4.1) password hashes for new passwords. This is useful for compatibility when the server must support older client programs. See Section 5.3.2.3, “Password Hashing in MySQL”.
| Command-Line Format | --old-style-user-limits | ||
| Option-File Format | old-style-user-limits | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Enable old-style user limits. (Before MySQL 5.0.3, account
resource limits were counted separately for each host from
which a user connected rather than per account row in the
user table.) See
Section 5.5.4, “Setting Account Resource Limits”.
| Command-Line Format | --one-thread | ||
| Option-File Format | one-thread | ||
Only use one thread (for debugging under Linux). This option is available only if the server is built with debugging enabled. See MySQL Internals: Porting.
This option is deprecated; use
--thread_handling=no-threads
instead.
| Command-Line Format | --open-files-limit=# | ||
| Option-File Format | open-files-limit | ||
| Option Sets Variable | Yes, open_files_limit | ||
| Variable Name | open_files_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-65535 | ||
Changes the number of file descriptors available to
mysqld. You should try increasing the value
of this option if mysqld gives you the
error Too many open files.
mysqld uses the option value to reserve
descriptors with setrlimit(). If the
requested number of file descriptors cannot be allocated,
mysqld writes a warning to the error log.
mysqld may attempt to allocate more than
the requested number of descriptors (if they are available),
using the values of
max_connections and
table_open_cache to estimate
whether more descriptors will be needed.
| Command-Line Format | --partition | ||
| Option-File Format | partition | ||
| Option Sets Variable | Yes, have_partitioning | ||
| Variable Name | partition | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Disabled by | skip-partition | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | ON | ||
Enables or disables user-defined partitioning support in the MySQL Server.
| Command-Line Format | --pid-file=file_name | ||
| Option-File Format | pid-file=file_name | ||
| Option Sets Variable | Yes, pid_file | ||
| Variable Name | pid_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The path name of the process ID file. The server creates the file in the data directory unless an absolute path name is given to specify a different directory. This file is used by other programs such as mysqld_safe to determine the server's process ID.
Specifies an option that pertains to a server plugin. For
example, many storage engines can be built as plugins, and for
such engines, options for them can be specified with a
--plugin prefix. Thus, the
--innodb_file_per_table option
for InnoDB can be specified as
--plugin-innodb_file_per_table.
For boolean options that can be enabled or disabled, the
--skip prefix and other alternative formats
are supported as well (see
Section 4.2.3.2, “Program Option Modifiers”). For example,
--skip-plugin-innodb_file_per_table
disables innodb_file_per_table.
The rationale for the --plugin prefix is that
it enables plugin options to be specified unambigously if
there is a name conflict with a built-in server option. For
example, were a plugin writer to name a plugin
“sql” and implement a “mode” option,
the option name might be
--sql-mode, which would
conflict with the built-in option of the same name. In such
cases, references to the conflicting name are resolved in
favor of the built-in option. To avoid the ambiguity, users
can specify the plugin option as
--plugin-sql-mode. Use of the
--plugin prefix for plugin options is
recommended to avoid any question of ambiguity.
| Command-Line Format | --plugin-load=plugin_list | ||
| Option-File Format | plugin-load | ||
| Permitted Values | |||
| Type | string | ||
This option tells the server to load the named plugins at
startup. The option value is a semicolon-separated list of
name=plugin_libraryname is the name of the
plugin, and plugin_library is the
name of the shared library that contains the plugin code. Each
library file must be located in the directory named by the
plugin_dir system variable.
For example, if plugins named myplug1 and
myplug2 have library files
myplug1.so and
myplug2.so, use this option to load them
at startup:
shell> mysqld --plugin-load=myplug1=myplug1.so;myplug2=myplug2.so
All plugins to load must be named in the same
--plugin-load option. If
multiple --plugin-load options
are given, only the last one is used.
Each named plugin is loaded for a single invocation of
mysqld only. After a restart, the plugin is
not loaded unless --plugin-load
is used again. This is in contrast to
INSTALL PLUGIN, which adds an
entry to the mysql.plugins table to cause
the plugin to be loaded for every normal server startup.
Under normal startup, the server determines which plugins to
load by reading the mysql.plugins system
table. If the server is started with the
--skip-grant-tables option, it
does not consult the mysql.plugins table
and thus does not load plugins installed with the
INSTALL PLUGIN statement.
--plugin-load enables plugins
to be loaded even when
--skip-grant-tables is given.
--plugin-load also enables
plugins to be loaded at startup under configurations when
plugins cannot be loaded at runtime.
For additional information about plugin loading, see Section 12.4.3.1, “Installing and Uninstalling Plugins”.
--port=,
port_num-P
port_num
| Command-Line Format | --port=# | ||
-P | |||
| Option-File Format | port | ||
| Option Sets Variable | Yes, port | ||
| Variable Name | port | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 3306 | ||
The port number to use when listening for TCP/IP connections.
The port number must be 1024 or higher unless the server is
started by the root system user.
| Command-Line Format | --port-open-timeout=# | ||
| Option-File Format | port-open-timeout | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
On some systems, when the server is stopped, the TCP/IP port might not become available immediately. If the server is restarted quickly afterward, its attempt to reopen the port can fail. This option indicates how many seconds the server should wait for the TCP/IP port to become free if it cannot be opened. The default is not to wait.
| Command-Line Format | --safe-mode | ||
| Option-File Format | safe-mode | ||
| Deprecated | 5.0 | ||
Skip some optimization stages.
| Command-Line Format | --safe-show-database | (until 4.1.1) | |
| Option-File Format | safe-show-database | ||
| Variable Name | safe_show_database | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Deprecated | 4.0.2 | ||
| Permitted Values | |||
| Type | boolean | ||
This option is deprecated and does not do anything because
there is a SHOW DATABASES
privilege that can be used to control access to database names
on a per-account basis. See
Section 5.4.1, “Privileges Provided by MySQL”.
--safe-show-database was
removed in MySQL 5.5.3.
| Command-Line Format | --safe-user-create | ||
| Option-File Format | safe-user-create | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
If this option is enabled, a user cannot create new MySQL
users by using the GRANT
statement unless the user has the
INSERT privilege for the
mysql.user table or any column in the
table. If you want a user to have the ability to create new
users that have those privileges that the user has the right
to grant, you should grant the user the following privilege:
GRANT INSERT(user) ON mysql.user TO 'user_name'@'host_name';
This ensures that the user cannot change any privilege columns
directly, but has to use the
GRANT statement to give
privileges to other users.
| Command-Line Format | --secure-auth | ||
| Option-File Format | secure-auth | ||
| Option Sets Variable | Yes, secure_auth | ||
| Variable Name | secure_auth | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Disallow authentication by clients that attempt to use accounts that have old (pre-4.1) passwords.
| Command-Line Format | --secure-file-priv=path | ||
| Option-File Format | secure-file-priv=path | ||
| Option Sets Variable | Yes, secure_file_priv | ||
| Variable Name | secure-file-priv | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
This option limits the effect of the
LOAD_FILE() function and the
LOAD DATA and
SELECT ... INTO
OUTFILE statements to work only with files in the
specified directory.
Enable shared-memory connections by local clients. This option is available only on Windows.
--shared-memory-base-name=
name
The name of shared memory to use for shared-memory
connections. This option is available only on Windows. The
default name is MYSQL. The name is case
sensitive.
Turn off the ability to select and insert at the same time on
MyISAM tables. (This is to be used only if
you think you have found a bug in this feature.) See
Section 7.10.3, “Concurrent Inserts”.
Do not use external locking (system locking). For more information about external locking, including conditions under which it can and cannot be used, see Section 7.10.5, “External Locking”.
External locking has been disabled by default since MySQL 4.0.
| Command-Line Format | --skip-event-scheduler | ||
--disable-event-scheduler | |||
| Option-File Format | skip-event-scheduler | ||
Turns the Event Scheduler OFF. This is not
the same as disabling the Event Scheduler, which requires
setting
--event-scheduler=DISABLED; see
The
--event-scheduler Option, for more
information.
This option causes the server to start without using the
privilege system at all, which gives anyone with access to the
server unrestricted access to all
databases. You can cause a running server to start
using the grant tables again by executing mysqladmin
flush-privileges or mysqladmin
reload command from a system shell, or by issuing a
MySQL FLUSH
PRIVILEGES statement after connecting to the server.
This option also suppresses loading of plugins that were
installed with the INSTALL
PLUGIN statement, user-defined functions (UDFs), and
scheduled events. To cause plugins to be loaded anyway, use
the --plugin-load option.
--skip-grant-tables is
unavailable if MySQL was configured with the
DISABLE_GRANT_OPTIONS compiler
flag. See Section 2.11.4, “MySQL Source-Configuration Options”.
Do not use the internal host name cache for faster name-to-IP resolution. Instead, query the DNS server every time a client connects. See Section 7.11.5.2, “How MySQL Uses DNS”.
Disable the InnoDB storage engine. In this
case, the server will not start if the default storage engine
is set to InnoDB. Use
--default-storage-engine to set
the default to some other engine if necessary.
Do not resolve host names when checking client connections.
Use only IP addresses. If you use this option, all
Host column values in the grant tables must
be IP addresses or localhost. See
Section 7.11.5.2, “How MySQL Uses DNS”.
Do not listen for TCP/IP connections at all. All interaction with mysqld must be made using named pipes or shared memory (on Windows) or Unix socket files (on Unix). This option is highly recommended for systems where only local clients are permitted. See Section 7.11.5.2, “How MySQL Uses DNS”.
| Command-Line Format | --skip-partition | ||
--disable-partition | |||
| Option-File Format | skip-partition | ||
Disables user-defined partitioning. Existing partitioned tables cannot be accessed when the server has been started with this option.
Options that begin with --ssl
specify whether to permit clients to connect using SSL and
indicate where to find SSL keys and certificates. See
Section 5.5.8.3, “SSL Command Options”.
| Command-Line Format | --standalone | ||
| Option-File Format | standalone | ||
| Platform Specific | windows | ||
Available on Windows only; instructs the MySQL server not to run as a service.
Standard use of large pages in MySQL attempts to use the
largest size supported, up to 4MB. Under Solaris, a
“super large pages” feature enables uses of pages
up to 256MB. This feature is available for recent SPARC
platforms. It can be enabled or disabled by using the
--super-large-pages or
--skip-super-large-pages
option.
--symbolic-links,
--skip-symbolic-links
| Command-Line Format | --symbolic-links | ||
| Option-File Format | symbolic-links | ||
Enable or disable symbolic link support. This option has different effects on Windows and Unix:
On Windows, enabling symbolic links enables you to
establish a symbolic link to a database directory by
creating a
db_name.sym
On Unix, enabling symbolic links means that you can link a
MyISAM index file or data file to
another directory with the INDEX
DIRECTORY or DATA DIRECTORY
options of the CREATE TABLE
statement. If you delete or rename the table, the files
that its symbolic links point to also are deleted or
renamed. See Section 7.11.3.1.2, “Using Symbolic Links for Tables on Unix”.
| Version Removed | 5.5.6 | ||
| Command-Line Format | --skip-safemalloc | ||
| Option-File Format | skip-safemalloc | ||
Previously, if MySQL was configured with full debugging
support, all MySQL programs check for memory overruns during
each memory allocation and memory freeing operation. This
checking is very slow, so for the server you can avoid it when
you do not need it by using the
--skip-safemalloc option.
safemalloc, along with this option, was
removed in MySQL 5.5.6.
| Command-Line Format | --skip-show-database | ||
| Option-File Format | skip-show-database | ||
| Option Sets Variable | Yes, skip_show_database | ||
| Variable Name | skip_show_database | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
With this option, the SHOW
DATABASES statement is permitted only to users who
have the SHOW DATABASES
privilege, and the statement displays all database names.
Without this option, SHOW
DATABASES is permitted to all users, but displays
each database name only if the user has the
SHOW DATABASES privilege or
some privilege for the database. Note that
any global privilege is considered a
privilege for the database.
| Command-Line Format | --skip-stack-trace | ||
| Option-File Format | skip-stack-trace | ||
Do not write stack traces. This option is useful when you are running mysqld under a debugger. On some systems, you also must use this option to get a core file. See MySQL Internals: Porting.
| Command-Line Format | --skip-thread-priority | ||
| Option-File Format | skip-thread-priority | ||
| Deprecated | 5.1.29 | ||
Disable using thread priorities for faster response time. This option is deprecated.
| Command-Line Format | --slow-query-log | ||
| Option-File Format | slow-query-log | ||
| Option Sets Variable | Yes, slow_query_log | ||
| Variable Name | slow_query_log | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Specify the initial slow query log state. With no argument or
an argument of 1, the
--slow-query-log option enables
the log. If omitted or given with an argument of 0, the option
disables the log.
| Command-Line Format | --socket=name | ||
| Option-File Format | socket | ||
| Option Sets Variable | Yes, socket | ||
| Variable Name | socket | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
| Default | /tmp/mysql.sock | ||
On Unix, this option specifies the Unix socket file to use
when listening for local connections. The default value is
/tmp/mysql.sock. If this option is given,
the server creates the file in the data directory unless an
absolute path name is given to specify a different directory.
On Windows, the option specifies the pipe name to use when
listening for local connections that use a named pipe. The
default value is MySQL (not case
sensitive).
--sql-mode=
value[,value[,value...]]
| Command-Line Format | --sql-mode=name | ||
| Option-File Format | sql-mode | ||
| Option Sets Variable | Yes, sql_mode | ||
| Variable Name | sql_mode | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | set | ||
| Default | '' | ||
| Valid Values | ALLOW_INVALID_DATES, ANSI_QUOTES, ERROR_FOR_DIVISION_BY_ZERO, HIGH_NOT_PRECEDENCE, IGNORE_SPACE, NO_AUTO_CREATE_USER, NO_AUTO_VALUE_ON_ZERO, NO_BACKSLASH_ESCAPES, NO_DIR_IN_CREATE, NO_ENGINE_SUBSTITUTION, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_UNSIGNED_SUBTRACTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY, PAD_CHAR_TO_FULL_LENGTH, PIPES_AS_CONCAT, REAL_AS_FLOAT, STRICT_ALL_TABLES, STRICT_TRANS_TABLES | ||
Set the SQL mode. See Section 5.1.7, “Server SQL Modes”.
| Command-Line Format | --sysdate-is-now | ||
| Option-File Format | sysdate-is-now | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
SYSDATE() by default returns
the time at which it executes, not the time at which the
statement in which it occurs begins executing. This differs
from the behavior of NOW().
This option causes SYSDATE() to
be an alias for NOW(). For
information about the implications for binary logging and
replication, see the description for
SYSDATE() in
Section 11.7, “Date and Time Functions” and for SET
TIMESTAMP in
Section 5.1.4, “Server System Variables”.
--tc-heuristic-recover={COMMIT|ROLLBACK}
| Command-Line Format | --tc-heuristic-recover=name | ||
| Option-File Format | tc-heuristic-recover | ||
| Permitted Values | |||
| Type | enumeration | ||
| Valid Values | COMMIT, RECOVER | ||
The type of decision to use in the heuristic recovery process. Currently, this option is unused.
| Command-Line Format | --temp-pool | ||
| Option-File Format | temp-pool | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | TRUE | ||
This option causes most temporary files created by the server to use a small set of names, rather than a unique name for each new file. This works around a problem in the Linux kernel dealing with creating many new files with different names. With the old behavior, Linux seems to “leak” memory, because it is being allocated to the directory entry cache rather than to the disk cache. This option is ignored except on Linux.
| Command-Line Format | --transaction-isolation=name | ||
| Option-File Format | transaction-isolation | ||
| Permitted Values | |||
| Type | enumeration | ||
| Valid Values | READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE | ||
Sets the default transaction isolation level. The
level value can be
READ-UNCOMMITTED,
READ-COMMITTED,
REPEATABLE-READ, or
SERIALIZABLE. See
Section 12.3.6, “SET TRANSACTION Syntax”.
--tmpdir=,
path-t
path
| Command-Line Format | --tmpdir=path | ||
-t | |||
| Option-File Format | tmpdir | ||
| Option Sets Variable | Yes, tmpdir | ||
| Variable Name | tmpdir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The path of the directory to use for creating temporary files.
It might be useful if your default /tmp
directory resides on a partition that is too small to hold
temporary tables. This option accepts several paths that are
used in round-robin fashion. Paths should be separated by
colon characters (“:”) on Unix
and semicolon characters (“;”)
on Windows. If the MySQL server is acting as a replication
slave, you should not set
--tmpdir to point to a
directory on a memory-based file system or to a directory that
is cleared when the server host restarts. For more information
about the storage location of temporary files, see
Section C.5.4.4, “Where MySQL Stores Temporary Files”. A replication slave needs
some of its temporary files to survive a machine restart so
that it can replicate temporary tables or
LOAD DATA
INFILE operations. If files in the temporary file
directory are lost when the server restarts, replication
fails.
--user={,
user_name|user_id}-u
{
user_name|user_id}
| Command-Line Format | --user=name | ||
-u name | |||
| Option-File Format | user | ||
| Permitted Values | |||
| Type | string | ||
Run the mysqld server as the user having
the name user_name or the numeric
user ID user_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 as
root. See
Section 5.3.1, “General Security Guidelines”.
To avoid a possible security hole where a user adds a
--user=root option to a
my.cnf file (thus causing the server to
run as root), mysqld
uses only the first --user
option specified and produces a warning if there are multiple
--user options. Options in
/etc/my.cnf and
$MYSQL_HOME/my.cnf are processed before
command-line options, so it is recommended that you put a
--user option in
/etc/my.cnf and specify a value other
than root. The option in
/etc/my.cnf is found before any other
--user options, which ensures
that the server runs as a user other than
root, and that a warning results if any
other --user option is found.
Use this option with the --help
option for detailed help.
--version, -V
Display version information and exit.
You can assign a value to a server system variable by using an
option of the form
--.
For example, var_name=value--key_buffer_size=32M
sets the key_buffer_size variable
to a value of 32MB.
Note that 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.
If you want to restrict the maximum value to which a variable can
be set at runtime with
SET, you can
define this by using the
--maximum-
command-line option.
var_name=value
You can change the values of most system variables for a running
server with the
SET
statement. See Section 12.4.4, “SET Syntax”.
Section 5.1.4, “Server System Variables”, provides a full description for all variables, and additional information for setting them at server startup and runtime. Section 7.11.2, “Tuning Server Parameters”, includes information on optimizing the server by tuning system variables.
The server can load plugins under the control of startup options.
For example, many storage engines can be built in pluggable form
and loaded when the server starts. In the following descriptions,
plugin_name stands for a plugin name
such as innodb.
In MySQL 5.5, these options control plugin loading:
--
plugin_name=OFF
Do not enable the plugin.
--
plugin_name[=ON]
Enable the plugin. If plugin initialization fails, start the
server anyway, but with the plugin disabled. Specifying the
option as
--
without a value also enables the plugin.
plugin_name
--
plugin_name=FORCE
Enable the plugin. If plugin initialization fails, do not start the server. In other words, force the server to run with the plugin or not at all.
The values OFF, ON, and
FORCE are not case sensitive.
If a plugin is disabled, either explicitly with
OFF or implicitly because it was enabled with
ON but failed to initialize, aspects of server
operation that require the plugin will change. For example, if the
plugin implements a storage engine, existing tables for the
storage engine become inaccessible, and attempts to create new
tables for the storage engine result in tables that use the
default storage engine unless the
NO_ENGINE_SUBSTITUTION SQL mode
has been enabled to cause an error to occur instead.
Suppose that CSV, InnoDB,
and ARCHIVE are built as pluggable storage
engines and that you want the server to load them at startup,
subject to these conditions: The server is permitted to run if
CSV initialization fails, but must require that
InnoDB initialization succeed.
ARCHIVE should be disabled. To accomplish that,
use these lines in an option file:
[mysqld] csv=ON innodb=FORCE archive=OFF
--enable-
is also supported and is the same as
plugin_name--.
plugin_name=ON--disable-
and plugin_name--skip-
are also supported and are the same as
plugin_name--.
plugin_name=OFF
Before MySQL 5.5, plugin options are boolean options (see Section 4.2.3.2, “Program Option Modifiers”). That is, any of these options enable the plugin:
--plugin_name--plugin_name=1 --enable-plugin_name
And these options disable the plugin:
--plugin_name=0 --disable-plugin_name--skip-plugin_name
If the plugin fails to initialize, the server runs with the plugin disabled.
If you upgrade to MySQL 5.5 from an older version and
previously used options of the form
-- or
plugin_name=0--, the
equivalent options are now
plugin_name=1-- and
plugin_name=OFF--,
respectively. You also have the choice of requiring plugins to
start successfully by using
plugin_name=ON--.
plugin_name=FORCE
Plugins can also be loaded at runtime, using the
INSTALL PLUGIN statement. See
Section 12.4.3.1, “Installing and Uninstalling Plugins”.
The MySQL server maintains many system variables that indicate how
it is configured. Each system variable has a default value. System
variables can be set at server startup using options on the
command line or in an option file. Most of them can be changed
dynamically while the server is running by means of the
SET
statement, which enables you to modify operation of the server
without having to stop and restart it. You can refer to system
variable values in expressions.
There are several ways to see the names and values of system variables:
To see the values that a server will use based on its compiled-in defaults and any option files that it reads, use this command:
mysqld --verbose --help
To see the values that a server will use based on its compiled-in defaults, ignoring the settings in any option files, use this command:
mysqld --no-defaults --verbose --help
To see the current values used by a running server, use the
SHOW VARIABLES statement.
This section provides a description of each system variable. Variables with no version indicated are present in all MySQL 5.5 releases. For historical information concerning their implementation, please see http://dev.mysql.com/doc/refman/5.0/en/, and http://dev.mysql.com/doc/refman/4.1/en/.
The following table lists all available system variables:
Table 5.2. System Variable Summary
| Name | Cmd-Line | Option file | System Var | Var Scope | Dynamic |
|---|---|---|---|---|---|
| auto_increment_increment | Yes | Yes | Yes | Both | Yes |
| auto_increment_offset | Yes | Yes | Yes | Both | Yes |
| autocommit | Yes | Both | Yes | ||
| automatic_sp_privileges | Yes | Global | Yes | ||
| back_log | Yes | Yes | Yes | Global | No |
| basedir | Yes | Yes | Yes | Global | No |
| big-tables | Yes | Yes | Yes | ||
| - Variable: big_tables | Yes | Both | Yes | ||
| binlog_cache_size | Yes | Yes | Yes | Global | Yes |
| binlog_direct_non_transactional_updates | Yes | Yes | Yes | Both | Yes |
| binlog-format | Yes | Yes | Yes | ||
| - Variable: binlog_format | Yes | Both | Yes | ||
| bulk_insert_buffer_size | Yes | Yes | Yes | Both | Yes |
| character_set_client | Yes | Both | Yes | ||
| character_set_connection | Yes | Both | Yes | ||
| character_set_database[a] | Yes | Both | Yes | ||
| character-set-filesystem | Yes | Yes | Yes | ||
| - Variable: character_set_filesystem | Yes | Both | Yes | ||
| character_set_results | Yes | Both | Yes | ||
| character-set-server | Yes | Yes | Yes | ||
| - Variable: character_set_server | Yes | Both | Yes | ||
| character_set_system | Yes | Global | No | ||
| character-sets-dir | Yes | Yes | No | ||
| - Variable: character_sets_dir | Yes | Global | No | ||
| collation_connection | Yes | Both | Yes | ||
| collation_database[b] | Yes | Both | Yes | ||
| collation-server | Yes | Yes | Yes | ||
| - Variable: collation_server | Yes | Both | Yes | ||
| completion_type | Yes | Yes | Yes | Both | Yes |
| concurrent_insert | Yes | Yes | Yes | Global | Yes |
| connect_timeout | Yes | Yes | Yes | Global | Yes |
| datadir | Yes | Yes | Yes | Global | No |
| date_format | Yes | Both | No | ||
| datetime_format | Yes | Yes | Yes | Both | No |
| debug | Yes | Yes | Yes | Both | Yes |
| debug_sync | Yes | Session | Yes | ||
| default-storage-engine | Yes | Yes | Yes | ||
| - Variable: default_storage_engine | Yes | Both | Yes | ||
| default_week_format | Yes | Yes | Yes | Both | Yes |
| delay-key-write | Yes | Yes | Yes | ||
| - Variable: delay_key_write | Yes | Global | Yes | ||
| delayed_insert_limit | Yes | Yes | Yes | Global | Yes |
| delayed_insert_timeout | Yes | Yes | Yes | Global | Yes |
| delayed_queue_size | Yes | Yes | Yes | Global | Yes |
| div_precision_increment | Yes | Yes | Yes | Both | Yes |
| engine-condition-pushdown | Yes | Yes | Yes | ||
| - Variable: engine_condition_pushdown | Yes | Both | Yes | ||
| error_count | Yes | Session | No | ||
| event-scheduler | Yes | Yes | Yes | ||
| - Variable: event_scheduler | Yes | Global | Yes | ||
| expire_logs_days | Yes | Yes | Yes | Global | Yes |
| external_user | Yes | Session | No | ||
| flush | Yes | Yes | Yes | Global | Yes |
| flush_time | Yes | Yes | Yes | Global | Yes |
| foreign_key_checks | Yes | Both | Yes | ||
| ft_boolean_syntax | Yes | Yes | Yes | Global | Yes |
| ft_max_word_len | Yes | Yes | Yes | Global | No |
| ft_min_word_len | Yes | Yes | Yes | Global | No |
| ft_query_expansion_limit | Yes | Yes | Yes | Global | No |
| ft_stopword_file | Yes | Yes | Yes | Global | No |
| general-log | Yes | Yes | Yes | ||
| - Variable: general_log | Yes | Global | Yes | ||
| general_log_file | Yes | Yes | Yes | Global | Yes |
| group_concat_max_len | Yes | Yes | Yes | Both | Yes |
| have_compress | Yes | Global | No | ||
| have_crypt | Yes | Global | No | ||
| have_csv | Yes | Global | No | ||
| have_dynamic_loading | Yes | Global | No | ||
| have_geometry | Yes | Global | No | ||
| have_innodb | Yes | Global | No | ||
| have_ndbcluster | Yes | Global | No | ||
| have_openssl | Yes | Global | No | ||
| have_partitioning | Yes | Global | No | ||
| have_profiling | Yes | Global | No | ||
| have_query_cache | Yes | Global | No | ||
| have_rtree_keys | Yes | Global | No | ||
| have_ssl | Yes | Global | No | ||
| have_symlink | Yes | Global | No | ||
| hostname | Yes | Global | No | ||
| identity | Yes | Session | Yes | ||
| ignore-builtin-innodb | Yes | Yes | No | ||
| - Variable: ignore_builtin_innodb | Yes | Global | No | ||
| init_connect | Yes | Yes | Yes | Global | Yes |
| init-file | Yes | Yes | No | ||
| - Variable: init_file | Yes | Global | No | ||
| init_slave | Yes | Yes | Yes | Global | Yes |
| innodb_adaptive_flushing | Yes | Yes | Yes | Global | Yes |
| innodb_adaptive_hash_index | Yes | Yes | Yes | Global | Yes |
| innodb_additional_mem_pool_size | Yes | Yes | Yes | Global | No |
| innodb_autoextend_increment | Yes | Yes | Yes | Global | Yes |
| innodb_autoinc_lock_mode | Yes | Yes | Yes | Global | No |
| innodb_buffer_pool_size | Yes | Yes | Yes | Global | No |
| innodb_change_buffering | Yes | Yes | Yes | Global | Yes |
| innodb_checksums | Yes | Yes | Yes | Global | No |
| innodb_commit_concurrency | Yes | Yes | Yes | Global | Yes |
| innodb_concurrency_tickets | Yes | Yes | Yes | Global | Yes |
| innodb_data_file_path | Yes | Yes | Yes | Global | No |
| innodb_data_home_dir | Yes | Yes | Yes | Global | No |
| innodb_doublewrite | Yes | Yes | Yes | Global | No |
| innodb_fast_shutdown | Yes | Yes | Yes | Global | Yes |
| innodb_file_format | Yes | Yes | Yes | Global | Yes |
| innodb_file_format_check | Yes | Yes | Yes | Global | No |
| innodb_file_format_max | Yes | Yes | Yes | Global | Yes |
| innodb_file_per_table | Yes | Yes | Yes | Global | Yes |
| innodb_flush_log_at_trx_commit | Yes | Yes | Yes | Global | Yes |
| innodb_flush_method | Yes | Yes | Yes | Global | No |
| innodb_force_recovery | Yes | Yes | Yes | Global | No |
| innodb_io_capacity | Yes | Yes | Yes | Global | Yes |
| innodb_lock_wait_timeout | Yes | Yes | Yes | Both | Yes |
| innodb_locks_unsafe_for_binlog | Yes | Yes | Yes | Global | No |
| innodb_log_buffer_size | Yes | Yes | Yes | Global | No |
| innodb_log_file_size | Yes | Yes | Yes | Global | No |
| innodb_log_files_in_group | Yes | Yes | Yes | Global | No |
| innodb_log_group_home_dir | Yes | Yes | Yes | Global | No |
| innodb_max_dirty_pages_pct | Yes | Yes | Yes | Global | Yes |
| innodb_max_purge_lag | Yes | Yes | Yes | Global | Yes |
| innodb_mirrored_log_groups | Yes | Yes | Yes | Global | No |
| innodb_old_blocks_pct | Yes | Yes | Yes | Global | Yes |
| innodb_old_blocks_time | Yes | Yes | Yes | Global | Yes |
| innodb_open_files | Yes | Yes | Yes | Global | No |
| innodb_purge_batch_size | Yes | Yes | Yes | Global | No |
| innodb_purge_threads | Yes | Yes | Yes | Global | No |
| innodb_read_ahead_threshold | Yes | Yes | Yes | Global | Yes |
| innodb_read_io_threads | Yes | Yes | Yes | Global | No |
| innodb_replication_delay | Yes | Yes | Yes | Global | Yes |
| innodb_rollback_on_timeout | Yes | Yes | Yes | Global | No |
| innodb_spin_wait_delay | Yes | Yes | Yes | Global | Yes |
| innodb_stats_on_metadata | Yes | Yes | Yes | Global | Yes |
| innodb_stats_sample_pages | Yes | Yes | Yes | Global | Yes |
| innodb_strict_mode | Yes | Yes | Yes | Both | Yes |
| innodb_support_xa | Yes | Yes | Yes | Both | Yes |
| innodb_sync_spin_loops | Yes | Yes | Yes | Global | Yes |
| innodb_table_locks | Yes | Yes | Yes | Both | Yes |
| innodb_thread_concurrency | Yes | Yes | Yes | Global | Yes |
| innodb_thread_sleep_delay | Yes | Yes | Yes | Global | Yes |
| innodb_use_native_aio | Yes | Yes | Yes | Global | No |
| innodb_use_sys_malloc | Yes | Yes | Yes | Global | No |
| innodb_version | Yes | Global | No | ||
| innodb_write_io_threads | Yes | Yes | Yes | Global | No |
| insert_id | Yes | Session | Yes | ||
| interactive_timeout | Yes | Yes | Yes | Both | Yes |
| join_buffer_size | Yes | Yes | Yes | Both | Yes |
| keep_files_on_create | Yes | Yes | Yes | Both | Yes |
| key_buffer_size | Yes | Yes | Yes | Global | Yes |
| key_cache_age_threshold | Yes | Yes | Yes | Global | Yes |
| key_cache_block_size | Yes | Yes | Yes | Global | Yes |
| key_cache_division_limit | Yes | Yes | Yes | Global | Yes |
| language | Yes | Yes | Yes | Global | No |
| large_files_support | Yes | Global | No | ||
| large_page_size | Yes | Global | No | ||
| large-pages | Yes | Yes | No | ||
| - Variable: large_pages | Yes | Global | No | ||
| last_insert_id | Yes | Session | Yes | ||
| lc-messages | Yes | Yes | Yes | ||
| - Variable: lc_messages | Yes | Both | Yes | ||
| lc-messages-dir | Yes | Yes | No | ||
| - Variable: lc_messages_dir | Yes | Global | No | ||
| lc_time_names | Yes | Both | Yes | ||
| license | Yes | Global | No | ||
| local_infile | Yes | Global | Yes | ||
| lock_wait_timeout | Yes | Yes | Yes | Both | Yes |
| locked_in_memory | Yes | Global | No | ||
| log | Yes | Yes | Yes | Global | Yes |
| log_bin | Yes | Global | No | ||
| log-bin | Yes | Yes | Yes | Global | No |
| log-bin-trust-function-creators | Yes | Yes | Yes | ||
| - Variable: log_bin_trust_function_creators | Yes | Global | Yes | ||
| log-bin-trust-routine-creators | Yes | Yes | Yes | ||
| - Variable: log_bin_trust_routine_creators | Yes | Global | Yes | ||
| log-error | Yes | Yes | No | ||
| - Variable: log_error | Yes | Global | No | ||
| log-output | Yes | Yes | Yes | ||
| - Variable: log_output | Yes | Global | Yes | ||
| log-queries-not-using-indexes | Yes | Yes | Yes | ||
| - Variable: log_queries_not_using_indexes | Yes | Global | Yes | ||
| log-slave-updates | Yes | Yes | No | ||
| - Variable: log_slave_updates | Yes | Global | No | ||
| log-slow-queries | Yes | Yes | Yes | ||
| - Variable: log_slow_queries | Yes | Global | Yes | ||
| log-warnings | Yes | Yes | Yes | ||
| - Variable: log_warnings | Yes | Both | Yes | ||
| long_query_time | Yes | Yes | Yes | Both | Yes |
| low-priority-updates | Yes | Yes | Yes | ||
| - Variable: low_priority_updates | Yes | Both | Yes | ||
| lower_case_file_system | Yes | Yes | Yes | Global | No |
| lower_case_table_names | Yes | Yes | Yes | Global | No |
| master-bind | Yes | Yes | Yes | No | |
| max_allowed_packet | Yes | Yes | Yes | Global | Yes |
| max_binlog_cache_size | Yes | Yes | Yes | Global | Yes |
| max_binlog_size | Yes | Yes | Yes | Global | Yes |
| max_connect_errors | Yes | Yes | Yes | Global | Yes |
| max_connections | Yes | Yes | Yes | Global | Yes |
| max_delayed_threads | Yes | Yes | Yes | Both | Yes |
| max_error_count | Yes | Yes | Yes | Both | Yes |
| max_heap_table_size | Yes | Yes | Yes | Both | Yes |
| max_insert_delayed_threads | Yes | Both | Yes | ||
| max_join_size | Yes | Yes | Yes | Both | Yes |
| max_length_for_sort_data | Yes | Yes | Yes | Both | Yes |
| max_prepared_stmt_count | Yes | Yes | Yes | Global | Yes |
| max_relay_log_size | Yes | Yes | Yes | Global | Yes |
| max_seeks_for_key | Yes | Yes | Yes | Both | Yes |
| max_sort_length | Yes | Yes | Yes | Both | Yes |
| max_sp_recursion_depth | Yes | Yes | Yes | Both | Yes |
| max_tmp_tables | Yes | Yes | Yes | Both | Yes |
| max_user_connections | Yes | Yes | Yes | Both | Yes |
| max_write_lock_count | Yes | Yes | Yes | Global | Yes |
| memlock | Yes | Yes | Yes | Global | No |
| min-examined-row-limit | Yes | Yes | Yes | Both | Yes |
| myisam_data_pointer_size | Yes | Yes | Yes | Global | Yes |
| myisam_max_sort_file_size | Yes | Yes | Yes | Global | Yes |
| myisam_mmap_size | Yes | Yes | Yes | Global | No |
| myisam_recover_options | Yes | Global | No | ||
| myisam_repair_threads | Yes | Yes | Yes | Both | Yes |
| myisam_sort_buffer_size | Yes | Yes | Yes | Both | Yes |
| myisam_stats_method | Yes | Yes | Yes | Both | Yes |
| myisam_use_mmap | Yes | Yes | Yes | Global | Yes |
| named_pipe | Yes | Global | No | ||
| ndb_autoincrement_prefetch_sz | Yes | Yes | Yes | Both | Yes |
| net_buffer_length | Yes | Yes | Yes | Both | Yes |
| net_read_timeout | Yes | Yes | Yes | Both | Yes |
| net_retry_count | Yes | Yes | Yes | Both | Yes |
| net_write_timeout | Yes | Yes | Yes | Both | Yes |
| new | Yes | Yes | Yes | Both | Yes |
| old | Yes | Yes | Yes | Global | No |
| old-alter-table | Yes | Yes | Yes | ||
| - Variable: old_alter_table | Yes | Both | Yes | ||
| old-passwords | Yes | Yes | Yes | ||
| - Variable: old_passwords | Yes | Both | Yes | ||
| open-files-limit | Yes | Yes | No | ||
| - Variable: open_files_limit | Yes | Global | No | ||
| optimizer_prune_level | Yes | Yes | Yes | Both | Yes |
| optimizer_search_depth | Yes | Yes | Yes | Both | Yes |
| optimizer_switch | Yes | Yes | Yes | Both | Yes |
| partition | Yes | Yes | No | ||
| - Variable: have_partitioning | Yes | Global | No | ||
| performance_schema | Yes | Yes | Yes | Global | No |
| performance_schema_events_waits_history_long_size | Yes | Yes | Yes | Global | No |
| performance_schema_events_waits_history_size | Yes | Yes | Yes | Global | No |
| performance_schema_max_cond_classes | Yes | Yes | Yes | Global | No |
| performance_schema_max_cond_instances | Yes | Yes | Yes | Global | No |
| performance_schema_max_file_classes | Yes | Yes | Yes | Global | No |
| performance_schema_max_file_handles | Yes | Yes | Yes | Global | No |
| performance_schema_max_file_instances | Yes | Yes | Yes | Global | No |
| performance_schema_max_mutex_classes | Yes | Yes | Yes | Global | No |
| performance_schema_max_mutex_instances | Yes | Yes | Yes | Global | No |
| performance_schema_max_rwlock_classes | Yes | Yes | Yes | Global | No |
| performance_schema_max_rwlock_instances | Yes | Yes | Yes | Global | No |
| performance_schema_max_table_handles | Yes | Yes | Yes | Global | No |
| performance_schema_max_table_instances | Yes | Yes | Yes | Global | No |
| performance_schema_max_thread_classes | Yes | Yes | Yes | Global | No |
| performance_schema_max_thread_instances | Yes | Yes | Yes | Global | No |
| pid-file | Yes | Yes | No | ||
| - Variable: pid_file | Yes | Global | No | ||
| plugin_dir | Yes | Yes | Yes | Global | No |
| port | Yes | Yes | Yes | Global | No |
| preload_buffer_size | Yes | Yes | Yes | Both | Yes |
| profiling | Yes | Both | Yes | ||
| profiling_history_size | Yes | Both | Yes | ||
| protocol_version | Yes | Global | No | ||
| proxy_user | Yes | Session | No | ||
| pseudo_thread_id | Yes | Session | Yes | ||
| query_alloc_block_size | Yes | Yes | Yes | Both | Yes |
| query_cache_limit | Yes | Yes | Yes | Global | Yes |
| query_cache_min_res_unit | Yes | Yes | Yes | Global | Yes |
| query_cache_size | Yes | Yes | Yes | Global | Yes |
| query_cache_type | Yes | Yes | Yes | Both | Yes |
| query_cache_wlock_invalidate | Yes | Yes | Yes | Both | Yes |
| query_prealloc_size | Yes | Yes | Yes | Both | Yes |
| rand_seed1 | Yes | Session | Yes | ||
| rand_seed2 | Yes | Session | Yes | ||
| range_alloc_block_size | Yes | Yes | Yes | Both | Yes |
| read_buffer_size | Yes | Yes | Yes | Both | Yes |
| read_only | Yes | Yes | Yes | Global | Yes |
| read_rnd_buffer_size | Yes | Yes | Yes | Both | Yes |
| relay-log-index | Yes | Yes | No | ||
| - Variable: relay_log_index | Yes | Both | No | ||
| relay_log_purge | Yes | Yes | Yes | Global | Yes |
| relay_log_recovery | Yes | Yes | Yes | Global | Yes |
| relay_log_space_limit | Yes | Yes | Yes | Global | No |
| report-host | Yes | Yes | No | ||
| - Variable: report_host | Yes | Global | No | ||
| report-password | Yes | Yes | No | ||
| - Variable: report_password | Yes | Global | No | ||
| report-port | Yes | Yes | No | ||
| - Variable: report_port | Yes | Global | No | ||
| report-user | Yes | Yes | No | ||
| - Variable: report_user | Yes | Global | No | ||
| rpl_recovery_rank | Yes | Global | Yes | ||
| rpl_semi_sync_master_enabled | Yes | Global | Yes | ||
| rpl_semi_sync_master_timeout | Yes | Global | Yes | ||
| rpl_semi_sync_master_trace_level | Yes | Global | Yes | ||
| rpl_semi_sync_master_wait_no_slave | Yes | Global | Yes | ||
| rpl_semi_sync_slave_enabled | Yes | Global | Yes | ||
| rpl_semi_sync_slave_trace_level | Yes | Global | Yes | ||
| safe-show-database | Yes | Yes | Yes | Global | Yes |
| secure-auth | Yes | Yes | Yes | ||
| - Variable: secure_auth | Yes | Global | Yes | ||
| secure-file-priv | Yes | Yes | No | ||
| - Variable: secure_file_priv | Yes | Global | No | ||
| server-id | Yes | Yes | Yes | ||
| - Variable: server_id | Yes | Global | Yes | ||
| shared_memory | Yes | Global | No | ||
| shared_memory_base_name | Yes | Global | No | ||
| skip-external-locking | Yes | Yes | No | ||
| - Variable: skip_external_locking | Yes | Global | No | ||
| skip-name-resolve | Yes | Yes | No | ||
| - Variable: skip_name_resolve | Yes | Global | No | ||
| skip-networking | Yes | Yes | No | ||
| - Variable: skip_networking | Yes | Global | No | ||
| skip-show-database | Yes | Yes | No | ||
| - Variable: skip_show_database | Yes | Global | No | ||
| slave_compressed_protocol | Yes | Yes | Yes | Global | Yes |
| slave_exec_mode | Yes | Global | Yes | ||
| slave-load-tmpdir | Yes | Yes | No | ||
| - Variable: slave_load_tmpdir | Yes | Global | No | ||
| slave-net-timeout | Yes | Yes | Yes | ||
| - Variable: slave_net_timeout | Yes | Global | Yes | ||
| slave-skip-errors | Yes | Yes | No | ||
| - Variable: slave_skip_errors | Yes | Global | No | ||
| slave_transaction_retries | Yes | Yes | Yes | Global | Yes |
| slave_type_conversions | Yes | Yes | Yes | Global | No |
| slow_launch_time | Yes | Yes | Yes | Global | Yes |
| slow-query-log | Yes | Yes | Yes | ||
| - Variable: slow_query_log | Yes | Global | Yes | ||
| slow_query_log_file | Yes | Yes | Yes | Global | Yes |
| socket | Yes | Yes | Yes | Global | No |
| sort_buffer_size | Yes | Yes | Yes | Both | Yes |
| sql_auto_is_null | Yes | Both | Yes | ||
| sql_big_selects | Yes | Both | Yes | ||
| sql_big_tables | Yes | Session | Yes | ||
| sql_buffer_result | Yes | Session | Yes | ||
| sql_log_bin | Yes | Both | Yes | ||
| sql_log_off | Yes | Both | Yes | ||
| sql_log_update | Yes | Session | Yes | ||
| sql_low_priority_updates | Yes | Both | Yes | ||
| sql_max_join_size | Yes | Both | Yes | ||
| sql-mode | Yes | Yes | Yes | ||
| - Variable: sql_mode | Yes | Both | Yes | ||
| sql_notes | Yes | Both | Yes | ||
| sql_quote_show_create | Yes | Both | Yes | ||
| sql_safe_updates | Yes | Both | Yes | ||
| sql_select_limit | Yes | Both | Yes | ||
| sql_slave_skip_counter | Yes | Global | Yes | ||
| sql_warnings | Yes | Both | Yes | ||
| ssl-ca | Yes | Yes | No | ||
| - Variable: ssl_ca | Yes | Global | No | ||
| ssl-capath | Yes | Yes | No | ||
| - Variable: ssl_capath | Yes | Global | No | ||
| ssl-cert | Yes | Yes | No | ||
| - Variable: ssl_cert | Yes | Global | No | ||
| ssl-cipher | Yes | Yes | No | ||
| - Variable: ssl_cipher | Yes | Global | No | ||
| ssl-key | Yes | Yes | No | ||
| - Variable: ssl_key | Yes | Global | No | ||
| storage_engine | Yes | Both | Yes | ||
| sync_binlog | Yes | Yes | Yes | Global | Yes |
| sync_frm | Yes | Yes | Yes | Global | Yes |
| sync_master_info | Yes | Yes | Yes | Global | Yes |
| sync_relay_log | Yes | Yes | Yes | Global | Yes |
| sync_relay_log_info | Yes | Yes | Yes | Global | Yes |
| system_time_zone | Yes | Global | No | ||
| table_definition_cache | Yes | Yes | Yes | Global | Yes |
| table_lock_wait_timeout | Yes | Yes | Yes | Global | Yes |
| table_open_cache | Yes | Yes | Yes | Global | Yes |
| table_type | Yes | Both | Yes | ||
| thread_cache_size | Yes | Yes | Yes | Global | Yes |
| thread_concurrency | Yes | Yes | Yes | Global | No |
| thread_handling | Yes | Yes | Yes | Global | No |
| thread_stack | Yes | Yes | Yes | Global | No |
| time_format | Yes | Yes | Yes | Both | No |
| time_zone | Yes | Yes | Yes | Both | Yes |
| timed_mutexes | Yes | Yes | Yes | Global | Yes |
| timestamp | Yes | Session | Yes | ||
| tmp_table_size | Yes | Yes | Yes | Both | Yes |
| tmpdir | Yes | Yes | Yes | Global | No |
| transaction_alloc_block_size | Yes | Yes | Yes | Both | Yes |
| transaction_prealloc_size | Yes | Yes | Yes | Both | Yes |
| tx_isolation | Yes | Both | Yes | ||
| unique_checks | Yes | Both | Yes | ||
| updatable_views_with_limit | Yes | Yes | Yes | Both | Yes |
| version | Yes | Global | No | ||
| version_comment | Yes | Global | No | ||
| version_compile_machine | Yes | Global | No | ||
| version_compile_os | Yes | Global | No | ||
| wait_timeout | Yes | Yes | Yes | Both | Yes |
| warning_count | Yes | Session | No | ||
[a] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. [b] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. | |||||
For additional system variable information, see these sections:
Section 5.1.5, “Using System Variables”, discusses the syntax for setting and displaying system variable values.
Section 5.1.5.2, “Dynamic System Variables”, lists the variables that can be set at runtime.
Information on tuning system variables can be found in Section 7.11.2, “Tuning Server Parameters”.
Section 13.6.4, “InnoDB Startup Options and System Variables”, lists
InnoDB system variables.
MySQL Cluster System Variables, lists system variables which are specific to MySQL Cluster.
For information on server system variables specific to replication, see Section 17.1.3, “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. However, to set
such a variable on the command line or in an option file, you
must set it to 1 or 0;
setting it to ON or OFF
will not work. For example, on the command line,
--delay_key_write=1 works but
--delay_key_write=ON does not.
Some system variables control the size of buffers or caches. For a given buffer, the server might need to allocate internal data structures. These structures typically are allocated from the total memory allocated to the buffer, and the amount of space required might be platform dependent. This means that when you assign a value to a system variable that controls a buffer size, the amount of space actually available might differ from the value assigned. In some cases, the amount might be less than the value assigned. It is also possible that the server will adjust a value upward. For example, if you assign a value of 0 to a variable for which the minimal value is 1024, the server will set the value to 1024.
Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.
Some system variables take file name values. Unless otherwise
specified, the default file location is the data directory if the
value is a relative path name. To specify the location explicitly,
use an absolute path name. Suppose that the data directory is
/var/mysql/data. If a file-valued variable is
given as a relative path name, it will be located under
/var/mysql/data. If the value is an absolute
path name, its location is as given by the path name.
| Variable Name | autocommit | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
The autocommit mode. If set to 1, all changes to a table take
effect immediately. If set to 0, you must use
COMMIT to accept a transaction
or ROLLBACK
to cancel it. If autocommit
is 0 and you change it to 1, MySQL performs an automatic
COMMIT of any open transaction.
Another way to begin a transaction is to use a
START
TRANSACTION or
BEGIN
statement. See Section 12.3.1, “START TRANSACTION,
COMMIT, and
ROLLBACK Syntax”.
By default, client connections begin with
autocommit set to 1. To cause
clients to begin with a default of 0, set the server's
init_connect system variable.
See the description of that variable for instructions that
show how to do this.
| Variable Name | automatic_sp_privileges | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | TRUE | ||
When this variable has a value of 1 (the default), the server
automatically grants the
EXECUTE and
ALTER ROUTINE privileges to the
creator of a stored routine, if the user cannot already
execute and alter or drop the routine. (The
ALTER ROUTINE privilege is
required to drop the routine.) The server also automatically
drops those privileges from the creator when the routine is
dropped. If
automatic_sp_privileges is 0,
the server does not automatically add or drop these
privileges.
The creator of a routine is the account used to execute the
CREATE statement for it. This might not be
the same as the account named as the
DEFINER in the routine definition.
See also Section 19.2.2, “Stored Routines and MySQL Privileges”.
| Command-Line Format | --back_log=# | ||
| Option-File Format | back_log | ||
| Option Sets Variable | Yes, back_log | ||
| Variable Name | back_log | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 50 | ||
| Range | 1-65535 | ||
The number of outstanding connection requests MySQL can have.
This comes into play when the main MySQL thread gets very many
connection requests in a very short time. It then takes some
time (although very little) for the main thread to check the
connection and start a new thread. The
back_log value indicates how
many requests can be stacked during this short time before
MySQL momentarily stops answering new requests. You need to
increase this only if you expect a large number of connections
in a short period of time.
In other words, this value is the size of the listen queue for
incoming TCP/IP connections. Your operating system has its own
limit on the size of this queue. The manual page for the Unix
listen() system call should have more
details. Check your OS documentation for the maximum value for
this variable. back_log
cannot be set higher than your operating system limit.
| Command-Line Format | --basedir=path | ||
-b | |||
| Option-File Format | basedir | ||
| Option Sets Variable | Yes, basedir | ||
| Variable Name | basedir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The MySQL installation base directory. This variable can be
set with the --basedir option.
Relative path names for other variables usually are resolved
relative to the base directory.
If set to 1, all temporary tables are stored on disk rather
than in memory. This is a little slower, but the error
The table does not occur for
tbl_name is
fullSELECT operations that require
a large temporary table. The default value for a new
connection is 0 (use in-memory temporary tables). Normally,
you should never need to set this variable, because in-memory
tables are automatically converted to disk-based tables as
required.
This variable was formerly named
sql_big_tables.
| Command-Line Format | --bulk_insert_buffer_size=# | ||
| Option-File Format | bulk_insert_buffer_size | ||
| Option Sets Variable | Yes, bulk_insert_buffer_size | ||
| Variable Name | bulk_insert_buffer_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 8388608 | ||
| Range | 0-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 8388608 | ||
| Range | 0-18446744073709547520 | ||
MyISAM uses a special tree-like cache to
make bulk inserts faster for
INSERT ...
SELECT, INSERT ... VALUES (...), (...),
..., and
LOAD DATA
INFILE when adding data to nonempty tables. This
variable limits the size of the cache tree in bytes per
thread. Setting it to 0 disables this optimization. The
default value is 8MB.
| Variable Name | character_set_client | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The character set for statements that arrive from the client.
The session value of this variable is set using the character
set requested by the client when the client connects to the
server. (Many clients support a
--default-character-set option to enable this
character set to be specified explicitly. See also
Section 9.1.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 is from a version of MySQL older than MySQL 4.1, and thus does not request a character set.
The client requests a character set not known to the
server. For example, a Japanese-enabled client requests
sjis when connecting to a server not
configured with sjis support.
mysqld was started with the
--skip-character-set-client-handshake
option, which causes it to ignore client character set
configuration. This reproduces MySQL 4.0 behavior and is
useful should you wish to upgrade the server without
upgrading all the clients.
ucs2, utf16, and
utf32 cannot be used as a client character
set, which means that they also do not work for SET
NAMES or SET CHARACTER SET.
| Variable Name | character_set_connection | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The character set used for literals that do not have a character set introducer and for number-to-string conversion.
| Variable Name | character_set_database | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Footnote | This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. | ||
| Permitted Values | |||
| Type | string | ||
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.
| Command-Line Format | --character-set-filesystem=name | ||
| Option-File Format | character-set-filesystem | ||
| Option Sets Variable | Yes, character_set_filesystem | ||
| Variable Name | character_set_filesystem | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The file system character set. This variable is used to
interpret string literals that refer to file names, such as in
the LOAD DATA
INFILE and
SELECT ... INTO
OUTFILE statements and the
LOAD_FILE() function. Such file
names are converted from
character_set_client to
character_set_filesystem
before the file opening attempt occurs. The default value is
binary, which means that no conversion
occurs. For systems on which multi-byte file names are
permitted, a different value may be more appropriate. For
example, if the system represents file names using UTF-8, set
character_set_filesystem to
'utf8'.
| Variable Name | character_set_results | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The character set used for returning query results such as result sets or error messages to the client.
| Command-Line Format | --character-set-server | ||
| Option-File Format | character-set-server | ||
| Option Sets Variable | Yes, character_set_server | ||
| Variable Name | character_set_server | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The server's default character set.
| Variable Name | character_set_system | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The character set used by the server for storing identifiers.
The value is always utf8.
| Command-Line Format | --character-sets-dir=path | ||
| Option-File Format | character-sets-dir=path | ||
| Option Sets Variable | Yes, character_sets_dir | ||
| Variable Name | character-sets-dir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The directory where character sets are installed.
| Variable Name | collation_connection | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The collation of the connection character set.
| Variable Name | collation_database | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Footnote | This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. | ||
| Permitted Values | |||
| Type | string | ||
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.
| Command-Line Format | --collation-server | ||
| Option-File Format | collation-server | ||
| Option Sets Variable | Yes, collation_server | ||
| Variable Name | collation_server | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The server's default collation.
| Command-Line Format | --completion_type=# | ||
| Option-File Format | completion_type | ||
| Option Sets Variable | Yes, completion_type | ||
| Variable Name | competion_type | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values (<= 5.5.2) | |||
| Type | numeric | ||
| Default | 0 | ||
| Valid Values | 0, 1, 2 | ||
| Permitted Values (>= 5.5.3) | |||
| Type | enumeration | ||
| Default | NO_CHAIN | ||
| Valid Values | NO_CHAIN, CHAIN, RELEASE, 0, 1, 2 | ||
The transaction completion type. This variable can take the values shown in the following table. As of MySQL 5.5.3, the variable can be assigned using either the name values or corresponding integer values. Before 5.5.3, only the integer values can be used.
| Value | Description |
|---|---|
NO_CHAIN (or 0) | COMMIT and
ROLLBACK
are unaffected. This is the default value. |
CHAIN (or 1) | COMMIT and
ROLLBACK
are equivalent to COMMIT AND CHAIN
and ROLLBACK AND CHAIN,
respectively. (A new transaction starts immediately
with the same isolation level as the just-terminated
transaction.) |
RELEASE (or 2) | COMMIT and
ROLLBACK
are equivalent to COMMIT RELEASE
and ROLLBACK RELEASE, respectively.
(The server disconnects after terminating the
transaction.) |
completion_type affects
transactions that begin with
START
TRANSACTION or
BEGIN and
end with COMMIT or
ROLLBACK. It
does not apply to implicit commits resulting from execution of
the statements listed in Section 12.3.3, “Statements That Cause an Implicit Commit”. It
also does not apply for
XA
COMMIT,
XA
ROLLBACK, or when
autocommit=1.
| Command-Line Format | --concurrent_insert[=#] | ||
| Option-File Format | concurrent_insert | ||
| Option Sets Variable | Yes, concurrent_insert | ||
| Variable Name | concurrent_insert | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values (<= 5.5.2) | |||
| Type | numeric | ||
| Default | 1 | ||
| Valid Values | 0, 1, 2 | ||
| Permitted Values (>= 5.5.3) | |||
| Type | enumeration | ||
| Default | AUTO | ||
| Valid Values | NEVER, AUTO, ALWAYS, 0, 1, 2 | ||
If AUTO (the default), MySQL permits
INSERT and
SELECT statements to run
concurrently for MyISAM tables that have no
free blocks in the middle of the data file. If you start
mysqld with
--skip-new,
this variable is set to NEVER.
This variable can take the values shown in the following table. As of MySQL 5.5.3, the variable can be assigned using either the name values or corresponding integer values. Before 5.5.3, only the integer values can be used.
| Value | Description |
|---|---|
NEVER (or 0) | Disables concurrent inserts |
AUTO (or 1) | (Default) Enables concurrent insert for MyISAM tables
that do not have holes |
ALWAYS (or 2) | Enables concurrent inserts for all MyISAM tables,
even those that have holes. For a table with a hole,
new rows are inserted at the end of the table if it is
in use by another thread. Otherwise, MySQL acquires a
normal write lock and inserts the row into the hole. |
See also Section 7.10.3, “Concurrent Inserts”.
| Command-Line Format | --connect_timeout=# | ||
| Option-File Format | connect_timeout | ||
| Option Sets Variable | Yes, connect_timeout | ||
| Variable Name | connect_timeout | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 10 | ||
The number of seconds that the mysqld
server waits for a connect packet before responding with
Bad handshake. The default value is 10
seconds.
Increasing the
connect_timeout value might
help if clients frequently encounter errors of the form
Lost connection to MySQL server at
'.
XXX', system error:
errno
| Command-Line Format | --datadir=path | ||
-h | |||
| Option-File Format | datadir | ||
| Option Sets Variable | Yes, datadir | ||
| Variable Name | datadir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The MySQL data directory. This variable can be set with the
--datadir option.
This variable is unused.
This variable is unused.
| Command-Line Format | --debug[=debug_options] | ||
| Option-File Format | debug | ||
| Variable Name | debug | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
| Default | 'd:t:o,/tmp/mysqld.trace' | ||
This variable indicates the current debugging settings. It is
available only for servers built with debugging support. The
initial value comes from the value of instances of the
--debug option given at server
startup. The global and session values may be set at runtime;
the SUPER privilege is
required, even for the session value.
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 | +---------+
| Variable Name | debug_sync | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
This variable is the user interface to the Debug Sync
facility. Use of Debug Sync requires that MySQL be configured
with the -DENABLE_DEBUG_SYNC=1
option (see Section 2.11.4, “MySQL Source-Configuration Options”).
If Debug Sync is not compiled in, this system variable is not
available.
The global variable value is read only and indicates whether
the facility is enabled. By default, Debug Sync is disabled
and the value of debug_sync
is OFF. If the server is started with
--debug-sync-timeout=,
where NN is a timeout value greater
than 0, Debug Sync is enabled and the value of
debug_sync is ON -
current signal followed by the signal name. Also,
N becomes the default timeout for
individual synchronization points.
The session value can be read by any user and will have the
same value as the global variable. The session value can be
set by users that have the
SUPER privilege to control
synchronization points.
For a description of the Debug Sync facility and how to use synchronization points, see MySQL Internals: Test Synchronization.
| Command-Line Format | --default-storage-engine=name | ||
| Option-File Format | default-storage-engine | ||
| Option Sets Variable | Yes, default_storage_engine | ||
| Variable Name | default-storage-engine | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values (<= 5.5.4) | |||
| Type | enumeration | ||
| Default | MyISAM | ||
| Permitted Values (>= 5.5.5) | |||
| Type | enumeration | ||
| Default | InnoDB | ||
The default storage engine. To set the storage engine at
server startup, use the
--default-storage-engine
option. See Section 5.1.2, “Server Command Options”.
This variable was added in MySQL 5.5.3 to be used in
preference to storage_engine,
which is now deprecated.
| Command-Line Format | --default_week_format=# | ||
| Option-File Format | default_week_format | ||
| Option Sets Variable | Yes, default_week_format | ||
| Variable Name | default_week_format | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-7 | ||
The default mode value to use for the
WEEK() function. See
Section 11.7, “Date and Time Functions”.
| Command-Line Format | --delay-key-write[=name] | ||
| Option-File Format | delay-key-write | ||
| Option Sets Variable | Yes, delay_key_write | ||
| Variable Name | delay-key-write | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | ON | ||
| Valid Values | ON, OFF, ALL | ||
This option applies only to MyISAM tables.
It can have one of the following values to affect handling of
the DELAY_KEY_WRITE table option that can
be used in CREATE TABLE
statements.
| Option | Description |
|---|---|
OFF | DELAY_KEY_WRITE is ignored. |
ON | MySQL honors any DELAY_KEY_WRITE option specified in
CREATE TABLE
statements. This is the default value. |
ALL | All new opened tables are treated as if they were created with the
DELAY_KEY_WRITE option enabled. |
If DELAY_KEY_WRITE is enabled for a table,
the key buffer is not flushed for the table on every index
update, but only when the table is closed. This speeds up
writes on keys a lot, but if you use this feature, you should
add automatic checking of all MyISAM tables
by starting the server with the
--myisam-recover-options option
(for example,
--myisam-recover-options=BACKUP,FORCE).
See Section 5.1.2, “Server Command Options”, and
Section 13.5.1, “MyISAM Startup Options”.
If 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=# | ||
| Option-File Format | delayed_insert_limit | ||
| Option Sets Variable | Yes, delayed_insert_limit | ||
| Variable Name | delayed_insert_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 100 | ||
| Range | 1-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 100 | ||
| Range | 1-18446744073709547520 | ||
After inserting
delayed_insert_limit delayed
rows, the INSERT DELAYED
handler thread checks whether there are any
SELECT statements pending. If
so, it permits them to execute before continuing to insert
delayed rows.
| Command-Line Format | --delayed_insert_timeout=# | ||
| Option-File Format | delayed_insert_timeout | ||
| Option Sets Variable | Yes, delayed_insert_timeout | ||
| Variable Name | delayed_insert_timeout | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 300 | ||
How many seconds an INSERT
DELAYED handler thread should wait for
INSERT statements before
terminating.
| Command-Line Format | --delayed_queue_size=# | ||
| Option-File Format | delayed_queue_size | ||
| Option Sets Variable | Yes, delayed_queue_size | ||
| Variable Name | delayed_queue_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 1000 | ||
| Range | 1-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 1000 | ||
| Range | 1-18446744073709547520 | ||
This is a per-table limit on the number of rows to queue when
handling INSERT DELAYED
statements. If the queue becomes full, any client that issues
an INSERT DELAYED statement
waits until there is room in the queue again.
| Command-Line Format | --div_precision_increment=# | ||
| Option-File Format | div_precision_increment | ||
| Option Sets Variable | Yes, div_precision_increment | ||
| Variable Name | div_precision_increment | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 4 | ||
| Range | 0-30 | ||
This variable indicates the number of digits by which to
increase the scale of the result of division operations
performed with the
/ operator.
The default value is 4. The minimum and maximum values are 0
and 30, respectively. The following example illustrates the
effect of increasing the default value.
mysql>SELECT 1/7;+--------+ | 1/7 | +--------+ | 0.1429 | +--------+ mysql>SET div_precision_increment = 12;mysql>SELECT 1/7;+----------------+ | 1/7 | +----------------+ | 0.142857142857 | +----------------+
| Version Deprecated | 5.5.3 | ||
| Command-Line Format | --engine-condition-pushdown | ||
| Option-File Format | engine-condition-pushdown | ||
| Option Sets Variable | Yes, engine_condition_pushdown | ||
| Variable Name | engine_condition_pushdown | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Deprecated | 5.5.3, by optimizer_switch | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | ON | ||
The engine condition pushdown optimization enables processing for certain comparisons to be “pushed down” to the storage engine level for more efficient execution. For more information, see Section 7.13.3, “Engine Condition Pushdown Optimization”.
Engine condition pushdown is used only by the
NDBCLUSTER storage engine.
Enabling this optimization on a MySQL Server acting as a MySQL
Cluster SQL node causes WHERE conditions on
unindexed columns to be evaluated on the cluster's data nodes
and only the rows that match to be sent back to the SQL node
that issued the query. This greatly reduces the amount of
cluster data that must be sent over the network, increasing
the efficiency with which results are returned.
The engine_condition_pushdown
variable controls whether engine condition pushdown is
enabled. By default, this variable is ON
(1). Setting it to OFF (0) disables
pushdown.
This variable is deprecated as of MySQL 5.5.3. Use the
engine_condition_pushdown flag of the
optimizer_switch variable
instead. See Section 7.8.4.2, “Controlling Switchable Optimizations”.
The number of errors that resulted from the last statement
that generated messages. This variable is read only. See
Section 12.4.5.18, “SHOW ERRORS Syntax”.
| Command-Line Format | --event-scheduler[=value] | ||
| Option-File Format | event-scheduler | ||
| Option Sets Variable | Yes, event_scheduler | ||
| Variable Name | event_scheduler | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | OFF | ||
| Valid Values | ON, OFF, DISABLED | ||
This variable indicates the status of the Event Scheduler;
possible values are ON,
OFF, and DISABLED, with
the default being OFF. This variable and
its effects on the Event Scheduler's operation are discussed
in greater detail in the
Overview section
of the Events chapter.
| Command-Line Format | --expire_logs_days=# | ||
| Option-File Format | expire_logs_days | ||
| Option Sets Variable | Yes, expire_logs_days | ||
| Variable Name | expire_logs_days | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-99 | ||
The number of days for automatic binary log file removal. The default is 0, which means “no automatic removal.” Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in Section 5.2, “MySQL Server Logs”.
To remove binary log files manually, use the
PURGE BINARY LOGS statement.
See Section 12.5.1.1, “PURGE BINARY LOGS Syntax”.
| Version Introduced | 5.5.7 | ||
| Variable Name | external_user | ||
| Variable Scope | Session | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The extern user name used during the authentication process.
With native (built-in) MySQL authentication, this variable is
NULL. See Section 5.5.7, “Proxy Users”.
This variable was added in MySQL 5.5.7.
| Command-Line Format | --flush | ||
| Option-File Format | flush | ||
| Variable Name | flush | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
If ON, the server flushes (synchronizes)
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 C.5.4.2, “What to Do If MySQL Keeps Crashing”. This
variable is set to ON if you start
mysqld with the
--flush option.
| Command-Line Format | --flush_time=# | ||
| Option-File Format | flush_time | ||
| Option Sets Variable | Yes, flush_time | ||
| Variable Name | flush_time | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Permitted Values | |||
| Type (windows) | numeric | ||
| Default | 1800 | ||
| Min Value | 0 | ||
If this is set to a nonzero value, all tables are closed every
flush_time seconds to free up
resources and synchronize unflushed data to disk. This option
is best used only on Windows 9x or Me, or on systems with
minimal resources.
If set to 1 (the default), foreign key constraints for
InnoDB tables are checked. If set to 0,
they are ignored. Disabling foreign key checking can be useful
for reloading InnoDB tables in an order
different from that required by their parent/child
relationships. See
Section 13.6.5.4, “FOREIGN KEY Constraints”.
Setting foreign_key_checks to
0 also affects data definition statements:
DROP
SCHEMA drops a schema even if it contains tables
that have foreign keys that are referred to by tables outside
the schema, and DROP TABLE
drops tables that have foreign keys that are referred to by
other tables.
Setting foreign_key_checks
to 1 does not trigger a scan of the existing table data.
Therefore, rows added to the table while
foreign_key_checks = 0 will
not be verified for consistency.
| Command-Line Format | --ft_boolean_syntax=name | ||
| Option-File Format | ft_boolean_syntax | ||
| Variable Name | ft_boolean_syntax | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
| Default | +-><()~*:""& | ||
The list of operators supported by boolean full-text searches
performed using IN BOOLEAN MODE. See
Section 11.9.2, “Boolean Full-Text Searches”.
The default variable value is
'+ -><()~*:""&|'. The rules
for changing the value are as follows:
Operator function is determined by position within the string.
The replacement value must be 14 characters.
Each character must be an ASCII nonalphanumeric character.
Either the first or second character must be a space.
No duplicates are permitted except the phrase quoting operators in positions 11 and 12. These two characters are not required to be the same, but they are the only two that may be.
Positions 10, 13, and 14 (which by default are set to
“:”,
“&”, and
“|”) are reserved for
future extensions.
| Command-Line Format | --ft_max_word_len=# | ||
| Option-File Format | ft_max_word_len | ||
| Option Sets Variable | Yes, ft_max_word_len | ||
| Variable Name | ft_max_word_len | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Min Value | 10 | ||
The maximum length of the word to be included in a
FULLTEXT index.
FULLTEXT indexes must be rebuilt after
changing this variable. Use REPAIR TABLE
.
tbl_name QUICK
| Command-Line Format | --ft_min_word_len=# | ||
| Option-File Format | ft_min_word_len | ||
| Option Sets Variable | Yes, ft_min_word_len | ||
| Variable Name | ft_min_word_len | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 4 | ||
| Min Value | 1 | ||
The minimum length of the word to be included in a
FULLTEXT index.
FULLTEXT indexes must be rebuilt after
changing this variable. Use REPAIR TABLE
.
tbl_name QUICK
| Command-Line Format | --ft_query_expansion_limit=# | ||
| Option-File Format | ft_query_expansion_limit | ||
| Option Sets Variable | Yes, ft_query_expansion_limit | ||
| Variable Name | ft_query_expansion_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 20 | ||
| Range | 0-1000 | ||
The number of top matches to use for full-text searches
performed using WITH QUERY EXPANSION.
| Command-Line Format | --ft_stopword_file=file_name | ||
| Option-File Format | ft_stopword_file=file_name | ||
| Option Sets Variable | Yes, ft_stopword_file | ||
| Variable Name | ft_stopword_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The file from which to read the list of stopwords for
full-text searches. The server looks for the file in the data
directory unless an absolute path name is given to specify a
different directory. All the words from the file are used;
comments are not honored. By default, a
built-in list of stopwords is used (as defined in the
storage/myisam/ft_static.c file). Setting
this variable to the empty string ('')
disables stopword filtering. See also
Section 11.9.4, “Full-Text Stopwords”.
FULLTEXT indexes must be rebuilt after
changing this variable or the contents of the stopword file.
Use REPAIR TABLE
.
tbl_name QUICK
| Command-Line Format | --general-log | ||
| Option-File Format | general-log | ||
| Option Sets Variable | Yes, general_log | ||
| Variable Name | general_log | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Whether the general query log is enabled. The value can be 0
(or OFF) to disable the log or 1 (or
ON) to enable the log. The default value
depends on whether the
--general_log option is given.
The destination for log output is controlled by the
log_output system variable;
if that value is NONE, no log entries are
written even if the log is enabled.
| Command-Line Format | --general-log-file=file_name | ||
| Option-File Format | general_log_file | ||
| Option Sets Variable | Yes, general_log_file | ||
| Variable Name | general_log_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | file name | ||
| Default | host_name.log | ||
The name of the general query log file. The default value is
host_name.log--general_log_file option.
| Command-Line Format | --group_concat_max_len=# | ||
| Option-File Format | group_concat_max_len | ||
| Option Sets Variable | Yes, group_concat_max_len | ||
| Variable Name | group_concat_max_len | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 1024 | ||
| Range | 4-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 1024 | ||
| Range | 4-18446744073709547520 | ||
The maximum permitted result length in bytes for the
GROUP_CONCAT() function. The
default is 1024.
YES if the zlib
compression library is available to the server,
NO if not. If not, the
COMPRESS() and
UNCOMPRESS() functions cannot
be used.
YES if the crypt()
system call is available to the server, NO
if not. If not, the ENCRYPT()
function cannot be used.
YES if mysqld supports
ARCHIVE tables, NO if
not.
YES if mysqld supports
dynamic loading of plugins, NO if not.
YES if the server supports spatial data
types, NO if not.
YES if mysqld supports
InnoDB tables. DISABLED
if
--skip-innodb
is used.
This variable is an alias for
have_ssl.
YES if mysqld supports
partitioning.
YES if statement profiling is enabled,
NO if not. See
Section 12.4.5.31, “SHOW PROFILE Syntax”.
YES if mysqld supports
the query cache, NO if not.
YES if RTREE indexes are
available, NO if not. (These are used for
spatial indexes in MyISAM tables.)
YES if mysqld supports
SSL connections, NO if not.
YES if symbolic link support is enabled,
NO if not. This is required on Unix for
support of the DATA DIRECTORY and
INDEX DIRECTORY table options, and on
Windows for support of data directory symlinks.
| Variable Name | hostname | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The server sets this variable to the server host name at startup.
This variable is a synonym for the
last_insert_id variable. It
exists for compatibility with other database systems. You can
read its value with SELECT @@identity, and
set it using SET identity.
| Command-Line Format | --init-connect=name | ||
| Option-File Format | init_connect | ||
| Option Sets Variable | Yes, init_connect | ||
| Variable Name | init_connect | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
A string to be executed by the server for each client that
connects. The string consists of one or more SQL statements.
To specify multiple statements, separate them by semicolon
characters. For example, each client begins by default with
autocommit mode enabled. There is no global system variable to
specify that autocommit should be disabled by default, but
init_connect can be used to
achieve the same effect:
SET GLOBAL init_connect='SET autocommit=0';
This variable can also be set on the command line or in an option file. To set the variable as just shown using an option file, include these lines:
[mysqld] init_connect='SET autocommit=0'
The content of init_connect
is not executed for users that have the
SUPER privilege. This is done
so that an erroneous value for
init_connect does not prevent
all clients from connecting. For example, the value might
contain a statement that has a syntax error, thus causing
client connections to fail. Not executing
init_connect for users that
have the SUPER privilege
enables them to open a connection and fix the
init_connect value.
| Command-Line Format | --init-file=file_name | ||
| Option-File Format | init-file=file_name | ||
| Option Sets Variable | Yes, init_file | ||
| Variable Name | init_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The name of the file specified with the
--init-file option when you
start the server. This should be a file containing SQL
statements that you want the server to execute when it starts.
Each statement must be on a single line and should not include
comments. No statement terminator such as
;, \g, or
\G should be given at the end of each
statement.
Note that the --init-file
option is unavailable if MySQL was configured with the
DISABLE_GRANT_OPTIONS compiler
flag. See Section 2.11.4, “MySQL Source-Configuration Options”.
innodb_
xxx
InnoDB system variables are listed in
Section 13.6.4, “InnoDB Startup Options and System Variables”.
The value to be used by the following
INSERT or
ALTER TABLE statement when
inserting an AUTO_INCREMENT value. This is
mainly used with the binary log.
| Command-Line Format | --interactive_timeout=# | ||
| Option-File Format | interactive_timeout | ||
| Option Sets Variable | Yes, interactive_timeout | ||
| Variable Name | interactive_timeout | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 28800 | ||
| Min Value | 1 | ||
The number of seconds the server waits for activity on an
interactive connection before closing it. An interactive
client is defined as a client that uses the
CLIENT_INTERACTIVE option to
mysql_real_connect(). See also
wait_timeout.
| Command-Line Format | --join_buffer_size=# | ||
| Option-File Format | join_buffer_size | ||
| Option Sets Variable | Yes, join_buffer_size | ||
| Variable Name | join_buffer_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
The minimum size of the buffer that is used for plain index
scans, range index scans, and joins that do not use indexes
and thus perform full table scans. Normally, the best way to
get fast joins is to add indexes. Increase the value of
join_buffer_size to get a
faster full join when adding indexes is not possible. One join
buffer is allocated for each full join between two tables. For
a complex join between several tables for which indexes are
not used, multiple join buffers might be necessary. There is
no gain from setting the buffer larger than required to hold
each matching row, and all joins allocate at least the minimum
size, so use caution in setting this variable to a large value
globally. It is better to keep the global setting small and
change to a larger setting only in sessions that are doing
large joins. Memory allocation time can cause substantial
performance drops if the global size is larger than needed by
most queries that use it.
The maximum permissible setting for
join_buffer_size is 4GB.
Values larger than 4GB are permitted for 64-bit platforms
(except 64-bit Windows, for which large values are truncated
to 4GB with a warning).
| Command-Line Format | --keep_files_on_create=# | ||
| Option-File Format | keep_files_on_create | ||
| Option Sets Variable | Yes, keep_files_on_create | ||
| Variable Name | keep_files_on_create | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
If a MyISAM table is created with no
DATA DIRECTORY option, the
.MYD file is created in the database
directory. By default, if MyISAM finds an
existing .MYD file in this case, it
overwrites it. The same applies to .MYI
files for tables created with no INDEX
DIRECTORY option. To suppress this behavior, set the
keep_files_on_create variable
to ON (1), in which case
MyISAM will not overwrite existing files
and returns an error instead. The default value is
OFF (0).
If a MyISAM table is created with a
DATA DIRECTORY or INDEX
DIRECTORY option and an existing
.MYD or .MYI file is
found, MyISAM always returns an error. It will not overwrite a
file in the specified directory.
| Command-Line Format | --key_buffer_size=# | ||
| Option-File Format | key_buffer_size | ||
| Option Sets Variable | Yes, key_buffer_size | ||
| Variable Name | key_buffer_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 8388608 | ||
| Range | 8-4294967295 | ||
Index blocks for MyISAM tables are buffered
and are shared by all threads.
key_buffer_size is the size
of the buffer used for index blocks. The key buffer is also
known as the key cache.
The maximum permissible setting for
key_buffer_size is 4GB on
32-bit platforms. Values larger than 4GB are permitted for
64-bit platforms. The effective maximum size might be less,
depending on your available physical RAM and per-process RAM
limits imposed by your operating system or hardware platform.
The value of this variable indicates the amount of memory
requested. Internally, the server allocates as much memory as
possible up to this amount, but the actual allocation might be
less.
You can increase the value to get better index handling for
all reads and multiple writes; on a system whose primary
function is to run MySQL using the
MyISAM storage engine, 25% of the
machine's total memory is an acceptable value for this
variable. However, you should be aware that, if you make the
value too large (for example, more than 50% of the
machine's total memory), your system might start to page
and become extremely slow. This is because MySQL relies on the
operating system to perform file system caching for data
reads, so you must leave some room for the file system cache.
You should also consider the memory requirements of any other
storage engines that you may be using in addition to
MyISAM.
For even more speed when writing many rows at the same time,
use LOCK TABLES. See
Section 7.2.2.1, “Speed of INSERT Statements”.
You can check the performance of the key buffer by issuing a
SHOW STATUS statement and
examining the
Key_read_requests,
Key_reads,
Key_write_requests, and
Key_writes status variables.
(See Section 12.4.5, “SHOW Syntax”.) The
Key_reads/Key_read_requests ratio should
normally be less than 0.01. The
Key_writes/Key_write_requests ratio is
usually near 1 if you are using mostly updates and deletes,
but might be much smaller if you tend to do updates that
affect many rows at the same time or if you are using the
DELAY_KEY_WRITE table option.
The fraction of the key buffer in use can be determined using
key_buffer_size in
conjunction with the
Key_blocks_unused status
variable and the buffer block size, which is available from
the key_cache_block_size
system variable:
1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)
This value is an approximation because some space in the key buffer is allocated internally for administrative structures. Factors that influence the amount of overhead for these structures include block size and pointer size. As block size increases, the percentage of the key buffer lost to overhead tends to decrease. Larger blocks results in a smaller number of read operations (because more keys are obtained per read), but conversely an increase in reads of keys that are not examined (if not all keys in a block are relevant to a query).
It is possible to create multiple MyISAM
key caches. The size limit of 4GB applies to each cache
individually, not as a group. See
Section 7.9.2, “The MyISAM Key Cache”.
| Command-Line Format | --key_cache_age_threshold=# | ||
| Option-File Format | key_cache_age_threshold | ||
| Option Sets Variable | Yes, key_cache_age_threshold | ||
| Variable Name | key_cache_age_threshold | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 300 | ||
| Range | 100-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 300 | ||
| Range | 100-18446744073709547520 | ||
This value controls the demotion of buffers from the hot
sublist of a key cache to the warm sublist. Lower values cause
demotion to happen more quickly. The minimum value is 100. The
default value is 300. See Section 7.9.2, “The MyISAM Key Cache”.
| Command-Line Format | --key_cache_block_size=# | ||
| Option-File Format | key_cache_block_size | ||
| Option Sets Variable | Yes, key_cache_block_size | ||
| Variable Name | key_cache_block_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 1024 | ||
| Range | 512-16384 | ||
The size in bytes of blocks in the key cache. The default
value is 1024. See Section 7.9.2, “The MyISAM Key Cache”.
| Command-Line Format | --key_cache_division_limit=# | ||
| Option-File Format | key_cache_division_limit | ||
| Option Sets Variable | Yes, key_cache_division_limit | ||
| Variable Name | key_cache_division_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 100 | ||
| Range | 1-100 | ||
The division point between the hot and warm sublists of the
key cache buffer list. The value is the percentage of the
buffer list to use for the warm sublist. Permissible values
range from 1 to 100. The default value is 100. See
Section 7.9.2, “The MyISAM Key Cache”.
| Version Deprecated | 5.5.0 | ||
| Command-Line Format | --language=name | ||
-L | |||
| Option-File Format | language | ||
| Option Sets Variable | Yes, language | ||
| Variable Name | language | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Deprecated | 5.5.0, by lc-messages-dir | ||
| Permitted Values | |||
| Type | directory name | ||
| Default | /usr/local/mysql/share/mysql/english/ | ||
The directory where error messages are located. See Section 9.2, “Setting the Error Message Language”.
language is removed as of
MySQL 5.5.0. Similar information is available from the
lc_messages_dir and
lc_messages variables.
| Variable Name | large_files_support | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
Whether mysqld was compiled with options for large file support.
| Command-Line Format | --large-pages | ||
| Option-File Format | large-pages | ||
| Option Sets Variable | Yes, large_pages | ||
| Variable Name | large_pages | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Platform Specific | linux | ||
| Permitted Values | |||
| Type (linux) | boolean | ||
| Default | FALSE | ||
Whether large page support is enabled (via the
--large-pages option). See
Section 7.11.4.2, “Enabling Large Page Support”.
| Variable Name | large_page_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type (linux) | numeric | ||
| Default | 0 | ||
If large page support is enabled, this shows the size of memory pages. Currently, large memory pages are supported only on Linux; on other platforms, the value of this variable is always 0. See Section 7.11.4.2, “Enabling Large Page Support”.
The value to be returned from
LAST_INSERT_ID(). This is
stored in the binary log when you use
LAST_INSERT_ID() in a statement
that updates a table. Setting this variable does not update
the value returned by the
mysql_insert_id() C API
function.
| Command-Line Format | --lc-messages=name | ||
| Option-File Format | lc-messages | ||
| Option Sets Variable | Yes, lc_messages | ||
| Variable Name | lc-messages | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The locale to use for error messages. The server converts the
value to a language name and combines it with the value of the
lc_messages_dir to produce
the location for the error message file. See
Section 9.2, “Setting the Error Message Language”.
| Command-Line Format | --lc-messages-dir=path | ||
| Option-File Format | lc-messages-dir | ||
| Option Sets Variable | Yes, lc_messages_dir | ||
| Variable Name | lc-messages-dir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The directory where error messages are located. The value is
used together with the value of
lc_messages to produce the
location for the error message file. See
Section 9.2, “Setting the Error Message Language”.
| Variable Name | lc_time_names | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
This variable specifies the locale that controls the language
used to display day and month names and abbreviations. This
variable affects the output from the
DATE_FORMAT(),
DAYNAME() and
MONTHNAME() functions. Locale
names are POSIX-style values such as
'ja_JP' or 'pt_BR'. The
default value is 'en_US' regardless of your
system's locale setting. For further information, see
Section 9.7, “MySQL Server Locale Support”.
| Variable Name | license | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
| Default | GPL | ||
The type of license the server has.
| Variable Name | local_infile | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
Whether LOCAL is supported for
LOAD DATA
INFILE statements. See
Section 5.3.5, “Security Issues with LOAD
DATA LOCAL”.
| Version Introduced | 6.0.14 | ||
| Command-Line Format | --lock_wait_timeout=# | ||
| Option-File Format | lock_wait_timeout | ||
| Option Sets Variable | Yes, lock_wait_timeout | ||
| Variable Name | lock_wait_timeout | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 31536000 | ||
| Range | 1-31536000 | ||
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 3153600 (1 year). The default is 3153600.
This timeout applies to all statements that use metadata
locks. These include DML and DDL operations on tables, views,
stored procedures, and stored functions, as well as
LOCK TABLES,
FLUSH TABLES WITH READ
LOCK, and HANDLER
statements.
The timeout value applies separately for each metadata lock
attempt. A given statement can require more than one lock, so
it is possible for the statement to block for longer than the
lock_wait_timeout value
before reporting a timeout error. When lock timeout occurs,
ER_LOCK_WAIT_TIMEOUT is
reported.
lock_wait_timeout does not
apply to delayed inserts, which always execute with a timeout
of 1 year. This is done to avoid unnecessary timeouts because
a session that issues a delayed insert receives no
notification of delayed insert timeouts.
This variable was added in MySQL 5.5.3.
| Variable Name | locked_in_memory | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
Whether logging of all statements to the general query log is enabled. See Section 5.2.3, “The General Query Log”.
This variable is deprecated; use general_log
instead.
| Variable Name | log_bin | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
Whether the binary log is enabled. If the
--log-bin option is used, then
the value of this variable is ON; otherwise
it is OFF. This variable reports only on
the status of binary logging (enabled or disabled); it does
not actually report the value to which
--log-bin is set.
log_bin_trust_function_creators
| Command-Line Format | --log-bin-trust-function-creators | ||
| Option-File Format | log-bin-trust-function-creators | ||
| Option Sets Variable | Yes, log_bin_trust_function_creators | ||
| Variable Name | log_bin_trust_function_creators | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
This variable applies when binary logging is enabled. It
controls whether stored function creators can be trusted not
to create stored functions that will cause unsafe events to be
written to the binary log. If set to 0 (the default), users
are not permitted to create or alter stored functions unless
they have the SUPER privilege
in addition to the CREATE
ROUTINE or ALTER
ROUTINE privilege. A setting of 0 also enforces the
restriction that a function must be declared with the
DETERMINISTIC characteristic, or with the
READS SQL DATA or NO SQL
characteristic. If the variable is set to 1, MySQL does not
enforce these restrictions on stored function creation. This
variable also applies to trigger creation. See
Section 19.7, “Binary Logging of Stored Programs”.
| Command-Line Format | --log-error[=name] | ||
| Option-File Format | log-error | ||
| Option Sets Variable | Yes, log_error | ||
| Variable Name | log_error | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The location of the error log.
| Command-Line Format | --log-output[=name] | ||
| Option-File Format | log-output | ||
| Option Sets Variable | Yes, log_output | ||
| Variable Name | log_output | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | set | ||
| Default | FILE | ||
| Valid Values | TABLE, FILE, NONE | ||
The destination for general query log and slow query log
output. The value can be a comma-separated list of one or more
of the words TABLE (log to tables),
FILE (log to files), or
NONE (do not log to tables or files). The
default value is TABLE.
NONE, if present, takes precedence over any
other specifiers. If the value is NONE log
entries are not written even if the logs are enabled. If the
logs are not enabled, no logging occurs even if the value of
log_output is not
NONE. For more information, see
Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”.
| Command-Line Format | --log-queries-not-using-indexes | ||
| Option-File Format | log-queries-not-using-indexes | ||
| Option Sets Variable | Yes, log_queries_not_using_indexes | ||
| Variable Name | log_queries_not_using_indexes | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
Whether queries that do not use indexes are logged to the slow query log. See Section 5.2.5, “The Slow Query Log”.
Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect. See Section 17.1.3, “Replication and Binary Logging Options and Variables”.
| Command-Line Format | --log-slow-queries[=name] | ||
| Option-File Format | log-slow-queries | ||
| Option Sets Variable | Yes, log_slow_queries | ||
| Variable Name | log_slow_queries | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Deprecated | 5.1.29, by slow-query-log | ||
| Permitted Values | |||
| Type | boolean | ||
Whether slow queries should be logged. “Slow” is
determined by the value of the
long_query_time variable. See
Section 5.2.5, “The Slow Query Log”.
This variable is deprecated; use
slow_query_log instead.
| Command-Line Format | --log-warnings[=#] | ||
-W [#] | |||
| Option-File Format | log-warnings | ||
| Option Sets Variable | Yes, log_warnings | ||
| Variable Name | log_warnings | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Disabled by | skip-log-warnings | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 1 | ||
| Range | 0-18446744073709547520 | ||
Whether to produce additional warning messages to the error log. It is enabled (1) by default and can be disabled by setting it to 0. Aborted connections and access-denied errors for new connection attempts are logged if the value is greater than 1. The server logs messages about statements that are unsafe for statement-based logging only if the value is greater than 0.
| Command-Line Format | --long_query_time=# | ||
| Option-File Format | long_query_time | ||
| Option Sets Variable | Yes, long_query_time | ||
| Variable Name | long_query_time | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 10 | ||
| Min Value | 0 | ||
If a query takes longer than this many seconds, the server
increments the Slow_queries
status variable. If the slow query log is enabled, the query
is logged to the slow query log file. This value is measured
in real time, not CPU time, so a query that is under the
threshold on a lightly loaded system might be above the
threshold on a heavily loaded one. The minimum value is 0, and
a resolution of microseconds is supported when logging to a
file. However, the microseconds part is ignored and only
integer values are written when logging to tables. The default
value is 10. See Section 5.2.5, “The Slow Query Log”.
| Command-Line Format | --low-priority-updates | ||
| Option-File Format | low-priority-updates | ||
| Option Sets Variable | Yes, low_priority_updates | ||
| Variable Name | low_priority_updates | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
If set to 1, all
INSERT,
UPDATE,
DELETE, and LOCK TABLE
WRITE statements wait until there is no pending
SELECT or LOCK TABLE
READ on the affected table. This affects only
storage engines that use only table-level locking (such as
MyISAM, MEMORY, and
MERGE). This variable previously was named
sql_low_priority_updates.
| Command-Line Format | --lower_case_file_system[=#] | ||
| Option-File Format | lower_case_file_system | ||
| Option Sets Variable | Yes, lower_case_file_system | ||
| Variable Name | lower_case_file_system | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | boolean | ||
This variable describes the case sensitivity of file names on
the file system where the data directory is located.
OFF means file names are case sensitive,
ON means they are not case sensitive. This
variable is read only because it reflects a file system
attribute and setting it would have no effect on the file
system.
| Command-Line Format | --lower_case_table_names[=#] | ||
| Option-File Format | lower_case_table_names | ||
| Option Sets Variable | Yes, lower_case_table_names | ||
| Variable Name | lower_case_table_names | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-2 | ||
If set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional information, see Section 8.2.2, “Identifier Case Sensitivity”.
You should not set this variable to 0 if
you are running MySQL on a system that has case-insensitive
file names (such as Windows or Mac OS X). If you set this
variable to 0 on such a system and access
MyISAM tablenames using different
lettercases, index corruption may result. On Windows the
default value is 1. On Mac OS X, the default value is 2.
If you are using InnoDB tables, you should
set this variable to 1 on all platforms to force names to be
converted to lowercase.
The setting of this variable has no effect on replication filtering options. This is a known issue which is fixed in MySQL 5.6. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”, for more information.
| Command-Line Format | --max_allowed_packet=# | ||
| Option-File Format | max_allowed_packet | ||
| Option Sets Variable | Yes, max_allowed_packet | ||
| Variable Name | max_allowed_packet | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 1048576 | ||
| Range | 1024-1073741824 | ||
The maximum size of one packet or any generated/intermediate string.
The packet message buffer is initialized to
net_buffer_length bytes, but
can grow up to
max_allowed_packet bytes when
needed. This value by default is small, to catch large
(possibly incorrect) packets.
You must increase this value if you are using large
BLOB columns or long strings.
It should be as big as the largest
BLOB you want to use. The
protocol limit for
max_allowed_packet is 1GB.
The value should be a multiple of 1024; nonmultiples are
rounded down to the nearest multiple.
When you change the message buffer size by changing the value
of the max_allowed_packet
variable, you should also change the buffer size on the client
side if your client program permits it. On the client side,
max_allowed_packet has a
default of 1GB. Some programs such as mysql
and mysqldump enable you to change the
client-side value by setting
max_allowed_packet on the
command line or in an option file.
The session value of this variable is read only.
| Command-Line Format | --max_connect_errors=# | ||
| Option-File Format | max_connect_errors | ||
| Option Sets Variable | Yes, max_connect_errors | ||
| Variable Name | max_connect_errors | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 10 | ||
| Range | 1-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 10 | ||
| Range | 1-18446744073709547520 | ||
If there are more than this number of interrupted connections
from a host, that host is blocked from further connections.
You can unblock blocked hosts with the
FLUSH HOSTS
statement. If a connection is established successfully within
fewer than max_connect_errors
attempts after a previous connection was interrupted, the
error count for the host is cleared to zero. However, once a
host is blocked, the
FLUSH HOSTS
statement is the only way to unblock it.
| Command-Line Format | --max_connections=# | ||
| Option-File Format | max_connections | ||
| Option Sets Variable | Yes, max_connections | ||
| Variable Name | max_connections | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 151 | ||
| Range | 1-100000 | ||
The maximum permitted number of simultaneous client
connections. By default, this is 151. See
Section C.5.2.7, “Too many connections”, for more information.
Increasing this value increases the number of file descriptors that mysqld requires. See Section 7.4.3.1, “How MySQL Opens and Closes Tables”, for comments on file descriptor limits.
| Command-Line Format | --max_delayed_threads=# | ||
| Option-File Format | max_delayed_threads | ||
| Option Sets Variable | Yes, max_delayed_threads | ||
| Variable Name | max_delayed_threads | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 20 | ||
| Range | 0-16384 | ||
Do not start more than this number of threads to handle
INSERT DELAYED statements. If
you try to insert data into a new table after all
INSERT DELAYED threads are in
use, the row is inserted as if the DELAYED
attribute was not specified. If you set this to 0, MySQL never
creates a thread to handle DELAYED rows; in
effect, this disables DELAYED entirely.
For the SESSION value of this variable, the
only valid values are 0 or the GLOBAL
value.
| Command-Line Format | --max_error_count=# | ||
| Option-File Format | max_error_count | ||
| Option Sets Variable | Yes, max_error_count | ||
| Variable Name | max_error_count | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 64 | ||
| Range | 0-65535 | ||
The maximum number of error, warning, and note messages to be
stored for display by the SHOW
ERRORS and SHOW
WARNINGS statements.
| Command-Line Format | --max_heap_table_size=# | ||
| Option-File Format | max_heap_table_size | ||
| Option Sets Variable | Yes, max_heap_table_size | ||
| Variable Name | max_heap_table_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 16777216 | ||
| Range | 16384-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 16777216 | ||
| Range | 16384-1844674407370954752 | ||
This variable sets the maximum size to which user-created
MEMORY tables are permitted to grow. The
value of the variable is used to calculate
MEMORY table MAX_ROWS
values. Setting this variable has no effect on any existing
MEMORY table, unless the table is
re-created with a statement such as
CREATE TABLE or altered with
ALTER TABLE or
TRUNCATE TABLE. A server
restart also sets the maximum size of existing
MEMORY tables to the global
max_heap_table_size value.
This variable is also used in conjunction with
tmp_table_size to limit the
size of internal in-memory tables. See
Section 7.4.3.3, “How MySQL Uses Internal Temporary Tables”.
| Variable Name | max_insert_delayed_threads | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
This variable is a synonym for
max_delayed_threads.
| Command-Line Format | --max_join_size=# | ||
| Option-File Format | max_join_size | ||
| Option Sets Variable | Yes, max_join_size | ||
| Variable Name | max_join_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values (>= 5.5.0) | |||
| Type | numeric | ||
| Default | 18446744073709551615 | ||
| Range | 1-18446744073709551615 | ||
Do not permit SELECT statements
that probably need to examine more than
max_join_size rows (for
single-table statements) or row combinations (for
multiple-table statements) or that are likely to do more than
max_join_size disk seeks. By
setting this value, you can catch
SELECT statements where keys
are not used properly and that would probably take a long
time. Set it if your users tend to perform joins that lack a
WHERE clause, that take a long time, or
that return millions of rows.
Setting this variable to a value other than
DEFAULT resets the value of
sql_big_selects to
0. If you set the
sql_big_selects value again,
the max_join_size variable is
ignored.
If a query result is in the query cache, no result size check is performed, because the result has previously been computed and it does not burden the server to send it to the client.
This variable previously was named
sql_max_join_size.
| Command-Line Format | --max_length_for_sort_data=# | ||
| Option-File Format | max_length_for_sort_data | ||
| Option Sets Variable | Yes, max_length_for_sort_data | ||
| Variable Name | max_length_for_sort_data | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 1024 | ||
| Range | 4-8388608 | ||
The cutoff on the size of index values that determines which
filesort algorithm to use. See
Section 7.13.9, “ORDER BY Optimization”.
| Command-Line Format | --max_prepared_stmt_count=# | ||
| Option-File Format | max_prepared_stmt_count | ||
| Option Sets Variable | Yes, max_prepared_stmt_count | ||
| Variable Name | max_prepared_stmt_count | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 16382 | ||
| Range | 0-1048576 | ||
This variable limits the total number of prepared statements in the server. It can be used in environments where there is the potential for denial-of-service attacks based on running the server out of memory by preparing huge numbers of statements. If the value is set lower than the current number of prepared statements, existing statements are not affected and can be used, but no new statements can be prepared until the current number drops below the limit. The default value is 16,382. The permissible range of values is from 0 to 1 million. Setting the value to 0 disables prepared statements.
| Command-Line Format | --max_relay_log_size=# | ||
| Option-File Format | max_relay_log_size | ||
| Option Sets Variable | Yes, max_relay_log_size | ||
| Variable Name | max_relay_log_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-1073741824 | ||
If a write by a replication slave to its relay log causes the
current log file size to exceed the value of this variable,
the slave rotates the relay logs (closes the current file and
opens the next one). If
max_relay_log_size is 0, the
server uses max_binlog_size
for both the binary log and the relay log. If
max_relay_log_size is greater
than 0, it constrains the size of the relay log, which enables
you to have different sizes for the two logs. You must set
max_relay_log_size to between
4096 bytes and 1GB (inclusive), or to 0. The default value is
0. See Section 17.2.1, “Replication Implementation Details”.
| Command-Line Format | --max_seeks_for_key=# | ||
| Option-File Format | max_seeks_for_key | ||
| Option Sets Variable | Yes, max_seeks_for_key | ||
| Variable Name | max_seeks_for_key | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 4294967295 | ||
| Range | 1-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 18446744073709547520 | ||
| Range | 1-18446744073709547520 | ||
Limit the assumed maximum number of seeks when looking up rows
based on a key. The MySQL optimizer assumes that no more than
this number of key seeks are required when searching for
matching rows in a table by scanning an index, regardless of
the actual cardinality of the index (see
Section 12.4.5.23, “SHOW INDEX Syntax”). By setting this to a low value
(say, 100), you can force MySQL to prefer indexes instead of
table scans.
| Command-Line Format | --max_sort_length=# | ||
| Option-File Format | max_sort_length | ||
| Option Sets Variable | Yes, max_sort_length | ||
| Variable Name | max_sort_length | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 1024 | ||
| Range | 4-8388608 | ||
The number of bytes to use when sorting
BLOB or
TEXT values. Only the first
max_sort_length bytes of each
value are used; the rest are ignored.
| Command-Line Format | --max_sp_recursion_depth[=#] | ||
| Option-File Format | max_sp_recursion_depth | ||
| Option Sets Variable | Yes, max_sp_recursion_depth | ||
| Variable Name | max_sp_recursion_depth | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Max Value | 255 | ||
The number of times that any given stored procedure may be called recursively. The default value for this option is 0, which completely disables recursion in stored procedures. The maximum value is 255.
Stored procedure recursion increases the demand on thread
stack space. If you increase the value of
max_sp_recursion_depth, it
may be necessary to increase thread stack size by increasing
the value of thread_stack at
server startup.
| Command-Line Format | --max_tmp_tables=# | ||
| Option-File Format | max_tmp_tables | ||
| Option Sets Variable | Yes, max_tmp_tables | ||
| Variable Name | max_tmp_tables | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 32 | ||
| Range | 1-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 32 | ||
| Range | 1-18446744073709547520 | ||
The maximum number of temporary tables a client can keep open at the same time. (This variable does not yet do anything.)
| Command-Line Format | --max_user_connections=# | ||
| Option-File Format | max_user_connections | ||
| Option Sets Variable | Yes, max_user_connections | ||
| Variable Name | max_user_connections | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-4294967295 | ||
The maximum number of simultaneous connections permitted to any given MySQL user account. A value of 0 (the default) means “no limit.”
This variable has a global value that can be set at server startup or runtime. It also has a read-only session value that indicates the effective simultaneous-connection limit that applies to the account associated with the current session. The session value is initialized as follows:
If the user account has a nonzero
MAX_USER_CONNECTIONS resource limit,
the session
max_user_connections
value is set to that limit.
Otherwise, the session
max_user_connections
value is set to the global value.
Account resource limits are specified using the
GRANT statement. See
Section 5.5.4, “Setting Account Resource Limits”, and Section 12.4.1.3, “GRANT Syntax”.
| Command-Line Format | --max_write_lock_count=# | ||
| Option-File Format | max_write_lock_count | ||
| Option Sets Variable | Yes, max_write_lock_count | ||
| Variable Name | max_write_lock_count | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 4294967295 | ||
| Range | 1-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 18446744073709547520 | ||
| Range | 1-18446744073709547520 | ||
After this many write locks, permit some pending read lock requests to be processed in between.
| Command-Line Format | --min-examined-row-limit=# | ||
| Option-File Format | min-examined-row-limit | ||
| Variable Name | min_examined_row_limit | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-18446744073709547520 | ||
Queries that examine fewer than this number of rows are not logged to the slow query log.
| Command-Line Format | --myisam_data_pointer_size=# | ||
| Option-File Format | myisam_data_pointer_size | ||
| Option Sets Variable | Yes, myisam_data_pointer_size | ||
| Variable Name | myisam_data_pointer_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 6 | ||
| Range | 2-7 | ||
The default pointer size in bytes, to be used by
CREATE TABLE for
MyISAM tables when no
MAX_ROWS option is specified. This variable
cannot be less than 2 or larger than 7. The default value is
6. See Section C.5.2.12, “The table is full”.
| Command-Line Format | --myisam_max_sort_file_size=# | ||
| Option-File Format | myisam_max_sort_file_size | ||
| Option Sets Variable | Yes, myisam_max_sort_file_size | ||
| Variable Name | myisam_max_sort_file_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 2147483648 | ||
The maximum size of the temporary file that MySQL is permitted
to use while re-creating a MyISAM index
(during REPAIR TABLE,
ALTER TABLE, or
LOAD DATA
INFILE). If the file size would be larger than this
value, the index is created using the key cache instead, which
is slower. The value is given in bytes.
The default value is 2GB. If MyISAM index
files exceed this size and disk space is available, increasing
the value may help performance. The space must be available in
the file system containing the directory where the original
index file is located.
| Version Introduced | 5.5.1 | ||
| Command-Line Format | --myisam_mmap_size=# | ||
| Option-File Format | myisam_mmap_size | ||
| Option Sets Variable | Yes, myisam_mmap_size | ||
| Variable Name | myisam_mmap_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 4294967295 | ||
| Range | 7-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 18446744073709547520 | ||
| Range | 7-18446744073709547520 | ||
The maximum amount of memory to use for memory mapping
compressed MyISAM files. If many
compressed MyISAM tables are used, the
value can be decreased to reduce the likelihood of
memory-swapping problems. This variable was added in MySQL
5.5.1.
| Variable Name | myisam_recover_options | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
The value of the
--myisam-recover-options
option. See Section 5.1.2, “Server Command Options”.
| Command-Line Format | --myisam_repair_threads=# | ||
| Option-File Format | myisam_repair_threads | ||
| Option Sets Variable | Yes, myisam_repair_threads | ||
| Variable Name | myisam_repair_threads | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 1 | ||
| Range | 1-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 1 | ||
| Range | 1-18446744073709547520 | ||
If this value is greater than 1, MyISAM
table indexes are created in parallel (each index in its own
thread) during the Repair by sorting
process. The default value is 1.
Multi-threaded repair is still beta-quality code.
| Command-Line Format | --myisam_sort_buffer_size=# | ||
| Option-File Format | myisam_sort_buffer_size | ||
| Option Sets Variable | Yes, myisam_sort_buffer_size | ||
| Variable Name | myisam_sort_buffer_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 8388608 | ||
| Range | 4-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 8388608 | ||
| Range | 4-18446744073709547520 | ||
The size of the buffer that is allocated when sorting
MyISAM indexes during a
REPAIR TABLE or when creating
indexes with CREATE INDEX or
ALTER TABLE.
The maximum permissible setting for
myisam_sort_buffer_size is
4GB. Values larger than 4GB are permitted for 64-bit platforms
(except 64-bit Windows, for which large values are truncated
to 4GB with a warning).
| Command-Line Format | --myisam_stats_method=name | ||
| Option-File Format | myisam_stats_method | ||
| Option Sets Variable | Yes, myisam_stats_method | ||
| Variable Name | myisam_stats_method | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Valid Values | nulls_equal, nulls_unequal, nulls_ignored | ||
How the server treats NULL values when
collecting statistics about the distribution of index values
for MyISAM tables. This variable has three
possible values, nulls_equal,
nulls_unequal, and
nulls_ignored. For
nulls_equal, all NULL
index values are considered equal and form a single value
group that has a size equal to the number of
NULL values. For
nulls_unequal, NULL
values are considered unequal, and each
NULL forms a distinct value group of size
1. For nulls_ignored,
NULL values are ignored.
The method that is used for generating table statistics
influences how the optimizer chooses indexes for query
execution, as described in
Section 7.6.2, “MyISAM Index Statistics Collection”.
| Command-Line Format | --myisam_use_mmap | ||
| Option-File Format | myisam_use_mmap | ||
| Option Sets Variable | Yes, myisam_use_mmap | ||
| Variable Name | myisam_use_mmap | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Use memory mapping for reading and writing
MyISAM tables.
| Variable Name | named_pipe | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Platform Specific | windows | ||
| Permitted Values | |||
| Type (windows) | boolean | ||
| Default | OFF | ||
(Windows only.) Indicates whether the server supports connections over named pipes.
| Command-Line Format | --net_buffer_length=# | ||
| Option-File Format | net_buffer_length | ||
| Option Sets Variable | Yes, net_buffer_length | ||
| Variable Name | net_buffer_length | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 16384 | ||
| Range | 1024-1048576 | ||
Each client thread is associated with a connection buffer and
result buffer. Both begin with a size given by
net_buffer_length but are
dynamically enlarged up to
max_allowed_packet bytes as
needed. The result buffer shrinks to
net_buffer_length after each
SQL statement.
This variable should not normally be changed, but if you have
very little memory, you can set it to the expected length of
statements sent by clients. If statements exceed this length,
the connection buffer is automatically enlarged. The maximum
value to which
net_buffer_length can be set
is 1MB.
The session value of this variable is read only.
| Command-Line Format | --net_read_timeout=# | ||
| Option-File Format | net_read_timeout | ||
| Option Sets Variable | Yes, net_read_timeout | ||
| Variable Name | net_read_timeout | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 30 | ||
| Min Value | 1 | ||
The number of seconds to wait for more data from a connection
before aborting the read. This timeout applies only to TCP/IP
connections, not to connections made through Unix socket
files, named pipes, or shared memory. When the server is
reading from the client,
net_read_timeout is the
timeout value controlling when to abort. When the server is
writing to the client,
net_write_timeout is the
timeout value controlling when to abort. See also
slave_net_timeout.
| Command-Line Format | --net_retry_count=# | ||
| Option-File Format | net_retry_count | ||
| Option Sets Variable | Yes, net_retry_count | ||
| Variable Name | net_retry_count | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 10 | ||
| Range | 1-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 10 | ||
| Range | 1-18446744073709547520 | ||
If a read on a communication port is interrupted, retry this many times before giving up. This value should be set quite high on FreeBSD because internal interrupts are sent to all threads.
| Command-Line Format | --net_write_timeout=# | ||
| Option-File Format | net_write_timeout | ||
| Option Sets Variable | Yes, net_write_timeout | ||
| Variable Name | net_write_timeout | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 60 | ||
| Min Value | 1 | ||
The number of seconds to wait for a block to be written to a
connection before aborting the write. This timeout applies
only to TCP/IP connections, not to connections made using Unix
socket files, named pipes, or shared memory. See also
net_read_timeout.
| Command-Line Format | --new | ||
-n | |||
| Option-File Format | new | ||
| Option Sets Variable | Yes, new | ||
| Variable Name | new | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Disabled by | skip-new | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
This variable was used in MySQL 4.0 to turn on some 4.1
behaviors, and is retained for backward compatibility. In
MySQL 5.5, its value is always
OFF.
| Command-Line Format | --old | ||
| Option-File Format | old | ||
| Variable Name | old | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
old is a compatibility
variable. It is disabled by default, but can be enabled at
startup to revert the server to behaviors present in older
versions.
Currently, when old is
enabled, it changes the default scope of index hints to that
used prior to MySQL 5.1.17. That is, index hints with no
FOR clause apply only to how indexes are
used for row retrieval and not to resolution of ORDER
BY or GROUP BY clauses. (See
Section 12.2.9.2, “Index Hint Syntax”.) Take care about enabling this
in a replication setup. With statement-based binary logging,
having different modes for the master and slaves might lead to
replication errors.
| Command-Line Format | --old-alter-table | ||
| Option-File Format | old-alter-table | ||
| Option Sets Variable | Yes, old_alter_table | ||
| Variable Name | old-alter-table | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
When this variable is enabled, the server does not use the
optimized method of processing an ALTER
TABLE operation. It reverts to using a temporary
table, copying over the data, and then renaming the temporary
table to the original, as used by MySQL 5.0 and earlier. For
more information on the operation of
ALTER TABLE, see
Section 12.1.6, “ALTER TABLE Syntax”.
| Command-Line Format | --old_passwords | ||
| Option-File Format | old-passwords | ||
| Option Sets Variable | Yes, old_passwords | ||
| Variable Name | old_passwords | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Whether the server should use pre-4.1-style passwords for
MySQL user accounts. See Section C.5.2.4, “Client does not support authentication protocol”.
This is not a variable, but it can be used when setting some
variables. It is described in Section 12.4.4, “SET Syntax”.
| Command-Line Format | --open-files-limit=# | ||
| Option-File Format | open-files-limit | ||
| Option Sets Variable | Yes, open_files_limit | ||
| Variable Name | open_files_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-65535 | ||
The number of files that the operating system permits
mysqld to open. This is the real value
permitted by the system and might be different from the value
you gave using the
--open-files-limit option to
mysqld or mysqld_safe.
The value is 0 on systems where MySQL cannot change the number
of open files.
| Command-Line Format | --optimizer_prune_level[=#] | ||
| Option-File Format | optimizer_prune_level | ||
| Option Sets Variable | Yes, optimizer_prune_level | ||
| Variable Name | optimizer_prune_level | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 1 | ||
Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. A value of 0 disables heuristics so that the optimizer performs an exhaustive search. A value of 1 causes the optimizer to prune plans based on the number of rows retrieved by intermediate plans.
| Command-Line Format | --optimizer_search_depth[=#] | ||
| Option-File Format | optimizer_search_depth | ||
| Option Sets Variable | Yes, optimizer_search_depth | ||
| Variable Name | optimizer_search_depth | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values (<= 5.5.99) | |||
| Type | numeric | ||
| Default | 62 | ||
| Range | 0-63 | ||
The maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to generate an execution plan for a query. Values smaller than the number of relations in a query return an execution plan quicker, but the resulting plan may be far from being optimal. If set to 0, the system automatically picks a reasonable value. If set to 63, the optimizer switches to the algorithm used in MySQL 5.0.0 (and previous versions) for performing searches. The value of 63 is deprecated and will be treated as invalid in a future MySQL release.
| Command-Line Format | --optimizer_switch=value | ||
| Option-File Format | optimizer_switch | ||
optimizer_switch | |||
optimizer_switch | |||
| Option Sets Variable | Yes, optimizer_switch | ||
| Variable Name | optimizer_switch | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values (<= 5.5.2) | |||
| Type | set | ||
| Valid Values | index_merge={on|off}, index_merge_intersection={on|off}, index_merge_sort_union={on|off}, index_merge_union={on|off} | ||
| Permitted Values (>= 5.5.3) | |||
| Type | set | ||
| Valid Values | engine_condition_pushdown={on|off}, index_merge={on|off}, index_merge_intersection={on|off}, index_merge_sort_union={on|off}, index_merge_union={on|off} | ||
The optimizer_switch system
variable enables control over optimizer behavior. The value of
this variable is a set of flags, each of which has a value of
on or off to indicate
whether the corresponding optimizer behavior is enabled or
disabled. This variable has global and session values and can
be changed at runtime. The global default can be set at server
startup.
To see the current set of optimizer flags, select the variable value:
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on
For more information about the syntax of this variable and the optimizer behaviors that it controls, see Section 7.8.4.2, “Controlling Switchable Optimizations”.
| Command-Line Format | --pid-file=file_name | ||
| Option-File Format | pid-file=file_name | ||
| Option Sets Variable | Yes, pid_file | ||
| Variable Name | pid_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The path name of the process ID (PID) file. This variable can
be set with the --pid-file
option.
| Command-Line Format | --plugin_dir=path | ||
| Option-File Format | plugin_dir | ||
| Option Sets Variable | Yes, plugin_dir | ||
| Variable Name | plugin_dir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
| Default | /usr/local/mysql/lib/mysql | ||
The path name of the plugin directory.
If the plugin directory is writable by the server, it may be
possible for a user to write executable code to a file in the
directory using SELECT
... INTO DUMPFILE. This can be prevented by making
plugin_dir read only to the
server or by setting
--secure-file-priv to a
directory where SELECT writes
can be made safely.
| Command-Line Format | --port=# | ||
-P | |||
| Option-File Format | port | ||
| Option Sets Variable | Yes, port | ||
| Variable Name | port | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 3306 | ||
The number of the port on which the server listens for TCP/IP
connections. This variable can be set with the
--port option.
| Command-Line Format | --preload_buffer_size=# | ||
| Option-File Format | preload_buffer_size | ||
| Option Sets Variable | Yes, preload_buffer_size | ||
| Variable Name | preload_buffer_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 32768 | ||
| Range | 1024-1073741824 | ||
The size of the buffer that is allocated when preloading indexes.
If set to 0 (the default), statement profiling is disabled. If
set to 1, statement profiling is enabled and the
SHOW PROFILES and
SHOW PROFILE statements provide
access to profiling information. See
Section 12.4.5.32, “SHOW PROFILES Syntax”.
The number of statements for which to maintain profiling
information if profiling is
enabled. The default value is 15. The maximum value is 100.
Setting the value to 0 effectively disables profiling. See
Section 12.4.5.32, “SHOW PROFILES Syntax”.
| Variable Name | protocol_version | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
The version of the client/server protocol used by the MySQL server.
| Version Introduced | 5.5.7 | ||
| Variable Name | proxy_user | ||
| Variable Scope | Session | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
If the current client is a proxy for another user, this
variable is the proxy user account name. Otherwise, this
variable is NULL. See
Section 5.5.7, “Proxy Users”.
This variable was added in MySQL 5.5.7.
| Variable Name | pseudo_thread_id | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
This variable is for internal server use.
| Command-Line Format | --query_alloc_block_size=# | ||
| Option-File Format | query_alloc_block_size | ||
| Option Sets Variable | Yes, query_alloc_block_size | ||
| Variable Name | query_alloc_block_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 8192 | ||
| Range | 1024-4294967295 | ||
| Block Size | 1024 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 8192 | ||
| Range | 1024-18446744073709547520 | ||
| Block Size | 1024 | ||
The allocation size of memory blocks that are allocated for objects created during statement parsing and execution. If you have problems with memory fragmentation, it might help to increase this parameter.
| Command-Line Format | --query_cache_limit=# | ||
| Option-File Format | query_cache_limit | ||
| Option Sets Variable | Yes, query_cache_limit | ||
| Variable Name | query_cache_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 1048576 | ||
| Range | 0-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 1048576 | ||
| Range | 0-18446744073709547520 | ||
Do not cache results that are larger than this number of bytes. The default value is 1MB.
| Command-Line Format | --query_cache_min_res_unit=# | ||
| Option-File Format | query_cache_min_res_unit | ||
| Option Sets Variable | Yes, query_cache_min_res_unit | ||
| Variable Name | query_cache_min_res_unit | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 4096 | ||
| Range | 512-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 4096 | ||
| Range | 512-18446744073709547520 | ||
The minimum size (in bytes) for blocks allocated by the query cache. The default value is 4096 (4KB). Tuning information for this variable is given in Section 7.9.3.3, “Query Cache Configuration”.
| Command-Line Format | --query_cache_size=# | ||
| Option-File Format | query_cache_size | ||
| Option Sets Variable | Yes, query_cache_size | ||
| Variable Name | query_cache_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-18446744073709547520 | ||
The amount of memory allocated for caching query results. The
default value is 0, which disables the query cache. To reduce
overhead significantly, you should also start the server with
query_cache_type=0 if you
will not be using the query cache. The permissible values are
multiples of 1024; other values are rounded down to the
nearest multiple. Note that
query_cache_size bytes of
memory are allocated even if
query_cache_type is set to 0.
See Section 7.9.3.3, “Query Cache Configuration”, for more
information.
The query cache needs a minimum size of about 40KB to allocate
its structures. (The exact size depends on system
architecture.) If you set the value of
query_cache_size too small, a
warning will occur, as described in
Section 7.9.3.3, “Query Cache Configuration”.
| Command-Line Format | --query_cache_type=# | ||
| Option-File Format | query_cache_type | ||
| Option Sets Variable | Yes, query_cache_type | ||
| Variable Name | query_cache_type | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | 1 | ||
| Valid Values | 0, 1, 2 | ||
Set the query cache type. Setting the
GLOBAL value sets the type for all clients
that connect thereafter. Individual clients can set the
SESSION value to affect their own use of
the query cache. Possible values are shown in the following
table.
| Option | Description |
|---|---|
0 or OFF | Do not cache results in or retrieve results from the query cache. Note
that this does not deallocate the query cache buffer.
To do that, you should set
query_cache_size to
0. |
1 or ON | Cache all cacheable query results except for those that begin with
SELECT SQL_NO_CACHE. |
2 or DEMAND | Cache results only for cacheable queries that begin with SELECT
SQL_CACHE. |
This variable defaults to ON.
If the server is started with
query_cache_type set to 0, it does not
acquire the query cache mutex at all, which means that the
query cache cannot be enabled at runtime and there is reduced
overhead in query execution.
| Command-Line Format | --query_cache_wlock_invalidate | ||
| Option-File Format | query_cache_wlock_invalidate | ||
| Option Sets Variable | Yes, query_cache_wlock_invalidate | ||
| Variable Name | query_cache_wlock_invalidate | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Normally, when one client acquires a WRITE
lock on a MyISAM table, other clients are
not blocked from issuing statements that read from the table
if the query results are present in the query cache. Setting
this variable to 1 causes acquisition of a
WRITE lock for a table to invalidate any
queries in the query cache that refer to the table. This
forces other clients that attempt to access the table to wait
while the lock is in effect.
| Command-Line Format | --query_prealloc_size=# | ||
| Option-File Format | query_prealloc_size | ||
| Option Sets Variable | Yes, query_prealloc_size | ||
| Variable Name | query_prealloc_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 8192 | ||
| Range | 8192-4294967295 | ||
| Block Size | 1024 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 8192 | ||
| Range | 8192-18446744073709547520 | ||
| Block Size | 1024 | ||
The size of the persistent buffer used for statement parsing
and execution. This buffer is not freed between statements. If
you are running complex queries, a larger
query_prealloc_size value
might be helpful in improving performance, because it can
reduce the need for the server to perform memory allocation
during query execution operations.
The rand_seed1 and
rand_seed2 variables exist as
session variables only, and can be set but not read. The
variables—but not their values—are shown in the
output of SHOW VARIABLES.
The purpose of these variables is to support replication of
the RAND() function. For
statements that invoke RAND(),
the master passes two values to the slave, where they are used
to seed the random number generator. The slave uses these
values to set the session variables
rand_seed1 and
rand_seed2 so that
RAND() on the slave generates
the same value as on the master.
See the description for
rand_seed1.
| Command-Line Format | --range_alloc_block_size=# | ||
| Option-File Format | range_alloc_block_size | ||
| Option Sets Variable | Yes, range_alloc_block_size | ||
| Variable Name | range_alloc_block_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 4096 | ||
| Range | 4096-4294967295 | ||
| Block Size | 1024 | ||
The size of blocks that are allocated when doing range optimization.
| Command-Line Format | --read_buffer_size=# | ||
| Option-File Format | read_buffer_size | ||
| Option Sets Variable | Yes, read_buffer_size | ||
| Variable Name | read_buffer_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 131072 | ||
| Range | 8200-2147479552 | ||
Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB.
The maximum permissible setting for
read_buffer_size is 2GB.
read_buffer_size and
read_rnd_buffer_size are not
specific to any storage engine and apply in a general manner
for optimization. See Section 7.11.4.1, “How MySQL Uses Memory”, for
example.
| Command-Line Format | --read-only | ||
| Option-File Format | read_only | ||
| Option Sets Variable | Yes, read_only | ||
| Variable Name | read_only | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
This variable is off by default. When it is enabled, the
server permits no updates except from users that have the
SUPER privilege or (on a slave
server) from updates performed by slave threads. In
replication setups, it can be useful to enable
read_only on slave servers to
ensure that slaves accept updates only from the master server
and not from clients.
read_only does not apply to
TEMPORARY tables, nor does it prevent the
server from inserting rows into the log tables (see
Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”). This variable does not
prevent the use of ANALYZE
TABLE or OPTIMIZE
TABLE statements because its purpose is to prevent
changes to table structure or contents. Analysis and
optimization do not qualify as such changes. This means, for
example, that consistency checks on read-only slaves can be
performed with mysqlcheck --all-databases
--analyze.
read_only exists only as a
GLOBAL variable, so changes to its value
require the SUPER privilege.
Changes to read_only on a
master server are not replicated to slave servers. The value
can be set on a slave server independent of the setting on the
master.
The following conditions apply:
If you attempt to enable
read_only while you have
any explicit locks (acquired with
LOCK TABLES) or have a
pending transaction, an error occurs.
If you attempt to enable
read_only while other
clients hold explicit table locks or have pending
transactions, the attempt blocks until the locks are
released and the transactions end. While the attempt to
enable read_only is
pending, requests by other clients for table locks or to
begin transactions also block until
read_only has been set.
read_only can be enabled
while you hold a global read lock (acquired with
FLUSH TABLES WITH
READ LOCK) because that does not involve table
locks.
As of MySQL 5.5.3, attempts to set
read_only block for active
transactions that hold metadata locks until those transactions
end.
| Command-Line Format | --read_rnd_buffer_size=# | ||
| Option-File Format | read_rnd_buffer_size | ||
| Option Sets Variable | Yes, read_rnd_buffer_size | ||
| Variable Name | read_rnd_buffer_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 262144 | ||
| Range | 8200-4294967295 | ||
When reading rows in sorted order following a key-sorting
operation, the rows are read through this buffer to avoid disk
seeks. See Section 7.13.9, “ORDER BY Optimization”. Setting
the variable to a large value can improve ORDER
BY performance by a lot. However, this is a buffer
allocated for each client, so you should not set the global
variable to a large value. Instead, change the session
variable only from within those clients that need to run large
queries.
The maximum permissible setting for
read_rnd_buffer_size is 2GB.
read_buffer_size and
read_rnd_buffer_size are not
specific to any storage engine and apply in a general manner
for optimization. See Section 7.11.4.1, “How MySQL Uses Memory”, for
example.
| Command-Line Format | --relay_log_purge | ||
| Option-File Format | relay_log_purge | ||
| Option Sets Variable | Yes, relay_log_purge | ||
| Variable Name | relay_log_purge | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | TRUE | ||
Disables or enables automatic purging of relay log files as
soon as they are not needed any more. The default value is 1
(ON).
| Command-Line Format | --relay_log_space_limit=# | ||
| Option-File Format | relay_log_space_limit | ||
| Option Sets Variable | Yes, relay_log_space_limit | ||
| Variable Name | relay_log_space_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-18446744073709547520 | ||
The maximum amount of space to use for all relay logs.
| Command-Line Format | --report-host=host_name | ||
| Option-File Format | report-host | ||
| Option Sets Variable | Yes, report_host | ||
| Variable Name | report-host | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The value of the --report-host
option.
| Command-Line Format | --report-password=name | ||
| Option-File Format | report-password | ||
| Option Sets Variable | Yes, report_password | ||
| Variable Name | report-password | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The value of the
--report-password option.
| Command-Line Format | --report-port=# | ||
| Option-File Format | report-port | ||
| Option Sets Variable | Yes, report_port | ||
| Variable Name | report-port | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 3306 | ||
The value of the --report-port
option.
| Command-Line Format | --report-user=name | ||
| Option-File Format | report-user | ||
| Option Sets Variable | Yes, report_user | ||
| Variable Name | report-user | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The value of the --report-user
option.
| Variable Name | rpl_semi_sync_master_enabled | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Controls whether semisynchronous replication is enabled on the
master. To enable or disable the plugin, set this variable to
ON or OFF (or 1 or 0),
respectively. The default is OFF.
This variable is available only if the master-side semisynchronous replication plugin is installed.
| Variable Name | rpl_semi_sync_master_timeout | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values (>= 5.5.0) | |||
| Type | numeric | ||
| Default | 10000 | ||
A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds).
This variable is available only if the master-side semisynchronous replication plugin is installed.
rpl_semi_sync_master_trace_level
| Variable Name | rpl_semi_sync_master_trace_level | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 32 | ||
The semisynchronous replication debug trace level on the master. Currently, four levels are defined:
1 = general level (for example, time function failures)
16 = detail level (more verbose information)
32 = net wait level (more information about network waits)
64 = function level (information about function entry and exit)
This variable is available only if the master-side semisynchronous replication plugin is installed.
rpl_semi_sync_master_wait_no_slave
| Variable Name | rpl_semi_sync_master_wait_no_slave | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | ON | ||
With semisynchronous replication, for each transaction, the master waits until timeout for acknowledgment of receipt from some semisynchronous slave. If no response occurs during this period, the master reverts to normal replication. This variable controls whether the master waits for the timeout to expire before reverting to normal replication even if the slave count drops to zero during the timeout period.
If the value is ON (the default), it is
permissible for the slave count to drop to zero during the
timeout period (for example, if slaves disconnect). The master
still waits for the timeout, so as long as some slave
reconnects and acknowledges the transaction within the timeout
interval, semisynchronous replication continues.
If the value is OFF, the master reverts to
normal replication if the slave count drops to zero during the
timeout period.
This variable is available only if the master-side semisynchronous replication plugin is installed.
| Variable Name | rpl_semi_sync_slave_enabled | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Controls whether semisynchronous replication is enabled on the
slave. To enable or disable the plugin, set this variable to
ON or OFF (or 1 or 0),
respectively. The default is OFF.
This variable is available only if the slave-side semisynchronous replication plugin is installed.
rpl_semi_sync_slave_trace_level
| Variable Name | rpl_semi_sync_slave_trace_level | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 32 | ||
The semisynchronous replication debug trace level on the
slave. See
rpl_semi_sync_master_trace_level
for the permissible values.
This variable is available only if the slave-side semisynchronous replication plugin is installed.
| Command-Line Format | --secure-auth | ||
| Option-File Format | secure-auth | ||
| Option Sets Variable | Yes, secure_auth | ||
| Variable Name | secure_auth | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
If the MySQL server has been started with the
--secure-auth option, it blocks
connections from all accounts that have passwords stored in
the old (pre-4.1) format. In that case, the value of this
variable is ON, otherwise it is
OFF.
You should enable this option if you want to prevent all use of passwords employing the old format (and hence insecure communication over the network).
Server startup fails with an error if this option is enabled
and the privilege tables are in pre-4.1 format. See
Section C.5.2.4, “Client does not support authentication protocol”.
| Command-Line Format | --secure-file-priv=path | ||
| Option-File Format | secure-file-priv=path | ||
| Option Sets Variable | Yes, secure_file_priv | ||
| Variable Name | secure-file-priv | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
By default, this variable is empty. If set to the name of a
directory, it limits the effect of the
LOAD_FILE() function and the
LOAD DATA and
SELECT ... INTO
OUTFILE statements to work only with files in that
directory.
| Command-Line Format | --server-id=# | ||
| Option-File Format | server-id | ||
| Option Sets Variable | Yes, server_id | ||
| Variable Name | server_id | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-4294967295 | ||
The server ID, used in replication to give each master and
slave a unique identity. This variable is set by the
--server-id option. For each
server participating in replication, you should pick a
positive integer in the range from 1 to
232 – 1 to act as that
server's ID.
| Variable Name | shared_memory | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Platform Specific | windows | ||
(Windows only.) Whether the server permits shared-memory connections.
| Variable Name | shared_memory_base_name | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Platform Specific | windows | ||
(Windows only.) The name of shared memory to use for
shared-memory connections. This is useful when running
multiple MySQL instances on a single physical machine. The
default name is MYSQL. The name is case
sensitive.
This is OFF if mysqld
uses external locking, ON if external
locking is disabled.
This variable is set from the value of the
--skip-name-resolve option. If
it is ON, mysqld
resolves host names when checking client connections. If
OFF, mysqld uses only IP
numbers and all Host column values in the
grant tables must be IP addresses or
localhost. See Section 7.11.5.2, “How MySQL Uses DNS”.
This variable was added in MySQL 5.5.5.
This is ON if the server permits only local
(non-TCP/IP) connections. On Unix, local connections use a
Unix socket file. On Windows, local connections use a named
pipe or shared memory. This variable can be set to
ON with the
--skip-networking option.
This prevents people from using the SHOW
DATABASES statement if they do not have the
SHOW DATABASES privilege. This
can improve security if you have concerns about users being
able to see databases belonging to other users. Its effect
depends on the SHOW DATABASES
privilege: If the variable value is ON, the
SHOW DATABASES statement is
permitted only to users who have the SHOW
DATABASES privilege, and the statement displays all
database names. If the value is OFF,
SHOW DATABASES is permitted to
all users, but displays the names of only those databases for
which the user has the SHOW
DATABASES or other privilege.
| Command-Line Format | --slow_launch_time=# | ||
| Option-File Format | slow_launch_time | ||
| Option Sets Variable | Yes, slow_launch_time | ||
| Variable Name | slow_launch_time | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 2 | ||
If creating a thread takes longer than this many seconds, the
server increments the
Slow_launch_threads status
variable.
| Command-Line Format | --slow-query-log | ||
| Option-File Format | slow-query-log | ||
| Option Sets Variable | Yes, slow_query_log | ||
| Variable Name | slow_query_log | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Whether the slow query log is enabled. The value can be 0 (or
OFF) to disable the log or 1 (or
ON) to enable the log. The default value
depends on whether the
--slow_query_log option is
given. The destination for log output is controlled by the
log_output system variable;
if that value is NONE, no log entries are
written even if the log is enabled.
| Command-Line Format | --slow-query-log-file=file_name | ||
| Option-File Format | slow_query_log_file | ||
| Option Sets Variable | Yes, slow_query_log_file | ||
| Variable Name | slow_query_log_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | file name | ||
The name of the slow query log file. The default value is
host_name-slow.log--slow_query_log_file option.
| Command-Line Format | --socket=name | ||
| Option-File Format | socket | ||
| Option Sets Variable | Yes, socket | ||
| Variable Name | socket | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
| Default | /tmp/mysql.sock | ||
On Unix platforms, this variable is the name of the socket
file that is used for local client connections. The default is
/tmp/mysql.sock. (For some distribution
formats, the directory might be different, such as
/var/lib/mysql for RPMs.)
On Windows, this variable is the name of the named pipe that
is used for local client connections. The default value is
MySQL (not case sensitive).
| Command-Line Format | --sort_buffer_size=# | ||
| Option-File Format | sort_buffer_size | ||
| Option Sets Variable | Yes, sort_buffer_size | ||
| Variable Name | sort_buffer_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 2097144 | ||
| Max Value | 4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 2097144 | ||
| Max Value | 18446744073709547520 | ||
Each session that needs to do a sort allocates a buffer of
this size. sort_buffer_size
is not specific to any storage engine and applies in a general
manner for optimization. See
Section 7.13.9, “ORDER BY Optimization”, for example.
If you see many
Sort_merge_passes per second
in SHOW GLOBAL
STATUS output, you can consider increasing the
sort_buffer_size value to
speed up ORDER BY or GROUP
BY operations that cannot be improved with query
optimization or improved indexing. The entire buffer is
allocated even if it is not all needed, so setting it larger
than required globally will slow down most queries that sort.
It is best to increase it as a session setting, and only for
the sessions that need a larger size. On Linux, there are
thresholds of 256KB and 2MB where larger values may
significantly slow down memory allocation, so you should
consider staying below one of those values. Experiment to find
the best value for your workload. See
Section C.5.4.4, “Where MySQL Stores Temporary Files”.
The maximum permissible setting for
sort_buffer_size is 4GB.
Values larger than 4GB are permitted for 64-bit platforms
(except 64-bit Windows, for which large values are truncated
to 4GB with a warning).
| Variable Name | sql_auto_is_null | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values (<= 5.5.2) | |||
| Type | boolean | ||
| Default | 1 | ||
| Permitted Values (>= 5.5.3) | |||
| Type | boolean | ||
| Default | 0 | ||
If this variable is set to 1, then after a statement that
successfully inserts an automatically generated
AUTO_INCREMENT value, you can find that
value by issuing a statement of the following form:
SELECT * FROMtbl_nameWHEREauto_colIS NULL
If the statement returns a row, the value returned is the same
as if you invoked the
LAST_INSERT_ID() function. For
details, including the return value after a multiple-row
insert, see Section 11.14, “Information Functions”. If no
AUTO_INCREMENT value was successfully
inserted, the SELECT statement
returns no row.
The behavior of retrieving an
AUTO_INCREMENT value by using an
IS NULL comparison is used by
some ODBC programs, such as Access. See
Section 22.1.7.1.1, “Obtaining Auto-Increment Values”.
This behavior can be disabled by setting
sql_auto_is_null to 0.
The default value of
sql_auto_is_null is 0 as of
MySQL 5.5.3, and 1 for earlier versions.
| Variable Name | sql_big_selects | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 1 | ||
If set to 0, MySQL aborts
SELECT statements that are
likely to take a very long time to execute (that is,
statements for which the optimizer estimates that the number
of examined rows exceeds the value of
max_join_size). This is
useful when an inadvisable WHERE statement
has been issued. The default value for a new connection is 1,
which permits all SELECT
statements.
If you set the max_join_size
system variable to a value other than
DEFAULT,
sql_big_selects is set to 0.
| Variable Name | sql_buffer_result | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 0 | ||
If set to 1,
sql_buffer_result forces
results from SELECT statements
to be put into temporary tables. This helps MySQL free the
table locks early and can be beneficial in cases where it
takes a long time to send results to the client. The default
value is 0.
| Variable Name | sql_log_bin | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
If set to 0, no logging is done to the binary log for the
client. The client must have the
SUPER privilege to set this
option. The default value is 1.
Beginning with MySQL 5.5.5, it is no longer possible to set
@@session.sql_log_bin within a transaction
or subquery. (Bug#53437)
| Variable Name | sql_log_off | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 0 | ||
If set to 1, no logging is done to the general query log for
this client. The client must have the
SUPER privilege to set this
option. The default value is 0.
| Version Removed | 5.5.3 | ||
| Variable Name | sql_log_update | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Deprecated | 5.0, by sql_log_bin | ||
| Permitted Values | |||
| Type | boolean | ||
This variable is deprecated, and is mapped to
sql_log_bin. It was removed
in MySQL 5.5.3.
| Command-Line Format | --sql-mode=name | ||
| Option-File Format | sql-mode | ||
| Option Sets Variable | Yes, sql_mode | ||
| Variable Name | sql_mode | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | set | ||
| Default | '' | ||
| Valid Values | ALLOW_INVALID_DATES, ANSI_QUOTES, ERROR_FOR_DIVISION_BY_ZERO, HIGH_NOT_PRECEDENCE, IGNORE_SPACE, NO_AUTO_CREATE_USER, NO_AUTO_VALUE_ON_ZERO, NO_BACKSLASH_ESCAPES, NO_DIR_IN_CREATE, NO_ENGINE_SUBSTITUTION, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_UNSIGNED_SUBTRACTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY, PAD_CHAR_TO_FULL_LENGTH, PIPES_AS_CONCAT, REAL_AS_FLOAT, STRICT_ALL_TABLES, STRICT_TRANS_TABLES | ||
The current server SQL mode, which can be set dynamically. See Section 5.1.7, “Server SQL Modes”.
If set to 1 (the default), warnings of Note
level are recorded. If set to 0, Note
warnings are suppressed. mysqldump includes
output to set this variable to 0 so that reloading the dump
file does not produce warnings for events that do not affect
the integrity of the reload operation.
If set to 1 (the default), the server quotes identifiers for
SHOW CREATE TABLE and
SHOW CREATE DATABASE
statements. If set to 0, quoting is disabled. This option is
enabled by default so that replication works for identifiers
that require quoting. See Section 12.4.5.12, “SHOW CREATE TABLE Syntax”,
and Section 12.4.5.8, “SHOW CREATE DATABASE Syntax”.
If set to 1, MySQL aborts
UPDATE or
DELETE statements that do not
use a key in the WHERE clause or a
LIMIT clause. This makes it possible to
catch UPDATE or
DELETE statements where keys
are not used properly and that would probably change or delete
a large number of rows. The default value is 0.
| Variable Name | sql_select_limit | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
The maximum number of rows to return from
SELECT statements. The default
value for a new connection is the maximum number of rows that
the server permits per table. Typical default values are
(232)–1 or
(264)–1. If you have changed
the limit, the default value can be restored by assigning a
value of DEFAULT.
If a SELECT has a
LIMIT clause, the LIMIT
takes precedence over the value of
sql_select_limit.
sql_select_limit does not
apply to SELECT statements
executed within stored routines. It also does not apply to
SELECT statements that do not
produce a result set to be returned to the client. These
include SELECT statements in
subqueries,
CREATE TABLE ...
SELECT, and
INSERT INTO ...
SELECT.
This variable controls whether single-row
INSERT statements produce an
information string if warnings occur. The default is 0. Set
the value to 1 to produce an information string.
| Command-Line Format | --ssl-ca=name | ||
| Option-File Format | ssl-ca | ||
| Option Sets Variable | Yes, ssl_ca | ||
| Variable Name | ssl_ca | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The path to a file with a list of trusted SSL CAs.
| Command-Line Format | --ssl-capath=name | ||
| Option-File Format | ssl-capath | ||
| Option Sets Variable | Yes, ssl_capath | ||
| Variable Name | ssl_capath | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The path to a directory that contains trusted SSL CA certificates in PEM format.
| Command-Line Format | --ssl-cert=name | ||
| Option-File Format | ssl-cert | ||
| Option Sets Variable | Yes, ssl_cert | ||
| Variable Name | ssl_cert | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The name of the SSL certificate file to use for establishing a secure connection.
| Command-Line Format | --ssl-cipher=name | ||
| Option-File Format | ssl-cipher | ||
| Option Sets Variable | Yes, ssl_cipher | ||
| Variable Name | ssl_cipher | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
A list of permissible ciphers to use for SSL encryption.
| Command-Line Format | --ssl-key=name | ||
| Option-File Format | ssl-key | ||
| Option Sets Variable | Yes, ssl_key | ||
| Variable Name | ssl_key | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The name of the SSL key file to use for establishing a secure connection.
| Variable Name | storage_engine | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values (>= 5.5.3, <= 5.5.4) | |||
| Type | enumeration | ||
| Default | MyISAM | ||
| Permitted Values (>= 5.5.5) | |||
| Type | enumeration | ||
| Default | InnoDB | ||
The default storage engine (table type). To set the storage
engine at server startup, use the
--default-storage-engine
option. See Section 5.1.2, “Server Command Options”.
This variable is deprecated as of MySQL 5.5.3 in favor of
default_storage_engine.
| Command-Line Format | --sync-frm | ||
| Option-File Format | sync_frm | ||
| Option Sets Variable | Yes, sync_frm | ||
| Variable Name | sync_frm | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | TRUE | ||
If this variable is set to 1, when any nontemporary table is
created its .frm file is synchronized to
disk (using fdatasync()). This is slower
but safer in case of a crash. The default is 1.
| Variable Name | system_time_zone | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The server system time zone. When the server begins executing,
it inherits a time zone setting from the machine defaults,
possibly modified by the environment of the account used for
running the server or the startup script. The value is used to
set system_time_zone.
Typically the time zone is specified by the
TZ environment variable. It also can be
specified using the
--timezone option of the
mysqld_safe script.
The system_time_zone variable
differs from time_zone.
Although they might have the same value, the latter variable
is used to initialize the time zone for each client that
connects. See Section 9.6, “MySQL Server Time Zone Support”.
| Command-Line Format | --table_definition_cache=# | ||
| Option-File Format | table_definition_cache | ||
| Option Sets Variable | Yes, table_definition_cache | ||
| Variable Name | table_definition_cache | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 400 | ||
| Range | 400-524288 | ||
The number of table definitions that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. The minimum and default values are both 400.
| Version Removed | 5.5.3 | ||
| Command-Line Format | --table_lock_wait_timeout=# | ||
| Option-File Format | table_lock_wait_timeout | ||
| Option Sets Variable | Yes, table_lock_wait_timeout | ||
| Variable Name | table_lock_wait_timeout | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 50 | ||
| Range | 1-1073741824 | ||
This variable is unused. It was removed in 5.5.3.
| Command-Line Format | --table-open-cache=# | ||
| Option-File Format | table_open_cache | ||
| Variable Name | table_open_cache | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 400 | ||
| Range | 400-524288 | ||
The number of open tables for all threads. Increasing this
value increases the number of file descriptors that
mysqld requires. You can check whether you
need to increase the table cache by checking the
Opened_tables status
variable. See Section 5.1.6, “Server Status Variables”. If
the value of Opened_tables
is large and you do not use
FLUSH TABLES
often (which just forces all tables to be closed and
reopened), then you should increase the value of the
table_open_cache variable.
For more information about the table cache, see
Section 7.4.3.1, “How MySQL Opens and Closes Tables”.
This variable was removed in MySQL 5.5.3. Use
storage_engine instead.
| Command-Line Format | --thread_cache_size=# | ||
| Option-File Format | thread_cache_size | ||
| Option Sets Variable | Yes, thread_cache_size | ||
| Variable Name | thread_cache_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Range | 0-16384 | ||
How many threads the server should cache for reuse. When a
client disconnects, the client's threads are put in the cache
if there are fewer than
thread_cache_size threads
there. Requests for threads are satisfied by reusing threads
taken from the cache if possible, and only when the cache is
empty is a new thread created. This variable can be increased
to improve performance if you have a lot of new connections.
Normally, this does not provide a notable performance
improvement if you have a good thread implementation. However,
if your server sees hundreds of connections per second you
should normally set
thread_cache_size high enough
so that most new connections use cached threads. By examining
the difference between the
Connections and
Threads_created status
variables, you can see how efficient the thread cache is. For
details, see Section 5.1.6, “Server Status Variables”.
| Command-Line Format | --thread_concurrency=# | ||
| Option-File Format | thread_concurrency | ||
| Option Sets Variable | Yes, thread_concurrency | ||
| Variable Name | thread_concurrency | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 10 | ||
| Range | 1-512 | ||
This variable is specific to Solaris systems, for which
mysqld invokes the
thr_setconcurrency() with the variable
value. This function enables applications to give the threads
system a hint about the desired number of threads that should
be run at the same time.
| Command-Line Format | --thread_handling=name | ||
| Option-File Format | thread_handling | ||
| Option Sets Variable | Yes, thread_handling | ||
| Variable Name | thread_handling | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
The thread-handling model. The permissible values are
no-threads (the server uses one thread) and
one-thread-per-connection (the server uses
one thread to handle each client connection).
no-threads is useful for debugging under
Linux; see
MySQL
Internals: Porting.
| Command-Line Format | --thread_stack=# | ||
| Option-File Format | thread_stack | ||
| Option Sets Variable | Yes, thread_stack | ||
| Variable Name | thread_stack | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 196608 | ||
| Range | 131072-4294967295 | ||
| Block Size | 1024 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 262144 | ||
| Range | 131072-18446744073709547520 | ||
| Block Size | 1024 | ||
The stack size for each thread. Many of the limits detected by
the crash-me test are dependent on this
value. See Section 7.12.2, “The MySQL Benchmark Suite”. The default of
192KB (256KB for 64-bit systems) is large enough for normal
operation. If the thread stack size is too small, it limits
the complexity of the SQL statements that the server can
handle, the recursion depth of stored procedures, and other
memory-consuming actions.
This variable is unused.
| Command-Line Format | --default_time_zone=string | ||
| Option-File Format | default_time_zone | ||
| Variable Name | time_zone | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The current time zone. This variable is used to initialize the
time zone for each client that connects. By default, the
initial value of this is 'SYSTEM' (which
means, “use the value of
system_time_zone”).
The value can be specified explicitly at server startup with
the --default-time-zone option.
See Section 9.6, “MySQL Server Time Zone Support”.
| Command-Line Format | --timed_mutexes | ||
| Option-File Format | timed_mutexes | ||
| Option Sets Variable | Yes, timed_mutexes | ||
| Variable Name | timed_mutexes | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
This variable controls whether InnoDB
mutexes are timed. If this variable is set to 0 or
OFF (the default), mutex timing is
disabled. If the variable is set to 1 or
ON, mutex timing is enabled. With timing
enabled, the os_wait_times value in the
output from SHOW
ENGINE INNODB MUTEX indicates the amount of time (in
ms) spent in operating system waits. Otherwise, the value is
0.
timestamp =
{
timestamp_value |
DEFAULT}
Set the time for this client. This is used to get the original
timestamp if you use the binary log to restore rows.
timestamp_value should be a Unix
epoch timestamp, not a MySQL timestamp.
SET timestamp affects the value returned by
NOW() but not by
SYSDATE(). This means that
timestamp settings in the binary log have no effect on
invocations of SYSDATE(). The
server can be started with the
--sysdate-is-now option to
cause SYSDATE() to be an alias
for NOW(), in which case
SET timestamp affects both functions.
| Command-Line Format | --tmp_table_size=# | ||
| Option-File Format | tmp_table_size | ||
| Option Sets Variable | Yes, tmp_table_size | ||
| Variable Name | tmp_table_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | system dependent | ||
| Range | 1024-4294967295 | ||
The maximum size of internal in-memory temporary tables. (The
actual limit is determined as the minimum of
tmp_table_size and
max_heap_table_size.) If an
in-memory temporary table exceeds the limit, MySQL
automatically converts it to an on-disk
MyISAM table. Increase the value of
tmp_table_size (and
max_heap_table_size if
necessary) if you do many advanced GROUP BY
queries and you have lots of memory. This variable does not
apply to user-created MEMORY tables.
You can compare the number of internal on-disk temporary
tables created to the total number of internal temporary
tables created by comparing the values of the
Created_tmp_disk_tables and
Created_tmp_tables
variables.
See also Section 7.4.3.3, “How MySQL Uses Internal Temporary Tables”.
| Command-Line Format | --tmpdir=path | ||
-t | |||
| Option-File Format | tmpdir | ||
| Option Sets Variable | Yes, tmpdir | ||
| Variable Name | tmpdir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The directory used for temporary files and temporary tables.
This variable can be set to a list of several paths that are
used in round-robin fashion. Paths should be separated by
colon characters (“:”) on Unix
and semicolon characters (“;”)
on Windows.
The multiple-directory feature can be used to spread the load
between several physical disks. If the MySQL server is acting
as a replication slave, you should not set
tmpdir to point to a
directory on a memory-based file system or to a directory that
is cleared when the server host restarts. A replication slave
needs some of its temporary files to survive a machine restart
so that it can replicate temporary tables or
LOAD DATA
INFILE operations. If files in the temporary file
directory are lost when the server restarts, replication
fails. You can set the slave's temporary directory using the
slave_load_tmpdir variable.
In that case, the slave will not use the general
tmpdir value and you can set
tmpdir to a nonpermanent
location.
| Command-Line Format | --transaction_alloc_block_size=# | ||
| Option-File Format | transaction_alloc_block_size | ||
| Option Sets Variable | Yes, transaction_alloc_block_size | ||
| Variable Name | transaction_alloc_block_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 8192 | ||
| Range | 1024-4294967295 | ||
| Block Size | 1024 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 8192 | ||
| Range | 1024-18446744073709547520 | ||
| Block Size | 1024 | ||
The amount in bytes by which to increase a per-transaction
memory pool which needs memory. See the description of
transaction_prealloc_size.
| Command-Line Format | --transaction_prealloc_size=# | ||
| Option-File Format | transaction_prealloc_size | ||
| Option Sets Variable | Yes, transaction_prealloc_size | ||
| Variable Name | transaction_prealloc_size | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 4096 | ||
| Range | 1024-4294967295 | ||
| Block Size | 1024 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 4096 | ||
| Range | 1024-18446744073709547520 | ||
| Block Size | 1024 | ||
There is a per-transaction memory pool from which various
transaction-related allocations take memory. The initial size
of the pool in bytes is
transaction_prealloc_size.
For every allocation that cannot be satisfied from the pool
because it has insufficient memory available, the pool is
increased by
transaction_alloc_block_size
bytes. When the transaction ends, the pool is truncated to
transaction_prealloc_size
bytes.
By making
transaction_prealloc_size
sufficiently large to contain all statements within a single
transaction, you can avoid many malloc()
calls.
| Variable Name | tx_isolation | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | REPEATABLE-READ | ||
| Valid Values | READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE | ||
The default transaction isolation level. Defaults to
REPEATABLE-READ.
This variable is set by the
SET
TRANSACTION ISOLATION LEVEL statement. See
Section 12.3.6, “SET TRANSACTION Syntax”. If you set
tx_isolation directly to an
isolation level name that contains a space, the name should be
enclosed within quotation marks, with the space replaced by a
dash. For example:
SET tx_isolation = 'READ-COMMITTED';
| Variable Name | unique_checks | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 1 | ||
If set to 1 (the default), uniqueness checks for secondary
indexes in InnoDB tables are performed. If
set to 0, storage engines are permitted to assume that
duplicate keys are not present in input data. If you know for
certain that your data does not contain uniqueness violations,
you can set this to 0 to speed up large table imports to
InnoDB.
Note that setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still permitted to check for them and issue duplicate-key errors if it detects them.
| Command-Line Format | --updatable_views_with_limit=# | ||
| Option-File Format | updatable_views_with_limit | ||
| Option Sets Variable | Yes, updatable_views_with_limit | ||
| Variable Name | updatable_views_with_limit | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 1 | ||
This variable controls whether updates to a view can be made
when the view does not contain all columns of the primary key
defined in the underlying table, if the update statement
contains a LIMIT clause. (Such updates
often are generated by GUI tools.) An update is an
UPDATE or
DELETE statement. Primary key
here means a PRIMARY KEY, or a
UNIQUE index in which no column can contain
NULL.
The variable can have two values:
1 or YES: Issue a
warning only (not an error message). This is the default
value.
0 or NO: Prohibit
the update.
The version number for the server.
| Variable Name | version_comment | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The CMake configuration program has a
WITH_COMMENT option that permits
a comment to be specified when building MySQL. This variable
contains the value of that comment. See
Section 2.11.4, “MySQL Source-Configuration Options”.
| Variable Name | version_compile_machine | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The type of machine or architecture on which MySQL was built.
| Variable Name | version_compile_os | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The type of operating system on which MySQL was built.
| Command-Line Format | --wait_timeout=# | ||
| Option-File Format | wait_timeout | ||
| Option Sets Variable | Yes, wait_timeout | ||
| Variable Name | wait_timeout | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 28800 | ||
| Range | 1-31536000 | ||
| Permitted Values | |||
| Type (windows) | numeric | ||
| Default | 28800 | ||
| Range | 1-2147483 | ||
The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory.
On thread startup, the session
wait_timeout value is
initialized from the global
wait_timeout value or from
the global
interactive_timeout value,
depending on the type of client (as defined by the
CLIENT_INTERACTIVE connect option to
mysql_real_connect()). See
also interactive_timeout.
The number of errors, warnings, and notes that resulted from
the last statement that generated messages. This variable is
read only. See Section 12.4.5.41, “SHOW WARNINGS Syntax”.
The MySQL server maintains many system variables that indicate how
it is configured. Section 5.1.4, “Server System Variables”,
describes the meaning of these variables. Each system variable has
a default value. System variables can be set at server startup
using options on the command line or in an option file. Most of
them can be changed dynamically while the server is running by
means of the
SET
statement, which enables you to modify operation of the server
without having to stop and restart it. You can refer to system
variable values in expressions.
The server maintains two kinds of system variables. Global variables affect the overall operation of the server. Session variables affect its operation for individual client connections. A given system variable can have both a global and a session value. Global and session system variables are related as follows:
When the server starts, it initializes all global variables to their default values. These defaults can be changed by options specified on the command line or in an option file. (See Section 4.2.3, “Specifying Program Options”.)
The server also maintains a set of session variables for each
client that connects. The client's session variables are
initialized at connect time using the current values of the
corresponding global variables. For example, the client's SQL
mode is controlled by the session
sql_mode value, which is
initialized when the client connects to the value of the
global sql_mode value.
System variable values can be set globally at server startup by
using options on the command line or in an option file. When you
use a startup option to set a variable that takes a numeric value,
the value can be given with a suffix of K,
M, or G (either uppercase or
lowercase) to indicate a multiplier of 1024,
10242 or
10243; that is, units of kilobytes,
megabytes, or gigabytes, respectively. Thus, the following command
starts the server with a query cache size of 16 megabytes and a
maximum packet size of one gigabyte:
mysqld --query_cache_size=16M --max_allowed_packet=1G
Within an option file, those variables are set like this:
[mysqld] query_cache_size=16M max_allowed_packet=1G
The lettercase of suffix letters does not matter;
16M and 16m are equivalent,
as are 1G and 1g.
If you want to restrict the maximum value to which a system
variable can be set at runtime with the
SET
statement, you can specify this maximum by using an option of the
form
--maximum-
at server startup. For example, to prevent the value of
var_name=valuequery_cache_size from being
increased to more than 32MB at runtime, use the option
--maximum-query_cache_size=32M.
Many system variables are dynamic and can be changed while the
server runs by using the
SET
statement. For a list, see
Section 5.1.5.2, “Dynamic System Variables”. To change a system
variable with
SET, refer
to it as var_name, optionally preceded
by a modifier:
To indicate explicitly that a variable is a global variable,
precede its name by GLOBAL or
@@global.. The
SUPER privilege is required to
set global variables.
To indicate explicitly that a variable is a session variable,
precede its name by SESSION,
@@session., or @@.
Setting a session variable requires no special privilege, but
a client can change only its own session variables, not those
of any other client.
LOCAL and @@local. are
synonyms for SESSION and
@@session..
If no modifier is present,
SET
changes the session variable.
A SET
statement can contain multiple variable assignments, separated by
commas. If you set several system variables, the most recent
GLOBAL or SESSION modifier
in the statement is used for following variables that have no
modifier specified.
Examples:
SET sort_buffer_size=10000; SET @@local.sort_buffer_size=10000; SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000; SET @@sort_buffer_size=1000000; SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
The @@
syntax for system variables is supported for compatibility with
some other database systems.
var_name
If you change a session system variable, the value remains in effect until your session ends or until you change the variable to a different value. The change is not visible to other clients.
If you change a global system variable, the value is remembered
and used for new connections until the server restarts. (To make a
global system variable setting permanent, you should set it in an
option file.) The change is visible to any client that accesses
that global variable. However, the change affects the
corresponding session variable only for clients that connect after
the change. The global variable change does not affect the session
variable for any client that is currently connected (not even that
of the client that issues the
SET GLOBAL
statement).
To prevent incorrect usage, MySQL produces an error if you use
SET GLOBAL
with a variable that can only be used with
SET SESSION
or if you do not specify GLOBAL (or
@@global.) when setting a global variable.
To set a SESSION variable to the
GLOBAL value or a GLOBAL
value to the compiled-in MySQL default value, use the
DEFAULT keyword. For example, the following two
statements are identical in setting the session value of
max_join_size to the global
value:
SET max_join_size=DEFAULT; SET @@session.max_join_size=@@global.max_join_size;
Not all system variables can be set to DEFAULT.
In such cases, use of DEFAULT results in an
error.
You can refer to the values of specific global or sesson system
variables in expressions by using one of the
@@-modifiers. For example, you can retrieve
values in a SELECT statement like
this:
SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
When you refer to a system variable in an expression as
@@ (that is,
when you do not specify var_name@@global. or
@@session.), MySQL returns the session value if
it exists and the global value otherwise. (This differs from
SET @@, which always refers to
the session value.)
var_name =
value
Some variables displayed by SHOW VARIABLES
may not be available using SELECT
@@ syntax; an
var_nameUnknown system variable occurs. As a
workaround in such cases, you can use SHOW VARIABLES
LIKE '.
var_name'
Suffixes for specifying a value multiplier can be used when
setting a variable at server startup, but not to set the value
with SET at
runtime. On the other hand, with
SET you can
assign a variable's value using an expression, which is not true
when you set a variable at server startup. For example, the first
of the following lines is legal at server startup, but the second
is not:
shell>mysql --max_allowed_packet=16Mshell>mysql --max_allowed_packet=16*1024*1024
Conversely, the second of the following lines is legal at runtime, but the first is not:
mysql>SET GLOBAL max_allowed_packet=16M;mysql>SET GLOBAL max_allowed_packet=16*1024*1024;
Some system variables can be enabled with the
SET
statement by setting them to ON or
1, or disabled by setting them to
OFF or 0. However, to set
such a variable on the command line or in an option file, you
must set it to 1 or 0;
setting it to ON or OFF
will not work. For example, on the command line,
--delay_key_write=1 works but
--delay_key_write=ON does not.
To display system variable names and values, use the
SHOW VARIABLES statement:
mysql> SHOW VARIABLES;
+---------------------------------+-----------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /home/mysql/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /home/mysql/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
...
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
...
| version | 5.1.6-alpha-log |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | suse-linux |
| wait_timeout | 28800 |
+---------------------------------+-----------------------------------+
With a LIKE clause, the statement
displays only those variables that match the pattern. To obtain a
specific variable name, use a LIKE
clause as shown:
SHOW VARIABLES LIKE 'max_join_size'; SHOW SESSION VARIABLES LIKE 'max_join_size';
To get a list of variables whose name match a pattern, use the
“%” wildcard character in a
LIKE clause:
SHOW VARIABLES LIKE '%size%'; SHOW GLOBAL VARIABLES LIKE '%size%';
Wildcard characters can be used in any position within the pattern
to be matched. Strictly speaking, because
“_” is a wildcard that matches any
single character, you should escape it as
“\_” to match it literally. In
practice, this is rarely necessary.
For SHOW VARIABLES, if you specify
neither GLOBAL nor SESSION,
MySQL returns SESSION values.
The reason for requiring the GLOBAL keyword
when setting GLOBAL-only variables but not when
retrieving them is to prevent problems in the future. If we were
to remove a SESSION variable that has the same
name as a GLOBAL variable, a client with the
SUPER privilege might accidentally
change the GLOBAL variable rather than just the
SESSION variable for its own connection. If we
add a SESSION variable with the same name as a
GLOBAL variable, a client that intends to
change the GLOBAL variable might find only its
own SESSION variable changed.
A structured variable differs from a regular system variable in two respects:
Its value is a structure with components that specify server parameters considered to be closely related.
There might be several instances of a given type of structured variable. Each one has a different name and refers to a different resource maintained by the server.
MySQL 5.5 supports one structured variable type, which specifies parameters governing the operation of key caches. A key cache structured variable has these components:
This section describes the syntax for referring to structured
variables. Key cache variables are used for syntax examples, but
specific details about how key caches operate are found
elsewhere, in Section 7.9.2, “The MyISAM Key Cache”.
To refer to a component of a structured variable instance, you
can use a compound name in
instance_name.component_name format.
Examples:
hot_cache.key_buffer_size hot_cache.key_cache_block_size cold_cache.key_cache_block_size
For each structured system variable, an instance with the name
of default is always predefined. If you refer
to a component of a structured variable without any instance
name, the default instance is used. Thus,
default.key_buffer_size and
key_buffer_size both refer to
the same system variable.
Structured variable instances and components follow these naming rules:
For a given type of structured variable, each instance must
have a name that is unique within
variables of that type. However, instance names need not be
unique across structured variable
types. For example, each structured variable has an instance
named default, so
default is not unique across variable
types.
The names of the components of each structured variable type must be unique across all system variable names. If this were not true (that is, if two different types of structured variables could share component member names), it would not be clear which default structured variable to use for references to member names that are not qualified by an instance name.
If a structured variable instance name is not legal as an
unquoted identifier, refer to it as a quoted identifier
using backticks. For example, hot-cache
is not legal, but `hot-cache` is.
global, session, and
local are not legal instance names. This
avoids a conflict with notation such as
@@global.
for referring to nonstructured system variables.
var_name
Currently, the first two rules have no possibility of being violated because the only structured variable type is the one for key caches. These rules will assume greater significance if some other type of structured variable is created in the future.
With one exception, you can refer to structured variable components using compound names in any context where simple variable names can occur. For example, you can assign a value to a structured variable using a command-line option:
shell> mysqld --hot_cache.key_buffer_size=64K
In an option file, use this syntax:
[mysqld] hot_cache.key_buffer_size=64K
If you start the server with this option, it creates a key cache
named hot_cache with a size of 64KB in
addition to the default key cache that has a default size of
8MB.
Suppose that you start the server as follows:
shell>mysqld --key_buffer_size=256K \--extra_cache.key_buffer_size=128K \--extra_cache.key_cache_block_size=2048
In this case, the server sets the size of the default key cache
to 256KB. (You could also have written
--default.key_buffer_size=256K.) In addition,
the server creates a second key cache named
extra_cache that has a size of 128KB, with
the size of block buffers for caching table index blocks set to
2048 bytes.
The following example starts the server with three different key caches having sizes in a 3:1:1 ratio:
shell>mysqld --key_buffer_size=6M \--hot_cache.key_buffer_size=2M \--cold_cache.key_buffer_size=2M
Structured variable values may be set and retrieved at runtime
as well. For example, to set a key cache named
hot_cache to a size of 10MB, use either of
these statements:
mysql>SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;mysql>SET @@global.hot_cache.key_buffer_size = 10*1024*1024;
To retrieve the cache size, do this:
mysql> SELECT @@global.hot_cache.key_buffer_size;
However, the following statement does not work. The variable is
not interpreted as a compound name, but as a simple string for a
LIKE pattern-matching operation:
mysql> SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size';
This is the exception to being able to use structured variable names anywhere a simple variable name may occur.
Many server system variables are dynamic and can be set at
runtime using SET
GLOBAL or
SET
SESSION. You can also obtain their values using
SELECT. See
Section 5.1.5, “Using System Variables”.
The following table shows the full list of all dynamic system
variables. The last column indicates for each variable whether
GLOBAL or SESSION (or
both) apply. The table also lists session options that can be
set with the
SET
statement. Section 5.1.4, “Server System Variables”, discusses
these options.
Variables that have a type of “string” take a
string value. Variables that have a type of
“numeric” take a numeric value. Variables that have
a type of “boolean” can be set to 0, 1,
ON or OFF. (If you set
them on the command line or in an option file, use the numeric
values.) Variables that are marked as “enumeration”
normally should be set to one of the available values for the
variable, but can also be set to the number that corresponds to
the desired enumeration value. For enumerated system variables,
the first enumeration value corresponds to 0. This differs from
ENUM columns, for which the first
enumeration value corresponds to 1.
Table 5.3. Dynamic Variable Summary
| Variable Name | Variable Type | Variable Scope |
|---|---|---|
auto_increment_increment | numeric | GLOBAL | SESSION |
auto_increment_offset | numeric | GLOBAL | SESSION |
autocommit | boolean | GLOBAL | SESSION |
automatic_sp_privileges | boolean | GLOBAL |
big_tables | boolean | GLOBAL | SESSION |
binlog_cache_size | numeric | GLOBAL |
binlog_direct_non_transactional_updates | boolean | GLOBAL | SESSION |
binlog_format | enumeration | GLOBAL | SESSION |
bulk_insert_buffer_size | numeric | GLOBAL | SESSION |
character_set_client | string | GLOBAL | SESSION |
character_set_connection | string | GLOBAL | SESSION |
character_set_database | string | GLOBAL | SESSION |
character_set_filesystem | string | GLOBAL | SESSION |
character_set_results | string | GLOBAL | SESSION |
character_set_server | string | GLOBAL | SESSION |
collation_connection | string | GLOBAL | SESSION |
collation_database | string | GLOBAL | SESSION |
collation_server | string | GLOBAL | SESSION |
completion_type | numeric | GLOBAL | SESSION |
concurrent_insert | boolean | GLOBAL |
connect_timeout | numeric | GLOBAL |
debug | string | GLOBAL | SESSION |
debug_sync | string | SESSION |
default_storage_engine | enumeration | GLOBAL | SESSION |
default_week_format | numeric | GLOBAL | SESSION |
delay_key_write | enumeration | GLOBAL |
delayed_insert_limit | numeric | GLOBAL |
delayed_insert_timeout | numeric | GLOBAL |
delayed_queue_size | numeric | GLOBAL |
div_precision_increment | numeric | GLOBAL | SESSION |
engine_condition_pushdown | boolean | GLOBAL | SESSION |
event_scheduler | enumeration | GLOBAL |
expire_logs_days | numeric | GLOBAL |
flush | boolean | GLOBAL |
flush_time | numeric | GLOBAL |
foreign_key_checks | boolean | GLOBAL | SESSION |
ft_boolean_syntax | string | GLOBAL |
general_log | boolean | GLOBAL |
general_log_file | filename | GLOBAL |
group_concat_max_len | numeric | GLOBAL | SESSION |
identity | numeric | SESSION |
init_connect | string | GLOBAL |
init_slave | string | GLOBAL |
innodb_adaptive_flushing | boolean | GLOBAL |
innodb_adaptive_hash_index | boolean | GLOBAL |
innodb_autoextend_increment | numeric | GLOBAL |
innodb_change_buffering | enumeration | GLOBAL |
innodb_commit_concurrency | numeric | GLOBAL |
innodb_concurrency_tickets | numeric | GLOBAL |
innodb_fast_shutdown | boolean | GLOBAL |
innodb_file_format | string | GLOBAL |
innodb_file_format_max | string | GLOBAL |
innodb_file_per_table | boolean | GLOBAL |
innodb_flush_log_at_trx_commit | numeric | GLOBAL |
innodb_io_capacity | numeric | GLOBAL |
innodb_lock_wait_timeout | numeric | GLOBAL | SESSION |
innodb_max_dirty_pages_pct | numeric | GLOBAL |
innodb_max_purge_lag | numeric | GLOBAL |
innodb_old_blocks_pct | numeric | GLOBAL |
innodb_old_blocks_time | numeric | GLOBAL |
innodb_read_ahead_threshold | numeric | GLOBAL |
innodb_replication_delay | numeric | GLOBAL |
innodb_spin_wait_delay | numeric | GLOBAL |
innodb_stats_on_metadata | boolean | GLOBAL |
innodb_stats_sample_pages | numeric | GLOBAL |
innodb_strict_mode | boolean | GLOBAL | SESSION |
innodb_support_xa | boolean | GLOBAL | SESSION |
innodb_sync_spin_loops | numeric | GLOBAL |
innodb_table_locks | boolean | GLOBAL | SESSION |
innodb_thread_concurrency | numeric | GLOBAL |
innodb_thread_sleep_delay | numeric | GLOBAL |
insert_id | numeric | SESSION |
interactive_timeout | numeric | GLOBAL | SESSION |
join_buffer_size | numeric | GLOBAL | SESSION |
keep_files_on_create | boolean | GLOBAL | SESSION |
key_buffer_size | numeric | GLOBAL |
key_cache_age_threshold | numeric | GLOBAL |
key_cache_block_size | numeric | GLOBAL |
key_cache_division_limit | numeric | GLOBAL |
last_insert_id | numeric | SESSION |
lc_messages | string | GLOBAL | SESSION |
lc_time_names | string | GLOBAL | SESSION |
local_infile | boolean | GLOBAL |
lock_wait_timeout | numeric | GLOBAL | SESSION |
log | string | GLOBAL |
log_bin_trust_function_creators | boolean | GLOBAL |
log_bin_trust_routine_creators | boolean | GLOBAL |
log_output | set | GLOBAL |
log_queries_not_using_indexes | boolean | GLOBAL |
log_slow_queries | boolean | GLOBAL |
log_warnings | numeric | GLOBAL | SESSION |
long_query_time | numeric | GLOBAL | SESSION |
low_priority_updates | boolean | GLOBAL | SESSION |
max_allowed_packet | numeric | GLOBAL |
max_binlog_cache_size | numeric | GLOBAL |
max_binlog_size | numeric | GLOBAL |
max_connect_errors | numeric | GLOBAL |
max_connections | numeric | GLOBAL |
max_delayed_threads | numeric | GLOBAL | SESSION |
max_error_count | numeric | GLOBAL | SESSION |
max_heap_table_size | numeric | GLOBAL | SESSION |
max_insert_delayed_threads | numeric | GLOBAL | SESSION |
max_join_size | numeric | GLOBAL | SESSION |
max_length_for_sort_data | numeric | GLOBAL | SESSION |
max_prepared_stmt_count | numeric | GLOBAL |
max_relay_log_size | numeric | GLOBAL |
max_seeks_for_key | numeric | GLOBAL | SESSION |
max_sort_length | numeric | GLOBAL | SESSION |
max_sp_recursion_depth | numeric | GLOBAL | SESSION |
max_tmp_tables | numeric | GLOBAL | SESSION |
max_user_connections | numeric | GLOBAL | SESSION |
max_write_lock_count | numeric | GLOBAL |
min_examined_row_limit | numeric | GLOBAL | SESSION |
myisam_data_pointer_size | numeric | GLOBAL |
myisam_max_sort_file_size | numeric | GLOBAL |
myisam_repair_threads | numeric | GLOBAL | SESSION |
myisam_sort_buffer_size | numeric | GLOBAL | SESSION |
myisam_stats_method | enumeration | GLOBAL | SESSION |
myisam_use_mmap | boolean | GLOBAL |
ndb_autoincrement_prefetch_sz | numeric | GLOBAL | SESSION |
net_buffer_length | numeric | GLOBAL | SESSION |
net_read_timeout | numeric | GLOBAL | SESSION |
net_retry_count | numeric | GLOBAL | SESSION |
net_write_timeout | numeric | GLOBAL | SESSION |
new | boolean | GLOBAL | SESSION |
old_alter_table | boolean | GLOBAL | SESSION |
old_passwords | boolean | GLOBAL | SESSION |
optimizer_prune_level | boolean | GLOBAL | SESSION |
optimizer_search_depth | numeric | GLOBAL | SESSION |
optimizer_switch | set | GLOBAL | SESSION |
preload_buffer_size | numeric | GLOBAL | SESSION |
profiling | boolean | GLOBAL | SESSION |
profiling_history_size | numeric | GLOBAL | SESSION |
pseudo_thread_id | numeric | SESSION |
query_alloc_block_size | numeric | GLOBAL | SESSION |
query_cache_limit | numeric | GLOBAL |
query_cache_min_res_unit | numeric | GLOBAL |
query_cache_size | numeric | GLOBAL |
query_cache_type | enumeration | GLOBAL | SESSION |
query_cache_wlock_invalidate | boolean | GLOBAL | SESSION |
query_prealloc_size | numeric | GLOBAL | SESSION |
rand_seed1 | numeric | SESSION |
rand_seed2 | numeric | SESSION |
range_alloc_block_size | numeric | GLOBAL | SESSION |
read_buffer_size | numeric | GLOBAL | SESSION |
read_only | numeric | GLOBAL |
read_rnd_buffer_size | numeric | GLOBAL | SESSION |
relay_log_purge | boolean | GLOBAL |
relay_log_recovery | boolean | GLOBAL |
rpl_recovery_rank | numeric | GLOBAL |
rpl_semi_sync_master_enabled | boolean | GLOBAL |
rpl_semi_sync_master_timeout | numeric | GLOBAL |
rpl_semi_sync_master_trace_level | numeric | GLOBAL |
rpl_semi_sync_master_wait_no_slave | boolean | GLOBAL |
rpl_semi_sync_slave_enabled | boolean | GLOBAL |
rpl_semi_sync_slave_trace_level | numeric | GLOBAL |
safe_show_database | boolean | GLOBAL |
secure_auth | boolean | GLOBAL |
server_id | numeric | GLOBAL |
slave_compressed_protocol | boolean | GLOBAL |
slave_exec_mode | enumeration | GLOBAL |
slave_net_timeout | numeric | GLOBAL |
slave_transaction_retries | numeric | GLOBAL |
slow_launch_time | numeric | GLOBAL |
slow_query_log | boolean | GLOBAL |
slow_query_log_file | filename | GLOBAL |
sort_buffer_size | numeric | GLOBAL | SESSION |
sql_auto_is_null | boolean | GLOBAL | SESSION |
sql_big_selects | boolean | GLOBAL | SESSION |
sql_big_tables | boolean | SESSION |
sql_buffer_result | boolean | SESSION |
sql_log_bin | boolean | GLOBAL | SESSION |
sql_log_off | boolean | GLOBAL | SESSION |
sql_log_update | boolean | SESSION |
sql_low_priority_updates | boolean | GLOBAL | SESSION |
sql_max_join_size | numeric | GLOBAL | SESSION |
sql_mode | set | GLOBAL | SESSION |
sql_notes | boolean | GLOBAL | SESSION |
sql_quote_show_create | boolean | GLOBAL | SESSION |
sql_safe_updates | boolean | GLOBAL | SESSION |
sql_select_limit | numeric | GLOBAL | SESSION |
sql_slave_skip_counter | numeric | GLOBAL |
sql_warnings | boolean | GLOBAL | SESSION |
storage_engine | enumeration | GLOBAL | SESSION |
sync_binlog | numeric | GLOBAL |
sync_frm | boolean | GLOBAL |
sync_master_info | numeric | GLOBAL |
sync_relay_log | numeric | GLOBAL |
sync_relay_log_info | numeric | GLOBAL |
table_definition_cache | numeric | GLOBAL |
table_lock_wait_timeout | numeric | GLOBAL |
table_open_cache | numeric | GLOBAL |
table_type | enumeration | GLOBAL | SESSION |
thread_cache_size | numeric | GLOBAL |
time_zone | string | GLOBAL | SESSION |
timed_mutexes | boolean | GLOBAL |
timestamp | numeric | SESSION |
tmp_table_size | numeric | GLOBAL | SESSION |
transaction_alloc_block_size | numeric | GLOBAL | SESSION |
transaction_prealloc_size | numeric | GLOBAL | SESSION |
tx_isolation | enumeration | GLOBAL | SESSION |
unique_checks | boolean | GLOBAL | SESSION |
updatable_views_with_limit | boolean | GLOBAL | SESSION |
wait_timeout | numeric | GLOBAL | SESSION |
The server maintains many status variables that provide
information about its operation. You can view these variables and
their values by using the SHOW [GLOBAL | SESSION]
STATUS statement (see Section 12.4.5.36, “SHOW STATUS Syntax”).
The optional GLOBAL keyword aggregates the
values over all connections, and SESSION shows
the values for the current connection.
mysql> SHOW GLOBAL STATUS;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |
...
| Connections | 30023 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 3 |
| Created_tmp_tables | 2 |
...
| Threads_created | 217 |
| Threads_running | 88 |
| Uptime | 1389872 |
+-----------------------------------+------------+
The following table lists all available server status variables:
Table 5.4. Status Variable Summary
| Variable Name | Variable Type | Variable Scope |
|---|---|---|
Aborted_clients | numeric | GLOBAL |
Aborted_connects | numeric | GLOBAL |
Binlog_cache_disk_use | numeric | GLOBAL |
Binlog_cache_use | numeric | GLOBAL |
Bytes_received | numeric | GLOBAL | SESSION |
Bytes_sent | numeric | GLOBAL | SESSION |
Com_admin_commands | numeric | GLOBAL | SESSION |
Com_alter_db | numeric | GLOBAL | SESSION |
Com_alter_db_upgrade | numeric | GLOBAL | SESSION |
Com_alter_event | numeric | GLOBAL | SESSION |
Com_alter_function | numeric | GLOBAL | SESSION |
Com_alter_procedure | numeric | GLOBAL | SESSION |
Com_alter_server | numeric | GLOBAL | SESSION |
Com_alter_table | numeric | GLOBAL | SESSION |
Com_alter_tablespace | numeric | GLOBAL | SESSION |
Com_analyze | numeric | GLOBAL | SESSION |
Com_assign_to_keycache | numeric | GLOBAL | SESSION |
Com_backup_table | numeric | GLOBAL | SESSION |
Com_begin | numeric | GLOBAL | SESSION |
Com_binlog | numeric | GLOBAL | SESSION |
Com_call_procedure | numeric | GLOBAL | SESSION |
Com_change_db | numeric | GLOBAL | SESSION |
Com_change_master | numeric | GLOBAL | SESSION |
Com_check | numeric | GLOBAL | SESSION |
Com_checksum | numeric | GLOBAL | SESSION |
Com_commit | numeric | GLOBAL | SESSION |
Com_create_db | numeric | GLOBAL | SESSION |
Com_create_event | numeric | GLOBAL | SESSION |
Com_create_function | numeric | GLOBAL | SESSION |
Com_create_index | numeric | GLOBAL | SESSION |
Com_create_procedure | numeric | GLOBAL | SESSION |
Com_create_server | numeric | GLOBAL | SESSION |
Com_create_table | numeric | GLOBAL | SESSION |
Com_create_trigger | numeric | GLOBAL | SESSION |
Com_create_udf | numeric | GLOBAL | SESSION |
Com_create_user | numeric | GLOBAL | SESSION |
Com_create_view | numeric | GLOBAL | SESSION |
Com_dealloc_sql | numeric | GLOBAL | SESSION |
Com_delete | numeric | GLOBAL | SESSION |
Com_delete_multi | numeric | GLOBAL | SESSION |
Com_do | numeric | GLOBAL | SESSION |
Com_drop_db | numeric | GLOBAL | SESSION |
Com_drop_event | numeric | GLOBAL | SESSION |
Com_drop_function | numeric | GLOBAL | SESSION |
Com_drop_index | numeric | GLOBAL | SESSION |
Com_drop_procedure | numeric | GLOBAL | SESSION |
Com_drop_server | numeric | GLOBAL | SESSION |
Com_drop_table | numeric | GLOBAL | SESSION |
Com_drop_trigger | numeric | GLOBAL | SESSION |
Com_drop_user | numeric | GLOBAL | SESSION |
Com_drop_view | numeric | GLOBAL | SESSION |
Com_empty_query | numeric | GLOBAL | SESSION |
Com_execute_sql | numeric | GLOBAL | SESSION |
Com_flush | numeric | GLOBAL | SESSION |
Com_grant | numeric | GLOBAL | SESSION |
Com_ha_close | numeric | GLOBAL | SESSION |
Com_ha_open | numeric | GLOBAL | SESSION |
Com_ha_read | numeric | GLOBAL | SESSION |
Com_help | numeric | GLOBAL | SESSION |
Com_insert | numeric | GLOBAL | SESSION |
Com_insert_select | numeric | GLOBAL | SESSION |
Com_install_plugin | numeric | GLOBAL | SESSION |
Com_kill | numeric | GLOBAL | SESSION |
Com_load | numeric | GLOBAL | SESSION |
Com_lock_tables | numeric | GLOBAL | SESSION |
Com_optimize | numeric | GLOBAL | SESSION |
Com_preload_keys | numeric | GLOBAL | SESSION |
Com_prepare_sql | numeric | GLOBAL | SESSION |
Com_purge | numeric | GLOBAL | SESSION |
Com_purge_before_date | numeric | GLOBAL | SESSION |
Com_release_savepoint | numeric | GLOBAL | SESSION |
Com_rename_table | numeric | GLOBAL | SESSION |
Com_rename_user | numeric | GLOBAL | SESSION |
Com_repair | numeric | GLOBAL | SESSION |
Com_replace | numeric | GLOBAL | SESSION |
Com_replace_select | numeric | GLOBAL | SESSION |
Com_reset | numeric | GLOBAL | SESSION |
Com_resignal | numeric | GLOBAL | SESSION |
Com_restore_table | numeric | GLOBAL | SESSION |
Com_revoke | numeric | GLOBAL | SESSION |
Com_revoke_all | numeric | GLOBAL | SESSION |
Com_rollback | numeric | GLOBAL | SESSION |
Com_rollback_to_savepoint | numeric | GLOBAL | SESSION |
Com_savepoint | numeric | GLOBAL | SESSION |
Com_select | numeric | GLOBAL | SESSION |
Com_set_option | numeric | GLOBAL | SESSION |
Com_show_authors | numeric | GLOBAL | SESSION |
Com_show_binlog_events | numeric | GLOBAL | SESSION |
Com_show_binlogs | numeric | GLOBAL | SESSION |
Com_show_charsets | numeric | GLOBAL | SESSION |
Com_show_collations | <