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.17, “SHOW ENGINES Syntax”.
The following table provides a list of all the command line
options, server and status variables applicable within
mysqld.
The table lists command-line options (Cmd-line), options valid in configuration files (Option file), server system variables (System Var), and status variables (Status var) in one unified list, with notification of where each option/variable is valid. If a server option set on the command line or in an option file differs from the name of the corresponding server system or status variable, the variable name is noted immediately below the corresponding option. For status variables, the scope of the variable is shown (Scope) as either global, session, or both. Please see the corresponding sections for details on setting and using the options and variables. Where appropriate, a direct link to further information on the item as available.
Table 5.1. Option/Variable Summary
When you start the mysqld server, you can specify program options using any of the methods described in Section 4.2.3, “Specifying Program Options”. The most common methods are to provide options in an option file or on the command line. However, in most cases it is desirable to make sure that the server uses the same options each time it runs. The best way to ensure this is to list them in an option file. See Section 4.2.3.3, “Using Option Files”.
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 5.2.4, “The Binary Log”.
Replication-related options: See Section 16.1.3, “Replication and Binary Logging Options and Variables”.
Options for loading plugins such as pluggable storage engines: See Section 5.1.3, “Server Options for Loading Plugins”.
Options specific to particular storage engines: See
Section 13.5.1, “MyISAM Startup Options”,
Section 13.6.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.
| Command Line Format | --allow-suspicious-udfs | ||||
| Config File Format | allow-suspicious-udfs | ||||
| Permitted Values |
|
This option controls whether user-defined functions that have
only an xxx symbol for the main function
can be loaded. By default, the option is off and only UDFs
that have at least one auxiliary symbol can be loaded; this
prevents attempts at loading functions from shared object
files other than those containing legitimate UDFs. See
Section 22.3.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.8, “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 | ||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
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 | ||||||
| Permitted Values |
|
The IP address to bind to. Only one address can be selected. If this option is specified multiple times, the last address given is used.
If no address or 0.0.0.0 is specified, the
server listens on all interfaces.
--binlog-format={ROW|STATEMENT|MIXED}
| Version Introduced | 5.1.5 | ||||||
| Command Line Format | --binlog-format | ||||||
| Config File Format | binlog-format | ||||||
| Option Sets Variable | Yes, binlog_format | ||||||
| Variable Name | binlog_format | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Permitted Values (>= 5.1.5, <= 5.1.7) |
| ||||||
| Permitted Values (>= 5.1.8, <= 5.1.11) |
| ||||||
| Permitted Values (>= 5.1.12, <= 5.1.28) |
| ||||||
| Permitted Values (>= 5.1.29) |
|
Specify whether to use row-based, statement-based, or mixed replication (statement-based was the default prior to MySQL 5.1.12; in 5.1.12, the default was changed to mixed replication; in 5.1.29, the default was changed back to statement-based). See Section 16.1.2, “Replication Formats”. This option was added in MySQL 5.1.5.
Setting the binary logging format without enabling binary logging prevents the MySQL server from starting. This is a known issue in MySQL 5.1 which is fixed in MySQL 5.4. (Bug#42928)
MySQL Cluster.
The default value for this option in all MySQL Cluster NDB
6.1, 6.2, 6.3, and later 6.x releases is
MIXED. See
Section 17.9.2, “MySQL Cluster Replication — Assumptions and General Requirements”, for
more information.
| 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.10.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 | ||
| Permitted Values |
|
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 | ||||
| Permitted Values |
|
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.1.6 | ||
| 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 | ||
| Permitted Values |
|
The file system character set. This option sets the
character_set_filesystem
system variable. It was added in MySQL 5.1.6.
--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 | ||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
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 | ||||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
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 | ||||
| Permitted Values |
|
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.1.12, 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.
This option may be given multiple times. Values that begin
with + or - are added to
or subtracted from the previous value. For example,
--debug=T
--debug=+P sets the value to
P:T.
--default-character-set=
(DEPRECATED)
charset_name
| Command Line Format | --default-character-set=name | ||
| Config File Format | default-character-set | ||
| Deprecated | 5.0 | ||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
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.
| Command Line Format | --default-table-type=name | ||
| Config File Format | default-table-type | ||
| Deprecated | 5.0, by default-storage-engine | ||
| Permitted Values |
|
This option is a deprecated synonym for
--default-storage-engine.
| Command Line Format | --default-time-zone=name | ||
| Config File Format | default-time-zone | ||
| Permitted Values |
|
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 | ||||||
| Permitted Values |
|
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.5.1, “MyISAM Startup Options”.
If you set this variable to ALL, you
should not use MyISAM tables from within
another program (such as another MySQL server or
myisamchk) when the tables are in use.
Doing so leads to index corruption.
| Command Line Format | --des-key-file=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 applies only on
Windows NT, 2000, XP, and 2003 systems. For MySQL 5.1.20 and
earlier, this option is available only when using the
mysqld-nt and
mysqld-debug servers that support
named-pipe connections. For MySQL 5.1.21 and later,
mysqld-nt is not available, but support is
included in the standard mysqld and
mysqld-debug servers.
| Command Line Format | --enable-pstack | ||||
| Config File Format | enable-pstack | ||||
| Permitted Values |
|
Print a symbolic stack trace on failure.
--engine-condition-pushdown={ON|OFF}
| 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 | ||||
| Permitted Values (>= 5.1.0) |
|
Sets the
engine_condition_pushdown
system variable. For more information, see
Section 7.2.7, “Condition Pushdown Optimization”.
| Version Introduced | 5.1.6 | ||||||
| Command Line Format | --event-scheduler[=value] | ||||||
| Config File Format | event-scheduler | ||||||
| Option Sets Variable | Yes, event_scheduler | ||||||
| Variable Name | event_scheduler | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Permitted Values |
|
Enable or disable, and start or stop, the event scheduler. This option was added in MySQL 5.1.6. Note that its permitted values and behavior changed in MySQL 5.1.11, and again in MySQL 5.1.12.
For detailed information, see
The
event-scheduler Option.
--exit-info[=,
flags]-T [
flags]
| Command Line Format | --exit-info[=flags] | ||
| Config File Format | exit-info | ||
| Permitted Values |
|
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 | ||||
| Permitted Values |
|
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 | ||||
| Permitted Values |
|
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 | ||||
| Permitted Values |
|
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.
| Version Introduced | 5.1.12 | ||||
| Command Line Format | --general-log | ||||
| Config File Format | general-log | ||||
| Option Sets Variable | Yes, general_log | ||||
| Variable Name | general_log | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Permitted Values |
|
Specify the initial general query log state. With no argument
or an argument of 1, the
--general-log option enables
the log. If omitted or given with an argument of 0, the option
disables the log. This option was added in MySQL 5.1.12.
| 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 | ||
| Permitted Values |
|
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.10.2, “Typical configure Options”.
--innodb-
xxx
The InnoDB options are listed in
Section 13.6.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 | ||||
| Deprecated | 5.4.4, by lc-messages-dir | ||||
| Permitted Values |
|
The language to use for error messages.
lang_name can be given as the
language name or as the full path name to the directory where
the language files are installed. See
Section 9.3, “Setting the Error Message Language”.
| 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 | ||||
| Permitted Values |
|
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.
--log[=,
file_name]-l [
file_name]
| Version Deprecated | 5.1.29 | ||||
| Command Line Format | --log[=name] | ||||
| Config File Format | log | ||||
| Option Sets Variable | Yes, log | ||||
| Variable Name | log | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Deprecated | 5.1.29, by general-log | ||||
| Permitted Values |
|
This option enables logging to the general query log, which
contains entries that record client connections and SQL
statements received from clients. The log output destination
can be selected with the
--log-output option as of MySQL
5.1.6. Before 5.1.6, logging occurs to the general query log
file. If you omit the file name, MySQL uses
host_name.log
As of MySQL 5.1.29, the --log
option is deprecated and will be removed (along with the
log system variable) in MySQL
7.0. Instead, use the
--general_log option to enable
the general query log and the
--general_log_file=
option to set the general query log file name.
file_name
| Command Line Format | --log-error[=name] | ||
| Config File Format | log-error | ||
| Option Sets Variable | Yes, log_error | ||
| Variable Name | log_error | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values |
|
Log errors and startup messages to this file. See
Section 5.2.2, “The Error Log”. If you omit the file name, MySQL
uses
host_name.err.err.
| Command Line Format | --log-isam[=name] | ||
| Config File Format | log-isam | ||
| Permitted Values |
|
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 binary log and slow query log, if
they have been activated. For example, the user name and
timestamp are logged for all queries. This option is
deprecated, as it now represents the default logging behavior.
(See the description for
--log-short-format.) The
--log-queries-not-using-indexes
option is available for the purpose of logging queries that do
not use indexes to the slow query log.
| Version Introduced | 5.1.6 | ||||||
| Command Line Format | --log-output[=name] | ||||||
| Config File Format | log-output | ||||||
| Option Sets Variable | Yes, log_output | ||||||
| Variable Name | log_output | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Permitted Values |
|
This option determines the destination for general query log
and slow query log output. The option value can be given as
one or more of the words TABLE,
FILE, or NONE. If the
option is given without a value, the default is
FILE. (For MySQL 5.1.6 through 5.1.20, the
default is TABLE.) TABLE
select logging to the
general_log and
slow_log tables in the
mysql database as a destination.
FILE selects logging to log files as a
destination. NONE disables logging. If
NONE is present in the option value, it
takes precedence over any other words that are present.
TABLE and FILE can both
be given to select to both log output destinations.
This option selects log output destinations, but does not
enable log output. To do that, use the
--general_log and
--slow_query_log options. For
FILE logging, the
--general_log_file and
-slow_query_log_file options determine the
log file location. (Before MySQL 5.1.29, enable the logs with
the --log and
--log-slow-queries options. The
options take an optional file name argument to specify the log
file name.) For more information, see
Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”.
The --log-output option was
added in MySQL 5.1.6.
--log-queries-not-using-indexes
| Version Deprecated | 5.1.29 | ||
| 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 | ||
| Permitted Values |
|
If you are using this option with the slow query log enabled, queries that are expected to retrieve all rows are logged. See Section 5.2.5, “The Slow Query Log”. This option does not necessarily mean that no index is used. For example, a query that uses a full index scan uses an index but would be logged because the index would not limit the number of rows.
| Command Line Format | --log-short-format | ||||
| Config File Format | log-short-format | ||||
| Permitted Values |
|
Originally intended to log less information to the binary log and slow query log, if they have been activated. However, this option is not operational.
| Command Line Format | --log-slow-admin-statements | ||||
| Config File Format | log-slow-admin-statements | ||||
| Permitted Values |
|
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 | Yes | ||
| Permitted Values |
|
This option enables logging to the slow query log, which
contains entries for all queries that have taken more than
long_query_time seconds to
execute. See the descriptions of the
--log-long-format and
--log-short-format options for
details.
The log output destination can be selected with the
--log-output option as of MySQL
5.1.6. Before 5.1.6, logging occurs to the slow query log
file. If you omit the file name, MySQL uses
host_name-slow.log
As of MySQL 5.1.29, the
--log-slow-queries option is
deprecated and will be removed (along with the
log_slow_queries system
variable) in MySQL 7.0. Instead, use the
--slow_query_log option to
enable the slow query log and the
--slow_query_log_file=
option to set the slow query log file name.
file_name
| Command Line Format | --log-tc=name | ||||
| Config File Format | log-tc | ||||
| Permitted Values |
|
The name of the memory-mapped transaction coordinator log file
(for XA transactions that affect multiple storage engines when
the binary log is disabled). The default name is
tc.log. The file is created under the
data directory if not given as a full path name. Currently,
this option is unused.
| Command Line Format | --log-tc-size=# | ||||||||
| Config File Format | log-tc-size | ||||||||
| Permitted Values |
| ||||||||
| Permitted Values |
|
The size in bytes of the memory-mapped transaction coordinator log. The default size is 24KB.
--log-warnings[=,
level]-W [
level]
| Command Line Format | --log-warnings[=#] | ||||||||
| 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 | ||||||||
| Permitted Values |
|
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. As of MySQL 5.1.38, the server logs messages about
statements that are unsafe for statement-based logging only if
--log-warnings is enabled.
| Command Line Format | --low-priority-updates | ||||
| Config File Format | low-priority-updates | ||||
| Option Sets Variable | Yes, low_priority_updates | ||||
| Variable Name | low_priority_updates | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Permitted Values |
|
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”.
--min-examined-row-limit=
number
| Version Introduced | 5.1.21 | ||||||||
| Command Line Format | --min-examined-row-limit=# | ||||||||
| Config File Format | min-examined-row-limit | ||||||||
| Variable Name | min_examined_row_limit | ||||||||
| Variable Scope | Both | ||||||||
| Dynamic Variable | Yes | ||||||||
| Permitted Values |
| ||||||||
| Permitted Values |
|
When this option is set, queries which examine fewer than
number rows are not written to the
slow query log. The default is 0.
This option was introduced in MySQL 5.1.21.
| Command Line Format | --memlock | ||||
| Config File Format | memlock | ||||
| Variable Name | locked_in_memory | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Permitted Values |
|
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 | ||||||
| Permitted Values |
|
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 | ||||||
| Permitted Values |
|
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-alter-table | ||||
| Config File Format | old-alter-table | ||||
| Option Sets Variable | Yes, old_alter_table | ||||
| Variable Name | old-alter-table | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Permitted Values |
|
When this option is given, the server does not use the
optimized method of processing an ALTER
TABLE operation. It reverts to using a temporary
table, copying over the data, and then renaming the temporary
table to the original, as used by MySQL 5.0 and earlier. For
more information on the operation of
ALTER TABLE, see
Section 12.1.7, “ALTER TABLE Syntax”.
| 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 | ||||
| Permitted Values |
|
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”.
| Command Line Format | --old-style-user-limits | ||||
| Config File Format | old-style-user-limits | ||||
| Permitted Values |
|
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”.
| 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.
As of MySQL 5.1.17, this option is deprecated; use
--thread_handling=no-threads
instead.
| 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 | ||||||
| Permitted Values |
|
Changes the number of file descriptors available to
mysqld. You should try increasing the value
of this option if mysqld gives you the
error Too many open files.
mysqld uses the option value to reserve
descriptors with setrlimit(). If the
requested number of file descriptors cannot be allocated,
mysqld writes a warning to the error log.
mysqld may attempt to allocate more than
the requested number of descriptors (if they are available),
using the values of
max_connections and
table_open_cache to estimate
whether more descriptors will be needed.
| Command Line Format | --pid-file=name | ||
| Config File Format | pid-file | ||
| Option Sets Variable | Yes, pid_file | ||
| Variable Name | pid_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values |
|
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.
| Version Introduced | 5.1.18 | ||
| Command Line Format | --plugin-load=plugin_list | ||
| Config File Format | plugin-load | ||
| Permitted Values |
|
Under normal startup, the server determines which plugins to
load by reading the mysql.plugins system
table. This option enables plugins to be loaded even when
--skip-grant-tables is given
(which tells the server not to read system tables). This
option also enables plugins to be loaded at startup under
configurations when plugins cannot be loaded at runtime. The
option was added in MySQL 5.1.18.
The option value is a semicolon-separated list of
name=plugin_libraryname is the name of
the plugin, and plugin_library is
the name of the shared library that contains the plugin code.
Each library file must be located in the directory named by
the plugin_dir system
variable. For example, if plugins named
myplug1 and myplug2 have
library files myplug1.so and
myplug2.so, use this option to load them
at startup:
shell> mysqld --plugin-load=myplug1=myplug1.so;myplug2=myplug2.so
All plugins to load must be named in the same
--plugin-load option. If
multiple --plugin-load options
are given, only the last one is used.
The plugins are loaded for a single invocation of
mysqld only. After a restart, the plugins
are not loaded unless
--plugin-load is used again.
This is in contrast to INSTALL
PLUGIN, which adds an entry to the
mysql.plugins table to cause the plugin to
be loaded for every normal server startup.
--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 | ||||
| Permitted Values |
|
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.1.5 | ||||
| Command Line Format | --port-open-timeout=# | ||||
| Config File Format | port-open-timeout | ||||
| Permitted Values |
|
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.1.5.
| 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 | ||
| Permitted Values |
|
| Command Line Format | --safe-user-create | ||||
| Config File Format | safe-user-create | ||||
| Permitted Values |
|
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 | ||||
| Permitted Values |
|
Disallow authentication by clients that attempt to use accounts that have old (pre-4.1) passwords.
| Version Introduced | 5.1.17 | ||
| 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 | ||
| Permitted Values |
|
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.1.17.
Enable shared-memory connections by local clients. This option is available only on Windows.
--shared-memory-base-name=
name
The name of shared memory to use for shared-memory
connections. This option is available only on Windows. The
default name is MYSQL. The name is case
sensitive.
Turn off the ability to select and insert at the same time on
MyISAM tables. (This is to be used only if
you think you have found a bug in this feature.) See
Section 7.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 plugins, user-defined
functions (UDFs), and, beginning with MySQL 5.1.17, scheduled
events. To cause plugins to be loaded anyway, use the
--plugin-load option.
--skip-grant-tables is
unavailable if MySQL was configured with the
--disable-grant-options
option. See Section 2.10.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.
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 |
Available on Windows NT-based systems only; 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.
| Version Deprecated | 5.1.29 |
| Command Line Format | --skip-thread-priority |
| Config File Format | skip-thread-priority |
| Deprecated | 5.1.29 |
Disable using thread priorities for faster response time. This option is deprecated as of MySQL 5.1.29.
| Version Introduced | 5.1.12 | ||||
| Command Line Format |
| ||||
| Config File Format |
| ||||
| Option Sets Variable | Yes, slow_query_log | ||||
| Variable Name | slow_query_log | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Permitted Values |
|
Specify the initial slow query log state. With no argument or
an argument of 1, the
--slow-query-log option enables
the log. If omitted or given with an argument of 0, the option
disables the log. This option was added in MySQL 5.1.12.
| Command Line Format | --socket=name | ||||
| Config File Format | socket | ||||
| Option Sets Variable | Yes, socket | ||||
| Variable Name | socket | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | No | ||||
| Permitted Values |
|
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 | ||||||
| Permitted Values |
|
Set the SQL mode. See Section 5.1.8, “Server SQL Modes”.
| Version Introduced | 5.1.8 | ||||
| Command Line Format | --sysdate-is-now | ||||
| Config File Format | sysdate-is-now | ||||
| Permitted Values |
|
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.5, “Session System Variables”.
This option was added in MySQL 5.1.8.
--tc-heuristic-recover={COMMIT|ROLLBACK}
| Command Line Format | --tc-heuristic-recover=name | ||||
| Config File Format | tc-heuristic-recover | ||||
| Permitted Values |
|
The type of decision to use in the heuristic recovery process. Currently, this option is unused.
| Command Line Format | --temp-pool | ||||
| Config File Format | temp-pool | ||||
| Permitted Values |
|
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. As of MySQL 5.1.31, this option is ignored except on Linux.
| Command Line Format | --transaction-isolation=name | ||||
| Config File Format | transaction-isolation | ||||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
Run the mysqld server as the user having
the name user_name or the numeric
user ID user_id.
(“User” in this context refers to a system login
account, not a MySQL user listed in the grant tables.)
This option is mandatory when starting
mysqld as root. The
server changes its user ID during its startup sequence,
causing it to run as that particular user rather than as
root. See
Section 5.3.1, “General Security Guidelines”.
To avoid a possible security hole where a user adds a
--user=root option to a
my.cnf file (thus causing the server to
run as root), mysqld
uses only the first --user
option specified and produces a warning if there are multiple
--user options. Options in
/etc/my.cnf and
$MYSQL_HOME/my.cnf are processed before
command-line options, so it is recommended that you put a
--user option in
/etc/my.cnf and specify a value other
than root. The option in
/etc/my.cnf is found before any other
--user options, which ensures
that the server runs as a user other than
root, and that a warning results if any
other --user option is found.
Use this option with the --help
option for detailed help.
--version, -V
Display version information and exit.
You can assign a value to a server system variable by using an
option of the form
--.
For example, var_name=value--key_buffer_size=32M
sets the key_buffer_size variable
to a value of 32MB.
Note that when you assign a value to a variable, MySQL might automatically correct the value to stay within a given range, or adjust the value to the closest 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
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.4, “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 server can load plugins under the control of startup options.
For example, many storage engines can be built in pluggable form
and loaded when the server starts. In the following descriptions,
plugin_name stands for a plugin name
such as innodb.
As of MySQL 5.1.36, options used to control plugin loading have a tristate format:
--
plugin_name=OFF
Do not enable the plugin.
--
plugin_name[=ON]
Enable the plugin. If plugin initialization fails, start the
server anyway, but with the plugin disabled. Specifying the
option as
--
without a value also enables the plugin.
plugin_name
--
plugin_name=FORCE
Enable the plugin. If plugin initialization fails, do not start the server. In other words, force the server to run with the plugin or not at all.
The values OFF, ON, and
FORCE are not case sensitive.
If a plugin is disabled, either explicitly with
OFF or implicitly because it was enabled with
ON but failed to initialize, aspects of server
operation that require the plugin will change. For example, if the
plugin implements a storage engine, existing tables for the
storage engine become inaccessible, and attempts to create new
tables for the storage engine result in tables that use the
default storage engine unless the
NO_ENGINE_SUBSTITUTION SQL mode
has been enabled to cause an error to occur instead.
Suppose that CSV, InnoDB,
and ARCHIVE are built as pluggable storage
engines and that you want the server to load them at startup,
subject to these conditions: The server is allowed to run if
CSV initialization fails, but must require that
InnoDB initialization succeed.
ARCHIVE should be disabled. To accomplish that,
use these lines in an option file:
[mysqld] csv=ON innodb=FORCE archive=OFF
--enable-
is also supported and is the same as
plugin_name--.
plugin_name=ON--disable-
and plugin_name--skip-
are also supported and are the same as
plugin_name--.
plugin_name=OFF
Before MySQL 5.1.36, plugin options are boolean options (see Section 4.2.3.2, “Program Option Modifiers”). That is, any of these options enable the plugin:
--plugin_name--plugin_name=1 --enable-plugin_name
And these options disable the plugin:
--plugin_name=0 --disable-plugin_name--skip-plugin_name
If the plugin fails to initialize, the server runs with the plugin disabled.
If you upgrade to MySQL 5.1.36 or later from an older version and
previously used options of the form
-- or
plugin_name=0--, the
equivalent options are now
plugin_name=1-- and
plugin_name=OFF--,
respectively. You also have the choice of requiring plugins to
start successfully by using
plugin_name=ON--.
plugin_name=FORCE
Plugins can also be loaded at runtime, via the
INSTALL PLUGIN statement (see
Section 12.5.3.3, “INSTALL PLUGIN Syntax”).
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.1 releases. For historical information concerning their implementation, please see http://www.mysql.com/products/enterprise//5.0/en/, and http://www.mysql.com/products/enterprise//4.1/en/.
The following table lists all available system variables:
Table 5.2. System Variable Summary
| Name | Cmd-Line | Option file | System Var | Var Scope | Dynamic |
|---|---|---|---|---|---|
| auto_increment_increment | Yes | Yes | Yes | Both | Yes |
| auto_increment_offset | Yes | Yes | Yes | Both | Yes |
| autocommit | Yes | Session | Yes | ||
| automatic_sp_privileges | Yes | Global | Yes | ||
| back_log | Yes | Yes | Yes | Global | No |
| basedir | Yes | Yes | Yes | Global | No |
| big-tables | Yes | Yes | Yes | ||
| - Variable: big_tables | Yes | Session | Yes | ||
| binlog_cache_size | Yes | Yes | Yes | Global | Yes |
| binlog-format | Yes | Yes | Yes | ||
| - Variable: binlog_format | Yes | Both | Yes | ||
| bulk_insert_buffer_size | Yes | Yes | Yes | Both | Yes |
| character_set_client | Yes | Both | Yes | ||
| character_set_connection | Yes | Both | Yes | ||
| character_set_database[a] | Yes | Both | Yes | ||
| character-set-filesystem | Yes | Yes | Yes | ||
| - Variable: character_set_filesystem | Yes | Both | Yes | ||
| character_set_results | Yes | Both | Yes | ||
| character-set-server | Yes | Yes | Yes | ||
| - Variable: character_set_server | Yes | Both | Yes | ||
| character_set_system | Yes | Global | No | ||
| character-sets-dir | Yes | Yes | No | ||
| - Variable: character_sets_dir | Yes | Global | No | ||
| collation_connection | Yes | Both | Yes | ||
| collation_database[b] | Yes | Both | Yes | ||
| collation-server | Yes | Yes | Yes | ||
| - Variable: collation_server | Yes | Both | Yes | ||
| completion_type | Yes | Yes | Yes | Both | Yes |
| concurrent_insert | Yes | Yes | Yes | Global | Yes |
| connect_timeout | Yes | Yes | Yes | Global | Yes |
| datadir | Yes | Yes | Yes | Global | No |
| date_format | Yes | Both | 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 | ||
| event-scheduler | Yes | Yes | Yes | ||
| - Variable: event_scheduler | Yes | Global | Yes | ||
| 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 |
| general-log | Yes | Yes | Yes | ||
| - Variable: general_log | Yes | Global | Yes | ||
| general_log_file | Yes | Yes | Yes | Global | Yes |
| group_concat_max_len | Yes | Yes | Yes | Both | Yes |
| have_archive | Yes | Global | No | ||
| have_blackhole_engine | Yes | Global | No | ||
| have_community_features | Yes | Global | No | ||
| have_compress | Yes | Global | No | ||
| have_crypt | Yes | Global | No | ||
| have_csv | Yes | Global | No | ||
| have_dynamic_loading | 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_partitioning | Yes | Global | No | ||
| have_query_cache | Yes | Global | No | ||
| have_raid | Yes | Global | No | ||
| have_row_based_replication | Yes | Global | No | ||
| have_rtree_keys | Yes | Global | No | ||
| have_ssl | Yes | Global | No | ||
| have_symlink | Yes | Global | No | ||
| hostname | Yes | Global | No | ||
| identity | Yes | Session | Yes | ||
| ignore_builtin_innodb | Yes | Yes | Yes | Global | No |
| init_connect | Yes | Yes | Yes | Global | Yes |
| init-file | Yes | Yes | No | ||
| - Variable: init_file | Yes | Global | No | ||
| init_slave | Yes | Yes | Yes | Global | Yes |
| innodb_adaptive_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_autoinc_lock_mode | Yes | Yes | Yes | Global | No |
| 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_stats_on_metadata | Yes | Yes | Yes | Global | Yes |
| innodb_support_xa | Yes | Yes | Yes | Both | Yes |
| innodb_sync_spin_loops | Yes | Yes | Yes | Global | Yes |
| innodb_table_locks | Yes | Yes | Yes | Both | Yes |
| innodb_thread_concurrency | Yes | Yes | Yes | Global | Yes |
| innodb_thread_sleep_delay | Yes | Yes | Yes | Global | Yes |
| innodb_use_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_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 | Yes |
| log_bin | Yes | Global | No | ||
| log-bin | Yes | Yes | Yes | Global | No |
| log-bin-trust-function-creators | Yes | Yes | Yes | ||
| - Variable: log_bin_trust_function_creators | Yes | Global | Yes | ||
| log-bin-trust-routine-creators | Yes | Yes | Yes | ||
| - Variable: log_bin_trust_routine_creators | Yes | Global | Yes | ||
| log-error | Yes | Yes | No | ||
| - Variable: log_error | Yes | Global | No | ||
| log-output | Yes | Yes | Yes | ||
| - Variable: log_output | Yes | Global | Yes | ||
| log-queries-not-using-indexes | Yes | Yes | Yes | ||
| - Variable: log_queries_not_using_indexes | Yes | Global | Yes | ||
| log-slave-updates | Yes | Yes | No | ||
| - Variable: log_slave_updates | Yes | Global | No | ||
| log-slow-queries | Yes | Yes | Yes | ||
| - Variable: log_slow_queries | Yes | Global | Yes | ||
| log-warnings | Yes | Yes | Yes | ||
| - Variable: log_warnings | Yes | Both | Yes | ||
| long_query_time | Yes | Yes | Yes | Both | Yes |
| low-priority-updates | Yes | Yes | Yes | ||
| - Variable: low_priority_updates | Yes | Both | Yes | ||
| lower_case_file_system | Yes | Yes | Yes | Global | No |
| lower_case_table_names | Yes | Yes | Yes | Global | No |
| master-bind | Yes | Yes | Yes | No | |
| max_allowed_packet | Yes | Yes | Yes | 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 |
| min-examined-row-limit | Yes | Yes | Yes | Both | Yes |
| multi_range_count | Yes | Yes | Yes | Both | Yes |
| myisam_data_pointer_size | Yes | Yes | Yes | Global | Yes |
| myisam_max_sort_file_size | Yes | Yes | Yes | Global | Yes |
| myisam_recover_options | Yes | Global | No | ||
| myisam_repair_threads | Yes | Yes | Yes | Both | Yes |
| myisam_sort_buffer_size | Yes | Yes | Yes | Both | Yes |
| myisam_stats_method | Yes | Yes | Yes | Both | Yes |
| myisam_use_mmap | Yes | Yes | Yes | Global | Yes |
| named_pipe | Yes | Global | No | ||
| ndb_autoincrement_prefetch_sz | Yes | Yes | Yes | Both | Yes |
| ndb-batch-size | Yes | Yes | Yes | Global | No |
| ndb_cache_check_time | Yes | Yes | Yes | Global | Yes |
| ndb_extra_logging | Yes | Yes | Yes | Global | Yes |
| ndb_force_send | Yes | Yes | Yes | Both | Yes |
| ndb_log_empty_epochs | Yes | Yes | Yes | Global | Yes |
| ndb_log_orig | Yes | Global | No | ||
| ndb-log-update-as-write | Yes | Yes | Yes | Global | Yes |
| ndb_log_updated_only | Yes | Yes | Yes | Global | Yes |
| ndb_optimization_delay | Yes | Global | Yes | ||
| ndb_table_no_logging | Yes | Session | Yes | ||
| ndb_table_temporary | Yes | Session | Yes | ||
| ndb_use_copying_alter_table | Yes | Both | No | ||
| ndb_use_exact_count | Yes | Both | Yes | ||
| ndb_use_transactions | Yes | Yes | Yes | Both | Yes |
| ndb_wait_connected | Yes | Yes | Yes | No | |
| net_buffer_length | Yes | Yes | Yes | Both | Yes |
| net_read_timeout | Yes | Yes | Yes | Both | Yes |
| net_retry_count | Yes | Yes | Yes | Both | Yes |
| net_write_timeout | Yes | Yes | Yes | Both | Yes |
| new | Yes | Yes | Yes | Both | Yes |
| old | Yes | Yes | Yes | Global | No |
| old-alter-table | Yes | Yes | Yes | ||
| - Variable: old_alter_table | Yes | Both | Yes | ||
| old-passwords | Yes | Yes | Yes | ||
| - Variable: old_passwords | Yes | Both | Yes | ||
| open-files-limit | Yes | Yes | No | ||
| - Variable: open_files_limit | Yes | Global | No | ||
| optimizer_prune_level | Yes | Yes | Yes | Both | Yes |
| optimizer_search_depth | Yes | Yes | Yes | Both | Yes |
| optimizer_switch | Yes | Yes | Yes | Both | Yes |
| 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 | ||
| slave-allow-batching | Yes | Yes | Yes | ||
| - Variable: slave_allow_batching | Yes | Global | Yes | ||
| slave_compressed_protocol | Yes | Yes | Yes | Global | Yes |
| slave_exec_mode | Yes | Global | Yes | ||
| slave-load-tmpdir | Yes | Yes | No | ||
| - Variable: slave_load_tmpdir | Yes | Global | No | ||
| slave-net-timeout | Yes | Yes | Yes | ||
| - Variable: slave_net_timeout | Yes | Global | Yes | ||
| slave-skip-errors | Yes | Yes | No | ||
| - Variable: slave_skip_errors | Yes | Global | No | ||
| slave_transaction_retries | Yes | Yes | Yes | Global | Yes |
| slow_launch_time | Yes | Yes | Yes | Global | Yes |
| slow-query-log | Yes | Yes | Yes | ||
| - Variable: slow_query_log | Yes | Global | Yes | ||
| slow_query_log_file | Yes | Yes | Yes | Global | Yes |
| socket | Yes | Yes | Yes | Global | No |
| sort_buffer_size | Yes | Yes | Yes | Both | Yes |
| sql_auto_is_null | Yes | Session | Yes | ||
| sql_big_selects | Yes | Session | 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-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_cache | Yes | Yes | Yes | Global | Yes |
| table_definition_cache | Yes | Yes | Yes | Global | Yes |
| table_lock_wait_timeout | Yes | Yes | Yes | Global | Yes |
| table_open_cache | Yes | Yes | Yes | Global | Yes |
| table_type | Yes | Both | Yes | ||
| thread_cache_size | Yes | Yes | Yes | Global | Yes |
| thread_concurrency | Yes | Yes | Yes | Global | No |
| thread_handling | Yes | Yes | Yes | Global | No |
| thread_stack | Yes | Yes | Yes | Global | No |
| time_format | Yes | Yes | Yes | Both | 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_allow_batching | Yes | Session | 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] HASH(0x31c1550) [b] HASH(0x31ca410) | |||||
For additional system variable information, see these sections:
Section 5.1.5, “Session System Variables”, describes system variables that exist only as session variables (that is, they do not have any global counterpart).
Section 5.1.6, “Using System Variables”, discusses the syntax for setting and displaying system variable values.
Section 5.1.6.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.6.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.3, “Replication and Binary Logging Options and Variables”.
Some of the following variable descriptions refer to
“enabling” or “disabling” a variable.
These variables can be enabled with the
SET
statement by setting them to ON or
1, or disabled by setting them to
OFF or 0. However, to set
such a variable on the command line or in an option file, you
must set it to 1 or 0;
setting it to ON or OFF
will not work. For example, on the command line,
--delay_key_write=1 works but
--delay_key_write=ON does not.
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.
| Variable Name | automatic_sp_privileges | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Permitted Values |
|
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.
| 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 | ||||||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
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 | --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 | ||||||||
| Permitted Values |
| ||||||||
| Permitted Values |
|
MyISAM uses a special tree-like cache to
make bulk inserts faster for
INSERT ...
SELECT, INSERT ... VALUES (...), (...),
..., and
LOAD DATA
INFILE when adding data to nonempty tables. This
variable limits the size of the cache tree in bytes per
thread. Setting it to 0 disables this optimization. The
default value is 8MB.
| Variable Name | character_set_client | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
The character set used for literals that do not have a character set introducer and for number-to-string conversion.
| Variable Name | character_set_database | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Footnote | This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. | ||
| Permitted Values |
|
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.1.6 | ||
| 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 | ||
| Permitted Values |
|
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.1.6.
| Variable Name | character_set_results | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values |
|
The character set used for returning query results such as result sets or error messages 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 | ||
| Permitted Values |
|
The server's default character set.
| Variable Name | character_set_system | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
The directory where character sets are installed.
| Variable Name | collation_connection | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Permitted Values |
|
The collation of the connection character set.
| Variable Name | collation_database | ||
| Variable Scope | Both | ||
| Dynamic Variable | Yes | ||
| Footnote | This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. | ||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
The server's default collation.
| 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 | ||||||
| Permitted Values |
|
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.)
| 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 | ||||||
| Permitted Values |
|
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.
This variable can 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 | ||||
| Permitted Values (<= 5.1.22) |
| ||||
| Permitted Values (>= 5.1.23) |
|
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.1.23 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 | ||
| Permitted Values |
|
The MySQL data directory. This variable can be set with the
--datadir option.
This variable is unused.
This variable is unused.
| Command Line Format | --debug[=debug_options] | ||||
| Config File Format | debug | ||||
| Variable Name | debug | ||||
| Variable Scope | Both | ||||
| Dynamic Variable | Yes | ||||
| Permitted Values |
|
This variable indicates the current debugging settings. It is
available only for servers built with debugging support. The
initial value comes from the value of instances of the
--debug option given at server
startup. The global and session values may be set at runtime;
the SUPER privilege is
required, even for the session value.
Assigning a value that begins with + or
- cause the value to added to or subtracted
from the current value:
mysql>SET debug = 'T';mysql>SELECT @@debug;+---------+ | @@debug | +---------+ | T | +---------+ mysql>SET debug = '+P';mysql>SELECT @@debug;+---------+ | @@debug | +---------+ | P:T | +---------+ mysql>SET debug = '-P';mysql>SELECT @@debug;+---------+ | @@debug | +---------+ | T | +---------+
This variable was added in MySQL 5.1.7.
| 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 | ||||||
| Permitted Values |
|
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 | ||||||
| Permitted Values |
|
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.5.1, “MyISAM Startup Options”.
If you enable external locking with
--external-locking, there is
no protection against index corruption for tables that use
delayed key writes.
| Command Line Format | --delayed_insert_limit=# | ||||||||
| Config File Format | delayed_insert_limit | ||||||||
| Option Sets Variable | Yes, delayed_insert_limit | ||||||||
| Variable Name | delayed_insert_limit | ||||||||
| Variable Scope | Global | ||||||||
| Dynamic Variable | Yes | ||||||||
| Permitted Values |
| ||||||||
| Permitted Values |
|
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 | ||||
| Permitted Values |
|
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 | ||||||||
| Permitted Values |
| ||||||||
| Permitted Values |
|
This is a per-table limit on the number of rows to queue when
handling INSERT DELAYED
statements. If the queue becomes full, any client that issues
an INSERT DELAYED statement
waits until there is room in the queue again.
| Command Line Format | --div_precision_increment=# | ||||||
| Config File Format | div_precision_increment | ||||||
| Option Sets Variable | Yes, div_precision_increment | ||||||
| Variable Name | div_precision_increment | ||||||
| Variable Scope | Both | ||||||
| Dynamic Variable | Yes | ||||||
| Permitted Values |
|
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 | +----------------+
| 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 | ||||
| Permitted Values (>= 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.1, 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”.
| Version Introduced | 5.1.6 | ||||||
| Command Line Format | --event-scheduler[=value] | ||||||
| Config File Format | event-scheduler | ||||||
| Option Sets Variable | Yes, event_scheduler | ||||||
| Variable Name | event_scheduler | ||||||
| Variable Scope | Global | ||||||
| Dynamic Variable | Yes | ||||||
| Permitted Values |
|
This variable indicates the status of the Event Scheduler; as
of MySQL 5.1.12, possible values are ON,
OFF, and DISABLED, with
the default being OFF. This variable and
its effects on the Event Scheduler's operation are discussed
in greater detail in the
Overview section
of the Events chapter.
This variable was added in MySQL 5.1.6.
| 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 | ||||||
| Permitted Values |
|
The number of days for automatic binary log removal. The default is 0, which means “no automatic removal.” Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in Section 5.2, “MySQL Server Logs”.
| Command Line Format | --flush | ||||
| Config File Format | flush | ||||
| Variable Name | flush | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Permitted Values |
|
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 | ||||||
| Permitted Values |
| ||||||
| Permitted Values |
|
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 | ||||
| Permitted Values |
|
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 | ||||
| Permitted Values |
|
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 | ||||||
| Permitted Values |
|
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 | ||||||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
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
storage/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
| Version Introduced | 5.1.12 | ||||
| Command Line Format | --general-log | ||||
| Config File Format | general-log | ||||
| Option Sets Variable | Yes, general_log | ||||
| Variable Name | general_log | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Permitted Values |
|
Whether the general query log is enabled. The value can be 0
(or OFF) to disable the log or 1 (or
ON) to enable the log. The default value
depends on whether the
--general_log option is given
(--log before MySQL 5.1.29).
The destination for log output is controlled by the
log_output system variable;
if that value is NONE, no log entries are
written even if the log is enabled. The
general_log variable was
added in MySQL 5.1.12.
| Version Introduced | 5.1.12 | ||||
| Command Line Format |
| ||||
| Config File Format |
| ||||
| Option Sets Variable | Yes, general_log_file | ||||
| Variable Name | general_log_file | ||||
| Variable Scope | Global | ||||
| Dynamic Variable | Yes | ||||
| Permitted Values |
|
The name of the general query log file. The default value is
host_name.log--general_log_file option
(--log before MySQL 5.1.29).
This variable was added in MySQL 5.1.12.
| 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 | ||||||||
| Permitted Values |
| ||||||||
| Permitted Values |
|
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. This variable was removed in MySQL 5.1.14.
YES if mysqld supports
BLACKHOLE tables, NO if
not. This variable was removed in MySQL 5.1.14.
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 statement profiling is enabled,
NO if not. See
Section 12.5.5.32, “SHOW PROFILE Syntax”.
This variable is renamed to have_profiling
in MySQL 5.4.4.
YES if the crypt()
system call is available to the server, NO
if not. If not, the ENCRYPT()
function cannot be used.
YES if mysqld supports
ARCHIVE tables, NO if
not.
YES if mysqld supports
dynamic loading of plugins, NO if not. This
variable was added in MySQL 5.1.10.
YES if mysqld supports
EXAMPLE tables, NO if
not. This variable was removed in MySQL 5.1.14.
YES if mysqld supports
FEDERATED tables, NO if
not. This variable was removed in MySQL 5.1.14.
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.1, this variable appears only for
reasons of backward compatibility. It is always
NO because ISAM tables
are no longer supported. This variable was removed in MySQL
5.1.7.
YES if mysqld supports
MERGE tables. DISABLED
if --skip-merge is used. This
variable was removed in MySQL 5.1.3.
YES if mysqld supports
SSL connections, NO if not. As of MySQL
5.1.17, this variable is an alias for
have_ssl.
YES if mysqld supports
partitioning. Added in MySQL 5.1.1 as
have_partition_engine and renamed to
have_partioning in 5.1.6.
YES if mysqld supports
the query cache, NO if not.
| Version Introduced | 5.1.5 | ||
| Version Removed | 5.1.15 | ||
| Variable Name | have_row_based_replication | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values |
|
YES if the server can perform replication
using row-based binary logging. If the value is
NO, the server can use only statement-based
logging. See Section 16.1.2, “Replication Formats”. This
variable was added in MySQL 5.1.5 and removed in 5.1.15.
In MySQL 5.1, this variable appears only for
reasons of backward compatibility. It is always
NO because RAID tables
are no longer supported. This variable was removed in MySQL
5.1.7.
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.1.17. 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.1.17 | ||
| Variable Name | hostname | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values |
|
The server sets this variable to the server host name at startup. This variable was added in MySQL 5.1.17.
| 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 | ||
| Permitted Values |
|
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 | ||
| Permitted Values |
|
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.10.2, “Typical configure Options”.
innodb_
xxx
InnoDB system variables are listed in
Section 13.6.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 | ||||||
| Permitted Values |
|
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 | ||||||||
| Permitted Values |
|
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. As
of MySQL 5.1.23, 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).
| Version Introduced | 5.1.21 | ||||
| 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 | ||||
| Permitted Values |
|
If a MyISAM table is created with no
DATA DIRECTORY option, the
.MYD file is created in the database
directory.