| 4.5. MySQL Client Programs This section describes client programs that connect to the MySQL server. 4.5.1. mysql - The MySQL Command-Line Tool mysql is a simple SQL shell with input line editing capabilities. It supports interactive and noninteractive use. When used interactively, query results are presented in an ASCII-table format. When used noninteractively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command options. If you have problems due to insufficient memory for large result sets, use the --quick option. This forces mysql to retrieve results from the server a row at a time rather than retrieving the entire result set and buffering it in memory before displaying it. This is done by returning the result set using the mysql_use_result() C API function in the client/server library rather than mysql_store_result(). Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows: shell> mysql db_name Or: shell> mysql --user=user_name --password=your_password db_name Then type an SQL statement, end it with ";", \g, or \G and press Enter. Typing Control+C causes mysql to attempt to kill the current statement. If this cannot be done, or Control+C is typed again before the statement is killed, mysql exits. Previously, Control+C caused mysql to exit in all cases. You can execute SQL statements in a script file (batch file) like this: shell> mysql db_name < script.sql > output.tab On Unix, the mysql client writes a record of executed statements to a history file. See Section 4.5.1.3, "mysql History File". mysql supports the following options, which can be specified on the command line or in the [mysql] and [client] groups of an option file. mysql also supports the options for processing option files described at Section 4.2.3.4, "Command-Line Options that Affect Option-File Handling". --help, -? Display a help message and exit. --auto-rehash Enable automatic rehashing. This option is on by default, which enables database, table, and column name completion. Use --disable-auto-rehash to disable rehashing. That causes mysql to start faster, but you must issue the rehash command if you want to use name completion. To complete a name, enter the first part and press Tab. If the name is unambiguous, mysql completes it. Otherwise, you can press Tab again to see the possible names that begin with what you have typed so far. Completion does not occur if there is no default database. --auto-vertical-output Cause result sets to be displayed vertically if they are too wide for the current window, and using normal tabular format otherwise. (This applies to statements terminated by ; or \G.) This option was added in MySQL 5.5.3. --batch, -B Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file. Batch mode results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option. --bind-address=ip_address On a computer having multiple network interfaces, this option can be used to select which interface is employed when connecting to the MySQL server. This option is supported only in the version of the mysql client that is supplied with MySQL Cluster. It is not available in standard MySQL Server 5.5 releases. --character-sets-dir=path The directory where character sets are installed. See Section 10.5, "Character Set Configuration". --column-names Write column names in results. --column-type-info, -m Display result set metadata. --comments, -c Whether to preserve comments in statements sent to the server. The default is --skip-comments (discard comments), enable with --comments (preserve comments). --compress, -C Compress all information sent between the client and the server if both support compression. --database=db_name, -D db_name The database to use. This is useful primarily in an option file. --debug[=debug_options], -# [debug_options] Write a debugging log. A typical debug_options string is 'd:t:o,file_name'. The default is 'd:t:o,/tmp/mysql.trace'. --debug-check Print some debugging information when the program exits. --debug-info, -T Print debugging information and memory and CPU usage statistics when the program exits. --default-auth=plugin The client-side authentication plugin to use. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.7. --default-character-set=charset_name Use charset_name as the default character set for the client and connection. A common issue that can occur when the operating system uses utf8 or another multi-byte character set is that output from the mysql client is formatted incorrectly, due to the fact that the MySQL client uses the latin1 character set by default. You can usually fix such issues by using this option to force the client to use the system character set instead. See Section 10.5, "Character Set Configuration", for more information. --delimiter=str Set the statement delimiter. The default is the semicolon character (";"). --disable-named-commands Disable named commands. Use the \* form only, or use named commands only at the beginning of a line ending with a semicolon (";"). mysql starts with this option enabled by default. However, even with this option, long-format commands still work from the first line. See Section 4.5.1.2, "mysql Commands". --enable-cleartext-plugin Enable the mysql_clear_password cleartext authentication plugin. (See Section 6.3.6.4, "The Cleartext Client-Side Authentication Plugin".) This option was added in MySQL 5.5.27. --execute=statement, -e statement Execute the statement and quit. The default output format is like that produced with --batch. See Section 4.2.3.1, "Using Options on the Command Line", for some examples. With this option, mysql does not use the history file. --force, -f Continue even if an SQL error occurs. --host=host_name, -h host_name Connect to the MySQL server on the given host. --html, -H Produce HTML output. --ignore-spaces, -i Ignore spaces after function names. The effect of this is described in the discussion for the IGNORE_SPACE SQL mode (see Section 5.1.7, "Server SQL Modes"). --init-command=str SQL statement to execute after connecting to the server. If auto-reconnect is enabled, the statement is executed again after reconnection occurs. --line-numbers Write line numbers for errors. Disable this with --skip-line-numbers. --local-infile[={0|1}] Enable or disable LOCAL capability for LOAD DATA INFILE. With no value, the option enables LOCAL. The option may be given as --local-infile=0 or --local-infile=1 to explicitly disable or enable LOCAL. Enabling LOCAL has no effect if the server does not also support it. --named-commands, -G Enable named mysql commands. Long-format commands are permitted, not just short-format commands. For example, quit and \q both are recognized. Use --skip-named-commands to disable named commands. See Section 4.5.1.2, "mysql Commands". --no-auto-rehash, -A This has the same effect as -skip-auto-rehash. See the description for --auto-rehash. --no-beep, -b Do not beep when errors occur. --no-named-commands, -g Deprecated, use --disable-named-commands instead. --no-named-commands was removed in MySQL 5.5.3. --no-pager Deprecated form of --skip-pager. See the --pager option. --no-pager was removed in MySQL 5.5.3. --no-tee Deprecated form of --skip-tee. See the --tee option. --no-tee is removed in MySQL 5.5.3. --one-database, -o Ignore statements except those that occur while the default database is the one named on the command line. This option is rudimentary and should be used with care. Statement filtering is based only on USE statements. Initially, mysql executes statements in the input because specifying a database db_name on the command line is equivalent to inserting USE db_name at the beginning of the input. Then, for each USE statement encountered, mysql accepts or rejects following statements depending on whether the database named is the one on the command line. The content of the statements is immaterial. Suppose that mysql is invoked to process this set of statements: DELETE FROM db2.t2;USE db2;DROP TABLE db1.t1;CREATE TABLE db1.t1 (i INT);USE db1;INSERT INTO t1 (i) VALUES(1);CREATE TABLE db2.t1 (j INT); If the command line is mysql --force --one-database db1, mysql handles the input as follows: The DELETE statement is executed because the default database is db1, even though the statement names a table in a different database. The DROP TABLE and CREATE TABLE statements are not executed because the default database is not db1, even though the statements name a table in db1. The INSERT and CREATE TABLE statements are executed because the default database is db1, even though the CREATE TABLE statement names a table in a different database.
--pager[=command] Use the given command for paging query output. If the command is omitted, the default pager is the value of your PAGER environment variable. Valid pagers are less, more, cat [> filename], and so forth. This option works only on Unix and only in interactive mode. To disable paging, use --skip-pager. Section 4.5.1.2, "mysql Commands", discusses output paging further. --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, mysql prompts for one. Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, "End-User Guidelines for Password Security". You can use an option file to avoid giving the password on the command line. --pipe, -W On Windows, connect to the server using a named pipe. This option applies only if the server supports named-pipe connections. --plugin-dir=path The directory in which to look for plugins. It may be necessary to specify this option if the --default-auth option is used to specify an authentication plugin but mysql does not find it. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.7. --port=port_num, -P port_num The TCP/IP port number to use for the connection. --prompt=format_str Set the prompt to the specified format. The default is mysql>. The special sequences that the prompt can contain are described in Section 4.5.1.2, "mysql Commands". --protocol={TCP|SOCKET|PIPE|MEMORY} The connection protocol to use for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For details on the permissible values, see Section 4.2.2, "Connecting to the MySQL Server". --quick, -q Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file. --raw, -r For tabular output, the "boxing" around columns enables one column value to be distinguished from another. For nontabular output (such as is produced in batch mode or when the --batch or --silent option is given), special characters are escaped in the output so they can be identified easily. Newline, tab, NUL, and backslash are written as \n, \t, \0, and \\. The --raw option disables this character escaping. The following example demonstrates tabular versus nontabular output and the use of raw mode to disable escaping: % mysqlmysql> SELECT CHAR(92);+----------+| CHAR(92) |+----------+| \ |+----------+% mysql -smysql> SELECT CHAR(92);CHAR(92)\\% mysql -s -rmysql> SELECT CHAR(92);CHAR(92)\ --reconnect If the connection to the server is lost, automatically try to reconnect. A single reconnect attempt is made each time the connection is lost. To suppress reconnection behavior, use --skip-reconnect. --safe-updates, --i-am-a-dummy, -U Permit only those UPDATE and DELETE statements that specify which rows to modify by using key values. If you have set this option in an option file, you can override it by using --safe-updates on the command line. See Section 4.5.1.6, "mysql Tips", for more information about this option. --secure-auth Do not send passwords to the server in old (pre-4.1.1) format. This prevents connections except for servers that use the newer password format. --show-warnings Cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode. --sigint-ignore Ignore SIGINT signals (typically the result of typing Control+C). --silent, -s Silent mode. Produce less output. This option can be given multiple times to produce less and less output. This option results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option. --skip-column-names, -N Do not write column names in results. --skip-line-numbers, -L Do not write line numbers for errors. Useful when you want to compare result files that include 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 using SSL and indicate where to find SSL keys and certificates. See Section 6.3.8.4, "SSL Command Options". --table, -t Display output in table format. This is the default for interactive use, but can be used to produce table output in batch mode. --tee=file_name Append a copy of output to the given file. This option works only in interactive mode. Section 4.5.1.2, "mysql Commands", discusses tee files further. --unbuffered, -n Flush the buffer after each query. --user=user_name, -u user_name The MySQL user name to use when connecting to the server. --verbose, -v Verbose mode. Produce more output about what the program does. This option can be given multiple times to produce more and more output. (For example, -v -v -v produces table output format even in batch mode.) --version, -V Display version information and exit. --vertical, -E Print query output rows vertically (one line per column value). Without this option, you can specify vertical output for individual statements by terminating them with \G. --wait, -w If the connection cannot be established, wait and retry instead of aborting. --xml, -X Produce XML output. <field name="column_name">NULL</field> The output when --xml is used with mysql matches that of mysqldump --xml. See Section 4.5.4, "mysqldump - A Database Backup Program" for details. The XML output also uses an XML namespace, as shown here: shell> mysql --xml -uroot -e "SHOW VARIABLES LIKE 'version%'"<?xml version="1.0"?><resultset statement="SHOW VARIABLES LIKE 'version%'" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><row><field name="Variable_name">version</field><field name="Value">5.0.40-debug</field></row><row><field name="Variable_name">version_comment</field><field name="Value">Source distribution</field></row><row><field name="Variable_name">version_compile_machine</field><field name="Value">i686</field></row><row><field name="Variable_name">version_compile_os</field><field name="Value">suse-linux-gnu</field></row></resultset> (See Bug #25946.)
You can also set the following variables by using --var_name=value. The --set-variable format is deprecated and was removed in MySQL 5.5.3. connect_timeout The number of seconds before connection timeout. (Default value is 0.) max_allowed_packet The maximum packet length to send to or receive from the server. (Default value is 16MB.) max_join_size The automatic limit for rows in a join when using --safe-updates. (Default value is 1,000,000.) net_buffer_length The buffer size for TCP/IP and socket communication. (Default value is 16KB.) select_limit The automatic limit for SELECT statements when using --safe-updates. (Default value is 1,000.)
mysql sends each SQL statement that you issue to the server to be executed. There is also a set of commands that mysql itself interprets. For a list of these commands, type help or \h at the mysql> prompt: mysql> helpList of all MySQL commands:Note that all text commands must be first on line and end with ';'? (\?) Synonym for `help'.clear (\c) Clear command.connect (\r) Reconnect to the server. Optional arguments are db and host.delimiter (\d) Set statement delimiter.edit (\e) Edit command with $EDITOR.ego (\G) Send command to mysql server, display result vertically.exit (\q) Exit mysql. Same as quit.go (\g) Send command to mysql server.help (\h) Display this help.nopager (\n) Disable pager, print to stdout.notee (\t) Don't write into outfile.pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.print (\p) Print current command.prompt (\R) Change your mysql prompt.quit (\q) Quit mysql.rehash (\#) Rebuild completion hash.source (\.) Execute an SQL script file. Takes a file name as an argument.status (\s) Get status information from the server.system (\!) Execute a system shell command.tee (\T) Set outfile [to_outfile]. Append everything into given outfile.use (\u) Use another database. Takes database name as argument.charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.warnings (\W) Show warnings after every statement.nowarning (\w) Don't show warnings after every statement.For server side help, type 'help contents' Each command has both a long and short form. The long form is not case sensitive; the short form is. The long form can be followed by an optional semicolon terminator, but the short form should not. The use of short-form commands within multi-line /* ... */ comments is not supported. help [arg], \h [arg], \? [arg], ? [arg] Display a help message listing the available mysql commands. If you provide an argument to the help command, mysql uses it as a search string to access server-side help from the contents of the MySQL Reference Manual. For more information, see Section 4.5.1.4, "mysql Server-Side Help". charset charset_name, \C charset_name Change the default character set and issue a SET NAMES statement. This enables the character set to remain synchronized on the client and server if mysql is run with auto-reconnect enabled (which is not recommended), because the specified character set is used for reconnects. clear, \c Clear the current input. Use this if you change your mind about executing the statement that you are entering. connect [db_name host_name]], \r [db_name host_name]] Reconnect to the server. The optional database name and host name arguments may be given to specify the default database or the host where the server is running. If omitted, the current values are used. delimiter str, \d str Change the string that mysql interprets as the separator between SQL statements. The default is the semicolon character (";"). The delimiter string can be specified as an unquoted or quoted argument on the delimiter command line. Quoting can be done with either single quote ('), double quote ("), or backtick (`) characters. To include a quote within a quoted string, either quote the string with a different quote character or escape the quote with a backslash ("\") character. Backslash should be avoided outside of quoted strings because it is the escape character for MySQL. For an unquoted argument, the delimiter is read up to the first space or end of line. For a quoted argument, the delimiter is read up to the matching quote on the line. mysql interprets instances of the delimiter string as a statement delimiter anywhere it occurs, except within quoted strings. Be careful about defining a delimiter that might occur within other words. For example, if you define the delimiter as X, you will be unable to use the word INDEX in statements. mysql interprets this as INDE followed by the delimiter X. When the delimiter recognized by mysql is set to something other than the default of ";", instances of that character are sent to the server without interpretation. However, the server itself still interprets ";" as a statement delimiter and processes statements accordingly. This behavior on the server side comes into play for multiple-statement execution (see Section 22.8.13, "C API Support for Multiple Statement Execution"), and for parsing the body of stored procedures and functions, triggers, and events (see Section 19.1, "Defining Stored Programs"). edit, \e Edit the current input statement. mysql checks the values of the EDITOR and VISUAL environment variables to determine which editor to use. The default editor is vi if neither variable is set. The edit command works only in Unix. ego, \G Send the current statement to the server to be executed and display the result using vertical format. exit, \q Exit mysql. go, \g Send the current statement to the server to be executed. nopager, \n Disable output paging. See the description for pager. The nopager command works only in Unix. notee, \t Disable output copying to the tee file. See the description for tee. nowarning, \w Enable display of warnings after each statement. pager [command], \P [command] Enable output paging. By using the --pager option when you invoke mysql, it is possible to browse or search query results in interactive mode with Unix programs such as less, more, or any other similar program. If you specify no value for the option, mysql checks the value of the PAGER environment variable and sets the pager to that. Pager functionality works only in interactive mode. Output paging can be enabled interactively with the pager command and disabled with nopager. The command takes an optional argument; if given, the paging program is set to that. With no argument, the pager is set to the pager that was set on the command line, or stdout if no pager was specified. Output paging works only in Unix because it uses the popen() function, which does not exist on Windows. For Windows, the tee option can be used instead to save query output, although it is not as convenient as pager for browsing output in some situations. print, \p Print the current input statement without executing it. prompt [str], \R [str] Reconfigure the mysql prompt to the given string. The special character sequences that can be used in the prompt are described later in this section. If you specify the prompt command with no argument, mysql resets the prompt to the default of mysql>. quit, \q Exit mysql. rehash, \# Rebuild the completion hash that enables database, table, and column name completion while you are entering statements. (See the description for the --auto-rehash option.) source file_name, \. file_name Read the named file and executes the statements contained therein. On Windows, you can specify path name separators as / or \\. status, \s Provide status information about the connection and the server you are using. If you are running in --safe-updates mode, status also prints the values for the mysql variables that affect your queries. system command, \! command Execute the given command using your default command interpreter. The system command works only in Unix. tee [file_name], \T [file_name] By using the --tee option when you invoke mysql, you can log statements and their output. All the data displayed on the screen is appended into a given file. This can be very useful for debugging purposes also. mysql flushes results to the file after each statement, just before it prints its next prompt. Tee functionality works only in interactive mode. You can enable this feature interactively with the tee command. Without a parameter, the previous file is used. The tee file can be disabled with the notee command. Executing tee again re-enables logging. use db_name, \u db_name Use db_name as the default database. warnings, \W Enable display of warnings after each statement (if there are any).
Here are a few tips about the pager command: You can use it to write to a file and the results go only to the file: mysql> pager cat > /tmp/log.txt You can also pass any options for the program that you want to use as your pager: mysql> pager less -n -i -S In the preceding example, note the -S option. You may find it very useful for browsing wide query results. Sometimes a very wide result set is difficult to read on the screen. The -S option to less can make the result set much more readable because you can scroll it horizontally using the left-arrow and right-arrow keys. You can also use -S interactively within less to switch the horizontal-browse mode on and off. For more information, read the less manual page: shell> man less The -F and -X options may be used with less to cause it to exit if output fits on one screen, which is convenient when no scrolling is necessary: mysql> pager less -n -i -S -F -X You can specify very complex pager commands for handling query output: mysql> pager cat | tee /dr1/tmp/res.txt \ | tee /dr2/tmp/res2.txt | less -n -i -S In this example, the command would send query results to two files in two different directories on two different file systems mounted on /dr1 and /dr2, yet still display the results onscreen using less.
You can also combine the tee and pager functions. Have a tee file enabled and pager set to less, and you are able to browse the results using the less program and still have everything appended into a file the same time. The difference between the Unix tee used with the pager command and the mysql built-in tee command is that the built-in tee works even if you do not have the Unix tee available. The built-in tee also logs everything that is printed on the screen, whereas the Unix tee used with pager does not log quite that much. Additionally, tee file logging can be turned on and off interactively from within mysql. This is useful when you want to log some queries to a file, but not others. The prompt command reconfigures the default mysql> prompt. The string for defining the prompt can contain the following special sequences. You can set the prompt in several ways: Use an environment variable. You can set the MYSQL_PS1 environment variable to a prompt string. For example: shell> export MYSQL_PS1="(\u@\h) [\d]> " Use a command-line option. You can set the --prompt option on the command line to mysql. For example: shell> mysql --prompt="(\u@\h) [\d]> "(user@host) [database]> Use an option file. You can set the prompt option in the [mysql] group of any MySQL option file, such as /etc/my.cnf or the .my.cnf file in your home directory. For example: [mysql]prompt=(\\u@\\h) [\\d]>\\_ In this example, note that the backslashes are doubled. If you set the prompt using the prompt option in an option file, it is advisable to double the backslashes when using the special prompt options. There is some overlap in the set of permissible prompt options and the set of special escape sequences that are recognized in option files. (The rules for escape sequences in option files are listed in Section 4.2.3.3, "Using Option Files".) The overlap may cause you problems if you use single backslashes. For example, \s is interpreted as a space rather than as the current seconds value. The following example shows how to define a prompt within an option file to include the current time in HH:MM:SS> format: [mysql]prompt="\\r:\\m:\\s> " Set the prompt interactively. You can change your prompt interactively by using the prompt (or \R) command. For example: mysql> prompt (\u@\h) [\d]>\_PROMPT set to '(\u@\h) [\d]>\_'(user@host) [database]>(user@host) [database]> promptReturning to default PROMPT of mysql>mysql>
4.5.1.3. mysql History File On Unix, the mysql client writes a record of executed statements to a history file. By default, this file is named .mysql_history and is created in your home directory. To specify a different file, set the value of the MYSQL_HISTFILE environment variable. The .mysql_history should be protected with a restrictive access mode because sensitive information might be written to it, such as the text of SQL statements that contain passwords. See Section 6.1.2.1, "End-User Guidelines for Password Security". mysql does not write statements to the history file when used noninteractively (for example, when reading input from a file or a pipe). It is also possible to explicitly suppress logging of statements to the history file by using the --batch or --execute option. If you do not want to maintain a history file, first remove .mysql_history if it exists, and then use either of the following techniques: Set the MYSQL_HISTFILE variable to /dev/null. To cause this setting to take effect each time you log in, put the setting in one of your shell's startup files. Create .mysql_history as a symbolic link to /dev/null: shell> ln -s /dev/null $HOME/.mysql_history You need do this only once.
4.5.1.4. mysql Server-Side Helpmysql> help search_string If you provide an argument to the help command, mysql uses it as a search string to access server-side help from the contents of the MySQL Reference Manual. The proper operation of this command requires that the help tables in the mysql database be initialized with help topic information (see Section 5.1.10, "Server-Side Help"). If there is no match for the search string, the search fails: mysql> help meNothing foundPlease try to run 'help contents' for a list of all accessible topics Use help contents to see a list of the help categories: mysql> help contentsYou asked for help about help category: "Contents"For more information, type 'help <item>', where <item> is one of thefollowing categories: Account Management Administration Data Definition Data Manipulation Data Types Functions Functions and Modifiers for Use with GROUP BY Geographic Features Language Structure Plugins Storage Engines Stored Routines Table Maintenance Transactions Triggers If the search string matches multiple items, mysql shows a list of matching topics: mysql> help logsMany help items for your request exist.To make a more specific request, please type 'help <item>',where <item> is one of the following topics: SHOW SHOW BINARY LOGS SHOW ENGINE SHOW LOGS Use a topic as the search string to see the help entry for that topic: mysql> help show binary logsName: 'SHOW BINARY LOGS'Description:Syntax:SHOW BINARY LOGSSHOW MASTER LOGSLists the binary log files on the server. This statement is used aspart of the procedure described in [purge-binary-logs], that shows howto determine which logs can be purged.mysql> SHOW BINARY LOGS;+---------------+-----------+| Log_name | File_size |+---------------+-----------+| binlog.000015 | 724935 || binlog.000016 | 733481 |+---------------+-----------+ 4.5.1.5. Executing SQL Statements from a Text File The mysql client typically is used interactively, like this: shell> mysql db_name However, it is also possible to put your SQL statements in a file and then tell mysql to read its input from that file. To do so, create a text file text_file that contains the statements you wish to execute. Then invoke mysql as shown here: shell> mysql db_name < text_file If you place a USE db_name statement as the first statement in the file, it is unnecessary to specify the database name on the command line: shell> mysql < text_file If you are already running mysql, you can execute an SQL script file using the source command or \. command: mysql> source file_namemysql> \. file_name Sometimes you may want your script to display progress information to the user. For this you can insert statements like this: SELECT '<info_to_display>' AS ' '; The statement shown outputs <info_to_display>. You can also invoke mysql with the --verbose option, which causes each statement to be displayed before the result that it produces. mysql ignores Unicode byte order mark (BOM) characters at the beginning of input files. Previously, it read them and sent them to the server, resulting in a syntax error. Presence of a BOM does not cause mysql to change its default character set. To do that, invoke mysql with an option such as --default-character-set=utf8. For more information about batch mode, see Section 3.5, "Using mysql in Batch Mode". This section describes some techniques that can help you use mysql more effectively. 4.5.1.6.1. Displaying Query Results Vertically Some query results are much more readable when displayed vertically, instead of in the usual horizontal table format. Queries can be displayed vertically by terminating the query with \G instead of a semicolon. For example, longer text values that include newlines often are much easier to read with vertical output: mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G*************************** 1. row *************************** msg_nro: 3068 date: 2000-03-01 23:29:50time_zone: +0200mail_from: Monty reply: [email protected] mail_to: "Thimble Smith" <[email protected]> sbj: UTF-8 txt: >>>>> "Thimble" == Thimble Smith writes:Thimble> Hi. I think this is a good idea. Is anyone familiarThimble> with UTF-8 or Unicode? Otherwise, I'll put this on myThimble> TODO list and see what happens.Yes, please do that.Regards,Monty file: inbox-jani-1 hash: 1904029441 row in set (0.09 sec) For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). It is helpful for cases when you might have issued a DELETE FROM tbl_name statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table. With --safe-updates, you can delete rows only by specifying the key values that identify them. This helps prevent accidents. When you use the --safe-updates option, mysql issues the following statement when it connects to the MySQL server: SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000; See Section 5.1.4, "Server System Variables". The SET statement has the following effects: You are not permitted to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both). For example: UPDATE tbl_name SET not_key_column=val WHERE key_column=val;UPDATE tbl_name SET not_key_column=val LIMIT 1; The server limits all large SELECT results to 1,000 rows unless the statement includes a LIMIT clause. The server aborts multiple-table SELECT statements that probably need to examine more than 1,000,000 row combinations.
To specify limits different from 1,000 and 1,000,000, you can override the defaults by using the --select_limit and --max_join_size options: shell> mysql --safe-updates --select_limit=500 --max_join_size=10000 4.5.1.6.3. Disabling mysql Auto-Reconnect If the mysql client loses its connection to the server while sending a statement, it immediately and automatically tries to reconnect once to the server and send the statement again. However, even if mysql succeeds in reconnecting, your first connection has ended and all your previous session objects and settings are lost: temporary tables, the autocommit mode, and user-defined and session variables. Also, any current transaction rolls back. This behavior may be dangerous for you, as in the following example where the server was shut down and restarted between the first and second statements without you knowing it: mysql> SET @a=1;Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO t VALUES(@a);ERROR 2006: MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 1Current database: testQuery OK, 1 row affected (1.30 sec)mysql> SELECT * FROM t;+------+| a |+------+| NULL |+------+1 row in set (0.05 sec) The @a user variable has been lost with the connection, and after the reconnection it is undefined. If it is important to have mysql terminate with an error if the connection has been lost, you can start the mysql client with the --skip-reconnect option. For more information about auto-reconnect and its effect on state information when a reconnection occurs, see Section 22.8.12, "Controlling Automatic Reconnection Behavior". 4.5.2. mysqladmin - Client for Administering a MySQL Server mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more. Invoke mysqladmin like this: shell> mysqladmin [options] command [command-arg] [command [command-arg]] ... mysqladmin supports the following commands. Some of the commands take an argument following the command name. create db_name Create a new database named db_name. debug Tell the server to write debug information to the error log. This includes information about the Event Scheduler. See Section 19.4.5, "Event Scheduler Status". drop db_name Delete the database named db_name and all its tables. 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 (same as reload). flush-status Clear status variables. flush-tables Flush all tables. flush-threads Flush the thread cache. 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. (See Section 6.1.2.4, "Password Hashing in MySQL".) 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 quotation marks. On Windows, be sure to use double quotation marks rather than single quotation marks; single quotation marks are not stripped from the password, but rather are interpreted as part of the password. For example: shell> mysqladmin password "my new password" As of MySQL 5.5.3, the new password can be omitted following the password command. In this case, mysqladmin prompts for the password value, which enables you to avoid specifying the password on the command line. Omitting the password value should be done only if password is the final command on the mysqladmin command line. Otherwise, the next argument is taken as the password. Caution Do not use this command used if the server was started with the --skip-grant-tables option. No password change will be applied. This is true even if you precede the password command with flush-privileges on the same command line to re-enable the grant tables because the flush operation occurs after you connect. However, you can use mysqladmin flush-privileges to re-enable the grant table and then use a separate mysqladmin password command to change the password. ping Check whether the server is available. The return status from mysqladmin is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as Access denied, because this means that 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. (See Section 13.7.5.30, "SHOW PROCESSLIST Syntax".) 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. status Display a short server status message. stop-slave Stop replication on a slave server. variables Display the server system variables and their values. version Display version information from the server.
All commands can be shortened to any unique prefix. For example: shell> mysqladmin proc stat+----+-------+-----------+----+---------+------+-------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-------+-----------+----+---------+------+-------+------------------+| 51 | monty | localhost | | Query | 0 | | show processlist |+----+-------+-----------+----+---------+------+-------+------------------+Uptime: 1473624 Threads: 1 Questions: 39487Slow queries: 0 Opens: 541 Flush tables: 1Open tables: 19 Queries per second avg: 0.0268 The mysqladmin status command result displays the following values: 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.2.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 safemalloc, which is available only before MySQL 5.5.6. Maximum memory used The maximum amount of memory allocated directly by mysqld. This value is displayed only when MySQL has been compiled with safemalloc, which is available only before MySQL 5.5.6.
If you execute mysqladmin shutdown when connecting to a local server using a Unix socket file, mysqladmin waits until the server's process ID file has been removed, to ensure that the server has stopped properly. mysqladmin supports the following options, which can be specified on the command line or in the [mysqladmin] and [client] groups of an option file. mysqladmin also supports the options for processing option files described at Section 4.2.3.4, "Command-Line Options that Affect Option-File Handling". Table 4.3. mysqladmin Options --help, -? Display a help message and exit. --bind-address=ip_address On a computer having multiple network interfaces, this option can be used to select which interface is employed when connecting to the MySQL server. This option is supported only in the version of mysqladmin that is supplied with MySQL Cluster. It is not available in standard MySQL Server 5.5 releases. --character-sets-dir=path The directory where character sets are installed. See Section 10.5, "Character Set Configuration". --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 if the --sleep option is given. --debug[=debug_options], -# [debug_options] Write a debugging log. A typical debug_options string is 'd:t:o,file_name'. The default is 'd:t:o,/tmp/mysqladmin.trace'. --debug-check Print some debugging information when the program exits. --debug-info Print debugging information and memory and CPU usage statistics when the program exits. --default-auth=plugin The client-side authentication plugin to use. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.9. --default-character-set=charset_name Use charset_name as the default character set. See Section 10.5, "Character Set Configuration". --enable-cleartext-plugin Enable the mysql_clear_password cleartext authentication plugin. (See Section 6.3.6.4, "The Cleartext Client-Side Authentication Plugin".) This option was added in MySQL 5.5.27. --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. --no-beep, -b Suppress the warning beep that is emitted by default for errors such as a failure to connect to the server. --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, mysqladmin prompts for one. Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, "End-User Guidelines for Password Security". You can use an option file to avoid giving the password on the command line. --pipe, -W On Windows, connect to the server using a named pipe. This option applies only if the server supports named-pipe connections. --plugin-dir=path The directory in which to look for plugins. It may be necessary to specify this option if the --default-auth option is used to specify an authentication plugin but mysqladmin does not find it. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.9. --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 for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For details on the permissible values, see Section 4.2.2, "Connecting to the MySQL Server". --relative, -r Show the difference between the current and previous values when used with the --sleep option. 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. If --count is not given, mysqladmin executes commands indefinitely until interrupted. --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 using SSL and indicate where to find SSL keys and certificates. See Section 6.3.8.4, "SSL Command Options". --user=user_name, -u user_name The MySQL user name 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.
You can also set the following variables by using --var_name=value The --set-variable format is deprecated and was removed in MySQL 5.5.3. syntax: 4.5.3. mysqlcheck - A Table Maintenance Program The mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables. Each table is locked and therefore unavailable to other sessions while it is being processed, although for check operations, the table is locked with a READ lock only (see Section 13.3.5, "LOCK TABLES and UNLOCK TABLES Syntax", for more information about READ and WRITE locks). Table maintenance operations can be time-consuming, particularly for large tables. If you use the --databases or --all-databases option to process all tables in one or more databases, an invocation of mysqlcheck might take a long time. (This is also true for mysql_upgrade because that program invokes mysqlcheck to check all tables and repair them if necessary.) mysqlcheck is similar in function to myisamchk, but works differently. The main operational difference is that mysqlcheck must be used when the mysqld server is running, whereas myisamchk should be used when it is not. The benefit of using mysqlcheck is that you do not have to stop the server to perform table maintenance. mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user. It determines which statements to use for the operation you want to perform, and then sends the statements to the server to be executed. For details about which storage engines each statement works with, see the descriptions for those statements in Section 13.7.2, "Table Maintenance Statements". The MyISAM storage engine supports all four maintenance operations, so mysqlcheck can be used to perform any of them on MyISAM tables. Other storage engines do not necessarily support all operations. In such cases, an error message is displayed. For example, if test.t is a MEMORY table, an attempt to check it produces this result: shell> mysqlcheck test ttest.tnote : The storage engine for the table doesn't support check If mysqlcheck is unable to repair a table, see Section 2.12.4, "Rebuilding or Repairing Tables or Indexes" for manual table repair strategies. This will be the case, for example, for InnoDB tables, which can be checked with CHECK TABLE, but not repaired with REPAIR TABLE. Caution It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to file system errors. There are three general ways to invoke mysqlcheck: shell> mysqlcheck [options] db_name [tbl_name ...]shell> mysqlcheck [options] --databases db_name ...shell> mysqlcheck [options] --all-databases If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are checked. mysqlcheck has a special feature compared to other client programs. The default behavior of checking tables (--check) can be changed by renaming the binary. If you want to have a tool that repairs tables by default, you should just make a copy of mysqlcheck named mysqlrepair, or make a symbolic link to mysqlcheck named mysqlrepair. If you invoke mysqlrepair, it repairs tables. The names shown in the following table can be used to change mysqlcheck default behavior. mysqlcheck supports the following options, which can be specified on the command line or in the [mysqlcheck] and [client] groups of an option file. mysqlcheck also supports the options for processing option files described at Section 4.2.3.4, "Command-Line Options that Affect Option-File Handling". Table 4.4. mysqlcheck Options --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. --auto-repair If a checked table is corrupted, automatically fix it. Any necessary repairs are done after all tables have been checked. --bind-address=ip_address On a computer having multiple network interfaces, this option can be used to select which interface is employed when connecting to the MySQL server. This option is supported only in the version of mysqlcheck that is supplied with MySQL Cluster. It is not available in standard MySQL Server 5.5 releases. --character-sets-dir=path The directory where character sets are installed. See Section 10.5, "Character Set Configuration". --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. --check-upgrade, -g Invoke CHECK TABLE with the FOR UPGRADE option to check tables for incompatibilities with the current version of the server. This option automatically enables the --fix-db-names and --fix-table-names options. --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 'd:t:o,file_name'. The default is 'd:t:o'. --debug-check Print some debugging information when the program exits. --debug-info Print debugging information and memory and CPU usage statistics when the program exits. --default-character-set=charset_name Use charset_name as the default character set. See Section 10.5, "Character Set Configuration". --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! --default-auth=plugin The client-side authentication plugin to use. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.10. --fast, -F Check only tables that have not been closed properly. --fix-db-names Convert database names to 5.1 format. Only database names that contain special characters are affected. --fix-table-names Convert table names to 5.1 format. Only table names that contain special characters are affected. This option also applies to views. --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, mysqlcheck prompts for one. Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, "End-User Guidelines for Password Security". You can use an option file to avoid giving the password on the command line. --pipe, -W On Windows, connect to the server using a named pipe. This option applies only if the server supports named-pipe connections. --plugin-dir=path The directory in which to look for plugins. It may be necessary to specify this option if the --default-auth option is used to specify an authentication plugin but mysqlcheck does not find it. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.10. --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 for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For details on the permissible values, see Section 4.2.2, "Connecting to the MySQL Server". --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 using SSL and indicate where to find SSL keys and certificates. See Section 6.3.8.4, "SSL Command Options". --tables Override 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. --user=user_name, -u user_name The MySQL user name 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. --write-binlog This option is enabled by default, so that ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements generated by mysqlcheck are written to the binary log. Use --skip-write-binlog to cause NO_WRITE_TO_BINLOG to be added to the statements so that they are not logged. Use the --skip-write-binlog when these statements should not be sent to replication slaves or run when using the binary logs for recovery from backup.
4.5.4. mysqldump - A Database Backup Program The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format. mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions. If you are doing a backup on the server and your tables all are MyISAM tables, consider using the mysqlhotcopy instead because it can accomplish faster backups and faster restores. See Section 4.6.9, "mysqlhotcopy - A Database Backup Program". There are three general ways to invoke mysqldump: shell> mysqldump [options] db_name [tbl_name ...]shell> mysqldump [options] --databases db_name ...shell> mysqldump [options] --all-databases If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped. mysqldump does not dump the INFORMATION_SCHEMA database by default. mysqldump dumps INFORMATION_SCHEMA only if you name it explicitly on the command line, although currently you must also use the --skip-lock-tables option. Before MySQL 5.5 mysqldump silently ignores INFORMATION_SCHEMA even if you name it explicitly on the command line. mysqldump does not dump the performance_schema database. Before MySQL 5.5.25, mysqldump does not dump the general_log or slow_query_log tables for dumps of the mysql database. As of 5.5.25, the dump includes statements to recreate those tables so that they are not missing after reloading the dump file. Log table contents are not dumped. mysqldump also does not dump the MySQL Cluster ndbinfo information database. To see a list of the options your version of mysqldump supports, execute mysqldump --help. Some mysqldump options are shorthand for groups of other options: Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default. Use of --compact is the same as specifying --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset options.
To reverse the effect of a group option, uses its --skip-xxx form (--skip-opt or --skip-compact). It is also possible to select only part of the effect of a group option by following it with options that enable or disable specific features. Here are some examples: When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example, --disable-keys --lock-tables --skip-opt would not have the intended effect; it is the same as --skip-opt by itself. mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt, which enables --quick). The --opt option (and hence --quick) is enabled by default, so to enable memory buffering, use --skip-quick. If you are using a recent version of mysqldump to generate a dump to be reloaded into a very old MySQL server, you should not use the --opt or --extended-insert option. Use --skip-opt instead. For additional information about mysqldump, see Section 7.4, "Using mysqldump for Backups". mysqldump supports the following options, which can be specified on the command line or in the [mysqldump] and [client] groups of an option file. mysqldump also supports the options for processing option files described at Section 4.2.3.4, "Command-Line Options that Affect Option-File Handling". Table 4.5. mysqldump Options --help, -? Display a help message and exit. --add-drop-database Add a DROP DATABASE statement before each CREATE DATABASE statement. This option is typically used in conjunction with the --all-databases or --databases option because no CREATE DATABASE statements are written unless one of those options is specified. --add-drop-table Add a DROP TABLE statement before each CREATE TABLE statement. --add-drop-trigger Add a DROP TRIGGER statement before each CREATE TRIGGER statement. Note This option is supported only by mysqldump as supplied with MySQL Cluster. It is not available when using MySQL Server 5.5. --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 8.2.2.1, "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. --all-tablespaces, -Y Adds to a table dump all SQL statements needed to create any tablespaces used by an NDBCLUSTER table. This information is not otherwise included in the output from mysqldump. This option is currently relevant only to MySQL Cluster tables. --allow-keywords Permit creation of column names that are keywords. This works by prefixing each column name with the table name. --apply-slave-statements For a slave dump produced with the --dump-slave option, add a STOP SLAVE statement before the CHANGE MASTER TO statement and a START SLAVE statement at the end of the output. This option was added in MySQL 5.5.3. --bind-address=ip_address On a computer having multiple network interfaces, this option can be used to select which interface is employed when connecting to the MySQL server. This option is supported only in the version of mysqldump that is supplied with MySQL Cluster. It is not available in standard MySQL Server 5.5 releases. --character-sets-dir=path The directory where character sets are installed. See Section 10.5, "Character Set Configuration". --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. --compact Produce more compact output. This option enables the --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset options. --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.7, "Server 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. --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. A typical debug_options string is 'd:t:o,file_name'. The default value is 'd:t:o,/tmp/mysqldump.trace'. --debug-check Print some debugging information when the program exits. --debug-info Print debugging information and memory and CPU usage statistics when the program exits. --default-auth=plugin The client-side authentication plugin to use. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.9. --default-character-set=charset_name Use charset_name as the default character set. See Section 10.5, "Character Set Configuration". If no character set is specified, mysqldump 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 by sending a PURGE BINARY LOGS statement to the server after performing the dump operation. This option automatically enables --master-data. --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 faster because the indexes are created after all rows are inserted. This option is effective only for nonunique indexes of MyISAM tables. It has no effect for other tables. --dump-date If the --comments option is given, mysqldump produces a comment at the end of the dump of the following form: -- Dump completed on DATE However, the date causes dump files taken at different times to appear to be different, even if the data are otherwise identical. --dump-date and --skip-dump-date control whether the date is added to the comment. The default is --dump-date (include the date in the comment). --skip-dump-date suppresses date printing. --dump-slave[=value] This option is similar to --master-data except that it is used to dump a replication slave server to produce a dump file that can be used to set up another server as a slave that has the same master as the dumped server. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped slave's master (rather than the coordinates of the dumped server, as is done by the --master-data option). These are the master server coordinates from which the slave should start replicating. This option was added in MySQL 5.5.3. The option value is handled the same way as for --master-data and has the same effect as --master-data in terms of enabling or disabling other options and in how locking is handled. In conjunction with --dump-slave, the --apply-slave-statements and --include-master-host-port options can also be used. --events, -E Include Event Scheduler events for the dumped databases in the output. --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 --tab option and have the same meaning as the corresponding FIELDS clauses for LOAD DATA INFILE. See Section 13.2.6, "LOAD DATA INFILE Syntax". --first-slave Deprecated. Use --lock-all-tables instead. --first-slave was removed in MySQL 5.5.3. --flush-logs, -F Flush the MySQL server log files before starting the dump. This option requires the RELOAD privilege. If you use this option in combination with the --all-databases 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. --flush-privileges Send a FLUSH PRIVILEGES statement to the server after dumping the mysql database. This option should be used any time the dump contains the mysql database and any other database that depends on the data in the mysql database for proper restoration. --force, -f Continue even if an SQL error occurs during a table dump. One use for this option is to cause mysqldump to continue executing even when it encounters a view that has become invalid because the definition refers to a table that has been dropped. Without --force, mysqldump exits with an error message. With --force, mysqldump prints the error message, but it also writes an SQL comment containing the view definition to the dump output and continues executing. --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, the BLOB types, and BIT. --include-master-host-port For the CHANGE MASTER TO statement in a slave dump produced with the --dump-slave option, add MASTER_PORT and MASTER_PORT options for the host name and TCP/IP port number of the slave's master. This option was added in MySQL 5.5.3. --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 also can be used to ignore views. --insert-ignore Write INSERT IGNORE statements rather than INSERT statements. --lines-terminated-by=... This option is used with the --tab option and has the same meaning as the corresponding LINES clause for LOAD DATA INFILE. See Section 13.2.6, "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. --lock-tables, -l For each dumped database, lock all tables to be dumped before dumping them. The tables are locked with READ LOCAL to permit concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB, --single-transaction is a much better option than --lock-tables because it does not need to lock the tables at all. Because --lock-tables locks tables for each database separately, 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. --log-error=file_name Log warnings and errors by appending them to the named file. The default is to do no logging. --master-data[=value] Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave. If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1. This option requires the RELOAD privilege and the binary log must be enabled. 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 the description for --single-transaction). In all cases, any action on logs happens at the exact moment of the dump. It is also possible to set up a slave by dumping an existing slave of the master. To do this, use the following procedure on the existing slave: Stop the slave's SQL thread and get its current status: mysql> STOP SLAVE SQL_THREAD;mysql> SHOW SLAVE STATUS; From the output of the SHOW SLAVE STATUS statement, the binary log coordinates of the master server from which the new slave should start replicating are the values of the Relay_Master_Log_File and Exec_Master_Log_Pos fields. Denote those values as file_name and file_pos. Dump the slave server: shell> mysqldump --master-data=2 --all-databases > dumpfile Using --master-data=2 works only if binary logging has been enabled on the slave. Otherwise, mysqldump fails with the error Binlogging on server not active. In this case you must handle any locking issues in another manner, using one or more of --add-locks, --lock-tables, --lock-all-tables, or --single-transaction, as required by your application and environment. Restart the slave: mysql> START SLAVE; On the new slave, load the dump file: shell> mysql < dumpfile On the new slave, set the replication coordinates to those of the master server obtained earlier: mysql> CHANGE MASTER TO -> MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = file_pos; The CHANGE MASTER TO statement might also need other parameters, such as MASTER_HOST to point the slave to the correct master server host. Add any such parameters as necessary.
--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. Note This option does not not exclude statements creating log file groups or tablespaces from mysqldump output; however, you can use the --no-tablespaces option for this purpose. --no-data, -d Do not write any table row information (that is, do not dump table contents). This is useful if you want to dump only the CREATE TABLE statement for the table (for example, to create an empty copy of the table by loading the dump file). --no-set-names, -N This has the same effect as --skip-set-charset. --no-tablespaces, -y This option suppresses all CREATE LOGFILE GROUP and CREATE TABLESPACE statements in the output of mysqldump. --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. The --opt option 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 a subset of the options affected by --opt. --order-by-primary Dump each table's rows sorted 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 operation take considerably longer. --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, mysqldump prompts for one. Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, "End-User Guidelines for Password Security". You can use an option file to avoid giving the password on the command line. --pipe, -W On Windows, connect to the server using a named pipe. This option applies only if the server supports named-pipe connections. --plugin-dir=path The directory in which to look for plugins. It may be necessary to specify this option if the --default-auth option is used to specify an authentication plugin but mysqldump does not find it. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.9. --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 for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For details on the permissible values, see Section 4.2.2, "Connecting to the MySQL Server". --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 identifiers (such as database, table, and column names) within "`" characters. If the ANSI_QUOTES SQL mode is enabled, identifiers are quoted within """ characters. This option 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. --replace Write REPLACE statements rather than INSERT statements. --result-file=file_name, -r file_name 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 previous contents overwritten, even if an error occurs while generating the dump. --routines, -R Include stored routines (procedures and functions) for the dumped databases in the output. Use of this option requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time. If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead, dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the mysql database. Prior to MySQL 5.5.21, this option had no effect when used together with the --xml option. (Bug #11760384, Bug #52792) --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. --single-transaction This option sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, 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. While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE 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 that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail. The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly. 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 the --single-transaction 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 using SSL and indicate where to find SSL keys and certificates. See Section 6.3.8.4, "SSL Command Options". --tab=path, -T path Produce tab-separated text-format data files. For each dumped table, mysqldump creates a tbl_name.sql file that contains the CREATE TABLE statement that creates the table, and the server writes a tbl_name.txt file that contains its data. The option value is the directory in which to write the files. Note This option should be used only when mysqldump is run on the same machine as the mysqld server. You must have the FILE privilege, and the server must have permission to write files in the directory that you specify. 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. Column values are converted to the character set specified by the --default-character-set option. --tables Override the --databases or -B option. mysqldump regards all name arguments following the option as table names. --triggers Include triggers for each dumped table in the output. This option is enabled by default; disable it with --skip-triggers. --tz-utc This option enables TIMESTAMP columns to be dumped and reloaded between servers in different time zones. mysqldump sets its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the dump file. Without this option, TIMESTAMP columns are dumped and reloaded in the time zones local to the source and destination servers, which can cause the values to change if the servers are in different time zones. --tz-utc also protects against changes due to daylight saving time. --tz-utc is enabled by default. To disable it, use --skip-tz-utc. --user=user_name, -u user_name The MySQL user name 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. NULL, 'NULL', and Empty Values: For a column named column_name, the NULL value, an empty string, and the string value 'NULL' are distinguished from one another in the output generated by this option as follows. The output from the mysql client when run using the --xml option also follows the preceding rules. (See Section 4.5.1.1, "mysql Options".) XML output from mysqldump includes the XML namespace, as shown here: shell> mysqldump --xml -u root world City<?xml version="1.0"?><mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><database name="world"><table_structure name="City"><field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" /><field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" /><field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" /><field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" /><field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" /><key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" /><options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"Index_length="43008" Data_free="0" Auto_increment="4080"Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"Collation="latin1_swedish_ci" Create_options="" Comment="" /></table_structure><table_data name="City"><row><field name="ID">1</field><field name="Name">Kabul</field><field name="CountryCode">AFG</field><field name="District">Kabol</field><field name="Population">1780000</field></row>...<row><field name="ID">4079</field><field name="Name">Rafah</field><field name="CountryCode">PSE</field><field name="District">Rafah</field><field name="Population">92020</field></row></table_data></database></mysqldump> Prior to MySQL 5.5.21, this option prevented the --routines option from working correctly-that is, no stored routines, triggers, or events could be dumped in XML format. (Bug #11760384, Bug #52792)
You can also set the following variables by using --var_name=value syntax: max_allowed_packet The maximum size of the buffer for client/server communication. The maximum is 1GB. net_buffer_length The initial size of the buffer for client/server communication. When creating multiple-row INSERT statements (as with the --extended-insert or --opt option), 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. Some options, such as --opt, automatically enable --lock-tables. If you want to override this, use --skip-lock-tables at the end of the option list.
A common use of mysqldump is for making a backup of an entire database: shell> mysqldump db_name > backup-file.sql You can load the dump file back into the server like this: shell> mysql db_name < backup-file.sql Or like this: shell> mysql -e "source /path-to-backup/backup-file.sql" db_name mysqldump is also very useful for populating databases by copying data from one MySQL server to another: shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name It is possible to dump several databases with one command: shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql To dump all databases, use the --all-databases option: shell> mysqldump --all-databases > all_databases.sql For InnoDB tables, mysqldump provides a way of making an online backup: shell> mysqldump --all-databases --single-transaction > all_databases.sql This backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the MySQL server may get stalled until those statements finish. After that, the dump becomes lock free and does not disturb reads and writes on the tables. If the update statements that the MySQL server receives are short (in terms of execution time), the initial lock period should not be noticeable, even with many updates. For point-in-time recovery (also known as "roll-forward," when you need to restore an old backup and replay the changes that happened since that backup), it is often useful to rotate the binary log (see Section 5.2.4, "The Binary Log") or at least know the binary log coordinates to which the dump corresponds: shell> mysqldump --all-databases --master-data=2 > all_databases.sql Or: shell> mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql The --master-data and --single-transaction options can be used simultaneously, which provides a convenient way to make an online backup suitable for use prior to point-in-time recovery if tables are stored using the InnoDB storage engine. For more information on making backups, see Section 7.2, "Database Backup Methods", and Section 7.3, "Example Backup and Recovery Strategy". If you encounter problems backing up views, please read the section that covers restrictions on views which describes a workaround for backing up views when this fails due to insufficient privileges. See Section E.5, "Restrictions on Views". 4.5.5. mysqlimport - A Data Import Program The mysqlimport client provides a command-line interface to the LOAD DATA INFILE SQL statement. Most options to mysqlimport correspond directly to clauses of LOAD DATA INFILE syntax. See Section 13.2.6, "LOAD DATA INFILE Syntax". Invoke mysqlimport like this: shell> mysqlimport [options] db_name textfile1 [textfile2 ...] For each text file named on the command line, mysqlimport strips any extension from the file name and uses the result to determine the name of the table into which to import the file's contents. For example, files named patient.txt, patient.text, and patient all would be imported into a table named patient. For additional information about mysqldump, see Section 7.4, "Using mysqldump for Backups". mysqlimport supports the following options, which can be specified on the command line or in the [mysqlimport] and [client] groups of an option file. mysqlimport also supports the options for processing option files described at Section 4.2.3.4, "Command-Line Options that Affect Option-File Handling". Table 4.6. mysqlimport Options --help, -? Display a help message and exit. --bind-address=ip_address On a computer having multiple network interfaces, this option can be used to select which interface is employed when connecting to the MySQL server. This option is supported only in the version of mysqlimport that is supplied with MySQL Cluster. It is not available in standard MySQL Server 5.5 releases. --character-sets-dir=path The directory where character sets are installed. See Section 10.5, "Character Set Configuration". --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. A typical debug_options string is 'd:t:o,file_name'. The default is 'd:t:o'. --debug-check Print some debugging information when the program exits. --debug-info Print debugging information and memory and CPU usage statistics when the program exits. --default-character-set=charset_name Use charset_name as the default character set. See Section 10.5, "Character Set Configuration". --default-auth=plugin The client-side authentication plugin to use. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.10. --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 13.2.6, "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 13.2.6, "LOAD DATA INFILE Syntax". --local, -L Read input files locally from the client host. --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 (such as MyISAM, MEMORY, and 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, mysqlimport prompts for one. Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, "End-User Guidelines for Password Security". You can use an option file to avoid giving the password on the command line. --pipe, -W On Windows, connect to the server using a named pipe. This option applies only if the server supports named-pipe connections. --plugin-dir=path The directory in which to look for plugins. It may be necessary to specify this option if the --default-auth option is used to specify an authentication plugin but mysqlimport does not find it. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.10. --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 for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For details on the permissible values, see Section 4.2.2, "Connecting to the MySQL Server". --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 using SSL and indicate where to find SSL keys and certificates. See Section 6.3.8.4, "SSL Command Options". --user=user_name, -u user_name The MySQL user name to use when connecting to the server. --use-threads=N Load files in parallel using N threads. --verbose, -v Verbose mode. Print more information about what the program does. --version, -V Display version information and exit.
Here is a sample session that demonstrates use of mysqlimport: shell> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' testshell> eda100 Max Sydow101 Count Dracula.w imptest.txt32qshell> od -c imptest.txt0000000 1 0 0 \t M a x S y d o w \n 1 00000020 1 \t C o u n t D r a c u l a \n0000040shell> mysqlimport --local test imptest.txttest.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0shell> mysql -e 'SELECT * FROM imptest' test+------+---------------+| id | n |+------+---------------+| 100 | Max Sydow || 101 | Count Dracula |+------+---------------+ 4.5.6. mysqlshow - Display Database, Table, and Column Information The mysqlshow client can be used to quickly see which databases exist, their tables, or a table's columns or indexes. mysqlshow provides a command-line interface to several SQL SHOW statements. See Section 13.7.5, "SHOW Syntax". The same information can be obtained by using those statements directly. For example, you can issue them from the mysql client program. Invoke mysqlshow like this: shell> mysqlshow [options] [db_name [tbl_name [col_name]]] 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.
The output displays only the names of those databases, tables, or columns for which you have some privileges. If the last argument contains shell or SQL wildcard characters ("*", "?", "%", or "_"), only those names that are matched by the wildcard are shown. If a database name contains any underscores, those should be escaped with a backslash (some Unix shells require two) to get a list of the proper tables or columns. "*" and "?" characters are converted into SQL "%" and "_" wildcard characters. This might cause some confusion when you try to display the columns for a table with a "_" in the name, because in this case, mysqlshow shows you only the table names that match the pattern. This is easily fixed by adding an extra "%" last on the command line as a separate argument. mysqlshow supports the following options, which can be specified on the command line or in the [mysqlshow] and [client] groups of an option file. mysqlshow also supports the options for processing option files described at Section 4.2.3.4, "Command-Line Options that Affect Option-File Handling". Table 4.7. mysqlshow Options --help, -? Display a help message and exit. --bind-address=ip_address On a computer having multiple network interfaces, this option can be used to select which interface is employed when connecting to the MySQL server. This option is supported only in the version of mysqlshow that is supplied with MySQL Cluster. It is not available in standard MySQL Server 5.5 releases. --character-sets-dir=path The directory where character sets are installed. See Section 10.5, "Character Set Configuration". --compress, -C Compress all information sent between the client and the server if both support compression. --count Show the number of rows per table. This can be slow for non-MyISAM tables. --debug[=debug_options], -# [debug_options] Write a debugging log. A typical debug_options string is 'd:t:o,file_name'. The default is 'd:t:o'. --debug-check Print some debugging information when the program exits. --debug-info Print debugging information and memory and CPU usage statistics when the program exits. --default-character-set=charset_name Use charset_name as the default character set. See Section 10.5, "Character Set Configuration". --default-auth=plugin The client-side authentication plugin to use. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.10. --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, mysqlshow prompts for one. Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, "End-User Guidelines for Password Security". You can use an option file to avoid giving the password on the command line. --pipe, -W On Windows, connect to the server using a named pipe. This option applies only if the server supports named-pipe connections. --plugin-dir=path The directory in which to look for plugins. It may be necessary to specify this option if the --default-auth option is used to specify an authentication plugin but mysqlshow does not find it. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.10. --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 for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For details on the permissible values, see Section 4.2.2, "Connecting to the MySQL Server". --show-table-type, -t Show a column indicating the table type, as in SHOW FULL TABLES. The type is BASE TABLE or VIEW. --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 using SSL and indicate where to find SSL keys and certificates. See Section 6.3.8.4, "SSL Command Options". --status, -i Display extra information about each table. --user=user_name, -u user_name The MySQL user name 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.
4.5.7. mysqlslap - Load Emulation Client mysqlslap is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server. Invoke mysqlslap like this: shell> mysqlslap [options] Some options such as --create or --query enable you to specify a string containing an SQL statement or a file containing statements. If you specify a file, by default it must contain one statement per line. (That is, the implicit statement delimiter is the newline character.) Use the --delimiter option to specify a different delimiter, which enables you to specify statements that span multiple lines or place multiple statements on a single line. You cannot include comments in a file; mysqlslap does not understand them. mysqlslap runs in three stages: Create schema, table, and optionally any stored programs or data to use for the test. This stage uses a single client connection. Run the load test. This stage can use many client connections. Clean up (disconnect, drop table if specified). This stage uses a single client connection.
Examples: Supply your own create and query SQL statements, with 50 clients querying and 200 selects for each (enter the command on a single line): mysqlslap --delimiter=";" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FROM a" --concurrency=50 --iterations=200 Let mysqlslap build the query SQL statement with a table of two INT columns and three VARCHAR columns. Use five clients querying 20 times each. Do not create the table or insert the data (that is, use the previous test's schema and data): mysqlslap --concurrency=5 --iterations=20 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql Tell the program to load the create, insert, and query SQL statements from the specified files, where the create.sql file has multiple table creation statements delimited by ';' and multiple insert statements delimited by ';'. The --query file will have multiple queries delimited by ';'. Run all the load statements, then run all the queries in the query file with five clients (five times each): mysqlslap --concurrency=5 --iterations=5 --query=query.sql --create=create.sql --delimiter=";" mysqlslap supports the following options, which can be specified on the command line or in the [mysqlslap] and [client] groups of an option file. mysqlslap also supports the options for processing option files described at Section 4.2.3.4, "Command-Line Options that Affect Option-File Handling". Table 4.8. mysqlslap Options --help, -? Display a help message and exit. --auto-generate-sql, -a Generate SQL statements automatically when they are not supplied in files or using command options. --auto-generate-sql-add-autoincrement Add an AUTO_INCREMENT column to automatically generated tables. --auto-generate-sql-execute-number=N Specify how many queries to generate automatically. --auto-generate-sql-guid-primary Add a GUID-based primary key to automatically generated tables. --auto-generate-sql-load-type=type Specify the test load type. The permissible values are read (scan tables), write (insert into tables), key (read primary keys), update (update primary keys), or mixed (half inserts, half scanning selects). The default is mixed. --auto-generate-sql-secondary-indexes=N Specify how many secondary indexes to add to automatically generated tables. By default, none are added. --auto-generate-sql-unique-query-number=N How many different queries to generate for automatic tests. For example, if you run a key test that performs 1000 selects, you can use this option with a value of 1000 to run 1000 unique queries, or with a value of 50 to perform 50 different selects. The default is 10. --auto-generate-sql-unique-write-number=N How many different queries to generate for --auto-generate-sql-write-number. The default is 10. --auto-generate-sql-write-number=N How many row inserts to perform on each thread. The default is 100. --commit=N How many statements to execute before committing. The default is 0 (no commits are done). --compress, -C Compress all information sent between the client and the server if both support compression. --concurrency=N, -c N The number of clients to simulate when issuing the SELECT statement. --create=value The file or string containing the statement to use for creating the table. --create-and-drop-schema=value The schema in which to run the tests. mysqlslap drops the schema at the end of the test run. This option was added in MySQL 5.5.12. --create-schema=value The schema in which to run the tests. --csv[=file_name] Generate output in comma-separated values format. The output goes to the named file, or to the standard output if no file is given. --debug[=debug_options], -# [debug_options] Write a debugging log. A typical debug_options string is 'd:t:o,file_name'. The default is 'd:t:o,/tmp/mysqlslap.trace'. --debug-check Print some debugging information when the program exits. --debug-info, -T Print debugging information and memory and CPU usage statistics when the program exits. --default-auth=plugin The client-side authentication plugin to use. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.10. --delimiter=str, -F str The delimiter to use in SQL statements supplied in files or using command options. --detach=N Detach (close and reopen) each connection after each N statements. The default is 0 (connections are not detached). --enable-cleartext-plugin Enable the mysql_clear_password cleartext authentication plugin. (See Section 6.3.6.4, "The Cleartext Client-Side Authentication Plugin".) This option was added in MySQL 5.5.27. --engine=engine_name, -e engine_name The storage engine to use for creating tables. --host=host_name, -h host_name Connect to the MySQL server on the given host. --iterations=N, -i N The number of times to run the tests. --number-char-cols=N, -x N The number of VARCHAR columns to use if --auto-generate-sql is specified. --number-int-cols=N, -y N The number of INT columns to use if --auto-generate-sql is specified. --number-of-queries=N Limit each client to approximately this many queries. Query counting takes into account the statement delimiter. For example, if you invoke mysqlslap as follows, the ; delimiter is recognized so that each instance of the query string counts as two queries. As a result, 5 rows (not 10) are inserted. shell> mysqlslap --delimiter=";" --number-of-queries=10 --query="use test;insert into t values(null)" --only-print Do not connect to databases. mysqlslap only prints what it would have done. --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, mysqlslap prompts for one. Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, "End-User Guidelines for Password Security". You can use an option file to avoid giving the password on the command line. --pipe, -W On Windows, connect to the server using a named pipe. This option applies only if the server supports named-pipe connections. --plugin-dir=path The directory in which to look for plugins. It may be necessary to specify this option if the --default-auth option is used to specify an authentication plugin but mysqlslap does not find it. See Section 6.3.6, "Pluggable Authentication". This option was added in MySQL 5.5.10. --port=port_num, -P port_num The TCP/IP port number to use for the connection. --post-query=value The file or string containing the statement to execute after the tests have completed. This execution is not counted for timing purposes. --shared-memory-base-name=name On Windows, the shared-memory name to use, for connections made using shared memory to a local server. This option applies only if the server supports shared-memory connections. --post-system=str The string to execute using system() after the tests have completed. This execution is not counted for timing purposes. --pre-query=value The file or string containing the statement to execute before running the tests. This execution is not counted for timing purposes. --pre-system=str The string to execute using system() before running the tests. This execution is not counted for timing purposes. --protocol={TCP|SOCKET|PIPE|MEMORY} The connection protocol to use for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For details on the permissible values, see Section 4.2.2, "Connecting to the MySQL Server". --query=value, -q value The file or string containing the SELECT statement to use for retrieving data. --silent, -s Silent mode. No output. --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 using SSL and indicate where to find SSL keys and certificates. See Section 6.3.8.4, "SSL Command Options". --user=user_name, -u user_name The MySQL user name 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.
Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
| |
