mysqld(1)
NAME
mysqld - the MySQL server
SYNOPSIS
mysqld [options]
DESCRIPTION
mysqld is the MySQL server. The following discussion
covers these MySQL server configuration topics:
o Startup options that the server supports
o Server system variables
o Server status variables
o How to set the server SQL mode
o The server shutdown process
FBMYSQLDFR COMMAND OPTIONS
When you start the mysqld server, you can specify program
options using any of the methods described in Section 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 3.2, "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 [xxxxx_SERVER] groups, where
xxxxx is the name of the application into which the server
is embedded.
mysqld accepts many command options. For a brief list,
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:
o Options that affect security: See Section 5.3,
"Security-Related mysqld Options".
o SSL-related options: See Section 7.7.5, "SSL Command
Options".
o Binary log control options: See Section 10.3, "The
Binary Log".
o Replication-related options: See Section 8,
"Replication Startup Options".
o Options specific to particular storage engines: See
Section 1.1, "MyISAM Startup Options", Section 5.3,
"BDB Startup Options", Section 2.4, "InnoDB Startup
Options and System Variables", and Section 5.5.1,
"MySQL Cluster-Related Command Options for mysqld".
You can also set the values of server system variables by
using variable names as options, as described later in
this section.
o --help, -?
Display a short help message and exit. Use both the
--verbose and --help options to see the full message.
o --allow-suspicious-udfs
This option controls whether user-defined functions
that have only an xxx symbol for the main function can
be loaded. By default, the option is off and only UDFs
that have at least one auxiliary symbol can be loaded;
this prevents attempts at loading functions from shared
object files other than those containing legitimate
UDFs. This option was added in version 5.0.3. See
Section 2.4.6, "User-Defined Function Security
Precautions".
o --ansi
Use standard (ANSI) SQL syntax instead of MySQL syntax.
For more precise control over the server SQL mode, use
the --sql-mode option instead. See Section 9.3,
"Running MySQL in ANSI Mode", and the section called
"THE SERVER SQL MODE".
o --basedir=path, -b path
The path to the MySQL installation directory. All paths
are usually resolved relative to this directory.
o --bind-address=IP
The IP address to bind to.
o --bootstrap
This option is used by the mysql_install_db script to
create the MySQL privilege tables without having to
start a full MySQL server.
o --character-sets-dir=path
The directory where character sets are installed. See
Section 9.1, "The Character Set Used for Data and
Sorting".
o --character-set-client-handshake
Don't ignore character set information sent by the
client. To ignore client information and use the
default server character set, use
--skip-character-set-client-handshake; this makes MySQL
behave like MySQL 4.0.
o --character-set-filesystem=charset_name
The filesystem character set. This option sets the
character_set_filesystem system variable. It was added
in MySQL 5.0.19.
o --character-set-server=charset_name, -C charset_name
Use charset_name as the default server character set.
See Section 9.1, "The Character Set Used for Data and
Sorting".
o --chroot=path
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.
o --collation-server=collation_name
Use collation_name as the default server collation. See
Section 9.1, "The Character Set Used for Data and
Sorting".
o --console
(Windows only.) Write error log messages to stderr and
stdout even if --log-error is specified. mysqld does
not close the console window if this option is used.
o --core-file
Write a core file if mysqld dies. For some systems, you
must also specify the --core-file-size option to
mysqld_safe. See mysqld_safe(1). Note that on some
systems, such as Solaris, you do not get a core file if
you are also using the --user option.
o --datadir=path, -h path
The path to the data directory.
o --debug[=debug_options], -# [debug_options]
If MySQL is configured with --with-debug, you can use
this option to get a trace file of what mysqld is
doing. The debug_options string often is
'd:t:o,file_name'. The default is
'd:t:i:o,mysqld.trace'. See Section 1.2, "Creating
Trace Files".
o --default-character-set=charset_name (DEPRECATED)
Use charset_name as the default character set. This
option is deprecated in favor of
--character-set-server. See Section 9.1, "The Character
Set Used for Data and Sorting".
o --default-collation=collation_name
Use collation_name as the default collation. This
option is deprecated in favor of --collation-server.
See Section 9.1, "The Character Set Used for Data and
Sorting".
o --default-storage-engine=type
Set the default storage engine (table type) for tables.
See Chapter 14, Storage Engines and Table Types.
o --default-table-type=type
This option is a synonym for --default-storage-engine.
o --default-time-zone=timezone
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.
o --delay-key-write[= OFF | ON | 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 5.2, "Tuning Server Parameters", and
Section 1.1, "MyISAM Startup Options".
Note: 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.
o --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.
o --enable-named-pipe
Enable support for named pipes. This option applies
only on Windows NT, 2000, XP, and 2003 systems, and can
be used only with the mysqld-nt and mysqld-max-nt
servers that support named-pipe connections.
o --exit-info[=flags], -T [flags]
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!
o --external-locking
Enable external locking (system locking), which is
disabled by default as of MySQL 4.0. Note that if you
use this option on a system on which lockd does not
fully work (such as Linux), it is easy for mysqld to
deadlock. This option previously was named
--enable-locking.
Note: If you use this option to enable updates to
MyISAM tables from many MySQL processes, you must
ensure that the following conditions are satisfied:
o You should not use the query cache for queries that
use tables that are updated by another process.
o You should not use --delay-key-write=ALL or
DELAY_KEY_WRITE=1 on any shared tables.
The easiest way to ensure this is to always use
--external-locking together with --delay-key-write=OFF
and --query-cache-size=0. (This is not done by default
because in many setups it is useful to have a mixture
of the preceding options.)
o --flush
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 4.2, "What to Do If MySQL Keeps Crashing".
o --init-file=file
Read SQL statements from this file at startup. Each
statement must be on a single line and should not
include comments.
o --innodb-safe-binlog
Adds consistency guarantees between the content of
InnoDB tables and the binary log. See Section 10.3,
"The Binary Log". This option was removed in MySQL
5.0.3, having been made obsolete by the introduction of
XA transaction support.
o --innodb-xxx
The InnoDB options are listed in Section 2.4, "InnoDB
Startup Options and System Variables".
o --language=lang_name, -L lang_name
Return client error messages in the given language.
lang_name can be given as the language name or as the
full pathname to the directory where the language files
are installed. See Section 9.2, "Setting the Error
Message Language".
o --large-pages
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 OS. 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 pages support (which is called HugeTLB in
Linux). We have plans to extend this support to
FreeBSD, Solaris and possibly other platforms.
Before large pages can be used on Linux, it is
necessary to configure the HugeTLB memory pool. For
reference, consult the hugetlbpage.txt file in the
Linux kernel source.
This option is disabled by default. It was added in
MySQL 5.0.3.
o --log[=file_name], -l [file_name]
Log connections and SQL statements received from
clients to this file. See Section 10.2, "The General
Query Log". If you omit the filename, MySQL uses
host_name.log as the filename.
o --log-bin=[base_name]
Enable binary logging. The server logs all statements
that change data to the binary log, which is used for
backup and replication. See Section 10.3, "The Binary
Log".
The option value, if given, is the basename for the log
sequence. The server creates binary log files in
sequence by adding a numeric suffix to the basename. It
is recommended that you specify a basename (see
Section 8.1, "Open Issues in MySQL", for the reason).
Otherwise, MySQL uses host_name-bin as the basename.
o --log-bin-index[=file_name]
The index file for binary log filenames. See
Section 10.3, "The Binary Log". If you omit the
filename, and if you didn't specify one with --log-bin,
MySQL uses host_name-bin.index as the filename.
o --log-bin-trust-function-creators[={0|1}]
With no argument or an argument of 1, this option sets
the log_bin_trust_function_creators system variable to
1. With an argument of 0, this option sets the system
variable to 0. log_bin_trust_function_creators affects
how MySQL enforces restrictions on stored function
creation. See Section 4, "Binary Logging of Stored
Routines and Triggers".
This option was added in MySQL 5.0.16.
o --log-bin-trust-routine-creators[={0|1}]
This is the old name for
--log-bin-trust-function-creators. Before MySQL 5.0.16,
it also applies to stored procedures, not just stored
functions and sets the log_bin_trust_routine_creators
system variable. As of 5.0.16, this option is
deprecated. It is recognized for backward compatibility
but its use results in a warning.
This option was added in MySQL 5.0.6.
o --log-error[=file_name]
Log errors and startup messages to this file. See
Section 10.1, "The Error Log". If you omit the
filename, MySQL uses host_name.err. If the filename has
no extension, the server adds an extension of
o --log-isam[=file_name]
Log all MyISAM changes to this file (used only when
debugging MyISAM).
o --log-long-format (DEPRECATED)
Log extra information to the update log, binary update
log, and slow query log, if they have been activated.
For example, the username 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.
o --log-queries-not-using-indexes
If you are using this option with --log-slow-queries,
queries that do not use indexes are logged to the slow
query log. See Section 10.4, "The Slow Query Log".
o --log-short-format
Log less information to the update log, binary update
log, and slow query log, if they have been activated.
For example, the username and timestamp are not logged
for queries.
o --log-slow-admin-statements
Log slow administrative statements such as OPTIMIZE
TABLE, ANALYZE TABLE, and ALTER TABLE to the slow query
log.
o --log-slow-queries[=file_name]
Log all queries that have taken more than
long_query_time seconds to execute to this file. See
Section 10.4, "The Slow Query Log". See the
descriptions of the --log-long-format and
--log-short-format options for details.
o --log-warnings=[level], -W [level]
Print out warnings such as Aborted connection... to
the error log. Enabling this option is recommended, for
example, if you use replication (you get more
information about what is happening, such as messages
about network failures and reconnections). This option
is enabled (1) by default, and the default level value
if omitted is 1. To disable this option, use
--log-warnings=0. Aborted connections are not logged to
the error log unless the value is greater than 1. See
Section 2.10, "Communication Errors and Aborted
Connections".
o --low-priority-updates
Give table-modifying operations (INSERT, REPLACE,
DELETE, UPDATE) lower priority than selects. This can
also be done via {INSERT | REPLACE | DELETE | UPDATE}
LOW_PRIORITY ... to lower the priority of only one
query, or by SET LOW_PRIORITY_UPDATES=1 to change the
priority in one thread. See Section 3.2, "Table Locking
Issues".
o --memlock
Lock the mysqld process in memory. This works on
systems such as Solaris that support the mlockall()
system call. This might help if you have a problem
where the operating system is causing mysqld to swap on
disk. Note that use of this option requires that you
run the server as root, which is normally not a good
idea for security reasons. See Section 5.5, "How to Run
MySQL as a Normal User".
o --myisam-recover[=option[,option]...]]
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. You can also use a
value of "" to disable this option. If this option is
used, each time mysqld opens a MyISAM table, it checks
whether the table is marked as crashed or wasn't closed
properly. (The last option works only if you are
running with external locking disabled.) If this is the
case, mysqld runs a check on the table. If the table
was corrupted, mysqld attempts to repair it.
The following options affect how the repair works:
OptionDescriptionDEFAULTThe same as not giving any
option to --myisam-recover.BACKUPIf the data file was
changed during recovery, save a backup of the
tbl_name.MYD
file as
tbl_name-datetime.BAK.FORCERun
recovery even if we would lose more than one row from
the
.MYD file.QUICKDon't check the
rows in the table if there aren't any delete
blocks.Before the server automatically repairs a table,
it writes a note about the repair to the error log. If
you want to be able to recover from most problems
without user intervention, you should use the options
BACKUP,FORCE. This forces a repair of a table even if
some rows would be deleted, but it keeps the old data
file as a backup so that you can later examine what
happened.
See Section 1.1, "MyISAM Startup Options".
o --ndb-connectstring=connect_string
When using the NDB storage engine, it is possible to
point out the management server that distributes the
cluster configuration by setting the connect string
option. See Section 4.4.2, "The MySQL Cluster
connectstring", for syntax.
o --ndbcluster
If the binary includes support for the NDB Cluster
storage engine, this option enables the engine, which
is disabled by default. See Chapter 15, MySQL Cluster.
o --old-passwords
Force the server to generate short (pre-4.1) password
hashes for new passwords. This is useful for
compatibility when the server must support older client
programs. See Section 6.9, "Password Hashing as of
MySQL 4.1".
o --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 1, "Debugging a MySQL
Server".
o --open-files-limit=count
Change the number of file descriptors available to
mysqld. If this option is not set or is set to 0,
mysqld uses the value to reserve file descriptors with
setrlimit(). If the value is 0, mysqld reserves
max_connectionsx5 or max_connections +
table_open_cachex2 files (whichever is larger). You
should try increasing this value if mysqld gives you
the error Too many open files.
o --pid-file=path
The pathname of the process ID file. This file is used
by other programs such as mysqld_safe to determine the
server's process ID.
o --port=port_num, -P port_num
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.
o --port-open-timeout=num
On some systems, when the server is stopped, the TCP/IP
port might not become available immediately. If the
server is restarted quickly afterward, its attempt to
reopen the port can fail. This option indicates how
many seconds the server should wait for the TCP/IP port
to become free if it cannot be opened. The default is
not to wait. This option was added in MySQL 5.0.19.
o --safe-mode
Skip some optimization stages.
o --safe-show-database (DEPRECATED)
See Section 6.3, "Privileges Provided by MySQL".
o --safe-user-create
If this option is enabled, a user cannot create new
MySQL users by using the GRANT statement, if the user
doesn't have the INSERT privilege for the mysql.user
table or any column in the table.
o --secure-auth
Disallow authentication by clients that attempt to use
accounts that have old (pre-4.1) passwords.
o --shared-memory
Enable shared-memory connections by local clients. This
option is available only on Windows.
o --shared-memory-base-name=name
The name of shared memory to use for shared-memory
connections. This option is available only on Windows.
The default name is MYSQL. The name is case sensitive.
o --skip-bdb
Disable the BDB storage engine. This saves memory and
might speed up some operations. Do not use this option
if you require BDB tables.
o --skip-concurrent-insert
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.)
o --skip-external-locking
Do not use external locking (system locking). With
external locking disabled, you must shut down the
server to use myisamchk. (See Section 4.3, "MySQL
Stability".) To avoid this requirement, use the CHECK
TABLE and REPAIR TABLE statements to check and repair
MyISAM tables.
External locking has been disabled by default since
MySQL 4.0.
o --skip-grant-tables
This option causes the server not to use the privilege
system at all, which gives anyone with access to the
server unrestricted access to all databases. You can
cause a running server to start using the grant tables
again by executing mysqladmin flush-privileges or
mysqladmin reload command from a system shell, or by
issuing a MySQL FLUSH PRIVILEGES statement after
connecting to the server. This option also suppresses
loading of user-defined functions (UDFs).
o --skip-host-cache
Do not use the internal hostname cache for faster
name-to-IP resolution. Instead, query the DNS server
every time a client connects. See Section 5.6, "How
MySQL Uses DNS".
o --skip-innodb
Disable the InnoDB storage engine. This saves memory
and disk space and might speed up some operations. Do
not use this option if you require InnoDB tables.
o --skip-name-resolve
Do not resolve hostnames when checking client
connections. Use only IP numbers. If you use this
option, all Host column values in the grant tables must
be IP numbers or localhost. See Section 5.6, "How MySQL
Uses DNS".
o --skip-ndbcluster
Disable the NDB Cluster storage engine. This is the
default for binaries that were built with NDB Cluster
storage engine support; the server allocates memory and
other resources for this storage engine only if the
--ndbcluster option is given explicitly. See
Section 4.3, "Quick Test Setup of MySQL Cluster", for
an example of usage.
o --skip-networking
Don't listen for TCP/IP connections at all. All
interaction with mysqld must be made via named pipes or
shared memory (on Windows) or Unix socket files (on
Unix). This option is highly recommended for systems
where only local clients are allowed. See Section 5.6,
"How MySQL Uses DNS".
o --standalone
Available on Windows NT-based systems only; instructs
the MySQL server not to run as a service.
o --symbolic-links, --skip-symbolic-links
Enable or disable symbolic link support. This option
has different effects on Windows and Unix:
o On Windows, enabling symbolic links allows you to
establish a symbolic link to a database directory by
creating a db_name.sym file that contains the path
to the real directory. See Section 6.1.3, "Using
Symbolic Links for Databases on Windows".
o 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 6.1.2, "Using Symbolic Links for Tables on
Unix".
o --skip-safemalloc
If MySQL is configured with --with-debug=full, all
MySQL programs check for memory overruns during each
memory allocation and memory freeing operation. This
checking is very slow, so for the server you can avoid
it when you don't need it by using the
--skip-safemalloc option.
o --skip-show-database
With this option, the SHOW DATABASES statement is
allowed only to users who have the SHOW DATABASES
privilege, and the statement displays all database
names. Without this option, SHOW DATABASES is allowed
to all users, but displays each database name only if
the user has the SHOW DATABASES privilege or some
privilege for the database. Note that any global
privilege is considered a privilege for the database.
o --skip-stack-trace
Don't write stack traces. This option is useful when
you are running mysqld under a debugger. On some
systems, you also must use this option to get a core
file. See Section 1, "Debugging a MySQL Server".
o --skip-thread-priority
Disable using thread priorities for faster response
time.
o --socket=path
On Unix, this option specifies the Unix socket file to
use when listening for local connections. The default
value is /tmp/mysql.sock. On Windows, the option
specifies the pipe name to use when listening for local
connections that use a named pipe. The default value is
MySQL (not case sensitive).
o --sql-mode=value[,value[,value...]]
Set the SQL mode. See the section called "THE SERVER
SQL MODE".
o --temp-pool
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.
o --transaction-isolation=level
Sets the default transaction isolation level. The level
value can be READ-UNCOMMITTED, READ-COMMITTED,
REPEATABLE-READ, or SERIALIZABLE. See Section 4.6, "SET
TRANSACTION Syntax".
o --tmpdir=path, -t path
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 filesystem or to a
directory that is cleared when the server host
restarts. For more information about the storage
location of temporary files, see Section 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.
o --user={user_name | user_id}, -u {user_name | user_id}
Run the mysqld server as the user having the name
user_name or the numeric user ID user_id. ("User" in
this context refers to a system login account, not a
MySQL user listed in the grant tables.)
This option is mandatory when starting mysqld as root.
The server changes its user ID during its startup
sequence, causing it to run as that particular user
rather than as root. See Section 5.1, "General Security
Guidelines".
To avoid a possible security hole where a user adds a
--user=root option to a my.cnf file (thus causing the
server to run as root), mysqld uses only the first
--user option specified and produces a warning if there
are multiple --user options. Options in /etc/my.cnf and
$MYSQL_HOME/my.cnf are processed before command-line
options, so it is recommended that you put a --user
option in /etc/my.cnf and specify a value other than
root. The option in /etc/my.cnf is found before any
other --user options, which ensures that the server
runs as a user other than root, and that a warning
results if any other --user option is found.
o --version, -V
Display version information and exit.
You can assign a value to a server system variable by
using an option of the form --var_name=value. For example,
--key_buffer_size=32M sets the key_buffer_size variable to
a value of 32MB.
Note that when you assign a value to a variable, MySQL
might automatically correct the value to stay within a
given range, or adjust the value to the closest allowable
value if only certain values are allowed.
If you want to restrict the maximum value to which a
variable can be set at runtime with SET, you can define
this by using the --maximum-var_name command-line option.
It is also possible to set variables by using
--set-variable=var_name=value or -O var_name=value syntax.
This syntax is deprecated.
You can change the values of most system variables for a
running server with the SET statement. See Section 5.3,
"SET Syntax".
the section called "SERVER SYSTEM VARIABLES", provides a
full description for all variables, and additional
information for setting them at server startup and
runtime. Section 5.2, "Tuning Server Parameters",
includes information on optimizing the server by tuning
system variables.
SERVER 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:
o 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
o 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
o To see the current values used by a running server, use
the SHOW VARIABLES statement.
This section provides a description of each system
variable. Variables with no version indicated are present
in all MySQL 5.0 releases. For historical information
concerning their implementation, please see MySQL 3.23,
4.0, 4.1 Reference Manual.
For additional system variable information, see these
sections:
o the section called "USING SYSTEM VARIABLES", discusses
the syntax for setting and displaying system variable
values.
o the section called "Dynamic System Variables", lists
the variables that can be set at runtime.
o Information on tuning sytem variables can be found in
Section 5.2, "Tuning Server Parameters".
o Section 2.4, "InnoDB Startup Options and System
Variables", lists InnoDB system variables.
Note: Some of the following variable descriptions refer to
"enabling" or "disabling" a variable. These variables can
be enabled with the SET statement by setting them to ON or
1, or disabled by setting them to OFF or 0. However, to
set such a variable on the command line or in an option
file, you must set it to 1 or 0; setting it to ON or OFF
will not work. For example, on the command line,
--delay_key_write=1 works but --delay_key_write=ON does
not.
Values for buffer sizes, lengths, and stack sizes are
given in bytes unless otherwise specified.
o auto_increment_increment
auto_increment_increment and auto_increment_offset are
intended for use with master-to-master replication, and
can be used to control the operation of AUTO_INCREMENT
columns. Both variables can be set globally or locally,
and each can assume an integer value between 1 and
65,535 inclusive. Setting the value of either of these
two variables to 0 causes its value to be set to 1
instead. Attempting to set the value of either of these
two variables to an integer greater than 65,535 or less
than 0 causes its value to be set to 65,535 instead.
Attempting to set the value of auto_increment_increment
or auto_increment_offset to a non-integer value gives
rise to an error, and the actual value of the variable
remains unchanged.
These two variables effect AUTO_INCREMENT column
behavior as follows:
o auto_increment_increment controls the interval
between successive column values. For example:
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE autoinc1
-> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)
mysql> SET @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.01 sec)
mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
| 31 |
+-----+
4 rows in set (0.00 sec)
(Note how SHOW VARIABLES is used here to obtain the
current values for these variables.)
o auto_increment_offset determines the starting point
for the AUTO_INCREMENT column value. Consider the
following, assuming that these statements are
executed during the same session as the example
given in the description for
auto_increment_increment:
mysql> SET @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 5 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE autoinc2
-> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc2;
+-----+
| col |
+-----+
| 5 |
| 15 |
| 25 |
| 35 |
+-----+
4 rows in set (0.02 sec)
If the value of auto_increment_offset is greater
than that of auto_increment_increment, the value of
auto_increment_offset is ignored.
Should one or both of these variables be changed and
then new rows inserted into a table containing an
AUTO_INCREMENT column, the results may seem
counterintuitive because the series of AUTO_INCREMENT
values is calculated without regard to any values
already present in the column, and the next value
inserted is the least value in the series that is
greater than the maximum existing value in the
AUTO_INCREMENT column. In other words, the series is
calculated like so:
auto_increment_offset + N x auto_increment_increment
where N is a positive integer value in the series [1,
2, 3, ...]. For example:
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 5 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
| 31 |
+-----+
4 rows in set (0.00 sec)
mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
| 31 |
| 35 |
| 45 |
| 55 |
| 65 |
+-----+
8 rows in set (0.00 sec)
The values shown for auto_increment_increment and
auto_increment_offset generate the series 5 + N x 10,
that is, [5, 15, 25, 35, 45, ...]. The greatest value
present in the col column prior to the INSERT is 31,
and the next available value in the AUTO_INCREMENT
series is 35, so the inserted values for col begin at
that point and the results are as shown for the SELECT
query.
It is important to remember that it is not possible to
confine the effects of these two variables to a single
table, and thus they do not take the place of the
sequences offered by some other database management
systems; these variables control the behavior of all
AUTO_INCREMENT columns in all tables on the MySQL
server. If one of these variables is set globally, its
effects persist until the global value is changed or
overridden by setting them locally, or until mysqld is
restarted. If set locally, the new value affects
AUTO_INCREMENT columns for all tables into which new
rows are inserted by the current user for the duration
of the session, unless the values are changed during
that session.
The auto_increment_increment variable was added in
MySQL 5.0.2. Its default value is 1. See Section 13,
"Auto-Increment in Multiple-Master Replication".
o auto_increment_offset
This variable was introduced in MySQL 5.0.2. Its
default value is 1. For particulars, see the
description for auto_increment_increment.
o back_log
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.
o basedir
The MySQL installation base directory. This variable
can be set with the --basedir option.
o bdb_cache_size
The size of the buffer that is allocated for caching
indexes and rows for BDB tables. If you don't use BDB
tables, you should start mysqld with --skip-bdb to not
allocate memory for this cache.
o bdb_home
The base directory for BDB tables. This should be
assigned the same value as the datadir variable.
o bdb_log_buffer_size
The size of the buffer that is allocated for caching
indexes and rows for BDB tables. If you don't use BDB
tables, you should set this to 0 or start mysqld with
--skip-bdb to not allocate memory for this cache.
o bdb_logdir
The directory where the BDB storage engine writes its
log files. This variable can be set with the
--bdb-logdir option.
o bdb_max_lock
The maximum number of locks that can be active for a
BDB table (10,000 by default). You should increase this
value if errors such as the following occur when you
perform long transactions or when mysqld has to examine
many rows to calculate a query:
bdb: Lock table is out of available locks
Got error 12 from ...
o bdb_shared_data
This is ON if you are using --bdb-shared-data to start
Berkeley DB in multi-process mode. (Do not use
DB_PRIVATE when initializing Berkeley DB.)
o bdb_tmpdir
The BDB temporary file directory.
o binlog_cache_size
The size of the cache to hold the SQL statements for
the binary log during a transaction. A binary log cache
is allocated for each client if the server supports any
transactional storage engines and if the server has the
binary log enabled (--log-bin option). If you often use
large, multiple-statement transactions, you can
increase this cache size to get more performance. The
Binlog_cache_use and Binlog_cache_disk_use status
variables can be useful for tuning the size of this
variable. See Section 10.3, "The Binary Log".
o bulk_insert_buffer_size
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 non-empty 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.
o character_set_client
The character set for statements that arrive from the
client.
o character_set_connection
The character set used for literals that do not have a
character set introducer and for number-to-string
conversion.
o character_set_database
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.
o character_set_filesystem
The filesystem character set. This variable is used to
interpret string literals that refer to filenames, such
as in the LOAD DATA INFILE and SELECT ... INTO OUTFILE
statements and the LOAD_FILE() function. Such filenames
are converted from character_set_client to
character_set_filesystem before the file opening
attempt occurs. The default value is binary, which
means that no conversion occurs. For systems on which
multi-byte filenames are allowed, a different value may
be more appropriate. For example, if the system
represents filenames using UTF-8, set
character_set_filesytem to 'utf8'. This variable was
added in MySQL 5.0.19.
o character_set_results
The character set used for returning query results to
the client.
o character_set_server
The server's default character set.
o character_set_system
The character set used by the server for storing
identifiers. The value is always utf8.
o character_sets_dir
The directory where character sets are installed.
o collation_connection
The collation of the connection character set.
o collation_database
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.
o collation_server
The server's default collation.
o completion_type
The transaction completion type:
o If the value is 0 (the default), COMMIT and ROLLBACK
are unaffected.
o If the value is 1, COMMIT and ROLLBACK are
equivalent to COMMIT AND CHAIN and ROLLBACK AND
CHAIN, respectively. (A new transaction starts
immediately with the same isolation level as the
just-terminated transaction.)
o If the value is 2, COMMIT and ROLLBACK are
equivalent to COMMIT RELEASE and ROLLBACK RELEASE,
respectively. (The server disconnects after
terminating the transaction.)
This variable was added in MySQL 5.0.3
o concurrent_insert
If ON (the default), MySQL allows INSERT and SELECT
statements to run concurrently for MyISAM tables that
have no free blocks in the middle. You can turn this
option off by starting mysqld with --safe or
--skip-new.
In MySQL 5.0.6, this variable was changed to take three
integer values: ValueDescription0Off1(Default) Enables
concurrent insert for MyISAM tables
that don't have holes2Enables
concurrent inserts for all MyISAM tables. If
table has a hole and is in use by
another thread
the new row will be inserted at
end of table. If
table is not in use then MySQL
does a normal read
lock and inserts the new row into
the hole.See also Section 3.3, "Concurrent Inserts".
o connect_timeout
The number of seconds that the mysqld server waits for
a connect packet before responding with Bad handshake.
o datadir
The MySQL data directory. This variable can be set with
the --datadir option.
o date_format
This variable is not implemented.
o datetime_format
This variable is not implemented.
o default_week_format
The default mode value to use for the WEEK() function.
See Section 5, "Date and Time Functions".
o delay_key_write
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. OptionDescriptionOFFDELAY_KEY_WRITE
is ignored.ONMySQL honors any DELAY_KEY_WRITE option
specified in
CREATE TABLE statements. This
is the default value.ALLAll 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 the section called
"\FBMYSQLD\FR COMMAND OPTIONS", and Section 1.1,
"MyISAM Startup Options".
Note that enabling external locking with
--external-locking offers no protection against index
corruption for tables that use delayed key writes.
o delayed_insert_limit
After inserting delayed_insert_limit delayed rows, the
INSERT DELAYED handler thread checks whether there are
any SELECT statements pending. If so, it allows them to
execute before continuing to insert delayed rows.
o delayed_insert_timeout
How many seconds an INSERT DELAYED handler thread
should wait for INSERT statements before terminating.
o delayed_queue_size
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.
o div_precision_increment
This variable indicates the number of digits of
precision by which to increase the result of division
operations performed with the / operator. The default
value is 4. The minimum and maximum values are 0 and
30, respectively. The following example illustrates the
effect of increasing the default value.
mysql> SELECT 1/7;
+--------+
| 1/7 |
+--------+
| 0.1429 |
+--------+
mysql> SET div_precision_increment = 12;
mysql> SELECT 1/7;
+----------------+
| 1/7 |
+----------------+
| 0.142857142857 |
+----------------+
This variable was added in MySQL 5.0.6.
o engine_condition_pushdown
This variable applies to NDB. By default it is 0 (OFF):
If you execute a query such as SELECT * FROM t WHERE
mycol = 42, where mycol is a non-indexed column, the
query is executed as a full table scan on every NDB
node. Each node sends every row to the MySQL server,
which applies the WHERE condition. If
engine_condition_pushdown is set to 1 (ON), the
condition is "pushed down" to the storage engine and
sent to the NDB nodes. Each node uses the condition to
perform the scan, and only sends back to the MySQL
server the rows that match the condition.
This variable was added in MySQL 5.0.3. Before that,
the default NDB behavior is the same as for a value of
OFF.
o expire_logs_days
The number of days for automatic binary log removal.
The default is 0, which means "no automatic removal."
Possible removals happen at startup and at binary log
rotation.
o flush
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 4.2, "What to Do If
MySQL Keeps Crashing". This variable is set to ON if
you start mysqld with the --flush option.
o flush_time
If this is set to a non-zero value, all tables are
closed every flush_time seconds to free up resources
and synchronize unflushed data to disk. We recommend
that this option be used only on Windows 9x or Me, or
on systems with minimal resources.
o ft_boolean_syntax
The list of operators supported by boolean full-text
searches performed using IN BOOLEAN MODE. See
Section 7.1, "Boolean Full-Text Searches".
The default variable value is '+ -><()~*:""&|'. The
rules for changing the value are as follows:
o Operator function is determined by position within
the string.
o The replacement value must be 14 characters.
o Each character must be an ASCII non-alphanumeric
character.
o Either the first or second character must be a
space.
o No duplicates are allowed except the phrase quoting
operators in positions 11 and 12. These two
characters are not required to be the same, but they
are the only two that may be.
o Positions 10, 13, and 14 (which by default are set
to `:', `&', and `|') are reserved for future
extensions.
o ft_max_word_len
The maximum length of the word to be included in a
FULLTEXT index.
Note: FULLTEXT indexes must be rebuilt after changing
this variable. Use REPAIR TABLE tbl_name QUICK.
o ft_min_word_len
The minimum length of the word to be included in a
FULLTEXT index.
Note: FULLTEXT indexes must be rebuilt after changing
this variable. Use REPAIR TABLE tbl_name QUICK.
o ft_query_expansion_limit
The number of top matches to use for full-text searches
performed using WITH QUERY EXPANSION.
o ft_stopword_file
The file from which to read the list of stopwords for
full-text searches. All the words from the file are
used; comments are not honored. By default, a built-in
list of stopwords is used (as defined in the
myisam/ft_static.c file). Setting this variable to the
empty string ('') disables stopword filtering.
Note: FULLTEXT indexes must be rebuilt after changing
this variable or the contents of the stopword file. Use
REPAIR TABLE tbl_name QUICK.
o group_concat_max_len
The maximum allowed result length for the
GROUP_CONCAT() function. The default is 1024.
o have_archive
YES if mysqld supports ARCHIVE tables, NO if not.
o have_bdb
YES if mysqld supports BDB tables. DISABLED if
--skip-bdb is used.
o have_blackhole_engine
YES if mysqld supports BLACKHOLE tables, NO if not.
o have_compress
YES if the zlib compression library is available to the
server, NO if not. If not, the COMPRESS() and
UNCOMPRESS() functions cannot be used.
o have_crypt
YES if the crypt() system call is available to the
server, NO if not. If not, the ENCRYPT() function
cannot be used.
o have_csv
YES if mysqld supports ARCHIVE tables, NO if not.
o have_example_engine
YES if mysqld supports EXAMPLE tables, NO if not.
have_federated_engine
YES if mysqld supports FEDERATED tables, NO if not.
This variable was added in MySQL 5.0.3.
o have_geometry
YES if the server supports spatial data types, NO if
not.
o have_innodb
YES if mysqld supports InnoDB tables. DISABLED if
--skip-innodb is used.
o have_isam
In MySQL 5.0, this variable appears only for reasons of
backward compatibility. It is always NO because ISAM
tables are no longer supported.
o have_ndbcluster
YES if mysqld supports NDB Cluster tables. DISABLED if
--skip-ndbcluster is used.
o have_openssl
YES if mysqld supports SSL (encryption) of the
client/server protocol, NO if not.
o have_query_cache
YES if mysqld supports the query cache, NO if not.
o have_raid
In MySQL 5.0, this variable appears only for reasons of
backward compatibility. It is always NO because RAID
tables are no longer supported.
o have_rtree_keys
YES if RTREE indexes are available, NO if not. (These
are used for spatial indexes in MyISAM tables.)
o have_symlink
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.
o init_connect
A string to be executed by the server for each client
that connects. The string consists of one or more SQL
statements. To specify multiple statements, separate
them by semicolon characters. For example, each client
begins by default with autocommit mode enabled. There
is no global system variable to specify that autocommit
should be disabled by default, but init_connect can be
used to achieve the same effect:
SET GLOBAL init_connect='SET AUTOCOMMIT=0';
This variable can also be set on the command line or in
an option file. To set the variable as just shown using
an option file, include these lines:
[mysqld]
init_connect='SET AUTOCOMMIT=0'
Note that the content of init_connect is not executed
for users that have the SUPER privilege. This is done
so that an erroneous value for init_connect does not
prevent all clients from connecting. For example, the
value might contain a statement that has a syntax
error, thus causing client connections to fail. Not
executing init_connect for users that have the SUPER
privilege enables them to open a connection and fix the
init_connect value.
o init_file
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.
o init_slave
This variable is similar to init_connect, but is a
string to be executed by a slave server each time the
SQL thread starts. The format of the string is the same
as for the init_connect variable.
o innodb_xxx
InnoDB system variables are listed in Section 2.4,
"InnoDB Startup Options and System Variables".
o interactive_timeout
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.
o join_buffer_size
The size of the buffer that is used for 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.
o key_buffer_size
Index blocks for MyISAM tables are buffered and are
shared by all threads. key_buffer_size is the size of
the buffer used for index blocks. The key buffer is
also known as the key cache.
The maximum allowable setting for key_buffer_size is
4GB. 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.
Increase the value to get better index handling (for
all reads and multiple writes) to as much as you can
afford. Using a value that is 25% of total memory on a
machine that mainly runs MySQL is quite common.
However, if you make the value too large (for example,
more than 50% of your total memory) your system might
start to page and become extremely slow. MySQL relies
on the operating system to perform filesystem caching
for data reads, so you must leave some room for the
filesystem cache. Consider also the memory requirements
of other storage engines.
For even more speed when writing many rows at the same
time, use LOCK TABLES. See Section 2.16, "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 5.4, "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 x key_cache_block_size) / key_buffer_size)
This value is an approximation because some space in
the key buffer may be allocated internally for
administrative structures.
It is possible to create multiple MyISAM key caches.
The size limit of 4GB applies to each cache
individually, not as a group. See Section 4.6, "The
MyISAM Key Cache".
o key_cache_age_threshold
This value controls the demotion of buffers from the
hot sub-chain of a key cache to the warm sub-chain.
Lower values cause demotion to happen more quickly. The
minimum value is 100. The default value is 300. See
Section 4.6, "The MyISAM Key Cache".
o key_cache_block_size
The size in bytes of blocks in the key cache. The
default value is 1024. See Section 4.6, "The MyISAM Key
Cache".
o key_cache_division_limit
The division point between the hot and warm sub-chains
of the key cache buffer chain. The value is the
percentage of the buffer chain to use for the warm
sub-chain. Allowable values range from 1 to 100. The
default value is 100. See Section 4.6, "The MyISAM Key
Cache".
o language
The language used for error messages.
o large_file_support
Whether mysqld was compiled with options for large file
support.
o large_pages
Whether large page support is enabled. This variable
was added in MySQL 5.0.3.
o license
The type of license the server has.
o local_infile
Whether LOCAL is supported for LOAD DATA INFILE
statements. See Section 5.4, "Security Issues with LOAD
DATA LOCAL".
o locked_in_memory
Whether mysqld was locked in memory with --memlock.
o log
Whether logging of all statements to the general query
log is enabled. See Section 10.2, "The General Query
Log".
o log_bin
Whether the binary log is enabled. See Section 10.3,
"The Binary Log".
o log_bin_trust_function_creators
This variable applies when binary logging is enabled.
It controls whether stored function creators can be
trusted not to create stored functions that will cause
unsafe events to be written to the binary log. If set
to 0 (the default), users are not allowed to create or
alter stored functions unless they have the SUPER
privilege in addition to the CREATE ROUTINE or ALTER
ROUTINE privilege. A setting of 0 also enforces the
restriction that a function must be declared with the
DETERMINISTIC characteristic, or with the READS SQL
DATA or NO SQL characteristic. If the variable is set
to 1, MySQL does not enforce these restrictions on
stored function creation. See Section 4, "Binary
Logging of Stored Routines and Triggers".
This variable was added in MySQL 5.0.16.
o log_bin_trust_routine_creators
This is the old name for
log_bin_trust_function_creators. Before MySQL 5.0.16,
it also applies to stored procedures, not just stored
functions. As of 5.0.16, this variable is deprecated.
It is recognized for backward compatibility but its use
results in a warning.
This variable was added in MySQL 5.0.6.
o log_error
The location of the error log.
o log_slave_updates
Whether updates received by a slave server from a
master server should be logged to the slave's own
binary log. Binary logging must be enabled on the slave
for this to have any effect. See Section 8,
"Replication Startup Options".
o log_slow_queries
Whether slow queries should be logged. "Slow" is
determined by the value of the long_query_time
variable. See Section 10.4, "The Slow Query Log".
o log_warnings
Whether to produce additional warning messages. It is
enabled (1) by default. Aborted connections are not
logged to the error log unless the value is greater
than 1.
o long_query_time
If a query takes longer than this many seconds, the
server increments the Slow_queries status variable. If
you are using the --log-slow-queries option, the query
is logged to the slow query log file. This value is
measured in real time, not CPU time, so a query that is
under the threshold on a lightly loaded system might be
above the threshold on a heavily loaded one. The
minimum value is 1. See Section 10.4, "The Slow Query
Log".
o low_priority_updates
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 variable
previously was named sql_low_priority_updates.
o lower_case_file_system
This variable describes the case sensitivity of
filenames on the filesystem where the data directory is
located. OFF means filenames are case sensitive, ON
means they are not case sensitive.
o lower_case_table_names
If set to 1, table names are stored in lowercase on
disk and table name comparisons are not case sensitive.
If set to 2 table names are stored as given but
compared in lowercase. This option also applies to
database names and table aliases. See Section 2.2,
"Identifier Case Sensitivity".
If you are using InnoDB tables, you should set this
variable to 1 on all platforms to force names to be
converted to lowercase.
You should not set this variable to 0 if you are
running MySQL on a system that does not have
case-sensitive filenames (such as Windows or Mac OS X).
If this variable is not set at startup and the
filesystem on which the data directory is located does
not have case-sensitive filenames, MySQL automatically
sets lower_case_table_names to 2.
o max_allowed_packet
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 biggest BLOB you want to use. The protocol limit
for max_allowed_packet is 1GB.
o max_binlog_cache_size
If a multiple-statement transaction requires more than
this amount of memory, the server generates a
Multi-statement transaction required more than
'max_binlog_cache_size' bytes of storage error.
o max_binlog_size
If a write to the binary log causes the current log
file size to exceed the value of this variable, the
server rotates the binary logs (closes the current file
and opens the next one). You cannot set this variable
to more than 1GB or to less than 4096 bytes. The
default value is 1GB.
A transaction is written in one chunk to the binary
log, so it is never split between several binary logs.
Therefore, if you have big transactions, you might see
binary logs larger than max_binlog_size.
If max_relay_log_size is 0, the value of
max_binlog_size applies to relay logs as well.
o max_connect_errors
If there are more than this number of interrupted
connections from a host, that host is blocked from
further connections. You can unblock blocked hosts with
the FLUSH HOSTS statement.
o max_connections
The number of simultaneous client connections allowed.
Increasing this value increases the number of file
descriptors that mysqld requires. See Section 4.8, "How
MySQL Opens and Closes Tables", for comments on file
descriptor limits. See also Section 2.6, "Too many
connections".
o max_delayed_threads
Do not start more than this number of threads to handle
INSERT DELAYED statements. If you try to insert data
into a new table after all INSERT DELAYED threads are
in use, the row is inserted as if the DELAYED attribute
wasn't specified. If you set this to 0, MySQL never
creates a thread to handle DELAYED rows; in effect,
this disables DELAYED entirely.
o max_error_count
The maximum number of error, warning, and note messages
to be stored for display by the SHOW ERRORS and SHOW
WARNINGS statements.
o max_heap_table_size
This variable sets the maximum size to which MEMORY
tables are allowed to grow. The value of the variable
is used to calculate MEMORY table MAX_ROWS values.
Setting this variable has no effect on any existing
MEMORY table, unless the table is re-created with a
statement such as CREATE TABLE or altered with ALTER
TABLE or TRUNCATE TABLE.
o max_insert_delayed_threads
This variable is a synonym for max_delayed_threads.
o max_join_size
Do not allow SELECT statements that probably need to
examine more than max_join_size rows (for single-table
statements) or row combinations (for multiple-table
statements) or that are likely to do more than
max_join_size disk seeks. By setting this value, you
can catch SELECT statements where keys are not used
properly and that would probably take a long time. Set
it if your users tend to perform joins that lack a
WHERE clause, that take a long time, or that return
millions of rows.
Setting this variable to a value other than DEFAULT
resets the value of SQL_BIG_SELECTS to 0. If you set
the SQL_BIG_SELECTS value again, the max_join_size
variable is ignored.
If a query result is in the query cache, no result size
check is performed, because the result has previously
been computed and it does not burden the server to send
it to the client.
This variable previously was named sql_max_join_size.
o max_length_for_sort_data
The cutoff on the size of index values that determines
which filesort algorithm to use. See Section 2.12,
"ORDER BY Optimization".
o max_relay_log_size
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 3, "Replication Implementation Details".
o max_seeks_for_key
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 5.4.13, "SHOW INDEX Syntax"). By
setting this to a low value (say, 100), you can force
MySQL to prefer indexes instead of table scans.
o max_sort_length
The number of bytes to use when sorting BLOB or TEXT
values. Only the first max_sort_length bytes of each
value are used; the rest are ignored.
o max_tmp_tables
The maximum number of temporary tables a client can
keep open at the same time. (This option does not yet
do anything.)
o max_user_connections
The maximum number of simultaneous connections allowed
to any given MySQL account. A value of 0 means "no
limit."
Before MySQL 5.0.3, this variable has only global
scope. Beginning with MySQL 5.0.3, it also has a
read-only session scope. The session variable has the
same value as the global variable unless the current
account has a non-zero MAX_USER_CONNECTIONS resource
limit. In that case, the session value reflects the
account limit.
o max_write_lock_count
After this many write locks, allow some pending read
lock requests to be processed in between.
o myisam_data_pointer_size
The default pointer size in bytes, to be used by CREATE
TABLE for MyISAM tables when no MAX_ROWS option is
specified. This variable cannot be less than 2 or
larger than 7. The default value is 6 (4 before MySQL
5.0.6). This variable was added in MySQL 4.1.2. See
Section 2.11, "The table is full".
o myisam_max_extra_sort_file_size (DEPRECATED)
If the temporary file used for fast MyISAM index
creation would be larger than using the key cache by
the amount specified here, prefer the key cache method.
This is mainly used to force long character keys in
large tables to use the slower key cache method to
create the index. The value is given in bytes.
Note: This variable was removed in MySQL 5.0.6.
o myisam_max_sort_file_size
The maximum size of the temporary file MySQL is allowed
to use while re-creating a MyISAM index (during REPAIR
TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file
size would be larger than this value, the index is
created using the key cache instead, which is slower.
The value is given in bytes.
o myisam_recover_options
The value of the --myisam-recover option. See the
section called "\FBMYSQLD\FR COMMAND OPTIONS".
o myisam_repair_threads
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. Note: Multi-threaded repair is still
beta-quality code.
o myisam_sort_buffer_size
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.
o myisam_stats_method
How the server treats NULL values when collecting
statistics about the distribution of index values for
MyISAM tables. This variable has two possible values,
nulls_equal and nulls_unequal. 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.
The method that is used for generating table statistics
influences how the optimizer chooses indexes for query
execution, as described in Section 4.7, "MyISAM Index
Statistics Collection".
This variable was added in MySQL 5.0.14. For older
versions, the statistics collection method is
equivalent to nulls_equal.
o multi_read_range
Specifies the maximum number of ranges to send to a
storage engine during range selects. The default value
is 256. Sending multiple ranges to an engine is a
feature that can improve the performance of certain
selects dramatically, particularly for NDBCLUSTER. This
engine needs to send the range requests to all nodes,
and sending many of those requests at once reduces the
communication costs significantly. This variable was
added in MySQL 5.0.3.
o named_pipe
(Windows only.) Indicates whether the server supports
connections over named pipes.
o net_buffer_length
The communication buffer is reset to this size between
SQL statements. 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 buffer
is automatically enlarged, up to max_allowed_packet
bytes.
o net_read_timeout
The number of seconds to wait for more data from a
connection before aborting the read. This timeout
applies only to TCP/IP connections, not to connections
made via Unix socket files, named pipes, or shared
memory. When the server is reading from the client,
net_read_timeout is the timeout value controlling when
to abort. When the server is writing to the client,
net_write_timeout is the timeout value controlling when
to abort. See also slave_net_timeout.
o net_retry_count
If a read on a communication port is interrupted, retry
this many times before giving up. This value should be
set quite high on FreeBSD because internal interrupts
are sent to all threads.
o net_write_timeout
The number of seconds to wait for a block to be written
to a connection before aborting the write. This timeout
applies only to TCP/IP connections, not to connections
made via Unix socket files, named pipes, or shared
memory. See also net_read_timeout.
o new
This variable was used in MySQL 4.0 to turn on some 4.1
behaviors, and is retained for backward compatibility.
In MySQL 5.0, its value is always OFF.
o old_passwords
Whether the server should use pre-4.1-style passwords
for MySQL user accounts. See Section 2.3, "Client does
not support authentication protocol".
o one_shot
This is not a variable, but it can be used when setting
some variables. It is described in Section 5.3, "SET
Syntax".
o open_files_limit
The number of files that the operating system allows
mysqld to open. This is the real value allowed by the
system and might be different from the value you gave
using the --open-files-limit option to mysqld or
mysqld_safe. The value is 0 on systems where MySQL
can't change the number of open files.
o optimizer_prune_level
Controls the heuristics applied during query
optimization to prune less-promising partial plans from
the optimizer search space. A value of 0 disables
heuristics so that the optimizer performs an exhaustive
search. A value of 1 causes the optimizer to prune
plans based on the number of rows retrieved by
intermediate plans. This variable was added in MySQL
5.0.1.
o optimizer_search_depth
The maximum depth of search performed by the query
optimizer. Values larger than the number of relations
in a query result in better query plans, but take
longer to generate an execution plan for a query.
Values smaller than the number of relations in a query
return an execution plan quicker, but the resulting
plan may be far from being optimal. If set to 0, the
system automatically picks a reasonable value. If set
to the maximum number of tables used in a query plus 2,
the optimizer switches to the algorithm used in MySQL
5.0.0 (and previous versions) for performing searches.
This variable was added in MySQL 5.0.1.
o pid_file
The pathname of the process ID (PID) file. This
variable can be set with the --pid-file option.
o port
The number of the port on which the server listens for
TCP/IP connections. This variable can be set with the
--port option.
o preload_buffer_size
The size of the buffer that is allocated when
preloading indexes.
o protocol_version
The version of the client/server protocol used by the
MySQL server.
o query_alloc_block_size
The allocation size of memory blocks that are allocated
for objects created during statement parsing and
execution. If you have problems with memory
fragmentation, it might help to increase this a bit.
o query_cache_limit
Don't cache results that are larger than this number of
bytes. The default value is 1MB.
o query_cache_min_res_unit
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 12.3,
"Query Cache Configuration".
o query_cache_size
The amount of memory allocated for caching query
results. The default value is 0, which disables the
query cache. Note that this amount of memory is
allocated even if query_cache_type is set to 0. See
Section 12.3, "Query Cache Configuration", for more
information.
o query_cache_type
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: OptionDescription0 or
OFFDon't cache or retrieve results. Note that this does
not deallocate the
query cache buffer. To do that,
you should set
query_cache_size to 0.1 or
ONCache all query results except for those that begin
with SELECT
SQL_NO_CACHE.2 or DEMANDCache
results only for queries that begin with SELECT
SQL_CACHE.This variable defaults
to ON.
o query_cache_wlock_invalidate
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.
o query_prealloc_size
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.
o range_alloc_block_size
The size of blocks that are allocated when doing range
optimization.
o read_buffer_size
Each thread that does a sequential scan allocates a
buffer of this size (in bytes) for each table it scans.
If you do many sequential scans, you might want to
increase this value, which defaults to 131072.
o read_only
When the variable is set to ON for a replication slave
server, it causes the slave to allow no updates except
from slave threads or from users that have the SUPER
privilege. This can be useful to ensure that a slave
server accepts updates only from its master server and
not from clients. As of MySQL 5.0.16, this variable
does not apply to TEMPORARY tables.
o relay_log_purge
Disables or enables automatic purging of relay log
files as soon as they are not needed any more. The
default value is 1 (ON).
o read_rnd_buffer_size
When reading rows in sorted order following a
key-sorting operation, the rows are read through this
buffer to avoid disk seeks. 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.
o secure_auth
If the MySQL server has been started with the
--secure-auth option, it blocks connections from all
accounts that have passwords stored in the old
(pre-4.1) format. In that case, the value of this
variable is ON, otherwise it is OFF.
You should enable this option if you want to prevent
all use of passwords employing the old format (and
hence insecure communication over the network).
Server startup fails with an error if this option is
enabled and the privilege tables are in pre-4.1 format.
See Section 2.3, "Client does not support
authentication protocol".
o server_id
The server ID. This value is set by the --server-id
option. It is used for replication to enable master and
slave servers to identify themselves uniquely.
o shared_memory
(Windows only.) Whether the server allows shared-memory
connections.
o shared_memory_base_name
(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.
o skip_external_locking
This is OFF if mysqld uses external locking, ON if
external locking is disabled.
o skip_networking
This is ON if the server allows only local (non-TCP/IP)
connections. On Unix, local connections use a Unix
socket file. On Windows, local connections use a named
pipe or shared memory. On NetWare, only TCP/IP
connections are supported, so do not set this variable
to ON. This variable can be set to ON with the
--skip-networking option.
o skip_show_database
This prevents people from using the SHOW DATABASES
statement if they do not have the SHOW DATABASES
privilege. This can improve security if you have
concerns about users being able to see databases
belonging to other users. Its effect depends on the
SHOW DATABASES privilege: If the variable value is ON,
the SHOW DATABASES statement is allowed only to users
who have the SHOW DATABASES privilege, and the
statement displays all database names. If the value is
OFF, SHOW DATABASES is allowed to all users, but
displays the names of only those databases for which
the user has the SHOW DATABASES or other privilege.
o slave_compressed_protocol
Whether to use compression of the slave/master protocol
if both the slave and the master support it.
o slave_load_tmpdir
The name of the directory where the slave creates
temporary files for replicating LOAD DATA INFILE
statements.
o slave_net_timeout
The number of seconds to wait for more data from a
master/slave connection before aborting the read. This
timeout applies only to TCP/IP connections, not to
connections made via Unix socket files, named pipes, or
shared memory.
o slave_skip_errors
The replication errors that the slave should skip
(ignore).
o slave_transaction_retries
If a replication slave SQL thread fails to execute a
transaction because of an InnoDB deadlock or exceeded
InnoDB's innodb_lock_wait_timeout or NDBCluster's
TransactionDeadlockDetectionTimeout or
TransactionInactiveTimeout, it automatically retries
slave_transaction_retries times before stopping with an
error. The default priot to MySQL 4.0.3 is 0. You must
explicitly set the value greater than 0 to enable the
"retry" behavior, which is probably a good idea. In
MySQL 5.0.3 or newer, the default is 10.
o slow_launch_time
If creating a thread takes longer than this many
seconds, the server increments the Slow_launch_threads
status variable.
o socket
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).
o sort_buffer_size
Each thread that needs to do a sort allocates a buffer
of this size. Increase this value for faster ORDER BY
or GROUP BY operations. See Section 4.4, "Where MySQL
Stores Temporary Files".
o sql_mode
The current server SQL mode, which can be set
dynamically. See the section called "THE SERVER SQL
MODE".
o sql_slave_skip_counter
The number of events from the master that a slave
server should skip. See Section 6.2.6, "SET GLOBAL
SQL_SLAVE_SKIP_COUNTER Syntax".
o storage_engine
The default storage engine (table type). To set the
storage engine at server startup, use the
--default-storage-engine option. See the section called
"\FBMYSQLD\FR COMMAND OPTIONS".
o sync_binlog
If the value of this variable is positive, the MySQL
server synchronizes its binary log to disk (using
fdatasync()) after every sync_binlog writes to the
binary log. Note that there is one write to the binary
log per statement if autocommit is enabled, and one
write per transaction otherwise. The default value is
0, which does no synchronizing to disk. A value of 1 is
the safest choice, because in the event of a crash you
lose at most one statement or transaction from the
binary log. However, it is also the slowest choice
(unless the disk has a battery-backed cache, which
makes synchronization very fast).
If the value of sync_binlog is 0 (the default), no
extra flushing is done. The server relies on the
operating system to flush the file contents occasionaly
as for any other file.
o sync_frm
If this variable is set to 1, when any non-temporary
table is created its file is synchronized to disk
(using fdatasync()). This is slower but safer in case
of a crash. The default is 1.
o system_time_zone
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 tome zone for each
client that connects. See Section 9.8, "MySQL Server
Time Zone Support".
o table_cache
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 the section called "SERVER STATUS
VARIABLES". If the value of Opened_tables is large and
you don't do FLUSH TABLES a lot (which just forces all
tables to be closed and reopened), then you should
increase the value of the table_cache variable. For
more information about the table cache, see
Section 4.8, "How MySQL Opens and Closes Tables".
o table_type
This variable is a synonym for storage_engine. In MySQL
5.0, storage_engine is the preferred name.
o thread_cache_size
How many threads the server should cache for reuse.
When a client disconnects, the client's threads are put
in the cache if there are fewer than thread_cache_size
threads there. Requests for threads are satisfied by
reusing threads taken from the cache if possible, and
only when the cache is empty is a new thread created.
This variable can be increased to improve performance
if you have a lot of new connections. (Normally, this
doesn't provide a notable performance improvement if
you have a good thread implementation.) By examining
the difference between the Connections and
Threads_created status variables, you can see how
efficient the thread cache is. For details, see the
section called "SERVER STATUS VARIABLES".
o thread_concurrency
On Solaris, mysqld calls thr_setconcurrency() with this
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.
o thread_stack
The stack size for each thread. Many of the limits
detected by the crash-me test are dependent on this
value. The default is large enough for normal
operation. See Section 1.4, "The MySQL Benchmark
Suite". The default is 192KB.
o time_format
This variable is not implemented.
o time_zone
The current time zone. This variable is used to
initialize the tome zone for each client that connects.
By default, the initial value of this is 'SYSTEM'
(which means, "use the value of system_time_zone"). The
value can be specified explicitly at server startup
with the --default-time-zone option. See Section 9.8,
"MySQL Server Time Zone Support".
o tmp_table_size
If an in-memory temporary table exceeds this size,
MySQL automatically converts it to an on-disk MyISAM
table. Increase the value of tmp_table_size if you do
many advanced GROUP BY queries and you have lots of
memory.
o tmpdir
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
filesystem or to a directory that is cleared when the
server host restarts. A replication slave needs some of
its temporary files to survive a machine restart so
that it can replicate temporary tables or LOAD DATA
INFILE operations. If files in the temporary file
directory are lost when the server restarts,
replication fails. However, if you are using MySQL
4.0.0 or later, you can set the slave's temporary
directory using the slave_load_tmpdir variable. In that
case, the slave won't use the general tmpdir value and
you can set tmpdir to a non-permanent location.
o transaction_alloc_block_size
The amount in bytes by which to increase a
per-transaction memory pool which needs memory. See the
description of transaction_prealloc_size.
o transaction_prealloc_size
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.
o tx_isolation
The default transaction isolation level. Defaults to
REPEATABLE-READ.
This variable is set by the SET TRANSACTION ISOLATION
LEVEL statement. See Section 4.6, "SET TRANSACTION
Syntax". If you set tx_isolation directly to an
isolation level name that contains a space, the name
should be enclosed within quotes, with the space
replaced by a dash. For example:
SET tx_isolation = 'READ-COMMITTED';
o updatable_views_with_limit
This variable controls whether updates can be made
using a view that does not contain a primary key in the
underlying table, if the update 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:
o 1 or YES: Issue a warning only (not an error
message). This is the default value.
o 0 or NO: Prohibit the update.
This variable was added in MySQL 5.0.2.
o version
The version number for the server.
o version_bdb
The BDB storage engine version.
o version_comment
The configure script has a --with-comment option that
allows a comment to be specified when building MySQL.
This variable contains the value of that comment.
o version_compile_machine
The type of machine or architecture on which MySQL was
built.
o version_compile_os
The type of operating system on which MySQL was built.
o wait_timeout
The number of seconds the server waits for activity on
a non-interactive connection before closing it. This
timeout applies only to TCP/IP connections, not to
connections made via 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.
USING SYSTEM VARIABLES
The mysql server maintains many system variables that
indicate how it is configured. the section called "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:
o 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 3, "Specifying Program
Options".)
o 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
gigabygtes, 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-var_name at server startup. For
example, to prevent the value of query_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 the section called "Dynamic System Variables". To
change a system variable with SET, refer to it as
var_name, optionally preceded by a modifier:
o 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.
o 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.
o LOCAL and @@local. are synonyms for SESSION and
@@session..
o 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;
When you assign a value to a system variable with SET, you
cannot use suffix letters in the value (as can be done
with startup options). However, the value can take the
form of an expression:
SET sort_buffer_size = 10 * 1024 * 1024;
The @@var_name syntax for system variables is supported
for compatibility with some other database systems.
If you change a session system variable, the value remains
in effect until your session ends or until you change the
variable to a different value. The change is not visible
to other clients.
If you change a global system variable, the value is
remembered and used for new connections until the server
restarts. (To make a global system variable setting
permanent, you should set it in an option file.) The
change is visible to any client that accesses that global
variable. However, the change affects the corresponding
session variable only for clients that connect after the
change. The global variable change does not affect the
session variable for any client that is currently
connected (not even that of the client that issues the SET
GLOBAL statement).
To prevent incorrect usage, MySQL produces an error if you
use SET GLOBAL with a variable that can only be used with
SET SESSION or if you do not specify GLOBAL (or @@global.)
when setting a global variable.
To set a SESSION variable to the GLOBAL value or a GLOBAL
value to the compiled-in MySQL default value, use the
DEFAULT keyword. For example, the following two statements
are identical in setting the session value of
max_join_size to the global value:
SET max_join_size=DEFAULT;
SET @@session.max_join_size=@@global.max_join_size;
Not all system variables can be set to DEFAULT. In such
cases, use of DEFAULT results in an error.
You can refer to the values of specific global or sesson
system variables in expressions by using one of the
@@-modifiers. For example, you can retrieve values in a
SELECT statement like this:
SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
When you refer to a system variable in an expression as
@@var_name (that is, when you do not specify @@global. or
@@session.), MySQL returns the session value if it exists
and the global value otherwise. (This differs from SET
@@var_name = value, which always refers to the session
value.)
Note: Some system variables can be enabled with the SET
statement by setting them to ON or 1, or disabled by
setting them to OFF or 0. However, to set such a variable
on the command line or in an option file, you must set it
to 1 or 0; setting it to ON or OFF will not work. For
example, on the command line, --delay_key_write=1 works
but --delay_key_write=ON does not.
To display system variable names and values, use the SHOW
VARIABLES statement.
mysql> SHOW VARIABLES;
+--------+--------------------------------------------------------------+
| Variable_name | Value |
+--------+--------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 32768 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| version | 5.0.19-Max |
| version_comment | MySQL Community Edition - Max (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+--------+--------------------------------------------------------------+
With a LIKE clause, the statement displays only those
variables that match the pattern. To obtain a specific
variable name, use a LIKE clause as shown:
SHOW VARIABLES LIKE 'max_join_size';
SHOW SESSION VARIABLES LIKE 'max_join_size';
To get a list of variables whose name match a pattern, use
the `%' wildcard character in a LIKE clause:
SHOW VARIABLES LIKE '%size%';
SHOW GLOBAL VARIABLES LIKE '%size%';
Wildcard characters can be used in any position within the
pattern to be matched. Strictly speaking, because `_' is a
wildcard that matches any single character, you should
escape it as `\_' to match it literally. In practice, this
is rarely necessary.
For SHOW VARIABLES, if you specify neither GLOBAL nor
SESSION, MySQL returns SESSION values.
The reason for requiring the GLOBAL keyword when setting
GLOBAL-only variables but not when retrieving them is to
prevent problems in the future. If we were to remove a
SESSION variable that has the same name as a GLOBAL
variable, a client with the SUPER privilege might
accidentally change the GLOBAL variable rather than just
the SESSION variable for its own connection. If we add a
SESSION variable with the same name as a GLOBAL variable,
a client that intends to change the GLOBAL variable might
find only its own SESSION variable changed.
Structured System Variables
A structured variable differs from a regular system
variable in two respects:
o Its value is a structure with components that specify
server parameters considered to be closely related.
o There might be several instances of a given type of
structured variable. Each one has a different name and
refers to a different resource maintained by the
server.
MySQL 5.0 supports one structured variable type, which
specifies parameters governing the operation of key
caches. A key cache structured variable has these
components:
o key_buffer_size
o key_cache_block_size
o key_cache_division_limit
o key_cache_age_threshold
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 4.6, "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:
o 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.
o 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.
o 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.
o global, session, and local are not legal instance
names. This avoids a conflict with notation such as
@@global.var_name for referring to non-structured
system variables.
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.
Dynamic System Variables
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 the section called
"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.3, "SET Syntax", discusses these
options.
Variables that have a type of "string" take a string
value. Variables that have a type of "numeric" take a
numeric value. Variables that have a type of "boolean" can
be set to 0, 1, ON or OFF. (If you set them on the command
line or in an option file, use the numeric values.)
Variables that are marked as "enumeration" normally should
be set to one of the available values for the variable,
but can also be set to the number that corresponds to the
desired enumeration value. For enumerated system
variables, the first enumeration value corresponds to 0.
This differs from ENUM columns, for which the first
enumeration value corresponds to 1. Variable NameValue
TypeTypeautocommitbooleanSESSIONbig_tablesbooleanSESSIONbinlog_cache_sizenumericGLOBALbulk_insert_buffer_sizenumericGLOBAL
| SESSIONcharacter_set_clientstringGLOBAL |
SESSIONcharacter_set_connectionstringGLOBAL | SESSION
character_set_filesystemstringGLOBAL |
SESSIONcharacter_set_resultsstringGLOBAL |
SESSIONcharacter_set_serverstringGLOBAL |
SESSIONcollation_connectionstringGLOBAL |
SESSIONcollation_serverstringGLOBAL |
SESSIONcompletion_typenumericGLOBAL |
SESSIONconcurrent_insertbooleanGLOBALconnect_timeoutnumericGLOBALconvert_character_setstringGLOBAL
| SESSIONdefault_week_formatnumericGLOBAL |
SESSIONdelay_key_writeOFF | ON |
ALLGLOBALdelayed_insert_limitnumericGLOBALdelayed_insert_timeoutnumericGLOBALdelayed_queue_sizenumericGLOBALdiv_precision_incrementnumericGLOBAL
| SESSIONengine_condition_pushdownbooleanGLOBAL |
SESSIONerror_countnumericSESSIONexpire_logs_daysnumericGLOBALflushbooleanGLOBALflush_timenumericGLOBALforeign_key_checksbooleanSESSIONft_boolean_syntaxnumericGLOBALgroup_concat_max_lennumericGLOBAL
|
SESSIONidentitynumericSESSIONinnodb_autoextend_incrementnumericGLOBALinnodb_commit_concurrencynumericGLOBALinnodb_concurrency_ticketsnumericGLOBALinnodb_max_dirty_pages_pctnumericGLOBALinnodb_max_purge_lagnumericGLOBALinnodb_support_xabooleanGLOBAL
|
SESSIONinnodb_sync_spin_loopsnumericGLOBALinnodb_table_locksbooleanGLOBAL
|
SESSIONinnodb_thread_concurrencynumericGLOBALinnodb_thread_sleep_delaynumericGLOBALinsert_idbooleanSESSIONinteractive_timeoutnumericGLOBAL
| SESSIONjoin_buffer_sizenumericGLOBAL |
SESSIONkey_buffer_sizenumericGLOBAL
last_insert_idnumericSESSIONlocal_infilebooleanGLOBALlog_warningsnumericGLOBALlong_query_timenumericGLOBAL
| SESSIONlow_priority_updatesbooleanGLOBAL |
SESSIONmax_allowed_packetnumericGLOBAL |
SESSIONmax_binlog_cache_sizenumericGLOBALmax_binlog_sizenumericGLOBALmax_connect_errorsnumericGLOBALmax_connectionsnumericGLOBALmax_delayed_threadsnumericGLOBALmax_error_countnumericGLOBAL
| SESSIONmax_heap_table_sizenumericGLOBAL |
SESSIONmax_insert_delayed_threadsnumericGLOBALmax_join_sizenumericGLOBAL
|
SESSIONmax_relay_log_sizenumericGLOBALmax_seeks_for_keynumericGLOBAL
| SESSIONmax_sort_lengthnumericGLOBAL |
SESSIONmax_tmp_tablesnumericGLOBAL |
SESSIONmax_user_connectionsnumericGLOBALmax_write_lock_countnumericGLOBALmyisam_stats_methodenumGLOBAL
| SESSIONmulti_read_rangenumericGLOBAL |
SESSIONmyisam_data_pointer_sizenumericGLOBALlog_bin_trust_function_creatorsbooleanGLOBALmyisam_max_sort_file_sizenumericGLOBAL
| SESSIONmyisam_repair_threadsnumericGLOBAL |
SESSIONmyisam_sort_buffer_sizenumericGLOBAL |
SESSIONnet_buffer_lengthnumericGLOBAL |
SESSIONnet_read_timeoutnumericGLOBAL |
SESSIONnet_retry_countnumericGLOBAL |
SESSIONnet_write_timeoutnumericGLOBAL |
SESSIONold_passwordsnumericGLOBAL |
SESSIONoptimizer_prune_levelnumericGLOBAL |
SESSIONoptimizer_search_depthnumericGLOBAL |
SESSIONpreload_buffer_sizenumericGLOBAL |
SESSIONquery_alloc_block_sizenumericGLOBAL |
SESSIONquery_cache_limitnumericGLOBALquery_cache_sizenumericGLOBALquery_cache_typeenumerationGLOBAL
| SESSIONquery_cache_wlock_invalidatebooleanGLOBAL |
SESSIONquery_prealloc_sizenumericGLOBAL |
SESSIONrange_alloc_block_sizenumericGLOBAL |
SESSIONread_buffer_sizenumericGLOBAL |
SESSIONread_onlynumericGLOBALread_rnd_buffer_sizenumericGLOBAL
|
SESSIONrpl_recovery_ranknumericGLOBALsafe_show_databasebooleanGLOBALsecure_authbooleanGLOBALserver_idnumericGLOBALslave_compressed_protocolbooleanGLOBALslave_net_timeoutnumericGLOBALslave_transaction_retriesnumericGLOBALslow_launch_timenumericGLOBALsort_buffer_sizenumericGLOBAL
|
SESSIONsql_auto_is_nullbooleanSESSIONsql_big_selectsbooleanSESSIONsql_big_tablesbooleanSESSIONsql_buffer_resultbooleanSESSIONsql_log_binbooleanSESSIONsql_log_offbooleanSESSIONsql_log_updatebooleanSESSIONsql_low_priority_updatesbooleanGLOBAL
| SESSIONsql_max_join_sizenumericGLOBAL |
SESSIONsql_modeenumerationGLOBAL |
SESSIONsql_notesbooleanSESSIONsql_quote_show_createbooleanSESSIONsql_safe_updatesbooleanSESSIONsql_select_limitnumericSESSIONsql_slave_skip_counternumericGLOBALupdatable_views_with_limitenumerationGLOBAL
|
SESSIONsql_warningsbooleanSESSIONsync_binlognumericGLOBALsync_frmbooleanGLOBALstorage_engineenumerationGLOBAL
|
SESSIONtable_cachenumericGLOBALtable_typeenumerationGLOBAL
|
SESSIONthread_cache_sizenumericGLOBALtime_zonestringGLOBAL
|
SESSIONtimestampbooleanSESSIONtmp_table_sizeenumerationGLOBAL
| SESSIONtransaction_alloc_block_sizenumericGLOBAL |
SESSIONtransaction_prealloc_sizenumericGLOBAL |
SESSIONtx_isolationenumerationGLOBAL |
SESSIONunique_checksbooleanSESSIONwait_timeoutnumericGLOBAL
| SESSIONwarning_countnumericSESSION.SH "SERVER STATUS
VARIABLES"
The server maintains many status variables that provide
information about its operation. You can view these
variables and their values by using the SHOW STATUS
statement:
mysql> SHOW 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 status variables have the following meanings.
Variables with no version indicated were already present
prior to MySQL 5.0. For information regarding their
implementation history, see MySQL 3.23, 4.0, 4.1 Reference
Manual.
o Aborted_clients
The number of connections that were aborted because the
client died without closing the connection properly.
See Section 2.10, "Communication Errors and Aborted
Connections".
o Aborted_connects
The number of failed attempts to connect to the MySQL
server. See Section 2.10, "Communication Errors and
Aborted Connections".
o Binlog_cache_disk_use
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.
o Binlog_cache_use
The number of transactions that used the temporary
binary log cache.
o Bytes_received
The number of bytes received from all clients.
o Bytes_sent
The number of bytes sent to all clients.
o Com_xxx
The Com_xxx statement counter variables indicate the
number of times each xxx statement has been executed.
There is one status variable for each type of
statement. For example, Com_delete and Com_insert count
DELETE and INSERT statements, respectively.
The Com_stmt_xxx status variables were added in 5.0.8:
o Com_stmt_prepare
o Com_stmt_execute
o Com_stmt_fetch
o Com_stmt_send_long_data
o Com_stmt_reset
o Com_stmt_close
Those variables stand for prepared statement commands.
Their names refer to the COM_xxx 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, Com_stmt_prepare,
Com_stmt_execute and Com_stmt_close also increase for
PREPARE, EXECUTE, or DEALLOCATE PREPARE, respectively.
Additionally, the values of the older (available since
MySQL 4.1.3) statement counter variables
Com_prepare_sql, Com_execute_sql, and Com_dealloc_sql
increase for the PREPARE, EXECUTE, and DEALLOCATE
PREPARE statements. Com_stmt_fetch stands for the
total number of network round-trips issued when
fetching from cursors.
All of the Com_stmt_xxx 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.
o Compression
Whether the client connection uses compression in the
client/server protocol. Added in MySQL 5.0.16.
o Connections
The number of connection attempts (successful or not)
to the MySQL server.
o Created_tmp_disk_tables
The number of temporary tables on disk created
automatically by the server while executing statements.
o Created_tmp_files
How many temporary files mysqld has created.
o Created_tmp_tables
The number of in-memory temporary tables created
automatically by the server while executing statements.
If Created_tmp_disk_tables is large, you may want to
increase the tmp_table_size value to cause temporary
tables to be memory-based instead of disk-based.
o Delayed_errors
The number of rows written with INSERT DELAYED for
which some error occurred (probably duplicate key).
o Delayed_insert_threads
The number of INSERT DELAYED handler threads in use.
o Delayed_writes
The number of INSERT DELAYED rows written.
o Flush_commands
The number of executed FLUSH statements.
o Handler_commit
The number of internal COMMIT statements.
o Handler_discover
The MySQL server can ask the NDB Cluster storage engine
if it knows about a table with a given name. This is
called discovery. Handler_discover indicates the
number of times that tables have been discovered via
this mechanism.
o Handler_delete
The number of times that rows have been deleted from
tables.
o Handler_read_first
The number of times the first entry was read from an
index. If this value is high, it suggests that the
server is doing a lot of full index scans; for example,
SELECT col1 FROM foo, assuming that col1 is indexed.
o Handler_read_key
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.
o Handler_read_next
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.
o Handler_read_prev
The number of requests to read the previous row in key
order. This read method is mainly used to optimize
ORDER BY ... DESC.
o Handler_read_rnd
The number of requests to read a row based on a fixed
position. This value is high if you are doing a lot of
queries that require sorting of the result. You
probably have a lot of queries that require MySQL to
scan entire tables or you have joins that don't use
keys properly.
o Handler_read_rnd_next
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.
o Handler_rollback
The number of internal ROLLBACK statements.
o Handler_update
The number of requests to update a row in a table.
o Handler_write
The number of requests to insert a row in a table.
o Innodb_buffer_pool_pages_data
The number of pages containing data (dirty or clean).
Added in MySQL 5.0.2.
o Innodb_buffer_pool_pages_dirty
The number of pages currently dirty. Added in MySQL
5.0.2.
o Innodb_buffer_pool_pages_flushed
The number of buffer pool page-flush requests. Added in
MySQL 5.0.2.
o Innodb_buffer_pool_pages_free
The number of free pages. Added in MySQL 5.0.2.
o Innodb_buffer_pool_pages_latched
The number of latched pages in InnoDB buffer pool.
These are pages currently being read or written or that
cannot be flushed or removed for some other reason.
Added in MySQL 5.0.2.
o Innodb_buffer_pool_pages_misc
The number of pages that are busy because they have
been allocated for administrative overhead such as row
locks or the adaptive hash index. This value can also
be calculated as Innodb_buffer_pool_pages_total -
Innodb_buffer_pool_pages_free -
Innodb_buffer_pool_pages_data. Added in MySQL 5.0.2.
o Innodb_buffer_pool_pages_total
The total size of buffer pool, in pages. Added in MySQL
5.0.2.
o Innodb_buffer_pool_read_ahead_rnd
The number of "random" read-aheads initiated by InnoDB.
This happens when a query scans a large portion of a
table but in random order. Added in MySQL 5.0.2.
o Innodb_buffer_pool_read_ahead_seq
The number of sequential read-aheads initiated by
InnoDB. This happens when InnoDB does a sequential full
table scan. Added in MySQL 5.0.2.
o Innodb_buffer_pool_read_requests
The number of logical read requests InnoDB has done.
Added in MySQL 5.0.2.
o Innodb_buffer_pool_reads
The number of logical reads that InnoDB could not
satisfy from the buffer pool and had to do a
single-page read. Added in MySQL 5.0.2.
o Innodb_buffer_pool_wait_free
Normally, writes to the InnoDB buffer pool happen in
the background. However, if it is necessary to read or
create a page and no clean pages are available, it is
also necessary to wait for pages to be flushed first.
This counter counts instances of these waits. If the
buffer pool size has been set properly, this value
should be small. Added in MySQL 5.0.2.
o Innodb_buffer_pool_write_requests
The number writes done to the InnoDB buffer pool. Added
in MySQL 5.0.2.
o Innodb_data_fsyncs
The number of fsync() operations so far. Added in MySQL
5.0.2.
o Innodb_data_pending_fsyncs
The current number of pending fsync() operations. Added
in MySQL 5.0.2.
o Innodb_data_pending_reads
The current number of pending reads. Added in MySQL
5.0.2.
o Innodb_data_pending_writes
The current number of pending writes. Added in MySQL
5.0.2.
o Innodb_data_read
The amount of data read so far, in bytes. Added in
MySQL 5.0.2.
o Innodb_data_reads
The total number of data reads. Added in MySQL 5.0.2.
o Innodb_data_writes
The total number of data writes. Added in MySQL 5.0.2.
o Innodb_data_written
The amount of data written so far, in bytes. Added in
MySQL 5.0.2.
o Innodb_dblwr_writes, Innodb_dblwr_pages_written
The number of doublewrite operations that have been
performed and the number of pages that have been
written for this purpose. Added in MySQL 5.0.2. See
Section 2.14.1, "Disk I/O".
o Innodb_log_waits
The number of times that the log buffer was too small
and a wait was required for it to be flushed before
continuing. Added in MySQL 5.0.2.
o Innodb_log_write_requests
The number of log write requests. Added in MySQL 5.0.2.
o Innodb_log_writes
The number of physical writes to the log file. Added in
MySQL 5.0.2.
o Innodb_os_log_fsyncs
The number of fsync() writes done to the log file.
Added in MySQL 5.0.2.
o Innodb_os_log_pending_fsyncs
The number of pending log file fsync() operations.
Added in MySQL 5.0.2.
o Innodb_os_log_pending_writes
The number of pending log file writes. Added in MySQL
5.0.2.
o Innodb_os_log_written
The number of bytes written to the log file. Added in
MySQL 5.0.2.
o Innodb_page_size
The compiled-in InnoDB page size (default 16KB). Many
values are counted in pages; the page size allows them
to be easily converted to bytes. Added in MySQL 5.0.2.
o Innodb_pages_created
The number of pages created. Added in MySQL 5.0.2.
o Innodb_pages_read
The number of pages read. Added in MySQL 5.0.2.
o Innodb_pages_written
The number of pages written. Added in MySQL 5.0.2.
o Innodb_row_lock_current_waits
The number of row locks currently being waited for.
Added in MySQL 5.0.3.
o Innodb_row_lock_time
The total time spent in acquiring row locks, in
milliseconds. Added in MySQL 5.0.3.
o Innodb_row_lock_time_avg
The average time to acquire a row lock, in
milliseconds. Added in MySQL 5.0.3.
o Innodb_row_lock_time_max
The maximum time to acquire a row lock, in
milliseconds. Added in MySQL 5.0.3.
o Innodb_row_lock_waits
The number of times a row lock had to be waited for.
Added in MySQL 5.0.3.
o Innodb_rows_deleted
The number of rows deleted from InnoDB tables. Added in
MySQL 5.0.2.
o Innodb_rows_inserted
The number of rows inserted into InnoDB tables. Added
in MySQL 5.0.2.
o Innodb_rows_read
The number of rows read from InnoDB tables. Added in
MySQL 5.0.2.
o Innodb_rows_updated
The number of rows updated in InnoDB tables. Added in
MySQL 5.0.2.
o Key_blocks_not_flushed
The number of key blocks in the key cache that have
changed but have not yet been flushed to disk.
o Key_blocks_unused
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 the
section called "SERVER SYSTEM VARIABLES".
o Key_blocks_used
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.
o Key_read_requests
The number of requests to read a key block from the
cache.
o Key_reads
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.
o Key_write_requests
The number of requests to write a key block to the
cache.
o Key_writes
The number of physical writes of a key block to disk.
o Last_query_cost
The total cost of the last compiled query as computed
by the query optimizer. This is useful for comparing
the cost of different query plans for the same query.
The default value of 0 means that no query has been
compiled yet. This variable was added in MySQL 5.0.1,
with a default value of -1. In MySQL 5.0.7, the default
was changed to 0; also in version 5.0.7, the scope of
Last_query_cost was changed to session rather than
global.
Prior to MySQL 5.0.16, this variable was not updated
for queries served from the query cache.
o Max_used_connections
The maximum number of connections that have been in use
simultaneously since the server started.
o Not_flushed_delayed_rows
The number of rows waiting to be written in INSERT
DELAY queues.
o Open_files
The number of files that are open.
o Open_streams
The number of streams that are open (used mainly for
logging).
o Open_tables
The number of tables that are open.
o Opened_tables
The number of tables that have been opened. If
Opened_tables is big, your table_cache value is
probably too small.
o Qcache_free_blocks
The number of free memory blocks in the query cache.
o Qcache_free_memory
The amount of free memory for the query cache.
o Qcache_hits
The number of query cache hits.
o Qcache_inserts
The number of queries added to the query cache.
o Qcache_lowmem_prunes
The number of queries that were deleted from the query
cache because of low memory.
o Qcache_not_cached
The number of non-cached queries (not cacheable, or not
cached due to the query_cache_type setting).
o Qcache_queries_in_cache
The number of queries registered in the query cache.
o Qcache_total_blocks
The total number of blocks in the query cache.
o Questions
The number of statements that clients have sent to the
server.
o Rpl_status
The status of failsafe replication (not yet
implemented).
o Select_full_join
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.
o Select_full_range_join
The number of joins that used a range search on a
reference table.
o Select_range
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.
o Select_range_check
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.
o Select_scan
The number of joins that did a full scan of the first
table.
o Slave_open_temp_tables
The number of temporary tables that the slave SQL
thread currently has open.
o Slave_running
This is ON if this server is a slave that is connected
to a master.
o Slave_retried_transactions
The total number of times since startup that the
replication slave SQL thread has retried transactions.
This variable was added in version 5.0.4.
o Slow_launch_threads
The number of threads that have taken more than
slow_launch_time seconds to create.
o Slow_queries
The number of queries that have taken more than
long_query_time seconds. See Section 10.4, "The Slow
Query Log".
o Sort_merge_passes
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.
o Sort_range
The number of sorts that were done using ranges.
o Sort_rows
The number of sorted rows.
o Sort_scan
The number of sorts that were done by scanning the
table.
o Ssl_xxx
Variables used for SSL connections.
o Table_locks_immediate
The number of times that a table lock was acquired
immediately.
o Table_locks_waited
The number of times that a table lock could not be
acquired 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.
o Threads_cached
The number of threads in the thread cache.
o Threads_connected
The number of currently open connections.
o Threads_created
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 hit rate can be
calculated as Threads_created/Connections.
o Threads_running
The number of threads that are not sleeping.
o Uptime
The number of seconds that the server has been up.
THE SERVER SQL MODE
The MySQL server can operate in different SQL modes, and
can apply these modes differently for different clients.
This capability enables each application to tailor the
server's operating mode to its own requirements.
Modes define what SQL syntax MySQL should support and what
kind of data validation checks it should perform. This
makes it easier to use MySQL in different environments and
to use MySQL together with other database servers.
You can set the default SQL mode by starting mysqld with
the --sql-mode="modes" option. modes is a list of
different modes separated by comma (`,') characters. The
default value is empty (no modes set). The modes value
also can be empty (--sql-mode="") if you want to clear it
explicitly.
You can change the SQL mode at runtime by using a SET
[GLOBAL|SESSION] sql_mode='modes' statement to set the
sql_mode system value. 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. Any
client can change its own session sql_mode value at any
time.
You can retrieve the current global or session sql_mode
value with the following statements:
SELECT @@global.sql_mode;
SELECT @@session.sql_mode;
The most important sql_mode values are probably these:
o ANSI
Change syntax and behavior to be more conformant to
standard SQL.
o STRICT_TRANS_TABLES
If a value could not be inserted as given into a
transactional table, abort the statement. For a
non-transactional table, abort the statement if the
value occurs in a single-row statement or the first row
of a multiple-row statement. More detail is given later
in this section. (Implemented in MySQL 5.0.2)
o TRADITIONAL
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. Note: The INSERT/UPDATE aborts as
soon as the error is noticed. This may not be what you
want if you are using a non-transactional storage
engine, because data changes made prior to the error
are not be rolled back, resulting in a "partially done"
update. (Added in MySQL 5.0.2)
When this manual refers to "strict mode," it means a mode
where at least one of STRICT_TRANS_TABLES or
STRICT_ALL_TABLES is enabled.
The following list describes all supported modes:
o ALLOW_INVALID_DATES
Don't do 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.
This mode is implemented in MySQL 5.0.2. Before 5.0.2,
this was the default MySQL date-handling mode. As of
5.0.2, 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 allow such dates,
enable ALLOW_INVALID_DATES.
o ANSI_QUOTES
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 quotes to quote a literal string,
because it is interpreted as an identifier.
o ERROR_FOR_DIVISION_BY_ZERO
Produce an error in strict mode (otherwise a warning)
when we encounter a division by zero (or MOD(X,0))
during an INSERT or UPDATE. If this mode is not
enabled, MySQL instead returns NULL for divisions by
zero. If used in INSERT IGNORE or UPDATE IGNORE, MySQL
generates a warning for divisions by zero, but the
result of the operation is NULL. (Implemented in MySQL
5.0.2)
o HIGH_NOT_PRECEDENCE
From MySQL 5.0.2 on, 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). Before MySQL
5.0.2, the expression is parsed as (NOT a) BETWEEN b
AND c. The old higher-precedence behavior can be
obtained by enabling the HIGH_NOT_PRECEDENCE SQL mode.
(Added in MySQL 5.0.2)
mysql> SET sql_mode = '';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
-> 0
mysql> SET sql_mode = 'broken_not';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
-> 1
o IGNORE_SPACE
Allow spaces between a function name and the `('
character. This forces all function names to be treated
as reserved words. As a result, if you want to access
any database, table, or column name that is a reserved
word, you must quote it. For example, because there is
a USER() function, the name of the user table in the
mysql database and the User column in that table become
reserved, so you must quote them:
SELECT "User" FROM mysql."user";
The IGNORE_SPACE SQL mode applies to built-in
functions, not to stored routines. it is always
allowable to have spaces after a routine name,
regardless of whether IGNORE_SPACE is enabled.
o NO_AUTO_CREATE_USER
Prevent GRANT from automatically creating new users if
it would otherwise do so, unless a non-empty password
also is specified. (Added in MySQL 5.0.2)
o NO_AUTO_VALUE_ON_ZERO
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.
o NO_BACKSLASH_ESCAPES
Disable the use of the backslash character (`\') as an
escape character within strings. With this mode
enabled, backslash becomes any ordinary character like
any other. (Implemented in MySQL 5.0.1)
o NO_DIR_IN_CREATE
When creating a table, ignore all INDEX DIRECTORY and
DATA DIRECTORY directives. This option is useful on
slave replication servers.
o NO_ENGINE_SUBSTITUTION
Prevents automatic substitution of the default storage
engine when a statement such as CREATE TABLE specifies
a storage engine that is disabled or not compiled in.
(Implemented in MySQL 5.0.8)
o NO_FIELD_OPTIONS
Do not print MySQL-specific column options in the
output of SHOW CREATE TABLE. This mode is used by
mysqldump in portability mode.
o NO_KEY_OPTIONS
Do not print MySQL-specific index options in the output
of SHOW CREATE TABLE. This mode is used by mysqldump in
portability mode.
o NO_TABLE_OPTIONS
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.
o NO_UNSIGNED_SUBTRACTION
In subtraction operations, do not mark the result as
UNSIGNED if one of the operands is unsigned. Note that
this makes BIGINT UNSIGNED not 100% usable in all
contexts. See Section 8, "Cast Functions and
Operators".
o NO_ZERO_DATE
In strict mode, don't allow '0000-00-00' as a valid
date. You can still insert zero dates with the IGNORE
option. When not in strict mode, the date is accepted
but a warning is generated. (Added in MySQL 5.0.2)
o NO_ZERO_IN_DATE
In strict mode, don't accept dates where the month or
day part is 0. If used with the IGNORE option, MySQL
inserts a '0000-00-00' date for any such date. When not
in strict mode, the date is accepted but a warning is
generated. (Added in MySQL 5.0.2)
o ONLY_FULL_GROUP_BY
Do not allow queries for which the GROUP BY clause
refers to a column that is not present in the output
column list.
o PIPES_AS_CONCAT
Treat || as a string concatenation operator (same as
CONCAT()) rather than as a synonym for OR.
o REAL_AS_FLOAT
Treat REAL as a synonym for FLOAT. By default, MySQL
treats REAL as a synonym for DOUBLE.
o STRICT_ALL_TABLES
Enable strict mode for all storage engines. Invalid
data values are rejected. Additional detail follows.
(Added in MySQL 5.0.2)
o STRICT_TRANS_TABLES
Enable strict mode for transactional storage engines,
and when possible for non-transactional storage
engines. Additional details follow. (Implemented in
MySQL 5.0.2)
Strict mode controls how MySQL handles input values that
are invalid or missing. 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 column that has no explicit DEFAULT clause in
its definition.
For transactional tables, an error occurs for invalid or
missing values in a statement when either of the
STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are
enabled. The statement is aborted and rolled back.
For non-transactional 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
option is enabled:
o For STRICT_ALL_TABLES, MySQL returns an error and
ignores the rest of the rows. However, in this case,
the earlier rows still have been inserted or updated.
This means that you might get a partial update, which
might not be what you want. To avoid this, it's best to
use single-row statements because these can be aborted
without changing the table.
o For STRICT_TRANS_TABLES, MySQL converts an invalid
value to the closest valid value for the column and
insert 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 1.4, "Data
Type Default Values".
Strict mode disallows invalid date values such as
'2004-04-31'. It does not disallow dates with zero parts
such as '2004-04-00' or "zero" dates. To disallow these as
well, enable the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL
modes in addition to strict mode.
If you are not using strict mode (that is, neither
STRICT_TRANS_TABLES nor STRICT_ALL_TABLES is enabled),
MySQL inserts adjusted values for invalid or missing
values and produces warnings. In strict mode, you can
produce this behavior by using INSERT IGNORE or UPDATE
IGNORE. See Section 5.4.25, "SHOW WARNINGS Syntax".
The following special modes are provided as shorthand for
combinations of mode values from the preceding list. All
are available in MySQL 5.0 beginning with version 5.0.0,
except for TRADITIONAL, which was implemented in MySQL
5.0.2.
The descriptions include all mode values that are
available in the most recent version of MySQL. For older
versions, a combination mode does not include individual
mode values that are not available except in newer
versions.
o ANSI
Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT,
ANSI_QUOTES, IGNORE_SPACE. Before MySQL 5.0.3, ANSI
also includes ONLY_FULL_GROUP_BY. See Section 9.3,
"Running MySQL in ANSI Mode".
o DB2
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES,
IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS.
o MAXDB
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES,
IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.
o MSSQL
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES,
IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS.
o MYSQL323
Equivalent to NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE.
o MYSQL40
Equivalent to NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE.
o ORACLE
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES,
IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.
o POSTGRESQL
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES,
IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS,
NO_FIELD_OPTIONS.
o TRADITIONAL
Equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES,
NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER.
THE MYSQL SERVER SHUTDOWN PROCESS
The server shutdown process takes place as follows:
1. The shutdown process is initiated.
Server shutdown can be 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.
2. 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
3. The server stops accepting new connections.
To prevent new activity from being initiated during
shutdown, the server stops accepting new client
connections. It does this by closing the network
connections to which it normally listens for
connections: the TCP/IP port, the Unix socket file, the
Windows named pipe, and shared memory on Windows.
4. The server terminates current activity.
For each thread that is associated with a client
connection, the connection to the client is broken and
the thread is marked 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 5.5.3, "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 non-transactional 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, threads
associated with currently connected slaves are treated
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, the I/O
and SQL threads, if active, are stopped before client
threads are marked as killed. The SQL thread is allowed
to finish its current statement (to avoid causing
replication problems), and then stops. If the SQL
thread was in the middle of a transaction at this
point, the transaction is rolled back.
5. Storage engines are shut down or closed.
At this stage, the table cache is flushed and all open
tables are closed.
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 (starting from 5.0.5: unless
innodb_fast_shutdown is 2), writes the current LSN to
the tablespace, and terminates its own internal
threads.
6. The server exits.
SEE ALSO
msql2mysql(1), myisamchk(1), myisamlog(1), myisampack(1),
mysql(1), mysql.server(1), mysql_config(1),
mysql_fix_privilege_tables(1), mysql_upgrade(1),
mysql_zap(1), mysqlaccess(1), mysqladmin(1),
mysqlbinlog(1), mysqlcheck(1), mysqld_multi(1),
mysqld_safe(1), mysqldump(1), mysqlhotcopy(1),
mysqlimport(1), mysqlmanager(1), mysqlshow(1), perror(1),
replace(1), safe_mysqld(1)
For more information, please refer to the MySQL Reference
Manual, which may already be installed locally and which
is also available online at http://dev.mysql.com/doc/.
AUTHOR
MySQL AB (http://www.mysql.com/). This software comes
with no warranty.
MySQL 5.0 03/04/2006 FBMYSQLDFR(1)
Man(1) output converted with
man2html