MySQL Reference Manual

MySQL Client-Side Scripts and Utilities

Overview of the Client-Side Scripts and Utilities

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

Name	Description
MYSQL_UNIX_PORT	The default socket; used for connections to localhost
MYSQL_TCP_PORT	The default TCP/IP port
MYSQL_PWD	The default password
MYSQL_DEBUG	Debug-trace options when debugging
TMPDIR	The directory where temporary tables/files are created

Use of MYSQL_PWD is insecure. See Section 4.2.8.

The mysql client uses the file named in the MYSQL_HISTFILE environment variable to save the command-line history. The default value for the history file is $HOME/.mysql_history, where $HOME is the value of the HOME environment variable. See Appendix E.

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

You can override default options for all standard client programs with an option file. See Section 4.1.2.

The following list briefly describes the MySQL programs:

myisamchk

Utility to describe, check, optimise, and repair MySQL tables. Because myisamchk has many functions, it is described in its own chapter. See Chapter 4.

make_binary_distribution

Makes a binary release of a compiled MySQL. This could be sent by FTP to /pub/mysql/Incoming on support.mysql.com for the convenience of other MySQL users.

msql2mysql

A shell script that converts mSQL programs to MySQL. It doesn’t handle all cases, but it gives a good start when converting.

mysqlaccess

A script that checks the access privileges for a host, user, and database combination.

mysqladmin

Utility for performing administrative operations, such as creating or dropping databases, reloading the grant tables, flushing tables to disk, and reopening log files. mysqladmin can also be used to retrieve version, process, and status information from the server. See Section 4.8.3.

mysqlbug

The MySQL bug report script. This script should always be used when filing a bug report to the MySQL list.

mysqld

The SQL daemon. This should always be running.

mysqldump

Dumps a MySQL database into a file as SQL statements or as tab-separated text files. Enhanced freeware originally by Igor Romanenko. See Section 4.8.5.

mysqlimport

Imports text files into their respective tables using LOAD DATA INFILE. See Section 4.8.7.

mysqlshow

Displays information about databases, tables, columns, and indexes.

mysql_install_db

Creates the MySQL grant tables with default privileges. This is usually executed only once, when first installing MySQL on a system.

replace

A utility program that is used by msql2mysql, but that has more general applicability as well. replace changes strings in place in files or on the standard input. Uses a finite state machine to match longer strings first. Can be used to swap strings. For example, this command swaps a and b in the given files:

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

The Command-Line Tool

mysql is a simple SQL shell (with GNU readline capabilities). It supports interactive and non-interactive use. When used interactively, query results are presented in an ASCII-table format. When used non-interactively (for example, as a filter), the result is presented in tab-separated format. (The output format can be changed using command-line options.) You can run scripts simply like this:

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

If you have problems due to insufficient memory in the client, use the --quick option! This forces mysql to use mysql_use_result( ) rather than mysql_store_result( ) to retrieve the result set.

Using mysql is very easy. Just start it as follows: mysql database or mysql --user=user_name --password=your_password database. Type a SQL statement, end it with ;, \g, or \G, and press Enter.

mysql supports the following options:

-?, --help

Display this help and exit.

-A, --no-auto-rehash

No automatic rehashing. One has to use ‘rehash’ to get table and field completion. This gives a quicker start of mysql.

-B, --batch

Print results with a tab as separator, each row on a new line. Doesn’t use history file.

--character-sets-dir=...

Directory where character sets are located.

-C, --compress

Use compression in server/client protocol.

-#, --debug[=...]

Debug log. Default is ‘d:t:o,/tmp/mysql.trace’.

-D, --database=...

Database to use. This is mainly useful in the my.cnf file.

--default-character-set=...

Set the default character set.

-e, --execute=...

Execute command and quit. (Output like with --batch.)

-E, --vertical

Print the output of a query (rows) vertically. Without this option you can also force this output by ending your statements with \G.

-f, --force

Continue even in case of a SQL error.

-g, --no-named-commands

Named commands are disabled. Use \* form only, or use named commands only in the beginning of a line ending with a semicolon (;). Since Version 10.9, the client now starts with this option enabled by default! With the -g option, long-format commands will still work from the first line, however.

-G, --enable-named-commands

Named commands are enabled. Long-format commands are allowed as well as shortened \* commands.

-i, --ignore-space

Ignore space after function names.

-h, --host=...

Connect to the given host.

-H, --html

Produce HTML output.

-L, --skip-line-numbers

Don’t write line number for errors. Useful when one wants to compare result files that include error messages.

--no-pager

Disable pager and print to stdout. See interactive help (\h) also.

--no-tee

Disable outfile. See interactive help (\h) also.

-n, --unbuffered

Flush buffer after each query.

-N, --skip-column-names

Don’t write column names in results.

-O, --set-variable var=option

Give a variable a value. --help lists variables.

-o, --one-database

Only update the default database. This is useful for skipping updates to other databases in the update log.

--pager[=...]

Output type. Default is your ENV variable PAGER. Valid pagers are less, more, cat [> filename], etc. See interactive help (\h) also. This option does not work in batch mode. Pager works only in Unix.

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

Password to use when connecting to the server. If a password is not given on the command-line, you will be prompted for it. Note that if you use the short form -p you can’t have a space between the option and the password.

-P --port=...

TCP/IP port number to use for connection.

-q, --quick

Don’t cache result, print it row-by-row. This may slow down the server if the output is suspended. Doesn’t use history file.

-r, --raw

Write column values without escape conversion. Used with --batch.

-s, --silent

Be more silent.

-S --socket=...

Socket file to use for connection.

-t --table

Output in table format. This is default in non-batch mode.

-T, --debug-info

Print some debug information at exit.

--tee=...

Append everything into outfile. See interactive help (\h) also. Does not work in batch mode.

-u, --user=#

User for login if not current user.

-U, --safe-updates[=#], --i-am-a-dummy[=#]

Only allow UPDATE and DELETE that uses keys. More information about this option is provided later in this section. You can reset this option if you have it in your my.cnf file by using --safe-updates=0.

-v, --verbose

More verbose output (-v -v -v gives the table output format).

-V, --version

Output version information and exit.

-w, --wait

Wait and retry if connection is down instead of aborting.

You can also set the following variables with -O or --set-variable:

Variable Name	Default	Description
connect_timeout	0	Number of seconds before timeout connection.
max_allowed_packet	16777216	Max packet length to send/receive from to server.
net_buffer_length	16384	Buffer for TCP/IP and socket communication.
select_limit	1000	Automatic limit for SELECT when using --i-am-a-dummy.
max_join_size	1000000	Automatic limit for rows in a join when using --i-am-a-dummy.

If you type ‘help’ on the command-line, mysql will print out the commands that it supports:

mysql> help

MySQL commands:
help    (\h)    Display this text.
?       (\h)    Synonym for `help'.
clear   (\c)    Clear command.
connect (\r)    Reconnect to the server.
                Optional arguments are db and host.
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.
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.
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.
tee     (\T)    Set outfile [to_outfile].
                Append everything into given outfile.
use     (\u)    Use another database.
                Takes database name as argument.

The pager command works only in Unix.

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

A useful startup option for beginners (introduced in MySQL Version 3.23.11) is --safe-updates (or --i-am-a-dummy for users that have at some time done a DELETE FROM table_name but forgot the WHERE clause). When using this option, mysql sends the following command to the MySQL server when opening the connection:

SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=#select_limit#,
    SQL_MAX_JOIN_SIZE=#max_join_size#"

where #select_limit# and #max_join_size# are variables that can be set from the mysql command-line. See Section 5.5.6.

This results in the following:

• You are not allowed to do an UPDATE or DELETE statement if you don’t have a key constraint in the WHERE part. One can, however, force an UPDATE/DELETE by using LIMIT:

  UPDATE table_name SET not_key_column=# WHERE not_key_column=# LIMIT 1;

• All big results are automatically limited to #select_limit# rows.

• SELECT’s that will probably need to examine more than #max_join_size row combinations will be aborted.

Some useful hints about the mysql client:

Some data is much more readable when displayed vertically, instead of the usual horizontal box-type output. For example, longer text, which includes new lines, is often 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: [e-mail]
  mail_to: "Thimble Smith" <[e-mail]>
      sbj: UTF-8
      txt: >>>>> "Thimble" == Thimble Smith writes:

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

Yes, please do that.

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

• For logging, you can use the tee option. The tee can be started with option --tee=..., or from the command-line interactively with command tee. All the data displayed on the screen will also be appended into a given file. This can be very useful for debugging purposes also. The tee can be disabled from the command-line with command notee. Executing tee again starts logging again. Without a parameter the previous file will be used. Note that tee will flush the results into the file after each command, just before the command-line appears again waiting for the next command.

• Browsing, or searching the results in interactive mode in Unix less, more, or any other similar program, is now possible with option --pager[=...]. Without this argument, mysql client will look for the environment variable PAGER and set pager to that. pager can be started from the interactive command-line with command pager and disabled with command nopager. The command takes an argument optionally and the pager will be set to that. Command pager can be called without an argument, but this requires that the option --pager was used, or the pager will default to stdout. pager works only in Unix, since it uses the popen( ) function, which doesn’t exist in Windows. In Windows, the tee option can be used instead, although it may not be as handy as pager can be in some situations.

• Here are a few tips about pager: you can use it to write to a file:

  mysql> pager cat > /tmp/log.txt

  and the results will only go to a file. You can also pass any options for the programs that you want to use with the pager:

  mysql> pager less -n -i -S

  Note the option '-S’. You may find it very useful when browsing the results; try the option with horizontal output (end commands with '\g’ or ';') and with vertical output (end commands with '\G'). Sometimes a very wide result set is hard to read from the screen. With option -S set to less you can browse the results within the interactive less from left to right, preventing lines longer than your screen from being continued to the next line. This can make the result set much more readable. You can switch the mode between on and off within the interactive less with '-S’. See the ‘h’ for more help about less.

• Last (unless you already understood this from the previous examples) you can combine very complex methods to handle the results—for example, the following would send the results to two files in two different directories, on two different hard disks mounted on /dr1 and /dr2, yet let the results still be seen on the screen via less:

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

• You can also combine these two functions; have the tee enabled and set the pager to ‘less', and you will be able to browse the results in Unix ‘less’ and still have everything appended into a file the same time. The difference between Unix tee used with the pager and the mysql client built-in tee is that the built-in tee works even if you don’t have the Unix tee available. The built-in tee also logs everything that is printed on the screen, where the Unix tee used with pager doesn’t log quite that much. Last, but not least, the interactive tee is easier to switch on and off, when you want to log something into a file but want to be able to turn the feature off sometimes.

mysqladmin, Administrating a MySQL Server

This is a utility for performing administrative operations. The syntax is:

shell> mysqladmin [OPTIONS] command [command-option] command ...

You can get a list of the options your version of mysqladmin supports by executing mysqladmin --help.

The current mysqladmin supports the following commands:

create databasename

Create a new database.

drop databasename

Delete a database and all its tables.

extended-status

Gives an extended status message from the server.

flush-hosts

Flush all cached hosts.

flush-logs

Flush all logs.

flush-tables

Flush all tables.

flush-privileges

Reload grant tables (same as reload).

kill id,id,...

Kill mysql threads.

password

Set a new password. Change old password to new-password.

ping

Check if mysqld is alive.

processlist

Show list of active threads in server.

reload

Reload grant tables.

refresh

Flush all tables and close and open log files.

shutdown

Take server down.

slave-start

Start slave replication thread.

slave-stop

Stop slave replication thread.

status

Gives a short status message from the server.

variables

Prints variables available.

version

Get version info from server.

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

shell> mysqladmin proc stat
+----+-------+-----------+----+-------------+------+-------+------+
| Id | User  | Host      | db | Command     | Time | State | Info |
+----+-------+-----------+----+-------------+------+-------+------+
| 6  | monty | localhost |    | Processlist | 0    |       |      |
+----+-------+-----------+----+-------------+------+-------+------+
Uptime: 10077  Threads: 1  Questions: 9  Slow queries: 0
Opens: 6 Flush tables: 1  Open tables: 2
Memory in use: 1092K  Max memory used: 1116K

The mysqladmin status command result has the following columns:

Column	Description
Uptime	Number of seconds the MySQL server has been up.
Threads	Number of active threads (clients).
Questions	Number of questions from clients since mysqld was started.
Slow queries	Queries that have taken more than long_query_time seconds. See Section 4.9.5.
Opens	How many tables mysqld has opened.
Flush tables	Number of flush ..., refresh, and reload commands.
Open tables	Number of tables that are open now.
Memory in use	Memory allocated directly by the mysqld code (only available when MySQL is compiled with --with-debug=full).
Max memory used	Maximum memory allocated directly by the mysqld code (only available when MySQL is compiled with --with-debug=full).

If you do myslqadmin shutdown on a socket (in other words, on the computer where mysqld is running), mysqladmin will wait until the MySQL pid-file is removed to ensure that the mysqld server has stopped properly.

Using mysqlcheck for Table Maintenance and Crash Recovery

Since MySQL Version 3.23.38 you can use a new checking and repairing tool for MyISAM tables. The difference between this tool and myisamchk is that mysqlcheck should be used when the mysqld server is running, where as myisamchk should be used when it is not. The benefit is that you no longer have to take the server down for checking or repairing your tables.

mysqlcheck uses MySQL server commands CHECK, REPAIR, ANALYZE, and OPTIMIZE in a convenient way for the user.

There are three alternative ways to invoke mysqlcheck:

shell> mysqlcheck [OPTIONS] database [tables]
shell> mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
shell> mysqlcheck [OPTIONS] --all-databases

In this way, mysqlcheck can be used in a similar way as mysqldump when it comes to what databases and tables you want to choose.

mysqlcheck does have a special feature compared to the other clients; the default behavior, checking tables (-c), can be changed by renaming the binary. So if you want to have a tool that repairs tables by default, you should just copy mysqlcheck to your hard drive with a new name, mysqlrepair, or alternatively make a symbolic link to mysqlrepair and name the symbolic link mysqlrepair. If you invoke mysqlrepair now, it will repair tables by default.

The names that you can use to change mysqlcheck default behavior are as follows:

mysqlrepair:   The default option will be -r
mysqlanalyze:  The default option will be -a
mysqloptimize: The default option will be -o

The options available for mysqlcheck are listed here. Please check what your version supports with mysqlcheck --help.

-A, --all-databases

Check all the databases. This will be the same as --databases with all databases selected.

-1, --all-in-1

Instead of making one query for each table, execute all queries in 1 query separately for each database. Table names will be in a comma-separated list.

-a, --analyze

Analyse given tables.

--auto-repair

If a checked table is corrupted, automatically fix it. Repairing will be done after all tables have been checked, if corrupted ones were found.

-#, --debug=...

Output debug log. Often this is ‘d:t:o,filename’.

--character-sets-dir=...

Directory where character sets are located.

-c, --check

Check table for errors.

-C, --check-only-changed

Check only tables that have changed since last check or haven’t been closed properly.

--compress

Use compression in server/client protocol.

-?, --help

Display this help message and exit.

-B, --databases

To check several databases. Note the difference in usage; in this case no tables are given. All name arguments are regarded as database names.

--default-character-set=...

Set the default character set.

-F, --fast

Check only tables that haven’t been closed properly.

-f, --force

Continue even if we get an sql-error.

-e, --extended

If you are using this option with CHECK TABLE, it will ensure that the table is 100% consistent, but will take a long time.

If you are using this option with REPAIR TABLE, it will run an extended repair on the table, which may not only take a long time to execute, but may also produce a lot of garbage rows!

-h, --host=...

Connect to host.

-m, --medium-check

Faster than extended-check, but only finds 99.99% of all errors. Should be good enough for most cases.

-o, --optimize

Optimise tables.

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

Password to use when connecting to server. If password is not given it’s solicited on the tty.

-P, --port=...

Port number to use for connection.

-q, --quick

If you are using this option with CHECK TABLE, it prevents the check from scanning the rows to check for wrong links. This is the fastest check.

If you are using this option with REPAIR TABLE, it will try to repair only the index tree. This is the fastest repair method for a table.

-r, --repair

Can fix almost anything except unique keys that aren’t reallyunique.

-s, --silent

Print only error messages.

-S, --socket=...

Socket file to use for connection.

--tables

Overrides option --databases (-B).

-u, --user=#

User for login if not current user.

-v, --verbose

Print info about the various stages.

-V, --version

Output version information and exit.

mysqldump, Dumping Table Structure and Data

mysqldump is a utility to dump a database or a collection of databases for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump will contain SQL statements to create the table and/or populate the table.

If you are doing a backup on the server, you should consider using the mysqlhotcopy instead. See Section 4.8.6.

shell> mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

If you don’t give any tables or use the --databases or --all-databases, the whole database(s) will be dumped.

You can get a list of the options your version of mysqldump supports by executing mysqldump --help.

Note that if you run mysqldump without --quick or --opt, mysqldump will load the whole result set into memory before dumping the result. This will probably be a problem if you are dumping a big database.

Note that if you are using a new copy of the mysqldump program and you are going to do a dump that will be read into a very old MySQL server, you should not use the --opt or -e options.

mysqldump supports the following options:

--add-locks

Add LOCK TABLES before and UNLOCK TABLES after each table dump (to get faster inserts into MySQL).

--add-drop-table

Add a drop table before each create statement.

-A, --all-databases

Dump all the databases. This will be the same as --databases with all databases selected.

-a, --all

Include all MySQL-specific create options.

--allow-keywords

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

-c, --complete-insert

Use complete insert statements (with column names).

-C, --compress

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

-B, --databases

To dump several databases. Note the difference in usage. In this case no tables are given. All name arguments are regarded as database names. USE db_name; will be included in the output before each new database.

--delayed

Insert rows with the INSERT DELAYED command.

-e, --extended-insert

Use the new multi-line INSERT syntax. (Gives more compact and faster insert statements.)

-#, --debug[=option_string]

Trace usage of the program (for debugging).

--help

Display a help message and exit.

--fields-terminated-by=... , --fields-enclosed-by=... , --fields-optionally-enclosed-by=... , --fields-escaped-by=... , --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 6.4.9.

-F, --flush-logs

Flush log file in the MySQL server before starting the dump.

-f, --force,

Continue even in case of a SQL error during a table dump.

-h, --host=..

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

-l, --lock-tables

Lock all tables before starting the dump. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables.

-K, --disable-keys

/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and /*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put in the output.

-n, --no-create-db

CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; will not be put in the output. This line will be added otherwise, if --databases or --all-databases option were given.

-t, --no-create-info

Don’t write table creation information (the CREATE TABLE statement).

-d, --no-data

Don’t write any row information for the table. This is very useful if you just want to get a dump of the structure for a table!

--opt

Same as --quick --add-drop-table --add-locks --extended-insert --lock-tables. Should give you the fastest possible dump for reading into a MySQL server.

-pyour_pass, --password[=your_pass]

The password to use when connecting to the server. If you specify no =your_pass part, mysqldump you will be prompted for a password.

-P port_num, --port=port_num

The TCP/IP port number to use for connecting to a host. (This is used for connections to hosts other than localhost, for which Unix sockets are used.)

-q, --quick

Don’t buffer query, dump directly to stdout. Uses mysql_use_result( ) to do this.

-r, --result-file=...

Direct output to a given file. This option should be used in MS-DOS because it prevents new line '\n’ from being converted to '\n\r’ (new line + carriage return).

-S /path/to/socket, --socket=/path/to/socket

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

--tables

Overrides option --databases (-B).

-T, --tab=path-to-some-directory

Creates a table_name.sql file that contains the SQL CREATE commands, and a table_name.txt file that contains the data, for each give table. Note: This only works if mysqldump is run on the same machine as the mysqld daemon. The format of the .txt file is made according to the --fields-xxx and --lines--xxx options.

-u user_name, --user=user_name

The MySQL username to use when connecting to the server. The default value is your Unix login name.

-O var=option, --set-variable var=option

Set the value of a variable. The possible variables are listed below.

-v, --verbose

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

-V, --version

Print version information and exit.

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

Dump only selected records. Note that quotes are mandatory.

-X, --xml

Dumps a database as well-formed XML.

-x, --first-slave

Locks all tables across all databases.

"--where=user='jimf'" "-wuserid>1" "-wuserid<1"

-O net_buffer_length=#, where # < 16M

When creating multi-row-insert statements (as with option --extended-insert or --opt), mysqldump will create rows up to net_buffer_length length. If you increase this variable, you should also ensure that the max_allowed_packet variable in the MySQL server is bigger than the net_buffer_length.

The most normal use of mysqldump is probably for making a backup of whole databases. See Section 4.4.1.

mysqldump --opt database > backup-file.sql

You can read this back into MySQL with:

mysql database < backup-file.sql

or

mysql -e "source /patch-to-backup/backup-file.sql" database

However, it’s also very useful to populate another MySQL server with information from a database:

mysqldump --opt database | mysql ---host=remote-host -C database

It is possible to dump several databases with one command:

mysqldump --databases database1 [database2 ...] > my_databases.sql

If all the databases are wanted, one can use:

mysqldump --all-databases > all_databases.sql

mysqlhotcopy, Copying MySQL Databases and Tables

mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH TABLES, and cp or scp to quickly make a backup of a database. It’s the fastest way to make a backup of a database of single tables, but it can only be run on the same machine where the database directories are.

mysqlhotcopy db_name [/path/to/new_directory]

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

mysqlhotcopy db_name./regex/

mysqlhotcopy supports the following options:

-?, --help

Display a help screen and exit.

-u, --user=#

User for database login.

-p, --password=#

Password to use when connecting to server.

-P, --port=#

Port to use when connecting to local server.

-S, --socket=#

Socket to use when connecting to local server.

--allowold

Don’t abort if target already exists (rename it _old).

--keepold

Don’t delete previous (now renamed) target when done.

--noindices

Don’t include full index files in copy to make the backup smaller and faster. The indexes can later be reconstructed with myisamchk -rq.

--method=#

Method for copy (cp or scp).

-q, --quiet

Be silent except for errors.

--debug

Enable debug.

-n, --dryrun

Report actions without doing them.

--regexp=#

Copy all databases with names matching regexp.

--suffix=#

Suffix for names of copied databases.

--checkpoint=#

Insert checkpoint entry into specified db.table.

--flushlog

Flush logs once all tables are locked.

--tmpdir=#

Temporary directory (instead of /tmp).

You can use perldoc mysqlhotcopy to get ore complete documentation for mysqlhotcopy.

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

To be able to execute mysqlhotcopy you need write access to the backup directory, the select privilege for the tables you are about to copy, and the MySQL reload privilege (to be able to execute FLUSH TABLES).

mysqlimport, Importing Data from Text Files

mysqlimport provides a command-line interface to the LOAD DATA INFILE SQL statement. Most options to mysqlimport correspond directly to the same options to LOAD DATA INFILE. See Section 6.4.9.

mysqlimport is invoked like this:

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

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

mysqlimport supports the following options:

-c, --columns=...

This option takes a comma-separated list of field names as an argument. The field list is used to create a proper LOAD DATA INFILE command, which is then passed to MySQL. See Section 6.4.9.

-C, --compress

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

-#, --debug[=option_string]

Trace usage of the program (for debugging).

-d, --delete

Empty the table before importing the text file.

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

These options have the same meaning as the corresponding clauses for LOAD DATA INFILE. See Section 6.4.9.

-f, --force

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

--help

Display a help message and exit.

-h host_name, --host=host_name

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

-i, --ignore

See the description for the --replace option.

-l, --lock-tables

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

-L, --local

Read input files from the client. By default, text files are assumed to be on the server if you connect to localhost (which is the default host).

-pyour_pass, --password[=your_pass]

The password to use when connecting to the server. If you specify no =your_pass part, mysqlimport you will be prompted for a password.

-P port_num, --port=port_num

The TCP/IP port number to use for connecting to a host. (This is used for connections to hosts other than localhost, for which Unix sockets are used.)

-r, --replace

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

-s, --silent

Silent mode. Write output only when errors occur.

-S /path/to/socket, --socket=/path/to/socket

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

-u user_name, --user=user_name

The MySQL username to use when connecting to the server. The default value is your Unix login name.

-v, --verbose

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

-V, --version

Print version information and exit.

Here is a sample run using mysqlimport:

$ mysql --version
mysql  Ver 9.33 Distrib 3.22.25, for pc-linux-gnu (i686)
$ uname -a
Linux xxx.com 2.2.5-15 #1 Mon Apr 19 22:21:09 EDT 1999 i586 unknown
$ mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
$ ed
a
100     Max Sydow
101     Count Dracula
.
w imptest.txt
32
q
$ od -c imptest.txt
0000000   1   0   0  \t   M   a   x       S   y   d   o   w  \n   1   0
0000020   1  \t   C   o   u   n   t       D   r   a   c   u   l   a  \n
0000040
$ mysqlimport --local test imptest.txt
test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
$ mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id   | n             |
+------+---------------+
|  100 | Max Sydow     |
|  101 | Count Dracula |
+------+---------------+

Showing Databases, Tables, and Columns

mysqlshow can be used to quickly look at which databases exist, their tables, and the tables’ columns.

With the mysql program you can get the same information with the SHOW commands. See Section 4.5.6.

mysqlshow is invoked like this:

shell> mysqlshow [OPTIONS] [database [table [column]]]

• 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 only see those databases/tables/columns for which you have some privileges.

If the last argument contains a shell or SQL wildcard (*, ?, % or _), only what’s matched by the wildcard is shown. This may cause some confusion when you try to display the columns for a table with a _, as in this case mysqlshow only shows you the table names that match the pattern. This is easily fixed by adding an extra % last on the command-line (as a separate argument).

perror, Explaining Error Codes

For most system errors MySQL will, in addition to an internal text message, also print the system error code in one of the following styles: message ... (errno: #) or message ... (Errcode: #).

You can find out what the error code means by either examining the documentation for your system or using the perror utility.

perror prints a description for a system error code, or a MyISAM/ISAM table handler error code.

perror is invoked like this:

shell> perror [OPTIONS] [ERRORCODE [ERRORCODE...]]

Example:

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

Note that the error messages are mostly system-dependent!

How to Run SQL Commands from a Text File

The mysql client typically is used interactively, like this:

shell> mysql database

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

shell> mysql database < text_file

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

shell> mysql < text_file

If you are already running mysql, you can execute a SQL script file using the source command:

mysql> source filename;

For more information about batch mode, see Section 3.6.