List the options on the command line following the program name. This is most common for options that apply to a specific invocation of the program.

List the options in an option file that the program reads when it starts. This is common for options that you want the program to use each time it runs.

List the options in environment variables (see Section 4.2.3, “Setting Environment Variables”). This method is useful for options that you want to apply each time the program runs. In practice, option files are used more commonly for this purpose, but Section 5.8.2, “Running Multiple Servers on Unix”, discusses one situation in which environment variables can be very helpful. It describes a handy technique that uses such variables to specify the TCP/IP port number and Unix socket file for the server and for client programs.

On Linux, the MySQL-Max RPM relies on this mysqld_safe behavior. The RPM installs an executable named mysqld-max, which causes mysqld_safe to automatically use that executable rather than mysqld from that point on.

If you install a MySQL-Max distribution that includes a server named mysqld-max, and then upgrade later to a non-Max version of MySQL, mysqld_safe will still attempt to run the old mysqld-max server. If you perform such an upgrade, you should manually remove the old mysqld-max server to ensure that mysqld_safe runs the new mysqld server.

--autoclose

(NetWare only) On NetWare, mysqld_safe provides a screen presence. When you unload (shut down) the mysqld_safe NLM, the screen does not by default go away. Instead, it prompts for user input:

*<NLM has terminated; Press any key to close the screen>*

If you want NetWare to close the screen automatically instead, use the --autoclose option to mysqld_safe.

--basedir=path

The path to the MySQL installation directory.

--core-file-size=size

The size of the core file that mysqld should be able to create. The option value is passed to ulimit -c.

--datadir=path

The path to the data directory.

--defaults-extra-file=path

The name of an option file to be read in addition to the usual option files. This must be the first option on the command line if it is used.

--defaults-file=file_name

The name of an option file to be read instead of the usual option files. This must be the first option on the command line if it is used.

--err-log=file_name

The old form of the --log-error option, to be used before MySQL 4.0.

--ledir=path

If mysqld_safe cannot find the server, use this option to indicate the pathname to the directory where the server is located.

--log-error=file_name

Write the error log to the given file. See Section 5.3.1, “The Error Log”.

--mysqld=prog_name

The name of the server program (in the ledir directory) that you want to start. This option is needed if you use the MySQL binary distribution but have the data directory outside of the binary distribution. If mysqld_safe cannot find the server, use the --ledir option to indicate the pathname to the directory where the server is located.

--mysqld-version=suffix

This option is similar to the --mysqld option, but you specify only the suffix for the server program name. The basename is assumed to be mysqld. For example, if you use --mysqld-version=max, mysqld_safe starts the mysqld-max program in the ledir directory. If the argument to --mysqld-version is empty, mysqld_safe uses mysqld in the ledir directory.

--nice=priority

Use the nice program to set the server's scheduling priority to the given value. This option was added in MySQL 4.0.14.

--no-defaults

Do not read any option files. This must be the first option on the command line if it is used.

--open-files-limit=count

The number of files that mysqld should be able to open. The option value is passed to ulimit -n. Note that you need to start mysqld_safe as root for this to work properly.

--pid-file=file_name

The pathname of the process ID file.

--port=port_num

The port number that the server should use when listening for TCP/IP connections. The port number must be 1024 or higher unless the server is started by the root system user.

--skip-kill-mysqld

Do not try to kill stray mysqld processes at startup. This option works only on Linux.

--socket=path

The Unix socket file that the server should use when listening for local connections.

--timezone=timezone

Set the TZ time zone environment variable to the given option value. Consult your operating system documentation for legal time zone specification formats.

--user={user_name|user_id}

Run the mysqld server as the user having the name user_name or the numeric user ID user_id. (“User” in this context refers to a system login account, not a MySQL user listed in the grant tables.)

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

If the server and databases cannot be found relative to the working directory, mysqld_safe attempts to locate them by absolute pathnames. Typical locations are /usr/local/libexec and /usr/local/var. The actual locations are determined from the values configured into the distribution at the time it was built. They should be correct if MySQL is installed in the location specified at configuration time.

--basedir=path

The path to the MySQL installation directory.

--datadir=path

The path to the MySQL data directory.

--pid-file=file_name

The pathname of the file in which the server should write its process ID.

- -help

Display a help message and exit.

--config-file=file_name

Specify the name of an alternative option file. This affects where mysqld_multi looks for [mysqldN] option groups. Without this option, all options are read from the usual my.cnf file. The option does not affect where mysqld_multi reads its own options, which are always taken from the [mysqld_multi] group in the usual my.cnf file.

--example

Display a sample option file.

--log=file_name

Specify the name of the log file. If the file exists, log output is appended to it.

--mysqladmin=prog_name

The mysqladmin binary to be used to stop servers.

--mysqld=prog_name

The mysqld binary to be used. Note that you can specify mysqld_safe as the value for this option also. If you use mysqld_safe to start the server, you can include the mysqld or ledir options in the corresponding [mysqldN] option group. These options indicate the name of the server that mysqld_safe should start and the pathname of the directory where the server is located. (See the descriptions for these options in Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.) Example:

[mysqld38]
mysqld = mysqld-max
ledir  = /opt/local/mysql/libexec

--no-log

Print log information to stdout rather than to the log file. By default, output goes to the log file.

--password=password

The password of the MySQL account to use when invoking mysqladmin. Note that the password value is not optional for this option, unlike for other MySQL programs.

--silent

Silent mode; disable warnings. This option was added in MySQL 4.1.6.

--tcp-ip

Connect to each MySQL server via the TCP/IP port instead of the Unix socket file. (If a socket file is missing, the server might still be running, but accessible only via the TCP/IP port.) By default, connections are made using the Unix socket file. This option affects stop and report operations.

--user=user_name

The username of the MySQL account to use when invoking mysqladmin.

--verbose

Be more verbose. This option was added in MySQL 4.1.6.

--version

Display version information and exit.

Most important: Before using mysqld_multi be sure that you understand the meanings of the options that are passed to the mysqld servers and why you would want to have separate mysqld processes. Beware of the dangers of using multiple mysqld servers with the same data directory. Use separate data directories, unless you know what you are doing. Starting multiple servers with the same data directory does not give you extra performance in a threaded system. See Section 5.8, “Running Multiple MySQL Servers on the Same Machine”.

Make sure that the MySQL account used for stopping the mysqld servers (with the mysqladmin program) has the same username and password for each server. Also, make sure that the account has the SHUTDOWN privilege. If the servers that you want to manage have different usernames or passwords for the administrative accounts, you might want to create an account on each server that has the same username and password. For example, you might set up a common multi_admin account by executing the following commands for each server:

shell> mysql -u root -S /tmp/mysql.sock -p
Enter password:
mysql> GRANT SHUTDOWN ON *.*
    -> TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';

See Section 5.5.2, “How the Privilege System Works”. You have to do this for each mysqld server. Change the connection parameters appropriately when connecting to each one. Note that the hostname part of the account name must allow you to connect as multi_admin from the host where you want to run mysqld_multi.

The Unix socket file and the TCP/IP port number must be different for every mysqld. (Alternatively, if the host has multiple network addresses, you can use --bind-adress to cause different servers to listen to different interfaces.)

The --pid-file option is very important if you are using mysqld_safe to start mysqld (for example, --mysqld=mysqld_safe) Every mysqld should have its own process ID file. The advantage of using mysqld_safe instead of mysqld is that mysqld_safe monitors its mysqld process and restarts it if the process terminates due to a signal sent using kill -9 or for other reasons, such as a segmentation fault. Please note that the mysqld_safe script might require that you start it from a certain place. This means that you might have to change location to a certain directory before running mysqld_multi. If you have problems starting, please see the mysqld_safe script. Check especially the lines:

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

The test performed by these lines should be successful, or you might encounter problems. See Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.

You might want to use the --user option for mysqld, but to do this you need to run the mysqld_multi script as the Unix root user. Having the option in the option file does not matter; you merely get a warning if you are not the superuser and the mysqld processes are started under your own Unix account.

-?, -I

Display a help message and exit.

-# debug_options

Write a debugging log. The debug_options string often is 'd:t:O,file_name'.

-V

Display version information and exit.

--help

Display a help message and exit.

--debug

Print information about script operations; do not create a package.

--tmp

Specify the temporary location.

--suffix

The suffix name for the package.

--dirname

Directory name to copy files (intermediate).

--silent

Do not print verbose list of files processed.

--tar

Create a tar.gz package instead of a .zip package.

By default, make_win_src_distribution creates a Zip-format archive with the name mysql-VERSION-win-src.zip, where VERSION represents the version of your MySQL source tree.

--basedir=path

The path to the MySQL installation directory.

--force

Causes mysql_install_db to run even if DNS does not work. In that case, grant table entries that normally use hostnames will use IP addresses.

--datadir=path, --ldata=path

The path to the MySQL data directory.

--rpm

For internal use. This option is used by RPM files during the MySQL installation process.

--skip-name-resolve

Use IP addresses rather than hostnames when creating grant table entries. This option can be useful if your DNS does not work.

--user=user_name

The login username to use for running mysqld. Files and directories created by mysqld will be owned by this user. You must be root to use this option. By default, mysqld runs using your current login name and files and directories that it creates will be owned by you.

--verbose

Verbose mode. Print more information about what the program does.

--windows

For internal use. This option is used for creating Windows distributions.

You can set a password for root accounts.

You can remove root accounts that are accessible from outside the local host.

You can remove anonymous-user accounts.

You can remove the test database, which by default can be accessed by anonymous users.

create db_name

Create a new database named db_name.

debug

Tell the server to write debug information to the error log.

drop db_name

Delete the database named db_name and all its tables.

extended-status

Display the server status variables and their values.

MySQL Enterprise For expert advice on using server system variables, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.

flush-hosts

Flush all information in the host cache.

flush-logs

Flush all logs.

flush-privileges

Reload the grant tables (same as reload).

flush-status

Clear status variables.

flush-tables

Flush all tables.

flush-threads

Flush the thread cache. (Added in MySQL 3.23.16.)

kill id,id,...

Kill server threads. If multiple thread ID values are given, there must be no spaces in the list.

old-password new-password

This is like the password command but stores the password using the old (pre-4.1) password-hashing format. This command was added in MySQL 4.1.0. (See Section 5.5.9, “Password Hashing as of MySQL 4.1”.)

MySQL Enterprise For expert advice on the security implications of using the old-password command, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.

password new-password

Set a new password. This changes the password to new-password for the account that you use with mysqladmin for connecting to the server. Thus, the next time you invoke mysqladmin (or any other client program) using the same account, you will need to specify the new password.

If the new-password value contains spaces or other characters that are special to your command interpreter, you need to enclose it within quotes. On Windows, be sure to use double quotes rather than single quotes; single quotes are not stripped from the password, but rather are interpreted as part of the password. For example:

shell> mysqladmin password "my new password"

ping

Check whether the server is alive. The return status from mysqladmin is 0 if the server is running, 1 if it is not. Beginning with MySQL 4.0.22, the status is 0 even in case of an error such as Access denied, because that means the server is running but refused the connection, which is different from the server not running.

processlist

Show a list of active server threads. This is like the output of the SHOW PROCESSLIST statement. If the --verbose option is given, the output is like that of SHOW FULL PROCESSLIST.

reload

Reload the grant tables.

refresh

Flush all tables and close and open log files.

shutdown

Stop the server.

start-slave

Start replication on a slave server. (Added in MySQL 3.23.16.)

status

Display a short server status message.

stop-slave

Stop replication on a slave server. (Added in MySQL 3.23.16.)

variables

Display the server system variables and their values.

MySQL Enterprise For expert advice on using server system variables, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.

version

Display version information from the server.

Uptime

The number of seconds the MySQL server has been running.

Threads

The number of active threads (clients).

Questions

The number of questions (queries) from clients since the server was started.

Slow queries

The number of queries that have taken more than long_query_time seconds. See Section 5.3.5, “The Slow Query Log”.

Opens

The number of tables the server has opened.

Flush tables

The number of flush-*, refresh, and reload commands the server has executed.

Open tables

The number of tables that currently are open.

Memory in use

The amount of memory allocated directly by mysqld. This value is displayed only when MySQL has been compiled with --with-debug=full.

Maximum memory used

The maximum amount of memory allocated directly by mysqld. This value is displayed only when MySQL has been compiled with --with-debug=full.

--help, -?

Display a help message and exit.

--character-sets-dir=path

The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

--compress, -C

Compress all information sent between the client and the server if both support compression.

--count=N, -c N

The number of iterations to make for repeated command execution. This works only with the --sleep option.

--debug[=debug_options], -# [debug_options]

Write a debugging log. The debug_options string often is 'd:t:o,file_name'. The default is 'd:t:o,/tmp/mysqladmin.trace'.

--default-character-set=charset_name

Use charset_name as the default character set. See Section 9.2, “The Character Set Used for Data and Sorting”. Added in MySQL 4.1.9.

--force, -f

Do not ask for confirmation for the drop db_name command. With multiple commands, continue even if an error occurs.

--host=host_name, -h host_name

Connect to the MySQL server on the given host.

--password[=password], -p[password]

The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, you are prompted for one.

Specifying a password on the command line should be considered insecure. See Section 5.6.6, “Keeping Your Password Secure”.

--port=port_num, -P port_num

The TCP/IP port number to use for the connection.

--protocol={TCP|SOCKET|PIPE|MEMORY}

The connection protocol to use. Added in MySQL 4.1.

--relative, -r

Show the difference between the current and previous values when used with the --sleep option. Currently, this option works only with the extended-status command.

--silent, -s

Exit silently if a connection to the server cannot be established.

--sleep=delay, -i delay

Execute commands repeatedly, sleeping for delay seconds in between. The --count option determines the number of iterations.

--socket=path, -S path

For connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

--ssl*

Options that begin with --ssl specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates. See Section 5.6.7.3, “SSL Command Options”.

--user=user_name, -u user_name

The MySQL username to use when connecting to the server.

--verbose, -v

Verbose mode. Print more information about what the program does.

--version, -V

Display version information and exit.

--vertical, -E

Print output vertically. This is similar to --relative, but prints output vertically.

--wait[=count], -w[count]

If the connection cannot be established, wait and retry instead of aborting. If a count value is given, it indicates the number of times to retry. The default is one time.

connect_timeout

The maximum number of seconds before connection timeout. The default value is 43200 (12 hours).

shutdown_timeout

The maximum number of seconds to wait for server shutdown. The default value is 3600 (1 hour).

--help, -?

Display a help message and exit.

--all-databases, -A

Check all tables in all databases. This is the same as using the --databases option and naming all the databases on the command line.

--all-in-1, -1

Instead of issuing a statement for each table, execute a single statement for each database that names all the tables from that database to be processed.

--analyze, -a

Analyze the tables.

MySQL Enterprise For expert advice on optimizing tables, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.

--auto-repair

If a checked table is corrupted, automatically fix it. Any necessary repairs are done after all tables have been checked.

--character-sets-dir=path

The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

--check, -c

Check the tables for errors. This is the default operation.

--check-only-changed, -C

Check only tables that have changed since the last check or that have not been closed properly.

--compress

Compress all information sent between the client and the server if both support compression.

--databases, -B

Process all tables in the named databases. Normally, mysqlcheck treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names.

--debug[=debug_options], -# [debug_options]

Write a debugging log. A typical debug_options string is often 'd:t:o,file_name'.

--default-character-set=charset_name

Use charset_name as the default character set. See Section 9.2, “The Character Set Used for Data and Sorting”.

--extended, -e

If you are using this option to check tables, it ensures that they are 100% consistent but takes a long time.

If you are using this option to repair tables, it runs an extended repair that may not only take a long time to execute, but may produce a lot of garbage rows also!

--fast, -F

Check only tables that have not been closed properly.

--force, -f

Continue even if an SQL error occurs.

--host=host_name, -h host_name

Connect to the MySQL server on the given host.

--medium-check, -m

Do a check that is faster than an --extended operation. This finds only 99.99% of all errors, which should be good enough in most cases.

--optimize, -o

Optimize the tables.

--password[=password], -p[password]

The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, you are prompted for one.

Specifying a password on the command line should be considered insecure. See Section 5.6.6, “Keeping Your Password Secure”.

--port=port_num, -P port_num

The TCP/IP port number to use for the connection.

--protocol={TCP|SOCKET|PIPE|MEMORY}

The connection protocol to use. This option was added in MySQL 4.1.

--quick, -q

If you are using this option to check tables, it prevents the check from scanning the rows to check for incorrect links. This is the fastest check method.

If you are using this option to repair tables, it tries to repair only the index tree. This is the fastest repair method.

--repair, -r

Perform a repair that can fix almost anything except unique keys that are not unique.

--silent, -s

Silent mode. Print only error messages.

--socket=path, -S path

For connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

--ssl*

Options that begin with --ssl specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates. See Section 5.6.7.3, “SSL Command Options”.

--tables

Overrides the --databases or -B option. All name arguments following the option are regarded as table names.

--use-frm

For repair operations on MyISAM tables, get the table structure from the .frm file so that the table can be repaired even if the .MYI header is corrupted. This option was added in MySQL 4.0.5.

--user=user_name, -u user_name

The MySQL username to use when connecting to the server.

--verbose, -v

Verbose mode. Print information about the various stages of program operation.

--version, -V

Display version information and exit.

To select the effect of --opt except for some features, use the --skip option for each feature. For example, to disable extended inserts and memory buffering, use --opt --skip-extended-insert --skip-quick. (As of MySQL 4.1, --skip-extended-insert --skip-quick is sufficient because --opt is on by default.)

To reverse --opt for all features except index disabling and table locking, use --skip-opt --disable-keys --lock-tables.

--help, -?

Display a help message and exit.

--add-drop-database

Add a DROP DATABASE statement before each CREATE DATABASE statement. Added in MySQL 4.1.13.

--add-drop-table

Add a DROP TABLE statement before each CREATE TABLE statement.

--add-locks

Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded. See Section 7.2.13, “Speed of INSERT Statements”.

--all-databases, -A

Dump all tables in all databases. This is the same as using the --databases option and naming all the databases on the command line.

--allow-keywords

Allow creation of column names that are keywords. This works by prefixing each column name with the table name.

--character-sets-dir=path

The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

--comments, -i

Write additional information in the dump file such as program version, server version, and host. This option is enabled by default. To suppress this additional information, use --skip-comments. This option was added in MySQL 4.0.17.

--compact

Produce less verbose output. This option enables the --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset options. Added in MySQL 4.1.2.

--compatible=name

Produce output that is more compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See Section 5.1.6, “SQL Modes”.

This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example, --compatible=oracle does not map data types to Oracle types or use Oracle comment syntax.

This option requires a server version of 4.1.0 or higher. With older servers, it does nothing.

--complete-insert, -c

Use complete INSERT statements that include column names.

--compress, -C

Compress all information sent between the client and the server if both support compression.

--create-options

Include all MySQL-specific table options in the CREATE TABLE statements. Before MySQL 4.1.2, use --all instead.

--databases, -B

Dump several databases. Normally, mysqldump treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names. CREATE DATABASE and USE statements are included in the output before each new database.

--debug[=debug_options], -# [debug_options]

Write a debugging log. The debug_options string is often 'd:t:o,file_name'. The default value is 'd:t:o,/tmp/mysqldump.trace'.

--default-character-set=charset_name

Use charset_name as the default character set. See Section 9.2, “The Character Set Used for Data and Sorting”. If no character set is specified, mysqldump from MySQL 4.1.2 or later uses utf8, and earlier versions use latin1.

--delayed-insert

Write INSERT DELAYED statements rather than INSERT statements.

--delete-master-logs

On a master replication server, delete the binary logs after performing the dump operation. This option automatically enables --first-slave before MySQL 4.1.8 and enables --master-data thereafter. It was added in MySQL 3.23.57 (for MySQL 3.23) and MySQL 4.0.13 (for MySQL 4.0).

--disable-keys, -K

For each table, surround the INSERT statements with /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading the dump file into a MySQL 4.0 or newer server faster because the indexes are created after all rows are inserted. This option is effective only for non-unique indexes of MyISAM tables. only.

--extended-insert, -e

Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.

--fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=...

These options are used with the -T option and have the same meaning as the corresponding clauses for LOAD DATA INFILE. See Section 12.2.5, “LOAD DATA INFILE Syntax”.

--first-slave, -x

Deprecated. Renamed to --lock-all-tables in MySQL 4.1.8.

--flush-logs, -F

Flush the MySQL server log files before starting the dump. This option requires the RELOAD privilege. Note that if you use this option in combination with the --all-databases (or -A) option, the logs are flushed for each database dumped. The exception is when using --lock-all-tables or --master-data: In this case, the logs are flushed only once, corresponding to the moment that all tables are locked. If you want your dump and the log flush to happen at exactly the same moment, you should use --flush-logs together with either --lock-all-tables or --master-data.

--force, -f

Continue even if an SQL error occurs during a table dump.

--host=host_name, -h host_name

Dump data from the MySQL server on the given host. The default host is localhost.

--hex-blob

Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263). The affected data types are BINARY, VARBINARY, and BLOB in MySQL 4.1 and up, and CHAR BINARY, VARCHAR BINARY, and BLOB in MySQL 4.0. This option was added in MySQL 4.0.23 and 4.1.8.

--ignore-table=db_name.tbl_name

Do not dump the given table, which must be specified using both the database and table names. To ignore multiple tables, use this option multiple times. This option was added in MySQL 4.1.9.

--insert-ignore

Write INSERT statements with the IGNORE option. This option was added in MySQL 4.1.12.

--lines-terminated-by=...

This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE. See Section 12.2.5, “LOAD DATA INFILE Syntax”.

--lock-all-tables, -x

Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables. Added in MySQL 4.1.8.

--lock-tables, -l

Lock all tables before dumping them. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB and BDB, --single-transaction is a much better option, because it does not need to lock the tables at all.

Please note that when dumping multiple databases, --lock-tables locks tables for each database separately. Therefore, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states.

--master-data[=value]

Write the binary log filename and position to the output. This option requires the RELOAD privilege and the binary log must be enabled. If the option value is equal to 1, the position and filename are written to the dump output in the form of a CHANGE MASTER statement. If the dump is from a master server and you use it to set up a slave server, the CHANGE MASTER statement causes the slave to start from the correct position in the master's binary logs. If the option value is equal to 2, the CHANGE MASTER statement is written as an SQL comment. If the value is not specified, then the default value is 1. value may be given as of MySQL 4.1.8; before that, do not specify an option value.

The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified (in which case, a global read lock is acquired only for a short time at the beginning of the dump. See also the description for --single-transaction. In all cases, any action on logs happens at the exact moment of the dump.

--no-autocommit

Enclose the INSERT statements for each dumped table within SET AUTOCOMMIT=0 and COMMIT statements.

--no-create-db, -n

This option suppresses the CREATE DATABASE statements that are otherwise included in the output if the --databases or --all-databases option is given.

--no-create-info, -t

Do not write CREATE TABLE statements that re-create each dumped table.

--no-data, -d

Do not write any table row information (that is, do not dump table contents). This is very useful if you want to dump only the CREATE TABLE statement for the table.

--opt

This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.

As of MySQL 4.1, --opt is enabled by default. Use --skip-opt to disable it. See the discussion at the beginning of this section for information about selectively enabling or disabling certain of the options affected by --opt.

--order-by-primary

Sorts each table's rows by its primary key, or by its first unique index, if such an index exists. This is useful when dumping a MyISAM table to be loaded into an InnoDB table, but will make the dump itself take considerably longer. This option was added in MySQL 4.1.8.

--password[=password], -p[password]

The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, you are prompted for one.

Specifying a password on the command line should be considered insecure. See Section 5.6.6, “Keeping Your Password Secure”.

--port=port_num, -P port_num

The TCP/IP port number to use for the connection.

--protocol={TCP|SOCKET|PIPE|MEMORY}

The connection protocol to use. Added in MySQL 4.1.

--quick, -q

This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

--quote-names, -Q

Quote database, table, and column names within “`” characters. If the ANSI_QUOTES SQL mode is enabled, names are quoted within “"” characters. As of MySQL 4.1.1, --quote-names is enabled by default. It can be disabled with --skip-quote-names, but this option should be given after any option such as --compatible that may enable --quote-names.

--result-file=file, -r file

Direct output to a given file. This option should be used on Windows to prevent newline “\n” characters from being converted to “\r\n” carriage return/newline sequences. The result file is created and its contents overwritten, even if an error occurs while generating the dump. The previous contents are lost.

--set-charset

Add SET NAMES default_character_set to the output. This option is enabled by default. To suppress the SET NAMES statement, use --skip-set-charset. This option was added in MySQL 4.1.2.

--single-transaction

This option issues a BEGIN SQL statement before dumping data from the server. It is useful only with transactional tables such as InnoDB and BDB, because then it dumps the consistent state of the database at the time when BEGIN was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

The --single-transaction option was added in MySQL 4.0.2. This option is mutually exclusive with the --lock-tables option, because LOCK TABLES causes any pending transactions to be committed implicitly.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCluster storage engine supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead.

To dump large tables, you should combine this option with --quick.

--skip-comments

See the description for the --comments option.

--skip-opt

See the description for the --opt option.

--socket=path, -S path

For connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

--ssl*

Options that begin with --ssl specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates. See Section 5.6.7.3, “SSL Command Options”.

--tab=path, -T path

Produce tab-separated data files. For each dumped table, mysqldump creates a tbl_name.sql file that contains the CREATE TABLE statement that creates the table, and a tbl_name.txt file that contains its data. The option value is the directory in which to write the files.

By default, the .txt data files are formatted using tab characters between column values and a newline at the end of each line. The format can be specified explicitly using the --fields-xxx and --lines-terminated-by options.

--tables

Override the --databases or -B option. mysqldump regards all name arguments following the option as table names.

--user=user_name, -u user_name

The MySQL username to use when connecting to the server.

--verbose, -v

Verbose mode. Print more information about what the program does.

--version, -V

Display version information and exit.

--where='where_condition', -w 'where_condition'

Dump only rows selected by the given WHERE condition. Quotes around the condition are mandatory if it contains spaces or other characters that are special to your command interpreter.

Examples:

--where="user='jimf'"
-w"userid>1"
-w"userid<1"

--xml, -X

Write dump output as well-formed XML.

max_allowed_packet

The maximum size of the buffer for client/server communication. The value of the variable can be up to 16MB before MySQL 4.0, and up to 1GB from MySQL 4.0 on.

net_buffer_length

The initial size of the buffer for client/server communication. When creating multiple-row-insert statements (as with option --extended-insert or --opt), mysqldump creates rows up to net_buffer_length length. If you increase this variable, you should also ensure that the net_buffer_length variable in the MySQL server is at least this large.

--help, -?

Display a help message and exit.

--character-sets-dir=path

The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

--columns=column_list, -c column_list

This option takes a comma-separated list of column names as its value. The order of the column names indicates how to match data file columns with table columns.

--compress, -C

Compress all information sent between the client and the server if both support compression.

--debug[=debug_options], -# [debug_options]

Write a debugging log. The debug_options string often is 'd:t:o,file_name'.

--default-character-set=charset_name

Use charset_name as the default character set. See Section 9.2, “The Character Set Used for Data and Sorting”.

--delete, -D

Empty the table before importing the text file.

--fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=...

These options have the same meaning as the corresponding clauses for LOAD DATA INFILE. See Section 12.2.5, “LOAD DATA INFILE Syntax”.

--force, -f

Ignore errors. For example, if a table for a text file does not exist, continue processing any remaining files. Without --force, mysqlimport exits if a table does not exist.

--host=host_name, -h host_name

Import data to the MySQL server on the given host. The default host is localhost.

--ignore, -i

See the description for the --replace option.

--ignore-lines=N

Ignore the first N lines of the data file.

--lines-terminated-by=...

This option has the same meaning as the corresponding clause for LOAD DATA INFILE. For example, to import Windows files that have lines terminated with carriage return/linefeed pairs, use --lines-terminated-by="\r\n". (You might have to double the backslashes, depending on the escaping conventions of your command interpreter.) See Section 12.2.5, “LOAD DATA INFILE Syntax”.

--local, -L

Read input files locally from the client host.

MySQL Enterprise For expert advice on the security implications of enabling LOCAL, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.

--lock-tables, -l

Lock all tables for writing before processing any text files. This ensures that all tables are synchronized on the server.

--low-priority

Use LOW_PRIORITY when loading the table. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE).

--password[=password], -p[password]

The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, you are prompted for one.

Specifying a password on the command line should be considered insecure. See Section 5.6.6, “Keeping Your Password Secure”.

--port=port_num, -P port_num

The TCP/IP port number to use for the connection.

--protocol={TCP|SOCKET|PIPE|MEMORY}

The connection protocol to use. Added in MySQL 4.1.

--replace, -r

The --replace and --ignore options control handling of input rows that duplicate existing rows 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 do not specify either option, an error occurs when a duplicate key value is found, and the rest of the text file is ignored.

--silent, -s

Silent mode. Produce output only when errors occur.

--socket=path, -S path

For connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

--ssl*

Options that begin with --ssl specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates. See Section 5.6.7.3, “SSL Command Options”.

--user=user_name, -u user_name

The MySQL username to use when connecting to the server.

--verbose, -v

Verbose mode. Print more information about what the program does.

--version, -V

Display version information and exit.

If no database is given, a list of database names is shown.

If no table is given, all matching tables in the database are shown.

If no column is given, all matching columns and column types in the table are shown.

--help, -?

Display a help message and exit.

--character-sets-dir=path

The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

--compress, -C

Compress all information sent between the client and the server if both support compression.

--debug[=debug_options], -# [debug_options]

Write a debugging log. The debug_options string often is 'd:t:o,file_name'.

--default-character-set=charset_name

Use charset_name as the default character set. See Section 9.2, “The Character Set Used for Data and Sorting”.

--host=host_name, -h host_name

Connect to the MySQL server on the given host.

--keys, -k

Show table indexes.

--password[=password], -p[password]

The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, you are prompted for one.

Specifying a password on the command line should be considered insecure. See Section 5.6.6, “Keeping Your Password Secure”.

Specifying a password on the command line should be considered insecure. See Section 5.6.6, “Keeping Your Password Secure”.

--port=port_num, -P port_num

The TCP/IP port number to use for the connection.

--protocol={TCP|SOCKET|PIPE|MEMORY}

The connection protocol to use. Added in MySQL 4.1.

--socket=path, -S path

For connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

--ssl*

Options that begin with --ssl specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates. See Section 5.6.7.3, “SSL Command Options”.

--status, -i

Display extra information about each table.

--user=user_name, -u user_name

The MySQL username to use when connecting to the server.

--verbose, -v

Verbose mode. Print more information about what the program does. This option can be used multiple times to increase the amount of information.

--version, -V

Display version information and exit.

--help, -h -?

Display a help message and exit.

--count, -c

Calculate per-word statistics (counts and global weights).

--dump, -d

Dump the index, including data offsets and word weights.

--length, -l

Report the length distribution.

--stats, -s

Report global index statistics. This is the default operation if no other operation is specified.

--verbose, -v

Verbose mode. Print more output about what the program does.