There are many different MySQL client programs that connect to the
server to access databases or perform administrative tasks. Other
utilities are available as well. These do not communicate with the
server but perform MySQL-related operations.
This chapter provides a brief overview of these programs and then a
more detailed description of each one. The descriptions indicate how
to invoke the programs and the options they understand. See
Chapter 4, Using MySQL Programs for general information on
invoking programs and specifying program options.
8.1. Overview of the Client-Side Scripts and Utilities
The following list briefly describes the MySQL client programs and
utilities:
A script that checks the access privileges for a hostname,
username, and database combination.
mysqladmin
A client that performs administrative operations, such as
creating or dropping databases, reloading the grant tables,
flushing tables to disk, and reopening log files.
mysqladmin can also be used to retrieve
version, process, and status information from the server. See
Section 8.5, “mysqladmin — Client for Administering a MySQL Server”.
MySQL AB also provide a number of GUI tools for administering and
otherwise working with MySQL servers. For basic information about
these, see Chapter 4, Using MySQL Programs.
Each MySQL program takes many different options. However, every
MySQL program provides a --help option that you
can use to get a full description of the program's different
options. For example, try mysql --help.
MySQL clients that communicate with the server using the
mysqlclient library use the following
environment variables:
MYSQL_UNIX_PORT
The default Unix socket file; used for connections to
localhost
MYSQL_TCP_PORT
The default port number; used for TCP/IP connections
MYSQL_PWD
The default password
MYSQL_DEBUG
Debug trace options when debugging
TMPDIR
The directory where temporary tables and files are created
You can override the default option values or values specified in
environment variables for all standard programs by specifying
options in an option file or on the command line.
Section 4.3, “Specifying Program Options”.
The myisampack utility compresses
MyISAM tables.
myisampack works by compressing each column
in the table separately. Usually,
myisampack packs the data file 40%-70%.
When the table is used later, the information needed to
decompress columns is read into memory. This results in much
better performance when accessing individual records, because
you only have to uncompress exactly one record.
MySQL uses mmap() when possible to perform
memory mapping on compressed tables. If
mmap() does not work, MySQL falls back to
normal read/write file operations.
A similar utility, pack_isam, compresses
ISAM tables. Because
ISAM tables are deprecated, this section
discusses only myisampack, but the general
procedures for using myisampack are also
true for pack_isam unless otherwise
specified.
Please note the following:
If the mysqld server was invoked with
the --skip-external-locking option, it is
not a good idea to invoke myisampack if
the table might be updated by the server during the
packing process.
After packing a table, it becomes read-only. This is
generally intended (such as when accessing packed tables
on a CD). Allowing writes to a packed table is on our TODO
list, but with low priority.
myisampack can pack
BLOB or TEXT
columns. The older pack_isam program
for ISAM tables cannot.
Invoke myisampack like this:
shell> myisampack [options] filename ...
Each filename should be the name of an index
(.MYI) file. If you are not in the
database directory, you should specify the pathname to the
file. It is permissible to omit the .MYI
extension.
myisampack supports the following options:
--help, -?
Display a help message and exit.
--backup, -b
Make a backup of the table data file using the name
tbl_name.OLD.
--debug[=debug_options],
-#
[debug_options]
Write a debugging log. The
debug_options string often is
'd:t:o,file_name'.
--force, -f
Produce a packed table even if it becomes larger than the
original or if the intermediate file from an earlier
invocation of myisampack exists.
(myisampack creates an intermediate
file named
tbl_name.TMD
in the database directory while it compresses the table.
If you kill myisampack, the
.TMD file might not be deleted.)
Normally, myisampack exits with an
error if it finds that
tbl_name.TMD
exists. With --force,
myisampack packs the table anyway.
--join=big_tbl_name,
-j
big_tbl_name
Join all tables named on the command line into a single
table big_tbl_name. All tables
that are to be combined must have
identical structure (same column names and types, same
indexes, and so forth).
--packlength=len,
-p len
Specify the record length storage size, in bytes. The
value should be 1, 2, or 3. myisampack
stores all rows with length pointers of 1, 2, or 3 bytes.
In most normal cases, myisampack can
determine the right length value before it begins packing
the file, but it may notice during the packing process
that it could have used a shorter length. In this case,
myisampack prints a note that the next
time you pack the same file, you could use a shorter
record length.
--silent, -s
Silent mode. Write output only when errors occur.
--test, -t
Do not actually pack the table, just test packing it.
--tmpdir=path,
-T path
Use the named directory as the location where
myisamchk creates temporary files.
--verbose, -v
Verbose mode. Write information about the progress of the
packing operation and its result.
--version, -V
Display version information and exit.
--wait, -w
Wait and retry if the table is in use. If the
mysqld server was invoked with the
--skip-external-locking option, it is not
a good idea to invoke myisampack if the
table might be updated by the server during the packing
process.
The following sequence of commands illustrates a typical table
compression session:
shell> ls -l station.*
-rw-rw-r-- 1 monty my 994128 Apr 17 19:00 station.MYD
-rw-rw-r-- 1 monty my 53248 Apr 17 19:00 station.MYI
-rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm
shell> myisamchk -dvv station
MyISAM file: station
Isam-version: 2
Creation time: 1996-03-13 10:08:58
Recover time: 1997-02-02 3:06:43
Data records: 1192 Deleted blocks: 0
Datafile parts: 1192 Deleted data: 0
Datafile pointer (bytes): 2 Keyfile pointer (bytes): 2
Max datafile length: 54657023 Max keyfile length: 33554431
Recordlength: 834
Record format: Fixed length
table description:
Key Start Len Index Type Root Blocksize Rec/key
1 2 4 unique unsigned long 1024 1024 1
2 32 30 multip. text 10240 1024 1
Field Start Length Type
1 1 1
2 2 4
3 6 4
4 10 1
5 11 20
6 31 1
7 32 30
8 62 35
9 97 35
10 132 35
11 167 4
12 171 16
13 187 35
14 222 4
15 226 16
16 242 20
17 262 20
18 282 20
19 302 30
20 332 4
21 336 4
22 340 1
23 341 8
24 349 8
25 357 8
26 365 2
27 367 2
28 369 4
29 373 4
30 377 1
31 378 2
32 380 8
33 388 4
34 392 4
35 396 4
36 400 4
37 404 1
38 405 4
39 409 4
40 413 4
41 417 4
42 421 4
43 425 4
44 429 20
45 449 30
46 479 1
47 480 1
48 481 79
49 560 79
50 639 79
51 718 79
52 797 8
53 805 1
54 806 1
55 807 20
56 827 4
57 831 4
shell> myisampack station.MYI
Compressing station.MYI: (1192 records)
- Calculating statistics
normal: 20 empty-space: 16 empty-zero: 12 empty-fill: 11
pre-space: 0 end-space: 12 table-lookups: 5 zero: 7
Original trees: 57 After join: 17
- Compressing file
87.14%
Remember to run myisamchk -rq on compressed tables
shell> ls -l station.*
-rw-rw-r-- 1 monty my 127874 Apr 17 19:00 station.MYD
-rw-rw-r-- 1 monty my 55296 Apr 17 19:04 station.MYI
-rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm
shell> myisamchk -dvv station
MyISAM file: station
Isam-version: 2
Creation time: 1996-03-13 10:08:58
Recover time: 1997-04-17 19:04:26
Data records: 1192 Deleted blocks: 0
Datafile parts: 1192 Deleted data: 0
Datafile pointer (bytes): 3 Keyfile pointer (bytes): 1
Max datafile length: 16777215 Max keyfile length: 131071
Recordlength: 834
Record format: Compressed
table description:
Key Start Len Index Type Root Blocksize Rec/key
1 2 4 unique unsigned long 10240 1024 1
2 32 30 multip. text 54272 1024 1
Field Start Length Type Huff tree Bits
1 1 1 constant 1 0
2 2 4 zerofill(1) 2 9
3 6 4 no zeros, zerofill(1) 2 9
4 10 1 3 9
5 11 20 table-lookup 4 0
6 31 1 3 9
7 32 30 no endspace, not_always 5 9
8 62 35 no endspace, not_always, no empty 6 9
9 97 35 no empty 7 9
10 132 35 no endspace, not_always, no empty 6 9
11 167 4 zerofill(1) 2 9
12 171 16 no endspace, not_always, no empty 5 9
13 187 35 no endspace, not_always, no empty 6 9
14 222 4 zerofill(1) 2 9
15 226 16 no endspace, not_always, no empty 5 9
16 242 20 no endspace, not_always 8 9
17 262 20 no endspace, no empty 8 9
18 282 20 no endspace, no empty 5 9
19 302 30 no endspace, no empty 6 9
20 332 4 always zero 2 9
21 336 4 always zero 2 9
22 340 1 3 9
23 341 8 table-lookup 9 0
24 349 8 table-lookup 10 0
25 357 8 always zero 2 9
26 365 2 2 9
27 367 2 no zeros, zerofill(1) 2 9
28 369 4 no zeros, zerofill(1) 2 9
29 373 4 table-lookup 11 0
30 377 1 3 9
31 378 2 no zeros, zerofill(1) 2 9
32 380 8 no zeros 2 9
33 388 4 always zero 2 9
34 392 4 table-lookup 12 0
35 396 4 no zeros, zerofill(1) 13 9
36 400 4 no zeros, zerofill(1) 2 9
37 404 1 2 9
38 405 4 no zeros 2 9
39 409 4 always zero 2 9
40 413 4 no zeros 2 9
41 417 4 always zero 2 9
42 421 4 no zeros 2 9
43 425 4 always zero 2 9
44 429 20 no empty 3 9
45 449 30 no empty 3 9
46 479 1 14 4
47 480 1 14 4
48 481 79 no endspace, no empty 15 9
49 560 79 no empty 2 9
50 639 79 no empty 2 9
51 718 79 no endspace 16 9
52 797 8 no empty 2 9
53 805 1 17 1
54 806 1 3 9
55 807 20 no empty 3 9
56 827 4 no zeros, zerofill(2) 2 9
57 831 4 no zeros, zerofill(1) 2 9
myisampack displays the following kinds of
information:
normal
The number of columns for which no extra packing is used.
empty-space
The number of columns containing values that are only
spaces; these occupy one bit.
empty-zero
The number of columns containing values that are only
binary zeros; these occupy one bit.
empty-fill
The number of integer columns that do not occupy the full
byte range of their type; these are changed to a smaller
type. For example, a BIGINT column
(eight bytes) can be stored as a
TINYINT column (one byte) if all its
values are in the range from -128 to
127.
pre-space
The number of decimal columns that are stored with leading
spaces. In this case, each value contains a count for the
number of leading spaces.
end-space
The number of columns that have a lot of trailing spaces.
In this case, each value contains a count for the number
of trailing spaces.
table-lookup
The column had only a small number of different values,
which were converted to an ENUM before
Huffman compression.
zero
The number of columns for which all values are zero.
Original trees
The initial number of Huffman trees.
After join
The number of distinct Huffman trees left after joining
trees to save some header space.
After a table has been compressed, myisamchk
-dvv prints additional information about each
column:
Type
The column type. The value may contain any of the
following descriptors:
constant
All rows have the same value.
no endspace
Do not store endspace.
no endspace, not_always
Do not store endspace and do not do endspace
compression for all values.
no endspace, no empty
Do not store endspace. Do not store empty values.
table-lookup
The column was converted to an
ENUM.
zerofill(n)
The most significant n
bytes in the value are always 0 and are not stored.
no zeros
Do not store zeros.
always zero
Zero values are stored using one bit.
Huff tree
The number of the Huffman tree associated with the column.
Bits
The number of bits used in the Huffman tree.
After you run myisampack, you must run
myisamchk to re-create any indexes. At this
time, you can also sort the index blocks and create statistics
needed for the MySQL optimizer to work more efficiently:
After you have installed the packed table into the MySQL
database directory, you should execute mysqladmin
flush-tables to force mysqld to
start using the new table.
To unpack a packed table, use the --unpack
option to myisamchk or
isamchk.
mysql is a simple SQL shell (with GNU
readline capabilities). It supports
interactive and non-interactive use. When used interactively,
query results are presented in an ASCII-table format. When
used non-interactively (for example, as a filter), the result
is presented in tab-separated format. The output format can be
changed using command-line 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 using mysql_use_result()
rather than mysql_store_result() to
retrieve the result set.
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_passworddb_name
Then type an SQL statement, end it with
‘;’, \g, or
\G and press Enter.
You can run a script simply like this:
shell> mysql db_name < script.sql > output.tab
8.3.1. Options
mysql supports the following options:
--help, -?
Display a help message and exit.
--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.
Enable or disable LOCAL capability for
LOAD DATA INFILE. With no value, the
option enables LOCAL. It 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
Named commands are enabled. Long
format commands are allowed as well as shortened \*
commands. For example, quit and
\q both are recognized.
--no-auto-rehash, -A
No automatic rehashing. This option causes
mysql to start faster, but you must
issue the rehash command if you want to
use table and column name completion.
--no-beep, -b
Do not beep when errors occur.
--no-named-commands, -g
Named commands are disabled. Use the \*
form only, or use named commands only at the beginning of
a line ending with a semicolon
(‘;’). As of MySQL 3.23.22,
mysql starts with this option
enabled by default. However, even
with this option, long-format commands still work from the
first line.
Ignore statements except those for the default database
named on the command line. This is useful for skipping
updates to other databases in the binary log.
--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. It does not work in batch mode. Output paging is
discussed further in Section 8.3.2, “mysql Commands”.
--password[=password],
-p[password]
The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the
option and the password. If you omit the
password value following the
--password or -p option
on the command line, you are prompted for one. The
password should be omitted on SysV-based UNIX systems, as
the password may be displayed in the output of
ps.
--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 8.3.2, “mysql Commands”.
--protocol={TCP | SOCKET | PIPE | MEMORY}
The connection protocol to use. Added in MySQL 4.1.
--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
Write column values without escape conversion. Often used
with the --batch option.
--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. Added in MySQL
4.1.0.
--safe-updates,
--i-am-a-dummy, -U
Allow only those UPDATE and
DELETE statements that specify rows to
affect 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 8.3.4, “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. This option was added in
MySQL 4.1.1.
--sigint-ignore
Ignore SIGINT signals (typically the
result of typing Control-C). This option was added in
MySQL 4.1.6.
--silent, -s
Silent mode. Produce less output. This option can be given
multiple times to produce less and less output.
--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
The socket file to use for the connection.
--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
does not work in batch mode. Tee files are discussed
further in Section 8.3.2, “mysql Commands”.
--unbuffered, -n
Flush the buffer after each query.
--user=user_name,
-u user_name
The MySQL username to use when connecting to the server.
--verbose, -v
Verbose mode. Produce more output. This option can be
given multiple times to produce more and more output. (For
example, -v -v -v produces the table
output format even in batch mode.)
--version, -V
Display version information and exit.
--vertical, -E
Print the rows of query output vertically. 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.
You can also set the following variables by using
--var_name=value
options:
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.)
It is also possible to set variables by using
--set-variable=var_name=value
or -O
var_name=value
syntax. In MySQL 4.1, this syntax is deprecated.
On Unix, the mysql client writes a record
of executed statements to a history file. By default, the
history 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.
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.
8.3.2. mysql Commands
mysql sends SQL statements 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> help
MySQL commands:
? (\?) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new 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.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
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.
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.
Browsing or searching query results in interactive mode by
using Unix programs such as less,
more, or any other similar program is
possible with the --pager option. 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.
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
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.
From MySQL 4.0.2 on, the default mysql>
prompt can be reconfigured. The string for defining the prompt
can contain the following special sequences:
Option
Description
\v
The server version
\d
The current 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 name
\\
A literal ‘\’ backslash character
\n
A newline character
\t
A tab character
\
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
\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
\'
Single quote
\"
Double quote
‘\’ 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>.
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 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 4.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> "
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]>
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>
8.3.3. 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
You can also start your text file with a USE
db_name statement. In
this case, it is unnecessary to specify the database name on
the command line:
shell> mysql < text_file
If you are running mysql, you can execute
an SQL script file using the source or
\. command:
mysql> source filename
mysql> \. filename
Sometimes you may want your script to display progress
information to the user; for this you can insert some lines
like
This section describes some techniques that can help you use
mysql more effectively.
8.3.4.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:50
time_zone: +0200
mail_from: Monty
reply: monty@no.spam.com
mail_to: "Thimble Smith" <tim@no.spam.com>
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)
8.3.4.2. Using the --safe-updates Option
For beginners, a useful startup option is
--safe-updates (or
--i-am-a-dummy, which has the same effect).
This option was introduced in MySQL 3.23.11. 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;
You are not allowed 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;
All large SELECT results are
automatically limited to 1,000 rows unless the statement
includes a LIMIT clause.
Multiple-table SELECT statements that
probably need to examine more than 1,000,000 row
combinations are aborted.
To specify limits other than 1,000 and 1,000,000, you can
override the defaults by using
--select_limit and
--max_join_size options:
shell> mysql --safe-updates --select_limit=500 --max_join_size=10000
8.3.4.3. Disabling mysql Auto-Reconnect
If the mysql client loses its connection
to the server while sending a query, it immediately and
automatically tries to reconnect once to the server and send
the query 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 and session
variables. This behavior may be dangerous for you, 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 |
+------+
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.
8.4. mysqlaccess — Client for Checking Access Privileges
mysqlaccess is a diagnostic tool that Yves
Carlier has provided for the MySQL distribution. It checks the
access privileges for a hostname, username, and database
combination. Note that mysqlaccess checks
access using only the user,
db, and host tables. It
does not check table, column, or routine privileges specified
in the tables_priv,
columns_priv, or
procs_priv tables.
Copy the new access privileges from the temporary tables
to the original grant tables. The grant tables must be
flushed for the new privileges to take effect. (For
example, execute a mysqladmin reload
command.)
--copy
Reload the temporary grant tables from original ones.
--db=db_name,
-d db_name
Specify the database name.
--debug=N
Specify the debug level. N can
be an integer from 0 to 3.
--host=host_name,
-h host_name
The hostname to use in the access privileges.
--howto
Display some examples that show how to use
mysqlaccess.
--old_server
Assume that the server is an old MySQL server (before
MySQL 3.21) that does not yet know how to handle full
WHERE clauses.
--password[=password],
-p[password]
The password to use when connecting to the server. If you
omit the password value
following the --password or
-p option on the command line, you are
prompted for one.
--plan
Display suggestions and ideas for future releases.
--preview
Show the privilege differences after making changes to the
temporary grant tables.
--relnotes
Display the release notes.
--rhost=host_name,
-H host_name
Connect to the MySQL server on the given host.
--rollback
Undo the most recent changes to the temporary grant
tables.
--spassword[=password],
-P[password]
The password to use when connecting to the server as the
superuser. If you omit the
password value following the
--password or -p option
on the command line, you are prompted for one.
--superuser=user_name,
-U user_name
Specify the username for connecting as the superuser.
--table, -t
Generate reports in table format.
--user=user_name,
-u user_name
The hostname to use in the access privileges.
--version, -v
Display version information and exit.
If your MySQL distribution is installed in some non-standard
location, you must change the location where
mysqlaccess expects to find the
mysql client. Edit the
mysqlaccess script at approximately line
18. Search for a line that looks like this:
$MYSQL = '/usr/local/bin/mysql'; # path to mysql executable
Change the path to reflect the location where
mysql actually is stored on your system. If
you do not do this, a Broken pipe error
will occur when you run mysqlaccess.
8.5. 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, create and drop
databases, and more.
Tell the server to write debug information to the error
log.
drop db_name
Delete the database named
db_name and all its tables.
extended-status
Display the server status variables and their values.
flush-hosts
Flush all information in the host cache.
flush-logs
Flush all logs.
flush-privileges
Reload the grant tables (same as
reload).
flush-status
Clear status variables.
flush-tables
Flush all tables.
flush-threads
Flush the thread cache. (Added in MySQL 3.23.16.)
kill id,id,...
Kill server threads.
old-password
new-password
This is like the password command but
stores the password using the old (pre-4.1)
password-hashing format. This command was added in MySQL
4.1.0. (See Section 5.6.9, “Password Hashing in MySQL 4.1”.)
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.
If new-password contains spaces
or other characters that are special to your command
interpreter, you need to enclose it within quotes. On
Windows, be sure to use double quotes rather than single
quotes; single quotes are not stripped from the password,
but rather are interpreted as part of the password. For
example:
shell> mysqladmin password "my new password"
ping
Check whether the server is alive. The return status from
mysqladmin is 0 if the server is
running, 1 if it is not. Beginning with MySQL 4.0.22, the
status is 0 even in case of an error such as
Access denied, because that means the
server is running but disallowed the connection, which is
different from the server not running.
processlist
Show a list of active server threads. This is like the
output of the SHOW PROCESSLIST
statement. If the --verbose option is
given, the output is like that of SHOW FULL
PROCESSLIST.
reload
Reload the grant tables.
refresh
Flush all tables and close and open log files.
shutdown
Stop the server.
start-slave
Start replication on a slave server. (Added in MySQL
3.23.16.)
status
Display a short server status message.
stop-slave
Stop replication on a slave server. (Added in MySQL
3.23.16.)
variables
Display the server system variables and their values.
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: 39487
Slow queries: 0 Opens: 541 Flush tables: 1
Open 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.
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 code. This value is displayed
only when MySQL has been compiled with
--with-debug=full.
Maximum memory used
The maximum amount of memory allocated directly by
mysqld code. This value is displayed
only when MySQL has been compiled with
--with-debug=full.
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.
Do not ask for confirmation for the drop
database command. With multiple commands,
continue even if an error occurs.
--host=host_name,
-h host_name
Connect to the MySQL server on the given host.
--password[=password],
-p[password]
The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the
option and the password. If you omit the
password value following the
--password or -p option
on the command line, you are prompted for one.
--port=port_num,
-P port_num
The TCP/IP port number to use for the connection.
--protocol={TCP | SOCKET | PIPE | MEMORY}
The connection protocol to use. Added in MySQL 4.1.
--relative, -r
Show the difference between the current and previous
values when used with -i. Currently, this
option works only with the
extended-status command.
--silent, -s
Exit silently if a connection to the server cannot be
established.
--sleep=delay,
-i delay
Execute commands again and again, sleeping for
delay seconds in between.
--socket=path,
-S path
The socket file to use for the connection.
--user=user_name,
-u user_name
The MySQL username to use when connecting to the server.
--verbose, -v
Verbose mode. Print out more information on 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 an option 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
options:
connect_timeout
The maximum number of seconds before connection timeout.
The default value is 43200 (12 hours).
shutdown_timeout
The maximum number of seconds to wait for shutdown. The
default value is 3600 (1 hour).
It is also possible to set variables by using
--set-variable=var_name=value
or -O
var_name=value
syntax. However, this syntax is deprecated as of MySQL 4.0.
8.6. mysqlbinlog — Utility for Processing Binary Log Files
The binary log files that the server generates are written in
binary format. To examine these files in text format, use the
mysqlbinlog utility. It is available as of
MySQL 3.23.14.
Invoke mysqlbinlog like this:
shell> mysqlbinlog [options] log-file ...
For example, to display the contents of the binary log
binlog.000003, use this command:
shell> mysqlbinlog binlog.0000003
The output includes all statements contained in
binlog.000003, together with other
information such as the time each statement took, the thread
ID of the client that issued it, the timestamp when it was
issued, and so forth.
Normally, you use mysqlbinlog to read
binary log files directly and apply them to the local MySQL
server. It is also possible to read binary logs from a remote
server by using the --read-from-remote-server
option.
When you read remote binary logs, the connection parameter
options can be given to indicate how to connect to the server,
but they are ignored unless you also specify the
--read-from-remote-server option. These
options are --host,
--password, --port,
--protocol, --socket, and
--user.
You can also use mysqlbinlog to read relay
log files written by a slave server in a replication setup.
Relay logs have the same format as binary log files.
List entries for just this database (local log only).
--force-read, -f
With this option, if mysqlbinlog reads
a binary log event that it does not recognize, it prints a
warning, ignores the event, and continues. Without this
option, mysqlbinlog stops if it reads
such an event.
--host=host_name,
-h host_name
Get the binary log from the MySQL server on the given
host.
--local-load=path,
-l path
Prepare local temporary files for LOAD DATA
INFILE in the specified directory.
--offset=N,
-o N
Skip the first N entries.
--password[=password],
-p[password]
The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the
option and the password. If you omit the
password value following the
--password or -p option
on the command line, you are prompted for one.
--port=port_num,
-P port_num
The TCP/IP port number to use for connecting to a remote
server.
--position=N,
-j N
Deprecated, use --start-position instead
(starting from MySQL 4.1.4).
--protocol={TCP | SOCKET | PIPE | MEMORY}
The connection protocol to use. Added in MySQL 4.1.
--read-from-remote-server,
-R
Read the binary log from a MySQL server. Any connection
parameter options are ignored unless this option is given
as well. These options are --host,
--password, --port,
--protocol, --socket,
and --user.
--result-file=name,
-r name
Direct output to the given file.
--short-form, -s
Display only the statements contained in the log, without
any extra information.
--socket=path,
-S path
The socket file to use for the connection.
--start-datetime=datetime
Start reading the binary log at the first event having a
datetime equal to or later than the
datetime argument. The
datetime value is relative to
the local time zone on the machine where you run
mysqlbinlog. The value should be in a
format accepted for the DATETIME or
TIMESTAMP data types. For example:
This option is available as of MySQL 4.1.4. It is useful
for point-in-time recovery.
--stop-datetime=datetime
Stop reading the binary log at the first event having a
datetime equal or posterior to the
datetime argument. See the
description of the --start-datetime
option for information about the
datetime value. This option is
availa
