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 section 4 Using MySQL Programs for general information on invoking programs and specifying program options.
The following list briefly describes the MySQL client programs and utilities:
myisampack
MyISAM tables to produce smaller read-only
tables.
See section 8.2 myisampack, the MySQL Compressed Read-only Table Generator.
mysql
mysql, the Command-Line Tool.
mysqlaccess
mysqladmin
mysqladmin can also be used to retrieve version,
process, and status information from the server.
See section 8.4 mysqladmin, Administering a MySQL Server.
mysqlbinlog
mysqlbinlog Binary Log Utility.
mysqlcc
mysqlcc, the MySQL Control Center.
mysqlcheck
mysqlcheck Table Maintenance and Repair Program.
mysqldump
mysqldump Database Backup Program.
mysqlhotcopy
MyISAM or ISAM tables
while the server is running.
See section 8.9 The mysqlhotcopy Database Backup Program.
mysqlimport
LOAD DATA INFILE.
See section 8.10 The mysqlimport Data Import Program.
mysqlshow
mysqlshow, Showing Databases, Tables, and Columns.
perror
perror, Explaining Error Codes.
replace
replace String-Replacement Utility.
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 |
Use of MYSQL_PWD is insecure.
See section 5.6.6 Keeping Your Password Secure.
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.
myisampack, the MySQL Compressed Read-only Table Generator
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, not a much larger disk block as when using Stacker on MS-DOS.
MySQL uses mmap() when possible to perform memory mapping on
compressed tables. If mmap() doesn't 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:
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.
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, -?
--backup, -b
--debug[=debug_options], -# [debug_options]
'd:t:o,file_name'.
--force, -f
myisampack exists.
(myisampack creates a temporary file named `tbl_name.TMD' 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
--packlength=#, -p #
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 will print a note that the next time you pack the same file,
you could use a shorter record length.
--silent, -s
--test, -t
--tmp_dir=path, -T path
--verbose, -v
--version, -V
--wait, -w
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
empty-space
empty-zero
empty-fill
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
end-space
table-lookup
ENUM before Huffman compression.
zero
Original trees
After join
After a table has been compressed, myisamchk -dvv prints additional
information about each column:
Type
constant
no endspace
no endspace, not_always
no endspace, no empty
table-lookup
ENUM.
zerofill(n)
no zeros
always zero
Huff tree
Bits
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:
shell> myisamchk -rq --sort-index --analyze tbl_name.MYI
A similar procedure applies for ISAM tables. After using
pack_isam, use isamchk to re-create the indexes:
shell> isamchk -rq --sort-index --analyze tbl_name.ISM
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, the Command-Line Tool
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_password db_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
mysql supports the following options:
--help, -?
--batch, -B
mysql doesn't use the history file.
--character-sets-dir=path
--compress, -C
--database=db_name, -D db_name
--debug[=debug_options], -# [debug_options]
'd:t:o,file_name'.
The default is 'd:t:o,/tmp/mysql.trace'.
--debug-info, -T
--default-character-set=charset
--execute=statement, -e statement
--batch.
--force, -f
--host=host_name, -h host_name
--html, -H
--ignore-space, -i
IGNORE_SPACE in
section 5.2.2 The Server SQL Mode.
--local-infile[={0|1}]
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
quit and \q both
are recognized.
--no-auto-rehash, -A
mysql to start faster,
but you must issue the rehash command if you want to use table and
column name completion.
--no-beep, -b
--no-named-commands, -g
\* 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.
--no-pager
mysql Commands.
--no-tee
mysql Commands.
--one-database, -O
--pager[=command]
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.1 mysql Commands.
--password[=password], -p[password]
-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 will be
prompted for one.
--port=port_num, -P port_num
--prompt=format_str
mysql>.
The special sequences that the prompt can contain are described in
section 8.3.1 mysql Commands.
--protocol={TCP | SOCKET | PIPE | MEMORY}
--quick, -q
mysql
doesn't use the history file.
--raw, -r
--batch option.
--reconnect
--skip-reconnect.
New in MySQL 4.1.0.
--safe-updates, --i-am-a-dummy, -U
UPDATE and DELETE statements that specify rows
to affect using key values. If you have this option in an option file,
you can override it by using --safe-updates on the command line.
See section 8.3.3 mysql Tips for more information about
this option.
--sigint-ignore
SIGINT signals (typically the result of typing Control-C).
This option was added in MySQL 4.1.6.
--silent, -s
--skip-column-names, -N
--skip-line-numbers, -L
--socket=path, -S path
--table, -t
--tee=file_name
mysql Commands.
--unbuffered, -n
--user=user_name, -u user_name
--verbose, -v
-v -v -v produces the table output format even in batch
mode.)
--version, -V
--vertical, -E
\G.
--wait, -w
--xml, -X
You can also set the following variables by using --var_name=value
options:
connect_timeout
max_allowed_packet
max_join_size
--safe-updates.
(Default value is 1,000,000.)
net_buffer_length
select_limit
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. However, this syntax is deprecated as of MySQL 4.0.
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:
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.
shell> ln -s /dev/null $HOME/.mysql_historyYou need do this only once.
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:
? (\h) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server.
Optional arguments are db and host.
delimiter (\d) Set query 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.
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 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 will be 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 will be 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
now 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 doesn't 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:
mysql> pager cat > /tmp/log.txtYou can also pass any options for the program that you want to use as your pager:
mysql> pager less -n -i -S
-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
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 will be
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 don't 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 doesn't 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 host |
\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:
MYSQL_PS1 environment variable to a prompt string. For
example:
shell> export MYSQL_PS1="(\u@\h) [\d]> "
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 will be 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> "
--prompt option on the command line to mysql.
For example:
shell> mysql --prompt="(\u@\h) [\d]> " (user@host) [database]>
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>
The mysql client typically is used interactively, like this:
shell> mysql db_name
However, it's 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 already 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
SELECT '<info_to_display>' AS ' ';
which will output <info_to_display>.
For more information about batch mode, see section 3.5 Using mysql in Batch Mode.
mysql Tips
This section describes some techniques that can help you use mysql more
effectively.
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: [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 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 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 will delete 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 13.5.3 SET Syntax.
The SET statement has the following effects:
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=# WHERE key_column=#; UPDATE tbl_name SET not_key_column=# LIMIT 1;
SELECT results are automatically limited to 1,000 rows
unless the statement includes a LIMIT clause.
SELECT statements that will 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
mysql Auto-Reconnect
If the mysql client loses its connection to the server while sending
a query, it will immediately and automatically try 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.
mysqladmin, 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.
Invoke mysqladmin like this:
shell> mysqladmin [options] command [command-option] command ...
mysqladmin supports the following commands:
create db_name
drop db_name
extended-status
flush-hosts
flush-logs
flush-privileges
reload).
flush-status
flush-tables
flush-threads
kill id,id,...
old-password new-password
password command but stores the password using
the old (pre-4.1) password-hashing format. This command was added in MySQL
4.1.0.
password new-password
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 will need to enclose it within quotes.
On Windows, be sure to use double quotes rather than single quotes;
single quotes will be not be stripped from the password, they will be
interpreted as part of the password. For example:
shell> mysqladmin password "my new password"
ping
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 PROCESSLIST statement.
If the --verbose option is given, the output is like that of
SHOW FULL PROCESSLIST.
reload
refresh
shutdown
start-slave
status
stop-slave
variables
version
All commands can be shortened to any unique prefix. For example:
shell> mysqladmin proc stat +----+-------+-----------+----+-------------+------+-------+------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+-----------+----+-------------+------+-------+------+ | 6 | monty | localhost | | Processlist | 0 | | | +----+-------+-----------+----+-------------+------+-------+------+ Uptime: 10077 Threads: 1 Questions: 9 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 2 Memory in use: 1092K Max memory used: 1116K
The mysqladmin status command result displays the following values:
Uptime
Threads
Questions
Slow queries
long_query_time
seconds. See section 5.9.5 The Slow Query Log.
Opens
Flush tables
flush ..., refresh, and reload commands the
server has executed.
Open tables
Memory in use
mysqld code.
This value is displayed only when MySQL has been compiled with
--with-debug=full.
Maximum memory used
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.
mysqladmin supports the following options:
--help, -?
--character-sets-dir=path
--compress, -C
--count=#, -c #
--sleep
(-i).
--debug[=debug_options], -# [debug_options]
'd:t:o,file_name'.
The default is 'd:t:o,/tmp/mysqladmin.trace'.
--default-character-set=charset
--force, -f
drop database command.
With multiple commands, continue even if an error occurs.
--host=host_name, -h host_name
--password[=password], -p[password]
-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 will be
prompted for one.
--port=port_num, -P port_num
--protocol={TCP | SOCKET | PIPE | MEMORY}
--relative, -r
-i. Currently, this option works only with the extended-status
command.
--silent, -s
--sleep=delay, -i delay
--socket=path, -S path
--user=user_name, -u user_name
--verbose, -v
--version, -V
--vertical, -E
--relative, but
prints output vertically.
--wait[=#], -w[#]
You can also set the following variables by using --var_name=value
options:
connect_timeout
shutdown_timeout
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.
mysqlbinlog Binary Log Utility
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.
The binary log is discussed further in section 5.9.4 The Binary Log.
mysqlbinlog supports the following options:
--help, -?
--database=db_name, -d db_name
--force-read, -f
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
--local-load=path, -l path
LOAD DATA INFILE in the specified
directory.
--offset=N, -o N
--password[=password], -p[password]
-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 will be
prompted for one.
--port=port_num, -P port_num
--position=N, -j N
--start-position instead (starting from MySQL 4.1.4).
--protocol={TCP | SOCKET | PIPE | MEMORY}
--read-from-remote-server, -R
--host, --password, --port,
--protocol,
--socket, and --user.
--result-file=name, -r name
--short-form, -s
--socket=path, -S path
--start-datetime=datetime
datetime argument. Available as of MySQL 4.1.4.
--stop-datetime=datetime
datetime argument. Available as of MySQL 4.1.4. Useful
for point-in-time recovery.
--start-position=N
N argument. Available as of MySQL 4.1.4 (previously named
--position).
--stop-position=N
N argument. Available as of MySQL 4.1.4.
--to-last-log, -t
--read-from-remote-server. Available as of MySQL 4.1.2.
--disable-log-bin, -D
--to-last-log option and are sending the output to the same
MySQL server. This option also is useful when restoring after a crash
to avoid duplication of the statements you already have logged. Note:
This option requires that you have the SUPER privilege. Available
as of MySQL 4.1.8.
--user=user_name, -u user_name
--version, -V
You can also set the following variable by using --var_name=value
options:
open_files_limit
You can pipe the output of mysqlbinlog into a mysql client to
execute the statements contained in the binary log. This is used to recover
from a crash when you have an old backup (see section 5.7.1 Database Backups):
shell> mysqlbinlog hostname-bin.000001 | mysql
Or:
shell> mysqlbinlog hostname-bin.[0-9]* | mysql
You can also redirect the output of mysqlbinlog to a text file
instead, if you need to modify the statement log first (for example, to
remove statements that you don't want to execute for some reason). After
editing the file, execute the statements that it contains by using it as
input to the mysql program.
mysqlbinlog has the --position option, which prints only
those statements with an offset in the binary log greater than or equal to
a given position (the given position must match the start of one event). It
also has options to stop or start when it sees an event of a given date and
time. This enables you to perform point-in-time recovery using the
--stop-datetime option (to be able to say, for example, "roll forward
my databases to how they were today at 10:30 AM").
If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server. Here is an example that demonstrates what may be unsafe:
shell> mysqlbinlog hostname-bin.000001 | mysql # DANGER!! shell> mysqlbinlog hostname-bin.000002 | mysql # DANGER!!
Processing binary logs this way using different connections to the server
will cause problems if the first log file contains a CREATE TEMPORARY
TABLE statement and the second log contains a statement that uses the
temporary table. When the first mysql process terminates, the server
will drop the temporary table. When the second mysql process attempts
to use the table, the server will report ``unknown table.''
To avoid problems like this, use a single connection to execute the contents of all binary logs that you want to process. Here is one way to do that:
shell> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql
Another approach is to do this:
shell> mysqlbinlog hostname-bin.000001 > /tmp/statements.sql shell> mysqlbinlog hostname-bin.000002 >> /tmp/statements.sql shell> mysql -e "source /tmp/statements.sql"
In MySQL 3.23, the binary log did not contain the data to load for
LOAD DATA INFILE statements. To execute such a statement from a
binary log file, the original data file was needed. Starting from MySQL
4.0.14, the binary log does contain the data, so mysqlbinlog can
produce output that reproduces the LOAD DATA INFILE operation without
the original data file. mysqlbinlog copies the data to a temporary
file and writes a LOAD DATA LOCAL INFILE statement that refers to the
file. The default location of the directory where these files are written
is system-specific. To specify a directory explicitly, use the
--local-load option.
Because mysqlbinlog converts LOAD DATA INFILE statements to
LOAD DATA LOCAL INFILE statements (that is, it adds LOCAL),
both the client and the server that you use to process the statements must be
configured to allow LOCAL capability.
See section 5.4.4 Security Issues with LOAD DATA LOCAL.
Warning: The temporary files created for LOAD DATA LOCAL
statements are not automatically deleted
because they are needed until you actually execute those statements. You
should delete the temporary files yourself after you no longer need the
statement log. The files can be found in the temporary file directory and
have names like `original_file_name-#-#'.
In the future, we will fix this problem by allowing mysqlbinlog
to connect directly to a mysqld server. Then it will be possible
to safely remove the log files automatically as soon as the LOAD DATA
INFILE statements have been executed.
Before MySQL 4.1, mysqlbinlog could not prepare output suitable for
mysql if the binary log contained interlaced statements originating
from different clients that used temporary tables of the same name. This is
fixed in MySQL 4.1. However, the problem still existed for LOAD DATA
INFILE statements until it was fixed in MySQL 4.1.8.
mysqlcc, the MySQL Control Center
mysqlcc, the MySQL Control Center, is a platform-independent client that
provides a graphical user interface (GUI) to the MySQL database server.
It supports interactive use, including syntax highlighting and tab completion.
It provides database and table management, and allows server administration.
mysqlcc is now deprecated and it is recommended that users choose the new
MySQL Administrator and MySQL Query Browser, found at http://dev.mysql.com/downloads/.
Currently, mysqlcc runs on Windows and Linux platforms.
Invoke mysqlcc by double-clicking its icon in a graphical environment.
From the command line, invoke it like this:
shell> mysqlcc [options]
mysqlcc supports the following options:
--help, -?
--blocking_queries, -b
--compress, -C
--connection_name=name, -c name
--server.
--database=db_name, -d db_name
--history_size=#, -H #
--host=host_name, -h host_name
--local-infile[={0|1}]
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.
--password[=password], -p[password]
-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 will be
prompted for one.
--plugins_path=name, -g name
--port=port_num, -P port_num
--query, -q
--register, -r
--server=name, -s name
--socket=path, -S path
--syntax, -y
--syntax_file=name, -Y name
--translations_path=name, -T name
--user=user_name, -u user_name
--version, -V
You can also set the following variables by using --var_name=value
options:
connect_timeout
max_allowed_packet
max_join_size
net_buffer_length
select_limit
SELECT statements.
(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. However, this syntax is deprecated as of MySQL 4.0.
mysqlcheck Table Maintenance and Repair Program
The mysqlcheck client checks and repairs MyISAM tables. It
can also optimize and analyze tables. mysqlcheck is available as of
MySQL 3.23.38.
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 check or repair your tables.
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, then sends the statements to the server to be executed.
There are three general ways to invoke mysqlcheck:
shell> mysqlcheck [options] db_name [tables] shell> mysqlcheck [options] --databases DB1 [DB2 DB3...] shell> mysqlcheck [options] --all-databases
If you don't name any tables or use the --databases or
--all-databases option, entire databases will be checked.
mysqlcheck has a special feature compared to the other clients. 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 will repair tables by command.
The following names can be used to change mysqlcheck default behavior:
mysqlrepair | The default option will be --repair
|
mysqlanalyze | The default option will be --analyze
|
mysqloptimize | The default option will be --optimize
|
mysqlcheck supports the following options:
--help, -?
--all-databases, -A
--databases option and naming all the databases on the command line.
--all-in-1, -1
--analyze, -a
--auto-repair
--character-sets-dir=path
--check, -c
--check-only-changed, -C
--compress
--databases, -B
--debug[=debug_options], -# [debug_options]
'd:t:o,file_name'.
--default-character-set=charset
--extended, -e
--fast, -F
--force, -f
--host=host_name, -h host_name
--medium-check, -m
--extended operation.
This finds only 99.99% of all errors, which should be good enough in most cases.
--optimize, -o
--password[=password], -p[password]
-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 will be
prompted for one.
--port=port_num, -P port_num
--protocol={TCP | SOCKET | PIPE | MEMORY}
--quick, -q
--repair, -r
--silent, -s
--socket=path, -S path
--tables
--databases or -B option. All arguments
following the option are regarded as table names.
--user=user_name, -u user_name
--verbose, -v
--version, -V
mysqldump Database Backup Program
The mysqldump client can be used
to dump a database or a collection of databases for backup or for
transferring the data to another SQL server (not necessarily a MySQL
server). The dump will contain SQL statements to create the table
and/or populate the table.
If you are doing a backup on the server, and your tables all are
MyISAM tables, you could consider using
the mysqlhotcopy instead (faster backup, faster
restore). See section 8.9 The mysqlhotcopy Database Backup Program.
There are three general ways to invoke mysqldump:
shell> mysqldump [options] db_name [tables] shell> mysqldump [options] --databases DB1 [DB2 DB3...] shell> mysqldump [options] --all-databases
If you don't name any tables or use the --databases or
--all-databases option, entire databases will be dumped.
To get a list of the options your version of mysqldump supports,
execute mysqldump --help.
If you run mysqldump without the --quick or
--opt option, mysqldump will load the whole result set into
memory before dumping the result. This will probably be a problem if
you are dumping a big database. As of MySQL 4.1, --opt is on by
default, but can be disabled with --skip-opt.
If you are using a recent copy of the mysqldump program
and you are going to generate a dump that will be reloaded into a very old MySQL
server, you should not use the --opt or -e options.
Out-of-range numeric values such as -inf and inf, as well
as NaN (not-a-number) values are dumped by mysqldump as NULL.
You can see this using the following sample table:
mysql> CREATE TABLE t (f DOUBLE); mysql> INSERT INTO t VALUES(1e+111111111111111111111); mysql> INSERT INTO t VALUES(-1e111111111111111111111); mysql> SELECT f FROM t; +------+ | f | +------+ | inf | | -inf | +------+
For this table, mysqldump produces the following data output:
-- -- Dumping data for table `t` -- INSERT INTO t VALUES (NULL); INSERT INTO t VALUES (NULL);
The significance of this behavior is that if you dump and restore the
table, the new table has contents that differ from the original contents.
Note that since MySQL 4.1.2 you cannot insert inf in the table,
so this mysqldump behavior is only relevant when you deal
with old servers.
mysqldump supports the following options:
--help, -?
--add-drop-table
DROP TABLE statement before each CREATE TABLE statement.
--add-locks
LOCK TABLES and UNLOCK TABLES
statements. This results in faster inserts when the dump file is reloaded.
See section 7.2.14 Speed of INSERT Statements.
--all-databases, -A
--databases option and naming all the databases on the command line.
--allow-keywords
--comments[={0|1}]
0, suppresses additional information in the dump file such
as program version, server version, and host. --skip-comments has the
same effect as --comments=0. The default value is 1 to not
suppress the extra information. New in MySQL 4.0.17.
--compatible=name
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.2.2 The Server SQL Mode.
This option requires a server version of 4.1.0 or higher.
With older servers, it does nothing.
--complete-insert, -c
INSERT statements that include column names.
--compress, -C
--create-options
CREATE TABLE statements.
Before MySQL 4.1.2, use --all instead.
--databases, -B
USE db_name statement is included in the
output before each new database.
--debug[=debug_options], -# [debug_options]
'd:t:o,file_name'.
--default-character-set=charset
mysqldump from MySQL 4.1.2 or later
uses utf8; earlier versions use latin1.
--delayed
INSERT DELAYED statements.
--delete-master-logs
--first-slave.
It was added in MySQL 3.23.57 (for MySQL 3.23) and MySQL 4.0.13 (for MySQL
4.0).
--disable-keys, -K
INSERT statements with
/*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and
/*!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements.
This makes loading the dump file into a MySQL 4.0 server faster because the
indexes are created after all rows are inserted.
This option is effective only for MyISAM tables.
--extended-insert, -e
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=...
--lines-terminated-by=...
-T option and have the same
meaning as the corresponding clauses for LOAD DATA INFILE.
See section 13.1.5 LOAD DATA INFILE Syntax.
--first-slave, -x
--lock-all-tables in MySQL 4.1.8.
--flush-logs, -F
--all-databases (or
-A) option, the logs are flushed for each database dumped.
The exception is when using --lock-all-tables
or --master-data:
In this case, the logs are flushed only once, corresponding to the moment
that all tables are locked. If you want your dump and the log flush to
happen at exactly the same moment, you should use --flush-logs
together with either --lock-all-tables or --master-data.
--force, -f
--host=host_name, -h host_name
localhost.
--hex-blob
'abc' becomes 0x616263. The affected columns are BINARY,
VARBINARY, and BLOB in MySQL 4.1 and up, and CHAR
BINARY, VARCHAR BINARY, and BLOB in MySQL 4.0. This option
was added in MySQL 4.0.23 and 4.1.8.
--lock-all-tables, -x
--single-transaction and --lock-tables. Added in
MySQL 4.1.8.
--lock-tables, -l
READ LOCAL to allow concurrent inserts in the case of MyISAM
tables. For InnoDB tables, --single-transaction is a much better
option, because it does not need to lock the tables at all.
Please note that when dumping multiple databases, --lock-tables locks
tables for each database separately. So, using this option will not guarantee
that the tables in the dump file will be logically consistent between
databases. Tables in different databases may be dumped in completely
different states.
--master-data[=value]
CHANGE MASTER statement
that will make a slave server start from the correct
position in the master's binary logs if you use this SQL dump of the master
to set up a slave.
If the option value is equal to 2, the CHANGE MASTER statement is
written as an SQL comment.
This is the default action if value is omitted.
value may be given as of MySQL 4.1.8; before that, do not specify
an option value.
The --master-data option turns on --lock-all-tables, unless
--single-transaction also is specified (in which case, a
global read lock is only acquired a short time at the beginning of the
dump. See also the description for
--single-transaction. In all cases,
any action on logs happens at the exact moment of the dump.
This option automatically turns off --lock-tables.
--no-create-db, -n
CREATE DATABASE /*!32312 IF NOT EXISTS*/
db_name statements that are otherwise included in the output if the
--databases or --all-databases option is given.
--no-create-info, -t
CREATE TABLE statements that re-create each dumped table.
--no-data, -d
--opt
--add-drop-table
--add-locks --create-options --disable-keys --extended-insert
--lock-tables --quick --set-charset. It should
give you a fast dump operation and produce a dump file that can be reloaded
into a MySQL server quickly. As of MySQL 4.1, --opt is on by default,
but can be disabled with --skip-opt. To disable only certain of the
options enabled by --opt, use their --skip forms; for example,
--skip-add-drop-table or --skip-quick.
--password[=password], -p[password]
-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 will be
prompted for one.
--port=port_num, -P port_num
--protocol={TCP | SOCKET | PIPE | MEMORY}
--quick, -q
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
ANSI_QUOTES option, names are
quoted within `"' characters.
As of MySQL 4.1.1, --quote-names is on by default,
but can be disabled with --skip-quote-names.
--result-file=file, -r file
--set-charset
SET NAMES default_character_set to the output. This option
is enabled by default. To suppress the SET NAMES statement, use
--skip-set-charset. This option was added in MySQL 4.1.2.
--single-transaction
BEGIN SQL statement before dumping data from
the server. It is mostly useful with InnoDB tables and the default
REPEATABLE READ transaction isolation level, because in this mode it
will dump the consistent state of the database at the time then
BEGIN was issued without blocking any applications.
When using this option, you should keep in mind that only InnoDB
tables will be dumped in a consistent state. For example, any MyISAM or
HEAP tables dumped while using this option may still change
state.
The --single-transaction option was added in MySQL 4.0.2.
This option is mutually exclusive with the --lock-tables option,
because LOCK TABLES causes any pending transactions to be committed
implicitly.
To dump big tables, you should combine this option with --quick.
--socket=path, -S path
localhost (which is the
default host).
--skip-comments
--comments option.
--tab=path, -T path
mysqldump
creates a `tbl_name.sql' file that contains the CREATE TABLE
statement that creates the table, and a `tbl_name.txt' file that
contains its data. The option value is the directory in which to write the
files.
By default, the `.txt' data files are formatted using tab characters
between column values and a newline at the end of each line. The format can
be specified explicitly using the --fields-xxx and
--lines--xxx options.
Note: This option should be used only when mysqldump is run
on the same machine as the mysqld server. You must use a MySQL
account that has the FILE privilege, and the server must have
permission to write files in the directory you specify.
--tables
--databases or -B option. All arguments
following the option are regarded as table names.
--user=user_name, -u user_name
--verbose, -v
--version, -V
--where='where-condition', -w 'where-condition'
WHERE condition. Note that
quotes around the condition are mandatory if it contains spaces or
characters that are special to your command interpreter.
Examples:
"--where=user='jimf'" "-wuserid>1" "-wuserid<1"
--xml, -X
You can also set the following variables by using --var_name=value
options:
max_allowed_packet
--extended-insert or --opt), mysqldump will create
rows up to max_allowed_packet length. If you increase this
variable, you should also ensure that the max_allowed_packet
variable in the MySQL server is at least this large.
net_buffer_length
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.
The most common use of mysqldump is probably for making a backup of
entire databases.
shell> mysqldump --opt db_name > backup-file.sql
You can read the dump file back into the server with:
shell> mysql db_name < backup-file.sql
Or:
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
If you want to dump all databases, use the --all-databases option:
shell> mysqldump --all-databases > all_databases.sql
If tables are stored in the InnoDB storage engine, mysqldump provides a
way of making an online backup of these (see command below). This backup just
needs to acquire 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 lock is released. So if and
only if one long updating statement is running when the FLUSH... is
issued, the MySQL server may get stalled until that long statement finishes,
and then the dump becomes lock-free. So if the MySQL server receives only
short (in the sense of "short execution time") updating statements, even if
there are plenty of them, the initial lock period should not be noticeable.
shell> mysqldump --all-databases --single-transaction > all_databases.sql
For point-in-time recovery (also known as "roll-forward", when you need to restore an old backup and replay the changes which happened since that backup), it is often useful to rotate the binary log (section 5.9.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 simultaneous use of --master-data and --single-transaction
works as of MySQL 4.1.8. It provides a convenient way to make an online backup
suitable for point-in-time recovery, if tables are stored in the InnoDB storage
engine.
For more information on making backups, see section 5.7.1 Database Backups.
mysqlhotcopy Database Backup Program
mysqlhotcopy is a Perl script that was originally written and
contributed by Tim Bunce. It uses LOCK TABLES,
FLUSH TABLES, and cp or scp to quickly make a backup of
a database. It's the fastest way to make a backup of the database or single
tables, but it can be run only on the same machine where the database
directories are located. mysqlhotcopy works only for backing up
MyISAM and ISAM tables.
It runs on Unix, and as of MySQL 4.0.18 also on NetWare.
shell> mysqlhotcopy db_name [/path/to/new_directory]
shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory
shell> mysqlhotcopy db_name./regex/
mysqlhotcopy supports the following options:
--help, -?
--allowold
_old
suffix).
--checkpoint=db_name.tbl_name
--debug
--dryrun, -n
--flushlog
--keepold
--method=#
cp or scp).
--noindices
myisamchk -rq
for MyISAM tables or isamchk -rq for ISAM tables.
--password=password, -ppassword
--port=port_num, -P port_num
--quiet, -q
--regexp=expr
--socket=path, -S path
--suffix=str
--tmpdir=path
--user=user_name, -u user_name
mysqlhotcopy reads the [client] and [mysqlhotcopy]
option groups from option files.
To execute mysqlhotcopy, you must have access to the files
for the tables that you are backing up, the SELECT privilege
for those tables, and the RELOAD privilege (to be able to
execute FLUSH TABLES).
Use perldoc for additional mysqlhotcopy documentation:
shell> perldoc mysqlhotcopy
mysqlimport 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.
See section 13.1.5 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 filename 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.
mysqlimport supports the following options:
--help, -?
--columns=column_list, -c column_list
--compress, -C
--debug[=debug_options], -# [debug_options]
'd:t:o,file_name'.
--delete, -D
--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...
LOAD DATA INFILE. See section 13.1.5 LOAD DATA INFILE Syntax.
--force, -f
--force,
mysqlimport exits if a table doesn't exist.
--host=host_name, -h host_name
localhost.
--ignore, -i
--replace option.
--ignore-lines=n
--local, -L
--lock-tables, -l
--password[=password], -p[password]
-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 will be
prompted for one.
--port=port_num, -P port_num
--protocol={TCP | SOCKET | PIPE | MEMORY}
--replace, -r
--replace and --ignore options control handling of input
records that duplicate existing records 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 don't specify either option, an
error occurs when a duplicate key value is found, and the rest of the text
file is ignored.
--silent, -s
--socket=path, -S path
localhost (which is the
default host).
--user=user_name, -u user_name
--verbose, -v
--version, -V
Here is a sample session that demonstrates use of mysqlimport:
shell> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test shell> ed a 100 Max Sydow 101 Count Dracula . w imptest.txt 32 q shell> od -c imptest.txt 0000000 1 0 0 \t M a x S y d o w \n 1 0 0000020 1 \t C o u n t D r a c u l a \n 0000040 shell> mysqlimport --local test imptest.txt test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 shell> mysql -e 'SELECT * FROM imptest' test +------+---------------+ | id | n | +------+---------------+ | 100 | Max Sydow | | 101 | Count Dracula | +------+---------------+
mysqlshow, Showing Databases, Tables, and Columns
The mysqlshow client can be used to quickly look at which databases
exist, their tables, and a table's columns or indexes.
mysqlshow provides a command-line interface to several SQL
SHOW statements. The same information can be obtained by using those
statements directly. For example, you can issue them from the mysql
client program.
See section 13.5.4 SHOW Syntax.
Invoke mysqlshow like this:
shell> mysqlshow [options] [db_name [tbl_name [col_name]]]
Note that in newer MySQL versions, you see only those database, 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 will require two) in
order 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:
--help, -?
--character-sets-dir=path
--compress, -C
--debug[=debug_options], -# [debug_options]
'd:t:o,file_name'.
--default-character-set=charset
--host=host_name, -h host_name
--keys, -k
--password[=password], -p[password]
-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 will be
prompted for one.
--port=port_num, -P port_num
--protocol={TCP | SOCKET | PIPE | MEMORY}
--socket=path, -S path
localhost (which is the
default host).
--status, -i
--user=user_name, -u user_name
--verbose, -v
--version, -V
perror, Explaining Error CodesFor most system errors, MySQL displays, in addition to an internal text message, the system error code in one of the following styles:
message ... (errno: #) message ... (Errcode: #)
You can find out what the error code means by either examining the
documentation for your system or by using the perror utility.
perror prints a description for a system error code or for a storage
engine (table handler) error code.
Invoke perror like this:
shell> perror [options] errorcode ...
Example:
shell> perror 13 64 Error code 13: Permission denied Error code 64: Machine is not on the network
Note that the meaning of system error messages may be dependent on your operating system. A given error code may mean different things on different operating systems.
replace String-Replacement Utility
The replace utility program changes strings in place in files or on
the standard input. It uses a finite state machine to match longer strings
first. It can be used to swap strings. For example, the following command
swaps a and b in the given files, `file1' and `file2':
shell> replace a b b a -- file1 file2 ...
Use the -- option to indicate where the string-replacement list
ends and the filenames begin.
Any file named on the command line is modified in place, so you may want to make a copy of the original before converting it.
If no files are named on the command line, replace reads the standard
input and writes to the standard output. In this case, no -- option is
needed.
The replace program is used by msql2mysql.
See section 21.1.1 msql2mysql, Convert mSQL Programs for Use with MySQL.
replace supports the following options:
-?, -I
-# debug_options
debug_options string often is
'd:t:o,file_name'.
-s
-v
-V
Go to the first, previous, next, last section, table of contents.