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
--with-comment option that permits a comment
to be specified when building MySQL. This variable contains
the value of that comment.
| System Variable Name | version_compile_machine | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The type of machine or architecture on which MySQL was built.
| System Variable Name | version_compile_os | ||
| Variable Scope | Global | ||
| Dynamic Variable | No | ||
| Permitted Values | |||
| Type | string | ||
The type of operating system on which MySQL was built.
| Command-Line Format | --wait_timeout=# | ||
| System Variable Name | wait_timeout | ||
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | |||
| Type (Other) | numeric | ||
| Default | 28800 | ||
| Min Value | 1 | ||
| Max Value | 31536000 | ||
| Permitted Values | |||
| Type (Windows) | numeric | ||
| Default | 28800 | ||
| Min Value | 1 | ||
| Max Value | 2147483 | ||
The number of seconds the server waits for activity on a noninteractive connection before closing it. 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.
On thread startup, the session
wait_timeout value is
initialized from the global
wait_timeout value or from
the global
interactive_timeout value,
depending on the type of client (as defined by the
CLIENT_INTERACTIVE connect option to
mysql_real_connect()). See
also interactive_timeout.
The number of errors, warnings, and notes that resulted from the last statement that generated messages. This variable is read only. See Section 13.7.5.42, “SHOW WARNINGS Syntax”.
The MySQL server maintains many system variables that indicate how
it is configured. Section 5.1.4, “Server System Variables”,
describes the meaning of these variables. Each system variable has
a default value. System variables can be set at server startup
using options on the command line or in an option file. Most of
them can be changed dynamically while the server is running by
means of the
SET
statement, which enables you to modify operation of the server
without having to stop and restart it. You can refer to system
variable values in expressions.
The server maintains two kinds of system variables. Global variables affect the overall operation of the server. Session variables affect its operation for individual client connections. A given system variable can have both a global and a session value. Global and session system variables are related as follows:
When the server starts, it initializes all global variables to their default values. These defaults can be changed by options specified on the command line or in an option file. (See Section 4.2.3, “Specifying Program Options”.)
The server also maintains a set of session variables for each
client that connects. The client's session variables are
initialized at connect time using the current values of the
corresponding global variables. For example, the client's SQL
mode is controlled by the session
sql_mode value, which is
initialized when the client connects to the value of the
global sql_mode value.
System variable values can be set globally at server startup by
using options on the command line or in an option file. When you
use a startup option to set a variable that takes a numeric value,
the value can be given with a suffix of K,
M, or G (either uppercase or
lowercase) to indicate a multiplier of 1024,
10242 or
10243; that is, units of kilobytes,
megabytes, or gigabytes, respectively. Thus, the following command
starts the server with a query cache size of 16 megabytes and a
maximum packet size of one gigabyte:
mysqld --query_cache_size=16M --max_allowed_packet=1G
Within an option file, those variables are set like this:
[mysqld] query_cache_size=16M max_allowed_packet=1G
The lettercase of suffix letters does not matter;
16M and 16m are equivalent,
as are 1G and 1g.
If you want to restrict the maximum value to which a system
variable can be set at runtime with the
SET
statement, you can specify this maximum by using an option of the
form
--maximum-
at server startup. For example, to prevent the value of
var_name=valuequery_cache_size from being
increased to more than 32MB at runtime, use the option
--maximum-query_cache_size=32M.
Many system variables are dynamic and can be changed while the
server runs by using the
SET
statement. For a list, see
Section 5.1.5.2, “Dynamic System Variables”. To change a system
variable with
SET,
refer to it as var_name, optionally
preceded by a modifier:
To indicate explicitly that a variable is a global variable,
precede its name by GLOBAL or
@@global.. The
SUPER privilege is required to
set global variables.
To indicate explicitly that a variable is a session variable,
precede its name by SESSION,
@@session., or @@.
Setting a session variable requires no special privilege, but
a client can change only its own session variables, not those
of any other client.
LOCAL and @@local. are
synonyms for SESSION and
@@session..
If no modifier is present,
SET
changes the session variable.
A SET
statement can contain multiple variable assignments, separated by
commas. If you set several system variables, the most recent
GLOBAL or SESSION modifier
in the statement is used for following variables that have no
modifier specified.
Examples:
SET sort_buffer_size=10000; SET @@local.sort_buffer_size=10000; SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000; SET @@sort_buffer_size=1000000; SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
The @@
syntax for system variables is supported for compatibility with
some other database systems.
var_name
If you change a session system variable, the value remains in effect until your session ends or until you change the variable to a different value. The change is not visible to other clients.
If you change a global system variable, the value is remembered
and used for new connections until the server restarts. (To make a
global system variable setting permanent, you should set it in an
option file.) The change is visible to any client that accesses
that global variable. However, the change affects the
corresponding session variable only for clients that connect after
the change. The global variable change does not affect the session
variable for any client that is currently connected (not even that
of the client that issues the
SET
GLOBAL statement).
To prevent incorrect usage, MySQL produces an error if you use
SET
GLOBAL with a variable that can only be used with
SET
SESSION or if you do not specify
GLOBAL (or @@global.) when
setting a global variable.
To set a SESSION variable to the
GLOBAL value or a GLOBAL
value to the compiled-in MySQL default value, use the
DEFAULT keyword. For example, the following two
statements are identical in setting the session value of
max_join_size to the global
value:
SET max_join_size=DEFAULT; SET @@session.max_join_size=@@global.max_join_size;
Not all system variables can be set to DEFAULT.
In such cases, use of DEFAULT results in an
error.
You can refer to the values of specific global or session system
variables in expressions by using one of the
@@-modifiers. For example, you can retrieve
values in a SELECT statement like
this:
SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
When you refer to a system variable in an expression as
@@ (that is,
when you do not specify var_name@@global. or
@@session.), MySQL returns the session value if
it exists and the global value otherwise. (This differs from
SET @@, which always refers to
the session value.)
var_name =
value
Some variables displayed by SHOW VARIABLES
may not be available using SELECT
@@ syntax; an
var_nameUnknown system variable occurs. As a
workaround in such cases, you can use SHOW VARIABLES
LIKE '.
var_name'
Suffixes for specifying a value multiplier can be used when
setting a variable at server startup, but not to set the value
with SET
at runtime. On the other hand, with
SET you
can assign a variable's value using an expression, which is not
true when you set a variable at server startup. For example, the
first of the following lines is legal at server startup, but the
second is not:
shell>mysql --max_allowed_packet=16Mshell>mysql --max_allowed_packet=16*1024*1024
Conversely, the second of the following lines is legal at runtime, but the first is not:
mysql>SET GLOBAL max_allowed_packet=16M;mysql>SET GLOBAL max_allowed_packet=16*1024*1024;
Some system variables can be enabled with the
SET
statement by setting them to ON or
1, or disabled by setting them to
OFF or 0. However, to set
such a variable on the command line or in an option file, you
must set it to 1 or 0;
setting it to ON or OFF
will not work. For example, on the command line,
--delay_key_write=1 works but
--delay_key_write=ON does not.
To display system variable names and values, use the
SHOW VARIABLES statement:
mysql> SHOW VARIABLES;
+---------------------------------+-----------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /home/mysql/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /home/mysql/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
...
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
...
| version | 5.1.6-alpha-log |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | suse-linux |
| wait_timeout | 28800 |
+---------------------------------+-----------------------------------+
With a LIKE clause, the statement
displays only those variables that match the pattern. To obtain a
specific variable name, use a LIKE
clause as shown:
SHOW VARIABLES LIKE 'max_join_size'; SHOW SESSION VARIABLES LIKE 'max_join_size';
To get a list of variables whose name match a pattern, use the
“%” wildcard character in a
LIKE clause:
SHOW VARIABLES LIKE '%size%'; SHOW GLOBAL VARIABLES LIKE '%size%';
Wildcard characters can be used in any position within the pattern
to be matched. Strictly speaking, because
“_” is a wildcard that matches any
single character, you should escape it as
“\_” to match it literally. In
practice, this is rarely necessary.
For SHOW VARIABLES, if you specify
neither GLOBAL nor SESSION,
MySQL returns SESSION values.
The reason for requiring the GLOBAL keyword
when setting GLOBAL-only variables but not when
retrieving them is to prevent problems in the future. If we were
to remove a SESSION variable that has the same
name as a GLOBAL variable, a client with the
SUPER privilege might accidentally
change the GLOBAL variable rather than just the
SESSION variable for its own connection. If we
add a SESSION variable with the same name as a
GLOBAL variable, a client that intends to
change the GLOBAL variable might find only its
own SESSION variable changed.
A structured variable differs from a regular system variable in two respects:
Its value is a structure with components that specify server parameters considered to be closely related.
There might be several instances of a given type of structured variable. Each one has a different name and refers to a different resource maintained by the server.
MySQL 5.1 supports one structured variable type, which specifies parameters governing the operation of key caches. A key cache structured variable has these components:
This section describes the syntax for referring to structured variables. Key cache variables are used for syntax examples, but specific details about how key caches operate are found elsewhere, in Section 8.6.1, “The MyISAM Key Cache”.
To refer to a component of a structured variable instance, you
can use a compound name in
instance_name.component_name format.
Examples:
hot_cache.key_buffer_size hot_cache.key_cache_block_size cold_cache.key_cache_block_size
For each structured system variable, an instance with the name
of default is always predefined. If you refer
to a component of a structured variable without any instance
name, the default instance is used. Thus,
default.key_buffer_size and
key_buffer_size both refer to
the same system variable.
Structured variable instances and components follow these naming rules:
For a given type of structured variable, each instance must
have a name that is unique within
variables of that type. However, instance names need not be
unique across structured variable
types. For example, each structured variable has an instance
named default, so
default is not unique across variable
types.
The names of the components of each structured variable type must be unique across all system variable names. If this were not true (that is, if two different types of structured variables could share component member names), it would not be clear which default structured variable to use for references to member names that are not qualified by an instance name.
If a structured variable instance name is not legal as an
unquoted identifier, refer to it as a quoted identifier
using backticks. For example, hot-cache
is not legal, but `hot-cache` is.
global, session, and
local are not legal instance names. This
avoids a conflict with notation such as
@@global.
for referring to nonstructured system variables.
var_name
Currently, the first two rules have no possibility of being violated because the only structured variable type is the one for key caches. These rules will assume greater significance if some other type of structured variable is created in the future.
With one exception, you can refer to structured variable components using compound names in any context where simple variable names can occur. For example, you can assign a value to a structured variable using a command-line option:
shell> mysqld --hot_cache.key_buffer_size=64K
In an option file, use this syntax:
[mysqld] hot_cache.key_buffer_size=64K
If you start the server with this option, it creates a key cache
named hot_cache with a size of 64KB in
addition to the default key cache that has a default size of
8MB.
Suppose that you start the server as follows:
shell>mysqld --key_buffer_size=256K \--extra_cache.key_buffer_size=128K \--extra_cache.key_cache_block_size=2048
In this case, the server sets the size of the default key cache
to 256KB. (You could also have written
--default.key_buffer_size=256K.) In addition,
the server creates a second key cache named
extra_cache that has a size of 128KB, with
the size of block buffers for caching table index blocks set to
2048 bytes.
The following example starts the server with three different key caches having sizes in a 3:1:1 ratio:
shell>mysqld --key_buffer_size=6M \--hot_cache.key_buffer_size=2M \--cold_cache.key_buffer_size=2M
Structured variable values may be set and retrieved at runtime
as well. For example, to set a key cache named
hot_cache to a size of 10MB, use either of
these statements:
mysql>SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;mysql>SET @@global.hot_cache.key_buffer_size = 10*1024*1024;
To retrieve the cache size, do this:
mysql> SELECT @@global.hot_cache.key_buffer_size;
However, the following statement does not work. The variable is
not interpreted as a compound name, but as a simple string for a
LIKE pattern-matching operation:
mysql> SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size';
This is the exception to being able to use structured variable names anywhere a simple variable name may occur.
Many server system variables are dynamic and can be set at
runtime using SET
GLOBAL or
SET
SESSION. You can also obtain their values using
SELECT. See
Section 5.1.5, “Using System Variables”.
The following table shows the full list of all dynamic system
variables. The last column indicates for each variable whether
GLOBAL or SESSION (or
both) apply. The table also lists session options that can be
set with the
SET
statement. Section 5.1.4, “Server System Variables”, discusses
these options.
Variables that have a type of “string” take a
string value. Variables that have a type of
“numeric” take a numeric value. Variables that have
a type of “boolean” can be set to 0, 1,
ON or OFF. (If you set
them on the command line or in an option file, use the numeric
values.) Variables that are marked as “enumeration”
normally should be set to one of the available values for the
variable, but can also be set to the number that corresponds to
the desired enumeration value. For enumerated system variables,
the first enumeration value corresponds to 0. This differs from
ENUM columns, for which the first
enumeration value corresponds to 1.
Table 5.3 Dynamic Variable Summary
The server maintains many status variables that provide
information about its operation. You can view these variables and
their values by using the SHOW [GLOBAL | SESSION]
STATUS statement (see Section 13.7.5.37, “SHOW STATUS Syntax”).
The optional GLOBAL keyword aggregates the
values over all connections, and SESSION shows
the values for the current connection.
mysql> SHOW GLOBAL STATUS;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |
...
| Connections | 30023 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 3 |
| Created_tmp_tables | 2 |
...
| Threads_created | 217 |
| Threads_running | 88 |
| Uptime | 1389872 |
+-----------------------------------+------------+
Many status variables are reset to 0 by the
FLUSH STATUS
statement.
The following table lists all available server status variables:
Table 5.4 Status Variable Summary
The status variables have the following meanings. For meanings of status variables specific to MySQL Cluster, see Section 17.3.4.4, “MySQL Cluster Status Variables”.
The number of connections that were aborted because the client died without closing the connection properly. See Section B.5.2.11, “Communication Errors and Aborted Connections”.
The number of failed attempts to connect to the MySQL server. See Section B.5.2.11, “Communication Errors and Aborted Connections”.
The number of transactions that used the temporary binary log
cache but that exceeded the value of
binlog_cache_size and used a
temporary file to store statements from the transaction.
The number of transactions that used the temporary binary log cache.
The number of bytes received from all clients.
The number of bytes sent to all clients.
The Com_
statement counter variables indicate the number of times each
xxxxxx statement has been executed.
There is one status variable for each type of statement. For
example, Com_delete and
Com_update count
DELETE and
UPDATE statements,
respectively. Com_delete_multi and
Com_update_multi are similar but apply to
DELETE and
UPDATE statements that use
multiple-table syntax.
If a query result is returned from query cache, the server
increments the Qcache_hits
status variable, not Com_select. See
Section 8.6.3.4, “Query Cache Status and Maintenance”.
All of the
Com_stmt_
variables are increased even if a prepared statement argument
is unknown or an error occurred during execution. In other
words, their values correspond to the number of requests
issued, not to the number of requests successfully completed.
xxx
The Com_stmt_
status variables are as follows:
xxx
Com_stmt_prepare
Com_stmt_execute
Com_stmt_fetch
Com_stmt_send_long_data
Com_stmt_reset
Com_stmt_close
Those variables stand for prepared statement commands. Their
names refer to the
COM_ command
set used in the network layer. In other words, their values
increase whenever prepared statement API calls such as
mysql_stmt_prepare(),
mysql_stmt_execute(), and so forth are
executed. However, xxxCom_stmt_prepare,
Com_stmt_execute and
Com_stmt_close also increase for
PREPARE,
EXECUTE, or
DEALLOCATE PREPARE,
respectively. Additionally, the values of the older statement
counter variables Com_prepare_sql,
Com_execute_sql, and
Com_dealloc_sql increase for the
PREPARE,
EXECUTE, and
DEALLOCATE PREPARE statements.
Com_stmt_fetch stands for the total number
of network round-trips issued when fetching from cursors.
Com_stmt_reprepare indicates the number of
times statements were automatically reprepared by the server
after metadata changes to tables or views referred to by the
statement. This variable was added in MySQL 5.1.25. A
reprepare operation increments
Com_stmt_reprepare, and also
Com_stmt_prepare.
Whether the client connection uses compression in the client/server protocol. Added in MySQL 5.1.2.
The number of connection attempts (successful or not) to the MySQL server.
The number of internal on-disk temporary tables created by the server while executing statements.
If an internal temporary table is created initially as an
in-memory table but becomes too large, MySQL automatically
converts it to an on-disk table. The maximum size for
in-memory temporary tables is the minimum of the
tmp_table_size and
max_heap_table_size values.
If Created_tmp_disk_tables
is large, you may want to increase the
tmp_table_size or
max_heap_table_size value to
lessen the likelihood that internal temporary tables in memory
will be converted to on-disk 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”.
How many temporary files mysqld has created.
The number of internal temporary tables created by the server while executing statements.
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”.
Each invocation of the SHOW
STATUS statement uses an internal temporary table
and increments the global
Created_tmp_tables value.
The number of rows written with INSERT
DELAYED for which some error occurred (probably
duplicate key).
The number of INSERT DELAYED
handler threads in use.
The number of INSERT DELAYED
rows written.
The number of times the server flushes tables, whether because
a user executed a FLUSH
TABLES statement or due to internal server
operation. It is also incremented by receipt of a
COM_REFRESH packet. This is in contrast to
Com_flush,
which indicates how many FLUSH statements
have been executed, whether
FLUSH TABLES,
FLUSH LOGS,
and so forth.
The number of internal COMMIT
statements.
The number of times that rows have been deleted from tables.
A counter for the prepare phase of two-phase commit operations.
The number of times the first entry in an index was read. If
this value is high, it suggests that the server is doing a lot
of full index scans; for example, SELECT col1 FROM
foo, assuming that col1 is
indexed.
The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.
The number of requests to read the next row in key order. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan.
The number of requests to read the previous row in key order.
This read method is mainly used to optimize ORDER BY
... DESC.
The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.
The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
The number of requests for a storage engine to perform a rollback operation.
The number of requests for a storage engine to place a savepoint.
The number of requests for a storage engine to roll back to a savepoint.
The number of requests to update a row in a table.
The number of requests to insert a row in a table.
The number of pages containing data (dirty or clean).
Innodb_buffer_pool_pages_dirty
The number of pages currently dirty.
Innodb_buffer_pool_pages_flushed
The number of buffer pool page-flush requests.
The number of free pages.
Innodb_buffer_pool_pages_latched
The number of latched pages in InnoDB
buffer pool. These are pages currently being read or written
or that cannot be flushed or removed for some other reason.
Calculation of this variable is expensive, so as of MySQL
5.1.28, it is available only when the
UNIV_DEBUG system is defined at server
build time.
The number of pages that are busy because they have been
allocated for administrative overhead such as row locks or the
adaptive hash index. This value can also be calculated as
Innodb_buffer_pool_pages_total
–
Innodb_buffer_pool_pages_free
–
Innodb_buffer_pool_pages_data.
Innodb_buffer_pool_pages_total
The total size of the buffer pool, in pages.
(InnoDB Plugin only) The number of pages
read into the InnoDB buffer pool by the
read-ahead background thread.
This variable was added in MySQL 5.1.41.
Innodb_buffer_pool_read_ahead_evicted
(InnoDB Plugin only) The number of pages
read into the InnoDB buffer pool by the
read-ahead background thread that were subsequently evicted
without having been accessed by queries.
This variable was added in MySQL 5.1.41.
Innodb_buffer_pool_read_ahead_rnd
The number of “random” read-aheads initiated by
InnoDB. This happens when a query scans a
large portion of a table but in random order.
For InnoDB Plugin, this variable was
removed in MySQL 5.1.41.
Innodb_buffer_pool_read_ahead_seq
The number of sequential read-aheads initiated by
InnoDB. This happens when
InnoDB does a sequential full table scan.
For InnoDB Plugin, this variable was
removed in MySQL 5.1.41.
Innodb_buffer_pool_read_requests
The number of logical read requests.
The number of logical reads that InnoDB
could not satisfy from the buffer pool, and had to read
directly from the disk.
Normally, writes to the InnoDB buffer pool
happen in the background. However, if it is necessary to read
or create a page and no clean pages are available, it is also
necessary to wait for pages to be flushed first. This counter
counts instances of these waits. If the buffer pool size has
been set properly, this value should be small.
Innodb_buffer_pool_write_requests
The number writes done to the InnoDB buffer
pool.
The number of fsync() operations so far.
The current number of pending fsync()
operations.
The current number of pending reads.
The current number of pending writes.
The amount of data read since the server was started.
The total number of data reads.
The total number of data writes.
The amount of data written so far, in bytes.
The number of pages that have been written for doublewrite operations. See Section 14.6.6.1, “InnoDB Disk I/O”.
The number of doublewrite operations that have been performed. See Section 14.6.6.1, “InnoDB Disk I/O”.
(InnoDB Plugin only) Indicates whether the
server was built with atomic instructions. This variable was
added in MySQL 5.1.38.
The number of times that the log buffer was too small and a wait was required for it to be flushed before continuing.
The number of log write requests.
The number of physical writes to the log file.
The number of fsync() writes done to the
log file.
The number of pending log file fsync()
operations.
The number of pending log file writes.
The number of bytes written to the log file.
The compiled-in InnoDB page size (default
16KB). Many values are counted in pages; the page size enables
them to be easily converted to bytes.
The number of pages created.
The number of pages read.
The number of pages written.
The number of row locks currently being waited for.
The total time spent in acquiring row locks, in milliseconds.
The average time to acquire a row lock, in milliseconds.
The maximum time to acquire a row lock, in milliseconds.
The number of times a row lock had to be waited for.
The number of rows deleted from InnoDB
tables.
The number of rows inserted into InnoDB
tables.
The number of rows read from InnoDB tables.
The number of rows updated in InnoDB
tables.
The number of key blocks in the key cache that have changed but have not yet been flushed to disk.
The number of unused blocks in the key cache. You can use this
value to determine how much of the key cache is in use; see
the discussion of
key_buffer_size in
Section 5.1.4, “Server System Variables”.
The number of used blocks in the key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time.
The number of requests to read a key block from the cache.
The number of physical reads of a key block from disk. If
Key_reads is large, then
your key_buffer_size value is
probably too small. The cache miss rate can be calculated as
Key_reads/Key_read_requests.
The number of requests to write a key block to the cache.
The number of physical writes of a key block to disk.
The total cost of the last compiled query as computed by the
query optimizer. This is useful for comparing the cost of
different query plans for the same query. The default value of
0 means that no query has been compiled yet. The default value
is 0. Last_query_cost has
session scope.
The Last_query_cost value
can be computed accurately only for simple “flat”
queries, not complex queries such as those with subqueries or
UNION. For the latter, the
value is set to 0.
The maximum number of connections that have been in use simultaneously since the server started.
The number of rows waiting to be written in
INSERT DELAYED queues.
The number of files that are open. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes. Also, the count does not include files that storage engines open using their own internal functions rather than asking the server level to do so.
The number of streams that are open (used mainly for logging).
The number of cached .frm files. This
variable was added in MySQL 5.1.3.
The number of tables that are open.
The number of files that have been opened with
my_open() (a mysys
library function). Parts of the server that open files without
using this function do not increment the count. This variable
was added in MySQL 5.1.21.
The number of .frm files that have been
cached. This variable was added in MySQL 5.1.24.
The number of tables that have been opened. If
Opened_tables is big, your
table_open_cache value is
probably too small.
The current number of prepared statements. (The maximum number
of statements is given by the
max_prepared_stmt_count
system variable.) This variable was added in MySQL 5.1.14.
The number of free memory blocks in the query cache.
The amount of free memory for the query cache.
The number of query cache hits.
The number of queries added to the query cache.
The number of queries that were deleted from the query cache because of low memory.
The number of noncached queries (not cacheable, or not cached
due to the query_cache_type
setting).
The number of queries registered in the query cache.
The total number of blocks in the query cache.
The number of statements executed by the server. This variable
includes statements executed within stored programs, unlike
the Questions variable. It
does not count COM_PING or
COM_STATISTICS commands. This variable was
added in MySQL 5.1.31.
The number of statements executed by the server. As of MySQL
5.1.31, this includes only statements sent to the server by
clients and no longer includes statements executed within
stored programs, unlike the
Queries variable. This
variable does not count COM_PING,
COM_STATISTICS,
COM_STMT_PREPARE,
COM_STMT_CLOSE, or
COM_STMT_RESET commands.
The status of fail-safe replication (not implemented). This variable is unused and is removed in MySQL 5.6.
The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
The number of joins that used a range search on a reference table.
The number of joins that used ranges on the first table. This is normally not a critical issue even if the value is quite large.
The number of joins without keys that check for key usage after each row. If this is not 0, you should carefully check the indexes of your tables.
The number of joins that did a full scan of the first table.
The number of temporary tables that the slave SQL thread currently has open. If the value is greater than zero, it is not safe to shut down the slave; see Section 16.4.1.22, “Replication and Temporary Tables”.
The total number of times since startup that the replication slave SQL thread has retried transactions.
This is ON if this server is a replication
slave that is connected to a replication master, and both the
I/O and SQL threads are running; otherwise, it is
OFF.
The number of threads that have taken more than
slow_launch_time seconds to
create.
The number of queries that have taken more than
long_query_time seconds. This
counter increments regardless of whether the slow query log is
enabled. For information about that log, see
Section 5.2.5, “The Slow Query Log”.
The number of merge passes that the sort algorithm has had to
do. If this value is large, you should consider increasing the
value of the sort_buffer_size
system variable.
The number of sorts that were done using ranges.
The number of sorted rows.
The number of sorts that were done by scanning the table.
The number of negotiates needed to establish the connection.
The number of accepted SSL connections.
The number of callback cache hits.
The current SSL cipher (empty for non-SSL connections).
The list of possible SSL ciphers (empty for non-SSL connections).
The number of SSL connection attempts to an SSL-enabled master.
The number of negotiates needed to establish the connection to an SSL-enabled master.
The SSL context verification depth (how many certificates in the chain are tested).
The SSL context verification mode.
The default SSL timeout.
The number of successful SSL connections to the server.
The number of successful slave connections to an SSL-enabled master.
The number of SSL session cache hits.
The number of SSL session cache misses.
The SSL session cache mode.
The number of SSL session cache overflows.
The SSL session cache size.
The number of SSL session cache timeouts.
How many SSL connections were reused from the cache.
Ssl_used_session_cache_entries
How many SSL session cache entries were used.
The verification depth for replication SSL connections.
The verification mode for replication SSL connections.
The SSL protocol version of the connection.
The number of times that a request for a table lock could be granted immediately.
The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
For the memory-mapped implementation of the log that is used
by mysqld when it acts as the transaction
coordinator for recovery of internal XA transactions, this
variable indicates the largest number of pages used for the
log since the server started. If the product of
Tc_log_max_pages_used and
Tc_log_page_size is always
significantly less than the log size, the size is larger than
necessary and can be reduced. (The size is set by the
--log-tc-size option.
Currently, this variable is unused: It is unneeded for binary
log-based recovery, and the memory-mapped recovery log method
is not used unless the number of storage engines capable of
two-phase commit is greater than one.
(InnoDB is the only applicable engine.)
The page size used for the memory-mapped implementation of the
XA recovery log. The default value is determined using
getpagesize(). Currently, this variable is
unused for the same reasons as described for
Tc_log_max_pages_used.
For the memory-mapped implementation of the recovery log, this
variable increments each time the server was not able to
commit a transaction and had to wait for a free page in the
log. If this value is large, you might want to increase the
log size (with the
--log-tc-size option). For
binary log-based recovery, this variable increments each time
the binary log cannot be closed because there are two-phase
commits in progress. (The close operation waits until all such
transactions are finished.)
The number of threads in the thread cache.
The number of currently open connections.
The number of threads created to handle connections. If
Threads_created is big, you
may want to increase the
thread_cache_size value. The
cache miss rate can be calculated as
Threads_created/Connections.
The number of threads that are not sleeping.
The number of seconds that the server has been up.
The number of seconds since the most recent FLUSH
STATUS statement. This variable was added in 5.1.24.
The MySQL server can operate in different SQL modes, and can apply
these modes differently for different clients, depending on the
value of the sql_mode system
variable. DBAs can set the global SQL mode to match site server
operating requirements, and each application can set its session
SQL mode to its own requirements.
Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.
When working with InnoDB tables using the
InnoDB Plugin, consider also the
innodb_strict_mode system
variable. It enables additional error checks for
InnoDB tables.
The default SQL mode is empty (no modes set).
To set the SQL mode at server startup, use the
--sql-mode="
option on the command line, or
modes"sql-mode="
in an option file such as modes"my.cnf (Unix
operating systems) or my.ini (Windows).
modes is a list of different modes
separated by commas. To clear the SQL mode explicitly, set it to
an empty string using --sql-mode=""
on the command line, or sql-mode=""
in an option file.
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.
To change the SQL mode at runtime, set the global or session
sql_mode system variable using a
SET
statement:
SET GLOBAL sql_mode = 'modes'; SET SESSION sql_mode = 'modes';
Setting the GLOBAL variable requires the
SUPER privilege and affects the
operation of all clients that connect from that time on. Setting
the SESSION variable affects only the current
client. Each client can change its session
sql_mode value at any time.
To determine the current global or session
sql_mode value, use the following
statements:
SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;
SQL mode and user-defined partitioning. Changing the server SQL mode after creating and inserting data into partitioned tables can cause major changes in the behavior of such tables, and could lead to loss or corruption of data. It is strongly recommended that you never change the SQL mode once you have created tables employing user-defined partitioning.
When replicating partitioned tables, differing SQL modes on master and slave can also lead to problems. For best results, you should always use the same server SQL mode on the master and on the slave.
See Section 18.5, “Restrictions and Limitations on Partitioning”, for more information.
The most important sql_mode
values are probably these:
This mode changes syntax and behavior to conform more closely to standard SQL. It is one of the special combination modes listed at the end of this section.
If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More details are given later in this section.
Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. It is one of the special combination modes listed at the end of this section.
When this manual refers to “strict mode,” it means a
mode with either or both
STRICT_TRANS_TABLES or
STRICT_ALL_TABLES enabled.
The following list describes all supported SQL modes:
Do not perform full checking of dates. Check only that the
month is in the range from 1 to 12 and the day is in the range
from 1 to 31. This is very convenient for Web applications
where you obtain year, month, and day in three different
fields and you want to store exactly what the user inserted
(without date validation). This mode applies to
DATE and
DATETIME columns. It does not
apply TIMESTAMP columns, which
always require a valid date.
The server requires that month and day values be legal, and
not merely in the range 1 to 12 and 1 to 31, respectively.
With strict mode disabled, invalid dates such as
'2004-04-31' are converted to
'0000-00-00' and a warning is generated.
With strict mode enabled, invalid dates generate an error. To
permit such dates, enable
ALLOW_INVALID_DATES.
Treat “"” as an identifier
quote character (like the “`”
quote character) and not as a string quote character. You can
still use “`” to quote
identifiers with this mode enabled. With
ANSI_QUOTES enabled, you
cannot use double quotation marks to quote literal strings,
because it is interpreted as an identifier.
The
ERROR_FOR_DIVISION_BY_ZERO
mode affects handling of division by zero, which includes
MOD(.
For data-change operations
(N,0)INSERT,
UPDATE), its effect also
depends on whether strict SQL mode is enabled.
If this mode is not enabled, division by zero inserts
NULL and produces no warning.
If this mode is enabled, division by zero inserts
NULL and produces a warning.
If this mode and strict mode are enabled, division by zero
produces an error, unless IGNORE is
given as well. For INSERT IGNORE and
UPDATE IGNORE, division by zero inserts
NULL and produces a warning.
For SELECT, division by zero
returns NULL. Enabling
ERROR_FOR_DIVISION_BY_ZERO
causes a warning to be produced as well, regardless of whether
strict mode is enabled.
The precedence of the NOT
operator is such that expressions such as NOT a
BETWEEN b AND c are parsed as NOT (a
BETWEEN b AND c). In some older versions of MySQL,
the expression was parsed as (NOT a) BETWEEN b AND
c. The old higher-precedence behavior can be
obtained by enabling the
HIGH_NOT_PRECEDENCE SQL
mode.
mysql>SET sql_mode = '';mysql>SELECT NOT 1 BETWEEN -5 AND 5;-> 0 mysql>SET sql_mode = 'HIGH_NOT_PRECEDENCE';mysql>SELECT NOT 1 BETWEEN -5 AND 5;-> 1
Permit spaces between a function name and the
“(” character. This causes
built-in function names to be treated as reserved words. As a
result, identifiers that are the same as function names must
be quoted as described in Section 9.2, “Schema Object Names”. For
example, because there is a
COUNT() function, the use of
count as a table name in the following
statement causes an error:
mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax
The table name should be quoted:
mysql> CREATE TABLE `count` (i INT);
Query OK, 0 rows affected (0.00 sec)
The IGNORE_SPACE SQL mode
applies to built-in functions, not to user-defined functions
or stored functions. It is always permissible to have spaces
after a UDF or stored function name, regardless of whether
IGNORE_SPACE is enabled.
For further discussion of
IGNORE_SPACE, see
Section 9.2.4, “Function Name Parsing and Resolution”.
Prevent the GRANT statement
from automatically creating new users if it would otherwise do
so, unless a nonempty password also is specified.
NO_AUTO_VALUE_ON_ZERO
affects handling of AUTO_INCREMENT columns.
Normally, you generate the next sequence number for the column
by inserting either NULL or
0 into it.
NO_AUTO_VALUE_ON_ZERO
suppresses this behavior for 0 so that only
NULL generates the next sequence number.
This mode can be useful if 0 has been
stored in a table's AUTO_INCREMENT column.
(Storing 0 is not a recommended practice,
by the way.) For example, if you dump the table with
mysqldump and then reload it, MySQL
normally generates new sequence numbers when it encounters the
0 values, resulting in a table with
contents different from the one that was dumped. Enabling
NO_AUTO_VALUE_ON_ZERO before
reloading the dump file solves this problem.
mysqldump now automatically includes in its
output a statement that enables
NO_AUTO_VALUE_ON_ZERO, to
avoid this problem.
Disable the use of the backslash character
(“\”) as an escape character
within strings. With this mode enabled, backslash becomes an
ordinary character like any other.
When creating a table, ignore all INDEX
DIRECTORY and DATA DIRECTORY
directives. This option is useful on slave replication
servers.
Control automatic substitution of the default storage engine
when a statement such as CREATE
TABLE or ALTER TABLE
specifies a storage engine that is disabled or not compiled
in.
Up through MySQL 5.1.11, with
NO_ENGINE_SUBSTITUTION
disabled, the default engine is used and a warning occurs if
the desired engine is known but disabled or not compiled in.
If the desired engine is invalid (not a known engine name), an
error occurs and the table is not created or altered.
With NO_ENGINE_SUBSTITUTION
enabled, an error occurs and the table is not created or
altered if the desired engine is unavailable for any reason
(whether disabled or invalid).
As of MySQL 5.1.12, storage engines can be pluggable at runtime, so the distinction between disabled and invalid no longer applies. All unavailable engines are treated the same way:
With NO_ENGINE_SUBSTITUTION
disabled, for CREATE TABLE the
default engine is used and a warning occurs if the desired
engine is unavailable. For ALTER
TABLE, a warning occurs and the table is not
altered.
With NO_ENGINE_SUBSTITUTION
enabled, an error occurs and the table is not created or
altered if the desired engine is unavailable.
Do not print MySQL-specific column options in the output of
SHOW CREATE TABLE. This mode is
used by mysqldump in portability mode.
Do not print MySQL-specific index options in the output of
SHOW CREATE TABLE. This mode is
used by mysqldump in portability mode.
Do not print MySQL-specific table options (such as
ENGINE) in the output of
SHOW CREATE TABLE. This mode is
used by mysqldump in portability mode.
By default, subtraction between integer operands produces an
UNSIGNED result if any operand
isUNSIGNED. When
NO_UNSIGNED_SUBTRACTION is
enabled, the subtraction result is signed, even if
any operand is unsigned. For example, compare the
type of column c2 in table
t1 with that of column
c2 in table t2:
mysql>SET sql_mode='';mysql>CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);mysql>CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;mysql>DESCRIBE t1;+-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | c2 | bigint(21) unsigned | | | 0 | | +-------+---------------------+------+-----+---------+-------+ mysql>SET sql_mode='NO_UNSIGNED_SUBTRACTION';mysql>CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;mysql>DESCRIBE t2;+-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c2 | bigint(21) | | | 0 | | +-------+------------+------+-----+---------+-------+
Note that this means that BIGINT UNSIGNED
is not 100% usable in all contexts. See
Section 12.10, “Cast Functions and Operators”.
mysql>SET sql_mode = '';mysql>SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | 18446744073709551615 | +-------------------------+ mysql>SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';mysql>SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | -1 | +-------------------------+
The NO_ZERO_DATE mode
affects whether the server permits
'0000-00-00' as a valid date. Its effect
also depends on whether strict SQL mode is enabled.
If this mode is not enabled,
'0000-00-00' is permitted and inserts
produce no warning.
If this mode is enabled, '0000-00-00'
is permitted and inserts produce a warning.
If this mode and strict mode are enabled,
'0000-00-00' is not permitted and
inserts produce an error, unless IGNORE
is given as well. For INSERT IGNORE and
UPDATE IGNORE,
'0000-00-00' is permitted and inserts
produce a warning.
The NO_ZERO_IN_DATE mode
affects whether the server permits dates in which the year
part is nonzero but the month or day part is 0. (This mode
affects dates such as '2010-00-01' or
'2010-01-00', but not
'0000-00-00'. To control whether the server
permits '0000-00-00', use the
NO_ZERO_DATE mode.) The
effect of NO_ZERO_IN_DATE
also depends on whether strict SQL mode is enabled.
If this mode is not enabled, dates with zero parts are permitted and inserts produce no warning.
If this mode is enabled, dates with zero parts are
inserted as '0000-00-00' and produce a
warning.
If this mode and strict mode are enabled, dates with zero
parts are not permitted and inserts produce an error,
unless IGNORE is given as well. For
INSERT IGNORE and UPDATE
IGNORE, dates with zero parts are inserted as
'0000-00-00' and produce a warning.
Reject queries for which the select list or (as of MySQL
5.1.10) HAVING condition refer to
nonaggregated columns that are not named in the GROUP
BY clause.
The following queries are invalid with
ONLY_FULL_GROUP_BY enabled.
The first is invalid because the nonaggregated
address column in the select list is not
named in the GROUP BY clause, and the
second because max_age in the
HAVING clause is not named in the
GROUP BY clause:
mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): 't.address' isn't in GROUP BY
mysql>SELECT name, MAX(age) AS max_age FROM t GROUP BY name->HAVING max_age < 30;Empty set (0.00 sec) ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause
In the second example, the query could be rewritten to use
HAVING MAX(age) instead, so that the
reference is to a column named in an aggregate function.
(max_age fails because it
is an aggregate function.)
If a query has aggregate functions and no GROUP
BY clause, it cannot have nonaggregated columns in
the select list or ORDER BY list with
ONLY_FULL_GROUP_BY enabled:
mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause
For additional discussion, see Section 12.17.3, “MySQL Handling of GROUP BY”.
By default, trailing spaces are trimmed from
CHAR column values on
retrieval. If
PAD_CHAR_TO_FULL_LENGTH is
enabled, trimming does not occur and retrieved
CHAR values are padded to their
full length. This mode does not apply to
VARCHAR columns, for which
trailing spaces are retained on retrieval. This mode was added
in MySQL 5.1.20.
mysql>CREATE TABLE t1 (c1 CHAR(10));Query OK, 0 rows affected (0.37 sec) mysql>INSERT INTO t1 (c1) VALUES('xy');Query OK, 1 row affected (0.01 sec) mysql>SET sql_mode = '';Query OK, 0 rows affected (0.00 sec) mysql>SELECT c1, CHAR_LENGTH(c1) FROM t1;+------+-----------------+ | c1 | CHAR_LENGTH(c1) | +------+-----------------+ | xy | 2 | +------+-----------------+ 1 row in set (0.00 sec) mysql>SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';Query OK, 0 rows affected (0.00 sec) mysql>SELECT c1, CHAR_LENGTH(c1) FROM t1;+------------+-----------------+ | c1 | CHAR_LENGTH(c1) | +------------+-----------------+ | xy | 10 | +------------+-----------------+ 1 row in set (0.00 sec)
Treat || as a
string concatenation operator (same as
CONCAT()) rather than as a
synonym for OR.
Treat REAL as a synonym for
FLOAT. By default, MySQL treats
REAL as a synonym for
DOUBLE.
Enable strict mode for all storage engines. Invalid data values are rejected. Additional details follow.
Enable strict mode for transactional storage engines, and when possible for nontransactional storage engines. Additional details follow.
Strict mode controls how MySQL handles invalid or missing values
in data-change statements such as
INSERT or
UPDATE. A value can be invalid for
several reasons. For example, it might have the wrong data type
for the column, or it might be out of range. A value is missing
when a new row to be inserted does not contain a value for a
non-NULL column that has no explicit
DEFAULT clause in its definition. (For a
NULL column, NULL is
inserted if the value is missing.)
If strict mode is not in effect, MySQL inserts adjusted values for
invalid or missing values and produces warnings (see
Section 13.7.5.42, “SHOW WARNINGS Syntax”). In strict mode, you can produce
this behavior by using
INSERT IGNORE or
UPDATE IGNORE.
For statements such as SELECT that
do not change data, invalid values generate a warning in strict
mode, not an error.
Strict mode does not affect whether foreign key constraints are
checked. foreign_key_checks can
be used for that. (See Section 5.1.4, “Server System Variables”.)
Strict SQL mode is in effect if either
STRICT_ALL_TABLES or
STRICT_TRANS_TABLES is enabled,
although the effects of these modes differ somewhat:
For transactional tables, an error occurs for invalid or
missing values in a data-change statement when either
STRICT_ALL_TABLES or
STRICT_TRANS_TABLES is
enabled. The statement is aborted and rolled back.
For nontransactional tables, the behavior is the same for either mode if the bad value occurs in the first row to be inserted or updated: The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict mode is enabled:
For STRICT_ALL_TABLES,
MySQL returns an error and ignores the rest of the rows.
However, because the earlier rows have been inserted or
updated, the result is a partial update. To avoid this,
use single-row statements, which can be aborted without
changing the table.
For STRICT_TRANS_TABLES,
MySQL converts an invalid value to the closest valid value
for the column and inserts the adjusted value. If a value
is missing, MySQL inserts the implicit default value for
the column data type. In either case, MySQL generates a
warning rather than an error and continues processing the
statement. Implicit defaults are described in
Section 11.6, “Data Type Default Values”.
Strict mode also affects handling of division by zero, zero dates,
and zeros in dates, in conjunction with the
ERROR_FOR_DIVISION_BY_ZERO,
NO_ZERO_DATE, and
NO_ZERO_IN_DATE modes. For
details, see the descriptions of those modes.
The following special modes are provided as shorthand for combinations of mode values from the preceding list.
Equivalent to REAL_AS_FLOAT,
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE.
As of MySQL 5.1.18, ANSI
mode also causes the server to return an error for queries
where a set function S with an
outer reference
S(outer_ref)
SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2 WHERE ...);
Here, MAX(t1.b) cannot
aggregated in the outer query because it appears in the
WHERE clause of that query. Standard SQL
requires an error in this situation. If
ANSI mode is not enabled,
the server treats
S(outer_ref)S(const)
Equivalent to
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE,
NO_KEY_OPTIONS,
NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS.
Equivalent to
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE,
NO_KEY_OPTIONS,
NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS,
NO_AUTO_CREATE_USER.
Equivalent to
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE,
NO_KEY_OPTIONS,
NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS.
Equivalent to
NO_FIELD_OPTIONS,
HIGH_NOT_PRECEDENCE.
Equivalent to
NO_FIELD_OPTIONS,
HIGH_NOT_PRECEDENCE.
Equivalent to
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE,
NO_KEY_OPTIONS,
NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS,
NO_AUTO_CREATE_USER.
Equivalent to
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE,
NO_KEY_OPTIONS,
NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS.
Equivalent to
STRICT_TRANS_TABLES,
STRICT_ALL_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER.
MySQL 5.1 and up supports a plugin API that enables creation of server components. Plugins can be loaded at server startup, or loaded and unloaded at runtime without restarting the server. The components supported by this interface include, but are not limited to, storage engines, full-text parser plugins, partitioning support, and server extensions.
Server plugins must be loaded in to the server before they can be used. MySQL enables you to load a plugin at server startup or at runtime. It is also possible to control the activation of loaded plugins at startup, and to unload them at runtime.
Server plugins must be known to the server before they can be
used. A plugin can be made known several ways, as described
here. In the following descriptions,
plugin_name stands for a plugin name
such as innodb or csv.
Built-in plugins:
A plugin that is built in to the server is known by the server
automatically. Normally, the server enables the plugin at
startup, although this can be changed with the
--
option.
plugin_name
Plugins registered in the
mysql.plugin table:
The mysql.plugin table serves as a registry
of plugins. The server normally enables each plugin listed in
the table at startup, although whether a given plugin is enabled
can be changed with the
--
option. If the server is started with the
plugin_name--skip-grant-tables option, it
does not consult this table and does not load the plugins listed
there.
Plugins named with the
--plugin-load option:
A plugin that is located in a plugin library file can be loaded
at server startup with the
--plugin-load option. Normally,
the server enables the plugin at startup, although this can be
changed with the
--
option.
plugin_name
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. If a
plugin library is named without any preceding plugin name, the
server loads all plugins in the library. Each library file must
be located in the directory named by the
plugin_dir system variable.
This option does not register any plugin in the
mysql.plugin table. For subsequent restarts,
the server loads the plugin again only if
--plugin-load is given again.
That is, this option effects a one-time installation that
persists only for one server invocation.
--plugin-load enables plugins to
be loaded even when
--skip-grant-tables is given
(which causes the server to ignore the
mysql.plugin table).
--plugin-load also enables
plugins to be loaded at startup under configurations when
plugins cannot be loaded at runtime.
Plugins installed with the
INSTALL PLUGIN
statement:
A plugin that is located in a plugin library file can be loaded
at runtime with the INSTALL
PLUGIN statement. The statement also registers the
plugin in the mysql.plugin table to cause the
server to load it on subsequent restarts. For this reason,
INSTALL PLUGIN requires the
INSERT privilege for the
mysql.plugin table.
If a plugin is named both using a
--plugin-load option and in the
mysql.plugin table, the server starts but
writes these messages to the error log:
100310 19:15:44 [ERROR] Function 'plugin_name' already exists 100310 19:15:44 [Warning] Couldn't load plugin named 'plugin_name' with soname 'plugin_object_file'.
Example: The --plugin-load option
installs a plugin at server startup. To install a plugin named
myplugin in a plugin library file named
somepluglib.so, use these lines in a
my.cnf file:
[mysqld] plugin-load=myplugin=somepluglib.so
In this case, the plugin is not registered in
mysql.plugin. Restarting the server without
the --plugin-load option causes
the plugin not to be loaded at startup.
Alternatively, the INSTALL PLUGIN
statement causes the server to load the plugin code from the
library file at runtime:
mysql> INSTALL PLUGIN myplugin SONAME 'somepluglib.so';
INSTALL PLUGIN also causes
“permanent” plugin registration: The server lists
the plugin in the mysql.plugin table to
ensure that it is loaded on subsequent server restarts.
Many plugins can be loaded either at server startup or at
runtime. However, if a plugin is designed such that it must be
loaded and initialized during server startup, use
--plugin-load rather than
INSTALL PLUGIN.
While a plugin is loaded, information about it is available at
runtime from several sources, such as the
INFORMATION_SCHEMA.PLUGINS table
and the SHOW PLUGINS statement.
For more information, see
Section 5.1.8.2, “Obtaining Server Plugin Information”.
If the server knows about a plugin when it starts (for example,
because the plugin is named using a
--plugin-load option or
registered in the mysql.plugin table), the
server loads and enables the plugin by default. It is possible
to control activation for such a plugin using a
--
startup option named after the plugin. In the following
descriptions, plugin_name[=value]plugin_name stands for
a plugin name such as innodb or
csv. As with other options, dashes and
underscores are interchangeable in option names. For example,
--my_plugin=ON and
--my-plugin=ON are equivalent.
As of MySQL 5.1.36, these options control plugin loading:
--
plugin_name=OFF
Tells the server to disable the plugin.
--
plugin_name[=ON]
Tells the server to enable the plugin. (Specifying the
option as
--
without a value has the same effect.) If the plugin fails to
initialize, the server runs with the plugin disabled.
plugin_name
--
plugin_name=FORCE
Tells the server to enable the plugin, but if plugin initialization fails, the server does not start. In other words, this option forces the server to run with the plugin enabled or not at all.
The values OFF, ON, and
FORCE are not case sensitive.
Suppose that CSV,
BLACKHOLE, and ARCHIVE are
built-in pluggable storage engines and that you want the server
to load them at startup, subject to these conditions: The server
is permitted to run if CSV initialization
fails, but must require that BLACKHOLE
initialization succeeds, and ARCHIVE should
be disabled. To accomplish that, use these lines in an option
file:
[mysqld] csv=ON blackhole=FORCE archive=OFF
The
--enable-
option format is supported as a synonym for
plugin_name--.
The
plugin_name=ON--disable-
and
plugin_name--skip-
option formats are supported as synonyms for
plugin_name--.
plugin_name=OFF
Before MySQL 5.1.36, plugin options are boolean options (see Section 4.2.5, “Program Option Modifiers”). That is, any of these options enable the plugin:
--plugin_name--plugin_name=1 --enable-plugin_name
And these options disable the plugin:
--plugin_name=0 --disable-plugin_name--skip-plugin_name
If you upgrade to MySQL 5.1.36 or later from an older version
and previously used options of the form
-- or
plugin_name=0--, the
equivalent options are now
plugin_name=1--
and
plugin_name=OFF--,
respectively. You also have the choice of requiring plugins to
start successfully by using
plugin_name=ON--.
plugin_name=FORCE
If a plugin is disabled, either explicitly with
OFF or implicitly because it was enabled with
ON but failed to initialize, aspects of
server operation that require the plugin will change. For
example, if the plugin implements a storage engine, existing
tables for the storage engine become inaccessible, and attempts
to create new tables for the storage engine result in tables
that use the default storage engine unless the
NO_ENGINE_SUBSTITUTION SQL
mode has been enabled to cause an error to occur instead.
Disabling a plugin may require adjustment to other options. For
example, if you start the server using
--skip-innodb
to disable InnoDB, other
innodb_
options likely will need to be omitted from the startup command.
In addition, if the server is configured to use
xxxInnoDB as the default storage
engine, it will not start unless you specify another available
storage engine with
--default-storage-engine.
A plugin known to the server can be uninstalled to disable it at
runtime with the UNINSTALL PLUGIN
statement. The statement unloads the plugin and removes it from
the mysql.plugin table if it is registered
there. For this reason, UNINSTALL
PLUGIN statement requires the
DELETE privilege for the
mysql.plugin table. With the plugin no longer
registered in the table, the server will not load the plugin
automatically for subsequent restarts.
UNINSTALL PLUGIN can unload
plugins regardless of whether they were loaded with
INSTALL PLUGIN or
--plugin-load.
UNINSTALL PLUGIN cannot unload
plugins that are built in to the server. These can be identified
as those that have a library name of NULL in
the output from
INFORMATION_SCHEMA.PLUGINS or
SHOW PLUGINS.
There are several ways to determine which plugins are installed in the server:
The INFORMATION_SCHEMA.PLUGINS
table contains a row for each loaded plugin. Any that have a
PLUGIN_LIBRARY value of
NULL are built in and cannot be unloaded.
mysql> SELECT * FROM information_schema.PLUGINS\G
*************************** 1. row ***************************
PLUGIN_NAME: binlog
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 50158.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: MySQL AB
PLUGIN_DESCRIPTION: This is a pseudo storage engine to represent the binlog in a transaction
PLUGIN_LICENSE: GPL
...
*************************** 10. row ***************************
PLUGIN_NAME: InnoDB
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 50158.0
PLUGIN_LIBRARY: ha_innodb_plugin.so
PLUGIN_LIBRARY_VERSION: 1.0
PLUGIN_AUTHOR: Innobase Oy
PLUGIN_DESCRIPTION: Supports transactions, row-level locking,
and foreign keys
PLUGIN_LICENSE: GPL
...
The SHOW PLUGINS statement
displays a row for each loaded plugin. Any that have a
Library value of NULL
are built in and cannot be unloaded.
mysql> SHOW PLUGINS\G
*************************** 1. row ***************************
Name: binlog
Status: ACTIVE
Type: STORAGE ENGINE
Library: NULL
License: GPL
...
*************************** 10. row ***************************
Name: InnoDB
Status: ACTIVE
Type: STORAGE ENGINE
Library: ha_innodb_plugin.so
License: GPL
...
The mysql.plugin table shows which
plugins have been registered with
INSTALL PLUGIN. The table
contains only plugin names and library file names, so it
does not provide as much information as the
PLUGINS table or the
SHOW PLUGINS statement.
MySQL Server supports a HELP
statement that returns online information from the MySQL Reference
manual (see Section 13.8.3, “HELP Syntax”). The proper operation of this
statement requires that the help tables in the
mysql database be initialized with help topic
information, which is done by processing the contents of the
fill_help_tables.sql script.
If you install MySQL using a binary or source distribution on Unix, help table content initialization occurs when you run mysql_install_db. For an RPM distribution on Linux or binary distribution on Windows, content initialization occurs as part of the MySQL installation process.
If you upgrade MySQL using a binary distribution, help table
content is not upgraded automatically, but you can upgrade it
manually. Locate the fill_help_tables.sql
file in the share or
share/mysql directory. Change location into
that directory and process the file with the
mysql client as follows:
shell> mysql -u root mysql < fill_help_tables.sql
You can also obtain the latest
fill_help_tables.sql at any time to upgrade
your help tables. Download the proper file for your version of
MySQL from http://dev.mysql.com/doc/index-other.html. After
downloading and uncompressing the file, process it with
mysql as described previously.
If you are working with Bazaar and a MySQL development source
tree, you must use a downloaded copy of the
fill_help_tables.sql file because the source
tree contains only a “stub” version.
For a server that participates in replication, the help table content upgrade process involves multiple servers. For details, see Section 16.4.1.28, “Replication of Server-Side Help Tables”.
On Unix, signals can be sent to processes. mysqld responds to signals sent to it as follows:
SIGTERM causes the server to shut down.
SIGHUP causes the server to reload the
grant tables and to flush tables, logs, the thread cache, and
the host cache. These actions are like various forms of the
FLUSH statement. The server
also writes a status report to the error log that has this
format:
Status information: Current dir: /var/mysql/data/ Running threads: 0 Stack size: 196608 Current locks: Key caches: default Buffer_size: 8388600 Block_size: 1024 Division_limit: 100 Age_limit: 300 blocks used: 0 not flushed: 0 w_requests: 0 writes: 0 r_requests: 0 reads: 0 handler status: read_key: 0 read_next: 0 read_rnd 0 read_first: 1 write: 0 delete 0 update: 0 Table status: Opened tables: 5 Open tables: 0 Open files: 7 Open streams: 0 Alarm status: Active alarms: 1 Max used alarms: 2 Next alarm time: 67
On some Mac OS X 10.3 versions, mysqld ignores
SIGHUP and SIGQUIT.
The server shutdown process takes place as follows:
The shutdown process is initiated.
This can occur initiated several ways. For example, a user
with the SHUTDOWN privilege can
execute a mysqladmin shutdown command.
mysqladmin can be used on any platform
supported by MySQL. Other operating system-specific shutdown
initiation methods are possible as well: The server shuts down
on Unix when it receives a SIGTERM signal.
A server running as a service on Windows shuts down when the
services manager tells it to.
The server creates a shutdown thread if necessary.
Depending on how shutdown was initiated, the server might
create a thread to handle the shutdown process. If shutdown
was requested by a client, a shutdown thread is created. If
shutdown is the result of receiving a
SIGTERM signal, the signal thread might
handle shutdown itself, or it might create a separate thread
to do so. If the server tries to create a shutdown thread and
cannot (for example, if memory is exhausted), it issues a
diagnostic message that appears in the error log:
Error: Can't create thread to kill server
The server stops accepting new connections.
To prevent new activity from being initiated during shutdown, the server stops accepting new client connections by closing the handlers for the network interfaces to which it normally listens for connections: the TCP/IP port, the Unix socket file, the Windows named pipe, and shared memory on Windows.
The server terminates current activity.
For each thread associated with a client connection, the
server breaks the connection to the client and marks the
thread as killed. Threads die when they notice that they are
so marked. Threads for idle connections die quickly. Threads
that currently are processing statements check their state
periodically and take longer to die. For additional
information about thread termination, see
Section 13.7.6.4, “KILL Syntax”, in particular for the instructions
about killed REPAIR TABLE or
OPTIMIZE TABLE operations on
MyISAM tables.
For threads that have an open transaction, the transaction is
rolled back. Note that if a thread is updating a
nontransactional table, an operation such as a multiple-row
UPDATE or
INSERT may leave the table
partially updated because the operation can terminate before
completion.
If the server is a master replication server, it treats threads associated with currently connected slaves like other client threads. That is, each one is marked as killed and exits when it next checks its state.
If the server is a slave replication server, it stops the I/O
and SQL threads, if they are active, before marking client
threads as killed. The SQL thread is permitted to finish its
current statement (to avoid causing replication problems), and
then stops. In MySQL 5.1.34 and earlier, if the SQL thread was
in the middle of a transaction at this point, the transaction
was rolled back; in MySQL 5.1.35 and later, the server waits
until the current replication event group (if any) has
finished executing, or until the user issues a
KILL QUERY or
KILL
CONNECTION statement. See also
Section 13.4.2.8, “STOP SLAVE Syntax”.
If the slave is updating a nontransactional table when it is forcibly killed, the slave's data may become inconsistent with the master.
The server shuts down or closes storage engines.
At this stage, the server flushes the table cache and closes all open tables.
Each storage engine performs any actions necessary for tables
that it manages. For example, MyISAM
flushes any pending index writes for a table.
InnoDB flushes its buffer pool to disk
(unless innodb_fast_shutdown
is 2), writes the current LSN to the tablespace, and
terminates its own internal threads.
The server exits.
MySQL Server has several logs that can help you find out what activity is taking place.
| Log Type | Information Written to Log |
|---|---|
| Error log | Problems encountered starting, running, or stopping mysqld |
| General query log | Established client connections and statements received from clients |
| Binary log | Statements that change data (also used for replication) |
| Relay log | Data changes received from a replication master server |
| Slow query log | Queries that took more than
long_query_time seconds to
execute |
| DDL log (metadata log) | Metadata operations performed by DDL statements |
By default, no logs are enabled, except the error log on Windows. (The DDL log is always created when required, and has no user-configurable options; see Section 5.2.6, “The DDL Log”.) The following log-specific sections provide information about the server options that enable logging.
By default, the server writes files for all enabled logs in the data
directory. You can force the server to close and reopen the log
files (or in some cases switch to a new log file) by flushing the
logs. Log flushing occurs when you issue a
FLUSH LOGS
statement; execute mysqladmin with a
flush-logs or refresh
argument; or execute mysqldump with a
--flush-logs or
--master-data option. See
Section 13.7.6.3, “FLUSH Syntax”, Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”, and
Section 4.5.4, “mysqldump — A Database Backup Program”. In addition, the binary log is flushed
when its size reaches the value of the
max_binlog_size system variable.
You can control the general query and slow query logs during runtime. You can enable or disable logging, or change the log file name. You can tell the server to write general query and slow query entries to log tables, log files, or both. For details, see Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”, Section 5.2.3, “The General Query Log”, and Section 5.2.5, “The Slow Query Log”.
The relay log is used only on slave replication servers, to hold data changes from the master server that must also be made on the slave. For discussion of relay log contents and configuration, see Section 16.2.2.1, “The Slave Relay Log”.
For information about log maintenance operations such as expiration of old log files, see Section 5.2.7, “Server Log Maintenance”.
For information about keeping logs secure, see Section 6.1.2.3, “Passwords and Logging”.
As of MySQL 5.1.6, MySQL Server provides flexible control over the
destination of output to the general query log and the slow query
log, if those logs are enabled. Possible destinations for log
entries are log files or the general_log and
slow_log tables in the mysql
database. Either or both destinations can be selected. (Before
MySQL 5.1.6, the server uses only log files as the destination for
general query log and slow query log entries.)
For new installations of MySQL 5.1.6 or higher, the log tables are created during the installation procedure along with the other system tables. If you upgrade MySQL from a release older than 5.1.6 to MySQL 5.1.6 or higher, you must upgrade the system tables after upgrading to make sure that the log tables exist. See Section 4.4.8, “mysql_upgrade — Check and Upgrade MySQL Tables”.
Currently, logging to tables incurs significantly more server overhead than logging to files. If you enable the general log or slow query log and require highest performance, you should use file logging, not table logging.
Log control at server startup.
The --log-output option specifies
the destination for log output, if logging is enabled. This option
does not in itself enable the logs. Its syntax is
--log-output[=:
value,...]
If --log-output is given with a
value, the value should 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).
NONE, if present, takes precedence over any
other specifiers.
If --log-output is omitted or
given without a value, the default is FILE.
(For MySQL 5.1.6 through 5.1.20, the default logging
destination is TABLE.)
The general_log system variable
controls logging to the general query log for the selected log
destinations. If specified at server startup,
general_log takes an optional
argument of 1 or 0 to enable or disable the log. To specify a file
name other than the default for file logging, set the
general_log_file variable.
Similarly, the slow_query_log
variable controls logging to the slow query log for the selected
destinations and setting
slow_query_log_file specifies a
file name for file logging. If either log is enabled, the server
opens the corresponding log file and writes startup messages to
it. However, further logging of queries to the file does not occur
unless the FILE log destination is selected.
Prior to MySQL 5.1.29, the --log
and --log-slow-queries options
enable the general query log and slow query log. Either option may
be given with a file name argument to specify a log file name to
override the default.
Examples:
To write general query log entries to the log table and the
log file, use
--log-output=TABLE,FILE to
select both log destinations and
--general_log to enable the
general query log.
To write general and slow query log entries only to the log
tables, use --log-output=TABLE
to select tables as the log destination and
--general_log and
--slow_query_log to enable both
logs.
To write slow query log entries only to the log file, use
--log-output=FILE to select
files as the log destination and
--slow_query_log to enable the
slow query log. (In this case, because the default log
destination is FILE, you could omit the
--log-output option.)
Log control at runtime. The system variables associated with log tables and files enable runtime control over logging:
The global log_output system
variable indicates the current logging destination. It can be
modified at runtime to change the destination.
The global general_log and
slow_query_log variables
indicate whether the general query log and slow query log are
enabled (ON) or disabled
(OFF). You can set these variables at
runtime to control whether the logs are enabled.
The global general_log_file
and slow_query_log_file
variables indicate the names of the general query log and slow
query log files. As of MySQL 5.1.29, you can set these
variables at server startup or at runtime to change the names
of the log files. Before MySQL 5.1.29, you can set these
variables only at runtime, but the
--log and
--log-slow-queries options can be given
with a file name argument at startup to change the log file
names from their default values.
To disable or enable general query logging for the current
connection, set the session
sql_log_off variable to
ON or OFF.
The use of tables for log output offers the following benefits:
Log entries have a standard format. To display the current structure of the log tables, use these statements:
SHOW CREATE TABLE mysql.general_log; SHOW CREATE TABLE mysql.slow_log;
Log contents are accessible through SQL statements. This enables the use of queries that select only those log entries that satisfy specific criteria. For example, to select log contents associated with a particular client (which can be useful for identifying problematic queries from that client), it is easier to do this using a log table than a log file.
Logs are accessible remotely through any client that can connect to the server and issue queries (if the client has the appropriate log table privileges). It is not necessary to log in to the server host and directly access the file system.
The log table implementation has the following characteristics:
In general, the primary purpose of log tables is to provide an interface for users to observe the runtime execution of the server, not to interfere with its runtime execution.
CREATE TABLE,
ALTER TABLE, and
DROP TABLE are valid operations
on a log table. For ALTER TABLE
and DROP TABLE, the log table
cannot be in use and must be disabled, as described later.
By default, the log tables use the CSV
storage engine that writes data in comma-separated values
format. For users who have access to the
.CSV files that contain log table data,
the files are easy to import into other programs such as
spreadsheets that can process CSV input.
Beginning with MySQL 5.1.12, the log tables can be altered to
use the MyISAM storage engine. You cannot
use ALTER TABLE to alter a log
table that is in use. The log must be disabled first. No
engines other than CSV or
MyISAM are legal for the log tables.
To disable logging so that you can alter (or drop) a log
table, you can use the following strategy. The example uses
the general query log; the procedure for the slow query log is
similar but uses the slow_log table and
slow_query_log system
variable.
SET @old_log_state = @@global.general_log; SET GLOBAL general_log = 'OFF'; ALTER TABLE mysql.general_log ENGINE = MyISAM; SET GLOBAL general_log = @old_log_state;
TRUNCATE TABLE is a valid
operation on a log table. It can be used to expire log
entries.
As of MySQL 5.1.13, RENAME
TABLE is a valid operation on a log table. You can
atomically rename a log table (to perform log rotation, for
example) using the following strategy:
USE mysql; DROP TABLE IF EXISTS general_log2; CREATE TABLE general_log2 LIKE general_log; RENAME TABLE general_log TO general_log_backup, general_log2 TO general_log;
LOCK TABLES cannot be used on a
log table.
INSERT,
DELETE, and
UPDATE cannot be used on a log
table. These operations are permitted only internally to the
server itself.
FLUSH TABLES WITH READ
LOCK and the state of the global
read_only system variable
have no effect on log tables. The server can always write to
the log tables.
Entries written to the log tables are not written to the binary log and thus are not replicated to slave servers.
To flush the log tables or log files, use
FLUSH TABLES
or FLUSH
LOGS, respectively. (From MySQL 5.1.12 to 5.1.20,
FLUSH TABLES
ignores log tables and
FLUSH LOGS
flushes both the log tables and files.)
Partitioning of log tables is not permitted beginning with MySQL 5.1.20, and not recommended before that.
Before MySQL 5.1.64, mysqldump does not
dump the general_log or
slow_query_log tables for dumps of the
mysql database. As of 5.1.64, the dump
includes statements to recreate those tables so that they are
not missing after reloading the dump file. Log table contents
are not dumped.
The error log contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.
On some operating systems, the error log contains a stack trace if mysqld dies. The trace can be used to determine where mysqld died. See Section 22.4, “Debugging and Porting MySQL”.
If mysqld_safe is used to start
mysqld and mysqld dies
unexpectedly, mysqld_safe notices this,
restarts mysqld, and writes a
restarted mysqld message to the error log.
In the following discussion, “console” means
stderr, the standard error output; this is your
terminal or console window unless the standard error output has
been redirected. (For example, if invoked with the
--syslog option,
mysqld_safe arranges for the server's
stderr to be sent to syslog,
as described later.)
On Windows, the --log-error and
--console options both affect error
logging:
Without --log-error,
mysqld writes error messages to
host_name.err
With
--log-error[=,
mysqld writes error messages to an error
log file. The server uses the named file if present, creating
it in the data directory unless an absolute path name is given
to specify a different directory. If no file is named, the
default name is
file_name]host_name.err
With --console,
mysqld writes error messages to the
console, unless --log-error is
also given. If both options are present, the last one takes
precedence.
In addition, on Windows, the server writes events and error
messages to the Windows Event Log within the Application log.
Entries marked as Warning and
Note are written to the Event Log, but not
informational messages such as information statements from
individual storage engines. These log entries have a source of
MySQL. You cannot disable writing information
to the Windows Event Log.
On Unix and Unix-like systems, mysqld writes error log messages as follows:
Without --log-error,
mysqld writes error messages to the
console.
With
--log-error[=,
mysqld writes error messages to an error
log file. The server uses the named file if present, creating
it in the data directory unless an absolute path name is given
to specify a different directory. If no file is named, the
default name is
file_name]host_name.err
At runtime, log_error system
variable indicates the error log file name if error output is
written to a file.
If you specify --log-error in an
option file in a [mysqld],
[server], or [mysqld_safe]
section, mysqld_safe will find and use the
option.
If you flush the logs using
FLUSH LOGS or
mysqladmin flush-logs and
mysqld is writing the error log to a file (for
example, if it was started with the
--log-error option), the effect is
version dependent:
As of MySQL 5.1.51, the server closes and reopens the log file. To rename the file, do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, to rename the file and create a new one, use the following commands:
shell>mvshell>host_name.errhost_name.err-oldmysqladmin flush-logsshell>mvhost_name.err-oldbackup-directory
On Windows, use rename rather than mv.
Prior to MySQL 5.1.51, the server renames the current log file
with the suffix -old, then creates a new
empty log file. Be aware that a second log-flushing operation
thus causes the original error log file to be lost unless you
save it under a different name. On Windows, you cannot rename
the error log while the server has it open before MySQL
5.1.51. To avoid a restart, flush the logs first to cause the
server to rename the original file and create a new one, then
save the renamed file. That also works on Unix, or you can use
the commands shown earlier.
No error log renaming occurs when the logs are flushed in any case if the server is not writing to a named file.
If you use mysqld_safe to start
mysqld, mysqld_safe arranges
for mysqld to write error messages to a log
file or (as of MySQL 5.1.20) to syslog:
Before 5.1.20, mysqld_safe behavior is to
log to a file, using the default error log file if the
--log-error option is not given
to mysqld_safe. Otherwise,
mysqld_safe uses the file name specified
using
--log-error=.
file_name
From 5.1.20 on, mysqld_safe has two
additional error-logging options,
--syslog and
--skip-syslog.
In 5.1.21 and up, the default with no logging options is
--skip-syslog,
which is compatible with the default behavior of writing an
error log file for releases prior to 5.1.20. To explicitly
specify use of an error log file, specify
--log-error=
to mysqld_safe, and
mysqld_safe will arrange for
mysqld to write messages to a log file. To
use file_namesyslog instead, specify the
--syslog option.
In 5.1.20 only, the
following conditions apply: 1) The default is to
use syslog, which is not compatible with
releases prior to 5.1.20. 2) Logging to
syslog may fail to operate correctly in
some cases; if so, use
--skip-syslog
or --log-error.
For logging to syslog, messages from
mysqld_safe and mysqld
are written with a tag of mysqld_safe and
mysqld, respectively. As of MySQL 5.1.21,
to specify a suffix for the tag, use
--syslog-tag=,
which modifies the tags to be
tagmysqld_safe-
and tagmysqld-.
tag
The --log-warnings option or
log_warnings system variable can
be used to control warning logging to the error log. The default
value is enabled (1). Warning logging can be disabled using a
value of 0. 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”.
The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.
mysqld writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed. This logging order is in contrast with that of the binary log, for which statements are written after they are executed but before any locks are released. In addition, the query log may contain statements that only select data while such statements are never written to the binary log.
When using statement-based logging all statements are written to
the query log, but when using row-based logging, updates are sent
as row changes rather than SQL statements, and thus these
statements are never written to the query log when
binlog_format is
ROW. A given update also might not be written
to the query log when this variable is set to
MIXED, depending on the statement used. See
Section 16.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based
Replication”, for more information.
Control the general query log at server startup as follows:
Before 5.1.6, the general query log destination is always a
file. To enable the log, start mysqld with
the
--log[=
or file_name]-l [
option.
file_name]
As of MySQL 5.1.6, the destination can be a file or a table,
or both. Start mysqld with the
--log[=
or file_name]-l [
option to enable the general query log, and optionally use
file_name]--log-output to specify the log
destination (as described in
Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”).
As of MySQL 5.1.12, as an alternative to
--log or -l,
use --general_log[={0|1}] to
specify the initial general query log state. In this case, the
default general query log file name is used. With no argument
or an argument of 1,
--general_log enables the log.
With an argument of 0, this option disables the log.
As of MySQL 5.1.29, use
--general_log[={0|1}] to enable
or disable the general query log, and optionally
--general_log_file=
to specify a log file name. The
file_name--log and -l
options are deprecated.
If the general query log file is enabled but no name is specified,
the default name is
host_name.log
To disable or enable the general query log or change the log file
name at runtime, use the global
general_log and
general_log_file system
variables. Set general_log to 0
(or OFF) to disable the log or to 1 (or
ON) to enable it. Set
general_log_file to specify the
name of the log file. If a log file already is open, it is closed
and the new file is opened.
When the general query log is enabled, the server writes output to
any destinations specified by the
--log-output option or
log_output system variable. If
you enable the log, the server opens the log file and writes
startup messages to it. However, further logging of queries to the
file does not occur unless the FILE log
destination is selected. If the destination is
NONE, the server writes no queries even if the
general log is enabled. Setting the log file name has no effect on
logging if the log destination value does not contain
FILE.
Server restarts and log flushing do not cause a new general query log file to be generated (although flushing closes and reopens it). On Unix, you can rename the file and create a new one by using the following commands:
shell>mvshell>host_name.loghost_name-old.logmysqladmin flush-logsshell>mvhost_name-old.logbackup-directory
On Windows, you cannot rename the general query log file while the server has it open before MySQL 5.1.3. You must stop the server, rename the file, and then restart the server to create a new log file.
As of MySQL 5.1.12, you can rename the general query log file at runtime by disabling the log:
SET GLOBAL general_log = 'OFF';
With the log disabled, rename the log file externally; for example, from the command line. Then enable the log again:
SET GLOBAL general_log = 'ON';
This method works on any platform and does not require a server restart.
The session sql_log_off variable
can be set to ON or OFF to
disable or enable general query logging for the current
connection.
The general query log should be protected because logged statements might contain passwords. See Section 6.1.2.3, “Passwords and Logging”.
The binary log contains “events” that describe
database changes such as table creation operations or changes to
table data. It also contains events for statements that
potentially could have made changes (for example, a
DELETE which matched no rows),
unless row-based logging is used. The binary log also contains
information about how long each statement took that updated data.
The binary log has two important purposes:
For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 16.2, “Replication Implementation”.
Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
The binary log is not used for statements such as
SELECT or
SHOW that do not modify data. To
log all statements (for example, to identify a problem query), use
the general query log. See Section 5.2.3, “The General Query Log”.
Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.
The binary log should be protected because logged statements might contain passwords. See Section 6.1.2.3, “Passwords and Logging”.
The following discussion describes some of the server options and variables that affect the operation of binary logging. For a complete list, see Section 16.1.3.4, “Binary Log Options and Variables”.
To enable the binary log, start the server with the
--log-bin[=
option. If no base_name]base_name value is given,
the default name is the value of the pid-file
option (which by default is the name of host machine) followed by
-bin. If the basename is given, the server
writes the file in the data directory unless the basename is given
with a leading absolute path name to specify a different
directory. It is recommended that you specify a basename
explicitly rather than using the default of the host name; see
Section B.5.8, “Known Issues in MySQL”, for the reason.
From MySQL 5.1.18 through 5.1.22, “mysql” was used
when no base_name was specified. Also
in these versions, a path given as part of the
--log-bin options was treated as
absolute rather than relative. The previous behaviors were
restored in MySQL 5.1.23. (See Bug #28603 and Bug #28597.)
If you supply an extension in the log name (for example,
--log-bin=),
the extension is silently removed and ignored.
base_name.extension
mysqld appends a numeric extension to the
binary log basename to generate binary log file names. The number
increases each time the server creates a new log file, thus
creating an ordered series of files. The server creates a new file
in the series each time it starts or flushes the logs. The server
also creates a new binary log file automatically after the current
log's size reaches
max_binlog_size. A binary log
file may become larger than
max_binlog_size if you are using
large transactions because a transaction is written to the file in
one piece, never split between files.
To keep track of which binary log files have been used,
mysqld also creates a binary log index file
that contains the names of all used binary log files. By default,
this has the same basename as the binary log file, with the
extension '.index'. You can change the name of
the binary log index file with the
--log-bin-index[=
option. You should not manually edit this file while
mysqld is running; doing so would confuse
mysqld.
file_name]
The term “binary log file” generally denotes an individual numbered file containing database events. The term “binary log” collectively denotes the set of numbered binary log files plus the index file.
A client that has the SUPER
privilege can disable binary logging of its own statements by
using a SET sql_log_bin=0 statement. See
Section 5.1.4, “Server System Variables”.
The format of the events recorded in the binary log is dependent on the binary logging format. Three format types are supported, row-based logging, statement-based logging and mixed-base logging. The binary logging format used depends on the MySQL version. For general descriptions of the logging formats, see Section 5.2.4.1, “Binary Logging Formats”. For detailed information about the format of the binary log, see MySQL Internals: The Binary Log.
The server evaluates the
--binlog-do-db and
--binlog-ignore-db options in the
same way as it does the
--replicate-do-db and
--replicate-ignore-db options. For
information about how this is done, see
Section 16.2.3.1, “Evaluation of Database-Level Replication and Binary Logging Options”.
If you are replicating from a MySQL Cluster to a standalone MySQL
Server, you should be aware that the
NDB storage engine uses default
values for some binary logging options (including options specific
to NDB such as
--ndb-log-update-as-write) that
differ from those used by other storage engines. If not corrected
for, these differences can lead to divergence of the master's
and slave's binary logs. For more information, see
Replication from NDB to other storage engines. In
particular, if you are using a nontransactional storage engine
such as MyISAM on the slave, see
Replication from NDB to a nontransactional storage engine.
A replication slave server by default does not write to its own
binary log any data modifications that are received from the
replication master. To log these modifications, start the slave
with the --log-slave-updates option
in addition to the --log-bin option
(see Section 16.1.3.3, “Replication Slave Options and Variables”). This is done
when a slave is also to act as a master to other slaves in chained
replication.
You can delete all binary log files with the
RESET MASTER statement, or a subset
of them with PURGE BINARY LOGS. See
Section 13.7.6.6, “RESET Syntax”, and Section 13.4.1.1, “PURGE BINARY LOGS Syntax”.
If you are using replication, you should not delete old binary log
files on the master until you are sure that no slave still needs
to use them. For example, if your slaves never run more than three
days behind, once a day you can execute mysqladmin
flush-logs on the master and then remove any logs that
are more than three days old. You can remove the files manually,
but it is preferable to use PURGE BINARY
LOGS, which also safely updates the binary log index
file for you (and which can take a date argument). See
Section 13.4.1.1, “PURGE BINARY LOGS Syntax”.
You can display the contents of binary log files with the mysqlbinlog utility. This can be useful when you want to reprocess statements in the log for a recovery operation. For example, you can update a MySQL server from the binary log as follows:
shell> mysqlbinlog log_file | mysql -h server_name
mysqlbinlog also can be used to display replication slave relay log file contents because they are written using the same format as binary log files. For more information on the mysqlbinlog utility and how to use it, see Section 4.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”. For more information about the binary log and recovery operations, see Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.
Updates to nontransactional tables are stored in the binary log
immediately after execution. In MySQL 5.1.22 and earlier versions
of MySQL 5.1, an
UPDATE statement using a stored
function that modified a nontransactional table was not logged if
it failed, and an
INSERT ... ON
DUPLICATE KEY UPDATE statement that encountered a
duplicate key constraint—but did not actually change any
data—was not logged. Beginning with MySQL 5.1.23, both of
these statements are written to the binary log. (Bug #23333)
Within an uncommitted transaction, all updates
(UPDATE,
DELETE, or
INSERT) that change transactional
tables such as InnoDB tables are cached until a
COMMIT statement is received by the
server. At that point, mysqld writes the entire
transaction to the binary log before the
COMMIT is executed.
Modifications to nontransactional tables cannot be rolled back. If
a transaction that is rolled back includes modifications to
nontransactional tables, the entire transaction is logged with a
ROLLBACK
statement at the end to ensure that the modifications to those
tables are replicated.
When a thread that handles the transaction starts, it allocates a
buffer of binlog_cache_size to
buffer statements. If a statement is bigger than this, the thread
opens a temporary file to store the transaction. The temporary
file is deleted when the thread ends.
The Binlog_cache_use status
variable shows the number of transactions that used this buffer
(and possibly a temporary file) for storing statements. The
Binlog_cache_disk_use status
variable shows how many of those transactions actually had to use
a temporary file. These two variables can be used for tuning
binlog_cache_size to a large
enough value that avoids the use of temporary files.
The max_binlog_cache_size system
variable (default 4GB, which is also the maximum) can be used to
restrict the total size used to cache a multiple-statement
transaction. If a transaction is larger than this many bytes, it
fails and rolls back. The minimum value is 4096.
If you are using the binary log and row based logging, concurrent
inserts are converted to normal inserts for CREATE ...
SELECT or
INSERT ...
SELECT statements. This is done to ensure that you can
re-create an exact copy of your tables by applying the log during
a backup operation. If you are using statement-based logging, the
original statement is written to the log.
The binary log format has some known limitations that can affect recovery from backups. See Section 16.4.1, “Replication Features and Issues”.
Binary logging for stored programs is done as described in Section 19.7, “Binary Logging of Stored Programs”.
Note that the binary log format differs in MySQL 5.1 from previous versions of MySQL, due to enhancements in replication. See Section 16.4.2, “Replication Compatibility Between MySQL Versions”.
Writes to the binary log file and binary log index file are
handled in the same way as writes to MyISAM
tables. See Section B.5.4.3, “How MySQL Handles a Full Disk”.
By default, the binary log is not synchronized to disk at each
write. So if the operating system or machine (not only the MySQL
server) crashes, there is a chance that the last statements of the
binary log are lost. To prevent this, you can make the binary log
be synchronized to disk after every N
writes to the binary log, with the
sync_binlog system variable. See
Section 5.1.4, “Server System Variables”. 1 is the safest value
for sync_binlog, but also the
slowest. Even with sync_binlog
set to 1, there is still the chance of an inconsistency between
the table content and binary log content in case of a crash. For
example, if you are using InnoDB tables and the
MySQL server processes a COMMIT
statement, it writes the whole transaction to the binary log and
then commits this transaction into InnoDB. If
the server crashes between those two operations, the transaction
is rolled back by InnoDB at restart but still
exists in the binary log. To resolve this, you should set
--innodb_support_xa to 1. Although
this option is related to the support of XA transactions in
InnoDB, it also ensures that the binary log and InnoDB data files
are synchronized.
For this option to provide a greater degree of safety, the MySQL
server should also be configured to synchronize the binary log and
the InnoDB logs to disk at every transaction.
The InnoDB logs are synchronized by default,
and sync_binlog=1 can be used to synchronize
the binary log. The effect of this option is that at restart after
a crash, after doing a rollback of transactions, the MySQL server
cuts rolled back InnoDB transactions from the
binary log. This ensures that the binary log reflects the exact
data of InnoDB tables, and so, that the slave
remains in synchrony with the master (not receiving a statement
which has been rolled back).
If the MySQL server discovers at crash recovery that the binary
log is shorter than it should have been, it lacks at least one
successfully committed InnoDB transaction. This
should not happen if sync_binlog=1 and the
disk/file system do an actual sync when they are requested to
(some do not), so the server prints an error message The
binary log . In this case, this binary log is not
correct and replication should be restarted from a fresh snapshot
of the master's data.
file_name is shorter than
its expected size
For MySQL 5.1.20 and later (and MySQL 5.0.46 and later for backward compatibility), the session values of the following system variables are written to the binary log and honored by the replication slave when parsing the binary log:
sql_mode (except that the
NO_DIR_IN_CREATE mode is not
replicated; see
Section 16.4.1.36, “Replication and Variables”)
The server uses several logging formats to record information in the binary log. The exact format employed depends on the version of MySQL being used. There are three logging formats:
Replication capabilities in MySQL originally were based on
propagation of SQL statements from master to slave. This is
called statement-based logging. You can
cause this format to be used by starting the server with
--binlog-format=STATEMENT.
In row-based logging, the master writes
events to the binary log that indicate how individual table
rows are affected. You can cause the server to use row-based
logging by starting it with
--binlog-format=ROW.
A third option is also available: mixed
logging. With mixed logging, statement-based
logging is used by default, but the logging mode switches
automatically to row-based in certain cases as described
below. You can cause MySQL to use mixed logging explicitly
by starting mysqld with the option
--binlog-format=MIXED.
Support for row-based logging was added in MySQL 5.1.5. Mixed
logging is available beginning with MySQL 5.1.8. In MySQL
5.1.12, MIXED become the default logging
mode; in 5.1.29, the default was changed back to
STATEMENT for compatibility with MySQL 5.0.
Starting with MySQL 5.1.20, the logging format can also be set or limited by the storage engine being used. This helps to eliminate issues when replicating certain statements between a master and slave that are using different storage engines.
With statement-based replication, there may be issues with replicating nondeterministic statements. In deciding whether or not a given statement is safe for statement-based replication, MySQL determines whether it can guarantee that the statement can be replicated using statement-based logging. If MySQL cannot make this guarantee, it marks the statement as potentially unreliable and issues the warning, Statement may not be safe to log in statement format.
Prior to MySQL 5.1.36, this warning read, Statement is not safe to log in statement format. (Bug #42415)
You can avoid these issues by using MySQL's row-based replication instead.
You can select the binary logging format explicitly by starting
the MySQL server with
--binlog-format=.
The supported values for typetype are:
STATEMENT causes logging to be statement
based.
ROW causes logging to be row based.
MIXED causes logging to use mixed format.
The default binary logging format depends on the version of MySQL you are using:
For MySQL 5.1.11 and earlier, and for MySQL 5.1.29 and later, statement-based logging is used by default.
For MySQL 5.1.12 through MySQL 5.1.28, mixed logging is used by default.
Exception.
For all MySQL Cluster releases using version 6.1 or later of
the NDBCLUSTER storage engine
(even those releases based on MySQL 5.1.29 and later), the
default binary log format is MIXED. See
Section 17.6, “MySQL Cluster Replication”.
The logging format also can be switched at runtime. To specify
the format globally for all clients, set the global value of the
binlog_format system variable:
mysql>SET GLOBAL binlog_format = 'STATEMENT';mysql>SET GLOBAL binlog_format = 'ROW';mysql>SET GLOBAL binlog_format = 'MIXED';
An individual client can control the logging format for its own
statements by setting the session value of
binlog_format:
mysql>SET SESSION binlog_format = 'STATEMENT';mysql>SET SESSION binlog_format = 'ROW';mysql>SET SESSION binlog_format = 'MIXED';
Each MySQL Server can set its own and only its own binary
logging format (true whether
binlog_format is set with
global or session scope). This means that changing the logging
format on a replication master does not cause a slave to
change its logging format to match. (When using
STATEMENT mode, the
binlog_format system variable
is not replicated; when using MIXED or
ROW logging mode, it is replicated but is
ignored by the slave.) Changing the binary logging format on
the master while replication is ongoing, or without also
changing it on the slave can thus cause unexpected results, or
even cause replication to fail altogether.
To change the global
binlog_format value, you must
have the SUPER privilege. This is
also true for the session value as of MySQL 5.1.29.
In addition to switching the logging format manually, a slave
server may switch the format automatically.
This happens when the server is running in either
STATEMENT or MIXED format
and encounters an event in the binary log that is written in
ROW logging format. In that case, the slave
switches to row-based replication temporarily for that event,
and switches back to the previous format afterward.
There are several reasons why a client might want to set binary logging on a per-session basis:
A session that makes many small changes to the database might want to use row-based logging.
A session that performs updates that match many rows in the
WHERE clause might want to use
statement-based logging because it will be more efficient to
log a few statements than many rows.
Some statements require a lot of execution time on the master, but result in just a few rows being modified. It might therefore be beneficial to replicate them using row-based logging.
There are exceptions when you cannot switch the replication format at runtime:
From within a stored function or a trigger
If the NDBCLUSTER storage
engine is enabled
If the session is currently in row-based replication mode and has open temporary tables
Trying to switch the format in any of these cases results in an error.
If you are using InnoDB tables and
the transaction isolation level is READ
COMMITTED or READ
UNCOMMITTED, only row-based logging can be used. It is
possible to change the logging format to
STATEMENT, but doing so at runtime leads very
rapidly to errors because InnoDB can no
longer perform inserts.
Switching the replication format at runtime is not recommended
when any temporary tables exist, because temporary tables are
logged only when using statement-based replication, whereas with
row-based replication they are not logged. With mixed
replication, temporary tables are usually logged; exceptions
happen with user-defined functions (UDFs) and with the
UUID() function.
With the binary log format set to ROW, many
changes are written to the binary log using the row-based
format. Some changes, however, still use the statement-based
format. Examples include all DDL (data definition language)
statements such as CREATE TABLE,
ALTER TABLE, or
DROP TABLE.
The --binlog-row-event-max-size
option is available for servers that are capable of row-based
replication. Rows are stored into the binary log in chunks
having a size in bytes not exceeding the value of this option.
The value must be a multiple of 256. The default value is 1024.
When using statement-based logging for replication, it is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is nondeterministic; that is, it is left to the will of the query optimizer. In general, this is not a good practice even outside of replication. For a detailed explanation of this issue, see Section B.5.8, “Known Issues in MySQL”.
When running in MIXED logging format, the
server automatically switches from statement-based to row-based
logging under the following conditions:
When a DML statement updates an
NDBCLUSTER table.
When a function contains
UUID().
Prior to MySQL 5.1.40, when two or more tables with
AUTO_INCREMENT columns are updated. As of
5.1.40, when one or more tables with
AUTO_INCREMENT columns are updated and a
trigger or stored function is invoked. Unlike other unsafe
statements, this does not generate a warning if
binlog_format = STATEMENT.
For more information, see Section 16.4.1.1, “Replication and AUTO_INCREMENT”.
When any INSERT DELAYED is
executed.
When the body of a view requires row-based replication, the
statement creating the view also uses it. For example, this
occurs when the statement creating a view uses the
UUID() function.
When a call to a UDF is involved.
If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped.
This is true whether or not any temporary tables are actually logged.
Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe. The server approximates this condition by treating all statements executed during the session as unsafe until the session no longer holds any temporary tables.
Beginning with MySQL 5.1.23:
When FOUND_ROWS() or
ROW_COUNT() is used. (Bug
#12092, Bug #30244)
When USER(),
CURRENT_USER(), or
CURRENT_USER is used.
(Bug #28086)
Beginning with MySQL 5.1.24, when a statement refers to one or more system variables. (Bug #31168)
Exception. The following system variables, when used with session scope (only), do not cause the logging format to switch:
For information about determining system variable scope, see Section 5.1.5, “Using System Variables”.
For information about how replication treats
sql_mode, see
Section 16.4.1.36, “Replication and Variables”.
Beginning with MySQL 5.1.30, when one of the tables involved
is a log table in the mysql database.
Beginning with MySQL 5.1.34, when the
LOAD_FILE() function is used.
(Bug #39701)
Starting with MySQL 5.1.20, a warning is generated if you try
to execute a statement using statement-based logging that
should be written using row-based logging. The warning is
shown both in the client (in the output of
SHOW WARNINGS) and through the
mysqld error log. A warning is added to the
SHOW WARNINGS table each time
such a statement is executed. However, only the first
statement that generated the warning for each client session
is written to the error log to prevent flooding the log.
Starting with MySQL 5.1.20, in addition to the decisions above, individual engines can also determine the logging format used when information in a table is updated. The logging capabilities of an individual engine can be defined as follows:
If an engine supports row-based logging, the engine is said to be row-logging capable.
If an engine supports statement-based logging, the engine is said to be statement-logging capable.
A given storage engine can support either or both logging formats. The following table lists the logging formats supported by each storage engine.
| Storage Engine | Row Logging Supported | Statement Logging Supported |
|---|---|---|
ARCHIVE | Yes | Yes |
BLACKHOLE | Yes | Yes |
CSV | Yes | Yes |
EXAMPLE | Yes | No |
FEDERATED | Yes | Yes |
HEAP | Yes | Yes |
InnoDB | Yes | Yes when the transaction isolation level is
REPEATABLE READ or
SERIALIZABLE; No
otherwise. |
MyISAM | Yes | Yes |
MERGE | Yes | Yes |
NDBCLUSTER | Yes | No |
When determining the logging mode to be used, the capabilities of all the tables affected by the event are combined. The set of affected tables is then marked according to these rules:
A set of tables is defined as row-logging restricted if the tables are row-logging capable but not statement-logging capable.
A set of tables is defined as statement-logging restricted if the tables are statement-logging capable but not row-logging capable.
Once the determination of the possible logging formats required
by the statement is complete it is compared to the current
binlog_format setting. The
following table is used to decide how the information is
recorded in the binary log or, if appropriate, whether an error
is raised. In the table, a safe operation is defined as one that
is deterministic.
Several rules decide whether the statement is deterministic, as shown in the following table, where SLR stands for “statement-logging restricted” and RLR stands for “row-logging restricted”. A statement is statement-logging restricted if one or more of the tables it accesses is not row-logging capable. Similarly, a statement is row-logging restricted if any table accessed by the statement is not statement-logging capable.
| Condition | Action | ||||
|---|---|---|---|---|---|
| Safe/unsafe | binlog_format | SLR | RLR | Error/Warning | Logged as |
| Safe | STATEMENT | Yes | Yes | Error: not loggable | |
| Safe | STATEMENT | Yes | No | STATEMENT | |
| Safe | STATEMENT | No | Yes | Error: not loggable | |
| Safe | STATEMENT | No | No | STATEMENT | |
| Safe | MIXED | Yes | Yes | Error: not loggable | |
| Safe | MIXED | Yes | No | STATEMENT | |
| Safe | MIXED | No | Yes | ROW | |
| Safe | MIXED | No | No | STATEMENT | |
| Safe | ROW | Yes | Yes | Error: not loggable | |
| Safe | ROW | Yes | No | Error: not loggable | |
| Safe | ROW | No | Yes | ROW | |
| Safe | ROW | No | No | ROW | |
| Unsafe | STATEMENT | Yes | Yes | Error: not loggable | |
| Unsafe | STATEMENT | Yes | No | Warning: unsafe | STATEMENT |
| Unsafe | STATEMENT | No | Yes | Error: not loggable | |
| Unsafe | STATEMENT | No | No | Warning: unsafe | STATEMENT |
| Unsafe | MIXED | Yes | Yes | Error: not loggable | |
| Unsafe | MIXED | Yes | No | Error: not loggable | |
| Unsafe | MIXED | No | Yes | ROW | |
| Unsafe | MIXED | No | No | ROW | |
| Unsafe | ROW | Yes | Yes | Error: not loggable | |
| Unsafe | ROW | Yes | No | Error: not loggable | |
| Unsafe | ROW | No | Yes | ROW | |
| Unsafe | ROW | No | No | ROW | |
When a warning is produced by the determination, a standard
MySQL warning is produced (and is available using
SHOW WARNINGS). The information
is also written to the mysqld error log. Only
one error for each error instance per client connection is
logged to prevent flooding the log. The log message includes the
SQL statement that was attempted.
If a slave server was started with
--log-warnings enabled, the slave
prints messages to the error log to provide information about
its status, such as the binary log and relay log coordinates
where it starts its job, when it is switching to another relay
log, when it reconnects after a disconnect, and so forth.
The contents of the grant tables in the mysql
database can be modified directly (for example, with
INSERT or
DELETE) or indirectly (for
example, with GRANT or
CREATE USER). As of MySQL 5.1.17,
statements that affect mysql database tables
are written to the binary log using the following rules:
Data manipulation statements that change data in
mysql database tables directly are logged
according to the setting of the
binlog_format system
variable. This pertains to statements such as
INSERT,
UPDATE,
DELETE,
REPLACE,
DO,
LOAD DATA
INFILE, SELECT, and
TRUNCATE TABLE.
Statements that change the mysql database
indirectly are logged as statements regardless of the value
of binlog_format. This
pertains to statements such as
GRANT,
REVOKE,
SET PASSWORD,
RENAME USER,
CREATE (all forms except
CREATE TABLE
... SELECT), ALTER (all forms),
and DROP (all forms).
CREATE TABLE ...
SELECT is a combination of data definition and data
manipulation. The CREATE TABLE
part is logged using statement format and the
SELECT part is logged according
to the value of binlog_format.
The slow query log consists of SQL statements that took more than
long_query_time seconds to
execute and (as of MySQL 5.1.21) required at least
min_examined_row_limit rows to be
examined. 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.
By default, administrative statements are not logged, nor are
queries that do not use indexes for lookups. This behavior can be
changed using
--log-slow-admin-statements and
--log-queries-not-using-indexes, as
described later.
The time to acquire the initial locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might differ from execution order.
Control the slow query log at server startup as follows:
Before 5.1.6, the slow query log destination is always a file.
To enable the log, start mysqld with the
--log-slow-queries[=
option.
file_name]
As of MySQL 5.1.6, the destination can be a file or a table,
or both. Start mysqld with the
--log-slow-queries[=
option to enable the slow query log, and optionally use
file_name]--log-output to specify the log
destination (as described in
Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”).
As of MySQL 5.1.12, as an alternative to
--log-slow-queries, use
--slow_query_log[={0|1}] to
specify the initial slow query log state. In this case, the
default slow query log file name is used. With no argument or
an argument of 1,
--slow_query_log enables the
log. With an argument of 0, this option disables the log.
As of MySQL 5.1.29, use
--slow_query_log[={0|1}] to
enable or disable the slow query log, and optionally
--slow_query_log_file=
to specify a log file name. The
file_name--log-slow-queries option is
deprecated.
If the slow query log file is enabled but no name is specified,
the default name is
host_name-slow.log
To disable or enable the slow query log or change the log file
name at runtime, use the global
slow_query_log and
slow_query_log_file system
variables. Set slow_query_log to
0 (or OFF) to disable the log or to 1 (or
ON) to enable it. Set
slow_query_log_file to specify
the name of the log file. If a log file already is open, it is
closed and the new file is opened.
When the slow query log is enabled, the server writes output to
any destinations specified by the
--log-output option or
log_output system variable. If
you enable the log, the server opens the log file and writes
startup messages to it. However, further logging of queries to the
file does not occur unless the FILE log
destination is selected. If the destination is
NONE, the server writes no queries even if the
slow query log is enabled. Setting the log file name has no effect
on logging if the log destination value does not contain
FILE.
The server writes less information to the slow query log (and
binary log) if you use the
--log-short-format option.
To include slow administrative statements in the statements
written to the slow query log, use the
--log-slow-admin-statements server
option. Administrative statements include
ALTER TABLE,
ANALYZE TABLE,
CHECK TABLE,
CREATE INDEX,
DROP INDEX,
OPTIMIZE TABLE, and
REPAIR TABLE.
To include queries that do not use indexes for row lookups in the
statements written to the slow query log, use the
--log-queries-not-using-indexes
server option. See Section 5.1.3, “Server Command Options”. When such
queries are logged, the slow query log may grow quickly.
The server uses the controlling parameters in the following order to determine whether to write a query to the slow query log:
The query must either not be an administrative statement, or
--log-slow-admin-statements
must have been specified.
The query must have taken at least
long_query_time seconds, or
--log-queries-not-using-indexes
must have been specified and the query used no indexes for row
lookups.
The query must have examined at least
min_examined_row_limit rows.
The server does not write queries handled by the query cache to the slow query log, nor queries that would not benefit from the presence of an index because the table has zero rows or one row.
Prior to MySQL 5.1.45, replication slaves did not write replicated
queries to the slow query log, even if the same queries were
written to the slow query log on the master. (Bug #23300) In MySQL
5.1.45 and later, this behavior can be overridden using the
--log-slow-slave-statements option.
The slow query log should be protected because logged statements might contain passwords. See Section 6.1.2.3, “Passwords and Logging”.
The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process a slow query log file using the mysqldumpslow command to summarize the queries that appear in the log. See Section 4.6.8, “mysqldumpslow — Summarize Slow Query Log Files”.
In MySQL 5.1 and later, the DDL log, or metadata log, records
metadata operations generated by data definition statements such
as DROP TABLE and
ALTER TABLE. MySQL uses this log to
recover from crashes occurring in the middle of a metadata
operation. When executing the statement DROP TABLE t1,
t2, we need to ensure that both t1
and t2 are dropped, and that each table drop is
complete. Another example of this type of SQL statement is
ALTER
TABLE t3 DROP PARTITION p2, where we must make certain
that the partition is completely dropped and that its definition
is removed from the list of partitions for table
t3.
A record of metadata operations such as those just described are
written to the file ddl_log.log, in the MySQL
data directory. This is a binary file; it is not intended to be
human-readable, and you should not attempt to modify it in any
way.
ddl_log.log is not created until it is
actually needed for recording metadata statements, so it is
possible for this file not to be present on a MySQL server that is
functioning in a completely normal manner.
There are no user-configurable server options or variables associated with this file.
As described in Section 5.2, “MySQL Server Logs”, MySQL Server can create several different log files to help you see what activity is taking place. However, you must clean up these files regularly to ensure that the logs do not take up too much disk space.
When using MySQL with logging enabled, you may want to back up and remove old log files from time to time and tell MySQL to start logging to new files. See Section 7.2, “Database Backup Methods”.
On a Linux (Red Hat) installation, you can use the
mysql-log-rotate script for this. If you
installed MySQL from an RPM distribution, this script should have
been installed automatically. Be careful with this script if you
are using the binary log for replication. You should not remove
binary logs until you are certain that their contents have been
processed by all slaves.
On other systems, you must install a short script yourself that you start from cron (or its equivalent) for handling log files.
For the binary log, you can set the
expire_logs_days system variable
to expire binary log files automatically after a given number of
days (see Section 5.1.4, “Server System Variables”). If you are
using replication, you should set the variable no lower than the
maximum number of days your slaves might lag behind the master. To
remove binary logs on demand, use the PURGE
BINARY LOGS statement (see
Section 13.4.1.1, “PURGE BINARY LOGS Syntax”).
You can force MySQL to start using new log files by flushing the
logs. Log flushing occurs when you issue a
FLUSH LOGS
statement or execute a mysqladmin flush-logs,
mysqladmin refresh, mysqldump
--flush-logs, or mysqldump
--master-data command. See Section 13.7.6.3, “FLUSH Syntax”,
Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”, and Section 4.5.4, “mysqldump — A Database Backup Program”. In
addition, the binary log is flushed when its size reaches the
value of the max_binlog_size
system variable.
A log-flushing operation does the following:
If general query logging or slow query logging to a log file is enabled, the server closes and reopens the general query log file or slow query log file.
If binary logging is enabled, the server closes the current binary log file and opens a new log file with the next sequence number.
If the server was started with the
--log-error option to cause the
error log to be written to a file, the result of a
log-flushing operation is version dependent:
As of MySQL 5.1.51, the server closes and reopens the log file.
Prior to MySQL 5.1.51, the server renames the current log
file with the suffix -old, then creates
a new empty log file.
The server creates a new binary log file when you flush the logs.
However, it just closes and reopens the general and slow query log
files. To cause new files to be created on Unix, rename the
current log files before flushing them. At flush time, the server
opens new log files with the original names. For example, if the
general and slow query log files are named
mysql.log and
mysql-slow.log, you can use a series of
commands like this:
shell>cdshell>mysql-data-directorymv mysql.log mysql.oldshell>mv mysql-slow.log mysql-slow.oldshell>mysqladmin flush-logs
On Windows, use rename rather than mv.
At this point, you can make a backup of
mysql.old and
mysql-slow.old and then remove them from
disk.
As of MySQL 5.1.12, you can rename the general query log or slow query log at runtime by disabling the log:
SET GLOBAL general_log = 'OFF'; SET GLOBAL slow_query_log = 'OFF';
With the logs disabled, rename the log files externally; for example, from the command line. Then enable the logs again:
SET GLOBAL general_log = 'ON'; SET GLOBAL slow_query_log = 'ON';
This method works on any platform and does not require a server restart.
For older versions of MySQL on Windows, you cannot rename certain
log files while the server has them open. Before MySQL 5.1.3, this
restriction applies to all log files. You must stop the server,
rename the file, then restart the server to create a new log file.
From 5.1.4 to 5.1.50, the restriction applies only to the error
log file. To rename the error log file, a stop and restart can be
avoided by flushing the logs to cause the server to rename the
current log file with the suffix -old and
create a new empty error log file. For further information, see
Section 5.2.2, “The Error Log”.
In some cases, you might want to run multiple instances of MySQL on a single machine. You might want to test a new MySQL release while leaving an existing production setup undisturbed. Or you might want to give different users access to different mysqld servers that they manage themselves. (For example, you might be an Internet Service Provider that wants to provide independent MySQL installations for different customers.)
It is possible to use a different MySQL server binary per instance, or use the same binary for multiple instances, or any combination of the two approaches. For example, you might run a server from MySQL 5.0 and one from MySQL 5.1, to see how different versions handle a given workload. Or you might run multiple instances of the current production version, each managing a different set of databases.
Whether or not you use distinct server binaries, each instance that
you run must be configured with unique values for several operating
parameters. This eliminates the potential for conflict between
instances. Parameters can be set on the command line, in option
files, or by setting environment variables. See
Section 4.2.3, “Specifying Program Options”. To see the values used by a given
instance, connect to it and execute a SHOW
VARIABLES statement.
The primary resource managed by a MySQL instance is the data
directory. Each instance should use a different data directory, the
location of which is specified using the
--datadir=
option. For methods of configuring each instance with its own data
directory, and warnings about the dangers of failing to do so, see
Section 5.3.1, “Setting Up Multiple Data Directories”.
path
In addition to using different data directories, several other options must have different values for each server instance:
--port controls the port number
for TCP/IP connections. Alternatively, if the host has multiple
network addresses, you can use
--bind-address to cause each
server to listen to a different address.
--socket controls the Unix socket
file path on Unix or the named pipe name on Windows. On Windows,
it is necessary to specify distinct pipe names only for those
servers configured to permit named-pipe connections.
--shared-memory-base-name=
name
This option is used only on Windows. It designates the shared-memory name used by a Windows server to permit clients to connect using shared memory. It is necessary to specify distinct shared-memory names only for those servers configured to permit shared-memory connections.
This option indicates the path name of the file in which the server writes its process ID.
If you use the following log file options, their values must differ for each server:
For further discussion of log file options, see Section 5.2, “MySQL Server Logs”.
To achieve better performance, you can specify the following option differently for each server, to spread the load between several physical disks:
Having different temporary directories also makes it easier to determine which MySQL server created any given temporary file.
If you have multiple MySQL installations in different locations, you
can specify the base directory for each installation with the
--basedir=
option. This causes each instance to automatically use a different
data directory, log files, and PID file because the default for each
of those parameters is relative to the base directory. In that case,
the only other options you need to specify are the
path--socket and
--port options. Suppose that you
install different versions of MySQL using tar
file binary distributions. These install in different locations, so
you can start the server for each installation using the command
bin/mysqld_safe under its corresponding base
directory. mysqld_safe determines the proper
--basedir option to pass to
mysqld, and you need specify only the
--socket and
--port options to
mysqld_safe.
As discussed in the following sections, it is possible to start
additional servers by specifying appropriate command options or by
setting environment variables. However, if you need to run multiple
servers on a more permanent basis, it is more convenient to use
option files to specify for each server those option values that
must be unique to it. The
--defaults-file option is useful for
this purpose.
Each MySQL Instance on a machine should have its own data
directory. The location is specified using the
--datadir=
option.
path
There are different methods of setting up a data directory for a new instance:
Create a new data directory.
Copy an existing data directory.
The following discussion provides more detail about each method.
Normally, you should never have two servers that update data in the same databases. This may lead to unpleasant surprises if your operating system does not support fault-free system locking. If (despite this warning) you run multiple servers using the same data directory and they have logging enabled, you must use the appropriate options to specify log file names that are unique to each server. Otherwise, the servers try to log to the same files.
Even when the preceding precautions are observed, this kind of
setup works only with MyISAM and
MERGE tables, and not with any of the other
storage engines. Also, this warning against sharing a data
directory among servers always applies in an NFS environment.
Permitting multiple MySQL servers to access a common data
directory over NFS is a very bad idea. The
primary problem is that NFS is the speed bottleneck. It is not
meant for such use. Another risk with NFS is that you must
devise a way to ensure that two or more servers do not interfere
with each other. Usually NFS file locking is handled by the
lockd daemon, but at the moment there is no
platform that performs locking 100% reliably in every situation.
With this method, the data directory will be in the same state as when you first install MySQL. It will have the default set of MySQL accounts and no user data.
On Unix, initialize the data directory by running mysql_install_db. See Section 2.12.1, “Unix Postinstallation Procedures”.
On Windows, the data directory is included in MySQL distributions.
If you obtain a distribution in Windows Zip archive format, you
can unpack it into a temporary location, then copy the
data directory from this location to where
you are setting up the new instance.
With this method, any MySQL accounts or user data present in the data directory are carried over to the new data directory.
Stop the existing MySQL instance using the data directory. This must be a clean shutdown so that the instance flushes any pending changes to disk.
Copy the data directory to the location where the new data directory should be.
Copy the my.cnf or
my.ini option file used by the existing
instance. This serves as a basis for the new instance.
Modify the new option file so that any pathnames referring to the original data directory refer to the new data directory. Also, modify any other options that must be unique per instance, such as the TCP/IP port number and the log files. For a list of parameters that must be unique per instance, see Section 5.3, “Running Multiple MySQL Instances on One Machine”.
Start the new instance, telling it to use the new option file.
You can run multiple servers on Windows by starting them manually from the command line, each with appropriate operating parameters, or by installing several servers as Windows services and running them that way. General instructions for running MySQL from the command line or as a service are given in Section 2.3, “Installing MySQL on Microsoft Windows”. The following sections describe how to start each server with different values for those options that must be unique per server, such as the data directory. These options are listed in Section 5.3, “Running Multiple MySQL Instances on One Machine”.
The procedure for starting a single MySQL server manually from
the command line is described in
Section 2.3.6.5, “Starting MySQL Server from the Windows Command Line”. To start multiple
servers this way, you can specify the appropriate options on the
command line or in an option file. It is more convenient to
place the options in an option file, but it is necessary to make
sure that each server gets its own set of options. To do this,
create an option file for each server and tell the server the
file name with a --defaults-file
option when you run it.
Suppose that you want to run mysqld on port
3307 with a data directory of C:\mydata1,
and mysqld-debug on port 3308 with a data
directory of C:\mydata2. Use this
procedure:
Make sure that each data directory exists, including its own
copy of the mysql database that contains
the grant tables.
Create two option files. For example, create one file named
C:\my-opts1.cnf that looks like this:
[mysqld] datadir = C:/mydata1 port = 3307
Create a second file named
C:\my-opts2.cnf that looks like this:
[mysqld] datadir = C:/mydata2 port = 3308
Use the --defaults-file
option to start each server with its own option file:
C:\>C:\mysql\bin\mysqld --defaults-file=C:\my-opts1.cnfC:\>C:\mysql\bin\mysqld-debug --defaults-file=C:\my-opts2.cnf
Each server starts in the foreground (no new prompt appears until the server exits later), so you will need to issue those two commands in separate console windows.
To shut down the servers, connect to each using the appropriate port number:
C:\>C:\mysql\bin\mysqladmin --port=3307 shutdownC:\>C:\mysql\bin\mysqladmin --port=3308 shutdown
Servers configured as just described permit clients to connect
over TCP/IP. If your version of Windows supports named pipes and
you also want to permit named-pipe connections, use the
mysqld-nt (MySQL 5.1.20 and earlier),
mysqld (MySQL 5.1.21 and later) or
mysqld-debug server and specify options that
enable the named pipe and specify its name. Each server that
supports named-pipe connections must use a unique pipe name. For
example, the C:\my-opts1.cnf file might be
written like this:
[mysqld] datadir = C:/mydata1 port = 3307 enable-named-pipe socket = mypipe1
Modify C:\my-opts2.cnf similarly for use by
the second server. Then start the servers as described
previously.
A similar procedure applies for servers that you want to permit
shared-memory connections. Enable such connections with the
--shared-memory option and
specify a unique shared-memory name for each server with the
--shared-memory-base-name option.
On Windows, a MySQL server can run as a Windows service. The procedures for installing, controlling, and removing a single MySQL service are described in Section 2.3.6.7, “Starting MySQL Server as a Microsoft Windows Service”.
To set up multiple MySQL services, you must make sure that each instance uses a different service name in addition to the other parameters that must be unique per instance.
For the following instructions, suppose that you want to run the
mysqld server from two different versions of
MySQL that are installed at C:\mysql-5.0.92
and C:\mysql-5.1.73,
respectively. (This might be the case if you are running 5.0.92
as your production server, but also want to conduct tests using
5.1.73.)
To install MySQL as a Windows service, use the
--install or --install-manual
option. For information about these options, see
Section 2.3.6.7, “Starting MySQL Server as a Microsoft Windows Service”.
Based on the preceding information, you have several ways to set up multiple services. The following instructions describe some examples. Before trying any of them, shut down and remove any existing MySQL services.
Approach 1: Specify the
options for all services in one of the standard option
files. To do this, use a different service name for each
server. Suppose that you want to run the 5.0.92
mysqld using the service name of
mysqld1 and the 5.1.73
mysqld using the service name
mysqld2. In this case, you can use the
[mysqld1] group for 5.0.92 and the
[mysqld2] group for 5.1.73.
For example, you can set up C:\my.cnf
like this:
# options for mysqld1 service [mysqld1] basedir = C:/mysql-5.0.92 port = 3307 enable-named-pipe socket = mypipe1 # options for mysqld2 service [mysqld2] basedir = C:/mysql-5.1.73 port = 3308 enable-named-pipe socket = mypipe2
Install the services as follows, using the full server path names to ensure that Windows registers the correct executable program for each service:
C:\>C:\mysql-5.0.92\bin\mysqld --install mysqld1C:\>C:\mysql-5.1.73\bin\mysqld --install mysqld2
To start the services, use the services manager, or use NET START with the appropriate service names:
C:\>NET START mysqld1C:\>NET START mysqld2
To stop the services, use the services manager, or use NET STOP with the appropriate service names:
C:\>NET STOP mysqld1C:\>NET STOP mysqld2
Approach 2: Specify options
for each server in separate files and use
--defaults-file when you
install the services to tell each server what file to use.
In this case, each file should list options using a
[mysqld] group.
With this approach, to specify options for the 5.0.92
mysqld-nt, create a file
C:\my-opts1.cnf that looks like this:
[mysqld] basedir = C:/mysql-5.0.92 port = 3307 enable-named-pipe socket = mypipe1
For the 5.1.73 mysqld, create
a file C:\my-opts2.cnf that looks like
this:
[mysqld] basedir = C:/mysql-5.1.73 port = 3308 enable-named-pipe socket = mypipe2
Install the services as follows (enter each command on a single line):
C:\>C:\mysql-5.0.92\bin\mysqld --install mysqld1--defaults-file=C:\my-opts1.cnfC:\>C:\mysql-5.1.73\bin\mysqld --install mysqld2--defaults-file=C:\my-opts2.cnf
When you install a MySQL server as a service and use a
--defaults-file option, the
service name must precede the option.
After installing the services, start and stop them the same way as in the preceding example.
To remove multiple services, use mysqld
--remove for each one, specifying a service name
following the --remove option. If
the service name is the default (MySQL), you
can omit it.
One way is to run multiple MySQL instances on Unix is to compile different servers with different default TCP/IP ports and Unix socket files so that each one listens on different network interfaces. Compiling in different base directories for each installation also results automatically in a separate, compiled-in data directory, log file, and PID file location for each server.
Assume that an existing 5.0 server is configured for
the default TCP/IP port number (3306) and Unix socket file
(/tmp/mysql.sock). To configure a new
5.1.73 server to have different operating parameters,
use a configure command something like this:
shell>./configure --with-tcp-port=port_number\--with-unix-socket-path=file_name\--prefix=/usr/local/mysql-5.1.73
Here, port_number and
file_name must be different from the
default TCP/IP port number and Unix socket file path name, and the
--prefix value should specify an
installation directory different from the one under which the
existing MySQL installation is located.
If you have a MySQL server listening on a given port number, you can use the following command to find out what operating parameters it is using for several important configurable variables, including the base directory and Unix socket file name:
shell> mysqladmin --host=host_name --port=port_number variables
With the information displayed by that command, you can tell what option values not to use when configuring an additional server.
If you specify localhost as the host name,
mysqladmin defaults to using a Unix socket file
connection rather than TCP/IP. To explicitly specify the
connection protocol, use the
--protocol={TCP|SOCKET|PIPE|MEMORY}
option.
You need not compile a new MySQL server just to start with a different Unix socket file and TCP/IP port number. It is also possible to use the same server binary and start each invocation of it with different parameter values at runtime. One way to do so is by using command-line options:
shell> mysqld_safe --socket=file_name --port=port_number
To start a second server, provide different
--socket and
--port option values, and pass a
--datadir=
option to mysqld_safe so that the server uses a
different data directory.
path
Alternatively, put the options for each server in a different
option file, then start each server using a
--defaults-file option that
specifies the path to the appropriate option file. For example, if
the option files for two server instances are named
/usr/local/mysql/my.cnf and
/usr/local/mysql/my.cnf2, start the servers
like this: command:
shell>mysqld_safe --defaults-file=/usr/local/mysql/my.cnfshell>mysqld_safe --defaults-file=/usr/local/mysql/my.cnf2
Another way to achieve a similar effect is to use environment variables to set the Unix socket file name and TCP/IP port number:
shell>MYSQL_UNIX_PORT=/tmp/mysqld-new.sockshell>MYSQL_TCP_PORT=3307shell>export MYSQL_UNIX_PORT MYSQL_TCP_PORTshell>mysql_install_db --user=mysqlshell>mysqld_safe --datadir=/path/to/datadir &
This is a quick way of starting a second server to use for testing. The nice thing about this method is that the environment variable settings apply to any client programs that you invoke from the same shell. Thus, connections for those clients are automatically directed to the second server.
Section 2.14, “Environment Variables”, includes a list of other environment variables you can use to affect MySQL programs.
On Unix, the mysqld_multi script provides another way to start multiple servers. See Section 4.3.4, “mysqld_multi — Manage Multiple MySQL Servers”.
To connect with a client program to a MySQL server that is listening to different network interfaces from those compiled into your client, you can use one of the following methods:
Start the client with
--host=
host_name--port=
to connect using TCP/IP to a remote server, with
port_number--host=127.0.0.1
--port=
to connect using TCP/IP to a local server, or with
port_number--host=localhost
--socket=
to connect to a local server using a Unix socket file or a
Windows named pipe.
file_name
Start the client with
--protocol=TCP to connect
using TCP/IP,
--protocol=SOCKET to connect
using a Unix socket file,
--protocol=PIPE to connect
using a named pipe, or
--protocol=MEMORY to connect
using shared memory. For TCP/IP connections, you may also need
to specify --host and
--port options. For the other
types of connections, you may need to specify a
--socket option to specify a
Unix socket file or Windows named-pipe name, or a
--shared-memory-base-name
option to specify the shared-memory name. Shared-memory
connections are supported only on Windows.
On Unix, set the MYSQL_UNIX_PORT and
MYSQL_TCP_PORT environment variables to
point to the Unix socket file and TCP/IP port number before
you start your clients. If you normally use a specific socket
file or port number, you can place commands to set these
environment variables in your .login file
so that they apply each time you log in. See
Section 2.14, “Environment Variables”.
Specify the default Unix socket file and TCP/IP port number in
the [client] group of an option file. For
example, you can use C:\my.cnf on
Windows, or the .my.cnf file in your home
directory on Unix. See Section 4.2.6, “Using Option Files”.
In a C program, you can specify the socket file or port number
arguments in the
mysql_real_connect() call. You
can also have the program read option files by calling
mysql_options(). See
Section 21.8.7, “C API Function Descriptions”.
If you are using the Perl DBD::mysql
module, you can read options from MySQL option files. For
example:
$dsn = "DBI:mysql:test;mysql_read_default_group=client;"
. "mysql_read_default_file=/usr/local/mysql/data/my.cnf";
$dbh = DBI->connect($dsn, $user, $password);
See Section 21.10, “MySQL Perl API”.
Other programming interfaces may provide similar capabilities for reading option files.