Table of Contents
Access denied ErrorsEnd of Product LifecycleActive development and support for MySQL database server versions 3.23, 4.0, and 4.1 have ended. For MySQL 4.0 and 4.1, there is still extended support available, though. For details, see http://www.mysql.com/company/legal/lifecycle/#calendar.According to the MySQL Lifecycle Policy (see http://www.mysql.com/company/legal/lifecycle/#policy), only Security Level 1 issues will still be fixed for those versions. Please consider upgrading to a recent version (MySQL 5.0 or 5.1).
This chapter covers topics that deal with administering a MySQL installation:
Configuring the server
Managing user accounts
Performing backups
The server log files
The query cache
The MySQL server, mysqld, is the main program that does most of the work in a MySQL installation. The server is accompanied by several related scripts that perform setup operations when you install MySQL or that assist you in starting and stopping the server. This section provides an overview of the server and related programs. The following sections provide more detailed information about each of these programs.
Each MySQL program takes many different options. Most programs
provide a --help option that you can use to get a
description of the program's different options. For example, try
mysqld --help.
You can override default option values for MySQL programs by specifying options on the command line or in an option file. Section 4.3, “Specifying Program Options�.
The following list briefly describes the MySQL server and server-related programs:
The SQL daemon (that is, the MySQL server). To use client programs, mysqld must be running, because clients gain access to databases by connecting to the server. See Section 5.2, “mysqld — The MySQL Server�.
A version of the server that includes additional features. See Section 5.3, “The mysqld-max Extended MySQL Server�.
A server startup script. mysqld_safe attempts to start mysqld-max if it exists, and mysqld otherwise. See Section 5.4.1, “mysqld_safe — MySQL Server Startup Script�.
A server startup script. This script is used on systems that use System V-style run directories containing scripts that start system services for particular run levels. It invokes mysqld_safe to start the MySQL server. See Section 5.4.2, “mysql.server — MySQL Server Startup Script�.
A server startup script that can start or stop multiple servers installed on the system. See Section 5.4.3, “mysqld_multi — Manage Multiple MySQL Servers�.
This script creates the MySQL database and initializes the grant tables with default privileges. It is usually executed only once, when first installing MySQL on a system. See Section 2.10.2, “Unix Post-Installation Procedures�.
This script is used after a MySQL upgrade operation. It updates the grant tables with any changes that have been made in newer versions of MySQL. See Section 5.5.1, “mysql_fix_privilege_tables — Upgrade MySQL System Tables�.
This program loads the time zone tables in the
mysql database using the contents of the
host system zoneinfo database (the set
of files describing time zones). SQL. See
Section 5.5.3, “mysql_tzinfo_to_sql — Load the Time Zone Tables�.
There are several other programs that are run on the server host:
mysqld is the MySQL server. The following discussion covers these MySQL server configuration topics:
Startup options that the server supports
Server system variables
Server status variables
How to set the server SQL mode
The server shutdown process
Note: Not all storage engines
(also known in older versions of MySQL as “table
types�) are supported by all MySQL server binaries and
configurations. To find out how to determine which storage
engines are supported by your MySQL server installation, see
Section 13.5.4.8, “SHOW ENGINES Syntax�.
When you start the mysqld server, you can specify program options using any of the methods described in Section 4.3, “Specifying Program Options�. The most common methods are to provide options in an option file or on the command line. However, in most cases it is desirable to make sure that the server uses the same options each time it runs. The best way to ensure this is to list them in an option file. See Section 4.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
[
groups, where xxxxx_SERVER]xxxxx is the name of
the application into which the server is embedded.
mysqld accepts many command options. For a
list, execute mysqld --help. Before MySQL
4.1.1, --help prints the full help message.
As of 4.1.1, it prints a brief message; to see the full list,
use mysqld --verbose --help.
The following list shows some of the most common server options. Additional options are described in other sections:
Options that affect security: See Section 5.6.3, “Security-Related mysqld Options�.
SSL-related options: See Section 5.8.7.3, “SSL Command Options�.
Binary log control options: See Section 5.11.4, “The Binary Log�.
Replication-related options: See Section 6.8, “Replication Startup Options�.
Options specific to particular storage engines: See
Section 14.1.1, “MyISAM Startup Options�,
Section 14.5.3, “BDB Startup Options�,
Section 14.2.5, “InnoDB Startup Options and System Variables�, and
Section 15.6.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.
Display a short help message and exit. Before MySQL 4.1.1,
--help displays the full help message. As
of 4.1.1, it displays an abbreviated message only. Use
both the --verbose and
--help options to see the full message.
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 MySQL 4.0.24, and 4.1.10a.
See Section 19.2.4.6, “User-Defined Function Security Precautions�.
Use standard (ANSI) SQL syntax instead of MySQL syntax.
For more precise control over the server SQL mode, use the
--sql-mode option instead. See
Section 1.9.3, “Running MySQL in ANSI Mode�, and
Section 5.2.5, “SQL Modes�.
The path to the MySQL installation directory. All paths are usually resolved relative to this directory.
Allow large result sets by saving all temporary sets in files. This option prevents most “table full� errors, but also slows down queries for which in-memory tables would suffice. Since MySQL 3.23.2, the server is able to handle large result sets automatically by using memory for small temporary tables and switching to disk tables where necessary.
The IP address to bind to.
This option is used by the mysql_install_db script to create the MySQL privilege tables without having to start a full MySQL server.
The directory where character sets are installed. See Section 5.10.1, “The Character Set Used for Data and Sorting�.
--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 4.1 and higher behave like MySQL 4.0.
This option was added in MySQL 4.1.15.
--character-set-server=,
charset_name-C
charset_name
Use charset_name as the default
server character set. See
Section 5.10.1, “The Character Set Used for Data and Sorting�. If you use this option
to specify a non-default character set, you should also
use --collation-server to specify the
collation. This option is available as of MySQL 4.1.3.
Put the mysqld server in a closed
environment during startup by using the
chroot() system call. This is a
recommended security measure as of MySQL 4.0. (MySQL 3.23
is not able to provide a chroot() jail
that is 100% closed.) Note that use of this option
somewhat limits LOAD DATA INFILE and
SELECT ... INTO OUTFILE.
--collation-server=
collation_name
Use collation_name as the
default server collation. This option is available as of
MySQL 4.1.3. See Section 5.10.1, “The Character Set Used for Data and Sorting�.
(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.
Write a core file if mysqld dies. For
some systems, you must also specify the
--core-file-size option to
mysqld_safe. See
Section 5.4.1, “mysqld_safe — MySQL Server Startup Script�. Note that on some systems,
such as Solaris, you do not get a core file if you are
also using the --user option.
The path to the data directory.
--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,.
The default is file_name''d:t:i:o,mysqld.trace'.
See Section E.1.2, “Creating Trace Files�.
--default-character-set=,
charset_name-C
charset_name
Use charset_name as the default
character set. This option is deprecated in favor of
--character-set-server as of MySQL 4.1.3.
See Section 5.10.1, “The Character Set Used for Data and Sorting�.
--default-collation=
collation_name
Use collation_name as the
default collation. This option is deprecated in favor of
--collation-server as of MySQL 4.1.3. See
Section 5.10.1, “The Character Set Used for Data and Sorting�.
This option is a synonym for
--default-table-type. It is available as
of MySQL 4.1.2.
Set the default table type (storage engine) for tables. See Chapter 14, Storage Engines and Table Types.
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. This
option is available as of MySQL 4.1.3.
--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. Available as of MySQL
4.0.3. See Section 7.5.2, “Tuning Server Parameters�, and
Section 14.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.
--delay-key-write-for-all-tables
Old form of --delay-key-write=ALL for use
prior to MySQL 4.0.3. As of 4.0.3, use
--delay-key-write instead.
Read the default DES keys from this file. These keys are
used by the DES_ENCRYPT() and
DES_DECRYPT() functions.
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.
--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!
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 was
named --enable-locking before MySQL
4.0.3.
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:
You should not use the query cache for queries that use tables that are updated by another process.
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.)
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 A.4.2, “What to Do If MySQL Keeps Crashing�.
Read SQL statements from this file at startup. Each statement must be on a single line and should not include comments.
Adds consistency guarantees between the content of
InnoDB tables and the binary log. See
Section 5.11.4, “The Binary Log�.
--innodb-
xxx
The InnoDB options are listed in
Section 14.2.5, “InnoDB Startup Options and System Variables�.
--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 5.10.2, “Setting the Error Message Language�.
--log[=,
file_name]-l [
file_name]
Log connections and SQL statements received from clients
to this file. See Section 5.11.2, “The General Query Log�. If you omit
the filename, MySQL uses
host_name.log
Enable binary logging. The server logs all statements that change data to the binary log, which is used for backup and replication. See Section 5.11.4, “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 A.8.4, “Open Issues in MySQL�, for the reason). Otherwise,
MySQL uses
host_name-bin
The index file for binary log filenames. See
Section 5.11.4, “The Binary Log�. If you omit the filename,
and if you didn't specify one with
--log-bin, MySQL uses
host_name-bin.index
Log errors and startup messages to this file. See
Section 5.11.1, “The Error Log�. If you omit the filename,
MySQL uses
host_name.err.err.
Log all ISAM/MyISAM
changes to this file (used only when debugging
ISAM/MyISAM).
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
queries. Before MySQL 4.1, if you are using
--log-slow-queries and
--log-long-format, queries that are not
using indexes also are logged to the slow query log.
--log-long-format is deprecated as of
MySQL version 4.1, when
--log-short-format was introduced. (Long
log format is the default setting since version 4.1.) Also
note that starting with MySQL 4.1, the
--log-queries-not-using-indexes option is
available for the purpose of logging queries that do not
use indexes to the slow query log.
--log-queries-not-using-indexes
If you are using this option with
--log-slow-queries, queries that do not
use indexes also are logged to the slow query log. This
option is available as of MySQL 4.1. See
Section 5.11.5, “The Slow Query Log�.
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. This option was introduced in MySQL 4.1.
Log slow administrative statements such as
OPTIMIZE TABLE, ANALYZE
TABLE, and ALTER TABLE to the
slow query log.
This option was added in MySQL 4.1.13. (It is unnecessary in MySQL 4.0 because slow administrative statements are logged by default.)
--log-slow-queries[=
file_name]
Log all queries that have taken more than
long_query_time seconds to execute to
this file. See Section 5.11.5, “The Slow Query Log�. Note that
the default for the amount of information logged has
changed in MySQL 4.1. See the
--log-long-format and
--log-short-format options for details.
Log updates to fileN where
N is a unique number if not
given. See Section 5.11.3, “The Update Log�. The update log is
now deprecated; you should use the binary log instead
(--log-bin). See
Section 5.11.4, “The Binary Log�.
--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 by default as of MySQL 4.0.19 and 4.1.2;
to disable it, use --log-warnings=0. As
of MySQL 4.0.21 and 4.1.3, a
level argument can be given. If
omitted, the default level is
1. Aborted connections are not logged to the error log
unless the value is greater than 1. See
Section A.2.10, “Communication Errors and Aborted Connections�.
Before MySQL 4.0.21 and 4.1.3, this is a boolean option,
not an integer-valued option. Before 4.0, this option was
named --warnings.
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 7.3.2, “Table Locking Issues�.
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.6.5, “How to Run MySQL as a Normal User�.
--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:
| Option | Description |
DEFAULT | The same as not giving any option to --myisam-recover. |
BACKUP | If the data file was changed during recovery, save a backup of the
|
FORCE | Run recovery even if we would lose more than one row from the
.MYD file. |
QUICK | do not check the rows in the table if there are not any delete blocks. |
Before the server automatically repairs a table, it writes
a note about the repair to the error log. If you want to
be able to recover from most problems without user
intervention, you should use the options
BACKUP,FORCE. This forces a repair of a
table even if some rows would be deleted, but it keeps the
old data file as a backup so that you can later examine
what happened.
See Section 14.1.1, “MyISAM Startup Options�.
This option is available as of MySQL 3.23.25.
--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 15.4.4.2, “The Cluster connectstring�, for syntax.
If the binary includes support for the NDB
Cluster storage engine (from version 4.1.3, the
MySQL-Max binaries are built with NDB
Cluster enabled), this option enables the
engine, which is disabled by default. Using the
NDB Cluster storage engine is necessary
for using MySQL Cluster. See
Chapter 15, MySQL Cluster.
The --new option can be used to make the
server behave as 4.1 in certain respects, easing a 4.0 to
4.1 upgrade:
Hexadecimal strings such as 0xFF
are treated as strings by default rather than as
numbers. (Works in 4.0.12 and up.)
TIMESTAMP is returned as a string
with the format 'YYYY-MM-DD
HH:MM:SS'. (Works in 4.0.13 and up.) See
Chapter 11, Data Types.
This option can be used to help you see how your applications behave in MySQL 4.1, without actually upgrading to 4.1.
Force the server to generate short (pre-4.1) password hashes for new passwords. This is useful for compatibility when the server must support older client programs. See Section 5.7.9, “Password Hashing as of MySQL 4.1�.
Use the 3.20 protocol for compatibility with some very old clients.
Only use one thread (for debugging under Linux). This option is available only if the server is built with debugging enabled. See Section E.1, “Debugging a MySQL Server�.
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_connections×5 or
max_connections +
table_open_cache×2 files (whichever is
larger). You should try increasing this value if
mysqld gives you the error Too
many open files.
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.
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.
Skip some optimization stages.
With this option, the SHOW DATABASES
statement displays only the names of those databases for
which the user has some kind of privilege. As of MySQL
4.0.2, this option is deprecated and does not do anything
(it is enabled by default), because there is a
SHOW DATABASES privilege that can be
used to control access to database names on a per-account
basis. See Section 5.7.3, “Privileges Provided by MySQL�.
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.
Disallow authentication by clients that attempt to use accounts that have old (pre-4.1) passwords. This option is available as of MySQL 4.1.1.
Enable shared-memory connections by local clients. This option is available only on Windows. It was added in MySQL 4.1.0.
--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. This option was added in MySQL 4.1.0.
Disable the BDB storage engine. This
saves memory and might speed up some operations. Do not
use this option if you require BDB
tables.
Turn off the ability to select and insert at the same time
on MyISAM tables. (This is to be used
only if you think you have found a bug in this feature.)
See Section 7.3.3, “Concurrent Inserts�.
Ignore the DELAY_KEY_WRITE option for
all tables. As of MySQL 4.0.3, you should use
--delay-key-write=OFF instead. See
Section 7.5.2, “Tuning Server Parameters�.
Do not use external locking (system locking). With
external locking disabled, you must shut down the server
to use myisamchk or
isamchk. See
Section 7.5.1, “System Factors and Startup Parameter Tuning�. As of MySQL 3.23,
you can use the CHECK TABLE and
REPAIR TABLE statements to check and
repair MyISAM tables. This option
previously was named --skip-locking.
External locking has been disabled by default since MySQL 4.0.
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).
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 7.5.5, “How MySQL Uses DNS�.
Disable the InnoDB storage engine. This
saves memory and disk space and might speed up some
operations. Do not use this option if you require
InnoDB tables.
Disable the ISAM storage engine. As of
MySQL 4.1, ISAM is disabled by default,
so this option applies only if the server was configured
with support for ISAM. This option was
added in MySQL 4.1.1.
Disable the MERGE storage engine. This
option was added in MySQL 4.1.21. It can be used if the
following behavior is undesirable: If a user has access to
MyISAM table
t, that user can create a
MERGE table
m that accesses
t. However, if the user's
privileges on t are
subsequently revoked, the user can continue to access
t by doing so through
m.
Do not resolve 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 7.5.5, “How MySQL Uses DNS�.
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 15.4.3, “Quick Test Setup of MySQL Cluster�, for an example of
usage.
Do not listen for TCP/IP connections at all. All interaction with mysqld must be made via named pipes or shared memory (on Windows) or Unix socket files (on Unix). This option is highly recommended for systems where only local clients are allowed. See Section 7.5.5, “How MySQL Uses DNS�.
do not use new, possibly wrong routines.
This is the old form of
--skip-symbolic-links, for use before
MySQL 4.0.13.
Options that begin with --ssl specify
whether to allow clients to connect via SSL and indicate
where to find SSL keys and certificates. See
Section 5.8.7.3, “SSL Command Options�.
Available on Windows NT-based systems only; instructs the MySQL server not to run as a service.
--symbolic-links,
--skip-symbolic-links
Enable or disable symbolic link support. This option has different effects on Windows and Unix:
On Windows, enabling symbolic links allows you to
establish a symbolic link to a database directory by
creating a
db_name.sym
On Unix, enabling symbolic links means that you can
link a MyISAM index file or data
file to another directory with the INDEX
DIRECTORY or DATA
DIRECTORY options of the CREATE
TABLE statement. If you delete or rename the
table, the files that its symbolic links point to also
are deleted or renamed. See
Section 7.6.1.2, “Using Symbolic Links for Tables on Unix�.
This option was added in MySQL 4.0.13.
If MySQL is configured with
--with-debug=full, all MySQL programs
check for memory overruns during each memory allocation
and memory freeing operation. This checking is very slow,
so for the server you can avoid it when you do not need it
by using the --skip-safemalloc option.
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.
do not write stack traces. This option is useful when you are running mysqld under a debugger. On some systems, you also must use this option to get a core file. See Section E.1, “Debugging a MySQL Server�.
Disable using thread priorities for faster response time.
On Unix, this option specifies the Unix socket file to use
when listening for local connections. The default value is
/tmp/mysql.sock. On Windows, the
option specifies the pipe name to use when listening for
local connections that use a named pipe. The default value
is MySQL (not case sensitive).
--sql-mode=
value[,value[,value...]]
Set the SQL mode. See Section 5.2.5, “SQL Modes�. This option was added in 3.23.41.
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.
Sets the default transaction isolation level. The
level value can be
READ-UNCOMMITTED,
READ-COMMITTED,
REPEATABLE-READ, or
SERIALIZABLE. See
Section 13.4.6, “SET TRANSACTION Syntax�.
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. Starting from
MySQL 4.1.0, 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 A.4.4, “Where MySQL Stores Temporary Files�. A
replication slave needs some of its temporary files to
survive a machine restart so that it can replicate
temporary tables or LOAD DATA INFILE
operations. If files in the temporary file directory are
lost when the server restarts, replication fails.
--user={,
user_name|user_id}-u
{
user_name|user_id}
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.6.1, “General Security Guidelines�.
Starting from MySQL 3.23.56 and 4.0.12: 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.
Display version information and exit.
As of MySQL 4.0, you can assign a value to a server system
variable by using an option of the form
--.
For example, var_name=value--key_buffer_size=32M sets the
key_buffer_size variable to a value of
32MB.
Note that when you assign a value to a variable, MySQL might automatically correct the value to stay within a given range, or adjust the value to the closest allowable value if only certain values are allowed.
If you want to restrict the maximum value to which a variable
can be set at runtime with SET, you can
define this by using the
--maximum-
command-line option.
var_name=value
It is also possible to set variables by using
--set-variable=
or
var_name=value--
syntax. This syntax is deprecated as of MySQL
4.0.
var_name=value
You can change the values of most system variables for a
running server with the SET statement. See
Section 13.5.3, “SET Syntax�.
Section 5.2.2, “System Variables�, provides a full description for all variables, and additional information for setting them at server startup and runtime. Section 7.5.2, “Tuning Server Parameters�, includes information on optimizing the server by tuning system variables.
The mysql server maintains many system
variables that indicate how it is configured. Each system
variable has a default value. System variables can be set at
server startup using options on the command line or in an
option file. As of MySQL 4.0.3, most of them can be changed
dynamically while the server is running by means of the
SET statement, which enables you to modify
operation of the server without having to stop and restart it.
You can refer to system variable values in expressions.
There are several ways to see the names and values of system variables:
To see the values that a server will use based on its
compiled-in defaults and any option files that it reads,
use this command (omit --verbose before
MySQL 4.1.1):
mysqld --verbose --help
To see the values that a server will use based on its
compiled-in defaults, ignoring the settings in any option
files, use this command (omit --verbose
before MySQL 4.1.1):
mysqld --no-defaults --verbose --help
To see the current values used by a running server, use
the SHOW VARIABLES statement.
This section provides a description of each system variable. Variables with no version indicated have been present since at least MySQL 3.22.
For additional system variable information, see these sections:
Section 5.2.3, “Using System Variables�, discusses the syntax for setting and displaying system variable values.
Section 5.2.3.2, “Dynamic System Variables�, lists the variables that can be set at runtime.
Information on tuning sytem variables can be found in Section 7.5.2, “Tuning Server Parameters�.
Section 14.2.5, “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.
ansi_mode
This is ON if mysqld
was started with --ansi. See
Section 1.9.3, “Running MySQL in ANSI Mode�. This variable was added in
MySQL 3.23.6 and removed in 3.23.41. See the description
for sql_mode.
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.
basedir
The MySQL installation base directory. This variable can
be set with the --basedir option.
bdb_cache_size
The size of the buffer that is allocated for caching
indexes and rows for BDB tables. If you
do not use BDB tables, you should start
mysqld with --skip-bdb
to not allocate memory for this cache. This variable was
added in MySQL 3.23.14.
bdb_home
The base directory for BDB tables. This
should be assigned the same value as the
datadir variable. This variable was
added in MySQL 3.23.14.
bdb_log_buffer_size
The size of the buffer that is allocated for caching
indexes and rows for BDB tables. If you
do not use BDB tables, you should set
this to 0 or start mysqld with
--skip-bdb in order not to allocate
memory for this cache. This variable was added in MySQL
3.23.31.
bdb_logdir
The directory where the BDB storage
engine writes its log files. This variable can be set with
the --bdb-logdir option. This variable
was added in MySQL 3.23.14.
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 ...
This variable was added in MySQL 3.23.29.
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.) This variable was added in MySQL 3.23.29.
bdb_tmpdir
The BDB temporary file directory. This
variable was added in MySQL 3.23.14.
bdb_version
See the description for version_bdb.
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, starting from MySQL
4.1.2, 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. This
variable was added in MySQL 3.23.29. See
Section 5.11.4, “The Binary Log�.
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.
This variable was added in MySQL 4.0.3. This variable
previously was named
myisam_bulk_insert_tree_size.
character_set
The default character set. This variable was added in
MySQL 3.23.3, then removed in MySQL 4.1.1 and replaced by
the various
character_set_
variables.
xxx
character_set_client
The character set for statements that arrive from the client. This variable was added in MySQL 4.1.1.
character_set_connection
The character set used for literals that do not have a character set introducer and for number-to-string conversion. This variable was added in MySQL 4.1.1.
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. This
variable was added in MySQL 4.1.1.
character_set_results
The character set used for returning query results to the client. This variable was added in MySQL 4.1.1.
character_set_server
The server default character set. This variable was added in MySQL 4.1.1.
character_set_system
The character set used by the server for storing
identifiers. The value is always utf8.
This variable was added in MySQL 4.1.1.
character_sets
The supported character sets. This variable was added in
MySQL 3.23.15 and removed in MySQL 4.1.1. (Use
SHOW CHARACTER SET for a list of
character sets.)
character_sets_dir
The directory where character sets are installed. This variable was added in MySQL 4.1.2.
collation_connection
The collation of the connection character set. This variable was added in MySQL 4.1.1.
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. This
variable was added in MySQL 4.1.1.
collation_server
The server default collation. This variable was added in MySQL 4.1.1.
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. This variable was added in
MySQL 3.23.7.
See also Section 7.3.3, “Concurrent Inserts�.
The number of seconds that the mysqld
server waits for a connect packet before responding with
Bad handshake.
convert_character_set
The current character set mapping that was set by
SET CHARACTER SET. This variable was
removed in MySQL 4.1.
datadir
The MySQL data directory. This variable can be set with
the --datadir option.
date_format
This variable is not implemented.
datetime_format
This variable is not implemented.
default_week_format
The default mode value to use for the
WEEK() function. See
Section 12.5, “Date and Time Functions�. This variable
is available as of MySQL 4.0.14.
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.
| Option | Description |
OFF | DELAY_KEY_WRITE is ignored. |
ON | MySQL honors any DELAY_KEY_WRITE option specified in
CREATE TABLE statements. This
is the default value. |
ALL | All new opened tables are treated as if they were created with the
DELAY_KEY_WRITE option enabled. |
If DELAY_KEY_WRITE is enabled for a
table, the key buffer is not flushed for the table on
every index update, but only when the table is closed.
This speeds up writes on keys a lot, but if you use this
feature, you should add automatic checking of all
MyISAM tables by starting the server
with the --myisam-recover option (for
example, --myisam-recover=BACKUP,FORCE).
See Section 5.2.1, “Command Options�, and
Section 14.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.
This variable was added in MySQL 3.23.8.
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.
delayed_insert_timeout
How many seconds an INSERT DELAYED
handler thread should wait for INSERT
statements before terminating.
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.
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. This variable was added in MySQL 4.1.0.
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 A.4.2, “What to Do If MySQL Keeps Crashing�. This variable is set to
ON if you start
mysqld with the
--flush option. This variable was added
in MySQL 3.22.9.
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. This variable
was added in MySQL 3.22.18.
ft_boolean_syntax
The list of operators supported by boolean full-text
searches performed using IN BOOLEAN
MODE. See Section 12.7.1, “Boolean Full-Text Searches�.
This variable was added as a read-only variable in MySQL
4.0.1. It can be modified as of MySQL 4.1.2.
The default variable value is
'+Â -><()~*:""&|'. The
rules for changing the value are as follows:
Operator function is determined by position within the string.
The replacement value must be 14 characters.
Each character must be an ASCII non-alphanumeric character.
Either the first or second character must be a space.
No duplicates are allowed except the phrase quoting operators in positions 11 and 12. These two characters are not required to be the same, but they are the only two that may be.
Positions 10, 13, and 14 (which by default are set to
‘:’,
‘&’, and
‘|’) are reserved for
future extensions.
ft_max_word_len
The maximum length of the word to be included in a
FULLTEXT index. This variable was added
in MySQL 4.0.0.
Note:
FULLTEXT indexes must be rebuilt after
changing this variable. Use REPAIR TABLE
.
tbl_name QUICK
ft_min_word_len
The minimum length of the word to be included in a
FULLTEXT index. This variable was added
in MySQL 4.0.0.
Note:
FULLTEXT indexes must be rebuilt after
changing this variable. Use REPAIR TABLE
.
tbl_name QUICK
ft_query_expansion_limit
The number of top matches to use for full-text searches
performed using WITH QUERY EXPANSION.
This variable was added in MySQL 4.1.1.
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. This variable was added in
MySQL 4.0.10.
Note:
FULLTEXT indexes must be rebuilt after
changing this variable or the contents of the stopword
file. Use REPAIR TABLE
.
tbl_name QUICK
group_concat_max_len
The maximum allowed result length for the
GROUP_CONCAT() function. The default is
1024. This variable was added in MySQL 4.1.0.
have_archive
YES if mysqld
supports ARCHIVE tables,
NO if not. This variable was added in
MySQL 4.1.3.
have_bdb
YES if mysqld
supports BDB tables.
DISABLED if --skip-bdb
is used. This variable was added in MySQL 3.23.30.
have_blackhole_engine
YES if mysqld
supports BLACKHOLE table