mysql - the MySQL command-line tool
mysql [options] db_name
a SQL shell (with GNU readline capabilities). supports interactive and non-interactive use.
> mysql db_name
Or:
> mysql --user=user_name --password=your_password db_name
Then type an SQL statement, end it with \g, or \G and press Enter.
Execute SQL statements in a script file (batch file)
>mysql db_name script.sql output.tab
--host=host_name | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
History file
On Unix, mysql writes statements to .mysql_historyi.
To specify a different file, set MYSQL_HISTFILE environment variable.
To disable this first remove .mysql_history and either
Set MYSQL_HISTFILE to /dev/null or
Create .mysql_history as a symbolic link to /dev/null:
shell> ln -s /dev/null $HOME/.mysql_history
These are commands that mysql itself processes.
warnings |
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.
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 the section called "MYSQL SERVER-SIDE HELP".
In the delimiter command, you should avoid the use of the backslash ('\') character because that is the escape character for MySQL.
The edit, nopager, pager, and system commands work only in Unix.
The status command provides some 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.
To log queries and their output, use the tee command. All the data displayed on the screen is appended into a given file. This can be very useful for debugging purposes also. You can enable this feature on the command line with the --tee option, or interactively with the tee command. The tee file can be disabled interactively with the notee command. Executing tee again re-enables logging. Without a parameter, the previous file is used. Note that tee flushes query results to the file after each statement, just before mysql prints its next prompt.
By using the --pager option, 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. 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 this is not as convenient as pager for browsing output in some situations.
Here are a few tips about the pager command:
o 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
o 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
o 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 filesystems mounted on /dr1 and /dr2, yet still display the results onscreen via 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 default mysql> prompt can be reconfigured. The string for defining the prompt can contain the following special sequences: |Option | Description |\t | A tab character T}:T{ A space (a space follows the backslash) \_ | A space \R | The current time, in 24-hour military time (0-23) \r | The current time, standard 12-hour time (1-12) \m | Minutes of the current time \y | The current year, two digits \Y | The current year, four digits \D | The full current date \s | Seconds of the current time \v | The server version \w | The current day of the week in three-letter format (Mon, Tue, ...) \P | am/pm \o | The current month in numeric format \O | The current month in three-letter format (Jan, Feb, ...) \c | A counter that increments for each statement you issue \S | Semicolon \' | apostrophe \" | Double quote \d | The default database \h | The server host \p | The current TCP/IP port or socket file \u | Your username \U | Your full user_name@host_name | account \T}:T{ A literal '\' backslash character | A newline character '\' followed by any other letter just becomes that letter.
If you specify the prompt command with no argument, mysql resets the prompt to the default of mysql>.
Set the prompt using : o 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]> " o 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]> o 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 allowable prompt options and the set of special escape sequences that are recognized in option files. (These sequences are listed in Section 3.2, "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> " o 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]> prompt Returning to default PROMPT of mysql> mysql> o
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. This requires that the help tables in the mysql database be initialized with
help topic information (see the section called "SERVER-SIDE HELP").
to see a list of the help categories:
mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help - ', where
- is one of the following categories:
Account Management
Administration
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Language Structure
Storage Engines
Table Maintenance
Transactions
If the search string matches multiple items, mysql shows a list of matching topics:
mysql> help logs
Many help items for your request exist.
To make a more specific request, please type 'help
- ',
where
- 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 logs
Name: 'SHOW BINARY LOGS'
Description:
Syntax:
SHOW BINARY LOGS
SHOW MASTER LOGS
Lists the binary log files on the server.
This statement is used as part of the procedure described in [purge-master-logs],
that shows how to determine which logs can be purged.
mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name | File_size
+---------------+-----------+
| binlog.000015 | 724935
| binlog.000016 | 733481
+---------------+-----------+
USE db_name in the file avoids specifing 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 or \. command:
mysql> source file_name
mysql> \. 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.
For more information about batch mode, see Section 5, "Using mysql in Batch Mode".
; semicolon.
mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
*************************** 1. row ***************************
msg_nro: 3068
date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
reply: monty@no.spam.com
mail_to: "Thimble Smith"
sbj: UTF-8
txt: >>>>> "Thimble" == Thimble Smith writes:
Thimble> Hi. I think this is a good idea. Is anyone familiar
Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my
Thimble> TODO list and see what happens.
Yes, please do that.
Regards,
Monty
file: inbox-jani-1
hash: 190402944
1 row in set (0.09 sec)
--safe-updates is helpfulDELETE FROM tbl_name; ommitting the WHERE clause.--safe-updates delete rows requires key values.
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.3
UPDATEs and DELETEs require a key constraint in a WHERE or provide a LIMIT . 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 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,
use --select_limit and --max_join_size
shell> mysql --safe-updates --select_limit=500 --max_join_size=10000
Disabling mysql Auto-Reconnect
If mysql loses its connection to the server while sending a query,
all session objects and settings are lost: temporary tables, the autocommit
mode, and user-defined and session variables. Current transaction rolls back.
This behavior may be dangerous as in
the following example where the server was shut down and restarted 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 away
No connection. Trying to reconnect...
Connection id: 1
Current database: test
Query OK, 1 row affected (1.30 sec)
mysql> SELECT * FROM t;
+------+
| a
+------+
| NULL Expected 1
+------+
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.
With auto-reconnect mySQL immediately tries once to reconnect to the server and send the query again.
SEE isamchk(1), isamlog(1), msql2mysql(1), myisam_ftdump(1), myisamchk(1),
myisamlog(1), myisampack(1), mysql.server(1), mysql_config(1),
mysql_explain_log(1), mysql_fix_privilege_tables(1), mysql_zap(1),
mysqlaccess(1), mysqladmin(1), mysqlbinlog(1), mysqlcheck(1),
mysqld(1), mysqld(8), mysqld_multi(1), mysqld_safe(1), mysqldump(1),
mysqlhotcopy(1), mysqlimport(1), mysqlshow(1), pack_isam(1), perror(1),
replace(1), safe_mysqld(1)
http://dev.mysql.com/doc/.