[ << ] [ >> ]           [Top] [Contents] [Index] [ ? ]

4. Database Administration

4.1 Configuring MySQL  
4.2 General Security Issues and the MySQL Access Privilege System  
4.3 MySQL User Account Management  
4.4 Disaster Prevention and Recovery  
4.5 Database Administration Language Reference  
4.6 MySQL Localisation and International Usage  
4.7 MySQL Server-Side Scripts and Utilities  
4.8 MySQL Client-Side Scripts and Utilities  
4.9 The MySQL Log Files  
4.10 Replication in MySQL  

4.1 Configuring MySQL

4.1.1 mysqld Command-line Options  
4.1.2 `my.cnf' Option Files  
4.1.3 Running Multiple MySQL Servers on the Same Machine  

4.1.1 mysqld Command-line Options

In most cases you should manage mysqld options through option files. See section `my.cnf' Option Files.

mysqld and mysqld.server read options from the mysqld and server groups. mysqld_safe read options from the mysqld, server, mysqld_safe and safe_mysqld groups. An embedded MySQL server usually reads options from the server, embedded and xxxxx_SERVER, where xxxxx is the name of the application.

mysqld accepts a lot of command-line options. Here follows some of the most common ones. For a full list execute mysqld --help. Options used for replication are listed in a separate section, see Replication Options in `my.cnf'.

--ansi

Use SQL-99 syntax instead of MySQL syntax. See section Running MySQL in ANSI Mode.

-b, --basedir=path

Path to installation directory. All paths are usually resolved relative to this.

--big-tables

Allow big result sets by saving all temporary sets on file. It solves most 'table full' errors, but also slows down the queries where in-memory tables would suffice. Since Version 3.23.2, MySQL is able to solve it automatically by using memory for small temporary tables and switching to disk tables where necessary.

--bind-address=IP

IP address to bind to.

--console

Write the error log messages to stderr/stdout even if --log-error is specified. On Windows, mysqld will not close the console screen if this option is used.

--character-sets-dir=path

Directory where character sets are. See section The Character Set Used for Data and Sorting.

--chroot=path

Put mysqld daemon in chroot environment at startup. Recommended security measure since MySQL 4.0 (MySQL 3.23 is not able to provide 100% closed chroot jail). It somewhat limits LOAD DATA INFILE and SELECT ... INTO OUTFILE though.

--core-file

Write a core file if mysqld dies. For some systems you must also specify --core-file-size to mysqld_safe. See section mysqld_safe. Note that on some systems, like Solaris, you will not get a core file if you are also using the --user option.

-h, --datadir=path

Path to the database root.

--debug[...]=

If MySQL is configured with --with-debug, you can use this option to get a trace file of what mysqld is doing. See section Creating Trace Files.

--default-character-set=charset

Set the default character set. See section The Character Set Used for Data and Sorting.

--default-table-type=type

Set the default table type for tables. See section MySQL Table Types.

--delay-key-write[= OFF | ON | ALL]

How MyISAM DELAYED KEYS should be used. See section Tuning Server Parameters.

--delay-key-write-for-all-tables; In MySQL 4.0.3 you should use --delay-key-write=ALL instead.

Don't flush key buffers between writes for any MyISAM table. See section Tuning Server Parameters.

--des-key-file=filename

Read the default keys used by DES_ENCRYPT() and DES_DECRYPT() from this file.

--enable-external-locking (was --enable-locking)

Enable system locking. Note that if you use this option on a system on which lockd does not fully work (as on Linux), you will easily get mysqld to deadlock.

--enable-named-pipe

Enable support for named pipes (only on NT/Win2000/XP).

-T, --exit-info

This is a bit mask of different flags one can use for debugging the mysqld server; one should not use this option if one doesn't know exactly what it does!

--flush

Flush all changes to disk after each SQL command. Normally MySQL only does a write of all changes to disk after each SQL command and lets the operating system handle the syncing to disk. See section What To Do If MySQL Keeps Crashing.

-?, --help

Display short help and exit.

--init-file=file

Read SQL commands from this file at startup.

-L, --language=...

Client error messages in given language. May be given as a full path. See section Non-English Error Messages.

-l, --log[=file]

Log connections and queries to file. See section The General Query Log.

--log-bin=[file]

Log all queries that change data to the file. Used for backup and replication. See section The Binary Log.

--log-bin-index[=file]

Index file for binary log file names. See section The Binary Log.

--log-error[=file]

Log errors and startup messages to this file. See section The Error Log.

--log-isam[=file]

Log all ISAM/MyISAM changes to file (only used when debugging ISAM/MyISAM).

--log-slow-queries[=file]

Log all queries that have taken more than long_query_time seconds to execute to file. See section The Slow Query Log.

--log-update[=file]

Log updates to file.# where # is a unique number if not given. See section The Update Log. The update log is deprecated and will be removed in MySQL 5.0; you should use the binary log instead (--log-bin). See section The Binary Log. Starting from version 5.0, using --log-update will just turn on the binlog instead.

--log-long-format

Log some extra information to the update log. If you are using --log-slow-queries then queries that are not using indexes are logged to the slow query log.

--low-priority-updates

Table-modifying operations (INSERT/DELETE/UPDATE) will have lower priority than selects. It can also be done via {INSERT | REPLACE | UPDATE | DELETE} 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 Table Locking Issues.

--memlock

Lock the mysqld process in memory. This works only if your system supports the mlockall() system call (like Solaris). This may 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...]]]

Option is any combination of DEFAULT, BACKUP, FORCE or QUICK. You can also set this explicitly to "" if you want to disable this option. If this option is used, mysqld will on open check if the table is marked as crashed or if the table wasn't closed properly. (The last option only works if you are running with --skip-external-locking.) If this is the case mysqld will run check on the table. If the table was corrupted, mysqld will attempt to repair it.

The following options affects how the repair works.

Option

Description

DEFAULT

The same as not giving any option to --myisam-recover.

BACKUP

If the data table was changed during recover, save a backup of the `table_name.MYD' datafile as `table_name-datetime.BAK'.

FORCE

Run recover even if we will lose more than one row from the .MYD file.

QUICK

Don't check the rows in the table if there aren't any delete blocks.

Before a table is automatically repaired, MySQL will add a note about this in the error log. If you want to be able to recover from most things without user intervention, you should use the options BACKUP,FORCE. This will force a repair of a table even if some rows would be deleted, but it will keep the old datafile as a backup so that you can later examine what happened.

--new

From version 4.0.12, the --new option can be used to make the server behave as 4.1 in certain aspects, easing a 4.0 to 4.1 upgrade:

--pid-file=path

Path to pid file used by mysqld_safe.

-P, --port=...

Port number to listen for TCP/IP connections.

-o, --old-protocol

Use the 3.20 protocol for compatibility with some very old clients. See section Upgrading from Version 3.20 to 3.21.

--one-thread

Only use one thread (for debugging under Linux). See section Debugging a MySQL server.

--open-files-limit=

To change the number of file descriptors available to mysqld. If this is not set or set to 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 then mysqld will reserve 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'.

-O, --set-variable var=option

Give a variable a value. --help lists variables. You can find a full description for all variables in the SHOW VARIABLES section in this manual. See section SHOW VARIABLES. The tuning server parameters section includes information of how to optimise these. Please note that --set-variable is deprecated since MySQL 4.0, just use --var=option on its own. See section Tuning Server Parameters.

In MySQL 4.0.2 one can set a variable directly with --variable-name=option and set-variable is no longer needed in option files.

If you want to restrict the maximum value a startup option can be set to with SET, you can define this by using the --maximum-variable-name command line option. See section SET Syntax.

Note that when setting a variable to a value, MySQL may automatically correct it to stay within a given range and also adjusts the value a little to fix for the used algorithm.

--safe-mode

Skip some optimise stages.

--safe-show-database

With this option, the SHOW DATABASES command returns only those databases for which the user has some kind of privilege. From version 4.0.2 this option is deprecated and doesn't do anything (the option is enabled by default) as we now have the SHOW DATABASES privilege. See section GRANT and REVOKE Syntax.

--safe-user-create

If this is enabled, a user can't create new users with the GRANT command, if the user doesn't have INSERT privilege to the mysql.user table or any column in this table.

--skip-bdb

Disable usage of BDB tables. This will save memory and may speed up some things.

--skip-concurrent-insert

Turn off the ability to select and insert at the same time on MyISAM tables. (This is only to be used if you think you have found a bug in this feature.)

--skip-delay-key-write

In MySQL 4.0.3 you should use -delay-key-write=OFF instead. Ignore the DELAY_KEY_WRITE option for all tables. See section Tuning Server Parameters.

--skip-grant-tables

This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload.)

--skip-host-cache

Never use host name cache for faster name-ip resolution, but query DNS server on every connect instead. See section How MySQL uses DNS.

--skip-innodb

Disable usage of Innodb tables. This will save memory and disk space and speed up some things.

--skip-external-locking (was --skip-locking)

Don't use system locking. To use isamchk or myisamchk you must shut down the server. See section How Stable Is MySQL?. Note that in MySQL Version 3.23 you can use REPAIR and CHECK to repair/check MyISAM tables.

--skip-name-resolve

Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost. See section How MySQL uses DNS.

--skip-networking

Don't listen for TCP/IP connections at all. All interaction with mysqld must be made via named pipes or Unix sockets. This option is highly recommended for systems where only local requests are allowed. See section How MySQL uses DNS.

--skip-new

Don't use new, possibly wrong routines.

--skip-symlink

Deprecated option in 4.0.13; use --skip-symbolic-links instead.

--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 directory.sym file that contains the path to the real directory. See section Distributing Data Across Different Disks on Windows.

On Unix, enabling symbolic links means that you can link a MyISAM index file or datafile 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 will be deleted or renamed.

--skip-safemalloc

If MySQL is configured with --with-debug=full, all programs check memory for overruns for every memory allocation and memory freeing operations. This checking is very slow, so for the server you can avoid it when you don't need it by using the --skip-safemalloc option.

--skip-show-database

Don't allow the SHOW DATABASES command, unless the user has the SHOW DATABASES privilege.

--skip-stack-trace

Don't write stack traces. This option is useful when you are running mysqld under a debugger. On some systems, you also must use this option to get a core file. See section Debugging a MySQL server.

--skip-thread-priority

Disable using thread priorities for faster response time.

--socket=path

On Unix, the socket file to use for local connections (default `/tmp/mysql.sock'). On Windows, the pipe name to use for local connections that use a named pipe (default MySql).

--sql-mode=value[,value[,value...]]

The option values can be any combination of: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY, NO_UNSIGNED_SUBTRACTION, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_DIR_IN_CREATE, MYSQL323, MYSQL40, DB2, MSSQL, ORACLE, POSTGRESQL, SAPDB, or ANSI. The value also can be empty (--sql-mode="") if you want to reset it.

Several of the option values are used for compatibility with other servers. If specified, they cause the server to omit from the output of SHOW CREATE TABLE those parts of the statement that are not understood by earlier versions of MySQL or other database servers. Using these option values results in CREATE TABLE statements that are more portable for use with other servers:

These options also affect the output of mysqldump, because that program uses SHOW CREATE TABLE to obtain the table-creation statements that it includes in its own output.

Several of the option values have a complex effect because they are shorthand for a group or set of values. For example, you can tell the server to run in ANSI mode by using the --sql-mode=ANSI (or --ansi) option, which is equivalent to specifying both of the following command-line options:

 
--sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY
--transaction-isolation=SERIALIZABLE

Note that specifying ANSI mode in this way also has the effect of setting the transaction isolation level. For more information about running the server in ANSI mode, see Running MySQL in ANSI Mode.

Other "group" values are DB2, MSSQL, ORACLE, POSTGRESQL, and SAPDB. Specifying any of them turns on the PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_KEY_OPTIONS values.

The --sql-mode option was added in MySQL 3.23.41. The NO_UNSIGNED_SUBTRACTION value was added in 4.0.0. The NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, MYSQL323, MYSQL40, DB2, MSSQL, ORACLE, POSTGRESQL, SAPDB, and values were added in 4.1.1. ANSI was added in 4.1.1.

--temp-pool

Using this option will cause most temporary files created by the server to use a small set of names, rather than a unique name for each new file. This is to work around a problem in the Linux kernel dealing with creating many new files with different names. With the old behaviour, Linux seems to "leak" memory, as it's being allocated to the directory entry cache rather than to the disk cache.

--transaction-isolation={ READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE }

Sets the default transaction isolation level. See section SET TRANSACTION.

-t, --tmpdir=path

Path of the directory to use for creating temporary files. It may be useful if your default /tmp directory resides on a partition that is too small to hold temporary tables. Starting from MySQL 4.1, 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.

-u, --user={user_name | user_id}

Run the mysqld server as the user having the name user_name or 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 will change its user ID during its startup sequence, causing it to run as that particular user rather than as root. See section How to Make MySQL Secure Against Crackers.

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 some `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 `datadir/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' will be 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.

-V, --version

Display version information and exit.

-W, --log-warnings

Print out warnings like Aborted connection... to the `.err' file. Enabling this option is recommended, for example, if you use replication (you will get more information about what is happening, such as messages about network failures and reconnections). See section Communication Errors / Aborted Connection.

This option used to be called --warnings.

You can change most values for a running server with the SET command. See section SET OPTION.

4.1.2 `my.cnf' Option Files

MySQL can, since Version 3.22, read default startup options for the server and for clients from option files.

On Windows, MySQL reads default options from the following files:

Filename

Purpose

windows-directory\my.ini

Global options

C:\my.cnf

Global options

windows-directory is the location of your Windows directory.

On Unix, MySQL reads default options from the following files:

Filename

Purpose

/etc/my.cnf

Global options

DATADIR/my.cnf

Server-specific options

defaults-extra-file

The file specified with --defaults-extra-file=path

~/.my.cnf

User-specific options

DATADIR is the MySQL data directory (typically `/usr/local/mysql/data' for a binary installation or `/usr/local/var' for a source installation). Note that this is the directory that was specified at configuration time, not the one specified with --datadir when mysqld starts up! (--datadir has no effect on where the server looks for option files, because the server looks for files before it processes any command-line arguments.)

Note that on Windows you should specify all paths in option files with `/' instead of `\'. If you use `\', you need to specify it twice, because `\' is the escape character in MySQL.

MySQL tries to read option files in the order listed above. If multiple option files exist, an option specified in a file read later takes precedence over the same option specified in a file read earlier. Options specified on the command-line take precedence over options specified in any option file. Some options can be specified using environment variables. Options specified on the command-line or in option files take precedence over environment variable values. See section Environment Variables.

The following programs support option files: mysql, mysqladmin, mysqld, mysqld_safe, mysql.server, mysqldump, mysqlimport, mysqlshow, mysqlcheck, myisamchk, and myisampack.

Since Version 4.0.2, you can use the loose prefix for command-line options (or options in my.cnf). If an option is prefixed by loose, the program reading it will not exit with an error if an option is unknown, but will rather only issue a warning:

 
shell> mysql --loose-no-such-option

Any long option that may be given on the command-line when running a MySQL program can be given in an option file as well (without the leading double dash). Run the program with the --help option to get a list of available options.

An option file can contain lines of the following forms:

#comment

Comment lines start with `#' or `;'. Empty lines are ignored.

[group]

group is the name of the program or group for which you want to set options. After a group line, any option or set-variable lines apply to the named group until the end of the option file or another group line is given.

option

This is equivalent to --option on the command-line.

option=value

This is equivalent to --option=value on the command-line.

set-variable = variable=value

This is equivalent to --set-variable variable=value on the command-line. Please note that --set-variable is deprecated since MySQL 4.0; as of that version, program variable names can be used as option names. On the command line, just use --variable=value. In an option file, use variable=value.

The [client] group allows you to specify options that apply to all MySQL clients (not mysqld). This is the perfect group to use to specify the password that you use to connect to the server. (But make sure the option file is readable and writable only by yourself.)

Note that for options and values, all leading and trailing blanks are automatically deleted. You may use the escape sequences `\b', `\t', `\n', `\r', `\\', and `\s' in your value string (`\s' == blank).

Here is a typical global option file:

 
[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
set-variable = key_buffer_size=16M
set-variable = max_allowed_packet=1M

[mysqldump]
quick

Here is typical user option file:

 
[client]
# The following password will be sent to all standard MySQL clients
password=my_password

[mysql]
no-auto-rehash
set-variable = connect_timeout=2

[mysqlhotcopy]
interactive-timeout

If you have a source distribution, you will find sample configuration files named `my-xxxx.cnf' in the `support-files' directory. If you have a binary distribution, look in the `DIR/support-files' directory, where DIR is the pathname to the MySQL installation directory (typically `C:\mysql' or `/usr/local/mysql'). Currently there are sample configuration files for small, medium, large, and very large systems. You can copy `my-xxxx.cnf' to your home directory (rename the copy to `.my.cnf') to experiment with this.

All MySQL programs that support option files support the following options:

Option

Description

--no-defaults

Don't read any option files.

--print-defaults

Print the program name and all options that it will get.

--defaults-file=full-path-to-default-file

Only use the given configuration file.

--defaults-extra-file=full-path-to-default-file

Read this configuration file after the global configuration file but before the user configuration file.

Note that the options just shown must be first on the command line to work, with the exception that --print-defaults may be used immediately after --defaults-file or --defaults-extra-file.

Note for developers: Option file handling is implemented simply by processing all matching options (that is, options in the appropriate group) before any command-line arguments. This works nicely for programs that use the last instance of an option that is specified multiple times. If you have an old program that handles multiply specified options this way but doesn't read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MySQL clients to see how to do this.

In shell scripts, you can use the my_print_defaults command to parse the option files. The following example shows the output that my_print_defaults might produce when asked to show the options found in the [client] and [mysql] groups:

 
shell> my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash

4.1.3 Running Multiple MySQL Servers on the Same Machine

4.1.3.1 Running Multiple Servers on Windows  
4.1.3.2 Running Multiple Servers on Unix  
4.1.3.3 Using Client Programs in a Multiple-Server Environment  

In some cases you might want to run multiple mysqld servers on the same machine. For example, you might want to test a new MySQL release while leaving your existing production setup undisturbed. Or you may want to give different users access to different mysqld servers that they manage themselves. (For example, you might be an Internet service provider that wants to provide independent MySQL installations for different customers.)

When you run multiple servers on a single machine, each server must have unique values for several operating parameters. At least the following options must be different for each server:

--port controls the port number for TCP/IP connections. --socket controls the socket file path on Unix and the name of the named pipe on Windows. (It's necessary to specify distinct pipe names on Windows only for those servers that support named pipe connections.) --shared-memory-base-name designates the shared memory name used by a Windows server to allow clients to connect via shared memory. --pid-file indicates the name of the file in which a Unix server writes its process ID.

If you use the following options, they must be different for each server:

If you want more performance, you can also specify the following options differently for each server, to spread load between several physical disks:

See section mysqld Command-line Options.

Generally, each server should also use a different data directory, which is specified using the --datadir=path option.

If you have multiple MySQL installations in different locations, normally you can specify the base installation directory for each server with the --basedir=path option to cause each server to use a different data directory, log files, and PID file. (The defaults for all these values are determined relative to the base directory.) In that case, the only other options you need to specify are the --socket and --port options. For example, suppose you install binary MySQL versions (`.tar' files) in different locations and start them using the command ./bin/mysqld_safe under the corresponding base directory of each installation. mysqld_safe will determine the proper --basedir option to pass to mysqld, and you need specify only the --socket and --port options to mysqld_safe.

As discussed in the following sections, it is possible to start additional servers by setting environment variables or by specifying appropriate command-line options. However, if you need to run multiple servers on a more permanent basis, it will be more convenient to use option files to specify for each server those option values that must be unique to it. See section `my.cnf' Option Files.

Warning: Normally you should never have two servers that update data in the same databases! If your OS doesn't support fault-free system locking, this may lead to unpleasant surprises! If (despite this warning) you run multiple servers using the same data directory and they have logging enabled, you must specify the names of the log files using the appropriate options. Otherwise, the servers may try to log to the same files.

This warning against sharing a data directory among servers also applies in an NFS environment. Allowing several MySQL servers to access a common data directory over NFS is a bad idea!

Make it easy for yourself: Forget about sharing a data directory among servers over NFS. A better solution is to have one computer with an operating system that efficiently handles threads and have several CPUs in it.

4.1.3.1 Running Multiple Servers on Windows

You can run multiple servers on Windows by starting them manually from the command line, each with appropriate operating parameters. On Windows NT-based systems, you also have the option of installing several servers as services and running them that way. General instructions for running MySQL servers from the command line or as services are given in Windows Notes. This section describes how to make sure you start each server with different values for those startup options that must be unique per server, such as the data directory. (These options are described in Running Multiple MySQL Servers on the Same Machine.)

Starting Multiple Windows Servers at the Command Line

To start multiple servers manually from the command line, you can specify the appropriate options on the command line or in an option file. It's more convenient to place the options in an option file, but it's necessary to make sure that each server gets its own set of options. To do this, create an option file for each server and tell the server the filename with a --defaults-file option when you run it.

Suppose you want to run mysqld on port 3307 with a data directory of `C:\mydata1', and mysqld-max on port 3308 with a data directory of `C:\mydata2'. To accomplish this, create two option files. For example, create one file `C:\my-opts1.cnf' that looks like this:

 
[mysqld]
datadir = C:/mydata1
port = 3307

Create a second file `C:\my-opts2.cnf' that looks like this:

 
[mysqld]
datadir = C:/mydata2
port = 3308

Then start each server with its own option file:

 
shell> mysqld --defaults-file=C:\my-opts1.cnf
shell> mysqld-max --defaults-file=C:\my-opts2.cnf

(On NT, the servers will start in the foreground, so you'll need to issue those two commands in separate console windows.)

To shut down the servers, you must connect to the appropriate port number:

 
shell> mysqladmin --port=3307 shutdown
shell> mysqladmin --port=3308 shutdown

If you want to allow named pipe connections in addition to TCP/IP connections, use the mysqld-nt or mysqld-max-nt servers and specify options that enable the named pipe and specify its name. (Each server must have a unique pipe name.) For example, the `C:\my-opts1.cnf' file might be written like this:

 
[mysqld]
datadir = C:/mydata1
port = 3307
enable-named-pipe
socket = mypipe1

Then start the server this way:

 
shell> mysqld-nt --defaults-file=C:\my-opts1.cnf

`C:\my-opts2.cnf' would be modified similarly.

Starting Multiple Windows Servers as Services

On NT-based systems, you can install multiple servers as services. (This is possible as of MySQL 4.0.2.) In this case, you must make sure that each server uses a different service name in addition to all the other parameters that must be unique per server.

For the following instructions, assume that you want to run mysqld-nt servers from two different versions of MySQL that are installed at `C:\mysql-4.0.8' and `C:\mysql-4.0.14', respectively. (This might be the case if you're running 4.0.8 as your production server, but want to test 4.0.14 before upgrading to it.)

The following principles are relevant when installing a MySQL service with the --install (or --install-manual) option:

These principles give you several ways to set up multiple services. The following instructions describe some examples. Before trying any of them, be sure you shut down and remove any existing MySQL services first.

To remove multiple services, use mysqld --remove for each one, specifying a service name following the --remove option if the service to remove has a name different than the default.

4.1.3.2 Running Multiple Servers on Unix

The easiest way is to run multiple servers on Unix is to compile them with different TCP/IP ports and socket files so that each one is listening on different network interfaces. Also, by compiling in different base directories for each installation, that automatically results in different compiled-in data directory, log file, and PID file locations for each of your servers.

Assume an existing server is configured for the default port number and socket file. To configure a new server to have different operating parameters, use a configure command something like this:

 
shell> ./configure --with-tcp-port=port_number \
             --with-unix-socket-path=file_name \
             --prefix=/usr/local/mysql-4.0.14

Here port_number and file_name should be different from the default port number and socket file pathname, and the --prefix value should specify an installation directory different than the one under which the existing MySQL installation is located.

If you have a MySQL server listening on a given port number, you can use the following command to find out what operating parameters it is using for several important configurable variables, including the base directory and socket name:

 
shell> mysqladmin --host=host_name --port=port_number variables

With the information displayed by that command, you can tell what option values not to use when configuring an additional server.

Note that if you specify "localhost" as a hostname, mysqladmin will default to using a Unix socket connection rather than TCP/IP. In MySQL 4.1, you can explicitly specify the connection protocol to use by using the --protocol={TCP | SOCKET | PIPE | MEMORY} option.

You don't have to compile a new MySQL server just to start with a different socket file and TCP/IP port number. It is also possible to specify those values at runtime. One way to do so is by using command-line options:

 
shell> /path/to/mysqld_safe --socket=file_name --port=port_number

To use another database directory for the second server, pass a --datadir=path option to mysqld_safe.

Another way to achieve a similar effect is to use environment variables to set the socket name and port number:

 
shell> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock
shell> MYSQL_TCP_PORT=3307
shell> export MYSQL_UNIX_PORT MYSQL_TCP_PORT
shell> scripts/mysql_install_db
shell> bin/mysqld_safe &

This is a quick and dirty method for starting a second server to use for testing. The nice thing about this method is that the environment variable settings will apply to any client programs that you invoke from the above shell. Thus, connections for those clients automatically will be directed to the second server!

Environment Variables includes a list of other environment variables you can use to affect mysqld.

For automatic server execution, your startup script that is executed at boot time should execute the following command once for each server with an appropriate option file path for each command:

 
mysqld_safe --defaults-file=path-to-option-file

Each option file should contain option values specific to a given server.

On Unix, the mysqld_multi script is another way to start multiple servers. See section mysqld_multi.

4.1.3.3 Using Client Programs in a Multiple-Server Environment

When you want to connect with a client program to a MySQL server that is listening to different network interfaces than those compiled into your client, you can use one of the following methods:

4.2 General Security Issues and the MySQL Access Privilege System

MySQL has an advanced but non-standard security/privilege system. This section describes how it works.

4.2.1 General Security Guidelines  
4.2.2 How to Make MySQL Secure Against Crackers  
4.2.3 Startup Options for mysqld Concerning Security  
4.2.4 Security issues with LOAD DATA LOCAL  
4.2.5 What the Privilege System Does  
4.2.6 How the Privilege System Works  
4.2.7 Privileges Provided by MySQL  
4.2.8 Connecting to the MySQL Server  
4.2.9 Access Control, Stage 1: Connection Verification  
4.2.10 Access Control, Stage 2: Request Verification  
4.2.11 Password Hashing in MySQL 4.1  
4.2.12 Causes of Access denied Errors  

4.2.1 General Security Guidelines

Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.

In discussing security, we emphasise the necessity of fully protecting the entire server host (not simply the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.

MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that a user may attempt to perform. There is also some support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications.

When running MySQL, follow these guidelines whenever possible:

4.2.2 How to Make MySQL Secure Against Crackers

When you connect to a MySQL server, you normally should use a password. The password is not transmitted in clear text over the connection, however the encryption algorithm is not very strong, and with some effort a clever attacker can crack the password if he is able to sniff the traffic between the client and the server. If the connection between the client and the server goes through an untrusted network, you should use an SSH tunnel to encrypt the communication.

All other information is transferred as text that can be read by anyone who is able to watch the connection. If you are concerned about this, you can use the compressed protocol (in MySQL Version 3.22 and above) to make things much harder. To make things even more secure you should use ssh. You can find an Open Source ssh client at http://www.openssh.org/, and a commercial ssh client at http://www.ssh.com/. With this, you can get an encrypted TCP/IP connection between a MySQL server and a MySQL client.

If you are using MySQL 4.0, you can also use internal OpenSSL support. See section Using Secure Connections.

To make a MySQL system secure, you should strongly consider the following suggestions:

4.2.3 Startup Options for mysqld Concerning Security

The following mysqld options affect security:

--local-infile[=(0|1)]

If one uses --local-infile=0 then one can't use LOAD DATA LOCAL INFILE.

--safe-show-database

With this option, the SHOW DATABASES command returns only those databases for which the user has some kind of privilege. From version 4.0.2 this option is deprecated and doesn't do anything (the option is enabled by default) as we now have the SHOW DATABASES privilege. See section GRANT and REVOKE Syntax.

--safe-user-create

If this is enabled, an user can't create new users with the GRANT command, if the user doesn't have the INSERT privilege for the mysql.user table. If you want to give a user access to just create new users with those privileges that the user has right to grant, you should give the user the following privilege:

 
mysql> GRANT INSERT(user) ON mysql.user TO 'user'@'hostname';

This will ensure that the user can't change any privilege columns directly, but has to use the GRANT command to give privileges to other users.

--skip-grant-tables

This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload.)

--skip-name-resolve

Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost.

--skip-networking

Don't allow TCP/IP connections over the network. All connections to mysqld must be made via Unix sockets. This option is unsuitable when using a MySQL version prior to 3.23.27 with the MIT-pthreads package, because Unix sockets were not supported by MIT-pthreads at that time.

--skip-show-database

Don't allow SHOW DATABASES command, unless the user has the SHOW DATABASES privilege. From version 4.0.2 you should no longer need this option, since access can now be granted specifically with the SHOW DATABASES privilege.

4.2.4 Security issues with LOAD DATA LOCAL

In MySQL 3.23.49 and MySQL 4.0.2, we added some new options to deal with possible security issues when it comes to LOAD DATA LOCAL.

There are two possible problems with supporting this command:

As the reading of the file is initiated from the server, one could theoretically create a patched MySQL server that could read any file on the client machine that the current user has read access to, when the client issues a query against the table.

In a web environment where the clients are connecting from a web server, a user could use LOAD DATA LOCAL to read any files that the web server process has read access to (assuming a user could run any command against the SQL server).

There are two separate fixes for this:

If you don't configure MySQL with --enable-local-infile, then LOAD DATA LOCAL will be disabled by all clients, unless one calls mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0) in the client. See section mysql_options().

For the mysql command-line client, LOAD DATA LOCAL can be enabled by specifying the option --local-infile[=1], or disabled with --local-infile=0.

By default, all MySQL clients and libraries are compiled with --enable-local-infile, to be compatible with MySQL 3.23.48 and before.

One can disable all LOAD DATA LOCAL commands in the MySQL server by starting mysqld with --local-infile=0.

In the case that LOAD DATA LOCAL INFILE is disabled in the server or the client, you will get the error message (1148):

 
The used command is not allowed with this MySQL version

4.2.5 What the Privilege System Does

The primary function of the MySQL privilege system is to authenticate a user connecting from a given host, and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE and DELETE.

Additional functionality includes the ability to have an anonymous user and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations.

4.2.6 How the Privilege System Works

The MySQL privilege system ensures that all users may do exactly the things that they are supposed to be allowed to do. When you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify. The system grants privileges according to your identity and what you want to do.

MySQL considers both your hostname and user name in identifying you because there is little reason to assume that a given user name belongs to the same person everywhere on the Internet. For example, the user joe who connects from office.com need not be the same person as the user joe who connects from elsewhere.com. MySQL handles this by allowing you to distinguish users on different hosts that happen to have the same name: you can grant joe one set of privileges for connections from office.com, and a different set of privileges for connections from elsewhere.com.

MySQL access control involves two stages:

Note that if your privileges are changed (either by yourself or someone else) while you are connected, those changes will not necessarily take effect with your next query or queries. See When Privilege Changes Take Effect for details.

The server uses the user, db, and host tables in the mysql database at both stages of access control. The fields in these grant tables are shown here:

Table name

user

db

host

Scope fields

Host

Host

Host

User

Db

Db

Password

User

Privilege fields

Select_priv

Select_priv

Select_priv

Insert_priv

Insert_priv

Insert_priv

Update_priv

Update_priv

Update_priv

Delete_priv

Delete_priv

Delete_priv

Index_priv

Index_priv

Index_priv

Alter_priv

Alter_priv

Alter_priv

Create_priv

Create_priv

Create_priv

Drop_priv

Drop_priv

Drop_priv

Grant_priv

Grant_priv

Grant_priv

References_priv

Reload_priv

Shutdown_priv

Process_priv

File_priv

Show_db_priv

Super_priv

Create_tmp_table_priv

Create_tmp_table_priv

Create_tmp_table_priv

Lock_tables_priv

Lock_tables_priv

Lock_tables_priv

Execute_priv

Repl_slave_priv

Repl_client_priv

ssl_type

ssl_cypher

x509_issuer

x509_cubject

max_questions

max_updates

max_connections

For the second stage of access control (request verification), the server may, if the request involves tables, additionally consult the tables_priv and columns_priv tables. The fields in these tables are shown here:

Table name

tables_priv

columns_priv

Scope fields

Host

Host

Db

Db

User

User

Table_name

Table_name

Column_name

Privilege fields

Table_priv

Column_priv

Column_priv

Other fields

Timestamp

Timestamp

Grantor

Each grant table contains scope fields and privilege fields.

Scope fields determine the scope of each entry in the tables, that is, the context in which the entry applies. For example, a user table entry with Host and User values of 'thomas.loc.gov' and 'bob' would be used for authenticating connections made to the server by bob from the host thomas.loc.gov. Similarly, a db table entry with Host, User, and Db fields of 'thomas.loc.gov', 'bob' and 'reports' would be used when bob connects from the host thomas.loc.gov to access the reports database. The tables_priv and columns_priv tables contain scope fields indicating tables or table/column combinations to which each entry applies.

For access-checking purposes, comparisons of Host values are case-insensitive. User, Password, Db, and Table_name values are case-sensitive. Column_name values are case-insensitive in MySQL Version 3.22.12 or later.

Privilege fields indicate the privileges granted by a table entry, that is, what operations can be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. The rules used to do this are described in Access Control, Stage 2: Request Verification.

Scope fields are strings, declared as shown here; the default value for each is the empty string:

Field name

Type

Notes

Host

CHAR(60)

User

CHAR(16)

Password

CHAR(16)

Db

CHAR(64)

(CHAR(60) for the tables_priv and columns_priv tables)

Table_name

CHAR(60)

Column_name

CHAR(60)

In the user, db and host tables, all privilege fields are declared as ENUM('N','Y')--each can have a value of 'N' or 'Y', and the default value is 'N'.

In the tables_priv and columns_priv tables, the privilege fields are declared as SET fields:

Table name

Field name

Possible set elements

tables_priv

Table_priv

'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'

tables_priv

Column_priv

'Select', 'Insert', 'Update', 'References'

columns_priv

Column_priv

'Select', 'Insert', 'Update', 'References'

Briefly, the server uses the grant tables like this:

Note that administrative privileges (RELOAD, SHUTDOWN, etc.) are specified only in the user table. This is because administrative operations are operations on the server itself and are not database-specific, so there is no reason to list such privileges in the other grant tables. In fact, only the user table need be consulted to determine whether you can perform an administrative operation.

The FILE privilege is specified only in the user table, too. It is not an administrative privilege as such, but your ability to read or write files on the server host is independent of the database you are accessing.

The mysqld server reads the contents of the grant tables once, when it starts up. Changes to the grant tables take effect as indicated in When Privilege Changes Take Effect.

When you modify the contents of the grant tables, it is a good idea to make sure that your changes set up privileges the way you want. For help in diagnosing problems, see Causes of Access denied Errors. For advice on security issues, see How to Make MySQL Secure Against Crackers.

A useful diagnostic tool is the mysqlaccess script, which Yves Carlier has provided for the MySQL distribution. Invoke mysqlaccess with the --help option to find out how it works. Note that mysqlaccess checks access using only the user, db and host tables. It does not check table- or column-level privileges.

4.2.7 Privileges Provided by MySQL

Information about user privileges is stored in the user, db, host, tables_priv, and columns_priv tables in the mysql database (that is, in the database named mysql). The MySQL server reads the contents of these tables when it starts up and under the circumstances indicated in When Privilege Changes Take Effect.

The names used in this manual to refer to the privileges provided by MySQL version 4.0.2 are shown here, along with the table column name associated with each privilege in the grant tables and the context in which the privilege applies:

Privilege

Column

Context

ALTER

Alter_priv

tables

DELETE

Delete_priv

tables

INDEX

Index_priv

tables

INSERT

Insert_priv

tables

SELECT

Select_priv

tables

UPDATE

Update_priv

tables

CREATE

Create_priv

databases, tables, or indexes

DROP

Drop_priv

databases or tables

GRANT

Grant_priv

databases or tables

REFERENCES

References_priv

databases or tables

CREATE TEMPORARY TABLES

Create_tmp_table_priv

server administration

EXECUTE

Execute_priv

server administration

FILE

File_priv

file access on server

LOCK TABLES

Lock_tables_priv

server administration

PROCESS

Process_priv

server administration

RELOAD

Reload_priv

server administration

REPLICATION CLIENT

Repl_client_priv

server administration

REPLICATION SLAVE

Repl_slave_priv

server administration

SHOW DATABASES

Show_db_priv

server administration

SHUTDOWN

Shutdown_priv

server administration

SUPER

Super_priv

server administration

The SELECT, INSERT, UPDATE, and DELETE privileges allow you to perform operations on rows in existing tables in a database.

SELECT statements require the SELECT privilege only if they actually retrieve rows from a table. You can execute certain SELECT statements even without permission to access any of the databases on the server. For example, you could use the mysql client as a simple calculator:

 
mysql> SELECT 1+1;
mysql> SELECT PI()*2;

The INDEX privilege allows you to create or drop (remove) indexes.

The ALTER privilege allows you to use ALTER TABLE.

The CREATE and DROP privileges allow you to create new databases and tables, or to drop (remove) existing databases and tables.

Note that if you grant the DROP privilege for the mysql database to a user, that user can drop the database in which the MySQL access privileges are stored!

The GRANT privilege allows you to give to other users those privileges you yourself possess.

The FILE privilege gives you permission to read and write files on the server using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. Any user to whom this privilege is granted can read any world readable file accessable by the MySQL server and create a new world readable file in any directory where the MySQL server can write. The user can also read any file in the current database directory. The user can however not change any existing file.

The remaining privileges are used for administrative operations, which are performed using the mysqladmin program. The table here shows which mysqladmin commands each administrative privilege allows you to execute:

Privilege

Commands permitted to privilege holders

RELOAD

reload, refresh, flush-privileges, flush-hosts, flush-logs, and flush-tables

SHUTDOWN

shutdown

PROCESS

processlist

SUPER

kill

The reload command tells the server to re-read the grant tables. The refresh command flushes all tables and opens and closes the log files. flush-privileges is a synonym for reload. The other flush-* commands perform functions similar to refresh but are more limited in scope, and may be preferable in some instances. For example, if you want to flush just the log files, flush-logs is a better choice than refresh.

The shutdown command shuts down the server.

The processlist command displays information about the threads executing within the server. The kill command kills server threads. You can always display or kill your own threads, but you need the PROCESS privilege to display and SUPER privilege to kill threads initiated by other users. See section KILL Syntax.

It is a good idea in general to grant privileges only to those users who need them, but you should exercise particular caution in granting certain privileges:

There are some things that you cannot do with the MySQL privilege system:

4.2.8 Connecting to the MySQL Server

MySQL client programs generally require that you specify connection parameters when you want to access a MySQL server: the host you want to connect to, your user name, and your password. For example, the mysql client can be started like this (optional arguments are enclosed between `[' and `]'):

 
shell> mysql [-h host_name] [-u user_name] [-pyour_pass]

Alternate forms of the -h, -u, and -p options are --host=host_name, --user=user_name, and --password=your_pass. Note that there is no space between -p or --password= and the password following it.

Note: Specifying a password on the command-line is not secure! Any user on your system may then find out your password by typing a command like: ps auxww. See section `my.cnf' Option Files.

mysql uses default values for connection parameters that are missing from the command-line:

Thus, for a Unix user joe, the following commands are equivalent:

 
shell> mysql -h localhost -u joe
shell> mysql -h localhost
shell> mysql -u joe
shell> mysql

Other MySQL clients behave similarly.

On Unix systems, you can specify different default values to be used when you make a connection, so that you need not enter them on the command-line each time you invoke a client program. This can be done in a couple of ways:

4.2.9 Access Control, Stage 1: Connection Verification

When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, then enters Stage 2 and waits for requests.

Your identity is based on two pieces of information:

Identity checking is performed using the three user table scope fields (Host, User, and Password). The server accepts the connection only if a user table entry matches your hostname and user name, and you supply the correct password.

Values in the user table scope fields may be specified as follows:

Non-blank Password values represent encrypted passwords. MySQL does not store passwords in plaintext form for anyone to see. Rather, the password supplied by a user who is attempting to connect is encrypted (using the PASSWORD() function). The encrypted password is then used when the client/server is checking if the password is correct. (This is done without the encrypted password ever traveling over the connection.) Note that from MySQL's point of view the encrypted password is the REAL password, so you should not give anyone access to it! In particular, don't give normal users read access to the tables in the mysql database! From version 4.1, MySQL employs a different password and login mechanism that is secure even if TCP/IP packets are sniffed and/or the mysql database is captured.

The examples here show how various combinations of Host and User values in user table entries apply to incoming connections:

Host value

User value

Connections matched by entry

'thomas.loc.gov'

'fred'

fred, connecting from thomas.loc.gov

'thomas.loc.gov'

''

Any user, connecting from thomas.loc.gov

'%'

'fred'

fred, connecting from any host

'%'

''

Any user, connecting from any host

'%.loc.gov'

'fred'

fred, connecting from any host in the loc.gov domain

'x.y.%'

'fred'

fred, connecting from x.y.net, x.y.com,x.y.edu, etc. (this is probably not useful)

'144.155.166.177'

'fred'

fred, connecting from the host with IP address 144.155.166.177

'144.155.166.%'

'fred'

fred, connecting from any host in the 144.155.166 class C subnet

'144.155.166.0/255.255.255.0'

'fred'

Same as previous example

Because you can use IP wildcard values in the Host field (for example, '144.155.166.%' to match every host on a subnet), there is the possibility that someone might try to exploit this capability by naming a host 144.155.166.somewhere.com. To foil such attempts, MySQL disallows matching on hostnames that start with digits and a dot. Thus, if you have a host named something like 1.2.foo.com, its name will never match the Host column of the grant tables. Only an IP number can match an IP wildcard value.

An incoming connection may be matched by more than one entry in the user table. For example, a connection from thomas.loc.gov by fred would be matched by several of the entries just shown above. How does the server choose which entry to use if more than one matches? The server resolves this question by sorting the user table after reading it at startup time, then looking through the entries in sorted order when a user attempts to connect. The first matching entry is the one that is used.

user table sorting works as follows. Suppose the user table looks like this:

 
+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ...
| %         | jeffrey  | ...
| localhost | root     | ...
| localhost |          | ...
+-----------+----------+-

When the server reads in the table, it orders the entries with the most-specific Host values first ('%' in the Host column means "any host" and is least specific). Entries with the same Host value are ordered with the most-specific User values first (a blank User value means "any user" and is least specific). The resulting sorted user table looks like this:

 
+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| localhost | root     | ...
| localhost |          | ...
| %         | jeffrey  | ...
| %         | root     | ...
+-----------+----------+-

When a connection is attempted, the server looks through the sorted entries and uses the first match found. For a connection from localhost by jeffrey, the entries with 'localhost' in the Host column match first. Of those, the entry with the blank user name matches both the connecting hostname and user name. (The '%'/'jeffrey' entry would have matched, too, but it is not the first match in the table.)

Here is another example. Suppose the user table looks like this:

 
+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| %              | jeffrey  | ...
| thomas.loc.gov |          | ...
+----------------+----------+-

The sorted table looks like this:

 
+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| thomas.loc.gov |          | ...
| %              | jeffrey  | ...
+----------------+----------+-

A connection from thomas.loc.gov by jeffrey is matched by the first entry, whereas a connection from whitehouse.gov by jeffrey is matched by the second.

A common misconception is to think that for a given user name, all entries that explicitly name that user will be used first when the server attempts to find a match for the connection. This is simply not true. The previous example illustrates this, where a connection from thomas.loc.gov by jeffrey is first matched not by the entry containing 'jeffrey' as the User field value, but by the entry with no user name!

If you have problems connecting to the server, print out the user table and sort it by hand to see where the first match is being made. If connection was successful, but your privileges are not what you expected you may use CURRENT_USER() function (new in version 4.0.6) to see what user/host combination your connection actually matched. See section CURRENT_USER().

4.2.10 Access Control, Stage 2: Request Verification

Once you establish a connection, the server enters Stage 2. For each request that comes in on the connection, the server checks whether you have sufficient privileges to perform it, based on the type of operation you wish to perform. This is where the privilege fields in the grant tables come into play. These privileges can come from any of the user, db, host, tables_priv, or columns_priv tables. The grant tables are manipulated with GRANT and REVOKE commands. See section GRANT. (You may find it helpful to refer to How the Privilege System Works, which lists the fields present in each of the grant tables.)

The user table grants privileges that are assigned to you on a global basis and that apply no matter what the current database is. For example, if the user table grants you the DELETE privilege, you can delete rows from any database on the server host! In other words, user table privileges are superuser privileges. It is wise to grant privileges in the user table only to superusers such as server or database administrators. For other users, you should leave the privileges in the user table set to 'N' and grant privileges on a database-specific basis only, using the db and host tables.

The db and host tables grant database-specific privileges. Values in the scope fields may be specified as follows:

The db and host tables are read in and sorted when the server starts up (at the same time that it reads the user table). The db table is sorted on the Host, Db, and User scope fields, and the host table is sorted on the Host and Db scope fields. As with the user table, sorting puts the most-specific values first and least-specific values last, and when the server looks for matching entries, it uses the first match that it finds.

The tables_priv and columns_priv tables grant table- and column-specific privileges. Values in the scope fields may be specified as follows:

The tables_priv and columns_priv tables are sorted on the Host, Db, and User fields. This is similar to db table sorting, although the sorting is simpler because only the Host field may contain wildcards.

The request verification process is described here. (If you are familiar with the access-checking source code, you will notice that the description here differs slightly from the algorithm used in the code. The description is equivalent to what the code actually does; it differs only to make the explanation simpler.)

For administrative requests (SHUTDOWN, RELOAD, etc.), the server checks only the user table entry, because that is the only table that specifies administrative privileges. Access is granted if the entry allows the requested operation and denied otherwise. For example, if you want to execute mysqladmin shutdown but your user table entry doesn't grant the SHUTDOWN privilege to you, access is denied without even checking the db or host tables. (They contain no Shutdown_priv column, so there is no need to do so.)

For database-related requests (INSERT, UPDATE, etc.), the server first checks the user's global (superuser) privileges by looking in the user table entry. If the entry allows the requested operation, access is granted. If the global privileges in the user table are insufficient, the server determines the user's database-specific privileges by checking the db and host tables:

  1. The server looks in the db table for a match on the Host, Db, and User fields. The Host and User fields are matched to the connecting user's hostname and MySQL user name. The Db field is matched to the database the user wants to access. If there is no entry for the Host and User, access is denied.
  2. If there is a matching db table entry and its Host field is not blank, that entry defines the user's database-specific privileges.
  3. If the matching db table entry's Host field is blank, it signifies that the host table enumerates which hosts should be allowed access to the database. In this case, a further lookup is done in the host table to find a match on the Host and Db fields. If no host table entry matches, access is denied. If there is a match, the user's database-specific privileges are computed as the intersection (not the union!) of the privileges in the db and host table entries, that is, the privileges that are 'Y' in both entries. (This way you can grant general privileges in the db table entry and then selectively restrict them on a host-by-host basis using the host table entries.)

After determining the database-specific privileges granted by the db and host table entries, the server adds them to the global privileges granted by the user table. If the result allows the requested operation, access is granted. Otherwise, the server checks the user's table and column privileges in the tables_priv and columns_priv tables and adds those to the user's privileges. Access is allowed or denied based on the result.

Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarised like this:

 
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges

It may not be apparent why, if the global user entry privileges are initially found to be insufficient for the requested operation, the server adds those privileges to the database-, table-, and column-specific privileges later. The reason is that a request might require more than one type of privilege. For example, if you execute an INSERT ... SELECT statement, you need both INSERT and SELECT privileges. Your privileges might be such that the user table entry grants one privilege and the db table entry grants the other. In this case, you have the necessary privileges to perform the request, but the server cannot tell that from either table by itself; the privileges granted by the entries in both tables must be combined.

The host table can be used to maintain a list of secure servers.

At TcX, the host table contains a list of all machines on the local network. These are granted all privileges.

You can also use the host table to indicate hosts that are not secure. Suppose you have a machine public.your.domain that is located in a public area that you do not consider secure. You can allow access to all hosts on your network except that machine by using host table entries like this:

 
+--------------------+----+-
| Host               | Db | ...
+--------------------+----+-
| public.your.domain | %  | ... (all privileges set to 'N')
| %.your.domain      | %  | ... (all privileges set to 'Y')
+--------------------+----+-

Naturally, you should always test your entries in the grant tables (for example, using mysqlaccess) to make sure your access privileges are actually set up the way you think they are.

4.2.11 Password Hashing in MySQL 4.1

MySQL user accounts are listed in the user table of the mysql database. Each MySQL account is assigned a password, although what is stored in the Password column of the user table is not the plaintext version of the password, but a hash value computed from it. Password hash values are computed by the PASSWORD() function.

MySQL uses passwords in two phases of client/server communication:

In other words, the server uses hash values during authentication when a client first attempts to connect. The server generates hash values if a connected client invokes the PASSWORD() function or uses a GRANT or SET PASSWORD statement to set or change a password.

The password hashing mechanism was updated in MySQL 4.1 to provide better security and to reduce the risk of passwords being stolen. However, this new mechanism is understood only by the 4.1 server and 4.1 clients, which can result in some compatibility problems. A 4.1 client can connect to a pre-4.1 server, because the client understands both the old and new password hashing mechanisms. However, a pre-4.1 client that attempts to connect to a 4.1 server may run into difficulties. For example, a 4.0 mysql client that attempts to connect to a 4.1 server may fail with the following error message:

 
shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client

The following discussion describes the differences between the old and new password mechanisms, and what you should do if you upgrade your server to 4.1 but need to maintain backward compatibility with pre-4.1 clients.

Note: This discussion contrasts 4.1 behaviour with pre-4.1 behaviour, but the 4.1 behaviour described here actually begins with 4.1.1. MySQL 4.1.0 is an "odd" release because it has a slightly different mechanism than that implemented in 4.1.1 and up. Differences between 4.1.0 and more recent versions are described later.

Prior to MySQL 4.1, password hashes computed by the PASSWORD() function are 16 bytes long. Such hashes look like this:

 
mysql> SELECT PASSWORD('mypass');
+--------------------+
| PASSWORD('mypass') |
+--------------------+
| 6f8c114b58f2ce9e   |
+--------------------+

The Password column of the user table (in which these hashes are stored) also is 16 bytes long before MySQL 4.1.

As of MySQL 4.1, the PASSWORD() function has been modified to produce a longer 41-byte hash value:

 
mysql> SELECT PASSWORD('mypass');
+-----------------------------------------------+
| PASSWORD('mypass')                            |
+-----------------------------------------------+
| *43c8aa34cdc98eddd3de1fe9a9c2c2a9f92bb2098d75 |
+-----------------------------------------------+

Accordingly, the Password column in the user table also must be 41 bytes long to store these values:

A widened Password column can store password hashes in both the old and new formats. The format of any given password hash value can be determined two ways:

The longer password hash format has better cryptographic properties, and client authentication based on long hashes is more secure than that based on the older short hashes.

The differences between short and long password hashes are relevant both for how the server uses passwords during authentication and for how it generates password hashes for connected clients that perform password-changing operations.

The way in which the server uses password hashes during authentication is affected by the width of the Password column:

For short-hash accounts, the authentication process is actually a bit more secure for 4.1 clients than for older clients. In terms of security, the gradient from least to most secure is:

The way in which the server generates password hashes for connected clients is affected by the width of the Password column and by the --old-passwords option. A 4.1 server generates long hashes only if certain conditions are met: The Password column must be wide enough to hold long values and the --old-passwords option must not be given. These conditions apply as follows:

The purpose of the --old-passwords option is to allow you to maintain backward compatibility with pre-4.1 clients under circumstances where the server would otherwise generate long password hashes. It doesn't affect authentication (4.1 clients can still use accounts that have long password hashes), but it does prevent creation of a long password hash in the user table as the result of a password-changing operation. Were that to occur, the account no longer could be used by pre-4.1 clients. Without the --old-passwords option, the following scenario is possible:

This scenario illustrates that it is dangerous to run a 4.1 server without using the --old-passwords option if you must support older pre-4.1 clients. By running the server with --old-passwords, password-changing operations will not generate long password hashes and thus do not cause accounts to become inaccessible to older clients. (Those clients cannot inadvertently lock themselves out by changing their password and ending up with a long password hash.)

The downside of the --old-passwords option is that any passwords you create or change will use short hashes, even for 4.1 clients. Thus, you lose the additional security provided by long password hashes. If you want to create an account that has a long hash (for example, for use by 4.1 clients), you must do so while running the server without --old-passwords.

The following scenarios are possible for running a 4.1 server:

Scenario 1) Narrow Password column in user table

Scenario 2) Long Password column; server not started with --old-passwords option

As indicated earlier, a danger in this scenario is that it is possible for accounts that have a short password hash to become inaccessible to pre-4.1 clients. Any change to such an account's password made via GRANT, SET PASSWORD, or PASSWORD() results in the account being given a long password hash, and from that point on, no pre-4.1 client can authenticate to that account until the client upgrades to 4.1.

Scenario 3) Long Password column; server started with --old-passwords option

In this scenario, you cannot create accounts that have long password hashes, because --old-passwords prevents generation of long hashes. Also, if you create an account with a long hash before using the --old-passwords option, changing the account's password while --old-passwords is in effect results in the account being given a short password, causing it to lose the security benefits of a longer hash.

The disadvantages for these scenarios may be summarized as follows:

Scenario 1) You cannot take advantage of longer hashes that provide more secure authentication.

Scenario 2) Accounts with short hashes become inaccessible to pre-4.1 clients if you change their passwords without explicitly using OLD_PASSWORD().

Scenario 3) --old-passwords prevents accounts with short hashes from becoming inaccessible, but password-changing operations cause accounts with long hashes to revert to short hashes, and you cannot change them back to long hashes while --old-passwords is in effect.

Implications of Password Hashing Changes for Application Programs

An upgrade to MySQL 4.1 can cause a compatibility issue for applications that use PASSWORD() to generate passwords for their own purposes. (Applications really should not do this, because PASSWORD() should be used only to manage passwords for MySQL accounts. But some applications use PASSWORD() for their own purposes anyway.) If you upgrade to 4.1 and run the server under conditions where it generates long password hashes, an application that uses PASSWORD() for its own passwords will break. The recommended course of action is to modify the application to use another function such as SHA1() or MD5() to produce hashed values. If that is not possible, you can use the OLD_PASSWORD() function, which is provided to generate short hashes in the old format. (But note that OLD_PASSWORD() may one day no longer be supported.)

If the server is running under circumstances where it generates short hashes, OLD_PASSWORD() is available but is equivalent to PASSWORD().

Password hashing in MySQL 4.1.0 differs from hashing in 4.1.1 and up. The 4.1.0 differences are:

4.2.12 Causes of Access denied Errors

If you encounter Access denied errors when you try to connect to the MySQL server, the following list indicates some courses of action you can take to correct the problem:

4.3 MySQL User Account Management

4.3.1 GRANT and REVOKE Syntax  
4.3.2 MySQL User Names and Passwords  
4.3.3 When Privilege Changes Take Effect  
4.3.4 Setting Up the Initial MySQL Privileges  
4.3.5 Adding New Users to MySQL  
4.3.6 Limiting user resources  
4.3.7 Setting Up Passwords  
4.3.8 Keeping Your Password Secure  
4.3.9 Using Secure Connections  

4.3.1 GRANT and REVOKE Syntax

 
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY [PASSWORD] 'password']
        [, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER cipher [AND]]
        [ISSUER issuer [AND]]
        [SUBJECT subject]]
    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
                          MAX_UPDATES_PER_HOUR # |
                          MAX_CONNECTIONS_PER_HOUR #]]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    FROM user_name [, user_name ...]

GRANT is implemented in MySQL Version 3.22.11 or later. For earlier MySQL versions, the GRANT statement does nothing.

The GRANT and REVOKE commands allow system administrators to create users and grant and revoke rights to MySQL users at four privilege levels:

Global level

Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table. REVOKE ALL ON *.* will revoke only global privileges.

Database level

Database privileges apply to all tables in a given database. These privileges are stored in the mysql.db and mysql.host tables. REVOKE ALL ON db.* will revoke only database privileges.

Table level

Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. REVOKE ALL ON db.table will revoke only table privileges.

Column level

Column privileges apply to single columns in a given table. These privileges are stored in the mysql.columns_priv table. When using REVOKE you must specify the same columns that were granted.

For the GRANT and REVOKE statements, priv_type may be specified as any of the following:

ALL [PRIVILEGES]

Sets all simple privileges except WITH GRANT OPTION

ALTER

Allows usage of ALTER TABLE

CREATE

Allows usage of CREATE TABLE

CREATE TEMPORARY TABLES

Allows usage of CREATE TEMPORARY TABLE

DELETE

Allows usage of DELETE

DROP

Allows usage of DROP TABLE.

EXECUTE

Allows the user to run stored procedures (MySQL 5.0)

FILE

Allows usage of SELECT ... INTO OUTFILE and LOAD DATA INFILE.

INDEX

Allows usage of CREATE INDEX and DROP INDEX

INSERT

Allows usage of INSERT

LOCK TABLES

Allows usage of LOCK TABLES on tables for which one has the SELECT privilege.

PROCESS

Allows usage of SHOW FULL PROCESSLIST

REFERENCES

For the future

RELOAD

Allows usage of FLUSH

REPLICATION CLIENT

Gives the right to the user to ask where the slaves/masters are.

REPLICATION SLAVE

Needed for the replication slaves (to read binlogs from master).

SELECT

Allows usage of SELECT

SHOW DATABASES

SHOW DATABASES shows all databases.

SHUTDOWN

Allows usage of mysqladmin shutdown

SUPER

Allows one connect (once) even if max_connections is reached and execute commands CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS and SET GLOBAL

UPDATE

Allows usage of UPDATE

USAGE

Synonym for "no privileges."

GRANT OPTION

Synonym for WITH GRANT OPTION

USAGE can be used when you want to create a user that has no privileges.

The privileges CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION ..., SHOW DATABASES and SUPER are new for in version 4.0.2. To use these new privileges after upgrading to 4.0.2, you have to run the mysql_fix_privilege_tables script.

In older MySQL versions, the PROCESS privilege gives the same rights as the new SUPER privilege.

To revoke the GRANT privilege from a user, use a priv_type value of GRANT OPTION:

 
mysql> REVOKE GRANT OPTION ON ... FROM ...;

The only priv_type values you can specify for a table are SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX, and ALTER.

The only priv_type values you can specify for a column (that is, when you use a column_list clause) are SELECT, INSERT, and UPDATE.

MySQL allows you to create database level privileges even if the database doesn't exist, to make it easy to prepare for database usage. Currently MySQL does however not allow one to create table level grants if the table doesn't exist. MySQL will not automatically revoke any privileges even if you drop a table or drop a database.

You can set global privileges by using ON *.* syntax. You can set database privileges by using ON db_name.* syntax. If you specify ON * and you have a current database, you will set the privileges for that database. (Warning: if you specify ON * and you don't have a current database, you will affect the global privileges!)

Please note: the `_' and `%' wildcards are allowed when specifying database names in GRANT commands. This means that if you wish to use for instance a `_' character as part of a database name, you should specify it as `\_' in the GRANT command, to prevent the user from being able to access additional databases matching the wildcard pattern, for example, GRANT ... ON `foo\_bar`.* TO ....

In order to accommodate granting rights to users from arbitrary hosts, MySQL supports specifying the user_name value in the form user@host. If you want to specify a user string containing special characters (such as `-'), or a host string containing special characters or wildcard characters (such as `%'), you can quote the user or host name (for example, 'test-user'@'test-hostname').

You can specify wildcards in the hostname. For example, user@'%.loc.gov' applies to user for any host in the loc.gov domain, and user@'144.155.166.%' applies to user for any host in the 144.155.166 class C subnet.

The simple form user is a synonym for user@"%".

MySQL doesn't support wildcards in user names. Anonymous users are defined by inserting entries with User='' into the mysql.user table or creating an user with an empty name with the GRANT command.

Note: if you allow anonymous users to connect to the MySQL server, you should also grant privileges to all local users as user@localhost because otherwise the anonymous user entry for the local host in the mysql.user table will be used when the user tries to log into the MySQL server from the local machine!

You can verify if this applies to you by executing this query:

 
mysql> SELECT Host,User FROM mysql.user WHERE User='';

For the moment, GRANT only supports host, table, database, and column names up to 60 characters long. A user name can be up to 16 characters.

The privileges for a table or column are formed from the logical OR of the privileges at each of the four privilege levels. For example, if the mysql.user table specifies that a user has a global SELECT privilege, this can't be denied by an entry at the database, table, or column level.

The privileges for a column can be calculated as follows:

 
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges

In most cases, you grant rights to a user at only one of the privilege levels, so life isn't normally as complicated as above. The details of the privilege-checking procedure are presented in General Security Issues and the MySQL Access Privilege System.

If you grant privileges for a user/hostname combination that does not exist in the mysql.user table, an entry is added and remains there until deleted with a DELETE command. In other words, GRANT may create user table entries, but REVOKE will not remove them; you must do that explicitly using DELETE.

In MySQL Version 3.22.12 or later, if a new user is created or if you have global grant privileges, the user's password will be set to the password specified by the IDENTIFIED BY clause, if one is given. If the user already had a password, it is replaced by the new one.

If you don't want to send the password in clear text you can use the PASSWORD option followed by a scrambled password from SQL function PASSWORD() or the C API function make_scrambled_password(char *to, const char *password).

Warning: if you create a new user but do not specify an IDENTIFIED BY clause, the user has no password. This is insecure.

Passwords can also be set with the SET PASSWORD command. See section SET.

If you grant privileges for a database, an entry in the mysql.db table is created if needed. When all privileges for the database have been removed with REVOKE, this entry is deleted.

If a user doesn't have any privileges on a table, the table is not displayed when the user requests a list of tables (for example, with a SHOW TABLES statement). The same is true for SHOW DATABASES.

The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level. You should be careful to whom you give the GRANT privilege, as two users with different privileges may be able to join privileges!

MAX_QUERIES_PER_HOUR #, MAX_UPDATES_PER_HOUR # and MAX_CONNECTIONS_PER_HOUR # are new in MySQL version 4.0.2. These options limit the number of queries/updates and logins the user can do during one hour. If # is 0 (default), then this means that there are no limitations for that user. See section Limiting user resources. Note: to specify any of these options for an existing user without adding other additional privileges, use GRANT USAGE ON *.* ... WITH MAX_....

You cannot grant another user a privilege you don't have yourself; the GRANT privilege allows you to give away only those privileges you possess.

Be aware that when you grant a user the GRANT privilege at a particular privilege level, any privileges the user already possesses (or is given in the future!) at that level are also grantable by that user. Suppose you grant a user the INSERT privilege on a database. If you then grant the SELECT privilege on the database and specify WITH GRANT OPTION, the user can give away not only the SELECT privilege, but also INSERT. If you then grant the UPDATE privilege to the user on the database, the user can give away the INSERT, SELECT and UPDATE.

You should not grant ALTER privileges to a normal user. If you do that, the user can try to subvert the privilege system by renaming tables!

Note that if you are using table or column privileges for even one user, the server examines table and column privileges for all users and this will slow down MySQL a bit.

When mysqld starts, all privileges are read into memory. Database, table, and column privileges take effect at once, and user-level privileges take effect the next time the user connects. Modifications to the grant tables that you perform using GRANT or REVOKE are noticed by the server immediately. If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges to tell the server to reload the grant tables. See section When Privilege Changes Take Effect.

The biggest differences between the SQL standard and MySQL versions of GRANT are:

For a description of using REQUIRE, see Using Secure Connections.

4.3.2 MySQL User Names and Passwords

There are several distinctions between the way user names and passwords are used by MySQL and the way they are used by Unix or Windows:

MySQL users and their privileges are normally created with the GRANT command. See section GRANT and REVOKE Syntax.

When you login to a MySQL server with a command-line client you should specify the password with --password=your-password. See section Connecting to the MySQL Server.

 
mysql --user=monty --password=guess database_name

If you want the client to prompt for a password, you should use --password without any argument

 
mysql --user=monty --password database_name

or the short form:

 
mysql -u monty -p database_name

Note that in the last example the password is not 'database_name'.

If you want to use the -p option to supply a password you should do so like this:

 
mysql -u monty -pguess database_name

On some systems, the library call that MySQL uses to prompt for a password will automatically cut the password to 8 characters. Internally MySQL doesn't have any limit for the length of the password.

4.3.3 When Privilege Changes Take Effect

When mysqld starts, all grant table contents are read into memory and become effective at that point.

Modifications to the grant tables that you perform using GRANT, REVOKE, or SET PASSWORD are noticed by the server immediately.

If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges or mysqladmin reload to tell the server to reload the grant tables. Otherwise, your changes will have no effect until you restart the server. If you change the grant tables manually but forget to reload the privileges, you will be wondering why your changes don't seem to make any difference!

When the server notices that the grant tables have been changed, existing client connections are affected as follows:

4.3.4 Setting Up the Initial MySQL Privileges

After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. See section Quick Installation Overview. The mysql_install_db script starts up the mysqld server, then initialises the grant tables to contain the following set of privileges:

Note: the default privileges are different for Windows. See section Running MySQL on Windows.

Because your installation is initially wide open, one of the first things you should do is specify a password for the MySQL root user. You can do this as follows (note that you specify the password using the PASSWORD() function):

 
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');

Replace 'new_password' with the password that you want to use.

If you know what you are doing, you can also directly manipulate the privilege tables:

 
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
    ->     WHERE user='root';
mysql> FLUSH PRIVILEGES;

Another way to set the password is by using the mysqladmin command:

 
shell> mysqladmin -u root password new_password

Only users with write/update access to the mysql database can change the password for other users. All normal users (not anonymous ones) can only change their own password with either of the above commands or with SET PASSWORD=PASSWORD('new_password').

Note that if you update the password in the user table directly using UPDATE, you must tell the server to re-read the grant tables (with FLUSH PRIVILEGES), because the change will go unnoticed otherwise.

Once the root password has been set, thereafter you must supply that password when you connect to the server as root.

You may wish to leave the root password blank so that you don't need to specify it while you perform additional setup or testing. However, be sure to set it before using your installation for any real production work.

See the scripts/mysql_install_db script to see how it sets up the default privileges. You can use this as a basis to see how to add other users.

If you want the initial privileges to be different from those just described above, you can modify mysql_install_db before you run it.

To re-create the grant tables completely, remove all the `.frm', `.MYI', and `.MYD' files in the directory containing the mysql database. (This is the directory named `mysql' under the database directory, which is listed when you run mysqld --help.) Then run the mysql_install_db script, possibly after editing it first to have the privileges you want.

Note: for MySQL versions older than Version 3.22.10, you should not delete the `.frm' files. If you accidentally do this, you should copy them back from your MySQL distribution before running mysql_install_db.

4.3.5 Adding New Users to MySQL

You can add users two different ways: by using GRANT statements or by manipulating the MySQL grant tables directly. The preferred method is to use GRANT statements, because they are more concise and less error-prone. See section GRANT and REVOKE Syntax.

There are also several contributed programs (such as phpMyAdmin) that can be used to create and administrate users.

The following examples show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults described in the previous section. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the INSERT privilege for the mysql database and the RELOAD administrative privilege. Also, if you have changed the root user password, you must specify it for the mysql commands here.

First, use the mysql program to connect to the server as the MySQL root user:

 
shell> mysql --user=root mysql

Then you can add new users by issuing GRANT statements:

 
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@'%'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;

These GRANT statements set up three new users:

monty

A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order.

admin

A user who can connect from localhost without a password and who is granted the RELOAD and PROCESS administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin processlist . No database-level privileges are granted. (They can be granted later by issuing additional GRANT statements.)

dummy

A user who can connect without a password, but only from the local host. No privileges are granted--the USAGE privilege type allows you to create a user with no privileges. It has the effect of setting all the global privileges to 'N'. It is assumed that you will grant specific privileges to the account later.

You can also add the same user access information directly by issuing INSERT statements and then telling the server to reload the grant tables:

 
shell> mysql --user=root mysql
mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),
    ->          'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'),
    ->          'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
    ->           Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
    ->                  VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;

Depending on your MySQL version, you may have to use a different number of 'Y' values above. (Versions prior to Version 3.22.11 have fewer privilege columns, and versions from 4.0.2 on have more.) For the admin user, the more readable extended INSERT syntax using SET that is available starting with Version 3.22.11 is used.

Note that to set up a superuser, you need only create a user table entry with the privilege fields set to 'Y'. No db or host table entries are necessary.

In the last INSERT statement (for the dummy user), only the Host, User, and Password columns in the user table record are assigned values. None of the privilege columns are set explicitly, so MySQL assigns them all the default value of 'N'. This is the same thing that GRANT USAGE does.

The following example adds a user custom who can access the bankaccount database only from the local host, the expenses database only from the host whitehouse.gov, and the customer database only from the host server.domain. He wants to use the password obscure from all three hosts.

To set up this user's privileges using GRANT statements, run these commands:

 
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON bankaccount.*
    ->     TO custom@localhost
    ->     IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON expenses.*
    ->     TO custom@'whitehouse.gov'
    ->     IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON customer.*
    ->     TO custom@'server.domain'
    ->     IDENTIFIED BY 'obscure';

To set up the user's privileges by modifying the grant tables directly, run these commands (note the FLUSH PRIVILEGES at the end):

 
shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('localhost','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('whitehouse.gov','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('server.domain','custom',PASSWORD('obscure'));
mysql> INSERT INTO db
    -> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
    ->  Create_priv,Drop_priv)
    -> VALUES
    -> ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
    -> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
    ->  Create_priv,Drop_priv)
    -> VALUES
    -> ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
    -> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
    ->  Create_priv,Drop_priv)
    -> VALUES('server.domain','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

As in the preceding example that used INSERT statements, you may need to use a different number of 'Y' values, depending on your version of MySQL.

The first three INSERT statements add user table entries that allow user custom to connect from the various hosts with the given password, but grant no permissions to him (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual, after you modify the grant tables directly , you must tell the server to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.

If you want to give a specific user access from any machine in a given domain (for example, mydomain.com), you can issue a GRANT statement like the following:

 
mysql> GRANT ...
    ->     ON *.*
    ->     TO myusername@'%.mydomain.com'
    ->     IDENTIFIED BY 'mypassword';

To do the same thing by modifying the grant tables directly, do this:

 
mysql> INSERT INTO user VALUES ('%.mydomain.com', 'myusername',
    ->             PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;

4.3.6 Limiting user resources

Starting from MySQL 4.0.2 one can limit certain resources per user.

So far, the only available method of limiting usage of MySQL server resources has been setting the max_user_connections startup variable to a non-zero value. But this method is strictly global and does not allow for management of individual users, which could be of particular interest to Internet Service Providers.

Therefore, management of three resources is introduced on the individual user level:

A user in the aforementioned context is a single entry in the user table, which is uniquely identified by its user and host columns.

All users are by default not limited in using the above resources, unless the limits are granted to them. These limits can be granted only via global GRANT (*.*), using this syntax:

 
GRANT ... WITH MAX_QUERIES_PER_HOUR N1
               MAX_UPDATES_PER_HOUR N2
               MAX_CONNECTIONS_PER_HOUR N3;

One can specify any combination of the above resources. N1, N2, and N3 are integers and represent counts per hour.

If a user reaches the limit on number of connections within one hour, no further connections will be accepted until that hour is up. Similarly, if the user reaches the limit on number of queries or updates, further queries or updates will be rejected until the hour is up. In all cases, an appropriate error message shall be issued.

Current usage values for a particular user can be flushed (set to zero) by issuing a GRANT statement with any of the above clauses, including a GRANT statement with the current values.

Also, current values for all users will be flushed if privileges are reloaded (in the server or using mysqladmin reload) or if the FLUSH USER_RESOURCES command is issued.

The feature is enabled as soon as a single user is granted with any of the limiting GRANT clauses.

As a prerequisite for enabling this feature, the user table in the mysql database must contain the additional columns, as defined in the table creation scripts mysql_install_db and mysql_install_db.sh in `scripts' subdirectory.

4.3.7 Setting Up Passwords

In most cases you should use GRANT to set up your users/passwords, so the following only applies for advanced users. See section GRANT.

The examples in the preceding sections illustrate an important principle: when you store a non-empty password using INSERT or UPDATE statements, you must use the PASSWORD() function to encrypt it. This is because the user table stores passwords in encrypted form, not as plaintext. If you forget that fact, you are likely to attempt to set passwords like this:

 
shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;

The result is that the plaintext value 'biscuit' is stored as the password in the user table. When the user jeffrey attempts to connect to the server using this password, the mysql client encrypts it with PASSWORD(), generates an authentification vector based on encrypted password and a random number, obtained from server, and sends the result to the server. The server uses the password value in the user table (that is not encrypted value 'biscuit') to perform the same calculations, and compares results. The comparison fails and the server rejects the connection:

 
shell> mysql -u jeffrey -pbiscuit test
Access denied

Passwords must be encrypted when they are inserted in the user table, so the INSERT statement should have been specified like this instead:

 
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('%','jeffrey',PASSWORD('biscuit'));

You must also use the PASSWORD() function when you use SET PASSWORD statements:

 
mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');

If you set passwords using the GRANT ... IDENTIFIED BY statement or the mysqladmin password command, the PASSWORD() function is unnecessary. They both take care of encrypting the password for you, so you would specify a password of 'biscuit' like this:

 
mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';

or

 
shell> mysqladmin -u jeffrey password biscuit

Note: PASSWORD() is different from Unix password encryption. See section MySQL User Names and Passwords.

4.3.8 Keeping Your Password Secure

It is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed here, along with an assessment of the risks of each method:

All in all, the safest methods are to have the client program prompt for the password or to specify the password in a properly protected `.my.cnf' file.

4.3.9 Using Secure Connections

4.3.9.1 Basics  
4.3.9.2 Requirements  
4.3.9.3 Setting Up SSL Certificates for MySQL  
4.3.9.4 GRANT Options  

4.3.9.1 Basics

Beginning with version 4.0.0, MySQL has support for SSL encrypted connections. To understand how MySQL uses SSL, it's necessary to explain some basic SSL and X509 concepts. People who are already familiar with them can skip this part.

By default, MySQL uses unencrypted connections between the client and the server. This means that someone could watch all your traffic and look at the data being sent or received. They could even change the data while it is in transit between client and server. Sometimes you need to move information over public networks in a secure fashion; in such cases, using an unencrypted connection is unacceptable.

SSL is a protocol that uses different encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect any change, loss or replay of data. SSL also incorporates algorithms to recognise and provide identity verification using the X509 standard.

Encryption is the way to make any kind of data unreadable. In fact, today's practice requires many additional security elements from encryption algorithms. They should resist many kind of known attacks like just messing with the order of encrypted messages or replaying data twice.

X509 is a standard that makes it possible to identify someone on the Internet. It is most commonly used in e-commerce applications. In basic terms, there should be some company (called a "Certificate Authority") that assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys (a public key and a secret key). A certificate owner can prove his identity by showing his certificate to other party. A certificate consists of its owner's public key. Any data encrypted with this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate.

MySQL doesn't use encrypted connections by default, because doing so would make the client/server protocol much slower. Any kind of additional functionality requires the computer to do additional work and encrypting data is a CPU-intensive operation that requires time and can delay MySQL main tasks. By default MySQL is tuned to be fast as possible.

If you need more information about SSL, X509, or encryption, you should use your favourite Internet search engine and search for keywords in which you are interested.

4.3.9.2 Requirements

To get secure connections to work with MySQL you must do the following:

  1. Install the OpenSSL library. We have tested MySQL with OpenSSL 0.9.6. http://www.openssl.org/.
  2. Configure MySQL with --with-vio --with-openssl.
  3. If you are using an old MySQL installation, you have to update your mysql.user table with some new SSL-related columns. You can do this by running the mysql_fix_privilege_tables.sh script. This is necessary if your grant tables date from a version prior to MySQL 4.0.0.
  4. You can check if a running mysqld server supports OpenSSL by examining if SHOW VARIABLES LIKE 'have_openssl' returns YES.

4.3.9.3 Setting Up SSL Certificates for MySQL

Here is an example for setting up SSL certificates for MySQL:

 
DIR=`pwd`/openssl
PRIV=$DIR/private

mkdir $DIR $PRIV $DIR/newcerts
cp /usr/share/ssl/openssl.cnf $DIR
replace ./demoCA $DIR -- $DIR/openssl.cnf

# Create necessary files: $database, $serial and $new_certs_dir 
# directory (optional)

touch $DIR/index.txt
echo "01" > $DIR/serial

#
# Generation of Certificate Authority(CA)
#

openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem \
    -config $DIR/openssl.cnf

# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ................++++++
# .........++++++
# writing new private key to '/home/monty/openssl/private/cakey.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be incorporated
# into your certificate request.
# What you are about to enter is what is called a Distinguished Name or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL admin
# Email Address []:

#
# Create server request and key
#
openssl req -new -keyout $DIR/server-key.pem -out \
    $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf

# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ..++++++
# ..........++++++
# writing new private key to '/home/monty/openssl/server-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be incorporated
# into your certificate request.
# What you are about to enter is what is called a Distinguished Name or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL server
# Email Address []:
# 
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:

#
# Remove the passphrase from the key (optional)
#

openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem

#
# Sign server cert
#
openssl ca  -policy policy_anything -out $DIR/server-cert.pem \
    -config $DIR/openssl.cnf -infiles $DIR/server-req.pem

# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName           :PRINTABLE:'FI'
# organizationName      :PRINTABLE:'MySQL AB'
# commonName            :PRINTABLE:'MySQL admin'
# Certificate is to be certified until Sep 13 14:22:46 2003 GMT (365 days)
# Sign the certificate? [y/n]:y
# 
# 
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated

#
# Create client request and key
#
openssl req -new -keyout $DIR/client-key.pem -out \
    $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf

# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# .....................................++++++
# .............................................++++++
# writing new private key to '/home/monty/openssl/client-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be incorporated
# into your certificate request.
# What you are about to enter is what is called a Distinguished Name or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL user
# Email Address []:
# 
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:

#
# Remove a passphrase from the key (optional)
#
openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem

#
# Sign client cert
#

openssl ca  -policy policy_anything -out $DIR/client-cert.pem \
    -config $DIR/openssl.cnf -infiles $DIR/client-req.pem

# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName           :PRINTABLE:'FI'
# organizationName      :PRINTABLE:'MySQL AB'
# commonName            :PRINTABLE:'MySQL user'
# Certificate is to be certified until Sep 13 16:45:17 2003 GMT (365 days)
# Sign the certificate? [y/n]:y
# 
# 
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated

#
# Create a my.cnf file that you can use to test the certificates
#

cnf=""
cnf="$cnf [client]"
cnf="$cnf ssl-ca=$DIR/cacert.pem"
cnf="$cnf ssl-cert=$DIR/client-cert.pem"
cnf="$cnf ssl-key=$DIR/client-key.pem"
cnf="$cnf [mysqld]"
cnf="$cnf ssl-ca=$DIR/cacert.pem"
cnf="$cnf ssl-cert=$DIR/server-cert.pem"
cnf="$cnf ssl-key=$DIR/server-key.pem"
echo $cnf | replace " " '
' > $DIR/my.cnf

#
# To test MySQL

mysqld --defaults-file=$DIR/my.cnf &

mysql --defaults-file=$DIR/my.cnf

You can also test your setup by modifying the above `my.cnf' file to refer to the demo certificates in the mysql-source-dist/SSL direcory.

4.3.9.4 GRANT Options

MySQL can check X509 certificate attributes in addition to the normal username/password scheme. All the usual options are still required (username, password, IP address mask, database/table name).

There are different possibilities to limit connections:

4.4 Disaster Prevention and Recovery

4.4.1 Database Backups  
4.4.2 BACKUP TABLE Syntax  
4.4.3 RESTORE TABLE Syntax  
4.4.4 CHECK TABLE Syntax  
4.4.5 REPAIR TABLE Syntax  
4.4.6 Using myisamchk for Table Maintenance and Crash Recovery  
4.4.7 Setting Up a Table Maintenance Regimen  
4.4.8 Getting Information About a Table  

4.4.1 Database Backups

Because MySQL tables are stored as files, it is easy to do a backup. To get a consistent backup, do a LOCK TABLES on the relevant tables followed by FLUSH TABLES for the tables. See section LOCK TABLES. See section FLUSH. You only need a read lock; this allows other threads to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLE is needed to ensure that the all active index pages is written to disk before you start the backup.

Starting from 3.23.56 and 4.0.12 BACKUP TABLE will not allow you to overwrite existing files as this would be a security risk.

If you want to make an SQL level backup of a table, you can use SELECT INTO OUTFILE or BACKUP TABLE. See section SELECT Syntax. See section BACKUP TABLE Syntax.

Another way to back up a database is to use the mysqldump program or the mysqlhotcopy script. See section mysqldump. See section mysqlhotcopy.

  1. Do a full backup of your database:
     
    shell> mysqldump --tab=/path/to/some/dir --opt db_name

or

shell> mysqlhotcopy db_name /path/to/some/dir

    You can also simply copy all table files (`*.frm', `*.MYD', and `*.MYI' files) as long as the server isn't updating anything. The script mysqlhotcopy does use this method. (But note that these methods will not work if your database contains InnoDB tables. InnoDB does not store table contents in database directories, and mysqlhotcopy works only for MyISAM and ISAM tables.)

  2. Stop mysqld if it's running, then start it with the --log-bin[=file_name] option. See section The Binary Log. The binary log file(s) provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you executed mysqldump.

If you have to restore something, try to recover your tables using REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, try the following procedure (this will only work if you have started MySQL with --log-bin, see The Binary Log):

  1. Restore the original mysqldump backup, or binary backup.
  2. Execute the following command to re-run the updates in the binary log:
     
    shell> mysqlbinlog hostname-bin.[0-9]* | mysql

    In your case you may want to re-run only certain binlogs, from certain positions (usually you want to re-run all binlogs from the date of the restored backup, possibly excepted some wrong queries). See mysqlbinlog, Executing the queries from a binary log for more information on the mysqlbinlog utility and how to use it.

    If you are using the update log (which will be removed in MySQL 5.0) you can execute the content of the update log like this:

     
    shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql

ls is used to get all the update log files in the right order.

You can also do selective backups with SELECT * INTO OUTFILE 'file_name' FROM tbl_name and restore with LOAD DATA INFILE 'file_name' REPLACE ... To avoid duplicate records, you need a PRIMARY KEY or a UNIQUE key in the table. The REPLACE keyword causes old records to be replaced with new ones when a new record duplicates an old record on a unique key value.

If you get performance problems in making backups on your system, you can solve this by setting up replication and do the backups on the slave instead of on the master. See section Introduction.

If you are using a Veritas filesystem, you can do:

  1. From a client (or Perl), execute: FLUSH TABLES WITH READ LOCK.
  2. From another shell, execute: mount vxfs snapshot.
  3. From the first client, execute: UNLOCK TABLES.
  4. Copy files from snapshot.
  5. Unmount snapshot.

4.4.2 BACKUP TABLE Syntax

 
BACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory'

Copies to the backup directory the minimum number of table files needed to restore the table, after flushing any buffered changes to disk. Currently works only for MyISAM tables. For MyISAM tables, copies `.frm' (definition) and `.MYD' (data) files. The index file can be rebuilt from those two.

Before using this command, please see Database Backups.

During the backup, a read lock will be held for each table, one at time, as they are being backed up. If you want to back up several tables as a snapshot, you must first issue LOCK TABLES obtaining a read lock for each table in the group.

The command returns a table with the following columns:

Column

Value

Table

Table name

Op

Always backup

Msg_type

One of status, error, info, or warning

Msg_text

The message

Note that BACKUP TABLE is only available in MySQL version 3.23.25 and later.

4.4.3 RESTORE TABLE Syntax

 
RESTORE TABLE tbl_name[,tbl_name...] FROM '/path/to/backup/directory'

Restores the table(s) from the backup that was made with BACKUP TABLE. Existing tables will not be overwritten; if you try to restore over an existing table, you will get an error. Restoring will take longer than backing up due to the need to rebuild the index. The more keys you have, the longer it will take. Just as BACKUP TABLE, RESTORE TABLE currently works only for MyISAM tables.

The command returns a table with the following columns:

Column

Value

Table

Table name

Op

Always restore

Msg_type

One of status, error, info, or warning

Msg_text

The message

4.4.4 CHECK TABLE Syntax

 
CHECK TABLE tbl_name[,tbl_name...] [option [option...]]

option = QUICK | FAST | MEDIUM | EXTENDED | CHANGED

CHECK TABLE works only on MyISAM and InnoDB tables. On MyISAM tables, it's the same thing as running myisamchk --medium-check table_name on the table.

If you don't specify any option, MEDIUM is used.

Checks the table or tables for errors. For MyISAM tables, the key statistics are updated. The command returns a table with the following columns:

Column

Value

Table

Table name

Op

Always check

Msg_type

One of status, error, info, or warning

Msg_text

The message

Note that the statement may produce many rows of information for each checked table. The last row will be of Msg_type status and should normally be OK. If you don't get OK, or Table is already up to date you should normally run a repair of the table. See section Using myisamchk for Table Maintenance and Crash Recovery. Table is already up to date means that the storage manager for the table indicated that there was no need to check the table.

The different check types are as follows:

Type

Meaning

QUICK

Don't scan the rows to check for incorrect links.

FAST

Only check tables that haven't been closed properly.

CHANGED

Only check tables that have been changed since the last check or haven't been closed properly.

MEDIUM

Scan rows to verify that deleted links are okay. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.

EXTENDED

Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but will take a long time!

For dynamically sized MyISAM tables, a started check will always do a MEDIUM check. For statically sized rows, we skip the row scan for QUICK and FAST as the rows are very seldom corrupted.

You can combine check options, as in the following example that does a quick check on the table to see whether it was closed properly:

 
CHECK TABLE test_table FAST QUICK;

Note: that in some cases CHECK TABLE will change the table! This happens if the table is marked as 'corrupted' or 'not closed properly' but CHECK TABLE didn't find any problems in the table. In this case, CHECK TABLE will mark the table as okay.

If a table is corrupted, then it's most likely that the problem is in the indexes and not in the data part. All of the above check types checks the indexes thoroughly and should thus find most errors.

If you just want to check a table that you assume is okay, you should use no check options or the QUICK option. The latter should be used when you are in a hurry and can take the very small risk that QUICK didn't find an error in the datafile. (In most cases MySQL should find, under normal usage, any error in the datafile. If this happens then the table will be marked as 'corrupted', in which case the table can't be used until it's repaired.)

FAST and CHANGED are mostly intended to be used from a script (for example to be executed from cron) if you want to check your table from time to time. In most cases, FAST is to be prefered over CHANGED. (The only case when it isn't is when you suspect that you have found a bug in the MyISAM code.)

EXTENDED is only to be used after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key (this is very unlikely if a normal check has succeeded!).

Some things reported by CHECK TABLE can't be corrected automatically:

4.4.5 REPAIR TABLE Syntax

 
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name...] [QUICK] [EXTENDED] [USE_FRM]

REPAIR TABLE works only on MyISAM tables and is the same as running myisamchk -r table_name on the table.

Normally you should never have to run this command, but if disaster strikes you are very likely to get back all your data from a MyISAM table with REPAIR TABLE. If your tables get corrupted a lot, you should try to find the reason for it, to eliminate the need to use REPAIR TABLE. See section What To Do If MySQL Keeps Crashing. See section MyISAM Table Problems.

REPAIR TABLE repairs a possibly corrupted table. The command returns a table with the following columns:

Column

Value

Table

Table name

Op

Always repair

Msg_type

One of status, error, info, or warning

Msg_text

The message

Note that the statement may produce many rows of information for each repaired table. The last one row will be of Msg_type status and should normally be OK. If you don't get OK, you should try repairing the table with myisamchk --safe-recover, as REPAIR TABLE does not yet implement all the options of myisamchk. In the near future, we will make it more flexible.

If QUICK is given, REPAIR TABLE tries to repair only the index tree.

If you use EXTENDED, MySQL will create the index row by row instead of creating one index at a time with sorting; this may be better than sorting on fixed-length keys if you have long CHAR keys that compress very well. This type of repair is like that done by myisamchk --safe-recover.

As of MySQL 4.0.2, there is a USE_FRM mode for REPAIR. Use it if the `.MYI' file is missing or if its header is corrupted. In this mode MySQL will recreate the table, using information from the `.frm' file. This kind of repair cannot be done with myisamchk.

Warning: If mysqld dies during a REPAIR TABLE, it's essential that you do at once another REPAIR on the table before executing any other commands on it. (It's of course always good to start with a backup). In the worst case you can have a new clean index file without information about the datafile and when the next command you do may overwrite the datafile. This is not a likely, but possible scenario.

Strictly before MySQL 4.1.1, REPAIR commands are not written to the binary log. Since MySQL 4.1.1 they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) was used.

4.4.6 Using myisamchk for Table Maintenance and Crash Recovery

Starting with MySQL Version 3.23.13, you can check MyISAM tables with the CHECK TABLE command. See section CHECK TABLE Syntax. You can repair tables with the REPAIR TABLE command. See section REPAIR TABLE Syntax.

To check/repair MyISAM tables (`.MYI' and `.MYD') you should use the myisamchk utility. To check/repair ISAM tables (`.ISM' and `.ISD') you should use the isamchk utility. See section MySQL Table Types.

In the following text we will talk about myisamchk, but everything also applies to the old isamchk.

You can use the myisamchk utility to get information about your database tables, check and repair them, or optimise them. The following sections describe how to invoke myisamchk (including a description of its options), how to set up a table maintenance schedule, and how to use myisamchk to perform its various functions.

You can, in most cases, also use the command OPTIMIZE TABLES to optimise and repair tables, but this is not as fast or reliable (in case of real fatal errors) as myisamchk. On the other hand, OPTIMIZE TABLE is easier to use and you don't have to worry about flushing tables. See section OPTIMIZE TABLE.

Even though the repair in myisamchk is quite secure, it's always a good idea to make a backup before doing a repair (or anything that could make a lot of changes to a table)

4.4.6.1 myisamchk Invocation Syntax  
4.4.6.2 General Options for myisamchk  
4.4.6.3 Check Options for myisamchk  
4.4.6.4 Repair Options for myisamchk  
4.4.6.5 Other Options for myisamchk  
4.4.6.6 myisamchk Memory Usage  
4.4.6.7 Using myisamchk for Crash Recovery  
4.4.6.8 How to Check Tables for Errors  
4.4.6.9 How to Repair Tables  
4.4.6.10 Table Optimisation  

4.4.6.1 myisamchk Invocation Syntax

myisamchk is invoked like this:

 
shell> myisamchk [options] tbl_name

The options specify what you want myisamchk to do. They are described here. (You can also get a list of options by invoking myisamchk --help.) With no options, myisamchk simply checks your table. To get more information or to tell myisamchk to take corrective action, specify options as described here and in the following sections.

tbl_name is the database table you want to check/repair. If you run myisamchk somewhere other than in the database directory, you must specify the path to the file, because myisamchk has no idea where your database is located. Actually, myisamchk doesn't care whether the files you are working on are located in a database directory; you can copy the files that correspond to a database table into another location and perform recovery operations on them there.

You can name several tables on the myisamchk command-line if you wish. You can also specify a name as an index file name (with the `.MYI' suffix), which allows you to specify all tables in a directory by using the pattern `*.MYI'. For example, if you are in a database directory, you can check all the tables in the directory like this:

 
shell> myisamchk *.MYI

If you are not in the database directory, you can check all the tables there by specifying the path to the directory:

 
shell> myisamchk /path/to/database_dir/*.MYI

You can even check all tables in all databases by specifying a wildcard with the path to the MySQL data directory:

 
shell> myisamchk /path/to/datadir/*/*.MYI

The recommended way to quickly check all tables is:

 
myisamchk --silent --fast /path/to/datadir/*/*.MYI
isamchk --silent /path/to/datadir/*/*.ISM

If you want to check all tables and repair all tables that are corrupted, you can use the following line:

 
myisamchk --silent --force --fast --update-state -O key_buffer=64M \
          -O sort_buffer=64M -O read_buffer=1M -O write_buffer=1M \
          /path/to/datadir/*/*.MYI
isamchk --silent --force -O key_buffer=64M -O sort_buffer=64M \
        -O read_buffer=1M -O write_buffer=1M /path/to/datadir/*/*.ISM

The above assumes that you have more than 64 M free.

Note that if you get an error like:

 
myisamchk: warning: 1 clients is using or hasn't closed the table properly

This means that you are trying to check a table that has been updated by another program (like the mysqld server) that hasn't yet closed the file or that has died without closing the file properly.

If mysqld is running, you must force a sync/close of all tables with FLUSH TABLES and ensure that no one is using the tables while you are running myisamchk. In MySQL Version 3.23 the easiest way to avoid this problem is to use CHECK TABLE instead of myisamchk to check tables.

4.4.6.2 General Options for myisamchk

myisamchk supports the following options.

-# or --debug=debug_options

Output debug log. The debug_options string often is 'd:t:o,filename'.

-? or --help

Display a help message and exit.

-O var=option, --set-variable var=option

Set the value of a variable. Please note that --set-variable is deprecated since MySQL 4.0, just use --var=option on its own. The possible variables and their default values for myisamchk can be examined with myisamchk --help:

Variable

Value

key_buffer_size

523264

read_buffer_size

262136

write_buffer_size

262136

sort_buffer_size

2097144

sort_key_blocks

16

decode_bits

9

sort_buffer_size is used when the keys are repaired by sorting keys, which is the normal case when you use --recover.

key_buffer_size is used when you are checking the table with --extended-check or when the keys are repaired by inserting key row by row in to the table (like when doing normal inserts). Repairing through the key buffer is used in the following cases:

Reparing through the key buffer takes much less disk space than using sorting, but is also much slower.

If you want a faster repair, set the above variables to about 1/4 of your available memory. You can set both variables to big values, as only one of the above buffers will be used at a time.

-s or --silent

Silent mode. Write output only when errors occur. You can use -s twice (-ss) to make myisamchk very silent.

-v or --verbose

Verbose mode. Print more information. This can be used with -d and -e. Use -v multiple times (-vv, -vvv) for more verbosity!

-V or --version

Print the myisamchk version and exit.

-w or, --wait

Instead of giving an error if the table is locked, wait until the table is unlocked before continuing. Note that if you are running mysqld on the table with --skip-external-locking, the table can only be locked by another myisamchk command.

4.4.6.3 Check Options for myisamchk

-c or --check

Check table for errors. This is the default operation if you are not giving myisamchk any options that override this.

-e or --extend-check

Check the table very thoroughly (which is quite slow if you have many indexes). This option should only be used in extreme cases. Normally, myisamchk or myisamchk --medium-check should, in most cases, be able to find out if there are any errors in the table.

If you are using --extended-check and have much memory, you should increase the value of key_buffer_size a lot!

-F or --fast

Check only tables that haven't been closed properly.

-C or --check-only-changed

Check only tables that have changed since the last check.

-f or --force

Restart myisamchk with -r (repair) on the table, if myisamchk finds any errors in the table.

-i or --information

Print informational statistics about the table that is checked.

-m or --medium-check

Faster than extended-check, but only finds 99.99% of all errors. Should, however, be good enough for most cases.

-U or --update-state

Store in the `.MYI' file when the table was checked and if the table crashed. This should be used to get full benefit of the --check-only-changed option, but you shouldn't use this option if the mysqld server is using the table and you are running mysqld with --skip-external-locking.

-T or --read-only

Don't mark table as checked. This is useful if you use myisamchk to check a table that is in use by some other application that doesn't use locking (like mysqld --skip-external-locking).

4.4.6.4 Repair Options for myisamchk

The following options are used if you start myisamchk with -r or -o:

-D # or --data-file-length=#

Max length of datafile (when re-creating datafile when it's 'full').

-e or --extend-check

Try to recover every possible row from the datafile. Normally this will also find a lot of garbage rows. Don't use this option if you are not totally desperate.

-f or --force

Overwrite old temporary files (table_name.TMD) instead of aborting.

-k # or keys-used=#

If you are using ISAM, tells the ISAM storage engine to update only the first # indexes. If you are using MyISAM, tells which keys to use, where each binary bit stands for one key (first key is bit 0). This can be used to get faster inserts! Deactivated indexes can be reactivated by using myisamchk -r. keys.

-l or --no-symlinks

Do not follow symbolic links. Normally myisamchk repairs the table a symlink points at. This option doesn't exist in MySQL 4.0, as MySQL 4.0 will not remove symlinks during repair.

-r or --recover

Can fix almost anything except unique keys that aren't unique (which is an extremely unlikely error with ISAM/MyISAM tables). If you want to recover a table, this is the option to try first. Only if myisamchk reports that the table can't be recovered by -r, you should then try -o. (Note that in the unlikely case that -r fails, the datafile is still intact.) If you have lots of memory, you should increase the size of sort_buffer_size!

-o or --safe-recover

Uses an old recovery method (reads through all rows in order and updates all index trees based on the found rows); this is an order of magnitude slower than -r, but can handle a couple of very unlikely cases that -r cannot handle. This recovery method also uses much less disk space than -r. Normally one should always first repair with -r, and only if this fails use -o.

If you have lots of memory, you should increase the size of key_buffer_size!

-n or --sort-recover

Force myisamchk to use sorting to resolve the keys even if the temporary files should be very big.

--character-sets-dir=...

Directory where character sets are stored.

--set-character-set=name

Change the character set used by the index

-t or --tmpdir=path

Path for storing temporary files. If this is not set, myisamchk will use the environment variable TMPDIR for this. Starting from MySQL 4.1, tmpdir can be set to a list of paths separated by colon : (semicolon ; on Windows). They will be used in round-robin fashion.

-q or --quick

Faster repair by not modifying the datafile. One can give a second -q to force myisamchk to modify the original datafile in case of duplicate keys

-u or --unpack

Unpack file packed with myisampack.

4.4.6.5 Other Options for myisamchk

Other actions that myisamchk can do, besides repair and check tables:

-a or --analyze

Analyse the distribution of keys. This improves join performance by enabling the join optimiser to better choose in which order it should join the tables and which keys it should use: myisamchk --describe --verbose table_name' or using SHOW KEYS in MySQL.

-d or --description

Prints some information about table.

-A or --set-auto-increment[=value]

Force AUTO_INCREMENT to start at this or higher value. If no value is given, then sets the next AUTO_INCREMENT value to the highest used value for the auto key + 1.

-S or --sort-index

Sort the index tree blocks in high-low order. This will optimise seeks and will make table scanning by key faster.

-R or --sort-records=#

Sorts records according to an index. This makes your data much more localised and may speed up ranged SELECT and ORDER BY operations on this index. (It may be very slow to do a sort the first time!) To find out a table's index numbers, use SHOW INDEX, which shows a table's indexes in the same order that myisamchk sees them. Indexes are numbered beginning with 1.

4.4.6.6 myisamchk Memory Usage

Memory allocation is important when you run myisamchk. myisamchk uses no more memory than you specify with the -O options. If you are going to use myisamchk on very large files, you should first decide how much memory you want it to use. The default is to use only about 3M to fix things. By using larger values, you can get myisamchk to operate faster. For example, if you have more than 32M RAM, you could use options such as these (in addition to any other options you might specify):

 
shell> myisamchk -O sort=16M -O key=16M -O read=1M -O write=1M ...

Using -O sort=16M should probably be enough for most cases.

Be aware that myisamchk uses temporary files in TMPDIR. If TMPDIR points to a memory filesystem, you may easily get out of memory errors. If this happens, set TMPDIR to point at some directory with more space and restart myisamchk.

When repairing, myisamchk will also need a lot of disk space:

If you have a problem with disk space during repair, you can try to use --safe-recover instead of --recover.

4.4.6.7 Using myisamchk for Crash Recovery

If you run mysqld with --skip-external-locking (which is the default on some systems, like Linux), you can't reliably use myisamchk to check a table when mysqld is using the same table. If you can be sure that no one is accessing the tables through mysqld while you run myisamchk, you only have to do mysqladmin flush-tables before you start checking the tables. If you can't guarantee the above, then you must take down mysqld while you check the tables. If you run myisamchk while mysqld is updating the tables, you may get a warning that a table is corrupt even if it isn't.

If you are not using --skip-external-locking, you can use myisamchk to check tables at any time. While you do this, all clients that try to update the table will wait until myisamchk is ready before continuing.

If you use myisamchk to repair or optimise tables, you must always ensure that the mysqld server is not using the table (this also applies if you are using --skip-external-locking). If you don't take down mysqld you should at least do a mysqladmin flush-tables before you run myisamchk. Your tables may be corrupted if the server and myisamchk access the tables simultaneously.

This chapter describes how to check for and deal with data corruption in MySQL databases. If your tables get corrupted frequently you should try to find the reason for this! See section What To Do If MySQL Keeps Crashing.

The MyISAM table section contains reason for why a table could be corrupted. See section MyISAM Table Problems.

When performing crash recovery, it is important to understand that each table tbl_name in a database corresponds to three files in the database directory:

File

Purpose

`tbl_name.frm'

Table definition (form) file

`tbl_name.MYD'

Datafile

`tbl_name.MYI'

Index file

Each of these three file types is subject to corruption in various ways, but problems occur most often in datafiles and index files.

myisamchk works by creating a copy of the `.MYD' (data) file row by row. It ends the repair stage by removing the old `.MYD' file and renaming the new file to the original file name. If you use --quick, myisamchk does not create a temporary `.MYD' file, but instead assumes that the `.MYD' file is correct and only generates a new index file without touching the `.MYD' file. This is safe, because myisamchk automatically detects if the `.MYD' file is corrupt and aborts the repair in this case. You can also give two --quick options to myisamchk. In this case, myisamchk does not abort on some errors (like duplicate key) but instead tries to resolve them by modifying the `.MYD' file. Normally the use of two --quick options is useful only if you have too little free disk space to perform a normal repair. In this case you should at least make a backup before running myisamchk.

4.4.6.8 How to Check Tables for Errors

To check a MyISAM table, use the following commands:

myisamchk tbl_name

This finds 99.99% of all errors. What it can't find is corruption that involves only the datafile (which is very unusual). If you want to check a table, you should normally run myisamchk without options or with either the -s or --silent option.

myisamchk -m tbl_name

This finds 99.999% of all errors. It checks first all index entries for errors and then it reads through all rows. It calculates a checksum for all keys in the rows and verifies that they checksum matches the checksum for the keys in the index tree.

myisamchk -e tbl_name

This does a complete and thorough check of all data (-e means "extended check"). It does a check-read of every key for each row to verify that they indeed point to the correct row. This may take a long time on a big table with many keys. myisamchk will normally stop after the first error it finds. If you want to obtain more information, you can add the --verbose (-v) option. This causes myisamchk to keep going, up through a maximum of 20 errors. In normal usage, a simple myisamchk (with no arguments other than the table name) is sufficient.

myisamchk -e -i tbl_name

Like the previous command, but the -i option tells myisamchk to print some informational statistics, too.

4.4.6.9 How to Repair Tables

In the following section we only talk about using myisamchk on MyISAM tables (extensions `.MYI' and `.MYD'). If you are using ISAM tables (extensions `.ISM' and `.ISD'), you should use isamchk instead.

Starting with MySQL Version 3.23.14, you can repair MyISAM tables with the REPAIR TABLE command. See section REPAIR TABLE Syntax.

The symptoms of a corrupted table include queries that abort unexpectedly and observable errors such as these:

In the other cases, you must repair your tables. myisamchk can usually detect and fix most things that go wrong.

The repair process involves up to four stages, described here. Before you begin, you should cd to the database directory and check the permissions of the table files. Make sure they are readable by the Unix user that mysqld runs as (and to you, because you need to access the files you are checking). If it turns out you need to modify files, they must also be writable by you.

If you are using MySQL Version 3.23.16 and above, you can (and should) use the CHECK and REPAIR commands to check and repair MyISAM tables. See section CHECK TABLE Syntax. See section REPAIR TABLE Syntax.

The manual section about table maintenance includes the options to isamchk/myisamchk. See section Using myisamchk for Table Maintenance and Crash Recovery.

The following section is for the cases where the above command fails or if you want to use the extended features that isamchk/myisamchk provides.

If you are going to repair a table from the command-line, you must first take down the mysqld server. Note that when you do mysqladmin shutdown on a remote server, the mysqld server will still be alive for a while after mysqladmin returns, until all queries are stopped and all keys have been flushed to disk.

Stage 1: Checking your tables

Run myisamchk *.MYI or myisamchk -e *.MYI if you have more time. Use the -s (silent) option to suppress unnecessary information.

If the mysqld server is done you should use the -update option to tell myisamchk to mark the table as 'checked'.

You have to repair only those tables for which myisamchk announces an error. For such tables, proceed to Stage 2.

If you get weird errors when checking (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 2: Easy safe repair

Note: If you want repairing to go much faster, you should add: -O sort_buffer=# -O key_buffer=# (where # is about 1/4 of the available memory) to all isamchk/myisamchk commands.

First, try myisamchk -r -q tbl_name (-r -q means "quick recovery mode"). This will attempt to repair the index file without touching the datafile. If the datafile contains everything that it should and the delete links point at the correct locations within the datafile, this should work, and the table is fixed. Start repairing the next table. Otherwise, use the following procedure:

  1. Make a backup of the datafile before continuing.
  2. Use myisamchk -r tbl_name (-r means "recovery mode"). This will remove incorrect records and deleted records from the datafile and reconstruct the index file.
  3. If the preceding step fails, use myisamchk --safe-recover tbl_name. Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode doesn't (but is slower).

If you get weird errors when repairing (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 3: Difficult repair

You should only reach this stage if the first 16K block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it's necessary to create a new index file. Do so as follows:

  1. Move the datafile to some safe place.
  2. Use the table description file to create new (empty) data and index files:
     
    shell> mysql db_name
mysql> SET AUTOCOMMIT=1;
mysql> TRUNCATE TABLE table_name;
mysql> quit

    If your SQL version doesn't have TRUNCATE TABLE, use DELETE FROM table_name instead.

  3. Copy the old datafile back onto the newly created datafile. (Don't just move the old file back onto the new file; you want to retain a copy in case something goes wrong.)

Go back to Stage 2. myisamchk -r -q should work now. (This shouldn't be an endless loop.)

As of MySQL 4.0.2 you can also use REPAIR ... USE_FRM which performs the whole procedure automatically.

Stage 4: Very difficult repair

You should reach this stage only if the description file has also crashed. That should never happen, because the description file isn't changed after the table is created:

  1. Restore the description file from a backup and go back to Stage 3. You can also restore the index file and go back to Stage 2. In the latter case, you should start with myisamchk -r.
  2. If you don't have a backup but know exactly how the table was created, create a copy of the table in another database. Remove the new datafile, then move the description and index files from the other database to your crashed database. This gives you new description and index files, but leaves the datafile alone. Go back to Stage 2 and attempt to reconstruct the index file.

4.4.6.10 Table Optimisation

To coalesce fragmented records and eliminate wasted space resulting from deleting or updating records, run myisamchk in recovery mode:

 
shell> myisamchk -r tbl_name

You can optimise a table in the same way using the SQL OPTIMIZE TABLE statement. OPTIMIZE TABLE does a repair of the table and a key analysis, and also sorts the index tree to give faster key lookups. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use OPTIMIZE TABLE. See section OPTIMIZE TABLE Syntax.

myisamchk also has a number of other options you can use to improve the performance of a table:

For a full description of the option. See section myisamchk Invocation Syntax.

4.4.7 Setting Up a Table Maintenance Regimen

Starting with MySQL Version 3.23.13, you can check MyISAM tables with the CHECK TABLE command. See section CHECK TABLE Syntax. You can repair tables with the REPAIR TABLE command. See section REPAIR TABLE Syntax.

It is a good idea to perform table checks on a regular basis rather than waiting for problems to occur. For maintenance purposes, you can use myisamchk -s to check tables. The -s option (short for --silent) causes myisamchk to run in silent mode, printing messages only when errors occur.

It's also a good idea to check tables when the server starts up. For example, whenever the machine has done a reboot in the middle of an update, you usually need to check all the tables that could have been affected. (This is an "expected crashed table".) You could add a test to mysqld_safe that runs myisamchk to check all tables that have been modified during the last 24 hours if there is an old `.pid' (process ID) file left after a reboot. (The `.pid' file is created by mysqld when it starts up and removed when it terminates normally. The presence of a `.pid' file at system startup time indicates that mysqld terminated abnormally.)

An even better test would be to check any table whose last-modified time is more recent than that of the `.pid' file.

You should also check your tables regularly during normal system operation. At MySQL AB, we run a cron job to check all our important tables once a week, using a line like this in a `crontab' file:

 
35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI

This prints out information about crashed tables so we can examine and repair them when needed.

As we haven't had any unexpectedly crashed tables (tables that become corrupted for reasons other than hardware trouble) for a couple of years now (this is really true), once a week is more than enough for us.

We recommend that to start with, you execute myisamchk -s each night on all tables that have been updated during the last 24 hours, until you come to trust MySQL as much as we do.

Normally you don't need to maintain MySQL tables that much. If you are changing tables with dynamic size rows (tables with VARCHAR, BLOB or TEXT columns) or have tables with many deleted rows you may want to from time to time (once a month?) defragment/reclaim space from the tables.

You can do this by using OPTIMIZE TABLE on the tables in question or if you can take the mysqld server down for a while do:

 
isamchk -r --silent --sort-index -O sort_buffer_size=16M */*.ISM
myisamchk -r --silent --sort-index  -O sort_buffer_size=16M */*.MYI

4.4.8 Getting Information About a Table

To get a description of a table or statistics about it, use the commands shown here. We explain some of the information in more detail later:

Example of myisamchk -d output:

 
MyISAM file:     company.MYI
Record format:   Fixed length
Data records:    1403698  Deleted blocks:         0
Recordlength:    226

table description:
Key Start Len Index   Type
1   2     8   unique  double
2   15    10  multip. text packed stripped
3   219   8   multip. double
4   63    10  multip. text packed stripped
5   167   2   multip. unsigned short
6   177   4   multip. unsigned long
7   155   4   multip. text
8   138   4   multip. unsigned long
9   177   4   multip. unsigned long
    193   1           text

Example of myisamchk -d -v output:

 
MyISAM file:         company
Record format:       Fixed length
File-version:        1
Creation time:       1999-10-30 12:12:51
Recover time:        1999-10-31 19:13:01
Status:              checked
Data records:           1403698  Deleted blocks:              0
Datafile parts:         1403698  Deleted data:                0
Datafilepointer (bytes):      3  Keyfile pointer (bytes):     3
Max datafile length: 3791650815  Max keyfile length: 4294967294
Recordlength:               226

table description:
Key Start Len Index   Type                  Rec/key     Root Blocksize
1   2     8   unique  double                      1 15845376      1024
2   15    10  multip. text packed stripped        2 25062400      1024
3   219   8   multip. double                     73 40907776      1024
4   63    10  multip. text packed stripped        5 48097280      1024
5   167   2   multip. unsigned short           4840 55200768      1024
6   177   4   multip. unsigned long            1346 65145856      1024
7   155   4   multip. text                     4995 75090944      1024
8   138   4   multip. unsigned long              87 85036032      1024
9   177   4   multip. unsigned long             178 96481280      1024
    193   1           text

Example of myisamchk -eis output:

 
Checking MyISAM file: company
Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:  98%  Packed:   17%

Records:          1403698    M.recordlength:     226
Packed:             0%
Recordspace used:     100%   Empty space:          0%
Blocks/Record:   1.00
Record blocks:    1403698    Delete blocks:        0
Recorddata:     317235748    Deleted data:         0
Lost space:             0    Linkdata:             0

User time 1626.51, System time 232.36
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 627, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 639, Involuntary context switches 28966

Example of myisamchk -eiv output:

 
Checking MyISAM file: company
Data records: 1403698   Deleted blocks:       0
- check file-size
- check delete-chain
block_size 1024:
index  1:
index  2:
index  3:
index  4:
index  5:
index  6:
index  7:
index  8:
index  9:
No recordlinks
- check index reference
- check data record references index: 1
Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
- check data record references index: 2
Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
- check data record references index: 3
Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
- check data record references index: 4
Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
- check data record references index: 5
Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 6
Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 7
Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 8
Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 9
Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:   9%  Packed:   17%

- check records and index references
[LOTS OF ROW NUMBERS DELETED]

Records:          1403698    M.recordlength:     226   Packed:             0%
Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
Record blocks:    1403698    Delete blocks:        0
Recorddata:     317235748    Deleted data:         0
Lost space:             0    Linkdata:             0

User time 1639.63, System time 251.61
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0
Blocks in 4 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 10604, Involuntary context switches 122798

Here are the sizes of the data and index files for the table used in the preceding examples:

 
-rw-rw-r--   1 monty    tcx     317235748 Jan 12 17:30 company.MYD
-rw-rw-r--   1 davida   tcx      96482304 Jan 12 18:35 company.MYM

Explanations for the types of information myisamchk produces are given here. The "keyfile" is the index file. "Record" and "row" are synonymous:

If a table has been compressed with myisampack, myisamchk -d prints additional information about each table column. See myisampack, for an example of this information and a description of what it means.

4.5 Database Administration Language Reference

4.5.1 OPTIMIZE TABLE Syntax  
4.5.2 ANALYZE TABLE Syntax  
4.5.3 FLUSH Syntax  
4.5.4 RESET Syntax  
4.5.5 PURGE MASTER LOGS Syntax  
4.5.6 KILL Syntax  
4.5.7 SHOW Syntax  

4.5.1 OPTIMIZE TABLE Syntax

 
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]...

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the datafile.

In most setups you don't have to run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable length rows it's not likely that you need to do this more than once a month/week and only on certain tables.

For the moment, OPTIMIZE TABLE works only on MyISAM and BDB tables. For BDB tables, OPTIMIZE TABLE is currently mapped to ANALYZE TABLE. See section ANALYZE TABLE.

You can get OPTIMIZE TABLE to work on other table types by starting mysqld with --skip-new or --safe-mode, but in this case OPTIMIZE TABLE is just mapped to ALTER TABLE.

OPTIMIZE TABLE works the following way:

Note that the table is locked during the time OPTIMIZE TABLE is running!

Strictly before MySQL 4.1.1, OPTIMIZE commands are not written to the binary log. Since MySQL 4.1.1 they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) was used.

4.5.2 ANALYZE TABLE Syntax

 
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name...]

Analyse and store the key distribution for the table. During the analysis, the table is locked with a read lock. This works on MyISAM and BDB tables.

This is equivalent to running myisamchk -a on the table.

MySQL uses the stored key distribution to decide in which order tables should be joined when one does a join on something else than a constant.

The command returns a table with the following columns:

Column

Value

Table

Table name

Op

Always analyze

Msg_type

One of status, error, info, or warning

Msg_text

The message

You can check the stored key distribution with the SHOW INDEX command. See section Retrieving information about Database, Tables, Columns, and Indexes.

If the table hasn't changed since the last ANALYZE TABLE command, the table will not be analysed again.

Strictly before MySQL 4.1.1, ANALYZE commands are not written to the binary log. Since MySQL 4.1.1 they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) was used.

4.5.3 FLUSH Syntax

 
FLUSH [LOCAL | NO_WRITE_TO_BINLOG] flush_option [,flush_option] ...

You should use the FLUSH command if you want to clear some of the internal caches MySQL uses. To execute FLUSH, you must have the RELOAD privilege.

flush_option can be any of the following:

Option

Description

HOSTS

Empties the host cache tables. You should flush the host tables if some of your hosts change IP number or if you get the error message Host ... is blocked. When more than max_connect_errors errors occur in a row for a given host while connection to the MySQL server, MySQL assumes something is wrong and blocks the host from further connection requests. Flushing the host tables allows the host to attempt to connect again. See section Host '...' is blocked Error. You can start mysqld with -O max_connect_errors=999999999 to avoid this error message.

DES_KEY_FILE

Reloads the DES keys from the file that was specified with the --des-key-file option at server startup time.

LOGS

Closes and reopens all log files. If you have specified an update log file or a binary log file without an extension, the extension number of the log file will be incremented by one relative to the previous file. If you have used an extension in the file name, MySQL will close and reopen the update log file. See section The Update Log. This is the same thing as sending the SIGHUP signal to the mysqld server.

PRIVILEGES

Reloads the privileges from the grant tables in the mysql database.

QUERY CACHE

Defragment the query cache to better utilise its memory. This command will not remove any queries from the cache, unlike RESET QUERY CACHE.

TABLES

Closes all open tables and force all tables in use to be closed. This also flushes the query cache.

[TABLE | TABLES] tbl_name [,tbl_name...]

Flushes only the given tables.

TABLES WITH READ LOCK

Closes all open tables and locks all tables for all databases with a read lock until you execute UNLOCK TABLES. This is very convenient way to get backups if you have a filesystem, like Veritas, that can take snapshots in time.

STATUS

Resets most status variables to zero. This is something one should only use when debugging a query.

USER_RESOURCES

Resets all user resources to zero. This will enable blocked users to login again. See section Limiting user resources.

Strictly before MySQL 4.1.1, FLUSH commands are not written to the binary log. Since MySQL 4.1.1 they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) was used, or unless the command contained one of these arguments: LOGS, MASTER, SLAVE, TABLES WITH READ LOCK, because any of these arguments may cause problems if replicated to a slave.

You can also access some of the commands shown above with the mysqladmin utility, using the flush-hosts, flush-logs, flush-privileges, flush-status or flush-tables commands.

Take also a look at the RESET command used with replication. See section RESET.

4.5.4 RESET Syntax

 
RESET reset_option [,reset_option] ...

The RESET command is used to clear things. It also acts as an stronger version of the FLUSH command. See section FLUSH.

To execute RESET, you must have the RELOAD privilege.

Option

Description

MASTER

Deletes all binary logs listed in the index file, resetting the binlog index file to be empty. Previously named FLUSH MASTER. See section SQL Commands Related to Replication.

SLAVE

Makes the slave forget its replication position in the master logs. Previously named FLUSH SLAVE. See section SQL Commands Related to Replication.

QUERY CACHE

Removes all query results from the query cache.

4.5.5 PURGE MASTER LOGS Syntax

 
PURGE {MASTER|BINARY} LOGS TO binlog_name
PURGE {MASTER|BINARY} LOGS BEFORE date

This command is used to delete all binary logs strictly prior to the specified binlog or date. See section SQL Commands Related to Replication.

PURGE BINARY LOGS is available as a synonym for PURGE MASTER LOGS as of MySQL 4.1.1.

4.5.6 KILL Syntax

 
KILL thread_id

Each connection to mysqld runs in a separate thread. You can see which threads are running with the SHOW PROCESSLIST command and kill a thread with the KILL thread_id command.

If you have the PROCESS privilege, you can see all threads. If you have the SUPER privilege, you can kill all threads. Otherwise, you can only see and kill your own threads.

You can also use the mysqladmin processlist and mysqladmin kill commands to examine and kill threads.

Note: You currently cannot use KILL with the Embedded MySQL Server library, because the embedded server merely runs inside the threads of the host application, it does not create connection threads of its own.

When you do a KILL, a thread-specific kill flag is set for the thread.

In most cases it may take some time for the thread to die as the kill flag is only checked at specific intervals.

4.5.7 SHOW Syntax

 
   SHOW DATABASES [LIKE wild]
or SHOW [OPEN] TABLES [FROM db_name] [LIKE wild]
or SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
or SHOW STATUS [LIKE wild]
or SHOW VARIABLES [LIKE wild]
or SHOW [BDB] LOGS
or SHOW [FULL] PROCESSLIST
or SHOW GRANTS FOR user
or SHOW CREATE TABLE table_name
or SHOW MASTER STATUS
or SHOW MASTER LOGS
or SHOW SLAVE STATUS
or SHOW WARNINGS [LIMIT row_count]
or SHOW ERRORS [LIMIT row_count]
or SHOW TABLE TYPES

SHOW provides information about databases, tables, columns, or status information about the server. If the LIKE wild part is used, the wild string can be a string that uses the SQL `%' and `_' wildcard characters.

4.5.7.1 Retrieving information about Database, Tables, Columns, and Indexes  
4.5.7.2 SHOW TABLE STATUS  
4.5.7.3 SHOW STATUS  
4.5.7.4 SHOW VARIABLES  
4.5.7.5 SHOW [BDB] LOGS  
4.5.7.6 SHOW PROCESSLIST  
4.5.7.7 SHOW GRANTS  
4.5.7.8 SHOW CREATE TABLE  
4.5.7.9 SHOW WARNINGS | ERRORS  
4.5.7.10 SHOW TABLE TYPES  
4.5.7.11 SHOW PRIVILEGES  

4.5.7.1 Retrieving information about Database, Tables, Columns, and Indexes

You can use db_name.tbl_name as an alternative to the tbl_name FROM db_name syntax. These two statements are equivalent:

 
mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;

SHOW DATABASES lists the databases on the MySQL server host. You can also get this list using the mysqlshow command line tool. In version 4.0.2 you will only see those databases for which you have some kind of privilege, if you don't have the global SHOW DATABASES privilege.

SHOW TABLES lists the tables in a given database. You can also get this list using the mysqlshow db_name command.

Note: if a user doesn't have any privileges for a table, the table will not show up in the output from SHOW TABLES or mysqlshow db_name.

SHOW OPEN TABLES lists the tables that are currently open in the table cache. See section How MySQL Opens and Closes Tables. The Comment field tells how many times the table is cached and in_use.

SHOW COLUMNS lists the columns in a given table. If you specify the FULL option, you will also get the privileges you have for each column. If the column types are different from what you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes column types. See section Silent Column Specification Changes. As of MySQL 4.1, the FULL keyword also causes any per-column comments to be displayed.

The DESCRIBE statement provides information similar to SHOW COLUMNS. See section DESCRIBE.

SHOW FIELDS is a synonym for SHOW COLUMNS, and SHOW KEYS is a synonym for SHOW INDEX. You can also list a table's columns or indexes with mysqlshow db_name tbl_name or mysqlshow -k db_name tbl_name.

SHOW INDEX returns the index information in a format that closely resembles the SQLStatistics call in ODBC. The following columns are returned:

Column

Meaning

Table

Name of the table.

Non_unique

0 if the index can't contain duplicates, 1 if it can.

Key_name

Name of the index.

Seq_in_index

Column sequence number in index, starting with 1.

Column_name

Column name.

Collation

How the column is sorted in the index. In MySQL, this can have values `A' (Ascending) or NULL (Not sorted).

Cardinality

Number of unique values in the index. This is updated by running isamchk -a.

Sub_part

Number of indexed characters if the column is only partly indexed. NULL if the entire key is indexed.

Null

Contains 'YES' if the column may contain NULL.

Index_type

Index method used.

Comment

Various remarks. For now, it tells in MySQL < 4.0.2 whether index is FULLTEXT or not.

Note that as the Cardinality is counted based on statistics stored as integers, it's not necessarily accurate for small tables.

The Null and Index_type columns were added in MySQL 4.0.2.

4.5.7.2 SHOW TABLE STATUS

 
SHOW TABLE STATUS [FROM db_name] [LIKE wild]

SHOW TABLE STATUS (new in Version 3.23) works likes SHOW STATUS, but provides a lot of information about each table. You can also get this list using the mysqlshow --status db_name command. The following columns are returned:

Column

Meaning

Name

Name of the table.

Type

Type of table. See section MySQL Table Types.

Row_format

The row storage format (Fixed, Dynamic, or Compressed).

Rows

Number of rows.

Avg_row_length

Average row length.

Data_length

Length of the datafile.

Max_data_length

Max length of the datafile. For fixed row formats, this is the max number of rows in the table. For dynamic row formats, this is the total number of data bytes that can be stored in the table, given the data pointer size used.

Index_length

Length of the index file.

Data_free

Number of allocated but not used bytes.

Auto_increment

Next autoincrement value.

Create_time

When the table was created.

Update_time

When the datafile was last updated.

Check_time

When the table was last checked.

Create_options

Extra options used with CREATE TABLE.

Comment

The comment used when creating the table (or some information why MySQL couldn't access the table information).

InnoDB tables will report the free space in the tablespace in the table comment.

4.5.7.3 SHOW STATUS

SHOW STATUS provides server status information (like mysqladmin extended-status). The output resembles that shown here, though the format and numbers probably differ:

 
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 0          |
| Aborted_connects         | 0          |
| Bytes_received           | 155372598  |
| Bytes_sent               | 1176560426 |
| Connections              | 30023      |
| Created_tmp_disk_tables  | 0          |
| Created_tmp_tables       | 8340       |
| Created_tmp_files        | 60         |
| Delayed_insert_threads   | 0          |
| Delayed_writes           | 0          |
| Delayed_errors           | 0          |
| Flush_commands           | 1          |
| Handler_delete           | 462604     |
| Handler_read_first       | 105881     |
| Handler_read_key         | 27820558   |
| Handler_read_next        | 390681754  |
| Handler_read_prev        | 6022500    |
| Handler_read_rnd         | 30546748   |
| Handler_read_rnd_next    | 246216530  |
| Handler_update           | 16945404   |
| Handler_write            | 60356676   |
| Key_blocks_used          | 14955      |
| Key_read_requests        | 96854827   |
| Key_reads                | 162040     |
| Key_write_requests       | 7589728    |
| Key_writes               | 3813196    |
| Max_used_connections     | 0          |
| Not_flushed_key_blocks   | 0          |
| Not_flushed_delayed_rows | 0          |
| Open_tables              | 1          |
| Open_files               | 2          |
| Open_streams             | 0          |
| Opened_tables            | 44600      |
| Questions                | 2026873    |
| Select_full_join         | 0          |
| Select_full_range_join   | 0          |
| Select_range             | 99646      |
| Select_range_check       | 0          |
| Select_scan              | 30802      |
| Slave_running            | OFF        |
| Slave_open_temp_tables   | 0          |
| Slow_launch_threads      | 0          |
| Slow_queries             | 0          |
| Sort_merge_passes        | 30         |
| Sort_range               | 500        |
| Sort_rows                | 30296250   |
| Sort_scan                | 4650       |
| Table_locks_immediate    | 1920382    |
| Table_locks_waited       | 0          |
| Threads_cached           | 0          |
| Threads_created          | 30022      |
| Threads_connected        | 1          |
| Threads_running          | 1          |
| Uptime                   | 80380      |
+--------------------------+------------+

The status variables listed above have the following meaning:

Variable

Meaning

Aborted_clients

Number of connections aborted because the client died without closing the connection properly. See section Communication Errors / Aborted Connection.

Aborted_connects

Number of tries to connect to the MySQL server that failed. See section Communication Errors / Aborted Connection.

Bytes_received

Number of bytes received from all clients.

Bytes_sent

Number of bytes sent to all clients.

Com_xxx

Number of times each xxx command has been executed.

Connections

Number of connection attempts to the MySQL server.

Created_tmp_disk_tables

Number of implicit temporary tables on disk created while executing statements.

Created_tmp_tables

Number of implicit temporary tables in memory created while executing statements.

Created_tmp_files

How many temporary files mysqld has created.

Delayed_insert_threads

Number of delayed insert handler threads in use.

Delayed_writes

Number of rows written with INSERT DELAYED.

Delayed_errors

Number of rows written with INSERT DELAYED for which some error occurred (probably duplicate key).

Flush_commands

Number of executed FLUSH commands.

Handler_commit

Number of internal COMMIT commands.

Handler_delete

Number of times a row was deleted from a table.

Handler_read_first

Number of times the first entry was read from an index. If this is high, it suggests that the server is doing a lot of full index scans, for example, SELECT col1 FROM foo, assuming that col1 is indexed.

Handler_read_key

Number of requests to read a row based on a key. If this is high, it is a good indication that your queries and tables are properly indexed.

Handler_read_next

Number of requests to read next row in key order. This will be incremented if you are querying an index column with a range constraint. This also will be incremented if you are doing an index scan.

Handler_read_prev

Number of requests to read previous row in key order. This is mainly used to optimise ORDER BY ... DESC.

Handler_read_rnd

Number of requests to read a row based on a fixed position. This will be high if you are doing a lot of queries that require sorting of the result.

Handler_read_rnd_next

Number of requests to read the next row in the datafile. This will be high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Handler_rollback

Number of internal ROLLBACK commands.

Handler_update

Number of requests to update a row in a table.

Handler_write

Number of requests to insert a row in a table.

Key_blocks_used

The number of used blocks in the key cache.

Key_read_requests

The number of requests to read a key block from the cache.

Key_reads

The number of physical reads of a key block from disk.

Key_write_requests

The number of requests to write a key block to the cache.

Key_writes

The number of physical writes of a key block to disk.

Max_used_connections

The maximum number of connections in use simultaneously.

Not_flushed_key_blocks

Keys blocks in the key cache that has changed but hasn't yet been flushed to disk.

Not_flushed_delayed_rows

Number of rows waiting to be written in INSERT DELAY queues.

Open_tables

Number of tables that are open.

Open_files

Number of files that are open.

Open_streams

Number of streams that are open (used mainly for logging).

Opened_tables

Number of tables that have been opened.

Rpl_status

Status of failsafe replication. (Not yet in use).

Select_full_join

Number of joins without keys (If this is not 0, you should carefully check the indexes of your tables).

Select_full_range_join

Number of joins where we used a range search on reference table.

Select_range

Number of joins where we used ranges on the first table. (It's normally not critical even if this is big.)

Select_scan

Number of joins where we did a full scan of the first table.

Select_range_check

Number of joins without keys where we check for key usage after each row (If this is not 0, you should carefully check the indexes of your tables).

Questions

Number of queries sent to the server.

Slave_open_temp_tables

Number of temporary tables currently open by the slave thread

Slave_running

Is ON if this is a slave that is connected to a master.

Slow_launch_threads

Number of threads that have taken more than slow_launch_time to create.

Slow_queries

Number of queries that have taken more than long_query_time seconds. See section The Slow Query Log.

Sort_merge_passes

Number of merges passes the sort algoritm have had to do. If this value is large you should consider increasing sort_buffer.

Sort_range

Number of sorts that were done with ranges.

Sort_rows

Number of sorted rows.

Sort_scan

Number of sorts that were done by scanning the table.

ssl_xxx

Variables used by SSL; Not yet implemented.

Table_locks_immediate

Number of times a table lock was acquired immediately. Available after 3.23.33.

Table_locks_waited

Number of times a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimise your queries, and then either split your table(s) or use replication. Available after 3.23.33.

Threads_cached

Number of threads in the thread cache.

Threads_connected

Number of currently open connections.

Threads_created

Number of threads created to handle connections.

Threads_running

Number of threads that are not sleeping.

Uptime

How many seconds the server has been up.

Some comments about the above:

4.5.7.4 SHOW VARIABLES

 
SHOW [GLOBAL | SESSION] VARIABLES [LIKE wild]

SHOW VARIABLES shows the values of some MySQL system variables. The options GLOBAL and SESSION are new in MySQL 4.0.3. With GLOBAL you will get the variables that will be used for new connections to MySQL. With SESSION you will get the values that are in effect for the current connection. If you are not using either option, SESSION is used.

If the default values are unsuitable, you can set most of these variables using command-line options when mysqld starts up. See section mysqld Command-line Options. It is also possible to change most variables with the SET statement. See section SET.

The output from SHOW VARIABLES resembles that shown in the following list, though the format and numbers may differ somewhat. You can also get this information using the mysqladmin variables command.

 
+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------|
| back_log                        | 50                           |
| basedir                         | /usr/local/mysql             |
| bdb_cache_size                  | 8388572                      |
| bdb_log_buffer_size             | 32768                        |
| bdb_home                        | /usr/local/mysql             |
| bdb_max_lock                    | 10000                        |
| bdb_logdir                      |                              |
| bdb_shared_data                 | OFF                          |
| bdb_tmpdir                      | /tmp/                        |
| bdb_version                     | Sleepycat Software: ...      |
| binlog_cache_size               | 32768                        |
| bulk_insert_buffer_size         | 8388608                      |
| character_set                   | latin1                       |
| character_sets                  | latin1 big5 czech euc_kr     |
| concurrent_insert               | ON                           |
| connect_timeout                 | 5                            |
| convert_character_set           |                              |
| datadir                         | /usr/local/mysql/data/       |
| delay_key_write                 | ON                           |
| delayed_insert_limit            | 100                          |
| delayed_insert_timeout          | 300                          |
| delayed_queue_size              | 1000                         |
| flush                           | OFF                          |
| flush_time                      | 0                            |
| ft_boolean_syntax               | + -><()~*:""&|               |
| ft_min_word_len                 | 4                            |
| ft_max_word_len                 | 254                          |
| ft_max_word_len_for_sort        | 20                           |
| ft_stopword_file                | (built-in)                   |
| have_bdb                        | YES                          |
| have_innodb                     | YES                          |
| have_isam                       | YES                          |
| have_raid                       | NO                           |
| have_symlink                    | DISABLED                     |
| have_openssl                    | YES                          |
| have_query_cache                | YES                          |
| init_file                       |                              |
| innodb_additional_mem_pool_size | 1048576                      |
| innodb_buffer_pool_size         | 8388608                      |
| innodb_data_file_path           | ibdata1:10M:autoextend       |
| innodb_data_home_dir            |                              |
| innodb_file_io_threads          | 4                            |
| innodb_force_recovery           | 0                            |
| innodb_thread_concurrency       | 8                            |
| innodb_flush_log_at_trx_commit  | 1                            |
| innodb_fast_shutdown            | ON                           |
| innodb_flush_method             |                              |
| innodb_lock_wait_timeout        | 50                           |
| innodb_log_arch_dir             |                              |
| innodb_log_archive              | OFF                          |
| innodb_log_buffer_size          | 1048576                      |
| innodb_log_file_size            | 5242880                      |
| innodb_log_files_in_group       | 2                            |
| innodb_log_group_home_dir       | ./                           |
| innodb_mirrored_log_groups      | 1                            |
| interactive_timeout             | 28800                        |
| join_buffer_size                | 131072                       |
| key_buffer_size                 | 16773120                     |
| language                        | /usr/local/mysql/share/...   |
| large_files_support             | ON                           |
| local_infile                    | ON                           |
| locked_in_memory                | OFF                          |
| log                             | OFF                          |
| log_update                      | OFF                          |
| log_bin                         | OFF                          |
| log_slave_updates               | OFF                          |
| log_slow_queries                | OFF                          |
| log_warnings                    | OFF                          |
| long_query_time                 | 10                           |
| low_priority_updates            | OFF                          |
| lower_case_table_names          | OFF                          |
| max_allowed_packet              | 1047552                      |
| max_binlog_cache_size           | 4294967295                   |
| max_binlog_size                 | 1073741824                   |
| max_connections                 | 100                          |
| max_connect_errors              | 10                           |
| max_delayed_threads             | 20                           |
| max_heap_table_size             | 16777216                     |
| max_join_size                   | 4294967295                   |
| max_relay_log_size              | 0                            |
| max_sort_length                 | 1024                         |
| max_user_connections            | 0                            |
| max_tmp_tables                  | 32                           |
| max_write_lock_count            | 4294967295                   |
| myisam_max_extra_sort_file_size | 268435456                    |
| myisam_repair_threads           | 1                            |
| myisam_max_sort_file_size       | 2147483647                   |
| myisam_recover_options          | force                        |
| myisam_sort_buffer_size         | 8388608                      |
| net_buffer_length               | 16384                        |
| net_read_timeout                | 30                           |
| net_retry_count                 | 10                           |
| net_write_timeout               | 60                           |
| open_files_limit                | 1024                         |
| pid_file                        | /usr/local/mysql/name.pid    |
| port                            | 3306                         |
| protocol_version                | 10                           |
| query_cache_limit               | 1048576                      |
| query_cache_size                | 0                            |
| query_cache_type                | ON                           |
| read_buffer_size                | 131072                       |
| read_rnd_buffer_size            | 262144                       |
| rpl_recovery_rank               | 0                            |
| safe_show_database              | OFF                          |
| server_id                       | 0                            |
| slave_net_timeout               | 3600                         |
| skip_external_locking           | ON                           |
| skip_networking                 | OFF                          |
| skip_show_database              | OFF                          |
| slow_launch_time                | 2                            |
| socket                          | /tmp/mysql.sock              |
| sort_buffer_size                | 2097116                      |
| sql_mode                        |                              |
| table_cache                     | 64                           |
| table_type                      | MYISAM                       |
| thread_cache_size               | 3                            |
| thread_stack                    | 131072                       |
| tx_isolation                    | READ-COMMITTED               |
| timezone                        | EEST                         |
| tmp_table_size                  | 33554432                     |
| tmpdir                          | /tmp/:/mnt/hd2/tmp/          |
| version                         | 4.0.4-beta                   |
| wait_timeout                    | 28800                        |
+---------------------------------+------------------------------+

Each option is described here. Values for buffer sizes, lengths, and stack sizes are given in bytes. You can specify values with a suffix of `K' or `M' to indicate kilobytes or megabytes. For example, 16M indicates 16 megabytes. The case of suffix letters does not matter; 16M and 16m are equivalent:

The manual section that describes tuning MySQL contains some information of how to tune the above variables. See section Tuning Server Parameters.

4.5.7.5 SHOW [BDB] LOGS

SHOW LOGS shows you status information about existing log files. It currently only displays information about Berkeley DB log files, so an alias for it (available as of MySQL 4.1.1) is SHOW BDB LOGS.

4.5.7.6 SHOW PROCESSLIST

SHOW [FULL] PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the SUPER privilege, you can see all threads. Otherwise, you can see only your own threads. See section KILL. If you don't use the FULL option, then only the first 100 characters of each query will be shown.

Starting from 4.0.12, MySQL reports the hostname for TCP/IP connections as hostname:client_port to make it easier to find out which client is doing what.

This command is very useful if you get the 'too many connections' error message and want to find out what's going on. MySQL reserves one extra connection for a client with the SUPER privilege to ensure that you should always be able to login and check the system (assuming you are not giving this privilege to all your users).

Some states commonly seen in mysqladmin processlist

Most states are very quick operations. If threads last in any of these states for many seconds, there may be a problem around that needs to be investigated.

There are some other states that are not mentioned previously, but most of these are only useful to find bugs in mysqld.

4.5.7.7 SHOW GRANTS

SHOW GRANTS FOR user lists the grant commands that must be issued to duplicate the grants for a user.

 
mysql> SHOW GRANTS FOR root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

To list grants for the current session one may use CURRENT_USER() function (new in version 4.0.6) to find out what user the session was authenticated as. See section CURRENT_USER().

4.5.7.8 SHOW CREATE TABLE

Shows a CREATE TABLE statement that will create the given table:

 
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  id INT(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) TYPE=MyISAM

SHOW CREATE TABLE will quote table and column names according to SQL_QUOTE_SHOW_CREATE option. SET SQL_QUOTE_SHOW_CREATE.

4.5.7.9 SHOW WARNINGS | ERRORS

 
SHOW WARNINGS [LIMIT row_count]
SHOW ERRORS [LIMIT row_count]

This command is implemented in MySQL 4.1.0.

It shows the errors, warnings and notes that one got for the last command. The errors/warnings are reset for each new command that uses a table.

The MySQL server sends back the total number of warnings and errors you got for the last commend; This can be retrieved by calling mysql_warning_count().

Up to max_error_count messages are stored (Global and thread specific variable).

You can retrieve the number of errors from @error_count and warnings from @warning_count.

SHOW WARNINGS shows all errors, warnings and notes you got for the last command while SHOW ERRORS only shows you the errors.

 
mysql> DROP TABLE IF EXISTS no_such_table;
mysql> SHOW WARNINGS;

+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1051 | Unknown table 'no_such_table' |
+-------+------+-------------------------------+

Note that in MySQL 4.1.0 we have just added the frame work for warnings and not many MySQL command do yet generate warnings. 4.1.1 supports all kind of warnings for LOAD DATA INFILE and DML statements such as INSERT, UPDATE and ALTER commands.

For example, here is a simple case which produces conversion warnings for a insert statement.

 
mysql> create table t1(a tinyint NOT NULL, b char(4));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(10,'mysql'),(NULL,'test'),(300,'open source');
Query OK, 3 rows affected, 4 warnings (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 4

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1263 | Data truncated for column 'b' at row 1                        |
| Warning | 1261 | Data truncated, NULL supplied to NOT NULL column 'a' at row 2 |
| Warning | 1262 | Data truncated, out of range for column 'a' at row 3          |
| Warning | 1263 | Data truncated for column 'b' at row 3                        |
+---------+------+---------------------------------------------------------------+
4 rows in set (0.00 sec)

Maximum number of warnings can be specified using the server variable 'max_error_count', SET max_error_count=[count]; By default it is 64. In case to disable warnings, simply reset this variable to '0'. In case if max_error_count is 0, then still the warning count represents how many warnings have occurred, but none of the messages are stored.

For example, consider the following ALTER table statement for the above example, which returns only one warning message even though total warnings occurred is 3 when you set max_error_count=1.

 
mysql> show variables like 'max_error_count';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 64    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> set max_error_count=1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 modify b char;
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1263 | Data truncated for column 'b' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql>

4.5.7.10 SHOW TABLE TYPES

 
SHOW TABLE TYPES

This command is implemented in MySQL 4.1.0.

SHOW TABLE TYPES shows you status information about the table types. This is particulary useful for checking if a table type is supported; or to see what is the default table type is.

 
mysql> SHOW TABLE TYPES;

+--------+---------+-----------------------------------------------------------+
| Type   | Support | Comment                                                   |
+--------+---------+-----------------------------------------------------------+
| MyISAM | DEFAULT | Default type from 3.23 with great performance             |
| HEAP   | YES     | Hash based, stored in memory, useful for temporary tables |
| MERGE  | YES     | Collection of identical MyISAM tables                     |
| ISAM   | YES     | Obsolete table type; Is replaced by MyISAM                |
| InnoDB | YES     | Supports transactions, row-level locking and foreign keys |
| BDB    | NO      | Supports transactions and page-level locking              |
+--------+---------+-----------------------------------------------------------+
6 rows in set (0.00 sec)

The 'Support' option DEFAULT indicates whether the particular table type is supported, and which is the default type. If the server is started with --default-table-type=InnoDB, then the InnoDB 'Support' field will have the value DEFAULT.

4.5.7.11 SHOW PRIVILEGES

 
SHOW PRIVILEGES

This command is implemented in MySQL 4.1.0.

SHOW PRIVILEGES shows the list of system privileges that the underlying MySQL server supports.

 
mysql> show privileges;
+------------+--------------------------+-------------------------------------------------------+
| Privilege  | Context                  | Comment                                               |
+------------+--------------------------+-------------------------------------------------------+
| Select     | Tables                   | To retrieve rows from table                           |
| Insert     | Tables                   | To insert data into tables                            |
| Update     | Tables                   | To update existing rows                               |
| Delete     | Tables                   | To delete existing rows                               |
| Index      | Tables                   | To create or drop indexes                             |
| Alter      | Tables                   | To alter the table                                    |
| Create     | Databases,Tables,Indexes | To create new databases and tables                    |
| Drop       | Databases,Tables         | To drop databases and tables                          |
| Grant      | Databases,Tables         | To give to other users those privileges you possess   |
| References | Databases,Tables         | To have references on tables                          |
| Reload     | Server Admin             | To reload or refresh tables, logs and privileges      |
| Shutdown   | Server Admin             | To shutdown the server                                |
| Process    | Server Admin             | To view the plain text of currently executing queries |
| File       | File access on server    | To read and write files on the server                 |
+------------+--------------------------+-------------------------------------------------------+
14 rows in set (0.00 sec)

4.6 MySQL Localisation and International Usage

4.6.1 The Character Set Used for Data and Sorting  
4.6.2 Non-English Error Messages  
4.6.3 Adding a New Character Set  
4.6.4 The Character Definition Arrays  
4.6.5 String Collating Support  
4.6.6 Multi-byte Character Support  
4.6.7 Problems With Character Sets  

4.6.1 The Character Set Used for Data and Sorting

By default, MySQL uses the ISO-8859-1 (Latin1) character set with sorting according to Swedish/Finnish. This is the character set suitable in the USA and western Europe.

All standard MySQL binaries are compiled with --with-extra-charsets=complex. This will add code to all standard programs to be able to handle latin1 and all multi-byte character sets within the binary. Other character sets will be loaded from a character-set definition file when needed.

The character set determines what characters are allowed in names and how things are sorted by the ORDER BY and GROUP BY clauses of the SELECT statement.

You can change the character set with the --default-character-set option when you start the server. The character sets available depend on the --with-charset=charset and --with-extra-charsets= list-of-charset | complex | all | none options to configure, and the character set configuration files listed in `SHAREDIR/charsets/Index'. See section Typical configure Options.

If you change the character set when running MySQL (which may also change the sort order), you must run myisamchk -r -q --set-character-set=charset on all tables. Otherwise, your indexes may not be ordered correctly.

When a client connects to a MySQL server, the server sends the default character set in use to the client. The client will switch to use this character set for this connection.

One should use mysql_real_escape_string() when escaping strings for an SQL query. mysql_real_escape_string() is identical to the old mysql_escape_string() function, except that it takes the MYSQL connection handle as the first parameter.

If the client is compiled with different paths than where the server is installed and the user who configured MySQL didn't include all character sets in the MySQL binary, one must specify for the client where it can find the additional character sets it will need if the server runs with a different character set than the client.

One can specify this by putting in a MySQL option file:

 
[client]
character-sets-dir=/usr/local/mysql/share/mysql/charsets

where the path points to the directory in which the dynamic MySQL character sets are stored.

One can force the client to use specific character set by specifying:

 
[client]
default-character-set=character-set-name

but normally this is never needed.

4.6.1.1 German character set  

4.6.1.1 German character set

To get German sorting order, you should start mysqld with --default-character-set=latin1_de. This will give you the following characteristics.

When sorting and comparing strings, the following mapping is done on the strings before doing the comparison:

 
ä  ->  ae
ö  ->  oe
ü  ->  ue
ß  ->  ss

All accented characters, are converted to their un-accented uppercase counterpart. All letters are converted to uppercase.

When comparing strings with LIKE the one -> two character mapping is not done. All letters are converted to uppercase. Accent are removed from all letters except: Ü, ü, Ö, ö, Ä and ä.

4.6.2 Non-English Error Messages

mysqld can issue error messages in the following languages: Czech, Danish, Dutch, English (the default), Estonian, French, German, Greek, Hungarian, Italian, Japanese, Korean, Norwegian, Norwegian-ny, Polish, Portuguese, Romanian, Russian, Slovak, Spanish, and Swedish.

To start mysqld with a particular language, use either the --language=lang or -L lang options. For example:

 
shell> mysqld --language=swedish

or:

 
shell> mysqld --language=/usr/local/share/swedish

Note that all language names are specified in lowercase.

The language files are located (by default) in `mysql_base_dir/share/LANGUAGE/'.

To update the error message file, you should edit the `errmsg.txt' file and execute the following command to generate the `errmsg.sys' file:

 
shell> comp_err errmsg.txt errmsg.sys

If you upgrade to a newer version of MySQL, remember to repeat your changes with the new `errmsg.txt' file.

4.6.3 Adding a New Character Set

To add another character set to MySQL, use the following procedure.

Decide if the set is simple or complex. If the character set does not need to use special string collating routines for sorting and does not need multi-byte character support, it is simple. If it needs either of those features, it is complex.

For example, latin1 and danish are simple charactersets while big5 or czech are complex character sets.

In the following section, we have assumed that you name your character set MYSET.

For a simple character set do the following:

  1. Add MYSET to the end of the `sql/share/charsets/Index' file Assign a unique number to it.
  2. Create the file `sql/share/charsets/MYSET.conf'. (You can use `sql/share/charsets/latin1.conf' as a base for this.)

    The syntax for the file is very simple:

    See section The Character Definition Arrays.

  3. Add the character set name to the CHARSETS_AVAILABLE and COMPILED_CHARSETS lists in configure.in.
  4. Reconfigure, recompile, and test.

For a complex character set do the following:

  1. Create the file `strings/ctype-MYSET.c' in the MySQL source distribution.
  2. Add MYSET to the end of the `sql/share/charsets/Index' file. Assign a unique number to it.
  3. Look at one of the existing `ctype-*.c' files to see what needs to be defined, for example `strings/ctype-big5.c'. Note that the arrays in your file must have names like ctype_MYSET, to_lower_MYSET, and so on. This corresponds to the arrays in the simple character set. See section The Character Definition Arrays.
  4. Near the top of the file, place a special comment like this:
     
    /*
 * This comment is parsed by configure to create ctype.c,
 * so don't change it unless you know what you are doing.
 *
 * .configure. number_MYSET=MYNUMBER
 * .configure. strxfrm_multiply_MYSET=N
 * .configure. mbmaxlen_MYSET=N
 */

    The configure program uses this comment to include the character set into the MySQL library automatically.

    The strxfrm_multiply and mbmaxlen lines will be explained in the following sections. Only include these if you need the string collating functions or the multi-byte character set functions, respectively.

  5. You should then create some of the following functions:

    See section String Collating Support.

  6. Add the character set name to the CHARSETS_AVAILABLE and COMPILED_CHARSETS lists in configure.in.
  7. Reconfigure, recompile, and test.

The file `sql/share/charsets/README' includes some more instructions.

If you want to have the character set included in the MySQL distribution, mail a patch to the MySQL internals mailing list. See section The MySQL Mailing Lists.

4.6.4 The Character Definition Arrays

to_lower[] and to_upper[] are simple arrays that hold the lowercase and uppercase characters corresponding to each member of the character set. For example:

 
to_lower['A'] should contain 'a'
to_upper['a'] should contain 'A'

sort_order[] is a map indicating how characters should be ordered for comparison and sorting purposes. Quite often (but not for all character sets) this is the same as to_upper[] (which means sorting will be case-insensitive). MySQL will sort characters based on the value of sort_order[character]. For more complicated sorting rules, see the discussion of string collating below. See section String Collating Support.

ctype[] is an array of bit values, with one element for one character. (Note that to_lower[], to_upper[], and sort_order[] are indexed by character value, but ctype[] is indexed by character value + 1. This is an old legacy to be able to handle EOF.)

You can find the following bitmask definitions in `m_ctype.h':

 
#define _U      01      /* Uppercase */
#define _L      02      /* Lowercase */
#define _N      04      /* Numeral (digit) */
#define _S      010     /* Spacing character */
#define _P      020     /* Punctuation */
#define _C      040     /* Control character */
#define _B      0100    /* Blank */
#define _X      0200    /* heXadecimal digit */

The ctype[] entry for each character should be the union of the applicable bitmask values that describe the character. For example, 'A' is an uppercase character (_U) as well as a hexadecimal digit (_X), so ctype['A'+1] should contain the value:

 
_U + _X = 01 + 0200 = 0201

4.6.5 String Collating Support

If the sorting rules for your language are too complex to be handled with the simple sort_order[] table, you need to use the string collating functions.

Right now the best documentation on this is the character sets that are already implemented. Look at the big5, czech, gbk, sjis, and tis160 character sets for examples.

You must specify the strxfrm_multiply_MYSET=N value in the special comment at the top of the file. N should be set to the maximum ratio the strings may grow during my_strxfrm_MYSET (it must be a positive integer).

4.6.6 Multi-byte Character Support

If your want to add support for a new character set that includes multi-byte characters, you need to use the multi-byte character functions.

Right now the best documentation on this is the character sets that are already implemented. Look at the euc_kr, gb2312, gbk, sjis, and ujis character sets for examples. These are implemented in the `ctype-'charset'.c' files in the `strings' directory.

You must specify the mbmaxlen_MYSET=N value in the special comment at the top of the source file. N should be set to the size in bytes of the largest character in the set.

4.6.7 Problems With Character Sets

If you try to use a character set that is not compiled into your binary, you can run into a couple of different problems:

For MyISAM tables, you can check the character set name and number for a table with myisamchk -dvv table_name.

4.7 MySQL Server-Side Scripts and Utilities

4.7.1 Overview of the Server-Side Scripts and Utilities  
4.7.2 mysqld_safe, The Wrapper Around mysqld  
4.7.3 mysqld_multi, A Program for Managing Multiple MySQL Servers  
4.7.4 myisampack, The MySQL Compressed Read-only Table Generator  
4.7.5 mysqld-max, An Extended mysqld Server  

4.7.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 full description of the program's different options. For example, try mysql --help.

You can override default options for all standard programs with an option file. `my.cnf' Option Files.

The following list briefly describes the server-side MySQL programs:

myisamchk

Utility to describe, check, optimise, and repair MySQL tables. Because myisamchk has many functions, it is described in its own chapter. See section Database Administration.

make_binary_distribution

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.

mysqlbug

The MySQL bug report script. This script should always be used when filing a bug report to the MySQL list.

mysqld

The SQL daemon. This should always be running.

mysql_install_db

Creates the MySQL grant tables with default privileges. This is usually executed only once, when first installing MySQL on a system.

4.7.2 mysqld_safe, The Wrapper Around mysqld

mysqld_safe is the recommended way to start a mysqld daemon 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. This makes it very easy to test to use mysqld-max instead of mysqld; just copy mysqld-max to where you have mysqld and it will be used.

Normally one should never edit the mysqld_safe script, but instead put the options to mysqld_safe in the [mysqld_safe] section in the `my.cnf' file. 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.) See section `my.cnf' Option Files.

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

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

mysqld_safe supports the following options:

--basedir=path
--core-file-size=#

Size of the core file mysqld should be able to create. Passed to ulimit -c.

--datadir=path
--defaults-extra-file=path
--defaults-file=path
--err-log=path (this is marked obsolete in 4.0; Use --log-error instead)
--log-error=path

Write the error log to the above file. See section The Error Log.

--ledir=path

Path to mysqld

--log=path
--mysqld=mysqld-version

Name of the mysqld version in the ledir directory you want to start.

--mysqld-version=version

Similar to --mysqld= but here you only give the suffix for mysqld. For example if you use --mysqld-version=max, mysqld_safe will start the ledir/mysqld-max version. If the argument to --mysqld-version is empty, ledir/mysqld will be used.

--nice=# (added in MySQL 4.0.14)
--no-defaults
--open-files-limit=#

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!

--pid-file=path
--port=#
--socket=path
--timezone=#

Set the timezone (the TZ) variable to the value of this parameter.

--user=#

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 version of MySQL, even if these install the server in slightly different locations. mysqld_safe expects one of these conditions to be true:

Because mysqld_safe will try 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 start 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 modify it to use the path to mysqld and the pathname options that are correct for your system. Note that if you upgrade MySQL in the future, your modified version of mysqld_safe will be overwritten, so you should make a copy of your edited version that you can reinstall.

4.7.3 mysqld_multi, A Program for Managing Multiple MySQL Servers

mysqld_multi is meant for managing several mysqld processes that listen for connections on different Unix sockets and TCP/IP ports.

The program will search for group(s) named [mysqld#] from `my.cnf' (or the file named by the --config-file=... option), where # can be any positive number starting from 1. This number is referred to in the following discussion as the option group number, or GNR. Group numbers distinquish option groups from one another and are used as arguments to mysqld_multi to specify which servers you want to start, stop, or obtain status for. Options listed in these groups should be the same as you would use in the usual [mysqld] group used for starting mysqld. (See, for example, Starting and Stopping MySQL Automatically.) However, for mysqld_multi, be sure that each group includes options for values such as the port, socket, etc., to be used for each individual mysqld process.

mysqld_multi is invoked using the following syntax:

 
Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
or     mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]

Each GNR represents an option group number. You can start, stop or report any GNR, or several of them at the same time. For an example of how you might set up an option file, use this command:

 
shell> mysqld_multi --example

The GNR values in the list can be comma-separated or combined with a dash; in the latter case, all the GNRs between GNR1-GNR2 will be affected. With no GNR argument, all groups listed in the option file will be either started, stopped, or reported. Note that you must not have any white spaces in the GNR list. Anything after a white space is ignored.

mysqld_multi supports the following options:

--config-file=...

Alternative config file. Note: This will not affect this program's own options (group [mysqld_multi]), but only groups [mysqld#]. Without this option, everything will be searched from the ordinary `my.cnf' file.

--example

Display an example option file.

--help

Print this help and exit.

--log=...

Log file. Full path to and the name for the log file. Note: If the file exists, everything will be appended.

--mysqladmin=...

mysqladmin binary to be used for a server shutdown.

--mysqld=...

mysqld binary to be used. Note that you can give mysqld_safe to this option also. The options are passed to mysqld. Just make sure you have mysqld in your environment variable PATH or fix mysqld_safe.

--no-log

Print to stdout instead of the log file. By default the log file is turned on.

--password=...

Password for user for mysqladmin.

--tcp-ip

Connect to the MySQL server(s) via the TCP/IP port instead of the Unix socket. This affects stopping and reporting. If a socket file is missing, the server may still be running, but can be accessed only via the TCP/IP port. By default, connections are made using the Unix socket.

--user=...

MySQL user for mysqladmin.

--version

Print the version number and exit.

Some notes about mysqld_multi:

See section Running Multiple MySQL Servers on the Same Machine.

This is an example of the config file on behalf of mysqld_multi.

 
# 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

See section `my.cnf' Option Files.

4.7.4 myisampack, The MySQL Compressed Read-only Table Generator

myisampack is used to compress MyISAM tables, and pack_isam is used to compress ISAM tables. Because ISAM tables are deprecated, we will only discuss myisampack here, but everything said about myisampack should also be true for pack_isam.

myisampack works by compressing each column in the table separately. The information needed to decompress columns is read into memory when the table is opened. This results in much better performance when accessing individual records, because you only have to uncompress exactly one record, not a much larger disk block as when using Stacker on MS-DOS. Usually, myisampack packs the datafile 40%-70%.

MySQL uses memory mapping (mmap()) on compressed tables and falls back to normal read/write file usage if mmap() doesn't work.

Please note the following:

myisampack is invoked like this:

 
shell> myisampack [options] filename ...

Each filename should be the name of an index (`.MYI') file. If you are not in the database directory, you should specify the pathname to the file. It is permissible to omit the `.MYI' extension.

myisampack supports the following options:

-b, --backup

Make a backup of the table as tbl_name.OLD.

-#, --debug=debug_options

Output debug log. The debug_options string often is 'd:t:o,filename'.

-f, --force

Force packing of the table even if it becomes bigger or if the temporary file exists. myisampack creates a temporary file named `tbl_name.TMD' while it compresses the table. If you kill myisampack, the `.TMD' file may not be deleted. Normally, myisampack exits with an error if it finds that `tbl_name.TMD' exists. With --force, myisampack packs the table anyway.

-?, --help

Display a help message and exit.

-j big_tbl_name, --join=big_tbl_name

Join all tables named on the command-line into a single table big_tbl_name. All tables that are to be combined must be identical (same column names and types, same indexes, etc.).

-p #, --packlength=#

Specify the record length storage size, in bytes. The value should be 1, 2, or 3. (myisampack stores all rows with length pointers of 1, 2, or 3 bytes. In most normal cases, myisampack can determine the right length value before it begins packing the file, but it may notice during the packing process that it could have used a shorter length. In this case, myisampack will print a note that the next time you pack the same file, you could use a shorter record length.)

-s, --silent

Silent mode. Write output only when errors occur.

-t, --test

Don't actually pack table, just test packing it.

-T dir_name, --tmp_dir=dir_name

Use the named directory as the location in which to write the temporary table.

-v, --verbose

Verbose mode. Write information about progress and packing result.

-V, --version

Display version information and exit.

-w, --wait

Wait and retry if table is in use. If the mysqld server was invoked with the --skip-external-locking option, it is not a good idea to invoke myisampack if the table might be updated during the packing process.

The sequence of commands shown here illustrates a typical table compression session:

 
shell> ls -l station.*
-rw-rw-r--   1 monty    my         994128 Apr 17 19:00 station.MYD
-rw-rw-r--   1 monty    my          53248 Apr 17 19:00 station.MYI
-rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frm

shell> myisamchk -dvv station

MyISAM file:     station
Isam-version:  2
Creation time: 1996-03-13 10:08:58
Recover time:  1997-02-02  3:06:43
Data records:              1192  Deleted blocks:              0
Datafile: Parts:           1192  Deleted data:                0
Datafile pointer (bytes):     2  Keyfile pointer (bytes):     2
Max datafile length:   54657023  Max keyfile length:   33554431
Recordlength:               834
Record format: Fixed length

table description:
Key Start Len Index   Type                       Root  Blocksize    Rec/key
1   2     4   unique  unsigned long              1024       1024          1
2   32    30  multip. text                      10240       1024          1

Field Start Length Type
1     1     1
2     2     4
3     6     4
4     10    1
5     11    20
6     31    1
7     32    30
8     62    35
9     97    35
10    132   35
11    167   4
12    171   16
13    187   35
14    222   4
15    226   16
16    242   20
17    262   20
18    282   20
19    302   30
20    332   4
21    336   4
22    340   1
23    341   8
24    349   8
25    357   8
26    365   2
27    367   2
28    369   4
29    373   4
30    377   1
31    378   2
32    380   8
33    388   4
34    392   4
35    396   4
36    400   4
37    404   1
38    405   4
39    409   4
40    413   4
41    417   4
42    421   4
43    425   4
44    429   20
45    449   30
46    479   1
47    480   1
48    481   79
49    560   79
50    639   79
51    718   79
52    797   8
53    805   1
54    806   1
55    807   20
56    827   4
57    831   4

shell> myisampack station.MYI
Compressing station.MYI: (1192 records)
- Calculating statistics

normal:     20  empty-space:      16  empty-zero:        12  empty-fill:  11
pre-space:   0  end-space:        12  table-lookups:      5  zero:         7
Original trees:  57  After join: 17
- Compressing file
87.14%

shell> ls -l station.*
-rw-rw-r--   1 monty    my         127874 Apr 17 19:00 station.MYD
-rw-rw-r--   1 monty    my          55296 Apr 17 19:04 station.MYI
-rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frm

shell> myisamchk -dvv station

MyISAM file:     station
Isam-version:  2
Creation time: 1996-03-13 10:08:58
Recover time:  1997-04-17 19:04:26
Data records:              1192  Deleted blocks:              0
Datafile: Parts:           1192  Deleted data:                0
Datafilepointer (bytes):      3  Keyfile pointer (bytes):     1
Max datafile length:   16777215  Max keyfile length:     131071
Recordlength:               834
Record format: Compressed

table description:
Key Start Len Index   Type                       Root  Blocksize    Rec/key
1   2     4   unique  unsigned long             10240       1024          1
2   32    30  multip. text                      54272       1024          1

Field Start Length Type                         Huff tree  Bits
1     1     1      constant                             1     0
2     2     4      zerofill(1)                          2     9
3     6     4      no zeros, zerofill(1)                2     9
4     10    1                                           3     9
5     11    20     table-lookup                         4     0
6     31    1                                           3     9
7     32    30     no endspace, not_always              5     9
8     62    35     no endspace, not_always, no empty    6     9
9     97    35     no empty                             7     9
10    132   35     no endspace, not_always, no empty    6     9
11    167   4      zerofill(1)                          2     9
12    171   16     no endspace, not_always, no empty    5     9
13    187   35     no endspace, not_always, no empty    6     9
14    222   4      zerofill(1)                          2     9
15    226   16     no endspace, not_always, no empty    5     9
16    242   20     no endspace, not_always              8     9
17    262   20     no endspace, no empty                8     9
18    282   20     no endspace, no empty                5     9
19    302   30     no endspace, no empty                6     9
20    332   4      always zero                          2     9
21    336   4      always zero                          2     9
22    340   1                                           3     9
23    341   8      table-lookup                         9     0
24    349   8      table-lookup                        10     0
25    357   8      always zero                          2     9
26    365   2                                           2     9
27    367   2      no zeros, zerofill(1)                2     9
28    369   4      no zeros, zerofill(1)                2     9
29    373   4      table-lookup                        11     0
30    377   1                                           3     9
31    378   2      no zeros, zerofill(1)                2     9
32    380   8      no zeros                             2     9
33    388   4      always zero                          2     9
34    392   4      table-lookup                        12     0
35    396   4      no zeros, zerofill(1)               13     9
36    400   4      no zeros, zerofill(1)                2     9
37    404   1                                           2     9
38    405   4      no zeros                             2     9
39    409   4      always zero                          2     9
40    413   4      no zeros                             2     9
41    417   4      always zero                          2     9
42    421   4      no zeros                             2     9
43    425   4      always zero                          2     9
44    429   20     no empty                             3     9
45    449   30     no empty                             3     9
46    479   1                                          14     4
47    480   1                                          14     4
48    481   79     no endspace, no empty               15     9
49    560   79     no empty                             2     9
50    639   79     no empty                             2     9
51    718   79     no endspace                         16     9
52    797   8      no empty                             2     9
53    805   1                                          17     1
54    806   1                                           3     9
55    807   20     no empty                             3     9
56    827   4      no zeros, zerofill(2)                2     9
57    831   4      no zeros, zerofill(1)                2     9

The information printed by myisampack is described here:

normal

The number of columns for which no extra packing is used.

empty-space

The number of columns containing values that are only spaces; these will occupy 1 bit.

empty-zero

The number of columns containing values that are only binary 0's; these will occupy 1 bit.

empty-fill

The number of integer columns that don't occupy the full byte range of their type; these are changed to a smaller type (for example, an INTEGER column may be changed to MEDIUMINT).

pre-space

The number of decimal columns that are stored with leading spaces. In this case, each value will contain a count for the number of leading spaces.

end-space

The number of columns that have a lot of trailing spaces. In this case, each value will contain a count for the number of trailing spaces.

table-lookup

The column had only a small number of different values, which were converted to an ENUM before Huffman compression.

zero

The number of columns for which all values are zero.

Original trees

The initial number of Huffman trees.

After join

The number of distinct Huffman trees left after joining trees to save some header space.

After a table has been compressed, myisamchk -dvv prints additional information about each field:

Type

The field type may contain the following descriptors:

constant

All rows have the same value.

no endspace

Don't store endspace.

no endspace, not_always

Don't store endspace and don't do end space compression for all values.

no endspace, no empty

Don't store endspace. Don't store empty values.

table-lookup

The column was converted to an ENUM.

zerofill(n)

The most significant n bytes in the value are always 0 and are not stored.

no zeros

Don't store zeros.

always zero

0 values are stored in 1 bit.

Huff tree

The Huffman tree associated with the field.

Bits

The number of bits used in the Huffman tree.

After you have run pack_isam/myisampack you must run isamchk/myisamchk to re-create the index. At this time you can also sort the index blocks and create statistics needed for the MySQL optimiser to work more efficiently:

 
myisamchk -rq --analyze --sort-index table_name.MYI
isamchk   -rq --analyze --sort-index table_name.ISM

After you have installed the packed table into the MySQL database directory you should do mysqladmin flush-tables to force mysqld to start using the new table.

If you want to unpack a packed table, you can do this with the --unpack option to isamchk or myisamchk.

4.7.5 mysqld-max, An Extended mysqld Server

mysqld-max is the MySQL server (mysqld) configured with the following configure options:

Option

Comment

-with-server-suffix=-max

Add a 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

The option for enabling InnoDB support is needed only in MySQL 3.23. In MySQL 4 and up, InnoDB is included by default.

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

The Windows MySQL binary distributions includes both the standard mysqld.exe binary and the mysqld-max.exe binary. http://www.mysql.com/downloads/mysql-4.0.html. See section Installing MySQL on Windows.

Note that as BerkeleyDB (BDB) is not available for all platforms, so some of the Max binaries may not have support for it. You can check which table types are supported by doing the following query:

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

The meanings of the values in the second column are:

Value

Meaning

YES

The option is activated and usable.

NO

MySQL is not compiled with support for this option.

DISABLED

The xxxx option is disabled because one started mysqld with --skip-xxxx or because one didn't start mysqld with all needed options to enable the option. In this case the hostname.err file should contain a reason indicating why the option is disabled.

Note: To be able to create InnoDB tables in MySQL version 3.23 you must edit your startup options to include at least the innodb_data_file_path option. See section InnoDB in MySQL Version 3.23.

To get better performance for BDB tables, you should add some configuration options for these, too. See section BDB startup options.

mysqld_safe automatically tries to start any mysqld binary with the -max suffix. This makes it very easy to test out another 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 old mysqld binary is. See section mysqld_safe.

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

The following table shows which table types our MySQL-Max binaries include:

System

BDB

InnoDB

Windows/NT

Y

Y

AIX 4.3

N

Y

HP-UX 11.0

N

Y

Linux-Alpha

N

Y

Linux-Intel

Y

Y

Linux-IA-64

N

Y

Solaris-Intel

N

Y

Solaris-SPARC

Y

Y

SCO OSR5

Y

Y

UnixWare

Y

Y

Mac OS X

N

Y

Note that as of MySQL 4, you do not need a MySQL Max server for InnoDB, because InnoDB is included by default.

4.8 MySQL Client-Side Scripts and Utilities

4.8.1 Overview of the Client-Side Scripts and Utilities  
4.8.2 mysql, The Command-line Tool  
4.8.3 mysqladmin, Administrating a MySQL Server  
4.8.4 mysqlbinlog, Executing the queries from a binary log  
4.8.5 Using mysqlcheck for Table Maintenance and Crash Recovery  
4.8.6 mysqldump, Dumping Table Structure and Data  
4.8.7 mysqlhotcopy, Copying MySQL Databases and Tables  
4.8.8 mysqlimport, Importing Data from Text Files  
4.8.9 mysqlshow, Showing Databases, Tables, and Columns  
4.8.10 mysql_config, Get compile options for compiling clients  
4.8.11 perror, Explaining Error Codes  
4.8.12 How to Run SQL Commands from a Text File  

4.8.1 Overview of the Client-Side Scripts and Utilities

All MySQL clients that communicate with the server using the mysqlclient library use the following environment variables:

Name

Description

MYSQL_UNIX_PORT

The default socket; used for connections to localhost

MYSQL_TCP_PORT

The default TCP/IP port

MYSQL_PWD

The default password

MYSQL_DEBUG

Debug-trace options when debugging

TMPDIR

The directory where temporary tables/files are created

Use of MYSQL_PWD is insecure. See section Connecting to the MySQL Server.

The `mysql' client uses the file named in the MYSQL_HISTFILE environment variable to save the command-line history. The default value for the history file is `$HOME/.mysql_history', where $HOME is the value of the HOME environment variable. See section Environment Variables.

All MySQL programs take many different options. However, every MySQL program provides a --help option that you can use to get a full description of the program's different options. For example, try mysql --help.

You can override default options for all standard client programs with an option file. `my.cnf' Option Files.

The following list briefly describes the client-side MySQL programs:

msql2mysql

A shell script that converts mSQL programs to MySQL. It doesn't handle all cases, but it gives a good start when converting.

mysql

The command-line tool for interactively entering queries or executing queries from a file in batch mode. See section mysql.

mysqlaccess

A script that checks the access privileges for a host, user, and database combination.

mysqladmin

Utility for performing administrative operations, such as creating or dropping databases, reloading the grant tables, flushing tables to disk, and reopening log files. mysqladmin can also be used to retrieve version, process, and status information from the server. See section mysqladmin.

mysqlbinlog

Utility for reading queries from a binary log. Can be used to recover from a crash with an old backup. See section mysqlbinlog.

mysqldump

Dumps a MySQL database into a file as SQL statements or as tab-separated text files. Enhanced freeware originally by Igor Romanenko. See section mysqldump.

mysqlimport

Imports text files into their respective tables using LOAD DATA INFILE. See section mysqlimport.

mysqlshow

Displays information about databases, tables, columns, and indexes.

replace

A utility program that is used by msql2mysql, but that has more general applicability as well. replace changes strings in place in files or on the standard input. Uses a finite state machine to match longer strings first. Can be used to swap strings. For example, this command swaps a and b in the given files:

 
shell> replace a b b a -- file1 file2 ...

4.8.2 mysql, The Command-line Tool

mysql is a simple SQL shell (with GNU readline capabilities). It supports interactive and non-interactive use. When used interactively, query results are presented in an ASCII-table format. When used non-interactively (for example, as a filter), the result is presented in tab-separated format. (The output format can be changed using command-line options.) You can run scripts simply like this:

 
shell> mysql database < script.sql > output.tab

If you have problems due to insufficient memory in the client, use the --quick option! This forces mysql to use mysql_use_result() rather than mysql_store_result() to retrieve the result set.

Using mysql is very easy. Just start it as follows: mysql database or mysql --user=user_name --password=your_password database. Type an SQL statement, end it with `;', `\g', or `\G' and press Enter.

mysql supports the following options:

-?, --help

Display this help and exit.

-A, --no-auto-rehash

No automatic rehashing. One has to use 'rehash' to get table and field completion. This gives a quicker start of mysql.

--prompt=...

Set the mysql prompt to specified format.

-b, --no-beep

Turn off beep-on-error.

-B, --batch

Print results with a tab as separator, each row on a new line. Doesn't use history file.

--character-sets-dir=...

Directory where character sets are located.

-C, --compress

Use compression in server/client protocol.

-#, --debug[=...]

Debug log. Default is 'd:t:o,/tmp/mysql.trace'.

-D, --database=...

Database to use. This is mainly useful in the `my.cnf' file.

--default-character-set=...

Set the default character set.

-e, --execute=...

Execute command and quit. (Output like with -batch)

-E, --vertical

Print the output of a query (rows) vertically. Without this option you can also force this output by ending your statements with \G.

-f, --force

Continue even if we get an SQL error.

-g, --no-named-commands

Named commands are disabled. Use \* form only, or use named commands only in the beginning of a line ending with a semicolon (`;'). Since Version 10.9, the client now starts with this option enabled by default! With the -g option, long format commands will still work from the first line, however.

-G, --enable-named-commands

Named commands are enabled. Long format commands are allowed as well as shortened \* commands.

-i, --ignore-space

Ignore space after function names.

-h, --host=...

Connect to the given host.

-H, --html

Produce HTML output.

-X, --xml

Produce XML output.

-L, --skip-line-numbers

Don't write line number for errors. Useful when one wants to compare result files that includes error messages

--no-pager

Disable pager and print to stdout. See interactive help (\h) also.

--no-tee

Disable outfile. See interactive help (\h) also.

-n, --unbuffered

Flush buffer after each query.

-N, --skip-column-names

Don't write column names in results.

-O, --set-variable var=option

Give a variable a value. --help lists variables. Please note that --set-variable is deprecated since MySQL 4.0, just use --var=option on its own.

-o, --one-database

Only update the default database. This is useful for skipping updates to other database in the binary log.

--pager[=...]

Output type. Default is your ENV variable PAGER. Valid pagers are less, more, cat [> filename], etc. See interactive help (\h) also. This option does not work in batch mode. Pager works only in Unix.

-p[password], --password[=...]

Password to use when connecting to server. If a password is not given on the command-line, you will be prompted for it. Note that if you use the short form -p you can't have a space between the option and the password.

-P port_num, --port=port_num

TCP/IP port number to use for connection.

--protocol=(TCP | SOCKET | PIPE | MEMORY)

To specify the connect protocol to use. New in MySQL 4.1.

-q, --quick

Don't cache result, print it row-by-row. This may slow down the server if the output is suspended. Doesn't use history file.

-r, --raw

Write column values without escape conversion. Used with --batch

--reconnect

If the connection is lost, automatically try to reconnect to the server (but only once).

-s, --silent

Be more silent.

-S --socket=...

Socket file to use for connection.

-t --table

Output in table format. This is default in non-batch mode.

-T, --debug-info

Print some debug information at exit.

--tee=...

Append everything into outfile. See interactive help (\h) also. Does not work in batch mode.

-u, --user=#

User for login if not current user.

-U, --safe-updates[=#], --i-am-a-dummy[=#]

Only allow UPDATE and DELETE that uses keys. See below for more information about this option. You can reset this option if you have it in your `my.cnf' file by using --safe-updates=0.

-v, --verbose

More verbose output (-v -v -v gives the table output format).

-V, --version

Output version information and exit.

-w, --wait

Wait and retry if connection is down instead of aborting.

You can also set the following variables with -O or --set-variable; please note that --set-variable is deprecated since MySQL 4.0, just use --var=option on its own:

Variable Name

Default

Description

connect_timeout

0

Number of seconds before timeout connection.

max_allowed_packet

16777216

Max packetlength to send/receive from to server

net_buffer_length

16384

Buffer for TCP/IP and socket communication

select_limit

1000

Automatic limit for SELECT when using -i-am-a-dummy

max_join_size

1000000

Automatic limit for rows in a join when using -i-am-a-dummy.

If the mysql client loses connection to the server while sending it a query, it will immediately and automatically try to reconnect once to the server and send the query again. Note that even if it succeeds in reconnecting, as your first connection has ended, all your previous session objects are lost : temporary tables, user and session variables. Therefore, the above behaviour may be dangerous for you, as in this example where the server was shut down and restarted without you knowing it :

 
mysql> set @a=1;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t values(@a);
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Query OK, 1 row affected (1.30 sec)

mysql> select * from t;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.05 sec)

The @a user variable has been lost with the connection, and after the reconnection it is undefined. To protect from this risk, you can start the mysql client with the --disable-reconnect option.

If you type 'help' on the command-line, mysql will print out the commands that it supports:

 
mysql> help

MySQL commands:
help    (\h)    Display this text.
?       (\h)    Synonym for `help'.
clear   (\c)    Clear command.
connect (\r)    Reconnect to the server.
                Optional arguments are db and host.
edit    (\e)    Edit command with $EDITOR.
ego     (\G)    Send command to mysql server,
                display result vertically.
exit    (\q)    Exit mysql. Same as quit.
go      (\g)    Send command to mysql server.
nopager (\n)    Disable pager, print to stdout.
notee   (\t)    Don't write into outfile.
pager   (\P)    Set PAGER [to_pager].
                Print the query results via PAGER.
print   (\p)    Print current command.
prompt  (\R)    Change your mysql prompt.
quit    (\q)    Quit mysql.
rehash  (\#)    Rebuild completion hash.
source  (\.)    Execute an SQL script file.
                Takes a file name as an argument.
status  (\s)    Get status information from the server.
system  (\!)    Execute a system shell command.
tee     (\T)    Set outfile [to_outfile].
                Append everything into given outfile.
use     (\u)    Use another database.
                Takes database name as argument.

The edit, nopager, pager, and system commands work only in Unix.

The status command gives you some information about the connection and the server you are using. If you are running in the --safe-updates mode, status will also print the values for the mysql variables that affect your queries.

A useful startup option for beginners (introduced in MySQL Version 3.23.11) is --safe-updates (or --i-am-a-dummy for users that once may have done a DELETE FROM table_name but forgot the WHERE clause). When using this option, mysql sends the following command to the MySQL server when opening the connection:

 
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=#select_limit#,
    SQL_MAX_JOIN_SIZE=#max_join_size#"

where #select_limit# and #max_join_size# are variables that can be set from the mysql command-line. See section SET.

The effect of the above is:

Some useful hints about the mysql client:

Some data is much more readable when displayed vertically, instead of the usual horizontal box type output. For example longer text, which includes new lines, is often much easier to be read with vertical output.

 
mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 lIMIT 300,1\G
*************************** 1. row ***************************
  msg_nro: 3068
     date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
    reply: monty@no.spam.com
  mail_to: "Thimble Smith" <tim@no.spam.com>
      sbj: UTF-8
      txt: >>>>> "Thimble" == Thimble Smith writes:

Thimble> Hi.  I think this is a good idea.  Is anyone familiar with UTF-8
Thimble> or Unicode? Otherwise, I'll put this on my TODO list and see what
Thimble> happens.

Yes, please do that.

Regards,
Monty
     file: inbox-jani-1
     hash: 190402944
1 row in set (0.09 sec)

For logging, you can use the tee option. The tee can be started with option --tee=..., or from the command-line interactively with command tee. All the data displayed on the screen will also be appended into a given file. This can be very useful for debugging purposes also. The tee can be disabled from the command-line with command notee. Executing tee again starts logging again. Without a parameter the previous file will be used. Note that tee will flush the results into the file after each command, just before the command-line appears again waiting for the next command.

Browsing, or searching the results in the interactive mode in Unix less, more, or any other similar program, is now possible with option --pager[=...]. Without argument, mysql client will look for the PAGER environment variable and set pager to that. pager can be started from the interactive command-line with command pager and disabled with command nopager. The command takes an argument optionally and the pager will be set to that. Command pager can be called without an argument, but this requires that the option --pager was used, or the pager will default to stdout. pager works only in Unix, since it uses the popen() function, which doesn't exist in Windows. In Windows, the tee option can be used instead, although it may not be as handy as pager can be in some situations.

A few tips about pager:

You can also combine the two functions above; have the tee enabled, pager set to 'less' and you will be able to browse the results in Unix 'less' and still have everything appended into a file the same time. The difference between Unix tee used with the pager and the mysql client in-built tee, is that the in-built tee works even if you don't have the Unix tee available. The in-built tee also logs everything that is printed on the screen, where the Unix tee used with pager doesn't log quite that much. Last, but not least, the interactive tee is more handy to switch on and off, when you want to log something into a file, but want to be able to turn the feature off sometimes.

From MySQL version 4.0.2 it is possible to change the prompt in the mysql command-line client.

You can use the following prompt options:

Option

Description

\v

mysqld version

\d

database in use

\h

host connected to

\p

port connected on

\u

username

\U

full username@host

\\

`\'

\n

new line break

\t

tab

\

space

\_

space

\R

military hour time (0-23)

\r

standard hour time (1-12)

\m

minutes

\y

two digit year

\Y

four digit year

\D

full date format

\s

seconds

\w

day of the week in three letter format (Mon, Tue, ...)

\P

am/pm

\o

month in number format

\O

month in three letter format (Jan, Feb, ...)

\c

counter that counts up for each command you do

`\' followed by any other letter just becomes that letter.

You may set the prompt in the following places:

Environment Variable

You may set the MYSQL_PS1 environment variable to a prompt string. For example:

 
shell> export MYSQL_PS1="(\u@\h) [\d]> "
`my.cnf'
`.my.cnf'

You may set the prompt option in any MySQL configuration file, in the mysql group. For example:

 
[mysql]
prompt=(\u@\h) [\d]>\_
Command Line

You may set the --prompt option on the command line to mysql. For example:

 
shell> mysql --prompt="(\u@\h) [\d]> "

(user@host) [database]>
Interactively

You may also use the prompt (or \R) command to change your prompt interactively. For example:

 
mysql> prompt (\u@\h) [\d]>\_
PROMPT set to '(\u@\h) [\d]>\_'
(user@host) [database]> 
(user@host) [database]> prompt
Returning to default PROMPT of mysql> 
mysql>

4.8.3 mysqladmin, Administrating a MySQL Server

A utility for performing administrative operations. The syntax is:

 
shell> mysqladmin [OPTIONS] command [command-option] command ...

You can get a list of the options your version of mysqladmin supports by executing mysqladmin --help.

The current mysqladmin supports the following commands:

create databasename

Create a new database.

drop databasename

Delete a database and all its tables.

extended-status

Gives an extended status message from the server.

flush-hosts

Flush all cached hosts.

flush-logs

Flush all logs.

flush-tables

Flush all tables.

flush-privileges

Reload grant tables (same as reload).

kill id,id,...

Kill mysql threads.

password

Set a new password. Change old password to new-password.

ping

Check if mysqld is alive.

processlist

Show list of active threads in server, as with the SHOW PROCESSLIST statement. If the --verbose option is given, the output is like that of SHOW FULL PROCESSLIST.

reload

Reload grant tables.

refresh

Flush all tables and close and open logfiles.

shutdown

Take server down.

slave-start

Start slave replication thread.

slave-stop

Stop slave replication thread.

status

Gives a short status message from the server.

variables

Prints variables available.

version

Get version information from server.

All commands can be shortened to their unique prefix. For example:

 
shell> mysqladmin proc stat
+----+-------+-----------+----+-------------+------+-------+------+
| Id | User  | Host      | db | Command     | Time | State | Info |
+----+-------+-----------+----+-------------+------+-------+------+
| 6  | monty | localhost |    | Processlist | 0    |       |      |
+----+-------+-----------+----+-------------+------+-------+------+
Uptime: 10077  Threads: 1  Questions: 9  Slow queries: 0
Opens: 6 Flush tables: 1  Open tables: 2
Memory in use: 1092K  Max memory used: 1116K

The mysqladmin status command result has the following columns:

Column

Description

Uptime

Number of seconds the MySQL server has been up.

Threads

Number of active threads (clients).

Questions

Number of questions from clients since mysqld was started.

Slow queries

Queries that have taken more than long_query_time seconds. See section The Slow Query Log.

Opens

How many tables mysqld has opened.

Flush tables

Number of flush ..., refresh, and reload commands.

Open tables

Number of tables that are open now.

Memory in use

Memory allocated directly by the mysqld code (only available when MySQL is compiled with -with-debug=full).

Max memory used

Maximum memory allocated directly by the mysqld code (only available when MySQL is compiled with -with-debug=full).

If you do mysqladmin shutdown on a socket (in other words, on a the computer where mysqld is running), mysqladmin will wait until the MySQL pid-file is removed to ensure that the mysqld server has stopped properly.

4.8.4 mysqlbinlog, Executing the queries from a binary log

You can examine the binary log file (see section The Binary Log) with the mysqlbinlog utility.

 
shell> mysqlbinlog hostname-bin.001

will print all queries contained in binlog `hostname-bin.001', together with information (time the query took, id of the thread which issued it, timestamp when it was issued etc).

You can pipe the output of mysqlbinlog into a mysql client; this is used to recover from a crash when you have an old backup (see section Database Backups):

 
shell> mysqlbinlog hostname-bin.001 | mysql

or

 
shell> mysqlbinlog hostname-bin.[0-9]* | mysql

You can also redirect the output of mysqlbinlog to a text file instead, modify this text file (to cut queries you don't want to execute for some reason), then execute the queries from the text file into mysql.

mysqlbinlog has the position=# options which will print only queries whose offset in the binlog is greater or equal to #.

If you have more than one binary log to execute on the MySQL server, the safe method is to do it in one unique MySQL connection. Here is what may be UNsafe:

 
shell> mysqlbinlog hostname-bin.001 | mysql # DANGER!!
shell> mysqlbinlog hostname-bin.002 | mysql # DANGER!!

It will cause problems if the first binlog contains a CREATE TEMPORARY TABLE and the second one contains a query which uses this temporary table: when the first mysql terminates, it will drop the temporary table, so the second mysql will report "unknown table". This is why you should run all binlogs you want in one unique connection, especially if you use temporary tables. Here are two possible ways:

 
shell> mysqlbinlog hostname-bin.001 hostname-bin.002 | mysql
 
shell> mysqlbinlog hostname-bin.001 >  /tmp/queries.sql
shell> mysqlbinlog hostname-bin.002 >> /tmp/queries.sql
shell> mysql -e "source /tmp/queries.sql"

Starting from MySQL 4.0.14, mysqlbinlog can prepare suitable input for mysql to execute a LOAD DATA INFILE from a binlog. As the binlog contains the data to load (this is true for MySQL 4.0; MySQL 3.23 did not write the loaded data into the binlog, so the original file was needed when one wanted to execute the content of the binlog), mysqlbinlog will copy this data to a temporary file and print a LOAD DATA INFILE command for mysql to load this temporary file. The location where the temporary file is created is by default the temporary directory; it can be changed with the local-load option of mysqlbinlog.

Before MySQL 4.1, mysqlbinlog could not prepare suitable output for mysql when the binary log contained queries from different threads using temporary tables of the same name, if these queries were interlaced. This is solved in MySQL 4.1.

You can also use mysqlbinlog to read the binary log directly from a remote MySQL server.

mysqlbinlog --help will give you more information.

4.8.5 Using mysqlcheck for Table Maintenance and Crash Recovery

Since MySQL version 3.23.38 you will be able to use a new checking and repairing tool for MyISAM tables. The difference to myisamchk is that mysqlcheck should be used when the mysqld server is running, whereas myisamchk should be used when it is not. The benefit is that you no longer have to take the server down for checking or repairing your tables.

mysqlcheck uses MySQL server commands CHECK, REPAIR, ANALYZE and OPTIMIZE in a convenient way for the user.

There are three alternative ways to invoke mysqlcheck:

 
shell> mysqlcheck [OPTIONS] database [tables]
shell> mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
shell> mysqlcheck [OPTIONS] --all-databases

So it can be used in a similar way as mysqldump when it comes to what databases and tables you want to choose.

mysqlcheck does have a special feature compared to the other clients; the default behaviour, checking tables (-c), can be changed by renaming the binary. So if you want to have a tool that repairs tables by default, you should just copy mysqlcheck to your harddrive with a new name, mysqlrepair, or alternatively make a symbolic link to mysqlrepair and name the symbolic link as mysqlrepair. If you invoke mysqlrepair now, it will repair tables by default.

The names that you can use to change mysqlcheck default behaviour are here:

 
mysqlrepair:   The default option will be -r
mysqlanalyze:  The default option will be -a
mysqloptimize: The default option will be -o

The options available for mysqlcheck are listed here, please check what your version supports with mysqlcheck --help.

-A, --all-databases

Check all the databases. This will be same as -databases with all databases selected

-1, --all-in-1

Instead of making one query for each table, execute all queries in 1 query separately for each database. Table names will be in a comma separated list.

-a, --analyze

Analyse given tables.

--auto-repair

If a checked table is corrupted, automatically fix it. Repairing will be done after all tables have been checked, if corrupted ones were found.

-#, --debug=...

Output debug log. Often this is 'd:t:o,filename'

--character-sets-dir=...

Directory where character sets are

-c, --check

Check table for errors

-C, --check-only-changed

Check only tables that have changed since last check or haven't been closed properly.

--compress

Use compression in server/client protocol.

-?, --help

Display this help message and exit.

-B, --databases

To check several databases. Note the difference in usage; in this case no tables are given. All name arguments are regarded as database names.

--default-character-set=...

Set the default character set

-F, --fast

Check only tables that hasn't been closed properly

-f, --force

Continue even if we get an sql-error.

-e, --extended

If you are using this option with CHECK TABLE, it will ensure that the table is 100 percent consistent, but will take a long time.

If you are using this option with REPAIR TABLE, it will run an extended repair on the table, which may not only take a long time to execute, but may produce a lot of garbage rows also!

-h, --host=...

Connect to host.

-m, --medium-check

Faster than extended-check, but only finds 99.99 percent of all errors. Should be good enough for most cases.

-o, --optimize

Optimise table

-p, --password[=...]

Password to use when connecting to server. If password is not given it's solicited on the tty.

-P, --port=...

Port number to use for TCP/IP connections.

--protocol=(TCP | SOCKET | PIPE | MEMORY)

To specify the connect protocol to use. New in MySQL 4.1.

-q, --quick

If you are using this option with CHECK TABLE, it prevents the check from scanning the rows to check for wrong links. This is the fastest check.

If you are using this option with REPAIR TABLE, it will try to repair only the index tree. This is the fastest repair method for a table.

-r, --repair

Can fix almost anything except unique keys that aren't unique.

-s, --silent

Print only error messages.

-S, --socket=...

Socket file to use for connection.

--tables

Overrides option -databases (-B).

-u, --user=#

User for login if not current user.

-v, --verbose

Print information about the various stages.

-V, --version

Output version information and exit.

4.8.6 mysqldump, Dumping Table Structure and Data

Utility to dump a database or a collection of database for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump will contain SQL statements to create the table and/or populate the table.

If you are doing a backup on the server, you should consider using the mysqlhotcopy instead. See section mysqlhotcopy.

 
shell> mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

If you don't give any tables or use the --databases or --all-databases, the whole database(s) will be dumped.

You can get a list of the options your version of mysqldump supports by executing mysqldump --help.

Note that if you run mysqldump without --quick or --opt, mysqldump will load the whole result set into memory before dumping the result. This will probably be a problem if you are dumping a big database.

Note that if you are using a new copy of the mysqldump program and you are going to do a dump that will be read into a very old MySQL server, you should not use the --opt or -e options.

mysqldump supports the following options:

--add-locks

Add LOCK TABLES before and UNLOCK TABLE after each table dump. (To get faster inserts into MySQL.)

--add-drop-table

Add a drop table before each create statement.

-A, --all-databases

Dump all the databases. This will be same as --databases with all databases selected.

-a, --all

Include all MySQL-specific create options.

--allow-keywords

Allow creation of column names that are keywords. This works by prefixing each column name with the table name.

-c, --complete-insert

Use complete insert statements (with column names).

-C, --compress

Compress all information between the client and the server if both support compression.

-B, --databases

To dump several databases. Note the difference in usage. In this case no tables are given. All name arguments are regarded as database names. USE db_name; will be included in the output before each new database.

--delayed

Insert rows with the INSERT DELAYED command.

-e, --extended-insert

Use the new multiline INSERT syntax. (Gives more compact and faster inserts statements.)

-#, --debug[=option_string]

Trace usage of the program (for debugging).

--help

Display a help message and exit.

--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...

These options are used with the -T option and have the same meaning as the corresponding clauses for LOAD DATA INFILE. See section LOAD DATA.

-F, --flush-logs

Flush log file in the MySQL server before starting the dump.

-f, --force,

Continue even if we get an SQL error during a table dump.

-h, --host=..

Dump data from the MySQL server on the named host. The default host is localhost.

-l, --lock-tables.

Lock all tables before starting the dump. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables.

Please note that when dumping multiple databases, --lock-tables will lock tables for each database separately. So using this option will not guarantee your tables will be logically consistent between databases. Tables in different databases may be dumped in completely different states.

-K, --disable-keys

/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and /*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put in the output. This will make loading the data into a MySQL 4.0 server faster as the indexes are created after all data are inserted.

-n, --no-create-db

CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; will not be put in the output. The above line will be added otherwise, if a --databases or --all-databases option was given.

-t, --no-create-info

Don't write table creation information (the CREATE TABLE statement).

-d, --no-data

Don't write any row information for the table. This is very useful if you just want to get a dump of the structure for a table!

--opt

Same as --quick --add-drop-table --add-locks --extended-insert --lock-tables. Should give you the fastest possible dump for reading into a MySQL server.

-pyour_pass, --password[=your_pass]

The password to use when connecting to the server. If you specify no `=your_pass' part, mysqldump you will be prompted for a password.

-P, --port=...

Port number to use for TCP/IP connections.

--protocol=(TCP | SOCKET | PIPE | MEMORY)

To specify the connect protocol to use. New in MySQL 4.1.

-q, --quick

Don't buffer query, dump directly to stdout. Uses mysql_use_result() to do this.

-Q, --quote-names

Quote table and column names within ``' characters.

-r, --result-file=...

Direct output to a given file. This option should be used in MSDOS, because it prevents new line `\n' from being converted to `\n\r' (new line + carriage return).

--single-transaction

This option issues a BEGIN SQL command before dumping data from server. It is mostly useful with InnoDB tables and READ_COMMITTED transaction isolation level, as in this mode it will dump the consistent state of the database at the time then BEGIN was issued without blocking any applications.

When using this option you should keep in mind that only transactional tables will be dumped in a consistent state, for example, any MyISAM or HEAP tables dumped while using this option may still change state.

The --single-transaction option was added in version 4.0.2. This option is mutually exclusive with the --lock-tables option as LOCK TABLES already commits a previous transaction internally.

-S /path/to/socket, --socket=/path/to/socket

The socket file to use when connecting to localhost (which is the default host).

--tables

Overrides option -databases (-B).

-T, --tab=path-to-some-directory

Creates a table_name.sql file, that contains the SQL CREATE commands, and a table_name.txt file, that contains the data, for each give table. The format of the `.txt' file is made according to the --fields-xxx and --lines--xxx options. Note: This option only works if mysqldump is run on the same machine as the mysqld daemon. You must use a MySQL account that has the FILE privilege, and the login user/group that mysqld is running as (normally user mysql, group mysql) must have permission to create/write a file at the location you specify.

-u user_name, --user=user_name

The MySQL user name to use when connecting to the server. The default value is your Unix login name.

-O var=option, --set-variable var=option

Set the value of a variable. The possible variables are listed below. Please note that --set-variable is deprecated since MySQL 4.0, just use --var=option on its own.

-v, --verbose

Verbose mode. Print out more information on what the program does.

-V, --version

Print version information and exit.

-w, --where='where-condition'

Dump only selected records. Note that quotes are mandatory:

 
"--where=user='jimf'" "-wuserid>1" "-wuserid<1"
-X, --xml

Dumps a database as well formed XML

-x, --first-slave

Locks all tables across all databases.

--master-data

Like --first-slave, but also prints some CHANGE MASTER TO commands which will later make your slave start from the right position in the master's binlogs, if you have set up your slave using this SQL dump of the master.

-O net_buffer_length=#, where # < 16M

When creating multi-row-insert statements (as with option --extended-insert or --opt), mysqldump will create rows up to net_buffer_length length. If you increase this variable, you should also ensure that the max_allowed_packet variable in the MySQL server is bigger than the net_buffer_length.

The most normal use of mysqldump is probably for making a backup of whole databases. See section Database Backups.

 
mysqldump --opt database > backup-file.sql

You can read this back into MySQL with:

 
mysql database < backup-file.sql

or

 
mysql -e "source /patch-to-backup/backup-file.sql" database

However, it's also very useful to populate another MySQL server with information from a database:

 
mysqldump --opt database | mysql ---host=remote-host -C database

It is possible to dump several databases with one command:

 
mysqldump --databases database1 [database2 ...] > my_databases.sql

If all the databases are wanted, one can use:

 
mysqldump --all-databases > all_databases.sql

4.8.7 mysqlhotcopy, Copying MySQL Databases and Tables

mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH TABLES and cp or scp to quickly make a backup of a database. It's the fastest way to make a backup of the database or single tables, but it can only be run on the same machine where the database directories are. mysqlhotcopy works only on Unix, and it works only for MyISAM and ISAM tables.

 
mysqlhotcopy db_name [/path/to/new_directory]

mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

mysqlhotcopy db_name./regex/

mysqlhotcopy supports the following options:

-?, --help

Display a help screen and exit

-u, --user=#

User for database login

-p, --password=#

Password to use when connecting to server

-P, --port=#

Port to use when connecting to local server

-S, --socket=#

Socket to use when connecting to local server

--allowold

Don't abort if target already exists (rename it _old)

--keepold

Don't delete previous (now renamed) target when done

--noindices

Don't include full index files in copy to make the backup smaller and faster The indexes can later be reconstructed with myisamchk -rq..

--method=#

Method for copy (cp or scp).

-q, --quiet

Be silent except for errors

--debug

Enable debug

-n, --dryrun

Report actions without doing them

--regexp=#

Copy all databases with names matching regexp

--suffix=#

Suffix for names of copied databases

--checkpoint=#

Insert checkpoint entry into specified db.table

--flushlog

Flush logs once all tables are locked.

--tmpdir=#

Temporary directory (instead of /tmp).

You can use perldoc mysqlhotcopy to get more complete documentation for mysqlhotcopy.

mysqlhotcopy reads the groups [client] and [mysqlhotcopy] from the option files.

To be able to execute mysqlhotcopy you need write access to the backup directory, the SELECT privilege for the tables you are about to copy and the MySQL RELOAD privilege (to be able to execute FLUSH TABLES).

4.8.8 mysqlimport, Importing Data from Text Files

mysqlimport provides a command-line interface to the LOAD DATA INFILE SQL statement. Most options to mysqlimport correspond directly to the same options to LOAD DATA INFILE. See section LOAD DATA.

mysqlimport is invoked like this:

 
shell> mysqlimport [options] database textfile1 [textfile2 ...]

For each text file named on the command-line, mysqlimport strips any extension from the filename and uses the result to determine which table to import the file's contents into. For example, files named `patient.txt', `patient.text', and `patient' would all be imported into a table named patient.

mysqlimport supports the following options:

-c, --columns=...

This option takes a comma-separated list of field names as an argument. The field list is used to create a proper LOAD DATA INFILE command, which is then passed to MySQL. See section LOAD DATA.

-C, --compress

Compress all information between the client and the server if both support compression.

-#, --debug[=option_string]

Trace usage of the program (for debugging).

-d, --delete

Empty the table before importing the text file.

--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...

These options have the same meaning as the corresponding clauses for LOAD DATA INFILE. See section LOAD DATA.

-f, --force

Ignore errors. For example, if a table for a text file doesn't exist, continue processing any remaining files. Without --force, mysqlimport exits if a table doesn't exist.

--help

Display a help message and exit.

-h host_name, --host=host_name

Import data to the MySQL server on the named host. The default host is localhost.

-i, --ignore

See the description for the --replace option.

--ignore-lines=n

Ignore first n lines of the datafile.

-l, --lock-tables

Lock all tables for writing before processing any text files. This ensures that all tables are synchronised on the server.

-L, --local

Read input files from the client. By default, text files are assumed to be on the server if you connect to localhost (which is the default host).

-pyour_pass, --password[=your_pass]

The password to use when connecting to the server. If you specify no `=your_pass' part, mysqlimport you will be prompted for a password.

-P port_num, --port=port_num

TCP/IP port number to use for connection.

--protocol=(TCP | SOCKET | PIPE | MEMORY)

To specify the connect protocol to use. New in MySQL 4.1.

-r, --replace

The --replace and --ignore options control handling of input records that duplicate existing records on unique key values. If you specify --replace, new rows replace existing rows that have the same unique key value. If you specify --ignore, input rows that duplicate an existing row on a unique key value are skipped. If you don't specify either option, an error occurs when a duplicate key value is found, and the rest of the text file is ignored.

-s, --silent

Silent mode. Write output only when errors occur.

-S /path/to/socket, --socket=/path/to/socket

The socket file to use when connecting to localhost (which is the default host).

-u user_name, --user=user_name

The MySQL user name to use when connecting to the server. The default value is your Unix login name.

-v, --verbose

Verbose mode. Print out more information what the program does.

-V, --version

Print version information and exit.

Here is a sample run using mysqlimport:

 
$ mysql --version
mysql  Ver 9.33 Distrib 3.22.25, for pc-linux-gnu (i686)
$ uname -a
Linux xxx.com 2.2.5-15 #1 Mon Apr 19 22:21:09 EDT 1999 i586 unknown
$ mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
$ ed
a
100     Max Sydow
101     Count Dracula
.
w imptest.txt
32
q
$ od -c imptest.txt
0000000   1   0   0  \t   M   a   x       S   y   d   o   w  \n   1   0
0000020   1  \t   C   o   u   n   t       D   r   a   c   u   l   a  \n
0000040
$ mysqlimport --local test imptest.txt
test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
$ mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id   | n             |
+------+---------------+
|  100 | Max Sydow     |
|  101 | Count Dracula |
+------+---------------+

4.8.9 mysqlshow, Showing Databases, Tables, and Columns

mysqlshow can be used to quickly look at which databases exist, their tables, and the table's columns.

With the mysql program you can get the same information with the SHOW commands. See section SHOW Syntax.

mysqlshow is invoked like this:

 
shell> mysqlshow [OPTIONS] [database [table [column]]]

Note that in newer MySQL versions, you only see those database/tables/columns for which you have some privileges.

If the last argument contains a shell or SQL wildcard (*, ?, % or _) then only what's matched by the wildcard is shown. If a database contains underscore(s), those should be escaped with backslash (some Unix shells will require two), in order to get tables / columns properly. '*' are converted into SQL '%' wildcard and '?' into SQL '_' wildcard. This may cause some confusion when you try to display the columns for a table with a _ as in this case mysqlshow only shows you the table names that match the pattern. This is easily fixed by adding an extra % last on the command-line (as a separate argument).

4.8.10 mysql_config, Get compile options for compiling clients

mysql_config provides you with useful information how to compile your MySQL client and connect it to MySQL.

mysql_config supports the following options:

--cflags

Compiler flags to find include files

--libs

Libs and options required to link with the MySQL client library.

--socket

The default socket name, defined when configuring MySQL.

--port

The default port number, defined when configuring MySQL.

--version

Version number and version for the MySQL distribution

--libmysqld-libs

Libs and options required to link with the MySQL embedded server.

If you execute mysql_config without any options it will print all options it supports plus the value of all options:

 
shell> mysql_config
sage: /usr/local/mysql/bin/mysql_config [OPTIONS]
Options:
        --cflags         [-I'/usr/local/mysql/include/mysql']
        --libs           [-L'/usr/local/mysql/lib/mysql' -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib -lssl -lcrypto]
        --socket         [/tmp/mysql.sock]
        --port           [3306]
        --version        [4.0.8-gamma]
        --libmysqld-libs [ -L'/usr/local/mysql/lib/mysql' -lmysqld -lpthread -lz -lcrypt -lnsl -lm  -lpthread  -lrt]

You can use this to compile a MySQL client by as follows:

 
CFG=/usr/local/mysql/bin/mysql_config
sh -c "gcc -o progname `$CFG --cflags` progname.c `$CFG --libs`"

4.8.11 perror, Explaining Error Codes

For most system errors MySQL will, in addition to a internal text message, also print the system error code in one of the following styles: message ... (errno: #) or message ... (Errcode: #).

You can find out what the error code means by either examining the documentation for your system or use the perror utility.

perror prints a description for a system error code, or an MyISAM/ISAM storage engine (table handler) error code.

perror is invoked like this:

 
shell> perror [OPTIONS] [ERRORCODE [ERRORCODE...]]

Example:

shell> perror 13 64
Error code  13:  Permission denied
Error code  64:  Machine is not on the network

Note that the error messages are mostly system dependent!

4.8.12 How to Run SQL Commands from a Text File

The mysql client typically is used interactively, like this:

 
shell> mysql database

However, it's also possible to put your SQL commands in a file and tell mysql to read its input from that file. To do so, create a text file `text_file' that contains the commands you wish to execute. Then invoke mysql as shown here:

 
shell> mysql database < text_file

You can also start your text file with a USE db_name statement. In this case, it is unnecessary to specify the database name on the command line:

 
shell> mysql < text_file

If you are already running mysql, you can execute an SQL script file using the source command:

 
mysql> source filename;

For more information about batch mode, Using mysql in Batch Mode.

4.9 The MySQL Log Files

MySQL has several different log files that can help you find out what's going on inside mysqld:

Log file

Description

The error log

Problems encountering starting, running or stopping mysqld.

The isam log

Logs all changes to the ISAM tables. Used only for debugging the isam code.

The query log

Established connections and executed queries.

The update log

Deprecated: Stores all statements that changes data

The binary log

Stores all statements that changes something. Used also for replication

The slow log

Stores all queries that took more than long_query_time seconds to execute or didn't use indexes.

All logs can be found in the mysqld data directory. You can force mysqld to reopen the log files (or in some cases switch to a new log) by executing FLUSH LOGS. See section FLUSH Syntax.

4.9.1 The Error Log  
4.9.2 The General Query Log  
4.9.3 The Update Log  
4.9.4 The Binary Log  
4.9.5 The Slow Query Log  
4.9.6 Log File Maintenance  

4.9.1 The Error Log

The error log file contains information indicating when mysqld was started and stopped and also any critical errors found when running.

If mysqld dies unexpectedly and mysqld_safe needs to restart mysqld, mysqld_safe will write a restarted mysqld row in this file. This log also holds a warning if mysqld notices a table that needs to be automatically checked or repaired.

On some operating systems, the error log will contain a stack trace for where mysqld died. This can be used to find out where mysqld died. See section Using a Stack Trace.

Beginning with MySQL 4.0.10 you can specify where mysqld stores the error log file with the option --log-error[=filename]. If no file name is given mysqld will use mysql-data-dir/'hostname'.err on Unix and `\mysql\data\mysql.err' on Windows. If you execute flush logs the old file will be prefixed with --old and mysqld will create a new empty log file.

In older MySQL versions the error log handling was done by mysqld_safe which redirected the error file to 'hostname'.err. One could change this file name with the option --err-log=filename.

If you don't specify --log-error or if you use the --console option the errors will be written to stderr (the terminal).

On Windows, the output is always written to the .err file if --console is not given.

4.9.2 The General Query Log

If you want to know what happens within mysqld, you should start it with --log[=file]. This will log all connections and queries to the log file (by default named `'hostname'.log'). This log can be very useful when you suspect an error in a client and want to know exactly what mysqld thought the client sent to it.

Older versions of the mysql.server script (from MySQL 3.23.4 to 3.23.8) pass safe_mysqld a --log option (enable general query log). If you need better performance when you start using MySQL in a production environment, you can remove the --log option from mysql.server or change it to --log-bin. See section The Binary Log.

The entries in this log are written as mysqld receives the questions. This may be different from the order in which the statements are executed. This is in contrast to the update log and the binary log which are written after the query is executed, but before any locks are released.

4.9.3 The Update Log

Note: the update log is replaced by the binary log. See section The Binary Log. With this you can do anything that you can do with the update log. The update log will be removed in MySQL 5.0.

When started with the --log-update[=file_name] option, mysqld writes a log file containing all SQL commands that update data. If no filename is given, it defaults to the name of the host machine. If a filename is given, but it doesn't contain a path, the file is written in the data directory. If `file_name' doesn't have an extension, mysqld will create log file names like so: `file_name.###', where ### is a number that is incremented each time you execute mysqladmin refresh, execute mysqladmin flush-logs, execute the FLUSH LOGS statement, or restart the server.

Note: for the above scheme to work, you must not create your own files with the same filename as the update log + some extensions that may be regarded as a number, in the directory used by the update log!

If you use the --log or -l options, mysqld writes a general log with a filename of `hostname.log', and restarts and refreshes do not cause a new log file to be generated (although it is closed and reopened). In this case you can copy it (on Unix) by doing:

 
mv hostname.log hostname-old.log
mysqladmin flush-logs
cp hostname-old.log to-backup-directory
rm hostname-old.log

Update logging is smart because it logs only statements that really update data. So an UPDATE or a DELETE with a WHERE that finds no rows is not written to the log. It even skips UPDATE statements that set a column to the value it already has.

The update logging is done immediately after a query completes but before any locks are released or any commit is done. This ensures that the log will be logged in the execution order.

If you want to update a database from update log files, you could do the following (assuming your update logs have names of the form `file_name.###'):

 
shell> ls -1 -t -r file_name.[0-9]* | xargs cat | mysql

ls is used to get all the log files in the right order.

This can be useful if you have to revert to backup files after a crash and you want to redo the updates that occurred between the time of the backup and the crash.

4.9.4 The Binary Log

The intention is that the binary log should replace the update log, so we recommend you to switch to this log format as soon as possible! The update log will be removed in MySQL 5.0.

The binary log contains all information that is available in the update log in a more efficient format. It also contains information about how long each query took that updated the database. It doesn't contain queries that don't modify any data. If you want to log all queries (for example to find a problem query) you should use the general query log. See section The General Query Log.

The binary log is also used when you are replicating a slave from a master. See section Replication in MySQL.

When started with the --log-bin[=file_name] option, mysqld writes a log file containing all SQL commands that update data. If no file name is given, it defaults to the name of the host machine followed by -bin. If file name is given, but it doesn't contain a path, the file is written in the data directory.

If you supply an extension to --log-bin=filename.extension, the extension will be silenty removed.

To the binary log filename mysqld will append an extension that is a number that is incremented each time you execute mysqladmin refresh, execute mysqladmin flush-logs, execute the FLUSH LOGS statement or restart the server. A new binary log will also automatically be created when the current one's size reaches max_binlog_size. Note if you are using transactions: a transaction is written in one chunk to the binary log, hence it is never split between several binary logs. Therefore, if you have big transactions, you may see binlogs bigger than max_binlog_size.

You can delete all binary log files with the RESET MASTER command (see section RESET), or only some of them with PURGE MASTER LOGS (see section SQL Commands Related to Replication).

You can use the following options to mysqld to affect what is logged to the binary log (please make sure to read the notes which follow this table):

Option

Description

binlog-do-db=database_name

Tells the master that it should log updates to the binary log if the current database (that is, the one selected by USE) database is 'database_name'. All others databases which are not explicitly mentioned are ignored. Note that if you use this you should ensure that you only do updates in the current database. (Example: binlog-do-db=some_database)

Example of what does not work as you could expect it: if the server is started with binlog-do-db=sales, and you do USE prices; UPDATE sales.january SET amount=amount+1000;, this query will not be written into the binary log.

binlog-ignore-db=database_name

Tells the master that updates where the current database (that is, the one selected by USE) is 'database_name' should not be stored in the binary log. Note that if you use this you should ensure that you only do updates in the current database. (Example: binlog-ignore-db=some_database)

Example of what does not work as you could expect it: if the server is started with binlog-ignore-db=sales, and you do USE prices; UPDATE sales.january SET amount=amount+1000;, this query will be written into the binary log.

The rules are evaluated in the following order, to decide if the query should be written to the binary log or not:

  1. Are there binlog-do-db or binlog-ignore-db rules?
  2. So there are some rules (binlog-do-db or binlog-ignore-db or both). Is there a current database (has any database been selected by USE?)?
  3. There is a current database. Are there some binlog-do-db rules?
  4. There are some binlog-ignore-db rules. Does the current database match any of the binlog-ignore-db rules?

So for example, a slave running with only binlog-do-db=sales will not write to the binlog any query whose current database is different from sales (in other words, binlog-do-db can sometimes mean "ignore other databases").

To be able to know which different binary log files have been used, mysqld will also create a binary log index file that contains the name of all used binary log files. By default this has the same name as the binary log file, with the extension '.index'. You can change the name of the binary log index file with the --log-bin-index=[filename] option. You should not manually edit this file while mysqld is running; doing this would confuse mysqld.

If you are using replication, you should not delete old binary log files until you are sure that no slave will ever need to use them. One way to do this is to do mysqladmin flush-logs once a day and then remove any logs that are more than 3 days old. You can remove them manually, or preferably using PURGE MASTER LOGS (see section SQL Commands Related to Replication) which will also safely update the binary log index file for you (and which can take a date argument since MySQL 4.1)

A connexion with the SUPER privilege can disable the binary logging of its queries using SET SQL_LOG_BIN=0. See section SQL Commands Related to Replication.

You can examine the binary log file with the mysqlbinlog utility. For example, you can update a MySQL server from the binary log as follows:

 
shell> mysqlbinlog log-file | mysql -h server_name

See mysqlbinlog, Executing the queries from a binary log for more information on the mysqlbinlog utility and how to use it.

If you are using BEGIN [WORK] or SET AUTOCOMMIT=0, you must use the MySQL binary log for backups instead of the old update log, which will be removed in MySQL 5.0.

The binary logging is done immediately after a query completes but before any locks are released or any commit is done. This ensures that the log will be logged in the execution order.

Updates to non-transactional tables are stored in the binary log immediately after execution. For transactional tables such as BDB or InnoDB tables, all updates (UPDATE, DELETE or INSERT) that change tables are cached until a COMMIT command is sent to the server. At this point mysqld writes the whole transaction to the binary log before the COMMIT is executed. Every thread will, on start, allocate a buffer of binlog_cache_size to buffer queries. If a query is bigger than this, the thread will open a temporary file to store the transaction. The temporary file will be deleted when the thread ends.

The max_binlog_cache_size (default 4G) can be used to restrict the total size used to cache a multi-query transaction. If a transaction is bigger than this it will fail and roll back.

If you are using the update or binary log, concurrent inserts will be converted to normal inserts when using CREATE ... SELECT or INSERT ... SELECT. This is to ensure that you can recreate an exact copy of your tables by applying the log on a backup.

4.9.5 The Slow Query Log

When started with the --log-slow-queries[=file_name] option, mysqld writes a log file containing all SQL commands that took more than long_query_time seconds to execute. The time to get the initial table locks are not counted as execution time.

The slow query log is logged after the query is executed and after all locks has been released. This may be different from the order in which the statements are executed.

If no file name is given, it defaults to the name of the host machine suffixed with -slow.log. If a filename is given, but doesn't contain a path, the file is written in the data directory.

The slow query log can be used to find queries that take a long time to execute and are thus candidates for optimisation. With a large log, that can become a difficult task. You can pipe the slow query log through the mysqldumpslow command to get a summary of the queries which appear in the log.

You are using --log-long-format then also queries that are not using indexes are printed. See section mysqld Command-line Options.

4.9.6 Log File Maintenance

The MySQL Server can create a number of different log files, which make it easy to see what is going on. See section The MySQL Log Files. One must however regularly clean up these files, to ensure that the logs don't take up too much disk space.

When using MySQL with log files, you will, from time to time, want to remove/backup old log files and tell MySQL to start logging on new files. See section Database Backups.

On a Linux (Red Hat) installation, you can use the mysql-log-rotate script for this. If you installed MySQL from an RPM distribution, the script should have been installed automatically. Note that you should be careful with this if you are using the log for replication!

On other systems you must install a short script yourself that you start from cron to handle log files.

You can force MySQL to start using new log files by using mysqladmin flush-logs or by using the SQL command FLUSH LOGS. If you are using MySQL Version 3.21 you must use mysqladmin refresh.

The above command does the following:

If you are using only an update log, you only have to flush the logs and then move away the old update log files to a backup. If you are using the normal logging, you can do something like:

 
shell> cd mysql-data-directory
shell> mv mysql.log mysql.old
shell> mysqladmin flush-logs

and then take a backup and remove `mysql.old'.

4.10 Replication in MySQL

4.10.1 Introduction  
4.10.2 Replication Implementation Overview  
4.10.3 Replication Implementation Details  
4.10.4 How To Set Up Replication  
4.10.5 Replication Features and Known Problems  
4.10.6 Replication Options in `my.cnf'  
4.10.7 SQL Commands Related to Replication  
4.10.8 Replication FAQ  
4.10.9 Troubleshooting Replication  

This section describes the various replication features in MySQL. It serves as a reference to the options available with replication. You will be introduced to replication and learn how to implement it. Toward the end, there are some frequently asked questions and descriptions of problems and how to solve them.

We suggest that you visit our website at http://www.mysql.com/ often and read updates to this section. Replication is constantly being improved, and we update the manual frequently with the most current information.

4.10.1 Introduction

One-way replication can be used is to increase both robustness and speed. For robustness you can have two systems and can switch to the backup if you have problems with the master. The extra speed is achieved by sending a part of the non-updating queries to the replica server. Of course this only works if non-updating queries dominate, but that is the normal case.

Starting in Version 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while the other acts as the slave. Note that one server could play the roles of master in one pair and slave in the other. The master server keeps a binary log of updates (see section The Binary Log) and an index file to binary logs to keep track of log rotation. The slave, upon connecting, informs the master where it left off since the last successfully propagated update, catches up on the updates, and then blocks and waits for the master to notify it of the new updates.

Note that if you are using replication all updates to the tables you replicate should be done through the master, unless you are always careful of avoiding conflicts between updates which users issue on the master and those which users issue on the slave.

Another benefit of using replication is that one can get non-disturbing backups of the system by doing a backup on a slave instead of doing it on the master. See section Database Backups.

4.10.2 Replication Implementation Overview

MySQL replication is based on the server keeping track of all changes to your database (updates, deletes, etc) in the binary log (see section The Binary Log) and the slave server(s) reading the saved queries from the master server's binary log so that the slave can execute the same queries on its copy of the data.

It is very important to realise that the binary log is simply a record starting from a fixed point in time (the moment you enable binary logging). Any slaves which you set up will need copies of the data from your master as it existed the moment that you enabled binary logging on the master. If you start your slaves with data that doesn't agree with what was on the master when the binary log was started, your slaves may fail.

Please see the following table for an indication of master-slave compatibility between different versions. With regard to version 4.0, we recommend using same version on both sides.

Master

Master

Master

Master

3.23.33 and up

4.0.0

4.0.1

4.0.3 and up

Slave

3.23.33 and up

yes

no

no

no

Slave

4.0.0

no

yes

no

no

Slave

4.0.1

yes

no

yes

no

Slave

4.0.3 and up

yes

no

no

yes

Note: MySQL Version 4.0.2 is not recommended for replication. As a general rule, it's always recommended to use recent MySQL versions for replication.

Note that when you upgrade a master from MySQL 3.23 to MySQL 4.0 (or 4.1) you should not restart replication using old 3.23 binary logs, because this will unfortunately confuse the 4.0 slave. The upgrade can be safely done this way:

Starting from 4.0.0, one can use LOAD DATA FROM MASTER to set up a slave. Be aware that LOAD DATA FROM MASTER currently works only if all the tables on the master are MyISAM type, and will acquire a global read lock, so no writes are possible while the tables are being transferred from the master. When we implement hot lock-free table backup (in MySQL 5.0), this global read lock will no longer be necessary.

Due to the above limitation, we recommend that at this point you use LOAD DATA FROM MASTER only if the dataset on the master is relatively small, or if a prolonged read lock on the master is acceptable. While the actual speed of LOAD DATA FROM MASTER may vary from system to system, a good rule for a rough estimate of how long it is going to take is 1 second per 1 MB of the datafile. You will get close to the estimate if both master and slave are equivalent to 700 MHz Pentium, are connected through 100 MBit/s network, and your index file is about half the size of your datafile. Of course, this is only a rough order of magnitude estimate.

Once a slave is properly configured and running, it will simply connect to the master and wait for updates to process. If the master goes away or the slave loses connectivity with your master, it will keep trying to connect every master-connect-retry seconds until it is able to reconnect and resume listening for updates.

Each slave keeps track of where it left off. The master server has no knowledge of how many slaves there are or which ones are up-to-date at any given time.

4.10.3 Replication Implementation Details

Three threads are involved in replication : one on the master and two on the slave. When START SLAVE is issued, the I/O thread is created on the slave. It connects to the master and asks it to send its binlogs. Then one thread (named Binlog_dump in SHOW PROCESSLIST on the master) is created on the master to send these binlogs. The I/O thread reads what Binlog_dump sends and simply copies it to some local files in the slave's data directory called relay logs. The last thread, the SQL thread, is created on the slave; it reads the relay logs and executes the queries it contains.

Here is how the three threads show up in SHOW PROCESSLIST. All SHOW PROCESSLIST examples are taken from MySQL version 4.0.15; the content of the State column was changed in that version to be more meaningful.

 
MASTER> show processlist\G
*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost:32931
     db: NULL
Command: Binlog Dump
   Time: 94
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
 
SLAVE> show processlist\G
*************************** 1. row ***************************
     Id: 10
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 11
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 11
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 11
  State: Has read all relay log; waiting for the I/O slave thread to update it
   Info: NULL

(MASTER> and SLAVE> prompts were obtained with option --prompt of mysql, see section mysql, The Command-line Tool.) Here thread 2 is on the master. Thread 10 is the I/O thread on the slave. Thread 11 is the SQL thread on the slave; note that the value in the Time column can tell how late the slave is compared to the master (see section Replication FAQ).

With SHOW PROCESSLIST you can know what is happening on the master and on the slave as regards replication.

Here are the most common states you will see in the State column for the Binlog_dump thread (if you don't see this thread, then replication is not running, for sure):

Here are the most common states you will see in the State column for the I/O thread:

Here are the most common states you will see in the State column for the SQL thread:

Before MySQL 4.0.2, the I/O and SQL threads were one. The advantage brought by the two separate threads is that it makes the reading job and the execution job independant, thus the reading job is not slowed down by the execution job. As soon as the slave starts, even if it has not been running for a while, the I/O thread can quickly fetch all the binlogs, while the SQL thread lags far behind and may take hours to catch. If the slave stops, though it has not executed everything yet, at least it has fetched everything, so binlogs can be purged on the master, as a safe copy is locally stored on the slave for future use.

Relay logs are by default named as the hostname followed by -relay-bin plus a numeric extension. A `-relay-bin.index' file contains the list of all relay logs currently in use. By default these files are in the slave's data directory. Relay logs have the same format than binary logs, so they can be read with mysqlbinlog. A relay log is automatically deleted by the SQL thread as soon as it no longer needs it (that is, as soon as it has executed all its events). The user has no command to delete relay logs as the SQL thread does the job. However, from MySQL 4.0.14, FLUSH LOGS rotates relay logs, which will influence deletion by the SQL thread. A new relay log is created when the I/O thread starts, or when the size of the current relay log exceeds max_relay_log_size (or if this variable is 0 or the slave is older than MySQL 4.0.14, when the size exceeds max_binlog_size), or when FLUSH LOGS is issued (from version 4.0.14).

Replication also creates two small files in the data directory: these files are the disk images of the output of SHOW SLAVE STATUS (see section SQL Commands Related to Replication for a description of this command); but as disk images they survive slave's shutdown; this way at restart time the slave still knows his master and where the slave is in the master's binlogs, and where it is in its own relay logs.

4.10.4 How To Set Up Replication

Here is a quick description of how to set up complete replication on your current MySQL server. It assumes you want to replicate all your databases and have not configured replication before. You will need to shutdown your master server briefly to complete the steps outlined here.

While this method is the most straightforward way to set up a slave, it is not the only one. For example, if you already have a snapshot of the master, and the master already has server id set and binary logging enabled, you can set up a slave without shutting the master down or even blocking the updates. For more details, please see Replication FAQ.

If you want to be able to administrate a MySQL replication setup, we suggest that you read this entire chapter through and try all commands mentioned in SQL Commands Related to Replication. You should also familiarise yourself with replication startup options in `my.cnf' in Replication Options in `my.cnf'.

  1. Make sure you have a recent version of MySQL installed on the master and slave(s), and that these versions match with the above yes/no array.

    Please, do not report bugs until you have verified that the problem is present in the latest release.

  2. Set up a replication user on the master with the FILE (in MySQL versions older than 4.0.2) or REPLICATION SLAVE privilege in newer MySQL versions. You must also have given this user permission to connect from all the slaves. If the user is only doing replication (which is recommended), you don't need to grant any additional privileges.

    For example, to create a user named repl which can access your master from any host, you might use this command:

     
    mysql> GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY '<password>'; # master < 4.0.2
     
    mysql> GRANT REPLICATION SLAVE ON *.* TO repl@"%" IDENTIFIED BY '<password>'; # master >= 4.0.2

    If you plan to use the LOAD TABLE FROM MASTER or LOAD DATA FROM MASTER commands, you will also need to grant, on the master, to the above user, the REPLICATION CLIENT (or SUPER if the master is older than 4.0.13) privilege, the RELOAD privilege, and SELECT privileges on all tables you want to load. All master tables on which the user can't SELECT will be ignored by LOAD DATA FROM MASTER.

  3. If you are using MyISAM tables, flush all the tables and block write queries by executing FLUSH TABLES WITH READ LOCK command.
     
    mysql> FLUSH TABLES WITH READ LOCK;

    and then take a snapshot of the data on your master server.

    The easiest way to do this is to simply use an archiving program (tar on Unix, PowerArchiver, WinRAR, WinZIP or any similar software on Windows) to produce an archive of the databases in your master's data directory. Include all the databases you want to replicate.

     
    tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir

    If you want to replicate only a database called this_db, you can do just this:

     
    tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir/this_db

    You may not want to replicate the mysql database, then you can exclude it from the archive too. Into the archive you needn't copy the master's binary logs, error log, `master.info' / `relay-log.info' / relay logs (if the master is itself a slave of another machine). You can exclude all this from the archive.

    After or during the process of taking a snapshot, read the value of the current binary log name and the offset on the master:

     
    mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+-------------------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db              |
+---------------+----------+--------------+-------------------------------+
| mysql-bin.003 | 73       | test,bar     | foo,manual,sasha_likes_to_run |
+---------------+----------+--------------+-------------------------------+
1 row in set (0.06 sec)

    The File column shows the name of the log, while Position shows the offset. In the above example, the binary log value is mysql-bin.003 and the offset is 73. Record the values - you will need to use them later when you are setting up the slave.

    Once you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master:

     
    mysql> UNLOCK TABLES;

    If you are using InnoDB tables, ideally you should use the InnoDB Hot Backup tool that is available to those who purchase MySQL commercial licenses, support, or the backup tool itself. It will take a consistent snapshot without acquiring any locks on the master server, and record the log name and offset corresponding to the snapshot to be later used on the slave. More information about the tool is avalaible at http://www.innodb.com/hotbackup.html.

    Without the hot backup tool, the quickest way to take a snapshot of InnoDB tables is to shut the master server down and copy the InnoDB datafiles and logs, and the table definition files (.frm). To record the current log file name and offset, you should do the following before you shut down the server:

     
    mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

    And then record the log name and the offset from the output of SHOW MASTER STATUS as was shown earlier. Once you have recorded the log name and the offset, shut the server down without unlocking the tables to make sure it goes down with the snapshot corresponding to the current log file and offset:

     
    shell> mysqladmin -uroot shutdown

    An alternative for both MyISAM and InnoDB tables is taking an SQL dump of the master instead of a binary copy like above; for this you can use mysqldump --master-data on your master and later run this SQL dump into your slave. This is however slower than doing a binary copy.

    If the master has been previously running without log-bin enabled, the values of log name and position displayed by SHOW MASTER STATUS or mysqldump will be empty. In that case, record empty string (") for the log name, and 4 for the offset.

  4. Make sure that `my.cnf' on the master has log-bin if it is not there already and server-id=unique number in the [mysqld] section. If those options are not present, add them and restart the server. It is very important that the id of the slave is different from the id of the master. Think of server-id as something similar to the IP address - it uniquely identifies the server instance in the community of replication partners.
     
    [mysqld]
log-bin
server-id=1
  5. Stop the slave server(s). Add the following to `my.cnf' on the slave(s):
     
    server-id=<some unique number between 1 and 2^32-1 that is different from
 that of the master>

    replacing the values in <> with what is relevant to your system.

    server-id must be different for each server participating in replication. If you don't specify a server-id, it will be set to 1 if you have not defined master-host, else it will be set to 2. Note that in the case of server-id omission the master will refuse connections from all slaves, and the slave will refuse to connect to a master. Thus, omitting server-id is only good for backup with a binary log.

  6. Start the slave server(s). If it has been replicating previously, start the slave server with option skip-slave-start. You may want to start the slave server with option log-warnings, this way you will get more messages about network/connection problems for example.
  7. Copy the snapshot data into your data directory on your slave(s) (or execute the output of the above mysqldump into the mysql). Make sure that the privileges on the files and directories are correct. The user which MySQL runs as needs to be able to read and write to them, just as on the master.
  8. Execute the following command on the slave(s):
     
    mysql> CHANGE MASTER TO MASTER_HOST='<master host name>',
 MASTER_USER='<replication user name>',
 MASTER_PASSWORD='<replication password>',
 MASTER_LOG_FILE='<recorded log file name>',
 MASTER_LOG_POS=<recorded log offset>;

    replacing the values in <> with the actual values relevant to your system.

    The maximum string length for the above variables are:

    MASTER_HOST

    60

    MASTER_USER

    16

    MASTER_PASSWORD

    32

    MASTER_LOG_FILE

    255

  9. Start the slave threads:
     
    mysql> START SLAVE;

After you have done the above, the slave(s) should connect to the master and catch up on any updates which happened since the snapshot was taken.

If you have forgotten to set server-id for the slave you will get the following error in the error log file:

 
Warning: one should set server_id to a non-0 value if master_host is set.
The server will not act as a slave.

If you have forgotten to do this for the master, the slaves will not be able to connect to the master.

If a slave is not able to replicate for any reason, you will find error messages in the error log on the slave.

Once a slave is replicating, you will find a file called `master.info' and one called `relay-log.info' in the data directory. These two files are used by the slave to keep track of how much of the master's binary log it has processed. Do not remove or edit these files, unless you really know what you are doing. Even in that case, it is preferred that you use CHANGE MASTER TO command. NOTE: the content of `master.info' overrides some options specified on the command-line or in `my.cnf' (see section Replication Options in `my.cnf' for more details).

Now that you have a snapshot, you can use it to set up other slaves. To do so, follow the slave portion of the procedure described above. You do not need to take another snapshot of the master.

4.10.5 Replication Features and Known Problems

Here is an explanation of what is supported and what is not:

The following table is about problems in 3.23 that are fixed in 4.0:

4.10.6 Replication Options in `my.cnf'

On both master and slave you need to use the server-id option. This sets a unique replication id. You should pick a unique value in the range between 1 to 2^32-1 for each master and slave. Example: server-id=3

The options you can use on the MASTER are all described there: see The Binary Log.

The following table describes the options you can use on the SLAVE. It is recommended to read the following paragraph; these options can help you customize replication to suit your needs.

NOTE: replication handles the following options :

in a special way. If no `master.info' file exists (replication is used for the very first time or you have run RESET SLAVE and shutdown/restarted the slave server), the slave uses values specified on the command-line or in `my.cnf'. But if `master.info' exists, the slave IGNORES any values specified on the command-line or in `my.cnf', and uses instead the values it reads from `master.info'. For example, if you have

master-host=this_host

in your `my.cnf', are using replication, then want to replicate from another host, modifying the above line in `my.cnf' will have no effect. You must use CHANGE MASTER TO instead. This holds true for master-host, master-user, master-password, master-port, master-connect-retry. Therefore, you may decide to put no master-* options in `my.cnf' and instead use only CHANGE MASTER TO (see section SQL Commands Related to Replication).

Option

Description

log-slave-updates

Tells the slave to log the updates done by the slave SQL thread to the slave's binary log. Off by default. Of course, it requires that the slave be started with binary logging enabled (log-bin option). You have to use log-slave-updates to chain several slaves ; for example for the following setup to work

 
A -> B ->C

(C is a slave of B which is a slave of A) you need to start B with the log-slave-updates option.

log-warnings

Makes the slave print more messages about what it is doing. For example, it will warn you that it succeeded in reconnecting after a network/connection failure, and warn you about how each slave thread started.

max-relay-log-size=#

To rotate the relay log automatically. See section SHOW VARIABLES.

master-host=host

Master hostname or IP address for replication. If not set, the slave thread will not be started. Note that the setting of master-host will be ignored if there exists a valid `master.info' file. Probably a better name for this options would have been something like bootstrap-master-host, but it is too late to change now.

Example: master-host=db-master.mycompany.com

master-user=username

The username the slave thread will use for authentication when connecting to the master. The user must have the FILE privilege. If the master user is not set, user test is assumed. The value in `master.info' will take precedence if it can be read.

Example: master-user=scott

master-password=password

The password the slave thread will authenticate with when connecting to the master. If not set, an empty password is assumed.The value in `master.info' will take precedence if it can be read.

Example: master-password=tiger

master-port=portnumber

The port the master is listening on. If not set, the compiled setting of MYSQL_PORT is assumed. If you have not tinkered with configure options, this should be 3306. The value in `master.info' will take precedence if it can be read.

Example: master-port=3306

master-connect-retry=seconds

The number of seconds the slave thread will sleep before retrying to connect to the master in case the master goes down or the connection is lost. Default is 60. The value in `master.info' will take precedence if it can be read.

Example: master-connect-retry=60

master-ssl

Planned to enable the slave to connect to the master using SSL. Does nothing yet!

Example: master-ssl

master-ssl-key=filename

Master SSL keyfile name. Only applies if you have enabled master-ssl. Does nothing yet.

Example: master-ssl-key=SSL/master-key.pem

master-ssl-cert=filename

Master SSL certificate file name. Only applies if you have enabled master-ssl. Does nothing yet.

Example: master-ssl-cert=SSL/master-cert.pem

master-ssl-capath

Master SSL CA path. Only applies if you have enabled master-ssl. Does nothing yet.

master-ssl-cipher

Master SSL cipher. Only applies if you have enabled master-ssl. Does nothing yet.

master-info-file=filename

To give `master.info' another name and/or to put it in another directory than the data directory.

relay-log=filename

To specify the location and name that should be used for relay logs. You can use this to have hostname-independant relay log names, or if your relay logs tend to be big (and you don't want to decrease max_relay_log_size) and you need to put them on some area different from the data directory, or if you want to increase speed by balancing load between disks.

relay-log-index=filename

To specify the location and name that should be used for the relay logs index file.

relay-log-info-file=filename

To give `relay-log.info' another name and/or to put it in another directory than the data directory.

relay-log-purge=0|1

Available since MySQL 4.1.1. Disables/enables automatic purging of relay logs as soon as they are not needed anymore. This is a global variable which can be dynamically changed with SET GLOBAL RELAY_LOG_PURGE=0|1. The default value is 1.

relay-log-space-limit=#

To put an upper limit on the total size of all relay logs on the slave (a value of 0 means "unlimited"). This is useful if you have a small hard disk on your slave machine. When the limit is reached, the I/O thread pauses (does not read the master's binlog) until the SQL thread has catched up and deleted some now unused relay logs. Note that this limit is not absolute: there are cases where the SQL thread needs more events to be able to delete; in that case the I/O thread will overgo the limit until deletion becomes possible. Not doing so would cause a deadlock (which happens before MySQL 4.0.13). Users should not set relay-log-space-limit to less than twice the value of max-relay-log-size (or max-binlog-size if max-relay-log-size is 0) because in that case there are chances that when the I/O thread waits for free space because relay-log-space-limit is exceeded, the SQL thread has no relay log to purge and so cannot satisfy the I/O thread, forcing the I/O thread to temporarily ignore relay-log-space-limit.

replicate-do-table=db_name.table_name

Tells the slave thread to restrict replication to the specified table. To specify more than one table, use the directive multiple times, once for each table. This will work for cross-database updates, in contrast to replicate-do-db. Please read notes which follow this table.

Example: replicate-do-table=some_db.some_table

replicate-ignore-table=db_name.table_name

Tells the slave thread to not replicate any command that updates the specified table (even if any other tables may be update by the same command). To specify more than one table to ignore, use the directive multiple times, once for each table. This will work for cross-database updates, in contrast to replicate-ignore-db. Please read notes which follow this table.

Example: replicate-ignore-table=db_name.some_table

replicate-wild-do-table=db_name.table_name

Tells the slave thread to restrict replication to queries where any of the updated tables match the specified wildcard pattern. To specify more than one table, use the directive multiple times, once for each table. This will work for cross-database updates. Please read notes which follow this table.

Example: replicate-wild-do-table=foo%.bar% will replicate only updates that uses a table in any databases that start with foo and whose table names start with bar.

Note that if you do replicate-wild-do-table=foo%.% then the rule will be propagated to CREATE DATABASE and DROP DATABASE, that is, these two statements will be replicated if the database name matches the database pattern ('foo%' here) (this magic is triggered by '%' being the table pattern).

replicate-wild-ignore-table=db_name.table_name

Tells the slave thread to not replicate a query where any table matches the given wildcard pattern. To specify more than one table to ignore, use the directive multiple times, once for each table. This will work for cross-database updates. Please read notes which follow this table.

Example: replicate-wild-ignore-table=foo%.bar% will not do updates to tables in databases that start with foo and whose table names start with bar.

Note that if you do replicate-wild-ignore-table=foo%.% then the rule will be propagated to CREATE DATABASE and DROP DATABASE, that is, these two statements will not be replicated if the database name matches the database pattern ('foo%' here) (this magic is triggered by '%' being the table pattern).

replicate-do-db=database_name

Tells the slave to restrict replication to commands where the current database (that is, the one selected by USE) is database_name. To specify more than one database, use the directive multiple times, once for each database. Note that this will not replicate cross-database queries such as UPDATE some_db.some_table SET foo='bar' while having selected a different or no database. If you need cross database updates to work, make sure you have 3.23.28 or later, and use replicate-wild-do-table=db_name.%. Please read notes which follow this table.

Example: replicate-do-db=some_db.

Example of what does not work as you could expect it: if the slave is started with replicate-do-db=sales, and you do USE prices; UPDATE sales.january SET amount=amount+1000;, this query will not be replicated.

If you need cross database updates to work, use replicate-wild-do-table=db_name.% instead.

The main reason for this "just-check-the-current-database" behaviour is that it's hard from the command alone to know if a query should be replicated or not ; for example if you are using multi-table-delete or multi-table-update commands that go across multiple databases. It's also very fast to just check the current database.

replicate-ignore-db=database_name

Tells the slave to not replicate any command where the current database (that is, the one selected by USE) is database_name. To specify more than one database to ignore, use the directive multiple times, once for each database. You should not use this directive if you are using cross table updates and you don't want these update to be replicated. Please read notes which follow this table.

Example: replicate-ignore-db=some_db.

Example of what does not work as you could expect it: if the slave is started with replicate-ignore-db=sales, and you do USE prices; UPDATE sales.january SET amount=amount+1000;, this query will be replicated.

If you need cross database updates to work, use replicate-wild-ignore-table=db_name.% instead.

replicate-rewrite-db=from_name->to_name

Tells the slave to translate the current database (that is, the one selected by USE) to to_name if it was from_name on the master. Only statements involving tables may be affected (CREATE DATABASE, DROP DATABASE won't), and only if from_name was the current database on the master. This will not work for cross-database updates. Note that the translation is done before replicate-* rules are tested.

Example: replicate-rewrite-db=master_db_name->slave_db_name

report-host=host

Available after 4.0.0. Hostname or IP of the slave to be reported to the master during slave registration. Will appear in the output of SHOW SLAVE HOSTS. Leave unset if you do not want the slave to register itself with the master. Note that it is not sufficient for the master to simply read the IP of the slave off the socket once the slave connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the slave from the master or other hosts. For the moment this option has no real interest ; it is meant for failover replication which is not implemented yet.

Example: report-host=slave1.mycompany.com

report-port=portnumber

Available after 4.0.0. Port for connecting to slave reported to the master during slave registration. Set it only if the slave is listening on a non-default port or if you have a special tunnel from the master or other clients to the slave. If not sure, leave this option unset. For the moment this option has no real interest ; it is meant for failover replication which is not implemented yet.

skip-slave-start

Tells the slave server not to start the slave threads on server startup. The user can start them later with START SLAVE.

slave_compressed_protocol=#

If 1, then use compression on the slave/client protocol if both slave and master support this.

slave-load-tmpdir=filename

This option is by default equal to tmpdir. When the SQL slave replicates a LOAD DATA INFILE command, it extracts the to-be-loaded file from the relay log into temporary files, then loads these into the table. If the file loaded on the master was huge, the temporary files on the slave will be huge too; therefore you may wish/have to tell the slave to put the temporary files on some large disk different from tmpdir, using this option. In that case, you may also use the relay-log option, as relay logs will be huge too.

slave-net-timeout=#

Number of seconds to wait for more data from the master before aborting the read, considering the connection broken and retrying to connect, first time immediately, then every master-connect-retry seconds.

slave-skip-errors= [err_code1,err_code2,... | all]

Tells the slave SQL thread to continue replication when a query returns an error from the provided list. Normally, replication will discontinue when an error is encountered, giving the user a chance to resolve the inconsistency in the data manually. Do not use this option unless you fully understand why you are getting the errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, you should never get an abort with error. Indiscriminate use of this option will result in slaves being hopelessly out of sync with the master and you having no idea how the problem happened.

For error codes, you should use the numbers provided by the error message in your slave error log and in the output of SHOW SLAVE STATUS. A full list of error messages can be found in the source distribution in `Docs/mysqld_error.txt'.

You can (but should not) also use a very non-recommended value of all which will ignore all error messages and keep barging along regardless. Needless to say, if you use it, we make no promises regarding your data integrity. Please do not complain if your data on the slave is not anywhere close to what it is on the master in this case - you have been warned.

Example:

slave-skip-errors=1062,1053 or slave-skip-errors=all

Some of these options, like all replicate-* options, can only be set at the slave server's startup, not on-the-fly. We plan to fix this.

Here is the order of evaluation of the replicate-* rules, to decide if the query is going to be executed by the slave or ignored by it:

  1. Are there some replicate-do-db or replicate-ignore-db rules?
  2. Are there some replicate-*-table rules?
  3. Are there some replicate-do-table rules?
  4. Are there some replicate-ignore-table rules?
  5. Are there some replicate-wild-do-table rules?
  6. Are there some replicate-wild-ignore-table rules?
  7. No replicate-*-table rule was matched. Is there another table to test against these rules?

4.10.7 SQL Commands Related to Replication

Replication can be controlled through the SQL interface. Here is the summary of commands. Near each command you will find "(Slave)", meaning this command is issued on the slave, or "Master", meaning it is issued on the master.

4.10.7.1 START SLAVE (slave)  
4.10.7.2 STOP SLAVE (slave)  
4.10.7.3 SET SQL_LOG_BIN=0|1 (master)  
4.10.7.4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n (slave)  
4.10.7.5 RESET MASTER (master)  
4.10.7.6 RESET SLAVE (slave)  
4.10.7.7 LOAD TABLE tblname FROM MASTER (slave)  
4.10.7.8 LOAD DATA FROM MASTER (slave)  
4.10.7.9 CHANGE MASTER TO master_def_list (slave)  
4.10.7.10 MASTER_POS_WAIT() (slave)  
4.10.7.11 SHOW MASTER STATUS (master)  
4.10.7.12 SHOW SLAVE HOSTS (master)  
4.10.7.13 SHOW SLAVE STATUS (slave)  
4.10.7.14 SHOW MASTER LOGS (master)  
4.10.7.15 SHOW BINLOG EVENTS (master)  
4.10.7.16 PURGE MASTER LOGS (master)  

4.10.7.1 START SLAVE (slave)

Starts the slave threads. Was called SLAVE START in MySQL 3.23. As of MySQL 4.0.2, you can add IO_THREAD or SQL_THREAD options to the statement to start only the I/O thread or the SQL thread. The I/O thread reads queries from the master server and stores them in the relay log. The SQL thread reads the relay log and executes the queries. Note that if START SLAVE succeeds in starting the slave threads it will return without any error. But even in that case it might be that slave threads start and then later stop (because they don't manage to connect to the master or read his binlogs or any other problem). START SLAVE will not warn you about this, you have to check your slave's `.err' file for error messages generated by the slave threads, or check that these are running fine with SHOW SLAVE STATUS.

4.10.7.2 STOP SLAVE (slave)

Stops the slave threads. Was called SLAVE STOP in MySQL 3.23. Like SLAVE START, this statement may be used with IO_THREAD and SQL_THREAD options.

4.10.7.3 SET SQL_LOG_BIN=0|1 (master)

Disables/enables binary logging for the user's connection (SQL_LOG_BIN is a session variable) if the user has the SUPER privilege. Ignored otherwise.

4.10.7.4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n (slave)

Skip the next n events from the master. Only valid when the slave thread is not running, otherwise, gives an error. Useful for recovering from replication stops caused by a statement.

4.10.7.5 RESET MASTER (master)

Deletes all binary logs listed in the index file, resetting the binlog index file to be empty. Previously named FLUSH MASTER.

4.10.7.6 RESET SLAVE (slave)

Makes the slave forget its replication position in the master's binlogs, deletes the `master.info' and `relay-log.info' files, all relay logs, starts a new relay log. Note: relay logs which had not been totally executed by the SQL slave thread (which are likely to exist if you issued STOP SLAVE in an highly-loaded replication slave) are also deleted. Connection information (master host, master port, master user, master password) is immediately reset to the values specified in startup options (master-host etc) if there were some. Previously named FLUSH SLAVE.

4.10.7.7 LOAD TABLE tblname FROM MASTER (slave)

Downloads a copy of the table from master to the slave. Implemented mainly for debugging of LOAD DATA FROM MASTER. Requires that the replication user which is used to connect to the master has RELOAD and SUPER privileges on the master, and SELECT on the master table to load. On the slave's side, the user which issues LOAD TABLE FROM MASTER should have grants to drop and create the table. Please read the timeout notes in the description of LOAD DATA FROM MASTER below, they apply here too.

4.10.7.8 LOAD DATA FROM MASTER (slave)

Takes a snapshot of the master and copies it to the slave. Updates the values of MASTER_LOG_FILE and MASTER_LOG_POS so that the slave will start replicating from the correct position. Will honor table and database exclusion rules specified with replicate-* options. So far works only with MyISAM tables and acquires a global read lock on the master while taking the snapshot. In the future it is planned to make it work with InnoDB tables and to remove the need for global read lock using the non-blocking online backup feature.

If you are loading big tables, you may have to increase the values of net_read_timeout and net_write_timeout on both your master and slave ; see SHOW VARIABLES.

Note that LOAD DATA FROM MASTER does NOT copy any tables from the mysql database. This is to make it easy to have different users and privileges on the master and the slave.

Requires that the replication user which is used to connect to the master has RELOAD and SUPER privileges on the master, SELECT privileges on all master's tables you want to load. All master's tables on which the user has no SELECT privilege will be ignored by LOAD DATA FROM MASTER; this is because the master will hide them to the user: LOAD DATA FROM MASTER calls SHOW DATABASES to know the master databases to load, but SHOW DATABASES returns only databases on which the user has some privilege, see section Retrieving information about Database, Tables, Columns, and Indexes. On the slave's side, the user which issues LOAD DATA FROM MASTER should have grants to drop and create the involved databases and tables.

4.10.7.9 CHANGE MASTER TO master_def_list (slave)

CHANGE MASTER is a "brutal" command, it is recommended to read this whole description before using it in production.

Changes the master parameters (connection and binlog information) to the values specified in master_def_list. master_def_list is a comma-separated list of master_def where master_def is one of the following: MASTER_HOST, MASTER_USER, MASTER_PASSWORD, MASTER_PORT, MASTER_CONNECT_RETRY, MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE, RELAY_LOG_POS (these last two only starting from MySQL 4.0). For example:

 
CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;
 
CHANGE MASTER TO
  RELAY_LOG_FILE='slave-relay-bin.006',
  RELAY_LOG_POS=4025;

You only need to specify the values that need to be changed. The values that you omit will stay the same with the exception of when you specify (not necessarily change) the host or port. In that case, the slave will assume that the master is different from before. Therefore, the old values of master's binlog name and position are considered no longer applicable, thus if you didn't specify MASTER_LOG_FILE and MASTER_LOG_POS in the command, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 will silently be appended to it.

MASTER_LOG_FILE and MASTER_LOG_POS are the coordinates from which the I/O slave thread will start reading from the master, next time this thread is started. As CHANGE MASTER deletes relay logs (see below), they are also the coordinates from which the SQL slave thread will start executing next time it is started.

CHANGE MASTER deletes all relay logs (and starts a new one), unless you specified RELAY_LOG_FILE or RELAY_LOG_POS (in that case relay logs will be kept; since MySQL 4.1.1 the RELAY_LOG_PURGE global variable will silently be set to 0). CHANGE MASTER updates `master.info' and `relay-log.info'.

Note: if, just before you issue CHANGE MASTER, the SQL slave thread is late by one or more queries compared to the I/O thread (a very common case when replication is running in high-load environments), then as CHANGE MASTER deletes relay logs containing these non-executed queries, and so replication then restarts from the coordinates of the I/O thread, the SQL thread will have "lept" over the non-executed queries. Therefore, unless these queries were not important, you should, before issuing CHANGE MASTER, either:

If you don't take care of this issue, even a simple STOP SLAVE; CHANGE MASTER TO MASTER_USER='repl'; START SLAVE; run in the middle of an highly-loaded replication could break this replication and spoil the slave's data.

CHANGE MASTER is useful for setting up a slave when you have the snapshot of the master and have recorded the log and the offset on the master that the snapshot corresponds to. You can run CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master', MASTER_LOG_POS=log_offset_on_master on the slave after restoring the snapshot.

The first example above (CHANGE MASTER TO MASTER_HOST='master2.mycompany.com' etc) changes the master and master's binlog coordinates. This is when you want the slave to replicate the master. The second example, less frequently used, is when the slave has relay logs which, for some reason, you want the slave to execute again; to do this the master needn't be reachable, you just have to do CHANGE MASTER TO and start the SQL thread (START SLAVE SQL_THREAD). You can even use this out of a replication setup, on a standalone, slave-of-nobody server, to recover after a crash. Suppose your server has crashed and you have restored a backup. You want to replay the server's own binlogs (not relay logs, but regular binary logs), supposedly named `myhost-bin.*'. First make a backup copy of these binlogs in some safe place, in case you don't exactly follow the procedure below and accidentally have the server purge the binlogs. If using MySQL 4.1.1 or newer, do SET GLOBAL RELAY_LOG_PURGE=0 for additional safety. Then start the server without log-bin, with a new (different from before) server id, with relay-log=myhost-bin (to make the server believe that these regular binlogs are relay logs) and skip-slave-start, then issue

 
CHANGE MASTER TO RELAY_LOG_FILE='myhost-bin.153',RELAY_LOG_POS=410, MASTER_HOST='some_dummy_string';
START SLAVE SQL_THREAD;

Then the server will read and execute its own binlogs, thus achieving crash recovery. Once the recovery is finished, run STOP SLAVE, shutdown the server, delete `master.info' and `relay-log.info', and restart the server with its original options. For the moment, specifying MASTER_HOST (even with a dummy value) is compulsory to make the server think he is a slave, and giving the server a new, different from before, server id is also compulsory otherwise the server will see events with its id and think it is in a circular replication setup and skip the events, which is unwanted. In the future we plan to add options to get rid of these small constraints.

4.10.7.10 MASTER_POS_WAIT() (slave)

This is not a command but a function, used to ensure that the slave has reached (read and executed up to) a given position in the master's binlog; see Miscellaneous Functions for a full description.

4.10.7.11 SHOW MASTER STATUS (master)

Provides status information on the binlog of the master.

4.10.7.12 SHOW SLAVE HOSTS (master)

Gives a listing of slaves currently registered with the master.

4.10.7.13 SHOW SLAVE STATUS (slave)

Provides status information on essential parameters of the slave threads (Slave). If you type it in the mysql client, you can put a \G instead of a semicolon at the end, to get a vertical, more readable layout:

 
SLAVE> show slave status\G
*************************** 1. row ***************************
          Master_Host: localhost
          Master_User: root
          Master_Port: 3306
        Connect_retry: 3
      Master_Log_File: gbichot-bin.005
  Read_Master_Log_Pos: 79
       Relay_Log_File: gbichot-relay-bin.005
        Relay_Log_Pos: 548
Relay_Master_Log_File: gbichot-bin.005
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_do_db:
  Replicate_ignore_db:
           Last_errno: 0
           Last_error:
         Skip_counter: 0
  Exec_master_log_pos: 79
      Relay_log_space: 552
1 row in set (0.00 sec)

4.10.7.14 SHOW MASTER LOGS (master)

Lists the binary logs on the master. You should use this command prior to PURGE MASTER LOGS to find out how far you should go.

4.10.7.15 SHOW BINLOG EVENTS (master)

SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ]

Shows the events in the binary log. If you do not specify 'logname', the first binary log will be displayed.

4.10.7.16 PURGE MASTER LOGS (master)

PURGE MASTER|BINARY LOGS TO 'logname' ; PURGE MASTER|BINARY LOGS BEFORE 'date'

The BEFORE variant is available in MySQL 4.1; its date argument can be in format 'YYYY-MM-DD HH:MI:SS'. MASTER and BINARY are here synonyms. Deletes all the binary logs that are listed in the log index as being strictly prior to the specified log or date, and removes them from the log index, so that the given log now becomes the first. Example:

 
PURGE MASTER LOGS TO 'mysql-bin.010' ;
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26' ;

This command will do nothing and fail with an error if you have an active slave that is currently reading one of the logs you are trying to delete. However, if you have a dormant slave, and happen to purge one of the logs it wants to read, the slave will be unable to replicate once it comes up. The command is safe to run while slaves are replicating - you do not need to stop them.

You must first check all the slaves with SHOW SLAVE STATUS to see which log they are on, then do a listing of the logs on the master with SHOW MASTER LOGS, find the earliest log among all the slaves (if all the slaves are up to date, this will be the last log on the list), backup all the logs you are about to delete (optional) and purge up to the target log.

4.10.8 Replication FAQ

Q: How do I configure a slave if the master is already running and I do not want to stop it?

A: There are several options. If you have taken a backup of the master at some point and recorded the binlog name and offset ( from the output of SHOW MASTER STATUS ) corresponding to the snapshot, do the following:

If you do not have a backup of the master already, here is a quick way to do it consistently:

An alternative is taking an SQL dump of the master instead of a binary copy like above; for this you can use mysqldump --master-data on your master and later run this SQL dump into your slave. This is however slower than doing a binary copy.

No matter which of the two ways you used, afterwards follow the instructions for the case when you have a snapshot and have recorded the log name and offset. You can use the same snapshot to set up several slaves. As long as the binary logs of the master are left intact, you can wait as long as several days or in some cases maybe a month to set up a slave once you have the snapshot of the master. In theory the waiting gap can be infinite. The two practical limitations is the diskspace of the master getting filled with old logs, and the amount of time it will take the slave to catch up.

You can also use LOAD DATA FROM MASTER. This is a convenient command that will take a snapshot, restore it to the slave, and adjust the log name and offset on the slave all at once. In the future, LOAD DATA FROM MASTER will be the recommended way to set up a slave. Be warned, howerver, that the read lock may be held for a long time if you use this command. It is not yet implemented as efficiently as we would like to have it. If you have large tables, the preferred method at this time is still with a local tar snapshot after executing FLUSH TABLES WITH READ LOCK.

Q: Does the slave need to be connected to the master all the time?

A: No, it does not. You can have the slave go down or stay disconnected for hours or even days, then reconnect, catch up on the updates, and then disconnect or go down for a while again. So you can, for example, use master-slave setup over a dial-up link that is up only for short periods of time. The implications of that are that at any given time the slave is not guaranteed to be in sync with the master unless you take some special measures. In the future, we will have the option to block the master until at least one slave is in sync.

Q: How do I know how late the slave is compared to the master? In other words, how do I know the date of the last query replicated by the slave?

A: This is possible only if the SQL slave thread exists (that is, if it shows up in SHOW PROCESSLIST, see section Replication Implementation Details) (in MySQL 3.23: if the slave thread exists, that is, shows up in SHOW PROCESSLIST), and if it has executed at least one event from the master. Indeed, when the SQL slave thread executes an event read from the master, this thread modifies its own time to the event's timestamp (this is why TIMESTAMP is well replicated). So in the Time column in the output of SHOW PROCESSLIST, the number of seconds displayed for the SQL slave thread is the number of seconds between the timestamp of the last replicated event and the real time of the slave machine. You can use this to determine the date of the last replicated event. Note that if your slave has been disconnected from the master for one hour, then reconnects, you may immediately see Time values like 3600 for the SQL slave thread in SHOW PROCESSLIST... This would be because the slave is executing queries that are one hour old.

Q: How do I force the master to block updates until the slave catches up?

A: Execute the following commands:

Q: What issues should I be aware of when setting up two-way replication?

A: MySQL replication currently does not support any locking protocol between master and slave to guarantee the atomicity of a distributed (cross-server) update. In other words, it is possible for client A to make an update to co-master 1, and in the meantime, before it propagates to co-master 2, client B could make an update to co-master 2 that will make the update of client A work differently than it did on co-master 1. Thus when the update of client A will make it to co-master 2, it will produce tables that will be different from what you have on co-master 1, even after all the updates from co-master 2 have also propagated. So you should not co-chain two servers in a two-way replication relationship, unless you are sure that you updates can safely happen in any order, or unless you take care of mis-ordered updates somehow in the client code.

You must also realise that two-way replication actually does not improve performance very much, if at all, as far as updates are concerned. Both servers need to do the same amount of updates each, as you would have one server do. The only difference is that there will be a little less lock contention, because the updates originating on another server will be serialised in one slave thread. This benefit, though, might be offset by network delays.

Q: How can I use replication to improve performance of my system?

A: You should set up one server as the master, and direct all writes to it, and configure as many slaves as you have the money and rackspace for, distributing the reads among the master and the slaves. You can also start the slaves with --skip-bdb, --low-priority-updates and --delay-key-write=ALL to get speed improvements for the slave. In this case the slave will use non-transactional MyISAM tables instead of BDB tables to get more speed.

Q: What should I do to prepare my client code to use performance-enhancing replication?

A: If the part of your code that is responsible for database access has been properly abstracted/modularised, converting it to run with the replicated setup should be very smooth and easy - just change the implementation of your database access to read from some slave or the master, and to always write to the master. If your code does not have this level of abstraction, setting up a replicated system will give you an opportunity/motivation to it clean up. You should start by creating a wrapper library /module with the following functions:

safe_ means that the function will take care of handling all the error conditions.

You should then convert your client code to use the wrapper library. It may be a painful and scary process at first, but it will pay off in the long run. All applications that follow the above pattern will be able to take advantage of one-master/many slaves solution. The code will be a lot easier to maintain, and adding troubleshooting options will be trivial. You will just need to modify one or two functions, for example, to log how long each query took, or which query, among your many thousands, gave you an error. If you have written a lot of code already, you may want to automate the conversion task by using Monty's replace utility, which comes with the standard distribution of MySQL, or just write your own Perl script. Hopefully, your code follows some recognisable pattern. If not, then you are probably better off rewriting it anyway, or at least going through and manually beating it into a pattern.

Note that, of course, you can use different names for the functions. What is important is having unified interface for connecting for reads, connecting for writes, doing a read, and doing a write.

Q: When and how much can MySQL replication improve the performance of my system?

A: MySQL replication is most beneficial for a system with frequent reads and not so frequent writes. In theory, by using a one master/many slaves setup you can scale by adding more slaves until you either run out of network bandwidth, or your update load grows to the point that the master cannot handle it.

In order to determine how many slaves you can get before the added benefits begin to level out, and how much you can improve performance of your site, you need to know your query patterns, and empirically (by benchmarking) determine the relationship between the throughput on reads (reads per second, or max_reads) and on writes max_writes) on a typical master and a typical slave. The example here will show you a rather simplified calculation of what you can get with replication for our imagined system.

Let's say our system load consists of 10% writes and 90% reads, and we have determined that max_reads = 1200 - 2 * max_writes, or in other words, our system can do 1200 reads per second with no writes, our average write is twice as slow as average read, and the relationship is linear. Let us suppose that our master and slave are of the same capacity, and we have N slaves and 1 master. Then we have for each server (master or slave):

reads = 1200 - 2 * writes (from bencmarks)

reads = 9* writes / (N + 1) (reads split, but writes go to all servers)

9*writes/(N+1) + 2 * writes = 1200

writes = 1200/(2 + 9/(N+1)

So if N = 0, which means we have no replication, our system can handle 1200/11, about 109 writes per second (which means we will have 9 times as many reads due to the nature of our application).

If N = 1, we can get up to 184 writes per second.

If N = 8, we get up to 400.

If N = 17, 480 writes.

Eventually as N approaches infinity (and our budget negative infinity), we can get very close to 600 writes per second, increasing system throughput about 5.5 times. However, with only 8 servers, we increased it almost 4 times already.

Note that our computations assumed infinite network bandwidth, and neglected several other factors that could turn out to be significant on your system. In many cases, you may not be able to make a computation similar to the one above that will accurately predict what will happen on your system if you add N replication slaves. However, answering the following questions should help you decided whether and how much, if at all, the replication will improve the performance of your system:

Q: How can I use replication to provide redundancy/high availability?

A: With the currently available features, you would have to set up a master and a slave (or several slaves), and write a script that will monitor the master to see if it is up, and instruct your applications and the slaves of the master change in case of failure. Some suggestions:

We are currently working on integrating an automatic master election system into MySQL, but until it is ready, you will have to create your own monitoring tools.

4.10.9 Troubleshooting Replication

If you have followed the instructions, and your replication setup is not working, first check the following:

When you have determined that there is no user error involved, and replication still either does not work at all or is unstable, it is time to send us a bug report. We need to get as much information as possible from you to be able to track down the bug. Please do spend some time and effort preparing a good bug report.

If you have a repeatable way to demonstrate the bug, use mysqlbug to prepare a bug report and enter it into our bugs database at http://bugs.mysql.com/. If you have a phantom - a problem that does occur but you cannot duplicate "at will" - fortunately this rarely happens:

Once you have collected the evidence on the phantom problem, try hard to isolate it into a separate test case first. Then enter the problem into our bugs database at http://bugs.mysql.com/ with as much information as possible.

[ << ] [ >> ]           [Top] [Contents] [Index] [ ? ]


Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:45