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. Section 4.1.2, “my.cnf Option Files ”.
The following list briefly describes the server-side MySQL programs:
Utility to describe, check, optimise, and repair MySQL tables. Because myisamchk has many functions, it is described in its own chapter. Chapter 4, Database Administration .
Makes a binary release of a compiled MySQL. This could be sent by FTP to /pub/mysql/Incoming on support.mysql.com for the convenience of other MySQL users.
The MySQL bug 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.
Creates the MySQL grant tables with default privileges. This is usually executed only once, when first installing MySQL on a system.
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.) Section 4.1.2, “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. Section 4.1.1, “mysqld Command-line Options ”.
mysqld_safe supports the following options:
Size of the core file mysqld should be able to create. Passed to ulimit -c.
Write the error log to the above file. Section 4.9.1, “The Error Log ”.
Path to mysqld
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, mysqld_safe will start the ledir/mysqld-max version. If the argument to -mysqld-version is empty, ledir/mysqld will be used.
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!
Set the timezone (the TZ) variable to the value of this parameter.
The mysqld_safe script is written so that it normally is able to start a server that was installed from either a source or a binary version of MySQL, even if these install the server in slightly different locations. mysqld_safe expects one of these conditions to be true:
The server and databases can be found relative to the directory from which mysqld_safe is invoked. mysqld_safe 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 mysqld_safe from your MySQL installation directory (for example, /usr/local/mysql for a binary distribution).
If the server and databases cannot be found relative to the working directory, mysqld_safe attempts to locate them by absolute pathnames. Typical locations are /usr/local/libexec and /usr/local/var. The actual locations are determined when the distribution was built from which mysqld_safe comes. They should be correct if MySQL was installed in a standard location.
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.
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, Section 2.4.3, “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:
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.
Log file. Full path to and the name for the log file. Note: If the file exists, everything will be appended.
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.
Print to stdout instead of the log file. By default the log file is turned on.
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.
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 program) have the same password and username for all the data directories accessed (to the mysql database) And make sure that the user has the SHUTDOWN 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 (see below). Example 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, “How the Privilege System Works ”. You will have to do the above 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 mysqld_safe to start mysqld (for example, -mysqld=mysqld_safe) Every mysqld should have its own pid-file. The advantage using mysqld_safe instead of mysqld directly here is, that mysqld_safe "guards" every mysqld process and will restart it, if a mysqld process terminates due to a signal sent using kill -9, or for other reasons such as a segmentation fault (which MySQL should never do, of course ;). Please note that the mysqld_safe 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 mysqld_safe 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, “mysqld_safe, The Wrapper Around mysqld”. The above test should be successful, or you may encounter 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. For example, the GNR for a group named [mysqld17] is 17.
You may want to use option -user for mysqld, but in order to do this you need to run the mysqld_multi script as the Unix root user. Having the option in the 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!
Section 4.1.3, “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
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:
After packing, the table is read-only. This is generally intended (such as when accessing packed tables on a CD). Also allowing writes to a packed table is on our TODO list but with low priority.
myisampack can also pack BLOB or TEXT columns. The older pack_isam (for ISAM tables) can not do this.
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:
Make a backup of the table as tbl_name.OLD.
Output debug log. The debug_options string often is 'd:t:o,filename'.
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.
Display a help message and exit.
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.).
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.)
Silent mode. Write output only when errors occur.
Don't actually pack table, just test packing it.
Use the named directory as the location in which to write the temporary table.
Verbose mode. Write information about progress and packing result.
Display version information and exit.
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:
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 0's; 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.
The initial number of Huffman trees.
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.
Don't store endspace.
Don't store endspace and don't do end space compression for all values.
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.
Don't store zeros.
0 values are stored in 1 bit.
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 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. Section 2.1.1, “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. Section 7.5.2, “InnoDB in MySQL Version 3.23 ”.
To get better performance for BDB tables, you should add some configuration options for these, too. Section 7.6.3, “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. Section 4.7.2, “mysqld_safe, The Wrapper Around mysqld”.
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.