Go to the first, previous, next, last section, table of contents.

5 Database Administration

This chapter covers topics that deal with administering a MySQL installation, such as configuring the server, managing user accounts, and performing backups.

5.1 The MySQL Server and Server Startup Scripts

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.

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 server-related MySQL programs:

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 the the server.
A version of the server that includes additional features.
A server startup script. mysqld_safe attempts to start mysqld-max if it exists, and mysqld otherwise.
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.
A server startup script that can start or stop multiple servers installed on the system.
This script creates the MySQL grant tables with default privileges. It is usually executed only once, when first installing MySQL on a system.
This script is used after an upgrade install operation, to update the grant tables with any changes that were made in newer versions of MySQL.

There are several other programs that also are run on the server host:

A utility to describe, check, optimize, and repair MySQL tables. myisamchk is described in section 5.5.2 Using myisamchk for Table Maintenance and Crash Recovery.
This program makes a binary release of a compiled MySQL. This could be sent by FTP to `/pub/mysql/Incoming' on support.mysql.com for the convenience of other MySQL users.
The MySQL bug reporting script. It can be be used to send a bug report to the MySQL list. (You can also visit http://bugs.mysql.com/ to file a bug report online.)

5.1.2 mysqld-max, An Extended mysqld Server

A MySQL-Max server is a version of the mysqld MySQL server that is configured to include additional features.

You can find the MySQL-Max binaries at http://www.mysql.com/downloads/mysql-max-4.0.html.

The MySQL binary distributions Windows include both the standard server (named mysqld.exe) and the MySQL-Max server (named mysqld-max.exe). http://www.mysql.com/downloads/mysql-4.0.html. See section 2.2.1 Installing MySQL on Windows.

If you install MySQL on Linux using RPM distributions, install the MySQL-server RPM first, and then the MySQL-Max RPM. The latter presupposes that you have already installed the regular server RPM. This process installs a standard server named mysqld and a MySQL-Max server named mysqld-max.

All other MySQL-Max distributions contain a single server that is named mysqld but that has the additional features.

MySQL-Max servers are built by using the following configure options:

Option Comment
--with-server-suffix=-max Add a -max suffix to the mysqld version string
--with-innodb Support for InnoDB tables (MySQL 3.23 only)
--with-bdb Support for Berkeley DB (BDB) tables
CFLAGS=-DUSE_SYMDIR Symbolic link support for Windows

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 always include the InnoDB storage engine. The --with-innodb option for enabling InnoDB support is needed only in MySQL 3.23. (In MySQL 4 and up, InnoDB is included by default. so you do not need a MySQL-Max server to obtain InnoDB support.)

MySQL-Max servers include the BerkeleyDB (BDB) storage engine whenever possible, but not all platforms support BDB. The following table shows which platforms allow MySQL-Max binaries to include BDB:

System BDB
Windows/NT Y
AIX 4.3 N
HP-UX 11.0 N
Linux-Alpha N
Linux-Intel Y
Linux-IA-64 N
Solaris-Intel N
Solaris-SPARC Y
UnixWare Y
Mac OS X N

As of Version 3.23, all MySQL servers support MyISAM tables, because MyISAM is the default storage engine. To find out which storage engines your server supports, issue the following statement:

mysql> SHOW VARIABLES LIKE "have_%";
| Variable_name    | Value    |
| have_bdb         | NO       |
| have_crypt       | YES      |
| have_innodb      | YES      |
| have_isam        | NO       |
| have_raid        | NO       |
| have_symlink     | DISABLED |
| have_openssl     | NO       |
| have_query_cache | YES      |

The values in the second column indicate the server's level of support for each feature:

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 hostname.err 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 the options, InnoDB disables itself. See section 14.4.2 InnoDB in MySQL Version 3.23. (You can specify configuration options for the BDB storage engine, too, but BDB will not disable itself without them. See section 14.5.3 BDB Startup Options.)

You may 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.

5.1.3 mysqld_safe, The Wrapper Around mysqld

mysqld_safe is the recommended way to start a mysqld server on Unix. mysqld_safe adds some safety features such as restarting the server when an error occurs and logging run-time information to a log file.

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.

If you don't use --mysqld=# or --mysqld-version=# mysqld_safe will use an executable named mysqld-max if it exists. If not, mysqld_safe will start mysqld.

On Linux, the MySQL-Max RPM uses this mysqld_safe feature. (It just installs the mysqld-max executable, so mysqld_safe automatically uses this executable when mysqld_safe is restarted.)

The preference of mysqld_safe for mysqld-max over mysqld makes it very easy to test a new mysqld binary in an existing installation. Just run configure with the options you want and then install the new mysqld binary as mysqld-max in the same directory where your existing mysqld binary is located.

On the other hand, this behavior means that 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 will still attempt to run the old mysqld-max server. If you perform such an upgrade, manually remove the old mysqld-max server to ensure that mysqld_safe runs the new mysqld server.

Normally, you should never edit the mysqld_safe script. Instead, put the options to mysqld_safe in the [mysqld_safe] section in a `my.cnf' option file. See section 4.3.2 Using Option Files. mysqld_safe reads all options from the [mysqld], [server] and [mysqld_safe] sections from the option files. (For backward compatibility, it also reads the [safe_mysqld] sections, though you should rename such sections to [mysqld_safe] once you begin using MySQL 4.0 or later.)

Note that all options specified to mysqld_safe on the command-line are passed to mysqld. If you wants to use any options for mysqld_safe that mysqld doesn't support, you must specify them in the option file.

Many of the options to mysqld_safe are the same as the options to mysqld. See section 5.2.1 mysqld Command-line Options.

mysqld_safe supports the following options:

The path to the installation directory.
The size of the core file mysqld should be able to create. Passed to ulimit -c.
The path to the data directory.
The name of an option file to be read in addition to the usual option files.
The name of an option file to be read instead of the usual option files.
Old form of the --log-error option, to be used before MySQL 4.0.
Write the error log to the above file. See section 5.7.1 The Error Log.
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.
Similar to --mysqld= but here you only give the suffix for the server program name. The base name is assumed to be mysqld. For example, if you use --mysqld-version=max, mysqld_safe will start the mysqld-max program in the ledir directory. If the argument to --mysqld-version is empty, mysqld in the ledir directory is used.
Use the nice program to set the server's scheduling priority to the given value. This option was added in MySQL 4.0.14.
Do not read any option files.
The number of files mysqld should be able to open. Passed to ulimit -n. Note that you need to start mysqld_safe as root for this to work properly!
The path to the process ID file.
The TCP/IP port number.
The Unix socket file path.
Set the time zone (the TZ) variable to the value of this parameter.

The mysqld_safe script is written so that it normally is able to start a server that was installed from either a source or a binary distribution of MySQL, even those these normally install the server in slightly different locations. See section 2.1.8 Installation Layouts. mysqld_safe expects one of the following conditions to be true: