This appendix contains listings of options for the most-used MySQL programs. These tables contain brief descriptions along with hyperlinks to the manual. Where applicable, listings of commands are also supplied.
Table C.1. mysql Option Reference
| Format | Config File | Description | Introduced |
|---|---|---|---|
| --auto-rehash | auto-rehash | Enable automatic rehashing | |
| --batch | batch | Don't use history file | |
| -B | |||
| --bind-address=host_name | Determine which client network interface (IP address or hostname) to use when connecting to the MySQL Server | 5.1.22-ndb-6.3.4 | |
| --character-sets-dir=name | character-sets-dir | Set the default character set | |
| --column-names | column-names | Write column names in results | |
| --column-type-info | column-type-info | Display result set metadata | 5.1.14 |
| -m | |||
| --comments | comments | Whether to retain or strip comments in statements sent to the server | 5.1.23 |
| -c | |||
| --compress | compress | Compress all information sent between the client and the server | |
| -C | |||
| --connect_timeout=value | connect_timeout | The number of seconds before connection timeout | |
| --database=dbname | database | The database to use | |
| -D dbname | |||
| dbname | |||
| -# [debug_options] | debug | Write a debugging log | |
| --debug[=debug_options] | |||
| --debug-check | debug-check | Print debugging information when the program exits | 5.1.21 |
| --debug-info | debug-info | Print debugging information, memory and CPU statistics when the program exits | |
| -T | |||
| --default-character-set=charset_name | default-character-set | Use charset_name as the default character set | |
| --delimiter=str | delimiter | Set the statement delimiter | |
| --execute=statement | execute | Execute the statement and quit | |
| -e statement | |||
| --force | force | Continue even if an SQL error occurs | |
| -f | |||
| --help | Display help message and exit | ||
| -? | |||
| -I | |||
| --host=host_name | host | Connect to the MySQL server on the given host | |
| -h host_name | |||
| --html | html | Produce HTML output | |
| -H | |||
| --ignore-spaces | ignore-spaces | Ignore spaces after function names | |
| -i | |||
| --line-numbers | line-numbers | Write line numbers for errors | |
| --local-infile[={0|1}] | local-infile | Enable or disable for LOCAL capability for LOAD DATA INFILE | |
| --max_allowed_packet=value | max_allowed_packet | The maximum packet length to send to or receive from the server | |
| --max_join_size=value | max_join_size | The automatic limit for rows in a join when using --safe-updates | |
| --named-commands | named-commands | Enable named mysql commands | |
| -G | |||
| --net_buffer_length=value | net_buffer_length | The buffer size for TCP/IP and socket communication | |
| --no-auto-rehash | Disable automatic rehashing | ||
| -A | |||
| --no-beep | no-beep | Do not beep when errors occur | |
| --no-named-commands | no-named-commands | Disable named mysql commands | |
| --no-pager | no-pager | Deprecated form of --skip-pager | |
| --no-tee | no-tee | Do not copy output to a file | |
| --one-database | one-database | Ignore statements except those for the default database named on the command line | |
| -o | |||
| --pager[=command] | pager | Use the given command for paging query output | |
| --password[=password] | password | The password to use when connecting to the server | |
| -p[password] | |||
| --port=port_num | port | The TCP/IP port number to use for the connection | |
| -P port_num | |||
| --prompt=format_str | prompt | Set the prompt to the specified format | |
| --protocol={TCP|SOCKET|PIPE|MEMORY} | protocol | The connection protocol to use | |
| --quick | quick | Do not cache each query result | |
| -q | |||
| --raw | raw | Write column values without escape conversion | |
| -r | |||
| --reconnect | reconnect | If the connection to the server is lost, automatically try to reconnect | |
| --i-am-a-dummy | safe-updates | Allow only UPDATE and DELETE statements that specify key values | |
| --safe-updates | |||
| -U | |||
| --secure-auth | secure-auth | Do not send passwords to the server in old (pre-4.1.1) format | |
| --select_limit=value | select_limit | The automatic limit for SELECT statements when using --safe-updates | |
| --show-warnings | show-warnings | Show warnings after each statement if there are any | |
| --sigint-ignore | sigint-ignore | Ignore SIGINT signals (typically the result of typing Control-C) | |
| --silent | silent | Silent mode | |
| -s | |||
| --skip-auto-rehash | skip-auto-rehash | Disable automatic rehashing | |
| --skip-column-names | skip-column-names | Do not write column names in results | |
| -N | |||
| --skip-line-numbers | skip-line-numbers | Skip line numbers for errors | |
| --skip-named-commands | skip-named-commands | Disable named mysql commands | |
| --skip-pager | skip-pager | Disable paging | |
| --skip-reconnect | skip-reconnect | Disable reconnecting | |
| --socket=path | socket | For connections to localhost | |
| -S path | |||
| --ssl-ca=file_name | ssl-ca | The path to a file that contains a list of trusted SSL CAs | |
| --ssl-capath=directory_name | ssl-capath | The path to a directory that contains trusted SSL CA certificates in PEM format | |
| --ssl-cert=file_name | ssl-cert | The name of the SSL certificate file to use for establishing a secure connection | |
| --ssl-cipher=cipher_list | ssl-cipher | A list of allowable ciphers to use for SSL encryption | |
| --ssl-key=file_name | ssl-key | The name of the SSL key file to use for establishing a secure connection | |
| --ssl-verify-server-cert | ssl-verify-server-cert | The server's Common Name value in its certificate is verified against the hostname used when connecting to the server | |
| --table | table | Display output in tabular format | |
| -t | |||
| --tee=file_name | tee | Append a copy of output to the given file | |
| --unbuffered | unbuffered | Flush the buffer after each query | |
| --user=user_name | user | The MySQL username to use when connecting to the server | |
| -u user_name | |||
| --verbose | Verbose mode | ||
| -v | |||
| --version | Display version information and exit | ||
| -V | |||
| --vertical | vertical | Print query output rows vertically (one line per column value) | |
| -E | |||
| --wait | wait | If the connection cannot be established, wait and retry instead of aborting | |
| -w | |||
| --xml | xml | Produce XML output | |
| -X |
Table C.2. mysqladmin Option Reference
| Format | Config File | Description | Introduced |
|---|---|---|---|
| --bind-address=host_name | Determine which client network interface (IP address or hostname) to use when connecting to the MySQL Server | 5.1.22-ndb-6.3.4 | |
| 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 | ||
| extended-status | Display the server status variables and their values | ||
| flush-hosts | Flush all information in the host cache | ||
| flush-logs | Flush all logs | ||
| flush-privileges | Reload the grant tables | ||
| --compress | compress | Compress all information sent between the client and the server | |
| -C | |||
| --connect_timeout=seconds | connect_timeout | The number of seconds before connection timeout | |
| --count=N | The number of iterations to make for repeated command execution | ||
| -c N | |||
| -# [debug_options] | debug | Write a debugging log | |
| --debug[=debug_options] | |||
| --debug-check | debug-check | Print debugging information when the program exits | 5.1.21 |
| --debug-info | debug-info | Print debugging information, memory and CPU statistics when the program exits | 5.1.21 |
| --default-character-set=charset_name | default-character-set | Use charset_name as the default character set | |
| --force | force | Continue even if an SQL error occurs | |
| -f | |||
| --help | Display help message and exit | ||
| -? | |||
| --host=host_name | host | Connect to the MySQL server on the given host | |
| -h host_name | |||
| --no-beep | no-beep | Do not beep when errors occur | 5.1.17 |
| --password[=password] | password | The password to use when connecting to the server | |
| -p[password] | |||
| --port=port_num | port | The TCP/IP port number to use for the connection | |
| -P port_num | |||
| --protocol={TCP|SOCKET|PIPE|MEMORY} | protocol | The connection protocol to use | |
| --r | relative | Show the difference between the current and previous values when used with the --sleep option | |
| --relative | |||
| --shutdown_timeout=seconds | The maximum number of seconds to wait for server shutdown | ||
| --silent | silent | Silent mode | |
| -s | |||
| --sleep=delay | sleep | Execute commands repeatedly, sleeping for delay seconds in between | |
| -s delay | |||
| --socket=path | socket | For connections to localhost | |
| -S path | |||
| --ssl-ca=file_name | ssl-ca | The path to a file that contains a list of trusted SSL CAs | |
| --ssl-capath=directory_name | ssl-capath | The path to a directory that contains trusted SSL CA certificates in PEM format | |
| --ssl-cert=file_name | ssl-cert | The name of the SSL certificate file to use for establishing a secure connection | |
| --ssl-cipher=cipher_list | ssl-cipher | A list of allowable ciphers to use for SSL encryption | |
| --ssl-key=file_name | ssl-key | The name of the SSL key file to use for establishing a secure connection | |
| --ssl-verify-server-cert | ssl-verify-server-cert | The server's Common Name value in its certificate is verified against the hostname used when connecting to the server | |
| --user=user_name, | user | The MySQL username to use when connecting to the server | |
| -u user_name | |||
| --verbose | Verbose mode | ||
| -v | |||
| --version | Display version information and exit | ||
| -V | |||
| --vertical | vertical | Print query output rows vertically (one line per column value) | |
| -E | |||
| --wait | wait | If the connection cannot be established, wait and retry instead of aborting | |
| -w |
Table C.3. mysqldump Option Reference
| Format | Config File | Description | Introduced |
|---|---|---|---|
| --add-drop-database | add-drop-database | Add a DROP DATABASE statement before each CREATE DATABASE statement | |
| --add-drop-table | add-drop-table | Add a DROP TABLE statement before each CREATE TABLE statement | |
| --add-locks | add-locks | Surround each table dump with LOCK TABLES and UNLOCK TABLES statements | |
| --all-databases | all-databases | Dump all tables in all databases | |
| -A | |||
| --all-tablespaces | all-tablespaces | Adds to a table dump all SQL statements needed to create any tablespaces used by an NDB Cluster table | 5.1.6 |
| -Y | |||
| --allow-keywords | allow-keywords | Allow creation of column names that are keywords | |
| --bind-address=host_name | Determine which client network interface (IP address or hostname) to use when connecting to the MySQL Server | 5.1.22-ndb-6.3.4 | |
| --comments | comments | Add comments to the dump file | |
| -i | |||
| --compact | compact | Produce less verbose output | |
| --compatible=name[,name,...] | compatible | Produce output that is more compatible with other database systems or with older MySQL servers | |
| --complete-insert | complete-insert | Use complete INSERT statements that include column names | |
| -c | |||
| --create-options | create-options | Include all MySQL-specific table options in the CREATE TABLE statements | |
| --databases | databases | Dump several databases | |
| -D | |||
| --delayed-insert | delayed-insert | Write INSERT DELAYED statements rather than INSERT statements | |
| --delete-master-logs | delete-master-logs | On a master replication server, delete the binary logs after performing the dump operation | |
| --disable-keys | disable-keys | For each table, surround the INSERT statements with disable and enable keys statements | |
| -K | |||
| --dump-date | dump-date | Include dump date in "Dump completed on" comment if --comments is given | 5.1.23 |
| --events | events | Dump events from the dumped databases | |
| -E | |||
| --extended-insert | extended-insert | Use multiple-row INSERT syntax that include several VALUES lists | |
| -e | |||
| --fields-enclosed-by=string | fields-enclosed-by | This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --fields-escaped-by | fields-escaped-by | This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --fields-optionally-enclosed-by=string | fields-optionally-enclosed-by | This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --fields-terminated-by=string | fields-terminated-by | This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --lock-all-tables | first-slave | Deprecated. Now renamed to --lock-all-tables | |
| --flush-logs | flush-logs | Flush the MySQL server log files before starting the dump | |
| -F | |||
| --flush-privileges | flush-privileges | Emit a FLUSH PRIVILEGES statement after dumping the mysql database | |
| --help | Display help message and exit | ||
| -? | |||
| --hex-blob | hex-blob | Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263) | |
| --ignore-table=db_name.tbl_name | ignore-table | Do not dump the given table | |
| --insert-ignore | insert-ignore | Write INSERT statements with the IGNORE option | |
| --lines-terminated-by=string | lines-terminated-by | This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --lock-all-tables | lock-all-tables | Lock all tables across all databases | |
| -x | |||
| --lock-tables | lock-tables | Lock all tables before dumping them | |
| --log-error=file_name | log-error | Append warnings and errors to the named file | 5.1.18 |
| --master-data[=value] | master-data | Write the binary log filename and position to the output | |
| --max_allowed_packet=value | max_allowed_packet | The maximum packet length to send to or receive from the server | |
| --net_buffer_length=value | net_buffer_length | The buffer size for TCP/IP and socket communication | |
| --no-autocommit | no-autocommit | Enclose the INSERT statements for each dumped table within SET AUTOCOMMIT=0 and COMMIT statements | |
| --no-create-db | no-create-db | This option suppresses the CREATE DATABASE statements | |
| -n | |||
| --no-create-info | no-create-info | Do not write CREATE TABLE statements that re-create each dumped table | |
| -t | |||
| --no-set-names | no-set-names | Turn off complete-insert | |
| --opt | 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. | |
| --order-by-primary | order-by-primary | Sorts each table's rows by its primary key, or by its first unique index | |
| --quick | quick | Retrieve rows for a table from the server a row at a time | |
| -q | |||
| --quote-names | quote-names | Quote database, table, and column names within backtick characters | |
| -Q | |||
| --replace | replace | Write REPLACE statements rather than INSERT statements | |
| --result-file=file | result-file | Direct output to a given file | |
| -r file | |||
| --routines | routines | Dump stored routines (functions and procedures) from the dumped databases | |
| -R | |||
| --set-charset | set-charset | Add SET NAMES default_character_set to the output | |
| --single-transaction | single-transaction | This option issues a BEGIN SQL statement before dumping data from the server | |
| --skip-add-drop-table | skip-add-drop-table | Do not add | |
| --skip-add-locks | skip-add-locks | Do not add locks | |
| --skip-comments | skip-comments | Do not add comments to the dump file | |
| --skip-compact | skip-compact | Turn off compact | |
| --skip-disable-keys | skip-disable-keys | Do not disable keys | |
| --skip-extended-insert | skip-extended-insert | Turn off extended-insert | |
| --skip-opt | skip-opt | Turn off the options set by opt | |
| --skip-quick | skip-quick | Do not retrieve rows for a table from the server a row at a time | |
| --skip-quote-names | skip-quote-names | Turn off quote names | |
| -skip-charset | skip-set-charset | Suppress the SET NAMES statement | |
| --skip-triggers | skip-triggers | Turn off triggers | |
| --skip-tz-utc | skip-tz-utc | Turn off tz-utc | |
| --ssl-ca=file_name | ssl-ca | The path to a file that contains a list of trusted SSL CAs | |
| --ssl-capath=directory_name | ssl-capath | The path to a directory that contains trusted SSL CA certificates in PEM format | |
| --ssl-cert=file_name | ssl-cert | The name of the SSL certificate file to use for establishing a secure connection | |
| --ssl-cipher=cipher_list | ssl-cipher | A list of allowable ciphers to use for SSL encryption | |
| --ssl-key=file_name | ssl-key | The name of the SSL key file to use for establishing a secure connection | |
| --ssl-verify-server-cert | ssl-verify-server-cert | The server's Common Name value in its certificate is verified against the hostname used when connecting to the server | |
| --tab=path | tab | Produce tab-separated data files | |
| -T path | |||
| --tables | tables | Override the --databases or -B option | |
| --triggers | triggers | Dump triggers for each dumped table | |
| --tz-utc | tz-utc | Add SET TIME_ZONE='+00:00' to the dump file | |
| --where='where_condition' | where | Dump only rows selected by the given WHERE condition | |
| -w 'where_condition' | |||
| --xml | xml | Produce XML output | |
| -X |