Chapter 8. Client and Utility Programs

Table of Contents

8.1. Overview of the Client-Side Scripts and Utilities
8.2. myisampack — Generate Compressed, Read-Only MyISAM Tables
8.3. mysql — The MySQL Command-Line Tool
8.3.1. Options
8.3.2. mysql Commands
8.3.3. Executing SQL Statements from a Text File
8.3.4. mysql Tips
8.4. mysqlaccess — Client for Checking Access Privileges
8.5. mysqladmin — Client for Administering a MySQL Server
8.6. mysqlbinlog — Utility for Processing Binary Log Files
8.7. mysqlcheck — A Table Maintenance and Repair Program
8.8. mysqldump — A Database Backup Program
8.9. mysqlhotcopy — A Database Backup Program
8.10. mysqlimport — A Data Import Program
8.11. mysqlshow — Display Database, Table, and Column Information
8.12. myisamlog — Display Contents of MyISAM Log File
8.13. perror — Explain Error Codes
8.14. replace — A String-Replacement Utility
8.15. mysql_zap — Kill Processes That Match a Pattern

There are many different MySQL client programs that connect to the server to access databases or perform administrative tasks. Other utilities are available as well. These do not communicate with the server but perform MySQL-related operations.

This chapter provides a brief overview of these programs and then a more detailed description of each one. The descriptions indicate how to invoke the programs and the options they understand. See Chapter 4, Using MySQL Programs for general information on invoking programs and specifying program options.

8.1. Overview of the Client-Side Scripts and Utilities

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

MySQL AB also provide a number of GUI tools for administering and otherwise working with MySQL servers. For basic information about these, see Chapter 4, Using MySQL Programs.

Each MySQL program takes many different options. However, every MySQL program provides a --help option that you can use to get a full description of the program's different options. For example, try mysql --help.

MySQL clients that communicate with the server using the mysqlclient library use the following environment variables:

MYSQL_UNIX_PORTThe default Unix socket file; used for connections to localhost
MYSQL_TCP_PORTThe default port number; used for TCP/IP connections
MYSQL_PWDThe default password
MYSQL_DEBUGDebug trace options when debugging
TMPDIRThe directory where temporary tables and files are created

Use of MYSQL_PWD is insecure. See Section 5.8.6, “Keeping Your Password Secure”.

You can override the default option values or values specified in environment variables for all standard programs by specifying options in an option file or on the command line. Section 4.3, “Specifying Program Options”.

8.2. 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 information needed to decompress columns is read into memory. This results in much better performance when accessing individual records, because you only have to uncompress exactly one record.

MySQL uses mmap() when possible to perform memory mapping on compressed tables. If mmap() does not work, MySQL falls back to normal read/write file operations.

Please note the following:

  • If the mysqld server was invoked with the --skip-external-locking option, it is not a good idea to invoke myisampack if the table might be updated by the server during the packing process.

  • After packing a table, it becomes read-only. This is generally intended (such as when accessing packed tables on a CD). Allowing writes to a packed table is on our TODO list, but with low priority.

  • myisampack can pack BLOB or TEXT columns. The older pack_isam program for ISAM tables cannot.

Invoke myisampack like this:

shell> myisampack [options] filename ...

Each filename should be the name of an index (.MYI) file. If you are not in the database directory, you should specify the pathname to the file. It is permissible to omit the .MYI extension.

myisampack supports the following options:

  • --help, -?

    Display a help message and exit.

  • --backup, -b

    Make a backup of the table data file using the name tbl_name.OLD.

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

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

  • --force, -f

    Produce a packed table even if it becomes larger than the original or if the intermediate file from an earlier invocation of myisampack exists. (myisampack creates an intermediate file named tbl_name.TMD in the database directory while it compresses the table. If you kill myisampack, the .TMD file might not be deleted.) Normally, myisampack exits with an error if it finds that tbl_name.TMD exists. With --force, myisampack packs the table anyway.

  • --join=big_tbl_name, -j big_tbl_name

    Join all tables named on the command line into a single table big_tbl_name. All tables that are to be combined must have identical structure (same column names and types, same indexes, and so forth).

  • --packlength=len, -p len

    Specify the record length storage size, in bytes. The value should be 1, 2, or 3. myisampack stores all rows with length pointers of 1, 2, or 3 bytes. In most normal cases, myisampack can determine the right length value before it begins packing the file, but it may notice during the packing process that it could have used a shorter length. In this case, myisampack prints a note that the next time you pack the same file, you could use a shorter record length.

  • --silent, -s

    Silent mode. Write output only when errors occur.

  • --test, -t

    Do not actually pack the table, just test packing it.

  • --tmpdir=path, -T path

    Use the named directory as the location where myisamchk creates temporary files.

  • --verbose, -v

    Verbose mode. Write information about the progress of the packing operation and its result.

  • --version, -V

    Display version information and exit.

  • --wait, -w

    Wait and retry if the table is in use. If the mysqld server was invoked with the --skip-external-locking option, it is not a good idea to invoke myisampack if the table might be updated by the server during the packing process.

The following sequence of commands illustrates a typical table compression session:

shell> ls -l station.*
-rw-rw-r--   1 monty    my         994128 Apr 17 19:00 station.MYD
-rw-rw-r--   1 monty    my          53248 Apr 17 19:00 station.MYI
-rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frm

shell> myisamchk -dvv station

MyISAM file:     station
Isam-version:  2
Creation time: 1996-03-13 10:08:58
Recover time:  1997-02-02  3:06:43
Data records:              1192  Deleted blocks:              0
Datafile parts:            1192  Deleted data:                0
Datafile pointer (bytes):     2  Keyfile pointer (bytes):     2
Max datafile length:   54657023  Max keyfile length:   33554431
Recordlength:               834
Record format: Fixed length

table description:
Key Start Len Index   Type                 Root  Blocksize    Rec/key
1   2     4   unique  unsigned long        1024       1024          1
2   32    30  multip. text                10240       1024          1

Field Start Length Type
1     1     1
2     2     4
3     6     4
4     10    1
5     11    20
6     31    1
7     32    30
8     62    35
9     97    35
10    132   35
11    167   4
12    171   16
13    187   35
14    222   4
15    226   16
16    242   20
17    262   20
18    282   20
19    302   30
20    332   4
21    336   4
22    340   1
23    341   8
24    349   8
25    357   8
26    365   2
27    367   2
28    369   4
29    373   4
30    377   1
31    378   2
32    380   8
33    388   4
34    392   4
35    396   4
36    400   4
37    404   1
38    405   4
39    409   4
40    413   4
41    417   4
42    421   4
43    425   4
44    429   20
45    449   30
46    479   1
47    480   1
48    481   79
49    560   79
50    639   79
51    718   79
52    797   8
53    805   1
54    806   1
55    807   20
56    827   4
57    831   4

shell> myisampack station.MYI
Compressing station.MYI: (1192 records)
- Calculating statistics

normal:     20  empty-space:   16  empty-zero:     12  empty-fill:  11
pre-space:   0  end-space:     12  table-lookups:   5  zero:         7
Original trees:  57  After join: 17
- Compressing file
87.14%
Remember to run myisamchk -rq on compressed tables

shell> ls -l station.*
-rw-rw-r--   1 monty    my         127874 Apr 17 19:00 station.MYD
-rw-rw-r--   1 monty    my          55296 Apr 17 19:04 station.MYI
-rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frm

shell> myisamchk -dvv station

MyISAM file:     station
Isam-version:  2
Creation time: 1996-03-13 10:08:58
Recover time:  1997-04-17 19:04:26
Data records:               1192  Deleted blocks:              0
Datafile parts:             1192  Deleted data:                0
Datafile pointer (bytes):      3  Keyfile pointer (bytes):     1
Max datafile length:    16777215  Max keyfile length:     131071
Recordlength:                834
Record format: Compressed

table description:
Key Start Len Index   Type                 Root  Blocksize    Rec/key
1   2     4   unique  unsigned long       10240       1024          1
2   32    30  multip. text                54272       1024          1

Field Start Length Type                         Huff tree  Bits
1     1     1      constant                             1     0
2     2     4      zerofill(1)                          2     9
3     6     4      no zeros, zerofill(1)                2     9
4     10    1                                           3     9
5     11    20     table-lookup                         4     0
6     31    1                                           3     9
7     32    30     no endspace, not_always              5     9
8     62    35     no endspace, not_always, no empty    6     9
9     97    35     no empty                             7     9
10    132   35     no endspace, not_always, no empty    6     9
11    167   4      zerofill(1)                          2     9
12    171   16     no endspace, not_always, no empty    5     9
13    187   35     no endspace, not_always, no empty    6     9
14    222   4      zerofill(1)                          2     9
15    226   16     no endspace, not_always, no empty    5     9
16    242   20     no endspace, not_always              8     9
17    262   20     no endspace, no empty                8     9
18    282   20     no endspace, no empty                5     9
19    302   30     no endspace, no empty                6     9
20    332   4      always zero                          2     9
21    336   4      always zero                          2     9
22    340   1                                           3     9
23    341   8      table-lookup                         9     0
24    349   8      table-lookup                        10     0
25    357   8      always zero                          2     9
26    365   2                                           2     9
27    367   2      no zeros, zerofill(1)                2     9
28    369   4      no zeros, zerofill(1)                2     9
29    373   4      table-lookup                        11     0
30    377   1                                           3     9
31    378   2      no zeros, zerofill(1)                2     9
32    380   8      no zeros                             2     9
33    388   4      always zero                          2     9
34    392   4      table-lookup                        12     0
35    396   4      no zeros, zerofill(1)               13     9
36    400   4      no zeros, zerofill(1)                2     9
37    404   1                                           2     9
38    405   4      no zeros                             2     9
39    409   4      always zero                          2     9
40    413   4      no zeros                             2     9
41    417   4      always zero                          2     9
42    421   4      no zeros                             2     9
43    425   4      always zero                          2     9
44    429   20     no empty                             3     9
45    449   30     no empty                             3     9
46    479   1                                          14     4
47    480   1                                          14     4
48    481   79     no endspace, no empty               15     9
49    560   79     no empty                             2     9
50    639   79     no empty                             2     9
51    718   79     no endspace                         16     9
52    797   8      no empty                             2     9
53    805   1                                          17     1
54    806   1                                           3     9
55    807   20     no empty                             3     9
56    827   4      no zeros, zerofill(2)                2     9
57    831   4      no zeros, zerofill(1)                2     9

myisampack displays the following kinds of information:

  • normal

    The number of columns for which no extra packing is used.

  • empty-space

    The number of columns containing values that are only spaces; these occupy one bit.

  • empty-zero

    The number of columns containing values that are only binary zeros; these occupy one bit.

  • empty-fill

    The number of integer columns that do not occupy the full byte range of their type; these are changed to a smaller type. For example, a BIGINT column (eight bytes) can be stored as a TINYINT column (one byte) if all its values are in the range from -128 to 127.

  • pre-space

    The number of decimal columns that are stored with leading spaces. In this case, each value contains a count for the number of leading spaces.

  • end-space

    The number of columns that have a lot of trailing spaces. In this case, each value contains a count for the number of trailing spaces.

  • table-lookup

    The column had only a small number of different values, which were converted to an ENUM before Huffman compression.

  • zero

    The number of columns for which all values are zero.

  • Original trees

    The initial number of Huffman trees.

  • After join

    The number of distinct Huffman trees left after joining trees to save some header space.

After a table has been compressed, myisamchk -dvv prints additional information about each column:

  • Type

    The column type. The value may contain any of the following descriptors:

    • constant

      All rows have the same value.

    • no endspace

      Do not store endspace.

    • no endspace, not_always

      Do not store endspace and do not do endspace compression for all values.

    • no endspace, no empty

      Do not store endspace. Do not store empty values.

    • table-lookup

      The column was converted to an ENUM.

    • zerofill(n)

      The most significant n bytes in the value are always 0 and are not stored.

    • no zeros

      Do not store zeros.

    • always zero

      Zero values are stored using one bit.

  • Huff tree

    The number of the Huffman tree associated with the column.

  • Bits

    The number of bits used in the Huffman tree.

After you run myisampack, you must run myisamchk to re-create any indexes. At this time, you can also sort the index blocks and create statistics needed for the MySQL optimizer to work more efficiently:

shell> myisamchk -rq --sort-index --analyze tbl_name.MYI

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.3. 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-line options.

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

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

shell> mysql db_name

Or:

shell> mysql --user=user_name --password=your_password db_name

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

You can run a script simply like this:

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

8.3.1. Options

mysql supports the following options:

  • --help, -?

    Display a help message and exit.

  • --batch, -B

    Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file.

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 5.10.1, “The Character Set Used for Data and Sorting”.

  • --compress, -C

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

  • --database=db_name, -D db_name

    The database to use. This is useful mainly in an 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/mysql.trace'.

  • --debug-info, -T

    Print some debugging information when the program exits.

  • --default-character-set=charset

    Use charset as the default character set. See Section 5.10.1, “The Character Set Used for Data and Sorting”.

  • --execute=statement, -e statement

    Execute the statement and quit. The default output format is like that produced with --batch. See Section 4.3.1, “Using Options on the Command Line” for some examples.

  • --force, -f

    Continue even if an SQL error occurs.

  • --host=host_name, -h host_name

    Connect to the MySQL server on the given host.

  • --html, -H

    Produce HTML output.

  • --ignore-space, -i

    Ignore spaces after function names. The effect of this is described in the discussion for IGNORE_SPACE in Section 5.3.2, “The Server SQL Mode”.

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

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

  • --named-commands, -G

    Named commands are enabled. Long format commands are allowed as well as shortened \* commands. For example, quit and \q both are recognized.

  • --no-auto-rehash, -A

    No automatic rehashing. This option causes mysql to start faster, but you must issue the rehash command if you want to use table and column name completion.

  • --no-beep, -b

    Do not beep when errors occur.

  • --no-named-commands, -g

    Named commands are disabled. Use the \* form only, or use named commands only at the beginning of a line ending with a semicolon (‘;’). As of MySQL 3.23.22, mysql starts with this option enabled by default. However, even with this option, long-format commands still work from the first line.

  • --no-pager

    Do not use a pager for displaying query output. Output paging is discussed further in Section 8.3.2, “mysql Commands”.

  • --no-tee

    Do not copy output to a file. Tee files are discussed further in Section 8.3.2, “mysql Commands”.

  • --one-database, -O

    Ignore statements except those for the default database named on the command line. This is useful for skipping updates to other databases in the binary log.

  • --pager[=command]

    Use the given command for paging query output. If the command is omitted, the default pager is the value of your PAGER environment variable. Valid pagers are less, more, cat [> filename], and so forth. This option works only on Unix. It does not work in batch mode. Output paging is discussed further in Section 8.3.2, “mysql Commands”.

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

    The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, you are prompted for one. The password should be omitted on SysV-based UNIX systems, as the password may be displayed in the output of ps.

  • --port=port_num, -P port_num

    The TCP/IP port number to use for the connection.

  • --prompt=format_str

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

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

    The connection protocol to use.

  • --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 rows to affect using key values. If you have set this option in an option file, you can override it by using --safe-updates on the command line. See Section 8.3.4, “mysql Tips” for more information about this option.

  • --secure-auth

    Do not send passwords to the server in old (pre-4.1.1) format. This prevents connections except for servers that use the newer password format.

  • --show-warnings

    Cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode. This option was added in MySQL 5.0.6.

  • --sigint-ignore

    Ignore SIGINT signals (typically the result of typing Control-C).

  • --silent, -s

    Silent mode. Produce less output. This option can be given multiple times to produce less and less output.

  • --skip-column-names, -N

    Do not write column names in results.

  • --skip-line-numbers, -L

    Do not write line numbers for errors. Useful when you want to compare result files that include error messages.

  • --socket=path, -S path

    The socket file to use for the connection.

  • --table, -t

    Display output in table format. This is the default for interactive use, but can be used to produce table output in batch mode.

  • --tee=file_name

    Append a copy of output to the given file. This option does not work in batch mode. Tee files are discussed further in Section 8.3.2, “mysql Commands”.

  • --unbuffered, -n

    Flush the buffer after each query.

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

  • --verbose, -v

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

  • --version, -V

    Display version information and exit.

  • --vertical, -E

    Print the rows of query output vertically. Without this option, you can specify vertical output for individual statements by terminating them with \G.

  • --wait, -w

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

  • --xml, -X

    Produce XML output.

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

  • connect_timeout

    The number of seconds before connection timeout. (Default value is 0.)

  • max_allowed_packet

    The maximum packet length to send to or receive from the server. (Default value is 16MB.)

  • max_join_size

    The automatic limit for rows in a join when using --safe-updates. (Default value is 1,000,000.)

  • net_buffer_length

    The buffer size for TCP/IP and socket communication. (Default value is 16KB.)

  • select_limit

    The automatic limit for SELECT statements when using --safe-updates. (Default value is 1,000.)

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

On Unix, the mysql client writes a record of executed statements to a history file. By default, the history file is named .mysql_history and is created in your home directory. To specify a different file, set the value of the MYSQL_HISTFILE environment variable.

If you do not want to maintain a history file, first remove .mysql_history if it exists, and then use either of the following techniques:

  • Set the MYSQL_HISTFILE variable to /dev/null. To cause this setting to take effect each time you log in, put the setting in one of your shell's startup files.

  • Create .mysql_history as a symbolic link to /dev/null:

    shell> ln -s /dev/null $HOME/.mysql_history
    

    You need do this only once.

8.3.2. mysql Commands

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

mysql> help

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear command.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute a SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

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

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

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

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

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

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

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

A few tips about the pager command:

  • You can use it to write to a file and the results go only to the file:

    mysql> pager cat > /tmp/log.txt
    

    You can also pass any options for the program that you want to use as your pager:

    mysql> pager less -n -i -S
    
  • In the preceding example, note the -S option. You may find it very useful for browsing wide query results. Sometimes a very wide result set is difficult to read on the screen. The -S option to less can make the result set much more readable because you can scroll it horizontally using the left-arrow and right-arrow keys. You can also use -S interactively within less to switch the horizontal-browse mode on and off. For more information, read the less manual page:

    shell> man less
    
  • You can specify very complex pager commands for handling query output:

    mysql> pager cat | tee /dr1/tmp/res.txt \
              | tee /dr2/tmp/res2.txt | less -n -i -S
    

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

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

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

OptionDescription
\vThe server version
\dThe current database
\hThe server host
\pThe current TCP/IP port or socket file
\uYour username
\UYour full user_name@host_name account name
\\A literal ‘\’ backslash character
\nA newline character
\tA tab character
\ A space (a space follows the backslash)
\_A space
\RThe current time, in 24-hour military time (0-23)
\rThe current time, standard 12-hour time (1-12)
\mMinutes of the current time
\yThe current year, two digits
\YThe current year, four digits
\DThe full current date
\sSeconds of the current time
\wThe current day of the week in three-letter format (Mon, Tue, ...)
\Pam/pm
\oThe current month in numeric format
\OThe current month in three-letter format (Jan, Feb, ...)
\cA counter that increments for each statement you issue
\SSemicolon
\'Single quote
\"Double quote

\’ followed by any other letter just becomes that letter.

If you specify the prompt command with no argument, mysql resets the prompt to the default of mysql>.

You can set the prompt in several ways:

  • Use an environment variable

    You can set the MYSQL_PS1 environment variable to a prompt string. For example:

    shell> export MYSQL_PS1="(\u@\h) [\d]> "
    
  • Use an option file

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

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

    In this example, note that the backslashes are doubled. If you set the prompt using the prompt option in an option file, it is advisable to double the backslashes when using the special prompt options. There is some overlap in the set of allowable prompt options and the set of special escape sequences that are recognized in option files. (These sequences are listed in Section 4.3.2, “Using Option Files”.) The overlap may cause you problems if you use single backslashes. For example, \s is interpreted as a space rather than as the current seconds value. The following example shows how to define a prompt within an option file to include the current time in HH:MM:SS> format:

    [mysql]
    prompt="\\r:\\m:\\s> "
    
  • Use a command-line option

    You can set the --prompt option on the command line to mysql. For example:

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

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

    mysql> prompt (\u@\h) [\d]>\_
    PROMPT set to '(\u@\h) [\d]>\_'
    (user@host) [database]>
    (user@host) [database]> prompt
    Returning to default PROMPT of mysql>
    mysql>
    

8.3.3. Executing SQL Statements from a Text File

The mysql client typically is used interactively, like this:

shell> mysql db_name

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

shell> mysql db_name < text_file

You can also start your text file with a USE db_name statement. In this case, it is unnecessary to specify the database name on the command line:

shell> mysql < text_file

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

mysql> source filename
mysql> \. filename

Sometimes you may want your script to display progress information to the user; for this you can insert some lines like

SELECT '<info_to_display>' AS ' ';

which outputs <info_to_display>.

For more information about batch mode, see Section 3.5, “Using mysql in Batch Mode”.

8.3.4. mysql Tips

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

8.3.4.1. Displaying Query Results Vertically

Some query results are much more readable when displayed vertically, instead of in the usual horizontal table format. Queries can be displayed vertically by terminating the query with \G instead of a semicolon. For example, longer text values that include newlines often are much easier to read with vertical output:

mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
*************************** 1. row ***************************
  msg_nro: 3068
     date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
    reply: monty@no.spam.com
  mail_to: "Thimble Smith" <tim@no.spam.com>
      sbj: UTF-8
      txt: >>>>> "Thimble" == Thimble Smith writes:

Thimble> Hi.  I think this is a good idea.  Is anyone familiar
Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my
Thimble> TODO list and see what happens.

Yes, please do that.

Regards,
Monty
     file: inbox-jani-1
     hash: 190402944
1 row in set (0.09 sec)

8.3.4.2. Using the --safe-updates Option

For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). 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;

See Section 13.5.3, “SET Syntax”.

The SET statement has the following effects:

  • You are not allowed to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both). For example:

    UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
    
    UPDATE tbl_name SET not_key_column=val LIMIT 1;
    
  • All large SELECT results are automatically limited to 1,000 rows unless the statement includes a LIMIT clause.

  • Multiple-table SELECT statements that probably need to examine more than 1,000,000 row combinations are aborted.

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

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

8.3.4.3. Disabling mysql Auto-Reconnect

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

mysql> SET @a=1;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t VALUES(@a);
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Query OK, 1 row affected (1.30 sec)

mysql> SELECT * FROM t;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.05 sec)

The @a user variable has been lost with the connection, and after the reconnection it is undefined. If it is important to have mysql terminate with an error if the connection has been lost, you can start the mysql client with the --skip-reconnect option.

8.4. mysqlaccess — Client for Checking Access Privileges

mysqlaccess is a diagnostic tool that Yves Carlier has provided for the MySQL distribution. It checks the access privileges for a hostname, username, and database combination. Note that mysqlaccess checks access using only the user, db, and host tables. It does not check table, column, or routine privileges specified in the tables_priv, columns_priv, or procs_priv tables.

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.

  • --plan

    Display suggestions and ideas for future releases.

  • --preview

    Show the privilege differences after making changes to the temporary grant tables.

  • --relnotes

    Display the release notes.

  • --rhost=host_name, -H host_name

    Connect to the MySQL server on the given host.

  • --rollback

    Undo the most recent changes to the temporary grant tables.

  • --spassword[=password], -P[password]

    The password to use when connecting to the server as the superuser. If you omit the password value following the --password or -p option on the command line, you are prompted for one.

  • --superuser=user_name, -U user_name

    Specify the username for connecting as the superuser.

  • --table, -t

    Generate reports in table format.

  • --user=user_name, -u user_name

    The hostname to use in the access privileges.

  • --version, -v

    Display version information and exit.

If your MySQL distribution is installed in some non-standard location, you must change the location where mysqlaccess expects to find the mysql client. Edit the mysqlaccess script at approximately line 18. Search for a line that looks like this:

$MYSQL     = '/usr/local/bin/mysql';    # path to mysql executable

Change the path to reflect the location where mysql actually is stored on your system. If you do not do this, a Broken pipe error will occur when you run mysqlaccess.

8.5. mysqladmin — Client for Administering a MySQL Server

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

Invoke mysqladmin like this:

shell> mysqladmin [options] command [command-options] [command [command-options]] ...

mysqladmin supports the following commands:

  • create db_name

    Create a new database named db_name.

  • debug

    Tell the server to write debug information to the error log.

  • drop db_name

    Delete the database named db_name and all its tables.

  • extended-status

    Display the server status variables and their values.

  • flush-hosts

    Flush all information in the host cache.

  • flush-logs

    Flush all logs.

  • flush-privileges

    Reload the grant tables (same as reload).

  • flush-status

    Clear status variables.

  • flush-tables

    Flush all tables.

  • flush-threads

    Flush the thread cache.

  • kill id,id,...

    Kill server threads.

  • old-password new-password

    This is like the password command but stores the password using the old (pre-4.1) password-hashing format. (See Section 5.7.9, “Password Hashing in MySQL 4.1”.)

  • password new-password

    Set a new password. This changes the password to new-password for the account that you use with mysqladmin for connecting to the server.

    If new-password contains spaces or other characters that are special to your command interpreter, you need to enclose it within quotes. On Windows, be sure to use double quotes rather than single quotes; single quotes are not stripped from the password, but rather are interpreted as part of the password. For example:

    shell> mysqladmin password "my new password"

  • ping

    Check whether the server is alive. The return status from mysqladmin is 0 if the server is running, 1 if it is not. 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 not the same as 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.16, “SHOW PROCESSLIST Syntax”.)

  • reload

    Reload the grant tables.

  • refresh

    Flush all tables and close and open log files.

  • shutdown

    Stop the server.

  • start-slave

    Start replication on a slave server.

  • status

    Display a short server status message.

  • stop-slave

    Stop replication on a slave server.

  • variables

    Display the server system variables and their values.

  • version

    Display version information from the server.

All commands can be shortened to any unique prefix. For example:

shell> mysqladmin proc stat
+----+-------+-----------+----+---------+------+-------+------------------+
| Id | User  | Host      | db | Command | Time | State | Info             |
+----+-------+-----------+----+---------+------+-------+------------------+
| 51 | monty | localhost |    | Query   | 0    |       | show processlist |
+----+-------+-----------+----+---------+------+-------+------------------+
Uptime: 1473624  Threads: 1  Questions: 39487  
Slow queries: 0  Opens: 541  Flush tables: 1  
Open tables: 19  Queries per second avg: 0.0268

The mysqladmin status command result displays the following values:

  • Uptime

    The number of seconds the MySQL server has been running.

  • Threads

    The number of active threads (clients).

  • Questions

    The number of questions (queries) from clients since the server was started.

  • Slow queries

    The number of queries that have taken more than long_query_time seconds. See Section 5.11.4, “The Slow Query Log”.

  • Opens

    The number of tables the server has opened.

  • Flush tables

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

  • Open tables

    The number of tables that currently are open.

  • Memory in use

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

  • Maximum memory used

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

If you execute mysqladmin shutdown when connecting to a local server using a Unix socket file, mysqladmin waits until the server's process ID file has been removed, to ensure that the server has stopped properly.

mysqladmin supports the following options:

  • --help, -?

    Display a help message and exit.

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 5.10.1, “The Character Set Used for Data and Sorting”.

  • --compress, -C

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

  • --count=num, -c num

    The number of iterations to make. This works only with --sleep (-i).

  • --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/mysqladmin.trace'.

  • --default-character-set=charset

    Use charset as the default character set. See Section 5.10.1, “The Character Set Used for Data and Sorting”.

  • --force, -f

    Do not ask for confirmation for the drop database command. With multiple commands, continue even if an error occurs.

  • --host=host_name, -h host_name

    Connect to the MySQL server on the given host.

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

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

  • --port=port_num, -P port_num

    The TCP/IP port number to use for the connection.

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

    The connection protocol to use.

  • --relative, -r

    Show the difference between the current and previous values when used with -i. Currently, this option works only with the extended-status command.

  • --silent, -s

    Exit silently if a connection to the server cannot be established.

  • --sleep=delay, -i delay

    Execute commands again and again, sleeping for delay seconds in between.

  • --socket=path, -S path

    The socket file to use for the connection.

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

  • --verbose, -v

    Verbose mode. Print out more information on what the program does.

  • --version, -V

    Display version information and exit.

  • --vertical, -E

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

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

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

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

  • connect_timeout

    The maximum number of seconds before connection timeout. The default value is 43200 (12 hours).

  • shutdown_timeout

    The maximum number of seconds to wait for shutdown. The default value is 3600 (1 hour).

It is also possible to set variables by using --set-variable=var_name=value or -O var_name=value syntax. However, this syntax is now deprecated, and should no longer be used.

8.6. mysqlbinlog — Utility for Processing Binary Log Files

The binary log files that the server generates are written in binary format. To examine these files in text format, use the mysqlbinlog utility.

Invoke mysqlbinlog like this:

shell> mysqlbinlog [options] log-file ...

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

shell> mysqlbinlog binlog.0000003

The output includes all statements contained in binlog.000003, together with other information such as the time each statement took, the thread ID of the client that issued it, the timestamp when it was issued, and so forth.

Normally, you use mysqlbinlog to read binary log files directly and apply them to the local MySQL server. It is also possible to read binary logs from a remote server by using the --read-from-remote-server option.

When you read remote binary logs, the connection parameter options can be given to indicate how to connect to the server, but they are ignored unless you also specify the --read-from-remote-server option. These options are --host, --password, --port, --protocol, --socket, and --user.

You can also use mysqlbinlog to read relay log files written by a slave server in a replication setup. Relay logs have the same format as binary log files.

The binary log is discussed further in Section 5.11.3, “The Binary Log”.

mysqlbinlog supports the following options:

  • --help, -?

    Display a help message and exit.

  • --database=db_name, -d db_name

    List entries for just this database (local log only).

  • --force-read, -f

    With this option, if mysqlbinlog reads a binary log event that it does not recognize, it prints a warning, ignores the event, and continues. Without this option, mysqlbinlog stops if it reads such an event.

  • --hexdump, -H

    Display a hex dump of the log in comments. This output can be helpful for replication debugging. 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.

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

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

  • --port=port_num, -P port_num

    The TCP/IP port number to use for connecting to a remote server.

  • --position=N, -j N

    Deprecated, use --start-position instead.

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

    The connection protocol to use.

  • --read-from-remote-server, -R

    Read the binary log from a MySQL server. Any connection parameter options are ignored unless this option is given as well. These options are --host, --password, --port, --protocol, --socket, and --user.

  • --result-file=name, -r name

    Direct output to the given file.

  • --short-form, -s

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

  • --socket=path, -S path

    The socket file to use for the connection.

  • --start-datetime=datetime

    Start reading the binary log at the first event having a datetime equal to or later than the datetime argument. The datetime value is relative to the local time zone on the machine where you run mysqlbinlog. The value should be in a format accepted for the DATETIME or TIMESTAMP data types. For example:

    shell> mysqlbinlog --start-datetime="2004-12-25 11:25:56" binlog.000003
    

    This option is useful for point-in-time recovery.

  • --stop-datetime=datetime

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

  • --start-position=N

    Start reading the binary log at the first event having a position equal to the N argument.

  • --stop-position=N

    Stop reading the binary log at the first event having a position equal or greater than the N argument.

  • --to-last-log, -t

    Do not stop at the end of the requested binary log of the 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.

  • --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. Note: This option requires that you have the SUPER privilege.

  • --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 options:

  • open_files_limit

    Specify the number of open file descriptors to reserve.

You can pipe the output of mysqlbinlog into a mysql client to execute the statements contained in the binary log. This is used to recover from a crash when you have an old backup (see Section 5.9.1, “Database Backups”):

shell> mysqlbinlog hostname-bin.000001 | mysql

Or:

shell> mysqlbinlog hostname-bin.[0-9]* | mysql

You can also redirect the output of mysqlbinlog to a text file instead, if you need to modify the statement log first (for example, to remove statements that you 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 --position option, which prints only those statements with an offset in the binary log greater than or equal to a given position (the given position must match the start of one event). It also has options to stop or start when it sees an event of a given date and time. This enables you to perform point-in-time recovery using the --stop-datetime option (to be able to say, for example, “roll forward my databases to how they were today at 10:30 AM”).

If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server. Here is an example that demonstrates what may be unsafe:

shell> mysqlbinlog hostname-bin.000001 | mysql # DANGER!!
shell> mysqlbinlog hostname-bin.000002 | mysql # DANGER!!

Processing binary logs this way using different connections to the server 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 this:

shell> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql

Another approach is to do this:

shell> mysqlbinlog hostname-bin.000001 >  /tmp/statements.sql
shell> mysqlbinlog hostname-bin.000002 >> /tmp/statements.sql
shell> mysql -e "source /tmp/statements.sql"

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:

shell> mysqlbinlog --hexdump master-bin.000001

With the preceding command, the output might look like this:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#051024 17:24:13 server id 1  end_log_pos 98
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
# 00000004 9d fc 5c 43   0f   01 00 00 00   5e 00 00 00   62 00 00 00   00 00
# 00000017 04 00 35 2e 30 2e 31 35  2d 64 65 62 75 67 2d 6c |..5.0.15.debug.l|
# 00000027 6f 67 00 00 00 00 00 00  00 00 00 00 00 00 00 00 |og..............|
# 00000037 00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00 |................|
# 00000047 00 00 00 00 9d fc 5c 43  13 38 0d 00 08 00 12 00 |.......C.8......|
# 00000057 04 04 04 04 12 00 00 4b  00 04 1a                |.......K...|
#       Start: binlog v 4, server v 5.0.15-debug-log created 051024 17:24:13
#       at startup
ROLLBACK;

The hex dump output contains the following elements:

  • Position: The byte position within the log file.

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

  • Type: The type of the log event. '0f' means that the example event is a FORMAT_DESCRIPTION_EVENT. The types are:

    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 queries 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 if 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 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 query.
    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 XA transaction
    11  BEGIN_LOAD_QUERY_EVENT
        Used for LOAD DATA statements in MySQL 5 and later.
    12  EXECUTE_LOAD_QUERY_EVENT
        Used for LOAD DATA 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.

    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 e.g. '01 00') in an
        FORMAT_DESCRIPTION_EVENT, then the log file has not been
        properly closed.  Most probably 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 (example '04 00'), e.g. if the event uses
        temporary tables.
    08  LOG_EVENT_SUPPRESS_USE_F
        Set in some circumstances when the event is not dependent on
        the current database
    

    The other flags are reserved for future use.

The hex dump output format might change in later releases.

8.7. mysqlcheck — A Table Maintenance and Repair Program

The mysqlcheck client checks and repairs MyISAM tables. It can also optimize and analyze tables.

mysqlcheck is similar in function to myisamchk, but works differently. The main operational difference is that mysqlcheck must be used when the mysqld server is running, whereas myisamchk should be used when it is not. The benefit of using mysqlcheck is that you do not have to stop the server to check or repair your tables.

mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user. It determines which statements to use for the operation you want to perform, then sends the statements to the server to be executed.

There are three general ways to invoke mysqlcheck:

shell> mysqlcheck [options] db_name [tables]
shell> mysqlcheck [options] --databases DB1 [DB2 DB3...]
shell> mysqlcheck [options] --all-databases

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

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

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

mysqlrepairThe default option is --repair
mysqlanalyzeThe default option is --analyze
mysqloptimizeThe 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.

  • --analyze, -a

    Analyze the tables.

  • --auto-repair

    If a checked table is corrupted, automatically fix it. Any necessary repairs are done after all tables have been checked.

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 5.10.1, “The Character Set Used for Data and Sorting”.

  • --check, -c

    Check the tables for errors.

  • --check-only-changed, -C

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

  • --compress

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

  • --databases, -B

    Process all tables in the named databases. With this option, all name arguments are regarded as database names, not as table names.

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

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

  • --default-character-set=charset

    Use charset as the default character set. See Section 5.10.1, “The Character Set Used for Data and Sorting”.

  • --extended, -e

    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.

  • --port=port_num, -P port_num

    The TCP/IP port number to use for the connection.

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

    The connection protocol to use.

  • --quick, -q

    If you are using this option to check tables, it prevents the check from scanning the rows to check for incorrect links. This is the fastest check method.

    If you are using this option to repair tables, it tries to repair only the index tree. This is the fastest repair method.

  • --repair, -r

    Perform a repair that can fix almost anything except unique keys that are not unique.

  • --silent, -s

    Silent mode. Print only error messages.

  • --socket=path, -S path

    The socket file to use for the connection.

  • --tables

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

  • --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.8. mysqldump — A Database Backup Program

The mysqldump client can be used to dump a database or a collection of databases for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump contains SQL statements to create the table and/or populate the table.

If you are doing a backup on the server, and your tables all are MyISAM tables, you could consider using the mysqlhotcopy instead since faster backups and faster restores can be accomplished with the latter. See Section 8.9, “mysqlhotcopy — A Database Backup Program”.

There are three general ways to invoke mysqldump:

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases DB1 [DB2 DB3...]
shell> mysqldump [options] --all-databases

If you do not name any tables or 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.

If you run mysqldump without the --quick or --opt option, mysqldump loads the whole result set into memory before dumping the result. This probably is a problem if you are dumping a big database. This option is enabled by default, but can be disabled with --skip-opt.

If you are using a recent copy of the mysqldump program to generate a dump to be reloaded into a very old MySQL server, you should not use the --opt or -e options.

mysqldump supports the following options:

  • --help, -?

    Display a help message and exit.

  • --add-drop-database

    Add a DROP DATABASE statement before each CREATE DATABASE statement.

  • --add-drop-table

    Add a DROP TABLE statement before each CREATE TABLE statement.

  • --add-locks

    Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded. See Section 7.2.16, “Speed of INSERT Statements”.

  • --all-databases, -A

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

  • --allow-keywords

    Allow creation of column names that are keywords. This works by prefixing each column name with the table name.

  • --comments[={0|1}]

    If set to 0, suppresses additional information in the dump file such as program version, server version, and host. --skip-comments has the same effect as --comments=0. The default value is 1, which includes the extra information.

  • --compact

    Produce less verbose output. This option suppresses comments and enables the --skip-add-drop-table, --no-set-names, --skip-disable-keys, and --skip-add-locks options.

  • --compatible=name

    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.3.2, “The Server SQL Mode”.

    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.

  • --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 IF NOT EXISTS db_name and USE db_name 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'.

  • --default-character-set=charset

    Use charset as the default character set. See Section 5.10.1, “The Character Set Used for Data and Sorting”. If not specified, mysqldump uses utf8.

  • --delayed-insert

    Insert rows using INSERT DELAYED statements.

  • --delete-master-logs

    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 for MyISAM tables only.

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

  • --fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=..., --lines-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 13.2.5, “LOAD DATA INFILE Syntax”.

  • --first-slave, -x

    Deprecated, now renamed to --lock-all-tables.

  • --flush-logs, -F

    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.

  • --force, -f

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

  • --host=host_name, -h host_name

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

  • --hex-blob

    Dump binary string columns using hexadecimal notation (for example, 'abc' becomes 0x616263). The affected columns are BINARY, VARBINARY, BLOB.

  • --lock-all-tables, -x

    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 starting the dump. 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. So, 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]

    This option causes the binary log position and filename to be written 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 that makes a slave server start from the correct position in the master's binary logs if you use this SQL dump of the master to set up a slave. If the option value is equal to 2, the CHANGE MASTER statement is written as an SQL comment. This is the default action if value is omitted.

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

  • --no-create-db, -n

    This option suppresses the CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name statements that are otherwise included in the output if the --databases or --all-databases option is given.

  • --no-create-info, -t

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

  • --no-data, -d

    Do not write any row information for the table. This is very useful if you want to get a dump of only the structure for a 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. This option is on by default, but can be disabled with --skip-opt. To disable only certain of the options enabled by --opt, use their --skip forms; for example, --skip-add-drop-table or --skip-quick.

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

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

  • --port=port_num, -P port_num

    The TCP/IP port number to use for the connection.

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

    The connection protocol to use.

  • --quick, -q

    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 server SQL mode includes the ANSI_QUOTES option, names are quoted within ‘"’ characters. It is on 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, because it prevents newline ‘\n’ characters from being converted to ‘\r\n’ carriage return/newline sequences.

  • --routines, -R

    Dump stored routines (functions and procedures) in the dumped databases. The output generated by using ---routines contains CREATE PROCEDURE and CREATE FUNCTION statements to recreate the routines. However, these statements do not include attributes such as the routine definer or the creation and modification timestamps. This means that when the routines are reloaded, they will be created with the definer set to the reloading user and timestamps equal to the reload time.

    If you require routines to be recreated with their original definer and 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.

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

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

  • --socket=path, -S path

    The socket file to use when connecting to localhost (which is the default host).

  • --skip-comments

    See the description for the --comments option.

  • --tab=path, -T path

    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--xxx 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. All arguments following the option are regarded as table names.

  • --triggers

    Dump triggers for each dumped table. This option is on 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 local time zones of the source and destination servers.) --tz-utc also 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 out more information on what the program does.

  • --version, -V

    Display version information and exit.

  • --where='where-condition', -w 'where-condition'

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

    Examples:

    "--where=user='jimf'"
    "-wuserid>1"
    "-wuserid<1"
    
  • --xml, -X

    Write dump output as well-formed XML.

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

  • 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. However, this syntax now deprecated.

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

shell> mysqldump --opt 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:

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

It is possible to dump several databases with one command:

shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

If you want to dump all databases, use the --all-databases option:

shell> mysqldump --all-databases > all_databases.sql

If tables are stored in the InnoDB storage engine, mysqldump provides a way of making an online backup of these (see command below). This backup just needs to acquire a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and lock is released. So if and only if one long updating statement is running when the FLUSH... is issued, the MySQL server may get stalled until that long statement finishes, and then the dump becomes lock-free. So if the MySQL server receives only short (in the sense of "short execution time") updating statements, even if there are plenty of them, the initial lock period should not be noticeable.

shell> mysqldump --all-databases --single-transaction > all_databases.sql

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

shell> mysqldump --all-databases --master-data=2 > all_databases.sql
or
shell> mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql

The simultaneous use of --master-data and --single-transaction provides a convenient way to make an online backup suitable for point-in-time recovery if tables are stored in the InnoDB storage engine.

For more information on making backups, see Section 5.9.1, “Database Backups”.

8.9. 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 backup of a database 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. 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.

  • --allowold

    Do not abort if target exists (rename it by adding an _old suffix).

  • --checkpoint=db_name.tbl_name

    Insert checkpoint entries into the specified db_name.tbl_name.

  • --debug

    Enable debug output.

  • --dryrun, -n

    Report actions without performing them.

  • --flushlog

    Flush logs after all tables are locked.

  • --keepold

    Do not delete previous (renamed) target when done.

  • --method=command

    Method for copy (cp or scp).

  • --noindices

    Do not include full index files in the backup. This makes the backup smaller and faster. The indexes 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.

  • --port=port_num, -P port_num

    The TCP/IP port number to use when connecting to the local server.

  • --quiet, -q

    Be silent except for errors.

  • --regexp=expr

    Copy all databases with names matching the given regular expression.

  • --socket=path, -S path

    The Unix socket file to use for the connection.

  • --suffix=str

    The suffix for names of copied databases.

  • --tmpdir=path

    The temporary directory (instead of /tmp).

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

mysqlhotcopy reads the [client] and [mysqlhotcopy] option groups from option files.

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

Use perldoc for additional mysqlhotcopy documentation:

shell> perldoc mysqlhotcopy

8.10. mysqlimport — A Data Import Program

The mysqlimport client provides a command-line interface to the LOAD DATA INFILE SQL statement. Most options to mysqlimport correspond directly to clauses of LOAD DATA INFILE. See Section 13.2.5, “LOAD DATA INFILE Syntax”.

Invoke mysqlimport like this:

shell> mysqlimport [options] db_name textfile1 [textfile2 ...]

For each text file named on the command line, mysqlimport strips any extension from the filename and uses the result to determine the name of the table into which to import the file's contents. For example, files named patient.txt, patient.text, and patient all would be imported into a table named patient.

mysqlimport supports the following options:

  • --help, -?

    Display a help message and exit.

  • --columns=column_list, -c column_list

    This option takes a comma-separated list of column names as its value. The order of the column names indicates how to match up data file columns with table columns.

  • --compress, -C

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

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

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

  • --delete, -D

    Empty the table before importing the text file.

  • --fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=..., --lines-terminated-by=...

    These options have the same meaning as the corresponding clauses for LOAD DATA INFILE. See Section 13.2.5, “LOAD DATA INFILE Syntax”.

  • --force, -f

    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.

  • --local, -L

    Read input files locally from the client host.

  • --lock-tables, -l

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

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

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

  • --port=port_num, -P port_num

    The TCP/IP port number to use for the connection.

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

    The connection protocol to use.

  • --replace, -r

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

    The socket file to use when connecting to localhost (which is the default host).

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

  • --verbose, -v

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

  • --version, -V

    Display version information and exit.

Here is a sample session that demonstrates use of mysqlimport:

shell> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
shell> ed
a
100     Max Sydow
101     Count Dracula
.
w imptest.txt
32
q
shell> od -c imptest.txt
0000000   1   0   0  \t   M   a   x       S   y   d   o   w  \n   1   0
0000020   1  \t   C   o   u   n   t       D   r   a   c   u   l   a  \n
0000040
shell> mysqlimport --local test imptest.txt
test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
shell> mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id   | n             |
+------+---------------+
|  100 | Max Sydow     |
|  101 | Count Dracula |
+------+---------------+

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

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

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

Invoke mysqlshow like this:

shell> mysqlshow [options] [db_name [tbl_name [col_name]]]
  • If no database is given, all matching databases are 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.

Note that in newer MySQL versions, you see only those database, tables, or columns for which you have some privileges.

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

mysqlshow supports the following options:

  • --help, -?

    Display a help message and exit.

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 5.10.1, “The Character Set Used for Data and Sorting”.

  • --compress, -C

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

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

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

  • --default-character-set=charset

    Use charset as the default character set. See Section 5.10.1, “The Character Set Used for Data and Sorting”.

  • --host=host_name, -h host_name

    Connect to the MySQL server on the given host.

  • --keys, -k

    Show table indexes.

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

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

  • --port=port_num, -P port_num

    The TCP/IP port number to use for the connection.

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

    The connection protocol to use.

  • --show-table-type

    Show a column indicating the table type, as in SHOW FULL TABLES. Added in MySQL 5.0.4.

  • --socket=path, -S path

    The socket file to use when connecting to localhost (which is the default host).

  • --status, -i

    Display extra information about each table.

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

  • --verbose, -v

    Verbose mode. Print out more information 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.12. myisamlog — Display Contents of MyISAM Log File

myisamlog processes the contents of a MyISAM log file.

Invoke myisamlog like this:

shell> myisamlog [options] [logfile-name [tbl_name] ...]

The normal operation is update (-u). If a recovery is done (-r), all writes and possibly updates and deletes are done and errors are only counted. If no logfile name is given, myisam.log is used. If table names 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

    Do only N commands.

  • -f N

    Specify the maximum number of open files.

  • -F filepath/

    TODO

  • -i

    Display extra information before exiting.

  • -o offset

    Specify the starting offset.

  • -p N

    Remove N components from path.

  • -r

    Recover.

  • -R record-pos-file record-pos

    Specify record position file and record position.

  • -u

    Update.

  • -v

    Verbose mode. Produce more output. This option can be given multiple times to produce more and more output.

  • -w write-file

    Specify write file.

  • -V

    Display version information.

8.13. 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 either examining the documentation for your system or by using the perror utility.

perror prints a description for a system error code or for a storage engine (table handler) error code.

Invoke perror like this:

shell> perror [options] errorcode ...

Example:

shell> perror 13 64
Error code  13:  Permission denied
Error code  64:  Machine is not on the network

Note: To obtain the error message for a MySQL Cluster error code, invoke perror with the --ndb option:

shell> perror --ndb errorcode

Note that the meaning of system error messages may be dependent on your operating system. A given error code may mean different things on different operating systems.

8.14. replace — A String-Replacement Utility

The replace utility program changes strings in place in files or on the standard input. It uses a finite state machine to match longer strings first. It can be used to swap strings. For example, the following command swaps a and b in the given files, file1 and file2:

shell> replace a b b a -- file1 file2 ...

Use the -- option to indicate where the string-replacement list ends and the filenames begin.

Any file named on the command line is modified in place, so you may want to make a copy of the original before converting it.

If no files are named on the command line, replace reads the standard input and writes to the standard output. In this case, no -- option is needed.

The replace program is used by msql2mysql. See Section 22.9.1, “msql2mysql — Convert mSQL Programs for Use with MySQL”.

replace supports the following options:

  • -?, -I

    Display a help message and exit.

  • -# debug_options

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

  • -s

    Silent mode. Print out less information what the program does.

  • -v

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

  • -V

    Display version information and exit.

8.15. mysql_zap — Kill Processes That Match a Pattern

mysql_zap kills processes that match a pattern. Invoke it 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.