Table of Contents
End of Product Lifecycle. Active development for MySQL Database Server version 5.1 has ended. Oracle offers various support offerings which may be of interest. For details and more information, see the MySQL section of the Lifetime Support Policy for Oracle Technology Products (http://www.oracle.com/us/support/lifetime-support/index.html). Please consider upgrading to a recent version.
MySQL Server (mysqld) is the main program that does most of the work in a MySQL installation. This chapter provides an overview of MySQL Server and covers general server administration:
Server configuration.
The server log files.
Management of multiple servers on a single machine.
For additional information on administrative topics, see also:
mysqld is the MySQL server. The following discussion covers these MySQL server configuration topics:
Startup options that the server supports. You can specify these options on the command line, through configuration files, or both.
Server system variables. These variables reflect the current state and values of the startup options, some of which can be modified while the server is running.
Server status variables. These variables contain counters and statistics about runtime operation.
How to set the server SQL mode. This setting modifies certain aspects of SQL syntax and semantics, for example for compatibility with code from other database systems, or to control the error handling for particular situations.
The server shutdown process. There are performance and reliability considerations depending on the type of table (transactional or nontransactional) and whether you use replication.
Not all storage engines are supported by all MySQL server binaries and configurations. To find out how to determine which storage engines your MySQL server installation supports, see Section 13.7.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.
For a version of this table that is specific to MySQL Cluster, see Section 17.3.4.1, “MySQL Cluster mysqld Option and Variable Reference”.
Table 5.1 Option/Variable Summary
The MySQL server has many operating parameters, which you can change at server startup using command-line options or configuration files (option files). It is also possible to change many parameters at runtime. For general instructions on setting parameters at startup or runtime, see Section 5.1.3, “Server Command Options”, and Section 5.1.4, “Server System Variables”.
MySQL provides a number of preconfigured option files that can be
used as a basis for tuning the MySQL server. Look for files named
my-small.cnf,
my-medium.cnf,
my-large.cnf, and
my-huge.cnf, which are sample option files
for small, medium, large, and very large systems. On Windows, the
extension is .ini rather than
.cnf.
On Windows, the .ini or
.cnf option file extension might not be
displayed.
For a binary distribution, look for the sample files in or under
your installation directory. If you have a source distribution,
look in the support-files directory. To use a
sample file as a base configuration file, rename a copy of it and
place the copy in the appropriate location. Regarding names and
appropriate location, see the general information provided in
Section 4.2.6, “Using Option Files”. That section also describes option
file format and syntax.
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.6, “Using Option Files”.
mysqld reads options from the
[mysqld] and [server]
groups. mysqld_safe reads options from the
[mysqld], [server],
[mysqld_safe], and
[safe_mysqld] groups.
mysql.server reads options from the
[mysqld] and [mysql.server]
groups.
An embedded MySQL server usually reads options from the
[server], [embedded], and
[
groups, where xxxxx_SERVER]xxxxx is the name of the
application into which the server is embedded.
mysqld accepts many command options. For a brief summary, execute mysqld --help. To see the full list, use mysqld --verbose --help.
The following list shows some of the most common server options. Additional options are described in other sections:
Options that affect security: See Section 6.1.4, “Security-Related mysqld Options and Variables”.
SSL-related options: See Section 6.3.6.4, “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.8.1, “Installing and Uninstalling Plugins”.
Options specific to particular storage engines: See Section 14.6.7, “InnoDB Startup Options and System Variables”, Section 14.5.1, “MyISAM Startup Options”, and Section 17.3.4.2, “MySQL Server 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.
Some options control the size of buffers or caches. For a given buffer, the server might need to allocate internal data structures. These structures typically are allocated from the total memory allocated to the buffer, and the amount of space required might be platform dependent. This means that when you assign a value to an option that controls a buffer size, the amount of space actually available might differ from the value assigned. In some cases, the amount might be less than the value assigned. It is also possible that the server will adjust a value upward. For example, if you assign a value of 0 to an option for which the minimal value is 1024, the server will set the value to 1024.
Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.
Some options take file name values. Unless otherwise specified,
the default file location is the data directory if the value is a
relative path name. To specify the location explicitly, use an
absolute path name. Suppose that the data directory is
/var/mysql/data. If a file-valued option is
given as a relative path name, it will be located under
/var/mysql/data. If the value is an absolute
path name, its location is as given by the path name.
--help, -?
| Command-Line Format | -? | ||
--help | |||
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 | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
This option controls whether user-defined functions that have
only an xxx symbol for the main function
can be loaded. By default, the option is off and only UDFs
that have at least one auxiliary symbol can be loaded; this
prevents attempts at loading functions from shared object
files other than those containing legitimate UDFs. See
Section 22.3.2.6, “User-Defined Function Security Precautions”.
| Command-Line Format | --ansi | ||
-a | |||
Use standard (ANSI) SQL syntax instead of MySQL syntax. For
more precise control over the server SQL mode, use the
--sql-mode option instead. See
Section 1.8, “MySQL Standards Compliance”, and
Section 5.1.7, “Server SQL Modes”.
| Command-Line Format | --basedir=path | ||
-b | |||
| System Variable Name | basedir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The path to the MySQL installation directory. All paths are usually resolved relative to this directory.
| Command-Line Format | --big-tables | ||
| System Variable Name | big_tables | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Enable large result sets by saving all temporary sets in files. This option prevents most “table full” errors, but also slows down queries for which in-memory tables would suffice. Since MySQL 3.23.2, the server is able to handle large result sets automatically by using memory for small temporary tables and switching to disk tables where necessary.
| Command-Line Format | --bind-address=addr | ||
| System Variable Name | bind_address | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
| Default | 0.0.0.0 | ||
The MySQL server listens on a single network socket for TCP/IP
connections. This socket is bound to a single address, but it
is possible for an address to map onto multiple network
interfaces. The default address is 0.0.0.0.
To specify an address explicitly, use the
--bind-address=
option at server startup, where
addraddr is an IPv4 address or a host
name. If addr is a host name, the
server resolves the name to an IPv4 address and binds to that
address.
The server treats different types of addresses as follows:
If the address is 0.0.0.0, the server
accepts TCP/IP connections on all server host IPv4
interfaces.
If the address is a “regular” IPv4 address
(such as 127.0.0.1), the server accepts
TCP/IP connections only for that particular IPv4 address.
If you intend to bind the server to a specific address, be
sure that the mysql.user grant table
contains an account with administrative privileges that you
can use connect to that address. Otherwise, you will not be
able to shut down the server. For example, if you bind to
0.0.0.0, you can connect to the server
using all existing accounts. But if you bind to
127.0.0.1, the server accepts connections
only on that address. In this case, first make sure that the
'root'@'127.0.0.1' account is present in
the mysql.user table so that you can still
connect to the server to shut it down.
--binlog-format={ROW|STATEMENT|MIXED}
| Introduced | 5.1.5 | ||
| Command-Line Format | --binlog-format=format | ||
| System Variable Name | binlog_format | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values (>= 5.1.5, <= 5.1.7) | |||
| Type | enumeration | ||
| Default | STATEMENT | ||
| Valid Values | ROW | ||
STATEMENT | |||
| Permitted Values (>= 5.1.8, <= 5.1.11) | |||
| Type | enumeration | ||
| Default | STATEMENT | ||
| Valid Values | ROW | ||
STATEMENT | |||
MIXED | |||
| Permitted Values (>= 5.1.12, <= 5.1.28) | |||
| Type | enumeration | ||
| Default | MIXED | ||
| Valid Values | ROW | ||
STATEMENT | |||
MIXED | |||
| Permitted Values (>= 5.1.29) | |||
| Type | enumeration | ||
| Default | STATEMENT | ||
| Valid Values | ROW | ||
STATEMENT | |||
MIXED | |||
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.
Under some conditions, changing this variable at runtime is not possible, or causes replication to fail. See Section 5.2.4.2, “Setting The Binary Log Format”, for more information.
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.5. (Bug #42928)
MySQL Cluster.
The default value for this option in all MySQL Cluster NDB
6.1 through 7.1 releases is MIXED. See
Section 17.6.2, “General Requirements for MySQL Cluster Replication”, for
more information.
| Command-Line Format | --bootstrap | ||
This option is used by the mysql_install_db program 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.11.4, “MySQL Source-Configuration Options”.
| Command-Line Format | --character-sets-dir=path | ||
| System Variable Name | character_sets_dir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The directory where character sets are installed. See Section 10.5, “Character Set Configuration”.
--character-set-client-handshake
| Command-Line Format | --character-set-client-handshake | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | TRUE | ||
Do not ignore character set information sent by the client. To
ignore client information and use the default server character
set, use
--skip-character-set-client-handshake;
this makes MySQL behave like MySQL 4.0.
--character-set-filesystem=
charset_name
| Introduced | 5.1.6 | ||
| Command-Line Format | --character-set-filesystem=name | ||
| System Variable Name | character_set_filesystem | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
| Default | binary | ||
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 | ||
| System Variable Name | character_set_server | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
| Default | latin1 | ||
Use charset_name as the default
server character set. See
Section 10.5, “Character Set Configuration”. If you use this
option to specify a nondefault character set, you should also
use --collation-server to
specify the collation.
--chroot=,
path-r
path
| Command-Line Format | --chroot=dir_name | ||
-r name | |||
| Permitted Values | |||
| Type | directory name | ||
Put the mysqld server in a closed
environment during startup by using the
chroot() system call. This is a recommended
security measure. Note that use of this option somewhat limits
LOAD DATA
INFILE and
SELECT ... INTO
OUTFILE.
--collation-server=
collation_name
| Command-Line Format | --collation-server | ||
| System Variable Name | collation_server | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
| Default | latin1_swedish_ci | ||
Use collation_name as the default
server collation. See Section 10.5, “Character Set Configuration”.
| Command-Line Format | --console | ||
| Platform Specific | Windows | ||
(Windows only.) Write error log messages to
stderr and stdout.
mysqld does not close the console window if
this option is used.
If both --log-error and
--console are specified,
whichever option is given last takes precedence.
| Command-Line Format | --core-file | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Write a core file if mysqld dies. The name
and location of the core file is system dependent. On Linux, a
core file named
core. is
written to the current working directory of the process, which
for mysqld is the data directory.
pidpid represents the process ID of
the server process. On Mac OS X, a core file named
core. is
written to the pid/cores directory. On
Solaris, use the coreadm command to specify
where to write the core file and how to name it.
For some systems, to get a core file you must also specify the
--core-file-size option to
mysqld_safe. See
Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”. On some systems, such as
Solaris, you do not get a core file if you are also using the
--user option. There might be
additional restrictions or limitations. For example, it might
be necessary to execute ulimit -c unlimited
before starting the server. Consult your system documentation.
--datadir=,
path-h
path
| Command-Line Format | --datadir=path | ||
-h | |||
| System Variable Name | datadir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The path to the data directory.
--debug[=,
debug_options]-# [
debug_options]
| Command-Line Format | --debug[=debug_options] | ||
| System Variable Name | debug | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type (Unix) | string | ||
| Default | d:t:i:o,/tmp/mysqld.trace | ||
| Permitted Values | |||
| Type (Windows) | string | ||
| Default | d:t:i:O,\mysqld.trace | ||
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_named:t:i:o,/tmp/mysqld.trace on
Unix and d:t:i:O,\mysqld.trace on Windows.
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.
For more information, see Section 22.4.3, “The DBUG Package”.
| Introduced | 5.1.41 | ||
| Command-Line Format | --debug-sync-timeout[=#] | ||
| Permitted Values | |||
| Type | numeric | ||
Controls whether the Debug Sync facility for testing and
debugging is enabled. Use of Debug Sync requires that MySQL be
configured with the
--enable-debug-sync option
(see Section 2.11.4, “MySQL Source-Configuration Options”). If Debug
Sync is not compiled in, this option is not available. The
option value is a timeout in seconds. The default value is 0,
which disables Debug Sync. To enable it, specify a value
greater than 0; this value also becomes the default timeout
for individual synchronization points. If the option is given
without a value, the timeout is set to 300 seconds.
For a description of the Debug Sync facility and how to use synchronization points, see MySQL Internals: Test Synchronization.
This option was added in MySQL 5.1.41.
--default-character-set=
charset_name
| Deprecated | 5.0.0 | ||
| Command-Line Format | --default-character-set=name | ||
-C name | |||
| Permitted Values | |||
| Type | string | ||
Use charset_name as the default
character set. This option is deprecated in favor of
--character-set-server. See
Section 10.5, “Character Set Configuration”.
--default-character-set is
removed in MySQL 5.5.
--default-collation=
collation_name
| Deprecated | 4.1.3 | ||
| Command-Line Format | --default-collation=name | ||
| Permitted Values | |||
| Type | string | ||
Use collation_name as the default
collation. This option is deprecated in favor of
--collation-server. See
Section 10.5, “Character Set Configuration”.
--default-collation is removed
in MySQL 5.5.
| Command-Line Format | --default-storage-engine=name | ||
| System Variable Name | storage_engine | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | MyISAM | ||
Set the default storage engine (table type) for tables. See Chapter 14, Storage Engines.
| Deprecated | 5.0.0, by default-storage-engine | ||
| Command-Line Format | --default-table-type=name | ||
| Permitted Values | |||
| Type | string | ||
This option is a synonym for
--default-storage-engine. It is
deprecated and removed in MySQL 5.5.
| Command-Line Format | --default-time-zone=name | ||
| Permitted Values | |||
| Type | string | ||
Set the default server time zone. This option sets the global
time_zone system variable. If
this option is not given, the default time zone is the same as
the system time zone (given by the value of the
system_time_zone system
variable.
--defaults-extra-file=
file_name
Read this option file after the global option file but (on
Unix) before the user option file. If the file does not exist
or is otherwise inaccessible, an error occurs.
file_name is the full path name to
the file.
Use only the given option file. If the file does not exist or
is otherwise inaccessible, an error occurs.
file_name is the full path name to
the file.
Read not only the usual option groups, but also groups with
the usual names and a suffix of
str. For example,
mysqld normally reads the
[mysqld] group. If the
--defaults-group-suffix=_other
option is given, mysqld also reads the
[mysqld_other] group.
--delay-key-write[={OFF|ON|ALL}]
| Command-Line Format | --delay-key-write[=name] | ||
| System Variable Name | delay_key_write | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | ON | ||
| Valid Values | ON | ||
OFF | |||
ALL | |||
Specify how to use delayed key writes. Delayed key writing
causes key buffers not to be flushed between writes for
MyISAM tables. OFF
disables delayed key writes. ON enables
delayed key writes for those tables that were created with the
DELAY_KEY_WRITE option.
ALL delays key writes for all
MyISAM tables. See
Section 8.9.2, “Tuning Server Parameters”, and
Section 14.5.1, “MyISAM Startup Options”.
If you set this variable to ALL, you
should not use MyISAM tables from within
another program (such as another MySQL server or
myisamchk) when the tables are in use.
Doing so leads to index corruption.
| Command-Line Format | --des-key-file=file_name | ||
Read the default DES keys from this file. These keys are used
by the DES_ENCRYPT() and
DES_DECRYPT() functions.
This option is deprecated. Use
--external-locking instead.
| Command-Line Format | --enable-named-pipe | ||
| Platform Specific | Windows | ||
Enable support for named pipes. This option applies only on
Windows. 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.
| Deprecated | 5.1.54 | ||
| Command-Line Format | --enable-pstack | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
This option is nonfunctional. It is deprecated as of MySQL 5.1.54 and removed in MySQL 5.5.
--engine-condition-pushdown={ON|OFF}
| Command-Line Format | --engine-condition-pushdown | ||
| System Variable Name | engine_condition_pushdown | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | ON | ||
Sets the
engine_condition_pushdown
system variable. For more information, see
Section 8.3.1.5, “Engine Condition Pushdown Optimization”.
| Introduced | 5.1.6 | ||
| Command-Line Format | --event-scheduler[=value] | ||
| System Variable Name | event_scheduler | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | OFF | ||
| Valid Values | ON | ||
OFF | |||
DISABLED | |||
Enable or disable, and start or stop, the event scheduler. 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] | ||
-T [flags] | |||
| Permitted Values | |||
| Type | numeric | ||
This is a bit mask of different flags that you can use for debugging the mysqld server. Do not use this option unless you know exactly what it does!
| Command-Line Format | --external-locking | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Enable external locking (system locking), which is disabled by
default as of MySQL 4.0. Note that if you use this option on a
system on which lockd does not fully work
(such as Linux), it is easy for mysqld to
deadlock.
To disable external locking explicitly, use
--skip-external-locking.
External locking affects only
MyISAM table access. For more
information, including conditions under which it can and
cannot be used, see Section 8.7.4, “External Locking”.
| Command-Line Format | --flush | ||
| System Variable Name | flush | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Flush (synchronize) all changes to disk after each SQL statement. Normally, MySQL does a write of all changes to disk only after each SQL statement and lets the operating system handle the synchronizing to disk. See Section B.5.4.2, “What to Do If MySQL Keeps Crashing”.
| Command-Line Format | --gdb | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Install an interrupt handler for SIGINT
(needed to stop mysqld with
^C to set breakpoints) and disable stack
tracing and core file handling. See Section 22.4, “Debugging and Porting MySQL”.
| Introduced | 5.1.12 | ||
| Command-Line Format | --general-log | ||
| System Variable Name | general_log | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Specify the initial general query log state. With no argument
or an argument of 1, the
--general-log option enables
the log. If omitted or given with an argument of 0, the option
disables the log. This option was added in MySQL 5.1.12.
| Command-Line Format | --init-file=file_name | ||
| System Variable Name | init_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
Read SQL statements from this file at startup. Each statement must be on a single line and should not include comments.
This option is unavailable if MySQL was configured with the
--disable-grant-options
option. See Section 2.11.4, “MySQL Source-Configuration Options”.
--innodb-
xxx
Set an option for the InnoDB storage
engine. The InnoDB options are listed in
Section 14.6.7, “InnoDB Startup Options and System Variables”.
| Command-Line Format | --install [service_name] | ||
(Windows only) Install the server as a Windows service that
starts automatically during Windows startup. The default
service name is MySQL if no
service_name value is given. For
more information, see Section 2.3.6.7, “Starting MySQL Server as a Microsoft Windows Service”.
If the server is started with the
--defaults-file and
--install options,
--install must be first.
--install-manual
[
service_name]
| Command-Line Format | --install-manual [service_name] | ||
(Windows only) Install the server as a Windows service that
must be started manually. It does not start automatically
during Windows startup. The default service name is
MySQL if no
service_name value is given. For
more information, see Section 2.3.6.7, “Starting MySQL Server as a Microsoft Windows Service”.
If the server is started with the
--defaults-file and
--install-manual options,
--install-manual must be
first.
--language=
lang_name,
-L lang_name
| Command-Line Format | --language=name | ||
-L | |||
| System Variable Name | language | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
| Default | /usr/local/mysql/share/mysql/english/ | ||
The language to use for error messages.
lang_name can be given as the
language name or as the full path name to the directory where
the language files are installed. See
Section 10.2, “Setting the Error Message Language”.
| Command-Line Format | --large-pages | ||
| System Variable Name | large_pages | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Platform Specific | Linux | ||
| Permitted Values | |||
| Type (Linux) | boolean | ||
| Default | FALSE | ||
Some hardware/operating system architectures support memory pages greater than the default (usually 4KB). The actual implementation of this support depends on the underlying hardware and operating system. Applications that perform a lot of memory accesses may obtain performance improvements by using large pages due to reduced Translation Lookaside Buffer (TLB) misses.
Currently, MySQL supports only the Linux implementation of large page support (which is called HugeTLB in Linux). See Section 8.9.7, “Enabling Large Page Support”.
--large-pages is disabled by
default.
| Command-Line Format | --local-service | ||
(Windows only) A --local-service option
following the service name causes the server to run using the
LocalService Windows account that has
limited system privileges. This account is available only for
Windows XP or newer. If both
--defaults-file and
--local-service are given following the
service name, they can be in any order. See
Section 2.3.6.7, “Starting MySQL Server as a Microsoft Windows Service”.
--log[=,
file_name]-l [
file_name]
| Deprecated | 5.1.29, by general-log | ||
| Command-Line Format | --log[=file_name] | ||
-l | |||
| System Variable Name | log | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | file name | ||
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 is removed (along with the
log system variable) in MySQL
5.6. 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[=file_name] | ||
| System Variable Name | log_error | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
Log errors and startup messages to this file. See
Section 5.2.2, “The Error Log”. If you omit the file name, MySQL
uses
host_name.err.err.
| Command-Line Format | --log-isam[=file_name] | ||
| Permitted Values | |||
| Type | file name | ||
Log all MyISAM changes to this file (used
only when debugging MyISAM).
| Deprecated | 4.1.0 | ||
| Command-Line Format | --log-long-format | ||
-0 | |||
Log extra information to the binary log and slow query log, if
they have been activated. For example, the user name and
timestamp are logged for all queries. This option is
deprecated, as it now represents the default logging behavior.
(See the description for
--log-short-format.) The
--log-queries-not-using-indexes
option is available for the purpose of logging queries that do
not use indexes to the slow query log.
--log-long-format is removed in
MySQL 5.5.
| Introduced | 5.1.6 | ||
| Command-Line Format | --log-output=name | ||
| System Variable Name | log_output | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | set | ||
| Default | FILE | ||
| Valid Values | TABLE | ||
FILE | |||
NONE | |||
This option determines the destination for general query log
and slow query log output. The option value can be given as
one or more of the words TABLE,
FILE, or NONE. If the
option is given without a value, the default is
FILE. (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
| Command-Line Format | --log-queries-not-using-indexes | ||
| System Variable Name | log_queries_not_using_indexes | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
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 | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Log less information to the binary log and slow query log, if they have been activated.
| Command-Line Format | --log-slow-admin-statements | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Include slow administrative statements in the statements
written to the slow query log. Administrative statements
include ALTER TABLE,
ANALYZE TABLE,
CHECK TABLE,
CREATE INDEX,
DROP INDEX,
OPTIMIZE TABLE, and
REPAIR TABLE.
--log-slow-queries[=
file_name]
| Deprecated | 5.1.29, by slow-query-log | ||
| Command-Line Format | --log-slow-queries[=name] | ||
| System Variable Name | log_slow_queries | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
This option enables logging to the slow query log, which
contains entries for all queries that have taken more than
long_query_time seconds to
execute. See the descriptions of the
--log-long-format and
--log-short-format options for
details.
The log output destination can be selected with the
--log-output option 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 is removed (along with the
log_slow_queries system
variable) in MySQL 5.6. 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=file_name | ||
| Permitted Values | |||
| Type | file name | ||
| Default | tc.log | ||
The name of the memory-mapped transaction coordinator log file
(for XA transactions that affect multiple storage engines when
the binary log is disabled). The default name is
tc.log. The file is created under the
data directory if not given as a full path name. Currently,
this option is unused.
| Command-Line Format | --log-tc-size=# | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 24576 | ||
| Max Value | 18446744073709547520 | ||
The size in bytes of the memory-mapped transaction coordinator log. The default size is 24KB.
--log-warnings[=,
level]-W [
level]
| Command-Line Format | --log-warnings[=#] | ||
-W [#] | |||
| System Variable Name | log_warnings | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 1 | ||
| Min Value | 0 | ||
| Max Value | 18446744073709547520 | ||
Print out warnings such as Aborted
connection... to the error log. This option is
enabled (1) by default. To disable it, use
--log-warnings=0. Specifying
the option without a level value
increments the current value by 1. Enabling this option by
setting it greater than 0 is recommended, for example, if you
use replication (you get more information about what is
happening, such as messages about network failures and
reconnections). If the value is greater than 1, aborted
connections are written to the error log. See
Section B.5.2.11, “Communication Errors and Aborted Connections”.
If a slave server was started with
--log-warnings enabled, the
slave prints messages to the error log to provide information
about its status, such as the binary log and relay log
coordinates where it starts its job, when it is switching to
another relay log, when it reconnects after a disconnect, and
so forth. As of MySQL 5.1.38, the server logs messages about
statements that are unsafe for statement-based logging if
--log-warnings is greater than
0.
| Command-Line Format | --low-priority-updates | ||
| System Variable Name | low_priority_updates | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Give table-modifying operations
(INSERT,
REPLACE,
DELETE,
UPDATE) lower priority than
selects. This can also be done using {INSERT |
REPLACE | DELETE | UPDATE} LOW_PRIORITY ... to lower
the priority of only one query, or by SET
LOW_PRIORITY_UPDATES=1 to change the priority in one
thread. This affects only storage engines that use only
table-level locking (MyISAM,
MEMORY, MERGE). See
Section 8.7.2, “Table Locking Issues”.
--min-examined-row-limit=
number
| Introduced | 5.1.21 | ||
| Command-Line Format | --min-examined-row-limit=# | ||
| System Variable Name | min_examined_row_limit | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | 4294967295 | ||
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 | ||
| System Variable Name | locked_in_memory | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Lock the mysqld process in memory. This option might help if you have a problem where the operating system is causing mysqld to swap to disk.
--memlock works on systems that
support the mlockall() system call; this
includes Solaris, most Linux distributions that use a 2.4 or
newer kernel, and perhaps other Unix systems. 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;
Use of this option may require you to run the server as
root, which, for reasons of security, is
normally not a good idea. See
Section 6.1.5, “How to Run MySQL as a Normal User”.
On Linux and perhaps other systems, you can avoid the need
to run the server as root by changing the
limits.conf file. See the notes
regarding the memlock limit in
Section 8.9.7, “Enabling Large Page Support”.
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=# | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 1024 | ||
| Min Value | 1024 | ||
| Max Value | 16384 | ||
The block size to be used for MyISAM index
pages.
--myisam-recover[=
option[,option]...]]
| Command-Line Format | --myisam-recover[=name] | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | OFF | ||
| Valid Values | OFF | ||
DEFAULT | |||
BACKUP | |||
FORCE | |||
QUICK | |||
Set the MyISAM storage engine recovery
mode. The option value is any combination of the values of
DEFAULT, BACKUP,
FORCE, or QUICK. If you
specify multiple values, separate them by commas. Specifying
the option with no argument is the same as specifying
DEFAULT, and specifying with an explicit
value of "" disables recovery (same as not
giving the option). If recovery is enabled, each time
mysqld opens a MyISAM
table, it checks whether the table is marked as crashed or was
not closed properly. (The last option works only if you are
running with external locking disabled.) If this is the case,
mysqld runs a check on the table. If the
table was corrupted, mysqld attempts to
repair it.
The following options affect how the repair works.
| Option | Description |
|---|---|
DEFAULT | Recovery without backup, forcing, or quick checking. |
BACKUP | If the data file was changed during recovery, save a backup of the
|
FORCE | Run recovery even if we would lose more than one row from the
.MYD file. |
QUICK | Do not check the rows in the table if there are not any delete blocks. |
Before the server automatically repairs a table, it writes a
note about the repair to the error log. If you want to be able
to recover from most problems without user intervention, you
should use the options BACKUP,FORCE. This
forces a repair of a table even if some rows would be deleted,
but it keeps the old data file as a backup so that you can
later examine what happened.
Do not read any option files. If program startup fails due to
reading unknown options from an option file,
--no-defaults can be used to
prevent them from being read.
| Command-Line Format | --old-alter-table | ||
| System Variable Name | old_alter_table | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
When this option is given, the server does not use the
optimized method of processing an ALTER
TABLE operation. It reverts to using a temporary
table, copying over the data, and then renaming the temporary
table to the original, as used by MySQL 5.0 and earlier. For
more information on the operation of
ALTER TABLE, see
Section 13.1.7, “ALTER TABLE Syntax”.
| Command-Line Format | --old-style-user-limits | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Enable old-style user limits. (Before MySQL 5.0.3, account
resource limits were counted separately for each host from
which a user connected rather than per account row in the
user table.) See
Section 6.3.4, “Setting Account Resource Limits”.
| Command-Line 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 Section 22.4, “Debugging and Porting MySQL”.
As of MySQL 5.1.17, this option is deprecated and is removed
in MySQL 5.6. Use
--thread_handling=no-threads
instead.
| Command-Line Format | --open-files-limit=# | ||
| System Variable Name | open_files_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | platform dependent | ||
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(). Internally,
the maximum value for this option is the maximum unsigned
integer value, but the actual maximum is platform dependent.
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.
On Unix, the value cannot be set less than ulimit -n.
| Command-Line Format | --partition | ||
| Disabled by | skip-partition | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | ON | ||
Enables or disables user-defined partitioning support in the MySQL Server.
| Command-Line Format | --pid-file=file_name | ||
| System Variable Name | pid_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The path name of the process ID file. The server creates the file in the data directory unless an absolute path name is given to specify a different directory. This file is used by other programs such as mysqld_safe to determine the server's process ID.
Specifies an option that pertains to a server plugin. For
example, many storage engines can be built as plugins, and for
such engines, options for them can be specified with a
--plugin prefix. Thus, the
--innodb_file_per_table option
for InnoDB can be specified as
--plugin-innodb_file_per_table.
For boolean options that can be enabled or disabled, the
--skip prefix and other alternative formats
are supported as well (see
Section 4.2.5, “Program Option Modifiers”). For example,
--skip-plugin-innodb_file_per_table
disables innodb_file_per_table.
The rationale for the --plugin prefix is that
it enables plugin options to be specified unambiguously if
there is a name conflict with a built-in server option. For
example, were a plugin writer to name a plugin
“sql” and implement a “mode” option,
the option name might be
--sql-mode, which would
conflict with the built-in option of the same name. In such
cases, references to the conflicting name are resolved in
favor of the built-in option. To avoid the ambiguity, users
can specify the plugin option as
--plugin-sql-mode. Use of the
--plugin prefix for plugin options is
recommended to avoid any question of ambiguity.
| Introduced | 5.1.18 | ||
| Command-Line Format | --plugin-load=plugin_list | ||
| Permitted Values | |||
| Type | string | ||
This option tells the server to load the named plugins at
startup. The option value is a semicolon-separated list of
name=plugin_libraryname is the name of the
plugin, and plugin_library is the
name of the shared library that contains the plugin code. Each
library file must be located in the directory named by the
plugin_dir system variable.
For example, if plugins named myplug1 and
myplug2 have library files
myplug1.so and
myplug2.so, use this option to load them
at startup:
shell> mysqld --plugin-load="myplug1=myplug1.so;myplug2=myplug2.so"
Quotes are used around the argument value here because
semicolon (;) is interpreted as a special
character by some command interpreters. (Unix shells treat it
as a command terminator, for example.)
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.
If a plugin library is named without any preceding plugin name, the server loads all plugins in the library.
Each plugin is loaded for a single invocation of
mysqld only. After a restart, the plugin is
not loaded unless --plugin-load
is used again. This is in contrast to
INSTALL PLUGIN, which adds an
entry to the mysql.plugins table to cause
the plugin to be loaded for every normal server startup.
Under normal startup, the server determines which plugins to
load by reading the mysql.plugins system
table. If the server is started with the
--skip-grant-tables option, it
does not consult the mysql.plugins table
and does not load plugins listed there.
--plugin-load enables plugins
to be loaded even when
--skip-grant-tables is given.
--plugin-load also enables
plugins to be loaded at startup under configurations when
plugins cannot be loaded at runtime.
For additional information about plugin loading, see Section 5.1.8.1, “Installing and Uninstalling Plugins”.
This option was added in MySQL 5.1.18.
--port=,
port_num-P
port_num
| Command-Line Format | --port=# | ||
-P | |||
| System Variable Name | port | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 3306 | ||
| Min Value | 0 | ||
| Max Value | 65535 | ||
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.
| Introduced | 5.1.5 | ||
| Command-Line Format | --port-open-timeout=# | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
On some systems, when the server is stopped, the TCP/IP port might not become available immediately. If the server is restarted quickly afterward, its attempt to reopen the port can fail. This option indicates how many seconds the server should wait for the TCP/IP port to become free if it cannot be opened. The default is not to wait. This option was added in MySQL 5.1.5.
Print the program name and all options that it gets from option files.
| Command-Line Format | --remove [service_name] | ||
(Windows only) Remove a MySQL Windows service. The default
service name is MySQL if no
service_name value is given. For
more information, see Section 2.3.6.7, “Starting MySQL Server as a Microsoft Windows Service”.
| Command-Line Format | --safe-mode | ||
Skip some optimization stages. This option is deprecated and is removed in MySQL 5.6.
| Deprecated | 4.0.2 | ||
| Command-Line Format | --safe-show-database | ||
| System Variable Name | safe_show_database | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
This option is deprecated and does not do anything because
there is a SHOW DATABASES
privilege that can be used to control access to database names
on a per-account basis. See
Section 6.2.1, “Privileges Provided by MySQL”.
--safe-show-database is removed
in MySQL 5.5.
| Command-Line Format | --safe-user-create | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
If this option is enabled, a user cannot create new MySQL
users by using the GRANT
statement unless the user has the
INSERT privilege for the
mysql.user table or any column in the
table. If you want a user to have the ability to create new
users that have those privileges that the user has the right
to grant, you should grant the user the following privilege:
GRANT INSERT(user) ON mysql.user TO 'user_name'@'host_name';
This ensures that the user cannot change any privilege columns
directly, but has to use the
GRANT statement to give
privileges to other users.
| Command-Line Format | --secure-auth | ||
| System Variable Name | secure_auth | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
This option causes the server to block connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format. Use it to prevent all use of passwords employing the old format (and hence insecure communication over the network).
Server startup fails with an error if this option is enabled and the privilege tables are in pre-4.1 format. See Section B.5.2.4, “Client does not support authentication protocol”.
The mysql client also has a
--secure-auth option, which
prevents connections to a server if the server requires a
password in old format for the client account.
| Introduced | 5.1.17 | ||
| Command-Line Format | --secure-file-priv=path | ||
| System Variable Name | secure_file_priv | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
This option limits the effect of the
LOAD_FILE() function and the
LOAD DATA and
SELECT ... INTO
OUTFILE statements to work only with files in the
specified directory.
This option was added in MySQL 5.1.17.
| Command-Line Format | --shared-memory-base-name=name | ||
| System Variable Name | shared_memory | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Platform Specific | Windows | ||
Enable shared-memory connections by local clients. This option is available only on Windows.
--shared-memory-base-name=
name
| System Variable Name | shared_memory_base_name | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Platform Specific | Windows | ||
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 8.7.3, “Concurrent Inserts”.
| Command-Line Format | --skip-event-scheduler | ||
--disable-event-scheduler | |||
Turns the Event Scheduler OFF. This is not
the same as disabling the Event Scheduler, which requires
setting
--event-scheduler=DISABLED; see
The
--event-scheduler Option, for more
information.
This option causes the server to start without using the
privilege system at all, which gives anyone with access to the
server unrestricted access to all
databases. You can cause a running server to start
using the grant tables again by executing mysqladmin
flush-privileges or mysqladmin
reload command from a system shell, or by issuing a
MySQL FLUSH
PRIVILEGES statement after connecting to the server.
This option also suppresses loading of plugins that were
installed with the INSTALL
PLUGIN statement, user-defined functions (UDFs),
and, beginning with MySQL 5.1.17, scheduled events. To cause
plugins to be loaded anyway, use the
--plugin-load option.
Note that FLUSH
PRIVILEGES might be executed implicitly by other
actions performed after startup. For example,
mysql_upgrade flushes the privileges during
the upgrade procedure.
--skip-grant-tables is
unavailable if MySQL was configured with the
--disable-grant-options
option. See Section 2.11.4, “MySQL Source-Configuration Options”.
Disable use of the internal host cache for faster name-to-IP resolution. In this case, the server performs a DNS lookup every time a client connects. See Section 8.9.8, “DNS Lookup Optimization and the Host Cache”.
Disable the InnoDB storage engine. In this
case, the server will not start if the default storage engine
is set to InnoDB. Use
--default-storage-engine to set
the default to some other engine if necessary.
Disable the MERGE storage engine. This
option was added in MySQL 5.1.12 and removed in 5.1.14.
Do not resolve host names when checking client connections.
Use only IP addresses. If you use this option, all
Host column values in the grant tables must
be IP addresses or localhost. See
Section 8.9.8, “DNS Lookup Optimization and the Host Cache”.
Depending on the network configuration of your system and the
Host values for your accounts, clients may
need to connect using an explicit --host
option, such as --host=localhost or
--host=127.0.0.1.
Do not listen for TCP/IP connections at all. All interaction with mysqld must be made using named pipes or shared memory (on Windows) or Unix socket files (on Unix). This option is highly recommended for systems where only local clients are permitted. See Section 8.9.8, “DNS Lookup Optimization and the Host Cache”.
| Command-Line Format | --skip-partition | ||
--disable-partition | |||
Disables user-defined partitioning. Partitioned tables can be
seen using SHOW TABLES or by
querying the
INFORMATION_SCHEMA.TABLES table,
but cannot be created or modified, nor can data in such tables
be accessed. All partition-specific columns in the
INFORMATION_SCHEMA.PARTITIONS
table display NULL.
Since DROP TABLE removes table
definition (.frm) files, this statement
works on partitioned tables even when partitioning is disabled
using the option. The statement, however, does not remove
.par files associated with partitioned
tables in such cases. For this reason, you should avoid
dropping partitioned tables with partitioning disabled, or
take action to remove the orphaned .par
files manually.
Options that begin with --ssl
specify whether to permit clients to connect using SSL and
indicate where to find SSL keys and certificates. See
Section 6.3.6.4, “SSL Command Options”.
| Command-Line Format | --standalone | ||
| Platform Specific | Windows | ||
Available on Windows only; instructs the MySQL server not to run as a service.
--symbolic-links,
--skip-symbolic-links
| Command-Line Format | --symbolic-links | ||
Enable or disable symbolic link support. This option has different effects on Windows and Unix:
On Windows, enabling symbolic links enables you to
establish a symbolic link to a database directory by
creating a
db_name.sym
On Unix, enabling symbolic links means that you can link a
MyISAM index file or data file to
another directory with the INDEX
DIRECTORY or DATA DIRECTORY
options of the CREATE TABLE
statement. If you delete or rename the table, the files
that its symbolic links point to also are deleted or
renamed. See Section 8.9.6.2, “Using Symbolic Links for MyISAM Tables on Unix”.
| Command-Line 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 do not need
it by using the
--skip-safemalloc option.
| Command-Line Format | --skip-show-database | ||
| System Variable Name | skip_show_database | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
This option sets the
skip_show_database system
variable that controls who is permitted to use the
SHOW DATABASES statement. See
Section 5.1.4, “Server System Variables”.
| Command-Line Format | --skip-stack-trace | ||
Do not write stack traces. This option is useful when you are running mysqld under a debugger. On some systems, you also must use this option to get a core file. See Section 22.4, “Debugging and Porting MySQL”.
| Deprecated | 5.1.29 | ||
| Command-Line Format | --skip-thread-priority | ||
Disable using thread priorities for faster response time. This option is deprecated as of MySQL 5.1.29 and is removed in MySQL 5.6.
Prior to MySQL 5.1.29, mysqld makes a large
number of invalid calls to thread scheduling routines on
Linux. These calls do not affect performance noticeably but
may be a source of “noise” for debugging tools.
For example, they can overwhelm other information of more
interest in kernel logs. To avoid these calls, start the
server with the
--skip-thread-priority option.
| Introduced | 5.1.12 | ||
| Command-Line Format | --slow-query-log | from 5.1.29 | |
| System Variable Name | slow_query_log | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Specify the initial slow query log state. With no argument or
an argument of 1, the
--slow-query-log option enables
the log. If omitted or given with an argument of 0, the option
disables the log. This option was added in MySQL 5.1.12.
| Command-Line Format | --socket=file_name | ||
| System Variable Name | socket | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
| Default | /tmp/mysql.sock | ||
On Unix, this option specifies the Unix socket file to use
when listening for local connections. The default value is
/tmp/mysql.sock. If this option is given,
the server creates the file in the data directory unless an
absolute path name is given to specify a different directory.
On Windows, the option specifies the pipe name to use when
listening for local connections that use a named pipe. The
default value is MySQL (not case
sensitive).
--sql-mode=
value[,value[,value...]]
| Command-Line Format | --sql-mode=name | ||
| System Variable Name | sql_mode | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | set | ||
| Default | '' | ||
| Valid Values | ALLOW_INVALID_DATES | ||
ANSI_QUOTES | |||
ERROR_FOR_DIVISION_BY_ZERO | |||
HIGH_NOT_PRECEDENCE | |||
IGNORE_SPACE | |||
NO_AUTO_CREATE_USER | |||
NO_AUTO_VALUE_ON_ZERO | |||
NO_BACKSLASH_ESCAPES | |||
NO_DIR_IN_CREATE | |||
NO_ENGINE_SUBSTITUTION | |||
NO_FIELD_OPTIONS | |||
NO_KEY_OPTIONS | |||
NO_TABLE_OPTIONS | |||
NO_UNSIGNED_SUBTRACTION | |||
NO_ZERO_DATE | |||
NO_ZERO_IN_DATE | |||
ONLY_FULL_GROUP_BY | |||
PAD_CHAR_TO_FULL_LENGTH | |||
PIPES_AS_CONCAT | |||
REAL_AS_FLOAT | |||
STRICT_ALL_TABLES | |||
STRICT_TRANS_TABLES | |||
Set the SQL mode. See Section 5.1.7, “Server SQL Modes”.
MySQL installation programs may configure the SQL mode during the installation process. If the SQL mode differs from the default or from what you expect, check for a setting in an option file that the server reads at startup.
| Introduced | 5.1.8 | ||
| Command-Line Format | --sysdate-is-now | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
SYSDATE() by default returns
the time at which it executes, not the time at which the
statement in which it occurs begins executing. This differs
from the behavior of NOW().
This option causes SYSDATE() to
be an alias for NOW(). For
information about the implications for binary logging and
replication, see the description for
SYSDATE() in
Section 12.7, “Date and Time Functions” and for SET
TIMESTAMP in
Section 5.1.4, “Server System Variables”.
This option was added in MySQL 5.1.8.
--tc-heuristic-recover={COMMIT|ROLLBACK}
| Command-Line Format | --tc-heuristic-recover=name | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | COMMIT | ||
| Valid Values | COMMIT | ||
ROLLBACK | |||
The type of decision to use in the heuristic recovery process. Currently, this option is unused.
| Command-Line Format | --temp-pool | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | TRUE | ||
This option causes most temporary files created by the server to use a small set of names, rather than a unique name for each new file. This works around a problem in the Linux kernel dealing with creating many new files with different names. With the old behavior, Linux seems to “leak” memory, because it is being allocated to the directory entry cache rather than to the disk cache. As of MySQL 5.1.31, this option is ignored except on Linux.
| Command-Line Format | --transaction-isolation=name | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | REPEATABLE-READ | ||
| Valid Values | READ-UNCOMMITTED | ||
READ-COMMITTED | |||
REPEATABLE-READ | |||
SERIALIZABLE | |||
Sets the default transaction isolation level. The
level value can be
READ-UNCOMMITTED,
READ-COMMITTED,
REPEATABLE-READ, or
SERIALIZABLE. See
Section 13.3.6, “SET TRANSACTION Syntax”.
The default transaction isolation level can also be set at
runtime using the SET
TRANSACTION statement or by setting the
tx_isolation system variable.
--tmpdir=,
path-t
path
| Command-Line Format | --tmpdir=path | ||
-t | |||
| System Variable Name | tmpdir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The path of the directory to use for creating temporary files.
It might be useful if your default /tmp
directory resides on a partition that is too small to hold
temporary tables. This option accepts several paths that are
used in round-robin fashion. Paths should be separated by
colon characters (“:”) on Unix
and semicolon characters (“;”)
on Windows, 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.5.4.4, “Where MySQL Stores Temporary Files”. A replication slave needs
some of its temporary files to survive a machine restart so
that it can replicate temporary tables or
LOAD DATA
INFILE operations. If files in the temporary file
directory are lost when the server restarts, replication
fails.
--user={,
user_name|user_id}-u
{
user_name|user_id}
| Command-Line Format | --user=name | ||
-u name | |||
| Permitted Values | |||
| Type | string | ||
Run the mysqld server as the user having
the name user_name or the numeric
user ID user_id.
(“User” in this context refers to a system login
account, not a MySQL user listed in the grant tables.)
This option is mandatory when starting
mysqld as root. The
server changes its user ID during its startup sequence,
causing it to run as that particular user rather than as
root. See
Section 6.1.1, “Security Guidelines”.
To avoid a possible security hole where a user adds a
--user=root option to a
my.cnf file (thus causing the server to
run as root), mysqld
uses only the first --user
option specified and produces a warning if there are multiple
--user options. Options in
/etc/my.cnf and
$MYSQL_HOME/my.cnf are processed before
command-line options, so it is recommended that you put a
--user option in
/etc/my.cnf and specify a value other
than root. The option in
/etc/my.cnf is found before any other
--user options, which ensures
that the server runs as a user other than
root, and that a warning results if any
other --user option is found.
Use this option with the --help
option for detailed help.
--version, -V
Display version information and exit.
You can assign a value to a server system variable by using an
option of the form
--.
For example, var_name=value--key_buffer_size=32M
sets the key_buffer_size variable
to a value of 32MB.
Note that when you assign a value to a variable, MySQL might automatically correct the value to stay within a given range, or adjust the value to the closest permissible value if only certain values are permitted.
If you want to restrict the maximum value to which a variable can
be set at runtime with
SET, you
can define this by using the
--maximum-
command-line option.
var_name=value
You can change the values of most system variables for a running
server with the
SET
statement. See Section 13.7.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 8.9.2, “Tuning Server Parameters”, includes information on optimizing the server by tuning system variables.
The MySQL server maintains many system variables that indicate how
it is configured. Each system variable has a default value. System
variables can be set at server startup using options on the
command line or in an option file. Most of them can be changed
dynamically while the server is running by means of the
SET
statement, which enables you to modify operation of the server
without having to stop and restart it. You can refer to system
variable values in expressions.
There are several ways to see the names and values of system variables:
To see the values that a server will use based on its compiled-in defaults and any option files that it reads, use this command:
mysqld --verbose --help
To see the values that a server will use based on its compiled-in defaults, ignoring the settings in any option files, use this command:
mysqld --no-defaults --verbose --help
To see the current values used by a running server, use the
SHOW VARIABLES statement.
This section provides a description of each system variable. Variables with no version indicated are present in all MySQL 5.1 releases. For historical information concerning their implementation, please see http://dev.mysql.com/doc/refman/5.0/en/, and http://dev.mysql.com/doc/refman/4.1/en/.
The following table lists all available system variables.
Table 5.2 System Variable Summary
For additional system variable information, see these sections:
Section 5.1.5, “Using System Variables”, discusses the syntax for setting and displaying system variable values.
Section 5.1.5.2, “Dynamic System Variables”, lists the variables that can be set at runtime.
Information on tuning system variables can be found in Section 8.9.2, “Tuning Server Parameters”.
Section 14.6.7, “InnoDB Startup Options and System Variables”, lists
InnoDB system variables.
Section 17.3.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.
Some system variables control the size of buffers or caches. For a given buffer, the server might need to allocate internal data structures. These structures typically are allocated from the total memory allocated to the buffer, and the amount of space required might be platform dependent. This means that when you assign a value to a system variable that controls a buffer size, the amount of space actually available might differ from the value assigned. In some cases, the amount might be less than the value assigned. It is also possible that the server will adjust a value upward. For example, if you assign a value of 0 to a variable for which the minimal value is 1024, the server will set the value to 1024.
Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.
Some system variables take file name values. Unless otherwise
specified, the default file location is the data directory if the
value is a relative path name. To specify the location explicitly,
use an absolute path name. Suppose that the data directory is
/var/mysql/data. If a file-valued variable is
given as a relative path name, it will be located under
/var/mysql/data. If the value is an absolute
path name, its location is as given by the path name.
| System Variable Name | autocommit | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | ON | ||
The autocommit mode. If set to 1, all changes to a table take
effect immediately. If set to 0, you must use
COMMIT to accept a transaction
or ROLLBACK
to cancel it. If autocommit
is 0 and you change it to 1, MySQL performs an automatic
COMMIT of any open transaction.
Another way to begin a transaction is to use a
START
TRANSACTION or
BEGIN
statement. See Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.
By default, client connections begin with
autocommit set to 1. To cause
clients to begin with a default of 0, set the server's
init_connect system variable:
SET GLOBAL init_connect='SET autocommit=0';
The init_connect variable can
also be set on the command line or in an option file. To set
the variable as just shown using an option file, include these
lines:
[mysqld] init_connect='SET autocommit=0'
The content of init_connect
is not executed for users that have the
SUPER privilege.
| System Variable Name | automatic_sp_privileges | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | TRUE | ||
When this variable has a value of 1 (the default), the server
automatically grants the
EXECUTE and
ALTER ROUTINE privileges to the
creator of a stored routine, if the user cannot already
execute and alter or drop the routine. (The
ALTER ROUTINE privilege is
required to drop the routine.) The server also automatically
drops those privileges from the creator when the routine is
dropped. If
automatic_sp_privileges is 0,
the server does not automatically add or drop these
privileges.
The creator of a routine is the account used to execute the
CREATE statement for it. This might not be
the same as the account named as the
DEFINER in the routine definition.
See also Section 19.2.2, “Stored Routines and MySQL Privileges”.
| System Variable Name | back_log | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 50 | ||
| Min Value | 1 | ||
| Max Value | 65535 | ||
The number of outstanding connection requests MySQL can have.
This comes into play when the main MySQL thread gets very many
connection requests in a very short time. It then takes some
time (although very little) for the main thread to check the
connection and start a new thread. The
back_log value indicates how
many requests can be stacked during this short time before
MySQL momentarily stops answering new requests. You need to
increase this only if you expect a large number of connections
in a short period of time.
In other words, this value is the size of the listen queue for
incoming TCP/IP connections. Your operating system has its own
limit on the size of this queue. The manual page for the Unix
listen() system call should have more
details. Check your OS documentation for the maximum value for
this variable. back_log
cannot be set higher than your operating system limit.
| Command-Line Format | --basedir=path | ||
-b | |||
| System Variable Name | basedir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The MySQL installation base directory. This variable can be
set with the --basedir option.
Relative path names for other variables usually are resolved
relative to the base directory.
If set to 1, all temporary tables are stored on disk rather
than in memory. This is a little slower, but the error
The table does not occur for
tbl_name is
fullSELECT operations that require
a large temporary table. The default value for a new
connection is 0 (use in-memory temporary tables). Normally,
you should never need to set this variable, because in-memory
tables are automatically converted to disk-based tables as
required.
This variable was formerly named
sql_big_tables.
| Command-Line Format | --bulk_insert_buffer_size=# | ||
| System Variable Name | bulk_insert_buffer_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 8388608 | ||
| Min Value | 0 | ||
| Max Value | 18446744073709547520 | ||
MyISAM uses a special tree-like cache to
make bulk inserts faster for
INSERT ...
SELECT, INSERT ... VALUES (...), (...),
..., and
LOAD DATA
INFILE when adding data to nonempty tables. This
variable limits the size of the cache tree in bytes per
thread. Setting it to 0 disables this optimization. The
default value is 8MB.
| System Variable Name | character_set_client | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The character set for statements that arrive from the client.
The session value of this variable is set using the character
set requested by the client when the client connects to the
server. (Many clients support a
--default-character-set option to enable this
character set to be specified explicitly. See also
Section 10.1.4, “Connection Character Sets and Collations”.) The global value of the
variable is used to set the session value in cases when the
client-requested value is unknown or not available, or the
server is configured to ignore client requests:
The client is from a version of MySQL older than MySQL 4.1, and thus does not request a character set.
The client requests a character set not known to the
server. For example, a Japanese-enabled client requests
sjis when connecting to a server not
configured with sjis support.
mysqld was started with the
--skip-character-set-client-handshake
option, which causes it to ignore client character set
configuration. This reproduces MySQL 4.0 behavior and is
useful should you wish to upgrade the server without
upgrading all the clients.
ucs2 cannot be used as a client character
set, which means that it also does not work for SET
NAMES or SET CHARACTER SET.
| System Variable Name | character_set_connection | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The character set for the current connection. Used for literals that do not have an explicit character set specification and for number-to-string conversion.
| System Variable Name | character_set_database | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Footnote | This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. | ||
| Permitted Values | |||
| Type | string | ||
The character set used by the default database. The server
sets this variable whenever the default database changes. If
there is no default database, the variable has the same value
as character_set_server.
| Introduced | 5.1.6 | ||
| Command-Line Format | --character-set-filesystem=name | ||
| System Variable Name | character_set_filesystem | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
| Default | binary | ||
The file system character set. This variable is used to
interpret string literals that refer to file names, such as in
the LOAD DATA
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 multibyte file names are
permitted, a different value may be more appropriate. For
example, if the system represents file names using UTF-8, set
character_set_filesystem to
'utf8'. This variable was added in MySQL
5.1.6.
| System Variable Name | character_set_results | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The character set used for returning query results such as result sets or error messages to the client.
| Command-Line Format | --character-set-server | ||
| System Variable Name | character_set_server | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
| Default | latin1 | ||
The server's default character set.
| System Variable Name | character_set_system | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
| Default | utf8 | ||
The character set used by the server for storing identifiers.
The value is always utf8.
| Command-Line Format | --character-sets-dir=path | ||
| System Variable Name | character_sets_dir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The directory where character sets are installed.
| System Variable Name | collation_connection | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The collation of the connection character set.
| System Variable Name | collation_database | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Footnote | This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. | ||
| Permitted Values | |||
| Type | string | ||
The collation used by the default database. The server sets
this variable whenever the default database changes. If there
is no default database, the variable has the same value as
collation_server.
| Command-Line Format | --collation-server | ||
| System Variable Name | collation_server | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
| Default | latin1_swedish_ci | ||
The server's default collation.
| Command-Line Format | --completion_type=# | ||
| System Variable Name | completion_type | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Valid Values | 0 | ||
1 | |||
2 | |||
The transaction completion type. This variable can take the values shown in the following table.
| Value | Description |
|---|---|
| 0 | COMMIT and
ROLLBACK
are unaffected. This is the default value. |
| 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.) |
| 2 | COMMIT and
ROLLBACK
are equivalent to COMMIT RELEASE
and ROLLBACK RELEASE, respectively.
(The server disconnects after terminating the
transaction.) |
completion_type affects
transactions that begin with
START
TRANSACTION or
BEGIN and
end with COMMIT or
ROLLBACK. It
does not apply to implicit commits resulting from execution of
the statements listed in Section 13.3.3, “Statements That Cause an Implicit Commit”. It
also does not apply for
XA
COMMIT,
XA
ROLLBACK, or when
autocommit=1.
| Command-Line Format | --concurrent_insert[=#] | ||
| System Variable Name | concurrent_insert | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | TRUE | ||
If 1 (the default), MySQL permits
INSERT and
SELECT statements to run
concurrently for MyISAM tables that have no
free blocks in the middle of the data file. If you start
mysqld with
--skip-new,
this variable is set to 0.
This variable can take three integer values.
| Value | Description |
|---|---|
| 0 | Disables concurrent inserts |
| 1 | (Default) Enables concurrent insert for MyISAM tables
that do not 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 8.7.3, “Concurrent Inserts”.
| Command-Line Format | --connect_timeout=# | ||
| System Variable Name | connect_timeout | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values (<= 5.1.22) | |||
| Type | numeric | ||
| Default | 5 | ||
| Min Value | 2 | ||
| Max Value | 31536000 | ||
| Permitted Values (>= 5.1.23) | |||
| Type | numeric | ||
| Default | 10 | ||
| Min Value | 2 | ||
| Max Value | 31536000 | ||
The number of seconds that the mysqld
server waits for a connect packet before responding with
Bad handshake. The default value is 10
seconds 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=path | ||
-h | |||
| System Variable Name | datadir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The MySQL data directory. This variable can be set with the
--datadir option.
This variable is unused.
This variable is unused.
| Command-Line Format | --debug[=debug_options] | ||
| System Variable Name | debug | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type (Unix) | string | ||
| Default | d:t:i:o,/tmp/mysqld.trace | ||
| Permitted Values | |||
| Type (Windows) | string | ||
| Default | d:t:i:O,\mysqld.trace | ||
This variable indicates the current debugging settings. It is
available only for servers built with debugging support. The
initial value comes from the value of instances of the
--debug option given at server
startup. The global and session values may be set at runtime;
the SUPER privilege is
required, even for the session value.
Assigning a value that begins with + or
- cause the value to added to or subtracted
from the current value:
mysql>SET debug = 'T';mysql>SELECT @@debug;+---------+ | @@debug | +---------+ | T | +---------+ mysql>SET debug = '+P';mysql>SELECT @@debug;+---------+ | @@debug | +---------+ | P:T | +---------+ mysql>SET debug = '-P';mysql>SELECT @@debug;+---------+ | @@debug | +---------+ | T | +---------+
This variable was added in MySQL 5.1.7.
| Introduced | 5.1.41 | ||
| System Variable Name | debug_sync | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
This variable is the user interface to the Debug Sync
facility. Use of Debug Sync requires that MySQL be configured
with the --enable-debug-sync
option (see Section 2.11.4, “MySQL Source-Configuration Options”).
If Debug Sync is not compiled in, this system variable is not
available.
The global variable value is read only and indicates whether
the facility is enabled. By default, Debug Sync is disabled
and the value of debug_sync
is OFF. If the server is started with
--debug-sync-timeout=,
where NN is a timeout value greater
than 0, Debug Sync is enabled and the value of
debug_sync is ON -
current signal followed by the signal name. Also,
N becomes the default timeout for
individual synchronization points.
The session value can be read by any user and will have the
same value as the global variable. The session value can be
set by users that have the
SUPER privilege to control
synchronization points.
For a description of the Debug Sync facility and how to use synchronization points, see MySQL Internals: Test Synchronization.
This variable was added in MySQL 5.1.41.
| Command-Line Format | --default_week_format=# | ||
| System Variable Name | default_week_format | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | 7 | ||
The default mode value to use for the
WEEK() function. See
Section 12.7, “Date and Time Functions”.
| Command-Line Format | --delay-key-write[=name] | ||
| System Variable Name | delay_key_write | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | ON | ||
| Valid Values | ON | ||
OFF | |||
ALL | |||
This option applies only to MyISAM tables.
It can have one of the following values to affect handling of
the DELAY_KEY_WRITE table option that can
be used in CREATE TABLE
statements.
| Option | Description |
|---|---|
OFF | DELAY_KEY_WRITE is ignored. |
ON | MySQL honors any DELAY_KEY_WRITE option specified in
CREATE TABLE
statements. This is the default value. |
ALL | All new opened tables are treated as if they were created with the
DELAY_KEY_WRITE option enabled. |
If DELAY_KEY_WRITE is enabled for a table,
the key buffer is not flushed for the table on every index
update, but only when the table is closed. This speeds up
writes on keys a lot, but if you use this feature, you should
add automatic checking of all MyISAM tables
by starting the server with the
--myisam-recover option (for
example,
--myisam-recover=BACKUP,FORCE).
See Section 5.1.3, “Server Command Options”, and
Section 14.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=# | ||
| System Variable Name | delayed_insert_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 100 | ||
| Min Value | 1 | ||
| Max Value | 18446744073709547520 | ||
After inserting
delayed_insert_limit delayed
rows, the INSERT DELAYED
handler thread checks whether there are any
SELECT statements pending. If
so, it permits them to execute before continuing to insert
delayed rows.
| Command-Line Format | --delayed_insert_timeout=# | ||
| System Variable Name | delayed_insert_timeout | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 300 | ||
How many seconds an INSERT
DELAYED handler thread should wait for
INSERT statements before
terminating.
| Command-Line Format | --delayed_queue_size=# | ||
| System Variable Name | delayed_queue_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 1000 | ||
| Min Value | 1 | ||
| Max Value | 18446744073709547520 | ||
This is a per-table limit on the number of rows to queue when
handling INSERT DELAYED
statements. If the queue becomes full, any client that issues
an INSERT DELAYED statement
waits until there is room in the queue again.
| Command-Line Format | --div_precision_increment=# | ||
| System Variable Name | div_precision_increment | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 4 | ||
| Min Value | 0 | ||
| Max Value | 30 | ||
This variable indicates the number of digits by which to
increase the scale of the result of division operations
performed with the
/ operator.
The default value is 4. The minimum and maximum values are 0
and 30, respectively. The following example illustrates the
effect of increasing the default value.
mysql>SELECT 1/7;+--------+ | 1/7 | +--------+ | 0.1429 | +--------+ mysql>SET div_precision_increment = 12;mysql>SELECT 1/7;+----------------+ | 1/7 | +----------------+ | 0.142857142857 | +----------------+
For more information, see Section 22.4.3, “The DBUG Package”.
| Command-Line Format | --engine-condition-pushdown | ||
| System Variable Name | engine_condition_pushdown | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | ON | ||
The engine condition pushdown optimization enables processing for certain comparisons to be “pushed down” to the storage engine level for more efficient execution. For more information, see Section 8.3.1.5, “Engine Condition Pushdown Optimization”.
Engine condition pushdown is used only by the
NDBCLUSTER storage engine.
Enabling this optimization on a MySQL Server acting as a MySQL
Cluster SQL node causes WHERE conditions on
unindexed columns to be evaluated on the cluster's data nodes
and only the rows that match to be sent back to the SQL node
that issued the query. This greatly reduces the amount of
cluster data that must be sent over the network, increasing
the efficiency with which results are returned.
The engine_condition_pushdown
variable controls whether engine condition pushdown is
enabled. By default, this variable is ON
(1). Setting it to OFF (0) disables
pushdown.
The number of errors that resulted from the last statement that generated messages. This variable is read only. See Section 13.7.5.18, “SHOW ERRORS Syntax”.
| Introduced | 5.1.6 | ||
| Command-Line Format | --event-scheduler[=value] | ||
| System Variable Name | event_scheduler | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | OFF | ||
| Valid Values | ON | ||
OFF | |||
DISABLED | |||
This variable indicates the status of the Event Scheduler; 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=# | ||
| System Variable Name | expire_logs_days | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | 99 | ||
The number of days for automatic binary log file removal. The default is 0, which means “no automatic removal.” Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in Section 5.2, “MySQL Server Logs”.
To remove binary log files manually, use the
PURGE BINARY LOGS statement.
See Section 13.4.1.1, “PURGE BINARY LOGS Syntax”.
| Command-Line Format | --flush | ||
| System Variable Name | flush | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
If ON, the server flushes (synchronizes)
all changes to disk after each SQL statement. Normally, MySQL
does a write of all changes to disk only after each SQL
statement and lets the operating system handle the
synchronizing to disk. See Section B.5.4.2, “What to Do If MySQL Keeps Crashing”. This
variable is set to ON if you start
mysqld with the
--flush option.
| Command-Line Format | --flush_time=# | ||
| System Variable Name | flush_time | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
If this is set to a nonzero value, all tables are closed every
flush_time seconds to free up
resources and synchronize unflushed data to disk. This option
is best used only on systems with minimal resources.
If set to 1 (the default), foreign key constraints for
InnoDB tables are checked. If set to 0,
they are ignored. Disabling foreign key checking can be useful
for reloading InnoDB tables in an order
different from that required by their parent/child
relationships. See
Section 14.6.5.6, “InnoDB and FOREIGN KEY Constraints”.
Setting foreign_key_checks to
0 also affects data definition statements:
DROP
SCHEMA drops a schema even if it contains tables
that have foreign keys that are referred to by tables outside
the schema, and DROP TABLE
drops tables that have foreign keys that are referred to by
other tables.
Setting foreign_key_checks
to 1 does not trigger a scan of the existing table data.
Therefore, rows added to the table while
foreign_key_checks = 0 will
not be verified for consistency.
| Command-Line Format | --ft_boolean_syntax=name | ||
| System Variable Name | ft_boolean_syntax | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
| Default | + -><()~*:""&| | ||
The list of operators supported by boolean full-text searches
performed using IN BOOLEAN MODE. See
Section 12.9.2, “Boolean Full-Text Searches”.
The default variable value is
'+ -><()~*:""&|'. The rules
for changing the value are as follows:
Operator function is determined by position within the string.
The replacement value must be 14 characters.
Each character must be an ASCII nonalphanumeric character.
Either the first or second character must be a space.
No duplicates are permitted except the phrase quoting operators in positions 11 and 12. These two characters are not required to be the same, but they are the only two that may be.
Positions 10, 13, and 14 (which by default are set to
“:”,
“&”, and
“|”) are reserved for
future extensions.
| Command-Line Format | --ft_max_word_len=# | ||
| System Variable Name | ft_max_word_len | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Min Value | 10 | ||
The maximum length of the word to be included in a
FULLTEXT index.
FULLTEXT indexes must be rebuilt after
changing this variable. Use REPAIR TABLE
.
tbl_name QUICK
| Command-Line Format | --ft_min_word_len=# | ||
| System Variable Name | ft_min_word_len | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 4 | ||
| Min Value | 1 | ||
The minimum length of the word to be included in a
FULLTEXT index.
FULLTEXT indexes must be rebuilt after
changing this variable. Use REPAIR TABLE
.
tbl_name QUICK
| Command-Line Format | --ft_query_expansion_limit=# | ||
| System Variable Name | ft_query_expansion_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 20 | ||
| Min Value | 0 | ||
| Max Value | 1000 | ||
The number of top matches to use for full-text searches
performed using WITH QUERY EXPANSION.
| Command-Line Format | --ft_stopword_file=file_name | ||
| System Variable Name | ft_stopword_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The file from which to read the list of stopwords for
full-text searches. The server looks for the file in the data
directory unless an absolute path name is given to specify a
different directory. All the words from the file are used;
comments are not honored. By default, a
built-in list of stopwords is used (as defined in the
storage/myisam/ft_static.c file). Setting
this variable to the empty string ('')
disables stopword filtering. See also
Section 12.9.4, “Full-Text Stopwords”.
FULLTEXT indexes must be rebuilt after
changing this variable or the contents of the stopword file.
Use REPAIR TABLE
.
tbl_name QUICK
| Introduced | 5.1.12 | ||
| Command-Line Format | --general-log | ||
| System Variable Name | general_log | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Whether the general query log is enabled. The value can be 0
(or OFF) to disable the log or 1 (or
ON) to enable the log. The default value
depends on whether the
--general_log option is given
(--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.
| Introduced | 5.1.12 | ||
| Command-Line Format | --general-log-file=file_name | from 5.1.29 | |
| System Variable Name | general_log_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | file name | ||
| Default | host_name.log | ||
The name of the general query log file. The default value is
host_name.log--general_log_file option
(--log before MySQL 5.1.29).
This variable was added in MySQL 5.1.12.
| Command-Line Format | --group_concat_max_len=# | ||
| System Variable Name | group_concat_max_len | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 1024 | ||
| Min Value | 4 | ||
| Max Value | 18446744073709547520 | ||
The maximum permitted 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 capability is
present, NO if not. If present, the
profiling system variable controls whether
this capability is enabled or disabled. See
Section 13.7.5.33, “SHOW PROFILES Syntax”.
This variable was added in MySQL 5.1.24. It is renamed to
have_profiling in MySQL 5.5.
YES if the crypt()
system call is available to the server, NO
if not. If not, the ENCRYPT()
function cannot be used.
YES if mysqld supports
CSV tables, NO if not.
This variable is deprecated and is removed in MySQL 5.6. Use
SHOW ENGINES instead.
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.
This variable is deprecated and is removed in MySQL 5.6. Use
SHOW ENGINES instead.
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.
This variable is deprecated and is removed in MySQL 5.6. Use
SHOW PLUGINS instead. For more
information, see Chapter 18, Partitioning.
YES if mysqld supports
the query cache, NO if not.
| Introduced | 5.1.5 | ||
| Removed | 5.1.15 | ||
| System Variable Name | have_row_based_replication | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | boolean | ||
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.
DISABLED indicates that the server was
compiled with SSL support, but was not started with the
appropriate
--ssl- options.
For more information, see
Section 6.3.6.2, “Configuring MySQL for SSL”.
xxx
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. If the server
is started with the
--skip-symbolic-links
option, the value is DISABLED.
| Introduced | 5.1.17 | ||
| System Variable Name | hostname | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The server sets this variable to the server host name at startup. This variable was added in MySQL 5.1.17.
This variable is a synonym for the
last_insert_id variable. It
exists for compatibility with other database systems. You can
read its value with SELECT @@identity, and
set it using SET identity.
| Command-Line Format | --init-connect=name | ||
| System Variable Name | init_connect | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
A string to be executed by the server for each client that
connects. The string consists of one or more SQL statements,
separated by semicolon characters. For example, each client
session begins by default with autocommit mode enabled. There
is no global autocommit
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';
The init_connect variable can
also be set on the command line or in an option file. To set
the variable as just shown using an option file, include these
lines:
[mysqld] init_connect='SET autocommit=0'
The content of init_connect
is not executed for users that have the
SUPER privilege. This is done
so that an erroneous value for
init_connect does not prevent
all clients from connecting. For example, the value might
contain a statement that has a syntax error, thus causing
client connections to fail. Not executing
init_connect for users that
have the SUPER privilege
enables them to open a connection and fix the
init_connect value.
| Command-Line Format | --init-file=file_name | ||
| System Variable Name | init_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The name of the file specified with the
--init-file option when you
start the server. This should be a file containing SQL
statements that you want the server to execute when it starts.
Each statement must be on a single line and should not include
comments. No statement terminator such as
;, \g, or
\G should be given at the end of each
statement.
Note that the --init-file
option is unavailable if MySQL was configured with the
--disable-grant-options
option. See Section 2.11.4, “MySQL Source-Configuration Options”.
innodb_
xxx
InnoDB system variables are
listed in Section 14.6.7, “InnoDB Startup Options and System Variables”. These variables
control many aspects of storage, memory use, and I/O patterns
for InnoDB tables, and are especially
important in MySQL 5.5 and higher, where
InnoDB is the default
storage engine.
The value to be used by the following
INSERT or
ALTER TABLE statement when
inserting an AUTO_INCREMENT value. This is
mainly used with the binary log.
| Command-Line Format | --interactive_timeout=# | ||
| System Variable Name | interactive_timeout | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 28800 | ||
| Min Value | 1 | ||
The number of seconds the server waits for activity on an
interactive connection before closing it. An interactive
client is defined as a client that uses the
CLIENT_INTERACTIVE option to
mysql_real_connect(). See also
wait_timeout.
| Command-Line Format | --join_buffer_size=# | ||
| System Variable Name | join_buffer_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type (Other) | numeric | ||
| Default | 131072 | ||
| Min Value | 8200 | ||
| Max Value | 4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type (Other) | numeric | ||
| Default | 131072 | ||
| Min Value | 8228 | ||
| Max Value | 18446744073709547520 | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type (Windows) | numeric | ||
| Default | 131072 | ||
| Min Value | 8200 | ||
| Max Value | 4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type (Windows) | numeric | ||
| Default | 131072 | ||
| Min Value | 8228 | ||
| Max Value | 4294967295 | ||
The minimum size of the buffer that is used for plain index
scans, range index scans, and joins that do not use indexes
and thus perform full table scans. Normally, the best way to
get fast joins is to add indexes. Increase the value of
join_buffer_size to get a
faster full join when adding indexes is not possible. One join
buffer is allocated for each full join between two tables. For
a complex join between several tables for which indexes are
not used, multiple join buffers might be necessary.
There is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.
The maximum permissible setting for
join_buffer_size is
4GB–1. As of MySQL 5.1.23, larger values are permitted
for 64-bit platforms (except 64-bit Windows, for which large
values are truncated to 4GB–1 with a warning).
For additional information about join buffering, see Section 8.3.1.8, “Nested-Loop Join Algorithms”.
| Introduced | 5.1.21 | ||
| Command-Line Format | --keep_files_on_create=# | ||
| System Variable Name | keep_files_on_create | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
If a MyISAM table is created with no
DATA DIRECTORY option, the
.MYD file is created in the database
directory. By default, if MyISAM finds an
existing .MYD file in this case, it
overwrites it. The same applies to .MYI
files for tables created with no INDEX
DIRECTORY option. To suppress this behavior, set the
keep_files_on_create variable
to ON (1), in which case
MyISAM will not overwrite existing files
and returns an error instead. The default value is
OFF (0).
If a MyISAM table is created with a
DATA DIRECTORY or INDEX
DIRECTORY option and an existing
.MYD or .MYI file is
found, MyISAM always returns an error. It will not overwrite a
file in the specified directory.
This variable was added in MySQL 5.1.23.
| Command-Line Format | --key_buffer_size=# | ||
| System Variable Name | key_buffer_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values (<= 5.1.22) | |||
| Type | numeric | ||
| Default | 8388608 | ||
| Min Value | 8 | ||
| Max Value | 4294967295 | ||
| Permitted Values (>= 5.1.23) | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 8388608 | ||
| Min Value | 8 | ||
| Max Value | 4294967295 | ||
| Permitted Values (>= 5.1.23) | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 8388608 | ||
| Min Value | 8 | ||
| Max Value | OS_PER_PROCESS_LIMIT | ||
Index blocks for MyISAM tables are buffered
and are shared by all threads.
key_buffer_size is the size
of the buffer used for index blocks. The key buffer is also
known as the key cache.
The maximum permissible setting for
key_buffer_size is
4GB–1 on 32-bit platforms. As of MySQL 5.1.23, larger
values are permitted for 64-bit platforms, except 64-bit
Windows prior to MySQL 5.1.31, for which large values are
truncated to 4GB–1 with a warning. As of MySQL 5.1.31,
larger values are also permitted for 64-bit Windows. The
effective maximum size might be less, depending on your
available physical RAM and per-process RAM limits imposed by
your operating system or hardware platform. The value of this
variable indicates the amount of memory requested. Internally,
the server allocates as much memory as possible up to this
amount, but the actual allocation might be less.
You can increase the value to get better index handling for
all reads and multiple writes; on a system whose primary
function is to run MySQL using the
MyISAM storage engine, 25% of the
machine's total memory is an acceptable value for this
variable. However, you should be aware that, if you make the
value too large (for example, more than 50% of the
machine's total memory), your system might start to page
and become extremely slow. This is because MySQL relies on the
operating system to perform file system caching for data
reads, so you must leave some room for the file system cache.
You should also consider the memory requirements of any other
storage engines that you may be using in addition to
MyISAM.
For even more speed when writing many rows at the same time,
use LOCK TABLES. See
Section 8.3.2.1, “Speed of INSERT Statements”.
You can check the performance of the key buffer by issuing a
SHOW STATUS statement and
examining the
Key_read_requests,
Key_reads,
Key_write_requests, and
Key_writes status variables.
(See Section 13.7.5, “SHOW Syntax”.) The
Key_reads/Key_read_requests ratio should
normally be less than 0.01. The
Key_writes/Key_write_requests ratio is
usually near 1 if you are using mostly updates and deletes,
but might be much smaller if you tend to do updates that
affect many rows at the same time or if you are using the
DELAY_KEY_WRITE table option.
The fraction of the key buffer in use can be determined using
key_buffer_size in
conjunction with the
Key_blocks_unused status
variable and the buffer block size, which is available from
the key_cache_block_size
system variable:
1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)
This value is an approximation because some space in the key buffer is allocated internally for administrative structures. Factors that influence the amount of overhead for these structures include block size and pointer size. As block size increases, the percentage of the key buffer lost to overhead tends to decrease. Larger blocks results in a smaller number of read operations (because more keys are obtained per read), but conversely an increase in reads of keys that are not examined (if not all keys in a block are relevant to a query).
It is possible to create multiple MyISAM
key caches. The size limit of 4GB applies to each cache
individually, not as a group. See
Section 8.6.1, “The MyISAM Key Cache”.
| Command-Line Format | --key_cache_age_threshold=# | ||
| System Variable Name | key_cache_age_threshold | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 300 | ||
| Min Value | 100 | ||
| Max Value | 18446744073709547520 | ||
This value controls the demotion of buffers from the hot sublist of a key cache to the warm sublist. Lower values cause demotion to happen more quickly. The minimum value is 100. The default value is 300. See Section 8.6.1, “The MyISAM Key Cache”.
| Command-Line Format | --key_cache_block_size=# | ||
| System Variable Name | key_cache_block_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 1024 | ||
| Min Value | 512 | ||
| Max Value | 16384 | ||
The size in bytes of blocks in the key cache. The default value is 1024. See Section 8.6.1, “The MyISAM Key Cache”.
| Command-Line Format | --key_cache_division_limit=# | ||
| System Variable Name | key_cache_division_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 100 | ||
| Min Value | 1 | ||
| Max Value | 100 | ||
The division point between the hot and warm sublists of the key cache buffer list. The value is the percentage of the buffer list to use for the warm sublist. Permissible values range from 1 to 100. The default value is 100. See Section 8.6.1, “The MyISAM Key Cache”.
| Command-Line Format | --language=name | ||
-L | |||
| System Variable Name | language | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
| Default | /usr/local/mysql/share/mysql/english/ | ||
The directory where error messages are located. See Section 10.2, “Setting the Error Message Language”.
| System Variable Name | large_files_support | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
Whether mysqld was compiled with options for large file support.
| Command-Line Format | --large-pages | ||
| System Variable Name | large_pages | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Platform Specific | Linux | ||
| Permitted Values | |||
| Type (Linux) | boolean | ||
| Default | FALSE | ||
Whether large page support is enabled (via the
--large-pages option). See
Section 8.9.7, “Enabling Large Page Support”.
| System Variable Name | large_page_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type (Linux) | numeric | ||
| Default | 0 | ||
If large page support is enabled, this shows the size of memory pages. Currently, large memory pages are supported only on Linux; on other platforms, the value of this variable is always 0. See Section 8.9.7, “Enabling Large Page Support”.
The value to be returned from
LAST_INSERT_ID(). This is
stored in the binary log when you use
LAST_INSERT_ID() in a statement
that updates a table. Setting this variable does not update
the value returned by the
mysql_insert_id() C API
function.
| Introduced | 5.1.12 | ||
| System Variable Name | lc_time_names | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
This variable specifies the locale that controls the language
used to display day and month names and abbreviations. This
variable affects the output from the
DATE_FORMAT(),
DAYNAME() and
MONTHNAME() functions. Locale
names are POSIX-style values such as
'ja_JP' or 'pt_BR'. The
default value is 'en_US' regardless of your
system's locale setting. For further information, see
Section 10.7, “MySQL Server Locale Support”. This variable was added in
MySQL 5.1.12.
| System Variable Name | license | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
| Default | GPL | ||
The type of license the server has.
| System Variable Name | local_infile | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
Whether LOCAL is supported for
LOAD DATA
INFILE statements. If this variable is disabled,
clients cannot use LOCAL in
LOAD DATA statements. See
Section 6.1.6, “Security Issues with LOAD DATA LOCAL”.
| System Variable Name | locked_in_memory | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Deprecated | 5.1.29, by general-log | ||
| Command-Line Format | --log[=file_name] | ||
-l | |||
| System Variable Name | log | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | file name | ||
Whether logging of all statements to the general query log is enabled. See Section 5.2.3, “The General Query Log”.
This variable is deprecated as of MySQL 5.1.29 and is removed
in MySQL 5.6. Use general_log
instead.
log_bin_trust_function_creators
| Command-Line Format | --log-bin-trust-function-creators | ||
| System Variable Name | log_bin_trust_function_creators | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
This variable applies when binary logging is enabled. It
controls whether stored function creators can be trusted not
to create stored functions that will cause unsafe events to be
written to the binary log. If set to 0 (the default), users
are not permitted to create or alter stored functions unless
they have the SUPER privilege
in addition to the CREATE
ROUTINE or ALTER
ROUTINE privilege. A setting of 0 also enforces the
restriction that a function must be declared with the
DETERMINISTIC characteristic, or with the
READS SQL DATA or NO SQL
characteristic. If the variable is set to 1, MySQL does not
enforce these restrictions on stored function creation. This
variable also applies to trigger creation. See
Section 19.7, “Binary Logging of Stored Programs”.
| Command-Line Format | --log-error[=file_name] | ||
| System Variable Name | log_error | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The location of the error log, or empty if the server is writing error message to the standard error output. See Section 5.2.2, “The Error Log”.
| Introduced | 5.1.6 | ||
| Command-Line Format | --log-output=name | ||
| System Variable Name | log_output | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | set | ||
| Default | FILE | ||
| Valid Values | TABLE | ||
FILE | |||
NONE | |||
The destination for general query log and slow query log
output. The value can be a comma-separated list of one or more
of the words TABLE (log to tables),
FILE (log to files), or
NONE (do not log to tables or files). The
default value is FILE.
NONE, if present, takes precedence over any
other specifiers. If the value is NONE log
entries are not written even if the logs are enabled. If the
logs are not enabled, no logging occurs even if the value of
log_output is not
NONE. For more information, see
Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”. This variable was added in
MySQL 5.1.6.
| Command-Line Format | --log-queries-not-using-indexes | ||
| System Variable Name | log_queries_not_using_indexes | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Whether queries that do not use indexes are logged to the slow query log. See Section 5.2.5, “The Slow Query Log”. This variable was added in MySQL 5.1.11.
| Deprecated | 5.1.29, by slow-query-log | ||
| Command-Line Format | --log-slow-queries[=name] | ||
| System Variable Name | log_slow_queries | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
Whether slow queries should be logged. “Slow” is
determined by the value of the
long_query_time variable. See
Section 5.2.5, “The Slow Query Log”.
This variable is deprecated as of MySQL 5.1.29 and is removed
in MySQL 5.6. Use
slow_query_log instead.
| Command-Line Format | --log-warnings[=#] | ||
-W [#] | |||
| System Variable Name | log_warnings | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 1 | ||
| Min Value | 0 | ||
| Max Value | 18446744073709547520 | ||
Whether to produce additional warning messages to the error log. This variable is enabled (1) by default and can be disabled by setting it to 0. As of MySQL 5.1.38, the server logs messages about statements that are unsafe for statement-based logging if the value is greater than 0. Aborted connections and access-denied errors for new connection attempts are logged if the value is greater than 1.
| Command-Line Format | --long_query_time=# | ||
| System Variable Name | long_query_time | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values (>= 5.1.21) | |||
| Type | numeric | ||
| Default | 10 | ||
| Min Value | 0 | ||
If a query takes longer than this many seconds, the server
increments the Slow_queries
status variable. If the slow query log is enabled, the query
is logged to the slow query log file. This value is measured
in real time, not CPU time, so a query that is under the
threshold on a lightly loaded system might be above the
threshold on a heavily loaded one. The default value of
long_query_time is 10.
Beginning with MySQL 5.1.21, the minimum is 0, and the value
can be specified to a resolution of microseconds. For logging
to a file, times are written including the microseconds part.
For logging to tables, only integer times are written; the
microseconds part is ignored. Prior to MySQL 5.1.21, the
minimum value is 1, and the value for this variable must be an
integer. See Section 5.2.5, “The Slow Query Log”.
| Command-Line Format | --low-priority-updates | ||
| System Variable Name | low_priority_updates | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
If set to 1, all
INSERT,
UPDATE,
DELETE, and LOCK TABLE
WRITE statements wait until there is no pending
SELECT or LOCK TABLE
READ on the affected table. This affects only
storage engines that use only table-level locking (such as
MyISAM, MEMORY, and
MERGE). This variable previously was named
sql_low_priority_updates.
| System Variable Name | lower_case_file_system | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | boolean | ||
This variable describes the case sensitivity of file names on
the file system where the data directory is located.
OFF means file names are case sensitive,
ON means they are not case sensitive. This
variable is read only because it reflects a file system
attribute and setting it would have no effect on the file
system.
| Command-Line Format | --lower_case_table_names[=#] | ||
| System Variable Name | lower_case_table_names | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | 2 | ||
If set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional information, see Section 9.2.2, “Identifier Case Sensitivity”.
You should not set this variable to 0 if
you are running MySQL on a system that has case-insensitive
file names (such as Windows or Mac OS X). If you set this
variable to 0 on such a system and access
MyISAM tablenames using different
lettercases, index corruption may result. On Windows the
default value is 1. On Mac OS X, the default value is 2.
If you are using InnoDB tables, you should
set this variable to 1 on all platforms to force names to be
converted to lowercase. Prior to MySQL Cluster NDB 6.3.35,
MySQL Cluster NDB 7.0.15, and MySQL Cluster NDB 7.1.4, this is
also true for tables using the NDB storage
engine.
The setting of this variable has no effect on replication filtering options. See Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”, for more information.
You should not use different settings for
lower_case_table_names on replication
masters and slaves. In particular, you should not do this when
the slave uses a case-sensitive file system, as this can cause
replication to fail. For more information, see
Section 16.4.1.36, “Replication and Variables”.
| Command-Line Format | --max_allowed_packet=# | ||
| System Variable Name | max_allowed_packet | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 1048576 | ||
| Min Value | 1024 | ||
| Max Value | 1073741824 | ||
The maximum size of one packet or any generated/intermediate string.
The packet message buffer is initialized to
net_buffer_length bytes, but
can grow up to
max_allowed_packet bytes when
needed. This value by default is small, to catch large
(possibly incorrect) packets.
You must increase this value if you are using large
BLOB columns or long strings.
It should be as big as the largest
BLOB you want to use. The
protocol limit for
max_allowed_packet is 1GB.
The value should be a multiple of 1024; nonmultiples are
rounded down to the nearest multiple.
When you change the message buffer size by changing the value
of the max_allowed_packet
variable, you should also change the buffer size on the client
side if your client program permits it. The default
max_allowed_packet value
built in to the client library is 1GB, but individual client
programs might override this. For example,
mysql and mysqldump have
defaults of 16MB and 24MB, respectively. They also enable you
to change the client-side value by setting
max_allowed_packet on the
command line or in an option file.
As of MySQL 5.1.31, the session value of this variable is read only. Before 5.1.31, setting the session value is permitted but has no effect.
| Command-Line Format | --max_connect_errors=# | ||
| System Variable Name | max_connect_errors | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 10 | ||
| Min Value | 1 | ||
| Max Value | 18446744073709547520 | ||
If more than this many successive connection requests from a
host are interrupted without a successful connection, the
server blocks that host from further connections. You can
unblock blocked hosts by flushing the host cache. To do so,
issue a FLUSH
HOSTS statement or execute a mysqladmin
flush-hosts command. If a connection is established
successfully within fewer than
max_connect_errors attempts
after a previous connection was interrupted, the error count
for the host is cleared to zero. However, once a host is
blocked, flushing the host cache is the only way to unblock
it.
| Command-Line Format | --max_connections=# | ||
| System Variable Name | max_connections | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values (<= 5.1.14) | |||
| Type | numeric | ||
| Default | 100 | ||
| Permitted Values (>= 5.1.15) | |||
| Type | numeric | ||
| Default | 151 | ||
| Min Value | 1 | ||
| Max Value | 16384 | ||
| Permitted Values (>= 5.1.17) | |||
| Type | numeric | ||
| Default | 151 | ||
| Min Value | 1 | ||
| Max Value | 100000 | ||
The maximum permitted number of simultaneous client connections. By default, this is 151, beginning with MySQL 5.1.15. (Previously, the default was 100.) See Section B.5.2.7, “Too many connections”, for more information.
Increasing this value increases the number of file descriptors that mysqld requires. See Section 8.8.3, “How MySQL Opens and Closes Tables”, for comments on file descriptor limits.
| Command-Line Format | --max_delayed_threads=# | ||
| System Variable Name | max_delayed_threads | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 20 | ||
| Min Value | 0 | ||
| Max Value | 16384 | ||
Do not start more than this number of threads to handle
INSERT DELAYED statements. If
you try to insert data into a new table after all
INSERT DELAYED threads are in
use, the row is inserted as if the DELAYED
attribute was not specified. If you set this to 0, MySQL never
creates a thread to handle DELAYED rows; in
effect, this disables DELAYED entirely.
For the SESSION value of this variable, the
only valid values are 0 or the GLOBAL
value.
| Command-Line Format | --max_error_count=# | ||
| System Variable Name | max_error_count | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 64 | ||
| Min Value | 0 | ||
| Max Value | 65535 | ||
The maximum number of error, warning, and note messages to be
stored for display by the SHOW
ERRORS and SHOW
WARNINGS statements.
| Command-Line Format | --max_heap_table_size=# | ||
| System Variable Name | max_heap_table_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 16777216 | ||
| Min Value | 16384 | ||
| Max Value | 4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 16777216 | ||
| Min Value | 16384 | ||
| Max Value | 1844674407370954752 | ||
This variable sets the maximum size to which user-created
MEMORY tables are permitted to grow. The
value of the variable is used to calculate
MEMORY table MAX_ROWS
values. Setting this variable has no effect on any existing
MEMORY table, unless the table is
re-created with a statement such as
CREATE TABLE or altered with
ALTER TABLE or
TRUNCATE TABLE. A server
restart also sets the maximum size of existing
MEMORY tables to the global
max_heap_table_size value.
This variable is also used in conjunction with
tmp_table_size to limit the
size of internal in-memory tables. See
Section 8.8.5, “How MySQL Uses Internal Temporary Tables”.
max_heap_table_size is not replicated. See
Section 16.4.1.21, “Replication and MEMORY Tables”, and
Section 16.4.1.36, “Replication and Variables”, for more
information.
| System Variable Name | max_insert_delayed_threads | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
This variable is a synonym for
max_delayed_threads.
| Command-Line Format | --max_join_size=# | ||
| System Variable Name | max_join_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 4294967295 | ||
| Min Value | 1 | ||
| Max Value | 4294967295 | ||
Do not permit statements that probably need to examine more
than max_join_size rows (for
single-table statements) or row combinations (for
multiple-table statements) or that are likely to do more than
max_join_size disk seeks. By
setting this value, you can catch statements where keys are
not used properly and that would probably take a long time.
Set it if your users tend to perform joins that lack a
WHERE clause, that take a long time, or
that return millions of rows.
Setting this variable to a value other than
DEFAULT resets the value of
sql_big_selects to
0. If you set the
sql_big_selects value again,
the max_join_size variable is
ignored.
If a query result is in the query cache, no result size check is performed, because the result has previously been computed and it does not burden the server to send it to the client.
This variable previously was named
sql_max_join_size.
| Command-Line Format | --max_length_for_sort_data=# | ||
| System Variable Name | max_length_for_sort_data | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 1024 | ||
| Min Value | 4 | ||
| Max Value | 8388608 | ||
The cutoff on the size of index values that determines which
filesort algorithm to use. See
Section 8.3.1.11, “ORDER BY Optimization”.
| Introduced | 5.1.57 | ||
| Deprecated | 5.1.57 | ||
| Command-Line Format | --max_long_data_size=# | ||
| System Variable Name | max_long_data_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 1048576 | ||
| Min Value | 1024 | ||
| Max Value | 4294967295 | ||
The maximum size of parameter values that can be sent with the
mysql_stmt_send_long_data() C
API function. If not set at server startup, the default is the
value of the
max_allowed_packet system
variable. This variable is deprecated. In MySQL 5.6, it is
removed and the maximum parameter size is controlled by
max_allowed_packet.
| Introduced | 5.1.10 | ||
| Command-Line Format | --max_prepared_stmt_count=# | ||
| System Variable Name | max_prepared_stmt_count | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 16382 | ||
| Min Value | 0 | ||
| Max Value | 1048576 | ||
This variable limits the total number of prepared statements in the server. (The sum of the number of prepared statements across all sessions.) It can be used in environments where there is the potential for denial-of-service attacks based on running the server out of memory by preparing huge numbers of statements. If the value is set lower than the current number of prepared statements, existing statements are not affected and can be used, but no new statements can be prepared until the current number drops below the limit. The default value is 16,382. The permissible range of values is from 0 to 1 million. Setting the value to 0 disables prepared statements. This variable was added in MySQL 5.1.10.
| Command-Line Format | --max_relay_log_size=# | ||
| System Variable Name | max_relay_log_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | 1073741824 | ||
If a write by a replication slave to its relay log causes the
current log file size to exceed the value of this variable,
the slave rotates the relay logs (closes the current file and
opens the next one). If
max_relay_log_size is 0, the
server uses max_binlog_size
for both the binary log and the relay log. If
max_relay_log_size is greater
than 0, it constrains the size of the relay log, which enables
you to have different sizes for the two logs. You must set
max_relay_log_size to between
4096 bytes and 1GB (inclusive), or to 0. The default value is
0. See Section 16.2.1, “Replication Implementation Details”.
| Command-Line Format | --max_seeks_for_key=# | ||
| System Variable Name | max_seeks_for_key | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 4294967295 | ||
| Min Value | 1 | ||
| Max Value | 4294967295 | ||
Limit the assumed maximum number of seeks when looking up rows based on a key. The MySQL optimizer assumes that no more than this number of key seeks are required when searching for matching rows in a table by scanning an index, regardless of the actual cardinality of the index (see Section 13.7.5.23, “SHOW INDEX Syntax”). By setting this to a low value (say, 100), you can force MySQL to prefer indexes instead of table scans.
| Command-Line Format | --max_sort_length=# | ||
| System Variable Name | max_sort_length | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 1024 | ||
| Min Value | 4 | ||
| Max Value | 8388608 | ||
The number of bytes to use when sorting data values. Only the
first max_sort_length bytes
of each value are used; the rest are ignored.
| Command-Line Format | --max_sp_recursion_depth[=#] | ||
| System Variable Name | max_sp_recursion_depth | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Max Value | 255 | ||
The number of times that any given stored procedure may be called recursively. The default value for this option is 0, which completely disables recursion in stored procedures. The maximum value is 255.
Stored procedure recursion increases the demand on thread
stack space. If you increase the value of
max_sp_recursion_depth, it
may be necessary to increase thread stack size by increasing
the value of thread_stack at
server startup.
This variable is unused.
| Command-Line Format | --max_user_connections=# | ||
| System Variable Name | max_user_connections | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | 4294967295 | ||
The maximum number of simultaneous connections permitted to any given MySQL user account. A value of 0 (the default) means “no limit.”
This variable has a global value that can be set at server startup or runtime. It also has a read-only session value that indicates the effective simultaneous-connection limit that applies to the account associated with the current session. The session value is initialized as follows:
If the user account has a nonzero
MAX_USER_CONNECTIONS resource limit,
the session
max_user_connections
value is set to that limit.
Otherwise, the session
max_user_connections
value is set to the global value.
Account resource limits are specified using the
GRANT statement. See
Section 6.3.4, “Setting Account Resource Limits”, and Section 13.7.1.3, “GRANT Syntax”.
| Command-Line Format | --max_write_lock_count=# | ||
| System Variable Name | max_write_lock_count | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 4294967295 | ||
| Min Value | 1 | ||
| Max Value | 4294967295 | ||
After this many write locks, permit some pending read lock requests to be processed in between.
| Introduced | 5.1.21 | ||
| Command-Line Format | --min-examined-row-limit=# | ||
| System Variable Name | min_examined_row_limit | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | 4294967295 | ||
Queries that examine fewer than this number of rows are not logged to the slow query log. This variable was added in MySQL 5.1.21.
| Command-Line Format | --multi_range_count=# | ||
| System Variable Name | multi_range_count | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 256 | ||
| Min Value | 1 | ||
| Max Value | 4294967295 | ||
The maximum number of ranges to send to a table handler at
once during range selects. The default value is 256. Sending
multiple ranges to a handler at once can improve the
performance of certain selects dramatically. This is
especially true for the
NDBCLUSTER table handler, which
needs to send the range requests to all nodes. Sending a batch
of those requests at once reduces communication costs
significantly.
This variable is deprecated in MySQL 5.1, and is no longer supported in MySQL 5.5, in which arbitrarily long lists of ranges can be processed.
| Command-Line Format | --myisam_data_pointer_size=# | ||
| System Variable Name | myisam_data_pointer_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 6 | ||
| Min Value | 2 | ||
| Max Value | 7 | ||
The default pointer size in bytes, to be used by
CREATE TABLE for
MyISAM tables when no
MAX_ROWS option is specified. This variable
cannot be less than 2 or larger than 7. The default value is
6. See Section B.5.2.12, “The table is full”.
| Command-Line Format | --myisam_max_sort_file_size=# | ||
| System Variable Name | myisam_max_sort_file_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 2147483648 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 9223372036854775807 | ||
The maximum size of the temporary file that MySQL is permitted
to use while re-creating a MyISAM index
(during REPAIR TABLE,
ALTER TABLE, or
LOAD DATA
INFILE). If the file size would be larger than this
value, the index is created using the key cache instead, which
is slower. The value is given in bytes.
If MyISAM index files exceed this size and
disk space is available, increasing the value may help
performance. The space must be available in the file system
containing the directory where the original index file is
located.
| Introduced | 5.1.43 | ||
| Command-Line Format | --myisam_mmap_size=# | ||
| System Variable Name | myisam_mmap_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 4294967295 | ||
| Min Value | 7 | ||
| Max Value | 4294967295 | ||
The maximum amount of memory to use for memory mapping
compressed MyISAM files. If many
compressed MyISAM tables are used, the
value can be decreased to reduce the likelihood of
memory-swapping problems. This variable was added in MySQL
5.1.43.
| System Variable Name | myisam_recover_options | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
The value of the
--myisam-recover option. See
Section 5.1.3, “Server Command Options”.
| Command-Line Format | --myisam_repair_threads=# | ||
| System Variable Name | myisam_repair_threads | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 1 | ||
| Min Value | 1 | ||
| Max Value | 4294967295 | ||
If this value is greater than 1, MyISAM
table indexes are created in parallel (each index in its own
thread) during the Repair by sorting
process. The default value is 1.
Multi-threaded repair is still beta-quality code.
| Command-Line Format | --myisam_sort_buffer_size=# | ||
| System Variable Name | myisam_sort_buffer_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type (Windows) | numeric | ||
| Default | 8388608 | ||
| Min Value | 4096 | ||
| Max Value | 4294967295 | ||
The size of the buffer that is allocated when sorting
MyISAM indexes during a
REPAIR TABLE or when creating
indexes with CREATE INDEX or
ALTER TABLE.
The maximum permissible setting for
myisam_sort_buffer_size is
4GB–1. As of MySQL 5.1.23, larger values are permitted
for 64-bit platforms (except 64-bit Windows, for which large
values are truncated to 4GB–1 with a warning).
| Command-Line Format | --myisam_stats_method=name | ||
| System Variable Name | myisam_stats_method | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | nulls_unequal | ||
| Valid Values | nulls_equal | ||
nulls_unequal | |||
nulls_ignored | |||
How the server treats NULL values when
collecting statistics about the distribution of index values
for MyISAM tables. This variable has three
possible values, nulls_equal,
nulls_unequal, and
nulls_ignored. For
nulls_equal, all NULL
index values are considered equal and form a single value
group that has a size equal to the number of
NULL values. For
nulls_unequal, NULL
values are considered unequal, and each
NULL forms a distinct value group of size
1. For nulls_ignored,
NULL values are ignored.
The method that is used for generating table statistics influences how the optimizer chooses indexes for query execution, as described in Section 8.5.4, “InnoDB and MyISAM Index Statistics Collection”.
Any unique prefix of a valid value may be used to set the value of this variable.
| Introduced | 5.1.4 | ||
| Command-Line Format | --myisam_use_mmap | ||
| System Variable Name | myisam_use_mmap | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Use memory mapping for reading and writing
MyISAM tables. This variable was added in
MySQL 5.1.4.
| System Variable Name | named_pipe | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Platform Specific | Windows | ||
| Permitted Values | |||
| Type (Windows) | boolean | ||
| Default | OFF | ||
(Windows only.) Indicates whether the server supports connections over named pipes.
| Command-Line Format | --net_buffer_length=# | ||
| System Variable Name | net_buffer_length | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 16384 | ||
| Min Value | 1024 | ||
| Max Value | 1048576 | ||
Each client thread is associated with a connection buffer and
result buffer. Both begin with a size given by
net_buffer_length but are
dynamically enlarged up to
max_allowed_packet bytes as
needed. The result buffer shrinks to
net_buffer_length after each
SQL statement.
This variable should not normally be changed, but if you have
very little memory, you can set it to the expected length of
statements sent by clients. If statements exceed this length,
the connection buffer is automatically enlarged. The maximum
value to which
net_buffer_length can be set
is 1MB.
As of MySQL 5.1.31, the session value of this variable is read only. Before 5.1.31, setting the session value is permitted but has no effect.
| Command-Line Format | --net_read_timeout=# | ||
| System Variable Name | net_read_timeout | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 30 | ||
| Min Value | 1 | ||
The number of seconds to wait for more data from a connection
before aborting the read. Before MySQL 5.1.41, this timeout
applies only to TCP/IP connections, not to connections made
through Unix socket files, named pipes, or shared memory. When
the server is reading from the client,
net_read_timeout is the
timeout value controlling when to abort. When the server is
writing to the client,
net_write_timeout is the
timeout value controlling when to abort. See also
slave_net_timeout.
On Linux, the NO_ALARM build flag affects
timeout behavior as indicated in the description of the
net_retry_count system
variable.
| Command-Line Format | --net_retry_count=# | ||
| System Variable Name | net_retry_count | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 10 | ||
| Min Value | 1 | ||
| Max Value | 4294967295 | ||
If a read or write on a communication port is interrupted, retry this many times before giving up. This value should be set quite high on FreeBSD because internal interrupts are sent to all threads.
On Linux, the NO_ALARM build flag
(-DNO_ALARM) modifies how the binary treats
both net_read_timeout and
net_write_timeout. With this
flag enabled, neither timer cancels the current statement
until after the failing connection has been waited on an
additional net_retry_count
times. This means that the effective timeout value becomes
(timeout setting) ×
(net_retry_count+1).
| Command-Line Format | --net_write_timeout=# | ||
| System Variable Name | net_write_timeout | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 60 | ||
| Min Value | 1 | ||
The number of seconds to wait for a block to be written to a
connection before aborting the write. Before MySQL 5.1.41,
this timeout applies only to TCP/IP connections, not to
connections made using Unix socket files, named pipes, or
shared memory. See also
net_read_timeout.
On Linux, the NO_ALARM build flag affects
timeout behavior as indicated in the description of the
net_retry_count system
variable.
| Command-Line Format | --new | ||
-n | |||
| System Variable Name | new | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Disabled by | skip-new | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
This variable was used in MySQL 4.0 to turn on some 4.1
behaviors, and is retained for backward compatibility. In
MySQL 5.1, its value is always
OFF.
| Introduced | 5.1.18 | ||
| Command-Line Format | --old | ||
| System Variable Name | old | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
old is a compatibility
variable. It is disabled by default, but can be enabled at
startup to revert the server to behaviors present in older
versions.
Currently, when old is
enabled, it changes the default scope of index hints to that
used prior to MySQL 5.1.17. That is, index hints with no
FOR clause apply only to how indexes are
used for row retrieval and not to resolution of ORDER
BY or GROUP BY clauses. (See
Section 13.2.8.3, “Index Hint Syntax”.) Take care about enabling this
in a replication setup. With statement-based binary logging,
having different modes for the master and slaves might lead to
replication errors.
This variable was added as old_mode in
MySQL 5.1.17 and renamed to
old in MySQL 5.1.18.
| Command-Line Format | --old-alter-table | ||
| System Variable Name | old_alter_table | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
When this variable is enabled, the server does not use the
optimized method of processing an ALTER
TABLE operation. It reverts to using a temporary
table, copying over the data, and then renaming the temporary
table to the original, as used by MySQL 5.0 and earlier. For
more information on the operation of
ALTER TABLE, see
Section 13.1.7, “ALTER TABLE Syntax”.
| System Variable Name | old_passwords | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 0 | ||
This variable controls the password hashing method used by the
PASSWORD() function. It also
influences password hashing performed by
CREATE USER and
GRANT statements that specify a
password using an IDENTIFIED BY clause.
The value determines whether or not to use “old”
native MySQL password hashing. A value of 0 (or
OFF) causes passwords to be encrypted using
the format available from MySQL 4.1 on. A value of 1 (or
ON) causes password encryption to use the
older pre-4.1 format.
If old_passwords=1,
PASSWORD(
returns the same value as
str)OLD_PASSWORD(.
The latter function is not affected by the value of
str)old_passwords.
For information about hashing formats, see Section 6.1.2.4, “Password Hashing in MySQL”.
This is not a variable, but it can be used when setting some variables. It is described in Section 13.7.4, “SET Syntax”.
| Command-Line Format | --open-files-limit=# | ||
| System Variable Name | open_files_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | platform dependent | ||
The number of files that the operating system permits
mysqld to open. This is the real value
permitted by the system and might be different from the value
you gave using the
--open-files-limit option to
mysqld or mysqld_safe.
The value is 0 on systems where MySQL cannot change the number
of open files.
| Command-Line Format | --optimizer_prune_level[=#] | ||
| System Variable Name | optimizer_prune_level | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 1 | ||
Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. A value of 0 disables heuristics so that the optimizer performs an exhaustive search. A value of 1 causes the optimizer to prune plans based on the number of rows retrieved by intermediate plans.
| Command-Line Format | --optimizer_search_depth[=#] | ||
| System Variable Name | optimizer_search_depth | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 62 | ||
| Min Value | 0 | ||
| Max Value | 63 | ||
The maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to generate an execution plan for a query. Values smaller than the number of relations in a query return an execution plan quicker, but the resulting plan may be far from being optimal. If set to 0, the system automatically picks a reasonable value. If set to 63, the optimizer switches to the algorithm used in MySQL 5.0.0 (and previous versions) for performing searches. The value of 63 is deprecated and will be treated as invalid in a future MySQL release.
| Introduced | 5.1.34 | ||
| Command-Line Format | --optimizer_switch=value | ||
| System Variable Name | optimizer_switch | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | set | ||
| Valid Values | index_merge={on|off} | ||
index_merge_intersection={on|off} | |||
index_merge_sort_union={on|off} | |||
index_merge_union={on|off} | |||
The optimizer_switch system
variable enables control over optimizer behaviors. The value
of this variable is a set of flags, each of which has a value
of on or off to indicate
whether the corresponding optimizer behavior is enabled or
disabled. This variable has global and session values and can
be changed at runtime. The global default can be set at server
startup.
To see the current set of optimizer flags, select the variable value:
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,index_merge_intersection=on
For more information about the syntax of this variable and the optimizer behaviors that it controls, see Section 8.4.2, “Controlling Switchable Optimizations”.
| Command-Line Format | --pid-file=file_name | ||
| System Variable Name | pid_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The path name of the process ID (PID) file. This variable can
be set with the --pid-file
option.
| Introduced | 5.1.2 | ||
| Command-Line Format | --plugin_dir=path | ||
| System Variable Name | plugin_dir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values (>= 5.1.2) | |||
| Type (Windows) | directory name | ||
| Default | BASEDIR/lib/plugin | ||
| Permitted Values (>= 5.1.2) | |||
| Type (Other) | directory name | ||
| Default | BASEDIR/lib/mysql/plugin | ||
The path name of the plugin directory. This variable was added in MySQL 5.1.2.
If the plugin directory is writable by the server, it may be
possible for a user to write executable code to a file in the
directory using SELECT
... INTO DUMPFILE. This can be prevented by making
plugin_dir read only to the
server or by setting
--secure-file-priv to a
directory where SELECT writes
can be made safely.
| Command-Line Format | --port=# | ||
-P | |||
| System Variable Name | port | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 3306 | ||
| Min Value | 0 | ||
| Max Value | 65535 | ||
The number of the port on which the server listens for TCP/IP
connections. This variable can be set with the
--port option.
| Command-Line Format | --preload_buffer_size=# | ||
| System Variable Name | preload_buffer_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 32768 | ||
| Min Value | 1024 | ||
| Max Value | 1073741824 | ||
The size of the buffer that is allocated when preloading indexes.
| Introduced | 5.1.10 | ||
| Removed | 5.1.13 | ||
| System Variable Name | prepared_stmt_count | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
The current number of prepared statements. (The maximum number
of statements is given by the
max_prepared_stmt_count
system variable.) This variable was added in MySQL 5.1.10. In
MySQL 5.1.14, it was converted to the global
Prepared_stmt_count status
variable.
If set to 0 or OFF (the default), statement
profiling is disabled. If set to 1 or ON,
statement profiling is enabled and the
SHOW PROFILE and
SHOW PROFILES statements
provide access to profiling information. See
Section 13.7.5.33, “SHOW PROFILES Syntax”. This variable was added in
MySQL 5.1.24.
The number of statements for which to maintain profiling
information if profiling is
enabled. The default value is 15. The maximum value is 100.
Setting the value to 0 effectively disables profiling. See
Section 13.7.5.33, “SHOW PROFILES Syntax”. This variable was added in
MySQL 5.1.24.
| System Variable Name | protocol_version | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
The version of the client/server protocol used by the MySQL server.
| System Variable Name | pseudo_thread_id | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
This variable is for internal server use.
| Command-Line Format | --query_alloc_block_size=# | ||
| System Variable Name | query_alloc_block_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 8192 | ||
| Min Value | 1024 | ||
| Max Value | 4294967295 | ||
| Block Size | 1024 | ||
The allocation size of memory blocks that are allocated for objects created during statement parsing and execution. If you have problems with memory fragmentation, it might help to increase this parameter.
| Command-Line Format | --query_cache_limit=# | ||
| System Variable Name | query_cache_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 1048576 | ||
| Min Value | 0 | ||
| Max Value | 4294967295 | ||
Do not cache results that are larger than this number of bytes. The default value is 1MB.
| Command-Line Format | --query_cache_min_res_unit=# | ||
| System Variable Name | query_cache_min_res_unit | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 4096 | ||
| Min Value | 512 | ||
| Max Value | 4294967295 | ||
The minimum size (in bytes) for blocks allocated by the query cache. The default value is 4096 (4KB). Tuning information for this variable is given in Section 8.6.3.3, “Query Cache Configuration”.
| Command-Line Format | --query_cache_size=# | ||
| System Variable Name | query_cache_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | 18446744073709547520 | ||
The amount of memory allocated for caching query results. The
default value is 0, which disables the query cache. The
permissible values are multiples of 1024; other values are
rounded down to the nearest multiple. Note that
query_cache_size bytes of
memory are allocated even if
query_cache_type is set to 0.
See Section 8.6.3.3, “Query Cache Configuration”, for more
information.
The query cache needs a minimum size of about 40KB to allocate
its structures. (The exact size depends on system
architecture.) If you set the value of
query_cache_size too small, a
warning will occur, as described in
Section 8.6.3.3, “Query Cache Configuration”.
| Command-Line Format | --query_cache_type=# | ||
| System Variable Name | query_cache_type | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | 1 | ||
| Valid Values | 0 | ||
1 | |||
2 | |||
Set the query cache type. Setting the
GLOBAL value sets the type for all clients
that connect thereafter. Individual clients can set the
SESSION value to affect their own use of
the query cache. Possible values are shown in the following
table.
| Option | Description |
|---|---|
0 or OFF | Do not cache results in or retrieve results from the query cache. Note
that this does not deallocate the query cache buffer.
To do that, you should set
query_cache_size to
0. |
1 or ON | Cache all cacheable query results except for those that begin with
SELECT SQL_NO_CACHE. |
2 or DEMAND | Cache results only for cacheable queries that begin with SELECT
SQL_CACHE. |
This variable defaults to ON.
Any unique prefix of a valid value may be used to set the value of this variable.
| Command-Line Format | --query_cache_wlock_invalidate | ||
| System Variable Name | query_cache_wlock_invalidate | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | FALSE | ||
Normally, when one client acquires a WRITE
lock on a MyISAM table, other clients are
not blocked from issuing statements that read from the table
if the query results are present in the query cache. Setting
this variable to 1 causes acquisition of a
WRITE lock for a table to invalidate any
queries in the query cache that refer to the table. This
forces other clients that attempt to access the table to wait
while the lock is in effect.
| Command-Line Format | --query_prealloc_size=# | ||
| System Variable Name | query_prealloc_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 8192 | ||
| Min Value | 8192 | ||
| Max Value | 18446744073709551615 | ||
| Block Size | 1024 | ||
The size of the persistent buffer used for statement parsing
and execution. This buffer is not freed between statements. If
you are running complex queries, a larger
query_prealloc_size value
might be helpful in improving performance, because it can
reduce the need for the server to perform memory allocation
during query execution operations.
The rand_seed1 and
rand_seed2 variables exist as
session variables only, and can be set but not read. Beginning
with MySQL 5.1.18, the variables—but not their
values—are shown in the output of
SHOW VARIABLES.
The purpose of these variables is to support replication of
the RAND() function. For
statements that invoke RAND(),
the master passes two values to the slave, where they are used
to seed the random number generator. The slave uses these
values to set the session variables
rand_seed1 and
rand_seed2 so that
RAND() on the slave generates
the same value as on the master.
See the description for
rand_seed1.
| Command-Line Format | --range_alloc_block_size=# | ||
| System Variable Name | range_alloc_block_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 4096 | ||
| Min Value | 4096 | ||
| Max Value | 4294967295 | ||
| Block Size | 1024 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type | numeric | ||
| Default | 4096 | ||
| Min Value | 4096 | ||
| Max Value | 18446744073709547520 | ||
| Block Size | 1024 | ||
The size of blocks that are allocated when doing range optimization.
| Command-Line Format | --read_buffer_size=# | ||
| System Variable Name | read_buffer_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 131072 | ||
| Min Value | 8200 | ||
| Max Value | 2147479552 | ||
Each thread that does a sequential scan for a
MyISAM table allocates a buffer of this
size (in bytes) for each table it scans. If you do many
sequential scans, you might want to increase this value, which
defaults to 131072. The value of this variable should be a
multiple of 4KB. If it is set to a value that is not a
multiple of 4KB, its value will be rounded down to the nearest
multiple of 4KB.
This option is also used in the following context for all search engines:
For caching the indexes in a temporary file (not a
temporary table), when sorting rows for ORDER
BY.
For bulk insert into partitions.
For caching results of nested queries.
and in one other storage engine-specific way: to determine the
memory block size for MEMORY
tables.
The maximum permissible setting for
read_buffer_size is 2GB.
For more information about memory use during different operations, see Section 8.9.4, “How MySQL Uses Memory”.
| Command-Line Format | --read-only | ||
| System Variable Name | read_only | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | false | ||
This variable is off by default. When it is enabled, the
server permits no updates except from users that have the
SUPER privilege or (on a slave
server) from updates performed by slave threads. In
replication setups, it can be useful to enable
read_only on slave servers to
ensure that slaves accept updates only from the master server
and not from clients.
read_only does not apply to
TEMPORARY tables, nor does it prevent the
server from inserting rows into the log tables (see
Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”). This variable does not
prevent the use of ANALYZE
TABLE or OPTIMIZE
TABLE statements because its purpose is to prevent
changes to table structure or contents. Analysis and
optimization do not qualify as such changes. This means, for
example, that consistency checks on read-only slaves can be
performed with mysqlcheck --all-databases
--analyze.
read_only exists only as a
GLOBAL variable, so changes to its value
require the SUPER privilege.
Changes to read_only on a
master server are not replicated to slave servers. The value
can be set on a slave server independent of the setting on the
master.
In MySQL 5.1, enabling
read_only prevents the use of the
SET PASSWORD statement by any
user not having the SUPER
privilege. This is not necessarily the case for all MySQL
release series. When replicating from one MySQL release
series to another (for example, from a MySQL 5.0 master to a
MySQL 5.1 or later slave), you should check the
documentation for the versions running on both master and
slave to determine whether the behavior of
read_only in this regard is or is not the
same, and, if it is different, whether this has an impact on
your applications.
As of MySQL 5.1.15, the following conditions apply:
If you attempt to enable
read_only while you have
any explicit locks (acquired with
LOCK TABLES) or have a
pending transaction, an error occurs.
If you attempt to enable
read_only while other
clients hold explicit table locks or have pending
transactions, the attempt blocks until the locks are
released and the transactions end. While the attempt to
enable read_only is
pending, requests by other clients for table locks or to
begin transactions also block until
read_only has been set.
read_only can be enabled
while you hold a global read lock (acquired with
FLUSH TABLES WITH
READ LOCK) because that does not involve table
locks.
| Command-Line Format | --read_rnd_buffer_size=# | ||
| System Variable Name | read_rnd_buffer_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 262144 | ||
| Min Value | 8200 | ||
| Max Value | 2147483647 | ||
When reading rows from a MyISAM table in
sorted order following a key-sorting operation, the rows are
read through this buffer to avoid disk seeks. See
Section 8.3.1.11, “ORDER BY Optimization”. Setting the variable
to a large value can improve ORDER BY
performance by a lot. However, this is a buffer allocated for
each client, so you should not set the global variable to a
large value. Instead, change the session variable only from
within those clients that need to run large queries.
The maximum permissible setting for
read_rnd_buffer_size is 2GB.
For more information about memory use during different operations, see Section 8.9.4, “How MySQL Uses Memory”.
| Command-Line Format | --relay_log_purge | ||
| System Variable Name | relay_log_purge | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | TRUE | ||
Disables or enables automatic purging of relay log files as
soon as they are not needed any more. The default value is 1
(ON).
| Command-Line Format | --relay_log_space_limit=# | ||
| System Variable Name | relay_log_space_limit | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | 4294967295 | ||
The maximum amount of space to use for all relay logs.
| Command-Line Format | --report-host=host_name | ||
| System Variable Name | report_host | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The value of the --report-host
option. This variable was added in MySQL 5.1.24.
| Command-Line Format | --report-password=name | ||
| System Variable Name | report_password | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The value of the
--report-password option. Not
the same as the password for the MySQL replication user
account. This variable was added in MySQL 5.1.24.
| Command-Line Format | --report-port=# | ||
| System Variable Name | report_port | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 3306 | ||
| Min Value | 0 | ||
| Max Value | 65535 | ||
The value of the --report-port
option. This variable was added in MySQL 5.1.24.
| Command-Line Format | --report-user=name | ||
| System Variable Name | report_user | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The value of the --report-user
option. This variable was added in MySQL 5.1.24.
| Command-Line Format | --secure-auth | ||
| System Variable Name | secure_auth | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
If this variable is enabled, the server blocks connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format.
Enable this variable to prevent all use of passwords employing the old format (and hence insecure communication over the network).
Server startup fails with an error if this variable is enabled and the privilege tables are in pre-4.1 format. See Section B.5.2.4, “Client does not support authentication protocol”.
| Introduced | 5.1.17 | ||
| Command-Line Format | --secure-file-priv=path | ||
| System Variable Name | secure_file_priv | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
By default, this variable is empty. If set to the name of a
directory, it limits the effect of the
LOAD_FILE() function and the
LOAD DATA and
SELECT ... INTO
OUTFILE statements to work only with files in that
directory.
This variable was added in MySQL 5.1.17.
| Command-Line Format | --server-id=# | ||
| System Variable Name | server_id | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | 4294967295 | ||
The server ID, used in replication to give each master and
slave a unique identity. This variable is set by the
--server-id option. For each
server participating in replication, you should pick a
positive integer in the range from 1 to
232 – 1 to act as that
server's ID.
| Command-Line Format | --shared-memory-base-name=name | ||
| System Variable Name | shared_memory | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Platform Specific | Windows | ||
(Windows only.) Whether the server permits shared-memory connections.
| System Variable Name | shared_memory_base_name | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Platform Specific | Windows | ||
(Windows only.) The name of shared memory to use for
shared-memory connections. This is useful when running
multiple MySQL instances on a single physical machine. The
default name is MYSQL. The name is case
sensitive.
| Command-Line Format | --skip-external-locking | ||
| System Variable Name | skip_external_locking | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | ON | ||
This is OFF if mysqld
uses external locking (system locking), ON
if external locking is disabled. This affects only
MyISAM table access.
This variable is set by the
--external-locking or
--skip-external-locking
option. External locking has been disabled by default as of
MySQL 4.0.
External locking affects only
MyISAM table access. For more
information, including conditions under which it can and
cannot be used, see Section 8.7.4, “External Locking”.
| Command-Line Format | --skip-name-resolve | ||
| System Variable Name | skip_name_resolve | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
This variable is set from the value of the
--skip-name-resolve option. If
it is OFF, mysqld
resolves host names when checking client connections. If it is
ON, mysqld uses only IP
numbers; in this case, all Host column
values in the grant tables must be IP addresses or
localhost. See
Section 8.9.8, “DNS Lookup Optimization and the Host Cache”.
This variable was added in MySQL 5.1.46.
| Command-Line Format | --skip-networking | ||
| System Variable Name | skip_networking | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
This is ON if the server permits only local
(non-TCP/IP) connections. On Unix, local connections use a
Unix socket file. On Windows, local connections use a named
pipe or shared memory. On NetWare, only TCP/IP connections are
supported, so do not set this variable to
ON. This variable can be set to
ON with the
--skip-networking option.
| Command-Line Format | --skip-show-database | ||
| System Variable Name | skip_show_database | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
This prevents people from using the SHOW
DATABASES statement if they do not have the
SHOW DATABASES privilege. This
can improve security if you have concerns about users being
able to see databases belonging to other users. Its effect
depends on the SHOW DATABASES
privilege: If the variable value is ON, the
SHOW DATABASES statement is
permitted only to users who have the SHOW
DATABASES privilege, and the statement displays all
database names. If the value is OFF,
SHOW DATABASES is permitted to
all users, but displays the names of only those databases for
which the user has the SHOW
DATABASES or other privilege. (Note that
any global privilege is considered a
privilege for the database.)
| Command-Line Format | --slow_launch_time=# | ||
| System Variable Name | slow_launch_time | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 2 | ||
If creating a thread takes longer than this many seconds, the
server increments the
Slow_launch_threads status
variable.
| Introduced | 5.1.12 | ||
| Command-Line Format | --slow-query-log | from 5.1.29 | |
| System Variable Name | slow_query_log | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
Whether the slow query log is enabled. The value can be 0 (or
OFF) to disable the log or 1 (or
ON) to enable the log. The default value
depends on whether the
--slow_query_log option is
given (--log-slow-queries
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
slow_query_log variable was
added in MySQL 5.1.12.
“Slow” is determined by the value of the
long_query_time variable. See
Section 5.2.5, “The Slow Query Log”.
| Introduced | 5.1.12 | ||
| Command-Line Format | --slow-query-log-file=file_name | ||
| System Variable Name | slow_query_log_file | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | file name | ||
| Default | host_name-slow.log | ||
The name of the slow query log file. The default value is
host_name-slow.log--slow_query_log_file option
(--log-slow-queries before
MySQL 5.1.29). This variable was added in MySQL 5.1.12.
| Command-Line Format | --socket=file_name | ||
| System Variable Name | socket | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
| Default | /tmp/mysql.sock | ||
On Unix platforms, this variable is the name of the socket
file that is used for local client connections. The default is
/tmp/mysql.sock. (For some distribution
formats, the directory might be different, such as
/var/lib/mysql for RPMs.)
On Windows, this variable is the name of the named pipe that
is used for local client connections. The default value is
MySQL (not case sensitive).
| Command-Line Format | --sort_buffer_size=# | ||
| System Variable Name | sort_buffer_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type (Other) | numeric | ||
| Default | 2097144 | ||
| Min Value | 32768 | ||
| Max Value | 4294967295 | ||
| Permitted Values | |||
| Platform Bit Size | 64 | ||
| Type (Other) | numeric | ||
| Default | 2097144 | ||
| Min Value | 32768 | ||
| Max Value | 18446744073709551615 | ||
| Permitted Values | |||
| Type (Windows) | numeric | ||
| Default | 2097144 | ||
| Min Value | 32768 | ||
| Max Value | 4294967295 | ||
Each session that needs to do a sort allocates a buffer of
this size. sort_buffer_size
is not specific to any storage engine and applies in a general
manner for optimization. See
Section 8.3.1.11, “ORDER BY Optimization”, for example.
If you see many
Sort_merge_passes per second
in SHOW GLOBAL
STATUS output, you can consider increasing the
sort_buffer_size value to
speed up ORDER BY or GROUP
BY operations that cannot be improved with query
optimization or improved indexing. The entire buffer is
allocated even if it is not all needed, so setting it larger
than required globally will slow down most queries that sort.
It is best to increase it as a session setting, and only for
the sessions that need a larger size. On Linux, there are
thresholds of 256KB and 2MB where larger values may
significantly slow down memory allocation, so you should
consider staying below one of those values. Experiment to find
the best value for your workload. See
Section B.5.4.4, “Where MySQL Stores Temporary Files”.
The maximum permissible setting for
sort_buffer_size is
4GB–1. As of MySQL 5.1.23, larger values are permitted
for 64-bit platforms (except 64-bit Windows, for which large
values are truncated to 4GB–1 with a warning).
| System Variable Name | sql_auto_is_null | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 1 | ||
If this variable is set to 1 (the default), then after a
statement that successfully inserts an automatically generated
AUTO_INCREMENT value, you can find that
value by issuing a statement of the following form:
SELECT * FROMtbl_nameWHEREauto_colIS NULL
If the statement returns a row, the value returned is the same
as if you invoked the
LAST_INSERT_ID() function. For
details, including the return value after a multiple-row
insert, see Section 12.14, “Information Functions”. If no
AUTO_INCREMENT value was successfully
inserted, the SELECT statement
returns no row.
The behavior of retrieving an
AUTO_INCREMENT value by using an
IS NULL comparison is used by
some ODBC programs, such as Access. See
Obtaining Auto-Increment Values.
This behavior can be disabled by setting
sql_auto_is_null to 0.
| System Variable Name | sql_big_selects | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 1 | ||
If set to 0, MySQL aborts
SELECT statements that are
likely to take a very long time to execute (that is,
statements for which the optimizer estimates that the number
of examined rows exceeds the value of
max_join_size). This is
useful when an inadvisable WHERE statement
has been issued. The default value for a new connection is 1,
which permits all SELECT
statements.
If you set the max_join_size
system variable to a value other than
DEFAULT,
sql_big_selects is set to 0.
| System Variable Name | sql_buffer_result | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 0 | ||
If set to 1,
sql_buffer_result forces
results from SELECT statements
to be put into temporary tables. This helps MySQL free the
table locks early and can be beneficial in cases where it
takes a long time to send results to the client. The default
value is 0.
| System Variable Name | sql_log_bin | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
If set to 0, no logging is done to the binary log for the
client. The client must have the
SUPER privilege to set this
option. The default value is 1.
| System Variable Name | sql_log_off | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 0 | ||
If set to 1, no logging is done to the general query log for
this client. The client must have the
SUPER privilege to set this
option. The default value is 0.
| Deprecated | 5.0.0, by sql_log_bin | ||
| System Variable Name | sql_log_update | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
This variable is deprecated, and is mapped to
sql_log_bin. It is removed in
MySQL 5.5.
| Command-Line Format | --sql-mode=name | ||
| System Variable Name | sql_mode | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | set | ||
| Default | '' | ||
| Valid Values | ALLOW_INVALID_DATES | ||
ANSI_QUOTES | |||
ERROR_FOR_DIVISION_BY_ZERO | |||
HIGH_NOT_PRECEDENCE | |||
IGNORE_SPACE | |||
NO_AUTO_CREATE_USER | |||
NO_AUTO_VALUE_ON_ZERO | |||
NO_BACKSLASH_ESCAPES | |||
NO_DIR_IN_CREATE | |||
NO_ENGINE_SUBSTITUTION | |||
NO_FIELD_OPTIONS | |||
NO_KEY_OPTIONS | |||
NO_TABLE_OPTIONS | |||
NO_UNSIGNED_SUBTRACTION | |||
NO_ZERO_DATE | |||
NO_ZERO_IN_DATE | |||
ONLY_FULL_GROUP_BY | |||
PAD_CHAR_TO_FULL_LENGTH | |||
PIPES_AS_CONCAT | |||
REAL_AS_FLOAT | |||
STRICT_ALL_TABLES | |||
STRICT_TRANS_TABLES | |||
The current server SQL mode, which can be set dynamically. See Section 5.1.7, “Server SQL Modes”.
MySQL installation programs may configure the SQL mode during the installation process. If the SQL mode differs from the default or from what you expect, check for a setting in an option file that the server reads at startup.
If set to 1 (the default), warnings of Note
level increment warning_count and the
server records them. If set to 0, Note
warnings do not increment
warning_count and the server
does not record them. mysqldump includes
output to set this variable to 0 so that reloading the dump
file does not produce warnings for events that do not affect
the integrity of the reload operation.
If set to 1 (the default), the server quotes identifiers for
SHOW CREATE TABLE and
SHOW CREATE DATABASE
statements. If set to 0, quoting is disabled. This option is
enabled by default so that replication works for identifiers
that require quoting. See Section 13.7.5.12, “SHOW CREATE TABLE Syntax”,
and Section 13.7.5.8, “SHOW CREATE DATABASE Syntax”.
If set to 1, MySQL aborts
UPDATE or
DELETE statements that do not
use a key in the WHERE clause or a
LIMIT clause. (Specifically,
UPDATE statements must have a
WHERE clause that uses a key or a
LIMIT clause, or both.
DELETE statements must have
both.) This makes it possible to catch
UPDATE or
DELETE statements where keys
are not used properly and that would probably change or delete
a large number of rows. The default value is 0.
| System Variable Name | sql_select_limit | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
The maximum number of rows to return from
SELECT statements. The default
value for a new connection is the maximum number of rows that
the server permits per table, which depends on the server
configuration and may be affected if the server build was
configured with
--with-big-tables. Typical
default values are (232)–1 or
(264)–1. If you have changed
the limit, the default value can be restored by assigning a
value of DEFAULT.
If a SELECT has a
LIMIT clause, the LIMIT
takes precedence over the value of
sql_select_limit.
This variable controls whether single-row
INSERT statements produce an
information string if warnings occur. The default is 0. Set
the value to 1 to produce an information string.
| Introduced | 5.1.11 | ||
| Command-Line Format | --ssl-ca=file_name | ||
| System Variable Name | ssl_ca | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The path to a file with a list of trusted SSL CAs. This variable was added in MySQL 5.1.11.
| Introduced | 5.1.11 | ||
| Command-Line Format | --ssl-capath=dir_name | ||
| System Variable Name | ssl_capath | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The path to a directory that contains trusted SSL CA certificates in PEM format. This variable was added in MySQL 5.1.11.
| Introduced | 5.1.11 | ||
| Command-Line Format | --ssl-cert=file_name | ||
| System Variable Name | ssl_cert | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The name of the SSL certificate file to use for establishing a secure connection. This variable was added in MySQL 5.1.11.
| Introduced | 5.1.11 | ||
| Command-Line Format | --ssl-cipher=name | ||
| System Variable Name | ssl_cipher | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
A list of permissible ciphers to use for SSL encryption. This variable was added in MySQL 5.1.11.
| Introduced | 5.1.11 | ||
| Command-Line Format | --ssl-key=file_name | ||
| System Variable Name | ssl_key | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | file name | ||
The name of the SSL key file to use for establishing a secure connection. This variable was added in MySQL 5.1.11.
| System Variable Name | storage_engine | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | MyISAM | ||
The default storage engine (table type). To set the storage
engine at server startup, use the
--default-storage-engine
option. See Section 5.1.3, “Server Command Options”.
To see which storage engines are available and enabled, use
the SHOW ENGINES statement or
query the INFORMATION_SCHEMA
ENGINES table.
| Command-Line Format | --sync-frm | ||
| System Variable Name | sync_frm | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | TRUE | ||
If this variable is set to 1, when any nontemporary table is
created its .frm file is synchronized to
disk (using fdatasync()). This is slower
but safer in case of a crash. The default is 1.
| System Variable Name | system_time_zone | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The server system time zone. When the server begins executing,
it inherits a time zone setting from the machine defaults,
possibly modified by the environment of the account used for
running the server or the startup script. The value is used to
set system_time_zone.
Typically the time zone is specified by the
TZ environment variable. It also can be
specified using the
--timezone option of the
mysqld_safe script.
The system_time_zone variable
differs from time_zone.
Although they might have the same value, the latter variable
is used to initialize the time zone for each client that
connects. See Section 10.6, “MySQL Server Time Zone Support”.
| Deprecated | 5.1.3, by table_open_cache | ||
| Removed | 5.1.3 | ||
| Command-Line Format | --table_cache=# | ||
| System Variable Name | table_cache | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 64 | ||
| Min Value | 1 | ||
| Max Value | 524288 | ||
This is the old name of
table_open_cache before MySQL
5.1.3. From 5.1.3 on, use
table_open_cache instead.
| Introduced | 5.1.3 | ||
| System Variable Name | table_definition_cache | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values (<= 5.1.24) | |||
| Type | numeric | ||
| Default | 128 | ||
| Min Value | 1 | ||
| Max Value | 524288 | ||
| Permitted Values (>= 5.1.25) | |||
| Type | numeric | ||
| Default | 256 | ||
| Min Value | 256 | ||
| Max Value | 524288 | ||
The number of table definitions (from
.frm files) that can be stored in the
definition cache. If you use a large number of tables, you can
create a large table definition cache to speed up opening of
tables. The table definition cache takes less space and does
not use file descriptors, unlike the normal table cache. This
variable was added in MySQL 5.1.3. The minimum and default
values are 1 and 128 before MySQL 5.1.25. The minimum and
default are both 256 as of MySQL 5.1.25.
| Command-Line Format | --table_lock_wait_timeout=# | ||
| System Variable Name | table_lock_wait_timeout | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 50 | ||
| Min Value | 1 | ||
| Max Value | 1073741824 | ||
This variable is unused.
| Introduced | 5.1.3 | ||
| System Variable Name | table_open_cache | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 64 | ||
| Min Value | 1 | ||
| Max Value | 524288 | ||
The number of open tables for all threads. Increasing this
value increases the number of file descriptors that
mysqld requires. You can check whether you
need to increase the table cache by checking the
Opened_tables status
variable. See Section 5.1.6, “Server Status Variables”. If
the value of Opened_tables
is large and you do not use
FLUSH TABLES
often (which just forces all tables to be closed and
reopened), then you should increase the value of the
table_open_cache variable.
For more information about the table cache, see
Section 8.8.3, “How MySQL Opens and Closes Tables”. Before MySQL 5.1.3, this
variable is called
table_cache.
| System Variable Name | table_type | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
This variable is a synonym for
storage_engine. In MySQL
5.1,
storage_engine is the
preferred name; table_type is
deprecated and is removed in MySQL 5.5.
| Command-Line Format | --thread_cache_size=# | ||
| System Variable Name | thread_cache_size | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 0 | ||
| Min Value | 0 | ||
| Max Value | 16384 | ||
How many threads the server should cache for reuse. When a
client disconnects, the client's threads are put in the cache
if there are fewer than
thread_cache_size threads
there. Requests for threads are satisfied by reusing threads
taken from the cache if possible, and only when the cache is
empty is a new thread created. This variable can be increased
to improve performance if you have a lot of new connections.
Normally, this does not provide a notable performance
improvement if you have a good thread implementation. However,
if your server sees hundreds of connections per second you
should normally set
thread_cache_size high enough
so that most new connections use cached threads. By examining
the difference between the
Connections and
Threads_created status
variables, you can see how efficient the thread cache is. For
details, see Section 5.1.6, “Server Status Variables”.
| Command-Line Format | --thread_concurrency=# | ||
| System Variable Name | thread_concurrency | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | numeric | ||
| Default | 10 | ||
| Min Value | 1 | ||
| Max Value | 512 | ||
This variable is specific to Solaris systems, for which
mysqld invokes the
thr_setconcurrency() with the variable
value. This function enables applications to give the threads
system a hint about the desired number of threads that should
be run at the same time.
| Introduced | 5.1.17 | ||
| Command-Line Format | --thread_handling=name | ||
| System Variable Name | thread_handling | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | one-thread-per-connection | ||
| Valid Values | no-threads | ||
one-thread-per-connection | |||
The thread-handling model used by the server for connection
threads. The permissible values are
no-threads (the server uses a single thread
to handle one connection) and
one-thread-per-connection (the server uses
one thread to handle each client connection).
no-threads is useful for debugging under
Linux; see Section 22.4, “Debugging and Porting MySQL”. This variable was added
in MySQL 5.1.17
| Command-Line Format | --thread_stack=# | ||
| System Variable Name | thread_stack | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 196608 | ||
| Min Value | 131072 | ||
| Max Value | 4294967295 | ||
| Block Size | 1024 | ||
The stack size for each thread. Many of the limits detected by
the crash-me test are dependent on this
value. See Section 8.1.3, “The MySQL Benchmark Suite”. The default of
192KB (256KB for 64-bit systems) is large enough for normal
operation. If the thread stack size is too small, it limits
the complexity of the SQL statements that the server can
handle, the recursion depth of stored procedures, and other
memory-consuming actions.
This variable is unused.
| System Variable Name | time_zone | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | string | ||
The current time zone. This variable is used to initialize the
time zone for each client that connects. By default, the
initial value of this is 'SYSTEM' (which
means, “use the value of
system_time_zone”).
The value can be specified explicitly at server startup with
the --default-time-zone option.
See Section 10.6, “MySQL Server Time Zone Support”.
| Command-Line Format | --timed_mutexes | ||
| System Variable Name | timed_mutexes | ||
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | OFF | ||
This variable controls whether InnoDB
mutexes are timed. If this variable is set to 0 or
OFF (the default), mutex timing is
disabled. If the variable is set to 1 or
ON, mutex timing is enabled. With timing
enabled, the os_wait_times value in the
output from SHOW
ENGINE INNODB MUTEX indicates the amount of time (in
ms) spent in operating system waits. Otherwise, the value is
0.
| System Variable Name | timestamp | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | numeric | ||
Set the time for this client. This is used to get the original
timestamp if you use the binary log to restore rows.
timestamp_value should be a Unix
epoch timestamp (a value like that returned by
UNIX_TIMESTAMP(), not a value
in 'YYYY-MM-DD hh:mm:ss' format) or
DEFAULT.
Setting timestamp to a
constant value causes it to retain that value until it is
changed again. Setting
timestamp to
DEFAULT causes its value to be the current
date and time as of the time it is accessed.
SET timestamp affects the value returned by
NOW() but not by
SYSDATE(). This means that
timestamp settings in the binary log have no effect on
invocations of SYSDATE(). The
server can be started with the
--sysdate-is-now option to
cause SYSDATE() to be an alias
for NOW(), in which case
SET timestamp affects both functions.
| Command-Line Format | --tmp_table_size=# | ||
| System Variable Name | tmp_table_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values (<= 5.1.11) | |||
| Type | numeric | ||
| Default | 33554432 | ||
| Min Value | 1024 | ||
| Max Value | 4294967295 | ||
| Permitted Values (>= 5.1.12, <= 5.1.36) | |||
| Type | numeric | ||
| Default | 16777216 | ||
| Min Value | 1024 | ||
| Max Value | 4294967295 | ||
| Permitted Values (>= 5.1.37, <= 5.1.55) | |||
| Type | numeric | ||
| Default | 16777216 | ||
| Min Value | 1024 | ||
| Max Value | 9223372036854775807 | ||
| Permitted Values (>= 5.1.56) | |||
| Type | numeric | ||
| Default | 16777216 | ||
| Min Value | 1024 | ||
| Max Value | 18446744073709551615 | ||
The maximum size of internal in-memory temporary tables. (The
actual limit is determined as the minimum of
tmp_table_size and
max_heap_table_size.) If an
in-memory temporary table exceeds the limit, MySQL
automatically converts it to an on-disk
MyISAM table. Increase the value of
tmp_table_size (and
max_heap_table_size if
necessary) if you do many advanced GROUP BY
queries and you have lots of memory. This variable does not
apply to user-created MEMORY tables.
You can compare the number of internal on-disk temporary
tables created to the total number of internal temporary
tables created by comparing the values of the
Created_tmp_disk_tables and
Created_tmp_tables
variables.
See also Section 8.8.5, “How MySQL Uses Internal Temporary Tables”.
| Command-Line Format | --tmpdir=path | ||
-t | |||
| System Variable Name | tmpdir | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | directory name | ||
The directory used for temporary files and temporary tables.
This variable can be set to a list of several paths that are
used in round-robin fashion. Paths should be separated by
colon characters (“:”) on Unix
and semicolon characters (“;”)
on Windows, NetWare, and OS/2.
The multiple-directory feature can be used to spread the load
between several physical disks. If the MySQL server is acting
as a replication slave, you should not set
tmpdir to point to a
directory on a memory-based file system or to a directory that
is cleared when the server host restarts. A replication slave
needs some of its temporary files to survive a machine restart
so that it can replicate temporary tables or
LOAD DATA
INFILE operations. If files in the temporary file
directory are lost when the server restarts, replication
fails. You can set the slave's temporary directory using the
slave_load_tmpdir variable.
In that case, the slave will not use the general
tmpdir value and you can set
tmpdir to a nonpermanent
location.
| Command-Line Format | --transaction_alloc_block_size=# | ||
| System Variable Name | transaction_alloc_block_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 8192 | ||
| Min Value | 1024 | ||
| Max Value | 4294967295 | ||
| Block Size | 1024 | ||
The amount in bytes by which to increase a per-transaction
memory pool which needs memory. See the description of
transaction_prealloc_size.
| Command-Line Format | --transaction_prealloc_size=# | ||
| System Variable Name | transaction_prealloc_size | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Platform Bit Size | 32 | ||
| Type | numeric | ||
| Default | 4096 | ||
| Min Value | 1024 | ||
| Max Value | 4294967295 | ||
| Block Size | 1024 | ||
There is a per-transaction memory pool from which various
transaction-related allocations take memory. The initial size
of the pool in bytes is
transaction_prealloc_size.
For every allocation that cannot be satisfied from the pool
because it has insufficient memory available, the pool is
increased by
transaction_alloc_block_size
bytes. When the transaction ends, the pool is truncated to
transaction_prealloc_size
bytes.
By making
transaction_prealloc_size
sufficiently large to contain all statements within a single
transaction, you can avoid many malloc()
calls.
| System Variable Name | tx_isolation | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | enumeration | ||
| Default | REPEATABLE-READ | ||
| Valid Values | READ-UNCOMMITTED | ||
READ-COMMITTED | |||
REPEATABLE-READ | |||
SERIALIZABLE | |||
The default transaction isolation level. Defaults to
REPEATABLE-READ.
This variable can be set directly, or indirectly using the
SET TRANSACTION statement. See
Section 13.3.6, “SET TRANSACTION Syntax”. If you set
tx_isolation directly to an
isolation level name that contains a space, the name should be
enclosed within quotation marks, with the space replaced by a
dash. For example:
SET tx_isolation = 'READ-COMMITTED';
Any unique prefix of a valid value may be used to set the value of this variable.
The default transaction isolation level can also be set at
startup using the
--transaction-isolation server
option.
| System Variable Name | unique_checks | ||
| Variable Scope | Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 1 | ||
If set to 1 (the default), uniqueness checks for secondary
indexes in InnoDB tables are performed. If
set to 0, storage engines are permitted to assume that
duplicate keys are not present in input data. If you know for
certain that your data does not contain uniqueness violations,
you can set this to 0 to speed up large table imports to
InnoDB.
Note that setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still permitted to check for them and issue duplicate-key errors if it detects them.
| Command-Line Format | --updatable_views_with_limit=# | ||
| System Variable Name | updatable_views_with_limit | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type | boolean | ||
| Default | 1 | ||
This variable controls whether updates to a view can be made
when the view does not contain all columns of the primary key
defined in the underlying table, if the update statement
contains a LIMIT clause. (Such updates
often are generated by GUI tools.) An update is an
UPDATE or
DELETE statement. Primary key
here means a PRIMARY KEY, or a
UNIQUE index in which no column can contain
NULL.
The variable can have two values:
1 or YES: Issue a
warning only (not an error message). This is the default
value.
0 or NO: Prohibit
the update.
The version number for the server. The value might also
include a suffix indicating server build or configuration
information. -log indicates that one or
more of the general log, slow query log, or binary log are
enabled. -debug indicates that the server was
built with debugging support enabled.
| System Variable Name | version_comment | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The configure script has a <