O'Reilly logo

MySQL Reference Manual by Kaj Arno, David Axmark, Michael Widenius

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

MySQL Server-Side Scripts and Utilities

Overview of the Server-Side Scripts and Utilities

All MySQL clients that communicate with the server using the mysqlclient library use the following environment variables:




The default socket; used for connections to localhost


The default TCP/IP port


The default password


Debug-trace options when debugging


The directory where temporary tables/files are created

Use of MYSQL_PWD is insecure. See Section 4.2.8.

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 Appendix E.

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. See Section 4.1.2.

The following list briefly describes the MySQL programs:


Utility to describe, check, optimise, and repair MySQL tables. Because myisamchk has many functions, it is described in its own chapter. See Section 4.4.6.


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.


A shell script that converts mSQL programs to MySQL. It doesn’t handle all cases, but it gives a good start when converting.


A script that checks the access privileges for a host, user, and database combination.


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 4.8.3.


The MySQL bug report script. This script should always be used when filing a bug report to the MySQL list.


The SQL daemon. This should always be running.


Dumps a MySQL database into a file as SQL statements or as tab-separated text files. Enhanced freeware originally by Igor Romanenko. See Section 4.8.5.


Imports text files into their respective tables using LOAD DATA INFILE. See Section 4.8.7.


Displays information about databases, tables, columns, and indexes.


Creates the MySQL grant tables with default privileges. This is usually executed only once, when first installing MySQL on a system.


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

safe_mysqld, the Wrapper Around mysqld

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

If you don’t use --mysqld=#, or --mysqld-version=# safe_mysqld will use an executable named mysqld-max if it exists. If not, safe_mysqld 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 safe_mysqld script, but instead put the options to safe_mysqld in the [safe_mysqld] section in the my.cnf file. safe_mysqld will read all options from the [mysqld], [server], and [safe_mysqld] sections from the option files. See Section 4.1.2.

Note that all options on the command-line to safe_mysqld are passed to mysqld. If you wants to use any options in safe_mysqld that mysqld doesn’t support, you must specify these in the option file.

Most of the options to safe_mysqld are the same as the options to mysqld. See Section 4.1.1.

safe_mysqld 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 , --ledir=path

Path to mysqld

--log=path , --mysqld=mysqld-version

Name of the mysqld version in the ledir directory you want to start.


Similar to --mysqld=, but here you only give the suffix for mysqld. For example, if you use --mysqld-version=max, safe_mysqld will start the ledir/mysqld-max version. If the argument to --mysqld-version is empty, ledir/mysqld will be used.

--no-defaults , --open-files-limit=#

Number of files mysqld should be able to open. Passed to ulimit -n. Note that you need to start safe_mysqld 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.


The safe_mysqld 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. safe_mysqld expects one of these conditions to be true:

  • The server and databases can be found relative to the directory from which safe_mysqld is invoked. safe_mysqld looks under its working directory for bin and data directories (for binary distributions) or for libexec and var directories (for source distributions). This condition should be met if you execute safe_mysqld from your MySQL installation directory (for example, /usr/local/mysql for a binary distribution).

  • If the server and databases cannot be found relative to the working directory, safe_mysqld attempts to locate them by absolute pathnames. Typical locations are /usr/local/libexec and /usr/local/var. The actual locations are determined when the distribution was built from which safe_mysqld comes. They should be correct if MySQL was installed in a standard location.

Because safe_mysqld 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 safe_mysqld from the MySQL installation directory:

shell> cd mysql_installation_directory
shell> bin/safe_mysqld &

If safe_mysqld 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 safe_mysqld will be overwritten, so you should make a copy of your edited version that you can reinstall.

mysqld_multi, Program for Managing Multiple MySQL servers

mysqld_multi is meant for managing several mysqld processes running in different Unix sockets and TCP/IP ports.

The program will search for group(s) named [mysqld#] from my.cnf (or the given --config-file=...), where # can be any positive number starting from 1. These groups should be the same as the usual [mysqld] group (e.g., options to mysqld; see the MySQL manual for detailed information about this group), but with those port, socket, etc., options that are wanted for each separate mysqld process. The number in the group name has another function; it can be used for starting, stopping, or reporting some specific mysqld servers with this program. See the following usage and option for more details.

Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
or     mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]

The GNR in the preceding example means the group number. You can start, stop, or report any GNR, or several of them at the same time. (See example.) The GNRs list can be comma-separated or combined with a dash, of which the latter means that all the GNRs between GNR1-GNR2 will be affected. Without the GNR argument all the found groups will be either started, stopped, or reported. Note that you must not have any whitespace in the GNR list. Anything after a whitespace is ignored.

mysqld_multi supports the following options:


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.


Give an example of a config file.


Print this help and exit.


Log file. Full path to and the name for the log file. Note: If the file exists, everything will be appended.


mysqladmin binary to be used for a server shutdown.


mysqld binary to be used. Note that you can give safe_mysqld to this option also. The options are passed to mysqld. Just make sure you have mysqld in your environment variable PATH or fix safe_mysqld.


Print to stdout instead of the log file. By default, the log file is turned on.


Password for user for mysqladmin.


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 connecting is done via the Unix socket.


MySQL user for mysqladmin.


Print the version number and exit.

Some notes about mysqld_multi:

  • Make sure that the MySQL user who is stopping the mysqld services (e.g., using the mysqladmin) has the same password and username for all the data directories accessed (to the ‘mysql’ database) And make sure that the user has the ‘Shutdown_priv’ privilege! If you have many data directories and many different ‘mysql’ databases with different passwords for the MySQL ‘root’ user, you may want to create a common ‘multi_admin’ user for each using the same password. Here’s how to do it:

    shell> mysql -u root -S /tmp/mysql.sock -proot_password -e
    "GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'multipass'"
    Section 4.2.6.

    You will have to do this for each mysqld running in each data directory that you have (just change the socket, -S=...).

  • pid-file is very important if you are using safe_mysqld to start mysqld (e.g., --mysqld=safe_mysqld). Every mysqld should have its own pid-file. The advantage using safe_mysqld instead of mysqld directly is that safe_mysqld ‘guards’ every mysqld process and will restart it, if a mysqld process fails due to signal kill -9, or similar failure (like segmentation fault, which MySQL should never do, of course). Please note that the safe_mysqld script may require that you start it from a certain place. This means that you may have to cd to a certain directory before you start the mysqld_multi. If you have problems starting, please see the safe_mysqld script. Check especially the lines:

    MY_PWD=`pwd` Check if we are starting this relative (for the binary
    release) if test -d /data/mysql -a -f ./share/mysql/english/errmsg.sys
    -a -x ./bin/mysqld
    Section 4.7.2.

    This test should be successful. If it isn’t, you may encounter the following problems:

  • Beware of the dangers starting multiple mysqlds in the same data directory. Use separate data directories, unless you know what you are doing!

  • The socket file and the TCP/IP port must be different for every mysqld.

  • The first and fifth mysqld group were intentionally left out from the example. You may have ‘gaps’ in the config file. This gives you more flexibility. The order in which the mysqlds are started or stopped depends on the order in which they appear in the config file.

  • When you want to refer to a certain group using GNR with this program, just use the number in the end of the group name ([mysqld# <== ).

  • You may want to use option '--user’ for mysqld, but in order to do this you need to be root when you start the mysqld_multi script. Having the option in the config file doesn’t matter; you will just get a warning, if you are not the superuser and the mysqlds are started under your Unix account. Important: Make sure that the pid-file and the data directory are read+write (+execute for the latter one) accessible for that Unix user, who the specific mysqld process is started as. Do not use the Unix root account for this, unless you know what you are doing!

  • Most important: Make sure that you understand the meanings of the options that are passed to the mysqlds and why one would want to have separate mysqld processes. Starting multiple mysqlds in one data directory will not give you extra performance in a threaded system!

See Section 4.1.4.

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     = /usr/local/bin/safe_mysqld
mysqladmin = /usr/local/bin/mysqladmin
user       = multi_admin
password   = multipass

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

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

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

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 4.1.2.

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 data file 40%-70%.

MySQL uses memory mapping (mmap( )) on compressed tables and falls back to normal read/write file usage if mmap( ) doesn’t work.

There are currently two limitations with myisampack:

  • After packing, the table is read-only.

  • myisampack can also pack BLOB or TEXT columns. The older pack_isam could not do this.

Fixing these limitations is on our TODO list but with low priority.

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 the 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-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 data file 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

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 data file 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:


The number of columns for which no extra packing is used.


The number of columns containing values that are only spaces; these will occupy 1 bit.


The number of columns containing values that are only binary 0s; these will occupy 1 bit.


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).


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.


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.


The column had only a small number of different values, which were converted to an ENUM before Huffman compression.


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:


The field type may contain the following descriptors:


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.


The column was converted to an ENUM.


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.


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.

mysqld-max, an Extended mysqld Server

mysqld-max is the MySQL server (mysqld) configured with the following configure options:




Add a suffix to the mysqld version string.


Support for InnoDB tables.


Support for Berkeley DB (BDB) tables.


Symbolic links support for Windows.

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

The Windows MySQL binary distributions include both the standard mysqld.exe binary and the mysqld-max.exe binary. See http://www.mysql.com/downloads/mysql-3.23.html. See also Section 2.1.2.

Note that as InnoDB and Berkeley DB are not available for all platforms, some of the Max binaries may not have support for both of these. You can check which table types are supported by doing the following query:

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

The meanings of the values are:




The option is activated and usable.


MySQL is not compiled with support for this option.


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 why the option is disabled.

Note: To be able to create InnoDB tables you must edit your startup options to include at least the innodb_data_file_path option. See Section 7.5.2.

To get better performance for BDB tables, you should add some configuration options for these too. See Section 7.6.3.

safe_mysqld will automatically try to start any mysqld binary with the -max prefix. 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 4.7.2.

The mysqld-max RPM uses the aforementioned safe_mysqld feature. It just installs the mysqld-max executable and safe_mysqld will automatically use this executable when safe_mysqld is restarted.

The following table shows which table types our standard MySQL-Max binaries include:




AIX 4.3



HP-UX 11.0


















Caldera (SCO) OSR5









With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required