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.5.5.13, “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. mysqld 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”.
MySQL Enterprise For expert advice on setting command options, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
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.3, “Security-Related mysqld Options”.
SSL-related options: See Section 5.5.7.3, “SSL Command Options”.
Binary log control options: See Section 16.1.2.4, “Binary Log Options and Variables”.
Replication-related options: See Section 16.1.2, “Replication and Binary Logging Options and Variables”.
Options specific to particular storage engines: See
Section 13.1.1, “MyISAM Startup Options”, Section 13.5.3, “BDB Startup Options”,
Section 13.2.3, “InnoDB Startup Options and System Variables”, and
Section 17.4.2, “mysqld Command Options for MySQL Cluster”.
You can also set the values of server system variables by using variable names as options, as described at the end of this section.
--help, -?
| Command Line Format | -? |
| Config File Format | help |
Display a short help message and exit. Use both the
--verbose and
--help options to see the full
message.
| Version Introduced | 5.0.3 | ||||
| Command Line Format | --allow-suspicious-udfs | ||||
| Config File Format | allow-suspicious-udfs | ||||
| Value Set |
|
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. This option
was added in version 5.0.3. See
Section 21.2.2.6, “User-Defined Function Security Precautions”.
| Command Line Format | --ansi |
| Config 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.7.3, “Running MySQL in ANSI Mode”, and
Section 5.1.7, “Server SQL Modes”.
| Command Line Format | --basedir=name | ||
| Config File Format | basedir | ||
| Option Sets Variable | Yes, basedir | ||
| Variable Name | basedir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The path to the MySQL installation directory. All paths are usually resolved relative to this directory.
| Command Line Format | --big-tables | ||
| Config File Format | big-tables | ||
| Option Sets Variable | Yes, big_tables | ||
| Variable Name | big-tables | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
Allow 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 | ||||||
| Config File Format | bind-address | ||||||
| Value Set |
|
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.
| Command Line Format | --bootstrap |
| Config 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
option. See Section 2.16.2, “Typical configure Options”.
| Command Line Format | --character-sets-dir=name | ||
| Config File Format | character-sets-dir | ||
| Option Sets Variable | Yes, character_sets_dir | ||
| Variable Name | character-sets-dir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.
--character-set-client-handshake
| Command Line Format | --character-set-client-handshake | ||||
| Config File Format | character-set-client-handshake | ||||
| Value Set |
|
Don't 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
| Version Introduced | 5.0.19 | ||
| Command Line Format | --character-set-filesystem=name | ||
| Config File Format | character-set-filesystem | ||
| Option Sets Variable | Yes, character_set_filesystem | ||
| Variable Name | character_set_filesystem | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
The file system character set. This option sets the
character_set_filesystem
system variable. It was added in MySQL 5.0.19.
--character-set-server=,
charset_name-C
charset_name
| Command Line Format | --character-set-server | ||
| Config File Format | character-set-server | ||
| Option Sets Variable | Yes, character_set_server | ||
| Variable Name | character_set_server | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
Use charset_name as the default
server character set. See
Section 9.2, “The Character Set Used for Data and Sorting”. 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 | ||
| Config File Format | chroot | ||
| Value Set |
|
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 | ||
| Config File Format | collation-server | ||
| Option Sets Variable | Yes, collation_server | ||
| Variable Name | collation_server | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
Use collation_name as the default
server collation. See Section 9.2, “The Character Set Used for Data and Sorting”.
| Command Line Format | --console |
| Config 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 | ||||
| Config File Format | core-file | ||||
| Value Set |
|
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=name | ||
| Config File Format | datadir | ||
| Option Sets Variable | Yes, datadir | ||
| Variable Name | datadir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The path to the data directory.
--debug[=,
debug_options]-# [
debug_options]
| Command Line Format | --debug[=debug_options] | ||||
| Config File Format | debug | ||||
| Variable Name | debug | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
If MySQL is configured with
--with-debug, 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.
As of MySQL 5.0.25, using
--with-debug 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.
--default-character-set=
(DEPRECATED)
charset_name
| Version Deprecated | 5.0 | ||
| Command Line Format | --default-character-set=name | ||
| Config File Format | default-character-set | ||
| Deprecated | 5.0 | ||
| Value Set |
|
Use charset_name as the default
character set. This option is deprecated in favor of
--character-set-server. See
Section 9.2, “The Character Set Used for Data and Sorting”.
--default-collation=
collation_name
| Command Line Format | --default-collation=name | ||
| Variable Name | default-collation | ||
| Variable Scope | |||
| Dynamic Variable | No | ||
| Deprecated | 4.1.3 | ||
| Value Set |
|
Use collation_name as the default
collation. This option is deprecated in favor of
--collation-server. See
Section 9.2, “The Character Set Used for Data and Sorting”.
| Command Line Format | --default-storage-engine=name |
| Config File Format | default-storage-engine |
Set the default storage engine (table type) for tables. See Chapter 13, Storage Engines.
| Version Deprecated | 5.0 | ||
| Command Line Format | --default-table-type=name | ||
| Config File Format | default-table-type | ||
| Deprecated | 5.0, by default-storage-engine | ||
| Value Set |
|
This option is a deprecated synonym for
--default-storage-engine.
| Command Line Format | --default-time-zone=name | ||
| Config File Format | default-time-zone | ||
| Value Set |
|
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] | ||||||
| Config File Format | delay-key-write | ||||||
| Option Sets Variable | Yes, delay_key_write | ||||||
| Variable Name | delay-key-write | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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.5.3, “Tuning Server Parameters”, and
Section 13.1.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=name |
| Config File Format | des-key-file |
Read the default DES keys from this file. These keys are used
by the DES_ENCRYPT() and
DES_DECRYPT() functions.
| Command Line Format | --named_pipe |
| Config File Format | enable-named-pipe |
| Platform Specific | windows |
Enable support for named pipes. This option can be used only with the mysqld-nt and mysqld-debug servers that support named-pipe connections.
| Command Line Format | --enable-pstack | ||||
| Config File Format | enable-pstack | ||||
| Value Set |
|
Print a symbolic stack trace on failure.
--engine-condition-pushdown={ON|OFF}
| Version Introduced | 5.0.3 | ||||
| Command Line Format | --engine-condition-pushdown | ||||
| Config File Format | engine-condition-pushdown | ||||
| Option Sets Variable | Yes, engine_condition_pushdown | ||||
| Variable Name | engine_condition_pushdown | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Value Set (>= 5.0.3) |
|
Sets the
engine_condition_pushdown
system variable. For more information, see
Section 7.2.7, “Condition Pushdown Optimization”.
This variable was added in MySQL 5.0.3.
--exit-info[=,
flags]-T [
flags]
| Command Line Format | --exit-info[=flags] | ||
| Config File Format | exit-info | ||
| Value Set |
|
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 | ||||
| Config File Format | external-locking | ||||
| Option Sets Variable | Yes, skip_external_locking | ||||
| Disabled by | skip-external-locking | ||||
| Value Set |
|
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. This option previously was named
--enable-locking.
For more information about external locking, including conditions under which it can and cannot be used, see Section 7.3.4, “External Locking”.
| Command Line Format | --flush | ||||
| Config File Format | flush | ||||
| Variable Name | flush | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
Flush (synchronize) all changes to disk after each SQL statement. Normally, MySQL does a write of all changes to disk only after each SQL statement and lets the operating system handle the synchronizing to disk. See Section B.1.4.2, “What to Do If MySQL Keeps Crashing”.
| Command Line Format | --gdb | ||||
| Config File Format | gdb | ||||
| Value Set |
|
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 | --init-file=name | ||
| Config File Format | init-file | ||
| Option Sets Variable | Yes, init_file | ||
| Variable Name | init_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
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
option. See Section 2.16.2, “Typical configure Options”.
| Version Removed | 5.0.3 | ||
| Version Deprecated | 5.0.3 | ||
| Command Line Format | --innodb_safe_binlog | ||
| Config File Format | innodb_safe_binlog | ||
| Deprecated | 5.0.3 | ||
| Value Set |
|
If this option is given, then after a crash recovery by
InnoDB, mysqld truncates
the binary log after the last not-rolled-back transaction in
the log. The option also causes InnoDB to
print an error if the binary log is smaller or shorter than it
should be. See Section 5.2.3, “The Binary Log”. This option was
removed in MySQL 5.0.3, having been made obsolete by the
introduction of XA transaction support.
--innodb-
xxx
The InnoDB options are listed in
Section 13.2.3, “InnoDB Startup Options and System Variables”.
--language=
lang_name,
-L lang_name
| Command Line Format | --language=name | ||||
| Config File Format | language | ||||
| Option Sets Variable | Yes, language | ||||
| Variable Name | language | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
Return client error messages in the given language.
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.3, “Setting the Error Message Language”.
| Version Introduced | 5.0.3 | ||||
| Command Line Format | --large-pages | ||||
| Config File Format | large-pages | ||||
| Option Sets Variable | Yes, large_pages | ||||
| Variable Name | large_pages | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Platform Specific | linux | ||||
| Value Set |
|
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.
Currently, MySQL supports only the Linux implementation of large page support (which is called HugeTLB in Linux). See Section 7.5.9, “Enabling Large Page Support”.
--large-pages is disabled by
default. It was added in MySQL 5.0.3.
--log[=,
file_name]-l [
file_name]
| Command Line Format | --log[=name] | ||||
| Config File Format | log | ||||
| Option Sets Variable | Yes, log | ||||
| Variable Name | log | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Deprecated | 5.1.29, by general-log | ||||
| Value Set |
|
Log connections and SQL statements received from clients to
this file. See Section 5.2.2, “The General Query Log”. If you omit the
file name, MySQL uses
host_name.log
| Command Line Format | --log-error[=name] | ||
| Config File Format | log-error | ||
| Option Sets Variable | Yes, log_error | ||
| Variable Name | log_error | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
Log errors and startup messages to this file. See
Section 5.2.1, “The Error Log”. If you omit the file name, MySQL
uses
host_name.err.err.
| Command Line Format | --log-isam[=name] | ||
| Config File Format | log-isam | ||
| Value Set |
|
Log all MyISAM changes to this file (used
only when debugging MyISAM).
--log-long-format
(DEPRECATED)
| Command Line Format | --log-long-format |
| Config File Format | log-long-format |
| Deprecated | 4.1 |
Log extra information to the update log, binary update 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-queries-not-using-indexes
| Command Line Format | --log-queries-not-using-indexes | ||
| Config 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 | ||
| Deprecated | 5.1.29, by slow-query-log | ||
| Value Set |
|
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.4, “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 | ||||
| Config File Format | log-short-format | ||||
| Value Set |
|
Originally intended to log less information to the update log, binary log and slow query log, if they have been activated. However, this option is not operational.
| Command Line Format | --log-slow-admin-statements | ||||
| Config File Format | log-slow-admin-statements | ||||
| Value Set |
|
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] | ||
| Config File Format | log-slow-queries | ||
| Option Sets Variable | Yes, log_slow_queries | ||
| Variable Name | log_slow_queries | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
Log all queries that have taken more than
long_query_time seconds to
execute to this file. See Section 5.2.4, “The Slow Query Log”.
See the descriptions of the
--log-long-format and
--log-short-format options for
details.
| Version Introduced | 5.0.3 | ||||
| Command Line Format | --log-tc=name | ||||
| Config File Format | log-tc | ||||
| Value Set |
|
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. Added in MySQL 5.0.3.
| Version Introduced | 5.0.3 | ||||||||
| Command Line Format | --log-tc-size=# | ||||||||
| Config File Format | log-tc-size | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
The size in bytes of the memory-mapped transaction coordinator log. The default size is 24KB. Added in MySQL 5.0.3.
--log-warnings[=,
level]-W [
level]
| Command Line Format | --log-warnings[=#] | ||||||||
| Config 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 | ||||||||
| Value Set |
|
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. See Section B.1.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.
| Command Line Format | --low-priority-updates | ||||
| Config File Format | low-priority-updates | ||||
| Option Sets Variable | Yes, low_priority_updates | ||||
| Variable Name | low_priority_updates | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
Give table-modifying operations
(INSERT,
REPLACE,
DELETE,
UPDATE) lower priority than
selects. This can also be done via {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.3.2, “Table Locking Issues”.
| Command Line Format | --memlock | ||||
| Config File Format | memlock | ||||
| Variable Name | locked_in_memory | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
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.5, “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=# | ||||||
| Config File Format | myisam-block-size | ||||||
| Value Set |
|
The block size to be used for MyISAM index
pages.
--myisam-recover[=
option[,option]...]]
| Command Line Format | --myisam-recover[=name] | ||||||
| Config File Format | myisam-recover | ||||||
| Value Set |
|
Set the MyISAM storage engine recovery
mode. The option value is any combination of the values of
DEFAULT, 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
wasn't 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. |
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 | Don't check the rows in the table if there aren't 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.
| Command Line Format | --old_passwords | ||||
| Config File Format | old-passwords | ||||
| Option Sets Variable | Yes, old_passwords | ||||
| Variable Name | old_passwords | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
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.5.6.3, “Password Hashing in MySQL”.
| Version Introduced | 5.0.3 | ||||
| Command Line Format | --old-style-user-limits | ||||
| Config File Format | old-style-user-limits | ||||
| Value Set |
|
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, “Limiting Account Resources”. This option was added in
MySQL 5.0.3.
| Command Line Format | --one-thread |
| Config 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.
| Command Line Format | --open-files-limit=# | ||||||
| Config File Format | open-files-limit | ||||||
| Option Sets Variable | Yes, open_files_limit | ||||||
| Variable Name | open_files_limit | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | No | ||||||
| Value Set |
|
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_cache to estimate
whether more descriptors will be needed.
| Command Line Format | --pid-file=name | ||
| Config File Format | pid-file | ||
| Option Sets Variable | Yes, pid_file | ||
| Variable Name | pid_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The path name of the process ID file. This file is used by other programs such as mysqld_safe to determine the server's process ID.
--port=,
port_num-P
port_num
| Command Line Format | --port=# | ||||
| Config File Format | port | ||||
| Option Sets Variable | Yes, port | ||||
| Variable Name | port | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
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.
| Version Introduced | 5.0.19 | ||||
| Command Line Format | --port-open-timeout=# | ||||
| Config File Format | port-open-timeout | ||||
| Value Set |
|
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. This option was added in MySQL 5.0.19.
| Version Deprecated | 5.0 |
| Command Line Format | --safe-mode |
| Config File Format | safe-mode |
| Deprecated | 5.0 |
Skip some optimization stages.
--safe-show-database
(DEPRECATED)
| Command Line Format | --safe-show-database | ||
| Config File Format | safe-show-database | ||
| Variable Name | safe_show_database | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Deprecated | 4.0.2 | ||
| Value Set |
|
| Command Line Format | --safe-user-create | ||||
| Config File Format | safe-user-create | ||||
| Value Set |
|
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 | ||||
| Config File Format | secure-auth | ||||
| Option Sets Variable | Yes, secure_auth | ||||
| Variable Name | secure_auth | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
Disallow authentication by clients that attempt to use accounts that have old (pre-4.1) passwords.
| Version Introduced | 5.0.38 | ||
| Command Line Format | --secure-file-priv | ||
| Config File Format | secure-file-priv | ||
| Option Sets Variable | Yes, secure_file_priv | ||
| Variable Name | secure_file_priv | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
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.
This option was added in MySQL 5.0.38.
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.
Disable the BDB storage engine. This saves
memory and might speed up some operations. Do not use this
option if you require BDB tables.
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.3.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.3.4, “External Locking”.
External locking has been disabled by default since MySQL 4.0.
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 user-defined functions
(UDFs).
This option is unavailable if MySQL was configured with the
--disable-grant-options
option. See Section 2.16.2, “Typical configure 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.5.11, “How MySQL Uses DNS”.
Disable the InnoDB storage engine. This
saves memory and disk space and might speed up some
operations. Do not use this option if you require
InnoDB tables.
Disable the MERGE storage engine. This
option was added in MySQL 5.0.24. It can be used if the
following behavior is undesirable: If a user has access to
MyISAM table t,
that user can create a MERGE table
m that accesses
t. However, if the user's
privileges on t are subsequently
revoked, the user can continue to access
t by doing so through
m.
Do not resolve host names when checking client connections.
Use only IP numbers. If you use this option, all
Host column values in the grant tables must
be IP numbers or localhost. See
Section 7.5.11, “How MySQL Uses DNS”.
Don't listen for TCP/IP connections at all. All interaction with mysqld must be made via 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 allowed. See Section 7.5.11, “How MySQL Uses DNS”.
Options that begin with --ssl
specify whether to allow clients to connect via SSL and
indicate where to find SSL keys and certificates. See
Section 5.5.7.3, “SSL Command Options”.
| Command Line Format | --standalone |
| Config File Format | standalone |
| Platform Specific | windows |
Instructs the MySQL server not to run as a service.
--symbolic-links,
--skip-symbolic-links
| Command Line Format | --symbolic-links |
| Config File Format | symbolic-links |
Enable or disable symbolic link support. This option has different effects on Windows and Unix:
On Windows, enabling symbolic links allows 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.6.1.2, “Using Symbolic Links for Tables on Unix”.
| Command Line Format | --skip-safe-malloc |
| Config File Format | skip-safemalloc |
If MySQL is configured with
--with-debug=full, 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 don't need
it by using the
--skip-safemalloc option.
| Command Line Format | --skip-show-database |
| Config 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 allowed only to users who
have the SHOW DATABASES
privilege, and the statement displays all database names.
Without this option, SHOW
DATABASES is allowed 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 |
| Config File Format | skip-stack-trace |
Don't 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 |
| Config File Format | skip-thread-priority |
| Deprecated | 5.1.29 |
Disable using thread priorities for faster response time.
| Command Line Format | --socket=name | ||||
| Config File Format | socket | ||||
| Option Sets Variable | Yes, socket | ||||
| Variable Name | socket | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
On Unix, this option specifies the Unix socket file to use
when listening for local connections. The default value is
/tmp/mysql.sock. 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 | ||||||
| Config File Format | sql-mode | ||||||
| Option Sets Variable | Yes, sql_mode | ||||||
| Variable Name | sql_mode | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
Set the SQL mode. See Section 5.1.7, “Server SQL Modes”.
| Version Introduced | 5.0.20 | ||||
| Command Line Format | --sysdate-is-now | ||||
| Config File Format | sysdate-is-now | ||||
| Value Set |
|
As of MySQL 5.0.12, 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.6, “Date and Time Functions” and for SET
TIMESTAMP in
Section 5.1.4, “Session System Variables”.
This option was added in MySQL 5.0.20.
--tc-heuristic-recover={COMMIT|ROLLBACK}
| Version Introduced | 5.0.3 | ||||
| Command Line Format | --tc-heuristic-recover=name | ||||
| Config File Format | tc-heuristic-recover | ||||
| Value Set |
|
The type of decision to use in the heuristic recovery process. Currently, this option is unused. Added in MySQL 5.0.3.
| Command Line Format | --temp-pool | ||||
| Config File Format | temp-pool | ||||
| Value Set |
|
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.
| Command Line Format | --transaction-isolation=name | ||||
| Config File Format | transaction-isolation | ||||
| Value Set |
|
Sets the default transaction isolation level. The
level value can be
READ-UNCOMMITTED,
READ-COMMITTED,
REPEATABLE-READ, or
SERIALIZABLE. See
Section 12.4.6, “SET TRANSACTION Syntax”.
--tmpdir=,
path-t
path
| Command Line Format | --tmpdir=name | ||
| Config File Format | tmpdir | ||
| Option Sets Variable | Yes, tmpdir | ||
| Variable Name | tmpdir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
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, NetWare, and OS/2. 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 B.1.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 | ||
| Config File Format | user | ||
| Value Set |
|
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
| Command Line Format | --version |
| Config File Format | version |
| Variable Name | version |
| Variable Scope | Global |
| Dynamic Variable | No |
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 allowable value if only certain values are allowed.
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
It is also possible to set variables by using
--set-variable=
or var_name=value-O
syntax. This syntax is deprecated.
var_name=value
You can change the values of most system variables for a running
server with the
SET
statement. See Section 12.5.4, “SET Syntax”.
Section 5.1.3, “Server System Variables”, provides a full description for all variables, and additional information for setting them at server startup and runtime. Section 7.5.3, “Tuning Server Parameters”, includes information on optimizing the server by tuning system variables.
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.0 releases. For historical information concerning their implementation, please see http://www.mysql.com/products/enterprise//4.1/en/.
The following table lists all available system variables:
Table 5.2. mysqld 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 | Session | Yes | ||
| automatic_sp_privileges | Yes | Global | Yes | ||
| back_log | Yes | Yes | Yes | Global | No |
| basedir | Yes | Yes | Yes | Global | No |
| bdb_cache_size | Yes | Yes | Yes | Global | No |
| bdb-home | Yes | Yes | No | ||
| - Variable: bdb_home | Yes | Global | No | ||
| bdb-lock-detect | Yes | Yes | No | ||
| - Variable: bdb_lock_detect | Yes | Global | No | ||
| bdb_log_buffer_size | Yes | Yes | Yes | Global | No |
| bdb-logdir | Yes | Yes | No | ||
| - Variable: bdb_logdir | Yes | Global | No | ||
| bdb_max_lock | Yes | Yes | Yes | Global | No |
| bdb-shared-data | Yes | Yes | No | ||
| - Variable: bdb_shared_data | Yes | Global | No | ||
| bdb-tmpdir | Yes | Yes | No | ||
| - Variable: bdb_tmpdir | Yes | Global | No | ||
| big-tables | Yes | Yes | Yes | ||
| - Variable: big_tables | Yes | Session | Yes | ||
| binlog_cache_size | Yes | Yes | Yes | Global | 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 | Yes | ||
| datetime_format | Yes | Yes | Yes | Both | Yes |
| debug | Yes | Yes | 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 | ||
| expire_logs_days | Yes | Yes | Yes | Global | Yes |
| flush | Yes | Yes | Yes | Global | Yes |
| flush_time | Yes | Yes | Yes | Global | Yes |
| foreign_key_checks | Yes | Session | 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 |
| group_concat_max_len | Yes | Yes | Yes | Both | Yes |
| have_archive | Yes | Global | No | ||
| have_bdb | Yes | Global | No | ||
| have_blackhole_engine | Yes | Global | No | ||
| have_compress | Yes | Global | No | ||
| have_crypt | Yes | Global | No | ||
| have_csv | Yes | Global | No | ||
| have_example_engine | Yes | Global | No | ||
| have_federated_engine | Yes | Global | No | ||
| have_geometry | Yes | Global | No | ||
| have_innodb | Yes | Global | No | ||
| have_isam | Yes | Global | No | ||
| have_merge_engine | Yes | Global | No | ||
| have_ndbcluster | Yes | Global | No | ||
| have_openssl | Yes | Global | No | ||
| have_query_cache | Yes | Global | No | ||
| have_raid | 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 | ||
| 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_hash_index | Yes | Yes | Yes | Global | No |
| innodb_additional_mem_pool_size | Yes | Yes | Yes | Global | No |
| innodb_autoextend_increment | Yes | Yes | Yes | Global | Yes |
| innodb_buffer_pool_awe_mem_mb | Yes | Yes | Yes | Global | No |
| innodb_buffer_pool_size | Yes | Yes | Yes | Global | No |
| 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_io_threads | Yes | Yes | Yes | Global | No |
| innodb_file_per_table | Yes | Yes | Yes | Global | No |
| 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_lock_wait_timeout | Yes | Yes | Yes | Global | No |
| innodb_locks_unsafe_for_binlog | Yes | Yes | Yes | Global | No |
| innodb_log_arch_dir | Yes | Yes | Yes | Global | No |
| innodb_log_archive | 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_open_files | Yes | Yes | Yes | Global | No |
| innodb_rollback_on_timeout | Yes | Yes | Yes | Global | No |
| 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_legacy_cardinality_algorithm | Yes | Yes | Yes | Global | Yes |
| 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_time_names | Yes | Both | Yes | ||
| license | Yes | Global | No | ||
| local_infile | Yes | Global | Yes | ||
| locked_in_memory | Yes | Global | No | ||
| log | Yes | Yes | Yes | Global | No |
| 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-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 | No | ||
| - Variable: log_slow_queries | Yes | Global | No | ||
| 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 |
| max_allowed_packet | Yes | Yes | Yes | Both | 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 |
| multi_range_count | Yes | Yes | Yes | Both | Yes |
| myisam_data_pointer_size | Yes | Yes | Yes | Global | Yes |
| myisam_max_extra_sort_file_size | Yes | Yes | Yes | Global | No |
| myisam_max_sort_file_size | Yes | Yes | Yes | Global | Yes |
| 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 |
| named_pipe | Yes | Global | No | ||
| ndb_autoincrement_prefetch_sz | Yes | Yes | Yes | Both | Yes |
| ndb_cache_check_time | Yes | Yes | Yes | Global | Yes |
| ndb_force_send | Yes | Yes | Yes | Both | Yes |
| ndb_use_exact_count | 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-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 |
| 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 |
| prepared_stmt_count | Yes | Global | No | ||
| profiling | Yes | Session | Yes | ||
| profiling_history_size | Yes | Both | Yes | ||
| protocol_version | Yes | Global | No | ||
| pseudo_thread_id | Yes | Both | 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_purge | 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 | ||
| 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-networking | Yes | Yes | No | ||
| - Variable: skip_networking | Yes | Global | No | ||
| skip-show-database | Yes | Yes | No | ||
| - Variable: skip_show_database | Yes | Global | No | ||
| skip-sync-bdb-logs | Yes | Yes | Yes | Global | No |
| slave_compressed_protocol | Yes | Yes | 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 |
| slow_launch_time | Yes | Yes | Yes | Global | Yes |
| socket | Yes | Yes | Yes | Global | No |
| sort_buffer_size | Yes | Yes | Yes | Both | Yes |
| sql_auto_is_null | Yes | Session | Yes | ||
| sql_big_selects | Yes | Both | Yes | ||
| sql_big_tables | Yes | Session | Yes | ||
| sql_buffer_result | Yes | Session | Yes | ||
| sql_log_bin | Yes | Session | Yes | ||
| sql_log_off | Yes | Session | 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 | Session | Yes | ||
| sql_quote_show_create | Yes | Session | Yes | ||
| sql_safe_updates | Yes | Session | Yes | ||
| sql_select_limit | Yes | Both | Yes | ||
| sql_slave_skip_counter | Yes | Global | Yes | ||
| sql_warnings | Yes | Session | 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-bdb-logs | Yes | Yes | No | ||
| - Variable: sync_bdb_logs | Yes | Global | No | ||
| sync-binlog | Yes | Yes | Yes | ||
| - Variable: sync_binlog | Yes | Global | Yes | ||
| sync-frm | Yes | Yes | Yes | ||
| - Variable: sync_frm | Yes | Global | Yes | ||
| system_time_zone | Yes | Global | No | ||
| 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_stack | Yes | Yes | Yes | Global | No |
| time_format | Yes | Yes | Yes | Both | Yes |
| 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 | Session | Yes | ||
| updatable_views_with_limit | Yes | Yes | Yes | Both | Yes |
| version | Yes | Yes | 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.4, “Session System Variables”, describes system variables that exist only as session variables (that is, they do not have any global counterpart).
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.5.3, “Tuning Server Parameters”.
Section 13.2.3, “InnoDB Startup Options and System Variables”, lists
InnoDB system variables.
Section 17.4.3, “MySQL Cluster System Variables”, lists system variables which are specific to MySQL Cluster.
For information on server system variables specific to replication, see Section 16.1.2, “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.
Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.
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.
| Version Introduced | 5.0.3 | ||||
| Variable Name | automatic_sp_privileges | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
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 when the creator drops the routine. If
automatic_sp_privileges is 0,
the server does not automatically add or drop these
privileges. This variable was added in MySQL 5.0.3.
| Command Line Format | --back_log=# | ||||||
| Config File Format | back_log | ||||||
| Option Sets Variable | Yes, back_log | ||||||
| Variable Name | back_log | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | No | ||||||
| Value Set |
|
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=name | ||
| Config File Format | basedir | ||
| Option Sets Variable | Yes, basedir | ||
| Variable Name | basedir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
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.
| Command Line Format | --bdb_cache_size=# | ||||
| Config File Format | bdb_cache_size | ||||
| Option Sets Variable | Yes, bdb_cache_size | ||||
| Variable Name | bdb_cache_size | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
The size of the buffer that is allocated for caching indexes
and rows for BDB tables. If you don't use
BDB tables, you should start
mysqld with
--skip-bdb to not allocate
memory for this cache.
| Command Line Format | --bdb-home=name | ||
| Config File Format | bdb-home=name | ||
| Option Sets Variable | Yes, bdb_home | ||
| Variable Name | bdb_home | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The base directory for BDB tables. This
should be assigned the same value as the
datadir variable.
| Command Line Format | --bdb_log_buffer_size=# | ||||
| Config File Format | bdb_log_buffer_size | ||||
| Option Sets Variable | Yes, bdb_log_buffer_size | ||||
| Variable Name | bdb_log_buffer_size | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
The size of the buffer that is allocated for caching indexes
and rows for BDB tables. If you don't use
BDB tables, you should set this to 0 or
start mysqld with
--skip-bdb to not allocate
memory for this cache.
| Command Line Format | --bdb-logdir=file_name | ||
| Config File Format | bdb-logdir=file_name | ||
| Option Sets Variable | Yes, bdb_logdir | ||
| Variable Name | bdb_logdir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The directory where the BDB storage engine
writes its log files. This variable can be set with the
--bdb-logdir option.
| Command Line Format | --bdb_max_lock=# | ||||
| Config File Format | bdb_max_lock | ||||
| Option Sets Variable | Yes, bdb_max_lock | ||||
| Variable Name | bdb_max_lock | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
The maximum number of locks that can be active for a
BDB table (10,000 by default). You should
increase this value if errors such as the following occur when
you perform long transactions or when
mysqld has to examine many rows to
calculate a query:
bdb: Lock table is out of available locks Got error 12 from ...
| Command Line Format | --bdb-shared-data |
| Config File Format | bdb-shared-data |
| Option Sets Variable | Yes, bdb_shared_data |
| Variable Name | bdb-shared-data |
| Variable Scope | Global |
| Dynamic Variable | No |
This is ON if you are using
--bdb-shared-data to start
Berkeley DB in multi-process mode. (Do not use
DB_PRIVATE when initializing Berkeley DB.)
| Command Line Format | --bdb-tmpdir=name | ||
| Config File Format | bdb-tmpdir=name | ||
| Option Sets Variable | Yes, bdb_tmpdir | ||
| Variable Name | bdb-tmpdir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The BDB temporary file directory.
| Command Line Format | --binlog_cache_size=# | ||||||||
| Config File Format | binlog_cache_size | ||||||||
| Option Sets Variable | Yes, binlog_cache_size | ||||||||
| Variable Name | binlog_cache_size | ||||||||
| Variable Scope | Global | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
The size of the cache to hold the SQL statements for the
binary log during a transaction. A binary log cache is
allocated for each client if the server supports any
transactional storage engines and if the server has the binary
log enabled (--log-bin option).
If you often use large, multiple-statement transactions, you
can increase this cache size to get more performance. The
Binlog_cache_use and
Binlog_cache_disk_use status
variables can be useful for tuning the size of this variable.
See Section 5.2.3, “The Binary Log”.
MySQL Enterprise
For recommendations on the optimum setting for
binlog_cache_size subscribe
to the MySQL Enterprise Monitor. For more information, see
http://www.mysql.com/products/enterprise/advisors.html.
| Command Line Format | --bulk_insert_buffer_size=# | ||||||||
| Config 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 | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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 | ||
| Value Set |
|
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.
| Variable Name | character_set_connection | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
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. | ||
| Value Set |
|
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.
| Version Introduced | 5.0.19 | ||
| Command Line Format | --character-set-filesystem=name | ||
| Config File Format | character-set-filesystem | ||
| Option Sets Variable | Yes, character_set_filesystem | ||
| Variable Name | character_set_filesystem | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
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
allowed, a different value may be more appropriate. For
example, if the system represents file names using UTF-8, set
character_set_filesystem to
'utf8'. This variable was added in MySQL
5.0.19.
| Variable Name | character_set_results | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
The character set used for returning query results to the client.
| Command Line Format | --character-set-server | ||
| Config File Format | character-set-server | ||
| Option Sets Variable | Yes, character_set_server | ||
| Variable Name | character_set_server | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
The server's default character set.
| Variable Name | character_set_system | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The character set used by the server for storing identifiers.
The value is always utf8.
| Command Line Format | --character-sets-dir=name | ||
| Config File Format | character-sets-dir | ||
| Option Sets Variable | Yes, character_sets_dir | ||
| Variable Name | character-sets-dir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The directory where character sets are installed.
| Variable Name | collation_connection | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
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. | ||
| Value Set |
|
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 | ||
| Config File Format | collation-server | ||
| Option Sets Variable | Yes, collation_server | ||
| Variable Name | collation_server | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
The server's default collation.
| Version Introduced | 5.0.3 | ||||||
| Command Line Format | --completion_type=# | ||||||
| Config File Format | completion_type | ||||||
| Option Sets Variable | Yes, completion_type | ||||||
| Variable Name | competion_type | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
The transaction completion type:
If the value is 0 (the default),
COMMIT and
ROLLBACK
are unaffected.
If the value is 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.)
If the value is 2, COMMIT
and
ROLLBACK
are equivalent to COMMIT RELEASE and
ROLLBACK RELEASE, respectively. (The
server disconnects after terminating the transaction.)
This variable was added in MySQL 5.0.3
| Command Line Format | --concurrent_insert[=#] | ||||||
| Config File Format | concurrent_insert | ||||||
| Option Sets Variable | Yes, concurrent_insert | ||||||
| Variable Name | concurrent_insert | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set (<= 5.0.5) |
| ||||||
| Value Set (>= 5.0.6) |
|
If 1 (the default), MySQL allows
INSERT and
SELECT statements to run
concurrently for MyISAM tables that have no
free blocks in the middle of the data file. You can turn this
option off by starting mysqld with
--safe-mode or
--skip-new.
In MySQL 5.0.6, this variable was changed to take three integer values:
| Value | Description |
| 0 | Off |
| 1 | (Default) Enables concurrent insert for MyISAM tables
that don't have holes |
| 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.3.3, “Concurrent Inserts”.
| Command Line Format | --connect_timeout=# | ||||||
| Config File Format | connect_timeout | ||||||
| Option Sets Variable | Yes, connect_timeout | ||||||
| Variable Name | connect_timeout | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set (<= 5.0.51) |
| ||||||
| Value Set (>= 5.0.52) |
|
The number of seconds that the mysqld
server waits for a connect packet before responding with
Bad handshake. The default value is 10
seconds as of MySQL 5.0.52 and 5 seconds before that.
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=name | ||
| Config File Format | datadir | ||
| Option Sets Variable | Yes, datadir | ||
| Variable Name | datadir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The MySQL data directory. This variable can be set with the
--datadir option.
This variable is unused.
This variable is unused.
| Command Line Format | --default_week_format=# | ||||||
| Config File Format | default_week_format | ||||||
| Option Sets Variable | Yes, default_week_format | ||||||
| Variable Name | default_week_format | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
The default mode value to use for the
WEEK() function. See
Section 11.6, “Date and Time Functions”.
| Command Line Format | --delay-key-write[=name] | ||||||
| Config File Format | delay-key-write | ||||||
| Option Sets Variable | Yes, delay_key_write | ||||||
| Variable Name | delay-key-write | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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 option (for
example,
--myisam-recover=BACKUP,FORCE).
See Section 5.1.2, “Server Command Options”, and
Section 13.1.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=# | ||||||||
| Config File Format | delayed_insert_limit | ||||||||
| Option Sets Variable | Yes, delayed_insert_limit | ||||||||
| Variable Name | delayed_insert_limit | ||||||||
| Variable Scope | Global | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
After inserting
delayed_insert_limit delayed
rows, the INSERT DELAYED
handler thread checks whether there are any
SELECT statements pending. If
so, it allows them to execute before continuing to insert
delayed rows.
| Command Line Format | --delayed_insert_timeout=# | ||||
| Config File Format | delayed_insert_timeout | ||||
| Option Sets Variable | Yes, delayed_insert_timeout | ||||
| Variable Name | delayed_insert_timeout | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
How many seconds an INSERT
DELAYED handler thread should wait for
INSERT statements before
terminating.
| Command Line Format | --delayed_queue_size=# | ||||||||
| Config File Format | delayed_queue_size | ||||||||
| Option Sets Variable | Yes, delayed_queue_size | ||||||||
| Variable Name | delayed_queue_size | ||||||||
| Variable Scope | Global | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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.
| Version Introduced | 5.0.6 | ||||||
| Command Line Format | --div_precision_increment=# | ||||||
| Config File Format | div_precision_increment | ||||||
| Option Sets Variable | Yes, div_precision_increment | ||||||
| Variable Name | div_precision_increment | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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 | +----------------+
This variable was added in MySQL 5.0.6.
| Command Line Format | --engine-condition-pushdown | ||||
| Config File Format | engine-condition-pushdown | ||||
| Option Sets Variable | Yes, engine_condition_pushdown | ||||
| Variable Name | engine_condition_pushdown | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Value Set (>= 5.1.0) |
|
When the value of this variable is 0 (OFF),
a query such as SELECT * FROM t WHERE mycol =
42, where mycol is a nonindexed
column, is executed as a full table scan. The storage engine
sends every row to the MySQL server, which applies the
WHERE condition. If
engine_condition_pushdown is
set to 1 (ON), the condition is
“pushed down” to the storage engine, which uses
the condition to perform the scan, and sends back to the MySQL
server only those rows that match the condition. By default,
this variable is OFF.
In MySQL 5.0, this variable is useful only with
the NDBCLUSTER storage engine.
However, we intend to implement it for additional storage
engines in future MySQL releases.
Setting this variable to ON 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 means the amount of cluster data that must be sent over
the network is greatly reduced, increasing the efficiency with
which results are returned.
For more information, see Section 7.2.7, “Condition Pushdown Optimization”.
This variable was added in MySQL 5.0.3.
| Command Line Format | --expire_logs_days=# | ||||||
| Config File Format | expire_logs_days | ||||||
| Option Sets Variable | Yes, expire_logs_days | ||||||
| Variable Name | expire_logs_days | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
The number of days for automatic binary log removal. The default is 0, which means “no automatic removal.” Possible removals happen at startup and at binary log rotation.
| Command Line Format | --flush | ||||
| Config File Format | flush | ||||
| Variable Name | flush | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
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 B.1.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=# | ||||||
| Config File Format | flush_time | ||||||
| Option Sets Variable | Yes, flush_time | ||||||
| Variable Name | flush_time | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
| ||||||
| Value Set |
|
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.
| Command Line Format | --ft_boolean_syntax=name | ||||
| Config File Format | ft_boolean_syntax | ||||
| Variable Name | ft_boolean_syntax | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
The list of operators supported by boolean full-text searches
performed using IN BOOLEAN MODE. See
Section 11.8.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 allowed 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=# | ||||
| Config 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 | ||||
| Value Set |
|
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=# | ||||||
| Config 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 | ||||||
| Value Set |
|
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=# | ||||||
| Config 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 | ||||||
| Value Set |
|
The number of top matches to use for full-text searches
performed using WITH QUERY EXPANSION.
| Command Line Format | --ft_stopword_file=name | ||
| Config File Format | ft_stopword_file | ||
| Option Sets Variable | Yes, ft_stopword_file | ||
| Variable Name | ft_stopword_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The file from which to read the list of stopwords for
full-text searches. 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
myisam/ft_static.c file). Setting this
variable to the empty string ('') disables
stopword filtering.
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 | --group_concat_max_len=# | ||||||||
| Config 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 | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
The maximum allowed result length in bytes for the
GROUP_CONCAT() function. The
default is 1024.
YES if mysqld supports
ARCHIVE tables, NO if
not.
YES if mysqld supports
BDB tables. DISABLED if
--skip-bdb is used.
YES if mysqld supports
BLACKHOLE tables, NO if
not.
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
CSV tables, NO if not.
YES if mysqld supports
EXAMPLE tables, NO if
not.
YES if mysqld supports
FEDERATED tables, NO if
not. This variable was added in MySQL 5.0.3.
YES if the server supports spatial data
types, NO if not.
YES if mysqld supports
InnoDB tables. DISABLED
if --skip-innodb is used.
In MySQL 5.0, this variable appears only for
reasons of backward compatibility. It is always
NO because ISAM tables
are no longer supported.
YES if mysqld supports
MERGE tables. DISABLED
if --skip-merge is used. This
variable was added in MySQL 5.0.24.
YES if mysqld supports
SSL connections, NO if not. As of MySQL
5.0.38, this variable is an alias for
have_ssl.
YES if mysqld supports
the query cache, NO if not.
In MySQL 5.0, this variable appears only for
reasons of backward compatibility. It is always
NO because RAID tables
are no longer supported.
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. This variable
was added in MySQL 5.0.38. Before that, use
have_openssl.
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.
| Version Introduced | 5.0.38 | ||
| Variable Name | hostname | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The server sets this variable to the server host name at startup. This variable was added in MySQL 5.0.38.
| Command Line Format | --init-connect=name | ||
| Config File Format | init_connect | ||
| Option Sets Variable | Yes, init_connect | ||
| Variable Name | init_connect | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
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'
Note that 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=name | ||
| Config File Format | init-file | ||
| Option Sets Variable | Yes, init_file | ||
| Variable Name | init_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
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.
Note that the --init-file
option is unavailable if MySQL was configured with the
--disable-grant-options
option. See Section 2.16.2, “Typical configure Options”.
innodb_
xxx
InnoDB system variables are listed in
Section 13.2.3, “InnoDB Startup Options and System Variables”.
| Command Line Format | --interactive_timeout=# | ||||||
| Config File Format | interactive_timeout | ||||||
| Option Sets Variable | Yes, interactive_timeout | ||||||
| Variable Name | interactive_timeout | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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=# | ||||||||
| Config File Format | join_buffer_size | ||||||||
| Option Sets Variable | Yes, join_buffer_size | ||||||||
| Variable Name | join_buffer_size | ||||||||
| Variable Scope | Both | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
|
The 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.
The maximum allowable setting for
join_buffer_size is 4GB.
| Version Introduced | 5.0.48 | ||||
| Command Line Format | --keep_files_on_create=# | ||||
| Config 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 | ||||
| Value Set |
|
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.
This variable was added in MySQL 5.0.48.
| Command Line Format | --key_buffer_size=# | ||||||
| Config File Format | key_buffer_size | ||||||
| Option Sets Variable | Yes, key_buffer_size | ||||||
| Variable Name | key_buffer_size | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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 allowable setting for
key_buffer_size is 4GB on
32-bit platforms. As of MySQL 5.0.52, values larger than 4GB
are allowed for 64-bit platforms (except 64-bit Windows, for
which large values are truncated to 4GB with a warning). 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.
Increase the value to get better index handling (for all reads and multiple writes) to as much as you can afford. Using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common. However, if you make the value too large (for example, more than 50% of your total memory) your system might start to page and become extremely slow. 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. Consider also the memory requirements of other storage engines.
For even more speed when writing many rows at the same time,
use LOCK TABLES. See
Section 7.2.19, “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.5.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 may be allocated internally for administrative structures.
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.4.5, “The MyISAM Key Cache”.
| Command Line Format | --key_cache_age_threshold=# | ||||||||
| Config 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 | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
This value controls the demotion of buffers from the hot
sub-chain of a key cache to the warm sub-chain. Lower values
cause demotion to happen more quickly. The minimum value is
100. The default value is 300. See
Section 7.4.5, “The MyISAM Key Cache”.
| Command Line Format | --key_cache_block_size=# | ||||||
| Config 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 | ||||||
| Value Set |
|
The size in bytes of blocks in the key cache. The default
value is 1024. See Section 7.4.5, “The MyISAM Key Cache”.
| Command Line Format | --key_cache_division_limit=# | ||||||
| Config 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 | ||||||
| Value Set |
|
The division point between the hot and warm sub-chains of the
key cache buffer chain. The value is the percentage of the
buffer chain to use for the warm sub-chain. Allowable values
range from 1 to 100. The default value is 100. See
Section 7.4.5, “The MyISAM Key Cache”.
| Command Line Format | --language=name | ||||
| Config File Format | language | ||||
| Option Sets Variable | Yes, language | ||||
| Variable Name | language | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
The language used for error messages.
| Variable Name | large_files_support |
| Variable Scope | Global |
| Dynamic Variable | No |
Whether mysqld was compiled with options for large file support.
| Version Introduced | 5.0.3 | ||||
| Command Line Format | --large-pages | ||||
| Config File Format | large-pages | ||||
| Option Sets Variable | Yes, large_pages | ||||
| Variable Name | large_pages | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Platform Specific | linux | ||||
| Value Set |
|
Whether large page support is enabled (via the
--large-pages option). See
Section 7.5.9, “Enabling Large Page Support”. This variable was added
in MySQL 5.0.3.
For more information, see
the entry for the
--large-pages server
option.
| Version Introduced | 5.0.3 | ||||
| Variable Name | large_page_size | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
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. This variable was added in MySQL 5.0.3.
For more information, see
the entry for the
--large-pages server
option.
| Version Introduced | 5.0.25 | ||
| Variable Name | lc_time_names | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
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.8, “MySQL Server Locale Support”. This variable was added in
MySQL 5.0.25.
| Variable Name | license | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
The type of license the server has.
| Variable Name | local_infile | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
Whether LOCAL is supported for
LOAD DATA
INFILE statements. See
Section 5.3.4, “Security Issues with LOAD
DATA LOCAL”.
| 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.2, “The General Query Log”.
| Variable Name | log_bin |
| Variable Scope | Global |
| Dynamic Variable | No |
Whether the binary log is enabled. See Section 5.2.3, “The Binary Log”.
log_bin_trust_function_creators
| Version Introduced | 5.0.16 | ||||
| Command Line Format | --log-bin-trust-function-creators | ||||
| Config 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 | ||||
| Value Set |
|
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 allowed 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 18.5, “Binary Logging of Stored Programs”.
This variable was added in MySQL 5.0.16.
log_bin_trust_routine_creators
This is the old name for
log_bin_trust_function_creators.
Before MySQL 5.0.16, it also applies to stored procedures, not
just stored functions. As of 5.0.16, this variable is
deprecated. It is recognized for backward compatibility but
its use results in a warning.
This variable was added in MySQL 5.0.6.
| Command Line Format | --log-error[=name] | ||
| Config File Format | log-error | ||
| Option Sets Variable | Yes, log_error | ||
| Variable Name | log_error | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The location of the error log.
| Command Line Format | --log-queries-not-using-indexes | ||
| Config 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 | ||
| Deprecated | 5.1.29, by slow-query-log | ||
| Value Set |
|
Whether queries that do not use indexes are logged to the slow query log. See Section 5.2.4, “The Slow Query Log”. This variable was added in MySQL 5.0.23.
| Command Line Format | --log-slow-queries[=name] | ||
| Config File Format | log-slow-queries | ||
| Option Sets Variable | Yes, log_slow_queries | ||
| Variable Name | log_slow_queries | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
Whether slow queries should be logged. “Slow” is
determined by the value of the
long_query_time variable. See
Section 5.2.4, “The Slow Query Log”.
| Command Line Format | --log-warnings[=#] | ||||||||
| Config 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 | ||||||||
| Value Set |
|
Whether to produce additional warning messages. It is enabled (1) by default and can be disabled by setting it to 0. Aborted connections are not logged to the error log unless the value is greater than 1.
| Command Line Format | --long_query_time=# | ||||||
| Config File Format | long_query_time | ||||||
| Option Sets Variable | Yes, long_query_time | ||||||
| Variable Name | long_query_time | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set (<= 5.0.20) |
|
If a query takes longer than this many seconds, the server
increments the Slow_queries
status variable. If you are using the
--log-slow-queries option, 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 1. The
default is 10. See Section 5.2.4, “The Slow Query Log”.
| Command Line Format | --low-priority-updates | ||||
| Config File Format | low-priority-updates | ||||
| Option Sets Variable | Yes, low_priority_updates | ||||
| Variable Name | low_priority_updates | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
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
(MyISAM, MEMORY,
MERGE). This variable previously was named
sql_low_priority_updates.
| Command Line Format | --lower_case_file_system[=#] | ||
| Config 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 | ||
| Value Set |
|
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.
| Command Line Format | --lower_case_table_names[=#] | ||||||
| Config 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 | ||||||
| Value Set |
|
If set to 1, table names are stored in lowercase on disk and table name 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. See Section 8.2.2, “Identifier Case Sensitivity”.
If you are using InnoDB tables, you should
set this variable to 1 on all platforms to force names to be
converted to lowercase.
You should not set this variable to 0 if
you are running MySQL on a system that does not have
case-sensitive file names (such as Windows or Mac OS X). If
this variable is not set at startup and the file system on
which the data directory is located does not have
case-sensitive file names, MySQL automatically sets
lower_case_table_names to 2.
| Command Line Format | --max_allowed_packet=# | ||||||
| Config File Format | max_allowed_packet | ||||||
| Option Sets Variable | Yes, max_allowed_packet | ||||||
| Variable Name | max_allowed_packet | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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 allows 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.
| Command Line Format | --max_connect_errors=# | ||||||||
| Config File Format | max_connect_errors | ||||||||
| Option Sets Variable | Yes, max_connect_errors | ||||||||
| Variable Name | max_connect_errors | ||||||||
| Variable Scope | Global | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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.
| Command Line Format | --max_connections=# | ||||
| Config File Format | max_connections | ||||
| Option Sets Variable | Yes, max_connections | ||||
| Variable Name | max_connections | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
The number of simultaneous client connections allowed. By
default, this is 100. See
Section B.1.2.7, “Too many connections”, for more information.
MySQL Enterprise
For notification that the maximum number of connections is
getting dangerously high and for advice on setting the
optimum value for
max_connections subscribe
to the MySQL Enterprise Monitor. For more information, see
http://www.mysql.com/products/enterprise/advisors.html.
Increasing this value increases the number of file descriptors that mysqld requires. See Section 7.4.7, “How MySQL Opens and Closes Tables”, for comments on file descriptor limits.
| Command Line Format | --max_delayed_threads=# | ||||||
| Config File Format | max_delayed_threads | ||||||
| Option Sets Variable | Yes, max_delayed_threads | ||||||
| Variable Name | max_delayed_threads | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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 wasn't 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=# | ||||||
| Config File Format | max_error_count | ||||||
| Option Sets Variable | Yes, max_error_count | ||||||
| Variable Name | max_error_count | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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=# | ||||||
| Config 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 | ||||||
| Value Set |
|
This variable sets the maximum size to which
MEMORY tables are allowed 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.
On 64-bit platforms, the maximum value for this variable is 1844674407370954752.
MySQL Enterprise
Subscribers to the MySQL Enterprise Monitor receive
recommendations for the optimum setting for
max_heap_table_size. For
more information, see
http://www.mysql.com/products/enterprise/advisors.html.
| Variable Name | max_insert_delayed_threads | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
This variable is a synonym for
max_delayed_threads.
| Command Line Format | --max_join_size=# | ||||||
| Config File Format | max_join_size | ||||||
| Option Sets Variable | Yes, max_join_size | ||||||
| Variable Name | max_join_size | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
Do not allow 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=# | ||||||
| Config 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 | ||||||
| Value Set |
|
The cutoff on the size of index values that determines which
filesort algorithm to use. See
Section 7.2.13, “ORDER BY Optimization”.
| Version Introduced | 5.0.21 | ||||||
| Command Line Format | --max_prepared_stmt_count=# | ||||||
| Config 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 | ||||||
| Value Set |
|
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 allowable range of values is from 0 to 1 million. Setting the value to 0 disables prepared statements. This variable was added in MySQL 5.0.21.
| Command Line Format | --max_relay_log_size=# | ||||||
| Config 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 | ||||||
| Value Set |
|
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 16.4.1, “Replication Implementation Details”.
| Command Line Format | --max_seeks_for_key=# | ||||||||
| Config 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 | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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.5.5.18, “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=# | ||||||
| Config File Format | max_sort_length | ||||||
| Option Sets Variable | Yes, max_sort_length | ||||||
| Variable Name | max_sort_length | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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.
| Version Introduced | 5.0.17 | ||||||
| Command Line Format | --max_sp_recursion_depth[=#] | ||||||
| Config 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 | ||||||
| Value Set |
|
The number of times that any given stored procedure may be called recursively. The default value for this option is 0, which completely disallows 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.
This variable was added in MySQL 5.0.17.
| Command Line Format | --max_tmp_tables=# | ||||||||
| Config File Format | max_tmp_tables | ||||||||
| Option Sets Variable | Yes, max_tmp_tables | ||||||||
| Variable Name | max_tmp_tables | ||||||||
| Variable Scope | Both | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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=# | ||||
| Config File Format | max_user_connections | ||||
| Option Sets Variable | Yes, max_user_connections | ||||
| Variable Name | max_user_connections | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
The maximum number of simultaneous connections allowed to any given MySQL account. A value of 0 means “no limit.”
Before MySQL 5.0.3, this variable has only global scope.
Beginning with MySQL 5.0.3, it also has a read-only session
scope. The session variable has the same value as the global
variable unless the current account has a nonzero
MAX_USER_CONNECTIONS resource limit. In
that case, the session value reflects the account limit.
| Command Line Format | --max_write_lock_count=# | ||||||||
| Config 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 | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
After this many write locks, allow some pending read lock requests to be processed in between.
| Command Line Format | --myisam_data_pointer_size=# | ||||||
| Config 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 | ||||||
| Value Set (<= 5.0.5) |
| ||||||
| Value Set (>= 5.0.6) |
|
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
(4 before MySQL 5.0.6). See Section B.1.2.12, “The table is full”.
myisam_max_extra_sort_file_size
(DEPRECATED)
This variable is not used. It was removed in MySQL 5.0.6.
| Command Line Format | --myisam_max_sort_file_size=# | ||||
| Config 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 | ||||
| Value Set |
|
The maximum size of the temporary file that MySQL is allowed
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.
| Variable Name | myisam_recover_options |
| Variable Scope | Global |
| Dynamic Variable | No |
The value of the
--myisam-recover option. See
Section 5.1.2, “Server Command Options”.
| Command Line Format | --myisam_repair_threads=# | ||||||||
| Config File Format | myisam_repair_threads | ||||||||
| Option Sets Variable | Yes, myisam_repair_threads | ||||||||
| Variable Name | myisam_repair_threads | ||||||||
| Variable Scope | Both | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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=# | ||||||||
| Config 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 | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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 allowable setting for
myisam_sort_buffer_size is
4GB.
| Version Introduced | 5.0.14 | ||||
| Command Line Format | --myisam_stats_method=name | ||||
| Config File Format | myisam_stats_method | ||||
| Option Sets Variable | Yes, myisam_stats_method | ||||
| Variable Name | myisam_stats_method | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Value Set (>= 5.0) |
|
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.4.6, “MyISAM Index Statistics Collection”.
Any unique prefix of a valid value may be used to set the value of this variable.
This variable was added in MySQL 5.0.14. For older versions,
the statistics collection method is equivalent to
nulls_equal.
| Variable Name | named_pipe |
| Variable Scope | Global |
| Dynamic Variable | No |
| Platform Specific | windows |
(Windows only.) Indicates whether the server supports connections over named pipes.
| Command Line Format | --net_buffer_length=# | ||||||
| Config File Format | net_buffer_length | ||||||
| Option Sets Variable | Yes, net_buffer_length | ||||||
| Variable Name | net_buffer_length | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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.
| Command Line Format | --net_read_timeout=# | ||||||
| Config File Format | net_read_timeout | ||||||
| Option Sets Variable | Yes, net_read_timeout | ||||||
| Variable Name | net_read_timeout | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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 via 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=# | ||||||||
| Config File Format | net_retry_count | ||||||||
| Option Sets Variable | Yes, net_retry_count | ||||||||
| Variable Name | net_retry_count | ||||||||
| Variable Scope | Both | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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=# | ||||||
| Config File Format | net_write_timeout | ||||||
| Option Sets Variable | Yes, net_write_timeout | ||||||
| Variable Name | net_write_timeout | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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 via Unix
socket files, named pipes, or shared memory. See also
net_read_timeout.
| Command Line Format | --new | ||||
| Config File Format | new | ||||
| Option Sets Variable | Yes, new | ||||
| Variable Name | new | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Disabled by | skip-new | ||||
| Value Set |
|
This variable was used in MySQL 4.0 to turn on some 4.1
behaviors, and is retained for backward compatibility. In
MySQL 5.0, its value is always
OFF.
| Command Line Format | --old_passwords | ||||
| Config File Format | old-passwords | ||||
| Option Sets Variable | Yes, old_passwords | ||||
| Variable Name | old_passwords | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
Whether the server should use pre-4.1-style passwords for
MySQL user accounts. See Section B.1.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.5.4, “SET Syntax”.
| Command Line Format | --open-files-limit=# | ||||||
| Config File Format | open-files-limit | ||||||
| Option Sets Variable | Yes, open_files_limit | ||||||
| Variable Name | open_files_limit | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | No | ||||||
| Value Set |
|
The number of files that the operating system allows
mysqld to open. This is the real value
allowed 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 can't change the number
of open files.
| Version Introduced | 5.0.1 | ||||
| Command Line Format | --optimizer_prune_level[=#] | ||||
| Config File Format | optimizer_prune_level | ||||
| Option Sets Variable | Yes, optimizer_prune_level | ||||
| Variable Name | optimizer_prune_level | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
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. This variable was added in MySQL 5.0.1.
| Version Introduced | 5.0.1 | ||||
| Command Line Format | --optimizer_search_depth[=#] | ||||
| Config File Format | optimizer_search_depth | ||||
| Option Sets Variable | Yes, optimizer_search_depth | ||||
| Variable Name | optimizer_search_depth | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
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 the maximum number of tables used in a query plus 2, the optimizer switches to the algorithm used in MySQL 5.0.0 (and previous versions) for performing searches. This variable was added in MySQL 5.0.1.
| Command Line Format | --pid-file=name | ||
| Config File Format | pid-file | ||
| Option Sets Variable | Yes, pid_file | ||
| Variable Name | pid_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The path name of the process ID (PID) file. This variable can
be set with the --pid-file
option.
| Version Introduced | 5.0.67 | ||||
| Command Line Format | --plugin_dir=name | ||||
| Config File Format | plugin_dir | ||||
| Option Sets Variable | Yes, plugin_dir | ||||
| Variable Name | plugin_dir | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
The path name of the plugin directory. This variable was added in MySQL 5.0.67. If the value is nonempty, user-defined function object files must be located in this directory. If the value is empty, the behavior that is used before 5.0.67 applies: The UDF object files must be located in a directory that is searched by your system's dynamic linker.
| Command Line Format | --port=# | ||||
| Config File Format | port | ||||
| Option Sets Variable | Yes, port | ||||
| Variable Name | port | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
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=# | ||||||
| Config File Format | preload_buffer_size | ||||||
| Option Sets Variable | Yes, preload_buffer_size | ||||||
| Variable Name | preload_buffer_size | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
The size of the buffer that is allocated when preloading indexes.
| Version Introduced | 5.0.21 | ||
| Version Removed | 5.0.31 | ||
| Variable Name | prepared_stmt_count | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The current number of prepared statements. (The maximum number
of statements is given by the
max_prepared_stmt_count
system variable.) This variable was added in MySQL 5.0.21. In
MySQL 5.0.32, it was converted to the global
Prepared_stmt_count status
variable.
| Variable Name | protocol_version | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The version of the client/server protocol used by the MySQL server.
| Variable Name | pseudo_thread_id | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
This variable is for internal server use.
| Command Line Format | --query_alloc_block_size=# | ||||||||
| Config 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 | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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 a bit.
| Command Line Format | --query_cache_limit=# | ||||||||
| Config File Format | query_cache_limit | ||||||||
| Option Sets Variable | Yes, query_cache_limit | ||||||||
| Variable Name | query_cache_limit | ||||||||
| Variable Scope | Global | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
Don't cache results that are larger than this number of bytes. The default value is 1MB.
| Command Line Format | --query_cache_min_res_unit=# | ||||||||
| Config 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 | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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.5.5.3, “Query Cache Configuration”.
| Command Line Format | --query_cache_size=# | ||||||||
| Config File Format | query_cache_size | ||||||||
| Option Sets Variable | Yes, query_cache_size | ||||||||
| Variable Name | query_cache_size | ||||||||
| Variable Scope | Global | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
The amount of memory allocated for caching query results. The
default value is 0, which disables the query cache. The
allowable 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.5.5.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,
you'll get a warning, as described in
Section 7.5.5.3, “Query Cache Configuration”.
| Command Line Format | --query_cache_type=# | ||||||
| Config File Format | query_cache_type | ||||||
| Option Sets Variable | Yes, query_cache_type | ||||||
| Variable Name | query_cache_type | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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 | Don't 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.
Any unique prefix of a valid value may be used to set the value of this variable.
| Command Line Format | --query_cache_wlock_invalidate | ||||
| Config 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 | ||||
| Value Set |
|
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=# | ||||||||
| Config File Format | query_prealloc_size | ||||||||
| Option Sets Variable | Yes, query_prealloc_size | ||||||||
| Variable Name | query_prealloc_size | ||||||||
| Variable Scope | Both | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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.
| Command Line Format | --range_alloc_block_size=# | ||||||||
| Config 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 | ||||||||
| Value Set (>= 5.0.54) |
|
The size of blocks that are allocated when doing range optimization.
| Command Line Format | --read_buffer_size=# | ||||||
| Config File Format | read_buffer_size | ||||||
| Option Sets Variable | Yes, read_buffer_size | ||||||
| Variable Name | read_buffer_size | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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 allowable 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.5.8, “How MySQL Uses Memory”, for
example.
| Command Line Format | --read_only | ||||
| Config File Format | read_only | ||||
| Option Sets Variable | Yes, read_only | ||||
| Variable Name | read_only | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
This variable is off by default. When it is enabled, the
server allows no updates except from users that have the
SUPER privilege or (on a slave
server) from updates performed by slave threads. On a slave
server, this can be useful to ensure that the slave accepts
updates only from its master server and not from clients. As
of MySQL 5.0.16, this variable does not apply to
TEMPORARY tables.
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.
| Command Line Format | --read_rnd_buffer_size=# | ||||||
| Config 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 | ||||||
| Value Set |
|
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.2.13, “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 allowable 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.5.8, “How MySQL Uses Memory”, for
example.
| Command Line Format | --relay_log_purge | ||||
| Config File Format | relay_log_purge | ||||
| Option Sets Variable | Yes, relay_log_purge | ||||
| Variable Name | relay_log_purge | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
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=# | ||||||||
| Config 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 | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
The maximum amount of space to use for all relay logs.
| Command Line Format | --secure-auth | ||||
| Config File Format | secure-auth | ||||
| Option Sets Variable | Yes, secure_auth | ||||
| Variable Name | secure_auth | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
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 B.1.2.4, “Client does not support authentication protocol”.
| Version Introduced | 5.0.38 | ||
| Command Line Format | --secure-file-priv | ||
| Config File Format | secure-file-priv | ||
| Option Sets Variable | Yes, secure_file_priv | ||
| Variable Name | secure_file_priv | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
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.
This variable was added in MySQL 5.0.38.
| Command Line Format | --server-id=# | ||||||
| Config File Format | server-id | ||||||
| Option Sets Variable | Yes, server_id | ||||||
| Variable Name | server_id | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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 allows 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 is ON if the server allows 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. On NetWare, only TCP/IP connections are
supported, so do not set this variable to
ON. 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
allowed only to users who have the SHOW
DATABASES privilege, and the statement displays all
database names. If the value is OFF,
SHOW DATABASES is allowed 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=# | ||||
| Config File Format | slow_launch_time | ||||
| Option Sets Variable | Yes, slow_launch_time | ||||
| Variable Name | slow_launch_time | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
If creating a thread takes longer than this many seconds, the
server increments the
Slow_launch_threads status
variable.
| Command Line Format | --socket=name | ||||
| Config File Format | socket | ||||
| Option Sets Variable | Yes, socket | ||||
| Variable Name | socket | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Value Set |
|
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=# | ||||||||
| Config File Format | sort_buffer_size | ||||||||
| Option Sets Variable | Yes, sort_buffer_size | ||||||||
| Variable Name | sort_buffer_size | ||||||||
| Variable Scope | Both | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
Each thread that needs to do a sort allocates a buffer of this
size. Increase this value for faster ORDER
BY or GROUP BY operations. See
Section B.1.4.4, “Where MySQL Stores Temporary Files”.
The maximum allowable setting for
sort_buffer_size is 4GB.
| Command Line Format | --sql-mode=name | ||||||
| Config File Format | sql-mode | ||||||
| Option Sets Variable | Yes, sql_mode | ||||||
| Variable Name | sql_mode | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
The current server SQL mode, which can be set dynamically. See Section 5.1.7, “Server SQL Modes”.
| Variable Name | sql_select_limit | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
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 allows per table, which depends on the server
configuration and may be affected if the server build was
configured with
--with-big-tables. 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.
| Version Introduced | 5.0.23 | ||
| Command Line Format | --ssl-ca=name | ||
| Config File Format | ssl-ca | ||
| Option Sets Variable | Yes, ssl_ca | ||
| Variable Name | ssl_ca | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The path to a file with a list of trusted SSL CAs. This variable was added in MySQL 5.0.23.
| Version Introduced | 5.0.23 | ||
| Command Line Format | --ssl-capath=name | ||
| Config File Format | ssl-capath | ||
| Option Sets Variable | Yes, ssl_capath | ||
| Variable Name | ssl_capath | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The path to a directory that contains trusted SSL CA certificates in PEM format. This variable was added in MySQL 5.0.23.
| Version Introduced | 5.0.23 | ||
| Command Line Format | --ssl-cert=name | ||
| Config File Format | ssl-cert | ||
| Option Sets Variable | Yes, ssl_cert | ||
| Variable Name | ssl_cert | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The name of the SSL certificate file to use for establishing a secure connection. This variable was added in MySQL 5.0.23.
| Version Introduced | 5.0.23 | ||
| Command Line Format | --ssl-cipher=name | ||
| Config File Format | ssl-cipher | ||
| Option Sets Variable | Yes, ssl_cipher | ||
| Variable Name | ssl_cipher | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
A list of allowable ciphers to use for SSL encryption. This variable was added in MySQL 5.0.23.
| Version Introduced | 5.0.23 | ||
| Command Line Format | --ssl-key=name | ||
| Config File Format | ssl-key | ||
| Option Sets Variable | Yes, ssl_key | ||
| Variable Name | ssl_key | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The name of the SSL key file to use for establishing a secure connection. This variable was added in MySQL 5.0.23.
| Variable Name | storage_engine | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
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”.
| Command Line Format | --sync-frm | ||||
| Config File Format | sync-frm | ||||
| Option Sets Variable | Yes, sync_frm | ||||
| Variable Name | sync_frm | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
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 | ||
| Value Set |
|
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.7, “MySQL Server Time Zone Support”.
| Command Line Format | --table_cache=# | ||||||
| Config File Format | table_cache | ||||||
| Option Sets Variable | Yes, table_cache | ||||||
| Variable Name | table_cache | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Deprecated | 5.1.3, by table_open_cache | ||||||
| Value Set |
|
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 don't do
FLUSH TABLES
often (which just forces all tables to be closed and
reopened), then you should increase the value of the
table_cache variable. For
more information about the table cache, see
Section 7.4.7, “How MySQL Opens and Closes Tables”.
| Version Introduced | 5.0.10 | ||||||
| Command Line Format | --table_lock_wait_timeout=# | ||||||
| Config 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 | ||||||
| Value Set |
|
This variable currently is unused.
| Variable Name | table_type | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Deprecated | 5.2.5, by storage_engine | ||
| Value Set |
|
This variable is a synonym for
storage_engine. In MySQL
5.0,
storage_engine is the
preferred name.
| Command Line Format | --thread_cache_size=# | ||||||
| Config File Format | thread_cache_size | ||||||
| Option Sets Variable | Yes, thread_cache_size | ||||||
| Variable Name | thread_cache_size | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
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 doesn't provide a notable performance
improvement if you have a good thread implementation.) 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=# | ||||||
| Config File Format | thread_concurrency | ||||||
| Option Sets Variable | Yes, thread_concurrency | ||||||
| Variable Name | thread_concurrency | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | No | ||||||
| Value Set |
|
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_stack=# | ||||||||
| Config File Format | thread_stack | ||||||||
| Option Sets Variable | Yes, thread_stack | ||||||||
| Variable Name | thread_stack | ||||||||
| Variable Scope | Global | ||||||||
| Dynamic Variable | No | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
The stack size for each thread. Many of the limits detected by
the crash-me test are dependent on this
value. See Section 7.1.4, “The MySQL Benchmark Suite”. The default
(192KB) 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 | ||
| Config File Format | default_time_zone | ||
| Variable Name | time_zone | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Value Set |
|
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.7, “MySQL Server Time Zone Support”.
| Version Introduced | 5.0.3 | ||||
| Command Line Format | --timed_mutexes | ||||
| Config File Format | timed_mutexes | ||||
| Option Sets Variable | Yes, timed_mutexes | ||||
| Variable Name | timed_mutexes | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Value Set |
|
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. This variable was added in MySQL 5.0.3.
| Command Line Format | --tmp_table_size=# | ||||||
| Config File Format | tmp_table_size | ||||||
| Option Sets Variable | Yes, tmp_table_size | ||||||
| Variable Name | tmp_table_size | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
|
The maximum size of internal in-memory temporary tables. (The
actual limit is determined as the smaller of
max_heap_table_size and
tmp_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.
| Command Line Format | --tmpdir=name | ||
| Config File Format | tmpdir | ||
| Option Sets Variable | Yes, tmpdir | ||
| Variable Name | tmpdir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
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, NetWare, and OS/2.
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. However, if you are using MySQL 4.0.0 or later, you can
set the slave's temporary directory using the
slave_load_tmpdir variable.
In that case, the slave won't use the general
tmpdir value and you can set
tmpdir to a nonpermanent location.
| Command Line Format | --transaction_alloc_block_size=# | ||||||||
| Config 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 | ||||||||
| Value Set |
|
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=# | ||||||||
| Config File Format | transaction_prealloc_size | ||||||||
| Option Sets Variable | Yes, transaction_prealloc_size | ||||||||
| Variable Name | transaction_prealloc_size | ||||||||
| Variable Scope | Both | ||||||||
| Dynamic Variable | Yes | ||||||||
| Value Set |
| ||||||||
| Value Set |
|
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 | ||||||
| Value Set |
|
The default transaction isolation level. Defaults to
REPEATABLE-READ.
This variable is set by the
SET
TRANSACTION ISOLATION LEVEL statement. See
Section 12.4.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 quotes, with the space replaced by a dash. For
example:
SET tx_isolation = 'READ-COMMITTED';
Any unique prefix of a valid value may be used to set the value of this variable.
| Version Introduced | 5.0.2 | ||||
| Command Line Format | --updatable_views_with_limit=# | ||||
| Config 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 | ||||
| Value Set |
|
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.
This variable was added in MySQL 5.0.2.
| Command Line Format | --version |
| Config File Format | version |
| Variable Name | version |
| Variable Scope | Global |
| Dynamic Variable | No |
The version number for the server.
| Command Line Format | --version |
| Config File Format | version |
| Variable Name | version |
| Variable Scope | Global |
| Dynamic Variable | No |
Starting with MySQL 5.0.24, the version number will also
indicate whether the server is a standard release (Community)
or Enterprise release (for example,
5.0.28-enterprise-gpl-nt).
The BDB storage engine version.
The configure script has a
--with-comment option that allows a comment
to be specified when building MySQL. This variable contains
the value of that comment.
For precompiled binaries, this variable will hold the server
version and license information. Starting with MySQL 5.0.24,
version_comment will include
the full server type and license. For community users this
will appear as MySQL Community Edition - Standard
(GPL). For Enterprise users, the version might be
displayed as MySQL Enterprise Server (GPL).
The corresponding license for your MySQL binary is shown in
parentheses. For server compiled from source, the default
value will be the same as that for Community releases.
The type of machine or architecture on which MySQL was built.
| Variable Name | version_compile_os | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Value Set |
|
The type of operating system on which MySQL was built.
| Command Line Format | --wait_timeout=# | ||||||
| Config File Format | wait_timeout | ||||||
| Option Sets Variable | Yes, wait_timeout | ||||||
| Variable Name | wait_timeout | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Value Set |
| ||||||
| Value Set |
|
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 via 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.
MySQL Enterprise Expert use of server system variables is part of the service offered by the MySQL Enterprise Monitor. To subscribe, see http://www.mysql.com/products/enterprise/advisors.html.
Several system variables exist only as session variables. These
cannot be set at server startup but can be assigned values at
runtime using the
SET
statement (except for those that are read only). Most of them are
not displayed by SHOW VARIABLES,
but you can obtain their values using
SELECT. This section describes the
session system variables. For information about setting or
displaying their values, see
Section 5.1.5, “Using System Variables”. For example:
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
The lettercase of these variables does not matter.
The following table lists the system variables that have only session scope:
Table 5.3. mysqld Session System Variable Summary
| Name | Cmd-Line | Option file | System Var | Dynamic |
|---|---|---|---|---|
| autocommit | Yes | Yes | ||
| big-tables | Yes | Yes | ||
| - Variable: big_tables | Yes | Yes | ||
| error_count | Yes | No | ||
| foreign_key_checks | Yes | Yes | ||
| identity | Yes | Yes | ||
| insert_id | Yes | Yes | ||
| last_insert_id | Yes | Yes | ||
| profiling | Yes | Yes | ||
| rand_seed1 | Yes | Yes | ||
| rand_seed2 | Yes | Yes | ||
| sql_auto_is_null | Yes | Yes | ||
| sql_big_tables | Yes | Yes | ||
| sql_buffer_result | Yes | Yes | ||
| sql_log_bin | Yes | Yes | ||
| sql_log_off | Yes | Yes | ||
| sql_log_update | Yes | Yes | ||
| sql_notes | Yes | Yes | ||
| sql_quote_show_create | Yes | Yes | ||
| sql_safe_updates | Yes | Yes | ||
| sql_warnings | Yes | Yes | ||
| timestamp | Yes | Yes | ||
| unique_checks | Yes | Yes | ||
| warning_count | Yes | No |
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. By default, client connections begin with
autocommit set to 1. If you
change autocommit mode from 0
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.4.1, “START TRANSACTION,
COMMIT, and
ROLLBACK Syntax”.
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.
The number of errors that resulted from the last statement
that generated messages. This variable is read only. See
Section 12.5.5.14, “SHOW ERRORS Syntax”.
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.2.4.4, “FOREIGN KEY Constraints”.
Setting foreign_key_checks to
0 also affects data definition statements:
DROP DATABASE drops a database
even if it contains tables that have foreign keys that are
referred to by tables outside the database, 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.
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.
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.
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.
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.5.5.29, “SHOW PROFILES Syntax”. This variable was added in
MySQL 5.0.37. Note: This option does not
apply to MySQL Enterprise Server users.
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.5.5.29, “SHOW PROFILES Syntax”. This variable was added in
MySQL 5.0.37. Note: This option does not
apply to MySQL Enterprise Server users.
The rand_seed1 and
rand_seed2 variables exist as
session variables only, and can be set but not read. They are
not 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.
If set to 1 (the default), you can find the last inserted row
for a table that contains an AUTO_INCREMENT
column by using the following construct:
WHERE auto_increment_column IS NULL
This behavior is used by some ODBC programs, such as Access.
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 allows 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.
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.
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.
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.
This variable is deprecated, and is mapped to
sql_log_bin.
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.
sql_notes was added in MySQL
5.0.3.
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.5.5.9, “SHOW CREATE TABLE Syntax”,
and Section 12.5.5.6, “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.
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.
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.
If set to 1 (the default), uniqueness checks for secondary
indexes in InnoDB tables are performed. If
set to 0, storage engines are allowed 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 allowed to check for them and issue duplicate-key errors if it detects them.
The number of errors, warnings, and notes that resulted from
the last statement that generated messages. This variable is
read only. See Section 12.5.5.37, “SHOW WARNINGS Syntax”.
The MySQL server maintains many system variables that indicate how
it is configured. Section 5.1.3, “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 | / |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 32768 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| 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 | /usr/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_awe_mem_mb | 0 |
| 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.0.19 |
| version_comment | MySQL Community Edition - (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| 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.0 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.4.5, “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, “Session 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.
| Variable Name | Variable Type | Variable Scope |
|---|---|---|
auto_increment_increment | numeric | GLOBAL | SESSION |
auto_increment_offset | numeric | GLOBAL | SESSION |
autocommit | boolean | SESSION |
automatic_sp_privileges | boolean | GLOBAL |
big_tables | boolean | SESSION |
binlog_cache_size | numeric | GLOBAL |
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 |
date_format | string | GLOBAL | SESSION |
datetime_format | string | GLOBAL | SESSION |
debug | string | 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 |
expire_logs_days | numeric | GLOBAL |
flush | boolean | GLOBAL |
flush_time | numeric | GLOBAL |
foreign_key_checks | boolean | SESSION |
ft_boolean_syntax | string | GLOBAL |
group_concat_max_len | numeric | GLOBAL | SESSION |
identity | numeric | SESSION |
init_connect | string | GLOBAL |
init_slave | string | GLOBAL |
innodb_autoextend_increment | numeric | GLOBAL |
innodb_commit_concurrency | numeric | GLOBAL |
innodb_concurrency_tickets | numeric | GLOBAL |
innodb_fast_shutdown | boolean | GLOBAL |
innodb_flush_log_at_trx_commit | numeric | GLOBAL |
innodb_max_dirty_pages_pct | numeric | GLOBAL |
innodb_max_purge_lag | numeric | GLOBAL |
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 |
innodb_use_legacy_cardinality_algorithm | boolean | 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_time_names | string | GLOBAL | SESSION |
local_infile | GLOBAL | |
log_bin_trust_function_creators | boolean | GLOBAL |
log_bin_trust_routine_creators | boolean | GLOBAL |
log_queries_not_using_indexes | boolean | GLOBAL |
log_warnings | numeric | GLOBAL | SESSION |
long_query_time | numeric | GLOBAL | SESSION |
low_priority_updates | boolean | GLOBAL | SESSION |
max_allowed_packet | numeric | GLOBAL | SESSION |
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 |
multi_range_count | 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 |
ndb_autoincrement_prefetch_sz | numeric | GLOBAL | SESSION |
ndb_cache_check_time | numeric | GLOBAL |
ndb_force_send | boolean | GLOBAL | SESSION |
ndb_use_exact_count | boolean | 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_passwords | boolean | GLOBAL | SESSION |
optimizer_prune_level | boolean | GLOBAL | SESSION |
optimizer_search_depth | numeric | GLOBAL | SESSION |
preload_buffer_size | numeric | GLOBAL | SESSION |
profiling | boolean | SESSION |
profiling_history_size | numeric | GLOBAL | SESSION |
pseudo_thread_id | numeric | GLOBAL | 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 |
rpl_recovery_rank | numeric | GLOBAL |
safe_show_database | boolean | GLOBAL |
secure_auth | boolean | GLOBAL |
server_id | numeric | GLOBAL |
slave_compressed_protocol | boolean | GLOBAL |
slave_net_timeout | numeric | GLOBAL |
slave_transaction_retries | numeric | GLOBAL |
slow_launch_time | numeric | GLOBAL |
sort_buffer_size | numeric | GLOBAL | SESSION |
sql_auto_is_null | boolean | SESSION |
sql_big_selects | boolean | GLOBAL | SESSION |
sql_big_tables | boolean | SESSION |
sql_buffer_result | boolean | SESSION |
sql_log_bin | boolean | SESSION |
sql_log_off | boolean | 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 | SESSION |
sql_quote_show_create | boolean | SESSION |
sql_safe_updates | boolean | SESSION |
sql_select_limit | numeric | GLOBAL | SESSION |
sql_slave_skip_counter | numeric | GLOBAL |
sql_warnings | boolean | SESSION |
storage_engine | enumeration | GLOBAL | SESSION |
sync_binlog | numeric | GLOBAL |
sync_frm | boolean | GLOBAL |
table_lock_wait_timeout | numeric | GLOBAL |
table_open_cache | numeric | GLOBAL |
table_type | enumeration | GLOBAL | SESSION |
thread_cache_size | numeric | GLOBAL |
time_format | string | GLOBAL | SESSION |
time_zone | string | GLOBAL | SESSION |
timed_mutexes | boolean | GLOBAL |
timestamp | string | 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 | SESSION |
updatable_views_with_limit | boolean | GLOBAL | SESSION |
wait_timeout | numeric | GLOBAL | SESSION |
MySQL Enterprise Improper configuration of system variables can adversely affect performance and security. The MySQL Enterprise Monitor continually monitors system variables and provides expert advice about appropriate settings. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
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.5.5.32, “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:
| 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_event | numeric | GLOBAL | SESSION |
Com_alter_table | numeric | GLOBAL | SESSION |
Com_analyze | numeric | GLOBAL | SESSION |
Com_backup_table | numeric | GLOBAL | SESSION |
Com_begin | 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_table | numeric | GLOBAL | SESSION |
Com_create_user | 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_table | numeric | GLOBAL | SESSION |
Com_drop_user | 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_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_rename_table | 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_restore_table | numeric | GLOBAL | SESSION |
Com_revoke | numeric | GLOBAL | SESSION |
Com_revoke_all | numeric | GLOBAL | SESSION |
Com_rollback | numeric | GLOBAL | SESSION |
Com_savepoint | numeric | GLOBAL | SESSION |
Com_select | numeric | GLOBAL | SESSION |
Com_set_option | 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 | numeric | GLOBAL | SESSION |
Com_show_column_types | numeric | GLOBAL | SESSION |
Com_show_create_db | numeric | GLOBAL | SESSION |
Com_show_create_event | numeric | GLOBAL | SESSION |
Com_show_create_table | numeric | GLOBAL | SESSION |
Com_show_databases | numeric | GLOBAL | SESSION |
Com_show_engine_logs | numeric | GLOBAL | SESSION |
Com_show_engine_mutex | numeric | GLOBAL | SESSION |
Com_show_engine_status | numeric | GLOBAL | SESSION |
Com_show_errors | numeric | GLOBAL | SESSION |
Com_show_events | numeric | GLOBAL | SESSION |
Com_show_fields | numeric | GLOBAL | SESSION |
Com_show_grants | numeric | GLOBAL | SESSION |
Com_show_innodb_status | numeric | GLOBAL | SESSION |
Com_show_keys | numeric | GLOBAL | SESSION |
Com_show_logs | numeric | GLOBAL | SESSION |
Com_show_master_status | numeric | GLOBAL | SESSION |
Com_show_ndb_status | numeric | GLOBAL | SESSION |
Com_show_new_master | numeric | GLOBAL | SESSION |
Com_show_open_tables | numeric | GLOBAL | SESSION |
Com_show_plugins | numeric | GLOBAL | SESSION |
Com_show_privileges | numeric | GLOBAL | SESSION |
Com_show_processlist | numeric | GLOBAL | SESSION |
Com_show_slave_hosts | numeric | GLOBAL | SESSION |
Com_show_slave_status | numeric | GLOBAL | SESSION |
Com_show_status | numeric | GLOBAL | SESSION |
Com_show_storage_engines | numeric | GLOBAL | SESSION |
Com_show_tables | numeric | GLOBAL | SESSION |
Com_show_triggers | numeric | GLOBAL | SESSION |
Com_show_variables | numeric | GLOBAL | SESSION |
Com_show_warnings | numeric | GLOBAL | SESSION |
Com_slave_start | numeric | GLOBAL | SESSION |
Com_slave_stop | numeric | GLOBAL | SESSION |
Com_stmt_close | numeric | GLOBAL | SESSION |
Com_stmt_execute | numeric | GLOBAL | SESSION |
Com_stmt_fetch | numeric | GLOBAL | SESSION |
Com_stmt_prepare | numeric | GLOBAL | SESSION |
Com_stmt_reset | numeric | GLOBAL | SESSION |
Com_stmt_send_long_data | numeric | GLOBAL | SESSION |
Com_truncate | numeric | GLOBAL | SESSION |
Com_unlock_tables | numeric | GLOBAL | SESSION |
Com_update | numeric | GLOBAL | SESSION |
Com_update_multi | numeric | GLOBAL | SESSION |
Com_xa_commit | numeric | GLOBAL | SESSION |
Com_xa_end | numeric | GLOBAL | SESSION |
Com_xa_prepare | numeric | GLOBAL | SESSION |
Com_xa_recover | numeric | GLOBAL | SESSION |
Com_xa_rollback | numeric | GLOBAL | SESSION |
Com_xa_start | numeric | GLOBAL | SESSION |
Compression | numeric | SESSION |
Connections | numeric | GLOBAL |
Created_tmp_disk_tables | numeric | GLOBAL | SESSION |
Created_tmp_files | numeric | GLOBAL |
Created_tmp_tables | numeric | GLOBAL | SESSION |
Delayed_errors | numeric | GLOBAL |
Delayed_insert_threads | numeric | GLOBAL |
Delayed_writes | numeric | GLOBAL |
Flush_commands | numeric | GLOBAL |
Handler_commit | numeric | GLOBAL | SESSION |
Handler_delete | numeric | GLOBAL | SESSION |
Handler_discover | numeric | GLOBAL | SESSION |
Handler_prepare | numeric | GLOBAL | SESSION |
Handler_read_first | numeric | GLOBAL | SESSION |
Handler_read_key | numeric | GLOBAL | SESSION |
Handler_read_next | numeric | GLOBAL | SESSION |
Handler_read_prev | numeric | GLOBAL | SESSION |
Handler_read_rnd | numeric | GLOBAL | SESSION |
Handler_read_rnd_next | numeric | GLOBAL | SESSION |
Handler_rollback | numeric | GLOBAL | SESSION |
Handler_savepoint | numeric | GLOBAL | SESSION |
Handler_savepoint_rollback | numeric | GLOBAL | SESSION |
Handler_update | numeric | GLOBAL | SESSION |
Handler_write | numeric | GLOBAL | SESSION |
Innodb_buffer_pool_pages_data | numeric | GLOBAL |
Innodb_buffer_pool_pages_dirty | numeric | GLOBAL |
Innodb_buffer_pool_pages_flushed | numeric | GLOBAL |
Innodb_buffer_pool_pages_free | numeric | GLOBAL |
Innodb_buffer_pool_pages_latched | numeric | GLOBAL |
Innodb_buffer_pool_pages_misc | numeric | GLOBAL |
Innodb_buffer_pool_pages_total | numeric | GLOBAL |
Innodb_buffer_pool_read_ahead_rnd | numeric | GLOBAL |
Innodb_buffer_pool_read_ahead_seq | numeric | GLOBAL |
Innodb_buffer_pool_read_requests | numeric | GLOBAL |
Innodb_buffer_pool_reads | numeric | GLOBAL |
Innodb_buffer_pool_wait_free | numeric | GLOBAL |
Innodb_buffer_pool_write_requests | numeric | GLOBAL |
Innodb_data_fsyncs | numeric | GLOBAL |
Innodb_data_pending_fsyncs | numeric | GLOBAL |
Innodb_data_pending_reads | numeric | GLOBAL |
Innodb_data_pending_writes | numeric | GLOBAL |
Innodb_data_read | numeric | GLOBAL |
Innodb_data_reads | numeric | GLOBAL |
Innodb_data_writes | numeric | GLOBAL |
Innodb_data_written | numeric | GLOBAL |
Innodb_dblwr_pages_written | numeric | GLOBAL |
Innodb_dblwr_writes | numeric | GLOBAL |
Innodb_log_waits | numeric | GLOBAL |
Innodb_log_write_requests | numeric | GLOBAL |
Innodb_log_writes | numeric | GLOBAL |
Innodb_os_log_fsyncs | numeric | GLOBAL |
Innodb_os_log_pending_fsyncs | numeric | GLOBAL |
Innodb_os_log_pending_writes | numeric | GLOBAL |
Innodb_os_log_written | numeric | GLOBAL |
Innodb_page_size | numeric | GLOBAL |
Innodb_pages_created | numeric | GLOBAL |
Innodb_pages_read | numeric | GLOBAL |
Innodb_pages_written | numeric | GLOBAL |
Innodb_row_lock_current_waits | numeric | GLOBAL |
Innodb_row_lock_time | numeric | GLOBAL |
Innodb_row_lock_time_avg | numeric | GLOBAL |
Innodb_row_lock_time_max | numeric | GLOBAL |
Innodb_row_lock_waits | numeric | GLOBAL |
Innodb_rows_deleted | numeric | GLOBAL |
Innodb_rows_inserted | numeric | GLOBAL |
Innodb_rows_read | numeric | GLOBAL |
Innodb_rows_updated | numeric | GLOBAL |
Key_blocks_not_flushed | numeric | GLOBAL |
Key_blocks_unused | numeric | GLOBAL |
Key_blocks_used | numeric | GLOBAL |
Key_read_requests | numeric | GLOBAL |
Key_reads | numeric | GLOBAL |
Key_write_requests | numeric | GLOBAL |
Key_writes | numeric | GLOBAL |
Last_query_cost | numeric | SESSION |
Max_used_connections | numeric | GLOBAL |
Ndb_cluster_node_id | numeric | GLOBAL | SESSION |
Ndb_config_from_host | numeric | GLOBAL | SESSION |
Ndb_config_from_port | numeric | GLOBAL | SESSION |
ndb-nodeid | numeric | GLOBAL |
Not_flushed_delayed_rows | numeric | GLOBAL |
Open_files | numeric | GLOBAL |
Open_streams | numeric | GLOBAL |
Open_tables | numeric | GLOBAL | SESSION |
Opened_tables | numeric | GLOBAL | SESSION |
Prepared_stmt_count | numeric | GLOBAL |
Qcache_free_blocks | numeric | GLOBAL |
Qcache_free_memory | numeric | GLOBAL |
Qcache_hits | numeric | GLOBAL |
Qcache_inserts | numeric | GLOBAL |
Qcache_lowmem_prunes | numeric | GLOBAL |
Qcache_not_cached | numeric | GLOBAL |
Qcache_queries_in_cache | numeric | GLOBAL |
Qcache_total_blocks | numeric | GLOBAL |
Queries | numeric | GLOBAL | SESSION |
Questions | numeric | GLOBAL | SESSION |
Rpl_status | string | GLOBAL |
Select_full_join | numeric | GLOBAL | SESSION |
Select_full_range_join | numeric | GLOBAL | SESSION |
Select_range | numeric | GLOBAL | SESSION |
Select_range_check | numeric | GLOBAL | SESSION |
Select_scan | numeric | GLOBAL | SESSION |
Slave_open_temp_tables | numeric | GLOBAL |
Slave_retried_transactions | numeric | GLOBAL |
Slave_running | boolean | GLOBAL |
Slow_launch_threads | numeric | GLOBAL | SESSION |
Slow_queries | numeric | GLOBAL | SESSION |
Sort_merge_passes | numeric | GLOBAL | SESSION |
Sort_range | numeric | GLOBAL | SESSION |
Sort_rows | numeric | GLOBAL | SESSION |
Sort_scan | numeric | GLOBAL | SESSION |
Ssl_accept_renegotiates | numeric | GLOBAL |
Ssl_accepts | numeric | GLOBAL |
Ssl_callback_cache_hits | numeric | GLOBAL |
Ssl_cipher | string | GLOBAL | SESSION |
Ssl_cipher_list | string | GLOBAL | SESSION |
Ssl_client_connects | numeric | GLOBAL |
Ssl_connect_renegotiates | numeric | GLOBAL |
Ssl_ctx_verify_depth | numeric | GLOBAL |
Ssl_ctx_verify_mode | numeric | GLOBAL |
Ssl_default_timeout | numeric | GLOBAL | SESSION |
Ssl_finished_accepts | numeric | GLOBAL |
Ssl_finished_connects | numeric | GLOBAL |
Ssl_session_cache_hits | numeric | GLOBAL |
Ssl_session_cache_misses | numeric | GLOBAL |
Ssl_session_cache_mode | string | GLOBAL |
Ssl_session_cache_overflows | numeric | GLOBAL |
Ssl_session_cache_size | numeric | GLOBAL |
Ssl_session_cache_timeouts | numeric | GLOBAL |
Ssl_sessions_reused | numeric | GLOBAL | SESSION |
Ssl_used_session_cache_entries | numeric | GLOBAL |
Ssl_verify_depth | numeric | GLOBAL | SESSION |
Ssl_verify_mode | numeric | GLOBAL | SESSION |
Ssl_version | string | GLOBAL | SESSION |
Table_locks_immediate | numeric | GLOBAL |
Table_locks_waited | numeric | GLOBAL |
Tc_log_max_pages_used | numeric | GLOBAL |
Tc_log_page_size | numeric | GLOBAL |
Tc_log_page_waits | numeric | GLOBAL |
Threads_cached | numeric | GLOBAL |
Threads_connected | numeric | GLOBAL |
Threads_created | numeric | GLOBAL |
Threads_running | numeric | GLOBAL |
Uptime | numeric | GLOBAL |
Uptime_since_flush_status | numeric | GLOBAL |
Before MySQL 5.0.2, SHOW STATUS
returned global status values. Because the default as of 5.0.2
is to return session values, this is incompatible with previous
versions. To issue a SHOW STATUS
statement that will retrieve global status values for all
versions of MySQL, write it like this:
SHOW /*!50002 GLOBAL */ STATUS;
Many status variables are reset to 0 by the FLUSH
STATUS statement.
MySQL Enterprise For expert advice on using status variables, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
The status variables have the following meanings. Variables with no version indicated were already present prior to MySQL 5.0. For information regarding their implementation history, see MySQL 3.23, 4.0, 4.1 Reference Manual.
For meanings of status variables specific to MySQL Cluster, see Section 17.4.4, “MySQL Cluster Status Variables”.
The number of connections that were aborted because the client died without closing the connection properly. See Section B.1.2.11, “Communication Errors and Aborted Connections”.
The number of failed attempts to connect to the MySQL server. See Section B.1.2.11, “Communication Errors and Aborted Connections”.
The number of transactions that used the temporary binary log
cache but that exceeded the value of
binlog_cache_size and used a
temporary file to store statements from the transaction.
The number of transactions that used the temporary binary log cache.
The number of bytes received from all clients.
The number of bytes sent to all clients.
The Com_
statement counter variables indicate the number of times each
xxxxxx statement has been executed.
There is one status variable for each type of statement. For
example, Com_delete and
Com_insert count
DELETE and
INSERT statements,
respectively. However, if a query result is returned from
query cache, the server increments the
Qcache_hits status variable,
not Com_select. See
Section 7.5.5.4, “Query Cache Status and Maintenance”.
All of the
Com_stmt_
variables are increased even if a prepared statement argument
is unknown or an error occurred during execution. In other
words, their values correspond to the number of requests
issued, not to the number of requests successfully completed.
xxx
The Com_stmt_
status variables were added in 5.0.8:
xxx
Com_stmt_prepare
Com_stmt_execute
Com_stmt_fetch
Com_stmt_send_long_data
Com_stmt_reset
Com_stmt_close
Those variables stand for prepared statement commands. Their
names refer to the
COM_ command
set used in the network layer. In other words, their values
increase whenever prepared statement API calls such as
mysql_stmt_prepare(),
mysql_stmt_execute(), and so forth are
executed. However, xxxCom_stmt_prepare,
Com_stmt_execute and
Com_stmt_close also increase for
PREPARE,
EXECUTE, or
DEALLOCATE PREPARE,
respectively. Additionally, the values of the older (available
since MySQL 4.1.3) statement counter variables
Com_prepare_sql,
Com_execute_sql, and
Com_dealloc_sql increase for the
PREPARE,
EXECUTE, and
DEALLOCATE PREPARE statements.
Com_stmt_fetch stands for the total number
of network round-trips issued when fetching from cursors.
Whether the client connection uses compression in the client/server protocol. Added in MySQL 5.0.16.
The number of connection attempts (successful or not) to the MySQL server.
The number of temporary tables on disk created automatically by the server while executing statements.
How many temporary files mysqld has created.
The number of in-memory temporary tables created automatically
by the server while executing statements. If
Created_tmp_disk_tables is
large, you may want to increase the
tmp_table_size value to cause
temporary tables to be memory-based instead of disk-based.
The number of rows written with INSERT
DELAYED for which some error occurred (probably
duplicate key).
The number of INSERT DELAYED
handler threads in use.
The number of INSERT DELAYED
rows written.
The number of executed FLUSH
statements.
The number of internal COMMIT
statements.
The number of times that rows have been deleted from tables.
A counter for the prepare phase of two-phase commit operations. Added in MySQL 5.0.3.
The number of times the first entry was read from an index. If
this value is high, it suggests that the server is doing a lot
of full index scans; for example, SELECT col1 FROM
foo, assuming that col1 is
indexed.
The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.
The number of requests to read the next row in key order. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan.
The number of requests to read the previous row in key order.
This read method is mainly used to optimize ORDER BY
... DESC.
The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that don't use keys properly.
The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
The number of requests for a storage engine to perform a rollback operation.
The number of requests for a storage engine to place a savepoint. Added in MySQL 5.0.3.
The number of requests for a storage engine to roll back to a savepoint. Added in MySQL 5.0.3.
The number of requests to update a row in a table.
The number of requests to insert a row in a table.
The number of pages containing data (dirty or clean). Added in MySQL 5.0.2.
Innodb_buffer_pool_pages_dirty
The number of pages currently dirty. Added in MySQL 5.0.2.
Innodb_buffer_pool_pages_flushed
The number of buffer pool page-flush requests. Added in MySQL 5.0.2.
The number of free pages. Added in MySQL 5.0.2.
Innodb_buffer_pool_pages_latched
The number of latched pages in InnoDB
buffer pool. These are pages currently being read or written
or that cannot be flushed or removed for some other reason.
Added in MySQL 5.0.2. Calculation of this variable is
expensive, so as of MySQL 5.0.68, it is available only when
the UNIV_DEBUG system is defined at server
build time.
The number of pages that are busy because they have been
allocated for administrative overhead such as row locks or the
adaptive hash index. This value can also be calculated as
Innodb_buffer_pool_pages_total
–
Innodb_buffer_pool_pages_free
–
Innodb_buffer_pool_pages_data.
Added in MySQL 5.0.2.
Innodb_buffer_pool_pages_total
The total size of buffer pool, in pages. Added in MySQL 5.0.2.
Innodb_buffer_pool_read_ahead_rnd
The number of “random” read-aheads initiated by
InnoDB. This happens when a query scans a
large portion of a table but in random order. Added in MySQL
5.0.2.
Innodb_buffer_pool_read_ahead_seq
The number of sequential read-aheads initiated by
InnoDB. This happens when
InnoDB does a sequential full table scan.
Added in MySQL 5.0.2.
Innodb_buffer_pool_read_requests
The number of logical read requests InnoDB
has done. Added in MySQL 5.0.2.
The number of logical reads that InnoDB
could not satisfy from the buffer pool and had to do a
single-page read. Added in MySQL 5.0.2.
Normally, writes to the InnoDB buffer pool
happen in the background. However, if it is necessary to read
or create a page and no clean pages are available, it is also
necessary to wait for pages to be flushed first. This counter
counts instances of these waits. If the buffer pool size has
been set properly, this value should be small. Added in MySQL
5.0.2.
Innodb_buffer_pool_write_requests
The number writes done to the InnoDB buffer
pool. Added in MySQL 5.0.2.
The number of fsync() operations so far.
Added in MySQL 5.0.2.
The current number of pending fsync()
operations. Added in MySQL 5.0.2.
The current number of pending reads. Added in MySQL 5.0.2.
The current number of pending writes. Added in MySQL 5.0.2.
The amount of data read so far, in bytes. Added in MySQL 5.0.2.
The total number of data reads. Added in MySQL 5.0.2.
The total number of data writes. Added in MySQL 5.0.2.
The amount of data written so far, in bytes. Added in MySQL 5.0.2.
The number of pages that have been written for doublewrite
operations. Added in MySQL 5.0.2. See
Section 13.2.11.1, “InnoDB Disk I/O”.
The number of doublewrite operations that have been performed.
Added in MySQL 5.0.2. See Section 13.2.11.1, “InnoDB Disk I/O”.
The number of times that the log buffer was too small and a wait was required for it to be flushed before continuing. Added in MySQL 5.0.2.
The number of log write requests. Added in MySQL 5.0.2.
The number of physical writes to the log file. Added in MySQL 5.0.2.
The number of fsync() writes done to the
log file. Added in MySQL 5.0.2.
The number of pending log file fsync()
operations. Added in MySQL 5.0.2.
The number of pending log file writes. Added in MySQL 5.0.2.
The number of bytes written to the log file. Added in MySQL 5.0.2.
The compiled-in InnoDB page size (default
16KB). Many values are counted in pages; the page size allows
them to be easily converted to bytes. Added in MySQL 5.0.2.
The number of pages created. Added in MySQL 5.0.2.
The number of pages read. Added in MySQL 5.0.2.
The number of pages written. Added in MySQL 5.0.2.
The number of row locks currently being waited for. Added in MySQL 5.0.3.
The total time spent in acquiring row locks, in milliseconds. Added in MySQL 5.0.3.
The average time to acquire a row lock, in milliseconds. Added in MySQL 5.0.3.
The maximum time to acquire a row lock, in milliseconds. Added in MySQL 5.0.3.
The number of times a row lock had to be waited for. Added in MySQL 5.0.3.
The number of rows deleted from InnoDB
tables. Added in MySQL 5.0.2.
The number of rows inserted into InnoDB
tables. Added in MySQL 5.0.2.
The number of rows read from InnoDB tables.
Added in MySQL 5.0.2.
The number of rows updated in InnoDB
tables. Added in MySQL 5.0.2.
The number of key blocks in the key cache that have changed but have not yet been flushed to disk.
The number of unused blocks in the key cache. You can use this
value to determine how much of the key cache is in use; see
the discussion of
key_buffer_size in
Section 5.1.3, “Server System Variables”.
The number of used blocks in the key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time.
The number of requests to read a key block from the cache.
The number of physical reads of a key block from disk. If
Key_reads is large, then
your key_buffer_size value is
probably too small. The cache miss rate can be calculated as
Key_reads/Key_read_requests.
The number of requests to write a key block to the cache.
The number of physical writes of a key block to disk.
The total cost of the last compiled query as computed by the
query optimizer. This is useful for comparing the cost of
different query plans for the same query. The default value of
0 means that no query has been compiled yet. This variable was
added in MySQL 5.0.1, with a default value of -1. In MySQL
5.0.7, the default was changed to 0; also in version 5.0.7,
the scope of Last_query_cost
was changed to session rather than global.
The Last_query_cost value
can be computed accurately only for simple “flat”
queries, not complex queries such as those with subqueries or
UNION. For the latter, the
value is set to 0.
Prior to MySQL 5.0.16, this variable was not updated for queries served from the query cache.
The maximum number of connections that have been in use simultaneously since the server started.
The number of rows waiting to be written in INSERT
DELAY queues.
The number of files that are open. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes. Also, the count does not include files that storage engines open using their own internal functions rather than asking the server level to do so.
The number of streams that are open (used mainly for logging).
The number of tables that are open.
The number of tables that have been opened. If
Opened_tables is big, your
table_cache value is probably
too small.
The current number of prepared statements. (The maximum number
of statements is given by the
max_prepared_stmt_count
system variable.) This variable was added in MySQL 5.0.32.