Table of Contents
This chapter provides a brief overview of the command-line programs provided by MySQL AB. It also discusses the general syntax for specifying options when you run these programs. Most programs have options that are specific to their own operation, but the option syntax is similar for all of them. Finally, the chapter provides more detailed descriptions of individual programs, including which options they recognize.
There are many different programs in a MySQL installation. This section provides a brief overview of them. Later sections provide a more detailed description of each one, with the exception of MySQL Cluster programs. Each program's description indicates its invocation syntax and the options that it understands. Chapter 16, MySQL Cluster, describes programs specific to MySQL Cluster.
Most MySQL distributions include all of these programs, except for those programs that are platform-specific. (For example, the server startup scripts are not used on Windows.) The exception is that RPM distributions are more specialized. There is one RPM for the server, another for client programs, and so forth. If you appear to be missing one or more programs, see Chapter 2, Installing and Upgrading MySQL, for information on types of distributions and what they contain. It may be that you have a distribution that does not include all programs and you need to install an additional package.
Each MySQL program takes many different options. Most programs
provide a --help
option that you can use to get a
description of the program's different options. For example, try
mysql --help.
You can override default option values for MySQL programs by specifying options on the command line or in an option file. See Section 4.2, “Using MySQL Programs”, for general information on invoking programs and specifying program options.
The MySQL server, mysqld, is the main program that does most of the work in a MySQL installation. The server is accompanied by several related scripts that assist you in starting and stopping the server:
The SQL daemon (that is, the MySQL server). To use client programs, mysqld must be running, because clients gain access to databases by connecting to the server. See Section 5.1, “The MySQL Server”.
A server startup script. mysqld_safe attempts to start mysqld. See Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.
A server startup script. This script is used on systems that use System V-style run directories containing scripts that start system services for particular run levels. It invokes mysqld_safe to start the MySQL server. See Section 4.3.3, “mysql.server — MySQL Server Startup Script”.
A server startup script that can start or stop multiple
servers installed on the system. See
Section 4.3.4, “mysqld_multi — Manage Multiple MySQL Servers”. As of MySQL 5.0.3 (Unix-like
systems) or 5.0.13 (Windows), an alternative to
mysqld_multi is
mysqlmanager
, the MySQL Instance Manager.
See Section 4.6.9, “mysqlmanager — The MySQL Instance Manager”.
There are several programs that perform setup operations during MySQL installation or upgrading:
This program is used during the MySQL build/installation process. It compiles error message files from the error source files. See Section 4.4.1, “comp_err — Compile MySQL Error Message File”.
This program makes a binary release of a compiled MySQL. This
could be sent by FTP to
/pub/mysql/upload/
on
ftp.mysql.com
for the convenience of other
MySQL users.
This program is used on Windows. It packages a MySQL distribution for installation after the source distribution has been built. See Section 4.4.2, “make_win_bin_dist — Package MySQL Distribution as ZIP Archive”.
This program is used after a MySQL upgrade operation. It updates the grant tables with any changes that have been made in newer versions of MySQL. See Section 4.4.5, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”.
Note: As of MySQL 5.0.19, this program has been superseded by mysql_upgrade.
This script creates the MySQL database and initializes the grant tables with default privileges. It is usually executed only once, when first installing MySQL on a system. See Section 2.4.16.2, “Unix Post-Installation Procedures”, and Section 4.4.6, “mysql_install_db — Initialize MySQL Data Directory”.
This program enables you to improve the security of your MySQL installation. SQL. See Section 4.4.7, “mysql_secure_installation — Improve MySQL Installation Security”.
This program loads the time zone tables in the
mysql
database using the contents of the
host system zoneinfo database (the set
of files describing time zones). SQL. See
Section 4.4.8, “mysql_tzinfo_to_sql — Load the Time Zone Tables”.
This program is used after a MySQL upgrade operation. It checks tables for incompatibilities and repairs them if necessary, and updates the grant tables with any changes that have been made in newer versions of MySQL. See Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.
This program is used on Unix or Unix-like systems to create a MySQL source distribution that can be compiled on Windows. See Section 2.4.15.6.5, “Creating a Windows Source Package from the BitKeeper Repository”, and Section 4.4.3, “make_win_src_distribution — Create Source Distribution for Windows”.
MySQL client programs:
The command-line tool for interactively entering SQL statements or executing them from a file in batch mode. See Section 4.5.1, “mysql — The MySQL Command-Line Tool”.
A client that performs administrative operations, such as creating or dropping databases, reloading the grant tables, flushing tables to disk, and reopening log files. mysqladmin can also be used to retrieve version, process, and status information from the server. See Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”.
A table-maintenance client that checks, repairs, analyzes, and optimizes tables. See Section 4.5.3, “mysqlcheck — A Table Maintenance and Repair Program”.
A client that dumps a MySQL database into a file as SQL, text, or XML. See Section 4.5.4, “mysqldump — A Database Backup Program”.
A client that imports text files into their respective tables
using LOAD DATA INFILE
. See
Section 4.5.5, “mysqlimport — A Data Import Program”.
A client that displays information about databases, tables, columns, and indexes. See Section 4.5.6, “mysqlshow — Display Database, Table, and Column Information”.
MySQL administrative and utility programs:
An offline InnoDB
offline file checksum
utility. See Section 4.6.1, “innochecksum — Offline InnoDB File Checksum Utility”.
A utility that displays information about full-text indexes in
MyISAM
tables. See
Section 4.6.2, “myisam_ftdump — Display Full-Text Index information”.
A utility to describe, check, optimize, and repair
MyISAM
tables. See
Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
A utility that processes the contents of a
MyISAM
log file. See
Section 4.6.4, “myisamlog — Display MyISAM Log File Contents”.
A utility that compresses MyISAM
tables to
produce smaller read-only tables. See
Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
A script that checks the access privileges for a hostname, username, and database combination. See Section 4.6.6, “mysqlaccess — Client for Checking Access Privileges”.
A utility for reading statements from a binary log. The log of executed statements contained in the binary log files can be used to help recover from a crash. See Section 4.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”.
A utility that quickly makes backups of
MyISAM
tables while the server is running.
See Section 4.6.8, “mysqlhotcopy — A Database Backup Program”.
The MySQL Instance Manager, a program for monitoring and managing MySQL servers. See Section 4.6.9, “mysqlmanager — The MySQL Instance Manager”.
A utility that converts tables in a database to use a given storage engine. See Section 4.6.12, “mysql_convert_table_format — Convert Tables to Use a Given Storage Engine”.
A utility that analyzes queries in the MySQL query log using
EXPLAIN
See
Section 4.6.13, “mysql_explain_log — Use EXPLAIN on Statements in Query Log”.
A utility that reads files containing SQL statements (such as update logs) and extracts statements that match a given regular expression. See Section 4.6.14, “mysql_find_rows — Extract SQL Statements from Files”.
A utility that converts the extensions for
MyISAM
table files to lowercase. This can
be useful after transferring the files from a system with
case-insensitive filenames to a system with case-sensitive
filenames. See Section 4.6.15, “mysql_fix_extensions — Normalize Table Filename Extensions”.
A utility for interactively setting permissions in the MySQL grant tables. See Section 4.6.16, “mysql_setpermission — Interactively Set Permissions in Grant Tables”.
A utility that generates database metadata. Section 4.6.17, “mysql_tableinfo — Generate Database Metadata”.
A utility that kills the process with a given process ID. See Section 4.6.18, “mysql_waitpid — Kill Process and Wait for Its Termination”.
A utility that kills processes that match a pattern. See Section 4.6.19, “mysql_zap — Kill Processes That Match a Pattern”.
MySQL program-development utilities:
msql2mysql
A shell script that converts mSQL
programs
to MySQL. It doesn't handle every case, but it gives a good
start when converting.
mysql_config
A shell script that produces the option values needed when compiling MySQL programs.
A utility that shows which options are present in option groups of option files. See Section 4.7.3, “my_print_defaults — Display Options from Option Files”.
A utility program that resolves a numeric stack trace dump to symbols. See Section 4.7.4, “resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols”.
Miscellaneous utilities:
A utility that displays the meaning of system or MySQL error codes. See Section 4.8.1, “perror — Explain Error Codes”.
A utility program that performs string replacement in the input text. See Section 4.8.2, “replace — A String-Replacement Utility”.
A utility program that resolves a hostname to an IP address or vice versa. See Section 4.8.3, “resolveip — Resolve Hostname to IP Address or Vice Versa”.
MySQL AB also provides several GUI tools for administering and otherwise working with MySQL Server:
MySQL Administrator: This tool is used for administering MySQL servers, databases, tables, and user accounts.
MySQL Query Browser: This graphical tool is provided by MySQL AB for creating, executing, and optimizing queries on MySQL databases.
MySQL Migration Toolkit: This tool helps you migrate schemas and data from other relational database management systems for use with MySQL.
These GUI programs are available at http://dev.mysql.com/downloads/. Each has its own manual that you can access at http://dev.mysql.com/doc/.
MySQL client programs that communicate with the server using the MySQL client/server library use the following environment variables:
MYSQL_UNIX_PORT | The default Unix socket file; used for connections to
localhost |
MYSQL_TCP_PORT | The default port number; used for TCP/IP connections |
MYSQL_PWD | The default password |
MYSQL_DEBUG | Debug trace options when debugging |
TMPDIR | The directory where temporary tables and files are created |
For a full list of environment variables used by MySQL programs, see Section 2.4.20, “Environment Variables”.
Use of MYSQL_PWD
is insecure. See
Section 5.5.6, “Keeping Your Password Secure”.
To invoke a MySQL program from the command line (that is, from
your shell or command prompt), enter the program name followed by
any options or other arguments needed to instruct the program what
you want it to do. The following commands show some sample program
invocations. “shell>
”
represents the prompt for your command interpreter; it is not part
of what you type. The particular prompt you see depends on your
command interpreter. Typical prompts are $
for
sh or bash,
%
for csh or
tcsh, and C:\>
for the
Windows command.com or
cmd.exe command interpreters.
shell>mysql --user=root test
shell>mysqladmin extended-status variables
shell>mysqlshow --help
shell>mysqldump -u root personnel
Arguments that begin with a single or double dash
(“-
”,
“--
”) are option arguments.
Options typically specify the type of connection a program should
make to the server or affect its operational mode. Option syntax
is described in Section 4.2.2, “Specifying Program Options”.
Non-option arguments (arguments with no leading dash) provide
additional information to the program. For example, the
mysql program interprets the first non-option
argument as a database name, so the command mysql
--user=root test
indicates that you want to use the
test
database.
Later sections that describe individual programs indicate which options a program understands and describe the meaning of any additional non-option arguments.
Some options are common to a number of programs. The most common
of these are the --host
(or -h
),
--user
(or -u
), and
--password
(or -p
) options that
specify connection parameters. They indicate the host where the
MySQL server is running, and the username and password of your
MySQL account. All MySQL client programs understand these options;
they allow you to specify which server to connect to and the
account to use on that server.
Other connection options are --port
(or
-P
) to specify a TCP/IP port number and
--socket
(or -S
) to specify a
Unix socket file on Unix (or named pipe name on Windows).
The default hostname is localhost
. For client
programs on Unix, the hostname localhost
is
special. It causes the client to connect to the MySQL server
through a Unix socket file. This occurs even if a
--port
or -P
option is given to
specify a port number. To ensure that the client makes a TCP/IP
connection to the local server, use --host
or
-h
to specify a hostname value of
127.0.0.1
, or the IP address or name of the
local server. You can also specify the connection protocol
explicitly, even for localhost
, by using the
--protocol=tcp
option.
On Windows, the hostname .
causes the client to
connect to the local server using a named pipe, if the server has
named-pipe connections enabled. If named-pipe connections are not
enabled, an error occurs.
You may find it necessary to invoke MySQL programs using the
pathname to the bin
directory in which they
are installed. This is likely to be the case if you get a
“program not found” error whenever you attempt to run
a MySQL program from any directory other than the
bin
directory. To make it more convenient to
use MySQL, you can add the pathname of the
bin
directory to your PATH
environment variable setting. That enables you to run a program by
typing only its name, not its entire pathname. For example, if
mysql is installed in
/usr/local/mysql/bin
, you'll be able to run
it by invoking it as mysql; it will not be
necessary to invoke it as
/usr/local/mysql/bin/mysql.
Consult the documentation for your command interpreter for
instructions on setting your PATH
variable. The
syntax for setting environment variables is interpreter-specific.
(Some information is given in
Section 4.2.3, “Setting Environment Variables”.) After modifying
your PATH
setting, open a new console window on
Windows or log in again on Unix so that the setting goes into
effect.
There are several ways to specify options for MySQL programs:
List the options on the command line following the program name. This is most common for options that apply to a specific invocation of the program.
List the options in an option file that the program reads when it starts. This is common for options that you want the program to use each time it runs.
List the options in environment variables (see Section 4.2.3, “Setting Environment Variables”). This method is useful for options that you want to apply each time the program runs. In practice, option files are used more commonly for this purpose, but Section 5.6.2, “Running Multiple Servers on Unix”, discusses one situation in which environment variables can be very helpful. It describes a handy technique that uses such variables to specify the TCP/IP port number and Unix socket file for the server and for client programs.
MySQL programs determine which options are given first by examining environment variables, then by reading option files, and then by checking the command line. This means that environment variables have the lowest precedence and command-line options the highest.
Because options are processed in order, if an option is specified
multiple times, the last occurrence takes precedence. The
following command causes mysql to connect to
the server running on localhost
:
shell> mysql -h example.com -h localhost
If conflicting or related options are given, later options take precedence over earlier options. The following command runs mysql in “no column names” mode:
shell> mysql --column-names --skip-column-names
An option can be specified by writing it in full or as any
unambiguous prefix. For example, the --compress
option can be given to mysqldump as
--compr
, but not as --comp
because the latter is ambiguous:
shell> mysqldump --comp
mysqldump: ambiguous option '--comp' (compatible, compress)
Be aware that the use of option prefixes can cause problems in the event that new options are implemented for a program. A prefix that is unambigious now might become ambiguous in the future.
You can take advantage of the way that MySQL programs process options by specifying default values for a program's options in an option file. That enables you to avoid typing them each time you run the program, but also allows you to override the defaults if necessary by using command-line options.
Program options specified on the command line follow these rules:
Options are given after the command name.
An option argument begins with one dash or two dashes,
depending on whether it is a short form or long form of the
option name. Many options have both short and long forms.
For example, -?
and --help
are the short and long forms of the option that instructs a
MySQL program to display its help message.
Option names are case sensitive. -v
and
-V
are both legal and have different
meanings. (They are the corresponding short forms of the
--verbose
and --version
options.)
Some options take a value following the option name. For
example, -h localhost
or
--host=localhost
indicate the MySQL server
host to a client program. The option value tells the program
the name of the host where the MySQL server is running.
For a long option that takes a value, separate the option
name and the value by an “=
”
sign. For a short option that takes a value, the option
value can immediately follow the option letter, or there can
be a space between: -hlocalhost
and
-h localhost
are equivalent. An exception
to this rule is the option for specifying your MySQL
password. This option can be given in long form as
--password=
or as pass_val
--password
. In the latter case (with
no password value given), the program prompts you for the
password. The password option also may be given in short
form as
-p
or as
pass_val
-p
. However, for the short form, if the
password value is given, it must follow the option letter
with no intervening space. The reason
for this is that if a space follows the option letter, the
program has no way to tell whether a following argument is
supposed to be the password value or some other kind of
argument. Consequently, the following two commands have two
completely different meanings:
shell>mysql -ptest
shell>mysql -p test
The first command instructs mysql to use
a password value of test
, but specifies
no default database. The second instructs
mysql to prompt for the password value
and to use test
as the default database.
Another option that may occasionally be useful with
mysql is the --execute
or
-e
option, which can be used to pass SQL
statements to the server. When this option is used,
mysql executes the statements and exits. The
statements must be enclosed by quotation marks. For example, you
can use the following command to obtain a list of user accounts:
shell>mysql -u root -p --execute="SELECT User, Host FROM user" mysql
Enter password:******
+------+-----------+ | User | Host | +------+-----------+ | | gigan | | root | gigan | | | localhost | | jon | localhost | | root | localhost | +------+-----------+ shell>
Note that the long form (--execute
) is followed
by an equals sign (=
).
If you wish to use quoted values within a statement, you will either need to escape the inner quotes, or use a different type of quotes within the statement from those used to quote the statement itself. The capabilities of your command processor dictate your choices for whether you can use single or double quotation marks and the syntax for escaping quote characters. For example, if your command processor supports quoting with single or double quotes, you can double quotes around the statement, and single quotes for any quoted values within the statement.
In the preceding example, the name of the
mysql
database was passed as a separate
argument. However, the same statement could have been executed
using this command, which specifies no default database:
mysql> mysql -u root -p --execute="SELECT User, Host FROM mysql.user"
Multiple SQL statements may be passed on the command line, separated by semicolons:
shell>mysql -u root -p -e "SELECT VERSION();SELECT NOW()"
Enter password:******
+------------+ | VERSION() | +------------+ | 5.0.19-log | +------------+ +---------------------+ | NOW() | +---------------------+ | 2006-01-05 21:19:04 | +---------------------+
The --execute
or -e
option may
also be used to pass commands in an analogous fashion to the
ndb_mgm management client for MySQL Cluster.
See Section 16.2.6, “Safe Shutdown and Restart”, for
an example.
Some options control behavior that can be turned on or off.
For example, the mysql client supports a
--column-names
option that determines whether
or not to display a row of column names at the beginning of
query results. By default, this option is enabled. However,
you may want to disable it in some instances, such as when
sending the output of mysql into another
program that expects to see only data and not an initial
header line.
To disable column names, you can specify the option using any of these forms:
--disable-column-names --skip-column-names --column-names=0
The --disable
and --skip
prefixes and the =0
suffix all have the
same effect: They turn the option off.
The “enabled” form of the option may be specified in any of these ways:
--column-names --enable-column-names --column-names=1
If an option is prefixed by --loose
, a
program does not exit with an error if it does not recognize
the option, but instead issues only a warning:
shell> mysql --loose-no-such-option
mysql: WARNING: unknown option '--no-such-option'
The --loose
prefix can be useful when you run
programs from multiple installations of MySQL on the same
machine and list options in an option file, An option that may
not be recognized by all versions of a program can be given
using the --loose
prefix (or
loose
in an option file). Versions of the
program that recognize the option process it normally, and
versions that do not recognize it issue a warning and ignore
it.
mysqld enables a limit to be placed on how
large client programs can set dynamic system variables. To do
this, use a --maximum
prefix with the
variable name. For example,
--maximum-query_cache_size=4M
prevents any
client from making the query cache size larger than 4MB.
Most MySQL programs can read startup options from option files (also sometimes called configuration files). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program. For the MySQL server, MySQL provides a number of preconfigured option files.
To determine whether a program reads option files, invoke it
with the --help
option. (For
mysqld, use --verbose
and
--help
.) If the program reads option files, the
help message indicates which files it looks for and which option
groups it recognizes.
Option files used with MySQL Cluster programs are covered in Section 16.3, “MySQL Cluster Configuration”.
On Windows, MySQL programs read startup options from the following files:
Filename | Purpose |
,
| Global options |
C:\my.ini , C:\my.cnf | Global options |
,
| Global options |
defaults-extra-file | The file specified with
--defaults-extra-file= ,
if any |
WINDIR
represents the location of
your Windows directory. This is commonly
C:\WINDOWS
. You can determine its exact
location from the value of the WINDIR
environment variable using the following command:
C:\> echo %WINDIR%
INSTALLDIR
represents the MySQL
installation directory. This is typically
C:\
where
PROGRAMDIR
\MySQL\MySQL
5.0 ServerPROGRAMDIR
represents the programs
directory (usually Program Files
on
English-language versions of Windows), when MySQL
5.0 has been installed using the installation and
configuration wizards. See
Section 2.4.8.4.1.1, “The MySQL Server Configuration Wizard on Windows”.
On Unix, MySQL programs read startup options from the following files:
Filename | Purpose |
/etc/my.cnf | Global options |
| Global options |
$MYSQL_HOME/my.cnf | Server-specific options |
defaults-extra-file | The file specified with
--defaults-extra-file= ,
if any |
~/.my.cnf | User-specific options |
SYSCONFDIR
represents the directory
specified with the --sysconfdir
option to
configure when MySQL was built. By default,
this is the etc
directory located under the
compiled-in installation directory. This location is used as of
MySQL 5.0.21. (From 5.0.21 to 5.0.53, it was read last, after
~/.my.cnf
.)
MYSQL_HOME
is an environment variable
containing the path to the directory in which the
server-specific my.cnf
file resides. (This
was DATADIR
prior to MySQL version
5.0.3.)
If MYSQL_HOME
is not set and you start the
server using the mysqld_safe program,
mysqld_safe attempts to set
MYSQL_HOME
as follows:
Let BASEDIR
and
DATADIR
represent the pathnames
of the MySQL base directory and data directory,
respectively.
If there is a my.cnf
file in
DATADIR
but not in
BASEDIR
,
mysqld_safe sets
MYSQL_HOME
to
DATADIR
.
Otherwise, if MYSQL_HOME
is not set and
there is no my.cnf
file in
DATADIR
,
mysqld_safe sets
MYSQL_HOME
to
BASEDIR
.
In MySQL 5.0, use of
DATADIR
as the location for
my.cnf
is deprecated.
Typically, DATADIR
is
/usr/local/mysql/data
for a binary
installation or /usr/local/var
for a source
installation. Note that this is the data directory location that
was specified at configuration time, not the one specified with
the --datadir
option when
mysqld starts. Use of
--datadir
at runtime has no effect on where the
server looks for option files, because it looks for them before
processing any options.
MySQL looks for option files in the order just described and reads any that exist. If an option file that you want to use does not exist, create it with a plain text editor.
If multiple instances of a given option are found, the last
instance takes precedence. There is one exception: For
mysqld, the first
instance of the --user
option is used as a
security precaution, to prevent a user specified in an option
file from being overridden on the command line.
On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional as a security measure.
Any long option that may be given on the command line when
running a MySQL program can be given in an option file as well.
To get the list of available options for a program, run it with
the --help
option.
The syntax for specifying options in an option file is similar
to command-line syntax, except that you omit the leading two
dashes. For example, --quick
or
--host=localhost
on the command line should be
specified as quick
or
host=localhost
in an option file. To specify
an option of the form
--loose-
in
an option file, write it as
opt_name
loose-
.
opt_name
Empty lines in option files are ignored. Non-empty lines can take any of the following forms:
#
,
comment
;
comment
Comment lines start with “#
”
or “;
”. A
“#
” comment can start in the
middle of a line as well.
[
group
]
group
is the name of the program
or group for which you want to set options. After a group
line, any option-setting lines apply to the named group
until the end of the option file or another group line is
given.
opt_name
This is equivalent to
--
on
the command line.
opt_name
opt_name
=value
This is equivalent to
--
on the command line. In an option file, you can have spaces
around the “opt_name
=value
=
” character,
something that is not true on the command line. You can
enclose the value within single quotes or double quotes,
which is useful if the value contains a
“#
” comment character or
whitespace.
For options that take a numeric value, the value can be given
with a suffix of K
, M
, or
G
(either uppercase or lowercase) to indicate
a multiplier of 1024, 10242 or
10243. For example, the following
command tells mysqladmin to ping the server
1024 times, sleeping 10 seconds between each ping:
mysql> mysqladmin --count=1K --sleep=10 ping
Leading and trailing blanks are automatically deleted from
option names and values. You may use the escape sequences
“\b
”,
“\t
”,
“\n
”,
“\r
”,
“\\
”, and
“\s
” in option values to
represent the backspace, tab, newline, carriage return,
backslash, and space characters.
Because the “\\
” escape sequence
represents a single backslash, you must write each
“\
” as
“\\
”. Alternatively, you can
specify the value using “/
”
rather than “\
” as the pathname
separator.
If an option group name is the same as a program name, options
in the group apply specifically to that program. For example,
the [mysqld]
and [mysql]
groups apply to the mysqld server and the
mysql client program, respectively.
The [client]
option group is read by all
client programs (but not by
mysqld). This allows you to specify options
that apply to all clients. For example,
[client]
is the perfect group to use to
specify the password that you use to connect to the server. (But
make sure that the option file is readable and writable only by
yourself, so that other people cannot find out your password.)
Be sure not to put an option in the [client]
group unless it is recognized by all client
programs that you use. Programs that do not understand the
option quit after displaying an error message if you try to run
them.
Here is a typical global option file:
[client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock key_buffer_size=16M max_allowed_packet=8M [mysqldump] quick
The preceding option file uses
syntax for the lines that set the
var_name
=value
key_buffer_size
and
max_allowed_packet
variables.
Here is a typical user option file:
[client] # The following password will be sent to all standard MySQL clients password="my_password" [mysql] no-auto-rehash connect_timeout=2 [mysqlhotcopy] interactive-timeout
If you want to create option groups that should be read by
mysqld servers from a specific MySQL release
series only, you can do this by using groups with names of
[mysqld-4.1]
,
[mysqld-5.0]
, and so forth. The
following group indicates that the --new
option
should be used only by MySQL servers with 5.0.x
version numbers:
[mysqld-5.0] new
Beginning with MySQL 5.0.4, it is possible to use
!include
directives in option files to
include other option files and !includedir
to
search specific directories for option files. For example, to
include the /home/mydir/myopt.cnf
file, use
the following directive:
!include /home/mydir/myopt.cnf
To search the /home/mydir
directory and
read option files found there, use this directive:
!includedir /home/mydir
There is no guarantee about the order in which the option files in the directory will be read.
Currently, any files to be found and included using the
!includedir
directive on Unix operating
systems must have filenames ending in
.cnf
. On Windows, this directive checks
for files with the .ini
or
.cnf
extension.
Write the contents of an included option file like any other
option file. That is, it should contain groups of options, each
preceded by a
[
line that
indicates the program to which the options apply.
group
]
While an included file is being processed, only those options in
groups that the current program is looking for are used. Other
groups are ignored. Suppose that a my.cnf
file contains this line:
!include /home/mydir/myopt.cnf
And suppose that /home/mydir/myopt.cnf
looks like this:
[mysqladmin] force [mysqld] key_buffer_size=16M
If my.cnf
is processed by
mysqld, only the [mysqld]
group in /home/mydir/myopt.cnf
is used. If
the file is processed by mysqladmin, only the
[mysqldamin]
group is used. If the file is
processed by any other program, no options in
/home/mydir/myopt.cnf
are used.
The !includedir
directive is processed
similarly except that all option files in the named directory
are read.
Most MySQL programs that support option files handle the
following options. They affect option-file handling, so they
must be given on the command line and not in an option file.
To work properly, each of these options must immediately
follow the command name, with the exception that
--print-defaults
may be used immediately
after --defaults-file
or
--defaults-extra-file
. Also, when specifying
filenames, you should avoid the use of the
“~
” shell metacharacter
because it might not be interpreted as you expect.
Don't read any option files.
Print the program name and all options that it gets from option files.
Use only the given option file.
file_name
is the full pathname
to the file. If the file does not exist or is otherwise
inaccessible, the program will exit with an error.
--defaults-extra-file=
file_name
Read this option file after the global option file but (on
Unix) before the user option file.
file_name
is the full pathname
to the file. As of MySQL 5.0.6, if the file does not exist
or is otherwise inaccessible, the program will exit with
an error.
If this option is given, the program reads not only its
usual option groups, but also groups with the usual names
and a suffix of str
. For
example, the mysql client normally
reads the [client]
and
[mysql]
groups. If the
--default-group-suffix=_other
option is
given, mysql also reads the
[client_other]
and
[mysql_other]
groups. This option was
added in MySQL 5.0.10.
MySQL provides a number of preconfigured option files that can
be used as a basis for tuning the MySQL server. Look for files
such as my-small.cnf
,
my-medium.cnf
,
my-large.cnf
, and
my-huge.cnf
, which are sample option
files for small, medium, large, and very large systems. On
Windows, the extension is .ini
rather
than .cnf
extension.
On Windows, the .cnf
or
.ini
option file extension might not be
displayed.
For a binary distribution, look for the files in or under your
installation directory. If you have a source distribution,
look in the support-files
directory. You
can rename a copy of a sample file and place it in the
appropriate location for use as a base configuration file.
Regarding names and appropriate location, see the general
information provided in Section 4.2.2.2, “Using Option Files”.
Many MySQL programs have internal variables that can be set at
runtime. Program variables are set the same way as any other
long option that takes a value. For example,
mysql has a
max_allowed_packet
variable that controls the
maximum size of its communication buffer. To set the
max_allowed_packet
variable for
mysql to a value of 16MB, use either of the
following commands:
shell>mysql --max_allowed_packet=16777216
shell>mysql --max_allowed_packet=16M
The first command specifies the value in bytes. The second
specifies the value in megabytes. For variables that take a
numeric value, the value can be given with a suffix of
K
, M
, or
G
(either uppercase or lowercase) to indicate
a multiplier of 1024, 10242 or
10243. (For example, when used to set
max_allowed_packet
, the suffixes indicate
units of kilobytes, megabytes, or gigabytes.)
In an option file, variable settings are given without the leading dashes:
[mysql] max_allowed_packet=16777216
Or:
[mysql] max_allowed_packet=16M
If you like, underscores in a variable name can be specified as dashes. The following option groups are equivalent. Both set the size of the server's key buffer to 512MB:
[mysqld] key_buffer_size=512M [mysqld] key-buffer-size=512M
A variable can be specified by writing it in full or as any
unambiguous prefix. For example, the
max_buffer_length
variable can be set for
mysql as --max_a
, but not as
--max
because the latter is ambiguous:
shell> mysql --max=1000000
mysql: ambiguous option '--max=1000000' (max_allowed_packet, max_join_size)
Be aware that the use of variable prefixes can cause problems in the event that new variables are implemented for a program. A prefix that is unambigious now might become ambiguous in the future.
Before MySQL 4.0.2, the only syntax for setting program
variables was
--set-variable=
(or
option
=value
set-variable=
in option files). Underscores cannot be given as dashes, and
the variable name must be specified in full. This syntax still
is recognized, but is now deprecated.
option
=value
Many server system variables can also be set at runtime. For details, see Section 5.1.4.2, “Dynamic System Variables”.
By convention, long forms of options that assign a value are
written with an equals (=
) sign, like this:
shell> mysql --host=tonfisk --user=jon
For options that require a value (that is, not having a default value), the equals sign is not required, and so the following is also valid:
shell> mysql --host tonfisk --user jon
In both cases, the mysql client attempts to connect to a MySQL server running on the host named “tonfisk” using an account with the username “jon”.
Due to this behavior, problems can occasionally arise when no
value is provided for an option that expects one. Consider the
following example, where a user connects to a MySQL server
running on host tonfisk
as user
jon
:
shell>mysql --host 85.224.35.45 --user jon
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.56 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>SELECT CURRENT_USER();
+----------------+ | CURRENT_USER() | +----------------+ | jon@% | +----------------+ 1 row in set (0.00 sec)
Omitting the required value for one of these option yields an error, such as the one shown here:
shell> mysql --host 85.224.35.45 --user
mysql: option '--user' requires an argument
In this case, mysql was unable to find a value following the
--user
option because nothing came after it on
the command line. However, if you omit the value for an option
that is not the last option to be used, you
obtain a different error that you may not be expecting:
shell> mysql --host --user jon
ERROR 2005 (HY000): Unknown MySQL server host '--user' (1)
Because mysql assumes that any string
following --host
on the command line is a
hostname, --host --user
is interpreted as
--host=--user
, and the client attempts to
connect to a MySQL server running on a host named
“--user”.
Options having default values always require an equals sign when
assigning a value; failing to do so causes an error. For
example, the MySQL server --log-error
has the
default value
,
where host_name
.errhost_name
is the name of the
host on which MySQL is running. Assume that you are running
MySQL on a computer whose hostname is “tonfisk”,
and consider the following invocation of
mysqld_safe:
shell> mysqld_safe &
[1] 11699
shell> 080112 12:53:40 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080112 12:53:40 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
shell>
After shutting down the server, restart it as follows:
shell> mysqld_safe --log-errors &
[1] 11699
shell> 080112 12:53:40 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080112 12:53:40 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
shell>
The result is the same, since --log-errors
is
not followed by anything else on the command line, and it
supplies its own default value. (The &
character tells the operating system to run MySQL in the
background; it is ignored by MySQL itself.) Now suppose that you
wish to log errors to a file named
my-errors.err
. You might try starting the
server with --log-error my-errors
, but this
does not have the intended effect, as shown here:
shell> mysqld_safe --log-error my-errors &
[1] 31357
shell> 080111 22:53:31 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080111 22:53:32 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
080111 22:53:34 mysqld_safe mysqld from pid file /usr/local/mysql/var/tonfisk.pid ended
[1]+ Done ./mysqld_safe --log-error my-errors
The server attempted to start using
/usr/local/mysql/var/tonfisk.err
as the
error log, but then shut down. Examining the last few lines of
this file shows the reason:
shell> tail /usr/local/mysql/var/tonfisk.err
080111 22:53:32 InnoDB: Started; log sequence number 0 46409
/usr/local/mysql/libexec/mysqld: Too many arguments (first extra is 'my-errors').
Use --verbose --help to get a list of available options
080111 22:53:32 [ERROR] Aborting
080111 22:53:32 InnoDB: Starting shutdown...
080111 22:53:34 InnoDB: Shutdown completed; log sequence number 0 46409
080111 22:53:34 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete
080111 22:53:34 mysqld_safe mysqld from pid file /usr/local/mysql/var/tonfisk.pid ended
Because the --log-error
option supplies a
default value, you must use an equals sign to assign a different
value to it, as shown here:
shell> mysqld_safe --log-error=my-errors &
[1] 31437
shell> 080111 22:54:15 mysqld_safe Logging to '/usr/local/mysql/var/my-errors.err'.
080111 22:54:15 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
shell>
Now the server has been started successfully, and is logging
errors to the file
/usr/local/mysql/var/my-errors.err
.
Similar issues can arise when specifying option values in option
files. For example, consider a my.cnf
file
that contains the following:
[mysql] host user
When the mysql client reads this file, these
entries are parsed as --host --user
or
--host=--user
, with the result shown here:
shell> mysql
ERROR 2005 (HY000): Unknown MySQL server host '--user' (1)
Howver, in option files, an equals sign is not assumed. Suppose
the my.cnf
file is as shown here:
[mysql] user jon
Trying to start mysql in this case causes a different error:
shell> mysql
mysql: unknown option '--user jon'
A similar error would occur if you were to write host
tonfisk
in the option file rather than
host=tonfisk
. Instead, you must use the
equals sign:
[mysql] user=jon
shell>mysql
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.0.56 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>SELECT USER();
+---------------+ | USER() | +---------------+ | jon@localhost | +---------------+ 1 row in set (0.00 sec)
This is not the same behavior as with the command line, where the equals sign is not required:
shell>mysql --user jon --host tonfisk
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.0.56 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>SELECT USER();
+---------------+ | USER() | +---------------+ | jon@tonfisk | +---------------+ 1 row in set (0.00 sec)
Environment variables can be set at the command prompt to affect the current invocation of your command processor, or set permanently to affect future invocations. To set a variable permanently, you can set it in a startup file or by using the interface provided by your system for this purpose. Consult the documentation for your command interpreter for specific details. Section 2.4.20, “Environment Variables”, lists all environment variables that affect MySQL program operation.
To specify a value for an environment variable, use the syntax
appropriate for your command processor. For example, on Windows or
NetWare, you can set the USER
variable to
specify your MySQL account name. To do so, use this syntax:
SET USER=your_name
The syntax on Unix depends on your shell. Suppose that you want to
specify the TCP/IP port number using the
MYSQL_TCP_PORT
variable. Typical syntax (such
as for sh, bash
,
zsh, and so on) is as follows:
MYSQL_TCP_PORT=3306 export MYSQL_TCP_PORT
The first command sets the variable, and the
export
command exports the variable to the
shell environment so that its value becomes accessible to MySQL
and other processes.
For csh and tcsh, use setenv to make the shell variable available to the environment:
setenv MYSQL_TCP_PORT 3306
The commands to set environment variables can be executed at your command prompt to take effect immediately, but the settings persist only until you log out. To have the settings take effect each time you log in, use the interface provided by your system or place the appropriate command or commands in a startup file that your command interpreter reads each time it starts.
On Windows, you can set environment variables using the System Control Panel (under Advanced).
On Unix, typical shell startup files are
.bashrc
or .bash_profile
for bash, or .tcshrc
for
tcsh.
Suppose that your MySQL programs are installed in
/usr/local/mysql/bin
and that you want to make
it easy to invoke these programs. To do this, set the value of the
PATH
environment variable to include that
directory. For example, if your shell is bash,
add the following line to your .bashrc
file:
PATH=${PATH}:/usr/local/mysql/bin
bash uses different startup files for login and
non-login shells, so you might want to add the setting to
.bashrc
for login shells and to
.bash_profile
for non-login shells to make
sure that PATH
is set regardless.
If your shell is tcsh, add the following line
to your .tcshrc
file:
setenv PATH ${PATH}:/usr/local/mysql/bin
If the appropriate startup file does not exist in your home directory, create it with a text editor.
After modifying your PATH
setting, open a new
console window on Windows or log in again on Unix so that the
setting goes into effect.
This section describes mysqld, the MySQL server, and several programs that are used to start the server.
mysqld, also known as MySQL Server, is the main program that does most of the work in a MySQL installation. MySQL Server manages access to the MySQL data directory that contains databases and tables. The data directory is also the default location for other information such as log files and status files.
When MySQL server starts, it listens for network connections from client programs and manages access to databases on behalf of those clients.
The mysqld program has many options that can be specified at startup. For a complete list of options, run this command:
shell> mysqld --verbose --help
MySQL Server also has a set of system variables that affect its operation as it runs. System variables can be set at server startup, and many of them can be changed at runtime to effect dynamic server reconfiguration. MySQL Server also has a set of status variables that provide information about its operation. You can monitor these status variables to access runtime performance characteristics.
For a full description of MySQL Server command options, system variables, and status variables, see Section 5.1, “The MySQL Server”. For information about installing MySQL and setting up the initial configuration, see Chapter 2, Installing and Upgrading MySQL.
mysqld_safe is the recommended way to start a mysqld server on Unix and NetWare. mysqld_safe adds some safety features such as restarting the server when an error occurs and logging runtime information to an error log file. NetWare-specific behaviors are listed later in this section.
To preserve backward compatibility with older versions of MySQL, MySQL binary distributions still include safe_mysqld as a symbolic link to mysqld_safe. However, you should not rely on this because it is removed as of MySQL 5.1.
By default, mysqld_safe before MySQL 5.0.27 tries to start an executable named mysqld-max if it exists, and mysqld otherwise. Be aware of the implications of this behavior:
On Linux, the MySQL-Max
RPM relies on
this mysqld_safe behavior. The RPM
installs an executable named mysqld-max,
which causes mysqld_safe to automatically
use that executable rather than mysqld
from that point on.
If you install a MySQL-Max distribution that includes a server named mysqld-max, and then upgrade later to a non-Max version of MySQL, mysqld_safe will still attempt to run the old mysqld-max server. If you perform such an upgrade, you should manually remove the old mysqld-max server to ensure that mysqld_safe runs the new mysqld server.
To override the default behavior and specify explicitly the name
of the server you want to run, specify a
--mysqld
or --mysqld-version
option to mysqld_safe. You can also use
--ledir
to indicate the directory where
mysqld_safe should look for the server.
Many of the options to mysqld_safe are the same as the options to mysqld. See Section 5.1.2, “Command Options”.
All options specified to mysqld_safe on the
command line are passed to mysqld. If you
want to use any options that are specific to
mysqld_safe and that
mysqld doesn't support, do not specify them
on the command line. Instead, list them in the
[mysqld_safe]
group of an option file. See
Section 4.2.2.2, “Using Option Files”.
mysqld_safe reads all options from the
[mysqld]
, [server]
, and
[mysqld_safe]
sections in option files. For
example, if you specify a [mysqld]
section
like this, mysqld_safe will find and use the
--log-error
option:
[mysqld] log-error=error.log
For backward compatibility, mysqld_safe also
reads [safe_mysqld]
sections, although you
should rename such sections to [mysqld_safe]
in MySQL 5.0 installations.
mysqld_safe supports the following options:
Table 4.1. mysqld_safe Option Reference
Format | Config File | Description | Introduced |
---|---|---|---|
--autoclose | autoclose | On NetWare, mysqld_safe provides a screen presence | |
--basedir=path | basedir | The path to the MySQL installation directory | |
--core-file-size=size | core-file-size | The size of the core file that mysqld should be able to create | |
--datadir=path | datadir | The path to the data directory | |
--defaults-extra-file=path | defaults-extra-file | The name of an option file to be read in addition to the usual option files | |
--defaults-file=file_name | defaults-file | The name of an option file to be read instead of the usual option files | |
--help | Display a help message and exit | 5.0.3 | |
--ledir=path | ledir | Use this option to indicate the pathname to the directory where the server is located | |
--log-error=file_name | log-error | Write the error log to the given file | |
--mysqld=prog_name | mysqld | The name of the server program (in the ledir directory) that you want to start | |
--mysqld-version=suffix | mysqld-version | This option is similar to the --mysqld option, but you specify only the suffix for the server program name | |
--nice=priority | nice | Use the nice program to set the server's scheduling priority to the given value | |
--no-defaults | no-defaults | Do not read any option files | |
--open-files-limit=count | open-files-limit | The number of files that mysqld should be able to open | |
--pid-file | pid-file | The pathname of the process ID file | |
--port=number | port | The port number that the server should use when listening for TCP/IP connections | |
--skip-kill-mysqld | skip-kill-mysqld | Do not try to kill stray mysqld processes | |
--socket=path | socket | The Unix socket file that the server should use when listening for local connections | |
--timezone=timezone | timezone | Set the TZ time zone environment variable to the given option value | |
--user={user_name|user_id} | user | Run the mysqld server as the user having the name user_name or the numeric user ID user_id |
Display a help message and exit. (Added in MySQL 5.0.3)
(NetWare only) On NetWare, mysqld_safe provides a screen presence. When you unload (shut down) the mysqld_safe NLM, the screen does not by default go away. Instead, it prompts for user input:
*<NLM has terminated; Press any key to close the screen>*
If you want NetWare to close the screen automatically
instead, use the --autoclose
option to
mysqld_safe.
The path to the MySQL installation directory.
The size of the core file that mysqld should be able to create. The option value is passed to ulimit -c.
The path to the data directory.
The name of an option file to be read in addition to the usual option files. This must be the first option on the command line if it is used. As of MySQL 5.0.6, if the file does not exist or is otherwise inaccessible, the server will exit with an error.
The name of an option file to be read instead of the usual option files. This must be the first option on the command line if it is used.
If mysqld_safe cannot find the server, use this option to indicate the pathname to the directory where the server is located.
Write the error log to the given file. See Section 5.2.1, “The Error Log”.
The name of the server program (in the
ledir
directory) that you want to start.
This option is needed if you use the MySQL binary
distribution but have the data directory outside of the
binary distribution. If mysqld_safe
cannot find the server, use the --ledir
option to indicate the pathname to the directory where the
server is located.
This option is similar to the --mysqld
option, but you specify only the suffix for the server
program name. The basename is assumed to be
mysqld. For example, if you use
--mysqld-version=debug
,
mysqld_safe starts the
mysqld-debug program in the
ledir
directory. If the argument to
--mysqld-version
is empty,
mysqld_safe uses
mysqld in the ledir
directory.
Use the nice
program to set the server's
scheduling priority to the given value.
Do not read any option files. This must be the first option on the command line if it is used.
The number of files that mysqld should be
able to open. The option value is passed to ulimit
-n. Note that you need to start
mysqld_safe as root
for this to work properly!
The pathname of the process ID file.
The port number that the server should use when listening
for TCP/IP connections. The port number must be 1024 or
higher unless the server is started by the
root
system user.
Do not try to kill stray mysqld processes at startup. This option works only on Linux.
The Unix socket file that the server should use when listening for local connections.
Set the TZ
time zone environment variable
to the given option value. Consult your operating system
documentation for legal time zone specification formats.
Run the mysqld server as the user having
the name user_name
or the numeric
user ID user_id
.
(“User” in this context refers to a system
login account, not a MySQL user listed in the grant tables.)
If you execute mysqld_safe with the
--defaults-file
or
--defaults-extra-option
option to name an
option file, the option must be the first one given on the
command line or the option file will not be used. For example,
this command will not use the named option file:
mysql> mysqld_safe --port=port_num
--defaults-file=file_name
Instead, use the following command:
mysql> mysqld_safe --defaults-file=file_name
--port=port_num
The mysqld_safe script is written so that it normally can start a server that was installed from either a source or a binary distribution of MySQL, even though these types of distributions typically install the server in slightly different locations. (See Section 2.4.6, “Installation Layouts”.) mysqld_safe expects one of the following conditions to be true:
The server and databases can be found relative to the
working directory (the directory from which
mysqld_safe is invoked). For binary
distributions, mysqld_safe looks under
its working directory for bin
and
data
directories. For source
distributions, it looks for libexec
and
var
directories. This condition should
be met if you execute mysqld_safe from
your MySQL installation directory (for example,
/usr/local/mysql
for a binary
distribution).
If the server and databases cannot be found relative to the
working directory, mysqld_safe attempts
to locate them by absolute pathnames. Typical locations are
/usr/local/libexec
and
/usr/local/var
. The actual locations
are determined from the values configured into the
distribution at the time it was built. They should be
correct if MySQL is installed in the location specified at
configuration time.
Because mysqld_safe tries to find the server and databases relative to its own working directory, you can install a binary distribution of MySQL anywhere, as long as you run mysqld_safe from the MySQL installation directory:
shell>cd
shell>mysql_installation_directory
bin/mysqld_safe &
If mysqld_safe fails, even when invoked from
the MySQL installation directory, you can specify the
--ledir
and --datadir
options
to indicate the directories in which the server and databases
are located on your system.
Normally, you should not edit the mysqld_safe
script. Instead, configure mysqld_safe by
using command-line options or options in the
[mysqld_safe]
section of a
my.cnf
option file. In rare cases, it might
be necessary to edit mysqld_safe to get it to
start the server properly. However, if you do this, your
modified version of mysqld_safe might be
overwritten if you upgrade MySQL in the future, so you should
make a copy of your edited version that you can reinstall.
On NetWare, mysqld_safe is a NetWare Loadable Module (NLM) that is ported from the original Unix shell script. It starts the server as follows:
Runs a number of system and option checks.
Runs a check on MyISAM
tables.
Provides a screen presence for the MySQL server.
Starts mysqld, monitors it, and restarts it if it terminates in error.
Sends error messages from mysqld to the
file in the data directory.
host_name
.err
Sends mysqld_safe screen output to the
file in the data directory.
host_name
.safe
MySQL distributions on Unix include a script named mysql.server. It can be used on systems such as Linux and Solaris that use System V-style run directories to start and stop system services. It is also used by the Mac OS X Startup Item for MySQL.
mysql.server can be found in the
support-files
directory under your MySQL
installation directory or in a MySQL source distribution.
If you use the Linux server RPM package
(MySQL-server-
),
the mysql.server script will be installed in
the VERSION
.rpm/etc/init.d
directory with the name
mysql
. You need not install it manually.
See Section 2.4.9, “Installing MySQL from RPM Packages on Linux”, for more information on the
Linux RPM packages.
Some vendors provide RPM packages that install a startup script under a different name such as mysqld.
If you install MySQL from a source distribution or using a binary distribution format that does not install mysql.server automatically, you can install it manually. Instructions are provided in Section 2.4.16.2.2, “Starting and Stopping MySQL Automatically”.
mysql.server reads options from the
[mysql.server]
and
[mysqld]
sections of option files. For
backward compatibility, it also reads
[mysql_server]
sections, although you should
rename such sections to [mysql.server]
when
using MySQL 5.0.
mysql.server understands the following options:
The path to the MySQL installation directory.
The path to the MySQL data directory.
The pathname of the file in which the server should write its process ID.
--service-startup-timeout=
file_name
How long in seconds to wait for confirmation of server startup. If the server does not start within this time, mysql.server exits with an error. The default value is 900. A value of 0 means not to wait at all for startup. Negative values mean to wait forever (no timeout). This option was added in MySQL 5.0.40. Before that, a value of 900 is always used.
Use mysqld_safe to start the server. This is the default. This option was added in MySQL 5.0.4.
Use Instance Manager to start the server. This option was added in MySQL 5.0.4.
The login username to use for running mysqld. This option was added in MySQL 5.0.4.
mysqld_multi is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP ports. It can start or stop servers, or report their current status. The MySQL Instance Manager is an alternative means of managing multiple servers (see Section 4.6.9, “mysqlmanager — The MySQL Instance Manager”).
mysqld_multi searches for groups named
[mysqld
in
N
]my.cnf
(or in the file named by the
--config-file
option).
N
can be any positive integer. This
number is referred to in the following discussion as the option
group number, or GNR
. Group numbers
distinguish option groups from one another and are used as
arguments to mysqld_multi to specify which
servers you want to start, stop, or obtain a status report for.
Options listed in these groups are the same that you would use
in the [mysqld]
group used for starting
mysqld. (See, for example,
Section 2.4.16.2.2, “Starting and Stopping MySQL Automatically”.) However, when using multiple
servers, it is necessary that each one use its own value for
options such as the Unix socket file and TCP/IP port number. For
more information on which options must be unique per server in a
multiple-server environment, see
Section 5.6, “Running Multiple MySQL Servers on the Same Machine”.
To invoke mysqld_multi, use the following syntax:
shell> mysqld_multi [options
] {start|stop|report} [GNR
[,GNR
] ...]
start
, stop
, and
report
indicate which operation to perform.
You can perform the designated operation for a single server or
multiple servers, depending on the
GNR
list that follows the option
name. If there is no list, mysqld_multi
performs the operation for all servers in the option file.
Each GNR
value represents an option
group number or range of group numbers. The value should be the
number at the end of the group name in the option file. For
example, the GNR
for a group named
[mysqld17]
is 17
. To
specify a range of numbers, separate the first and last numbers
by a dash. The GNR
value
10-13
represents groups
[mysqld10]
through
[mysqld13]
. Multiple groups or group ranges
can be specified on the command line, separated by commas. There
must be no whitespace characters (spaces or tabs) in the
GNR
list; anything after a whitespace
character is ignored.
This command starts a single server using option group
[mysqld17]
:
shell> mysqld_multi start 17
This command stops several servers, using option groups
[mysqld8]
and [mysqld10]
through [mysqld13]
:
shell> mysqld_multi stop 8,10-13
For an example of how you might set up an option file, use this command:
shell> mysqld_multi --example
As of MySQL 5.0.42, mysqld_multi searches for option files as follows:
Option files read are searched for
[mysqld_multi]
and
[mysqld
option
groups.
N
]
Before MySQL 5.0.42, the preceding options are not recognized.
Files in the standard locations are read, and any file named by
the
--config-file=
option, if one is given. A file named by
file_name
--config-file
is read only for
[mysqld
option
groups, not the N
][mysqld_multi]
group.
mysqld_multi supports the following options:
Display a help message and exit.
As of MySQL 5.0.42, this option is deprecated. If given, it
is treated the same way as
--defaults-extra-file
, described earlier.
Before MySQL 5.0.42, this option specifies the name of an
extra option file. It affects where
mysqld_multi looks for
[mysqld
option groups. Without this option, all options are read
from the usual N
]my.cnf
file. The option
does not affect where mysqld_multi reads
its own options, which are always taken from the
[mysqld_multi]
group in the usual
my.cnf
file.
Display a sample option file.
Specify the name of the log file. If the file exists, log output is appended to it.
The mysqladmin binary to be used to stop servers.
The mysqld binary to be used. Note that
you can specify mysqld_safe as the value
for this option also. If you use
mysqld_safe to start the server, you can
include the mysqld
or
ledir
options in the corresponding
[mysqld
option group. These options indicate the name of the server
that mysqld_safe should start and the
pathname of the directory where the server is located. (See
the descriptions for these options in
Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.) Example:
N
]
[mysqld38] mysqld = mysqld-debug ledir = /opt/local/mysql/libexec
Print log information to stdout
rather
than to the log file. By default, output goes to the log
file.
The password of the MySQL account to use when invoking mysqladmin. Note that the password value is not optional for this option, unlike for other MySQL programs.
Silent mode; disable warnings.
Connect to each MySQL server via the TCP/IP port instead of
the Unix socket file. (If a socket file is missing, the
server might still be running, but accessible only via the
TCP/IP port.) By default, connections are made using the
Unix socket file. This option affects
stop
and report
operations.
The username of the MySQL account to use when invoking mysqladmin.
Be more verbose.
Display version information and exit.
Some notes about mysqld_multi:
Most important: Before using mysqld_multi be sure that you understand the meanings of the options that are passed to the mysqld servers and why you would want to have separate mysqld processes. Beware of the dangers of using multiple mysqld servers with the same data directory. Use separate data directories, unless you know what you are doing. Starting multiple servers with the same data directory does not give you extra performance in a threaded system. See Section 5.6, “Running Multiple MySQL Servers on the Same Machine”.
Make sure that the data directory for each server is fully
accessible to the Unix account that the specific
mysqld process is started as.
Do not use the Unix
root
account for this, unless
you know what you are doing. See
Section 5.3.5, “How to Run MySQL as a Normal User”.
Make sure that the MySQL account used for stopping the
mysqld servers (with the
mysqladmin program) has the same username
and password for each server. Also, make sure that the
account has the SHUTDOWN
privilege. If
the servers that you want to manage have different usernames
or passwords for the administrative accounts, you might want
to create an account on each server that has the same
username and password. For example, you might set up a
common multi_admin
account by executing
the following commands for each server:
shell>mysql -u root -S /tmp/mysql.sock -p
Enter password: mysql>GRANT SHUTDOWN ON *.*
->TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
See Section 5.4.2, “How the Privilege System Works”. You have to do this for
each mysqld server. Change the connection
parameters appropriately when connecting to each one. Note
that the hostname part of the account name must allow you to
connect as multi_admin
from the host
where you want to run mysqld_multi.
The Unix socket file and the TCP/IP port number must be
different for every mysqld.
(Alternatively, if the host has multiple network addresses,
you can use --bind-adress
to cause
different servers to listen to different interfaces.)
The --pid-file
option is very important if
you are using mysqld_safe to start
mysqld (for example,
--mysqld=mysqld_safe
) Every
mysqld should have its own process ID
file. The advantage of using mysqld_safe
instead of mysqld is that
mysqld_safe monitors its
mysqld process and restarts it if the
process terminates due to a signal sent using kill
-9
or for other reasons, such as a segmentation
fault. Please note that the mysqld_safe
script might require that you start it from a certain place.
This means that you might have to change location to a
certain directory before running
mysqld_multi. If you have problems
starting, please see the mysqld_safe
script. Check especially the lines:
---------------------------------------------------------------- MY_PWD=`pwd` # Check if we are starting this relative (for the binary release) if test -d $MY_PWD/data/mysql -a -f ./share/mysql/english/errmsg.sys -a \ -x ./bin/mysqld ----------------------------------------------------------------
The test performed by these lines should be successful, or you might encounter problems. See Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.
You might want to use the --user
option for
mysqld, but to do this you need to run
the mysqld_multi script as the Unix
root
user. Having the option in the
option file doesn't matter; you just get a warning if you
are not the superuser and the mysqld
processes are started under your own Unix account.
The following example shows how you might set up an option file
for use with mysqld_multi. The order in which
the mysqld programs are started or stopped
depends on the order in which they appear in the option file.
Group numbers need not form an unbroken sequence. The first and
fifth [mysqld
groups were intentionally omitted from the example to illustrate
that you can have “gaps” in the option file. This
gives you more flexibility.
N
]
# This file should probably be in your home dir (~/.my.cnf) # or /etc/my.cnf # Version 2.1 by Jani Tolonen [mysqld_multi] mysqld = /usr/local/bin/mysqld_safe mysqladmin = /usr/local/bin/mysqladmin user = multi_admin password = multipass [mysqld2] socket = /tmp/mysql.sock2 port = 3307 pid-file = /usr/local/mysql/var2/hostname.pid2 datadir = /usr/local/mysql/var2 language = /usr/local/share/mysql/english user = john [mysqld3] socket = /tmp/mysql.sock3 port = 3308 pid-file = /usr/local/mysql/var3/hostname.pid3 datadir = /usr/local/mysql/var3 language = /usr/local/share/mysql/swedish user = monty [mysqld4] socket = /tmp/mysql.sock4 port = 3309 pid-file = /usr/local/mysql/var4/hostname.pid4 datadir = /usr/local/mysql/var4 language = /usr/local/share/mysql/estonia user = tonu [mysqld6] socket = /tmp/mysql.sock6 port = 3311 pid-file = /usr/local/mysql/var6/hostname.pid6 datadir = /usr/local/mysql/var6 language = /usr/local/share/mysql/japanese user = jani
The programs in this section are used when installing or upgrading MySQL.
comp_err creates the
errmsg.sys
file that is used by
mysqld to determine the error messages to
display for different error codes. comp_err
normally is run automatically when MySQL is built. It compiles
the errmsg.sys
file from the plaintext file
located at sql/share/errmsg.txt
in MySQL
source distributions.
comp_err also generates
mysqld_error.h
,
mysqld_ername.h
, and
sql_state.h
header files.
For more information about how error messages are defined, see the MySQL Internals Manual.
Invoke comp_err like this:
shell> comp_err [options
]
comp_err understands the options described in the following list.
Display a help message and exit.
The character set directory. The default is
../sql/share/charsets
.
--debug=
debug_options
, -#
debug_options
Write a debugging log. The
debug_options
string often is
'd:t:O,
.
The default is
file_name
''d:t:O,/tmp/comp_err.trace'
.
Print some debugging information when the program exits.
--header_file=
file_name
,
-H file_name
The name of the error header file. The default is
mysqld_error.h
.
--in_file=
file_name
, -F
file_name
The name of the input file. The default is
../sql/share/errmsg.txt
.
--name_file=
file_name
, -N
file_name
The name of the error name file. The default is
mysqld_ername.h
.
The name of the output base directory. The default is
../sql/share/
.
--out_file=
file_name
, -O
file_name
The name of the output file. The default is
errmsg.sys
.
--statefile=
file_name
, -S
file_name
The name for the SQLSTATE header file. The default is
sql_state.h
.
Display version information and exit.
This script is used on Windows after building a MySQL distribution from source to create executable programs. It packages the binaries and support files into a ZIP archive that can be unpacked at the location where you want to install MySQL.
make_win_bin_dist is a shell script, so you must have Cygwin installed to use it.
This program's use is subject to change. Currently, you invoke it as follows from the root directory of your source distribution:
shell> make_win_bin_dist [options
] package_basename
[copy_def
...]
The package_basename
argument
provides the basename for the resulting ZIP archive. This name
will be the name of the directory that results from unpacking
the archive.
Because you might want to include files of directories from
other builds, you can instruct this script do copy them in for
you, via copy_def
arguments, which of
which is of the form
relative_dest_name
=source_name
.
Example:
bin/mysqld-max.exe=../my-max-build/sql/release/mysqld.exe
If you specify a directory, the entire directory will be copied.
make_win_bin_dist understands the following options:
Pack the debug binaries and produce an error if they were not built.
Pack the embedded server and produce an error if it was not built. The default is to pack it if it was built.
Add a suffix to the basename of the mysql
binary. For example, a suffix of -abc
produces a binary named mysqld-abc.exe.
Don't pack the debug binaries even if they were built.
Don't pack the embedded server even if it was built.
Use this option when the target for this build was
Debug
, and you just want to replace the
normal binaries with debug versions (that is, do not use
separate debug
directories).
make_win_src_distribution creates a Windows source package to be used on Windows systems. It is used after you configure and build the source distribution on a Unix or Unix-like system so that you have a server binary to work with. (See the instructions at Section 2.4.15.6.5, “Creating a Windows Source Package from the BitKeeper Repository”.)
Invoke make_win_src_distribution like this from the top-level directory of a MySQL source distribution:
shell> make_win_src_distribution [options
]
make_win_src_distribution understands the following options:
--help
Display a help message and exit.
--debug
Print information about script operations; do not create a package.
--tmp
Specify the temporary location.
--suffix
The suffix name for the package.
--dirname
Directory name to copy files (intermediate).
--silent
Do not print verbose list of files processed.
--tar
Create a tar.gz
package instead of a
.zip
package.
By default, make_win_src_distribution
creates a Zip-format archive with the name
mysql-
,
where VERSION
-win-src.zipVERSION
represents the
version of your MySQL source tree.
This program enables you to generate a bug report and send it to MySQL AB. It is a shell script and runs on Unix.
The normal way to report bugs is to visit http://bugs.mysql.com/, which is the address for our bugs database. This database is public and can be browsed and searched by anyone. If you log in to the system, you can enter new reports. If you have no Web access, you can generate a bug report by using the mysqlbug script.
mysqlbug helps you generate a report by
determining much of the following information automatically, but
if something important is missing, please include it with your
message. mysqlbug can be found in the
scripts
directory (source distribution) and
in the bin
directory under your MySQL
installation directory (binary distribution).
Invoke mysqlbug without arguments:
shell> mysqlbug
The script will place you in an editor with a copy of the report to be sent. Edit the lines near the beginning that indicate the nature of the problem. Then write the file to save your changes, quit the editor, and mysqlbug will send the report by email. perform.
Some releases of MySQL introduce changes to the structure of the
system tables in the mysql
database to add
new privileges or support new features. When you update to a new
version of MySQL, you should update your system tables as well
to make sure that their structure is up to date. Otherwise,
there might be capabilities that you cannot take advantage of.
First, make a backup of your mysql
database,
and then use the following procedure.
As of MySQL 5.0.19, mysql_fix_privilege_tables is superseded by mysql_upgrade, which should be used instead. See Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.
On Unix or Unix-like systems, update the system tables by running the mysql_fix_privilege_tables script:
shell> mysql_fix_privilege_tables
You must run this script while the server is running. It
attempts to connect to the server running on the local host as
root
. If your root
account
requires a password, indicate the password on the command line
like this:
shell> mysql_fix_privilege_tables --password=root_password
The mysql_fix_privilege_tables script
performs any actions necessary to convert your system tables to
the current format. You might see some Duplicate column
name
warnings as it runs; you can ignore them.
After running the script, stop the server and restart it so that it uses any changes that were made to the system tables.
On Windows systems, MySQL distributions include a
mysql_fix_privilege_tables.sql
SQL script
that you can run using the mysql client. For
example, if your MySQL installation is located at
C:\Program Files\MySQL\MySQL Server
5.0
, the commands look like this:
C:\>cd "C:\Program Files\MySQL\MySQL Server 5.0"
C:\>bin\mysql -u root -p mysql
mysql>SOURCE share/mysql_fix_privilege_tables.sql
Prior to version 5.0.38, this script is found in the
scripts
directory.
The mysql command will prompt you for the
root
password; enter it when prompted.
If your installation is located in some other directory, adjust the pathnames appropriately.
As with the Unix procedure, you might see some
Duplicate column name
warnings as
mysql processes the statements in the
mysql_fix_privilege_tables.sql
script; you
can ignore them.
After running the script, stop the server and restart it.
mysql_install_db initializes the MySQL data
directory and creates the system tables that it contains, if
they do not exist. Because the MySQL server,
mysqld, needs to access the data directory
when it runs later, you should either run
mysql_install_db from the same account that
will be used for running mysqld or run it as
root
and use the --user
option to indicate the username that mysqld
will run as.
To invoke mysql_install_db, use the following syntax:
shell> mysql_install_db [options
]
mysql_install_db needs to invoke
mysqld with the --bootstrap
and --skip-grant-tables
options (see
Section 2.4.15.2, “Typical configure Options”). If MySQL was configured
with the --disable-grant-options
option,
--bootstrap
and
--skip-grant-tables
will be disabled. To handle
this, set the MYSQLD_BOOTSTRAP
environment
variable to the full pathname of a server that has all options
enabled. mysql_install_db will use that
server.
mysql_install_db supports the following options:
--basedir=
path
The path to the MySQL installation directory.
--force
Causes mysql_install_db to run even if DNS does not work. In that case, grant table entries that normally use hostnames will use IP addresses.
--datadir=
,
path
--ldata=
path
The path to the MySQL data directory.
--rpm
For internal use. This option is used by RPM files during the MySQL installation process.
--skip-name-resolve
Use IP addresses rather than hostnames when creating grant table entries. This option can be useful if your DNS does not work.
--srcdir=
path
For internal use. The directory under which mysql_install_db looks for support files such as the error message file and the file for populating the help tables. This option was added in MySQL 5.0.32.
--user=
user_name
The login username to use for running
mysqld. Files and directories created by
mysqld will be owned by this user. You
must be root
to use this option. By
default, mysqld runs using your current
login name and files and directories that it creates will be
owned by you.
--verbose
Verbose mode. Print more information about what the program does.
--windows
For internal use. This option is used for creating Windows distributions.
This program enables you to improve the security of your MySQL installation in the following ways:
You can set a password for root
accounts.
You can remove root
accounts that are
accessible from outside the local host.
You can remove anonymous-user accounts.
You can remove the test
database, which
by default can be accessed by anonymous users.
Invoke mysql_secure_installation without arguments:
shell> mysql_secure_installation
The script will prompt you to determine which actions to perform.
The mysql_tzinfo_to_sql program loads the
time zone tables in the mysql
database. It is
used on systems that have a zoneinfo
database (the set of files describing time zones). Examples of
such systems are Linux, FreeBSD, Sun Solaris, and Mac OS X. One
likely location for these files is the
/usr/share/zoneinfo
directory. If your
system does not have a zoneinfo database, you can use the
downloadable package described in
Section 9.6, “MySQL Server Time Zone Support”.
mysql_tzinfo_to_sql can be invoked several ways:
shell>mysql_tzinfo_to_sql
shell>tz_dir
mysql_tzinfo_to_sql
shell>tz_file tz_name
mysql_tzinfo_to_sql --leap
tz_file
For the first invocation syntax, pass the zoneinfo directory pathname to mysql_tzinfo_to_sql and send the output into the mysql program. For example:
shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
mysql_tzinfo_to_sql reads your system's time zone files and generates SQL statements from them. mysql processes those statements to load the time zone tables.
The second syntax causes mysql_tzinfo_to_sql
to load a single time zone file
tz_file
that corresponds to a time
zone name tz_name
:
shell> mysql_tzinfo_to_sql tz_file
tz_name
| mysql -u root mysql
If your time zone needs to account for leap seconds, invoke
mysql_tzinfo_to_sql using the third syntax,
which initializes the leap second information.
tz_file
is the name of your time zone
file:
shell> mysql_tzinfo_to_sql --leap tz_file
| mysql -u root mysql
After running mysql_tzinfo_to_sql, it is best to restart the server so that it does not continue to use any previously cached time zone data.
mysql_upgrade should be executed each time you upgrade MySQL. It checks all tables in all databases for incompatibilities with the current version of MySQL Server. If a table is found to have a possible incompatibility, it is checked. If any problems are found, the table is repaired. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.
All checked and repaired tables are marked with the current MySQL version number. This ensures that next time you run mysql_upgrade with the same version of the server, it can tell whether there is any need to check or repair the table again.
mysql_upgrade also saves the MySQL version
number in a file named mysql_upgrade_info
in the data directory. This is used to quickly check if all
tables have been checked for this release so that table-checking
can be skipped. To ignore this file, use the
--force
option.
To check and repair tables and to upgrade the system tables, mysql_upgrade executes the following commands:
mysqlcheck --check-upgrade --all-databases --auto-repair mysql_fix_privilege_tables
mysql_upgrade supersedes the older mysql_fix_privilege_tables script. In MySQL 5.0.19, mysql_upgrade was added as a shell script and worked only for Unix systems. As of MySQL 5.0.25, mysql_upgrade is an executable binary and is available on all systems. On systems older than those supporting mysql_upgrade, you can execute the mysqlcheck command manually, and then upgrade your system tables as described in Section 4.4.5, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”.
If you install MySQL from RPM packages on Linux, you must install the server and client RPMs. mysql_upgrade is included in the server RPM but requires the client RPM because the latter includes mysqlcheck. (See Section 2.4.9, “Installing MySQL from RPM Packages on Linux”.)
For details about what is checked, see the description of the
FOR UPGRADE
option of the CHECK
TABLE
statement (see Section 12.5.2.3, “CHECK TABLE
Syntax”).
To use mysql_upgrade, make sure that the server is running, and then invoke it like this:
shell> mysql_upgrade [options
]
After running mysql_upgrade, stop the server and restart it so that it uses any changes that were made to the system tables.
mysql_upgrade reads options from the command
line and from the [mysql_upgrade]
group in
option files. It supports the following options:
Display a short help message and exit.
The path to the MySQL installation directory.
The path to the data directory.
Force execution of mysqlcheck even if
mysql_upgrade has already been executed
for the current version of MySQL. (In other words, this
option causes the mysql_upgrade_info
file to be ignored.)
--user=
,
user_name
-u
user_name
The MySQL username to use when connecting to the server. The
default username is root
.
Verbose mode. Print more information about what the program does.
Other options are passed to mysqlcheck and to
mysql_fix_privilege_tables. For example, it
might be necessary to specify the
--password[=
option.
password
]
mysql is a simple SQL shell (with GNU
readline
capabilities). It supports
interactive and non-interactive use. When used interactively,
query results are presented in an ASCII-table format. When used
non-interactively (for example, as a filter), the result is
presented in tab-separated format. The output format can be
changed using command options.
If you have problems due to insufficient memory for large result
sets, use the --quick
option. This forces
mysql to retrieve results from the server a
row at a time rather than retrieving the entire result set and
buffering it in memory before displaying it. This is done by
returning the result set using the
mysql_use_result()
C API
function in the client/server library rather than
mysql_store_result()
.
Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows:
shell> mysql db_name
Or:
shell> mysql --user=user_name
--password=your_password
db_name
Then type an SQL statement, end it with
“;
”, \g
, or
\G
and press Enter.
As of MySQL 5.0.25, typing Control-C causes mysql to attempt to kill the current statement. If this cannot be done, or Control-C is typed again before the statement is killed, mysql exits. Previously, Control-C caused mysql to exit in all cases.
You can execute SQL statements in a script file (batch file) like this:
shell> mysql db_name
< script.sql
> output.tab
mysql supports the following options:
Table 4.2. mysql Option Reference
Format | Config File | Description | Introduced |
---|---|---|---|
--auto-rehash | auto-rehash | Enable automatic rehashing | |
--batch | batch | Don't use history file | |
-B | |||
--character-sets-dir=name | character-sets-dir | Set the default character set | |
--column-names | column-names | Write column names in results | |
--comments | comments | Whether to retain or strip comments in statements sent to the server | 5.0.52 |
-c | |||
--compress | compress | Compress all information sent between the client and the server | |
-C | |||
--connect_timeout=value | connect_timeout | The number of seconds before connection timeout | |
--database=dbname | database | The database to use | |
-D dbname | |||
dbname | |||
-# [debug_options] | debug | Write a debugging log | |
--debug[=debug_options] | |||
--debug-info | debug-info | Print debugging information, memory and CPU statistics when the program exits | |
-T | |||
--default-character-set=charset_name | default-character-set | Use charset_name as the default character set | |
--delimiter=str | delimiter | Set the statement delimiter | |
--execute=statement | execute | Execute the statement and quit | |
-e statement | |||
--force | force | Continue even if an SQL error occurs | |
-f | |||
--help | Display help message and exit | ||
-? | |||
-I | |||
--host=host_name | host | Connect to the MySQL server on the given host | |
-h host_name | |||
--html | html | Produce HTML output | |
-H | |||
--ignore-spaces | ignore-spaces | Ignore spaces after function names | |
-i | |||
--line-numbers | line-numbers | Write line numbers for errors | |
--local-infile[={0|1}] | local-infile | Enable or disable for LOCAL capability for LOAD DATA INFILE | |
--max_allowed_packet=value | max_allowed_packet | The maximum packet length to send to or receive from the server | |
--max_join_size=value | max_join_size | The automatic limit for rows in a join when using --safe-updates | |
--named-commands | named-commands | Enable named mysql commands | |
-G | |||
--net_buffer_length=value | net_buffer_length | The buffer size for TCP/IP and socket communication | |
--no-auto-rehash | Disable automatic rehashing | ||
-A | |||
--no-beep | no-beep | Do not beep when errors occur | |
--no-named-commands | no-named-commands | Disable named mysql commands | |
--no-pager | no-pager | Deprecated form of --skip-pager | |
--no-tee | no-tee | Do not copy output to a file | |
--one-database | one-database | Ignore statements except those for the default database named on the command line | |
-o | |||
--pager[=command] | pager | Use the given command for paging query output | |
--password[=password] | password | The password to use when connecting to the server | |
-p[password] | |||
--port=port_num | port | The TCP/IP port number to use for the connection | |
-P port_num | |||
--prompt=format_str | prompt | Set the prompt to the specified format | |
--protocol={TCP|SOCKET|PIPE|MEMORY} | protocol | The connection protocol to use | |
--quick | quick | Do not cache each query result | |
-q | |||
--raw | raw | Write column values without escape conversion | |
-r | |||
--reconnect | reconnect | If the connection to the server is lost, automatically try to reconnect | |
--i-am-a-dummy | safe-updates | Allow only UPDATE and DELETE statements that specify key values | |
--safe-updates | |||
-U | |||
--secure-auth | secure-auth | Do not send passwords to the server in old (pre-4.1.1) format | |
--select_limit=value | select_limit | The automatic limit for SELECT statements when using --safe-updates | |
--show-warnings | show-warnings | Show warnings after each statement if there are any | 5.0.6 |
--sigint-ignore | sigint-ignore | Ignore SIGINT signals (typically the result of typing Control-C) | |
--silent | silent | Silent mode | |
-s | |||
--skip-auto-rehash | skip-auto-rehash | Disable automatic rehashing | |
--skip-column-names | skip-column-names | Do not write column names in results | |
-N | |||
--skip-line-numbers | skip-line-numbers | Skip line numbers for errors | |
--skip-named-commands | skip-named-commands | Disable named mysql commands | |
--skip-pager | skip-pager | Disable paging | |
--skip-reconnect | skip-reconnect | Disable reconnecting | |
--socket=path | socket | For connections to localhost | |
-S path | |||
--ssl-ca=file_name | ssl-ca | The path to a file that contains a list of trusted SSL CAs | |
--ssl-capath=directory_name | ssl-capath | The path to a directory that contains trusted SSL CA certificates in PEM format | |
--ssl-cert=file_name | ssl-cert | The name of the SSL certificate file to use for establishing a secure connection | |
--ssl-cipher=cipher_list | ssl-cipher | A list of allowable ciphers to use for SSL encryption | |
--ssl-key=file_name | ssl-key | The name of the SSL key file to use for establishing a secure connection | |
--ssl-verify-server-cert | ssl-verify-server-cert | The server's Common Name value in its certificate is verified against the hostname used when connecting to the server | |
--table | table | Display output in tabular format | |
-t | |||
--tee=file_name | tee | Append a copy of output to the given file | |
--unbuffered | unbuffered | Flush the buffer after each query | |
--user=user_name | user | The MySQL username to use when connecting to the server | |
-u user_name | |||
--verbose | Verbose mode | ||
-v | |||
--version | Display version information and exit | ||
-V | |||
--vertical | vertical | Print query output rows vertically (one line per column value) | |
-E | |||
--wait | wait | If the connection cannot be established, wait and retry instead of aborting | |
-w | |||
--xml | xml | Produce XML output | |
-X |
Display a help message and exit.
Enable automatic rehashing. This option is on by default,
which enables table and column name completion. Use
--skip-auto-rehash
to disable rehashing.
That causes mysql to start faster, but
you must issue the rehash
command if you
want to use table and column name completion.
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.
The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.
Write column names in results.
Whether to preserve comments in statements sent to the server. The default is --skip-comments (discard comments), enable with --comments (preserve comments). This option was added in MySQL 5.0.52.
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 primarily in an option file.
--debug[=
,
debug_options
]-#
[
debug_options
]
Write a debugging log. The
debug_options
string often is
'd:t:o,
.
The default is file_name
''d:t:o,/tmp/mysql.trace'
.
Print some debugging information when the program exits.
--default-character-set=
charset_name
Use charset_name
as the default
character set. See Section 9.2, “The Character Set Used for Data and Sorting”.
Set the statement delimiter. The default is the semicolon
character (“;
”).
--execute=
,
statement
-e
statement
Execute the statement and quit. The default output format is
like that produced with --batch
. See
Section 4.2.2.1, “Using Options on the Command Line”, for some examples.
Continue even if an SQL error occurs.
--host=
,
host_name
-h
host_name
Connect to the MySQL server on the given host.
Produce HTML output.
Ignore spaces after function names. The effect of this is
described in the discussion for the
IGNORE_SPACE
SQL mode (see
Section 5.1.6, “SQL Modes”).
Write line numbers for errors. Disable this with
--skip-line-numbers
.
Enable or disable LOCAL
capability for
LOAD DATA INFILE
. With no value, the
option enables LOCAL
. The option may be
given as --local-infile=0
or
--local-infile=1
to explicitly disable or
enable LOCAL
. Enabling
LOCAL
has no effect if the server does
not also support it.
MySQL Enterprise
For expert advice on the security implications of enabling
LOCAL
, subscribe to the MySQL
Enterprise Monitor. For more information see
http://www.mysql.com/products/enterprise/advisors.html.
Enable named mysql commands. Long-format
commands are allowed, not just short-format commands. For
example, quit
and \q
both are recognized. Use
--skip-named-commands
to disable named
commands. See Section 4.5.1.2, “mysql Commands”.
Deprecated form of -skip-auto-rehash
. See
the description for --auto-rehash
.
Do not beep when errors occur.
Disable named commands. Use the \*
form
only, or use named commands only at the beginning of a line
ending with a semicolon
(“;
”).
mysql starts with this option
enabled by default. However, even with
this option, long-format commands still work from the first
line. See Section 4.5.1.2, “mysql Commands”.
Deprecated form of --skip-pager
. See the
--pager
option.
Do not copy output to a file. Section 4.5.1.2, “mysql Commands”, discusses tee files further.
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.
Use the given command for paging query output. If the
command is omitted, the default pager is the value of your
PAGER
environment variable. Valid pagers
are less, more,
cat [> filename], and so forth. This
option works only on Unix. It does not work in batch mode.
To disable paging, use --skip-pager
.
Section 4.5.1.2, “mysql Commands”, discusses output paging
further.
--password[=
,
password
]-p[
password
]
The password to use when connecting to the server. If you
use the short option form (-p
), you
cannot have a space between the option
and the password. If you omit the
password
value following the
--password
or -p
option on
the command line, you are prompted for one.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
The TCP/IP port number to use for the connection.
Set the prompt to the specified format. The default is
mysql>
. The special sequences that the
prompt can contain are described in
Section 4.5.1.2, “mysql Commands”.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use.
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.
Write column values without escape conversion. Often used
with the --batch
option.
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 which rows
to modify by using key values. If you have set this option
in an option file, you can override it by using
--safe-updates
on the command line. See
Section 4.5.1.5, “mysql Tips”, for more information about
this option.
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.
MySQL Enterprise For expert advice on database security, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.
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.
Ignore SIGINT
signals (typically the
result of typing Control-C).
Silent mode. Produce less output. This option can be given multiple times to produce less and less output.
Do not write column names in results.
Do not write line numbers for errors. Useful when you want to compare result files that include error messages.
For connections to localhost
, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
Options that begin with --ssl
specify
whether to connect to the server via SSL and indicate where
to find SSL keys and certificates. See
Section 5.5.7.3, “SSL Command Options”.
Display output in table format. This is the default for interactive use, but can be used to produce table output in batch mode.
Append a copy of output to the given file. This option does not work in batch mode. in Section 4.5.1.2, “mysql Commands”, discusses tee files further.
Flush the buffer after each query.
--user=
,
user_name
-u
user_name
The MySQL username to use when connecting to the server.
Verbose mode. Produce more output about what the program
does. This option can be given multiple times to produce
more and more output. (For example, -v -v
-v
produces table output format even in batch
mode.)
Display version information and exit.
Print query output rows vertically (one line per column
value). Without this option, you can specify vertical output
for individual statements by terminating them with
\G
.
If the connection cannot be established, wait and retry instead of aborting.
Produce XML output.
Prior to MySQL 5.0.26, there was no differentiation in the
output when using this option between columns containing
the NULL
value and columns containing
the string literal 'NULL'
; both were
represented as
<field name="column_name
">NULL</field>
Beginning with MySQL 5.0.26, the output when
--xml
is used with mysql
matches that of mysqldump
--xml
. See
the section of the
Manual which discusses the --xml
option for
mysqldump for details.
Beginning with MySQL 5.0.40, the XML output also uses an XML namespace, as shown here:
shell> mysql --xml -uroot -e "SHOW VARIABLES LIKE 'version%'"
<?xml version="1.0"?>
<resultset statement="SHOW VARIABLES LIKE 'version%'" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Variable_name">version</field>
<field name="Value">5.0.40-debug</field>
</row>
<row>
<field name="Variable_name">version_comment</field>
<field name="Value">Source distribution</field>
</row>
<row>
<field name="Variable_name">version_compile_machine</field>
<field name="Value">i686</field>
</row>
<row>
<field name="Variable_name">version_compile_os</field>
<field name="Value">suse-linux-gnu</field>
</row>
</resultset>
(See Bug#25946.)
You can also set the following variables by using
--
syntax:
var_name
=value
The number of seconds before connection timeout. (Default
value is 0
.)
The maximum packet length to send to or receive from the server. (Default value is 16MB.)
The automatic limit for rows in a join when using
--safe-updates
. (Default value is
1,000,000.)
The buffer size for TCP/IP and socket communication. (Default value is 16KB.)
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=
or var_name
=value
-O
syntax. This syntax is deprecated.
var_name
=value
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.
mysql sends each SQL statement 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'.
charset (\C) Switch to another charset. Might be needed for processing
binlog with multi-byte charsets.
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 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.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
For server side help, type 'help contents'
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.
The use of short-form commands within multi-line /* ...
*/
comments is not supported.
If you provide an argument to the help
command, mysql uses it as a search string to
access server-side help from the contents of the MySQL Reference
Manual. For more information, see
Section 4.5.1.3, “mysql Server-Side Help”.
The charset
command changes the default
character set and issues a SET NAMES
statement. This enables the character set to remain synchronized
on the client and server if mysql is run with
auto-reconnect enabled (which is not recommended), because the
changed character set is used for reconnects. This command was
added in MySQL 5.0.25.
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.
By using the --pager
option, it is possible to
browse or search query results in interactive mode with Unix
programs such as less,
more, or any other similar program. 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.
Here are 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:
Option | Description |
\v | The server version |
\d | The default database |
\h | The server host |
\p | The current TCP/IP port or socket file |
\u | Your username |
\U | Your full
account name |
\\ | A literal “\ ” backslash character |
\n | A newline character |
\t | A tab character |
\ | A space (a space follows the backslash) |
\_ | A space |
\R | The current time, in 24-hour military time (0-23) |
\r | The current time, standard 12-hour time (1-12) |
\m | Minutes of the current time |
\y | The current year, two digits |
\Y | The current year, four digits |
\D | The full current date |
\s | Seconds of the current time |
\w | The current day of the week in three-letter format (Mon, Tue, …) |
\P | am/pm |
\o | The current month in numeric format |
\O | The current month in three-letter format (Jan, Feb, …) |
\c | A counter that increments for each statement you issue |
\l | The current delimiter. (New in 5.0.25) |
\S | Semicolon |
\' | 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 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]>
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.2.2.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> "
Set the prompt 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>
mysql> help search_string
If you provide an argument to the help
command, mysql uses it as a search string to
access server-side help from the contents of the MySQL Reference
Manual. The proper operation of this command requires that the
help tables in the mysql
database be
initialized with help topic information (see
Section 5.1.7, “Server-Side Help”).
If there is no match for the search string, the search fails:
mysql> help me
Nothing found
Please try to run 'help contents' for a list of all accessible topics
Use help contents to see a list of the help categories:
mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the
following categories:
Account Management
Administration
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Language Structure
Storage Engines
Stored Routines
Table Maintenance
Transactions
Triggers
If the search string matches multiple items, mysql shows a list of matching topics:
mysql> help logs
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following topics:
SHOW
SHOW BINARY LOGS
SHOW ENGINE
SHOW LOGS
Use a topic as the search string to see the help entry for that topic:
mysql> help show binary logs
Name: 'SHOW BINARY LOGS'
Description:
Syntax:
SHOW BINARY LOGS
SHOW MASTER LOGS
Lists the binary log files on the server. This statement is used as
part of the procedure described in [purge-master-logs], that shows how
to determine which logs can be purged.
mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000015 | 724935 |
| binlog.000016 | 733481 |
+---------------+-----------+
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
If you place a USE
statement as the
first statement in the file, it is unnecessary to specify the
database name on the command line:
db_name
shell> mysql < text_file
If you are already running mysql, you can
execute an SQL script file using the source
command or \.
command:
mysql>source
mysql>file_name
\.
file_name
Sometimes you may want your script to display progress information to the user. For this you can insert statements like this:
SELECT '<info_to_display>' AS ' ';
The statement shown outputs
<info_to_display>
.
As of MySQL 5.0.54, mysql ignores Unicode
byte order mark (BOM) characters at the beginning of input
files. Previously, it read them and sent them to the server,
resulting in a syntax error. Presence of a BOM does not cause
mysql to change its default character set. To
do that, invoke mysql with an option such as
--default-character-set=utf8
.
For more information about batch mode, see Section 3.5, “Using mysql in Batch Mode”.
This section describes some techniques that can help you use mysql more effectively.
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)
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
statement but
forgotten the tbl_name
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 12.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:
UPDATEtbl_name
SETnot_key_column
=val
WHEREkey_column
=val
; UPDATEtbl_name
SETnot_key_column
=val
LIMIT 1;
The server limits all large SELECT
results to 1,000 rows unless the statement includes a
LIMIT
clause.
The server aborts multiple-table SELECT
statements that probably need to examine more than
1,000,000 row combinations.
To specify limits different from 1,000 and 1,000,000, you can
override the defaults by using the
--select_limit
and
--max_join_size
options:
shell> mysql --safe-updates --select_limit=500 --max_join_size=10000
If the mysql client loses its connection to the server while sending a statement, it immediately and automatically tries to reconnect once to the server and send the statement 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-defined and session variables. Also, any current transaction rolls back. This behavior may be dangerous for you, as in the following example where the server was shut down and restarted between the first and second statements 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.
For more information about auto-reconnect and its effect on state information when a reconnection occurs, see Section 23.2.13, “Controlling Automatic Reconnect Behavior”.
mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more.
Invoke mysqladmin like this:
shell> mysqladmin [options
] command
[command-arg
] [command
[command-arg
]] ...
mysqladmin supports the commands described in the following list. Some of the commands take an argument following the command name.
Create a new database named
db_name
.
Tell the server to write debug information to the error log.
Delete the database named db_name
and all its tables.
Display the server status variables and their values.
MySQL Enterprise For expert advice on using server status variables, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.
Flush all information in the host cache.
Flush all logs.
Reload the grant tables (same as reload
).
Clear status variables.
Flush all tables.
Flush the thread cache.
Kill server threads. If multiple thread ID values are given, there must be no spaces in the list.
This is like the password
command but
stores the password using the old (pre-4.1) password-hashing
format. (See Section 5.4.9, “Password Hashing as of MySQL 4.1”.)
MySQL Enterprise
For expert advice on the security implications of using
the old-password
command, subscribe to
the MySQL Enterprise Monitor. For more information see
http://www.mysql.com/products/enterprise/advisors.html.
Set a new password. This changes the password to
new-password
for the account that
you use with mysqladmin for connecting to
the server. Thus, the next time you invoke
mysqladmin (or any other client program)
using the same account, you will need to specify the new
password.
If the new-password
value
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"
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
different from the server not running.
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 12.5.4.21, “SHOW PROCESSLIST
Syntax”.)
Reload the grant tables.
Flush all tables and close and open log files.
Stop the server.
Start replication on a slave server.
Display a short server status message.
Stop replication on a slave server.
Display the server system variables and their values.
MySQL Enterprise For expert advice on using server system variables, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.
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:
The number of seconds the MySQL server has been running.
The number of active threads (clients).
The number of questions (queries) from clients since the server was started.
The number of queries that have taken more than
long_query_time
seconds. See
Section 5.2.4, “The Slow Query Log”.
The number of tables the server has opened.
The number of flush-*
,
refresh
, and reload
commands the server has executed.
The number of tables that currently are open.
The amount of memory allocated directly by
mysqld. This value is displayed only when
MySQL has been compiled with
--with-debug=full
.
The maximum amount of memory allocated directly by
mysqld. 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:
Table 4.3. mysqladmin Option Reference
Format | Config File | Description | Introduced |
---|---|---|---|
--compress | compress | Compress all information sent between the client and the server | |
-C | |||
--connect_timeout=seconds | connect_timeout | The number of seconds before connection timeout | |
--count=# | count | The number of iterations to make for repeated command execution | |
-c N | |||
-# [debug_options] | debug | Write a debugging log | |
--debug[=debug_options] | |||
--default-character-set=charset_name | default-character-set | Use charset_name as the default character set | |
--force | force | Continue even if an SQL error occurs | |
-f | |||
--help | Display help message and exit | ||
-? | |||
--host=host_name | host | Connect to the MySQL server on the given host | |
-h host_name | |||
--password[=password] | password | The password to use when connecting to the server | |
-p[password] | |||
--port=port_num | port | The TCP/IP port number to use for the connection | |
-P port_num | |||
--protocol={TCP|SOCKET|PIPE|MEMORY} | protocol | The connection protocol to use | |
--r | relative | Show the difference between the current and previous values when used with the --sleep option | |
--relative | |||
--shutdown_timeout=seconds | shutdown_timeout | The maximum number of seconds to wait for server shutdown | |
--silent | silent | Silent mode | |
-s | |||
--sleep=delay | sleep | Execute commands repeatedly, sleeping for delay seconds in between | |
-s delay | |||
--socket=path | socket | For connections to localhost | |
-S path | |||
--ssl-ca=file_name | ssl-ca | The path to a file that contains a list of trusted SSL CAs | |
--ssl-capath=directory_name | ssl-capath | The path to a directory that contains trusted SSL CA certificates in PEM format | |
--ssl-cert=file_name | ssl-cert | The name of the SSL certificate file to use for establishing a secure connection | |
--ssl-cipher=cipher_list | ssl-cipher | A list of allowable ciphers to use for SSL encryption | |
--ssl-key=file_name | ssl-key | The name of the SSL key file to use for establishing a secure connection | |
--ssl-verify-server-cert | ssl-verify-server-cert | The server's Common Name value in its certificate is verified against the hostname used when connecting to the server | |
--user=user_name, | user | The MySQL username to use when connecting to the server | |
-u user_name | |||
--verbose | Verbose mode | ||
-v | |||
--version | Display version information and exit | ||
-V | |||
--vertical | vertical | Print query output rows vertically (one line per column value) | |
-E | |||
--wait | wait | If the connection cannot be established, wait and retry instead of aborting | |
-w |
Display a help message and exit.
The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.
Compress all information sent between the client and the server if both support compression.
The number of iterations to make for repeated command
execution. This works only with the --sleep
option.
--debug[=
,
debug_options
]-#
[
debug_options
]
Write a debugging log. The
debug_options
string often is
'd:t:o,
.
The default is
file_name
''d:t:o,/tmp/mysqladmin.trace'
.
--default-character-set=
charset_name
Use charset_name
as the default
character set. See Section 9.2, “The Character Set Used for Data and Sorting”.
Do not ask for confirmation for the drop
command. With
multiple commands, continue even if an error occurs.
db_name
--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.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
The TCP/IP port number to use for the connection.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use.
Show the difference between the current and previous values
when used with the --sleep
option.
Currently, this option works only with the
extended-status
command.
Exit silently if a connection to the server cannot be established.
Execute commands repeatedly, sleeping for
delay
seconds in between. The
--count
option determines the number of
iterations.
For connections to localhost
, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
Options that begin with --ssl
specify
whether to connect to the server via SSL and indicate where
to find SSL keys and certificates. See
Section 5.5.7.3, “SSL Command Options”.
--user=
,
user_name
-u
user_name
The MySQL username to use when connecting to the server.
Verbose mode. Print more information about what the program does.
Display version information and exit.
Print output vertically. This is similar to
--relative
, but prints output vertically.
If the connection cannot be established, wait and retry
instead of aborting. If a count
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
--
syntax:
var_name
=value
It is also possible to set variables by using
--set-variable=
or var_name
=value
-O
syntax. This syntax is deprecated.
var_name
=value
The mysqlcheck client checks, repairs, optimizes, and analyzes 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,
and then sends the statements to the server to be executed. For
details about which storage engines each statement works with,
see the descriptions for those statements in
Chapter 12, SQL Statement Syntax.
The MyISAM
storage engine supports all four
statements, so mysqlcheck can be used to
perform all four operations on MyISAM
tables.
Other storage engines do not necessarily support all operations.
In such cases, an error message is displayed. For example, if
test.t
is a MEMORY
table,
an attempt to check it produces this result:
shell> mysqlcheck test t
test.t
note : The storage engine for the table doesn't support check
It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to filesystem errors.
There are three general ways to invoke mysqlcheck:
shell>mysqlcheck [
shell>options
]db_name
[tables
]mysqlcheck [
shell>options
] --databasesdb_name1
[db_name2
db_name3
...]mysqlcheck [
options
] --all-databases
If you do not name any tables following
db_name
or if you use the
--databases
or --all-databases
option, entire databases are checked.
mysqlcheck has a special feature compared to
other client programs. 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.
The following names can be used to change mysqlcheck default behavior:
mysqlrepair | The default option is --repair |
mysqlanalyze | The default option is --analyze |
mysqloptimize | The default option is --optimize |
mysqlcheck supports the following options:
Table 4.4. mysqlcheck Option Reference
Format | Config File | Description | Introduced |
---|---|---|---|
--all-databases | all-databases | Check all tables in all databases | |
-A | |||
--all-in-1 | all-in-1 | Execute a single statement for each database that names all the tables from that database | |
-1 | |||
--analyze | analyze | Analyze the tables | |
-a | |||
--auto-repair | auto-repair | If a checked table is corrupted, automatically fix it | |
--character-sets-dir=path | character-sets-dir | The directory where character sets are installed | |
--check | check | Check the tables for errors | |
-c | |||
--check-only-changed | check-only-changed | Check only tables that have changed since the last check | |
-C | |||
--check-upgrade | check-upgrade | Invoke CHECK TABLE with the FOR UPGRADE option | 5.0.19 |
-g | |||
--compress | compress | Compress all information sent between the client and the server | |
-C | |||
--databases | databases | Process all tables in the named databases | |
-B | |||
-# [debug_options] | debug | Write a debugging log | |
--debug[=debug_options] | |||
--default-character-set=charset_name | default-character-set | Use charset_name as the default character set | |
--extended | extended | Check and repair tables | |
-e | |||
--fast | fast | Check only tables that have not been closed properly | |
-F | |||
--force | force | Continue even if an SQL error occurs | |
-f | |||
--help | Display help message and exit | ||
-? | |||
--host=host_name | host | Connect to the MySQL server on the given host | |
-h host_name | |||
--medium-check | medium-check | Do a check that is faster than an --extended operation | |
--optimize | optimize | Optimize the tables | |
-o | |||
--password[=password] | password | The password to use when connecting to the server | |
-p[password] | |||
--port=port_num | port | The TCP/IP port number to use for the connection | |
-P port_num | |||
--protocol={TCP|SOCKET|PIPE|MEMORY} | protocol | The connection protocol to use | |
--quick | quick | The fastest method of checking | |
-q | |||
--repair | repair | Perform a repair that can fix almost anything except unique keys that are not unique | |
-r | |||
--silent | silent | Silent mode | |
-s | |||
--socket=path | socket | For connections to localhost | |
-S path | |||
--ssl-ca=file_name | ssl-ca | The path to a file that contains a list of trusted SSL CAs | |
--ssl-capath=directory_name | ssl-capath | The path to a directory that contains trusted SSL CA certificates in PEM format | |
--ssl-cert=file_name | ssl-cert | The name of the SSL certificate file to use for establishing a secure connection | |
--ssl-cipher=cipher_list | ssl-cipher | A list of allowable ciphers to use for SSL encryption | |
--ssl-key=file_name | ssl-key | The name of the SSL key file to use for establishing a secure connection | |
--ssl-verify-server-cert | ssl-verify-server-cert | The server's Common Name value in its certificate is verified against the hostname used when connecting to the server | |
--tables | tables | Overrides the --databases or -B option | |
--use-frm | use-frm | For repair operations on MyISAM tables | |
--user=user_name, | user | The MySQL username to use when connecting to the server | |
-u user_name | |||
--verbose | Verbose mode | ||
-v | |||
--version | Display version information and exit | ||
-V |
Display a help message and exit.
Check all tables in all databases. This is the same as using
the --databases
option and naming all the
databases on the command line.
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 the tables.
MySQL Enterprise For expert advice on optimizing tables, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.
If a checked table is corrupted, automatically fix it. Any necessary repairs are done after all tables have been checked.
The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.
Check the tables for errors. This is the default operation.
Check only tables that have changed since the last check or that have not been closed properly.
Invoke CHECK TABLE
with the FOR
UPGRADE
option to check tables for
incompatibilities with the current version of the server.
This option was added in MySQL 5.0.19.
Compress all information sent between the client and the server if both support compression.
Process all tables in the named databases. Normally, mysqlcheck 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.
--debug[=
,
debug_options
]-#
[
debug_options
]
Write a debugging log. A typical
debug_options
string is often
'd:t:o,
.
file_name
'
--default-character-set=
charset_name
Use charset_name
as the default
character set. See Section 9.2, “The Character Set Used for Data and Sorting”.
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!
Check only tables that have not been closed properly.
Continue even if an SQL error occurs.
--host=
,
host_name
-h
host_name
Connect to the MySQL server on the given host.
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 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.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
The TCP/IP port number to use for the connection.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use.
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.
Perform a repair that can fix almost anything except unique keys that are not unique.
Silent mode. Print only error messages.
For connections to localhost
, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
Options that begin with --ssl
specify
whether to connect to the server via SSL and indicate where
to find SSL keys and certificates. See
Section 5.5.7.3, “SSL Command Options”.
Overrides the --databases
or
-B
option. All name arguments following the
option are regarded as table names.
For repair operations on MyISAM
tables,
get the table structure from the .frm
file so that the table can be repaired even if the
.MYI
header is corrupted.
--user=
,
user_name
-u
user_name
The MySQL username to use when connecting to the server.
Verbose mode. Print information about the various stages of program operation.
Display version information and exit.
The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.
If you are doing a backup on the server and your tables all are
MyISAM
tables, consider using the
mysqlhotcopy instead because it can
accomplish faster backups and faster restores. See
Section 4.6.8, “mysqlhotcopy — A Database Backup Program”.
There are three general ways to invoke mysqldump:
shell>mysqldump [
shell>options
]db_name
[tables
]mysqldump [
shell>options
] --databasesdb_name1
[db_name2
db_name3
...]mysqldump [
options
] --all-databases
If you do not name any tables following
db_name
or if you use the
--databases
or --all-databases
option, entire databases are dumped.
mysqldump does not dump the
INFORMATION_SCHEMA
database. If you name that
database explicitly on the command line,
mysqldump silently ignores it.
To get a list of the options your version of mysqldump supports, execute mysqldump --help.
Some mysqldump options are shorthand for
groups of other options. --opt
and
--compact
fall into this category. For example,
use of --opt
is the same as specifying
--add-drop-table --add-locks --create-options
--disable-keys --extended-insert --lock-tables --quick
--set-charset
. Note that all of the options that
--opt
stands for also are on by default because
--opt
is on by default.
To reverse the effect of a group option, uses its
--skip-
form
(xxx
--skip-opt
or
--skip-compact
). It is also possible to select
only part of the effect of a group option by following it with
options that enable or disable specific features. Here are some
examples:
To select the effect of --opt
except for
some features, use the --skip
option for
each feature. For example, to disable extended inserts and
memory buffering, use --opt --skip-extended-insert
--skip-quick
. (As of MySQL 5.0,
--skip-extended-insert --skip-quick
is
sufficient because --opt
is on by default.)
To reverse --opt
for all features except
index disabling and table locking, use --skip-opt
--disable-keys --lock-tables
.
When you selectively enable or disable the effect of a group
option, order is important because options are processed first
to last. For example, --disable-keys --lock-tables
--skip-opt
would not have the intended effect; it is
the same as --skip-opt
by itself.
mysqldump can retrieve and dump table
contents row by row, or it can retrieve the entire content from
a table and buffer it in memory before dumping it. Buffering in
memory can be a problem if you are dumping large tables. To dump
tables row by row, use the --quick
option (or
--opt
, which enables --quick
).
The --opt
option (and hence
--quick
) is enabled by default in MySQL
5.0; to enable memory buffering, use
--skip-quick
.
If you are using a recent version of
mysqldump to generate a dump to be reloaded
into a very old MySQL server, you should not use the
--opt
or --extended-insert
option. Use --skip-opt
instead.
Before MySQL 4.1.2, out-of-range numeric values such as
-inf
and inf
, as well as
NaN
(not-a-number) values are dumped by
mysqldump as NULL
. You can
see this using the following sample table:
mysql>CREATE TABLE t (f DOUBLE);
mysql>INSERT INTO t VALUES(1e+111111111111111111111);
mysql>INSERT INTO t VALUES(-1e111111111111111111111);
mysql>SELECT f FROM t;
+------+ | f | +------+ | inf | | -inf | +------+
For this table, mysqldump produces the following data output:
-- -- Dumping data for table `t` -- INSERT INTO t VALUES (NULL); INSERT INTO t VALUES (NULL);
The significance of this behavior is that if you dump and
restore the table, the new table has contents that differ from
the original contents. This problem is fixed as of MySQL 4.1.2;
you cannot insert inf
in the table, so this
mysqldump behavior is only relevant when you
deal with old servers.
mysqldump supports the following options:
Table 4.5. mysqldump Option Reference
Format | Config File | Description | Introduced |
---|---|---|---|
--add-drop-database | add-drop-database | Add a DROP DATABASE statement before each CREATE DATABASE statement | |
--add-drop-table | add-drop-table | Add a DROP TABLE statement before each CREATE TABLE statement | |
--add-locks | add-locks | Surround each table dump with LOCK TABLES and UNLOCK TABLES statements | |
--all-databases | all-databases | Dump all tables in all databases | |
-A | |||
--allow-keywords | allow-keywords | Allow creation of column names that are keywords | |
--all-tablespaces | all-tablespaces | Adds to a table dump all SQL statements needed to create any tablespaces used by an NDB Cluster table | |
-Y | |||
--comments | comments | Add comments to the dump file | |
-i | |||
--compact | compact | Produce less verbose output | |
--compatible=name[,name,...] | compatible | Produce output that is more compatible with other database systems or with older MySQL servers | |
--complete-insert | complete-insert | Use complete INSERT statements that include column names | |
-c | |||
--create-options | create-options | Include all MySQL-specific table options in the CREATE TABLE statements | |
--databases | databases | Dump several databases | |
-D | |||
-# [debug_options] | debug | Write a debugging log | |
--debug[=debug_options] | |||
--debug-info | debug-info | Print debugging information, memory and CPU statistics when the program exits | 5.0.32 |
--delayed-insert | delayed-insert | Write INSERT DELAYED statements rather than INSERT statements | |
--delete-master-logs | delete-master-logs | On a master replication server, delete the binary logs after performing the dump operation | |
--disable-keys | disable-keys | For each table, surround the INSERT statements with disable and enable keys statements | |
-K | |||
--dump-date | dump-date | Include dump date in "Dump completed on" comment if --comments is given | 5.0.52 |
--events | events | Dump events from the dumped databases | |
-E | |||
--extended-insert | extended-insert | Use multiple-row INSERT syntax that include several VALUES lists | |
-e | |||
--fields-enclosed-by=string | fields-enclosed-by | This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--fields-escaped-by | fields-escaped-by | This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--fields-optionally-enclosed-by=string | fields-optionally-enclosed-by | This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--fields-terminated-by=string | fields-terminated-by | This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--lock-all-tables | first-slave | Deprecated. Now renamed to --lock-all-tables | |
--flush-logs | flush-logs | Flush the MySQL server log files before starting the dump | |
-F | |||
--flush-privileges | flush-privileges | Emit a FLUSH PRIVILEGES statement after dumping the mysql database | |
--help | Display help message and exit | ||
-? | |||
--hex-blob | hex-blob | Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263) | |
--ignore-table=db_name.tbl_name | ignore-table | Do not dump the given table | |
--insert-ignore | insert-ignore | Write INSERT statements with the IGNORE option | |
--lines-terminated-by=string | lines-terminated-by | This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--lock-all-tables | lock-all-tables | Lock all tables across all databases | |
-x | |||
--lock-tables | lock-tables | Lock all tables before dumping them | |
--log-error=file_name | log-error | Append warnings and errors to the named file | 5.0.42 |
--master-data[=value] | master-data | Write the binary log filename and position to the output | |
--max_allowed_packet=value | max_allowed_packet | The maximum packet length to send to or receive from the server | |
--net_buffer_length=value | net_buffer_length | The buffer size for TCP/IP and socket communication | |
--no-autocommit | no-autocommit | Enclose the INSERT statements for each dumped table within SET AUTOCOMMIT=0 and COMMIT statements | |
--no-create-db | no-create-db | This option suppresses the CREATE DATABASE statements | |
-n | |||
--no-create-info | no-create-info | Do not write CREATE TABLE statements that re-create each dumped table | |
-t | |||
--no-data | no-data | Do not write any table row information (that is, do not dump table contents) | |
-d | |||
--no-set-names | no-set-names | Turn off complete-insert | |
--opt | 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. | |
--order-by-primary | order-by-primary | Sorts each table's rows by its primary key, or by its first unique index | |
--quick | quick | Retrieve rows for a table from the server a row at a time | |
-q | |||
--quote-names | quote-names | Quote database, table, and column names within backtick characters | |
-Q | |||
--replace | replace | Write REPLACE statements rather than INSERT statements | |
--result-file=file | result-file | Direct output to a given file | |
-r file | |||
--routines | routines | Dump stored routines (functions and procedures) from the dumped databases | |
-R | |||
--set-charset | set-charset | Add SET NAMES default_character_set to the output | |
--single-transaction | single-transaction | This option issues a BEGIN SQL statement before dumping data from the server | |
--skip-add-drop-table | skip-add-drop-table | Do not add | |
--skip-add-locks | skip-add-locks | Do not add locks | |
--skip-comments | skip-comments | Do not add comments to the dump file | |
--skip-compact | skip-compact | Turn off compact | |
--skip-disable-keys | skip-disable-keys | Do not disable keys | |
--skip-extended-insert | skip-extended-insert | Turn off extended-insert | |
--skip-opt | skip-opt | Turn off the options set by opt | |
--skip-quick | skip-quick | Do not retrieve rows for a table from the server a row at a time | |
--skip-quote-names | skip-quote-names | Turn off quote names | |
-skip-charset | skip-set-charset | Suppress the SET NAMES statement | |
--skip-triggers | skip-triggers | Turn off triggers | 5.0.11 |
--skip-tz-utc | skip-tz-utc | Turn off tz-utc | |
--ssl-ca=file_name | ssl-ca | The path to a file that contains a list of trusted SSL CAs | |
--ssl-capath=directory_name | ssl-capath | The path to a directory that contains trusted SSL CA certificates in PEM format | |
--ssl-cert=file_name | ssl-cert | The name of the SSL certificate file to use for establishing a secure connection | |
--ssl-cipher=cipher_list | ssl-cipher | A list of allowable ciphers to use for SSL encryption | |
--ssl-key=file_name | ssl-key | The name of the SSL key file to use for establishing a secure connection | |
--ssl-verify-server-cert | ssl-verify-server-cert | The server's Common Name value in its certificate is verified against the hostname used when connecting to the server | |
--tab=path | tab | Produce tab-separated data files | |
-T path | |||
--tables | tables | Override the --databases or -B option | |
--triggers | triggers | Dump triggers for each dumped table | |
--tz-utc | tz-utc | Add SET TIME_ZONE='+00:00' to the dump file | |
--verbose | Verbose mode | ||
-v | |||
--version | Display version information and exit | ||
-V | |||
--where='where_condition' | where | Dump only rows selected by the given WHERE condition | |
-w 'where_condition' | |||
--xml | xml | Produce XML output | |
-X |
Display a help message and exit.
Add a DROP DATABASE
statement before each
CREATE DATABASE
statement.
Add a DROP TABLE
statement before each
CREATE TABLE
statement.
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.17, “Speed of INSERT
Statements”.
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 creation of column names that are keywords. This works by prefixing each column name with the table name.
The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.
Write additional information in the dump file such as
program version, server version, and host. This option is
enabled by default. To suppress this additional information,
use --skip-comments
.
Produce less verbose output. This option enables the
--skip-add-drop-table
,
--skip-add-locks
,
--skip-comments
,
--skip-disable-keys
, and
--skip-set-charset
options.
Prior to release 5.0.48, this option did not create valid
SQL if the database dump contained views. The recreation
of views requires the creation and removal of temporary
tables and this option suppressed the removal of those
temporary tables. As a workaround, use
--compact
with the
--add-drop-table
option and then manually
adjust the dump file.
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.1.6, “SQL Modes”.
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.
This option requires a server version of 4.1.0 or higher. With older servers, it does nothing.
Use complete INSERT
statements that
include column names.
Compress all information sent between the client and the server if both support compression.
Include all MySQL-specific table options in the
CREATE TABLE
statements.
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
and USE
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,
.
The default value is
file_name
''d:t:o,/tmp/mysqldump.trace'
.
Print debugging information and memory and CPU usage statistics when the program exits. This option was added in MySQL 5.0.32.
--default-character-set=
charset_name
Use charset_name
as the default
character set. See Section 9.2, “The Character Set Used for Data and Sorting”.
If no character set is specified,
mysqldump uses utf8
,
and earlier versions use latin1
.
Write INSERT DELAYED
statements rather
than INSERT
statements.
On a master replication server, delete the binary logs after
performing the dump operation. This option automatically
enables --master-data
.
For each table, surround the INSERT
statements with /*!40000 ALTER TABLE
and tbl_name
DISABLE KEYS
*/;/*!40000 ALTER TABLE
statements. This makes loading the dump file
faster because the indexes are created after all rows are
inserted. This option is effective only for non-unique
indexes of tbl_name
ENABLE KEYS
*/;MyISAM
tables.
mysqldump produces a -- Dump
completed on
comment at the end of the dump if the
DATE
--comments
option is given. However, the
date causes dump files for identical data take at different
times to appear to be different.
--dump-date
and
--skip-dump-date
control whether the date
is added to the comment. The default is
--dump-date
(include the date in the
comment). --skip-dump-date
suppresses date
printing. This option was added in MySQL 5.0.52.
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=...
These options are used with the -T
option
and have the same meaning as the corresponding clauses for
LOAD DATA INFILE
. See
Section 12.2.5, “LOAD DATA INFILE
Syntax”.
Deprecated. Now renamed to
--lock-all-tables
.
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
.
Emit a FLUSH PRIVILEGES
statement after
dumping the mysql
database. This option
should be used any time the dump contains the
mysql
database and any other database
that depends on the data in the mysql
database for proper restoration. This option was added in
MySQL 5.0.26.
Continue even if an SQL error occurs during a table dump.
One use for this option is to cause
mysqldump to continue executing even when
it encounters a view that has become invalid because the
defintion refers to a table that has been dropped. Without
--force
, mysqldump exits
with an error message. With --force
,
mysqldump prints the error message, but
it also writes a SQL comment containing the view definition
to the dump output and continues executing.
--host=
,
host_name
-h
host_name
Dump data from the MySQL server on the given host. The
default host is localhost
.
Dump binary columns using hexadecimal notation (for example,
'abc'
becomes
0x616263
). The affected data types are
BINARY
, VARBINARY
, and
BLOB
. As of MySQL 5.0.13,
BIT
columns are affected as well.
--ignore-table=
db_name.tbl_name
Do not dump the given table, which must be specified using both the database and table names. To ignore multiple tables, use this option multiple times.
Write INSERT
statements with the
IGNORE
option.
This option is used with the -T
option and
has the same meaning as the corresponding clause for
LOAD DATA INFILE
. See
Section 12.2.5, “LOAD DATA INFILE
Syntax”.
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 all tables before dumping them. 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. Therefore, 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.
Append warnings and errors to the named file. This option was added in MySQL 5.0.42.
Write the binary log filename and position 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. If the dump is from a master
server and you use it to set up a slave server, the
CHANGE MASTER
statement causes the slave
to start from the correct position in the master's binary
logs. If the option value is equal to 2, the CHANGE
MASTER
statement is written as an SQL comment. If
the value is not specified, then the default value is 1.
The --master-data
option automatically
turns off --lock-tables
. It also turns on
--lock-all-tables
, unless
--single-transaction
also is specified (in
which case, a global read lock is acquired only for 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.
Enclose the INSERT
statements for each
dumped table within SET AUTOCOMMIT=0
and
COMMIT
statements.
This option suppresses the CREATE
DATABASE
statements that are otherwise included in
the output if the --databases
or
--all-databases
option is given.
Do not write CREATE TABLE
statements that
re-create each dumped table.
Do not write any table row information (that is, do not dump
table contents). This is very useful if you want to dump
only the CREATE TABLE
statement for the
table.
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.
The --opt
option is enabled by
default. Use --skip-opt
to disable
it. See the discussion at the beginning of this
section for information about selectively enabling or
disabling certain of the options affected by
--opt
.
Sorts each table's rows by its primary key, or by its first
unique index, if such an index exists. This is useful when
dumping a MyISAM
table to be loaded into
an InnoDB
table, but will make the dump
itself take considerably longer.
--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.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
The TCP/IP port number to use for the connection.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use.
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 database, table, and column names within
“`
” characters. If the
ANSI_QUOTES
SQL mode is enabled, names
are quoted within “"
”
characters. This option is enabled 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
.
Direct output to a given file. This option should be used on
Windows to prevent newline
“\n
” characters from being
converted to “\r\n
” carriage
return/newline sequences. The result file is created and its
contents overwritten, even if an error occurs while
generating the dump. The previous contents are lost.
Dump stored routines (functions and procedures) from the
dumped databases. Use of this option requires the
SELECT
privilege for the
mysql.proc
table. The output generated by
using --routines
contains CREATE
PROCEDURE
and CREATE FUNCTION
statements to re-create the routines. However, these
statements do not include attributes such as the routine
creation and modification timestamps. This means that when
the routines are reloaded, they will be created with the
timestamps equal to the reload time.
If you require routines to be re-created with their original
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. Routine DEFINER
values are not dumped until MySQL 5.0.20. This means that
before 5.0.20, when routines are reloaded, they will be
created with the definer set to the reloading user. If you
require routines to be re-created with their original
definer, dump and load the contents of the
mysql.proc
table directly as described
earlier.
Add SET NAMES
to the output. This option is enabled by default. To
suppress the default_character_set
SET NAMES
statement, use
--skip-set-charset
.
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
MEMORY
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.
While a --single-transaction
dump is in
process, to ensure a valid dump file (correct table contents
and binary log position), no other connection should use the
following statements: ALTER TABLE
,
DROP TABLE
, RENAME
TABLE
, TRUNCATE TABLE
. A
consistent read is not isolated from those statements, so
use of them on a table to be dumped can cause the
SELECT
performed by
mysqldump to retrieve the table contents
to obtain incorrect contents or fail.
This option is not supported for MySQL Cluster tables; the
results cannot be guaranteed to be consistent due to the
fact that the NDBCluster
storage engine
supports only the READ_COMMITTED
transaction isolation level. You should always use
NDB
backup and restore instead.
To dump large tables, you should combine this option with
--quick
.
See the description for the --comments
option.
See the description for the --opt
option.
For connections to localhost
, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
Options that begin with --ssl
specify
whether to connect to the server via SSL and indicate where
to find SSL keys and certificates. See
Section 5.5.7.3, “SSL Command Options”.
Produce tab-separated data files. For each dumped table,
mysqldump creates a
file that contains the tbl_name
.sqlCREATE TABLE
statement that creates the table, and a
file that contains its data. The option value is the
directory in which to write the files.
tbl_name
.txt
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-
and
xxx
--lines-terminated-by
options.
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.
Override the --databases
or
-B
option. mysqldump
regards all name arguments following the option as table
names.
Dump triggers for each dumped table. This option is enabled
by default; disable it with
--skip-triggers
. This option was added in
MySQL 5.0.11. Before that, triggers are not dumped.
This option enables TIMESTAMP
columns to
be dumped and reloaded between servers in different time
zones. mysqldump sets its connection time
zone to UTC and adds SET
TIME_ZONE='+00:00'
to the dump file. Without this
option, TIMESTAMP
columns are dumped and
reloaded in the time zones local to the source and
destination servers, which can cause the values to change.
--tz-utc
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 mode. Print more information about what the program does.
Display version information and exit.
--where='
,
where_condition
'-w
'
where_condition
'
Dump only rows selected by the given
WHERE
condition. Quotes around the
condition are mandatory if it contains spaces or other
characters that are special to your command interpreter.
Examples:
--where="user='jimf'" -w"userid>1" -w"userid<1"
Write dump output as well-formed XML.
NULL
,
'NULL'
, and Empty Values: For
some column named column_name
,
the NULL
value, an empty string, and the
string value 'NULL'
are distinguished
from one another in the output generated by this option as
follows:
Value: | XML Representation: |
NULL (unknown value) | <field name=" |
'' (empty string) | <field
name=" |
'NULL' (string value) | <field
name=" |
Beginning with MySQL 5.0.26, the output from the
mysql client when run using the
--xml
option also follows these rules. (See
Section 4.5.1.1, “mysql Options”.)
Beginning with MySQL 5.0.40, XML output from mysqldump includes the XML namespace, as shown here:
shell>mysqldump --xml -u root world City
<?xml version="1.0"?> <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <database name="world"> <table_structure name="City"> <field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" /> <field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" /> <field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" /> <field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" /> <field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" /> <key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID" Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" /> <options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079" Avg_row_length="67" Data_length="27329 3" Max_data_length="18858823439613951" Index_length="43008" Data_free="0" Auto_increment="4080" Create_time="2007-03-31 01:47:01" Updat e_time="2007-03-31 01:47:02" Collation="latin1_swedish_ci" Create_options="" Comment="" /> </table_structure> <table_data name="City"> <row> <field name="ID">1</field> <field name="Name">Kabul</field> <field name="CountryCode">AFG</field> <field name="District">Kabol</field> <field name="Population">1780000</field> </row>...
<row> <field name="ID">4079</field> <field name="Name">Rafah</field> <field name="CountryCode">PSE</field> <field name="District">Rafah</field> <field name="Population">92020</field> </row> </table_data> </database> </mysqldump>
You can also set the following variables by using
--
syntax:
var_name
=value
The maximum size of the buffer for client/server communication. The maximum is 1GB.
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=
or var_name
=value
-O
syntax. This syntax is deprecated.
var_name
=value
The most common use of mysqldump is probably for making a backup of an entire database:
shell> mysqldump 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
To dump all databases, use the --all-databases
option:
shell> mysqldump --all-databases > all_databases.sql
For InnoDB
tables,
mysqldump
provides a way of making an online
backup:
shell> mysqldump --all-databases --single-transaction > all_databases.sql
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 the lock is
released. If and only if one long updating statement is running
when the FLUSH
statement is issued, the MySQL
server may get stalled until that long statement finishes, and
then the dump becomes lock-free. If the update statements that
the MySQL server receives are short (in terms of execution
time), the initial lock period should not be noticeable, even
with many updates.
For point-in-time recovery (also known as “roll-forward,” when you need to restore an old backup and replay the changes that happened since that backup), it is often useful to rotate the binary log (see Section 5.2.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 --master-data
and
--single-transaction
options can be used
simultaneously, which provides a convenient way to make an
online backup suitable for point-in-time recovery if tables are
stored using the InnoDB
storage engine.
For more information on making backups, see Section 6.1, “Database Backups”, and Section 6.2, “Example Backup and Recovery Strategy”.
If you encounter problems backing up views, please read the section that covers restrictions on views which describes a workaround for backing up views when this fails due to insufficient privileges. See Section F.4, “Restrictions on Views”.
MySQL Enterprise MySQL Enterprise subscribers will find more information about mysqldump in the Knowledge Base article, How Can I Avoid Inserting Duplicate Rows From a Dump File?. Access to the MySQL Knowledge Base collection of articles is one of the advantages of subscribing to MySQL Enterprise. For more information see http://www.mysql.com/products/enterprise/advisors.html.
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
syntax. See
Section 12.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:
Table 4.6. mysqlimport Option Reference
Format | Config File | Description | Introduced |
---|---|---|---|
--columns=column_list | columns | This option takes a comma-separated list of column names as its value | |
-c columns_list | |||
--compress | compress | Compress all information sent between the client and the server | |
-C | |||
-# [debug_options] | debug | Write a debugging log | |
--debug[=debug_options] | |||
--default-character-set=charset_name | default-character-set | Use charset_name as the default character set | |
--delete | delete | Empty the table before importing the text file | |
-D | |||
--fields-enclosed-by=string | fields-enclosed-by | This option has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--fields-escaped-by | fields-escaped-by | This option has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--fields-optionally-enclosed-by=string | fields-optionally-enclosed-by | This option has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--fields-terminated-by=string | fields-terminated-by | -- This option has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--force | force | Continue even if an SQL error occurs | |
-f | |||
--help | Display help message and exit | ||
-? | |||
--host=host_name | host | Connect to the MySQL server on the given host | |
-h host_name | |||
--ignore | ignore | See the description for the --replace option | |
-i | |||
--ignore-lines=# | ignore-lines | Ignore the first N lines of the data file | |
--lines-terminated-by=string | lines-terminated-by | This option has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--local | local | Read input files locally from the client host | |
-L | |||
--lock-tables | lock-tables | Lock all tables for writing before processing any text files | |
-l | |||
--low-priority | low-priority | Use LOW_PRIORITY when loading the table. | |
--password[=password] | password | The password to use when connecting to the server | |
-p[password] | |||
--port=port_num | port | The TCP/IP port number to use for the connection | |
-P port_num | |||
--protocol={TCP|SOCKET|PIPE|MEMORY} | protocol | The connection protocol to use | |
--replace | replace | The --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values | |
--silent | silent | Produce output only when errors occur | |
-s | |||
--socket=path | socket | For connections to localhost | |
-S path | |||
--ssl-ca=file_name | ssl-ca | The path to a file that contains a list of trusted SSL CAs | |
--ssl-capath=directory_name | ssl-capath | The path to a directory that contains trusted SSL CA certificates in PEM format | |
--ssl-cert=file_name | ssl-cert | The name of the SSL certificate file to use for establishing a secure connection | |
--ssl-cipher=cipher_list | ssl-cipher | A list of allowable ciphers to use for SSL encryption | |
--ssl-key=file_name | ssl-key | The name of the SSL key file to use for establishing a secure connection | |
--ssl-verify-server-cert | ssl-verify-server-cert | The server's Common Name value in its certificate is verified against the hostname used when connecting to the server | |
--user=user_name, | user | The MySQL username to use when connecting to the server | |
-u user_name | |||
--verbose | Verbose mode | ||
-v | |||
--version | Display version information and exit | ||
-V |
Display a help message and exit.
The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.
--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 data file columns with table columns.
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_name
Use charset_name
as the default
character set. See Section 9.2, “The Character Set Used for Data and Sorting”.
Empty the table before importing the text file.
--fields-terminated-by=...
,
--fields-enclosed-by=...
,
--fields-optionally-enclosed-by=...
,
--fields-escaped-by=...
These options have the same meaning as the corresponding
clauses for LOAD DATA INFILE
. See
Section 12.2.5, “LOAD DATA INFILE
Syntax”.
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
.
See the description for the --replace
option.
Ignore the first N
lines of the
data file.
This option has the same meaning as the corresponding clause
for LOAD DATA INFILE
. For example, to
import Windows files that have lines terminated with
carriage return/linefeed pairs, use
--lines-terminated-by="\r\n"
. (You might
have to double the backslashes, depending on the escaping
conventions of your command interpreter.) See
Section 12.2.5, “LOAD DATA INFILE
Syntax”.
Read input files locally from the client host.
MySQL Enterprise
For expert advice on the security implications of enabling
LOCAL
, subscribe to the MySQL
Enterprise Monitor. For more information see
http://www.mysql.com/products/enterprise/advisors.html.
Lock all tables for writing before processing any text files. This ensures that all tables are synchronized on the server.
Use LOW_PRIORITY
when loading the table.
This affects only storage engines that use only table-level
locking (MyISAM
,
MEMORY
, MERGE
).
--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.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
The TCP/IP port number to use for the connection.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use.
The --replace
and --ignore
options control handling of input rows that duplicate
existing rows 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 mode. Produce output only when errors occur.
For connections to localhost
, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
Options that begin with --ssl
specify
whether to connect to the server via SSL and indicate where
to find SSL keys and certificates. See
Section 5.5.7.3, “SSL Command Options”.
--user=
,
user_name
-u
user_name
The MySQL username to use when connecting to the server.
Verbose mode. Print more information about what the program does.
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 | +------+---------------+
The mysqlshow client can be used to quickly see which databases exist, their tables, or a table's columns or indexes.
mysqlshow provides a command-line interface
to several SQL SHOW
statements. See
Section 12.5.4, “SHOW
Syntax”. The same information can be obtained by
using those statements directly. For example, you can issue them
from the mysql client program.
Invoke mysqlshow like this:
shell> mysqlshow [options
] [db_name
[tbl_name
[col_name
]]]
If no database is given, a list of database names is 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.
The output displays only the names of those databases, 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) 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:
Table 4.7. mysqlimport Option Reference
Format | Config File | Description | Introduced |
---|---|---|---|
--columns=column_list | columns | This option takes a comma-separated list of column names as its value | |
-c columns_list | |||
--compress | compress | Compress all information sent between the client and the server | |
-C | |||
-# [debug_options] | debug | Write a debugging log | |
--debug[=debug_options] | |||
--default-character-set=charset_name | default-character-set | Use charset_name as the default character set | |
--delete | delete | Empty the table before importing the text file | |
-D | |||
--fields-enclosed-by=string | fields-enclosed-by | This option has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--fields-escaped-by | fields-escaped-by | This option has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--fields-optionally-enclosed-by=string | fields-optionally-enclosed-by | This option has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--fields-terminated-by=string | fields-terminated-by | -- This option has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--force | force | Continue even if an SQL error occurs | |
-f | |||
--help | Display help message and exit | ||
-? | |||
--host=host_name | host | Connect to the MySQL server on the given host | |
-h host_name | |||
--ignore | ignore | See the description for the --replace option | |
-i | |||
--ignore-lines=# | ignore-lines | Ignore the first N lines of the data file | |
--lines-terminated-by=string | lines-terminated-by | This option has the same meaning as the corresponding clause for LOAD DATA INFILE | |
--local | local | Read input files locally from the client host | |
-L | |||
--lock-tables | lock-tables | Lock all tables for writing before processing any text files | |
-l | |||
--low-priority | low-priority | Use LOW_PRIORITY when loading the table. | |
--password[=password] | password | The password to use when connecting to the server | |
-p[password] | |||
--port=port_num | port | The TCP/IP port number to use for the connection | |
-P port_num | |||
--protocol={TCP|SOCKET|PIPE|MEMORY} | protocol | The connection protocol to use | |
--replace | replace | The --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values | |
--silent | silent | Produce output only when errors occur | |
-s | |||
--socket=path | socket | For connections to localhost | |
-S path | |||
--ssl-ca=file_name | ssl-ca | The path to a file that contains a list of trusted SSL CAs | |
--ssl-capath=directory_name | ssl-capath | The path to a directory that contains trusted SSL CA certificates in PEM format | |
--ssl-cert=file_name | ssl-cert | The name of the SSL certificate file to use for establishing a secure connection | |
--ssl-cipher=cipher_list | ssl-cipher | A list of allowable ciphers to use for SSL encryption | |
--ssl-key=file_name | ssl-key | The name of the SSL key file to use for establishing a secure connection | |
--ssl-verify-server-cert | ssl-verify-server-cert | The server's Common Name value in its certificate is verified against the hostname used when connecting to the server | |
--user=user_name, | user | The MySQL username to use when connecting to the server | |
-u user_name | |||
--verbose | Verbose mode | ||
-v | |||
--version | Display version information and exit | ||
-V |
Display a help message and exit.
The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.
Compress all information sent between the client and the server if both support compression.
Show the number of rows per table. This can be slow for
non-MyISAM
tables. This option was added
in MySQL 5.0.6.
--debug[=
,
debug_options
]-#
[
debug_options
]
Write a debugging log. The
debug_options
string often is
'd:t:o,
.
file_name
'
--default-character-set=
charset_name
Use charset_name
as the default
character set. See Section 9.2, “The Character Set Used for Data and Sorting”.
--host=
,
host_name
-h
host_name
Connect to the MySQL server on the given host.
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.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
The TCP/IP port number to use for the connection.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use.
Show a column indicating the table type, as in SHOW
FULL TABLES
. The type is BASE
TABLE
or VIEW
. This option was
added in MySQL 5.0.4.
For connections to localhost
, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
Options that begin with --ssl
specify
whether to connect to the server via SSL and indicate where
to find SSL keys and certificates. See
Section 5.5.7.3, “SSL Command Options”.
Display extra information about each table.
--user=
,
user_name
-u
user_name
The MySQL username to use when connecting to the server.
Verbose mode. Print more information about what the program does. This option can be used multiple times to increase the amount of information.
Display version information and exit.
innochecksum prints checksums for
InnoDB
files.
Invoke innochecksum like this:
shell> innochecksum [options
] file_name
innodchecksum understands the options described in the following list. For options that refer to page numbers, the numbers are zero-based.
myisam_ftdump displays information about
FULLTEXT
indexes in MyISAM
tables. It reads the MyISAM
index file
directly, so it must be run on the server host where the table
is located
Invoke myisam_ftdump like this:
shell> myisam_ftdump [options
] tbl_name
index_num
The tbl_name
argument should be the
name of a MyISAM
table. You can also specify
a table by naming its index file (the file with the
.MYI
suffix). If you do not invoke
myisam_ftdump in the directory where the
table files are located, the table or index file name must be
preceded by the pathname to the table's database directory.
Index numbers begin with 0.
Example: Suppose that the test
database
contains a table named mytexttablel
that has
the following definition:
CREATE TABLE mytexttable ( id INT NOT NULL, txt TEXT NOT NULL, PRIMARY KEY (id), FULLTEXT (txt) );
The index on id
is index 0 and the
FULLTEXT
index on txt
is
index 1. If your working directory is the
test
database directory, invoke
myisam_ftdump as follows:
shell> myisam_ftdump mytexttable 1
If the pathname to the test
database
directory is /usr/local/mysql/data/test
,
you can also specify the table name argument using that
pathname. This is useful if you do not invoke
myisam_ftdump in the database directory:
shell> myisam_ftdump /usr/local/mysql/data/test/mytexttable 1
myisam_ftdump understands the following options:
Display a help message and exit.
Calculate per-word statistics (counts and global weights).
Dump the index, including data offsets and word weights.
Report the length distribution.
Report global index statistics. This is the default operation if no other operation is specified.
Verbose mode. Print more output about what the program does.
The myisamchk utility gets information about
your database tables or checks, repairs, or optimizes them.
myisamchk works with
MyISAM
tables (tables that have
.MYD
and .MYI
files
for storing data and indexes).
It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to filesystem errors.
Invoke myisamchk like this:
shell> myisamchk [options
] tbl_name
...
The options
specify what you want
myisamchk to do. They are described in the
following sections. You can also get a list of options by
invoking myisamchk --help.
With no options, myisamchk simply checks your table as the default operation. To get more information or to tell myisamchk to take corrective action, specify options as described in the following discussion.
tbl_name
is the database table you
want to check or repair. If you run myisamchk
somewhere other than in the database directory, you must specify
the path to the database directory, because
myisamchk has no idea where the database is
located. In fact, myisamchk doesn't actually
care whether the files you are working on are located in a
database directory. You can copy the files that correspond to a
database table into some other location and perform recovery
operations on them there.
You can name several tables on the myisamchk
command line if you wish. You can also specify a table by naming
its index file (the file with the .MYI
suffix). This allows you to specify all tables in a directory by
using the pattern *.MYI
. For example, if
you are in a database directory, you can check all the
MyISAM
tables in that directory like this:
shell> myisamchk *.MYI
If you are not in the database directory, you can check all the tables there by specifying the path to the directory:
shell> myisamchk /path/to/database_dir/
*.MYI
You can even check all tables in all databases by specifying a wildcard with the path to the MySQL data directory:
shell> myisamchk /path/to/datadir/*/*
.MYI
The recommended way to quickly check all
MyISAM
tables is:
shell> myisamchk --silent --fast /path/to/datadir/*/*
.MYI
If you want to check all MyISAM
tables and
repair any that are corrupted, you can use the following
command:
shell>myisamchk --silent --force --fast --update-state \
--key_buffer_size=64M --sort_buffer_size=64M \
--read_buffer_size=1M --write_buffer_size=1M \
/path/to/datadir/*/*
.MYI
This command assumes that you have more than 64MB free. For more information about memory allocation with myisamchk, see Section 4.6.3.5, “myisamchk Memory Usage”.
MySQL Enterprise For expert advice on checking and repairing tables, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.
You must ensure that no other program is using the tables while you are running myisamchk. The most effective means of doing so is to shut down the MySQL server while running myisamchk, or to lock all tables that myisamchk is being used on.
Otherwise, when you run myisamchk, it may display the following error message:
warning: clients are using or haven't closed the table properly
This means that you are trying to check a table that has been
updated by another program (such as the
mysqld server) that hasn't yet closed the
file or that has died without closing the file properly, which
can sometimes lead to the corruption of one or more
MyISAM
tables.
If mysqld is running, you must force it to
flush any table modifications that are still buffered in
memory by using FLUSH TABLES
. You should
then ensure that no one is using the tables while you are
running myisamchk
However, the easiest way to avoid this problem is to use
CHECK TABLE
instead of
myisamchk to check tables. See
Section 12.5.2.3, “CHECK TABLE
Syntax”.
A complete listing of all the myisamchk options follows.
Table 4.8. myisamchk Option Reference
Format | Config File | Description | Introduced |
---|---|---|---|
--analyze | analyze | Analyze the distribution of key values | |
--backup | backup | Make a backup of the .MYD file as file_name-time.BAK | |
-B | |||
--block-search=offset | block-search | Find the record that a block at the given offset belongs to | |
-b offset | |||
--check | check | Check the table for errors | |
-c | |||
--check-only-changed | check-only-changed | Check only tables that have changed since the last check | |
--correct-checksum | correct-checksum | Correct the checksum information for the table | |
--data-file-length=len | data-file-length | Maximum length of the data file (when re-creating data file when it is full) | |
-# [debug_options] | debug | Write a debugging log | |
--debug[=debug_options] | |||
decode_bits=# | decode_bits | Decode_bits | |
--default-character-set=charset_name | default-character-set | Use charset_name as the default character set | |
--description | description | Print some descriptive information about the table | |
-d | |||
--extend-check | extend-check | Do a repair that tries to recover every possible row from the data file | |
--extended-check | extended-check | Check the table very thoroughly | |
-e | |||
--fast | fast | Check only tables that haven't been closed properly | |
-F | |||
--force | force | Do a repair operation automatically if myisamchk finds any errors in the table | |
-f | |||
--force | force-recover | Overwrite old temporary files. For use with the -r or -o option | |
-f | |||
ft_max_word_len=# | ft_max_word_len | Maximum word length for FULLTEXT indexes | |
ft_min_word_len=# | ft_min_word_len | Minimum word length for FULLTEXT indexes | |
ft_stopword_file=value | ft_stopword_file | Use stopwords from this file instead of built-in list | |
--help | Display help message and exit | ||
-? | |||
--host=host_name | host | Connect to the MySQL server on the given host | |
-h host_name | |||
--information | information | Print informational statistics about the table that is checked | |
-i | |||
key_buffer_size=# | key_buffer_size | The size of the buffer used for index blocks for MyISAM tables | |
--keys-used=val | keys-used | A bit-value that indicates which indexes to update | |
-k value | |||
--max-record-length=len | max-record-length | Skip rows larger than the given length if myisamchk cannot allocate memory to hold them | |
--medium-check | medium-check | Do a check that is faster than an --extend-check operation | |
-m | |||
myisam_block_size=# | myisam_block_size | Block size to be used for MyISAM index pages | |
--parallel-recover | parallel-recover | Uses the same technique as -r and -n, but creates all the keys in parallel, using different threads (beta) | |
-p | |||
--password[=password] | password | The password to use when connecting to the server | |
-p[password] | |||
--port=port_num | port | The TCP/IP port number to use for the connection | |
-P port_num | |||
--protocol={TCP|SOCKET|PIPE|MEMORY} | protocol | The connection protocol to use | |
--quick | quick | Achieve a faster repair by not modifying the data file. | |
-q | |||
read_buffer_size=# | read_buffer_size | Each thread that does a sequential scan allocates a buffer of this size for each table it scans | |
--read-only | read-only | Don't mark the table as checked | |
-T | |||
--recover | recover | Do a repair that can fix almost any problem except unique keys that aren't unique | |
-r | |||
--safe-recover | safe-recover | Do a repair using an old recovery method that reads through all rows in order and updates all index trees based on the rows found | |
-o | |||
--set-auto-increment[=value] | set-auto-increment | Force AUTO_INCREMENT numbering for new records to start at the given value | |
-A [value] | |||
--set-character-set=name | set-character-set | Change the character set used by the table indexes | |
--set-collation=name | set-collation | Specify the collation to use for sorting table indexes | |
--silent | silent | Silent mode | |
-s | |||
--socket=path | socket | For connections to localhost | |
-S path | |||
sort_buffer_size=# | sort_buffer_size | The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE | |
--sort-index | sort-index | Sort the index tree blocks in high-low order | |
-S | |||
sort_key_blocks=# | sort_key_blocks | sort_key_blocks | |
--sort-records=# | sort-records | Sort records according to a particular index | |
-R N | |||
--sort-recover | sort-recover | Force myisamchk to use sorting to resolve the keys even if the temporary files would be very large | |
-n | |||
stats_method=value | stats_method | Specifies how MyISAM index statistics collection code should treat NULLs | |
--tmpdir=path | tmpdir | Path of the directory to be used for storing temporary files | |
-t path | |||
--unpack | unpack | Unpack a table that was packed with myisampack | |
-u | |||
--update-state | update-state | Store information in the .MYI file to indicate when the table was checked and whether the table crashed | |
-U | |||
--user=user_name, | user | The MySQL username to use when connecting to the server | |
-u user_name | |||
--verbose | Verbose mode | ||
-v | |||
--version | Display version information and exit | ||
-V | |||
write_buffer_size=# | write_buffer_size | Write buffer size |
The options described in this section can be used for any type of table maintenance operation performed by myisamchk. The sections following this one describe options that pertain only to specific operations, such as table checking or repairing.
Display a help message and exit.
--debug=
debug_options
, -#
debug_options
Write a debugging log. The
debug_options
string often is
'd:t:o,
.
file_name
'
Silent mode. Write output only when errors occur. You can
use -s
twice (-ss
) to make
myisamchk very silent.
Verbose mode. Print more information about what the program
does. This can be used with -d
and
-e
. Use -v
multiple times
(-vv
, -vvv
) for even more
output.
Display version information and exit.
Instead of terminating with an error if the table is locked, wait until the table is unlocked before continuing. If you are running mysqld with external locking disabled, the table can be locked only by another myisamchk command.
You can also set the following variables by using
--
syntax:
var_name
=value
Variable | Default Value |
decode_bits | 9 |
ft_max_word_len | version-dependent |
ft_min_word_len | 4 |
ft_stopword_file | built-in list |
key_buffer_size | 523264 |
myisam_block_size | 1024 |
read_buffer_size | 262136 |
sort_buffer_size | 2097144 |
sort_key_blocks | 16 |
stats_method | nulls_unequal |
write_buffer_size | 262136 |
It is also possible to set variables by using
--set-variable=
or var_name
=value
-O
syntax. However, this syntax is deprecated as of MySQL 4.0.
var_name
=value
The possible myisamchk variables and their default values can be examined with myisamchk --help:
sort_buffer_size
is used when the keys are
repaired by sorting keys, which is the normal case when you use
--recover
.
key_buffer_size
is used when you are checking
the table with --extend-check
or when the keys
are repaired by inserting keys row by row into the table (like
when doing normal inserts). Repairing through the key buffer is
used in the following cases:
You use --safe-recover
.
The temporary files needed to sort the keys would be more
than twice as big as when creating the key file directly.
This is often the case when you have large key values for
CHAR
, VARCHAR
, or
TEXT
columns, because the sort operation
needs to store the complete key values as it proceeds. If
you have lots of temporary space and you can force
myisamchk to repair by sorting, you can
use the --sort-recover
option.
Repairing through the key buffer takes much less disk space than using sorting, but is also much slower.
If you want a faster repair, set the
key_buffer_size
and
sort_buffer_size
variables to about 25% of
your available memory. You can set both variables to large
values, because only one of them is used at a time.
myisam_block_size
is the size used for index
blocks.
stats_method
influences how
NULL
values are treated for index statistics
collection when the --analyze
option is given.
It acts like the myisam_stats_method
system
variable. For more information, see the description of
myisam_stats_method
in
Section 5.1.3, “System Variables”, and
Section 7.4.7, “MyISAM
Index Statistics Collection”. For MySQL
5.0, stats_method
was added in
MySQL 5.0.14. For older versions, the statistics collection
method is equivalent to nulls_equal
.
The ft_min_word_len
and
ft_max_word_len
variables are available as of
MySQL 4.0.0. ft_stopword_file
is available as
of MySQL 4.0.19.
ft_min_word_len
and
ft_max_word_len
indicate the minimum and
maximum word length for FULLTEXT
indexes.
ft_stopword_file
names the stopword file.
These need to be set under the following circumstances.
If you use myisamchk to perform an operation
that modifies table indexes (such as repair or analyze), the
FULLTEXT
indexes are rebuilt using the
default full-text parameter values for minimum and maximum word
length and the stopword file unless you specify otherwise. This
can result in queries failing.
The problem occurs because these parameters are known only by
the server. They are not stored in MyISAM
index files. To avoid the problem if you have modified the
minimum or maximum word length or the stopword file in the
server, specify the same ft_min_word_len
,
ft_max_word_len
, and
ft_stopword_file
values to
myisamchk that you use for
mysqld. For example, if you have set the
minimum word length to 3, you can repair a table with
myisamchk like this:
shell> myisamchk --recover --ft_min_word_len=3 tbl_name
.MYI
To ensure that myisamchk and the server use
the same values for full-text parameters, you can place each one
in both the [mysqld]
and
[myisamchk]
sections of an option file:
[mysqld] ft_min_word_len=3 [myisamchk] ft_min_word_len=3
An alternative to using myisamchk is to use
the REPAIR TABLE
, ANALYZE
TABLE
, OPTIMIZE TABLE
, or
ALTER TABLE
. These statements are performed
by the server, which knows the proper full-text parameter values
to use.
myisamchk supports the following options for table checking operations:
Check the table for errors. This is the default operation if you specify no option that selects an operation type explicitly.
Check only tables that have changed since the last check.
Check the table very thoroughly. This is quite slow if the table has many indexes. This option should only be used in extreme cases. Normally, myisamchk or myisamchk --medium-check should be able to determine whether there are any errors in the table.
If you are using --extend-check
and have
plenty of memory, setting the
key_buffer_size
variable to a large value
helps the repair operation run faster.
Check only tables that haven't been closed properly.
Do a repair operation automatically if
myisamchk finds any errors in the table.
The repair type is the same as that specified with the
--recover
or -r
option.
Print informational statistics about the table that is checked.
Do a check that is faster than an
--extend-check
operation. This finds only
99.99% of all errors, which should be good enough in most
cases.
Don't mark the table as checked. This is useful if you use myisamchk to check a table that is in use by some other application that doesn't use locking, such as mysqld when run with external locking disabled.
Store information in the .MYI
file to
indicate when the table was checked and whether the table
crashed. This should be used to get full benefit of the
--check-only-changed
option, but you
shouldn't use this option if the mysqld
server is using the table and you are running it with
external locking disabled.
myisamchk supports the following options for table repair operations:
Make a backup of the .MYD
file as
file_name
-time
.BAK
The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.
Correct the checksum information for the table.
--data-file-length=
len
,
-D len
Maximum length of the data file (when re-creating data file when it is “full”).
Do a repair that tries to recover every possible row from the data file. Normally, this also finds a lot of garbage rows. Don't use this option unless you are desperate.
Overwrite old intermediate files (files with names like
)
instead of aborting.
tbl_name
.TMD
For myisamchk, the option value is a bit-value that indicates which indexes to update. Each binary bit of the option value corresponds to a table index, where the first index is bit 0. An option value of 0 disables updates to all indexes, which can be used to get faster inserts. Deactivated indexes can be reactivated by using myisamchk -r.
Do not follow symbolic links. Normally myisamchk repairs the table that a symlink points to. This option does not exist as of MySQL 4.0 because versions from 4.0 on do not remove symlinks during repair operations.
Skip rows larger than the given length if myisamchk cannot allocate memory to hold them.
Uses the same technique as -r
and
-n
, but creates all the keys in parallel,
using different threads. This is beta-quality
code. Use at your own risk!
Achieve a faster repair by not modifying the data file. You can specify this option twice to force myisamchk to modify the original data file in case of duplicate keys.
Do a repair that can fix almost any problem except unique
keys that aren't unique (which is an extremely unlikely
error with MyISAM
tables). If you want to
recover a table, this is the option to try first. You should
try --safe-recover
only if
myisamchk reports that the table can't be
recovered using --recover
. (In the unlikely
case that --recover
fails, the data file
remains intact.)
If you have lots of memory, you should increase the value of
sort_buffer_size
.
Do a repair using an old recovery method that reads through
all rows in order and updates all index trees based on the
rows found. This is an order of magnitude slower than
--recover
, but can handle a couple of very
unlikely cases that --recover
cannot. This
recovery method also uses much less disk space than
--recover
. Normally, you should repair
first with --recover
, and then with
--safe-recover
only if
--recover
fails.
If you have lots of memory, you should increase the value of
key_buffer_size
.
Change the character set used by the table indexes. This
option was replaced by --set-collation
in
MySQL 5.0.3.
Specify the collation to use for sorting table indexes. The character set name is implied by the first part of the collation name. This option was added in MySQL 5.0.3.
Force myisamchk to use sorting to resolve the keys even if the temporary files would be very large.
Path of the directory to be used for storing temporary
files. If this is not set, myisamchk uses
the value of the TMPDIR
environment
variable. tmpdir
can be set to a list of
directory paths that are used successively in round-robin
fashion for creating temporary files. The separator
character between directory names is the colon
(“:
”) on Unix and the
semicolon (“;
”) on Windows,
NetWare, and OS/2.
Unpack a table that was packed with myisampack.
myisamchk supports the following options for actions other than table checks and repairs:
Analyze the distribution of key values. This improves join
performance by enabling the join optimizer to better choose
the order in which to join the tables and which indexes it
should use. To obtain information about the key
distribution, use a myisamchk --description
--verbose tbl_name
command or the SHOW INDEX FROM
statement.
tbl_name
MySQL Enterprise For expert advice on optimizing tables, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.
--block-search=
,
offset
-b
offset
Find the record that a block at the given offset belongs to.
Print some descriptive information about the table.
--set-auto-increment[=
,
value
]-A[
value
]
Force AUTO_INCREMENT
numbering for new
records to start at the given value (or higher, if there are
existing records with AUTO_INCREMENT
values this large). If value
is
not specified, AUTO_INCREMENT
numbers for
new records begin with the largest value currently in the
table, plus one.
Sort the index tree blocks in high-low order. This optimizes seeks and makes table scans that use indexes faster.
Sort records according to a particular index. This makes
your data much more localized and may speed up range-based
SELECT
and ORDER BY
operations that use this index. (The first time you use this
option to sort a table, it may be very slow.) To determine a
table's index numbers, use SHOW INDEX
,
which displays a table's indexes in the same order that
myisamchk sees them. Indexes are numbered
beginning with 1.
If keys are not packed (PACK_KEYS=0
),
they have the same length, so when
myisamchk sorts and moves records, it
just overwrites record offsets in the index. If keys are
packed (PACK_KEYS=1
),
myisamchk must unpack key blocks first,
then re-create indexes and pack the key blocks again. (In
this case, re-creating indexes is faster than updating
offsets for each index.)
Memory allocation is important when you run myisamchk. myisamchk uses no more memory than its memory-related variables are set to. If you are going to use myisamchk on very large tables, you should first decide how much memory you want it to use. The default is to use only about 3MB to perform repairs. By using larger values, you can get myisamchk to operate faster. For example, if you have more than 32MB RAM, you could use options such as these (in addition to any other options you might specify):
shell>myisamchk --sort_buffer_size=16M --key_buffer_size=16M \
--read_buffer_size=1M --write_buffer_size=1M ...
Using --sort_buffer_size=16M
should probably be
enough for most cases.
Be aware that myisamchk uses temporary files
in TMPDIR
. If TMPDIR
points to a memory filesystem, you may easily get out of memory
errors. If this happens, run myisamchk with
the --tmpdir=
option to specify some directory located on a filesystem that
has more space.
path
When repairing, myisamchk also needs a lot of disk space:
Double the size of the data file (the original file and a
copy). This space is not needed if you do a repair with
--quick
; in this case, only the index file
is re-created. This space is needed on the same filesystem
as the original data file! (The copy is created in the same
directory as the original.)
Space for the new index file that replaces the old one. The old index file is truncated at the start of the repair operation, so you usually ignore this space. This space is needed on the same filesystem as the original index file!
When using --recover
or
--sort-recover
(but not when using
--safe-recover
), you need space for a sort
buffer. The following formula yields the amount of space
required:
(largest_key
+row_pointer_length
) ×number_of_rows
× 2
You can check the length of the keys and the
row_pointer_length
with
myisamchk -dv
tbl_name
. This space is
allocated in the temporary directory (specified by
TMPDIR
or
--tmpdir=
).
path
If you have a problem with disk space during repair, you can try
--safe-recover
instead of
--recover
.
myisamlog processes the contents of a
MyISAM
log file.
Invoke myisamlog like this:
shell>myisamlog [
shell>options
] [log_file
[tbl_name
] ...]isamlog [
options
] [log_file
[tbl_name
] ...]
The default operation is update (-u
). If a
recovery is done (-r
), all writes and possibly
updates and deletes are done and errors are only counted. The
default log file name is myisam.log
for
myisamlog and isam.log
for isamlog if no
log_file
argument is given, If tables
are named on the command line, only those tables are updated.
myisamlog understands the following options:
Display a help message and exit.
Execute only N
commands.
Specify the maximum number of open files.
Display extra information before exiting.
Specify the starting offset.
Remove N
components from path.
Perform a recovery operation.
Specify record position file and record position.
Perform an update operation.
Verbose mode. Print more output about what the program does. This option can be given multiple times to produce more and more output.
Specify the write file.
Display version information.
The myisampack utility compresses
MyISAM
tables. myisampack
works by compressing each column in the table separately.
Usually, myisampack packs the data file
40%-70%.
When the table is used later, the server reads into memory the information needed to decompress columns. This results in much better performance when accessing individual rows, because you only have to uncompress exactly one row.
MySQL uses mmap()
when possible to perform
memory mapping on compressed tables. If
mmap()
does not work, MySQL falls back to
normal read/write file operations.
Please note the following:
If the mysqld server was invoked with external locking disabled, it is not a good idea to invoke myisampack if the table might be updated by the server during the packing process. It is safest to compress tables with the server stopped.
After packing a table, it becomes read only. This is generally intended (such as when accessing packed tables on a CD). Allowing writes to a packed table is on our TODO list, but with low priority.
Invoke myisampack like this:
shell> myisampack [options
] file_name
...
Each filename argument should be the name of an index
(.MYI
) file. If you are not in the database
directory, you should specify the pathname to the file. It is
permissible to omit the .MYI
extension.
After you compress a table with myisampack, you should use myisamchk -rq to rebuild its indexes. Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
myisampack supports the following options:
Display a help message and exit.
Make a backup of each table's data file using the name
.
tbl_name
.OLD
The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.
--debug[=
,
debug_options
]-#
[
debug_options
]
Write a debugging log. The
debug_options
string often is
'd:t:o,
.
file_name
'
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
in the database directory while it compresses the table. If
you kill myisampack, the
tbl_name
.TMD.TMD
file might not be deleted.)
Normally, myisampack exits with an error
if it finds that
exists. With tbl_name
.TMD--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).
Silent mode. Write output only when errors occur.
Do not actually pack the table, just test packing it.
Use the named directory as the location where myisampack creates temporary files.
Verbose mode. Write information about the progress of the packing operation and its result.
Display version information and exit.
Wait and retry if the table is in use. If the mysqld server was invoked with external locking disabled, it is not a good idea to invoke myisampack if the table might be updated by the server during the packing process.
The following sequence of commands illustrates a typical table compression session:
shell>ls -l station.*
-rw-rw-r-- 1 monty my 994128 Apr 17 19:00 station.MYD -rw-rw-r-- 1 monty my 53248 Apr 17 19:00 station.MYI -rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm shell>myisamchk -dvv station
MyISAM file: station Isam-version: 2 Creation time: 1996-03-13 10:08:58 Recover time: 1997-02-02 3:06:43 Data records: 1192 Deleted blocks: 0 Datafile parts: 1192 Deleted data: 0 Datafile pointer (bytes): 2 Keyfile pointer (bytes): 2 Max datafile length: 54657023 Max keyfile length: 33554431 Recordlength: 834 Record format: Fixed length table description: Key Start Len Index Type Root Blocksize Rec/key 1 2 4 unique unsigned long 1024 1024 1 2 32 30 multip. text 10240 1024 1 Field Start Length Type 1 1 1 2 2 4 3 6 4 4 10 1 5 11 20 6 31 1 7 32 30 8 62 35 9 97 35 10 132 35 11 167 4 12 171 16 13 187 35 14 222 4 15 226 16 16 242 20 17 262 20 18 282 20 19 302 30 20 332 4 21 336 4 22 340 1 23 341 8 24 349 8 25 357 8 26 365 2 27 367 2 28 369 4 29 373 4 30 377 1 31 378 2 32 380 8 33 388 4 34 392 4 35 396 4 36 400 4 37 404 1 38 405 4 39 409 4 40 413 4 41 417 4 42 421 4 43 425 4 44 429 20 45 449 30 46 479 1 47 480 1 48 481 79 49 560 79 50 639 79 51 718 79 52 797 8 53 805 1 54 806 1 55 807 20 56 827 4 57 831 4 shell>myisampack station.MYI
Compressing station.MYI: (1192 records) - Calculating statistics normal: 20 empty-space: 16 empty-zero: 12 empty-fill: 11 pre-space: 0 end-space: 12 table-lookups: 5 zero: 7 Original trees: 57 After join: 17 - Compressing file 87.14% Remember to run myisamchk -rq on compressed tables shell>ls -l station.*
-rw-rw-r-- 1 monty my 127874 Apr 17 19:00 station.MYD -rw-rw-r-- 1 monty my 55296 Apr 17 19:04 station.MYI -rw-rw-r-- 1 monty my 5767 Apr 17 19:00 station.frm shell>myisamchk -dvv station
MyISAM file: station Isam-version: 2 Creation time: 1996-03-13 10:08:58 Recover time: 1997-04-17 19:04:26 Data records: 1192 Deleted blocks: 0 Datafile parts: 1192 Deleted data: 0 Datafile pointer (bytes): 3 Keyfile pointer (bytes): 1 Max datafile length: 16777215 Max keyfile length: 131071 Recordlength: 834 Record format: Compressed table description: Key Start Len Index Type Root Blocksize Rec/key 1 2 4 unique unsigned long 10240 1024 1 2 32 30 multip. text 54272 1024 1 Field Start Length Type Huff tree Bits 1 1 1 constant 1 0 2 2 4 zerofill(1) 2 9 3 6 4 no zeros, zerofill(1) 2 9 4 10 1 3 9 5 11 20 table-lookup 4 0 6 31 1 3 9 7 32 30 no endspace, not_always 5 9 8 62 35 no endspace, not_always, no empty 6 9 9 97 35 no empty 7 9 10 132 35 no endspace, not_always, no empty 6 9 11 167 4 zerofill(1) 2 9 12 171 16 no endspace, not_always, no empty 5 9 13 187 35 no endspace, not_always, no empty 6 9 14 222 4 zerofill(1) 2 9 15 226 16 no endspace, not_always, no empty 5 9 16 242 20 no endspace, not_always 8 9 17 262 20 no endspace, no empty 8 9 18 282 20 no endspace, no empty 5 9 19 302 30 no endspace, no empty 6 9 20 332 4 always zero 2 9 21 336 4 always zero 2 9 22 340 1 3 9 23 341 8 table-lookup 9 0 24 349 8 table-lookup 10 0 25 357 8 always zero 2 9 26 365 2 2 9 27 367 2 no zeros, zerofill(1) 2 9 28 369 4 no zeros, zerofill(1) 2 9 29 373 4 table-lookup 11 0 30 377 1 3 9 31 378 2 no zeros, zerofill(1) 2 9 32 380 8 no zeros 2 9 33 388 4 always zero 2 9 34 392 4 table-lookup 12 0 35 396 4 no zeros, zerofill(1) 13 9 36 400 4 no zeros, zerofill(1) 2 9 37 404 1 2 9 38 405 4 no zeros 2 9 39 409 4 always zero 2 9 40 413 4 no zeros 2 9 41 417 4 always zero 2 9 42 421 4 no zeros 2 9 43 425 4 always zero 2 9 44 429 20 no empty 3 9 45 449 30 no empty 3 9 46 479 1 14 4 47 480 1 14 4 48 481 79 no endspace, no empty 15 9 49 560 79 no empty 2 9 50 639 79 no empty 2 9 51 718 79 no endspace 16 9 52 797 8 no empty 2 9 53 805 1 17 1 54 806 1 3 9 55 807 20 no empty 3 9 56 827 4 no zeros, zerofill(2) 2 9 57 831 4 no zeros, zerofill(1) 2 9
myisampack displays the following kinds of information:
normal
The number of columns for which no extra packing is used.
empty-space
The number of columns containing values that are only spaces. These occupy one bit.
empty-zero
The number of columns containing values that are only binary zeros. These occupy one bit.
empty-fill
The number of integer columns that do not occupy the full
byte range of their type. These are changed to a smaller
type. For example, a BIGINT
column (eight
bytes) can be stored as a TINYINT
column
(one byte) if all its values are in the range from
-128
to 127
.
pre-space
The number of decimal columns that are stored with leading spaces. In this case, each value contains a count for the number of leading spaces.
end-space
The number of columns that have a lot of trailing spaces. In this case, each value contains a count for the number of trailing spaces.
table-lookup
The column had only a small number of different values,
which were converted to an ENUM
before
Huffman compression.
zero
The number of columns for which all values are zero.
Original trees
The initial number of Huffman trees.
After join
The number of distinct Huffman trees left after joining trees to save some header space.
After a table has been compressed, myisamchk -dvv prints additional information about each column:
Type
The data type. The value may contain any of the following descriptors:
constant
All rows have the same value.
no endspace
Do not store endspace.
no endspace, not_always
Do not store endspace and do not do endspace compression for all values.
no endspace, no empty
Do not store endspace. Do not store empty values.
table-lookup
The column was converted to an ENUM
.
zerofill(
N
)
The most significant N
bytes
in the value are always 0 and are not stored.
no zeros
Do not store zeros.
always zero
Zero values are stored using one bit.
Huff tree
The number of the Huffman tree associated with the column.
Bits
The number of bits used in the Huffman tree.
After you run myisampack, you must run myisamchk to re-create any indexes. At this time, you can also sort the index blocks and create statistics needed for the MySQL optimizer to work more efficiently:
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.
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:
Table 4.9. mysqlaccess Option Reference
Format | Config File | Description | Introduced |
---|---|---|---|
--brief | brief | Generate reports in single-line tabular format | |
-b | |||
--commit | commit | Copy the new access privileges from the temporary tables to the original grant tables | |
--copy | copy | Reload the temporary grant tables from original ones | |
--db=db_name | db | Specify the database name | |
-d db_name | |||
--debug=# | debug | Specify the debug level | |
--help | Display help message and exit | ||
-? | |||
--host=host_name | host | Connect to the MySQL server on the given host | |
-h host_name | |||
--howto | howto | Display some examples that show how to use mysqlaccess | |
--old_server | old_server | Assume that the server is an old MySQL server (prior to MySQL 3.21) | |
--password[=password] | password | The password to use when connecting to the server | |
-p[password] | |||
--plan | plan | Display suggestions and ideas for future releases | |
--preview | preview | Show the privilege differences after making changes to the temporary grant tables | |
--relnotes | relnotes | Display the release notes | |
--rhost=host_name | rhost | Connect to the MySQL server on the given host | |
-H host_name | |||
--rollback | rollback | Undo the most recent changes to the temporary grant tables. | |
--spassword[=password] | spassword | The password to use when connecting to the server as the superuser | |
-P[password] | |||
--superuser=user_name | superuser | Specify the username for connecting as the superuser | |
--table | table | Generate reports in table format | |
-t | |||
--user=user_name, | user | The MySQL username to use when connecting | |
-u user_name | |||
--version | Display version information and exit | ||
-V |
Display a help message and exit.
Generate reports in single-line tabular format.
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.)
Reload the temporary grant tables from original ones.
Specify the database name.
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.
Display some examples that show how to use mysqlaccess.
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.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
Display suggestions and ideas for future releases.
Show the privilege differences after making changes to the temporary grant tables.
Display the release notes.
--rhost=
,
host_name
-H
host_name
Connect to the MySQL server on the given host.
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.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
--superuser=
,
user_name
-U
user_name
Specify the username for connecting as the superuser.
Generate reports in table format.
--user=
,
user_name
-u
user_name
The username to use in the access privileges.
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.
The binary log files that the server generates are written in binary format. To examine these files in text format, use the mysqlbinlog utility. 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.
Invoke mysqlbinlog like this:
shell> mysqlbinlog [options
] log_file
...
For example, to display the contents of the binary log file
named binlog.000003
, use this command:
shell> mysqlbinlog binlog.0000003
The output includes all events contained in
binlog.000003
. Event information includes
the statement executed, the time the statement took, the thread
ID of the client that issued it, the timestamp when it was
executed, and so forth.
The output from mysqlbinlog can be re-executed (for example, by using it as input to mysql) to reapply the statements in the log. This is useful for recovery operations after a server crash. For other usage examples, see the discussion later in this section.
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.
These options are --host
,
--password
, --port
,
--protocol
, --socket
, and
--user
; they are ignored except when you also
use the --read-from-remote-server
option.
Binary logs and relay logs are discussed further in Section 5.2.3, “The Binary Log”, and Section 15.4.2, “Replication Relay and Status Files”.
mysqlbinlog supports the following options:
Table 4.10. mysqlbinlog Option Reference
Format | Config File | Description | Introduced |
---|---|---|---|
--character-sets-dir=path | character-sets-dir | The directory where character sets are installed | |
--database=db_name | database | List entries for just this database | |
-d db_name | |||
-# [debug_options] | debug | Write a debugging log | |
--debug[=debug_options] | |||
--disable-log-bin | disable-log-bin | Disable binary logging | |
-D | |||
--force-read | force-read | If mysqlbinlog reads a binary log event that it does not recognize, it prints a warning | |
-f | |||
--help | Display help message and exit | ||
-? | |||
--hexdump | hexdump | Display a hex dump of the log in comments | 5.0.16 |
-H | |||
--host=host_name | host | Connect to the MySQL server on the given host | |
-h host_name | |||
--local-load=path | local-load | Prepare local temporary files for LOAD DATA INFILE in the specified directory | |
-l path | |||
--offset=# | offset | Skip the first N entries in the log | |
-o N | |||
--password[=password] | password | The password to use when connecting to the server | |
-p[password] | |||
--port=port_num | port | The TCP/IP port number to use for the connection | |
-P port_num | |||
--protocol={TCP|SOCKET|PIPE|MEMORY} | protocol | The connection protocol to use | |
--read-from-remote-server | read-from-remote-server | Read the binary log from a MySQL server rather than reading a local log file | |
-R | |||
--result-file=name | result-file | Direct output to the given file | |
-r name | |||
--set-charset=charset_name | set-charset | Add a SET NAMES charset_name statement to the output | 5.0.23 |
--short-form | short-form | Display only the statements contained in the log | |
-s | |||
--socket=path | socket | For connections to localhost | |
-S path | |||
--start-datetime=datetime | start-datetime | Start reading the binary log at the first event having a timestamp equal to or later than the datetime argument | |
--start-position=# | start-position | Start reading the binary log at the first event having a position equal to the N argument | |
--stop-datetime=datetime | stop-datetime | Stop reading the binary log at the first event having a timestamp equal or posterior to the datetime argument | |
--stop-position=# | stop-position | Stop reading the binary log at the first event having a position equal or greater than the N argument | |
--to-last-log | to-last-log | Do not stop at the end of the requested binary log from a MySQL server, but rather continue printing until the end of the last binary log | |
-t | |||
--user=user_name, | user | The MySQL username to use when connecting to the server | |
-u user_name | |||
--version | Display version information and exit | ||
-V |
Display a help message and exit.
The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.
--database=
,
db_name
-d
db_name
List entries for just this database (local log only). You
can only specify one database with this option - if you
specify multiple --database
options, only
the last one is used. This option forces
mysqlbinlog to output entries from the
binary log where the default database (that is, the one
selected by USE
) is
db_name
. Note that this does not
replicate cross-database statements such as UPDATE
while having selected a different
database or no database.
some_db.some_table
SET
foo='bar'
--debug[=
,
debug_options
]-#
[
debug_options
]
Write a debugging log. A typical
debug_options
string is often
'd:t:o,
.
file_name
'
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.
This option requires that you have the
SUPER
privilege. It causes
mysqlbinlog to include a SET
SQL_LOG_BIN=0
statement in its output to disable
binary logging of the remaining output. The
SET
statement is ineffective unless you
have the SUPER
privilege.
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.
Display a hex dump of the log in comments. This output can be helpful for replication debugging. Hex dump format is discussed later in this section. 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.
Prepare local temporary files for LOAD DATA
INFILE
in the specified directory.
Skip the first N
entries in the
log.
--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.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
The TCP/IP port number to use for connecting to a remote server.
Deprecated. Use --start-position
instead.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use.
Read the binary log from a MySQL server rather than reading
a local log file. Any connection parameter options are
ignored unless this option is given as well. These options
are --host
, --password
,
--port
, --protocol
,
--socket
, and --user
.
Direct output to the given file.
Add a SET NAMES
statement
to the output to specify the character set to be used for
processing log files. This option was added in MySQL 5.0.23.
charset_name
Display only the statements contained in the log, without any extra information.
For connections to localhost
, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
Start reading the binary log at the first event having a
timestamp 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="2005-12-25 11:25:56" binlog.000003
This option is useful for point-in-time recovery. See Section 6.2, “Example Backup and Recovery Strategy”.
Stop reading the binary log at the first event having a
timestamp equal or posterior to the
datetime
argument. This option is
useful for point-in-time recovery. See the description of
the --start-datetime
option for information
about the datetime
value.
Start reading the binary log at the first event having a
position equal to the N
argument.
This option applies to the first log file named on the
command line.
Stop reading the binary log at the first event having a
position equal or greater than the
N
argument. This option applies
to the last log file named on the command line.
Do not stop at the end of the requested binary log from a
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
.
--user=
,
user_name
-u
user_name
The MySQL username to use when connecting to a remote server.
Display version information and exit.
You can also set the following variable by using
--
syntax:
var_name
=value
It is also possible to set variables by using
--set-variable=
or var_name
=value
-O
syntax. This syntax is deprecated.
var_name
=value
You can pipe the output of mysqlbinlog into the 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 6.1, “Database Backups”). For example:
shell> mysqlbinlog binlog.000001 | mysql
Or:
shell> mysqlbinlog binlog.[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
--start-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 and start when
it sees an event with 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 a.m.”).
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 binlog.000001 | mysql # DANGER!!
shell>mysqlbinlog binlog.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 so:
shell> mysqlbinlog binlog.000001 binlog.000002 | mysql
Another approach is to write all the logs to a single file and then process the file:
shell>mysqlbinlog binlog.000001 > /tmp/statements.sql
shell>mysqlbinlog binlog.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.3.4, “Security Issues with LOAD DATA LOCAL
”.
MySQL Enterprise
For expert advice on the security implications of enabling
LOCAL
, subscribe to the MySQL Enterprise
Monitor. For more information see
http://www.mysql.com/products/enterprise/advisors.html.
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;
Hex dump output currently contains the following elements. This format might change in the future.
Position
: The byte position within the
log file.
Timestamp
: The event timestamp. In the
example shown, '9d fc 5c 43'
is the
representation of '051024 17:24:13'
in
hexadecimal.
Type
: The type of the log event. In the
example shown, '0f'
means that the
example event is a
FORMAT_DESCRIPTION_EVENT
. The following
table lists the possible types.
Type | Name | Meaning |
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 statements 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 when 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 a 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 statement. |
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 an XA transaction. |
11 | BEGIN_LOAD_QUERY_EVENT | Used for LOAD DATA INFILE statements in MySQL 5 and
later. |
12 | EXECUTE_LOAD_QUERY_EVENT | Used for LOAD DATA INFILE 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. Currently, the following
flags are used. The others are reserved for the future.
Flag | Name | Meaning |
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, for example,
'01 00' ) in a
FORMAT_DESCRIPTION_EVENT , the log
file has not been properly closed. Most probably
this is 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 (for
example, '04 00' ), for example,
if the event uses temporary tables. |
08 | LOG_EVENT_SUPPRESS_USE_F | Set in some circumstances when the event is not dependent on the default database. |
The other flags are reserved for future use.
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
database backup 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
and ARCHIVE
tables.
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:
Table 4.11. mysqlhotcopy Option Reference
Format | Config File | Description | Introduced |
---|---|---|---|
--addtodest | addtodest | Do not rename target directory (if it exists); merely add files to it | |
--allowold | allowold | Do not abort if a target exists; rename it by adding an _old suffix | |
--checkpoint=db_name.tbl_name | checkpoint | Insert checkpoint entries | |
--chroot=path | chroot | Base directory of the chroot jail in which mysqld operates | |
--debug | debug | Write a debugging log | |
--dryrun | dryrun | Report actions without performing them | |
-n | |||
--flushlogs | flushlogs | Flush logs after all tables are locked | |
--help | Display help message and exit | ||
-? | |||
--host=host_name | host | Connect to the MySQL server on the given host | |
-h host_name | |||
--keepold | keepold | Do not delete previous (renamed) target when done | |
--noindices | noindices | Do not include full index files in the backup | |
--password[=password] | password | The password to use when connecting to the server | |
-p[password] | |||
--port=port_num | port | The TCP/IP port number to use for the connection | |
-P port_num | |||
--quiet | quiet | Be silent except for errors | |
-q | |||
--regexp | regexp | Copy all databases with names that match the given regular expression | |
--resetmaster | resetmaster | Reset the binary log after locking all the tables | |
--resetslave | resetslave | Reset the master.info file after locking all the tables | |
--socket=path | socket | For connections to localhost | |
-S path | |||
--tmpdir=path | tmpdir | The temporary directory | |
--user=user_name, | user | The MySQL username to use when connecting to the server | |
-u user_name | |||
--version | Display version information and exit | ||
-V |
Display a help message and exit.
Do not rename target directory (if it exists); merely add files to it.
Do not abort if a target exists; rename it by adding an
_old
suffix.
Insert checkpoint entries into the specified database
db_name
and table
tbl_name
.
Base directory of the chroot jail in
which mysqld operates. The
path
value should match that of
the --chroot
option given to
mysqld.
Enable debug output.
Report actions without performing them.
Flush logs after all tables are locked.
--host=
,
host_name
-h
host_name
The hostname of the local host to use for making a TCP/IP
connection to the local server. By default, the connection
is made to localhost
using a Unix socket
file.
Do not delete previous (renamed) target when done.
The method for copying files (cp
or
scp
).
Do not include full index files in the backup. This makes the backup smaller and faster. The indexes for reloaded tables can be reconstructed later with myisamchk -rq.
--password=
,
password
-p
password
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. You can use an option file to avoid giving the password on the command line.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
The TCP/IP port number to use when connecting to the local server.
Be silent except for errors.
--record_log_pos=
db_name
.tbl_name
Record master and slave status in the specified database
db_name
and table
tbl_name
.
Copy all databases with names that match the given regular expression.
Reset the binary log after locking all the tables.
Reset the master.info
file after
locking all the tables.
The Unix socket file to use for the connection.
The suffix for names of copied databases.
The temporary directory. The default is
/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, the
RELOAD
privilege (to be able to execute
FLUSH TABLES
), and the LOCK
TABLES
privilege (to be able to lock the tables).
Use perldoc
for additional
mysqlhotcopy documentation, including
information about the structure of the tables needed for the
--checkpoint
and
--record_log_pos
options:
shell> perldoc mysqlhotcopy
MySQL Enterprise MySQL Enterprise subscribers will find more information about mysqlhotcopy in the Knowledge Base article, How Does mysqlhotcopy Work?. Access to the MySQL Knowledge Base collection of articles is one of the advantages of subscribing to MySQL Enterprise. For more information see http://www.mysql.com/products/enterprise/advisors.html.
mysqlmanager is the MySQL Instance Manager (IM). This program monitors and manages MySQL Database Server instances. MySQL Instance Manager is available for Unix-like operating systems, and also on Windows as of MySQL 5.0.13. It runs as a daemon that listens on a TCP/IP port. On Unix, it also listens on a Unix socket file.
MySQL Instance Manager is included in MySQL distributions from
version 5.0.3, and can be used in place of the
mysqld_safe
script to start and stop one or
more instances of MySQL Server. Because Instance Manager can
manage multiple server instances, it can also be used in place
of the mysqld_multi script. Instance Manager
offers these capabilities:
Instance Manager can start and stop instances, and report on the status of instances.
Server instances can be treated as guarded or unguarded:
When Instance Manager starts, it starts each guarded instance. If the instance crashes, Instance Manager detects this and restarts it. When Instance Manager stops, it stops the instance.
A nonguarded instance is not started when Instance Manager starts or monitored by it. If the instance crashes after being started, Instance Manager does not restart it. When Instance Manager exits, it does not stop the instance if it is running.
Instances are guarded by default. An instance can be
designated as nonguarded by including the
nonguarded
option in the configuration
file.
Instance Manager provides an interactive interface for configuring instances, so that the need to edit the configuration file manually is reduced or eliminated.
Instance Manager provides remote instance management. That is, it runs on the host where you want to control MySQL Server instances, but you can connect to it from a remote host to perform instance-management operations.
The following sections describe MySQL Instance Manager operation in more detail.
The MySQL Instance Manager supports a number of command options.
For a brief listing, invoke mysqlmanager with
the --help
option. Options may be given on the
command line or in the Instance Manager configuration file. On
Windows, the standard configuration file is
my.ini
in the directory where Instance
Manager is installed. On Unix, the standard file is
/etc/my.cnf
. To specify a different
configuration file, start Instance Manager with the
--defaults-file
option.
mysqlmanager supports the following options:
Display a help message and exit.
The file in which the angel process records its process ID
when mysqlmanager runs in daemon mode
(that is, when the --run-as-service
option
is given). The default filename is
mysqlmanager.angel.pid
.
If the --angel-pid-file
option is not
given, the default angel PID file has the same name as the
PID file except that any PID file extension is replaced with
an extension of .angel.pid
. (For
example, mysqlmanager.pid
becomes
mysqlmanager.angel.pid
.)
This option was added in MySQL 5.0.23.
The IP address to bind to.
The pathname of the MySQL Server binary. This pathname is
used for all server instance sections in the configuration
file for which no mysqld-path
option is
present. The default value of this option is the compiled-in
pathname, which depends on how the MySQL distribution was
configured. Example:
--default-mysqld-path=/usr/sbin/mysqld
Read Instance Manager and MySQL Server settings from the given file. All configuration changes made by the Instance Manager will be written to this file. This must be the first option on the command line if it is used, and the file must exist.
If this option is not given, Instance Manager uses its
standard configuration file. On Windows, the standard file
is my.ini
in the directory where
Instance Manager is installed. On Unix, the standard file is
/etc/my.cnf
.
On Windows, install Instance Manager as a Windows service.
The service name is MySQL Manager
. This
option was added in MySQL 5.0.11.
The path to the Instance Manager log file. This option has no effect unless the --run-as-service option is also given. If the filename specified for the option is a relative name, the log file is created under the directory from which Instance Manager is started. To ensure that the file is created in a specific directory, specify it as a full pathname.
If --run-as-service
is given without
--log
, the log file is
mysqlmanager.log
in the data directory.
If --run-as-service
is not given, log
messages go to the standard output. To capture log output,
you can redirect Instance Manager output to a file:
mysqlmanager > im.log
The interval in seconds for monitoring server instances. The
default value is 20 seconds. Instance Manager tries to
connect to each monitored (guarded) instance using the
non-existing MySQL_Instance_Manager
user
account to check whether it is alive/not hanging. If the
result of the connection attempt indicates that the instance
is unavailable, Instance Manager performs several attempts
to restart the instance.
Normally, the MySQL_Instance_Manager
account does not exist, so the connection attempts by
Instance Manager cause the monitored instance to produce
messages in its general query log similar to the following:
Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES)
The nonguarded
option in the appropriate
server instance section disables monitoring for a particular
instance. If the instance dies after being started, Instance
Manager will not restart it. Instance Manager tries to
connect to a nonguarded instance only when you request the
instance's status (for example, with the SHOW
INSTANCES
status.
See Section 4.6.9.5, “MySQL Server Instance Status Monitoring”, for more information.
Prepare an entry for the password file, print it to the standard output, and exit. You can redirect the output from Instance Manager to a file to save the entry in the file.
The name of the file where the Instance Manager looks for
users and passwords. On Windows, the default is
mysqlmanager.passwd
in the directory
where Instance Manager is installed. On Unix, the default
file is /etc/mysqlmanager.passwd
.
The process ID file to use. On Windows, the default file is
mysqlmanager.pid
in the directory where
Instance Manager is installed. On Unix, the default is
mysqlmanager.pid
in the data directory.
The port number to use when listening for TCP/IP connections from clients. The default port number (assigned by IANA) is 2273.
Print the current defaults and exit. This must be the first option on the command line if it is used.
On Windows, removes Instance Manager as a Windows service.
This assumes that Instance Manager has been run with
--install
previously. This option was added
in MySQL 5.0.11.
On Unix, daemonize and start an angel process. The angel process monitors Instance Manager and restarts it if it crashes. (The angel process itself is simple and unlikely to crash.)
On Unix, the socket file to use for incoming connections.
The default file is named
/tmp/mysqlmanager.sock
. This option has
no meaning on Windows.
This option is used on Windows to run Instance Manager in standalone mode. You should specify it when you start Instance Manager from the command line. This option was added in MySQL 5.0.13.
On Unix, the username of the system account to use for
starting and running mysqlmanager. This
option generates a warning and has no effect unless you
start mysqlmanager as
root
(so that it can change its effective
user ID), or as the named user. It is recommended that you
configure mysqlmanager to run using the
same account used to run the mysqld
server. (“User” in this context refers to a
system login account, not a MySQL user listed in the grant
tables.)
Display version information and exit.
The number of seconds to wait for activity on an incoming connection before closing it. The default is 28800 seconds (8 hours).
This option was added in MySQL 5.0.19. Before that, the timeout is 30 seconds and cannot be changed.
Instance Manager uses its standard configuration file unless it
is started with a --defaults-file
option that
specifies a different file. On Windows, the standard file is
my.ini
in the directory where Instance
Manager is installed. On Unix, the standard file is
/etc/my.cnf
. (Prior to MySQL 5.0.10, the
MySQL Instance Manager read the same configuration files as the
MySQL Server, including /etc/my.cnf
,
~/.my.cnf
, and so forth.)
Instance Manager reads options for itself from the
[manager]
section of the configuration file,
and options for server instances from
[mysqld]
or
[mysqld
sections. The N
][manager]
section contains any
of the options listed in
Section 4.6.9.1, “MySQL Instance Manager Command Options”, except for
those specified as having to be given as the first option on the
command line. Here is a sample [manager]
section:
# MySQL Instance Manager options section [manager] default-mysqld-path = /usr/local/mysql/libexec/mysqld socket=/tmp/manager.sock pid-file=/tmp/manager.pid password-file = /home/cps/.mysqlmanager.passwd monitoring-interval = 2 port = 1999 bind-address = 192.168.1.5
Each [mysqld]
or
[mysqld
instance
section specifies options given by Instance Manager to a server
instance at startup. These are mainly common MySQL Server
options (see Section 5.1.2, “Command Options”). In addition, a
N
][mysqld
section
can contain the options in the following list, which are
specific to Instance Manager. These options are interpreted by
Instance Manager itself; it does not pass them to the server
when it attempts to start that server.
N
]
The Instance Manager-specific options must not be used in a
[mysqld]
section. If a server is started
without using Instance Manager, it will not recognize these
options and will fail to start properly.
mysqld-path =
path
The pathname of the mysqld server binary to use for the server instance.
nonguarded
This option disables Instance Manager monitoring functionality for the server instance. By default, an instance is guarded: At Instance Manager start time, it starts the instance. It also monitors the instance status and attempts to restart it if it fails. At Instance Manager exit time, it stops the instance. None of these things happen for nonguarded instances.
shutdown-delay =
seconds
The number of seconds Instance Manager should wait for the
server instance to shut down. The default value is 35
seconds. After the delay expires, Instance Manager assumes
that the instance is hanging and attempts to terminate it.
If you use InnoDB
with large tables, you
should increase this value.
Here are some sample instance sections:
[mysqld1] mysqld-path=/usr/local/mysql/libexec/mysqld socket=/tmp/mysql.sock port=3307 server_id=1 skip-stack-trace core-file skip-bdb log-bin log-error log=mylog log-slow-queries [mysqld2] nonguarded port=3308 server_id=2 mysqld-path= /home/cps/mysql/trees/mysql-5.0/sql/mysqld socket = /tmp/mysql.sock5 pid-file = /tmp/hostname.pid5 datadir= /home/cps/mysql_data/data_dir1 language=/home/cps/mysql/trees/mysql-5.0/sql/share/english log-bin log=/tmp/fordel.log
This section discusses how Instance Manager starts server instances when it starts. However, before you start Instance Manager, you should set up a password file for it. Otherwise, you will not be able to connect to Instance Manager to control it after it starts. For details about creating Instance Manager accounts, see Section 4.6.9.4, “Instance Manager User and Password Management”.
On Unix, the mysqld MySQL database server
normally is started with the mysql.server
script, which usually resides in the
/etc/init.d/
directory. In MySQL 5.0.3, this
script invokes mysqlmanager (the MySQL
Instance Manager binary) to start MySQL. (In prior versions of
MySQL the mysqld_safe script is used for this
purpose.) Starting from MySQL 5.0.4, the behavior of the startup
script was changed again to incorporate both setup schemes. In
version 5.0.4, the startup script uses the old scheme (invoking
mysqld_safe) by default, but one can set the
use_mysqld_safe
variable in the script to
0
(zero) to use the MySQL Instance Manager to
start a server.
Starting with MySQL 5.0.19, you can use Instance Manager if you
modify the my.cnf
configuration file by
adding use-manager
to the
[mysql.server]
section:
[mysql.server] use-manager
When Instance Manager starts, it reads its configuration file if
it exists to find server instance sections and prepare a list of
instances. Instance sections have names of the form
[mysqld]
or
[mysqld
, where
N
]N
is an unsigned integer (for
example, [mysqld1]
,
[mysqld2]
, and so forth).
After preparing the list of instances, Instance Manager starts
the guarded instances in the list. If there are no instances,
Instance Manager creates an instance named
mysqld
and attempts to start it with default
(compiled-in) configuration values. This means that the Instance
Manager cannot find the mysqld program if it
is not installed in the default location.
(Section 2.4.6, “Installation Layouts”, describes default
locations for components of MySQL distributions.) If you have
installed the MySQL server in a non-standard location, you
should create the Instance Manager configuration file.
Instance Manager also stops all guarded server instances when it shuts down.
The allowable options for
[mysqld
server
instance sections are described in
Section 4.6.9.2, “MySQL Instance Manager Configuration Files”. In these
sections, you can use a special
N
]mysqld-path=
option that is recognized only by Instance Manager. Use this
option to let Instance Manager know where the
mysqld binary resides. If there are multiple
instances, it may also be necessary to set other options such as
path-to-mysqld-binary
datadir
and port
, to
ensure that each instance has a different data directory and
TCP/IP port number. Section 5.6, “Running Multiple MySQL Servers on the Same Machine”,
discusses the configuration values that must differ for each
instance when you run multiple instance on the same machine.
The [mysqld]
instance section, if it
exists, must not contain any Instance Manager-specific
options.
The typical Unix startup/shutdown cycle for a MySQL server with the MySQL Instance Manager enabled is as follows:
The /etc/init.d/mysql script starts MySQL Instance Manager.
Instance Manager starts the guarded server instances and monitors them.
If a server instance fails, Instance Manager restarts it.
If Instance Manager is shut down (for example, with the /etc/init.d/mysql stop command), it shuts down all server instances.
The Instance Manager stores its user information in a password
file. On Windows, the default is
mysqlmanager.passwd
in the directory where
Instance Manager is installed. On Unix, the default file is
/etc/mysqlmanager.passwd
. To specify a
different location for the password file, use the
--password-file
option.
If the password file does not exist or contains no password entries, you cannot connect to the Instance Manager.
Any Instance Manager process that is running to monitor server instances does not notice changes to the password file. You must stop it and restart it after making password entry changes.
Entries in the password file have the following format, where the two fields are the account username and encrypted password, separated by a colon:
petr:*35110DC9B4D8140F5DE667E28C72DD2597B5C848
Instance Manager password encryption is the same as that used by MySQL Server. It is a one-way operation; no means are provided for decrypting encrypted passwords.
Instance Manager accounts differ somewhat from MySQL Server accounts:
MySQL Server accounts are associated with a hostname, username, and password (see Section 5.5.1, “MySQL Usernames and Passwords”).
Instance Manager accounts are associated with a username and password only.
This means that a client can connect to Instance Manager with a
given username from any host. To limit connections so that
clients can connect only from the local host, start Instance
Manager with the --bind-address=127.0.0.1
option so that it listens only to the local network interface.
Remote clients will not be able to connect. Local clients can
connect like this:
shell> mysql -h 127.0.0.1 -P 2273
To generate a new entry, invoke Instance Manager with the
--passwd option and append the output to the
/etc/mysqlmanager.passwd
file. Here is an
example:
shell>mysqlmanager --passwd >> /etc/mysqlmanager.passwd
Creating record for new user. Enter user name:mike
Enter password:mikepass
Re-type password:mikepass
At the prompts, enter the username and password for the new Instance Manager user. You must enter the password twice. It does not echo to the screen, so double entry guards against entering a different password than you intend (if the two passwords do not match, no entry is generated).
The preceding command causes the following line to be added to
/etc/mysqlmanager.passwd
:
mike:*BBF1F551DD9DD96A01E66EC7DDC073911BAD17BA
To monitor the status of each guarded server instance, the MySQL
Instance Manager attempts to connect to the instance at regular
intervals using the
MySQL_Instance_Manager@localhost
user account
with a password of check_connection
.
You are not required to create this account for MySQL Server; in fact, it is expected that it will not exist. Instance Manager can tell that a server is operational if the server accepts the connection attempt but refuses access for the account by returning a login error. However, these failed connection attempts are logged by the server to its general query log (see Section 5.2.2, “The General Query Log”).
Instance Manager also attempts a connection to nonguarded server
instances when you use the SHOW INSTANCES
or
SHOW INSTANCE STATUS
command. This is the
only status monitoring done for nonguarded instances.
Instance Manager knows if a server instance fails at startup because it receives a status from the attempt. For an instance that starts but later crashes, Instance Manager receives a signal because it is the parent process of the instance.
After you set up a password file for the MySQL Instance Manager and Instance Manager is running, you can connect to it. The MySQL client-server protocol is used to communicate with the Instance Manager. For example, you can connect to it using the standard mysql client program:
shell> mysql --port=2273 --host=im.example.org --user=mysql --password
Instance Manager supports the version of the MySQL client-server protocol used by the client tools and libraries distributed with MySQL 4.1 or later, so other programs that use the MySQL C API also can connect to it.
After you connect to MySQL Instance Manager, you can issue commands. The following general principles apply to Instance Manager command execution:
Commands that take an instance name fail if the name is not a valid instance name.
Commands that take an instance name fail if the instance does not exist.
Instance Manager maintains information about instance configuration in an internal (in-memory) cache. Initially, this information comes from the configuration file if it exists, but some commands change the configuration of an instance. Commands that modify the configuration file fail if the file does not exist or is not accessible to Instance Manager.
On Windows, the standard file is my.ini
in the directory where Instance Manager is installed. On
Unix, the standard configuration file is
/etc/my.cnf
. To specify a different
configuration file, start Instance Manager with the
--defaults-file
option.
If a [mysqld]
instance section exists in
the configuration file, it must not contain any Instance
Manager-specific options (see
Section 4.6.9.2, “MySQL Instance Manager Configuration Files”).
Therefore, you must not add any of these options if you
change the configuration for an instance named
mysqld
.
The following list describes the commands that Instance Manager accepts, with examples.
START INSTANCE
instance_name
This command attempts to start an offline instance. The command is asynchronous; it does not wait for the instance to start.
mysql> START INSTANCE mysqld4;
Query OK, 0 rows affected (0,00 sec)
STOP INSTANCE
instance_name
This command attempts to stop an instance. The command is synchronous; it waits for the instance to stop.
mysql> STOP INSTANCE mysqld4;
Query OK, 0 rows affected (0,00 sec)
SHOW INSTANCES
Shows the names and status of all loaded instances.
mysql> SHOW INSTANCES;
+---------------+---------+
| instance_name | status |
+---------------+---------+
| mysqld3 | offline |
| mysqld4 | online |
| mysqld2 | offline |
+---------------+---------+
SHOW INSTANCE STATUS
instance_name
Shows status and version information for an instance.
mysql> SHOW INSTANCE STATUS mysqld3;
+---------------+--------+---------+
| instance_name | status | version |
+---------------+--------+---------+
| mysqld3 | online | unknown |
+---------------+--------+---------+
SHOW INSTANCE OPTIONS
instance_name
Shows the options used by an instance.
mysql> SHOW INSTANCE OPTIONS mysqld3;
+---------------+---------------------------------------------------+
| option_name | value |
+---------------+---------------------------------------------------+
| instance_name | mysqld3 |
| mysqld-path | /home/cps/mysql/trees/mysql-4.1/sql/mysqld |
| port | 3309 |
| socket | /tmp/mysql.sock3 |
| pid-file | hostname.pid3 |
| datadir | /home/cps/mysql_data/data_dir1/ |
| language | /home/cps/mysql/trees/mysql-4.1/sql/share/english |
+---------------+---------------------------------------------------+
SHOW
instance_name
LOG
FILES
The command lists all log files used by the instance. The
result set contains the path to the log file and the log
file size. If no log file path is specified in the instance
section of the configuration file (for example,
log=/var/mysql.log
), the Instance Manager
tries to guess its placement. If Instance Manager is unable
to guess the log file placement you should specify the log
file location explicitly by using a log option in the
appropriate instance section of the configuration file.
mysql> SHOW mysqld LOG FILES;
+-------------+------------------------------------+----------+
| Logfile | Path | Filesize |
+-------------+------------------------------------+----------+
| ERROR LOG | /home/cps/var/mysql/owlet.err | 9186 |
| GENERAL LOG | /home/cps/var/mysql/owlet.log | 471503 |
| SLOW LOG | /home/cps/var/mysql/owlet-slow.log | 4463 |
+-------------+------------------------------------+----------+
Log options are described in Section 5.1.2, “Command Options”.
SHOW
instance_name
LOG
{ERROR | SLOW | GENERAL}
size
[,offset_from_end
]
This command retrieves a portion of the specified log file.
Because most users are interested in the latest log
messages, the size
parameter
defines the number of bytes to retrieve from the end of the
log. To retrieve data from the middle of the log file,
specify the optional
offset_from_end
parameter. The
following example retrieves 21 bytes of data, starting 23
bytes before the end of the log file and ending 2 bytes
before the end:
mysql> SHOW mysqld LOG GENERAL 21, 2;
+---------------------+
| Log |
+---------------------+
| using password: YES |
+---------------------+
SET
instance_name
.option_name
[=option_value
]
This command edits the specified instance's configuration section to change or add instance options. The option is added to the section is it is not already present. Otherwise, the new setting replaces the existing one.
mysql> SET mysqld2.port=3322;
Query OK, 0 rows affected (0.00 sec)
Changes made to the configuration file do not take effect
until the MySQL server is restarted. In addition, these
changes are not stored in the instance manager's local cache
of instance settings until a FLUSH
INSTANCES
command is executed.
UNSET
instance_name
.option_name
This command removes an option from an instance's configuration section.
mysql> UNSET mysqld2.port;
Query OK, 0 rows affected (0.00 sec)
Changes made to the configuration file do not take effect
until the MySQL server is restarted. In addition, these
changes are not stored in the instance manager's local cache
of instance settings until a FLUSH
INSTANCES
command is executed.
FLUSH INSTANCES
This command forces Instance Manager reread the configuration file and to refresh internal structures. This command should be performed after editing the configuration file. The command does not restart instances.
mysql> FLUSH INSTANCES;
Query OK, 0 rows affected (0.04 sec)
FLUSH INSTANCES
is deprecated and will be
removed in MySQL 5.2.
This program was used internally for test purposes. As of MySQL 5.0, it is no longer used.
This program was used internally for test purposes. As of MySQL 5.0, it is no longer used.
mysql_convert_table_format converts the
tables in a database to use a particular storage engine
(MyISAM
by default).
mysql_convert_table_format is written in Perl
and requires that the DBI
and
DBD::mysql
Perl modules be installed (see
Section 2.4.21, “Perl Installation Notes”).
Invoke mysql_convert_table_format like this:
shell> mysql_convert_table_format [options
]db_name
The db_name
argument indicates the
database containing the tables to be converted.
mysql_convert_table_format understands the options described in the following list.
Display a help message and exit.
Continue even if errors occur.
Connect to the MySQL server on the given host.
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. You can use an option file to avoid giving the password on the command line.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
The TCP/IP port number to use for the connection.
--socket=
path
For connections to localhost
, the Unix
socket file to use.
Specify the storage engine that the tables should be
converted to use. The default is MyISAM
if this option is not given.
MySQL Enterprise For expert advice on choosing the optimum storage engine, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.
The MySQL username to use when connecting to the server.
Verbose mode. Print more information about what the program does.
Display version information and exit.
mysql_explain_log reads its standard input
for query log contents. It uses EXPLAIN
to
analyze SELECT
statements found in the input.
UPDATE
statements are rewritten to
SELECT
statements and also analyzed with
EXPLAIN
. mysql_explain_log
then displays a summary of its results.
The results may assist you in determining which queries result in table scans and where it would be beneficial to add indexes to your tables.
Invoke mysql_explain_log like this, where
log_file
contains all or part of a
MySQL query log:
shell> mysql_explain_log [options
] < log_file
mysql_explain_log understands the following options:
Display a help message and exit.
Select entries from the log only for the given date.
--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.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
Enable error output.
For connections to localhost
, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
--user=
,
user_name
-u
user_name
The MySQL username to use when connecting to the server.
mysql_find_rows reads files containing SQL
statements and extracts statements that match a given regular
expression or that contain USE
or
db_name
SET
statements. The utility was written for
use with update log files, but it can be used with other files
that contain SQL statements.
Invoke mysql_find_rows like this:
shell> mysql_find_rows [options
] [file_name
...]
Each file_name
argument should be the
name of file containing SQL statements. If no filenames are
given, mysql_find_rows reads the standard
input.
Examples:
mysql_find_rows --regexp=problem_table --rows=20 < update.log mysql_find_rows --regexp=problem_table update-log.1 update-log.2
mysql_find_rows supports the following options:
mysql_fix_extensions converts the extensions
for MyISAM
(or ISAM
) table
files to their canonical forms. It looks for files with
extensions matching any lettercase variant of
.frm
, .myd
,
.myi
, .isd
, and
.ism
and renames them to have extensions of
.frm
, .MYD
,
.MYI
, .ISD
, and
.ISM
, respectively. This can be useful
after transferring the files from a system with case-insensitive
filenames (such as Windows) to a system with case-sensitive
filenames.
Invoke mysql_fix_extensions like this, where
data_dir
is the pathname to the MySQL
data directory.
shell> mysql_fix_extensions data_dir
mysql_setpermission is a Perl script that was
originally written and contributed by Luuk de Boer. It
interactively sets permissions in the MySQL grant tables.
mysql_setpermission is written in Perl and
requires that the DBI
and
DBD::mysql
Perl modules be installed (see
Section 2.4.21, “Perl Installation Notes”).
Invoke mysql_setpermission like this:
shell> mysql_setpermission [options
]
options
should be either
--help
to display the help message, or options
that indicate how to connect to the MySQL server. The account
used when you connect determines which permissions you have when
attempting to modify existing permissions in the grant tables.
mysql_setpermissions also reads options from
the [client]
and [perl]
groups in the .my.cnf
file in your home
directory, if the file exists.
mysql_setpermission understands the following options:
Display a help message and exit.
Connect to the MySQL server on the given host.
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. You can use an option file to avoid giving the password on the command line.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
The TCP/IP port number to use for the connection.
For connections to localhost
, the Unix
socket file to use.
The MySQL username to use when connecting to the server.
mysql_tableinfo creates tables and populates
them with database metadata. It uses SHOW
DATABASES
, SHOW TABLES
,
SHOW TABLE STATUS
, SHOW
COLUMNS
, and SHOW INDEX
to obtain
the metadata.
In MySQL 5.0 and up, the INFORMATION_SCHEMA
database contains the same kind of information in the
SCHEMATA
, TABLES
,
COLUMNS
, and STATISTICS
tables. See Chapter 21, INFORMATION_SCHEMA
Tables.
Invoke mysql_tableinfo like this:
shell> mysql_tableinfo [options
] db_name
[db_like
[tbl_like
]]
The db_name
argument indicates which
database mysql_tableinfo should use as the
location for the metadata tables. The database will be created
if it does not exist. The tables will be named
db
, tbl
(or
tbl_status
), col
, and
idx
.
If the db_like
or
tbl_like
arguments are given, they
are used as patterns and metadata is generated only for
databases or tables that match the patterns. These arguments
default to %
if not given.
Examples:
mysql_tableinfo info mysql_tableinfo info world mysql_tableinfo info mydb tmp%
Each of the commands stores information into tables in the
info
database. The first stores information
for all databases and tables. The second stores information for
all tables in the world
database. The third
stores information for tables in the mydb
database that have names matching the pattern
tmp%
.
mysql_tableinfo supports the following options:
Table 4.12. mysql_tableinfo Option Reference
Format | Config File | Description | Introduced |
---|---|---|---|
--clear | clear | Before populating each metadata table, drop it if it exists | |
--clear-only | clear-only | Similar to --clear, but exits after dropping the metadata tables to be populated. | |
--col | col | Generate column metadata into the col table | |
--help | Display help message and exit | ||
--host=host_name | host | Connect to the MySQL server on the given host | |
--idx | idx | Generate index metadata into the idx table | |
--password=password | password | The password to use when connecting to the server -- not optional | |
-ppassword | |||
--port=port_num | port | The TCP/IP port number to use for the connection | |
--prefix=prefix_str | prefix | Add prefix_str at the beginning of each metadata table name | |
--quiet | quiet | Be silent except for errors | |
--socket=path | socket | Display version information and exit | |
--tbl-status | tbl-status | Use SHOW TABLE STATUS instead of SHOW TABLES | |
--user=user_name, | user | The mysql_tableinfo username to use when connecting to the server |
Display a help message and exit.
Before populating each metadata table, drop it if it exists.
Similar to --clear
, but exits after
dropping the metadata tables to be populated.
Generate column metadata into the col
table.
--host=
,
host_name
-h
host_name
Connect to the MySQL server on the given host.
Generate index metadata into the idx
table.
--password=
,
password
-p
password
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. You can use an option file to avoid giving the password on the command line.
Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.
The TCP/IP port number to use for the connection.
Add prefix_str
at the beginning
of each metadata table name.
Be silent except for errors.
The Unix socket file to use for the connection.
Use SHOW TABLE STATUS
instead of
SHOW TABLES
. This provides more complete
information, but is slower.
--user=
,
user_name
-u
user_name
The MySQL username to use when connecting to the server.
mysql_waitpid signals a process to terminate
and waits for the process to exit. It uses the
kill()
system call and Unix signals, so it
runs on Unix and Unix-like systems.
Invoke mysql_waitpid like this:
shell> mysql_waitpid [options
] pid
wait_time
mysql_waitpid sends signal 0 to the process
identified by pid
and waits up to
wait_time
seconds for the process to
terminate. pid
and
wait_time
must be positive integers.
If process termination occurs within the wait time or the process does not exist, mysql_waitpid returns 0. Otherwise, it returns 1.
If the kill()
system call cannot handle
signal 0, mysql_waitpid() uses signal 1
instead.
mysql_waitpid understands the following options:
mysql_zap kills processes that match a pattern. It uses the ps command and Unix signals, so it runs on Unix and Unix-like systems.
Invoke mysql_zap 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 -
option is given, it specifies the name or number of the signal
to send to each process. Otherwise, mysql_zap
tries first with signal
TERM
(signal 15) and then
with KILL
(signal 9).
mysql_zap understands the following additional options:
This section describes some utilities that you may find useful when developing MySQL programs.
In shell scripts, you can use the
my_print_defaults program to parse option files
and see what options would be used by a given program. The following
example shows the output that my_print_defaults
might produce when asked to show the options found in the
[client]
and [mysql]
groups:
shell> my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash
Note for developers: Option file handling is implemented in the C client library simply by processing all options in the appropriate group or groups before any command-line arguments. This works well for programs that use the last instance of an option that is specified multiple times. If you have a C or C++ program that handles multiply specified options this way but that doesn't read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MySQL clients to see how to do this.
Several other language interfaces to MySQL are based on the C client library, and some of them provide a way to access option file contents. These include Perl and Python. For details, see the documentation for your preferred interface.
Initially, the MySQL C API was developed to be very similar to that for the mSQL database system. Because of this, mSQL programs often can be converted relatively easily for use with MySQL by changing the names of the C API functions.
The msql2mysql utility performs the conversion of mSQL C API function calls to their MySQL equivalents. msql2mysql converts the input file in place, so make a copy of the original before converting it. For example, use msql2mysql like this:
shell>cp client-prog.c client-prog.c.orig
shell>msql2mysql client-prog.c
client-prog.c converted
Then examine client-prog.c
and make any
post-conversion revisions that may be necessary.
msql2mysql uses the replace utility to make the function name substitutions. See Section 4.8.2, “replace — A String-Replacement Utility”.
mysql_config provides you with useful information for compiling your MySQL client and connecting it to MySQL.
mysql_config supports the following options:
--cflags
Compiler flags to find include files and critical compiler
flags and defines used when compiling the
libmysqlclient
library. The options
returned are tied to the specific compiler that was used
when the library was created and might clash with the
settings for your own compiler. Use
--include
for more portable options that
contain only include paths.
--include
Compiler options to find MySQL include files.
--libmysqld-libs
,
--embedded
Libraries and options required to link with the MySQL embedded server.
--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.
--port
The default TCP/IP port number, defined when configuring MySQL.
--socket
The default Unix socket file, defined when configuring MySQL.
--version
Version number for the MySQL distribution.
If you invoke mysql_config with no options, it displays a list of all options that it supports, and their values:
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 mysql_config within a command line to include the value that it displays for a particular option. For example, to compile a MySQL client program, use mysql_config as follows:
shell>CFG=/usr/local/mysql/bin/mysql_config
shell>sh -c "gcc -o progname `$CFG --include` progname.c `$CFG --libs`"
When you use mysql_config this way, be sure
to invoke it within backtick
(“`
”) characters. That tells the
shell to execute it and substitute its output into the
surrounding command.
my_print_defaults displays the options that
are present in option groups of option files. The output
indicates what options will be used by programs that read the
specified option groups. For example, the
mysqlcheck program reads the
[mysqlcheck]
and [client]
option groups. To see what options are present in those groups
in the standard option files, invoke
my_print_defaults like this:
shell> my_print_defaults mysqlcheck client
--user=myusername
--password=secret
--host=localhost
The output consists of options, one per line, in the form that they would be specified on the command line.
my_print_defaults
understands the following
options:
Display a help message and exit.
--config-file=
,
file_name
--defaults-file=
,
file_name
-c
file_name
Read only the given option file.
--debug=
debug_options
, -#
debug_options
Write a debugging log. The
debug_options
string often is
'd:t:o,
.
The default is
file_name
''d:t:o,/tmp/my_print_defaults.trace'
.
--defaults-extra-file=
,
file_name
--extra-file=
,
file_name
-e
file_name
Read this option file after the global option file but (on Unix) before the user option file.
--defaults-group-suffix=
,
suffix
-g
suffix
In addition to the groups named on the command line, read groups that have the given suffix.
Return an empty string.
Verbose mode. Print more information about what the program does.
Display version information and exit.
resolve_stack_dump resolves a numeric stack dump to symbols.
Invoke resolve_stack_dump like this:
shell> resolve_stack_dump [options
] symbols_file
[numeric_dump_file
]
The symbols file should include the output from the nm --numeric-sort mysqld command. The numeric dump file should contain a numeric stack track from mysqld. If no numeric dump file is named on the command line, the stack trace is read from the standard input.
resolve_stack_dump understands the options described in the following list.
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 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
OS error code 13: Permission denied
OS error code 64: Machine is not on the network
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.
perror supports the following options:
Display a help message and exit.
Print the error message for a MySQL Cluster error code.
Silent mode. Print only the error message.
Verbose mode. Print error code and message. This is the default behavior.
Display version information and exit.
The replace utility program changes strings in place in files or on the standard input.
Invoke replace in one of the following ways:
shell>replace
shell>from
to
[from
to
] ... --file
[file
] ...replace
from
to
[from
to
] ... <file
from
represents a string to look for
and to
represents its replacement.
There can be one or more pairs of strings.
Use the --
option to indicate where the
string-replacement list ends and the filenames begin. In this
case, 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. replace
prints a message
indicating which of the input files it actually modifies.
If the --
option is not given,
replace reads the standard input and writes
to the standard output.
replace 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 ...
The replace program is used by msql2mysql. See Section 4.7.1, “msql2mysql — Convert mSQL Programs for Use with MySQL”.
replace supports the following options:
The resolveip utility resolves hostnames to IP addresses and vice versa.
Invoke resolveip like this:
shell> resolveip [options
] {host_name
|ip-addr
} ...
resolveip understands the options described in the following list.