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:
A client that performs administrative operations, such as creating or
dropping databases, reloading the grant tables, flushing tables to
disk, and reopening log files.
mysqladmin can also be
used to retrieve version, process, and status information from the
server. See
Section 8.8, “mysqladmin
— Client for Administering a MySQL Server”.
MySQL AB also provides a number of GUI tools for administering and
otherwise working with MySQL servers. For basic information about these,
see
Chapter 4, Using MySQL Programs.
Each MySQL program takes many different options. Most programs provide a
--help option that you can use to get a full description of the
program's different options. For example, try
mysql --help.
MySQL client programs that communicate with the server using the MySQL
client/server library use the following environment variables:
MYSQL_UNIX_PORT
The default Unix socket file; used
for connections to
localhost
MYSQL_TCP_PORT
The default port number; used for
TCP/IP connections
MYSQL_PWD
The default password
MYSQL_DEBUG
Debug trace options when debugging
TMPDIR
The directory where temporary tables and
files are created
You can override the default option values or values specified in
environment variables for all standard programs by specifying options in
an option file or on the command line. See
Section 4.3, “Specifying Program Options”.
8.2. 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 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:
CREATE TABLE mytexttable
(
id INT NOT NULL,
txt TEXT NOT NULL,
PRIMARY KEY (id),
FULLTEXT (txt)
);
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:
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). A related utility,
isamchk, works with
ISAM
tables (tables that have .ISD and
.ISM files for storing data and
indexes).
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 does not
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:
shell> myisamchk /path/to/datadir/*/*.MYI
The recommended way to quickly check all
MyISAM and ISAM
tables is:
These commands assume that you have more than 64MB free. For more
information about memory allocation with
myisamchk, see
Section 8.3.5, “myisamchk
Memory Usage”.
You must ensure that no other program is using the tables while you
are running myisamchk.
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.
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. The
easiest way to avoid this problem is to use
CHECK TABLE instead of
myisamchk to check tables.
8.3.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. Note that 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. It is available as of MySQL 4.0.0.
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.2, “Server System Variables”, and
Section 7.4.7, “MyISAM Index Statistics
Collection”.
stats_method was added in MySQL
4.1.15/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:
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:
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.3.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 does not 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.
8.3.3. myisamchk
Repair Options
myisamchk supports the
following options for table repair operations:
--backup, -B
Make a backup of the .MYD file as
file_name-time.BAK
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 totally 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. For
isamchk, the option
value indicates that only the first
val of the table indexes should be updated. In
either case, 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 or (isamchk
-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. This option was added in MySQL
4.1.1.
--parallel-recover,
-p
Uses the same technique as -r and
-n, but creates all the keys in
parallel, using different threads. This option was added in
MySQL 4.0.2. 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 are not unique (which is an extremely unlikely error with
ISAM/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 cannot be recovered by
--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 using --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 4.1.1.
--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 4.1.11.
--sort-recover,
-n
Force myisamchk to
use sorting to resolve the keys even if the temporary files
should be very big.
--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. Starting from MySQL 4.1,
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 should be colon (‘:’)
on Unix and semicolon (‘;’) on
Windows, NetWare, and OS/2.
--unpack, -u
Unpack a table that was packed with
myisampack.
8.3.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.
--block-search=offset,
-b offset
Find the record that a block at the given offset belongs to.
--description, -d
Print some descriptive information about the table.
--set-auto-increment[=value],
-A[value]
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.3.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 must
be available on the same filesystem as the original data file,
as 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 must be available on
the same filesystem as the original data 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:
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.4. myisamlog — Display
MyISAM Log File Contents
myisamlog processes the
contents of a
MyISAM log file.
isamlog
is similar, but is used with ISAM log
files.
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 and
isamlog
understand 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.
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 does 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).
--packlength=len,
-p len
Specify the row length storage size, in bytes. The value should be
1, 2, or 3. myisampack
stores all rows with length pointers of 1, 2, or 3 bytes. In
most normal cases, myisampack
can determine the correct length value before it begins packing
the file, but it may notice during the packing process that it
could have used a shorter length. In this case,
myisampack prints a
note that you could use a shorter row length the next time you
pack the same file.
--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:
shell> ls -l station.*
-rw-rw-r-- 1 monty my 994128 Apr 17 19:00 station.MYD
-rw-rw-r-- 1 monty my 53248 Apr 17 19:00 station.MYI
-rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm
shell> myisamchk -dvv station
MyISAM file: station
Isam-version: 2
Creation time: 1996-03-13 10:08:58
Recover time: 1997-02-02 3:06:43
Data records: 1192 Deleted blocks: 0
Datafile parts: 1192 Deleted data: 0
Datafile pointer (bytes): 2 Keyfile pointer (bytes): 2
Max datafile length: 54657023 Max keyfile length: 33554431
Recordlength: 834
Record format: Fixed length
table description:
Key Start Len Index Type Root Blocksize Rec/key
1 2 4 unique unsigned long 1024 1024 1
2 32 30 multip. text 10240 1024 1
Field Start Length Type
1 1 1
2 2 4
3 6 4
4 10 1
5 11 20
6 31 1
7 32 30
8 62 35
9 97 35
10 132 35
11 167 4
12 171 16
13 187 35
14 222 4
15 226 16
16 242 20
17 262 20
18 282 20
19 302 30
20 332 4
21 336 4
22 340 1
23 341 8
24 349 8
25 357 8
26 365 2
27 367 2
28 369 4
29 373 4
30 377 1
31 378 2
32 380 8
33 388 4
34 392 4
35 396 4
36 400 4
37 404 1
38 405 4
39 409 4
40 413 4
41 417 4
42 421 4
43 425 4
44 429 20
45 449 30
46 479 1
47 480 1
48 481 79
49 560 79
50 639 79
51 718 79
52 797 8
53 805 1
54 806 1
55 807 20
56 827 4
57 831 4
shell> myisampack station.MYI
Compressing station.MYI: (1192 records)
- Calculating statistics
normal: 20 empty-space: 16 empty-zero: 12 empty-fill: 11
pre-space: 0 end-space: 12 table-lookups: 5 zero: 7
Original trees: 57 After join: 17
- Compressing file
87.14%
Remember to run myisamchk -rq on compressed tables
shell> ls -l station.*
-rw-rw-r-- 1 monty my 127874 Apr 17 19:00 station.MYD
-rw-rw-r-- 1 monty my 55296 Apr 17 19:04 station.MYI
-rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm
shell> myisamchk -dvv station
MyISAM file: station
Isam-version: 2
Creation time: 1996-03-13 10:08:58
Recover time: 1997-04-17 19:04:26
Data records: 1192 Deleted blocks: 0
Datafile parts: 1192 Deleted data: 0
Datafile pointer (bytes): 3 Keyfile pointer (bytes): 1
Max datafile length: 16777215 Max keyfile length: 131071
Recordlength: 834
Record format: Compressed
table description:
Key Start Len Index Type Root Blocksize Rec/key
1 2 4 unique unsigned long 10240 1024 1
2 32 30 multip. text 54272 1024 1
Field Start Length Type Huff tree Bits
1 1 1 constant 1 0
2 2 4 zerofill(1) 2 9
3 6 4 no zeros, zerofill(1) 2 9
4 10 1 3 9
5 11 20 table-lookup 4 0
6 31 1 3 9
7 32 30 no endspace, not_always 5 9
8 62 35 no endspace, not_always, no empty 6 9
9 97 35 no empty 7 9
10 132 35 no endspace, not_always, no empty 6 9
11 167 4 zerofill(1) 2 9
12 171 16 no endspace, not_always, no empty 5 9
13 187 35 no endspace, not_always, no empty 6 9
14 222 4 zerofill(1) 2 9
15 226 16 no endspace, not_always, no empty 5 9
16 242 20 no endspace, not_always 8 9
17 262 20 no endspace, no empty 8 9
18 282 20 no endspace, no empty 5 9
19 302 30 no endspace, no empty 6 9
20 332 4 always zero 2 9
21 336 4 always zero 2 9
22 340 1 3 9
23 341 8 table-lookup 9 0
24 349 8 table-lookup 10 0
25 357 8 always zero 2 9
26 365 2 2 9
27 367 2 no zeros, zerofill(1) 2 9
28 369 4 no zeros, zerofill(1) 2 9
29 373 4 table-lookup 11 0
30 377 1 3 9
31 378 2 no zeros, zerofill(1) 2 9
32 380 8 no zeros 2 9
33 388 4 always zero 2 9
34 392 4 table-lookup 12 0
35 396 4 no zeros, zerofill(1) 13 9
36 400 4 no zeros, zerofill(1) 2 9
37 404 1 2 9
38 405 4 no zeros 2 9
39 409 4 always zero 2 9
40 413 4 no zeros 2 9
41 417 4 always zero 2 9
42 421 4 no zeros 2 9
43 425 4 always zero 2 9
44 429 20 no empty 3 9
45 449 30 no empty 3 9
46 479 1 14 4
47 480 1 14 4
48 481 79 no endspace, no empty 15 9
49 560 79 no empty 2 9
50 639 79 no empty 2 9
51 718 79 no endspace 16 9
52 797 8 no empty 2 9
53 805 1 17 1
54 806 1 3 9
55 807 20 no empty 3 9
56 827 4 no zeros, zerofill(2) 2 9
57 831 4 no zeros, zerofill(1) 2 9
myisampack displays the
following kinds of information:
normal
The number of columns for which no extra packing is used.
empty-space
The number of columns containing values that are only spaces.
These occupy one bit.
empty-zero
The number of columns containing values that are only binary
zeros. These occupy one bit.
empty-fill
The number of integer columns that do not occupy the full byte
range of their type. These are changed to a smaller type. For
example, a BIGINT column (eight
bytes) can be stored as a
TINYINT column (one byte) if all its
values are in the range from -128
to
127.
pre-space
The number of decimal columns that are stored with leading spaces.
In this case, each value contains a count for the number of
leading spaces.
end-space
The number of columns that have a lot of trailing spaces. In this
case, each value contains a count for the number of trailing
spaces.
table-lookup
The column had only a small number of different values, which were
converted to an ENUM before Huffman
compression.
zero
The number of columns for which all values are zero.
Original trees
The initial number of Huffman trees.
After join
The number of distinct Huffman trees left after joining trees to
save some header space.
After a table has been compressed,
myisamchk -dvv prints additional information about
each column:
Type
The 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:
After you have installed the packed table into the MySQL database
directory, you should execute
mysqladmin flush-tables to force
mysqld to start using the new table.
To unpack a packed table, use the --unpack
option to myisamchk or
isamchk.
mysql is a simple SQL
shell (with GNU
readline capabilities). It supports
interactive and non-interactive use. When used interactively, query
results are presented in an ASCII-table format. When used
non-interactively (for example, as a filter), the result is
presented in tab-separated format. The output format can be changed
using command 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.
You can execute SQL statements in a script file (batch file) like
this:
shell> mysql db_name < script.sql > output.tab
8.6.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.5, “The Server SQL Mode”).
--line-numbers
Write line numbers for errors. Disable this with
--skip-line-numbers.
--local-infile[={0|1}]
Enable or disable LOCAL capability
for
LOAD DATA INFILE. With no value, the
option enables LOCAL. The option
may be given as --local-infile=0 or
--local-infile=1 to explicitly disable
or enable LOCAL. Enabling
LOCAL has no effect if the server
does not also support it.
--named-commands,
-G
Enable named mysql
commands. Long-format commands are 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.6.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 (‘;’). As
of MySQL 3.23.22,
mysql starts with
this option
enabled by default.
However, even with this option, long-format commands still work
from the first line. See
Section 8.6.2, “mysql
Commands”.
--no-pager
Deprecated form of --skip-pager. See
the
--pager option.
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.6.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.6.2, “mysql
Commands”.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use. Added in MySQL 4.1.
--quick, -q
Do not cache each query result, print each row as it is received.
This may slow down the server if the output is suspended. With
this option, mysql
does not use the history file.
--raw, -r
Write column values without escape conversion. Often used with the
--batch option.
--reconnect
If the connection to the server is lost, automatically try to
reconnect. A single reconnect attempt is made each time the
connection is lost. To suppress reconnection behavior, use
--skip-reconnect. Added in MySQL 4.1.0.
--safe-updates,
--i-am-a-dummy,
-U
Allow only those UPDATE and
DELETE statements that specify 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.6.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. This option was added in MySQL 4.1.1.
--sigint-ignore
Ignore SIGINT signals (typically the
result of typing Control-C). This option was added in MySQL
4.1.6.
--silent, -s
Silent mode. Produce less output. This option can be given
multiple times to produce less and less output.
--skip-column-names,
-N
Do not write column names in results.
--skip-line-numbers,
-L
Do not write line numbers for errors. Useful when you want to
compare result files that include error messages.
--socket=path,
-S path
For connections to localhost, the
Unix socket file to use, or, on Windows, the name of the named
pipe to use.
--ssl*
Options that begin with --ssl specify
whether to connect to the server via SSL and indicate where to
find SSL keys and certificates. See
Section 5.8.7.3, “SSL Command Options”.
--table, -t
Display output in table format. This is the default for
interactive use, but can be used to produce table output in
batch mode.
--tee=file_name
Append a copy of output to the given file. This option does not
work in batch mode. in
Section 8.6.2, “mysql
Commands”, discusses tee files further.
--unbuffered, -n
Flush the buffer after each query.
--user=user_name,
-u user_name
The MySQL username to use when connecting to the server.
--verbose, -v
Verbose mode. Produce more output 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.