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:
password
`MYSQL_DEBUG' Debug-trace options when debugging
`TMPDIR' The directory where temporary tables/files
are created
Use of `MYSQL_PWD' is insecure. *Note Connecting::.
On Unix, 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. *Note Environment
variables::.
If you do not want to maintain a file that contains a record of your
queries, first remove `.mysql_history' if it exists, 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_histfile' as a symbolic link to `/dev/null':
shell> ln -s /dev/null $HOME/.mysql_history
You need do this only once.
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. *Note Option files::.
The following list briefly describes the client-side MySQL programs:
`msql2mysql'
A shell script that converts `mSQL' programs to MySQL. It doesn't
handle all cases, but it gives a good start when converting.
`mysql'
The command-line tool for interactively entering queries or
executing queries from a file in batch mode. *Note `mysql': mysql.
`mysqlaccess'
A script that checks the access privileges for a host, user, and
database combination.
can also be used
to retrieve version, process, and status information from the
server. *Note `mysqladmin': mysqladmin.
`mysqlbinlog'
Utility for reading queries from a binary log. Can be used to
recover from a crash with an old backup. *Note `mysqlbinlog':
mysqlbinlog.
`mysqldump'
Dumps a MySQL database into a file as SQL statements or as
tab-separated text files. Enhanced freeware originally by Igor
Romanenko. *Note `mysqldump': mysqldump.
`mysqlimport'
Imports text files into their respective tables using `LOAD DATA
INFILE'. *Note `mysqlimport': mysqlimport.
`mysqlshow'
Displays information about databases, tables, columns, and indexes.
`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 ...
`mysql', 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 an
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.
`--prompt=...'
Set the mysql prompt to specified format.
`-b, --no-beep'
Turn off beep-on-error.
`-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 if we get an SQL error.
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.
`-X, --xml'
Produce XML output.
`-L, --skip-line-numbers'
Don't write line number for errors. Useful when one wants to
compare result files that includes 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, --one-database'
Only update the default database. This is useful for skipping
updates to other database in the binary 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 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_num, --port=port_num'
TCP/IP port number to use for connection.
``--protocol=(TCP | SOCKET | PIPE | MEMORY)''
To specify the connect protocol to use. New in MySQL 4.1.
`-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'
`--reconnect'
If the connection is lost, automatically try to reconnect to the
server (but only once).
`-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. See below for
more information about this option. 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';
please note that `--set-variable' is deprecated since MySQL 4.0, just
use `--var=option' on its own:
*Variable Name* *Default**Description*
connect_timeout 0 Number of seconds before timeout connection.
max_allowed_packet 16777216Max packetlength 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 the `mysql' client loses connection to the server while sending it a
query, it will immediately and automatically try to reconnect once to
the server and send the query again. Note that even if it succeeds in
reconnecting, as your first connection has ended, all your previous
session objects are lost: temporary tables, user and session variables.
Therefore, the above behaviour may be dangerous for you, as in this
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. To protect from this risk, you can start
the `mysql' client with the `--disable-reconnect' option.
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.
delimiter (\d) Set query 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.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager].
Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file.
Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile].
Append everything into given outfile.
use (\u) Use another database.
Takes database name as argument.
The `edit', `nopager', `pager', and `system' commands work 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 once
may have 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. *Note `SET': SET OPTION.
The effect of the above is:
* 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 be 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"
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)
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 the interactive mode in Unix less,
more, or any other similar program, is now possible with option
`--pager[=...]'. Without argument, `mysql' client will look for the
`PAGER' environment variable 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.
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
(end commands with `\G'). Sometimes a very wide result set is hard
to be read from the screen, with option `-S' 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'.
* You can combine very complex ways 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
same
time. The difference between Unix `tee' used with the `pager' and the
`mysql' client in-built `tee', is that the in-built `tee' works even if
you don't have the Unix `tee' available. The in-built `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 more handy 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.
From MySQL version 4.0.2 it is possible to change the prompt in the
`mysql' command-line client.
You can use the following prompt options:
*Option**Description*
\v mysqld version
\d database in use
\h host connected to
\p port connected on
\u username
\U full username@host
\\ `\'
\n new line break
\t tab
\ space
\_ space
\R military hour time (0-23)
\r standard hour time (1-12)
\m minutes
\y two digit year
\Y four digit year
\D full date format
\s seconds
\w day of the week in three letter
format (Mon, Tue, ...)
\P am/pm
\o month in number format
\O month in three letter format (Jan,
Feb, ...)
\c counter that counts up for each
command you do
`\' followed by any other letter just becomes that letter.
You may set the prompt in the following places:
*Environment Variable*
You may set the `MYSQL_PS1' environment variable to a prompt
string. For example:
shell> export MYSQL_PS1="(\u@\h) [\d]> "
*`my.cnf'*
*`.my.cnf'*
You may set the `prompt' option in any MySQL configuration file,
in the `mysql' group. For example:
[mysql]
prompt=(\u@\h) [\d]>\_
*Command Line*
You may set the `--prompt' option on the command line to `mysql'.
For example:
shell> mysql --prompt="(\u@\h) [\d]> "
(user@host) [database]>
*Interactively*
You may also use the `prompt' (or `\R') command to change your
prompt interactively. 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>
`mysqladmin', Administrating a MySQL Server
-------------------------------------------
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.
`reload'
Reload grant tables.
`refresh'
Flush all tables and close and open logfiles.
`shutdown'
Take down the server.
`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 information from server.
All commands can be shortened to their unique prefix. For example:
shell>
| 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:
`mysqld' was started.
Slow queries Queries that have taken more than
`long_query_time' seconds. *Note Slow query
log::.
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 Maximum memory allocated directly by the
used `mysqld' code (only available when MySQL is
compiled with -with-debug=full).
has
stopped properly.
`mysqlbinlog', Executing the queries from a binary log
------------------------------------------------------
You can examine the binary log file (*note Binary log::) with the
`mysqlbinlog' utility.
shell> mysqlbinlog hostname-bin.001
will print all queries contained in binlog `hostname-bin.001', together
with information (time the query took, ID of the thread which issued
it, timestamp when it was issued etc).
You can pipe the output of `mysqlbinlog' into a `mysql' client; this is
used to recover from a crash when you have an old backup (*note
Backup::):
shell> mysqlbinlog hostname-bin.001 | mysql
or
shell> mysqlbinlog hostname-bin.[0-9]* | mysql
into `mysql'.
`mysqlbinlog' has the `position=#' options which will print only
queries whose offset in the binlog is greater or equal to `#'.
If you have more than one binary log to execute on the MySQL server,
the safe method is to do it in one unique MySQL connection. Here is
what may be UNsafe:
shell> mysqlbinlog hostname-bin.001 | mysql # DANGER!!
shell> mysqlbinlog hostname-bin.002 | mysql # DANGER!!
temporary
table, so the second `mysql' will report "unknown table". This is why
you should run all binlogs you want in one unique connection,
especially if you use temporary tables. Here are two possible ways:
shell> mysqlbinlog hostname-bin.001 hostname-bin.002 | mysql
shell> mysqlbinlog hostname-bin.001 > /tmp/queries.sql
shell> mysqlbinlog hostname-bin.002 >> /tmp/queries.sql
shell> mysql -e "source /tmp/queries.sql"
Starting from MySQL 4.0.14, `mysqlbinlog' can prepare suitable input
for `mysql' to execute a `LOAD DATA INFILE' from a binlog. As the binlog
contains the data to load (this is true for MySQL 4.0; MySQL 3.23 did
not write the loaded data into the binlog, so the original file was
needed when one wanted to execute the content of the binlog),
`mysqlbinlog' will copy this data to a temporary file and print a `LOAD
DATA INFILE' command for `mysql' to load this temporary file. The
location where the temporary file is created is by default the
temporary directory; it can be changed with the `local-load' option of
`mysqlbinlog'.
Before MySQL 4.1, `mysqlbinlog' could not prepare suitable output for
`mysql' when the binary log contained queries from different threads
using temporary tables of the same name, if these queries were
interlaced. This is solved in MySQL 4.1.
You can also use `mysqlbinlog --read-from-remote-server' to read the
binary log directly from a remote MySQL server. However, this is
something that is deprecated as we instead want to make it easy to to
apply binary logs to a running MySQL server.
`mysqlbinlog --help' will give you more information.
Using `mysqlcheck' for Table Maintenance and Crash Recovery
-----------------------------------------------------------
Since MySQL version 3.23.38 you will be able to use a new checking and
repairing tool for `MyISAM' tables. The difference to `myisamchk' is
that `mysqlcheck' should be used when the `mysqld' server is running,
whereas `myisamchk' should be used when it is not. The benefit is that
you no longer have to take down the server 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
So it can be used in a similar way as `mysqldump' when it comes to what
databases and tables you want to choose.
tables by
default, you should just copy `mysqlcheck' to your harddrive with a new
name, `mysqlrepair', or alternatively make a symbolic link to
`mysqlrepair' and name the symbolic link as `mysqlrepair'. If you
invoke `mysqlrepair' now, it will repair tables by default.
The names that you can use to change `mysqlcheck' default behaviour are
here:
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 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
`-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 hasn'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 percent consistent, but will take a long time.
`-h, --host=...'
Connect to host.
`-m, --medium-check'
Faster than extended-check, but only finds 99.99 percent of all
errors. Should be good enough for most cases.
`-o, --optimize'
Optimise table
`-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 TCP/IP connections.
``--protocol=(TCP | SOCKET | PIPE | MEMORY)''
To specify the connect protocol to use. New in MySQL 4.1.
`-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 unique.
`-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 information about the various stages.
`-V, --version'
Output version information and exit.
`mysqldump', Dumping Table Structure and Data
---------------------------------------------
the table
and/or populate the table.
If you are doing a backup on the server, you should consider using the
`mysqlhotcopy' instead. *Note `mysqlhotcopy': mysqlhotcopy.
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'.
big
database.
`mysqldump' supports the following options:
`--add-locks'
Add `LOCK TABLES' before and `UNLOCK TABLE' after each table dump.
(To get faster inserts into MySQL.)
`--add-drop-table'
Add a `drop table' before each create statement.
`-A, --all-databases'
Dump all the databases. This will be same as `--databases' with all
databases selected.
`-a, --all'
Include all MySQL-specific create options.
`--allow-keywords'
Allow creation of column names that are keywords. This works by
prefixing each column name with the table name.
`-c, --complete-insert'
Use complete insert statements (with column names).
`-C, --compress'
Compress all information between the client and the server if both
support compression.
included in the output
before each new database.
`--delayed'
Insert rows with the `INSERT DELAYED' command.
`-e, --extended-insert'
Use the new multiline `INSERT' syntax. (Gives more compact and
faster inserts statements.)
`-#, --debug[=option_string]'
Trace usage of the program (for debugging).
`--help'
Display a help message and exit.
`--fields-terminated-by=...'
`--fields-enclosed-by=...'
`--fields-optionally-enclosed-by=...'
`--fields-escaped-by=...'
`--lines-terminated-by=...'
These options are used with the `-T' option and have the same
meaning as the corresponding clauses for `LOAD DATA INFILE'.
*Note `LOAD DATA': LOAD DATA.
`-F, --flush-logs'
Flush log file in the MySQL server before starting the dump.
`-f, --force,'
Continue even if we get an SQL error during a table dump.
`-h, --host=..'
Dump data from the MySQL server on the named host. The default host
is `localhost'.
`-l, --lock-tables.'
Lock all tables before starting the dump. The tables are locked
with `READ LOCAL' to allow concurrent inserts in the case of
`MyISAM' tables.
between databases. Tables in different databases may be dumped in
completely different states.
`-K, --disable-keys'
`/*!40000 ALTER TABLE tb_name DISABLE KEYS */;' and `/*!40000
ALTER TABLE tb_name ENABLE KEYS */;' will be put in the output.
This will make loading the data into a MySQL 4.0 server faster as
the indexes are created after all data are inserted.
`-n, --no-create-db'
`CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;' will not be
put in the output. The above line will be added otherwise, if a
`--databases' or `--all-databases' option was 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.
`-P, --port=...'
Port number to use for TCP/IP connections.
``--protocol=(TCP | SOCKET | PIPE | MEMORY)''
To specify the connect protocol to use. New in MySQL 4.1.
`-q, --quick'
Don't buffer query, dump directly to stdout. Uses
`mysql_use_result()' to do this.
`-Q, --quote-names'
Quote table and column names within ``' characters.
`-r, --result-file=...'
Direct output to a given file. This option should be used in MSDOS,
because it prevents new line `\n' from being converted to `\n\r'
(new line + carriage return).
`--single-transaction'
This option issues a `BEGIN' SQL command before dumping data from
server. It is mostly useful with `InnoDB' tables and
`READ_COMMITTED' transaction isolation level, as in this mode it
will dump the consistent state of the database at the time then
`BEGIN' was issued without blocking any applications.
When using this option you should keep in mind that only
transactional tables will be 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 was added in version 4.0.2.
This option is mutually exclusive with the `--lock-tables' option
as `LOCK TABLES' already commits a previous transaction internally.
`-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. The format of the `.txt' file is made according
to the `--fields-xxx' and `--lines--xxx' options. *Note*: This
option only works if `mysqldump' is run on the same machine as the
`mysqld' daemon. You must use a MySQL account that has the `FILE'
privilege, and the login user/group that `mysqld' is running as
(normally user `mysql', group `mysql') must have permission to
create/write a file at the location you specify.
`-u user_name, --user=user_name'
The MySQL user name to use when connecting to the server. The
default value is your Unix login name.
`-O var=option, --set-variable var=option'
Set the value of a variable. The possible variables are listed
below. Please note that `--set-variable' is deprecated since
MySQL 4.0, just use `--var=option' on its own.
`-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:
"--where=user='jimf'" "-wuserid>1" "-wuserid<1"
`-X, --xml'
Dumps a database as well formed XML
`-x, --first-slave'
Locks all tables across all databases.
`--master-data'
Like `--first-slave', but also prints some `CHANGE MASTER TO'
commands which will later make your slave start from the right
position in the master's binlogs, if you have set up your slave
using this SQL dump of the master.
`-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. *Note Backup::.
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
--------------------------------------------------
but it
can only be run on the same machine where the database directories are.
`mysqlhotcopy' works only on Unix, and it works only for `MyISAM' and
`ISAM' tables.
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 more complete documentation
for `mysqlhotcopy'.
`mysqlhotcopy' reads the groups `[client]' and `[mysqlhotcopy]' from
the option files.
`FLUSH
TABLES').
`mysqlimport', Importing Data from Text Files
---------------------------------------------
LOAD DATA.
`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. *Note `LOAD DATA':
LOAD DATA.
`-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'. *Note `LOAD DATA': LOAD DATA.
`-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.
`--ignore-lines=n'
Ignore first `n' lines of the datafile.
`-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).
`-P port_num, --port=port_num'
TCP/IP port number to use for connection.
``--protocol=(TCP | SOCKET | PIPE | MEMORY)''
To specify the connect protocol to use. New in MySQL 4.1.
existing rows that have
the same unique key value. If you specify `--ignore', input rows
that duplicate an existing row on a unique key value are skipped.
If you don't specify either option, an error occurs when a
duplicate key value is found, and the rest of the text file is
ignored.
`-s, --silent'
Silent mode. Write output only when errors occur.
`-S /path/to/socket, --socket=/path/to/socket'
The socket file to use when connecting to `localhost' (which is the
default host).
`-u user_name, --user=user_name'
The MySQL user name to use when connecting to the server. The
default value is your Unix login name.
`-v, --verbose'
Verbose mode. Print out more information what the program does.
`-V, --version'
Print version information and exit.
Here is a sample run using `mysqlimport':
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 |
+------+---------------+
`mysqlshow', Showing Databases, Tables, and Columns
---------------------------------------------------
`mysqlshow' can be used to quickly look at which databases exist, their
tables, and the table's columns.
With the `mysql' program you can get the same information with the
`SHOW' commands. *Note SHOW::.
`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
database/tables/columns for which you have some privileges.
(some
Unix shells will require two), in order to get tables / columns
properly. '*' are converted into SQL '%' wildcard and '?' into SQL '_'
wildcard. 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).
`mysql_config', Get compile options for compiling clients
---------------------------------------------------------
`mysql_config' provides you with useful information how to compile your
MySQL client and connect it to MySQL.
`mysql_config' supports the following options:
`--cflags'
Compiler flags to find include files and critical ccompiler flags
and defines used when compiling the `libmysqlclient' library.
`--include'
Compiler options to find MySQL include files. (Normally one would
use `--cflags' instead of this)
`--libs'
Libraries and options required to link with the MySQL client
library.
`--libs_r'
Libraries and options required to link with the thread-safe MySQL
client library.
`--socket'
The default socket name, defined when configuring MySQL.
`--port'
The default port number, defined when configuring MySQL.
`--version'
Version number and version for the MySQL distribution.
`--libmysqld-libs or --embedded'
Libraries and options required to link with the MySQL embedded
server.
If you execute `mysql_config' without any options it will print all
options it supports plus the value of all options:
shell> mysql_config
Usage: /usr/local/mysql/bin/mysql_config [OPTIONS]
Options:
--cflags [-I/usr/local/mysql/include/mysql -mcpu=pentiumpro]
--include [-I/usr/local/mysql/include/mysql]
--libs [-L/usr/local/mysql/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib -lssl -lcrypto]
--libs_r [-L/usr/local/mysql/lib/mysql -lmysqlclient_r -lpthread -lz -lcrypt -lnsl -lm -lpthread]
--socket [/tmp/mysql.sock]
--port [3306]
--version [4.0.16]
--libmysqld-libs [-L/usr/local/mysql/lib/mysql -lmysqld -lpthread -lz -lcrypt -lnsl -lm -lpthread -lrt]
You can use this to compile a MySQL client by as follows:
CFG=/usr/local/mysql/bin/mysql_config
sh -c "gcc -o progname `$CFG --cflags` progname.c `$CFG --libs`"
`perror', Explaining Error Codes
--------------------------------
For most system errors MySQL will, in addition to a 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 use the `perror' utility.
`perror' prints a description for a system error code, or an MyISAM/ISAM
storage engine (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
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 an SQL script file
using the `source' command:
mysql> source filename;
For more information about batch mode, *Note Batch mode::.
[Назад] [Содержание] [Вперед]
| Главная |