Go to the first, previous, next, last section, table of contents.
All MySQL clients that communicate with the server using the
mysqlclient library use the following environment variables:
| Name | Description
|
MYSQL_UNIX_PORT | The default socket; used for connections to localhost
|
MYSQL_TCP_PORT | The default TCP/IP port
|
MYSQL_PWD | The default password
|
MYSQL_DEBUG | Debug-trace options when debugging
|
TMPDIR | The directory where temporary tables/files are created
|
Use of MYSQL_PWD is insecure.
See section 6.5 Connecting to the MySQL server.
The `mysql' client uses the file named in the MYSQL_HISTFILE
environment variable to save the command line history. The default value for
the history file is `$HOME/.mysql_history', where $HOME is the
value of the HOME environment variable.
All MySQL programs take many different options. However, every
MySQL program provides a --help option that you can use
to get a full description of the program's different options. For example, try
mysql --help.
You can override default options for all standard client programs with an
option file. section 4.15.4 Option files.
The list below briefly describes the MySQL programs:
myisamchk
-
Utility to describe, check, optimize and repair MySQL tables.
Because
myisamchk has many functions, it is described in its own
chapter. See section 13 Maintaining a MySQL installation.
make_binary_release
-
Makes a binary release of a compiled MySQL. This could be sent
by FTP to `/pub/mysql/Incoming' on
ftp.tcx.se for the
convenience of other MySQL users.
msql2mysql
-
A shell script that converts
mSQL programs to MySQL. It doesn't
handle all cases, but it gives a good start when converting.
mysql
-
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 (e.g., as a filter), the result is presented in
tab-separated format. (The output format can be changed using command-line
options.) You can run scripts simply like this:
shell> mysql database < script.sql > output.tab
If you have problems due to insufficient memory in the client, use the
--quick option! This forces mysql to use
mysql_use_result() rather than mysql_store_result() to retrieve
the result set.
mysqlaccess
-
A script that checks the access privileges for a host, user and database
combination.
mysqladmin
-
Utility for performing 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 12.2 Administering a MySQL server.
mysqlbug
-
The MySQL bug report script. This script should always be used when
filing a bug report to the MySQL list.
mysqld
-
The SQL daemon. This should always be running.
mysqldump
-
Dumps a MySQL database into a file as SQL statements or
as tab-separated text files. Enhanced freeware originally by Igor Romanenko.
See section 12.3 Dumping the structure and data from MySQL databases and tables.
mysqlimport
-
Imports text files into their respective tables using
LOAD DATA
INFILE. See section 12.4 Importing data from text files.
mysqlshow
-
Displays information about databases, tables, columns and indexes.
mysql_install_db
-
Creates the MySQL grant tables with default privileges. This is
usually executed only once, when first installing MySQL
on a system.
replace
-
A utility program that is used by
msql2mysql, but that has more
general applicability as well. replace changes strings in place in
files or on the standard input. Uses a finite state machine to match longer
strings first. Can be used to swap strings. For example, this command
swaps a and b in the given files:
shell> replace a b b a -- file1 file2 ...
safe_mysqld
-
A script that starts the
mysqld daemon with some safety features, such
as restarting the server when an error occurs and logging runtime information
to a log file.
Utility for performing administrative operations. The syntax is:
shell> mysqladmin [OPTIONS] command [command-option] command ...
You can get a list of the options your version of mysqladmin supports
by executing mysqladmin --help.
The current mysqladmin supports the following commands:
| create databasename | Create a new database.
|
| drop databasename | Delete a database and all its tables.
|
| extended-status | Gives an extended status message from the server.
|
| flush-hosts | Flush all cached hosts.
|
| flush-logs | Flush all logs.
|
| flush-tables | Flush all tables.
|
| flush-privileges | Reload grant tables (same as reload)
|
| kill id,id,... | Kill mysql threads.
|
| password | new-password Change old password to new-password
|
| ping | Check if mysqld is alive
|
| processlist | Show list of active threads in server
|
| reload | Reload grant tables
|
| refresh | Flush all tables and close and open logfiles
|
| shutdown | Take server down
|
| status | Gives a short status message from the server
|
| variables | Prints variables available
|
| version | Get version info from server
|
All commands can be shortened to their unique prefix. For example:
shell> mysqladmin proc stat
+----+-------+-----------+----+-------------+------+-------+------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+----+-------------+------+-------+------+
| 6 | monty | localhost | | Processlist | 0 | | |
+----+-------+-----------+----+-------------+------+-------+------+
Uptime: 10077 Threads: 1 Questions: 9 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 2 Memory in use: 1092K Max memory used: 1116K
The mysqladmin status command result has the following columns:
| Uptime | Number of seconds the MySQL server have been up
|
| Threads | Number of active threads (clients)
|
| Questions | Number of questions from clients since mysqld was started
|
| Slow queries | Queries that has taken more than long_query_time seconds
|
| Opens | How many tables mysqld has opened.
|
| Flush tables | Number of flush ..., refresh and reload commands.
|
| Open tables | Number of tables that are open now
|
| Memory in use | Memory allocated directly by the mysqld code (only available when MySQL is compiled with --with-debug)
|
| Max memory used | Maximum memory allocated directly by the mysqld code (only available when MySQL is compiled with --with-debug)
|
Utility to dump a database or a collection of database for backup or
for transferring the data to another SQL server. The dump will contain SQL
statements to create the table and/or populate the table.
shell> mysqldump [OPTIONS] database [tables]
If you don't give any tables, the whole database will be dumped.
You can get a list of the options your version of mysqldump supports
by executing mysqldump --help.
Note that if you run mysqldump without --quick or
--opt, mysqldump will load the whole result set into
memory before dumping the result. This will probably be a problem if
you are dumping a big database.
mysqldump supports the following options:
--add-locks
-
Add
LOCK TABLES before and UNLOCK TABLE after each table dump.
(To get faster inserts into MySQL).
--add-drop-table
-
Add a
drop table before each create statement.
--allow-keywords
-
Allow creation of column names that are keywords. This works by
prefixing each column name with the table name.
-c, --complete-insert
-
Use complete insert statements (with column names).
-C, --compress
-
Compress all information between the client and the server if both support
compression.
--delayed
-
Insert rows with the
INSERT DELAYED command.
-e, --extended-insert
-
Use the new multiline
INSERT syntax. (Gives more compact and
faster inserts statements)
-#, --debug[=option_string]
-
Trace usage of the program (for debugging).
--help
-
Display a help message and exit.
--fields-terminated-by=...
-
--fields-enclosed-by=...
-
--fields-optionally-enclosed-by=...
-
--fields-escaped-by=...
-
--fields-terminated-by=...
-
These options are used with the
-T option and have the same
meaning as the corresponding clauses for LOAD DATA INFILE.
See section 7.16 LOAD DATA INFILE syntax.
-F, --flush-logs
-
Flush logs file in the MySQL server before starting the dump.
-f, --force,
-
Continue even if we get an SQL error during a table dump.
-h, --host=..
-
Dump data from the MySQL server on the named host. The default host
is
localhost.
-l, --lock-tables.
-
Lock all tables for starting the dump.
-t, --no-create-info
-
Don't write table creation info (The
CREATE TABLE statment)
-d, --no-data
-
Don't write any row information for the table. This is very useful if you
just want to get a dump of the structure for a table!
--opt
-
Same as
--quick --add-drop-table --add-locks --extended-insert
--lock-tables. Should give you the fastest possible dump for reading
into a MySQL server.
-pyour_pass, --password[=your_pass]
-
The password to use when connecting to the server. If you specify
no `=your_pass' part,
mysqldump solicits the password from the terminal.
-P port_num, --port=port_num
-
The TCP/IP port number to use for connecting to a host. (This is used for
connections to hosts other than
localhost, for which Unix sockets are
used.)
-q, --quick
-
Don't buffer query, dump directly to stdout; Uses
mysql_use_result()
to do this.
-S /path/to/socket, --socket=/path/to/socket
-
The socket file to use when connecting to
localhost (which is the
default host).
-T, --tab=path-to-some-directory
-
Creates a
table_name.sql file, that conntains the SQL CREATE commands,
and a table_name.txt file, that contains the data, for each give table.
NOTE: This only works if mysqldump is run on the same
machine as the mysqld daemon. The format of the .txt file
is made according to the --fields-xxx and --lines--xxx options.
-u user_name, --user=user_name
-
The MySQL user name to use when connecting to the server. The
default value is your Unix login name.
-O var=option, --set-variable var=option
-
Set the value of a variable. The possible variables are listed below.
-v, --verbose
-
Verbose mode. Print out more information what the program does.
-V, --version
-
Print version information and exit.
-w, --where='where-condition'
-
Dump only selected records; Note that QUOTES are mandatory!
"--where=user='jimf'" "-wuserid>1" "-wuserid<1"
The most normal use of mysqldump is probably for making a backup of
whole database:
mysqldump --opt database > backup-file.sql
But it's also very useful to populate another MySQL server with
information from a database:
mysqldump --opt database | mysql --host=remote-host -C database
mysqlimport provides a command line interface to the LOAD DATA
INFILE SQL statement. Most options to mysqlimport correspond
directly to the same options to LOAD DATA INFILE.
See section 7.16 LOAD DATA INFILE syntax.
mysqlimport is invoked like this:
shell> mysqlimport [options] database textfile1 [textfile2....]
For each text file named on the command line,
mysqlimport strips any extension from the filename and uses the result
to determine which table to import the file's contents into. For example,
files named `patient.txt', `patient.text' and `patient' would
all be imported into a table named patient.
mysqlimport supports the following options:
-C, --compress
-
Compress all information between the client and the server if both support
compression.
-#, --debug[=option_string]
-
Trace usage of the program (for debugging).
-d, --delete
-
Empty the table before importing the text file.
--fields-terminated-by=...
-
--fields-enclosed-by=...
-
--fields-optionally-enclosed-by=...
-
--fields-escaped-by=...
-
--fields-terminated-by=...
-
These options have the same meaning as the corresponding clauses for
LOAD DATA INFILE. See section 7.16 LOAD DATA INFILE syntax.
-f, --force
-
Ignore errors. For example, if a table for a text file doesn't exist,
continue processing any remaining files. Without
--force,
mysqlimport exits if a table doesn't exist.
--help
-
Display a help message and exit.
-h host_name, --host=host_name
-
Import data to the MySQL server on the named host. The default host
is
localhost.
-i, --ignore
-
See the description for the
--replace option.
-l, --lock-tables
-
Lock ALL tables for writing before processing any text files. This
ensures that all tables are synchronized on the server.
-L, --local
-
Read input files from the client. By default, text files are assumed to be on
the server if you connect to
localhost (which is the default host).
-pyour_pass, --password[=your_pass]
-
The password to use when connecting to the server. If you specify
no `=your_pass' part,
mysqlimport solicits the password from the terminal.
-P port_num, --port=port_num
-
The TCP/IP port number to use for connecting to a host. (This is used for
connections to hosts other than
localhost, for which Unix sockets are
used.)
-r, --replace
-
The
--replace and --ignore options control handling of input
records that duplicate existing records on unique key values. If you specify
--replace, new rows replace existing rows that have the same unique key
value. If you specify --ignore, input rows that duplicate an existing
row on a unique key value are skipped. If you don't specify either option, an
error occurs when a duplicate key value is found, and the rest of the text
file is ignored.
-s, --silent
-
Silent mode. Write output only when errors occur.
-S /path/to/socket, --socket=/path/to/socket
-
The socket file to use when connecting to
localhost (which is the
default host).
-u user_name, --user=user_name
-
The MySQL user name to use when connecting to the server. The
default value is your Unix login name.
-v, --verbose
-
Verbose mode. Print out more information what the program does.
-V, --version
-
Print version information and exit.
Here follows a sample run of using mysqlimport:
$ mysql --version
mysql Ver 9.33 Distrib 3.22.25, for pc-linux-gnu (i686)
$ uname -a
Linux xxx.com 2.2.5-15 #1 Mon Apr 19 22:21:09 EDT 1999 i586 unknown
$ mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
$ ed
a
100 Max Sydow
101 Count Dracula
.
w imptest.txt
32
q
$ od -c imptest.txt
0000000 1 0 0 t M a x S y d o w n 1 0
0000020 1 t C o u n t D r a c u l a n
0000040
$ mysqlimport --local test imptest.txt
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
$ mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id | n |
+------+---------------+
| 100 | Max Sydow |
| 101 | Count Dracula |
+------+---------------+
myisampack is used to compress MyISAM tables and pack_isam
is used to compress ISAM tables. Since ISAM tables are deprecated we
will only discuss myisampack here.
myisampack are an extra utility that you get when you order more
than 10 licenses or extended support. Since these are distributed only
in binary form, they are available only on some platforms.
In the following we only talk about myisampack, but everything
holds also for pack_isam.
myisampack works by compressing each column in the table separately.
The information needed to decompress columns is read into memory when the
table is opened. This results in much better performance when accessing
individual records, since you only have to uncompress exactly one record, not
a much larger disk block like when using Stacker on MS-DOS.
Usually, myisampack packs the data file 40%-70%.
MySQL uses memory mapping (mmap()) on compressed tables and
falls back to normal read/write file usage if mmap() doesn't work.
There are currently two limitations with myisampack:
-
After packing, the table is read only.
-
myisampack can also pack BLOB or TEXT columns. The
older pack_isam could not do this.
Fixing these limitations is on our TODO list but with low priority.
myisampack is invoked like this:
shell> myisampack [options] filename ...
Each filename should be the name of an index (`.MYI') file. If you
are not in the database directory, you should specify the pathname to the
file. It is permissible to omit the `.MYI' extension.
myisampack supports the following options:
-b, --backup
-
Make a backup of the table as
tbl_name.OLD.
-#, --debug=debug_options
-
Output debug log. The
debug_options string often is
'd:t:o,filename'.
-f, --force
-
Force packing of the table even if it becomes bigger or if the temporary file
exists. (
myisampack creates a temporary file named `tbl_name.TMD'
while it compresses the table. If you kill myisampack, the `.TMD'
file may not be deleted. Normally, myisampack exits with an error if
it finds that `tbl_name.TMD' exists. With --force,
myisampack packs the table anyway.
-?, --help
-
Display a help message and exit.
-j big_tbl_name, --join=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 be identical (same column names and types, same indexes, etc.)
-p #, --packlength=#
-
Specify the record length storage size, in bytes. The value should be 1, 2
or 3. (
myisampack stores all rows with length pointers of 1, 2 or 3
bytes. In most normal cases, myisampack can determine the right length
value before it begins packing the file, but it may notice during the packing
process that it could have used a shorter length. In this case,
myisampack will print a note that the next time you pack the same file,
you could use a shorter record length.)
-s, --silent
-
Silent mode. Write output only when errors occur.
-t, --test
-
Don't pack table, only test packing it.
-T dir_name, --tmp_dir=dir_name
-
Use the named directory as the location in which to write the temporary table.
-v, --verbose
-
Verbose mode. Write info about progress and packing result.
-V, --version
-
Display version information and exit.
-w, --wait
-
Wait and retry if table is in use. If the
mysqld server was
invoked with the --skip-locking option, it is not a good idea to
invoke myisampack if the table might be updated during the
packing process.
The sequence of commands shown below 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%
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
Datafilepointer (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
The information printed by myisampack is described below:
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 will occupy 1 bit.
empty-zero
-
The number of columns containing
values that are only binary 0's; these will occupy 1 bit.
empty-fill
-
The number of integer columns that don't occupy the full byte range of their
type; these are changed to a smaller type (for example, an
INTEGER
column may be changed to MEDIUMINT).
pre-space
-
The number of decimal columns that are stored with leading space. In this
case, each value will contain a count for the number of leading spaces.
end-space
-
The number of columns that have a lot of trailing space. In this case, each
value will contain a count for the number of trailing spaces.
table-lookup
-
The column had only a small number of different values, and that 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 field:
Type
-
The field type may contain the following descriptors:
constant
-
All rows have the same value.
no endspace
-
Don't store endspace.
no endspace, not_always
-
Don't store endspace and don't do end space compression for all values.
no endspace, no empty
-
Don't store endspace. Don't 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
-
Don't store zeros.
always zero
-
0 values are stored in 1 bit.
Huff tree
-
The Huffman tree associated with the field
Bits
-
The number of bits used in the Huffman tree.
After you have run pack_isam/myisampack you must run
isamchk/myisamchk to recreate the index. At this time you
can also sort the index blocks and create statistics that is needed for
the MySQL optimizer to work more efficiently.
myisamchk -rq --analyze --sort-index table_name.MYI
isamchk -rq --analyze --sort-index table_name.ISM
After you have installed the packed table into the MySQL database
directory you should do mysqladmin flush-tables to force mysqld
to start using the new table.
Go to the first, previous, next, last section, table of contents.
|
