|
Chapter 4. MySQL Programs
This chapter provides a brief overview of the MySQL command-line
programs provided by Sun Microsystems, Inc. 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.
4.1. Overview of MySQL Programs
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 supports.
Chapter 17, 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:
mysqld
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 4.3.1, “mysqld — The MySQL Server”.
mysqld_safe
A server startup script. mysqld_safe
attempts to start mysqld. See
Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.
mysql.server
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”.
mysqld_multi
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.10, “mysqlmanager — The MySQL Instance Manager”.
There are several programs that perform setup operations during
MySQL installation or upgrading:
comp_err
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”.
make_binary_distribution
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.
make_win_bin_dist
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”.
mysql_fix_privilege_tables
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 and should no longer be used.
mysql_install_db
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 4.4.6, “mysql_install_db — Initialize MySQL Data Directory”,
Section 2.17.2, “Unix Post-Installation Procedures”, and
Section 4.4.6, “mysql_install_db — Initialize MySQL Data Directory”.
mysql_secure_installation
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”.
mysql_tzinfo_to_sql
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”.
mysql_upgrade
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”.
make_win_src_distribution
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.16.6.5, “Creating a Windows Source Package from the Bazaar Repository”, and
Section 4.4.3, “make_win_src_distribution — Create Source Distribution for Windows”.
MySQL client programs:
mysql
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”.
mysqladmin
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”.
mysqlcheck
A table-maintenance client that checks, repairs, analyzes, and
optimizes tables. See Section 4.5.3, “mysqlcheck — A Table Maintenance Program”.
mysqldump
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”.
mysqlimport
A client that imports text files into their respective tables
using LOAD DATA
INFILE . See Section 4.5.5, “mysqlimport — A Data Import Program”.
mysqlshow
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:
innochecksum
An offline InnoDB offline file checksum
utility. See Section 4.6.1, “innochecksum — Offline InnoDB File Checksum Utility”.
myisam_ftdump
A utility that displays information about full-text indexes in
MyISAM tables. See
Section 4.6.2, “myisam_ftdump — Display Full-Text Index information”.
myisamchk
A utility to describe, check, optimize, and repair
MyISAM tables. See
Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
myisamlog, isamlog
A utility that processes the contents of a
MyISAM log file. See
Section 4.6.4, “myisamlog — Display MyISAM Log File Contents”.
myisampack
A utility that compresses MyISAM tables to
produce smaller read-only tables. See
Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
mysqlaccess
A script that checks the access privileges for a host name,
user name, and database combination. See
Section 4.6.6, “mysqlaccess — Client for Checking Access Privileges”.
mysqlbinlog
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”.
mysqldumpslow
A utility to read and summarize the contents of a slow query
log. See Section 4.6.8, “mysqldumpslow — Summarize Slow Query Log Files”.
mysqlhotcopy
A utility that quickly makes backups of
MyISAM tables while the server is running.
See Section 4.6.9, “mysqlhotcopy — A Database Backup Program”.
mysqlmanager
The MySQL Instance Manager, a program for monitoring and
managing MySQL servers. See
Section 4.6.10, “mysqlmanager — The MySQL Instance Manager”.
Important
MySQL Instance Manager has been deprecated and is removed in
MySQL 5.4.
mysql_convert_table_format
A utility that converts tables in a database to use a given
storage engine. See
Section 4.6.11, “mysql_convert_table_format — Convert Tables to Use a Given Storage
Engine”.
mysql_explain_log
A utility that analyzes queries in the MySQL query log using
EXPLAIN See
Section 4.6.12, “mysql_explain_log — Use EXPLAIN on Statements in Query Log”.
mysql_find_rows
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.13, “mysql_find_rows — Extract SQL Statements from Files”.
mysql_fix_extensions
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 file names to a system with case-sensitive
file names. See Section 4.6.14, “mysql_fix_extensions — Normalize Table File Name Extensions”.
mysql_setpermission
A utility for interactively setting permissions in the MySQL
grant tables. See Section 4.6.15, “mysql_setpermission — Interactively Set Permissions in Grant
Tables”.
mysql_tableinfo
A utility that generates database metadata.
Section 4.6.16, “mysql_tableinfo — Generate Database Metadata”.
mysql_waitpid
A utility that kills the process with a given process ID. See
Section 4.6.17, “mysql_waitpid — Kill Process and Wait for Its Termination”.
mysql_zap
A utility that kills processes that match a pattern. See
Section 4.6.18, “mysql_zap — Kill Processes That Match a Pattern”.
MySQL program-development utilities:
Miscellaneous utilities:
Sun Microsystems, Inc. also provides several GUI tools for
administering and otherwise working with MySQL Server:
MySQL Workbench: This is the latest graphical tool for working
with MySQL databases.
MySQL Administrator: This tool is used for administering MySQL
servers, databases, tables, and user accounts.
MySQL Query Browser: This graphical tool is used 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.
For a full list of environment variables used by MySQL programs,
see Section 2.20, “Environment Variables”.
Use of MYSQL_PWD is insecure. See
Section 5.5.6.2, “End-User Guidelines for Password Security”.
4.2. Using MySQL Programs4.2.1. Invoking MySQL Programs
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
(“- ”,
“-- ”) specify program options.
Options typically indicate the type of connection a program should
make to the server or affect its operational mode. Option syntax
is described in Section 4.2.3, “Specifying Program Options”.
Nonoption arguments (arguments with no leading dash) provide
additional information to the program. For example, the
mysql program interprets the first nonoption
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 supports and describe the meaning of any
additional nonoption arguments.
Some options are common to a number of programs. The most
frequently used 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
user name 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). For more information on options that specify connection
options, see Section 4.2.2, “Connecting to the MySQL Server”.
You may find it necessary to invoke MySQL programs using the path
name 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 path name 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 path name. For example, if
mysql is installed in
/usr/local/mysql/bin , you can run the program
by invoking it as mysql, and it is not
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.4, “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.
4.2.2. Connecting to the MySQL Server
For a client program to be able to connect to the MySQL server, it
must use the proper connection parameters, such as the name of the
host where the server is running and the user name and password of
your MySQL account. Each connection parameter has a default value,
but you can override them as necessary using program options
specified either on the command line or in an option file.
The examples here use the mysql client program,
but the principles apply to other clients such as
mysqldump, mysqladmin, or
mysqlshow.
This command invokes mysql without specifying
any connection parameters explicitly:
shell> mysql
Because there are no parameter options, the default values apply:
The default host name is localhost . On
Unix, this has a special meaning, as described later.
The default user name is ODBC on Windows or
your Unix login name on Unix.
No password is sent if neither -p nor
--password is given.
For mysql, the first nonoption argument is
taken as the name of the default database. If there is no such
option, mysql does not select a default
database.
To specify the host name and user name explicitly, as well as a
password, supply appropriate options on the command line:
shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb
For password options, the password value is optional:
If you use a -p or
--password option and specify
the password value, there must be no
space between -p or
--password= and the password
following it.
If you use a -p or
--password option but do not
specify the password value, the client program prompts you to
enter the password. The password is not displayed as you enter
it. This is more secure than giving the password on the
command line. Other users on your system may be able to see a
password specified on the command line by executing a command
such as ps auxw. See
Section 5.5.6.2, “End-User Guidelines for Password Security”.
As just mentioned, including the password value on the command
line can be a security risk. To avoid this problem, specify the
--password or -p option without
any following password value:
shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb
When the password option has no password value, the client program
prints a prompt and waits for you to enter the password. (In these
examples, mydb is not
interpreted as a password because it is separated from the
preceding password option by a space.)
On some systems, the library routine that MySQL uses to prompt for
a password automatically limits the password to eight characters.
That is a problem with the system library, not with MySQL.
Internally, MySQL does not have any limit for the length of the
password. To work around the problem, change your MySQL password
to a value that is eight or fewer characters long, or put your
password in an option file.
On Unix, MySQL programs treat the host name
localhost specially, in a way that is likely
different from what you expect compared to other network-based
programs. For connections to localhost , MySQL
programs attempt to connect to the local server by using 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 host name 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. For
example:
shell> mysql --host=127.0.0.1
shell> mysql --protocol=TCP
The --protocol option enables you
to establish a particular type of connection even when the other
options would normally default to some other protocol.
On Windows, you can force a MySQL client to use a named-pipe
connection by specifying the
--pipe or
--protocol=PIPE option, or by
specifying . (period) as the host name. If
named-pipe connections are not enabled, an error occurs. Use the
--socket option to specify the
name of the pipe if you do not want to use the default pipe name.
Connections to remote servers always use TCP/IP. This command
connects to the server running on
remote.example.com using the default port
number (3306):
shell> mysql --host=remote.example.com
To specify a port number explicitly, use the
--port or -P
option:
shell> mysql --host=remote.example.com --port=13306
You can specify a port number for connections to a local server,
too. However, as indicated previously, connections to
localhost on Unix will use a socket file by
default. You will need to force a TCP/IP connection as already
described or any option that specifies a port number will be
ignored.
For this command, the program uses a socket file on Unix and the
--port option is ignored:
shell> mysql --port=13306 --host=localhost
To cause the port number to be used, invoke the program in either
of these ways:
shell> mysql --port=13306 --host=127.0.0.1
shell> mysql --port=13306 --protocol=TCP
The following list summarizes the options that can be used to
control how client programs connect to the server:
--host=host_name ,
-h host_name
The host where the server is running. The default value is
localhost .
--password[=pass_val ] ,
-p[pass_val ]
The password of the MySQL account. As described earlier, the
password value is optional, but if given, there must be
no space between -p or
--password= and the password
following it. The default is to send no password.
--pipe , -W
On Windows, connect to the server via a named pipe. The server
must be started with the
--enable-named-pipe option to
enable named-pipe connections.
--port=port_num ,
-P port_num
The port number to use for the connection, for connections
made via TCP/IP. The default port number is 3306.
--protocol={TCP|SOCKET|PIPE|MEMORY}
This option explicitly specifies a protocol to use for
connecting to the server. It is useful when the other
connection parameters normally would cause a protocol to be
used other than the one you want. For example, connections on
Unix to localhost are made via a Unix
socket file by default:
shell> mysql --host=localhost
To force a TCP/IP connection to be used instead, specify a
--protocol option:
shell> mysql --host=localhost --protocol=TCP
The following table shows the allowable
--protocol option values and
indicates the platforms on which each value may be used. The
values are not case sensitive.
--shared-memory-base-name=name
On Windows, the shared-memory name to use, for connections
made via shared memory to a local server. The default value is
MYSQL . The shared-memory name is case
sensitive.
The server must be started with the
--shared-memory option to
enable shared-memory connections.
--socket=file_name ,
-S file_name
On Unix, the name of the Unix socket file to use, for
connections made via a named pipe to a local server. The
default Unix socket file name is
/tmp/mysql.sock .
On Windows, the name of the named pipe to use, for connections
to a local server. The default Windows pipe name is
MySQL . The pipe name is not case sensitive.
The server must be started with the
--enable-named-pipe option to
enable named-pipe connections.
--ssl*
Options that begin with --ssl
are used for establishing a secure connection to the server
via SSL, if the server is configured with SSL support. For
details, see Section 5.5.7.3, “SSL Command Options”.
--user=user_name ,
-u user_name
The user name of the MySQL account you want to use. The
default user name is ODBC on Windows or
your Unix login name on Unix.
It is possible to specify different default values to be used when
you make a connection so that you need not enter them on the
command line each time you invoke a client program. This can be
done in a couple of ways:
4.2.3. Specifying Program Options
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.4, “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 unambiguous 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.
4.2.3.1. Using Options on the Command Line
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=pass_val
or as --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
-ppass_val or as
-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.
Within option names, dash
(“- ”) and underscore
(“_ ”) may be used
interchangeably. For example,
--skip-grant-tables and
--skip_grant_tables
are equivalent. (However, the leading dashes cannot be given
as underscores.)
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 17.2.5, “Safe Shutdown and Restart of MySQL Cluster”, for
an example.
4.2.3.2. Program Option Modifiers
Some options are “boolean” and 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.
4.2.3.3. Using Option Files
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.
On Windows, MySQL programs read startup options from the
following files.
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:\PROGRAMDIR \MySQL\MySQL
5.0 Server where
PROGRAMDIR 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.9.4.1, “Starting the MySQL Server Instance Configuration Wizard”.
On Unix, MySQL programs read startup options from the following
files.
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 path names
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.
Note
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 and you specify only one option per line. For example,
--quick and --host=localhost
on the command line should be specified as
quick and host=localhost
on separate lines in an option file. To specify an option of the
form
--loose-opt_name in
an option file, write it as
loose-opt_name .
Empty lines in option files are ignored. Nonempty 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
--opt_name on
the command line.
opt_name =value
This is equivalent to
--opt_name =value
on the command line. In an option file, you can have spaces
around the “= ” character,
something that is not true on the command line. You can
optionally enclose the value within single quotes or double
quotes, which is useful if the value contains a
“# ” comment character.
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 can 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. The escaping rules in option
files are:
If a backslash is followed by a valid escape sequence
character, the sequence is converted to the character
represented by the sequence. For example,
“\s ” is converted to a
space.
If a backslash is not followed by a valid escape sequence
character, it remains unchanged. For example,
“\S ” is retained as is.
The preceding rules mean that a literal backslash can be given
as “\\ ”, or as
“\ ” if it is not followed by a
valid escape sequence character.
The rules for escape sequences in option files differ slightly
from the rules for escape sequences in string literals in SQL
statements. In the latter context, if
“x ” is not a value
escape sequence character,
“\x ”
becomes “x ” rather than
“\x ”.
See Section 8.1.1, “Strings”.
The escaping rules for option file values are especially
pertinent for Windows path names, which use
“\ ” as a path name separator. A
separator in a Windows path name must be written as
“\\ ” if it is followed by an
escape sequence character. It can be written as
“\\ ” or
“\ ” if it is not. Alternatively,
“/ ” may be used in Windows path
names and will be treated as
“\ ”. Suppose that you want to
specify a base directory of C:\Program
Files\MySQL\MySQL Server 5.0 in an
option file. This can be done several ways. Some examples:
basedir="C:\Program Files\MySQL\MySQL Server 5.0"
basedir="C:\\Program Files\\MySQL\\MySQL Server 5.0"
basedir="C:/Program Files/MySQL/MySQL Server 5.0"
basedir=C:\\Program\sFiles\\MySQL\\MySQL\sServer\s5.0
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
var_name =value
syntax for the lines that set the
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.
Note
Currently, any files to be found and included using the
!includedir directive on Unix operating
systems must have file names 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
[group ] line that
indicates the program to which the options apply.
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.
4.2.3.3.1. Command-Line Options that Affect Option-File Handling
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 file names, you should avoid the use of the
“~ ” shell metacharacter
because it might not be interpreted as you expect.
--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 path name
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.
--defaults-file=file_name
Use only the given option file.
file_name is the full path name
to the file. If the file does not exist or is otherwise
inaccessible, the program will exit with an error.
--defaults-group-suffix=str
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
--defaults-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.
--no-defaults
Do not read any option files. If a program does not start
because it is reading unknown options from an option file,
--no-defaults can be used
to prevent the program from reading them.
--print-defaults
Print the program name and all options that it gets from
option files.
4.2.3.3.2. Preconfigured Option Files
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 .
Note
On Windows, the .ini or
.cnf 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.3.3, “Using Option Files”.
4.2.3.4. Using Options to Set Program Variables
Many MySQL programs have internal variables that can be set at
runtime using the
SET
statement. See Section 12.5.4, “SET Syntax”, and
Section 5.1.5, “Using System Variables”.
Most of these program variables also can be set at server
startup by using the same syntax that applies to specifying
program options. 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_allowed_packet 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 unambiguous now might become ambiguous in the
future.
Suffixes for specifying a value multiplier can be used when
setting a variable at server startup, but not to set the value
with SET
at runtime. On the other hand, with
SET you
can assign a variable's value using an expression, which is not
true when you set a variable at server startup. For example, the
first of the following lines is legal at server startup, but the
second is not:
shell> mysql --max_allowed_packet=16M
shell> mysql --max_allowed_packet=16*1024*1024
Conversely, the second of the following lines is legal at
runtime, but the first is not:
mysql> SET GLOBAL max_allowed_packet=16M;
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;
Note
Before MySQL 4.0.2, the only syntax for setting program
variables was
--set-variable=option =value
(or
set-variable=option =value
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.
4.2.3.5. Option Defaults, Options Expecting Values, and the =
Sign
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 user name
“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.91 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 host name, --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 option has the
default value
host_name .err ,
where host_name is the name of the
host on which MySQL is running. Assume that you are running
MySQL on a computer whose host name 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-error &
[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-error 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)
However, 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.91 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.91 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)
4.2.4. Setting Environment Variables
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.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
nonlogin shells, so you might want to add the setting to
.bashrc for login shells and to
.bash_profile for nonlogin 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.
4.3. MySQL Server and Server-Startup Programs
This section describes mysqld, the MySQL server,
and several programs that are used to start the server.
4.3.1. mysqld — The MySQL 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.
4.3.2. mysqld_safe — MySQL Server Startup Script
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.
Note
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, “Server Command Options”.
Options unknown to mysqld_safe are passed to
mysqld if they are specified on the command
line, but ignored if they are specified in the
[mysqld_safe] group of an option file. See
Section 4.2.3.3, “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.
It also reads option files and supports the options for
processing them described at
Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
Table 4.1. mysqld_safe Options
--help
Display a help message and exit. (Added in MySQL 5.0.3)
--autoclose
(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.
--basedir=path
The path to the MySQL installation directory.
--core-file-size=size
The size of the core file that mysqld
should be able to create. The option value is passed to
ulimit -c.
--datadir=path
The path to the data directory.
--defaults-extra-file=path
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.
--defaults-file=file_name
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.
--ledir=path
If mysqld_safe cannot find the server,
use this option to indicate the path name to the directory
where the server is located.
--log-error=file_name
Write the error log to the given file. See
Section 5.2.1, “The Error Log”.
--mysqld=prog_name
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 path name to the directory where the server is
located.
--mysqld-version=suffix
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.
--nice=priority
Use the nice program to set the server's
scheduling priority to the given value.
--no-defaults
Do not read any option files. This must be the first option
on the command line if it is used.
--open-files-limit=count
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!
--pid-file=file_name
The path name of the process ID file.
--port=port_num
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.
--skip-kill-mysqld
Do not try to kill stray mysqld processes
at startup. This option works only on Linux.
--socket=path
The Unix socket file that the server should use when
listening for local connections.
--timezone=timezone
Set the TZ time zone environment variable
to the given option value. Consult your operating system
documentation for legal time zone specification formats.
--user={user_name |user_id }
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-file 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.7, “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 path names. 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 mysql_installation_directory
shell> 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
host_name .err
file in the data directory.
Sends mysqld_safe screen output to the
host_name .safe
file in the data directory.
4.3.3. mysql.server — MySQL Server Startup Script
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-VERSION .rpm ),
the mysql.server script will be installed in
the /etc/init.d directory with the name
mysql . You need not install it manually.
See Section 2.10, “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.17.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 supports the following options.
--basedir=path
The path to the MySQL installation directory.
--datadir=path
The path to the MySQL data directory.
--pid-file=file_name
The path name 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
Use mysqld_safe to start the server. This
is the default. This option was added in MySQL 5.0.4.
--use-manager
Use Instance Manager to start the server. This option was
added in MySQL 5.0.4.
--user=user_name
The login user name to use for running
mysqld. This option was added in MySQL
5.0.4.
4.3.4. mysqld_multi — Manage Multiple MySQL Servers
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.10, “mysqlmanager — The MySQL Instance Manager”).
mysqld_multi searches for groups named
[mysqldN ] in
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.17.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:
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=file_name
option, if one is given. A file named by
--config-file is read only
for [mysqldN ]
option groups, not the [mysqld_multi] group.
Option files read are searched for
[mysqld_multi] and
[mysqldN ] option
groups. The [mysqld_multi] group can be used
for options to mysqld_multi itself.
[mysqldN ] groups
can be used for options passed to specific
mysqld instances.
As of MySQL 5.0.82, the [mysqld] or
[mysqld_safe] groups can be used for common
options read by all instances of mysqld or
mysqld_safe. You can specify a
--defaults-file=file_name
option to use a different configuration file for that instance,
in which case the [mysqld] or
[mysqld_safe] groups from that file will be
used for that instance. Before MySQL 5.0.82, some versions of
mysqld_multi pass the
--no-defaults options to
instances, so these techniques are inapplicable.
mysqld_multi supports the following options.
--help
Display a help message and exit.
--config-file=file_name
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
[mysqldN ]
option groups. Without this option, all options are read
from the usual 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.
--example
Display a sample option file.
--log=file_name
Specify the name of the log file. If the file exists, log
output is appended to it.
--mysqladmin=prog_name
The mysqladmin binary to be used to stop
servers.
--mysqld=prog_name
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
[mysqldN ]
option group. These options indicate the name of the server
that mysqld_safe should start and the
path name 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:
[mysqld38]
mysqld = mysqld-debug
ledir = /opt/local/mysql/libexec
--no-log
Print log information to stdout rather
than to the log file. By default, output goes to the log
file.
--password=password
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
Silent mode; disable warnings.
--tcp-ip
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.
--user=user_name
The user name of the MySQL account to use when invoking
mysqladmin.
--verbose
Be more verbose.
--version
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”.
Important
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 user
name 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 user names or passwords for the administrative
accounts, you might want to create an account on each server
that has the same user name 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, “The MySQL Access Privilege System”. You have to do this
for each mysqld server. Change the
connection parameters appropriately when connecting to each
one. Note that the host name 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-address 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 [mysqldN ]
groups were intentionally omitted from the example to illustrate
that you can have “gaps” in the option file. This
gives you more flexibility.
# 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
See Section 4.2.3.3, “Using Option Files”.
4.4. MySQL Installation-Related Programs
The programs in this section are used when installing or upgrading
MySQL.
4.4.1. comp_err — Compile MySQL Error Message File
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 supports the following options.
--help , -?
Display a help message and exit.
--charset=path ,
-C path
The character set directory. The default is
../sql/share/charsets .
--debug=debug_options ,
-# debug_options
Write a debugging log. A typical
debug_options string is
'd:t:O,file_name ' .
The default is
'd:t:O,/tmp/comp_err.trace' .
--debug-info ,
-T
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 .
--out_dir=path ,
-D path
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 .
--version ,
-V
Display version information and exit.
4.4.2. make_win_bin_dist — Package MySQL Distribution as ZIP Archive
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 supports the following
options.
--debug
Pack the debug binaries and produce an error if they were
not built.
--embedded
Pack the embedded server and produce an error if it was not
built. The default is to pack it if it was built.
--exe-suffix=suffix
Add a suffix to the basename of the mysql
binary. For example, a suffix of -abc
produces a binary named mysqld-abc.exe.
--no-debug
Do not pack the debug binaries even if they were built.
--no-embedded
Do not pack the embedded server even if it was built.
--only-debug
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).
4.4.3. make_win_src_distribution — Create Source Distribution for Windows
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.16.6.5, “Creating a Windows Source Package from the Bazaar 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.
--dirname
Directory name to copy files (intermediate).
--silent
Do not print verbose list of files processed.
--suffix
The suffix name for the package.
--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-VERSION -win-src.zip ,
where VERSION represents the
version of your MySQL source tree.
--tmp
Specify the temporary location.
4.4.4. mysqlbug — Generate Bug Report
This program enables you to generate a bug report and send it to
Sun Microsystems, Inc. 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.
4.4.5. mysql_fix_privilege_tables — Upgrade MySQL System Tables
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.
mysql_fix_privilege_tables is an older script
that previously was used to uprade the system tables in the
mysql database after a MySQL upgrade.
Before running mysql_fix_privilege_tables,
make a backup of your mysql database.
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
any changes made to the system tables take effect.
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
Note
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 path names 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.
4.4.6. mysql_install_db — Initialize MySQL Data Directory
mysql_install_db initializes the MySQL data
directory and creates the system tables that it contains, if
they do not exist.
To invoke mysql_install_db, use the following
syntax:
shell> mysql_install_db [options ]
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 user name that mysqld will run
as. It might be necessary to specify other options such as
--basedir or
--datadir if
mysql_install_db does not use the correct
locations for the installation directory or data directory. For
example:
shell> bin/mysql_install_db --user=mysql \
--basedir=/opt/mysql/mysql \
--datadir=/opt/mysql/mysql/data
mysql_install_db needs to invoke
mysqld with the
--bootstrap and
--skip-grant-tables options (see
Section 2.16.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 path name of a server that has all options enabled.
mysql_install_db will use that server.
mysql_install_db supports the following
options, which can be specified on the command line or in the
[mysql_install_db] and (if they are common to
mysqld) [mysqld] option
file groups.
--basedir=path
The path to the MySQL installation directory.
--force
Cause mysql_install_db to run even if DNS
does not work. In that case, grant table entries that
normally use host names 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 host names 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 user name 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.
4.4.7. mysql_secure_installation — Improve MySQL Installation Security
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.
4.4.8. mysql_tzinfo_to_sql — Load the Time Zone Tables
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
(/usr/share/lib/zoneinfo on Solaris). If
your system does not have a zoneinfo database, you can use the
downloadable package described in
Section 9.7, “MySQL Server Time Zone Support”.
mysql_tzinfo_to_sql can be invoked several
ways:
shell> mysql_tzinfo_to_sql tz_dir
shell> mysql_tzinfo_to_sql tz_file tz_name
shell> mysql_tzinfo_to_sql --leap tz_file
For the first invocation syntax, pass the zoneinfo directory
path name 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.
4.4.9. mysql_upgrade — Check Tables for MySQL Upgrade
mysql_upgrade examines all tables in all
databases for incompatibilities with the current version of
MySQL Server. mysql_upgrade also upgrades the
system tables so that you can take advantage of new privileges
or capabilities that might have been added.
mysql_upgrade should be executed each time
you upgrade MySQL. It supersedes the older
mysql_fix_privilege_tables script, which
should no longer be used.
If a table is found to have a possible incompatibility,
mysql_upgrade performs a table check. If any
problems are found, a table repair is attempted. If the table
cannot be repaired, see Section 2.18.4, “Rebuilding or Repairing Tables or Indexes” for
manual table repair strategies.
Caution
You should always back up your current MySQL installation
before performing an upgrade. See
Section 6.2, “Database Backup Methods”.
Some upgrade incompatibilities may require special handling
before you upgrade your MySQL installation and run
mysql_upgrade. See
Section 2.18.1, “Upgrading MySQL”, for instructions on determining
whether any such incompatibilities apply to your installation
and how to handle them.
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 any changes made to the system tables
take effect.
mysql_upgrade executes the following commands
to check and repair tables and to upgrade the system tables:
mysqlcheck --all-databases --check-upgrade --auto-repair
mysql < fix_priv_tables
Notes about the preceding commands:
Because mysql_upgrade invokes
mysqlcheck with the
--all-databases option,
it processes all tables in all databases, which might take a
long time to complete. Each table is locked and therefore
unavailable to other sessions while it is being processed.
Check and repair operations can be time-consuming,
particularly for large tables.
For details about what checks the
--check-upgrade option
entails, see the description of the FOR
UPGRADE option of the CHECK
TABLE statement (see
Section 12.5.2.3, “CHECK TABLE Syntax”).
fix_priv_tables represents a
script generated internally by
mysql_upgrade that contains SQL
statements to upgrade the tables in the
mysql database.
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 whether all
tables have been checked for this release so that table-checking
can be skipped. To ignore this file and perform the check
regardless, use the
--force option.
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.10, “Installing MySQL from RPM Packages on Linux”.)
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.
mysql_upgrade supports the following options,
which can be specified on the command line or in the
[mysql_upgrade] and
[client] option file groups. Other options
are passed to mysqlcheck. For example, it
might be necessary to specify the
--password[=password ]
option. mysql_upgrade also supports the
options for processing option files described at
Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
--help
Display a short help message and exit.
--basedir=path
The path to the MySQL installation directory. This option is
accepted for backward compatibility but ignored.
--datadir=path
The path to the data directory. This option is accepted for
backward compatibility but ignored.
--force
Ignore the mysql_upgrade_info file and
force execution of mysqlcheck even if
mysql_upgrade has already been executed
for the current version of MySQL.
--tmpdir=path ,
-t path
The path name of the directory to use for creating temporary
files. This option was added in MySQL 5.0.62.
--user=user_name ,
-u user_name
The MySQL user name to use when connecting to the server.
The default user name is root .
--verbose
Verbose mode. Print more information about what the program
does.
4.5. MySQL Client Programs
This section describes client programs that connect to the MySQL
server.
4.5.1. mysql — The MySQL Command-Line Tool
mysql is a simple SQL shell (with GNU
readline capabilities). It supports
interactive and noninteractive use. When used interactively,
query results are presented in an ASCII-table format. When used
noninteractively (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, which
can be specified on the command line or in the
[mysql] and [client]
option file groups. mysql also supports the
options for processing option files described at
Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
--help , -?
Display a help message and exit.
--auto-rehash
Enable automatic rehashing. This option is on by default,
which enables database, table, and column name completion.
Use
--disable-auto-rehash
to disable rehashing. That causes mysql
to start faster, but you must issue the
rehash command if you want to use name
completion.
To complete a name, enter the first part and press Tab. If
the name is unambiguous, mysql completes
it. Otherwise, you can press Tab again to see the possible
names that begin with what you have typed so far. Completion
does not occur if there is no default database.
--batch , -B
Print results using tab as the column separator, with each
row on a new line. With this option,
mysql does not use the history file.
Batch mode results in nontabular output format and escaping
of special characters. Escaping may be disabled by using raw
mode; see the description for the
--raw option.
--character-sets-dir=path
The directory where character sets are installed. See
Section 9.2, “The Character Set Used for Data and Sorting”.
--column-names
Write column names in results.
--comments ,
-c
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 ,
-C
Compress all information sent between the client and the
server if both support compression.
--database=db_name ,
-D db_name
The database to use. This is useful primarily in an option
file.
--debug[=debug_options ] ,
-#
[debug_options ]
Write a debugging log. A typical
debug_options string is
'd:t:o,file_name ' .
The default is 'd:t:o,/tmp/mysql.trace' .
--debug-info ,
-T
Print some debugging information when the program exits.
--default-character-set=charset_name
Use charset_name as the default
character set for the client and connection.
A common issue that can occur when the operating system uses
utf8 or another multi-byte character set
is that output from the mysql client is
formatted incorrectly, due to the fact that the MySQL client
uses the latin1 character set by default.
You can usually fix such issues by using this option to
force the client to use the system character set instead.
See Section 9.2, “The Character Set Used for Data and Sorting”, for more
information.
--delimiter=str
Set the statement delimiter. The default is the semicolon
character (“; ”).
--disable-named-commands
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”.
--execute=statement ,
-e statement
Execute the statement and quit. The default output format is
like that produced with
--batch . See
Section 4.2.3.1, “Using Options on the Command Line”, for some examples.
--force , -f
Continue even if an SQL error occurs.
--host=host_name ,
-h host_name
Connect to the MySQL server on the given host.
--html , -H
Produce HTML output.
--ignore-spaces ,
-i
Ignore spaces after function names. The effect of this is
described in the discussion for the
IGNORE_SPACE SQL mode (see
Section 5.1.7, “Server SQL Modes”).
--line-numbers
Write line numbers for errors. Disable this with
--skip-line-numbers .
--local-infile[={0|1}]
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.
--named-commands ,
-G
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”.
--no-auto-rehash ,
-A
This has the same effect as
-skip-auto-rehash .
See the description for
--auto-rehash .
--no-beep , -b
Do not beep when errors occur.
--no-named-commands ,
-g
Deprecated, use
--disable-named-commands
instead.
--no-pager
Deprecated form of
--skip-pager .
See the --pager option.
--no-tee
Do not copy output to a file.
Section 4.5.1.2, “mysql Commands”, discusses tee files
further.
--one-database ,
-o
Ignore statements except those for the default database
named on the command line. This is useful for skipping
updates to other databases in the binary log.
--pager[=command ]
Use the given command for paging query output. If the
command is omitted, the default pager is the value of your
PAGER environment variable. Valid pagers
are less, more,
cat [> filename], and so forth. This
option works only on Unix and only in interactive 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,
mysql prompts for one.
Specifying a password on the command line should be
considered insecure. See
Section 5.5.6.2, “End-User Guidelines for Password Security”. You can use an
option file to avoid giving the password on the command
line.
--pipe , -W
On Windows, connect to the server via a named pipe. This
option applies only if the server supports named-pipe
connections.
--port=port_num ,
-P port_num
The TCP/IP port number to use for the connection.
--prompt=format_str
Set the prompt to the specified format. The default is
mysql> . The special sequences that the
prompt can contain are described in
Section 4.5.1.2, “mysql Commands”.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the allowable values, see
Section 4.2.2, “Connecting to the MySQL Server”.
--quick , -q
Do not cache each query result, print each row as it is
received. This may slow down the server if the output is
suspended. With this option, mysql does
not use the history file.
--raw , -r
For tabular output, the “boxing” around columns
enables one column value to be distinguished from another.
For nontabular output (such as is produced in batch mode or
when the --batch or
--silent option is given),
special characters are escaped in the output so they can be
identified easily. Newline, tab, NUL , and
backslash are written as \n ,
\t , \0 , and
\\ . The
--raw option disables this
character escaping.
The following example demonstrates tabular versus nontabular
output and the use of raw mode to disable escaping:
% mysql
mysql> SELECT CHAR(92);
+----------+
| CHAR(92) |
+----------+
| \ |
+----------+
% mysql -s
mysql> SELECT CHAR(92);
CHAR(92)
\\
% mysql -s -r
mysql> SELECT CHAR(92);
CHAR(92)
\
--reconnect
If the connection to the server is lost, automatically try
to reconnect. A single reconnect attempt is made each time
the connection is lost. To suppress reconnection behavior,
use
--skip-reconnect .
--safe-updates ,
--i-am-a-dummy ,
-U
Allow only those UPDATE and
DELETE statements that
specify 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.
--secure-auth
Do not send passwords to the server in old (pre-4.1.1)
format. This prevents connections except for servers that
use the newer password format.
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.
--show-warnings
Cause warnings to be shown after each statement if there are
any. This option applies to interactive and batch mode. This
option was added in MySQL 5.0.6.
--sigint-ignore
Ignore SIGINT signals (typically the
result of typing Control-C).
--silent , -s
Silent mode. Produce less output. This option can be given
multiple times to produce less and less output.
This option results in nontabular output format and escaping
of special characters. Escaping may be disabled by using raw
mode; see the description for the
--raw option.
--skip-column-names ,
-N
Do not write column names in results.
--skip-line-numbers ,
-L
Do not write line numbers for errors. Useful when you want
to compare result files that include error messages.
--socket=path ,
-S path
For connections to localhost , the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
--ssl*
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”.
--table , -t
Display output in table format. This is the default for
interactive use, but can be used to produce table output in
batch mode.
--tee=file_name
Append a copy of output to the given file. This option works
only in interactive mode. Section 4.5.1.2, “mysql Commands”,
discusses tee files further.
--unbuffered ,
-n
Flush the buffer after each query.
--user=user_name ,
-u user_name
The MySQL user name to use when connecting to the server.
--verbose , -v
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.)
--version , -V
Display version information and exit.
--vertical ,
-E
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 .
--wait , -w
If the connection cannot be established, wait and retry
instead of aborting.
--xml , -X
Produce XML output.
Note
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
Section 4.5.4, “mysqldump — A Database Backup Program” 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
--var_name =value .
The --set-variable format is deprecated.
connect_timeout
The number of seconds before connection timeout. (Default
value is 0 .)
max_allowed_packet
The maximum packet length to send to or receive from the
server. (Default value is 16MB.)
max_join_size
The automatic limit for rows in a join when using
--safe-updates . (Default value
is 1,000,000.)
net_buffer_length
The buffer size for TCP/IP and socket communication.
(Default value is 16KB.)
select_limit
The automatic limit for
SELECT statements when using
--safe-updates . (Default value
is 1,000.)
It is also possible to set variables by using
--var_name =value .
The --set-variable format is deprecated.
On Unix, the mysql client writes a record of
executed statements to a history file. By default, this 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.
The .mysql_history should be protected with
a restrictive access mode because sensitive information might be
written to it, such as the text of SQL statements that contain
passwords. See Section 5.5.6.2, “End-User Guidelines for Password Security”.
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'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement 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.
charset (\C) Switch to another charset. Might be needed for processing
binlog with multi-byte charsets.
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.
help [arg ],
\h [arg ],
\? [arg ],
? [arg ]
Display a help message listing the available
mysql commands.
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”.
charset
charset_name ,
\C
charset_name
Change the default character set and issue 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 specified character
set is used for reconnects. This command was added in MySQL
5.0.19.
clear, \c
Clear the current input. Use this if you change your mind
about executing the statement that you are entering.
connect [db_name
host_name ]],
\r [db_name
host_name ]]
Reconnect to the server. The optional database name and host
name arguments may be given to specify the default database
or the host where the server is running. If omitted, the
current values are used.
delimiter str ,
\d str
Change the string that mysql interprets
as the separator between SQL statements. The default is the
semicolon character (“; ”).
The delimiter can be specified as an unquoted or quoted
argument. Quoting can be done with either single quote
(' ) or douple quote
(" ) characters. To include a quote within
a quoted string, either quote the string with the other
quote character or escape the quote with a backslash
(“\ ”) character. Backslash
should be avoided outside of quoted strings because it is
the escape character for MySQL. For an unquoted argument,
the delmiter is read up to the first space or end of line.
For a quoted argument, the delimiter is read up to the
matching quote on the line.
When the delimiter recognized by mysql is
set to something other than the default of
“; ”, instances of that
character are sent to the server without interpretation.
However, the server itself still interprets
“; ” as a statement delimiter
and processes statements accordingly. This behavior on the
server side comes into play for multiple-statement execution
(see Section 20.8.12, “C API Support for Multiple Statement Execution”), and for
parsing the body of stored procedures and functions and
triggers (see Section 18.1, “Defining Stored Programs”).
edit, \e
Edit the current input statement. mysql
checks the values of the EDITOR and
VISUAL environment variables to determine
which editor to use. The default editor is
vi if neither variable is set.
The edit command works only in Unix.
ego, \G
Send the current statement to the server to be executed and
display the result using vertical format.
exit, \q
Exit mysql.
go, \g
Send the current statement to the server to be executed.
nopager, \n
Disable output paging. See the description for
pager.
The nopager command works only in Unix.
notee, \t
Disable output copying to the tee file. See the description
for tee.
nowarning, \w
Enable display of warnings after each statement. This
command was added in MySQL 5.0.6.
pager
[command ], \P
[command ]
Enable output paging. By using the
--pager option when you invoke
mysql, 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. Pager functionality works only in interactive
mode.
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 it is not as
convenient as pager for browsing output
in some situations.
print, \p
Print the current input statement without executing it.
prompt [str ],
\R [str ]
Reconfigure the mysql prompt to the given
string. The special character sequences that can be used in
the prompt are described later in this section.
If you specify the prompt command with no
argument, mysql resets the prompt to the
default of mysql> .
quit, \q
Exit mysql.
rehash, \#
Rebuild the completion hash that enables database, table,
and column name completion while you are entering
statements. (See the description for the
--auto-rehash option.)
source
file_name , \.
file_name
Read the named file and executes the statements contained
therein. On Windows, you can specify path name separators as
/ or \\ .
status, \s
Provide status 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.
system
command , \!
command
Execute the given command using your default command
interpreter.
The system command works only in Unix.
tee
[file_name ],
\T [file_name ]
By using the --tee option when
you invoke mysql, you can log statements
and their output. All the data displayed on the screen is
appended into a given file. This can be very useful for
debugging purposes also. mysql flushes
results to the file after each statement, just before it
prints its next prompt. Tee functionality works only in
interactive mode.
You can enable this feature interactively with the
tee command. Without a parameter, the
previous file is used. The tee file can
be disabled with the notee command.
Executing tee again re-enables logging.
use db_name ,
\u db_name
Use db_name as the default
database.
warnings, \W
Enable display of warnings after each statement (if there
are any). This command was added in MySQL 5.0.6.
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
The -F and -X options may
be used with less to cause it to exit if
output fits on one screen, which is convenient when no
scrolling is necessary:
mysql> pager less -n -i -S -F -X
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 file
systems 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 prompt command reconfigures the default
mysql> prompt. The string for defining the
prompt can contain the following special sequences.
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. (The rules for escape sequences in option
files are listed in Section 4.2.3.3, “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>
4.5.1.3. mysql Server-Side Helpmysql> 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.8, “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-binary-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 |
+---------------+-----------+
4.5.1.4. Executing SQL Statements from a Text File
The mysql client typically is used
interactively, like this:
shell> mysql db_name
However, it is also possible to put your SQL statements in a
file and then tell mysql to read its input
from that file. To do so, create a text file
text_file that contains the
statements you wish to execute. Then invoke
mysql as shown here:
shell> mysql db_name < text_file
If you place a USE
db_name statement as the
first statement in the file, it is unnecessary to specify the
database name on the command line:
shell> mysql < text_file
If you are already running mysql, you can
execute an SQL script file using the source
command or \. command:
mysql> source file_name
mysql> \. 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> .
You can also invoke mysql with the
--verbose option, which causes
each statement to be displayed before the result that it
produces.
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.
4.5.1.5.1. Displaying Query Results Vertically
Some query results are much more readable when displayed
vertically, instead of in the usual horizontal table format.
Queries can be displayed vertically by terminating the query
with \G instead of a semicolon. For example, longer text
values that include newlines often are much easier to read
with vertical output:
mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
*************************** 1. row ***************************
msg_nro: 3068
date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
reply: monty@no.spam.com
mail_to: "Thimble Smith" <tim@no.spam.com>
sbj: UTF-8
txt: >>>>> "Thimble" == Thimble Smith writes:
Thimble> Hi. I think this is a good idea. Is anyone familiar
Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my
Thimble> TODO list and see what happens.
Yes, please do that.
Regards,
Monty
file: inbox-jani-1
hash: 190402944
1 row in set (0.09 sec)
For beginners, a useful startup option is
--safe-updates (or
--i-am-a-dummy ,
which has the same effect). It is helpful for cases when you
might have issued a DELETE FROM
tbl_name statement but
forgotten the WHERE clause. Normally, such
a statement deletes all rows from the table. With
--safe-updates , you can delete
rows only by specifying the key values that identify them.
This helps prevent accidents.
When you use the --safe-updates
option, mysql issues the following
statement when it connects to the MySQL server:
SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;
See Section 5.1.4, “Session System Variables”.
The SET
statement has the following effects:
You are not allowed to execute an
UPDATE or
DELETE statement unless you
specify a key constraint in the WHERE
clause or provide a LIMIT clause (or
both). For example:
UPDATE tbl_name SET not_key_column =val WHERE key_column =val ;
UPDATE tbl_name SET not_key_column =val LIMIT 1;
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
4.5.1.5.3. Disabling mysql Auto-Reconnect
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 20.8.11, “Controlling Automatic Reconnection Behavior”.
4.5.2. mysqladmin — Client for Administering a MySQL Server
mysqladmin is a client for performing
administrative operations. You can use it to check the server's
configuration and current status, to create and drop databases,
and more.
Invoke mysqladmin like this:
shell> mysqladmin [options ] command [command-arg ] [command [command-arg ]] ...
mysqladmin supports the following commands.
Some of the commands take an argument following the command
name.
create db_name
Create a new database named
db_name .
debug
Tell the server to write debug information to the error log.
drop db_name
Delete the database named db_name
and all its tables.
extended-status
Display the server status variables and their values.
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-hosts
Flush all information in the host cache.
flush-logs
Flush all logs.
flush-privileges
Reload the grant tables (same as reload ).
flush-status
Clear status variables.
flush-tables
Flush all tables.
flush-threads
Flush the thread cache.
kill
id ,id ,...
Kill server threads. If multiple thread ID values are given,
there must be no spaces in the list.
old-password
new-password
This is like the password command but
stores the password using the old (pre-4.1) password-hashing
format. (See Section 5.5.6.3, “Password Hashing in MySQL”.)
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.
password
new-password
Set a new password. This changes the password to
new-password for the account that
you use with mysqladmin for connecting to
the server. 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"
Caution
Do not use this command used if the server was started
with the
--skip-grant-tables option.
No password change will be applied. This is true even if
you precede the password command with
flush-privileges on the same command
line to re-enable the grant tables because the flush
operation occurs after you connect. However, you can use
mysqladmin flush-privileges to
re-enable the grant table and then use a separate
mysqladmin password command to change
the password.
ping
Check whether the server is alive. The return status from
mysqladmin is 0 if the server is running,
1 if it is not. This is 0 even in case of an error such as
Access denied , because this means that
the server is running but refused the connection, which is
different from the server not running.
processlist
Show a list of active server threads. This is like the
output of the SHOW
PROCESSLIST statement. If the
--verbose option is
given, the output is like that of
SHOW FULL
PROCESSLIST . (See
Section 12.5.5.27, “SHOW PROCESSLIST Syntax”.)
reload
Reload the grant tables.
refresh
Flush all tables and close and open log files.
shutdown
Stop the server.
start-slave
Start replication on a slave server.
status
Display a short server status message.
stop-slave
Stop replication on a slave server.
variables
Display the server system variables and their values.
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.
version
Display version information from the server.
All commands can be shortened to any unique prefix. For example:
shell> mysqladmin proc stat
+----+-------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+----+---------+------+-------+------------------+
| 51 | monty | localhost | | Query | 0 | | show processlist |
+----+-------+-----------+----+---------+------+-------+------------------+
Uptime: 1473624 Threads: 1 Questions: 39487
Slow queries: 0 Opens: 541 Flush tables: 1
Open tables: 19 Queries per second avg: 0.0268
The mysqladmin status command result displays
the following values:
Uptime
The number of seconds the MySQL server has been running.
Threads
The number of active threads (clients).
Questions
The number of questions (queries) from clients since the
server was started.
Slow queries
The number of queries that have taken more than
long_query_time seconds.
See Section 5.2.4, “The Slow Query Log”.
Opens
The number of tables the server has opened.
Flush tables
The number of flush-* ,
refresh , and reload
commands the server has executed.
Open tables
The number of tables that currently are open.
Memory in use
The amount of memory allocated directly by
mysqld. This value is displayed only when
MySQL has been compiled with
--with-debug=full .
Maximum memory used
The maximum amount of memory allocated directly by
mysqld. 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,
which can be specified on the command line or in the
[mysqladmin] and [client]
option file groups. mysqladmin also supports
the options for processing option files described at
Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
Table 4.3. mysqladmin Options
--help ,
-?
Display a help message and exit.
--character-sets-dir=path
The directory where character sets are installed. See
Section 9.2, “The Character Set Used for Data and Sorting”.
--compress ,
-C
Compress all information sent between the client and the
server if both support compression.
--count=N ,
-c N
The number of iterations to make for repeated command
execution if the --sleep
option is given.
--debug[=debug_options ] ,
-#
[debug_options ]
Write a debugging log. A typical
debug_options string is
'd:t:o,file_name ' .
The default is
'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”.
--force ,
-f
Do not ask for confirmation for the drop
db_name command. With
multiple commands, continue even if an error occurs.
--host=host_name ,
-h host_name
Connect to the MySQL server on the given host.
--password[=password ] ,
-p[password ]
The password to use when connecting to the server. If you
use the short option form (-p ), you
cannot have a space between the option
and the password. If you omit the
password value following the
--password or
-p option on the command line,
mysqladmin prompts for one.
Specifying a password on the command line should be
considered insecure. See
Section 5.5.6.2, “End-User Guidelines for Password Security”. You can use an
option file to avoid giving the password on the command
line.
--pipe ,
-W
On Windows, connect to the server via a named pipe. This
option applies only if the server supports named-pipe
connections.
--port=port_num ,
-P port_num
The TCP/IP port number to use for the connection.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the allowable values, see
Section 4.2.2, “Connecting to the MySQL Server”.
--relative ,
-r
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.
--silent ,
-s
Exit silently if a connection to the server cannot be
established.
--sleep=delay ,
-i delay
Execute commands repeatedly, sleeping for
delay seconds in between. The
--count option determines
the number of iterations. If
--count is not given,
mysqladmin executes commands indefinitely
until interrupted.
--socket=path ,
-S path
For connections to localhost , the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
--ssl*
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 user name to use when connecting to the server.
--verbose ,
-v
Verbose mode. Print more information about what the program
does.
--version ,
-V
Display version information and exit.
--vertical ,
-E
Print output vertically. This is similar to
--relative , but prints
output vertically.
--wait[=count ] ,
-w[count ]
If the connection cannot be established, wait and retry
instead of aborting. If 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
--var_name =value
The --set-variable format is deprecated.
syntax:
It is also possible to set variables by using
--var_name =value .
The --set-variable format is deprecated.
4.5.3. mysqlcheck — A Table Maintenance Program
The mysqlcheck client performs table
maintenance: It checks, repairs, optimizes, or analyzes tables.
Each table is locked and therefore unavailable to other sessions
while it is being processed, although for check operations, the
table is locked with a READ lock only (see
Section 12.4.5, “LOCK TABLES and
UNLOCK
TABLES Syntax”, for more information about
READ and WRITE locks).
Table maintenance operations can be time-consuming, particularly
for large tables. If you use the
--databases or
--all-databases option to
process all tables in one or more databases, an invocation of
mysqlcheck might take a long time. (This is
also true for mysql_upgrade because that
program invokes mysqlcheck to check all
tables and repair them if necessary.)
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 perform table maintenance.
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 Section 12.5.2, “Table Maintenance Statements”.
The MyISAM storage engine supports all four
maintenance operations, so mysqlcheck can be
used to perform any of them 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
If mysqlcheck is unable to repair a table,
see Section 2.18.4, “Rebuilding or Repairing Tables or Indexes” for manual table repair
strategies. This will be the case, for example, for
InnoDB tables, which can be checked with
CHECK TABLE , but not repaired
with REPAIR TABLE .
Caution
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 file system errors.
There are three general ways to invoke
mysqlcheck:
shell> mysqlcheck [options ] db_name [tbl_name ...]
shell> mysqlcheck [options ] --databases db_name ...
shell> 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.
mysqlcheck supports the following options,
which can be specified on the command line or in the
[mysqlcheck] and [client]
option file groups. mysqlcheck also supports
the options for processing option files described at
Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
Table 4.4. mysqlcheck Options
--help ,
-?
Display a help message and exit.
--all-databases ,
-A
Check all tables in all databases. This is the same as using
the --databases option
and naming all the databases on the command line.
--all-in-1 ,
-1
Instead of issuing a statement for each table, execute a
single statement for each database that names all the tables
from that database to be processed.
--analyze ,
-a
Analyze the tables.
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.
--auto-repair
If a checked table is corrupted, automatically fix it. Any
necessary repairs are done after all tables have been
checked.
--character-sets-dir=path
The directory where character sets are installed. See
Section 9.2, “The Character Set Used for Data and Sorting”.
--check ,
-c
Check the tables for errors. This is the default operation.
--check-only-changed ,
-C
Check only tables that have changed since the last check or
that have not been closed properly.
--check-upgrade ,
-g
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
Compress all information sent between the client and the
server if both support compression.
--databases ,
-B
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
'd:t:o,file_name ' .
The default is 'd:t:o' .
--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”.
--extended ,
-e
If you are using this option to check tables, it ensures
that they are 100% consistent but takes a long time.
If you are using this option to repair tables, it runs an
extended repair that may not only take a long time to
execute, but may produce a lot of garbage rows also!
--fast ,
-F
Check only tables that have not been closed properly.
--force ,
-f
Continue even if an SQL error occurs.
--host=host_name ,
-h host_name
Connect to the MySQL server on the given host.
--medium-check ,
-m
Do a check that is faster than an
--extended operation.
This finds only 99.99% of all errors, which should be good
enough in most cases.
--optimize ,
-o
Optimize the tables.
--password[=password ] ,
-p[password ]
The password to use when connecting to the server. If you
use the short option form (-p ), you
cannot have a space between the option
and the password. If you omit the
password value following the
--password or
-p option on the command line,
mysqlcheck prompts for one.
Specifying a password on the command line should be
considered insecure. See
Section 5.5.6.2, “End-User Guidelines for Password Security”. You can use an
option file to avoid giving the password on the command
line.
--pipe ,
-W
On Windows, connect to the server via a named pipe. This
option applies only if the server supports named-pipe
connections.
--port=port_num ,
-P port_num
The TCP/IP port number to use for the connection.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the allowable values, see
Section 4.2.2, “Connecting to the MySQL Server”.
--quick ,
-q
If you are using this option to check tables, it prevents
the check from scanning the rows to check for incorrect
links. This is the fastest check method.
If you are using this option to repair tables, it tries to
repair only the index tree. This is the fastest repair
method.
--repair ,
-r
Perform a repair that can fix almost anything except unique
keys that are not unique.
--silent ,
-s
Silent mode. Print only error messages.
--socket=path ,
-S path
For connections to localhost , the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
--ssl*
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”.
--tables
Override the --databases
or -B option. All name arguments following
the option are regarded as table names.
--use-frm
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 user name to use when connecting to the server.
--verbose ,
-v
Verbose mode. Print information about the various stages of
program operation.
--version ,
-V
Display version information and exit.
4.5.4. mysqldump — A Database Backup Program
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.9, “mysqlhotcopy — A Database Backup Program”.
There are three general ways to invoke
mysqldump:
shell> mysqldump [options ] db_name [tbl_name ...]
shell> mysqldump [options ] --databases db_name ...
shell> 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 see a list of the options your version of
mysqldump supports, execute
mysqldump --help.
Some mysqldump options are shorthand for
groups of other options:
Use of --opt is the same
as specifying
--add-drop-table ,
--add-locks ,
--create-options ,
--disable-keys ,
--extended-insert ,
--lock-tables ,
--quick , and
--set-charset . All of the
options that --opt stands
for also are on by default because
--opt is on by default.
Use of --compact is the
same as specifying
--skip-add-drop-table ,
--skip-add-locks ,
--skip-comments ,
--skip-disable-keys ,
and
--skip-set-charset
options.
To reverse the effect of a group option, uses its
--skip-xxx form
(--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:
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, so 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,
which can be specified on the command line or in the
[mysqldump] and [client]
option file groups. mysqldump also supports
the options for processing option files described at
Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
Table 4.5. mysqldump Options
--help ,
-?
Display a help message and exit.
--add-drop-database
Add a DROP DATABASE statement
before each CREATE DATABASE
statement. This option is typically used in conjunction with
the --all-databases or
--databases option because
no CREATE DATABASE statements
are written unless one of those options is specified.
--add-drop-table
Add a DROP TABLE statement
before each CREATE TABLE
statement.
--add-locks
Surround each table dump with LOCK
TABLES and
UNLOCK
TABLES statements. This results in faster inserts
when the dump file is reloaded. See
Section 7.2.19, “Speed of INSERT Statements”.
--all-databases ,
-A
Dump all tables in all databases. This is the same as using
the --databases option and
naming all the databases on the command line.
--allow-keywords
Allow creation of column names that are keywords. This works
by prefixing each column name with the table name.
--character-sets-dir=path
The directory where character sets are installed. See
Section 9.2, “The Character Set Used for Data and Sorting”.
--comments ,
-i
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 .
--compact
Produce more compact output. This option enables the
--skip-add-drop-table ,
--skip-add-locks ,
--skip-comments ,
--skip-disable-keys ,
and
--skip-set-charset
options.
Note
Prior to MySQL 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.
--compatible=name
Produce output that is more compatible with other database
systems or with older MySQL servers. The value of
name can be
ansi , mysql323 ,
mysql40 , postgresql ,
oracle , mssql ,
db2 , maxdb ,
no_key_options ,
no_table_options , or
no_field_options . To use several values,
separate them by commas. These values have the same meaning
as the corresponding options for setting the server SQL
mode. See Section 5.1.7, “Server 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.
--complete-insert ,
-c
Use complete INSERT
statements that include column names.
--compress ,
-C
Compress all information sent between the client and the
server if both support compression.
--create-options
Include all MySQL-specific table options in the
CREATE TABLE statements.
--databases ,
-B
Dump several databases. Normally,
mysqldump treats the first name argument
on the command line as a database name and following names
as table names. With this option, it treats all name
arguments as database names. CREATE
DATABASE and USE
statements are included in the output before each new
database.
--debug[=debug_options ] ,
-#
[debug_options ]
Write a debugging log. A typical
debug_options string is
'd:t:o,file_name ' .
The default value is
'd:t:o,/tmp/mysqldump.trace' .
--debug-info
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 .
This option has no effect for output data files produced by
using the --tab option.
See the description for that option.
--delayed-insert
Write INSERT DELAYED
statements rather than INSERT
statements.
--delete-master-logs
On a master replication server, delete the binary logs by
sending a PURGE BINARY LOGS
statement to the server after performing the dump operation.
This option automatically enables
--master-data .
--disable-keys ,
-K
For each table, surround the
INSERT statements with
/*!40000 ALTER TABLE
tbl_name DISABLE KEYS
*/; and /*!40000 ALTER TABLE
tbl_name ENABLE KEYS
*/; statements. This makes loading the dump file
faster because the indexes are created after all rows are
inserted. This option is effective only for nonunique
indexes of MyISAM tables.
--dump-date
If the --comments option
is given, mysqldump produces a comment at
the end of the dump of the following form:
-- Dump completed on DATE
However, the date causes dump files taken at different times
to appear to be different, even if the data are otherwise
identical. --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.
--extended-insert ,
-e
Use multiple-row INSERT
syntax that include several VALUES lists.
This results in a smaller dump file and speeds up inserts
when the file is reloaded.
--fields-terminated-by=... ,
--fields-enclosed-by=... ,
--fields-optionally-enclosed-by=... ,
--fields-escaped-by=...
These options are used with the
--tab option and have the
same meaning as the corresponding FIELDS
clauses for LOAD
DATA INFILE . See Section 12.2.6, “LOAD DATA INFILE
Syntax”.
--first-slave
Deprecated. Use
--lock-all-tables instead.
--flush-logs ,
-F
Flush the MySQL server log files before starting the dump.
This option requires the
RELOAD privilege. If you use
this option in combination with the
--all-databases 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 .
--flush-privileges
Send a FLUSH
PRIVILEGES statement to the server 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.
--force ,
-f
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
definition 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 an 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 .
--hex-blob
Dump binary columns using hexadecimal notation (for example,
'abc' becomes
0x616263 ). The affected data types are
BINARY ,
VARBINARY , and the
BLOB types. 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. This option also can
be used to ignore views.
--insert-ignore
Write INSERT
IGNORE statements rather than
INSERT statements.
--lines-terminated-by=...
This option is used with the
--tab option and has the
same meaning as the corresponding LINES
clause for LOAD
DATA INFILE . See Section 12.2.6, “LOAD DATA INFILE
Syntax”.
--lock-all-tables ,
-x
Lock all tables across all databases. This is achieved by
acquiring a global read lock for the duration of the whole
dump. This option automatically turns off
--single-transaction and
--lock-tables .
--lock-tables ,
-l
For each dumped database, lock all tables to be dumped
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 than
--lock-tables because it
does not need to lock the tables at all.
Because --lock-tables
locks tables for each database separately, 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.
--log-error=file_name
Log warnings and errors by appending them to the named file.
The default is to do no logging. This option was added in
MySQL 5.0.42.
--master-data[=value ]
Use this option to dump a master replication server to
produce a dump file that can be used to set up another
server as a slave of the master. It causes the dump output
to include a CHANGE MASTER TO
statement that indicates the binary log coordinates (file
name and position) of the dumped server. These are the
master server coordinates from which the slave should start
replicating after you load the dump file into the slave.
If the option value is 2, the CHANGE
MASTER TO statement is written as an SQL comment,
and thus is informative only; it has no effect when the dump
file is reloaded. If the option value is 1, the statement is
not written as a comment and takes effect when the dump file
is reloaded. If no option value is specified, the default
value is 1.
This option requires the
RELOAD privilege and the
binary log must be enabled.
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 the
description for
--single-transaction ). In
all cases, any action on logs happens at the exact moment of
the dump.
It is also possible to set up a slave by dumping an existing
slave of the master. To do this, use the following procedure
on the existing slave:
Stop the slave's SQL thread and get its current status:
mysql> STOP SLAVE SQL_THREAD;
mysql> SHOW SLAVE STATUS;
From the output of the SHOW SLAVE
STATUS statement, the binary log coordinates
of the master server from which the new slave should
start replicating are the values of the
Relay_Master_Log_File and
Exec_Master_Log_Pos fields. Denote
those values as file_name and
file_pos .
Dump the slave server:
shell> mysqldump --master-data=2 --all-databases > dumpfile
Restart the slave:
mysql> START SLAVE;
On the new slave, load the dump file:
shell> mysql < dumpfile
On the new slave, set the replication coordinates to
those of the master server obtained earlier:
mysql> CHANGE MASTER TO
-> MASTER_LOG_FILE = 'file_name ', MASTER_LOG_POS = file_pos ;
The CHANGE MASTER TO
statement might also need other parameters, such as
MASTER_HOST to point the slave to the
correct master server host. Add any such parameters as
necessary.
--no-autocommit
Enclose the INSERT statements
for each dumped table within SET autocommit =
0 and COMMIT
statements.
--no-create-db ,
-n
This option suppresses the CREATE
DATABASE statements that are otherwise included in
the output if the
--databases or
--all-databases option is
given.
--no-create-info ,
-t
Do not write CREATE TABLE
statements that re-create each dumped table.
--no-data ,
-d
Do not write any table row information (that is, do not dump
table contents). This is useful if you want to dump only the
CREATE TABLE statement for
the table (for example, to create an empty copy of the table
by loading the dump file).
--no-set-names ,
-N
This has the same effect as
--skip-set-charset .
--opt
This option is shorthand. It is the same as specifying
--add-drop-table
--add-locks
--create-options
--disable-keys
--extended-insert
--lock-tables
--quick
--set-charset . It should
give you a fast dump operation and produce a dump file that
can be reloaded into a MySQL server quickly.
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 a subset of the options affected by
--opt .
--order-by-primary
Dump each table's rows sorted 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 operation 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,
mysqldump prompts for one.
Specifying a password on the command line should be
considered insecure. See
Section 5.5.6.2, “End-User Guidelines for Password Security”. You can use an
option file to avoid giving the password on the command
line.
--pipe ,
-W
On Windows, connect to the server via a named pipe. This
option applies only if the server supports named-pipe
connections.
--port=port_num ,
-P port_num
The TCP/IP port number to use for the connection.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the allowable values, see
Section 4.2.2, “Connecting to the MySQL Server”.
--quick ,
-q
This option is useful for dumping large tables. It forces
mysqldump to retrieve rows for a table
from the server a row at a time rather than retrieving the
entire row set and buffering it in memory before writing it
out.
--quote-names ,
-Q
Quote identifiers (such as database, table, and column
names) within “` ”
characters. If the
ANSI_QUOTES SQL mode is
enabled, identifiers 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 .
--result-file=file_name ,
-r file_name
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
previous contents overwritten, even if an error occurs while
generating the dump.
--routines ,
-R
Included stored routines (procedures and functions) for the
dumped databases in the output. 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.
--set-charset
Add SET NAMES
default_character_set
to the output. This option is enabled by default. To
suppress the SET NAMES statement, use
--skip-set-charset .
--single-transaction
This option sends a
START
TRANSACTION SQL statement to the server before
dumping data. 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.
While a
--single-transaction dump
is in process, to ensure a valid dump file (correct table
contents and binary log coordinates), no other connection
should use the following statements:
ALTER TABLE ,
CREATE 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 that is performed by
mysqldump to retrieve the table contents
to obtain incorrect contents or fail.
The --single-transaction
option and the
--lock-tables option are
mutually exclusive because LOCK
TABLES causes any pending transactions to be
committed implicitly.
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 the
--single-transaction
option with --quick .
--skip-comments
See the description for the
--comments option.
--skip-opt
See the description for the
--opt option.
--socket=path ,
-S path
For connections to localhost , the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
--ssl*
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”.
--tab=path ,
-T path
Produce tab-separated text-format data files. For each
dumped table, mysqldump creates a
tbl_name .sql
file that contains the CREATE
TABLE statement that creates the table, and the
server writes a
tbl_name .txt
file that contains its data. The option value is the
directory in which to write the files.
Note
This option should be used only when
mysqldump is run on the same machine as
the mysqld server. You must have the
FILE privilege, and the
server must have permission to write files in the
directory that you specify.
By default, the .txt data files are
formatted using tab characters between column values and a
newline at the end of each line. The format can be specified
explicitly using the
--fields-xxx and
--lines-terminated-by
options.
Column values are dumped using the binary
character set and the
--default-character-set
option is ignored. In effect, there is no character set
conversion. If a table contains columns in several character
sets, the output data file will as well and you may not be
able to reload the file correctly.
--tables
Override the --databases
or -B option. mysqldump
regards all name arguments following the option as table
names.
--triggers
Include triggers for each dumped table in the output. 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.
--tz-utc
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 if the servers are in different time zones.
--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 user name to use when connecting to the server.
--verbose ,
-v
Verbose mode. Print more information about what the program
does.
--version ,
-V
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"
--xml , -X
Write dump output as well-formed XML.
NULL ,
'NULL' , and Empty Values: For
a 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.
Beginning with MySQL 5.0.26, the output from the
mysql client when run using the
--xml option also follows the
preceding 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="273293" Max_data_length="18858823439613951"
Index_length="43008" Data_free="0" Auto_increment="4080"
Create_time="2007-03-31 01:47:01" Update_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
--var_name =value
syntax:
max_allowed_packet
The maximum size of the buffer for client/server
communication. The maximum is 1GB.
net_buffer_length
The initial size of the buffer for client/server
communication. When creating multiple-row
INSERT statements (as with
the --extended-insert or
--opt option),
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
--var_name =value .
The --set-variable format is deprecated.
A common use of mysqldump is for making a
backup of an entire database:
shell> mysqldump db_name > backup-file.sql
You can load 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 acquires 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 long updating statements are running
when the FLUSH statement is
issued, the MySQL server may get stalled until those statements
finish. After that, the dump becomes lock free and does not
disturb reads and writes on the tables. 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 use prior to point-in-time
recovery if tables are stored using the
InnoDB storage engine.
For more information on making backups, see
Section 6.2, “Database Backup Methods”, and
Section 6.3, “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 D.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.
4.5.5. mysqlimport — A Data Import Program
The mysqlimport client provides a
command-line interface to the
LOAD DATA
INFILE SQL statement. Most options to
mysqlimport correspond directly to clauses of
LOAD DATA
INFILE syntax. See Section 12.2.6, “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
file name 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 .
mysqldump supports the following options,
which can be specified on the command line or in the
[mysqldump] and [client]
option file groups. mysqldump also supports
the options for processing option files described at
Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
Table 4.6. mysqlimport Options
--help ,
-?
Display a help message and exit.
--character-sets-dir=path
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 ,
-C
Compress all information sent between the client and the
server if both support compression.
--debug[=debug_options ] ,
-#
[debug_options ]
Write a debugging log. A typical
debug_options string is
'd:t:o,file_name ' .
The default is 'd:t:o' .
--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”.
--delete ,
-D
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.6, “LOAD DATA INFILE
Syntax”.
--force ,
-f
Ignore errors. For example, if a table for a text file does
not exist, continue processing any remaining files. Without
--force ,
mysqlimport exits if a table does not
exist.
--host=host_name ,
-h host_name
Import data to the MySQL server on the given host. The
default host is localhost .
--ignore ,
-i
See the description for the
--replace option.
--ignore-lines=N
Ignore the first N lines of the
data file.
--lines-terminated-by=...
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.6, “LOAD DATA INFILE
Syntax”.
--local ,
-L
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-tables ,
-l
Lock all tables for writing before
processing any text files. This ensures that all tables are
synchronized on the server.
--low-priority
Use LOW_PRIORITY when loading the table.
This affects only storage engines that use only table-level
locking (such as MyISAM ,
MEMORY , and 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,
mysqlimport prompts for one.
Specifying a password on the command line should be
considered insecure. See
Section 5.5.6.2, “End-User Guidelines for Password Security”. You can use an
option file to avoid giving the password on the command
line.
--pipe ,
-W
On Windows, connect to the server via a named pipe. This
option applies only if the server supports named-pipe
connections.
--port=port_num ,
-P port_num
The TCP/IP port number to use for the connection.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the allowable values, see
Section 4.2.2, “Connecting to the MySQL Server”.
--replace ,
-r
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 ,
-s
Silent mode. Produce output only when errors occur.
--socket=path ,
-S path
For connections to localhost , the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
--ssl*
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 user name to use when connecting to the server.
--verbose ,
-v
Verbose mode. Print more information about what the program
does.
--version ,
-V
Display version information and exit.
Here is a sample session that demonstrates use of
mysqlimport:
shell> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
shell> ed
a
100 Max Sydow
101 Count Dracula
.
w imptest.txt
32
q
shell> od -c imptest.txt
0000000 1 0 0 \t M a x S y d o w \n 1 0
0000020 1 \t C o u n t D r a c u l a \n
0000040
shell> mysqlimport --local test imptest.txt
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
shell> mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id | n |
+------+---------------+
| 100 | Max Sydow |
| 101 | Count Dracula |
+------+---------------+
4.5.6. mysqlshow — Display Database, Table, and Column Information
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.5, “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,
which can be specified on the command line or in the
[mysqlshow] and [client]
option file groups. mysqlshow also supports
the options for processing option files described at
Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
Table 4.7. mysqlshow Options
--help ,
-?
Display a help message and exit.
--character-sets-dir=path
The directory where character sets are installed. See
Section 9.2, “The Character Set Used for Data and Sorting”.
--compress ,
-C
Compress all information sent between the client and the
server if both support compression.
--count
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. A typical
debug_options string is
'd:t:o,file_name ' .
The default is 'd:t:o' .
--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.
--keys ,
-k
Show table indexes.
--password[=password ] ,
-p[password ]
The password to use when connecting to the server. If you
use the short option form (-p ), you
cannot have a space between the option
and the password. If you omit the
password value following the
--password or
-p option on the command line,
mysqlshow prompts for one.
Specifying a password on the command line should be
considered insecure. See
Section 5.5.6.2, “End-User Guidelines for Password Security”. You can use an
option file to avoid giving the password on the command
line. You can use an option file to avoid giving the
password on the command line.
--pipe ,
-W
On Windows, connect to the server via a named pipe. This
option applies only if the server supports named-pipe
connections.
--port=port_num ,
-P port_num
The TCP/IP port number to use for the connection.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the allowable values, see
Section 4.2.2, “Connecting to the MySQL Server”.
--show-table-type ,
-t
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.
--socket=path ,
-S path
For connections to localhost , the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
--ssl*
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”.
--status ,
-i
Display extra information about each table.
--user=user_name ,
-u user_name
The MySQL user name to use when connecting to the server.
--verbose ,
-v
Verbose mode. Print more information about what the program
does. This option can be used multiple times to increase the
amount of information.
--version ,
-V
Display version information and exit.
4.6. MySQL Administrative and Utility Programs
This section describes administrative programs and programs that
perform miscellaneous utility operations.
4.6.1. innochecksum — Offline InnoDB File Checksum Utility
innochecksum prints checksums for
InnoDB files. This tool reads an
InnoDB tablespace file, calculates the
checksum for each page, compares the calculated checksum to the
stored checksum, and reports mismatches, which indicate damaged
pages. It was originally developed to speed up verifying the
integrity of tablespace files after power outages but can also
be used after file copies. Because checksum mismatches will
cause InnoDB to deliberately shut down a
running server, it can be preferable to use this tool rather
than waiting for a server in production usage to encounter the
damaged pages.
innochecksum cannot be used on tablespace
files that the server already has open. For such files, you
should use CHECK TABLE to check
tables within the tablespace.
If checksum mismatches are found, you would normally restore the
tablespace from backup or start the server and attempt to use
mysqldump to make a backup of the tables
within the tablespace.
Invoke innochecksum like this:
shell> innochecksum [options ] file_name
innochecksum supports the following options.
For options that refer to page numbers, the numbers are
zero-based.
-c
Print a count of the number of pages in the file.
-d
Debug mode; prints checksums for each page.
-e num
End at this page number.
-p num
Check only this page number.
-s num
Start at this page number.
-v
Verbose mode; print a progress indicator every five seconds.
4.6.2. myisam_ftdump — Display Full-Text Index information
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. Before using myisam_ftdump, be
sure to issue a FLUSH TABLES statement first
if the server is running.
myisam_ftdump scans and dumps the entire
index, which is not particularly fast. On the other hand, the
distribution of words changes infrequently, so it need not be
run often.
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 path name 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 path name to the test database
directory is /usr/local/mysql/data/test ,
you can also specify the table name argument using that path
name. This is useful if you do not invoke
myisam_ftdump in the database directory:
shell> myisam_ftdump /usr/local/mysql/data/test/mytexttable 1
You can use myisam_ftdump to generate a list
of index entries in order of frequency of occurrence like this:
shell> myisam_ftdump -c mytexttable 1 | sort -r
myisam_ftdump supports the following options:
--help ,
-h -?
Display a help message and exit.
--count ,
-c
Calculate per-word statistics (counts and global weights).
--dump ,
-d
Dump the index, including data offsets and word weights.
--length ,
-l
Report the length distribution.
--stats ,
-s
Report global index statistics. This is the default
operation if no other operation is specified.
--verbose ,
-v
Verbose mode. Print more output about what the program does.
4.6.3. myisamchk — MyISAM Table-Maintenance Utility
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).
Caution
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 file system 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 does not 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.6, “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.
Important
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”.
myisamchk supports the following options,
which can be specified on the command line or in the
[myisamchk] option file group.
myisamchk also supports the options for
processing option files described at
Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
Table 4.8. myisamchk Options 4.6.3.1. myisamchk General Options
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.
--help ,
-?
Display a help message and exit. Options are grouped by type
of operation.
--HELP ,
-H
Display a help message and exit. Options are presented in a
single list.
--debug=debug_options ,
-# debug_options
Write a debugging log. A typical
debug_options string is
'd:t:o,file_name ' .
The default is
'd:t:o,/tmp/myisamchk.trace' .
--silent ,
-s
Silent mode. Write output only when errors occur. You can
use -s twice (-ss ) to make
myisamchk very silent.
--verbose ,
-v
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.
--version ,
-V
Display version information and exit.
--wait ,
-w
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
--var_name =value
syntax:
It is also possible to set variables by using
--set-variable=var_name =value
or -O
var_name =value
syntax. However, this syntax is deprecated as of MySQL 4.0.
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, “Server 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 .
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.
4.6.3.2. myisamchk Check Options
myisamchk supports the following options for
table checking operations:
--check ,
-c
Check the table for errors. This is the default operation if
you specify no option that selects an operation type
explicitly.
--check-only-changed ,
-C
Check only tables that have changed since the last check.
--extend-check ,
-e
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.
For a description of the output format, see
Section 4.6.3.5, “myisamchk Table Information”.
--fast ,
-F
Check only tables that haven't been closed properly.
--force ,
-f
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.
--information ,
-i
Print informational statistics about the table that is
checked.
--medium-check ,
-m
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.
--read-only ,
-T
Do not 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 does not use locking, such as
mysqld when run with external locking
disabled.
--update-state ,
-U
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.
4.6.3.3. myisamchk Repair Options
myisamchk supports the following options for
table repair operations (operations performed when an option
such as --recover or
--safe-recover is given):
--backup ,
-B
Make a backup of the .MYD file as
file_name -time .BAK
--character-sets-dir=path
The directory where character sets are installed. See
Section 9.2, “The Character Set Used for Data and Sorting”.
--correct-checksum
Correct the checksum information for the table.
--data-file-length=len ,
-D len
The maximum length of the data file (when re-creating data
file when it is “full”).
--extend-check ,
-e
Do a repair that tries to recover every possible row from
the data file. Normally, this also finds a lot of garbage
rows. Do not use this option unless you are desperate.
For a description of the output format, see
Section 4.6.3.5, “myisamchk Table Information”.
--force ,
-f
Overwrite old intermediate files (files with names like
tbl_name .TMD )
instead of aborting.
--keys-used=val ,
-k val
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.
--no-symlinks ,
-l
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.
--max-record-length=len
Skip rows larger than the given length if
myisamchk cannot allocate memory to hold
them.
--parallel-recover ,
-p
Use the same technique as -r and
-n , but create all the keys in parallel,
using different threads. This is beta-quality
code. Use at your own risk!
--quick ,
-q
Achieve a faster repair by modifying only the index file, not the data file. You
can specify this option twice to force
myisamchk to modify the original data
file in case of duplicate keys.
--recover ,
-r
Do a repair that can fix almost any problem except unique
keys that are not 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 cannot
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 .
--safe-recover ,
-o
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 using
--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 .
--set-character-set=name
Change the character set used by the table indexes. This
option was replaced by
--set-collation in MySQL
5.0.3.
--set-collation=name
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.
--sort-recover ,
-n
Force myisamchk to use sorting to resolve
the keys even if the temporary files would be very large.
--tmpdir=path ,
-t path
The 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 ,
-u
Unpack a table that was packed with
myisampack.
4.6.3.4. Other myisamchk Options
myisamchk supports the following options for
actions other than table checks and repairs:
--analyze ,
-a
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
tbl_name statement.
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.
--description ,
-d
Print some descriptive information about the table.
Specifying the --verbose
option once or twice produces additional information. See
Section 4.6.3.5, “myisamchk Table Information”.
--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-index ,
-S
Sort the index tree blocks in high-low order. This optimizes
seeks and makes table scans that use indexes faster.
--sort-records=N ,
-R N
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.)
4.6.3.5. myisamchk Table Information
To obtain a description of a MyISAM table or
statistics about it, use the commands shown here. The output
from these commands is explained later in this section.
myisamchk -d
tbl_name
Runs myisamchk in “describe
mode” to produce a description of your table. If you
start the MySQL server with external locking disabled,
myisamchk may report an error for a table
that is updated while it runs. However, because
myisamchk does not change the table in
describe mode, there is no risk of destroying data.
myisamchk -dv
tbl_name
Adding -v runs myisamchk
in verbose mode so that it produces more information about
the table. Adding -v a second time produces
even more information.
myisamchk -eis
tbl_name
Shows only the most important information from a table. This
operation is slow because it must read the entire table.
myisamchk -eiv
tbl_name
This is like -eis , but tells you what is
being done.
The tbl_name argument can be either
the name of a MyISAM table or the name of its
index file, as described in Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
Multiple tbl_name arguments can be
given.
Suppose that a table named person has the
following structure. (The MAX_ROWS table
option is included so that in the example output from
myisamchk shown later, some values are
smaller and fit the output format more easily.)
CREATE TABLE person
(
id INT NOT NULL AUTO_INCREMENT,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
birth DATE,
death DATE,
PRIMARY KEY (id),
INDEX (last_name, first_name),
INDEX (birth)
) MAX_ROWS = 1000000;
Suppose also that the table has these data and index file sizes:
-rw-rw---- 1 mysql mysql 9347072 Aug 19 11:47 person.MYD
-rw-rw---- 1 mysql mysql 6066176 Aug 19 11:47 person.MYI
Example of myisamchk -dvv output:
MyISAM file: person
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2009-08-19 16:47:41
Recover time: 2009-08-19 16:47:56
Status: checked,analyzed,optimized keys
Auto increment key: 1 Last value: 306688
Data records: 306688 Deleted blocks: 0
Datafile parts: 306688 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3
Datafile length: 9347072 Keyfile length: 6066176
Max datafile length: 4294967294 Max keyfile length: 17179868159
Recordlength: 54
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 4 unique long 1 99328 1024
2 6 20 multip. varchar prefix 512 3563520 1024
27 20 varchar 512
3 48 3 multip. uint24 NULL 306688 6065152 1024
Field Start Length Nullpos Nullbit Type
1 1 1
2 2 4 no zeros
3 6 21 varchar
4 27 21 varchar
5 48 3 1 1 no zeros
6 51 3 1 2 no zeros
Explanations for the types of information
myisamchk produces are given here.
“Keyfile” refers to the index file.
“Record” and “row” are synonymous, as
are “field” and “column.”
The initial part of the table description contains these values:
MyISAM file
Name of the MyISAM (index) file.
Record format
The format used to store table rows. The preceding examples
use Fixed length . Other possible values
are Compressed and
Packed .
Chararacter set
The table default character set.
File-version
Version of MyISAM format. Currently
always 1.
Creation time
When the data file was created.
Recover time
When the index/data file was last reconstructed.
Status
Table status flags. Possible values are
crashed , open ,
changed , analyzed ,
optimized keys , and sorted index
pages .
Auto increment key , Last
value
The key number associated the table's
AUTO_INCREMENT column, and the most
recently generated value for this column. These fields do
not appear if there is no such column.
Data records
The number of rows in the table.
Deleted blocks
How many deleted blocks still have reserved space. You can
optimize your table to minimize this space. See
Section 6.5.4, “MyISAM Table Optimization”.
Datafile parts
For dynamic-row format, this indicates how many data blocks
there are. For an optimized table without fragmented rows,
this is the same as Data records .
Deleted data
How many bytes of unreclaimed deleted data there are. You
can optimize your table to minimize this space. See
Section 6.5.4, “MyISAM Table Optimization”.
Datafile pointer
The size of the data file pointer, in bytes. It is usually
2, 3, 4, or 5 bytes. Most tables manage with 2 bytes, but
this cannot be controlled from MySQL yet. For fixed tables,
this is a row address. For dynamic tables, this is a byte
address.
Keyfile pointer
The size of the index file pointer, in bytes. It is usually
1, 2, or 3 bytes. Most tables manage with 2 bytes, but this
is calculated automatically by MySQL. It is always a block
address.
Max datafile length
How long the table data file can become, in bytes.
Max keyfile length
How long the table index file can become, in bytes.
Recordlength
How much space each row takes, in bytes.
The table description part of the output
includes a list of all keys in the table. For each key,
myisamchk displays some low-level
information:
Key
This key's number. This value is shown only for the first
column of the key. If this value is missing, the line
corresponds to the second or later column of a
multiple-column key. For the table shown in the example,
there are two table description lines for
the second index. This indicates that it is a multiple-part
index with two parts.
Start
Where in the row this portion of the index starts.
Len
How long this portion of the index is. For packed numbers,
this should always be the full length of the column. For
strings, it may be shorter than the full length of the
indexed column, because you can index a prefix of a string
column. The total length of a multiple-part key is the sum
of the Len values for all key parts.
Index
Whether a key value can exist multiple times in the index.
Possible values are unique or
multip. (multiple).
Type
What data type this portion of the index has. This is a
MyISAM data type with the possible values
packed , stripped , or
empty .
Root
Address of the root index block.
Blocksize
The size of each index block. By default this is 1024, but
the value may be changed at compile time when MySQL is built
from source.
Rec/key
This is a statistical value used by the optimizer. It tells
how many rows there are per value for this index. A unique
index always has a value of 1. This may be updated after a
table is loaded (or greatly changed) with myisamchk
-a. If this is not updated at all, a default value
of 30 is given.
The last part of the output provides information about each
column:
Field
The column number.
Start
The byte position of the column within table rows.
Length
The length of the column in bytes.
Nullpos , Nullbit
For columns that can be NULL ,
MyISAM stores NULL
values as a flag in a byte. Depending on how many nullable
columns there are, there can be one or more bytes used for
this purpose. The Nullpos and
Nullbit values, if nonempty, indicate
which byte and bit contains that flag indicating whether the
column is NULL .
The position and number of bytes used to store
NULL flags is shown in the line for field
1. This is why there are six Field lines
for the person table even though it has
only five columns.
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.
The Huff tree and Bits
fields are displayed if the table has been compressed with
myisampack. See Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”,
for an example of this information.
Example of myisamchk -eiv output:
Checking MyISAM file: person
Data records: 306688 Deleted blocks: 0
- check file-size
- check record delete-chain
No recordlinks
- check key delete-chain
block_size 1024:
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 98% Packed: 0% Max levels: 3
- check data record references index: 2
Key: 2: Keyblocks used: 99% Packed: 97% Max levels: 3
- check data record references index: 3
Key: 3: Keyblocks used: 98% Packed: -14% Max levels: 3
Total: Keyblocks used: 98% Packed: 89%
- check records and index references
*** LOTS OF ROW NUMBERS DELETED ***
Records: 306688 M.recordlength: 25 Packed: 83%
Recordspace used: 97% Empty space: 2% Blocks/Record: 1.00
Record blocks: 306688 Delete blocks: 0
Record data: 7934464 Deleted data: 0
Lost space: 256512 Linkdata: 1156096
User time 43.08, System time 1.68
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 0, Physical pagefaults 0, Swaps 0
Blocks in 0 out 7, Messages in 0 out 0, Signals 0
Voluntary context switches 0, Involuntary context switches 0
Maximum memory usage: 1046926 bytes (1023k)
myisamchk -eiv output includes the following
information:
Data records
The number of rows in the table.
Deleted blocks
How many deleted blocks still have reserved space. You can
optimize your table to minimize this space. See
Section 6.5.4, “MyISAM Table Optimization”.
Key
The key number.
Keyblocks used
What percentage of the keyblocks are used. When a table has
just been reorganized with myisamchk, the
values are very high (very near theoretical maximum).
Packed
MySQL tries to pack key values that have a common suffix.
This can only be used for indexes on
CHAR and
VARCHAR columns. For long
indexed strings that have similar leftmost parts, this can
significantly reduce the space used. In the preceding
example, the second key is 40 bytes long and a 97% reduction
in space is achieved.
Max levels
How deep the B-tree for this key is. Large tables with long
key values get high values.
Records
How many rows are in the table.
M.recordlength
The average row length. This is the exact row length for
tables with fixed-length rows, because all rows have the
same length.
Packed
MySQL strips spaces from the end of strings. The
Packed value indicates the percentage of
savings achieved by doing this.
Recordspace used
What percentage of the data file is used.
Empty space
What percentage of the data file is unused.
Blocks/Record
Average number of blocks per row (that is, how many links a
fragmented row is composed of). This is always 1.0 for
fixed-format tables. This value should stay as close to 1.0
as possible. If it gets too large, you can reorganize the
table. See Section 6.5.4, “MyISAM Table Optimization”.
Recordblocks
How many blocks (links) are used. For fixed-format tables,
this is the same as the number of rows.
Deleteblocks
How many blocks (links) are deleted.
Recorddata
How many bytes in the data file are used.
Deleted data
How many bytes in the data file are deleted (unused).
Lost space
If a row is updated to a shorter length, some space is lost.
This is the sum of all such losses, in bytes.
Linkdata
When the dynamic table format is used, row fragments are
linked with pointers (4 to 7 bytes each).
Linkdata is the sum of the amount of
storage used by all such pointers.
4.6.3.6. myisamchk Memory Usage
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 file system, out of memory errors can easily
occur. If this happens, run myisamchk with
the
--tmpdir=path
option to specify a directory located on a file system that has
more space.
When performing repair operations, myisamchk
also needs a lot of disk space:
Twice 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 must
be available on the same file system as the original data
file, as 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 must
be available on the same file system as the original data
file.
When using --recover or
--sort-recover (but not
when using
--safe-recover ), you need
space on disk for sorting. This space is allocated in the
temporary directory (specified by TMPDIR
or
--tmpdir=path ).
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 (see
Section 4.6.3.5, “myisamchk Table Information”). The
row_pointer_length and
number_of_rows values are the
Datafile pointer and Data
records values in the table description. To
determine the largest_key value,
check the Key lines in the table
description. The Len column indicates the
number of bytes for each key part. For a multiple-column
index, the key size is the sum of the Len
values for all key parts.
If you have a problem with disk space during repair, you can try
--safe-recover instead of
--recover .
4.6.4. myisamlog — Display MyISAM Log File Contents
myisamlog processes the contents of a
MyISAM log file.
Invoke myisamlog like this:
shell> myisamlog [options ] [log_file [tbl_name ] ...]
shell> 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 supports the following options:
-? , -I
Display a help message and exit.
-c N
Execute only N commands.
-f N
Specify the maximum number of open files.
-i
Display extra information before exiting.
-o offset
Specify the starting offset.
-p N
Remove N components from path.
-r
Perform a recovery operation.
-R record_pos_file
record_pos
Specify record position file and record position.
-u
Perform an update operation.
-v
Verbose mode. Print more output about what the program does.
This option can be given multiple times to produce more and
more output.
-w write_file
Specify the write file.
-V
Display version information.
4.6.5. myisampack — Generate Compressed, Read-Only MyISAM Tables
The myisampack utility compresses
MyISAM tables. myisampack
works by compressing each column in the table separately.
Usually, myisampack packs the data file
40%–70%.
When the table is used later, the 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 file name argument should be the name of an index
(.MYI ) file. If you are not in the database
directory, you should specify the path name 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. It
also reads option files and supports the options for processing
them described at Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
--help ,
-?
Display a help message and exit.
--backup ,
-b
Make a backup of each table's data file using the name
tbl_name .OLD .
--character-sets-dir=path
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. A typical
debug_options string is
'd:t:o,file_name ' .
The default is 'd:t:o' .
--force ,
-f
Produce a packed table even if it becomes larger than the
original or if the intermediate file from an earlier
invocation of myisampack exists.
(myisampack creates an intermediate file
named
tbl_name .TMD
in the database directory while it compresses the table. If
you kill myisampack, the
.TMD file might not be deleted.)
Normally, myisampack exits with an error
if it finds that
tbl_name .TMD
exists. With --force ,
myisampack packs the table anyway.
--join=big_tbl_name ,
-j big_tbl_name
Join all tables named on the command line into a single
packed 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).
big_tbl_name must not exist prior
to the join operation. All source tables named on the
command line to be merged into
big_tbl_name must exist. The
source tables are read for the join operation but not
modified. The join operation does not create a
.frm file for
big_tbl_name , so after the join
operation finishes, copy the .frm file
from one of the source tables and name it
big_tbl_name .frm .
--silent ,
-s
Silent mode. Write output only when errors occur.
--test ,
-t
Do not actually pack the table, just test packing it.
--tmpdir=path ,
-T path
Use the named directory as the location where
myisampack creates temporary files.
--verbose ,
-v
Verbose mode. Write information about the progress of the
packing operation and its result.
--version ,
-V
Display version information and exit.
--wait ,
-w
Wait and retry if the table is in use. If the
mysqld server was invoked with 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, the Field
lines displayed by myisamchk -dvv include
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.
4.6.6. mysqlaccess — Client for Checking Access Privileges
mysqlaccess is a diagnostic tool that Yves
Carlier has provided for the MySQL distribution. It checks the
access privileges for a host name, user name, 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 supports the following options.
Table 4.9. mysqlaccess Options
--help ,
-?
Display a help message and exit.
--brief ,
-b
Generate reports in single-line tabular format.
--commit
Copy the new access privileges from the temporary tables to
the original grant tables. The grant tables must be flushed
for the new privileges to take effect. (For example, execute
a mysqladmin reload command.)
--copy
Reload the temporary grant tables from original ones.
--db=db_name ,
-d db_name
Specify the database name.
--debug=N
Specify the debug level. N can be
an integer from 0 to 3.
--host=host_name ,
-h host_name
The host name to use in the access privileges.
--howto
Display some examples that show how to use
mysqlaccess.
--old_server
Assume that the server is an old MySQL server (before MySQL
3.21) that does not yet know how to handle full
WHERE clauses.
--password[=password ] ,
-p[password ]
The password to use when connecting to the server. If you
omit the password value following
the --password or
-p option on the command line,
mysqlaccess prompts for one.
Specifying a password on the command line should be
considered insecure. See
Section 5.5.6.2, “End-User Guidelines for Password Security”.
--plan
Display suggestions and ideas for future releases.
--preview
Show the privilege differences after making changes to the
temporary grant tables.
--relnotes
Display the release notes.
--rhost=host_name ,
-H host_name
Connect to the MySQL server on the given host.
--rollback
Undo the most recent changes to the temporary grant tables.
--spassword[=password ] ,
-P[password ]
The password to use when connecting to the server as the
superuser. If you omit the
password value following the
--spassword or
-p option on the command line,
mysqlaccess prompts for one.
Specifying a password on the command line should be
considered insecure. See
Section 5.5.6.2, “End-User Guidelines for Password Security”.
--superuser=user_name ,
-U user_name
Specify the user name for connecting as the superuser.
--table ,
-t
Generate reports in table format.
--user=user_name ,
-u user_name
The user name to use in the access privileges.
--version ,
-v
Display version information and exit.
If your MySQL distribution is installed in some nonstandard
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.
4.6.7. mysqlbinlog — Utility for Processing Binary Log Files
The server's binary log consists of files containing
“events” that describe modifications to database
contents. The server writes these files in binary format. To
display their contents in text format, use the
mysqlbinlog utility. You can also use
mysqlbinlog to display the contents of relay
log files written by a slave server in a replication setup
because relay logs have the same format as binary logs. The
binary log and relay log are discussed further in
Section 5.2.3, “The Binary Log”, and Section 16.4.2, “Replication Relay and Status 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 events contained in
binlog.000003 . Event information includes
the SQL statement, the ID of the server on which it was
executed, the timestamp when the statement was executed, how
much time it took, and so forth.
The output from mysqlbinlog can be
re-executed (for example, by using it as input to
mysql) to redo 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
and Section 6.4, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
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. To 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.
mysqlbinlog supports the following options,
which can be specified on the command line or in the
[mysqlbinlog] and [client]
option file groups. mysqlbinlog also supports
the options for processing option files described at
Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
Table 4.10. mysqlbinlog Options
--help ,
-?
Display a help message and exit.
--character-sets-dir=path
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
This option causes mysqlbinlog to output
entries from the binary log (local log only) that occur
while db_name is been selected as
the default database by USE .
The --database option
for mysqlbinlog is similar to the
--binlog-do-db option for
mysqld, but can be used to specify only
one database. If
--database is given
multiple times, only the last instance is used.
The --database option
works as follows:
While db_name is the default
database, statements are output whether they modify
tables in db_name or a
different database.
Unless db_name is selected as
the default database, statements are not output, even if
they modify tables in
db_name .
There is an exception for CREATE
DATABASE , ALTER
DATABASE , and DROP
DATABASE . The database being
created, altered, or dropped is
considered to be the default database when determining
whether to output the statement.
Suppose that the binary log contains these statements:
INSERT INTO test.t1 (i) VALUES(100);
INSERT INTO db2.t2 (j) VALUES(200);
USE test;
INSERT INTO test.t1 (i) VALUES(101);
INSERT INTO t1 (i) VALUES(102);
INSERT INTO db2.t2 (j) VALUES(201);
USE db2;
INSERT INTO test.t1 (i) VALUES(103);
INSERT INTO db2.t2 (j) VALUES(202);
INSERT INTO t2 (j) VALUES(203);
mysqlbinlog --database=test does not
output the first two INSERT
statements because there is no default database. It outputs
the three INSERT statements
following USE
test , but not the three
INSERT statements following
USE db2 .
mysqlbinlog --database=db2 does not
output the first two INSERT
statements because there is no default database. It does not
output the three INSERT
statements following
USE test , but
does output the three INSERT
statements following
USE db2 .
--debug[=debug_options ] ,
-#
[debug_options ]
Write a debugging log. A typical
debug_options string is
'd:t:o,file_name ' .
The default is
'd:t:o,/tmp/mysqlbinlog.trace' .
--disable-log-bin ,
-D
Disable binary logging. This is useful for avoiding an
endless loop if you use the
--to-last-log option and
are sending the output to the same MySQL server. This option
also is useful when restoring after a crash to avoid
duplication of the statements you have logged.
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.
--force-read ,
-f
With this option, if mysqlbinlog reads a
binary log event that it does not recognize, it prints a
warning, ignores the event, and continues. Without this
option, mysqlbinlog stops if it reads
such an event.
--hexdump ,
-H
Display a hex dump of the log in comments. The hex 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.
--local-load=path ,
-l path
Prepare local temporary files for
LOAD DATA
INFILE in the specified directory.
--offset=N ,
-o N
Skip the first N entries 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,
mysqlbinlog prompts for one.
Specifying a password on the command line should be
considered insecure. See
Section 5.5.6.2, “End-User Guidelines for Password Security”. You can use an
option file to avoid giving the password on the command
line.
--port=port_num ,
-P port_num
The TCP/IP port number to use for connecting to a remote
server.
--position=N
Deprecated. Use
--start-position
instead.
--protocol={TCP|SOCKET|PIPE|MEMORY}
The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the allowable values, see
Section 4.2.2, “Connecting to the MySQL Server”.
--read-from-remote-server ,
-R
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 .
This option requires that the remote server be running. It
works only for binary log files on the remote server, not
relay log files.
--result-file=name ,
-r name
Direct output to the given file.
--set-charset=charset_name
Add a SET NAMES
charset_name 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.
--short-form ,
-s
Display only the statements contained in the log, without
any extra information.
--socket=path ,
-S path
For connections to localhost , the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
--start-datetime=datetime
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.3, “Example Backup and Recovery Strategy”.
--start-position=N ,
-j N
Start reading the binary log at the first event having a
position equal to or greater than
N . This option applies to the
first log file named on the command line.
This option is useful for point-in-time recovery. See
Section 6.3, “Example Backup and Recovery Strategy”.
--stop-datetime=datetime
Stop reading the binary log at the first event having a
timestamp equal to or later than 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.
This option is useful for point-in-time recovery. See
Section 6.3, “Example Backup and Recovery Strategy”.
--stop-position=N
Stop reading the binary log at the first event having a
position equal to or greater than
N . This option applies to the
last log file named on the command line.
This option is useful for point-in-time recovery. See
Section 6.3, “Example Backup and Recovery Strategy”.
--to-last-log ,
-t
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 user name to use when connecting to a remote
server.
--version ,
-V
Display version information and exit.
You can also set the following variable by using
--var_name =value
syntax:
It is also possible to set variables by using
--set-variable=var_name =value
or -O
var_name =value
syntax. This syntax is deprecated.
You can pipe the output of mysqlbinlog into
the mysql client to execute the events
contained in the binary log. This technique is used to recover
from a crash when you have an old backup (see
Section 6.4, “Point-in-Time (Incremental) Recovery Using the Binary Log”). For example:
shell> mysqlbinlog binlog.000001 | mysql -u root -p
Or:
shell> mysqlbinlog binlog.[0-9]* | mysql -u root -p
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:
shell> mysqlbinlog binlog.000001 > tmpfile
shell> ... edit tmpfile ...
shell> mysql -u root -p < tmpfile
When mysqlbinlog is invoked with the
--start-position option, it
displays only those events 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 -u root -p # DANGER!!
shell> mysqlbinlog binlog.000002 | mysql -u root -p # 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
mysql process 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 -u root -p
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 -u root -p -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 with the
LOCAL capability enabled. See
Section 5.3.4, “Security Issues with LOAD
DATA LOCAL ”.
Warning
The temporary files created for
LOAD DATA
LOCAL statements are not
automatically deleted because they are needed until you
actually execute those statements. You should delete the
temporary files yourself after you no longer need the
statement log. The files can be found in the temporary file
directory and have names like
original_file_name-#-# .
The --hexdump option
produces a hex dump of the log contents:
shell> mysqlbinlog --hexdump master-bin.000001
The hex output consists of comment lines beginning with
# , so the output might look like this for the
preceding command:
/*!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 is subject to change.
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 event type code. In the example
shown, '0f' indicates a
FORMAT_DESCRIPTION_EVENT . The following
table lists the possible type codes.
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 next
event in the original master log file.
Flags : 16 flags. Currently, the following
flags are used. The others are reserved for future use.
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.
4.6.8. mysqldumpslow — Summarize Slow Query Log Files
The MySQL slow query log contains information about queries that
take a long time to execute (see
Section 5.2.4, “The Slow Query Log”).
mysqldumpslow parses MySQL slow query log
files and prints a summary of their contents.
Normally, mysqldumpslow groups queries that
are similar except for the particular values of number and
string data values. It “abstracts” these values to
N and 'S' when displaying
summary output. The -a and -n
options can be used to modify value abstracting behavior.
Invoke mysqldumpslow like this:
shell> mysqldumpslow [options ] [log_file ...]
mysqldumpslow supports the following options.
Table 4.11. mysqldumpslow Options Format | Config File | Description | Introduction | Deprecated | Removed |
---|
-a | | Do not abstract all numbers to N and strings to S | | | | -n num | | Abstract numbers with at least the specified digits | | | | --debug | debug | Write debugging information | | | | -g pattern | | Only consider statements that match the pattern | | | | --help | | Display help message and exit | | | | -h name | | Host name of the server in the log file name | | | | -i name | | Name of the server instance | | | | -l | | Do not subtract lock time from total time | | | | -r | | Reverse the sort order | | | | -s value | | How to sort output | | | | -t num | | Display only first num queries | | | | --verbose | verbose | Verbose mode | | | |
--help
Display a help message and exit.
-a
Do not abstract all numbers to N and
strings to 'S' .
--debug ,
-d
Run in debug mode.
-g pattern
Consider only queries that match the
(grep-style) pattern.
-h host_name
Host name of MySQL server for
*-slow.log file name. The value can
contain a wildcare. The default is *
(match all).
-i name
Name of server instance (if using
mysql.server startup script).
-l
Do not subtract lock time from total time.
-n N
Abstract numbers with at least N
digits within names.
-r
Reverse the sort order.
-s sort_type
How to sort the output. The value of
sort_type should be chosen from
the following list:
t , at : Sort by
query time or average query time
l , al : Sort by
lock time or average lock time
s , as : Sort by
rows sent or average rows sent
c : Sort by count
-t N
Display only the first N queries
in the output.
--verbose ,
-v
Verbose mode. Print more information about what the program
does.
Example of usage:
shell> mysqldumpslow
Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log
Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1
Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1 limit N
Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t1 select * from t1
4.6.9. mysqlhotcopy — A Database Backup Program
mysqlhotcopy is a Perl script that was
originally written and contributed by Tim Bunce. It uses
FLUSH TABLES ,
LOCK TABLES , and
cp or scp to make a
database backup. It is a fast 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.
To use mysqlhotcopy, you must have read
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).
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,
which can be specified on the command line or in the
[mysqlhotcopy] and
[client] option file groups.
Table 4.12. mysqlhotcopy Options
--help ,
-?
Display a help message and exit.
--addtodest
Do not rename target directory (if it exists); merely add
files to it.
--allowold
Do not abort if a target exists; rename it by adding an
_old suffix.
--checkpoint=db_name .tbl_name
Insert checkpoint entries into the specified database
db_name and table
tbl_name .
--chroot=path
Base directory of the chroot jail in
which mysqld operates. The
path value should match that of
the --chroot option given to
mysqld.
--debug
Enable debug output.
--dryrun ,
-n
Report actions without performing them.
--flushlog
Flush logs after all tables are locked.
--host=host_name ,
-h host_name
The host name 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.
--keepold
Do not delete previous (renamed) target when done.
--method=command
The method for copying files (cp or
scp ). The default is
cp .
--noindices
Do not include full index files for
MyISAM tables in the backup.
This makes the backup smaller and faster. The indexes for
reloaded tables can be reconstructed later with
myisamchk -rq.
--password=password ,
-ppassword
The password to use when connecting to the server. The
password value is not optional for this option, unlike for
other MySQL programs.
Specifying a password on the command line should be
considered insecure. See
Section 5.5.6.2, “End-User Guidelines for Password Security”. You can use an
option file to avoid giving the password on the command
line.
--port=port_num ,
-P port_num
The TCP/IP port number to use when connecting to the local
server.
--quiet ,
-q
Be silent except for errors.
--record_log_pos=db_name .tbl_name
Record master and slave status in the specified database
db_name and table
tbl_name .
--regexp=expr
Copy all databases with names that match the given regular
expression.
--resetmaster
Reset the binary log after locking all the tables.
--resetslave
Reset the master.info file after
locking all the tables.
--socket=path ,
-S path
The Unix socket file to use for connections to
localhost .
--suffix=str
The suffix to use for names of copied databases.
--tmpdir=path
The temporary directory. The default is
/tmp .
--user=user_name ,
-u user_name
The MySQL user name to use when connecting to the server.
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.
4.6.10. mysqlmanager — The MySQL Instance ManagerImportant
MySQL Instance Manager is been deprecated in MySQL 5.1 and is
removed in MySQL 5.4.
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.
4.6.10.1. MySQL Instance Manager Command OptionsImportant
MySQL Instance Manager is been deprecated in MySQL 5.1 and is
removed in MySQL 5.4.
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.
It also reads option files and supports the options for
processing them described at
Section 4.2.3.3.1, “Command-Line Options that Affect Option-File Handling”.
--help ,
-?
Display a help message and exit.
--angel-pid-file=file_name
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 file name 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.
--bind-address=IP
The IP address to bind to.
--default-mysqld-path=path
The path name of the MySQL Server binary. This path name 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
path name, which depends on how the MySQL distribution was
configured. Example:
--default-mysqld-path=/usr/sbin/mysqld
--defaults-file=file_name
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 .
--install
On Windows, install Instance Manager as a Windows service.
The service name is MySQL Manager . This
option was added in MySQL 5.0.11.
--log=file_name
The path to the Instance Manager log file. This option has
no effect unless the --run-as-service
option is also given. If the file name 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 path name.
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
--monitoring-interval=seconds
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
nonexisting 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.10.5, “MySQL Server Instance Status Monitoring”,
for more information.
--passwd ,
-P
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.
See also
Section 4.6.10.4, “Instance Manager User and Password Management”. This
--password-file=file_name
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 . See
also Section 4.6.10.4, “Instance Manager User and Password Management”.
--pid-file=file_name
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.
--port=port_num
The port number to use when listening for TCP/IP connections
from clients. The default port number (assigned by IANA) is
2273.
--print-defaults
Print the current defaults and exit. This must be the first
option on the command line if it is used.
--remove
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.
--run-as-service
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.)
--socket=path
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.
--standalone
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.
--user=user_name
On Unix, the user name 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.)
--version ,
-V
Display version information and exit.
--wait-timeout=N
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.
4.6.10.2. MySQL Instance Manager Configuration FilesImportant
MySQL Instance Manager is been deprecated in MySQL 5.1 and is
removed in MySQL 5.4.
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
[mysqldN ]
sections. The [manager] section contains any
of the options listed in
Section 4.6.10.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
[mysqldN ] 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, “Server Command Options”). In addition, a
[mysqldN ] 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.
Warning
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 path name 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
4.6.10.3. Starting the MySQL Server with MySQL Instance ManagerImportant
MySQL Instance Manager is been deprecated in MySQL 5.1 and is
removed in MySQL 5.4.
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.10.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
[mysqldN ] , where
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.7, “Installation Layouts”, describes default
locations for components of MySQL distributions.) If you have
installed the MySQL server in a nonstandard 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
[mysqldN ] server
instance sections are described in
Section 4.6.10.2, “MySQL Instance Manager Configuration Files”. In these
sections, you can use a special
mysqld-path=path-to-mysqld-binary
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
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.
Warning
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.
4.6.10.4. Instance Manager User and Password ManagementImportant
MySQL Instance Manager is been deprecated in MySQL 5.1 and is
removed in MySQL 5.4.
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.
Note
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 user name 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:
This means that a client can connect to Instance Manager with a
given user name 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 user name 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
Use of the --password
option fails if mysqlmanager is invoked
directly from an IBM 5250 terminal. To work around this, use a
command like the following from the command line to generate the
password entry:
shell> mysql -B --skip-column-name \
-e 'SELECT CONCAT("user_name ",":",PASSWORD("pass_val "));'
The output from the command can be used an entry in the
/etc/mysqlmanager.passwd file.
4.6.10.5. MySQL Server Instance Status MonitoringImportant
MySQL Instance Manager is been deprecated in MySQL 5.1 and is
removed in MySQL 5.4.
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.
4.6.10.6. Connecting to MySQL Instance ManagerImportant
MySQL Instance Manager is been deprecated in MySQL 5.1 and is
removed in MySQL 5.4.
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.
4.6.10.7. MySQL Instance Manager CommandsImportant
MySQL Instance Manager is been deprecated in MySQL 5.1 and is
removed in MySQL 5.4.
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.10.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, “Server 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.
4.6.12. mysql_explain_log — Use EXPLAIN on Statements in Query Log
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:
--help ,
-?
Display a help message and exit.
--date=YYMMDD ,
-d YYMMDD
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.2, “End-User Guidelines for Password Security”.
--printerror=1 ,
-e 1
Enable error output.
--socket=path ,
-S path
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 user name to use when connecting to the server.
4.6.13. mysql_find_rows — Extract SQL Statements from Files
mysql_find_rows reads files containing SQL
statements and extracts statements that match a given regular
expression or that contain USE
db_name or
SET
statements. The utility was written for use with update log
files (as used prior to MySQL 5.0) and as such expects
statements to be terminated with semicolon
(; ) characters. It may be useful with other
files that contain SQL statements as long as statements are
terminated with semicolons.
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 file names 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:
4.6.14. mysql_fix_extensions — Normalize Table File Name Extensions
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
file names (such as Windows) to a system with case-sensitive
file names.
Invoke mysql_fix_extensions like this, where
data_dir is the path name to the
MySQL data directory.
shell> mysql_fix_extensions data_dir
4.6.15. mysql_setpermission — Interactively Set Permissions in Grant
Tables
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.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 supports the following
options:
4.6.16. mysql_tableinfo — Generate Database Metadata
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 19, 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.13. mysql_tableinfo
Options
--help
Display a help message and exit.
--clear
Before populating each metadata table, drop it if it exists.
--clear-only
Similar to --clear ,
but exits after dropping the metadata tables to be
populated.
--col
Generate column metadata into the col
table.
--host=host_name ,
-h host_name
Connect to the MySQL server on the given host.
--idx
Generate index metadata into the idx
table.
--password=password ,
-ppassword
The password to use when connecting to the server. Note that
the password value is not optional for this option, unlike
for other MySQL programs. 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.2, “End-User Guidelines for Password Security”.
--port=port_num ,
-P port_num
The TCP/IP port number to use for the connection.
--prefix=prefix_str
Add prefix_str at the beginning
of each metadata table name.
--quiet ,
-q
Be silent except for errors.
--socket=path ,
-S path
The Unix socket file to use for the connection.
--tbl-status
Use SHOW TABLE STATUS instead
of SHOW TABLES . This provides
more complete information, but is slower.
--user=user_name ,
-u user_name
The MySQL user name to use when connecting to the server.
4.6.17. mysql_waitpid — Kill Process and Wait for Its Termination
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 supports the following options:
--help ,
-? , -I
Display a help message and exit.
--verbose ,
-v
Verbose mode. Display a warning if signal 0 could not be
used and signal 1 is used instead.
--version ,
-V
Display version information and exit.
4.6.18. mysql_zap — Kill Processes That Match a Pattern
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 -signal
option is given, it specifies the name or number of the signal
to send to each process. Otherwise, mysql_zap
tries first with TERM (signal 15) and then
with KILL (signal 9).
mysql_zap supports the following additional
options:
--help ,
-? , -I
Display a help message and exit.
-f
Force mode. mysql_zap attempts to kill
each process without confirmation.
-t
Test mode. Display information about each process but do not
kill it.
4.7. MySQL Program Development Utilities
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.
4.7.1. msql2mysql — Convert mSQL Programs for Use with MySQL
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”.
4.7.2. mysql_config — Get Compile Options for Compiling Clients
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.
4.7.3. my_print_defaults — Display Options from Option Files
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 supports the following
options.
--help ,
-?
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. A typical
debug_options string is
'd:t:o,file_name ' .
The default is
'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.
--no-defaults ,
-n
Return an empty string.
--verbose ,
-v
Verbose mode. Print more information about what the program
does.
--version ,
-V
Display version information and exit.
4.7.4. resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols
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 supports the following
options.
4.8. Miscellaneous Programs4.8.1. perror — Explain Error Codes
For most system errors, MySQL displays, in addition to an
internal text message, the system error code in one of the
following styles:
message ... (errno: #)
message ... (Errcode: #)
You can find out what the error code means by 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.
--help ,
--info ,
-I , -?
Display a help message and exit.
--ndb
Print the error message for a MySQL Cluster error code.
--silent , -s
Silent mode. Print only the error message.
--verbose ,
-v
Verbose mode. Print error code and message. This is the
default behavior.
--version ,
-V
Display version information and exit.
4.8.2. replace — A String-Replacement Utility
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 from to [from to ] ... -- file_name [file_name ] ...
shell> replace from to [from to ] ... < file_name
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 file names 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.
-? , -I
Display a help message and exit.
-#debug_options
Enable debugging.
-s
Silent mode. Print less information what the program does.
-v
Verbose mode. Print more information about what the program
does.
-V
Display version information and exit.
4.8.3. resolveip — Resolve Host name to IP Address or Vice
Versa
The resolveip utility resolves host names to
IP addresses and vice versa.
Invoke resolveip like this:
shell> resolveip [options ] {host_name |ip-addr } ...
resolveip supports the following options.
--help ,
--info ,
-? , -I
Display a help message and exit.
--silent ,
-s
Silent mode. Produce less output.
--version ,
-V
Display version information and exit.
|
|