This chapter covers topics that deal with administering a MySQL
installation, such as configuring the server, managing user
accounts, and performing backups.
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 are helper programs to
assist you in starting and stopping the server.
This section provides an overview of the server and related
programs, and information about server startup scripts.
Information about configuring the server itself is given in
Section 5.2, “mysqld — The MySQL Server”.
5.1.1. Overview of the Server-Side Scripts and Utilities
All MySQL programs take many different options. However, every
MySQL program provides a --help option that you
can use to get a description of the program's options. For
example, try mysqld --help.
You can override default options for all standard 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:
mysqld
The SQL daemon (that is, the MySQL server). To use client
programs, this program must be running, because clients gain
access to databases by connecting to the server. See
Section 5.2, “mysqld — The MySQL Server”.
A server startup script. This script is used on systems that
use run directories containing scripts that start system
services for particular run levels. It invokes
mysqld_safe to start the MySQL server.
See Section 5.1.4, “mysql.server — MySQL Server Startup Script”.
This script is used after an upgrade install operation, to
update the grant tables with any changes that have been made
in newer versions of MySQL. See
Section 2.10.3, “Upgrading the Grant Tables”.
There are several other programs that also are run on the server
host:
This program makes a binary release of a compiled MySQL.
This could be sent by FTP to
/pub/mysql/upload/ on
ftp.mysql.com for the convenience of
other MySQL users.
A MySQL-Max server is a version of the mysqld
MySQL server that has been built to include additional features.
The distribution to use depends on your platform:
For Windows, MySQL binary distributions include both the
standard server (mysqld.exe) and the
MySQL-Max server (mysqld-max.exe), so you
need not get a special distribution. Just use a regular
Windows distribution, available at
http://dev.mysql.com/downloads/. See
Section 2.3, “Installing MySQL on Windows”.
For Linux, if you install MySQL using RPM distributions, use
the regular MySQL-server RPM first to
install a standard server named mysqld.
Then use the MySQL-Max RPM to install a
server named mysqld-max. The
MySQL-Max RPM presupposes that you have
installed the regular server RPM. See
Section 2.4, “Installing MySQL on Linux” for more information on the
Linux RPM packages.
All other MySQL-Max distributions contain a single server
that is named mysqld but that has the
additional features included.
MySQL AB builds the MySQL-Max servers by using the following
configure options:
--with-server-suffix=-max
This option adds a -max suffix to the
mysqld version string.
--with-innodb
This option enables support for the
InnoDB storage engine. MySQL-Max servers
always include InnoDB support, but this
option actually is needed only for MySQL 3.23. From MySQL
4.0 onwards, InnoDB is included by
default in all binary distributions, so you do not need a
MySQL-Max server merely to obtain InnoDB
support.
--with-bdb
This option enables support for the Berkeley DB
(BDB) storage engine.
--with-blackhole-storage-engine
This option enables support for the
BLACKHOLE storage engine in MySQL 4.1.11
and newer.
USE_SYMDIR
This define is enabled to turn on database symbolic link
support for Windows. (This applies only before MySQL 4.0. As
of MySQL 4.0, symbolic link support is available for all
Windows servers, so a Max server is not needed to take
advantage of this feature.)
--with-ndbcluster
This option enables support for the NDB Cluster storage
engine in MySQL 4.1.2 and newer. Currently, MySQL
Cluster is supported on Linux, Solaris, and Mac OS X
only.
MySQL-Max binary distributions are a convenience for those who
wish to install precompiled programs. If you build MySQL using a
source distribution, you can build your own Max-like server by
enabling the same features at configuration time that the
MySQL-Max binary distributions are built with.
MySQL-Max servers include the BerkeleyDB
(BDB) storage engine whenever possible, but
not all platforms support BDB.
MySQL-Max servers versions 4.1.2 and above for Solaris, Mac OS
X, and Linux (on most platforms) include support for the
NDB Cluster storage engine. Note that the
server must be started with the ndbcluster
option in order to run the server as part of a MySQL Cluster.
(For details, see
Section 16.4, “MySQL Cluster Configuration”.)
The following table shows on which platforms allow MySQL-Max
binaries include support for BDB and/or
NDB Cluster:
System
BDB Support
NDB Support
AIX 4.3
N
N
HP-UX 11.0
N
N
Linux-Alpha
N
Y
Linux-IA-64
N
N
Linux-Intel
Y
Y
Mac OS X
N
N
NetWare
N
N
SCO OSR5
Y
N
Solaris-SPARC
Y
Y
Solaris-Intel
N
Y
UnixWare
Y
N
Windows NT/2000/XP
Y
N
To find out which storage engines your server supports, issue
the following statement:
mysql> SHOW ENGINES;
+------------+---------+------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+------------------------------------------------------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| HEAP | YES | Alias for MEMORY |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| MERGE | YES | Collection of identical MyISAM tables |
| MRG_MYISAM | YES | Alias for MERGE |
| ISAM | NO | Obsolete storage engine, now replaced by MyISAM |
| MRG_ISAM | NO | Obsolete storage engine, now replaced by MERGE |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| INNOBASE | YES | Alias for INNODB |
| BDB | YES | Supports transactions and page-level locking |
| BERKELEYDB | YES | Alias for BDB |
| NDBCLUSTER | NO | Clustered, fault-tolerant, memory-based tables |
| NDB | NO | Alias for NDBCLUSTER |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | NO | Archive storage engine |
| CSV | NO | CSV storage engine |
| BLACKHOLE | NO | Storage engine designed to act as null storage |
+------------+---------+------------------------------------------------------------+
17 rows in set (0.02 sec)
Before MySQL 4.1.2, SHOW ENGINES is
unavailable. Use the following statement instead and check the
value of the variable for the storage engine in which you are
interested:
mysql> SHOW VARIABLES LIKE 'have%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| have_archive | NO |
| have_bdb | YES |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | NO |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
+-----------------------+-------+
16 rows in set (0.00 sec)
The precise output from these SHOW commands
will vary according to the MySQL version used (and the features
which are enabled). The values in the second column indicate the
server's level of support for each feature, as shown here:
Value
Meaning
YES
The feature is supported and is active.
NO
The feature is not supported.
DISABLED
The feature is supported but has been disabled.
A value of NO means that the server was
compiled without support for the feature, so it cannot be
activated at runtime.
A value of DISABLED occurs either because the
server was started with an option that disables the feature, or
because not all options required to enable it were given. In the
latter case, the
host_name.err
error log file should contain a reason indicating why the option
is disabled.
One situation in which you might see DISABLED
occurs with MySQL 3.23 when the InnoDB
storage engine is compiled in. In MySQL 3.23, you must supply at
least the innodb_data_file_path option at
runtime to set up the InnoDB tablespace.
Without this option, InnoDB disables itself.
See Section 15.3, “InnoDB in MySQL 3.23”. You can specify
configuration options for the BDB storage
engine, too, but BDB does not disable itself
if you do not provide them. See Section 14.4.3, “BDB Startup Options”.
You might also see DISABLED for the
InnoDB, BDB, or
ISAM storage engines if the server was
compiled to support them, but was started with the
--skip-innodb, --skip-bdb, or
--skip-isam options at runtime. This is also
the case where the server supports NDB
Cluster, but was not started with the
ndbcluster option.
As of version 3.23, all MySQL servers support
MyISAM tables, because
MyISAM is the default storage engine.
5.1.3. mysqld_safe — MySQL Server Startup Script
mysqld_safe is the recommended way to
start a mysqld server on Unix and
NetWare. mysqld_safe adds some safety
features such as restarting the server when an error occurs
and logging runtime information to an error log file.
NetWare-specific behaviors are listed later in this section.
Note: Before MySQL 4.0,
mysqld_safe is named
safe_mysqld. To preserve backward
compatibility, MySQL binary distributions for some time will
include safe_mysqld as a symbolic link to
mysqld_safe.
By default, mysqld_safe tries to start an
executable named mysqld-max if it exists,
or mysqld otherwise. Be aware of the
implications of this behavior:
On Linux, the MySQL-Max RPM relies on
this mysqld_safe behavior. The RPM
installs an executable named
mysqld-max, which causes
mysqld_safe to automatically use that
executable from that point on.
If you install a MySQL-Max distribution that includes a
server named mysqld-max, then upgrade
later to a non-Max version of MySQL,
mysqld_safe still attempts to run the
old mysqld-max server. If you perform
such an upgrade, you should manually remove the old
mysqld-max server to ensure that
mysqld_safe runs the new
mysqld server.
To override the default behavior and specify explicitly
which server you want to run, specify a
--mysqld or
--mysqld-version option to
mysqld_safe.
All options specified to mysqld_safe on
the command line are passed to mysqld. If
you want to use any options that are specific to
mysqld_safe and that
mysqld does not support, do not specify
them on the command line. Instead, list them in the
[mysqld_safe] group of an option file.
See Section 4.3.2, “Using Option Files”.
mysqld_safe reads all options from the
[mysqld], [server],
and [mysqld_safe] sections in option
files. For backward compatibility, it also reads
[safe_mysqld] sections, although you
should rename such sections to
[mysqld_safe] when you begin using MySQL
4.0 or later.
mysqld_safe supports the following
options:
--autoclose
(NetWare only) On NetWare,
mysqld_safe provides a screen
presence. When you unload (shut down) the
mysqld_safe NLM, the screen does not
by default go away. Instead, it prompts for user input:
*<NLM has terminated; Press any key to close the screen>*
If you want NetWare to close the screen automatically
instead, use the --autoclose option to
mysqld_safe.
--basedir=path
The path to the MySQL installation directory.
--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.
--core-file-size=size
The size of the core file mysqld
should be able to create. The option value is passed to
ulimit -c.
--datadir=path
The path to the data directory.
--defaults-extra-file=path
The name of an option file to be read in addition to the
usual option files. If given, this option must be first.
--defaults-file=path
The name of an option file to be read instead of the
usual option files. If given, this option must be first.
--err-log=path
The old form of the --log-error option,
to be used before MySQL 4.0.
--ledir=path
The path to the directory containing the
mysqld program. Use this option to
explicitly indicate the location of the server.
The name of the server program (in the
ledir directory) that you want to
start. This option is needed if you use the MySQL binary
distribution but have the data directory outside of the
binary distribution.
--mysqld-version=suffix
This option is similar to the --mysqld
option, but you specify only the suffix for the server
program name. The basename is assumed to be
mysqld. For example, if you use
--mysqld-version=max,
mysqld_safe starts the
mysqld-max program in the
ledir directory. If the argument to
--mysqld-version is empty,
mysqld_safe uses
mysqld in the
ledir directory.
--nice=priority
Use the nice program to set the
server's scheduling priority to the given value. This
option was added in MySQL 4.0.14.
--no-defaults
Do not read any option files. If given, this option must
be first.
--open-files-limit=count
The number of files mysqld should be
able to open. The option value is passed to
ulimit -n. Note that you need to
start mysqld_safe as
root for this to work properly.
--pid-file=path
The path to the process ID file.
--port=port_num
The port number to use when listening for TCP/IP
connections. The port number must be
1024 or higher unless MySQL is run as
the root system user.
--socket=path
The Unix socket file to use for local connections.
--timezone=zone
Set the TZ time zone environment
variable to the given option value. Consult your
operating system documentation for legal time zone
specification formats.
--user={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.)
When executing mysqld_safe, the
--defaults-file or
--defaults-extra-option must be given
first, or the option file will not be used. For example,
this command will not use the named option file:
The mysqld_safe script is written so that
it normally can start a server that was installed from
either a source or a binary distribution of MySQL, even
though these types of distributions typically install the
server in slightly different locations. (See
Section 2.1.5, “Installation Layouts”.)
mysqld_safe expects one of the following
conditions to be true:
The server and databases can be found relative to the
directory from which mysqld_safe is
invoked. For binary distributions,
mysqld_safe looks under its working
directory for bin and
data directories. For source
distributions, it looks for libexec
and var directories. This condition
should be met if you execute
mysqld_safe from your MySQL
installation directory (for example,
/usr/local/mysql for a binary
distribution).
If the server and databases cannot be found relative to
the working directory, mysqld_safe
attempts to locate them by absolute pathnames. Typical
locations are /usr/local/libexec
and /usr/local/var. The actual
locations are determined from the values configured into
the distribution at the time it was built. They should
be correct if MySQL is installed in the location
specified at configuration time.
Because mysqld_safe tries to find the
server and databases relative to its own working directory,
you can install a binary distribution of MySQL anywhere, as
long as you run mysqld_safe from the
MySQL installation directory:
shell> cd mysql_installation_directory
shell> bin/mysqld_safe &
If mysqld_safe fails, even when invoked
from the MySQL installation directory, you can specify the
--ledir and --datadir
options to indicate the directories in which the server and
databases are located on your system.
Normally, you should not edit the
mysqld_safe script. Instead, configure
mysqld_safe by using command-line options
or options in the [mysqld_safe] section
of a my.cnf option file. In rare cases,
it might be necessary to edit mysqld_safe
to get it to start the server properly. However, if you do
this, your modified version of
mysqld_safe might be overwritten if you
upgrade MySQL in the future, so you should make a copy of
your edited version that you can reinstall.
On NetWare, mysqld_safe is a NetWare
Loadable Module (NLM) that is ported from the original Unix
shell script. It does the following:
Runs a number of system and option checks.
Runs a check on MyISAM and
ISAM tables.
Provides a screen presence for the MySQL server.
Starts mysqld, monitors it, and
restarts it if it terminates in error.
Sends error messages from mysqld to
the
host_name.err
file in the data directory.
Sends mysqld_safe screen output to
the
host_name.safe
file in the data directory.
5.1.4. mysql.server — MySQL Server Startup Script
MySQL distributions on Unix include a script named
mysql.server. It can be used on systems
such as Linux and Solaris that use System V-style run
directories to start and stop system services. It is also
used by the Mac OS X Startup Item for MySQL.
mysql.server can be found in the
support-files directory under your
MySQL installation directory or in a MySQL source tree.
If you use the Linux server RPM package
(MySQL-server-VERSION.rpm),
the mysql.server script will be installed
in the /etc/init.d directory with the
name mysql. You need not install it
manually. See Section 2.4, “Installing MySQL on Linux” for more
information on the Linux RPM packages.
Some vendors provide RPM packages that install a startup
script under a different name such as
mysqld.
If you install MySQL from a source distribution or using a
binary distribution format that does not install
mysql.server automatically, you can
install it manually. Instructions are provided in
Section 2.9.2.2, “Starting and Stopping MySQL Automatically”.
mysql.server reads options from the
[mysql.server] and
[mysqld] sections of option files. (For
backward compatibility, it also reads
[mysql_server] sections, although you
should rename such sections to
[mysql.server] when you begin using MySQL
4.0 or later.)
5.1.5. mysqld_multi — Program for Managing Multiple MySQL Servers
mysqld_multi is meant for managing
several mysqld processes that listen for
connections on different Unix socket files and TCP/IP ports.
It can start or stop servers, or report their current
status.
The program searches for groups named
[mysqldN] in
my.cnf (or in the file named by the
--config-file option).
N can be any positive integer.
This number is referred to in the following discussion as
the option group number, or GNR.
Group numbers distinguish option groups from one another and
are used as arguments to mysqld_multi to
specify which servers you want to start, stop, or obtain a
status report for. Options listed in these groups are the
same that you would use in the [mysqld]
group used for starting mysqld. (See, for
example, Section 2.9.2.2, “Starting and Stopping MySQL Automatically”.) However, when
using multiple servers it is necessary that each one use its
own value for options such as the Unix socket file and
TCP/IP port number. For more information on which options
must be unique per server in a multiple-server environment,
see Section 5.11, “Running Multiple MySQL Servers on the Same Machine”.
start, stop, and
report indicate which operation you want
to perform. You can perform the designated operation on a
single server or multiple servers, depending on the
GNR list that follows the option
name. If there is no list, mysqld_multi
performs the operation for all servers in the option file.
Each GNR value represents an
option group number or range of group numbers. The value
should be the number at the end of the group name in the
option file. For example, the GNR
for a group named [mysqld17] is
17. To specify a range of numbers,
separate the first and last numbers by a dash. The
GNR value
10-13 represents groups
[mysqld10] through
[mysqld13]. Multiple groups or group
ranges can be specified on the command line, separated by
commas. There must be no whitespace characters (spaces or
tabs) in the GNR list; anything
after a whitespace character is ignored.
This command starts a single server using option group
[mysqld17]:
shell> mysqld_multi start 17
This command stops several servers, using option groups
[mysql8] and
[mysqld10] through
[mysqld13]:
shell> mysqld_multi stop 8,10-13
For an example of how you might set up an option file, use
this command:
shell> mysqld_multi --example
mysqld_multi supports the following
options:
--config-file=name
Specify the name of an alternative option file. This
affects where mysqld_multi looks for
[mysqldN]
option groups. Without this option, all options are read
from the usual my.cnf file. The
option does not affect where
mysqld_multi reads its own options,
which are always taken from the
[mysqld_multi] group in the usual
my.cnf file.
--example
Display a sample option file.
--help
Display a help message and exit.
--log=name
Specify the name of the log file. If the file exists,
log output is appended to it.
--mysqladmin=prog_name
The mysqladmin binary to be used to
stop servers.
--mysqld=prog_name
The mysqld binary to be used. Note
that you can specify mysqld_safe as
the value for this option also. The options are passed
to mysqld. Just make sure that you
have the directory where mysqld is
located in your PATH environment
variable setting or fix mysqld_safe.
--no-log
Print log information to stdout rather than to the log
file. By default, output goes to the log file.
--password=password
The password of the MySQL account to use when invoking
mysqladmin. Note that the password
value is not optional for this option, unlike for other
MySQL programs.
--silent
Disable warnings. This option was added in MySQL 4.1.6.
--tcp-ip
Connect to each MySQL server via the TCP/IP port instead
of the Unix socket file. (If a socket file is missing,
the server might still be running, but accessible only
via the TCP/IP port.) By default, connections are made
using the Unix socket file. This option affects
stop and report
operations.
--user=user_name
The username of the MySQL account to use when invoking
mysqladmin.
--verbose
Be more verbose. This option was added in MySQL 4.1.6.
--version
Display version information and exit.
Some notes about mysqld_multi:
Make sure that the MySQL account used for stopping the
mysqld servers (with the
mysqladmin program) has the same
username and password for each server. Also, make sure
that the account has the SHUTDOWN
privilege. If the servers that you want to manage have
many different usernames or passwords for the
administrative accounts, you might want to create an
account on each server that has the same username and
password. For example, you might set up a common
multi_admin account by executing the
following commands for each server:
shell> mysql -u root -S /tmp/mysql.sock -proot_password
mysql> GRANT SHUTDOWN ON *.*
-> TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
See Section 5.6.2, “How the Privilege System Works”. You have to do this
for each mysqld server. Change the
connection parameters appropriately when connecting to
each one. Note that the host part of the account name
must allow you to connect as
multi_admin from the host where you
want to run mysqld_multi.
The --pid-file option is very important
if you are using mysqld_safe to start
mysqld (for example,
--mysqld=mysqld_safe) Every
mysqld should have its own process ID
file. The advantage of using
mysqld_safe instead of
mysqld is that
mysqld_safe “guards” its
mysqld process and restarts it if the
process terminates due to a signal sent using
kill -9 or for other reasons, such as
a segmentation fault. Please note that the
mysqld_safe script might require that
you start it from a certain place. This means that you
might have to change location to a certain directory
before running mysqld_multi. If you
have problems starting, please see the
mysqld_safe script. Check especially
the lines:
----------------------------------------------------------------
MY_PWD=`pwd`
# Check if we are starting this relative (for the binary release)
if test -d $MY_PWD/data/mysql -a -f ./share/mysql/english/errmsg.sys -a \
-x ./bin/mysqld
----------------------------------------------------------------
The Unix socket file and the TCP/IP port number must be
different for every mysqld.
You might want to use the --user option
for mysqld, but in order to do this
you need to run the mysqld_multi
script as the Unix root user. Having
the option in the option file does not matter; you
merely get a warning if you are not the superuser and
the mysqld processes are started
under your own Unix account.
Important: Make sure
that the data directory is fully accessible to the Unix
account that the specific mysqld
process is started as. Do not use
the Unix root account for this, unless you know
exactly what you are doing.
Most important: Before
using mysqld_multi be sure that you
understand the meanings of the options that are passed
to the mysqld servers and
why you would want to have separate
mysqld processes. Beware of the
dangers of using multiple mysqld
servers with the same data directory. Use separate data
directories, unless you know
exactly what you are doing.
Starting multiple servers with the same data directory
does not give you extra performance
in a threaded system. See
Section 5.11, “Running Multiple MySQL Servers on the Same Machine”.
The following example shows how you might set up an option
file for use with mysqld_multi. The first
and fifth
[mysqldN]
group were intentionally left out from the example to
illustrate that you can have “gaps” in the
option file. This gives you more flexibility. The order in
which the mysqld programs are started or
stopped depends on the order in which they appear in the
option file.
# This file should probably be in your home dir (~/.my.cnf)
# or /etc/my.cnf
# Version 2.1 by Jani Tolonen
[mysqld_multi]
mysqld = /usr/local/bin/mysqld_safe
mysqladmin = /usr/local/bin/mysqladmin
user = multi_admin
password = multipass
[mysqld2]
socket = /tmp/mysql.sock2
port = 3307
pid-file = /usr/local/mysql/var2/hostname.pid2
datadir = /usr/local/mysql/var2
language = /usr/local/share/mysql/english
user = john
[mysqld3]
socket = /tmp/mysql.sock3
port = 3308
pid-file = /usr/local/mysql/var3/hostname.pid3
datadir = /usr/local/mysql/var3
language = /usr/local/share/mysql/swedish
user = monty
[mysqld4]
socket = /tmp/mysql.sock4
port = 3309
pid-file = /usr/local/mysql/var4/hostname.pid4
datadir = /usr/local/mysql/var4
language = /usr/local/share/mysql/estonia
user = tonu
[mysqld6]
socket = /tmp/mysql.sock6
port = 3311
pid-file = /usr/local/mysql/var6/hostname.pid6
datadir = /usr/local/mysql/var6
language = /usr/local/share/mysql/japanese
user = jani
mysqld is the MySQL server. The following
discussion covers these MySQL server configuration topics:
Startup options that the server supports
How to set the server SQL mode
Server system variables
Server status variables
5.2.1. mysqld Command-Line Options
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
[xxxxx_SERVER]
groups, where xxxxx is the name of
the application into which the server is embedded.
mysqld accepts many command-line 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 elsewhere:
You can also set the value of a server system variable by
using the variable name as an option, as described later in
this section.
--help, -?
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.
--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 MySQL 4.0.24, and 4.1.10a.
See Section 20.2.4.6, “User-Defined Function Security Precautions”.
--ansi
Use standard SQL syntax instead of MySQL syntax. See
Section 1.8.3, “Running MySQL in ANSI Mode”. For more precise control over
the server SQL mode, use the --sql-mode
option instead.
--basedir=path, -b
path
The path to the MySQL installation directory. All paths
are usually resolved relative to this.
--big-tables
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.
--bind-address=IP
The IP address to bind to.
--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.
--console
Write the error log messages to stderr/stdout even if
--log-error is specified. On Windows,
mysqld does not close the console
screen if this option is used.
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.
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.1.3, “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.
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'.
See Section E.1.2, “Creating Trace Files”.
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]
How the DELAYED KEYS option should be
used. 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
DELAYED KEYS option.
ALL delays key writes for all
MyISAM tables. Available as of MySQL
4.0.3. See Section 7.5.2, “Tuning Server Parameters”. See
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 from another MySQL server or with
myisamchk) when the table is 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.
--des-key-file=file_name
Read the default keys used by
DES_ENCRYPT() and
DES_DECRYPT() from this file.
--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.
--exit-info[=flags],
-T [flags]
This is a bit mask of different flags you can use for
debugging the mysqld server. Do
not use this option unless you know
exactly what it does.
--external-locking
Enable system locking. Note that if you use this option on
a system on which lockd does not fully
work (as on 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 have to ensure that
these 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
--query-cache-size=0.
(This is not done by default because in many setups it is
useful to have a mixture of the above options.)
--flush
Flush 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 synching to disk. See
Section A.4.2, “What to Do If MySQL Keeps Crashing”.
--init-file=file
Read SQL statements from this file at startup. Each
statement must be on a single line and should not include
comments.
Client error messages in 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.9.2, “Setting the Error Message Language”.
--log[=file],
-l [file]
Log connections and queries to this file. See
Section 5.10.2, “The General Query Log”. If you do not specify a
filename, MySQL uses
host_name.log
as the filename.
--log-bin=[file]
The binary log file. Log all queries that change data to
this file. Used for backup and replication. See
Section 5.10.4, “The Binary Log”. It is recommended to specify
a filename (see Section A.8.4, “Open Issues in MySQL” for the
reason) otherwise MySQL uses
host_name-bin
as the log file basename.
--log-bin-index[=file]
The index file for binary log filenames. See
Section 5.10.4, “The Binary Log”. If you do not specify a
filename, and if you didn't specify one in
--log-bin, MySQL uses
host_name-bin.index
as the filename.
--log-error[=file]
Log errors and startup messages to this file. See
Section 5.10.1, “The Error Log”. If you do not specify a
filename, MySQL uses
host_name.err
as the filename. If the filename has no extension, an
extension of .err is added to the
name.
--log-isam[=file]
Log all ISAM/MyISAM
changes to this file (used only when debugging
ISAM/MyISAM).
--log-long-format
Log some extra information to the log files (update log,
binary update log, and slow queries log, whatever log has
been activated). For example, 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, then queries that are
not using indexes also are logged to the slow query log.
This option is available as of MySQL 4.1. See
Section 5.10.5, “The Slow Query Log”.
--log-short-format
Log less information to the log files (update log, binary
update log, and slow queries log, whatever log has been
activated). For example, username and timestamp are not
logged for queries. This option was introduced in MySQL
4.1.
--log-slow-admin-statements
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]
Log all queries that have taken more than
long_query_time seconds to execute to
this file. See Section 5.10.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.
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 --skip-log-warnings.
As of MySQL 4.0.21 and 4.1.3, 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”.
This option was named --warnings before
MySQL 4.0.
--low-priority-updates
Table-modifying operations (INSERT,
REPLACE, DELETE,
UPDATE) have 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”.
--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.
--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,
mysqld, when it opens a
MyISAM table, checks whether the table
is marked as crashed or wasn't closed properly. (The last
option works only if you are running with
--skip-external-locking.) 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
tbl_name.MYD
file as
tbl_name-datetime.BAK.
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 a table is automatically repaired, MySQL adds a
note about this in 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.
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 16.4.4.2, “The MySQL Cluster connectstring” for syntax.
--ndbcluster
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) the default disabling of
support for the NDB Cluster storage
engine can be overruled by using this option. Using the
NDB Cluster storage engine is necessary
for using MySQL Cluster. See Chapter 16, MySQL Cluster.
--new
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.
--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 5.6.9, “Password Hashing in MySQL 4.1”.
--old-protocol, -o
Use the 3.20 protocol for compatibility with some very old
clients.
--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 E.1, “Debugging a MySQL Server”.
--open-files-limit=count
To change the number of file descriptors available to
mysqld. If this is not set or set to 0,
then mysqld uses this value to reserve
file descriptors to use with
setrlimit(). If this value is 0 then
mysqld reserves
max_connections*5 or
max_connections + table_cache*2
(whichever is larger) number of files. You should try
increasing this if mysqld gives you the
error "Too many open files."
--pid-file=path
The path to the process ID file used by
mysqld_safe.
--port=port_num, -P
port_num
The port number to use when listening for TCP/IP
connections.
--safe-mode
Skip some optimization stages.
--safe-show-database
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.6.3, “Privileges Provided by MySQL”.
--safe-user-create
If this is enabled, a user cannot create new users with
the GRANT statement, if the user does
not have the INSERT privilege for the
mysql.user table or any column in the
table.
--secure-auth
Disallow authentication for accounts that have old
(pre-4.1) passwords. This option is available as of MySQL
4.1.1.
--shared-memory
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 to use for shared-memory connections. This option
is available only on Windows. It was added in MySQL 4.1.0.
--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.
--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.)
do not use system locking. To use
isamchk or
myisamchk, you must shut down the
server. See Section 1.4.3, “MySQL Stability”. In MySQL 3.23,
you can use CHECK TABLE and
REPAIR TABLE to check and repair
MyISAM tables. This option previously
was named --skip-locking.
--skip-grant-tables
This option causes the server not to use the privilege
system at all. This 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. This option also
suppresses loading of user-defined functions (UDFs).
--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 7.5.5, “How MySQL Uses DNS”.
--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.
--skip-isam
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.
--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 7.5.5, “How MySQL Uses DNS”.
--skip-ndbcluster
Disable the NDB Cluster storage engine.
This is the default for binaries that were built with
NDB Cluster storage engine support,
this means that the system allocates memory and other
resources for this storage engine only if it is explicitly
enabled.
--skip-networking
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”.
--skip-new
do not use new, possibly wrong routines.
--skip-symlink
This is the old form of
--skip-symbolic-links, for use before
MySQL 4.0.13.
--standalone
Windows-NT based systems only, instructs MySQL server to
not 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 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 13.1.5, “CREATE TABLE Syntax”.
This option was added in MySQL 4.0.13.
--skip-safemalloc
If MySQL is configured with
--with-debug=full, all MySQL programs
check for memory overruns during each memory allocation
and memory freeing operation. This checking is very slow,
so for the server you can avoid it when you do not need it
by using the --skip-safemalloc option.
--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