At Alden Hosting we eat and breathe Java! We are the industry leader in providing
affordable, quality and efficient Java web hosting in the shared hosting marketplace.
All our sites run on our Java hosing platform configured for
optimum performance using Java 1.6, Tomcat 6, MySQL 5, Apache 2.2 and web
application frameworks such as Struts, Hibernate, Cocoon, Ant, etc.

We offer only one type of Java hosting - Private Tomcat. Hosting accounts on the Private
Tomcat environment get their very own Tomcat server. You can start and re-start
your entire Tomcat server yourself.

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 establish a client
connection 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. Each program's description
indicates its invocation syntax and the options that it understands.
See Chapter 4, Using MySQL Programs, for general information
on invoking programs and specifying program options.

8.1. Overview of Client and Utility Programs

The following list briefly describes the MySQL client programs and
utilities:

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. See
Section 4.3, “Specifying Program Options”.

8.2. innochecksum — Offline InnoDB File Checksum Utility

innochecksum prints checksums for
InnoDB files.

Invoke innochecksum like this:

shell> innochecksum [options] file_name

innodchecksum understands the options
described in the following list. For options that refer to
page numbers, the numbers are zero-based.

-c

Print a count of the number of pages in the file.

-d

Debug mode; prints checksums for each page.

-e num

End at this page number.

-p num

Check only this page number.

-s num

Start at this page number.

-v

Verbose mode; print a progress indicator every five
seconds.

8.3. my_print_defaults — Display Options from Option Files

my_print_defaults displays the options that
are present in option groups of option files. The output
indicates what options will be used by programs that read the
specified option groups. For example, the
mysqlcheck program reads the
[mysqlcheck] and
[client] option groups. To see what options
are present in those groups in the standard option files,
invoke my_print_defaults like this:

The output consists of options, one per line, in the form that
they would be specified on the command line.

my_print_defaults understands the following
options:

--help, -?

Display a help message and exit.

--config-file=file_name,
--defaults-file=file_name,
-c file_name

Read only the given option file.

--debug=debug_options,
-# debug_options

Write a debugging log. The
debug_options string often is
'd:t:o,file_name'.
The default is
'd:t:o,/tmp/my_print_defaults.trace'.

--defaults-extra-file=file_name,
--extra-file=file_name,
-e file_name

Read this option file after the global option file but (on
Unix) before the user option file.

--defaults-group-suffix=suffix,
-g suffix

In addition to the groups named on the command line, read
groups that have the given suffix.

--no-defaults, -n

Return an empty string.

--verbose, -v

Verbose mode. Print more information about what the
program does.

--version, -V

Display version information and exit.

8.4. myisam_ftdump — Display Full-Text Index information

myisam_ftdump displays information about
FULLTEXT indexes in
MyISAM tables. It reads the
MyISAM index file directly, so it must be
run on the server host where the table is located

Invoke myisam_ftdump like this:

shell> myisam_ftdump [options] tbl_nameindex_num

The tbl_name argument should be the
name of a MyISAM table. You can also
specify a table by naming its index file (the file with the
.MYI suffix). If you do not invoke
myisam_ftdump in the directory where the
table files are located, the table or index file name must be
preceded by the pathname to the table's database directory.
Index numbers begin with 0.

Example: Suppose that the test database
contains a table named mytexttablel that
has the following definition:

The index on id is index 0 and the
FULLTEXT index on txt is
index 1. If your working directory is the
test database directory, invoke
myisam_ftdump as follows:

shell> myisam_ftdump mytexttable 1

If the pathname to the test database
directory is /usr/local/mysql/data/test,
you can also specify the table name argument using that
pathname. This is useful if you do not invoke
myisam_ftdump in the database directory:

shell> myisam_ftdump /usr/local/mysql/data/test/mytexttable 1

myisam_ftdump understands the following
options:

--help, -h-?

Display a help message and exit.

--count, -c

Calculate per-word statistics (counts and global weights).

--dump, -d

Dump the index, including data offsets and word weights.

--length, -l

Report the length distribution.

--stats, -s

Report global index statistics. This is the default
operation if no other operation is specified.

--verbose, -v

Verbose mode. Print more output about what the program
does.

8.5. myisamchk — MyISAM Table-Maintenance Utility

The myisamchk utility gets information
about your database tables or checks, repairs, or optimizes
them. myisamchk works with
MyISAM tables (tables that have
.MYD and .MYI files
for storing data and indexes).

Caution

It is best to make a backup of a table before performing a
table repair operation; under some circumstances the
operation might cause data loss. Possible causes include but
are not limited to filesystem errors.

Invoke myisamchk like this:

shell> myisamchk [options] tbl_name ...

The options specify what you want
myisamchk to do. They are described in the
following sections. You can also get a list of options by
invoking myisamchk --help.

With no options, myisamchk simply checks
your table as the default operation. To get more information
or to tell myisamchk to take corrective
action, specify options as described in the following
discussion.

tbl_name is the database table you
want to check or repair. If you run
myisamchk somewhere other than in the
database directory, you must specify the path to the database
directory, because myisamchk has no idea
where the database is located. In fact,
myisamchk doesn't actually care whether the
files you are working on are located in a database directory.
You can copy the files that correspond to a database table
into some other location and perform recovery operations on
them there.

You can name several tables on the
myisamchk command line if you wish. You can
also specify a table by naming its index file (the file with
the .MYI suffix). This allows you to
specify all tables in a directory by using the pattern
*.MYI. For example, if you are in a
database directory, you can check all the
MyISAM tables in that directory like this:

shell> myisamchk *.MYI

If you are not in the database directory, you can check all
the tables there by specifying the path to the directory:

shell> myisamchk /path/to/database_dir/*.MYI

You can even check all tables in all databases by specifying a
wildcard with the path to the MySQL data directory:

Important

You must ensure that no other program is using the
tables while you are running
myisamchk. The most effective
means of doing so is to shut down the MySQL server while
running myisamchk, or to lock all tables
that myisamchk is being used on.

Otherwise, when you run myisamchk, it may
display the following error message:

warning: clients are using or haven't closed the table properly

This means that you are trying to check a table that has
been updated by another program (such as the
mysqld server) that hasn't yet closed the
file or that has died without closing the file properly,
which can sometimes lead to the corruption of one or more
MyISAM tables.

If mysqld is running, you must force it
to flush any table modifications that are still buffered in
memory by using FLUSH TABLES. You should
then ensure that no one is using the tables while you are
running myisamchk

8.5.1. myisamchk General Options

The options described in this section can be used for any type
of table maintenance operation performed by
myisamchk. The sections following this one
describe options that pertain only to specific operations,
such as table checking or repairing.

--help, -?

Display a help message and exit.

--debug=debug_options,
-# debug_options

Write a debugging log. The
debug_options string often is
'd:t:o,file_name'.

--silent, -s

Silent mode. Write output only when errors occur. You can
use -s twice (-ss) to
make myisamchk very silent.

--verbose, -v

Verbose mode. Print more information about what the
program does. This can be used with -d
and -e. Use -v multiple
times (-vv, -vvv) for
even more output.

--version, -V

Display version information and exit.

--wait, -w

Instead of terminating with an error if the table is
locked, wait until the table is unlocked before
continuing. If you are running mysqld
with external locking disabled, the table can be locked
only by another myisamchk command.

You can also set the following variables by using
--var_name=value
syntax:

Variable

Default Value

decode_bits

9

ft_max_word_len

version-dependent

ft_min_word_len

4

ft_stopword_file

built-in list

key_buffer_size

523264

myisam_block_size

1024

read_buffer_size

262136

sort_buffer_size

2097144

sort_key_blocks

16

stats_method

nulls_unequal

write_buffer_size

262136

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 possible myisamchk variables and their
default values can be examined with myisamchk
--help:

sort_buffer_size is used when the keys are
repaired by sorting keys, which is the normal case when you
use --recover.

key_buffer_size is used when you are
checking the table with --extend-check or
when the keys are repaired by inserting keys row by row into
the table (like when doing normal inserts). Repairing through
the key buffer is used in the following cases:

You use --safe-recover.

The temporary files needed to sort the keys would be more
than twice as big as when creating the key file directly.
This is often the case when you have large key values for
CHAR, VARCHAR, or
TEXT columns, because the sort
operation needs to store the complete key values as it
proceeds. If you have lots of temporary space and you can
force myisamchk to repair by sorting,
you can use the --sort-recover option.

Repairing through the key buffer takes much less disk space
than using sorting, but is also much slower.

If you want a faster repair, set the
key_buffer_size and
sort_buffer_size variables to about 25% of
your available memory. You can set both variables to large
values, because only one of them is used at a time.

myisam_block_size is the size used for
index blocks.

stats_method influences how
NULL values are treated for index
statistics collection when the --analyze
option is given. It acts like the
myisam_stats_method system variable. For
more information, see the description of
myisam_stats_method in
Section 5.2.3, “System Variables”, and
Section 7.4.7, “MyISAM Index Statistics Collection”. For MySQL
5.0, stats_method was added in
MySQL 5.0.14. For older versions, the statistics collection
method is equivalent to nulls_equal.

The ft_min_word_len and
ft_max_word_len variables are available as
of MySQL 4.0.0. ft_stopword_file is
available as of MySQL 4.0.19.

ft_min_word_len and
ft_max_word_len indicate the minimum and
maximum word length for FULLTEXT indexes.
ft_stopword_file names the stopword file.
These need to be set under the following circumstances.

If you use myisamchk to perform an
operation that modifies table indexes (such as repair or
analyze), the FULLTEXT indexes are rebuilt
using the default full-text parameter values for minimum and
maximum word length and the stopword file unless you specify
otherwise. This can result in queries failing.

The problem occurs because these parameters are known only by
the server. They are not stored in MyISAM
index files. To avoid the problem if you have modified the
minimum or maximum word length or the stopword file in the
server, specify the same ft_min_word_len,
ft_max_word_len, and
ft_stopword_file values to
myisamchk that you use for
mysqld. For example, if you have set the
minimum word length to 3, you can repair a table with
myisamchk like this:

shell> myisamchk --recover --ft_min_word_len=3 tbl_name.MYI

To ensure that myisamchk and the server use
the same values for full-text parameters, you can place each
one in both the [mysqld] and
[myisamchk] sections of an option file:

[mysqld]
ft_min_word_len=3
[myisamchk]
ft_min_word_len=3

An alternative to using myisamchk is to use
the REPAIR TABLE, ANALYZE
TABLE, OPTIMIZE TABLE, or
ALTER TABLE. These statements are performed
by the server, which knows the proper full-text parameter
values to use.

8.5.2. myisamchk Check Options

myisamchk supports the following options
for table checking operations:

--check, -c

Check the table for errors. This is the default operation
if you specify no option that selects an operation type
explicitly.

--check-only-changed, -C

Check only tables that have changed since the last check.

--extend-check, -e

Check the table very thoroughly. This is quite slow if the
table has many indexes. This option should only be used in
extreme cases. Normally, myisamchk or
myisamchk --medium-check should be able
to determine whether there are any errors in the table.

If you are using --extend-check and have
plenty of memory, setting the
key_buffer_size variable to a large
value helps the repair operation run faster.

--fast, -F

Check only tables that haven't been closed properly.

--force, -f

Do a repair operation automatically if
myisamchk finds any errors in the
table. The repair type is the same as that specified with
the --recover or -r
option.

--information, -i

Print informational statistics about the table that is
checked.

--medium-check, -m

Do a check that is faster than an
--extend-check operation. This finds only
99.99% of all errors, which should be good enough in most
cases.

--read-only, -T

Don't mark the table as checked. This is useful if you use
myisamchk to check a table that is in
use by some other application that doesn't use locking,
such as mysqld when run with external
locking disabled.

--update-state, -U

Store information in the .MYI file to
indicate when the table was checked and whether the table
crashed. This should be used to get full benefit of the
--check-only-changed option, but you
shouldn't use this option if the mysqld
server is using the table and you are running it with
external locking disabled.

Maximum length of the data file (when re-creating data
file when it is “full”).

--extend-check, -e

Do a repair that tries to recover every possible row from
the data file. Normally, this also finds a lot of garbage
rows. Don't use this option unless you are desperate.

--force, -f

Overwrite old intermediate files (files with names like
tbl_name.TMD)
instead of aborting.

--keys-used=val,
-k val

For myisamchk, the option value is a
bit-value that indicates which indexes to update. Each
binary bit of the option value corresponds to a table
index, where the first index is bit 0. An option value of
0 disables updates to all indexes, which can be used to
get faster inserts. Deactivated indexes can be reactivated
by using myisamchk -r.

--no-symlinks, -l

Do not follow symbolic links. Normally
myisamchk repairs the table that a
symlink points to. This option does not exist as of MySQL
4.0 because versions from 4.0 on do not remove symlinks
during repair operations.

--max-record-length=len

Skip rows larger than the given length if
myisamchk cannot allocate memory to
hold them.

--parallel-recover, -p

Uses the same technique as -r and
-n, but creates all the keys in parallel,
using different threads. This is beta-quality
code. Use at your own risk!

--quick, -q

Achieve a faster repair by not modifying the data file.
You can specify this option twice to force
myisamchk to modify the original data
file in case of duplicate keys.

--recover, -r

Do a repair that can fix almost any problem except unique
keys that aren't unique (which is an extremely unlikely
error with MyISAM tables). If you want
to recover a table, this is the option to try first. You
should try --safe-recover only if
myisamchk reports that the table can't
be recovered using --recover. (In the
unlikely case that --recover fails, the
data file remains intact.)

If you have lots of memory, you should increase the value
of sort_buffer_size.

--safe-recover, -o

Do a repair using an old recovery method that reads
through all rows in order and updates all index trees
based on the rows found. This is an order of magnitude
slower than --recover, but can handle a
couple of very unlikely cases that
--recover cannot. This recovery method
also uses much less disk space than
--recover. Normally, you should repair
first with --recover, and then with
--safe-recover only if
--recover fails.

If you have lots of memory, you should increase the value
of key_buffer_size.

--set-character-set=name

Change the character set used by the table indexes. This
option was replaced by --set-collation in
MySQL 5.0.3.

--set-collation=name

Specify the collation to use for sorting table indexes.
The character set name is implied by the first part of the
collation name. This option was added in MySQL 5.0.3.

--sort-recover, -n

Force myisamchk to use sorting to
resolve the keys even if the temporary files would be very
large.

--tmpdir=path,
-t path

Path of the directory to be used for storing temporary
files. If this is not set, myisamchk
uses the value of the TMPDIR
environment variable. tmpdir can be set
to a list of directory paths that are used successively in
round-robin fashion for creating temporary files. The
separator character between directory names is the colon
(‘:’) on Unix and the
semicolon (‘;’) on Windows,
NetWare, and OS/2.

--unpack, -u

Unpack a table that was packed with
myisampack.

8.5.4. Other myisamchk Options

myisamchk supports the following options
for actions other than table checks and repairs:

--analyze, -a

Analyze the distribution of key values. This improves join
performance by enabling the join optimizer to better
choose the order in which to join the tables and which
indexes it should use. To obtain information about the key
distribution, use a myisamchk --description
--verbose tbl_name
command or the SHOW INDEX FROM
tbl_name statement.

Force AUTO_INCREMENT numbering for new
records to start at the given value (or higher, if there
are existing records with
AUTO_INCREMENT values this large). If
value is not specified,
AUTO_INCREMENT numbers for new records
begin with the largest value currently in the table, plus
one.

--sort-index, -S

Sort the index tree blocks in high-low order. This
optimizes seeks and makes table scans that use indexes
faster.

--sort-records=N,
-R N

Sort records according to a particular index. This makes
your data much more localized and may speed up range-based
SELECT and ORDER BY
operations that use this index. (The first time you use
this option to sort a table, it may be very slow.) To
determine a table's index numbers, use SHOW
INDEX, which displays a table's indexes in the
same order that myisamchk sees them.
Indexes are numbered beginning with 1.

If keys are not packed (PACK_KEYS=0),
they have the same length, so when
myisamchk sorts and moves records, it
just overwrites record offsets in the index. If keys are
packed (PACK_KEYS=1),
myisamchk must unpack key blocks first,
then re-create indexes and pack the key blocks again. (In
this case, re-creating indexes is faster than updating
offsets for each index.)

8.5.5. myisamchk Memory Usage

Memory allocation is important when you run
myisamchk. myisamchk
uses no more memory than its memory-related variables are set
to. If you are going to use myisamchk on
very large tables, you should first decide how much memory you
want it to use. The default is to use only about 3MB to
perform repairs. By using larger values, you can get
myisamchk to operate faster. For example,
if you have more than 32MB RAM, you could use options such as
these (in addition to any other options you might specify):

Using --sort_buffer_size=16M should probably
be enough for most cases.

Be aware that myisamchk uses temporary
files in TMPDIR. If
TMPDIR points to a memory filesystem, you
may easily get out of memory errors. If this happens, run
myisamchk with the
--tmpdir=path
option to specify some directory located on a filesystem that
has more space.

When repairing, myisamchk also needs a lot
of disk space:

Double the size of the data file (the original file and a
copy). This space is not needed if you do a repair with
--quick; in this case, only the index
file is re-created. This space is needed on the same
filesystem as the original data file! (The copy is created
in the same directory as the original.)

Space for the new index file that replaces the old one.
The old index file is truncated at the start of the repair
operation, so you usually ignore this space. This space is
needed on the same filesystem as the original index file!

When using --recover or
--sort-recover (but not when using
--safe-recover), you need space for a
sort buffer. The following formula yields the amount of
space required:

(largest_key + row_pointer_length) × number_of_rows × 2

You can check the length of the keys and the
row_pointer_length with
myisamchk -dv
tbl_name. This space
is allocated in the temporary directory (specified by
TMPDIR or
--tmpdir=path).

If you have a problem with disk space during repair, you can
try --safe-recover instead of
--recover.

8.6. myisamlog — Display MyISAM Log File Contents

The default operation is update (-u). If a
recovery is done (-r), all writes and
possibly updates and deletes are done and errors are only
counted. The default log file name is
myisam.log for
myisamlog and isam.log
for isamlog if no
log_file argument is given, If
tables are named on the command line, only those tables are
updated.

myisamlog understands the following
options:

-?, -I

Display a help message and exit.

-c N

Execute only N commands.

-f N

Specify the maximum number of open files.

-i

Display extra information before exiting.

-o offset

Specify the starting offset.

-p N

Remove N components from path.

-r

Perform a recovery operation.

-R record_pos_file
record_pos

Specify record position file and record position.

-u

Perform an update operation.

-v

Verbose mode. Print more output about what the program
does. This option can be given multiple times to produce
more and more output.

-w write_file

Specify the write file.

-V

Display version information.

8.7. myisampack — Generate Compressed, Read-Only MyISAM Tables

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 server reads into memory the
information needed to decompress columns. This results in much
better performance when accessing individual rows, because you
only have to uncompress exactly one row.

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. References to myisamchk should
be read as references to isamchk if you are
using pack_isam.

Please note the following:

If the mysqld server was invoked with
external locking disabled, it is not a good idea to invoke
myisampack if the table might be
updated by the server during the packing process. It is
safest to compress tables with the server stopped.

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 did not have this
capability.)

Invoke myisampack like this:

shell> myisampack [options] file_name ...

Each filename argument 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.

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).

--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
myisampack 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 external
locking disabled, 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:

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 data 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:

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.

8.8. mysql — The MySQL 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 options.

If you have problems due to insufficient memory for large
result sets, use the --quick option. This
forces mysql to retrieve results from the
server a row at a time rather than retrieving the entire
result set and buffering it in memory before displaying it.
This is done by returning the result set using the
mysql_use_result() C API function in the
client/server library rather than
mysql_store_result().

Using mysql is very easy. Invoke it from
the prompt of your command interpreter as follows:

shell> mysql db_name

Or:

shell> mysql --user=user_name --password=your_passworddb_name

Then type an SQL statement, end it with
‘;’, \g, or
\G and press Enter.

As of MySQL 5.0.25, typing Control-C causes
mysql to attempt to kill the current
statement. If this cannot be done, or Control-C is typed again
before the statement is killed, mysql
exits. Previously, Control-C caused mysql
to exit in all cases.

You can execute SQL statements in a script file (batch file)
like this:

shell> mysql db_name < script.sql > output.tab

8.8.1. mysql Options

mysql supports the following options:

--help, -?

Display a help message and exit.

--auto-rehash

Enable automatic rehashing. This option is on by default,
which enables table and column name completion. Use
--skip-auto-rehash to disable rehashing.
That causes mysql to start faster, but
you must issue the rehash command if
you want to use table and column name completion.

--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.

Ignore spaces after function names. The effect of this is
described in the discussion for the
IGNORE_SPACE SQL mode (see
Section 5.2.6, “SQL Modes”).

--line-numbers

Write line numbers for errors. Disable this with
--skip-line-numbers.

--local-infile[={0|1}]

Enable or disable LOCAL capability for
LOAD DATA INFILE. With no value, the
option enables LOCAL. The option may be
given as --local-infile=0 or
--local-infile=1 to explicitly disable or
enable LOCAL. Enabling
LOCAL has no effect if the server does
not also support it.

Enable named mysql commands.
Long-format commands are allowed, not just short-format
commands. For example, quit and
\q both are recognized. Use
--skip-named-commands to disable named
commands. See Section 8.8.2, “mysql Commands”.

--no-auto-rehash, -A

Deprecated form of -skip-auto-rehash. See
the description for --auto-rehash.

--no-beep, -b

Do not beep when errors occur.

--no-named-commands, -g

Disable named commands. Use the \* form
only, or use named commands only at the beginning of a
line ending with a semicolon
(‘;’).
mysql starts with this option
enabled by default. However, even
with this option, long-format commands still work from the
first line. See Section 8.8.2, “mysql Commands”.

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. To disable
paging, use --skip-pager.
Section 8.8.2, “mysql Commands”, discusses output paging
further.

--password[=password],
-p[password]

The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the
option and the password. If you omit the
password value following the
--password or -p option
on the command line, you are prompted for one.

Set the prompt to the specified format. The default is
mysql>. The special sequences that
the prompt can contain are described in
Section 8.8.2, “mysql Commands”.

--protocol={TCP|SOCKET|PIPE|MEMORY}

The connection protocol to use.

--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.

--safe-updates,
--i-am-a-dummy, -U

Allow only those UPDATE and
DELETE statements that specify which
rows to modify by using key values. If you have set this
option in an option file, you can override it by using
--safe-updates on the command line. See
Section 8.8.5, “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.

Verbose mode. Produce more output about what the program
does. This option can be given multiple times to produce
more and more output. (For example, -v -v
-v produces table output format even in batch
mode.)

--version, -V

Display version information and exit.

--vertical, -E

Print query output rows vertically (one line per column
value). Without this option, you can specify vertical
output for individual statements by terminating them with
\G.

--wait, -w

If the connection cannot be established, wait and retry
instead of aborting.

--xml, -X

Produce XML output.

Note: Prior to MySQL
5.0.26, there was no differentiation in the output when
using this option between columns containing the
NULL value and columns containing the
string literal 'NULL'; both were
represented as

You can also set the following variables by using
--var_name=value
syntax:

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. 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.8.2. mysql Commands

mysql sends each SQL statement that you
issue to the server to be executed. There is also a set of
commands that mysql itself interprets. For
a list of these commands, type help or
\h at the mysql>
prompt:

mysql> help
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
charset (\C) Switch to another charset. Might be needed for processing
binlog with multi-byte charsets.
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.
For server side help, type 'help contents'

Each command has both a long and short form. The long form is
not case sensitive; the short form is. The long form can be
followed by an optional semicolon terminator, but the short
form should not.

If you provide an argument to the help
command, mysql uses it as a search string
to access server-side help from the contents of the MySQL
Reference Manual. For more information, see
Section 8.8.3, “mysql Server-Side Help”.

The charset command changes the default
character set and issues a SET NAMES
statement. This enables the character set to remain
synchronized on the client and server if
mysql is run with auto-reconnect enabled
(which is not recommended), because the changed character set
is used for reconnects. This command was added in MySQL
5.0.25.

In the delimiter command, you should avoid
the use of the backslash (‘\’)
character because that is the escape character for MySQL.

The edit, nopager,
pager, and system
commands work only in Unix.

The status command provides some
information about the connection and the server you are using.
If you are running in --safe-updates mode,
status also prints the values for the
mysql variables that affect your queries.

To log queries and their output, use the
tee command. All the data displayed on the
screen is appended into a given file. This can be very useful
for debugging purposes also. You can enable this feature on
the command line with the --tee option, or
interactively with the tee command. The
tee file can be disabled interactively with
the notee command. Executing
tee again re-enables logging. Without a
parameter, the previous file is used. Note that
tee flushes query results to the file after
each statement, just before mysql prints
its next prompt.

By using the --pager option, it is possible
to browse or search query results in interactive mode with
Unix programs such as less,
more, or any other similar program. If you
specify no value for the option, mysql
checks the value of the PAGER environment
variable and sets the pager to that. Output paging can be
enabled interactively with the pager
command and disabled with nopager. The
command takes an optional argument; if given, the paging
program is set to that. With no argument, the pager is set to
the pager that was set on the command line, or
stdout if no pager was specified.

Output paging works only in Unix because it uses the
popen() function, which does not exist on
Windows. For Windows, the tee option can be
used instead to save query output, although this is not as
convenient as pager for browsing output in
some situations.

Here are a few tips about the pager
command:

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:

In this example, the command would send query results to
two files in two different directories on two different
filesystems mounted on /dr1 and
/dr2, yet still display the results
onscreen via less.

You can also combine the tee and
pager functions. Have a
tee file enabled and
pager set to less, and
you are able to browse the results using the
less program and still have everything
appended into a file the same time. The difference between the
Unix tee used with the
pager command and the
mysql built-in tee
command is that the built-in tee works even
if you do not have the Unix tee available.
The built-in tee also logs everything that
is printed on the screen, whereas the Unix
tee used with pager does
not log quite that much. Additionally, tee
file logging can be turned on and off interactively from
within mysql. This is useful when you want
to log some queries to a file, but not others.

The default mysql> prompt can be
reconfigured. The string for defining the prompt can contain
the following special sequences:

Option

Description

\v

The server version

\d

The default database

\h

The server host

\p

The current TCP/IP port or socket file

\u

Your username

\U

Your full
user_name@host_name
account 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

\l

The current delimiter. (New in 5.0.25)

\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 a command-line option. You can
set the --prompt option on the command
line to mysql. For example:

shell> mysql --prompt="(\u@\h) [\d]> "
(user@host) [database]>

Use an option file. You can set the
prompt option in the
[mysql] group of any MySQL option file,
such as /etc/my.cnf or the
.my.cnf file in your home directory.
For example:

[mysql]
prompt=(\\u@\\h) [\\d]>\\_

In this example, note that the backslashes are doubled. If
you set the prompt using the prompt
option in an option file, it is advisable to double the
backslashes when using the special prompt options. There
is some overlap in the set of 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> "

Set the prompt interactively. You can
change your prompt interactively by using the
prompt (or \R)
command. For example:

8.8.3. mysql Server-Side Help

mysql> help search_string

If you provide an argument to the help
command, mysql uses it as a search string
to access server-side help from the contents of the MySQL
Reference Manual. The proper operation of this command
requires that the help tables in the mysql
database be initialized with help topic information (see
Section 5.2.8, “Server-Side Help”).

If there is no match for the search string, the search fails:

mysql> help me
Nothing found
Please try to run 'help contents' for a list of all accessible topics

Use help contents to see a list of the help
categories:

mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the
following categories:
Account Management
Administration
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Language Structure
Storage Engines
Stored Routines
Table Maintenance
Transactions
Triggers

mysql> help logs
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following topics:
SHOW
SHOW BINARY LOGS
SHOW ENGINE
SHOW LOGS

Use a topic as the search string to see the help entry for
that topic:

mysql> help show binary logs
Name: 'SHOW BINARY LOGS'
Description:
Syntax:
SHOW BINARY LOGS
SHOW MASTER LOGS
Lists the binary log files on the server. This statement is used as
part of the procedure described in [purge-master-logs], that shows how
to determine which logs can be purged.
mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000015 | 724935 |
| binlog.000016 | 733481 |
+---------------+-----------+

8.8.4. Executing SQL Statements from a Text File

The mysql client typically is used
interactively, like this:

shell> mysql db_name

However, it is also possible to put your SQL statements in a
file and then tell mysql to read its input
from that file. To do so, create a text file
text_file that contains the
statements you wish to execute. Then invoke
mysql as shown here:

shell> mysql db_name < text_file

If you place a USE
db_name statement as the
first statement in the file, it is unnecessary to specify the
database name on the command line:

shell> mysql < text_file

If you are already running mysql, you can
execute an SQL script file using the source
command or \. command:

mysql> source file_name
mysql> \. file_name

Sometimes you may want your script to display progress
information to the user. For this you can insert statements
like this:

8.8.5. mysql Tips

This section describes some techniques that can help you use
mysql more effectively.

8.8.5.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:

8.8.5.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).
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;

The server limits all large SELECT
results to 1,000 rows unless the statement includes a
LIMIT clause.

The server aborts multiple-table
SELECT statements that probably need
to examine more than 1,000,000 row combinations.

To specify limits different from 1,000 and 1,000,000, you
can override the defaults by using the
--select_limit and
--max_join_size options:

shell> mysql --safe-updates --select_limit=500 --max_join_size=10000

8.8.5.3. Disabling mysql Auto-Reconnect

If the mysql client loses its connection
to the server while sending a statement, it immediately and
automatically tries to reconnect once to the server and send
the statement again. However, even if
mysql succeeds in reconnecting, your
first connection has ended and all your previous session
objects and settings are lost: temporary tables, the
autocommit mode, and user-defined and session variables.
Also, any current transaction rolls back. This behavior may
be dangerous for you, as in the following example where the
server was shut down and restarted between the first and
second statements without you knowing it:

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.9. 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.

Invoke mysqlaccess like this:

shell> mysqlaccess [host_name [user_name [db_name]]] [options]

mysqlaccess understands the following
options:

--help, -?

Display a help message and exit.

--brief, -b

Generate reports in single-line tabular format.

--commit

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.

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.10. mysqladmin — Client for Administering a MySQL Server

mysqladmin is a client for performing
administrative operations. You can use it to check the
server's configuration and current status, to create and drop
databases, and more.

Set a new password. This changes the password to
new-password for the account
that you use with mysqladmin for
connecting to the server. Thus, the next time you invoke
mysqladmin (or any other client
program) using the same account, you will need to specify
the new password.

If the new-password value
contains spaces or other characters that are special to
your command interpreter, you need to enclose it within
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. This is 0 even in case of an
error such as Access denied, because
this means that the server is running but refused the
connection, which is different from the server not
running.

processlist

Show a list of active server threads. This is like the
output of the SHOW PROCESSLIST
statement. If the --verbose option is
given, the output is like that of SHOW FULL
PROCESSLIST. (See
Section 13.5.4.21, “SHOW PROCESSLIST Syntax”.)

The number of flush-*,
refresh, and reload
commands the server has executed.

Open tables

The number of tables that currently are open.

Memory in use

The amount of memory allocated directly by
mysqld. This value is displayed only
when MySQL has been compiled with
--with-debug=full.

Maximum memory used

The maximum amount of memory allocated directly by
mysqld. 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
db_name command. With
multiple commands, continue even if an error occurs.

--host=host_name,
-h host_name

Connect to the MySQL server on the given host.

--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.

Print output vertically. This is similar to
--relative, but prints output vertically.

--wait[=count],
-w[count]

If the connection cannot be established, wait and retry
instead of aborting. If a count
value is given, it indicates the number of times to retry.
The default is one time.

You can also set the following variables by using
--var_name=value
syntax:

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 server 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. This syntax is deprecated.

8.11. 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. 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.

Invoke mysqlbinlog like this:

shell> mysqlbinlog [options] log_file ...

For example, to display the contents of the binary log file
named binlog.000003, use this command:

shell> mysqlbinlog binlog.0000003

The output includes all events contained in
binlog.000003. Event information includes
the statement executed, the time the statement took, the
thread ID of the client that issued it, the timestamp when it
was executed, and so forth.

The output from mysqlbinlog can be
re-executed (for example, by using it as input to
mysql) to reapply the statements in the
log. This is useful for recovery operations after a server
crash. For other usage examples, see the discussion later in
this section.

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. These options are --host,
--password, --port,
--protocol, --socket, and
--user; they are ignored except when you also
use the --read-from-remote-server option.

List entries for just this database (local log only). You
can only specify one database with this option - if you
specify multiple --database options, only
the last one is used. This option forces
mysqlbinlog to output entries from the
binary log where the default database (that is, the one
selected by USE) is
db_name. Note that this does
not replicate cross-database statements such as
UPDATE
some_db.some_table SET
foo='bar' while having selected a different
database or no database.

--debug[=debug_options],
-#
[debug_options]

Write a debugging log. A typical
debug_options string is often
'd:t:o,file_name'.

--disable-log-bin, -D

Disable binary logging. This is useful for avoiding an
endless loop if you use the --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 have
logged.

This option requires that you have the
SUPER privilege. It causes
mysqlbinlog to include a SET
SQL_LOG_BIN=0 statement in its output to disable
binary logging of the remaining output. The
SET statement is ineffective unless you
have the SUPER privilege.

--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.

--hexdump, -H

Display a hex dump of the log in comments. This output can
be helpful for replication debugging. Hex dump format is
discussed later in this section. This option was added in
MySQL 5.0.16.

--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 in the
log.

--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.

Read the binary log from a MySQL server rather than
reading a local log file. 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.

--set-charset=charset_name

Add a SET NAMES
charset_name
statement to the output to specify the character set to be
used for processing log files. This option was added in
MySQL 5.0.23.

--short-form, -s

Display only the statements contained in the log, without
any extra information.

--socket=path,
-S path

For connections to localhost, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.

--start-datetime=datetime

Start reading the binary log at the first event having a
timestamp 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:

Stop reading the binary log at the first event having a
timestamp equal or posterior to the
datetime argument. This option
is useful for point-in-time recovery. See the description
of the --start-datetime option for
information about the datetime
value.

--start-position=N

Start reading the binary log at the first event having a
position equal to the N
argument. This option applies to the first log file named
on the command line.

--stop-position=N

Stop reading the binary log at the first event having a
position equal or greater than the
N argument. This option applies
to the last log file named on the command line.

--to-last-log, -t

Do not stop at the end of the requested binary log from a
MySQL server, but rather continue printing until the end
of the last binary log. If you send the output to the same
MySQL server, this may lead to an endless loop. This
option requires
--read-from-remote-server.

--user=user_name,
-u user_name

The MySQL username to use when connecting to a remote
server.

--version, -V

Display version information and exit.

You can also set the following variable by using
--var_name=value
syntax:

open_files_limit

Specify the number of open file descriptors to reserve.

It is also possible to set variables by using
--set-variable=var_name=value
or -O
var_name=value
syntax. This syntax is deprecated.

You can pipe the output of mysqlbinlog into
the 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.9.1, “Database Backups”). For example:

shell> mysqlbinlog binlog.000001 | mysql

Or:

shell> mysqlbinlog binlog.[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 do not 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
--start-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 and start
when it sees an event with 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 a.m.”).

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:

Processing binary logs this way using different connections to
the server causes 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 drops the temporary table. When the second
mysql process attempts to use the table,
the server reports “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 so:

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql

Another approach is to write all the logs to a single file and
then process the file:

mysqlbinlog can produce output that
reproduces a 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.6.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-#-#.

The --hexdump option produces a hex dump of
the log contents in comments:

Hex dump output currently contains the following elements.
This format might change in the future.

Position: The byte position within the
log file.

Timestamp: The event timestamp. In the
example shown, '9d fc 5c 43' is the
representation of '051024 17:24:13' in
hexadecimal.

Type: The type of the log event. In the
example shown, '0f' means that the
example event is a
FORMAT_DESCRIPTION_EVENT. The following
table lists the possible types.

Type

Name

Meaning

00

UNKNOWN_EVENT

This event should never be present in the log.

01

START_EVENT_V3

This indicates the start of a log file written by MySQL 4 or earlier.

02

QUERY_EVENT

The most common type of events. These contain statements executed on the
master.

03

STOP_EVENT

Indicates that master has stopped.

04

ROTATE_EVENT

Written when the master switches to a new log file.

05

INTVAR_EVENT

Used mainly for AUTO_INCREMENT values and when the
LAST_INSERT_ID() function is
used in the statement.

06

LOAD_EVENT

Used for LOAD DATA INFILE in MySQL 3.23.

07

SLAVE_EVENT

Reserved for future use.

08

CREATE_FILE_EVENT

Used for LOAD DATA INFILE statements. This indicates
the start of execution of such a statement. A
temporary file is created on the slave. Used in
MySQL 4 only.

09

APPEND_BLOCK_EVENT

Contains data for use in a LOAD DATA INFILE
statement. The data is stored in the temporary
file on the slave.

0a

EXEC_LOAD_EVENT

Used for LOAD DATA INFILE statements. The contents of
the temporary file is stored in the table on the
slave. Used in MySQL 4 only.

0b

DELETE_FILE_EVENT

Rollback of a LOAD DATA INFILE statement. The
temporary file should be deleted on slave.

0c

NEW_LOAD_EVENT

Used for LOAD DATA INFILE in MySQL 4 and earlier.

0d

RAND_EVENT

Used to send information about random values if the
RAND() function is used in the
statement.

0e

USER_VAR_EVENT

Used to replicate user variables.

0f

FORMAT_DESCRIPTION_EVENT

This indicates the start of a log file written by MySQL 5 or later.

10

XID_EVENT

Event indicating commit of an XA transaction.

11

BEGIN_LOAD_QUERY_EVENT

Used for LOAD DATA INFILE statements in MySQL 5 and
later.

12

EXECUTE_LOAD_QUERY_EVENT

Used for LOAD DATA INFILE statements in MySQL 5 and
later.

13

TABLE_MAP_EVENT

Reserved for future use.

14

WRITE_ROWS_EVENT

Reserved for future use.

15

UPDATE_ROWS_EVENT

Reserved for future use.

16

DELETE_ROWS_EVENT

Reserved for future use.

Master ID: The server id of the master
that created the event.

Size: The size in bytes of the event.

Master Pos: The position of the event
in the original master log file.

Flags: 16 flags. Currently, the
following flags are used. The others are reserved for the
future.

Flag

Name

Meaning

01

LOG_EVENT_BINLOG_IN_USE_F

Log file correctly closed. (Used only in
FORMAT_DESCRIPTION_EVENT.) If
this flag is set (if the flags are, for example,
'01 00') in a
FORMAT_DESCRIPTION_EVENT, the
log file has not been properly closed. Most
probably this is because of a master crash (for
example, due to power failure).

02

Reserved for future use.

04

LOG_EVENT_THREAD_SPECIFIC_F

Set if the event is dependent on the connection it was executed in (for
example, '04 00'), for example,
if the event uses temporary tables.

08

LOG_EVENT_SUPPRESS_USE_F

Set in some circumstances when the event is not dependent on the default
database.

The other flags are reserved for future use.

8.12. mysqlcheck — A Table Maintenance and Repair Program

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, and then sends the statements to the
server to be executed. For details about which storage engines
each statement works with, see the descriptions for those
statements in Chapter 13, SQL Statement Syntax.

The MyISAM storage engine supports all four
statements, so mysqlcheck can be used to
perform all four operations on MyISAM
tables. Other storage engines do not necessarily support all
operations. In such cases, an error message is displayed. For
example, if test.t is a
MEMORY table, an attempt to check it
produces this result:

Caution

It is best to make a backup of a table before performing a
table repair operation; under some circumstances the
operation might cause data loss. Possible causes include but
are not limited to filesystem errors.

If you do not name any tables following
db_name or if you use the
--databases or
--all-databases option, entire databases are
checked.

mysqlcheck has a special feature compared
to other client programs. The default behavior of checking
tables (--check) can be changed by renaming
the binary. If you want to have a tool that repairs tables by
default, you should just make a copy of
mysqlcheck named
mysqlrepair, or make a symbolic link to
mysqlcheck named
mysqlrepair. If you invoke
mysqlrepair, it repairs tables.

The following names can be used to change
mysqlcheck default behavior:

mysqlrepair

The default option is --repair

mysqlanalyze

The default option is --analyze

mysqloptimize

The default option is --optimize

mysqlcheck supports the following options:

--help, -?

Display a help message and exit.

--all-databases, -A

Check all tables in all databases. This is the same as
using the --databases option and naming
all the databases on the command line.

--all-in-1, -1

Instead of issuing a statement for each table, execute a
single statement for each database that names all the
tables from that database to be processed.

Check only tables that have changed since the last check
or that have not been closed properly.

--check-upgrade, -g

Invoke CHECK TABLE with the
FOR UPGRADE option to check tables for
incompatibilities with the current version of the server.
This option was added in MySQL 5.0.19.

--compress

Compress all information sent between the client and the
server if both support compression.

--databases, -B

Process all tables in the named databases. Normally,
mysqlcheck treats the first name
argument on the command line as a database name and
following names as table names. With this option, it
treats all name arguments as database names.

--debug[=debug_options],
-#
[debug_options]

Write a debugging log. A typical
debug_options string is often
'd:t:o,file_name'.

If you are using this option to check tables, it ensures
that they are 100% consistent but takes a long time.

If you are using this option to repair tables, it runs an
extended repair that may not only take a long time to
execute, but may produce a lot of garbage rows also!

--fast, -F

Check only tables that have not been closed properly.

--force, -f

Continue even if an SQL error occurs.

--host=host_name,
-h host_name

Connect to the MySQL server on the given host.

--medium-check, -m

Do a check that is faster than an
--extended operation. This finds only
99.99% of all errors, which should be good enough in most
cases.

--optimize, -o

Optimize the tables.

--password[=password],
-p[password]

The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the
option and the password. If you omit the
password value following the
--password or -p option
on the command line, you are prompted for one.

Overrides the --databases or
-B option. All name arguments following
the option are regarded as table names.

--use-frm

For repair operations on MyISAM tables,
get the table structure from the .frm
file so that the table can be repaired even if the
.MYI header is corrupted.

--user=user_name,
-u user_name

The MySQL username to use when connecting to the server.

--verbose, -v

Verbose mode. Print information about the various stages
of program operation.

--version, -V

Display version information and exit.

8.13. mysqldump — A Database Backup Program

The mysqldump client is a backup program
originally written by Igor Romanenko. It can be used to dump a
database or a collection of databases for backup or transfer
to another SQL server (not necessarily a MySQL server). The
dump typically contains SQL statements to create the table,
populate it, or both. However, mysqldump
can also be used to generate files in CSV, other delimited
text, or XML format.

If you do not name any tables following
db_name or if you use the
--databases or
--all-databases option, entire databases are
dumped.

To get a list of the options your version of
mysqldump supports, execute
mysqldump --help.

Some mysqldump options are shorthand for
groups of other options. --opt and
--compact fall into this category. For
example, use of --opt is the same as
specifying --add-drop-table --add-locks
--create-options --disable-keys --extended-insert
--lock-tables --quick --set-charset. Note that all of
the options that --opt stands for also are on
by default because --opt is on by default.

To reverse the effect of a group option, uses its
--skip-xxx form
(--skip-opt or
--skip-compact). It is also possible to
select only part of the effect of a group option by following
it with options that enable or disable specific features. Here
are some examples:

To select the effect of --opt except for
some features, use the --skip option for
each feature. For example, to disable extended inserts and
memory buffering, use --opt --skip-extended-insert
--skip-quick. (As of MySQL 5.0,
--skip-extended-insert --skip-quick is
sufficient because --opt is on by
default.)

To reverse --opt for all features except
index disabling and table locking, use --skip-opt
--disable-keys --lock-tables.

When you selectively enable or disable the effect of a group
option, order is important because options are processed first
to last. For example, --disable-keys --lock-tables
--skip-opt would not have the intended effect; it is
the same as --skip-opt by itself.

mysqldump can retrieve and dump table
contents row by row, or it can retrieve the entire content
from a table and buffer it in memory before dumping it.
Buffering in memory can be a problem if you are dumping large
tables. To dump tables row by row, use the
--quick option (or --opt,
which enables --quick).
--opt (and hence --quick) is
enabled by default as of MySQL 5.0 to enable
memory buffering, use --skip-quick.

If you are using a recent version of
mysqldump to generate a dump to be reloaded
into a very old MySQL server, you should not use the
--opt or --extended-insert
option. Use --skip-opt instead.

Before MySQL 4.1.2, 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:

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. This problem is fixed as of MySQL
4.1.2; you cannot insert inf in the table,
so this mysqldump behavior is only relevant
when you deal with old servers.

Produce output that is more compatible with other database
systems or with older MySQL servers. The value of
name can be ansi,
mysql323, mysql40,
postgresql, oracle,
mssql, db2,
maxdb,
no_key_options,
no_table_options, or
no_field_options. To use several
values, separate them by commas. These values have the
same meaning as the corresponding options for setting the
server SQL mode. See Section 5.2.6, “SQL Modes”.

This option does not guarantee compatibility with other
servers. It only enables those SQL mode values that are
currently available for making dump output more
compatible. For example,
--compatible=oracle does not map data
types to Oracle types or use Oracle comment syntax.

This option requires a server version of 4.1.0
or higher. With older servers, it does nothing.

--complete-insert, -c

Use complete INSERT statements that
include column names.

--compress, -C

Compress all information sent between the client and the
server if both support compression.

--create-options

Include all MySQL-specific table options in the
CREATE TABLE statements.

--databases, -B

Dump several databases. Normally,
mysqldump treats the first name
argument on the command line as a database name and
following names as table names. With this option, it
treats all name arguments as database names.
CREATE DATABASE and
USE statements are included in the
output before each new database.

--debug[=debug_options],
-#
[debug_options]

Write a debugging log. The
debug_options string is often
'd:t:o,file_name'.
The default value is
'd:t:o,/tmp/mysqldump.trace'.

On a master replication server, delete the binary logs
after performing the dump operation. This option
automatically enables --master-data.

--disable-keys, -K

For each table, surround the INSERT
statements with /*!40000 ALTER TABLE
tbl_name DISABLE KEYS
*/; and /*!40000 ALTER TABLE
tbl_name ENABLE KEYS
*/; statements. This makes loading the dump file
faster because the indexes are created after all rows are
inserted. This option is effective only for non-unique
indexes of MyISAM tables.

--extended-insert, -e

Use multiple-row INSERT syntax that
include several VALUES lists. This
results in a smaller dump file and speeds up inserts when
the file is reloaded.

Flush the MySQL server log files before starting the dump.
This option requires the RELOAD
privilege. Note that if you use this option in combination
with the --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.

--flush-privileges

Emit a FLUSH PRIVILEGES statement after
dumping the mysql database. This option
should be used any time the dump contains the
mysql database and any other database
that depends on the data in the mysql
database for proper restoration. This option was added in
MySQL 5.0.26.

--force, -f

Continue even if an SQL error occurs during a table dump.

One use for this option is to cause
mysqldump to continue executing even
when it encounters a view that has become invalid because
the defintion refers to a table that has been dropped.
Without --force,
mysqldump exits with an error message.
With --force,
mysqldump prints the error message, but
it also writes a SQL comment containing the view
definition to the dump output and continues executing.

--host=host_name,
-h host_name

Dump data from the MySQL server on the given host. The
default host is localhost.

Lock all tables across all databases. This is achieved by
acquiring a global read lock for the duration of the whole
dump. This option automatically turns off
--single-transaction and
--lock-tables.

--lock-tables, -l

Lock all tables before dumping them. The tables are locked
with READ LOCAL to allow concurrent
inserts in the case of MyISAM tables.
For transactional tables such as InnoDB
and BDB,
--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. Therefore, this option does not
guarantee that the tables in the dump file are logically
consistent between databases. Tables in different
databases may be dumped in completely different states.

--master-data[=value]

Write the binary log filename and position to the output.
This option requires the RELOAD
privilege and the binary log must be enabled. If the
option value is equal to 1, the position and filename are
written to the dump output in the form of a
CHANGE MASTER statement. If the dump is
from a master server and you use it to set up a slave
server, the CHANGE MASTER statement
causes the slave to start from the correct position in the
master's binary logs. 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.)

The --master-data option automatically
turns off --lock-tables. It also turns on
--lock-all-tables, unless
--single-transaction also is specified
(in which case, a global read lock is acquired only for a
short time at the beginning of the dump. See also the
description for --single-transaction. In
all cases, any action on logs happens at the exact moment
of the dump.

--no-autocommit

Enclose the INSERT statements for each
dumped table within SET AUTOCOMMIT=0
and COMMIT statements.

--no-create-db, -n

This option suppresses the CREATE
DATABASE statements that are otherwise included
in the output if the --databases or
--all-databases option is given.

--no-create-info, -t

Do not write CREATE TABLE statements
that re-create each dumped table.

--no-data, -d

Do not write any table row information (that is, do not
dump table contents). This is very useful if you want to
dump only the CREATE TABLE statement
for the table.

--opt

This option is shorthand; it is the same as specifying
--add-drop-table --add-locks --create-options
--disable-keys --extended-insert --lock-tables --quick
--set-charset. It should give you a fast dump
operation and produce a dump file that can be reloaded
into a MySQL server quickly.

The --opt option is enabled by
default. Use --skip-opt to disable
it. See the discussion at the beginning of this
section for information about selectively enabling or
disabling certain of the options affected by
--opt.

--order-by-primary

Sorts each table's rows by its primary key, or by its
first unique index, if such an index exists. This is
useful when dumping a MyISAM table to
be loaded into an InnoDB table, but
will make the dump itself take considerably longer.

--password[=password],
-p[password]

The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the
option and the password. If you omit the
password value following the
--password or -p option
on the command line, you are prompted for one.

This option is useful for dumping large tables. It forces
mysqldump to retrieve rows for a table
from the server a row at a time rather than retrieving the
entire row set and buffering it in memory before writing
it out.

--quote-names, -Q

Quote database, table, and column names within
‘`’ characters. If the
ANSI_QUOTES SQL mode is enabled, names
are quoted within ‘"’
characters. This option is enabled by default. It can be
disabled with --skip-quote-names, but
this option should be given after any option such as
--compatible that may enable
--quote-names.

--result-file=file,
-r file

Direct output to a given file. This option should be used
on Windows to prevent newline
‘\n’ characters from being
converted to ‘\r\n’
carriage return/newline sequences. The result file is
created and its contents overwritten, even if an error
occurs while generating the dump. The previous contents
are lost.

--routines, -R

Dump stored routines (functions and procedures) from the
dumped databases. Use of this option requires the
SELECT privilege for the
mysql.proc table. The output generated
by using --routines contains
CREATE PROCEDURE and CREATE
FUNCTION statements to re-create the routines.
However, these statements do not include attributes such
as the routine creation and modification timestamps. This
means that when the routines are reloaded, they will be
created with the timestamps equal to the reload time.

If you require routines to be re-created with their
original timestamp attributes, do not use
--routines. Instead, dump and reload the
contents of the mysql.proc table
directly, using a MySQL account that has appropriate
privileges for the mysql database.

This option was added in MySQL 5.0.13. Before that, stored
routines are not dumped. Routine
DEFINER values are not dumped until
MySQL 5.0.20. This means that before 5.0.20, when routines
are reloaded, they will be created with the definer set to
the reloading user. If you require routines to be
re-created with their original definer, dump and load the
contents of the mysql.proc table
directly as described earlier.

--set-charset

Add SET NAMES
default_character_set
to the output. This option is enabled by default. To
suppress the SET NAMES statement, use
--skip-set-charset.

--single-transaction

This option issues a BEGIN SQL
statement before dumping data from the server. It is
useful only with transactional tables such as
InnoDB and BDB,
because then it dumps the consistent state of the database
at the time when BEGIN was issued
without blocking any applications.

When using this option, you should keep in mind that only
InnoDB tables are dumped in a
consistent state. For example, any
MyISAM or MEMORY
tables dumped while using this option may still change
state.

The --single-transaction option and the
--lock-tables option are mutually
exclusive, because LOCK TABLES causes
any pending transactions to be committed implicitly.

This option is not supported for MySQL Cluster tables; the
results cannot be guaranteed to be consistent due to the
fact that the NDBCluster storage engine
supports only the READ_COMMITTED
transaction isolation level. You should always use
NDB backup and restore instead.

To dump large tables, you should combine this option with
--quick.

--skip-opt

See the description for the --opt option.

--socket=path,
-S path

For connections to localhost, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.

Produce tab-separated data files. For each dumped table,
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-terminated-by options.

Note: This option should
be used only when mysqldump is run on
the same machine as the mysqld server.
You must have the FILE privilege, and
the server must have permission to write files in the
directory that you specify.

--tables

Override the --databases or
-B option. mysqldump
regards all name arguments following the option as table
names.

--triggers

Dump triggers for each dumped table. This option is
enabled by default; disable it with
--skip-triggers. This option was added in
MySQL 5.0.11. Before that, triggers are not dumped.

--tz-utc

Add SET TIME_ZONE='+00:00' to the dump
file so that TIMESTAMP columns can be
dumped and reloaded between servers in different time
zones. Without this option, TIMESTAMP
columns are dumped and reloaded in the time zones local to
the source and destination servers, which can cause the
values to change. --tz-utc also protects
against changes due to daylight saving time.
--tz-utc is enabled by default. To
disable it, use --skip-tz-utc. This
option was added in MySQL 5.0.15.

--user=user_name,
-u user_name

The MySQL username to use when connecting to the server.

--verbose, -v

Verbose mode. Print more information about what the
program does.

--version, -V

Display version information and exit.

--where='where_condition',
-w
'where_condition'

Dump only rows selected by the given
WHERE condition. Quotes around the
condition are mandatory if it contains spaces or other
characters that are special to your command interpreter.

Examples:

--where="user='jimf'"
-w"userid>1"
-w"userid<1"

--xml, -X

Write dump output as well-formed XML.

NULL,
'NULL', and Empty Values:
For some column named
column_name, the
NULL value, an empty string, and the
string value 'NULL' are distinguished
from one another in the output generated by this option as
follows:

Value:

XML Representation:

NULL (unknown value)

<field name="column_name"
xsi:nil="true" />

'' (empty string)

<field
name="column_name"></field>

'NULL' (string value)

<field
name="column_name">NULL</field>

Beginning with MySQL 5.0.26, the output from the
mysql client when run using the
--xml option also follows these rules.
(See Section 8.8.1, “mysql Options”.)

Beginning with MySQL 5.0.40, XML output from
mysqldump includes the XML namespace,
as shown here:

You can also set the following variables by using
--var_name=value
syntax:

max_allowed_packet

The maximum size of the buffer for client/server
communication. The maximum is 1GB.

net_buffer_length

The initial size of the buffer for client/server
communication. When creating multiple-row-insert
statements (as with option
--extended-insert or
--opt), mysqldump
creates rows up to net_buffer_length
length. If you increase this variable, you should also
ensure that the net_buffer_length
variable in the MySQL server is at least this large.

It is also possible to set variables by using
--set-variable=var_name=value
or -O
var_name=value
syntax. This syntax is deprecated.

The most common use of mysqldump is
probably for making a backup of an entire database:

shell> mysqldump db_name > backup-file.sql

You can read the dump file back into the server like this:

shell> mysql db_name < backup-file.sql

Or like this:

shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

mysqldump is also very useful for
populating databases by copying data from one MySQL server to
another:

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 the lock is
released. If and only if one long updating statement is
running when the FLUSH statement is issued,
the MySQL server may get stalled until that long statement
finishes, and then the dump becomes lock-free. If the update
statements that the MySQL server receives are short (in terms
of execution time), the initial lock period should not be
noticeable, even with many updates.

For point-in-time recovery (also known as
“roll-forward,” when you need to restore an old
backup and replay the changes that happened since that
backup), it is often useful to rotate the binary log (see
Section 5.11.3, “The Binary Log”) or at least know the binary log
coordinates to which the dump corresponds:

The --master-data and
--single-transaction options can be used
simultaneously, which provides a convenient way to make an
online backup suitable for point-in-time recovery if tables
are stored using the InnoDB storage engine.

If you encounter problems backing up views, please read the
section that covers restrictions on views which describes a
workaround for backing up views when this fails due to
insufficient privileges. See
Section F.4, “Restrictions on Views”.

8.14. mysqlhotcopy — A 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 make a database backup quickly. It
is 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,
and ARCHIVE tables. It runs on Unix and
NetWare.

shell> mysqlhotcopy db_name [/path/to/new_directory]

shell> mysqlhotcopy db_name_1 ... db_name_n/path/to/new_directory

Back up tables in the given database that match a regular
expression:

shell> mysqlhotcopy db_name./regex/

The regular expression for the table name can be negated by
prefixing it with a tilde
(‘~’):

shell> mysqlhotcopy db_name./~regex/

mysqlhotcopy supports the following
options:

--help, -?

Display a help message and exit.

--addtodest

Do not rename target directory (if it exists); merely add
files to it.

Base directory of the chroot jail in
which mysqld operates. The
path value should match that of
the --chroot option given to
mysqld.

--debug

Enable debug output.

--dryrun, -n

Report actions without performing them.

--flushlog

Flush logs after all tables are locked.

--host=host_name,
-h host_name

The hostname of the local host to use for making a TCP/IP
connection to the local server. By default, the connection
is made to localhost using a Unix
socket file.

--keepold

Do not delete previous (renamed) target when done.

--method=command

The method for copying files (cp or
scp).

--noindices

Do not include full index files in the backup. This makes
the backup smaller and faster. The indexes for reloaded
tables can be reconstructed later with myisamchk
-rq.

--password=password,
-ppassword

The password to use when connecting to the server. Note
that the password value is not optional for this option,
unlike for other MySQL programs. You can use an option
file to avoid giving the password on the command line.

To execute mysqlhotcopy, you must have
access to the files for the tables that you are backing up,
the SELECT privilege for those tables, the
RELOAD privilege (to be able to execute
FLUSH TABLES), and the LOCK
TABLES privilege (to be able to lock the tables).

Use perldoc for additional
mysqlhotcopy documentation, including
information about the structure of the tables needed for the
--checkpoint and
--record_log_pos options:

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.

Ignore errors. For example, if a table for a text file
does not exist, continue processing any remaining files.
Without --force,
mysqlimport exits if a table does not
exist.

--host=host_name,
-h host_name

Import data to the MySQL server on the given host. The
default host is localhost.

--ignore, -i

See the description for the --replace
option.

--ignore-lines=N

Ignore the first N lines of the
data file.

--lines-terminated-by=...

This option has the same meaning as the corresponding
clause for LOAD DATA INFILE. For
example, to import Windows files that have lines
terminated with carriage return/linefeed pairs, use
--lines-terminated-by="\r\n". (You might
have to double the backslashes, depending on the escaping
conventions of your command interpreter.) See
Section 13.2.5, “LOAD DATA INFILE Syntax”.

Lock all tables for writing before
processing any text files. This ensures that all tables
are synchronized on the server.

--low-priority

Use LOW_PRIORITY when loading the
table. This affects only storage engines that use only
table-level locking (MyISAM,
MEMORY, MERGE).

--password[=password],
-p[password]

The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the
option and the password. If you omit the
password value following the
--password or -p option
on the command line, you are prompted for one.

The --replace and
--ignore options control handling of
input rows that duplicate existing rows on unique key
values. If you specify --replace, new
rows replace existing rows that have the same unique key
value. If you specify --ignore, input
rows that duplicate an existing row on a unique key value
are skipped. If you do not specify either option, an error
occurs when a duplicate key value is found, and the rest
of the text file is ignored.

--silent, -s

Silent mode. Produce output only when errors occur.

--socket=path,
-S path

For connections to localhost, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.

8.16. mysqlmanagerc — Internal Test-Suite Program

This program was used internally for test purposes. As of
MySQL 5.0, it is no longer used.

8.17. mysqlmanager-pwgen — Internal Test-Suite Program

This program was used internally for test purposes. As of
MySQL 5.0, it is no longer used.

8.18. mysqlshow — Display Database, Table, and Column Information

The mysqlshow client can be used to quickly
see which databases exist, their tables, or a table's columns
or indexes.

mysqlshow provides a command-line interface
to several SQL SHOW statements. See
Section 13.5.4, “SHOW Syntax”. The same information can be obtained
by using those statements directly. For example, you can issue
them from the mysql client program.

Invoke mysqlshow like this:

shell> mysqlshow [options] [db_name [tbl_name [col_name]]]

If no database is given, a list of database names is
shown.

If no table is given, all matching tables in the database
are shown.

If no column is given, all matching columns and column
types in the table are shown.

The output displays only the names of those databases, tables,
or columns for which you have some privileges.

If the last argument contains shell or SQL wildcard characters
(‘*’,
‘?’,
‘%’, or
‘_’), only those names that are
matched by the wildcard are shown. If a database name contains
any underscores, those should be escaped with a backslash
(some Unix shells require two) to get a list of the proper
tables or columns. ‘*’ and
‘?’ characters are converted
into SQL ‘%’ and
‘_’ wildcard characters. This
might cause some confusion when you try to display the columns
for a table with a ‘_’ in the
name, because in this case, mysqlshow shows
you only the table names that match the pattern. This is
easily fixed by adding an extra
‘%’ last on the command line as
a separate argument.

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.

Verbose mode. Print more information about what the
program does. This option can be used multiple times to
increase the amount of information.

--version, -V

Display version information and exit.

8.19. mysql_convert_table_format — Convert Tables to Use a Given Storage
Engine

mysql_convert_table_format converts the
tables in a database to use a particular storage engine
(MyISAM by default).
mysql_convert_table_format is written in
Perl and requires that the DBI and
DBD::mysql Perl modules be installed (see
Section 2.4.20, “Perl Installation Notes”).

Invoke mysql_convert_table_format like
this:

shell> mysql_convert_table_format [options]db_name

The db_name argument indicates the
database containing the tables to be converted.

mysql_convert_table_format understands the
options described in the following list.

--help

Display a help message and exit.

--force

Continue even if errors occur.

--host=host_name

Connect to the MySQL server on the given host.

--password=password

The password to use when connecting to the server. Note
that the password value is not optional for this option,
unlike for other MySQL programs. You can use an option
file to avoid giving the password on the command line.

8.20. mysql_explain_log — Use EXPLAIN on Statements in Query Log

mysql_explain_log reads its standard input
for query log contents. It uses EXPLAIN to
analyze SELECT statements found in the
input. UPDATE statements are rewritten to
SELECT statements and also analyzed with
EXPLAIN.
mysql_explain_log then displays a summary
of its results.

The results may assist you in determining which queries result
in table scans and where it would be beneficial to add indexes
to your tables.

Invoke mysql_explain_log like this, where
log_file contains all or part of a
MySQL query log:

8.22. mysql_fix_extensions — Make Table Filename Extensions Lowercase

mysql_fix_extensions converts the
extensions for MyISAM (or
ISAM) table files to lowercase. It looks
for files with an extension that that matches any lettercase
variant of .frm,
.myd, .myi,
.isd, and .ism and
renames them to have extensionsn of .frm,
.MYD, .MYI,
.ISD, and .ISM,
respectively. This can be useful after transferring the files
from a system with case-insensitive filenames (such as
Windows) to a system with case-sensitive filenames.

Invoke mysql_fix_extensions like this,
where data_dir is the pathname to
the MySQL data directory.

mysql_setpermission is a Perl script that
was originally written and contributed by Luuk de Boer. It
interactively sets permissions in the MySQL grant tables.
mysql_setpermission is written in Perl and
requires that the DBI and
DBD::mysql Perl modules be installed (see
Section 2.4.20, “Perl Installation Notes”).

Invoke mysql_setpermission like this:

shell> mysql_setpermission [options]

options should be either
--help to display the help message, or
options that indicate how to connect to the MySQL server. The
account used when you connect determines which permissions you
have when attempting to modify existing permissions in the
grant tables.

mysql_setpermissions also reads options
from the [client] and
[perl] groups in the
.my.cnf file in your home directory, if
the file exists.

mysql_setpermission understands the
following options:

--help

Display a help message and exit.

--host=host_name

Connect to the MySQL server on the given host.

--password=password

The password to use when connecting to the server. Note
that the password value is not optional for this option,
unlike for other MySQL programs. You can use an option
file to avoid giving the password on the command line.

The db_name argument indicates
which database mysql_tableinfo should use
as the location for the metadata tables. The database will be
created if it does not exist. The tables will be named
db, tbl (or
tbl_status), col, and
idx.

If the db_like or
tbl_like arguments are given, they
are used as patterns and metadata is generated only for
databases or tables that match the patterns. These arguments
default to % if not given.

Each of the commands stores information into tables in the
info database. The first stores information
for all databases and tables. The second stores information
for all tables in the world database. The
third stores information for tables in the
mydb database that have names matching the
pattern tmp%.

mysql_tableinfo supports the following
options:

--help

Display a help message and exit.

--clear

Before populating each metadata table, drop it if it
exists.

--clear-only

Similar to --clear, but exits after
dropping the metadata tables to be populated.

--col

Generate column metadata into the col
table.

--host=host_name,
-h host_name

Connect to the MySQL server on the given host.

--idx

Generate index metadata into the idx
table.

--password=password,
-ppassword

The password to use when connecting to the server. Note
that the password value is not optional for this option,
unlike for other MySQL programs. You can use an option
file to avoid giving the password on the command line.

Verbose mode. Display a warning if signal 0 could not be
used and signal 1 is used instead.

--version, -V

Display version information and exit.

8.26. mysql_zap — Kill Processes That Match a Pattern

mysql_zap kills processes that match a
pattern. It uses the ps command and Unix
signals, so it runs on Unix and Unix-like systems.

Invoke mysql_zap like this:

shell> mysql_zap [-signal] [-?Ift] pattern

A process matches if its output line from the
ps command contains the pattern. By
default, mysql_zap asks for confirmation
for each process. Respond y to kill the
process, or q to exit
mysql_zap. For any other response,
mysql_zap does not attempt to kill the
process.

If the -signal
option is given, it specifies the name or number of the signal
to send to each process. Otherwise,
mysql_zap tries first with
TERM (signal 15) and then with
KILL (signal 9).

mysql_zap understands the following
additional options:

--help, -?,
-I

Display a help message and exit.

-f

Force mode. mysql_zap attempts to kill
each process without confirmation.

-t

Test mode. Display information about each process but do
not kill it.

8.27. perror — Explain Error Codes

For 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 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.

from represents a string to look
for and to represents its
replacement. There can be one or more pairs of strings.

Use the -- option to indicate where the
string-replacement list ends and the filenames begin. In this
case, 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. replace prints a
message indicating which of the input files it actually
modifies.

If the -- option is not given,
replace reads the standard input and writes
to the standard output.

replace 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:

The symbols file should include the output from the
nm --numeric-sort mysqld command. The
numeric dump file should contain a numeric stack track from
mysqld. If no numeric dump file is named on
the command line, the stack trace is read from the standard
input.

resolve_stack_dump understands the options
described in the following list.

At Alden Hosting we eat and breathe Java! We are the industry leader in providing
affordable, quality and efficient Java web hosting in the shared hosting marketplace.
All our sites run on our Java hosing platform configured for
optimum performance using Java 1.6, Tomcat 6, MySQL 5, Apache 2.2 and web
application frameworks such as Struts, Hibernate, Cocoon, Ant, etc.

We offer only one type of Java hosting - Private Tomcat. Hosting accounts on the Private
Tomcat environment get their very own Tomcat server. You can start and re-start
your entire Tomcat server yourself.