mysqld Command-line Options
In most cases you should manage mysqld options through option files.
See section 4.1.2 `my.cnf' Option Files.
mysqld and mysqld.server read options from the
mysqld and server groups. mysqld_safe read options
from the mysqld, server, mysqld_safe and
safe_mysqld groups. An embedded MySQL server usually reads
options from the server, embedded and
xxxxx_SERVER, where xxxxx is the name of the application.
mysqld accepts a lot of command-line options. Here follows some
of the most common ones. For a full list execute mysqld --help.
Options used for replication are listed in a separate section, see
section 4.10.6 Replication Options in `my.cnf'.
--ansi
-b, --basedir=path
--big-tables
--bind-address=IP
--console
--log-error
is specified. On Windows, mysqld will not close the console screen if
this option is used.
--character-sets-dir=path
--chroot=path
mysqld daemon in chroot environment at startup. Recommended
security measure since MySQL 4.0 (MySQL 3.23 is not able to provide
100% closed chroot jail).
It somewhat limits LOAD DATA INFILE and
SELECT ... INTO OUTFILE though.
--core-file
mysqld dies. For some systems you must also
specify --core-file-size to mysqld_safe.
See section 4.7.2 mysqld_safe, The Wrapper Around mysqld.
Note that on some systems, like Solaris, you will
not get a core file if you are also using the --user option.
-h, --datadir=path
--debug[...]=
--with-debug, you can use this
option to get a trace file of what mysqld is doing.
See section E.1.2 Creating Trace Files.
--default-character-set=charset
--default-table-type=type
--delay-key-write[= OFF | ON | ALL]
DELAYED KEYS should be used. See section 5.5.2 Tuning Server Parameters.
--delay-key-write-for-all-tables; In MySQL 4.0.3 you should use --delay-key-write=ALL instead.
MyISAM table.
See section 5.5.2 Tuning Server Parameters.
--des-key-file=filename
DES_ENCRYPT() and DES_DECRYPT()
from this file.
--enable-external-locking (was --enable-locking)
lockd does not fully work (as on Linux), you will easily get
mysqld to deadlock.
--enable-named-pipe
-T, --exit-info
--flush
-?, --help
--init-file=file
-L, --language=...
-l, --log[=file]
--log-bin=[file]
--log-bin-index[=file]
--log-error[=file]
--log-isam[=file]
--log-slow-queries[=file]
long_query_time seconds to
execute to file. See section 4.9.5 The Slow Query Log.
--log-update[=file]
file.# where # is a unique number if not
given. See section 4.9.3 The Update Log. The update log is deprecated and will be
removed in MySQL 5.0; you should use the binary log instead
(--log-bin). See section 4.9.4 The Binary Log. Starting from version 5.0,
using --log-update will just turn on the binlog instead.
--log-long-format
--log-slow-queries then queries that are not using indexes are logged
to the slow query log.
--low-priority-updates
INSERT/DELETE/UPDATE)
will have lower priority than selects. It can also be done via
{INSERT | REPLACE | UPDATE | DELETE} LOW_PRIORITY ... to lower
the priority of only one query, or by
SET LOW_PRIORITY_UPDATES=1 to change the priority in one
thread. See section 5.3.2 Table Locking Issues.
--memlock
mysqld process in memory. This works only if your
system supports the mlockall() system call (like Solaris). This
may help if you have a problem where the operating system is causing
mysqld to swap on disk.
--myisam-recover [=option[,option...]]]
DEFAULT, BACKUP, FORCE or QUICK. You can
also set this explicitly to "" if you want to disable this
option. If this option is used, mysqld will on open check if the
table is marked as crashed or if the table wasn't closed properly.
(The last option only works if you are running with
--skip-external-locking.) If this is the case mysqld will run
check on the table. If the table was corrupted, mysqld will
attempt to repair it.
The following options affects how the repair works.
| Option | Description |
| DEFAULT | The same as not giving any option to
--myisam-recover.
|
| BACKUP | If the data table was changed during recover, save a backup of the `table_name.MYD' datafile as `table_name-datetime.BAK'. |
| FORCE | Run recover even if we will lose more than one row from the .MYD file. |
| QUICK | Don't check the rows in the table if there aren't any delete blocks. |
BACKUP,FORCE. This will force a repair of a table even if some rows
would be deleted, but it will keep the old datafile as a backup so that
you can later examine what happened.
--new
--new option can be used to make the server
behave as 4.1 in certain aspects, easing a 4.0 to 4.1 upgrade:
TIMESTAMP is returned as a string with the format
'YYYY-MM-DD HH:MM:SS'.
See section 6.2 Column Types.
--pid-file=path
mysqld_safe.
-P, --port=...
-o, --old-protocol
--one-thread
-O, --set-variable var=option
--help lists variables. You can find a full
description for all variables in the SHOW VARIABLES section in this
manual. See section 4.5.7.4 SHOW VARIABLES. The tuning server parameters section includes
information of how to optimise these. Please note that --set-variable
is deprecated since MySQL 4.0, just use --var=option on its own.
See section 5.5.2 Tuning Server Parameters.
In MySQL 4.0.2 one can set a variable directly with
--variable-name=option and set-variable is no longer needed
in option files.
If you want to restrict the maximum value a startup option can be set to
with SET, you can define this by using the
--maximum-variable-name command line option. See section 5.5.6 SET Syntax.
Note that when setting a variable to a value, MySQL may automatically
correct it to stay within a given range and also adjusts the value a
little to fix for the used algorithm.
--safe-mode
--safe-show-database
SHOW DATABASES command returns only those
databases for which the user has some kind of privilege.
From version 4.0.2 this option is deprecated and doesn't do anything
(the option is enabled by default) as we now have the
SHOW DATABASES privilege. See section 4.3.1 GRANT and REVOKE Syntax.
--safe-user-create
INSERT privilege to the
mysql.user table or any column in this table.
--skip-bdb
--skip-concurrent-insert
MyISAM
tables. (This is only to be used if you think you have found a bug in this
feature.)
--skip-delay-key-write; In MySQL 4.0.3 you should use --delay-key-write=OFF instead.
DELAY_KEY_WRITE option for all tables.
See section 5.5.2 Tuning Server Parameters.
--skip-grant-tables
mysqladmin
flush-privileges or mysqladmin reload.)
--skip-host-cache
--skip-innodb
--skip-external-locking (was --skip-locking)
isamchk or myisamchk you must
shut down the server. See section 1.2.3 How Stable Is MySQL?. Note that in MySQL Version
3.23 you can use REPAIR and CHECK to repair/check MyISAM
tables.
--skip-name-resolve
Host column values in the grant tables
must be IP numbers or localhost. See section 5.5.5 How MySQL uses DNS.
--skip-networking
mysqld must be made via Unix sockets. This option is highly
recommended for systems where only local requests are allowed. See section 5.5.5 How MySQL uses DNS.
--skip-new
--skip-symlink
--symbolic-links || --skip-symbolic-links
CREATE TABLE .... INDEX/DATA DIRECTORY="path-to-dir" command.
When you delete or rename a table, the file that the symbolic link points
to will also be deleted/renamed.
On Windows, this means that you can create a directory.sym file that
contains the path to the real directory. See section 2.6.2.5 Splitting Data Across Different Disks on Windows.
--skip-safemalloc
--with-debug=full, all programs
will check the memory for overruns for every memory allocation and memory
freeing. As this checking is very slow, you can avoid this, when you don't
need memory checking, by using this option.
--skip-show-database
SHOW DATABASES command, unless the user has the
SHOW DATABASES privilege. From version 4.0.2 you should no longer
need this option, since access can now be granted specifically with the
SHOW DATABASES privilege.
--skip-stack-trace
mysqld under a debugger. On some systems you also have to use
this option to get a core file. See section E.1 Debugging a MySQL server.
--skip-thread-priority
--socket=path
/tmp/mysql.sock.
--sql-mode=option[,option[,option...]]
REAL_AS_FLOAT,
PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE,
SERIALIZE, ONLY_FULL_GROUP_BY. It can also be empty
("") if you want to reset this.
By specifying all of the above options is same as using --ansi.
With this option one can turn on only needed SQL modes. See section 1.8.2 Running MySQL in ANSI Mode.
--temp-pool
--transaction-isolation= { READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE }
SET TRANSACTION Syntax.
-t, --tmpdir=path
/tmp
directory resides on a partition too small to hold temporary tables.
Starting from MySQL 4.1, this option accepts several paths separated
by colon : (semicolon ; on Windows). They will be used
in round-robin fashion.
-u, --user= [user_name | userid]
mysqld daemon as user user_name or userid (numeric).
This option is mandatory when starting mysqld as root.
Starting from MySQL 3.23.56 and 4.0.12:
To avoid a possible security hole where a user adds an --user=root
option to some `my.cnf' file, mysqld will only use the first
--user option specified and give a warning if there are multiple
options. Note that `/etc/my.cnf' and `datadir/my.cnf' may override
a command line option - therefore it is recommended to put this option in
`/etc/my.cnf'.
-V, --version
-W, --log-warnings (Was --warnings)
Aborted connection... to the
`.err' file. Enabling it is recommended if you use replication
for example (you will get more messages about what is happening,
like network failures and reconnections). See section A.2.10 Communication Errors / Aborted Connection.
One can change most values for a running server with the
SET command. See section 5.5.6 SET Syntax.
MySQL can, since Version 3.22, read default startup options for the server and for clients from option files.
MySQL reads default options from the following files on Unix:
| Filename | Purpose |
/etc/my.cnf | Global options |
DATADIR/my.cnf | Server-specific options |
defaults-extra-file | The file specified with --defaults-extra-file=# |
~/.my.cnf | User-specific options |
DATADIR is the MySQL data directory (typically
`/usr/local/mysql/data' for a binary installation or
`/usr/local/var' for a source installation). Note that this is the
directory that was specified at configuration time, not the one specified
with --datadir when mysqld starts up! (--datadir has no
effect on where the server looks for option files, because it looks for them
before it processes any command-line arguments.)
MySQL reads default options from the following files on Windows:
| Filename | Purpose |
windows-directory\my.ini | Global options |
C:\my.cnf | Global options |
Note that on Windows, you should specify all paths with / instead of
\. If you use \, you need to specify this twice, as
\ is the escape character in MySQL.
MySQL tries to read option files in the order listed above. If multiple option files exist, an option specified in a file read later takes precedence over the same option specified in a file read earlier. Options specified on the command-line take precedence over options specified in any option file. Some options can be specified using environment variables. Options specified on the command-line or in option files take precedence over environment variable values. See section F Environment Variables.
The following programs support option files: mysql,
mysqladmin, mysqld, mysqld_safe, mysql.server,
mysqldump, mysqlimport, mysqlshow, mysqlcheck,
myisamchk, and myisampack.
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 (without the leading double
dash). Run the program with --help to get a list of available options.
An option file can contain lines of the following forms:
#comment
[group]
group is the name of the program or group for which you want to set
options. After a group line, any option or set-variable lines
apply to the named group until the end of the option file or another group
line is given.
option
--option on the command-line.
option=value
--option=value on the command-line.
set-variable = variable=value
--set-variable variable=value on the command-line.
This syntax must be used to set a mysqld variable.
Please note that --set-variable is deprecated since MySQL 4.0,
just use --variable=value on its own.
The client group allows you to specify options that apply to all
MySQL clients (not mysqld). This is the perfect group to use
to specify the password you use to connect to the server. (But make
sure the option file is readable and writable only by yourself.)
Note that for options and values, all leading and trailing blanks are automatically deleted. You may use the escape sequences `\b', `\t', `\n', `\r', `\\', and `\s' in your value string (`\s' == blank).
Here is a typical global option file:
[client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock set-variable = key_buffer_size=16M set-variable = max_allowed_packet=1M [mysqldump] quick
Here is typical user option file:
[client] # The following password will be sent to all standard MySQL clients password=my_password [mysql] no-auto-rehash set-variable = connect_timeout=2 [mysqlhotcopy] interactive-timeout
If you have a source distribution, you will find sample configuration
files named `my-xxxx.cnf' in the `support-files' directory.
If you have a binary distribution, look in the `DIR/support-files'
directory, where DIR is the pathname to the MySQL
installation directory (typically `/usr/local/mysql'). Currently
there are sample configuration files for small, medium, large, and very
large systems. You can copy `my-xxxx.cnf' to your home directory
(rename the copy to `.my.cnf') to experiment with this.
All MySQL clients that support option files support the following options:
| Option | Description |
| --no-defaults | Don't read any option files. |
| --print-defaults | Print the program name and all options that it will get. |
| --defaults-file=full-path-to-default-file | Only use the given configuration file. |
| --defaults-extra-file=full-path-to-default-file | Read this configuration file after the global configuration file but before the user configuration file. |
Note that the above options must be first on the command-line to work!
--print-defaults may however be used directly after the
--defaults-xxx-file commands.
Note for developers: Option file handling is implemented simply by processing all matching options (that is, options in the appropriate group) before any command-line arguments. This works nicely for programs that use the last instance of an option that is specified multiple times. If you have an old program that handles multiply-specified options this way but 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.
In shell scripts you can use the `my_print_defaults' command to parse the config files:
shell> my_print_defaults client mysql --port=3306 --socket=/tmp/mysql.sock --no-auto-rehash
The above output contains all options for the groups 'client' and 'mysql'.
In some cases you may want to have many different mysqld daemons
(servers) running on the same machine. You may for example want to run
a new version of MySQL for testing together with an old version
that is in production. Another case is when you want to give different
users access to different mysqld servers that they manage themselves.
One way to get a new server running is by starting it with a different socket and port as follows:
shell> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock shell> MYSQL_TCP_PORT=3307 shell> export MYSQL_UNIX_PORT MYSQL_TCP_PORT shell> scripts/mysql_install_db shell> bin/mysqld_safe &
The environment variables appendix includes a list of other environment
variables you can use to affect mysqld. See section F Environment Variables.
The above is the quick and dirty way that one commonly uses for testing. The nice thing with this is that all connections you do in the above shell will automatically be directed to the new running server!
If you need to do this more permanently, you should create an option file for each server. See section 4.1.2 `my.cnf' Option Files. In your startup script that is executed at boot time you should specify this command for both servers, with an appropriate path for each command:
mysqld_safe --defaults-file=path-to-option-file
At least the following options should be different per server:
The following options should be different, if they are used:
If you want more performance, you can also specify the following differently:
See section 4.1.1 mysqld Command-line Options.
Starting from MySQL 4.1, tmpdir can be set to a list of paths
separated by colon : (semicolon ; on Windows). They
will be used in round-robin fashion. This feature can be used to
spread load between several physical disks.
If you are installing binary MySQL versions (.tar files) and
start them with ./bin/mysqld_safe then in most cases the only
option you need to add/change is the socket and port
argument to mysqld_safe.
See section 4.1.4 Running Multiple MySQL Servers on the Same Machine.
There are circumstances when you might want to run multiple servers on the same machine. For example, you might want to test a new MySQL release while leaving your existing production setup undisturbed. Or you might be an Internet service provider that wants to provide independent MySQL installations for different customers.
If you want to run multiple servers, the easiest way is to compile the servers
with different TCP/IP ports and socket files so they are not
both listening to the same TCP/IP port or socket file. See section 4.7.3 mysqld_multi, A Program for Managing Multiple MySQL Servers.
Assume an existing server is configured for the default port number and
socket file. Then configure the new server with a configure command
something like this:
shell> ./configure --with-tcp-port=port_number \
--with-unix-socket-path=file_name \
--prefix=/usr/local/mysql-3.22.9
Here port_number and file_name should be different from the
default port number and socket file pathname, and the --prefix value
should specify an installation directory different from the one under which
the existing MySQL installation is located.
You can check the socket used by any currently executing MySQL server with this command:
shell> mysqladmin -h hostname --port=port_number variables
Note that if you specify ``localhost'' as a hostname, mysqladmin
will default to using Unix sockets instead of TCP/IP.
In MySQL 4.1 you can also specify the protocol to use by using the
--protocol=(TCP | SOCKET | PIPE | MEMORY) option.
If you have a MySQL server running on the port you used, you will get a list of some of the most important configurable variables in MySQL, including the socket name.
You don't have to recompile a new MySQL server just to start with
a different port and socket. You can change the port and socket to be used
by specifying them at runtime as options to mysqld_safe:
shell> /path/to/mysqld_safe --socket=file_name --port=port_number
mysqld_multi can also take mysqld_safe (or mysqld)
as an argument and pass the options from a configuration file to
mysqld_safe and further to mysqld.
If you run the new server on the same database directory as another
server with logging enabled, you should also specify the name of the log
files to mysqld_safe with --log, --log-update,
--log-bin or --log-slow-queries. Otherwise, both
servers may be trying to write to the same log file.
Warning: normally you should never have two servers that update data in the same database! If your OS doesn't support fault-free system locking, this may lead to unpleasant surprises!
If you want to use another database directory for the second server, you
can use the --datadir=path option to mysqld_safe.
Note also that starting several MySQL servers
(mysqlds) in different machines and letting them access one data
directory over NFS is generally a bad idea! The problem
is that the NFS will become the bottleneck with the speed. It is
not meant for such use. And last but not least, you would still have to
come up with a solution how to make sure that two or more mysqlds
are not interfering with each other. At the moment there is no platform
that would 100% reliable do the file locking (lockd daemon
usually) in every situation. Yet there would be one more possible risk
with NFS; it would make the work even more complicated for
lockd daemon to handle. So make it easy for your self and forget
about the idea. The working solution is to have one computer with an
operating system that efficiently handles threads and have several CPUs
in it.
When you want to connect to a MySQL server that is running with a different port than the port that is compiled into your client, you can use one of the following methods:
--host 'hostname' --port=port_number to connect
with TCP/IP, or [--host localhost] --socket=file_name to connect via
a Unix socket.
--protocol=tcp to connect with TCP/IP and
--protocol=socket to connect via a Unix socket.
DBD::mysql module you can read the options
from the MySQL option files. See section 4.1.2 `my.cnf' Option Files.
$dsn = "DBI:mysql:test;mysql_read_default_group=client;
mysql_read_default_file=/usr/local/mysql/data/my.cnf"
$dbh = DBI->connect($dsn, $user, $password);
MYSQL_UNIX_PORT and MYSQL_TCP_PORT environment variables
to point to the Unix socket and TCP/IP port before you start your clients.
If you normally use a specific socket or port, you should place commands
to set these environment variables in your `.login' file.
See section F Environment Variables.
MySQL has an advanced but non-standard security/privilege system. This section describes how it works.
Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.
In discussing security, we emphasise the necessity of fully protecting the entire server host (not simply the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.
MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that a user may attempt to perform. There is also some support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications.
When running MySQL, follow these guidelines whenever possible:
root user) access to the
user table in the mysql database! This is critical.
The encrypted password is the real password in MySQL.
Anyone who knows the password which is listed in the user table
and has access to the host listed for the account can easily log
in as that user.
GRANT and
REVOKE commands are used for controlling access to MySQL. Do
not grant any more privileges than necessary. Never grant privileges to all
hosts.
Checklist:
mysql -u root. If you are able to connect successfully to the
server without being asked for a password, you have problems. Anyone
can connect to your MySQL server as the MySQL
root user with full privileges!
Review the MySQL installation instructions, paying particular
attention to the item about setting a root password.
SHOW GRANTS and check to see who has access to
what. Remove those privileges that are not necessary using the REVOKE
command.
MD5(), SHA1() or
another one-way hashing function.
nmap. MySQL uses port 3306 by default. This port should
be inaccessible from untrusted hosts. Another simple way to check whether
or not your MySQL port is open is to try the following command
from some remote machine, where server_host is the hostname of
your MySQL server:
shell> telnet server_host 3306If you get a connection and some garbage characters, the port is open, and should be closed on your firewall or router, unless you really have a good reason to keep it open. If
telnet just hangs or the
connection is refused, everything is OK; the port is blocked.
; DROP
DATABASE mysql;''. This is an extreme example, but large security leaks
and data loss may occur as a result of hackers using similar techniques,
if you do not prepare for them.
Also remember to check numeric data. A common mistake is to protect only
strings. Sometimes people think that if a database contains only publicly
available data that it need not be protected. This is incorrect. At least
denial-of-service type attacks can be performed on such
databases. The simplest way to protect from this type of attack is to use
apostrophes around the numeric constants: SELECT * FROM table
WHERE ID='234' rather than SELECT * FROM table WHERE ID=234.
MySQL automatically converts this string to a number and
strips all non-numeric symbols from it.
Checklist:
%22 (`"'), %23
(`#'), and %27 (`'') in the URL.
addslashes() function.
As of PHP 4.0.3, a mysql_escape_string() function is available
that is based on the function of the same name in the MySQL C API.
mysql_real_escape_string() API call.
escape and quote modifiers for query streams.
quote() method or use placeholders.
PreparedStatement object and placeholders.
tcpdump and strings utilities. For most cases,
you can check whether MySQL data streams are unencrypted
by issuing a command like the following:
shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings(This works under Linux and should work with small modifications under other systems.) Warning: If you do not see data this doesn't always actually mean that it is encrypted. If you need high security, you should consult with a security expert.
When you connect to a MySQL server, you normally should use a password. The password is not transmitted in clear text over the connection, however the encryption algorithm is not very strong, and with some effort a clever attacker can crack the password if he is able to sniff the traffic between the client and the server. If the connection between the client and the server goes through an untrusted network, you should use an SSH tunnel to encrypt the communication.
All other information is transferred as text that can be read by anyone
who is able to watch the connection. If you are concerned about this,
you can use the compressed protocol (in MySQL Version 3.22 and above)
to make things much harder. To make things even more secure you should use
ssh. You can find an Open Source ssh client at
http://www.openssh.org/, and a commercial ssh client at
http://www.ssh.com/. With this, you can get an encrypted TCP/IP
connection between a MySQL server and a MySQL client.
If you are using MySQL 4.0, you can also use internal OpenSSL support. See section 4.3.9 Using Secure Connections.
To make a MySQL system secure, you should strongly consider the following suggestions:
mysql -u other_user db_name if
other_user has no password. It is common behaviour with client/server
applications that the client may specify any user name. You can change the
password of all users by editing the mysql_install_db script before
you run it, or only the password for the MySQL root user like
this:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
-> WHERE user='root';
mysql> FLUSH PRIVILEGES;
root user. This is
very dangerous, because any user with the FILE privilege will be able
to create files as root (for example, ~root/.bashrc). To
prevent this, mysqld will refuse to run as root unless it
is specified directly using a --user=root option.
mysqld can be run as an ordinary unprivileged user instead.
You can also create a new Unix user mysql to make everything
even more secure. If you run mysqld as another Unix user,
you don't need to change the root user name in the user
table, because MySQL user names have nothing to do with Unix
user names. To start mysqld as another Unix user, add a user
line that specifies the user name to the [mysqld] group of the
`/etc/my.cnf' option file or the `my.cnf' option file in the
server's data directory. For example:
[mysqld] user=mysqlThis will cause the server to start as the designated user whether you start it manually or by using
mysqld_safe or mysql.server.
For more details, see section A.3.2 How to Run MySQL As a Normal User.
--skip-symlink option). This is especially important if you run
mysqld as root as anyone that has write access to the mysqld data
directories could then delete any file in the system!
See section 5.6.1.2 Using Symbolic Links for Tables.
mysqld runs as is the only user with
read/write privileges in the database directories.
PROCESS privilege to all users. The output of
mysqladmin processlist shows the text of the currently executing
queries, so any user who is allowed to execute that command might be able to
see if another user issues an UPDATE user SET
password=PASSWORD('not_secure') query.
mysqld reserves an extra connection for users who have the
PROCESS privilege, so that a MySQL root user can log
in and check things even if all normal connections are in use.
FILE privilege to all users. Any user that has this
privilege can write a file anywhere in the filesystem with the privileges of
the mysqld daemon! To make this a bit safer, all files generated with
SELECT ... INTO OUTFILE are writeable by everyone, and you cannot
overwrite existing files.
The FILE privilege may also be used to read any world readable
file that is accessible to the Unix user that the server runs as. One can also
read any file to the current database (which the user need some privilege for).
This could be abused, for example, by using LOAD DATA to load
`/etc/passwd' into a table, which can then be read with
SELECT.
max_user_connections variable in
mysqld.
mysqld Concerning Security
The following mysqld options affect security:
--local-infile[=(0|1)]
--local-infile=0 then one can't use LOAD DATA LOCAL
INFILE.
--safe-show-database
SHOW DATABASES command returns only those
databases for which the user has some kind of privilege.
From version 4.0.2 this option is deprecated and doesn't do anything
(the option is enabled by default) as we now have the
SHOW DATABASES privilege. See section 4.3.1 GRANT and REVOKE Syntax.
--safe-user-create
GRANT
command, if the user doesn't have the INSERT privilege for the
mysql.user table. If you want to give a user access to just create
new users with those privileges that the user has right to grant, you should
give the user the following privilege:
mysql> GRANT INSERT(user) ON mysql.user TO 'user'@'hostname';This will ensure that the user can't change any privilege columns directly, but has to use the
GRANT command to give privileges to other users.
--skip-grant-tables
mysqladmin
flush-privileges or mysqladmin reload.)
--skip-name-resolve
Host column values in the grant
tables must be IP numbers or localhost.
--skip-networking
mysqld must be made via Unix sockets.
This option is unsuitable when using a MySQL version prior to 3.23.27 with
the MIT-pthreads package, because Unix sockets were not supported by
MIT-pthreads at that time.
--skip-show-database
SHOW DATABASES command, unless the user has the
SHOW DATABASES privilege. From version 4.0.2 you should no longer
need this option, since access can now be granted specifically with the
SHOW DATABASES privilege.
LOAD DATA LOCAL
In MySQL 3.23.49 and MySQL 4.0.2, we added some new options to deal with
possible security issues when it comes to LOAD DATA LOCAL.
There are two possible problems with supporting this command:
As the reading of the file is initiated from the server, one could theoretically create a patched MySQL server that could read any file on the client machine that the current user has read access to, when the client issues a query against the table.
In a web environment where the clients are connecting from a web
server, a user could use LOAD DATA LOCAL to read any files
that the web server process has read access to (assuming a user could
run any command against the SQL server).
There are two separate fixes for this:
If you don't configure MySQL with --enable-local-infile, then
LOAD DATA LOCAL will be disabled by all clients, unless one
calls mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0) in the client.
See section 9.1.3.39 mysql_options().
For the mysql command-line client, LOAD DATA LOCAL can be
enabled by specifying the option --local-infile[=1], or disabled
with --local-infile=0.
By default, all MySQL clients and libraries are compiled with
--enable-local-infile, to be compatible with MySQL 3.23.48 and
before.
One can disable all LOAD DATA LOCAL commands in the MySQL server
by starting mysqld with --local-infile=0.
In the case that LOAD DATA LOCAL INFILE is disabled in the server or
the client, you will get the error message (1148):
The used command is not allowed with this MySQL version
The primary function of the MySQL privilege system is to
authenticate a user connecting from a given host, and to associate that user
with privileges on a database such as
SELECT, INSERT, UPDATE and DELETE.
Additional functionality includes the ability to have an anonymous user and
to grant privileges for MySQL-specific functions such as LOAD
DATA INFILE and administrative operations.
The MySQL privilege system ensures that all users may do exactly the things that they are supposed to be allowed to do. When you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify. The system grants privileges according to your identity and what you want to do.
MySQL considers both your hostname and user name in identifying you
because there is little reason to assume that a given user name belongs to
the same person everywhere on the Internet. For example, the user
joe who connects from office.com need not be the same
person as the user joe who connects from elsewhere.com.
MySQL handles this by allowing you to distinguish users on different
hosts that happen to have the same name: you can grant joe one set
of privileges for connections from office.com, and a different set
of privileges for connections from elsewhere.com.
MySQL access control involves two stages:
SELECT
privilege for the table or the DROP privilege for the database.
Note that if your privileges are changed (either by yourself or someone else) while you are connected, those changes will not necessarily take effect with your next query or queries. See section 4.3.3 When Privilege Changes Take Effect for details.
The server uses the user, db, and host tables in the
mysql database at both stages of access control. The fields in these
grant tables are shown here:
| Table name | user | db | host |
| Scope fields | Host | Host | Host
|
User | Db | Db
| |
Password | User | ||
| Privilege fields | Select_priv | Select_priv | Select_priv
|
Insert_priv | Insert_priv | Insert_priv
| |
Update_priv | Update_priv | Update_priv
| |
Delete_priv | Delete_priv | Delete_priv
| |
Index_priv | Index_priv | Index_priv
| |
Alter_priv | Alter_priv | Alter_priv
| |
Create_priv | Create_priv | Create_priv
| |
Drop_priv | Drop_priv | Drop_priv
| |
Grant_priv | Grant_priv | Grant_priv
| |
References_priv | |||
Reload_priv | |||
Shutdown_priv | |||
Process_priv | |||
File_priv | |||
Show_db_priv | |||
Super_priv | |||
Create_tmp_table_priv | Create_tmp_table_priv | Create_tmp_table_priv
| |
Lock_tables_priv | Lock_tables_priv | Lock_tables_priv
| |
Execute_priv | |||
Repl_slave_priv | |||
Repl_client_priv | |||
ssl_type | |||
ssl_cypher | |||
x509_issuer | |||
x509_cubject | |||
max_questions | |||
max_updates | |||
max_connections |
For the second stage of access control (request verification), the server
may, if the request involves tables, additionally consult the
tables_priv and columns_priv tables. The fields in these
tables are shown here:
| Table name | tables_priv | columns_priv |
| Scope fields | Host | Host
|
Db | Db
| |
User | User
| |
Table_name | Table_name
| |
Column_name
| ||
| Privilege fields | Table_priv | Column_priv
|
Column_priv | ||
| Other fields | Timestamp | Timestamp
|
Grantor |
Each grant table contains scope fields and privilege fields.
Scope fields determine the scope of each entry in the tables, that is, the
context in which the entry applies. For example, a user table entry
with Host and User values of 'thomas.loc.gov' and
'bob' would be used for authenticating connections made to the server
by bob from the host thomas.loc.gov. Similarly, a db
table entry with Host, User, and Db fields of
'thomas.loc.gov', 'bob' and 'reports' would be used when
bob connects from the host thomas.loc.gov to access the
reports database. The tables_priv and columns_priv
tables contain scope fields indicating tables or table/column combinations
to which each entry applies.
For access-checking purposes, comparisons of Host values are
case-insensitive. User, Password, Db, and
Table_name values are case-sensitive.
Column_name values are case-insensitive in MySQL Version
3.22.12 or later.
Privilege fields indicate the privileges granted by a table entry, that is, what operations can be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. The rules used to do this are described in section 4.2.10 Access Control, Stage 2: Request Verification.
Scope fields are strings, declared as shown here; the default value for each is the empty string:
| Field name | Type | Notes |
Host | CHAR(60) | |
User | CHAR(16) | |
Password | CHAR(16) | |
Db | CHAR(64) | (CHAR(60) for the
tables_priv and columns_priv tables)
|
Table_name | CHAR(60) | |
Column_name | CHAR(60) |
In the user, db and host tables,
all privilege fields are declared as ENUM('N','Y')---each can have a
value of 'N' or 'Y', and the default value is 'N'.
In the tables_priv and columns_priv tables, the privilege
fields are declared as SET fields:
| Table name | Field name | Possible set elements |
tables_priv
| Table_priv
| 'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
|
tables_priv
| Column_priv
| 'Select', 'Insert', 'Update', 'References'
|
columns_priv
| Column_priv
| 'Select', 'Insert', 'Update', 'References'
|
Briefly, the server uses the grant tables like this:
user table scope fields determine whether to allow or reject
incoming connections. For allowed connections, any privileges granted in
the user table indicate the user's global (superuser) privileges.
These privileges apply to all databases on the server.
db and host tables are used together:
db table scope fields determine which users can access which
databases from which hosts. The privilege fields determine which operations
are allowed.
host table is used as an extension of the db table when you
want a given db table entry to apply to several hosts. For example,
if you want a user to be able to use a database from several hosts in
your network, leave the Host value empty in the user's db table
entry, then populate the host table with an entry for each of those
hosts. This mechanism is described more detail in section 4.2.10 Access Control, Stage 2: Request Verification.
tables_priv and columns_priv tables are similar to
the db table, but are more fine-grained: they apply at the
table and column levels rather than at the database level.
Note that administrative privileges (RELOAD, SHUTDOWN,
etc.) are specified only in the user table. This is because
administrative operations are operations on the server itself and are not
database-specific, so there is no reason to list such privileges in the
other grant tables. In fact, only the user table need
be consulted to determine whether you can perform an administrative
operation.
The FILE privilege is specified only in the user table, too.
It is not an administrative privilege as such, but your ability to read or
write files on the server host is independent of the database you are
accessing.
The mysqld server reads the contents of the grant tables once, when it
starts up. Changes to the grant tables take effect as indicated in
section 4.3.3 When Privilege Changes Take Effect.
When you modify the contents of the grant tables, it is a good idea to make
sure that your changes set up privileges the way you want. For help in
diagnosing problems, see section 4.2.13 Causes of Access denied Errors. For advice on security issues,
see section 4.2.2 How to Make MySQL Secure Against Crackers.
A useful
diagnostic tool is the mysqlaccess script, which Yves Carlier has
provided for the MySQL distribution. Invoke mysqlaccess with
the --help option to find out how it works.
Note that mysqlaccess checks access using only the user,
db and host tables. It does not check table- or column-level
privileges.
Information about user privileges is stored in the user, db,
host, tables_priv, and columns_priv tables in the
mysql database (that is, in the database named mysql). The
MySQL server reads the contents of these tables when it starts up
and under the circumstances indicated in section 4.3.3 When Privilege Changes Take Effect.
The names used in this manual to refer to the privileges provided by MySQL version 4.0.2 are shown here, along with the table column name associated with each privilege in the grant tables and the context in which the privilege applies:
| Privilege | Column | Context |
ALTER | Alter_priv | tables |
DELETE | Delete_priv | tables |
INDEX | Index_priv | tables |
INSERT | Insert_priv | tables |
SELECT | Select_priv | tables |
UPDATE | Update_priv | tables |
CREATE | Create_priv | databases, tables, or indexes |
DROP | Drop_priv | databases or tables |
GRANT | Grant_priv | databases or tables |
REFERENCES | References_priv | databases or tables |
CREATE TEMPORARY TABLES | Create_tmp_table_priv | server administration |
EXECUTE | Execute_priv | server administration |
FILE | File_priv | file access on server |
LOCK TABLES | Lock_tables_priv | server administration |
PROCESS | Process_priv | server administration |
RELOAD | Reload_priv | server administration |
REPLICATION CLIENT | Repl_client_priv | server administration |
REPLICATION SLAVE | Repl_slave_priv | server administration |
SHOW DATABASES | Show_db_priv | server administration |
SHUTDOWN | Shutdown_priv | server administration |
SUPER | Super_priv | server administration |
The SELECT, INSERT, UPDATE, and DELETE
privileges allow you to perform operations on rows in existing tables in
a database.
SELECT statements require the SELECT privilege only if they
actually retrieve rows from a table. You can execute certain SELECT
statements even without permission to access any of the databases on the
server. For example, you could use the mysql client as a simple
calculator:
mysql> SELECT 1+1; mysql> SELECT PI()*2;
The INDEX privilege allows you to create or drop (remove) indexes.
The ALTER privilege allows you to use ALTER TABLE.
The CREATE and DROP privileges allow you to create new
databases and tables, or to drop (remove) existing databases and tables.
Note that if you grant the DROP privilege for the mysql
database to a user, that user can drop the database in which the
MySQL access privileges are stored!
The GRANT privilege allows you to give to other users those
privileges you yourself possess.
The FILE privilege gives you permission to read and write files on
the server using the LOAD DATA INFILE and SELECT ... INTO
OUTFILE statements. Any user to whom this privilege is granted can read
any world readable file accessable by the MySQL server and create a new
world readable file in any directory where the MySQL server can write.
The user can also read any file in the current database directory.
The user can however not change any existing file.
The remaining privileges are used for administrative operations, which are
performed using the mysqladmin program. The table here shows which
mysqladmin commands each administrative privilege allows you to
execute:
| Privilege | Commands permitted to privilege holders |
RELOAD | reload, refresh, flush-privileges, flush-hosts, flush-logs, and flush-tables
|
SHUTDOWN | shutdown
|
PROCESS | processlist
|
SUPER | kill
|
The reload command tells the server to re-read the grant tables. The
refresh command flushes all tables and opens and closes the log
files. flush-privileges is a synonym for reload. The other
flush-* commands perform functions similar to refresh but are
more limited in scope, and may be preferable in some instances. For example,
if you want to flush just the log files, flush-logs is a better choice
than refresh.
The shutdown command shuts down the server.
The processlist command displays information about the threads
executing within the server. The kill command kills server
threads. You can always display or kill your own threads, but you need
the PROCESS privilege to display and SUPER privilege to
kill threads initiated by other users. See section 4.5.6 KILL Syntax.
It is a good idea in general to grant privileges only to those users who need them, but you should exercise particular caution in granting certain privileges:
GRANT privilege allows users to give away their privileges to
other users. Two users with different privileges and with the GRANT
privilege are able to combine privileges.
ALTER privilege may be used to subvert the privilege system
by renaming tables.
FILE privilege can be abused to read any world-readable file
on the server or any file in the current database directory on the
server into a database table, the contents of which can then be accessed
using SELECT.
SHUTDOWN privilege can be abused to deny service to other
users entirely, by terminating the server.
PROCESS privilege can be used to view the plain text of
currently executing queries, including queries that set or change passwords.
mysql database can be used to change passwords
and other access privilege information. (Passwords are stored
encrypted, so a malicious user cannot simply read them to know the plain
text password.) If they can access the mysql.user password
column, they can use it to log into the MySQL server
for the given user. (With sufficient privileges, the same user can
replace a password with a different one.)
There are some things that you cannot do with the MySQL privilege system:
MySQL client programs generally require that you specify connection
parameters when you want to access a MySQL server: the host you want
to connect to, your user name, and your password. For example, the
mysql client can be started like this (optional arguments are enclosed
between `[' and `]'):
shell> mysql [-h host_name] [-u user_name] [-pyour_pass]
Alternate forms of the -h, -u, and -p options are
--host=host_name, --user=user_name, and
--password=your_pass. Note that there is no space between
-p or --password= and the password following it.
Note: Specifying a password on the command-line is not secure!
Any user on your system may then find out your password by typing a command
like: ps auxww. See section 4.1.2 `my.cnf' Option Files.
mysql uses default values for connection parameters that are missing
from the command-line:
localhost.
-p is missing.
Thus, for a Unix user joe, the following commands are equivalent:
shell> mysql -h localhost -u joe shell> mysql -h localhost shell> mysql -u joe shell> mysql
Other MySQL clients behave similarly.
On Unix systems, you can 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:
[client] section of the
`.my.cnf' configuration file in your home directory. The relevant
section of the file might look like this:
[client] host=host_name user=user_name password=your_passSee section 4.1.2 `my.cnf' Option Files.
mysql using MYSQL_HOST. The
MySQL user name can be specified using USER (this is for
Windows only). The password can be specified using MYSQL_PWD
(but this is insecure; see the next section). See section F Environment Variables.
When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, then enters Stage 2 and waits for requests.
Your identity is based on two pieces of information:
Identity checking is performed using the three user table scope fields
(Host, User, and Password). The server accepts the
connection only if a user table entry matches your hostname and user
name, and you supply the correct password.
Values in the user table scope fields may be specified as follows:
Host value may be a hostname or an IP number, or 'localhost'
to indicate the local host.
Host
field.
Host value of '%' matches any hostname.
Host value means that the privilege should be anded
with the entry in the host table that matches the given host name.
You can find more information about this in the next chapter.
Host values specified as
IP numbers, you can specify a netmask indicating how many address bits to
use for the network number. For example:
mysql> GRANT ALL PRIVILEGES ON db.*
-> TO david@'192.58.197.0/255.255.255.0';
This will allow everyone to connect from an IP where the following is true:
user_ip & netmask = host_ip.In the above example all IP:s in the interval 192.58.197.0 - 192.58.197.255 can connect to the MySQL server.
User field, but you can
specify a blank value, which matches any name. If the user table
entry that matches an incoming connection has a blank user name, the user is
considered to be the anonymous user (the user with no name), rather than the
name that the client actually specified. This means that a blank user name
is used for all further access checking for the duration of the connection
(that is, during Stage 2).
Password field can be blank. This does not mean that any password
matches, it means the user must connect without specifying a password.
Non-blank Password values represent encrypted passwords.
MySQL does not store passwords in plaintext form for anyone to
see. Rather, the password supplied by a user who is attempting to
connect is encrypted (using the PASSWORD() function). The
encrypted password is then used when the client/server is checking if
the password is correct. (This is done without the encrypted password
ever traveling over the connection.) Note that from MySQL's
point of view the encrypted password is the REAL password, so you should
not give anyone access to it! In particular, don't give normal users
read access to the tables in the mysql database!
From version 4.1, MySQL employs a different password and login mechanism
that is secure even if TCP/IP packets are sniffed and/or the mysql database
is captured.
The examples here show how various combinations of Host and
User values in user table entries apply to incoming
connections:
Host value | User value | Connections matched by entry |
'thomas.loc.gov' | 'fred' | fred, connecting from thomas.loc.gov
|
'thomas.loc.gov' | '' | Any user, connecting from thomas.loc.gov
|
'%' | 'fred' | fred, connecting from any host
|
'%' | '' | Any user, connecting from any host |
'%.loc.gov' | 'fred' | fred, connecting from any host in the loc.gov domain
|
'x.y.%' | 'fred' | fred, connecting from x.y.net, x.y.com,x.y.edu, etc. (this is probably not useful)
|
'144.155.166.177' | 'fred' | fred, connecting from the host with IP address 144.155.166.177
|
'144.155.166.%' | 'fred' | fred, connecting from any host in the 144.155.166 class C subnet
|
'144.155.166.0/255.255.255.0' | 'fred' | Same as previous example |
Because you can use IP wildcard values in the Host field (for example,
'144.155.166.%' to match every host on a subnet), there is the
possibility that someone might try to exploit this capability by naming a
host 144.155.166.somewhere.com. To foil such attempts, MySQL
disallows matching on hostnames that start with digits and a dot. Thus, if
you have a host named something like 1.2.foo.com, its name will never
match the Host column of the grant tables. Only an IP number can
match an IP wildcard value.
An incoming connection may be matched by more than one entry in the
user table. For example, a connection from thomas.loc.gov by
fred would be matched by several of the entries just shown above. How
does the server choose which entry to use if more than one matches? The
server resolves this question by sorting the user table after reading
it at startup time, then looking through the entries in sorted order when a
user attempts to connect. The first matching entry is the one that is used.
user table sorting works as follows. Suppose the user table
looks like this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | % | root | ... | % | jeffrey | ... | localhost | root | ... | localhost | | ... +-----------+----------+-
When the server reads in the table, it orders the entries with the
most-specific Host values first ('%' in the Host column
means ``any host'' and is least specific). Entries with the same Host
value are ordered with the most-specific User values first (a blank
User value means ``any user'' and is least specific). The resulting
sorted user table looks like this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | localhost | root | ... | localhost | | ... | % | jeffrey | ... | % | root | ... +-----------+----------+-
When a connection is attempted, the server looks through the sorted entries
and uses the first match found. For a connection from localhost by
jeffrey, the entries with 'localhost' in the Host column
match first. Of those, the entry with the blank user name matches both the
connecting hostname and user name. (The '%'/'jeffrey' entry would
have matched, too, but it is not the first match in the table.)
Here is another example. Suppose the user table looks like this:
+----------------+----------+- | Host | User | ... +----------------+----------+- | % | jeffrey | ... | thomas.loc.gov | | ... +----------------+----------+-
The sorted table looks like this:
+----------------+----------+- | Host | User | ... +----------------+----------+- | thomas.loc.gov | | ... | % | jeffrey | ... +----------------+----------+-
A connection from thomas.loc.gov by jeffrey is matched by the
first entry, whereas a connection from whitehouse.gov by
jeffrey is matched by the second.
A common misconception is to think that for a given user name, all entries
that explicitly name that user will be used first when the server attempts to
find a match for the connection. This is simply not true. The previous
example illustrates this, where a connection from thomas.loc.gov by
jeffrey is first matched not by the entry containing 'jeffrey'
as the User field value, but by the entry with no user name!
If you have problems connecting to the server, print out the user
table and sort it by hand to see where the first match is being made.
If connection was successful, but your privileges are not what you
expected you may use CURRENT_USER() function (new in version
4.0.6) to see what user/host combination your connection actually
matched. See section 6.3.6.2 Miscellaneous Functions.
Once you establish a connection, the server enters Stage 2. For each request
that comes in on the connection, the server checks whether you have
sufficient privileges to perform it, based on the type of operation you wish
to perform. This is where the privilege fields in the grant tables come into
play. These privileges can come from any of the user, db,
host, tables_priv, or columns_priv tables. The grant
tables are manipulated with GRANT and REVOKE commands.
See section 4.3.1 GRANT and REVOKE Syntax. (You may find it helpful to refer to
section 4.2.6 How the Privilege System Works, which lists the fields present in each of the grant
tables.)
The user table grants privileges that are assigned to you on a global
basis and that apply no matter what the current database is. For example, if
the user table grants you the DELETE privilege, you can
delete rows from any database on the server host! In other words,
user table privileges are superuser privileges. It is wise to grant
privileges in the user table only to superusers such as server or
database administrators. For other users, you should leave the privileges
in the user table set to 'N' and grant privileges on a
database-specific basis only, using the db and host tables.
The db and host tables grant database-specific privileges.
Values in the scope fields may be specified as follows:
Host
and Db fields of either table. If you wish to use for instance a
`_' character as part of a database name, specify it as `\_' in
the GRANT command.
'%' Host value in the db table means ``any host.'' A
blank Host value in the db table means ``consult the
host table for further information.''
'%' or blank Host value in the host table means ``any
host.''
'%' or blank Db value in either table means ``any database.''
User value in either table matches the anonymous user.
The db and host tables are read in and sorted when the server
starts up (at the same time that it reads the user table). The
db table is sorted on the Host, Db, and User scope
fields, and the host table is sorted on the Host and Db
scope fields. As with the user table, sorting puts the most-specific
values first and least-specific values last, and when the server looks for
matching entries, it uses the first match that it finds.
The tables_priv and columns_priv tables grant table- and
column-specific privileges. Values in the scope fields may be specified as
follows:
Host field of either table.
'%' or blank Host value in either table means ``any host.''
Db, Table_name and Column_name fields cannot contain
wildcards or be blank in either table.
The tables_priv and columns_priv tables are sorted on
the Host, Db, and User fields. This is similar to
db table sorting, although the sorting is simpler because
only the Host field may contain wildcards.
The request verification process is described here. (If you are familiar with the access-checking source code, you will notice that the description here differs slightly from the algorithm used in the code. The description is equivalent to what the code actually does; it differs only to make the explanation simpler.)
For administrative requests (SHUTDOWN, RELOAD, etc.), the
server checks only the user table entry, because that is the only table
that specifies administrative privileges. Access is granted if the entry
allows the requested operation and denied otherwise. For example, if you
want to execute mysqladmin shutdown but your user table entry
doesn't grant the SHUTDOWN privilege to you, access is denied
without even checking the db or host tables. (They
contain no Shutdown_priv column, so there is no need to do so.)
For database-related requests (INSERT, UPDATE, etc.), the
server first checks the user's global (superuser) privileges by looking in
the user table entry. If the entry allows the requested operation,
access is granted. If the global privileges in the user table are
insufficient, the server determines the user's database-specific privileges
by checking the db and host tables:
db table for a match on the Host,
Db, and User fields. The Host and User fields are
matched to the connecting user's hostname and MySQL user name. The
Db field is matched to the database the user wants to access. If
there is no entry for the Host and User, access is denied.
db table entry and its Host field is
not blank, that entry defines the user's database-specific privileges.
db table entry's Host field is blank, it
signifies that the host table enumerates which hosts should be allowed
access to the database. In this case, a further lookup is done in the
host table to find a match on the Host and Db fields.
If no host table entry matches, access is denied. If there is a
match, the user's database-specific privileges are computed as the
intersection (not the union!) of the privileges in the db and
host table entries, that is, the privileges that are 'Y' in both
entries. (This way you can grant general privileges in the db table
entry and then selectively restrict them on a host-by-host basis using the
host table entries.)
After determining the database-specific privileges granted by the db
and host table entries, the server adds them to the global privileges
granted by the user table. If the result allows the requested
operation, access is granted. Otherwise, the server checks the user's
table and column privileges in the tables_priv and columns_priv
tables and adds those to the user's privileges. Access is allowed or denied
based on the result.
Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarised like this:
global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges
It may not be apparent why, if the global user entry privileges are
initially found to be insufficient for the requested operation, the server
adds those privileges to the database-, table-, and column-specific privileges
later. The reason is that a request might require more than one type of
privilege. For example, if you execute an INSERT ... SELECT
statement, you need both INSERT and SELECT privileges.
Your privileges might be such that the user table entry grants one
privilege and the db table entry grants the other. In this case, you
have the necessary privileges to perform the request, but the server cannot
tell that from either table by itself; the privileges granted by the entries
in both tables must be combined.
The host table can be used to maintain a list of secure servers.
At TcX, the host table contains a list of all machines on the local
network. These are granted all privileges.
You can also use the host table to indicate hosts that are not
secure. Suppose you have a machine public.your.domain that is located
in a public area that you do not consider secure. You can allow access to
all hosts on your network except that machine by using host table
entries
like this:
+--------------------+----+- | Host | Db | ... +--------------------+----+- | public.your.domain | % | ... (all privileges set to 'N') | %.your.domain | % | ... (all privileges set to 'Y') +--------------------+----+-
Naturally, you should always test your entries in the grant tables (for
example, using mysqlaccess) to make sure your access privileges are
actually set up the way you think they are.
MySQL user accounts are listed in the user table of the mysql
database. Each MySQL account is assigned a password, although
what is stored in the Password column of the user table is not the
plaintext version of the password, but a hash value computed from
it. Password hash values are computed by the PASSWORD() function.
MySQL uses passwords in two phases of client/server communication:
In other words, the server uses hash values during authentication when
a client first attempts to connect. The server generates hash values
if a connected client invokes the PASSWORD() function or uses a
GRANT
or SET PASSWORD statement to set or change a password.
The password hashing mechanism was updated in MySQL 4.1 to provide
better security and to reduce the risk of passwords being stolen.
However, this new mechanism is understood only by the 4.1 server and
4.1 clients, which can result in some compatibility problems.
A 4.1 client can connect to a pre-4.1 server, because the client
understands both the old and new password hashing mechanisms. However,
a pre-4.1 client that attempts to connect to a 4.1 server may run into
difficulties. For example, a 4.0 mysql client that attempts to connect
to a 4.1 server may fail with the following error message:
shell> mysql Client does not support authentication protocol requested by server; consider upgrading MySQL client
The following discussion describes the differences between the old and new password mechanisms, and what you should do if you upgrade your server to 4.1 but need to maintain backward compatibility with pre-4.1 clients.
Note: This discussion contrasts 4.1 behaviour with pre-4.1 behaviour, but the 4.1 behaviour described here actually begins with 4.1.1. MySQL 4.1.0 is an ``odd'' release because it has a slightly different mechanism than that implemented in 4.1.1 and up. Differences between 4.1.0 and more recent versions are described later.
Prior to MySQL 4.1, password hashes computed by the PASSWORD() function
are 16 bytes long. Such hashes look like this:
mysql> SELECT PASSWORD('mypass');
+--------------------+
| PASSWORD('mypass') |
+--------------------+
| 6f8c114b58f2ce9e |
+--------------------+
The Password column of the user table (in which these hashes are stored)
also is 16 bytes long before MySQL 4.1.
As of MySQL 4.1, the PASSWORD() function has been modified to produce
a longer 41-byte hash value:
mysql> SELECT PASSWORD('mypass');
+-----------------------------------------------+
| PASSWORD('mypass') |
+-----------------------------------------------+
| *43c8aa34cdc98eddd3de1fe9a9c2c2a9f92bb2098d75 |
+-----------------------------------------------+
Accordingly, the Password column in the user table also must be 41
bytes long to store these values:
Password column
will be made 41 bytes long automatically.
mysql_fix_privilege_tables script to update the length of the
Password
column from 16 to 41 bytes. (The script does not change existing
password values, which remain 16 bytes long.)
A widened Password column can store password hashes in both the old and
new formats. The format of any given password hash value can be
determined two ways:
The longer password hash format has better cryptographic properties, and client authentication based on long hashes is more secure than that based on the older short hashes.
The differences between short and long password hashes are relevant both for how the server uses passwords during authentication and for how it generates password hashes for connected clients that perform password-changing operations.
The way in which the server uses password hashes during authentication is affected by the width of the Password column:
For short-hash accounts, the authentication process is actually a bit more secure for 4.1 clients than for older clients. In terms of security, the gradient from least to most secure is:
The way in which the server generates password hashes for connected
clients is affected by the width of the Password column and by the
--old-passwords option. A 4.1 server generates long hashes only if certain
conditions are met:
The Password column must be wide enough to hold long
values and the --old-passwords option must not be given.
These conditions apply as follows:
Password column must be wide enough to hold long hashes (41 bytes).
If the column has not been updated and still has the pre-4.1 width
(16 bytes), the server notices that long hashes cannot fit into it and
generates only short hashes when a client performs password-changing
operations using PASSWORD(), GRANT, or SET PASSWORD.
(This behaviour
occurs if you have upgraded to 4.1 but have not run the
mysql_fix_privilege_tables script to widen the Password column.)
Password column is wide, it can store either short or long
password hashes. In this case, PASSWORD(), GRANT, and SET PASSWORD
will generate long hashes unless the server was started with the
--old-passwords option. This option forces the server to generate
short passsword hashes instead.
The purpose of the --old-passwords option is to allow you to maintain
backward compatibility with pre-4.1 clients under circumstances where
the server would otherwise generate long password hashes. It doesn't
affect authentication (4.1 clients can still use accounts that have
long password hashes), but it does prevent creation of a long
password hash in the user table as the result of a password-changing
operation. Were that to occur, the account no longer could be used
by pre-4.1 clients. Without the --old-passwords option, the following
scenario is possible:
--old-passwords,
this results in the account having a long password hash.
This scenario illustrates that it is dangerous to run a 4.1 server
without using the --old-passwords option if you must support older pre-4.1
clients. By running the server with --old-passwords, password-changing
operations will not generate long password hashes and thus do not cause
accounts to become inaccessible to older clients. (Those clients cannot
inadvertently lock themselves out by changing their password and ending
up with a long password hash.)
The downside of the --old-passwords option is that any passwords you
create or change will use short hashes, even for 4.1 clients. Thus, you
lose the additional security provided by long password hashes. If you want
to create an account that has a long hash (for example, for use by 4.1
clients), you must do so while running the server without
--old-passwords.
The following scenarios are possible for running a 4.1 server:
Scenario 1) Narrow Password column in user table
Password column.
PASSWORD(), GRANT, or SET PASSWORD use short hashes
exclusively. Any change to an account's password results in
that account having a short password hash.
--old-passwords option can be used but is superfluous because
with a narrow Password column, the server will be generating short
password hashes anyway.
Scenario 2) Long Password column; server not started with
--old-passwords option
Password column.
PASSWORD(), GRANT, or SET PASSWORD use long hashes
exclusively. Any change to an account's password results in that
account having a long password hash.
OLD_PASSWORD() may be used to explicitly generate a short hash. For
example, to assign an account a short password, use UPDATE as follows:
mysql> UPDATE user SET Password = OLD_PASSWORD('mypass')
-> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;
As indicated earlier, a danger in this scenario is that it is
possible for accounts that have a short password hash to become
inaccessible to pre-4.1 clients. Any change to such an account's
password made via GRANT, SET PASSWORD, or PASSWORD() results in
the account being given a long password hash, and from that point
on, no pre-4.1 client can authenticate to that account until the
client upgrades to 4.1.
Scenario 3) Long Password column; server started with
--old-passwords option
Password column.
--old-passwords).
PASSWORD(), GRANT, or SET PASSWORD use short hashes
exclusively. Any change to an account's password results in that
account having a short password hash.
In this scenario, you cannot create accounts that have long password
hashes, because --old-passwords prevents generation of long hashes. Also,
if you create an account with a long hash before using the
--old-passwords
option, changing the account's password while --old-passwords is in
effect results in the account being given a short password, causing it
to lose the security benefits of a longer hash.
The disadvantages for these scenarios may be summarized as follows:
Scenario 1) You cannot take advantage of longer hashes that provide more secure authentication.
Scenario 2) Accounts with short hashes become inaccessible to pre-4.1
clients if you change their passwords without explicitly using
OLD_PASSWORD().
Scenario 3) --old-passwords prevents accounts with short hashes from
becoming inaccessible, but password-changing operations cause accounts
with long hashes to revert to short hashes, and you cannot change them
back to long hashes while --old-passwords is in effect.
An upgrade to MySQL 4.1 can cause a compatibility issue for
applications that use PASSWORD() to generate passwords for their own
purposes. (Applications really should not do this, because PASSWORD()
should be used only to manage passwords for MySQL accounts. But some
applications use PASSWORD() for their own purposes anyway.) If you upgrade to
4.1 and run the server under conditions where it generates long password
hashes, an application that uses PASSWORD() for its own passwords will
break. The recommended course of action is to modify the application to
use another function such as SHA1() or MD5() to produce hashed values.
If that is not possible, you can use the OLD_PASSWORD() function, which
is provided to generate short hashes in the old format. (But note that
OLD_PASSWORD() may one day no longer be supported.)
If the server is running under circumstances where it generates short hashes,
OLD_PASSWORD() is available but is equivalent to PASSWORD().
Password hashing in MySQL 4.1.0 differs from hashing in 4.1.1 and up. The 4.1.0 differences are:
PASSWORD() function is non-repeatable. That is, with a given
argument X, successive calls to PASSWORD(X) generate different
results.
Access denied Errors
If you encounter Access denied errors when you try to connect to the
MySQL server, the following list indicates some courses of
action you can take to correct the problem:
mysql_install_db
script to set up the initial grant table contents? If not, do so.
See section 4.3.4 Setting Up the Initial MySQL Privileges. Test the initial privileges by executing
this command:
shell> mysql -u root testThe server should let you connect without error. You should also make sure you have a file `user.MYD' in the MySQL database directory. Ordinarily, this is `PATH/var/mysql/user.MYD', where
PATH is the
pathname to the MySQL installation root.
shell> mysql -u root mysqlThe server should let you connect because the MySQL
root user
has no password initially. That is also a security risk, so setting the
root password is something you should do while you're setting up
your other MySQL users.
If you try to connect as root and get this error:
Access denied for user: '@unknown' to database mysqlthis means that you don't have an entry in the
user table with a
User column value of 'root' and that mysqld cannot
resolve the hostname for your client. In this case, you must restart the
server with the --skip-grant-tables option and edit your
`/etc/hosts' or `\windows\hosts' file to add an entry for your
host.
shell> mysqladmin -u root -pxxxx ver Access denied for user: 'root@localhost' (Using password: YES)It means that you are using a wrong password. See section 4.3.7 Setting Up Passwords. If you have forgot the root password, you can restart
mysqld with
--skip-grant-tables to change the password.
See section A.4.2 How to Reset a Forgotten Root Password.
If you get the above error even if you haven't specified a password,
this means that you a wrong password in some my.ini
file. See section 4.1.2 `my.cnf' Option Files. You can avoid using option files with the --no-defaults option, as follows:
shell> mysqladmin --no-defaults -u root ver
mysql_fix_privilege_tables script? If not, do so. The structure of
the grant tables changed with MySQL Version 3.22.11 when the
GRANT statement became functional.
PASSWORD() function if you set the password with the
INSERT, UPDATE, or SET PASSWORD statements. The
PASSWORD() function is unnecessary if you specify the password using
the GRANT ... IDENTIFIED BY statement or the mysqladmin
password command.
See section 4.3.7 Setting Up Passwords.
localhost is a synonym for your local hostname, and is also the
default host to which clients try to connect if you specify no host
explicitly. However, connections to localhost do not work if you are
using a MySQL version prior to 3.23.27 that uses MIT-pthreads
(localhost connections are made using Unix sockets, which were not
supported by MIT-pthreads at that time). To avoid this problem on such
systems, you should use the --host option to name
the server host explicitly. This will make a TCP/IP connection to the
mysqld server. In this case, you must have your real hostname in
user table entries on the server host. (This is true even if you are
running a client program on the same host as the server.)
Access denied error when trying to connect to the
database with mysql -u user_name db_name, you may have a problem
with the user table. Check this by executing mysql -u root
mysql and issuing this SQL statement:
mysql> SELECT * FROM user;The result should include an entry with the
Host and User
columns matching your computer's hostname and your MySQL user name.
Access denied error message will tell you who you are trying
to log in as, the host from which you are trying to connect, and whether
or not you were using a password. Normally, you should have one entry in
the user table that exactly matches the hostname and user name
that were given in the error message. For example if you get an error
message that contains Using password: NO, this means that you
tried to login without an password.
user table that matches that host:
Host ... is not allowed to connect to this MySQL serverYou can fix this by using the command-line tool
mysql (on the
server host!) to add a row to the user, db, or host
table for the user/hostname combination from which you are trying to
connect and then execute mysqladmin flush-privileges. If you are
not running MySQL Version 3.22 and you don't know the IP number or
hostname of the machine from which you are connecting, you should put an
entry with '%' as the Host column value in the user
table and restart mysqld with the --log option on the
server machine. After trying to connect from the client machine, the
information in the MySQL log will indicate how you really did
connect. (Then replace the '%' in the user table entry
with the actual hostname that shows up in the log. Otherwise, you'll
have a system that is insecure.)
Another reason for this error on Linux is that you are using a binary
MySQL version that is compiled with a different glibc version
than the one you are using. In this case you should either upgrade your
OS/glibc or download the source MySQL version and compile this
yourself. A source RPM is normally trivial to compile and install, so
this isn't a big problem.
shell> mysqladmin -u root -pxxxx -h some-hostname ver Access denied for user: 'root@' (Using password: YES)This means that MySQL got some error when trying to resolve the IP to a hostname. In this case you can execute
mysqladmin
flush-hosts to reset the internal DNS cache. See section 5.5.5 How MySQL uses DNS.
Some permanent solutions are:
mysqld with --skip-name-resolve.
mysqld with --skip-host-cache.
localhost if you are running the server and the client
on the same machine.
/etc/hosts.
mysql -u root test works but mysql -h your_hostname -u root
test results in Access denied, then you may not have the correct name
for your host in the user table. A common problem here is that the
Host value in the user table entry specifies an unqualified hostname,
but your system's name resolution routines return a fully qualified domain
name (or vice-versa). For example, if you have an entry with host
'tcx' in the user table, but your DNS tells MySQL that
your hostname is 'tcx.subnet.se', the entry will not work. Try adding
an entry to the user table that contains the IP number of your host as
the Host column value. (Alternatively, you could add an entry to the
user table with a Host value that contains a wildcard--for
example, 'tcx.%'. However, use of hostnames ending with `%' is
insecure and is not recommended!)
mysql -u user_name test works but mysql -u user_name
other_db_name doesn't work, you don't have an entry for other_db_name
listed in the db table.
mysql -u user_name db_name works when executed on the server
machine, but mysql -h host_name -u user_name db_name doesn't work when
executed on another client machine, you don't have the client machine listed
in the user table or the db table.
Access denied, remove from the
user table all entries that have Host values containing
wildcards (entries that contain `%' or `_'). A very common error
is to insert a new entry with Host='%' and
User='some user', thinking that this will allow you to specify
localhost to connect from the same machine. The reason that this
doesn't work is that the default privileges include an entry with
Host='localhost' and User=''. Because that entry
has a Host value 'localhost' that is more specific than
'%', it is used in preference to the new entry when connecting from
localhost! The correct procedure is to insert a second entry with
Host='localhost' and User='some_user', or to
remove the entry with Host='localhost' and
User=''.
db or
host table:
Access to database deniedIf the entry selected from the
db table has an empty value in the
Host column, make sure there are one or more corresponding entries in
the host table specifying which hosts the db table entry
applies to.
If you get the error when using the SQL commands SELECT ...
INTO OUTFILE or LOAD DATA INFILE, your entry in the user table
probably doesn't have the FILE privilege enabled.
Access denied when you run a client without any options, make
sure you haven't specified an old password in any of your option files!
See section 4.1.2 `my.cnf' Option Files.
INSERT or
UPDATE statement) and your changes seem to be ignored, remember
that you must issue a FLUSH PRIVILEGES statement or execute a
mysqladmin flush-privileges command to cause the server to re-read
the privilege tables. Otherwise, your changes have no effect until the
next time the server is restarted. Remember that after you set the
root password with an UPDATE command, you won't need to
specify it until after you flush the privileges, because the server
won't know you've changed the password yet!
mysql -u user_name db_name or mysql
-u user_name -pyour_pass db_name. If you are able to connect using the
mysql client, there is a problem with your program and not with the
access privileges. (Note that there is no space between -p and the
password; you can also use the --password=your_pass syntax to specify
the password. If you use the -p option alone, MySQL will
prompt you for the password.)
mysqld daemon with the
--skip-grant-tables option. Then you can change the MySQL
grant tables and use the mysqlaccess script to check whether
your modifications have the desired effect. When you are satisfied with your
changes, execute mysqladmin flush-privileges to tell the mysqld
server to start using the new grant tables. Note: reloading the
grant tables overrides the --skip-grant-tables option. This allows
you to tell the server to begin using the grant tables again without bringing
it down and restarting it.
mysqld daemon with a debugging
option (for example, --debug=d,general,query). This will print host and
user information about attempted connections, as well as information about
each command issued. See section E.1.2 Creating Trace Files.
mysqldump mysql command. As always, post your problem using
the mysqlbug script. See section 1.7.1.3 How to Report Bugs or Problems. In some cases you may need
to restart mysqld with --skip-grant-tables to run
mysqldump.
GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
MAX_UPDATES_PER_HOUR # |
MAX_CONNECTIONS_PER_HOUR #]]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]
GRANT is implemented in MySQL Version 3.22.11 or later. For
earlier MySQL versions, the GRANT statement does nothing.
The GRANT and REVOKE commands allow system administrators
to create users and grant and revoke rights to MySQL users at
four privilege levels:
mysql.user table.
REVOKE ALL ON *.* will revoke only global privileges.
mysql.db and mysql.host tables.
REVOKE ALL ON db.* will revoke only database privileges.
mysql.tables_priv table.
REVOKE ALL ON db.table will revoke only table privileges.
mysql.columns_priv table.
When using REVOKE you must specify the same columns that were granted.
For the GRANT and REVOKE statements, priv_type may be
specified as any of the following:
ALL [PRIVILEGES] | Sets all simple privileges except WITH GRANT OPTION
|
ALTER | Allows usage of ALTER TABLE
|
CREATE | Allows usage of CREATE TABLE
|
CREATE TEMPORARY TABLES | Allows usage of CREATE TEMPORARY TABLE
|
DELETE | Allows usage of DELETE
|
DROP | Allows usage of DROP TABLE.
|
EXECUTE | Allows the user to run stored procedures (MySQL 5.0) |
FILE | Allows usage of SELECT ... INTO OUTFILE and LOAD DATA INFILE.
|
INDEX | Allows usage of CREATE INDEX and DROP INDEX
|
INSERT | Allows usage of INSERT
|
LOCK TABLES | Allows usage of LOCK TABLES on tables for which one has the SELECT privilege.
|
PROCESS | Allows usage of SHOW FULL PROCESSLIST
|
REFERENCES | For the future |
RELOAD | Allows usage of FLUSH
|
REPLICATION CLIENT | Gives the right to the user to ask where the slaves/masters are. |
REPLICATION SLAVE | Needed for the replication slaves (to read binlogs from master). |
SELECT | Allows usage of SELECT
|
SHOW DATABASES | SHOW DATABASES shows all databases.
|
SHUTDOWN | Allows usage of mysqladmin shutdown
|
SUPER | Allows one connect (once) even if
max_connections is reached and execute commands CHANGE MASTER,
KILL thread, mysqladmin debug, PURGE [MASTER] LOGS and SET GLOBAL
|
UPDATE | Allows usage of UPDATE
|
USAGE | Synonym for ``no privileges.'' |
GRANT OPTION | Synonym for WITH GRANT OPTION
|
USAGE can be used when you want to create a user that has no privileges.
The privileges CREATE TEMPORARY TABLES, EXECUTE,
LOCK TABLES, REPLICATION ..., SHOW DATABASES and
SUPER are new for in version 4.0.2. To use these new privileges
after upgrading to 4.0.2, you have to run the
mysql_fix_privilege_tables script.
In older MySQL versions, the PROCESS privilege gives the same
rights as the new SUPER privilege.
To revoke the GRANT privilege from a user, use a priv_type
value of GRANT OPTION:
mysql> REVOKE GRANT OPTION ON ... FROM ...;
The only priv_type values you can specify for a table are SELECT,
INSERT, UPDATE, DELETE, CREATE, DROP,
GRANT OPTION, INDEX, and ALTER.
The only priv_type values you can specify for a column (that is, when
you use a column_list clause) are SELECT, INSERT, and
UPDATE.
MySQL allows you to create database level privileges even if the database doesn't exist, to make it easy to prepare for database usage. Currently MySQL does however not allow one to create table level grants if the table doesn't exist. MySQL will not automatically revoke any privileges even if you drop a table or drop a database.
You can set global privileges by using ON *.* syntax. You can set
database privileges by using ON db_name.* syntax. If you specify
ON * and you have a current database, you will set the privileges for
that database. (Warning: if you specify ON * and you
don't have a current database, you will affect the global privileges!)
Please note: the `_' and `%' wildcards are allowed when
specifying database names in GRANT commands. This means that if you
wish to use for instance a `_' character as part of a database name,
you should specify it as `\_' in the GRANT command, to prevent
the user from being able to access additional databases matching the
wildcard pattern, for example, GRANT ... ON `foo\_bar`.* TO ....
In order to accommodate granting rights to users from arbitrary hosts,
MySQL supports specifying the user_name value in the form
user@host. If you want to specify a user string
containing special characters (such as `-'), or a host string
containing special characters or wildcard characters (such as `%'), you
can quote the user or host name (for example, 'test-user'@'test-hostname').
You can specify wildcards in the hostname. For example,
user@'%.loc.gov' applies to user for any host in the
loc.gov domain, and user@'144.155.166.%' applies to user
for any host in the 144.155.166 class C subnet.
The simple form user is a synonym for user@"%".
MySQL doesn't support wildcards in user names. Anonymous users are
defined by inserting entries with User='' into the
mysql.user table or creating an user with an empty name with the
GRANT command.
Note: if you allow anonymous users to connect to the MySQL
server, you should also grant privileges to all local users as
user@localhost because otherwise the anonymous user entry for
the local host in the mysql.user table will be used when the user
tries to log into the MySQL server from the local machine!
You can verify if this applies to you by executing this query:
mysql> SELECT Host,User FROM mysql.user WHERE User='';
For the moment, GRANT only supports host, table, database, and
column names up to 60 characters long. A user name can be up to 16
characters.
The privileges for a table or column are formed from the
logical OR of the privileges at each of the four privilege
levels. For example, if the mysql.user table specifies that a
user has a global SELECT privilege, this can't be denied by an
entry at the database, table, or column level.
The privileges for a column can be calculated as follows:
global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges
In most cases, you grant rights to a user at only one of the privilege levels, so life isn't normally as complicated as above. The details of the privilege-checking procedure are presented in section 4.2 General Security Issues and the MySQL Access Privilege System.
If you grant privileges for a user/hostname combination that does not exist
in the mysql.user table, an entry is added and remains there until
deleted with a DELETE command. In other words, GRANT may
create user table entries, but REVOKE will not remove them;
you must do that explicitly using DELETE.
In MySQL Version 3.22.12 or later,
if a new user is created or if you have global grant privileges, the user's
password will be set to the password specified by the IDENTIFIED BY
clause, if one is given. If the user already had a password, it is replaced
by the new one.
If you don't want to send the password in clear text you can use the
PASSWORD option followed by a scrambled password from SQL
function PASSWORD() or the C API function
make_scrambled_password(char *to, const char *password).
Warning: if you create a new user but do not specify an
IDENTIFIED BY clause, the user has no password. This is insecure.
Passwords can also be set with the SET PASSWORD command.
See section 5.5.6 SET Syntax.
If you grant privileges for a database, an entry in the mysql.db
table is created if needed. When all privileges for the database have been
removed with REVOKE, this entry is deleted.
If a user doesn't have any privileges on a table, the table is not displayed
when the user requests a list of tables (for example, with a SHOW TABLES
statement). The same is true for SHOW DATABASES.
The WITH GRANT OPTION clause gives the user the ability to give
to other users any privileges the user has at the specified privilege level.
You should be careful to whom you give the GRANT privilege, as two
users with different privileges may be able to join privileges!
MAX_QUERIES_PER_HOUR #, MAX_UPDATES_PER_HOUR # and
MAX_CONNECTIONS_PER_HOUR # are new in MySQL version 4.0.2.
These options limit the number of queries/updates and logins the user can
do during one hour. If # is 0 (default), then this means that there
are no limitations for that user. See section 4.3.6 Limiting user resources.
Note: to specify any of these options for an existing user without adding
other additional privileges, use GRANT USAGE ON *.* ... WITH MAX_....
You cannot grant another user a privilege you don't have yourself;
the GRANT privilege allows you to give away only those privileges
you possess.
Be aware that when you grant a user the GRANT privilege at a
particular privilege level, any privileges the user already possesses (or
is given in the future!) at that level are also grantable by that user.
Suppose you grant a user the INSERT privilege on a database. If
you then grant the SELECT privilege on the database and specify
WITH GRANT OPTION, the user can give away not only the SELECT
privilege, but also INSERT. If you then grant the UPDATE
privilege to the user on the database, the user can give away the
INSERT, SELECT and UPDATE.
You should not grant ALTER privileges to a normal user. If you
do that, the user can try to subvert the privilege system by renaming
tables!
Note that if you are using table or column privileges for even one user, the server examines table and column privileges for all users and this will slow down MySQL a bit.
When mysqld starts, all privileges are read into memory.
Database, table, and column privileges take effect at once, and
user-level privileges take effect the next time the user connects.
Modifications to the grant tables that you perform using GRANT or
REVOKE are noticed by the server immediately.
If you modify the grant tables manually (using INSERT, UPDATE,
etc.), you should execute a FLUSH PRIVILEGES statement or run
mysqladmin flush-privileges to tell the server to reload the grant
tables.
See section 4.3.3 When Privilege Changes Take Effect.
The biggest differences between the SQL standard and MySQL versions of
GRANT are:
TRIGGER or UNDER
privileges.
INSERT privilege on only some of the
columns in a table, you can execute INSERT statements on the
table; the columns for which you don't have the INSERT privilege
will be set to their default values. SQL-99 requires you to have the
INSERT privilege on all columns.
REVOKE commands or by manipulating the
MySQL grant tables.
For a description of using REQUIRE, see section 4.3.9 Using Secure Connections.
There are several distinctions between the way user names and passwords are used by MySQL and the way they are used by Unix or Windows:
-u or
--user options. This means that you can't make a database secure in
any way unless all MySQL user names have passwords. Anyone may
attempt to connect to the server using any name, and they will succeed if
they specify any name that doesn't have a password.
PASSWORD() and ENCRYPT() functions in section 6.3.6.2 Miscellaneous Functions. Note that even if the password is stored 'scrambled', and
knowing your 'scrambled' password is enough to be able to connect to
the MySQL server!
From version 4.1, MySQL employs a different password and login mechanism
that is secure even if TCP/IP packets are sniffed and/or the mysql
database is captured.
MySQL users and their privileges are normally created with the
GRANT command. See section 4.3.1 GRANT and REVOKE Syntax.
When you login to a MySQL server with a command-line client you
should specify the password with --password=your-password.
See section 4.2.8 Connecting to the MySQL Server.
mysql --user=monty --password=guess database_name
If you want the client to prompt for a password, you should use
--password without any argument
mysql --user=monty --password database_name
or the short form:
mysql -u monty -p database_name
Note that in the last example the password is not 'database_name'.
If you want to use the -p option to supply a password you should do so
like this:
mysql -u monty -pguess database_name
On some systems, the library call that MySQL uses to prompt for a password will automatically cut the password to 8 characters. Internally MySQL doesn't have any limit for the length of the password.
When mysqld starts, all grant table contents are read into memory and
become effective at that point.
Modifications to the grant tables that you perform using GRANT,
REVOKE, or SET PASSWORD are noticed by the server immediately.
If you modify the grant tables manually (using INSERT, UPDATE,
etc.), you should execute a FLUSH PRIVILEGES statement or run
mysqladmin flush-privileges or mysqladmin reload to tell the
server to reload the grant tables. Otherwise, your changes will have no
effect until you restart the server. If you change the grant tables manually
but forget to reload the privileges, you will be wondering why your changes
don't seem to make any difference!
When the server notices that the grant tables have been changed, existing client connections are affected as follows:
USE db_name
command.
After installing MySQL, you set up the initial access privileges by
running scripts/mysql_install_db.
See section 2.3.1 Quick Installation Overview.
The mysql_install_db script starts up the mysqld
server, then initialises the grant tables to contain the following set
of privileges:
root user is created as a superuser who can do
anything. Connections must be made from the local host.
Note:
The initial root password is empty, so anyone can connect as root
without a password and be granted all privileges.
'test' or starting with 'test_'. Connections must be
made from the local host. This means any local user can connect without a
password and be treated as the anonymous user.
mysqladmin shutdown or mysqladmin processlist.
Note: the default privileges are different for Windows. See section 2.6.2.3 Running MySQL on Windows.
Because your installation is initially wide open, one of the first things you
should do is specify a password for the MySQL
root user. You can do this as follows (note that you specify the
password using the PASSWORD() function):
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
If you know what you are doing, you can also directly manipulate the privilege tables:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
-> WHERE user='root';
mysql> FLUSH PRIVILEGES;
Another way to set the password is by using the mysqladmin command:
shell> mysqladmin -u root password new_password
Only users with write/update access to the mysql database can change the
password for others users. All normal users (not anonymous ones) can only
change their own password with either of the above commands or with
SET PASSWORD=PASSWORD('new password').
Note that if you update the password in the user table directly using
the first method, you must tell the server to re-read the grant tables (with
FLUSH PRIVILEGES), because the change will go unnoticed otherwise.
Once the root password has been set, thereafter you must supply that
password when you connect to the server as root.
You may wish to leave the root password blank so that you don't need
to specify it while you perform additional setup or testing. However, be sure
to set it before using your installation for any real production work.
See the scripts/mysql_install_db script to see how it sets up
the default privileges. You can use this as a basis to see how to
add other users.
If you want the initial privileges to be different from those just described
above, you can modify mysql_install_db before you run it.
To re-create the grant tables completely, remove all the `.frm',
`.MYI', and `.MYD' files in the directory containing the
mysql database. (This is the directory named `mysql' under
the database directory, which is listed when you run mysqld
--help.) Then run the mysql_install_db script, possibly after
editing it first to have the privileges you want.
Note: for MySQL versions older than Version 3.22.10,
you should not delete the `.frm' files. If you accidentally do this,
you should copy them back from your MySQL distribution before
running mysql_install_db.
You can add users two different ways: by using GRANT statements
or by manipulating the MySQL grant tables directly. The
preferred method is to use GRANT statements, because they are
more concise and less error-prone. See section 4.3.1 GRANT and REVOKE Syntax.
There are also a lot of contributed programs like phpmyadmin
that can be used to create and administrate users.
The following examples show how to use the mysql client to set up new
users. These examples assume that privileges are set up according to the
defaults described in the previous section. This means that to make changes,
you must be on the same machine where mysqld is running, you must
connect as the MySQL root user, and the root user must
have the INSERT privilege for the mysql database and the
RELOAD administrative privilege. Also, if you have changed the
root user password, you must specify it for the mysql commands here.
You can add new users by issuing GRANT statements:
shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;
These GRANT statements set up three new users:
monty
'some_pass' to do so. Note that we must issue
GRANT statements for both monty@localhost and
monty@"%". If we don't add the entry with localhost, the
anonymous user entry for localhost that is created by
mysql_install_db will take precedence when we connect from the local
host, because it has a more specific Host field value and thus comes
earlier in the user table sort order.
admin
localhost without a password and who is
granted the RELOAD and PROCESS administrative privileges.
This allows the user to execute the mysqladmin reload,
mysqladmin refresh, and mysqladmin flush-* commands, as well as
mysqladmin processlist . No database-related privileges are granted.
(They can be granted later by issuing additional GRANT statements.)
dummy
'N'---the USAGE privilege
type allows you to create a user with no privileges. It is assumed that you
will grant database-specific privileges later.
You can also add the same user access information directly by issuing
INSERT statements and then telling the server to reload the grant
tables:
shell> mysql --user=root mysql
mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
-> Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;
Depending on your MySQL version, you may have to use a different
number of 'Y' values above (versions prior to Version 3.22.11 had fewer
privilege columns). For the admin user, the more readable extended
INSERT syntax that is available starting with Version 3.22.11 is used.
Note that to set up a superuser, you need only create a user table
entry with the privilege fields set to 'Y'. No db or
host table entries are necessary.
The privilege columns in the user table were not set explicitly in the
last INSERT statement (for the dummy user), so those columns
are assigned the default value of 'N'. This is the same thing that
GRANT USAGE does.
The following example adds a user custom who can connect from hosts
localhost, server.domain, and whitehouse.gov. He wants
to access the bankaccount database only from localhost,
the expenses database only from whitehouse.gov, and
the customer database from all three hosts. He wants
to use the password stupid from all three hosts.
To set up this user's privileges using GRANT statements, run these
commands:
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON bankaccount.*
-> TO custom@localhost
-> IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON expenses.*
-> TO custom@whitehouse.gov
-> IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON customer.*
-> TO custom@'%'
-> IDENTIFIED BY 'stupid';
The reason that we issue two grant statements for the user 'custom' is that we want the give the user access to MySQL both from the local machine with Unix sockets and from the remote machine 'whitehouse.gov' over TCP/IP.
To set up the user's privileges by modifying the grant tables directly,
run these commands (note the FLUSH PRIVILEGES at the end):
shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('server.domain','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
-> Create_priv,Drop_priv)
-> VALUES
-> ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
-> Create_priv,Drop_priv)
-> VALUES
-> ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
-> Create_priv,Drop_priv)
-> VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
The first three INSERT statements add user table entries that
allow user custom to connect from the various hosts with the given
password, but grant no permissions to him (all privileges are set to the
default value of 'N'). The next three INSERT statements add
db table entries that grant privileges to custom for the
bankaccount, expenses, and customer databases, but only
when accessed from the proper hosts. As usual, when the grant tables are
modified directly, the server must be told to reload them (with
FLUSH PRIVILEGES) so that the privilege changes take effect.
If you want to give a specific user access from any machine in a given
domain, you can issue a GRANT statement like the following:
mysql> GRANT ...
-> ON *.*
-> TO myusername@"%.mydomainname.com"
-> IDENTIFIED BY 'mypassword';
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
-> PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;
Starting from MySQL 4.0.2 one can limit certain resources per user.
So far, the only available method of limiting usage of MySQL
server resources has been setting the max_user_connections
startup variable to a non-zero value. But this method is strictly
global and does not allow for management of individual users, which
could be of particular interest to Internet Service Providers.
Therefore, management of three resources is introduced on the individual user level:
A user in the aforementioned context is a single entry in the
user table, which is uniquely identified by its user
and host columns.
All users are by default not limited in using the above resources,
unless the limits are granted to them. These limits can be granted
only via global GRANT (*.*), using this syntax:
GRANT ... WITH MAX_QUERIES_PER_HOUR N1
MAX_UPDATES_PER_HOUR N2
MAX_CONNECTIONS_PER_HOUR N3;
One can specify any combination of the above resources.
N1, N2, and N3 are integers and represent counts per hour.
If a user reaches the limit on number of connections within one hour, no further connections will be accepted until that hour is up. Similarly, if the user reaches the limit on number of queries or updates, further queries or updates will be rejected until the hour is up. In all cases, an appropriate error message shall be issued.
Current usage values for a particular user can be flushed (set to zero)
by issuing a GRANT statement with any of the above clauses,
including a GRANT statement with the current values.
Also, current values for all users will be flushed if privileges are
reloaded (in the server or using mysqladmin reload)
or if the FLUSH USER_RESOURCES command is issued.
The feature is enabled as soon as a single user is granted with any
of the limiting GRANT clauses.
As a prerequisite for enabling this feature, the user table in
the mysql database must contain the additional columns, as
defined in the table creation scripts mysql_install_db and
mysql_install_db.sh in `scripts' subdirectory.
In most cases you should use GRANT to set up your users/passwords,
so the following only applies for advanced users. See section 4.3.1 GRANT and REVOKE Syntax.
The examples in the preceding sections illustrate an important principle:
when you store a non-empty password using INSERT or UPDATE
statements, you must use the PASSWORD() function to encrypt it. This
is because the user table stores passwords in encrypted form, not as
plaintext. If you forget that fact, you are likely to attempt to set
passwords like this:
shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;
The result is that the plaintext value 'biscuit' is stored as the
password in the user table. When the user jeffrey attempts to
connect to the server using this password, the mysql client encrypts
it with PASSWORD(), generates an authentification vector
based on encrypted password and a random number,
obtained from server, and sends the result to the server.
The server uses the password value in the user table
(that is not encrypted value 'biscuit')
to perform the same calculations, and compares results.
The comparison fails and the server rejects the
connection:
shell> mysql -u jeffrey -pbiscuit test Access denied
Passwords must be encrypted when they are inserted in the user
table, so the INSERT statement should have been specified like this
instead:
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','jeffrey',PASSWORD('biscuit'));
You must also use the PASSWORD() function when you use SET
PASSWORD statements:
mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');
If you set passwords using the GRANT ... IDENTIFIED BY statement
or the mysqladmin password command, the PASSWORD() function
is unnecessary. They both take care of encrypting the password for you,
so you would specify a password of 'biscuit' like this:
mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';
or
shell> mysqladmin -u jeffrey password biscuit
Note: PASSWORD() is different from Unix password encryption.
See section 4.3.2 MySQL User Names and Passwords.
It is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed here, along with an assessment of the risks of each method:
mysql.user table. Knowing
the encrypted password for a user makes it possible to login as this
user. The passwords are only scrambled so that one shouldn't be able to
see the real password you used (if you happen to use a similar password
with your other applications).
-pyour_pass or --password=your_pass option on the command
line. This is convenient but insecure, because your password becomes visible
to system status programs (such as ps) that may be invoked by other
users to display command-lines. (MySQL clients typically overwrite
the command-line argument with zeroes during their initialisation sequence,
but there is still a brief interval during which the value is visible.)
-p or --password option (with no your_pass value
specified). In this case, the client program solicits the password from
the terminal:
shell> mysql -u user_name -p Enter password: ********The `*' characters represent your password. It is more secure to enter your password this way than to specify it on the command-line because it is not visible to other users. However, this method of entering a password is suitable only for programs that you run interactively. If you want to invoke a client from a script that runs non-interactively, there is no opportunity to enter the password from the terminal. On some systems, you may even find that the first line of your script is read and interpreted (incorrectly) as your password!
[client] section of the `.my.cnf' file in your
home directory:
[client] password=your_passIf you store your password in `.my.cnf', the file should not be group or world readable or writable. Make sure the file's access mode is
400
or 600.
See section 4.1.2 `my.cnf' Option Files.
MYSQL_PWD environment variable, but
this method must be considered extremely insecure and should not be used.
Some versions of ps include an option to display the environment of
running processes; your password will be in plain sight for all to see if
you set MYSQL_PWD. Even on systems without such a version of
ps, it is unwise to assume there is no other method to observe process
environments. See section F Environment Variables.
All in all, the safest methods are to have the client program prompt for the password or to specify the password in a properly protected `.my.cnf' file.
Beginning with version 4.0.0, MySQL has support for SSL encrypted connections. To understand how MySQL uses SSL, it's necessary to explain some basic SSL and X509 concepts. People who are already familiar with them can skip this part.
By default, MySQL uses unencrypted connections between the client and the server. This means that someone could watch all your traffic and look at the data being sent or received. They could even change the data while it is in transit between client and server. Sometimes you need to move information over public networks in a secure fashion; in such cases, using an unencrypted connection is unacceptable.
SSL is a protocol that uses different encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect any change, loss or replay of data. SSL also incorporates algorithms to recognise and provide identity verification using the X509 standard.
Encryption is the way to make any kind of data unreadable. In fact, today's practice requires many additional security elements from encryption algorithms. They should resist many kind of known attacks like just messing with the order of encrypted messages or replaying data twice.
X509 is a standard that makes it possible to identify someone on the Internet. It is most commonly used in e-commerce applications. In basic terms, there should be some company (called a ``Certificate Authority'') that assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys (a public key and a secret key). A certificate owner can prove his identity by showing his certificate to other party. A certificate consists of its owner's public key. Any data encrypted with this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate.
MySQL doesn't use encrypted connections by default, because doing so would make the client/server protocol much slower. Any kind of additional functionality requires the computer to do additional work and encrypting data is a CPU-intensive operation that requires time and can delay MySQL main tasks. By default MySQL is tuned to be fast as possible.
If you need more information about SSL, X509, or encryption, you should use your favourite Internet search engine and search for keywords in which you are interested.
To get secure connections to work with MySQL you must do the following:
--with-vio --with-openssl.
mysql.user table with some new SSL-related columns. You can do this by
running the mysql_fix_privilege_tables.sh script.
This is necessary if your grant tables date from a version prior to MySQL
4.0.0.
mysqld server supports OpenSSL by
examining if SHOW VARIABLES LIKE 'have_openssl' returns YES.
Here is an example for setting up SSL certificates for MySQL:
DIR=`pwd`/openssl
PRIV=$DIR/private
mkdir $DIR $PRIV $DIR/newcerts
cp /usr/share/ssl/openssl.cnf $DIR
replace ./demoCA $DIR -- $DIR/openssl.cnf
# Create necessary files: $database, $serial and $new_certs_dir
# directory (optional)
touch $DIR/index.txt
echo "01" > $DIR/serial
#
# Generation of Certificate Authority(CA)
#
openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem \
-config $DIR/openssl.cnf
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ................++++++
# .........++++++
# writing new private key to '/home/monty/openssl/private/cakey.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be incorporated
# into your certificate request.
# What you are about to enter is what is called a Distinguished Name or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL admin
# Email Address []:
#
# Create server request and key
#
openssl req -new -keyout $DIR/server-key.pem -out \
$DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ..++++++
# ..........++++++
# writing new private key to '/home/monty/openssl/server-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be incorporated
# into your certificate request.
# What you are about to enter is what is called a Distinguished Name or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL server
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:
#
# Remove the passphrase from the key (optional)
#
openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem
#
# Sign server cert
#
openssl ca -policy policy_anything -out $DIR/server-cert.pem \
-config $DIR/openssl.cnf -infiles $DIR/server-req.pem
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName :PRINTABLE:'FI'
# organizationName :PRINTABLE:'MySQL AB'
# commonName :PRINTABLE:'MySQL admin'
# Certificate is to be certified until Sep 13 14:22:46 2003 GMT (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated
#
# Create client request and key
#
openssl req -new -keyout $DIR/client-key.pem -out \
$DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# .....................................++++++
# .............................................++++++
# writing new private key to '/home/monty/openssl/client-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be incorporated
# into your certificate request.
# What you are about to enter is what is called a Distinguished Name or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL user
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:
#
# Remove a passphrase from the key (optional)
#
openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem
#
# Sign client cert
#
openssl ca -policy policy_anything -out $DIR/client-cert.pem \
-config $DIR/openssl.cnf -infiles $DIR/client-req.pem
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName :PRINTABLE:'FI'
# organizationName :PRINTABLE:'MySQL AB'
# commonName :PRINTABLE:'MySQL user'
# Certificate is to be certified until Sep 13 16:45:17 2003 GMT (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated
#
# Create a my.cnf file that you can use to test the certificates
#
cnf=""
cnf="$cnf [client]"
cnf="$cnf ssl-ca=$DIR/cacert.pem"
cnf="$cnf ssl-cert=$DIR/client-cert.pem"
cnf="$cnf ssl-key=$DIR/client-key.pem"
cnf="$cnf [mysqld]"
cnf="$cnf ssl-ca=$DIR/cacert.pem"
cnf="$cnf ssl-cert=$DIR/server-cert.pem"
cnf="$cnf ssl-key=$DIR/server-key.pem"
echo $cnf | replace " " '
' > $DIR/my.cnf
#
# To test MySQL
mysqld --defaults-file=$DIR/my.cnf &
mysql --defaults-file=$DIR/my.cnf
You can also test your setup by modifying the above `my.cnf' file to refer to the demo certificates in the mysql-source-dist/SSL direcory.
GRANT OptionsMySQL can check X509 certificate attributes in addition to the normal username/password scheme. All the usual options are still required (username, password, IP address mask, database/table name).
There are different possibilities to limit connections:
REQUIRE SSL option limits the server to allow only SSL
encrypted connections. Note that this option can be omitted
if there are any ACL records which allow non-SSL connections.
mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
-> IDENTIFIED BY "goodsecret" REQUIRE SSL;
REQUIRE X509 means that the client should have a valid certificate
but we do not care about the exact certificate, issuer or subject.
The only restriction is that it should be possible to verify its
signature with one of the CA certificates.
mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
-> IDENTIFIED BY "goodsecret" REQUIRE X509;
REQUIRE ISSUER "issuer" places a restriction on connection attempts:
The client must present a valid X509 certificate issued by CA "issuer".
Using X509 certificates always implies encryption, so the SSL option
is unneccessary.
mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
-> IDENTIFIED BY "goodsecret"
-> REQUIRE ISSUER "C=FI, ST=Some-State, L=Helsinki,
"> O=MySQL Finland AB, CN=Tonu Samuel/Email=tonu@mysql.com";
REQUIRE SUBJECT "subject" requires clients to have valid X509
certificate with subject "subject" on it. If the client presents a
certificate that is valid but has a different "subject", the connection
is disallowed.
mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
-> IDENTIFIED BY "goodsecret"
-> REQUIRE SUBJECT "C=EE, ST=Some-State, L=Tallinn,
"> O=MySQL demo client certificate,
"> CN=Tonu Samuel/Email=tonu@mysql.com";
REQUIRE CIPHER "cipher" is needed to assure enough strong ciphers
and keylengths will be used. SSL itself can be weak if old algorithms
with short encryption keys are used. Using this option, we can ask for
some exact cipher method to allow a connection.
mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
-> IDENTIFIED BY "goodsecret"
-> REQUIRE CIPHER "EDH-RSA-DES-CBC3-SHA";
The SUBJECT, ISSUER, and CIPHER options can be
combined in the REQUIRE clause like this:
mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
-> IDENTIFIED BY "goodsecret"
-> REQUIRE SUBJECT "C=EE, ST=Some-State, L=Tallinn,
"> O=MySQL demo client certificate,
"> CN=Tonu Samuel/Email=tonu@mysql.com"
-> AND ISSUER "C=FI, ST=Some-State, L=Helsinki,
"> O=MySQL Finland AB, CN=Tonu Samuel/Email=tonu@mysql.com"
-> AND CIPHER "EDH-RSA-DES-CBC3-SHA";
Starting from MySQL 4.0.4 the AND keyword is optional between
REQUIRE options.
The order of the options does not matter, but no option can be specified
twice.
Because MySQL tables are stored as files, it is easy to do a
backup. To get a consistent backup, do a LOCK TABLES on the
relevant tables followed by FLUSH TABLES for the tables.
See section 6.7.2 LOCK TABLES/UNLOCK TABLES Syntax.
See section 4.5.3 FLUSH Syntax.
You only need a read lock; this allows other threads to continue to
query the tables while you are making a copy of the files in the
database directory. The FLUSH TABLE is needed to ensure that
the all active index pages is written to disk before you start the backup.
Starting from 3.23.56 and 4.0.12 BACKUP TABLE will not allow you
to overwrite existing files as this would be a security risk.
If you want to make an SQL level backup of a table, you can use
SELECT INTO OUTFILE or BACKUP TABLE. See section 6.4.1 SELECT Syntax.
See section 4.4.2 BACKUP TABLE Syntax.
Another way to back up a database is to use the mysqldump program or
the mysqlhotcopy script. See section 4.8.5 mysqldump, Dumping Table Structure and Data.
See section 4.8.6 mysqlhotcopy, Copying MySQL Databases and Tables.
shell> mysqldump --tab=/path/to/some/dir --opt --all or shell> mysqlhotcopy database /path/to/some/dirYou can also simply copy all table files (`*.frm', `*.MYD', and `*.MYI' files) as long as the server isn't updating anything. The script
mysqlhotcopy does use this method.
mysqld if it's running, then start it with the
--log-bin[=file_name] option. See section 4.9.4 The Binary Log. The binary
log file(s) provide you with the information you need to replicate
changes to the database that are made subsequent to the point at which
you executed mysqldump.
If you have to restore something, try to recover your tables using
REPAIR TABLE or myisamchk -r first. That should work in
99.9% of all cases. If myisamchk fails, try the following
procedure (this will only work if you have started MySQL with
--log-bin, see section 4.9.4 The Binary Log):
mysqldump backup.
shell> mysqlbinlog hostname-bin.[0-9]* | mysqlIf you are using the update log (which will be removed in MySQL 5.0) you can use:
shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql
ls is used to get all the update log files in the right order.
You can also do selective backups with SELECT * INTO OUTFILE 'file_name'
FROM tbl_name and restore with LOAD DATA INFILE 'file_name' REPLACE
... To avoid duplicate records, you need a PRIMARY KEY or a
UNIQUE key in the table. The REPLACE keyword causes old records
to be replaced with new ones when a new record duplicates an old record on
a unique key value.
If you get performance problems in making backups on your system, you can solve this by setting up replication and do the backups on the slave instead of on the master. See section 4.10.1 Introduction.
If you are using a Veritas filesystem, you can do:
FLUSH TABLES WITH READ LOCK.
mount vxfs snapshot.
UNLOCK TABLES.
BACKUP TABLE SyntaxBACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory'
Copies to the backup directory the minimum number of table files needed
to restore the table, after flushing any buffered changes to disk. Currently
works only for MyISAM tables.
For MyISAM tables, copies `.frm' (definition) and
`.MYD' (data) files. The index file can be rebuilt from those two.
Before using this command, please see section 4.4.1 Database Backups.
During the backup, a read lock will be held for each table, one at time,
as they are being backed up. If you want to back up several tables as
a snapshot, you must first issue LOCK TABLES obtaining a read
lock for each table in the group.
The command returns a table with the following columns:
| Column | Value |
| Table | Table name |
| Op | Always ``backup'' |
| Msg_type | One of status, error, info or warning.
|
| Msg_text | The message. |
Note that BACKUP TABLE is only available in MySQL
version 3.23.25 and later.
RESTORE TABLE SyntaxRESTORE TABLE tbl_name[,tbl_name...] FROM '/path/to/backup/directory'
Restores the table(s) from the backup that was made with
BACKUP TABLE. Existing tables will not be overwritten; if you
try to restore over an existing table, you will get an error. Restoring
will take longer than backing up due to the need to rebuild the index. The
more keys you have, the longer it will take. Just as BACKUP TABLE,
RESTORE TABLE currently works only for MyISAM tables.
The command returns a table with the following columns:
| Column | Value |
| Table | Table name |
| Op | Always ``restore'' |
| Msg_type | One of status, error, info or warning.
|
| Msg_text | The message. |
CHECK TABLE SyntaxCHECK TABLE tbl_name[,tbl_name...] [option [option...]] option = QUICK | FAST | MEDIUM | EXTENDED | CHANGED
CHECK TABLE works only on MyISAM and InnoDB tables. On
MyISAM tables it's the same thing as running myisamchk -m
table_name on the table.
If you don't specify any option MEDIUM is used.
Checks the table(s) for errors. For MyISAM tables the key statistics
are updated. The command returns a table with the following columns:
| Column | Value |
| Table | Table name. |
| Op | Always ``check''. |
| Msg_type | One of status, error, info, or warning.
|
| Msg_text | The message. |
Note that you can get many rows of information for each checked table. The
last row will be of Msg_type status and should normally be
OK. If you don't get OK, or Table is already up to
date you should normally run a repair of the table. See section 4.4.6 Using myisamchk for Table Maintenance and Crash Recovery. Table is already up to date means that the table the
given TYPE told MySQL that there wasn't any need to check the
table.
The different check types stand for the following:
| Type | Meaning |
QUICK | Don't scan the rows to check for wrong links. |
FAST | Only check tables which haven't been closed properly. |
CHANGED | Only check tables which have been changed since last check or haven't been closed properly. |
MEDIUM | Scan rows to verify that deleted links are okay. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys. |
EXTENDED | Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but will take a long time! |
For dynamically sized MyISAM tables a started check will always
do a MEDIUM check. For statically sized rows we skip the row scan
for QUICK and FAST as the rows are very seldom corrupted.
You can combine check options as in:
CHECK TABLE test_table FAST QUICK;
Which would simply do a quick check on the table to see whether it was closed properly.
Note: that in some case CHECK TABLE will change the
table! This happens if the table is marked as 'corrupted' or 'not
closed properly' but CHECK TABLE didn't find any problems in the
table. In this case CHECK TABLE will mark the table as okay.
If a table is corrupted, then it's most likely that the problem is in the indexes and not in the data part. All of the above check types checks the indexes thoroughly and should thus find most errors.
If you just want to check a table that you assume is okay, you should use
no check options or the QUICK option. The latter should be used
when you are in a hurry and can take the very small risk that
QUICK didn't find an error in the datafile. (In most cases
MySQL should find, under normal usage, any error in the data
file. If this happens then the table will be marked as 'corrupted',
in which case the table can't be used until it's repaired.)
FAST and CHANGED are mostly intended to be used from a
script (for example to be executed from cron) if you want to check your
table from time to time. In most cases you FAST is to be prefered
over CHANGED. (The only case when it isn't is when you suspect a
bug you have found a bug in the MyISAM code.)
EXTENDED is only to be used after you have run a normal check but
still get strange errors from a table when MySQL tries to
update a row or find a row by key (this is very unlikely if a
normal check has succeeded!).
Some things reported by CHECK TABLE can't be corrected automatically:
Found row where the auto_increment column has the value 0.
This means that you have in the table a row where the
AUTO_INCREMENT index column contains the value 0.
(It's possible to create a row where the AUTO_INCREMENT column is 0 by
explicitly setting the column to 0 with an UPDATE statement.)
This isn't an error in itself, but could cause trouble if you decide to
dump the table and restore it or do an ALTER TABLE on the
table. In this case the AUTO_INCREMENT column will change value,
according to the rules of AUTO_INCREMENT columns, which could cause
problems such as a duplicate key error.
To get rid of the warning, just execute an UPDATE statement
to set the column to some other value than 0.
REPAIR TABLE SyntaxREPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name...] [QUICK] [EXTENDED] [USE_FRM]
REPAIR TABLE works only on MyISAM tables and is the same
as running myisamchk -r table_name on the table.
Normally you should never have to run this command, but if disaster strikes
you are very likely to get back all your data from a MyISAM table with
REPAIR TABLE. If your tables get corrupted a lot you should
try to find the reason for this! See section A.4.1 What To Do If MySQL Keeps Crashing. See section 7.1.3 MyISAM Table Problems.
REPAIR TABLE repairs a possible corrupted table. The command returns a
table with the following columns:
| Column | Value |
| Table | Table name |
| Op | Always ``repair'' |
| Msg_type | One of status, error, info or warning.
|
| Msg_text | The message. |
Note that you can get many rows of information for each repaired
table. The last one row will be of Msg_type status and should
normally be OK. If you don't get OK, you should try
repairing the table with myisamchk -o, as REPAIR TABLE
does not yet implement all the options of myisamchk. In the near
future, we will make it more flexible.
If QUICK is given then MySQL will try to do a
REPAIR of only the index tree.
If you use EXTENDED then MySQL will create the index row
by row instead of creating one index at a time with sorting; this may be
better than sorting on fixed-length keys if you have long CHAR
keys that compress very well. This type of repair is like that done by
myisamchk --safe-recover.
As of MySQL 4.0.2, there is a USE_FRM mode for REPAIR.
Use it if the `.MYI' file is missing or if its header is corrupted.
In this mode MySQL will recreate the table, using information from the
`.frm' file. This kind of repair cannot be done with myisamchk.
Warning: If mysqld dies during a REPAIR TABLE,
it's essential that you do at once another REPAIR on the table
before executing any other commands on it. (It's of course always good
to start with a backup). In the worst case you can have a new clean
index file without information about the data file and when the next
command you do may overwrite the data file. This is not a likely, but
possible scenario.
Strictly before MySQL 4.1.1, REPAIR commands are not written
to the binary log. Since MySQL 4.1.1 they are written to the binary
log unless the optional NO_WRITE_TO_BINLOG keyword
(or its alias LOCAL) was used.
myisamchk for Table Maintenance and Crash Recovery
Starting with MySQL Version 3.23.13, you can check MyISAM
tables with the CHECK TABLE command. See section 4.4.4 CHECK TABLE Syntax. You can
repair tables with the REPAIR TABLE command. See section 4.4.5 REPAIR TABLE Syntax.
To check/repair MyISAM tables (`.MYI' and `.MYD') you should
use the myisamchk utility. To check/repair ISAM tables
(`.ISM' and `.ISD') you should use the isamchk
utility. See section 7 MySQL Table Types.
In the following text we will talk about myisamchk, but everything
also applies to the old isamchk.
You can use the myisamchk utility to get information about your
database tables, check and repair them, or optimise them. The following
sections describe how to invoke myisamchk (including a
description of its options), how to set up a table maintenance schedule,
and how to use myisamchk to perform its various functions.
You can, in most cases, also use the command OPTIMIZE TABLES to
optimise and repair tables, but this is not as fast or reliable (in case
of real fatal errors) as myisamchk. On the other hand,
OPTIMIZE TABLE is easier to use and you don't have to worry about
flushing tables.
See section 4.5.1 OPTIMIZE TABLE Syntax.
Even that the repair in myisamchk is quite secure, it's always a
good idea to make a backup before doing a repair (or anything that could
make a lot of changes to a table)
myisamchk Invocation Syntax
myisamchk is invoked like this:
shell> myisamchk [options] tbl_name
The options specify what you want myisamchk to do. They are
described here. (You can also get a list of options by invoking
myisamchk --help.) With no options, myisamchk simply checks your
table. To get more information or to tell myisamchk to take corrective
action, specify options as described here and in the following sections.
tbl_name is the database table you want to check/repair. If you run
myisamchk somewhere other than in the database directory, you must
specify the path to the file, because myisamchk has no idea where your
database is located. Actually, myisamchk doesn't 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 another 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 name as an index file
name (with the `.MYI' suffix), which 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
tables in the 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 tables is:
myisamchk --silent --fast /path/to/datadir/*/*.MYI isamchk --silent /path/to/datadir/*/*.ISM
If you want to check all tables and repair all tables that are corrupted, you can use the following line:
myisamchk --silent --force --fast --update-state -O key_buffer=64M \
-O sort_buffer=64M -O read_buffer=1M -O write_buffer=1M \
/path/to/datadir/*/*.MYI
isamchk --silent --force -O key_buffer=64M -O sort_buffer=64M \
-O read_buffer=1M -O write_buffer=1M /path/to/datadir/*/*.ISM
The above assumes that you have more than 64 M free.
Note that if you get an error like:
myisamchk: warning: 1 clients is using or hasn't closed the table properly
This means that you are trying to check a table that has been updated by
the another program (like the mysqld server) that hasn't yet closed
the file or that has died without closing the file properly.
If you mysqld is running, you must force a sync/close of all
tables with FLUSH TABLES and ensure that no one is using the
tables while you are running myisamchk. In MySQL Version 3.23
the easiest way to avoid this problem is to use CHECK TABLE
instead of myisamchk to check tables.
myisamchk
myisamchk supports the following options.
-# or --debug=debug_options
debug_options string often is
'd:t:o,filename'.
-? or --help
-O var=option, --set-variable var=option
--set-variable
is deprecated since MySQL 4.0, just use --var=option on its own.
The possible variables and their default values
for myisamchk can be examined with myisamchk --help:
| Variable | Value |
| key_buffer_size | 523264 |
| read_buffer_size | 262136 |
| write_buffer_size | 262136 |
| sort_buffer_size | 2097144 |
| sort_key_blocks | 16 |
| decode_bits | 9 |
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
--extended-check or when the keys are repaired by inserting key
row by row in to the table (like when doing normal inserts). Repairing
through the key buffer is used in the following cases:
--safe-recover.
CHAR, VARCHAR or TEXT keys as the
sort needs to store the whole keys during sorting. If you have lots
of temporary space and you can force myisamchk to repair by sorting
you can use the --sort-recover option.
-s or --silent
-s
twice (-ss) to make myisamchk very silent.
-v or --verbose
-d and
-e. Use -v multiple times (-vv, -vvv) for more
verbosity!
-V or --version
myisamchk version and exit.
-w or, --wait
mysqld
on the table with --skip-external-locking, the table can only be locked
by another myisamchk command.
myisamchk-c or --check
myisamchk any options that override this.
-e or --extend-check
myisamchk or myisamchk --medium-check should, in most
cases, be able to find out if there are any errors in the table.
If you are using --extended-check and have much memory, you should
increase the value of key_buffer_size a lot!
-F or --fast
-C or --check-only-changed
-f or --force
myisamchk with -r (repair) on the table, if
myisamchk finds any errors in the table.
-i or --information
-m or --medium-check
-U or --update-state
--check-only-changed option, but you shouldn't use this
option if the mysqld server is using the table and you are
running mysqld with --skip-external-locking.
-T or --read-only
myisamchk
to check a table that is in use by some other application that doesn't
use locking (like mysqld --skip-external-locking).
The following options are used if you start myisamchk with
-r or -o:
-D # or --data-file-length=#
-e or --extend-check
-f or --force
table_name.TMD) instead of aborting.
-k # or keys-used=#
# indexes. If you are using MyISAM, tells which keys
to use, where each binary bit stands for one key (first key is bit 0).
This can be used to get faster inserts! Deactivated indexes can be
reactivated by using myisamchk -r. keys.
-l or --no-symlinks
myisamchk repairs the
table a symlink points at. This option doesn't exist in MySQL 4.0,
as MySQL 4.0 will not remove symlinks during repair.
-r or --recover
-r, you
should then try -o. (Note that in the unlikely case that -r
fails, the datafile is still intact.)
If you have lots of memory, you should increase the size of
sort_buffer_size!
-o or --safe-recover
-r, but can handle a couple of very unlikely cases that
-r cannot handle. This recovery method also uses much less disk
space than -r. Normally one should always first repair with
-r, and only if this fails use -o.
If you have lots of memory, you should increase the size of
key_buffer_size!
-n or --sort-recover
myisamchk to use sorting to resolve the keys even if the
temporary files should be very big.
--character-sets-dir=...
--set-character-set=name
-t or --tmpdir=path
myisamchk will
use the environment variable TMPDIR for this.
Starting from MySQL 4.1, tmpdir can be set to a list of paths
separated by colon : (semicolon ; on Windows). They
will be used in round-robin fashion.
-q or --quick
-q to force myisamchk to modify the original datafile in case
of duplicate keys
-u or --unpack
myisamchk
Other actions that myisamchk can do, besides repair and check tables:
-a or --analyze
myisamchk --describe --verbose table_name' or using SHOW KEYS in
MySQL.
-d or --description
-A or --set-auto-increment[=value]
AUTO_INCREMENT to start at this or higher value. If no value is
given, then sets the next AUTO_INCREMENT value to the highest used value
for the auto key + 1.
-S or --sort-index
-R or --sort-records=#
SELECT and ORDER BY operations on
this index. (It may be very slow to do a sort the first time!)
To find out a table's index numbers, use SHOW INDEX, which shows a
table's indexes in the same order that myisamchk sees them. Indexes are
numbered beginning with 1.
myisamchk Memory Usage
Memory allocation is important when you run myisamchk.
myisamchk uses no more memory than you specify with the -O
options. If you are going to use myisamchk on very large files,
you should first decide how much memory you want it to use. The default
is to use only about 3M to fix things. By using larger values, you can
get myisamchk to operate faster. For example, if you have more
than 32M RAM, you could use options such as these (in addition to any
other options you might specify):
shell> myisamchk -O sort=16M -O key=16M -O read=1M -O write=1M ...
Using -O sort=16M should probably be enough for most cases.
Be aware that myisamchk uses temporary files in TMPDIR. If
TMPDIR points to a memory filesystem, you may easily get out of
memory errors. If this happens, set TMPDIR to point at some directory
with more space and restart myisamchk.
When repairing, myisamchk will also need a lot of disk space:
--quick, as in this
case only the index file will be re-created. This space is needed on the
same disk as the original record file!
--recover or --sort-recover
(but not when using --safe-recover), you will need space for a
sort buffer for:
(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 table.
This space is allocated on the temporary disk (specified by TMPDIR or
--tmpdir=#).
If you have a problem with disk space during repair, you can try to use
--safe-recover instead of --recover.
myisamchk for Crash Recovery
If you run mysqld with --skip-external-locking (which is the
default on some systems, like Linux), you can't reliably use myisamchk
to check a table when mysqld is using the same table. If you
can be sure that no one is accessing the tables through mysqld
while you run myisamchk, you only have to do mysqladmin
flush-tables before you start checking the tables. If you can't
guarantee the above, then you must take down mysqld while you
check the tables. If you run myisamchk while mysqld is updating
the tables, you may get a warning that a table is corrupt even if it
isn't.
If you are not using --skip-external-locking, you can use
myisamchk to check tables at any time. While you do this, all clients
that try to update the table will wait until myisamchk is ready before
continuing.
If you use myisamchk to repair or optimise tables, you
must always ensure that the mysqld server is not using
the table (this also applies if you are using --skip-external-locking).
If you don't take down mysqld you should at least do a
mysqladmin flush-tables before you run myisamchk.
Your tables may be corrupted if the server and myisamchk
access the tables simultaneously.
This chapter describes how to check for and deal with data corruption in MySQL databases. If your tables get corrupted frequently you should try to find the reason for this! See section A.4.1 What To Do If MySQL Keeps Crashing.
The MyISAM table section contains reason for why a table could be
corrupted. See section 7.1.3 MyISAM Table Problems.
When performing crash recovery, it is important to understand that each table
tbl_name in a database corresponds to three files in the database
directory:
| File | Purpose |
| `tbl_name.frm' | Table definition (form) file |
| `tbl_name.MYD' | Datafile |
| `tbl_name.MYI' | Index file |
Each of these three file types is subject to corruption in various ways, but problems occur most often in datafiles and index files.
myisamchk works by creating a copy of the `.MYD' (data) file
row by row. It ends the repair stage by removing the old `.MYD'
file and renaming the new file to the original file name. If you use
--quick, myisamchk does not create a temporary `.MYD'
file, but instead assumes that the `.MYD' file is correct and only
generates a new index file without touching the `.MYD' file. This
is safe, because myisamchk automatically detects if the
`.MYD' file is corrupt and aborts the repair in this case. You can
also give two --quick options to myisamchk. In this case,
myisamchk does not abort on some errors (like duplicate key) but
instead tries to resolve them by modifying the `.MYD'
file. Normally the use of two --quick options is useful only if
you have too little free disk space to perform a normal repair. In this
case you should at least make a backup before running myisamchk.
To check a MyISAM table, use the following commands:
myisamchk tbl_name
myisamchk without options or
with either the -s or --silent option.
myisamchk -m tbl_name
myisamchk -e tbl_name
-e means
``extended check''). It does a check-read of every key for each row to verify
that they indeed point to the correct row. This may take a long time on a
big table with many keys. myisamchk will normally stop after the first
error it finds. If you want to obtain more information, you can add the
--verbose (-v) option. This causes myisamchk to keep
going, up through a maximum of 20 errors. In normal usage, a simple
myisamchk (with no arguments other than the table name) is sufficient.
myisamchk -e -i tbl_name
-i option tells myisamchk to
print some informational statistics, too.
In the following section we only talk about using myisamchk on
MyISAM tables (extensions `.MYI' and `.MYD'). If you
are using ISAM tables (extensions `.ISM' and `.ISD'),
you should use isamchk instead.
Starting with MySQL Version 3.23.14, you can repair MyISAM
tables with the REPAIR TABLE command. See section 4.4.5 REPAIR TABLE Syntax.
The symptoms of a corrupted table include queries that abort unexpectedly and observable errors such as these:
perror ###. Here
is the most common errors that indicates a problem with the table:
shell> perror 126 127 132 134 135 136 141 144 145 126 = Index file is crashed / Wrong file format 127 = Record-file is crashed 132 = Old database file 134 = Record was already deleted (or record file crashed) 135 = No more room in record file 136 = No more room in index file 141 = Duplicate unique key or constraint on write or update 144 = Table is crashed and last repair failed 145 = Table was marked as crashed and should be repairedNote that error 135, no more room in record file, is not an error that can be fixed by a simple repair. In this case you have to do:
ALTER TABLE table MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;
In the other cases, you must repair your tables. myisamchk
can usually detect and fix most things that go wrong.
The repair process involves up to four stages, described here. Before you
begin, you should cd to the database directory and check the
permissions of the table files. Make sure they are readable by the Unix user
that mysqld runs as (and to you, because you need to access the files
you are checking). If it turns out you need to modify files, they must also
be writable by you.
If you are using MySQL Version 3.23.16 and above, you can (and
should) use the CHECK and REPAIR commands to check and repair
MyISAM tables. See section 4.4.4 CHECK TABLE Syntax. See section 4.4.5 REPAIR TABLE Syntax.
The manual section about table maintenance includes the options to
isamchk/myisamchk. See section 4.4.6 Using myisamchk for Table Maintenance and Crash Recovery.
The following section is for the cases where the above command fails or
if you want to use the extended features that isamchk/myisamchk provides.
If you are going to repair a table from the command-line, you must first
take down the mysqld server. Note that when you do
mysqladmin shutdown on a remote server, the mysqld server
will still be alive for a while after mysqladmin returns, until
all queries are stopped and all keys have been flushed to disk.
Stage 1: Checking your tables
Run myisamchk *.MYI or myisamchk -e *.MYI if you have
more time. Use the -s (silent) option to suppress unnecessary
information.
If the mysqld server is done you should use the --update option to tell
myisamchk to mark the table as 'checked'.
You have to repair only those tables for which myisamchk announces an
error. For such tables, proceed to Stage 2.
If you get weird errors when checking (such as out of
memory errors), or if myisamchk crashes, go to Stage 3.
Stage 2: Easy safe repair
Note: If you want repairing to go much faster, you should add: -O
sort_buffer=# -O key_buffer=# (where # is about 1/4 of the available
memory) to all isamchk/myisamchk commands.
First, try myisamchk -r -q tbl_name (-r -q means ``quick
recovery mode''). This will attempt to repair the index file without
touching the datafile. If the datafile contains everything that it
should and the delete links point at the correct locations within the
datafile, this should work, and the table is fixed. Start repairing the
next table. Otherwise, use the following procedure:
myisamchk -r tbl_name (-r means ``recovery mode''). This will
remove incorrect records and deleted records from the datafile and
reconstruct the index file.
myisamchk --safe-recover tbl_name.
Safe recovery mode uses an old recovery method that handles a few cases that
regular recovery mode doesn't (but is slower).
If you get weird errors when repairing (such as out of
memory errors), or if myisamchk crashes, go to Stage 3.
Stage 3: Difficult repair
You should only reach this stage if the first 16K block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it's necessary to create a new index file. Do so as follows:
shell> mysql db_name mysql> SET AUTOCOMMIT=1; mysql> TRUNCATE TABLE table_name; mysql> quitIf your SQL version doesn't have
TRUNCATE TABLE, use DELETE FROM
table_name instead.
Go back to Stage 2. myisamchk -r -q should work now. (This shouldn't
be an endless loop.)
As of MySQL 4.0.2 you can also use REPAIR ... USE_FRM
which performs the whole procedure automatically.
Stage 4: Very difficult repair
You should reach this stage only if the description file has also crashed. That should never happen, because the description file isn't changed after the table is created:
myisamchk -r.
To coalesce fragmented records and eliminate wasted space resulting from
deleting or updating records, run myisamchk in recovery mode:
shell> myisamchk -r tbl_name
You can optimise a table in the same way using the SQL OPTIMIZE TABLE
statement. OPTIMIZE TABLE does a repair of the table and a key
analysis, and also sorts the index tree to give faster key lookups.
There is also no possibility of unwanted interaction between a utility
and the server, because the server does all the work when you use
OPTIMIZE TABLE. See section 4.5.1 OPTIMIZE TABLE Syntax.
myisamchk also has a number of other options you can use to improve
the performance of a table:
-S, --sort-index
-R index_num, --sort-records=index_num
-a, --analyze
For a full description of the option. See section 4.4.6.1 myisamchk Invocation Syntax.
Starting with MySQL Version 3.23.13, you can check MyISAM
tables with the CHECK TABLE command. See section 4.4.4 CHECK TABLE Syntax. You can
repair tables with the REPAIR TABLE command. See section 4.4.5 REPAIR TABLE Syntax.
It is a good idea to perform table checks on a regular basis rather than
waiting for problems to occur. For maintenance purposes, you can use
myisamchk -s to check tables. The -s option (short for
--silent) causes myisamchk to run in silent mode, printing
messages only when errors occur.
It's also a good idea to check tables when the server starts up.
For example, whenever the machine has done a reboot in the middle of an
update, you usually need to check all the tables that could have been
affected. (This is an ``expected crashed table''.) You could add a test to
mysqld_safe that runs myisamchk to check all tables that have
been modified during the last 24 hours if there is an old `.pid'
(process ID) file left after a reboot. (The `.pid' file is created by
mysqld when it starts up and removed when it terminates normally. The
presence of a `.pid' file at system startup time indicates that
mysqld terminated abnormally.)
An even better test would be to check any table whose last-modified time is more recent than that of the `.pid' file.
You should also check your tables regularly during normal system
operation. At MySQL AB, we run a cron job to check all
our important tables once a week, using a line like this in a `crontab'
file:
35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI
This prints out information about crashed tables so we can examine and repair them when needed.
As we haven't had any unexpectedly crashed tables (tables that become corrupted for reasons other than hardware trouble) for a couple of years now (this is really true), once a week is more than enough for us.
We recommend that to start with, you execute myisamchk -s each
night on all tables that have been updated during the last 24 hours,
until you come to trust MySQL as much as we do.
Normally you don't need to maintain MySQL tables that much. If
you are changing tables with dynamic size rows (tables with VARCHAR,
BLOB or TEXT columns) or have tables with many deleted rows
you may want to from time to time (once a month?) defragment/reclaim space
from the tables.
You can do this by using OPTIMIZE TABLE on the tables in question or
if you can take the mysqld server down for a while do:
isamchk -r --silent --sort-index -O sort_buffer_size=16M */*.ISM myisamchk -r --silent --sort-index -O sort_buffer_size=16M */*.MYI
To get a description of a table or statistics about it, use the commands shown here. We explain some of the information in more detail later:
myisamchk in ``describe mode'' to produce a description of
your table. If you start the MySQL server using the
--skip-external-locking option, myisamchk may report an error
for a table that is updated while it runs. However, because myisamchk
doesn't change the table in describe mode, there isn't any risk of
destroying data.
myisamchk is doing, add -v
to tell it to run in verbose mode.
-eis, but tells you what is being done.
Example of myisamchk -d output:
MyISAM file: company.MYI
Record format: Fixed length
Data records: 1403698 Deleted blocks: 0
Recordlength: 226
table description:
Key Start Len Index Type
1 2 8 unique double
2 15 10 multip. text packed stripped
3 219 8 multip. double
4 63 10 multip. text packed stripped
5 167 2 multip. unsigned short
6 177 4 multip. unsigned long
7 155 4 multip. text
8 138 4 multip. unsigned long
9 177 4 multip. unsigned long
193 1 text
Example of myisamchk -d -v output:
MyISAM file: company
Record format: Fixed length
File-version: 1
Creation time: 1999-10-30 12:12:51
Recover time: 1999-10-31 19:13:01
Status: checked
Data records: 1403698 Deleted blocks: 0
Datafile parts: 1403698 Deleted data: 0
Datafilepointer (bytes): 3 Keyfile pointer (bytes): 3
Max datafile length: 3791650815 Max keyfile length: 4294967294
Recordlength: 226
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 8 unique double 1 15845376 1024
2 15 10 multip. text packed stripped 2 25062400 1024
3 219 8 multip. double 73 40907776 1024
4 63 10 multip. text packed stripped 5 48097280 1024
5 167 2 multip. unsigned short 4840 55200768 1024
6 177 4 multip. unsigned long 1346 65145856 1024
7 155 4 multip. text 4995 75090944 1024
8 138 4 multip. unsigned long 87 85036032 1024
9 177 4 multip. unsigned long 178 96481280 1024
193 1 text
Example of myisamchk -eis output:
Checking MyISAM file: company Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4 Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4 Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4 Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3 Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4 Total: Keyblocks used: 98% Packed: 17% Records: 1403698 M.recordlength: 226 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Record blocks: 1403698 Delete blocks: 0 Recorddata: 317235748 Deleted data: 0 Lost space: 0 Linkdata: 0 User time 1626.51, System time 232.36 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 627, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 639, Involuntary context switches 28966
Example of myisamchk -eiv output:
Checking MyISAM file: company Data records: 1403698 Deleted blocks: 0 - check file-size - check delete-chain block_size 1024: index 1: index 2: index 3: index 4: index 5: index 6: index 7: index 8: index 9: No recordlinks - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4 - check data record references index: 2 Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4 - check data record references index: 3 Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4 - check data record references index: 4 Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3 - check data record references index: 5 Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 6 Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 7 Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 8 Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 9 Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4 Total: Keyblocks used: 9% Packed: 17% - check records and index references [LOTS OF ROW NUMBERS DELETED] Records: 1403698 M.recordlength: 226 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Record blocks: 1403698 Delete blocks: 0 Recorddata: 317235748 Deleted data: 0 Lost space: 0 Linkdata: 0 User time 1639.63, System time 251.61 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0 Blocks in 4 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 10604, Involuntary context switches 122798
Here are the sizes of the data and index files for the table used in the preceding examples:
-rw-rw-r-- 1 monty tcx 317235748 Jan 12 17:30 company.MYD -rw-rw-r-- 1 davida tcx 96482304 Jan 12 18:35 company.MYM
Explanations for the types of information myisamchk produces are
given here. The ``keyfile'' is the index file. ``Record'' and ``row''
are synonymous:
Data
records.
Fixed length.
Other possible values are Compressed and Packed.
unique or multip. (multiple). Indicates whether one value
can exist multiple times in this index.
packed, stripped or empty.
myisamchk -a. If this is not updated at all, a default
value of 30 is given.
myisamchk, the values are very
high (very near the theoretical maximum).
CHAR/VARCHAR/DECIMAL keys. For long strings like
names, this can significantly reduce the space used. In the third example
above, the 4th key is 10 characters long and a 60% reduction in space is
achieved.
Packed
value indicates the percentage of savings achieved by doing this.
myisamchk.
See section 4.4.6.10 Table Optimisation.
Linkdata is the sum of the amount of
storage used by all such pointers.
If a table has been compressed with myisampack, myisamchk
-d prints additional information about each table column. See
section 4.7.4 myisampack, The MySQL Compressed Read-only Table Generator, for an example of this
information and a description of what it means.
OPTIMIZE TABLE SyntaxOPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]...
OPTIMIZE TABLE should be used if you have deleted a large part of a
table or if you have made many changes to a table with variable-length rows
(tables that have VARCHAR, BLOB, or TEXT columns).
Deleted records are maintained in a linked list and subsequent INSERT
operations reuse old record positions. You can use OPTIMIZE TABLE to
reclaim the unused space and to defragment the datafile.
In most setups you don't have to run OPTIMIZE TABLE at all. Even
if you do a lot of updates to variable length rows it's not likely that
you need to do this more than once a month/week and only on certain
tables.
For the moment, OPTIMIZE TABLE works only on MyISAM and
BDB tables. For BDB tables, OPTIMIZE TABLE is
currently mapped to ANALYZE TABLE.
See section 4.5.2 ANALYZE TABLE Syntax.
You can get OPTIMIZE TABLE to work on other table types by starting
mysqld with --skip-new or --safe-mode, but in this
case OPTIMIZE TABLE is just mapped to ALTER TABLE.
OPTIMIZE TABLE works the following way:
Note that the table is locked during the time OPTIMIZE TABLE is
running!
Strictly before MySQL 4.1.1, OPTIMIZE commands are not written
to the binary log. Since MySQL 4.1.1 they are written to the binary
log unless the optional NO_WRITE_TO_BINLOG keyword
(or its alias LOCAL) was used.
ANALYZE TABLE SyntaxANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name...]
Analyse and store the key distribution for the table. During the
analysis, the table is locked with a read lock. This works on
MyISAM and BDB tables.
This is equivalent to running myisamchk -a on the table.
MySQL uses the stored key distribution to decide in which order tables should be joined when one does a join on something else than a constant.
The command returns a table with the following columns:
| Column | Value |
| Table | Table name |
| Op | Always ``analyze'' |
| Msg_type | One of status, error, info or warning.
|
| Msg_text | The message. |
You can check the stored key distribution with the SHOW INDEX command.
See section 4.5.7.1 Retrieving information about Database, Tables, Columns, and Indexes.
If the table hasn't changed since the last ANALYZE TABLE command,
the table will not be analysed again.
Strictly before MySQL 4.1.1, ANALYZE commands are not written
to the binary log. Since MySQL 4.1.1 they are written to the binary
log unless the optional NO_WRITE_TO_BINLOG keyword
(or its alias LOCAL) was used.
FLUSH SyntaxFLUSH [LOCAL | NO_WRITE_TO_BINLOG] flush_option [,flush_option] ...
You should use the FLUSH command if you want to clear some of the
internal caches MySQL uses. To execute FLUSH, you must have
the RELOAD privilege.
flush_option can be any of the following:
| Option | Description |
HOSTS | Empties the host cache tables. You should flush the
host tables if some of your hosts change IP number or if you get the
error message Host ... is blocked. When more than
max_connect_errors errors occur in a row for a given host while
connection to the MySQL server, MySQL assumes
something is wrong and blocks the host from further connection requests.
Flushing the host tables allows the host to attempt to connect
again. See section A.2.5 Host '...' is blocked Error. You can start mysqld with
-O max_connect_errors=999999999 to avoid this error message.
|
DES_KEY_FILE | Reloads the DES keys from the file that was
specified with the --des-key-file option at server startup time.
|
LOGS | Closes and reopens all log files.
If you have specified an update log file or a binary log file without
an extension, the extension number of the log file will be incremented
by one relative to the previous file. If you have used an extension in
the file name, MySQL will close and reopen the update log file.
See section 4.9.3 The Update Log. This is the same thing as sending the SIGHUP
signal to the mysqld server.
|
PRIVILEGES | Reloads the privileges from the grant tables in
the mysql database.
|
QUERY CACHE | Defragment the query cache to better utilise its
memory. This command will not remove any queries from the cache, unlike
RESET QUERY CACHE.
|
TABLES | Closes all open tables and force all tables in use to be closed. This also flushes the query cache. |
[TABLE | TABLES] tbl_name [,tbl_name...] | Flushes only the given tables. |
TABLES WITH READ LOCK | Closes all open tables and locks all tables for all databases with a read lock until you execute UNLOCK TABLES. This is very convenient way to get backups if you have a filesystem, like Veritas, that can take snapshots in time.
|
STATUS | Resets most status variables to zero. This is something one should only use when debugging a query. |
USER_RESOURCES | Resets all user resources to zero. This will enable blocked users to login again. See section 4.3.6 Limiting user resources. |
Strictly before MySQL 4.1.1, FLUSH commands are not written
to the binary log. Since MySQL 4.1.1 they are written to the binary
log unless the optional NO_WRITE_TO_BINLOG keyword
(or its alias LOCAL) was used, or
unless the command contained one of these arguments: LOGS,
MASTER, SLAVE, TABLES WITH READ LOCK, because any
of these arguments may cause problems if replicated to a slave.
You can also access some of the commands shown above with the mysqladmin
utility, using the flush-hosts, flush-logs, flush-privileges,
flush-status or flush-tables commands.
Take also a look at the RESET command used with replication.
See section 4.5.4 RESET Syntax.
RESET SyntaxRESET reset_option [,reset_option] ...
The RESET command is used to clear things. It also acts as an stronger
version of the FLUSH command. See section 4.5.3 FLUSH Syntax.
To execute RESET, you must have the RELOAD privilege.
| Option | Description |
MASTER | Deletes all binary logs listed in the index file, resetting the binlog
index file to be empty. Previously named FLUSH MASTER. See section 4.10.7 SQL Commands Related to Replication.
|
SLAVE | Makes the slave forget its replication position in the master
logs. Previously named FLUSH SLAVE. See section 4.10.7 SQL Commands Related to Replication.
|
QUERY CACHE | Removes all query results from the query cache. |
PURGE [MASTER] LOGS SyntaxPURGE [MASTER] LOGS TO binlog_name PURGE [MASTER] LOGS BEFORE date
MASTER is a useless keyword which can be specified or not.
This command is used to delete all binary logs strictly prior to the
specified binlog or date, see section 4.10.7 SQL Commands Related to Replication.
KILL SyntaxKILL thread_id
Each connection to mysqld runs in a separate thread. You can see
which threads are running with the SHOW PROCESSLIST command and kill
a thread with the KILL thread_id command.
If you have the PROCESS privilege, you can see all threads.
If you have the SUPER privilege, you can kill all threads.
Otherwise, you can only see and kill your own threads.
You can also use the mysqladmin processlist and mysqladmin kill
commands to examine and kill threads.
Note: You currently cannot use KILL with the Embedded MySQL
Server library, because the embedded server merely runs inside the threads
of the host application, it does not create connection threads of its own.
When you do a KILL, a thread-specific kill flag is set for
the thread.
In most cases it may take some time for the thread to die as the kill flag is only checked at specific intervals.
SELECT, ORDER BY and GROUP BY loops, the flag is
checked after reading a block of rows. If the kill flag is set, the
statement is aborted.
ALTER TABLE the kill flag is checked before each block of
rows are read from the original table. If the kill flag was set the command
is aborted and the temporary table is deleted.
UPDATE or DELETE, the kill flag
is checked after each block read and after each updated or deleted
row. If the kill flag is set, the statement is aborted. Note that if you
are not using transactions, the changes will not be rolled back!
GET_LOCK() will abort with NULL.
INSERT DELAYED thread will quickly flush all rows it has in
memory and die.
Locked),
the table lock will be quickly aborted.
write call, the
write is aborted with an disk full error message.
SHOW SyntaxSHOW DATABASES [LIKE wild] or SHOW [OPEN] TABLES [FROM db_name] [LIKE wild] or SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild] or SHOW INDEX FROM tbl_name [FROM db_name] or SHOW TABLE STATUS [FROM db_name] [LIKE wild] or SHOW STATUS [LIKE wild] or SHOW VARIABLES [LIKE wild] or SHOW LOGS or SHOW [FULL] PROCESSLIST or SHOW GRANTS FOR user or SHOW CREATE TABLE table_name or SHOW MASTER STATUS or SHOW MASTER LOGS or SHOW SLAVE STATUS or SHOW WARNINGS [LIMIT #] or SHOW ERRORS [LIMIT #] or SHOW TABLE TYPES
SHOW provides information about databases, tables, columns, or
status information about the server. If the LIKE wild part is
used, the wild string can be a string that uses the SQL `%'
and `_' wildcard characters.
You can use db_name.tbl_name as an alternative to the tbl_name
FROM db_name syntax. These two statements are equivalent:
mysql> SHOW INDEX FROM mytable FROM mydb; mysql> SHOW INDEX FROM mydb.mytable;
SHOW DATABASES lists the databases on the MySQL server host.
You can also get this list using the mysqlshow command line tool.
In version 4.0.2 you will only see those databases for which you have some
kind of privilege, if you don't have the global SHOW DATABASES
privilege.
SHOW TABLES lists the tables in a given database. You can also
get this list using the mysqlshow db_name command.
Note: if a user doesn't have any privileges for a table, the table
will not show up in the output from SHOW TABLES or mysqlshow
db_name.
SHOW OPEN TABLES lists the tables that are currently open in
the table cache. See section 5.4.7 How MySQL Opens and Closes Tables. The Comment field tells
how many times the table is cached and in_use.
SHOW COLUMNS lists the columns in a given table. If you specify
the FULL option, you will also get the privileges you have for
each column. If the column types are different from what you expect them to
be based on a CREATE TABLE statement, note that MySQL
sometimes changes column types. See section 6.5.3.1 Silent Column Specification Changes.
The DESCRIBE statement provides information similar to
SHOW COLUMNS.
See section 6.6.2 DESCRIBE Syntax (Get Information About Columns).
SHOW FIELDS is a synonym for SHOW COLUMNS, and
SHOW KEYS is a synonym for SHOW INDEX. You can also
list a table's columns or indexes with mysqlshow db_name tbl_name
or mysqlshow -k db_name tbl_name.
SHOW INDEX returns the index information in a format that closely
resembles the SQLStatistics call in ODBC. The following columns
are returned:
| Column | Meaning |
Table | Name of the table. |
Non_unique | 0 if the index can't contain duplicates. |
Key_name | Name of the index. |
Seq_in_index | Column sequence number in index, starting with 1. |
Column_name | Column name. |
Collation | How the column is sorted in the index.
In MySQL, this can have values
`A' (Ascending) or NULL (Not
sorted).
|
Cardinality | Number of unique values in the index.
This is updated by running
isamchk -a.
|
Sub_part | Number of indexed characters if the
column is only partly indexed.
NULL if the entire key is indexed.
|
Null | Contains 'YES' if the column may contain NULL.
|
Index_type | Index method used. |
Comment | Various remarks. For now, it tells
in MySQL < 4.0.2 whether index is FULLTEXT or not.
|
Note that as the Cardinality is counted based on statistics
stored as integers, it's not necessarily accurate for small tables.
The Null and Index_type columns were added in MySQL 4.0.2.
SHOW TABLE STATUSSHOW TABLE STATUS [FROM db_name] [LIKE wild]
SHOW TABLE STATUS (new in Version 3.23) works likes SHOW
STATUS, but provides a lot of information about each table. You can
also get this list using the mysqlshow --status db_name command.
The following columns are returned:
| Column | Meaning |
Name | Name of the table. |
Type | Type of table. See section 7 MySQL Table Types. |
Row_format | The row storage format (Fixed, Dynamic, or Compressed). |
Rows | Number of rows. |
Avg_row_length | Average row length. |
Data_length | Length of the datafile. |
Max_data_length | Max length of the datafile. For fixed row formats, this is the max number of rows in the table. For dynamic row formats, this is the total number of data bytes that can be stored in the table, given the data pointer size used. |
Index_length | Length of the index file. |
Data_free | Number of allocated but not used bytes. |
Auto_increment | Next autoincrement value. |
Create_time | When the table was created. |
Update_time | When the datafile was last updated. |
Check_time | When the table was last checked. |
Create_options | Extra options used with CREATE TABLE.
|
Comment | The comment used when creating the table (or some information why MySQL couldn't access the table information). |
InnoDB tables will report the free space in the tablespace
in the table comment.
SHOW STATUS
SHOW STATUS provides server status information
(like mysqladmin extended-status). The output resembles that shown
here, though the format and numbers probably differ:
+--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Bytes_received | 155372598 | | Bytes_sent | 1176560426 | | Connections | 30023 | | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 8340 | | Created_tmp_files | 60 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 462604 | | Handler_read_first | 105881 | | Handler_read_key | 27820558 | | Handler_read_next | 390681754 | | Handler_read_prev | 6022500 | | Handler_read_rnd | 30546748 | | Handler_read_rnd_next | 246216530 | | Handler_update | 16945404 | | Handler_write | 60356676 | | Key_blocks_used | 14955 | | Key_read_requests | 96854827 | | Key_reads | 162040 | | Key_write_requests | 7589728 | | Key_writes | 3813196 | | Max_used_connections | 0 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 1 | | Open_files | 2 | | Open_streams | 0 | | Opened_tables | 44600 | | Questions | 2026873 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 99646 | | Select_range_check | 0 | | Select_scan | 30802 | | Slave_running | OFF | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 30 | | Sort_range | 500 | | Sort_rows | 30296250 | | Sort_scan | 4650 | | Table_locks_immediate | 1920382 | | Table_locks_waited | 0 | | Threads_cached | 0 | | Threads_created | 30022 | | Threads_connected | 1 | | Threads_running | 1 | | Uptime | 80380 | +--------------------------+------------+
The status variables listed above have the following meaning:
| Variable | Meaning |
Aborted_clients | Number of connections aborted because the client died without closing the connection properly. See section A.2.10 Communication Errors / Aborted Connection. |
Aborted_connects | Number of tries to connect to the MySQL server that failed. See section A.2.10 Communication Errors / Aborted Connection. |
Bytes_received | Number of bytes received from all clients. |
Bytes_sent | Number of bytes sent to all clients. |
Com_xxx | Number of times each xxx command has been executed. |
Connections | Number of connection attempts to the MySQL server. |
Created_tmp_disk_tables | Number of implicit temporary tables on disk created while executing statements. |
Created_tmp_tables | Number of implicit temporary tables in memory created while executing statements. |
Created_tmp_files | How many temporary files mysqld has created.
|
Delayed_insert_threads | Number of delayed insert handler threads in use. |
Delayed_writes | Number of rows written with INSERT DELAYED.
|
Delayed_errors | Number of rows written with INSERT DELAYED for which some error occurred (probably duplicate key).
|
Flush_commands | Number of executed FLUSH commands.
|
Handler_commit | Number of internal COMMIT commands.
|
Handler_delete | Number of times a row was deleted from a table. |
Handler_read_first | Number of times the first entry was read from an index.
If this is high, it suggests that the server is doing a lot of full index scans, for example,
SELECT col1 FROM foo, assuming that col1 is indexed.
|
Handler_read_key | Number of requests to read a row based on a key. If this is high, it is a good indication that your queries and tables are properly indexed. |
Handler_read_next | Number of requests to read next row in key order. This will be incremented if you are querying an index column with a range constraint. This also will be incremented if you are doing an index scan. |
Handler_read_prev | Number of requests to read previous row in key order. This is mainly used to optimise ORDER BY ... DESC.
|
Handler_read_rnd | Number of requests to read a row based on a fixed position. This will be high if you are doing a lot of queries that require sorting of the result. |
Handler_read_rnd_next | Number of requests to read the next row in the datafile. This will be high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have. |
Handler_rollback | Number of internal ROLLBACK commands.
|
Handler_update | Number of requests to update a row in a table. |
Handler_write | Number of requests to insert a row in a table. |
Key_blocks_used | The number of used blocks in the key cache. |
Key_read_requests | The number of requests to read a key block from the cache. |
Key_reads | The number of physical reads of a key block from disk. |
Key_write_requests | The number of requests to write a key block to the cache. |
Key_writes | The number of physical writes of a key block to disk. |
Max_used_connections | The maximum number of connections in use simultaneously. |
Not_flushed_key_blocks | Keys blocks in the key cache that has changed but hasn't yet been flushed to disk. |
Not_flushed_delayed_rows | Number of rows waiting to be written in INSERT DELAY queues.
|
Open_tables | Number of tables that are open. |
Open_files | Number of files that are open. |
Open_streams | Number of streams that are open (used mainly for logging). |
Opened_tables | Number of tables that have been opened. |
Rpl_status | Status of failsafe replication. (Not yet in use). |
Select_full_join | Number of joins without keys (If this is not 0, you should carefully check the indexes of your tables). |
Select_full_range_join | Number of joins where we used a range search on reference table. |
Select_range | Number of joins where we used ranges on the first table. (It's normally not critical even if this is big.) |
Select_scan | Number of joins where we did a full scan of the first table. |
Select_range_check | Number of joins without keys where we check for key usage after each row (If this is not 0, you should carefully check the indexes of your tables). |
Questions | Number of queries sent to the server. |
Slave_open_temp_tables | Number of temporary tables currently open by the slave thread |
Slave_running | Is ON if this is a slave that is connected to a master.
|
Slow_launch_threads | Number of threads that have taken more than slow_launch_time to create.
|
Slow_queries | Number of queries that have taken more than long_query_time. See section 4.9.5 The Slow Query Log.
|
Sort_merge_passes | Number of merges passes the sort algoritm have had to do. If this value is large you should consider increasing sort_buffer.
|
Sort_range | Number of sorts that were done with ranges. |
Sort_rows | Number of sorted rows. |
Sort_scan | Number of sorts that were done by scanning the table. |
ssl_xxx | Variables used by SSL; Not yet implemented. |
Table_locks_immediate | Number of times a table lock was acquired immediately. Available after 3.23.33. |
Table_locks_waited | Number of times a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimise your queries, and then either split your table(s) or use replication. Available after 3.23.33. |
Threads_cached | Number of threads in the thread cache. |
Threads_connected | Number of currently open connections. |
Threads_created | Number of threads created to handle connections. |
Threads_running | Number of threads that are not sleeping. |
Uptime | How many seconds the server has been up. |
Some comments about the above:
Opened_tables is big, then your table_cache
variable is probably too small.
Key_reads is big, then your key_buffer_size variable is
probably too small. The cache miss rate can be calculated with
Key_reads/Key_read_requests.
Handler_read_rnd is big, then you probably have a lot of
queries that require MySQL to scan whole tables or you have
joins that don't use keys properly.
Threads_created is big, you may want to increase the
thread_cache_size variable. The cache hit rate can be calculated
with Threads_created/Connections.
Created_tmp_disk_tables is big, you may want to increase the
tmp_table_size variable to get the temporary tables memory-based
instead of disk based.
SHOW VARIABLESSHOW [GLOBAL | SESSION] VARIABLES [LIKE wild]
SHOW VARIABLES shows the values of some MySQL system variables.
The options GLOBAL and SESSION are new in MySQL 4.0.3.
With GLOBAL you will get the variables that will be used for new
connections to MySQL. With SESSION you will get the values that
are in effect for the current connection. If you are not using either
option, SESSION is used.
If the default values are unsuitable, you can set most of these
variables using command-line options when mysqld starts up.
See section 4.1.1 mysqld Command-line Options. It is also possible to change most variables
with the SET statement.
See section 5.5.6 SET Syntax.
The output from SHOW VARIABLES resembles that shown in the
following list, though the format and numbers may differ somewhat.
You can also get this information using the mysqladmin variables
command.
+---------------------------------+------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------| | back_log | 50 | | basedir | /usr/local/mysql | | bdb_cache_size | 8388572 | | bdb_log_buffer_size | 32768 | | bdb_home | /usr/local/mysql | | bdb_max_lock | 10000 | | bdb_logdir | | | bdb_shared_data | OFF | | bdb_tmpdir | /tmp/ | | bdb_version | Sleepycat Software: ... | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set | latin1 | | character_sets | latin1 big5 czech euc_kr | | concurrent_insert | ON | | connect_timeout | 5 | | convert_character_set | | | datadir | /usr/local/mysql/data/ | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -><()~*:""&| | | ft_min_word_len | 4 | | ft_max_word_len | 254 | | ft_max_word_len_for_sort | 20 | | ft_stopword_file | (built-in) | | have_bdb | YES | | have_innodb | YES | | have_isam | YES | | have_raid | NO | | have_symlink | DISABLED | | have_openssl | YES | | have_query_cache | YES | | init_file | | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_file_io_threads | 4 | | innodb_force_recovery | 0 | | innodb_thread_concurrency | 8 | | innodb_flush_log_at_trx_commit | 1 | | innodb_fast_shutdown | ON | | innodb_flush_method | | | innodb_lock_wait_timeout | 50 | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 16773120 | | language | /usr/local/mysql/share/... | | large_files_support | ON | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_update | OFF | | log_bin | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | OFF | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_table_names | OFF | | max_allowed_packet | 1047552 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_connections | 100 | | max_connect_errors | 10 | | max_delayed_threads | 20 | | max_heap_table_size | 16777216 | | max_join_size | 4294967295 | | max_sort_length | 1024 | | max_user_connections | 0 | | max_tmp_tables | 32 | | max_write_lock_count | 4294967295 | | myisam_max_extra_sort_file_size | 268435456 | | myisam_repair_threads | 1 | | myisam_max_sort_file_size | 2147483647 | | myisam_recover_options | force | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | open_files_limit | 0 | | pid_file | /usr/local/mysql/name.pid | | port | 3306 | | protocol_version | 10 | | read_buffer_size | 131072 | | read_rnd_buffer_size | 262144 | | rpl_recovery_rank | 0 | | query_cache_limit | 1048576 | | query_cache_size | 0 | | query_cache_type | ON | | safe_show_database | OFF | | server_id | 0 | | slave_net_timeout | 3600 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slow_launch_time | 2 | | socket | /tmp/mysql.sock | | sort_buffer_size | 2097116 | | sql_mode | 0 | | table_cache | 64 | | table_type | MYISAM | | thread_cache_size | 3 | | thread_stack | 131072 | | tx_isolation | READ-COMMITTED | | timezone | EEST | | tmp_table_size | 33554432 | | tmpdir | /tmp/:/mnt/hd2/tmp/ | | version | 4.0.4-beta | | wait_timeout | 28800 | +---------------------------------+------------------------------+
Each option is described here. Values for buffer sizes, lengths, and stack
sizes are given in bytes. You can specify values with a suffix of `K'
or `M' to indicate kilobytes or megabytes. For example, 16M
indicates 16 megabytes. The case of suffix letters does not matter;
16M and 16m are equivalent:
ansi_mode.
Is ON if mysqld was started with --ansi.
See section 1.8.2 Running MySQL in ANSI Mode.
back_log
The number of outstanding connection requests MySQL can have. This
comes into play when the main MySQL thread gets very
many connection requests in a very short time. It then takes some time
(although very little) for the main thread to check the connection and start
a new thread. The back_log value indicates how many requests can be
stacked during this short time before MySQL momentarily stops
answering new requests. You need to increase this only if you expect a large
number of connections in a short period of time.
In other words, this value is the size of the listen queue for incoming
TCP/IP connections. Your operating system has its own limit on the size
of this queue. The manual page for the Unix listen(2) system
call should have more details. Check your OS documentation for the
maximum value for this variable. Attempting to set back_log
higher than your operating system limit will be ineffective.
basedir
The value of the --basedir option.
bdb_cache_size
The buffer that is allocated to cache index and rows for BDB
tables. If you don't use BDB tables, you should start
mysqld with --skip-bdb to not waste memory for this
cache.
bdb_log_buffer_size
The buffer that is allocated to cache index and rows for BDB
tables. If you don't use BDB tables, you should set this to 0 or
start mysqld with --skip-bdb to not waste memory for this
cache.
bdb_home
The value of the --bdb-home option.
bdb_max_lock
The maximum number of locks (10,000 by default) you can have active on a
BDB table. You should increase this if you get errors of type bdb:
Lock table is out of available locks or Got error 12 from ...
when you have do long transactions or when mysqld has to examine
a lot of rows to calculate the query.
bdb_logdir
The value of the --bdb-logdir option.
bdb_shared_data
Is ON if you are using --bdb-shared-data.
bdb_tmpdir
The value of the --bdb-tmpdir option.
binlog_cache_size. The size of the cache to hold the SQL
statements for the binary log during a transaction. If you often use
big, multi-statement transactions you can increase this to get more
performance. See section 6.7.1 BEGIN/COMMIT/ROLLBACK Syntax.
bulk_insert_buffer_size (was myisam_bulk_insert_tree_size)
MyISAM uses special tree-like cache to make bulk inserts (that is,
INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and
LOAD DATA INFILE) faster. This variable limits
the size of the cache tree in bytes per thread. Setting it to 0
will disable this optimisation.
Note: this cache is only used when adding data to non-empty table.
Default value is 8 MB.
character_set
The default character set.
character_sets
The supported character sets.
concurrent_inserts
If ON (the default), MySQL will allow you to use INSERT on
MyISAM tables at the same time as you run SELECT queries
on them. You can turn this option off by starting mysqld with
--safe or --skip-new.
connect_timeout
The number of seconds the mysqld server is waiting for a connect
packet before responding with Bad handshake.
datadir
The value of the --datadir option.
delay_key_write
Option for MyISAM tables. Can have one of the following values:
| OFF | All CREATE TABLE ... DELAYED_KEY_WRITES are ignored. |
| ON | (default) MySQL will honor the DELAY_KEY_WRITE option
for CREATE TABLE.
|
| ALL | All new opened tables are treated as if they were created with the DELAY_KEY_WRITE option.
|
DELAY_KEY_WRITE is enabled this means that the key buffer for
tables with this option will not get flushed on every index update, but
only when a table is closed. This will speed up writes on keys a lot,
but you should add automatic checking of all tables with myisamchk
--fast --force if you use this.
delayed_insert_limit
After inserting delayed_insert_limit rows, the INSERT
DELAYED handler will check if there are any SELECT statements
pending. If so, it allows these to execute before continuing.
delayed_insert_timeout
How long a INSERT DELAYED thread should wait for INSERT
statements before terminating.
delayed_queue_size
What size queue (in rows) should be allocated for handling INSERT
DELAYED. If the queue becomes full, any client that does INSERT
DELAYED will wait until there is room in the queue again.
flush
This is ON if you have started MySQL with the --flush
option.
flush_time
If this is set to a non-zero value, then every flush_time seconds all
tables will be closed (to free up resources and sync things to disk). We
only recommend this option on Windows 9x/Me, or on systems where you have
very little resources.
ft_boolean_syntax
List of operators supported by MATCH ... AGAINST(... IN BOOLEAN MODE).
See section 6.8 MySQL Full-text Search.
ft_min_word_len
The minimum length of the word to be included in a FULLTEXT index.
Note: FULLTEXT indexes must be rebuilt after changing
this variable. (This option is new for MySQL 4.0.)
ft_max_word_len
The maximum length of the word to be included in a FULLTEXT index.
Note: FULLTEXT indexes must be rebuilt after changing
this variable. (This option is new for MySQL 4.0.)
ft_max_word_len_for_sort
The maximum length of the word in a FULLTEXT index
to be used in fast index recreation method in REPAIR,
CREATE INDEX, or ALTER TABLE. Longer words are inserted the
slow way. The rule of the thumb is as follows: with
ft_max_word_len_for_sort increasing, MySQL will create bigger
temporary files (thus slowing the process down, due to disk I/O), and will put
fewer keys in one sort block (again, decreasing the efficiency). When
ft_max_word_len_for_sort is too small, instead, MySQL will insert a
lot of words into index the slow way, but short words will be inserted very
quickly.
ft_stopword_file
The file to read the list of stopwords for fulltext search from.
All the words from the file will be used, comments are not honored.
By default, built-in list of stopwords is used
(as defined in `myisam/ft_static.c').
Setting this parameter to an empty string ("") will disable
stopword filtering.
Note: FULLTEXT indexes must be rebuilt after changing
this variable. (This option is new for MySQL 4.0.10)
have_innodb
YES if mysqld supports InnoDB tables. DISABLED
if --skip-innodb is used.
have_bdb
YES if mysqld supports Berkeley DB tables. DISABLED
if --skip-bdb is used.
have_raid
YES if mysqld supports the RAID option.
have_openssl
YES if mysqld supports SSL (encryption) on the client/server
protocol.
init_file
The name of the file specified with the --init-file option when
you start the server. This is a file of SQL statements you want the
server to execute when it starts.
interactive_timeout
The number of seconds the server waits for activity on an interactive
connection before closing it. An interactive client is defined as a
client that uses the CLIENT_INTERACTIVE option to
mysql_real_connect(). See also wait_timeout.
join_buffer_size
The size of the buffer that is used for full joins (joins that do not
use indexes). The buffer is allocated one time for each full join
between two tables. Increase this value to get a faster full join when
adding indexes is not possible. (Normally the best way to get fast joins
is to add indexes.)
key_buffer_size
Index blocks are buffered and are shared by all threads.
key_buffer_size is the size of the buffer used for index blocks.
Increase this to get better index handling (for all reads and multiple
writes) to as much as you can afford; 64M on a 256M machine that mainly
runs MySQL is quite common. If you, however, make this too big
(for instance more than 50% of your total memory) your system may start
to page and become extremely slow. Remember that because MySQL does not
cache data reads, you will have to leave some room for the OS
filesystem cache.
You can check the performance of the key buffer by doing SHOW
STATUS and examine the variables Key_read_requests,
Key_reads, Key_write_requests, and Key_writes. The
Key_reads/Key_read_request ratio should normally be < 0.01.
The Key_write/Key_write_requests is usually near 1 if you are
using mostly updates/deletes but may be much smaller if you tend to
do updates that affect many at the same time or if you are
using DELAY_KEY_WRITE. See section 4.5.7 SHOW Syntax.
To get even more speed when writing many rows at the same time, use
LOCK TABLES. See section 6.7.2 LOCK TABLES/UNLOCK TABLES Syntax.
language
The language used for error messages.
large_file_support
If mysqld was compiled with options for big file support.
locked_in_memory
If mysqld was locked in memory with --memlock
log
If logging of all queries is enabled.
log_update
If the update log is enabled.
log_bin
If the binary log is enabled.
log_slave_updates
If the updates from the slave should be logged.
long_query_time
If a query takes longer than this (in seconds), the Slow_queries counter
will be incremented. If you are using --log-slow-queries, the query
will be logged to the slow query logfile. This value is measured in real
time, not CPU time, so a query that may be under the threshold on a lightly
loaded system may be above the threshold on a heavily loaded one.
See section 4.9.5 The Slow Query Log.
lower_case_table_names
If set to 1 table names are stored in lowercase on disk and table
name comparisons will be case-insensitive.
From version 4.0.2, this option also applies to database names.
From 4.1.1 this option also applies to table alias.
See section 6.1.3 Case Sensitivity in Names.
max_allowed_packet
The maximum size of one packet. The message buffer is initialised to
net_buffer_length bytes, but can grow up to max_allowed_packet
bytes when needed. This value by default is small, to catch big (possibly
wrong) packets. You must increase this value if you are using big
BLOB columns. It should be as big as the biggest BLOB you want
to use. The protocol limits for max_allowed_packet is 16M in MySQL
3.23 and 1G in MySQL 4.0.
max_binlog_cache_size
If a multi-statement transaction requires more than this amount of memory,
one will get the error "Multi-statement transaction required more than
'max_binlog_cache_size' bytes of storage".
max_binlog_size
Available after 3.23.33. If a write to the binary (replication) log exceeds
the given value, rotate the logs. You cannot set it to less than 1024 bytes,
or more than 1 GB. Default is 1 GB. Note if you are using
transactions: a transaction is written in one chunk to the binary log,
hence it is never split between several binary logs. Therefore, if you
have big transactions, you may see binlogs bigger than max_binlog_size.
max_connections
The number of simultaneous clients allowed. Increasing this value increases
the number of file descriptors that mysqld requires. See below for
comments on file descriptor limits. See section A.2.6 Too many connections Error.
max_connect_errors
If there is more than this number of interrupted connections from a host
this host will be blocked from further connections. You can unblock a host
with the command FLUSH HOSTS.
max_delayed_threads
Don't start more than this number of threads to handle INSERT DELAYED
statements. If you try to insert data into a new table after all INSERT
DELAYED threads are in use, the row will be inserted as if the
DELAYED attribute wasn't specified. If you set this to 0, MySQL
will never create a max_delayed thread.
max_heap_table_size
Don't allow creation of heap tables bigger than this.
max_join_size
Joins that are probably going to read more than max_join_size
records return an error. Set this value if your users tend to perform joins
that lack a WHERE clause, that take a long time, and that return
millions of rows.
max_seeks_for_key
Limit assumed max number of seeks when looking up rows based on a key.
The MySQL optimiser will assume that when searching after matching rows
in a table through scanning a key, we will not cause more than this
number of key seeks independent of the cardinality of the key. By setting
this to a low value (100 ?) you can force MySQL to prefer keys instead
of table scans.
max_sort_length
The number of bytes to use when sorting BLOB or TEXT
values (only the first max_sort_length bytes of each value
are used; the rest are ignored).
max_user_connections
The maximum number of active connections for a single user (0 = no limit).
max_tmp_tables
(This option doesn't yet do anything.)
Maximum number of temporary tables a client can keep open at the same time.
max_write_lock_count
After this many write locks, allow some read locks to run in between.
myisam_recover_options
The value of the --myisam-recover option.
myisam_sort_buffer_size
The buffer that is allocated when sorting the index when doing a
REPAIR or when creating indexes with CREATE INDEX or
ALTER TABLE.
myisam_max_extra_sort_file_size.
If the temporary file used for fast index creation would be bigger than
using the key cache by the amount specified here, then prefer the key
cache method. This is mainly used to force long character keys in large
tables to use the slower key cache method to create the index.
Note that this parameter is given in megabytes before 4.0.3 and
in bytes beginning with this version.
myisam_repair_threads.
If this value is greater than one, MyISAM table indexes during
Repair by sorting process will be created in parallel -
each index in its own thread. Note: multi-threaded repair
is still alpha quality code.
myisam_max_sort_file_size
The maximum size of the temporary file MySQL is allowed to use
while recreating the index (during REPAIR, ALTER TABLE
or LOAD DATA INFILE. If the file-size would be bigger than this,
the index will be created through the key cache (which is slower).
Note that this parameter is given in megabytes before 4.0.3 and
in bytes beginning with this version.
net_buffer_length
The communication buffer is reset to this size between queries. This
should not normally be changed, but if you have very little memory, you
can set it to the expected size of a query. (That is, the expected length of
SQL statements sent by clients. If statements exceed this length, the buffer
is automatically enlarged, up to max_allowed_packet bytes.)
net_read_timeout
Number of seconds to wait for more data from a connection before aborting
the read. Note that when we don't expect data from a connection, the timeout
is defined by write_timeout. See also slave_net_timeout.
net_retry_count
If a read on a communication port is interrupted, retry this many times
before giving up. This value should be quite high on FreeBSD as
internal interrupts are sent to all threads.
net_write_timeout
Number of seconds to wait for a block to be written to a connection before
aborting the write.
open_files_limit
If this is not 0, then mysqld will use this value to reserve file
descriptors to use with setrlimit(). If this value is 0 then
mysqld will reserve max_connections*5 or
max_connections + table_cache*2 (whichever is larger) number of
files. You should try increasing this if mysqld gives you the
error 'Too many open files'.
pid_file
The value of the --pid-file option.
port
The value of the --port option.
protocol_version
The protocol version used by the MySQL server.
read_buffer_size (was record_buffer)
Each thread that does a sequential scan allocates a buffer of this
size for each table it scans. If you do many sequential scans, you may
want to increase this value.
record_rnd_buffer_size
When reading rows in sorted order after a sort, the rows are read
through this buffer to avoid a disk seeks. Can improve ORDER BY
by a lot if set to a high value. As this is a thread-specific variable,
one should not set this big globally, but just change this when running
some specific big queries.
query_cache_limit
Don't cache results that are bigger than this. (Default 1M).
query_cache_size
The memory allocated to store results from old queries.
If this is 0, the query cache is disabled (default).
query_cache_type
This may be set (only numeric) to
| Value | Alias | Comment |
| 0 | OFF | Don't cache or retrieve results. |
| 1 | ON | Cache all results except SELECT SQL_NO_CACHE ... queries.
|
| 2 | DEMAND | Cache only SELECT SQL_CACHE ... queries.
|
safe_show_database
Don't show databases for which the user doesn't have any database or
table privileges. This can improve security if you're concerned about
people being able to see what databases other users have. See also
skip_show_database.
server_id
The value of the --server-id option.
skip_locking
Is OFF if mysqld uses external locking.
skip_networking
Is ON if we only allow local (socket) connections.
skip_show_database
This prevents people from doing SHOW DATABASES if they don't have
the PROCESS privilege. This can improve security if you're
concerned about people being able to see what databases other users
have. See also safe_show_database.
slave_net_timeout
Number of seconds to wait for more data from a master/slave connection
before aborting the read.
slow_launch_time
If creating the thread takes longer than this value (in seconds), the
Slow_launch_threads counter will be incremented.
socket
The Unix socket used by the server.
sort_buffer_size
Each thread that needs to do a sort allocates a buffer of this
size. Increase this value for faster ORDER BY or GROUP BY
operations.
See section A.4.4 Where MySQL Stores Temporary Files.
table_cache
The number of open tables for all threads. Increasing this value
increases the number of file descriptors that mysqld requires.
You can check if you need to increase the table cache by checking the
Opened_tables variable.
See section 4.5.7.3 SHOW STATUS.
If this variable
is big and you don't do FLUSH TABLES a lot (which just forces all
tables to be closed and reopenend), then you should increase the value of this
variable.
For more information about the table cache, see section 5.4.7 How MySQL Opens and Closes Tables.
table_type
The default table type.
thread_cache_size
How many threads we should keep in a cache for reuse. When a
client disconnects, the client's threads are put in the cache if there
aren't more than thread_cache_size threads from before. All new
threads are first taken from the cache, and only when the cache is empty
is a new thread created. This variable can be increased to improve
performance if you have a lot of new connections. (Normally this doesn't
give a notable performance improvement if you have a good
thread implementation.) By examing the difference between
the Connections and Threads_created status variables
(see section 4.5.7.3 SHOW STATUS for details) you can see how efficient
thread cache is.
thread_concurrency
On Solaris, mysqld will call thr_setconcurrency() with
this value. thr_setconcurrency() permits the application to give
the threads system a hint for the desired number of threads that should
be run at the same time.
thread_stack
The stack size for each thread. Many of the limits detected by the
crash-me test are dependent on this value. The default is
large enough for normal operation. See section 5.1.4 The MySQL Benchmark Suite.
timezone
The timezone for the server.
tmp_table_size
If an in-memory temporary table exceeds this size, MySQL
will automatically convert it to an on-disk MyISAM table.
Increase the value of tmp_table_size if you do many advanced
GROUP BY queries and you have lots of memory.
tmpdir
The directory used for temporary files and temporary tables.
Starting from MySQL 4.1, it can be set to a list of paths
separated by colon : (semicolon ; on Windows). They
will be used in round-robin fashion. This feature can be used to
spread load between several physical disks.
version
The version number for the server.
wait_timeout
The number of seconds the server waits for activity on a not interactive
connection before closing it.
On thread startup SESSION.WAIT_TIMEOUT is initialised from
GLOBAL.WAIT_TIMEOUT or GLOBAL.INTERACTIVE_TIMEOUT depending
on the type of client (as defined by the CLIENT_INTERACTIVE connect
option). See also interactive_timeout.
The manual section that describes tuning MySQL contains some information of how to tune the above variables. See section 5.5.2 Tuning Server Parameters.
SHOW LOGS
SHOW LOGS shows you status information about existing log
files. It currently only displays information about Berkeley DB log
files.
File shows the full path to the log file
Type shows the type of the log file (BDB for Berkeley
DB log files)
Status shows the status of the log file (FREE if the
file can be removed, or IN USE if the file is needed by the transaction
subsystem)
SHOW PROCESSLIST
SHOW [FULL] PROCESSLIST shows you which threads are running.
You can also get this information using the mysqladmin processlist
command. If you have the SUPER privilege, you can see all
threads. Otherwise, you can see only your own threads.
See section 4.5.6 KILL Syntax.
If you don't use the FULL option, then only the first 100
characters of each query will be shown.
Starting from 4.0.12, MySQL reports the hostname for TCP/IP connections
as hostname:client_port to make it easier to find out which client
is doing what.
This command is very useful if you get the 'too many connections' error
message and want to find out what's going on. MySQL reserves
one extra connection for a client with the SUPER privilege
to ensure that you should always be able to login and check the system
(assuming you are not giving this privilege to all your users).
Some states commonly seen in mysqladmin processlist
Checking table
The thread is performing [automatic] checking of the table.
Closing tables
Means that the thread is flushing the changed table data to disk and
closing the used tables. This should be a fast operation. If not, then
you should check that you don't have a full disk or that the disk is not
in very heavy use.
Connect Out
Slave connecting to master.
Copying to tmp table on disk
The temporary result set was larger than tmp_table_size and the
thread is now changing the in memory-based temporary table to a disk
based one to save memory.
Creating tmp table
The thread is creating a temporary table to hold a part of the result for
the query.
deleting from main table
When executing the first part of a multi-table delete and we are only
deleting from the first table.
deleting from reference tables
When executing the second part of a multi-table delete and we are deleting
the matched rows from the other tables.
Flushing tables
The thread is executing FLUSH TABLES and is waiting for all
threads to close their tables.
Killed
Someone has sent a kill to the thread and it should abort next time it
checks the kill flag. The flag is checked in each major loop in MySQL,
but in some cases it may still take a short time for the thread to die.
If the thread is locked by some other thread, the kill will take affect
as soon as the other thread releases it's lock.
Sending data
The thread is processing rows for a SELECT statement and is
also sending data to the client.
Sorting for group
The thread is doing a sort to satsify a GROUP BY.
Sorting for order
The thread is doing a sort to satsify a ORDER BY.
Opening tables
This simply means that the thread is trying to open a table. This is
should be very fast procedure, unless something prevents opening. For
example an ALTER TABLE or a LOCK TABLE can prevent opening
a table until the command is finished.
Removing duplicates
The query was using SELECT DISTINCT in such a way that MySQL
couldn't optimise that distinct away at an early stage. Because of this
MySQL has to do an extra stage to remove all duplicated rows before
sending the result to the client.
Reopen table
The thread got a lock for the table, but noticed after getting the lock
that the underlying table structure changed. It has freed the lock,
closed the table and is now trying to reopen it.
Repair by sorting
The repair code is using sorting to create indexes.
Repair with keycache
The repair code is using creating keys one by one through the key cache.
This is much slower than Repair by sorting.
Searching rows for update
The thread is doing a first phase to find all matching rows before
updating them. This has to be done if the UPDATE is changing
the index that is used to find the involved rows.
Sleeping
The thread is wating for the client to send a new command to it.
System lock
The thread is waiting for getting to get a external system lock for the
table. If you are not using multiple mysqld servers that are accessing
the same tables, you can disable system locks with the
--skip-external-locking option.
Upgrading lock
The INSERT DELAYED handler is trying to get a lock for the table
to insert rows.
Updating
The thread is searching for rows to update and updating them.
User Lock
The thread is waiting on a GET_LOCK().
Waiting for tables
The thread got a notification that the underlying structure for a table
has changed and it needs to reopen the table to get the new structure.
To be able to reopen the table it must however wait until all other
threads have closed the table in question.
This notification happens if another thread has used FLUSH TABLES
or one of the following commands on the table in question: FLUSH
TABLES table_name, ALTER TABLE, RENAME TABLE,
REPAIR TABLE, ANALYZE TABLE or OPTIMIZE TABLE.
waiting for handler insert
The INSERT DELAYED handler has processed all inserts and are
waiting to get new ones.
Most states are very quick operations. If threads last in any of these states for many seconds, there may be a problem around that needs to be investigated.
There are some other states that are not mentioned previously, but most of
these are only useful to find bugs in mysqld.
SHOW GRANTS
SHOW GRANTS FOR user lists the grant commands that must be issued to
duplicate the grants for a user.
mysql> SHOW GRANTS FOR root@localhost; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+
To list grants for the current session one may use CURRENT_USER()
function (new in version 4.0.6) to find out what user the session
was authenticated as.
See section 6.3.6.2 Miscellaneous Functions.
SHOW CREATE TABLE
Shows a CREATE TABLE statement that will create the given table:
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE t (
id INT(11) default NULL auto_increment,
s char(60) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM
SHOW CREATE TABLE will quote table and column names according to
SQL_QUOTE_SHOW_CREATE option.
section 5.5.6 SET Syntax.
SHOW WARNINGS | ERRORSSHOW WARNINGS [LIMIT #] SHOW ERRORS [LIMIT #]
This command is implemented in MySQL 4.1.0.
It shows the errors, warnings and notes that one got for the last command. The errors/warnings are reset for each new command that uses a table.
The MySQL server sends back the total number of warnings and errors you
got for the last commend; This can be retrieved by calling
mysql_warning_count().
Up to max_error_count messages are stored (Global and thread
specific variable).
You can retrieve the number of errors from @error_count and
warnings from @warning_count.
SHOW WARNINGS shows all errors, warnings and notes you got for
the last command while SHOW ERRORS only shows you the errors.
mysql> DROP TABLE IF EXISTS no_such_table; mysql> SHOW WARNINGS; +-------+------+-------------------------------+ | Level | Code | Message | +-------+------+-------------------------------+ | Note | 1051 | Unknown table 'no_such_table' | +-------+------+-------------------------------+
Note that in MySQL 4.1.0 we have just added the frame work for warnings
and not many MySQL command do yet generate warnings. 4.1.1 supports all
kind of warnings for LOAD DATA INFILE and DML statements such as
INSERT, UPDATE and ALTER commands.
For example, here is a simple case which produces conversion warnings for a insert statement.
mysql> create table t1(a tinyint NOT NULL, b char(4)); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(10,'mysql'),(NULL,'test'),(300,'open source'); Query OK, 3 rows affected, 4 warnings (0.15 sec) Records: 3 Duplicates: 0 Warnings: 4 mysql> show warnings; +---------+------+---------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------+ | Warning | 1263 | Data truncated for column 'b' at row 1 | | Warning | 1261 | Data truncated, NULL supplied to NOT NULL column 'a' at row 2 | | Warning | 1262 | Data truncated, out of range for column 'a' at row 3 | | Warning | 1263 | Data truncated for column 'b' at row 3 | +---------+------+---------------------------------------------------------------+ 4 rows in set (0.00 sec)
Maximum number of warnings can be specified using the server variable
'max_error_count', SET max_error_count=[count]; By default
it is 64. In case to disable warnings, simply reset this variable to
'0'. In case if max_error_count is 0, then still the warning
count represents how many warnings has occured, but none of the messages
are stored.
For example, consider the following ALTER table statement for the
above example, which returns only one warning message even though total
warnings occured is 3 when you set max_error_count=1.
mysql> show variables like 'max_error_count'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_error_count | 64 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> set max_error_count=1; Query OK, 0 rows affected (0.00 sec) mysql> alter table t1 modify b char; Query OK, 3 rows affected, 3 warnings (0.00 sec) Records: 3 Duplicates: 0 Warnings: 3 mysql> show warnings; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1263 | Data truncated for column 'b' at row 1 | +---------+------+----------------------------------------+ 1 row in set (0.00 sec) mysql>
SHOW TABLE TYPESSHOW TABLE TYPES
This command is implemented in MySQL 4.1.0.
SHOW TABLE TYPES shows you status information about the table
types. This is particulary useful for checking if a table type is supported;
or to see what is the default table type is.
mysql> SHOW TABLE TYPES; +--------+---------+-----------------------------------------------------------+ | Type | Support | Comment | +--------+---------+-----------------------------------------------------------+ | MyISAM | DEFAULT | Default type from 3.23 with great performance | | HEAP | YES | Hash based, stored in memory, useful for temporary tables | | MERGE | YES | Collection of identical MyISAM tables | | ISAM | YES | Obsolete table type; Is replaced by MyISAM | | InnoDB | YES | Supports transactions, row-level locking and foreign keys | | BDB | NO | Supports transactions and page-level locking | +--------+---------+-----------------------------------------------------------+ 6 rows in set (0.00 sec)
The 'Support' option DEFAULT indicates whether the perticular table
type is supported, and which is the default type. If the server is started with
--default-table-type=InnoDB, then the InnoDB 'Support' field will
have the value DEFAULT.
SHOW PRIVILEGESSHOW PRIVILEGES
This command is implemented in MySQL 4.1.0.
SHOW PRIVILEGES shows the list of system privileges that the underlying
MySQL server supports.
mysql> show privileges; +------------+--------------------------+-------------------------------------------------------+ | Privilege | Context | Comment | +------------+--------------------------+-------------------------------------------------------+ | Select | Tables | To retrieve rows from table | | Insert | Tables | To insert data into tables | | Update | Tables | To update existing rows | | Delete | Tables | To delete existing rows | | Index | Tables | To create or drop indexes | | Alter | Tables | To alter the table | | Create | Databases,Tables,Indexes | To create new databases and tables | | Drop | Databases,Tables | To drop databases and tables | | Grant | Databases,Tables | To give to other users those privileges you possess | | References | Databases,Tables | To have references on tables | | Reload | Server Admin | To reload or refresh tables, logs and privileges | | Shutdown | Server Admin | To shutdown the server | | Process | Server Admin | To view the plain text of currently executing queries | | File | File access on server | To read and write files on the server | +------------+--------------------------+-------------------------------------------------------+ 14 rows in set (0.00 sec)
By default, MySQL uses the ISO-8859-1 (Latin1) character set with sorting according to Swedish/Finnish. This is the character set suitable in the USA and western Europe.
All standard MySQL binaries are compiled with
--with-extra-charsets=complex. This will add code to all
standard programs to be able to handle latin1 and all multi-byte
character sets within the binary. Other character sets will be
loaded from a character-set definition file when needed.
The character set determines what characters are allowed in names and how
things are sorted by the ORDER BY and GROUP BY clauses of
the SELECT statement.
You can change the character set with the --default-character-set
option when you start the server. The character sets available depend
on the --with-charset=charset and --with-extra-charsets=
list-of-charset | complex | all options to configure, and the
character set configuration files listed in
`SHAREDIR/charsets/Index'. See section 2.3.3 Typical configure Options.
If you change the character set when running MySQL (which may
also change the sort order), you must run myisamchk -r -q
--set-character-set=charset on all
tables. Otherwise, your indexes may not be ordered correctly.
When a client connects to a MySQL server, the server sends the default character set in use to the client. The client will switch to use this character set for this connection.
One should use mysql_real_escape_string() when escaping strings
for an SQL query. mysql_real_escape_string() is identical to the
old mysql_escape_string() function, except that it takes the MYSQL
connection handle as the first parameter.
If the client is compiled with different paths than where the server is installed and the user who configured MySQL didn't include all character sets in the MySQL binary, one must specify for the client where it can find the additional character sets it will need if the server runs with a different character set than the client.
One can specify this by putting in a MySQL option file:
[client] character-sets-dir=/usr/local/mysql/share/mysql/charsets
where the path points to the directory in which the dynamic MySQL character sets are stored.
One can force the client to use specific character set by specifying:
[client] default-character-set=character-set-name
but normally this is never needed.
To get German sorting order, you should start mysqld with
--default-character-set=latin1_de. This will give you the following
characteristics.
When sorting and comparing strings, the following mapping is done on the strings before doing the comparison:
ä -> ae ö -> oe ü -> ue ß -> ss
All accented characters, are converted to their un-accented uppercase counterpart. All letters are converted to uppercase.
When comparing strings with LIKE the one -> two character mapping
is not done. All letters are converted to uppercase. Accent are removed
from all letters except: Ü, ü, Ö, ö,
Ä and ä.
mysqld can issue error messages in the following languages:
Czech, Danish, Dutch, English (the default), Estonian, French, German, Greek,
Hungarian, Italian, Japanese, Korean, Norwegian, Norwegian-ny, Polish,
Portuguese, Romanian, Russian, Slovak, Spanish, and Swedish.
To start mysqld with a particular language, use either the
--language=lang or -L lang options. For example:
shell> mysqld --language=swedish
or:
shell> mysqld --language=/usr/local/share/swedish
Note that all language names are specified in lowercase.
The language files are located (by default) in `mysql_base_dir/share/LANGUAGE/'.
To update the error message file, you should edit the `errmsg.txt' file and execute the following command to generate the `errmsg.sys' file:
shell> comp_err errmsg.txt errmsg.sys
If you upgrade to a newer version of MySQL, remember to repeat your changes with the new `errmsg.txt' file.
To add another character set to MySQL, use the following procedure.
Decide if the set is simple or complex. If the character set does not need to use special string collating routines for sorting and does not need multi-byte character support, it is simple. If it needs either of those features, it is complex.
For example, latin1 and danish are simple charactersets while
big5 or czech are complex character sets.
In the following section, we have assumed that you name your character
set MYSET.
For a simple character set do the following:
ctype array takes up the first 257 words. The
to_lower[], to_upper[] and sort_order[] arrays take up
256 words each after that.
CHARSETS_AVAILABLE and
COMPILED_CHARSETS lists in configure.in.
For a complex character set do the following:
ctype_MYSET,
to_lower_MYSET, and so on. This corresponds to the arrays
in the simple character set. See section 4.6.4 The Character Definition Arrays.
/* * This comment is parsed by configure to create ctype.c, * so don't change it unless you know what you are doing. * * .configure. number_MYSET=MYNUMBER * .configure. strxfrm_multiply_MYSET=N * .configure. mbmaxlen_MYSET=N */The
configure program uses this comment to include
the character set into the MySQL library automatically.
The strxfrm_multiply and mbmaxlen lines will be explained in
the following sections. Only include these if you need the string
collating functions or the multi-byte character set functions,
respectively.
my_strncoll_MYSET()
my_strcoll_MYSET()
my_strxfrm_MYSET()
my_like_range_MYSET()
CHARSETS_AVAILABLE and
COMPILED_CHARSETS lists in configure.in.
The file `sql/share/charsets/README' includes some more instructions.
If you want to have the character set included in the MySQL distribution, mail a patch to internals@lists.mysql.com.
to_lower[] and to_upper[] are simple arrays that hold the
lowercase and uppercase characters corresponding to each member of the
character set. For example:
to_lower['A'] should contain 'a' to_upper['a'] should contain 'A'
sort_order[] is a map indicating how characters should be ordered for
comparison and sorting purposes. Quite often (but not for all character sets)
this is the same as to_upper[] (which means sorting will be
case-insensitive). MySQL will sort characters based on the value of
sort_order[character]. For more complicated sorting rules, see
the discussion of string collating below. See section 4.6.5 String Collating Support.
ctype[] is an array of bit values, with one element for one character.
(Note that to_lower[], to_upper[], and sort_order[]
are indexed by character value, but ctype[] is indexed by character
value + 1. This is an old legacy to be able to handle EOF.)
You can find the following bitmask definitions in `m_ctype.h':
#define _U 01 /* Uppercase */ #define _L 02 /* Lowercase */ #define _N 04 /* Numeral (digit) */ #define _S 010 /* Spacing character */ #define _P 020 /* Punctuation */ #define _C 040 /* Control character */ #define _B 0100 /* Blank */ #define _X 0200 /* heXadecimal digit */
The ctype[] entry for each character should be the union of the
applicable bitmask values that describe the character. For example,
'A' is an uppercase character (_U) as well as a
hexadecimal digit (_X), so ctype['A'+1] should contain the
value:
_U + _X = 01 + 0200 = 0201
If the sorting rules for your language are too complex to be handled
with the simple sort_order[] table, you need to use the string
collating functions.
Right now the best documentation on this is the character sets that are
already implemented. Look at the big5, czech, gbk,
sjis, and tis160 character sets for examples.
You must specify the strxfrm_multiply_MYSET=N value in the
special comment at the top of the file. N should be set to
the maximum ratio the strings may grow during my_strxfrm_MYSET (it
must be a positive integer).
If your want to add support for a new character set that includes multi-byte characters, you need to use the multi-byte character functions.
Right now the best documentation on this is the character sets that are
already implemented. Look at the euc_kr, gb2312,
gbk, sjis, and ujis character sets for
examples. These are implemented in the `ctype-'charset'.c' files
in the `strings' directory.
You must specify the mbmaxlen_MYSET=N value in the special
comment at the top of the source file. N should be set to the
size in bytes of the largest character in the set.
If you try to use a character set that is not compiled into your binary, you can run into a couple of different problems:
--character-sets-dir
option to the program in question.
ERROR 1105: File '/usr/local/share/mysql/charsets/?.conf' not found (Errcode: 2)In this case you should either get a new
Index file or add
by hand the name of any missing character sets.
For MyISAM tables, you can check the character set name and number for a
table with myisamchk -dvv table_name.
All MySQL programs take many different options. However, every
MySQL program provides a --help option that you can use
to get a full description of the program's different options. For example, try
mysql --help.
You can override default options for all standard programs with an option file. section 4.1.2 `my.cnf' Option Files.
The following list briefly describes the server-side MySQL programs:
myisamchk
myisamchk has many functions, it is described in its own
chapter. See section 4 Database Administration.
make_binary_distribution
support.mysql.com for the
convenience of other MySQL users.
mysqlbug
mysqld
mysql_install_db
mysqld_safe, The Wrapper Around mysqld
mysqld_safe is the recommended way to start a mysqld
daemon on Unix. mysqld_safe adds some safety features such as
restarting the server when an error occurs and logging run-time
information to a log file.
Note:
Before MySQL 4.0, mysqld_safe is named safe_mysqld.
To preserve backward compatibility, MySQL binary distributions for
some time will include safe_mysqld as a symbolic link to
mysqld_safe.
If you don't use --mysqld=# or --mysqld-version=#
mysqld_safe will use an executable named mysqld-max if it
exists. If not, mysqld_safe will start mysqld.
This makes it very easy to test to use mysqld-max instead of
mysqld; just copy mysqld-max to where you have
mysqld and it will be used.
Normally one should never edit the mysqld_safe script, but
instead put the options to mysqld_safe in the
[mysqld_safe] section in the `my.cnf'
file. mysqld_safe reads all options from the [mysqld],
[server] and [mysqld_safe] sections from the option files.
(For backward compatibility, it also reads the
[safe_mysqld] sections.)
See section 4.1.2 `my.cnf' Option Files.
Note that all options on the command-line to mysqld_safe are passed
to mysqld. If you wants to use any options in mysqld_safe that
mysqld doesn't support, you must specify these in the option file.
Most of the options to mysqld_safe are the same as the options to
mysqld. See section 4.1.1 mysqld Command-line Options.
mysqld_safe supports the following options:
--basedir=path
--core-file-size=#
mysqld should be able to create. Passed to ulimit -c.
--datadir=path
--defaults-extra-file=path
--defaults-file=path
--err-log=path (this is marked obsolete in 4.0; Use --log-error instead)
--log-error=path
--ledir=path
mysqld
--log=path
--mysqld=mysqld-version
mysqld version in the ledir directory you want to start.
--mysqld-version=version
--mysqld= but here you only give the suffix for mysqld.
For example if you use --mysqld-version=max, mysqld_safe will
start the ledir/mysqld-max version. If the argument to
--mysqld-version is empty, ledir/mysqld will be used.
--nice=# (added in MySQL 4.0.14)
--no-defaults
--open-files-limit=#
mysqld should be able to open. Passed to ulimit -n. Note that you need to start mysqld_safe as root for this to work properly!
--pid-file=path
--port=#
--socket=path
--timezone=#
TZ) variable to the value of this parameter.
--user=#
The mysqld_safe script is written so that it normally is able to start
a server that was installed from either a source or a binary version of
MySQL, even if these install the server in slightly different
locations. mysqld_safe expects one of these conditions to be true:
mysqld_safe is invoked. mysqld_safe looks under its working
directory for `bin' and `data' directories (for binary
distributions) or for `libexec' and `var' directories (for source
distributions). This condition should be met if you execute
mysqld_safe from your MySQL installation directory (for
example, `/usr/local/mysql' for a binary distribution).
mysqld_safe attempts to locate them by absolute pathnames. Typical
locations are `/usr/local/libexec' and `/usr/local/var'.
The actual locations are determined when the distribution was built from which
mysqld_safe comes. They should be correct if
MySQL was installed in a standard location.
Because mysqld_safe will try 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 start 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 modify it to use the path to mysqld
and the pathname options that are correct for your system. Note that if you
upgrade MySQL in the future, your modified version of
mysqld_safe will be overwritten, so you should make a copy of your
edited version that you can reinstall.
mysqld_multi, A Program for Managing Multiple MySQL Servers
mysqld_multi is meant for managing several mysqld
processes that listen for connections on different Unix sockets and
TCP/IP ports.
The program will search for group(s) named [mysqld#] from
`my.cnf' (or the file named by the --config-file=... option),
where # can be any positive number starting from 1. This number
is referred to in the following discussion as the option group number,
or GNR. Group numbers distinquish option groups from one another and are
used as arguments to mysqld_multi to specify which servers you want
to start, stop, or obtain status for. Options listed in these groups
should be the same as you would use in the usual [mysqld]
group used for starting mysqld. (See, for example, section 2.4.3 Starting and Stopping MySQL Automatically.) However, for mysqld_multi, be sure that each group
includes options for values such as the port, socket, etc., to be used
for each individual mysqld process.
mysqld_multi is invoked using the following syntax:
Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
or mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
Each GNR represents an option group number. You can start, stop or report any GNR, or several of them at the same time. For an example of how you might set up an option file, use this command:
shell> mysqld_multi --example
The GNR values in the list can be comma-separated or combined with a dash; in the latter case, all the GNRs between GNR1-GNR2 will be affected. With no GNR argument, all groups listed in the option file will be either started, stopped, or reported. Note that you must not have any white spaces in the GNR list. Anything after a white space is ignored.
mysqld_multi supports the following options:
--config-file=...
[mysqld_multi]), but only groups
[mysqld#]. Without this option, everything will be searched from the
ordinary `my.cnf' file.
--example
--help
--log=...
--mysqladmin=...
mysqladmin binary to be used for a server shutdown.
--mysqld=...
mysqld binary to be used. Note that you can give
mysqld_safe to this option also. The options are passed to
mysqld. Just make sure you have mysqld in your environment
variable PATH or fix mysqld_safe.
--no-log
--password=...
mysqladmin.
--tcp-ip
--user=...
mysqladmin.
--version
Some notes about mysqld_multi:
mysqld services (e.g using the mysqladmin program) have the same
password and username for all the data directories accessed (to the
mysql database) And make sure that the user has the SHUTDOWN
privilege! If you have many data directories and many different mysql
databases with different passwords for the MySQL root user,
you may want to create a common multi_admin user for each using the
same password (see below). Example how to do it:
shell> mysql -u root -S /tmp/mysql.sock -proot_password -e "GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'multipass'"See section 4.2.6 How the Privilege System Works. You will have to do the above for each
mysqld running in each
data directory, that you have (just change the socket, -S=...).
pid-file is very important, if you are using mysqld_safe
to start mysqld (for example, --mysqld=mysqld_safe) Every
mysqld should have its own pid-file. The advantage
using mysqld_safe instead of mysqld directly here is,
that mysqld_safe ``guards'' every mysqld process and will
restart it, if a mysqld process terminates due to a signal
sent using kill -9, or for other reasons such as a segmentation
fault (which MySQL should never do, of course ;). Please note that the
mysqld_safe script may require that you start it from a certain
place. This means that you may have to cd to a certain directory,
before you start the 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 /data/mysql -a -f ./share/mysql/english/errmsg.sys -a -x ./bin/mysqld --------------------------------------------------------------------------See section 4.7.2
mysqld_safe, The Wrapper Around mysqld.
The above test should be successful, or you may encounter problems.
mysqlds in the same data
directory. Use separate data directories, unless you know what
you are doing!
mysqld.
mysqld group were intentionally left out from
the example. You may have 'gaps' in the config file. This gives you
more flexibility. The order in which the mysqlds are started or
stopped depends on the order in which they appear in the config file.
[mysqld17] is 17.
--user for mysqld, but in order to
do this you need to run the mysqld_multi script as the Unix root
user. Having the option in the config file doesn't matter; you will
just get a warning, if you are not the superuser and the mysqlds
are started under your Unix account. Important: Make
sure that the pid-file and the data directory are
read+write(+execute for the latter one) accessible for that
Unix user, who the specific mysqld process is started
as. Do not use the Unix root account for this, unless you
know what you are doing!
mysqlds and why one
would want to have separate mysqld processes. Starting multiple
mysqlds in one data directory will not give you extra
performance in a threaded system!
See section 4.1.4 Running Multiple MySQL Servers on the Same Machine.
This is an example of the config file on behalf of mysqld_multi.
# 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.1.2 `my.cnf' Option Files.
myisampack, The MySQL Compressed Read-only Table Generator
myisampack is used to compress MyISAM tables, and pack_isam
is used to compress ISAM tables. Because ISAM tables are deprecated, we
will only discuss myisampack here, but everything said about
myisampack should also be true for pack_isam.
myisampack works by compressing each column in the table separately.
The information needed to decompress columns is read into memory when the
table is opened. This results in much better performance when accessing
individual records, because you only have to uncompress exactly one record, not
a much larger disk block as when using Stacker on MS-DOS.
Usually, myisampack packs the datafile 40%-70%.
MySQL uses memory mapping (mmap()) on compressed tables and
falls back to normal read/write file usage if mmap() doesn't work.
Please note the following:
myisampack can also pack BLOB or TEXT columns.
The older pack_isam (for ISAM tables) can not do this.
myisampack is invoked like this:
shell> myisampack [options] filename ...
Each filename should be the name of an index (`.MYI') file. If you are not in the database directory, you should specify the pathname to the file. It is permissible to omit the `.MYI' extension.
myisampack supports the following options:
-b, --backup
tbl_name.OLD.
-#, --debug=debug_options
debug_options string often is
'd:t:o,filename'.
-f, --force
myisampack creates a temporary file named `tbl_name.TMD'
while it compresses the table. If you kill myisampack, the `.TMD'
file may not be deleted. Normally, myisampack exits with an error if
it finds that `tbl_name.TMD' exists. With --force,
myisampack packs the table anyway.
-?, --help
-j big_tbl_name, --join=big_tbl_name
big_tbl_name. All tables that are to be combined
must be identical (same column names and types, same indexes, etc.).
-p #, --packlength=#
myisampack stores all rows with length pointers of 1, 2, or 3
bytes. In most normal cases, myisampack can determine the right length
value before it begins packing the file, but it may notice during the packing
process that it could have used a shorter length. In this case,
myisampack will print a note that the next time you pack the same file,
you could use a shorter record length.)
-s, --silent
-t, --test
-T dir_name, --tmp_dir=dir_name
-v, --verbose
-V, --version
-w, --wait
mysqld server was
invoked with the --skip-external-locking option, it is not a good idea
to invoke myisampack if the table might be updated during the
packing process.
The sequence of commands shown here 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% 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 Datafilepointer (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
The information printed by myisampack is described here:
normal
empty-space
empty-zero
empty-fill
INTEGER
column may be changed to MEDIUMINT).
pre-space
end-space
table-lookup
ENUM before Huffman compression.
zero
Original trees
After join
After a table has been compressed, myisamchk -dvv prints additional
information about each field:
Type
constant
no endspace
no endspace, not_always
no endspace, no empty
table-lookup
ENUM.
zerofill(n)
n bytes in the value are always 0 and are not
stored.
no zeros
always zero
Huff tree
Bits
After you have run pack_isam/myisampack you must run
isamchk/myisamchk to re-create the index. At this time you
can also sort the index blocks and create statistics needed for
the MySQL optimiser to work more efficiently:
myisamchk -rq --analyze --sort-index table_name.MYI isamchk -rq --analyze --sort-index table_name.ISM
After you have installed the packed table into the MySQL database
directory you should do mysqladmin flush-tables to force mysqld
to start using the new table.
If you want to unpack a packed table, you can do this with the
--unpack option to isamchk or myisamchk.
mysqld-max, An Extended mysqld Server
mysqld-max is the MySQL server (mysqld) configured with
the following configure options:
| Option | Comment |
| --with-server-suffix=-max | Add a suffix to the mysqld version string.
|
| --with-innodb | Support for InnoDB tables in version 3.23. |
| --with-bdb | Support for Berkeley DB (BDB) tables |
| CFLAGS=-DUSE_SYMDIR | Symbolic links support for Windows. |
You can find the MySQL-Max binaries at http://www.mysql.com/downloads/mysql-max-3.23.html.
The Windows MySQL binary distributions includes both the
standard mysqld.exe binary and the mysqld-max.exe binary.
http://www.mysql.com/downloads/mysql-3.23.html.
See section 2.1.2 Installing MySQL on Windows.
Note that as BerkeleyDB (BDB) is not available for all platforms,
so some of the Max binaries may not have support for it.
You can check which table types are supported by doing the following
query:
mysql> SHOW VARIABLES LIKE "have_%"; +------------------+----------+ | Variable_name | Value | +------------------+----------+ | have_bdb | NO | | have_crypt | YES | | have_innodb | YES | | have_isam | YES | | have_raid | NO | | have_symlink | DISABLED | | have_openssl | NO | | have_query_cache | YES | +------------------+----------+
The meaning of the values are:
| Value | Meaning |
YES | The option is activated and usable. |
NO | MySQL is not compiled with support for this option. |
DISABLED | The xxxx option is disabled because one started mysqld with --skip-xxxx or because one didn't start mysqld with all needed options to enable the option. In this case the hostname.err file should contain a reason for why the option is disabled.
|
Note: To be able to create InnoDB tables in MySQL version 3.23
you must edit
your startup options to include at least the innodb_data_file_path
option. See section 7.5.2 InnoDB in MySQL Version 3.23.
To get better performance for BDB tables, you should add some configuration
options for these too. See section 7.6.3 BDB startup options.
mysqld_safe will automatically try to start any mysqld binary
with the -max suffix. This makes it very easy to test out a
another mysqld binary in an existing installation. Just
run configure with the options you want and then install the
new mysqld binary as mysqld-max in the same directory
where your old mysqld binary is. See section 4.7.2 mysqld_safe, The Wrapper Around mysqld.
The mysqld-max RPM uses the above mentioned mysqld_safe
feature. It just installs the mysqld-max executable and
mysqld_safe will automatically use this executable when
mysqld_safe is restarted.
The following table shows which table types our standard MySQL-Max binaries includes:
| System | BDB | InnoDB
|
| AIX 4.3 | N | Y |
| HP-UX 11.0 | N | Y |
| Linux-Alpha | N | Y |
| Linux-Intel | Y | Y |
| Linux-IA-64 | N | Y |
| Solaris-Intel | N | Y |
| Solaris-SPARC | Y | Y |
| SCO OSR5 | Y | Y |
| UnixWare | Y | Y |
| Windows/NT | Y | Y |
All MySQL clients that communicate with the server using the
mysqlclient library use the following environment variables:
| Name | Description |
MYSQL_UNIX_PORT | The default socket; used for connections to localhost
|
MYSQL_TCP_PORT | The default TCP/IP port |
MYSQL_PWD | The default password |
MYSQL_DEBUG | Debug-trace options when debugging |
TMPDIR | The directory where temporary tables/files are created |
Use of MYSQL_PWD is insecure.
See section 4.2.8 Connecting to the MySQL Server.
The `mysql' client uses the file named in the MYSQL_HISTFILE
environment variable to save the command-line history. The default value for
the history file is `$HOME/.mysql_history', where $HOME is the
value of the HOME environment variable. See section F Environment Variables.
All MySQL programs take many different options. However, every
MySQL program provides a --help option that you can use
to get a full description of the program's different options. For example, try
mysql --help.
You can override default options for all standard client programs with an option file. section 4.1.2 `my.cnf' Option Files.
The following list briefly describes the client-side MySQL programs:
msql2mysql
mSQL programs to MySQL. It doesn't
handle all cases, but it gives a good start when converting.
mysqlaccess
mysqladmin
mysqladmin can also be used to retrieve version,
process, and status information from the server.
See section 4.8.3 mysqladmin, Administrating a MySQL Server.
mysqldump
mysqldump, Dumping Table Structure and Data.
mysqlimport
LOAD DATA
INFILE. See section 4.8.7 mysqlimport, Importing Data from Text Files.
mysqlshow
replace
msql2mysql, but that has more
general applicability as well. replace changes strings in place in
files or on the standard input. Uses a finite state machine to match longer
strings first. Can be used to swap strings. For example, this command
swaps a and b in the given files:
shell> replace a b b a -- file1 file2 ...
mysql, The Command-line Tool
mysql is a simple SQL shell (with GNU readline capabilities).
It supports interactive and non-interactive use. When used interactively,
query results are presented in an ASCII-table format. When used
non-interactively (for example, as a filter), the result is presented in
tab-separated format. (The output format can be changed using command-line
options.) You can run scripts simply like this:
shell> mysql database < script.sql > output.tab
If you have problems due to insufficient memory in the client, use the
--quick option! This forces mysql to use
mysql_use_result() rather than mysql_store_result() to
retrieve the result set.
Using mysql is very easy. Just start it as follows:
mysql database or mysql --user=user_name --password=your_password
database. Type an SQL statement, end it with `;', `\g', or `\G'
and press Enter.
mysql supports the following options:
-?, --help
-A, --no-auto-rehash
--prompt=...
-b, --no-beep
-B, --batch
--character-sets-dir=...
-C, --compress
-#, --debug[=...]
-D, --database=...
--default-character-set=...
-e, --execute=...
-E, --vertical
\G.
-f, --force
-g, --no-named-commands
-G, --enable-named-commands
-i, --ignore-space
-h, --host=...
-H, --html
-X, --xml
-L, --skip-line-numbers
--no-pager
--no-tee
-n, --unbuffered
-N, --skip-column-names
-O, --set-variable var=option
--help lists variables.
Please note that --set-variable is deprecated since MySQL 4.0,
just use --var=option on its own.
-o, --one-database
--pager[=...]ENV variable PAGER. Valid
pagers are less, more, cat [> filename], etc. See interactive help (\h)
also. This option does not work in batch mode. Pager works only in Unix.
-p[password], --password[=...]
-p you can't have a space between the option and the
password.
-P port_num, --port=port_num
--protocol=(TCP | SOCKET | PIPE | MEMORY)-q, --quick
-r, --raw
--batch
--reconnect
-s, --silent
-S --socket=...
-t --table
-T, --debug-info
--tee=...
-u, --user=#
-U, --safe-updates[=#], --i-am-a-dummy[=#]
UPDATE and DELETE that uses keys. See below for
more information about this option. You can reset this option if you have
it in your `my.cnf' file by using --safe-updates=0.
-v, --verbose
-V, --version
-w, --wait
You can also set the following variables with -O or
--set-variable; please note that --set-variable
is deprecated since MySQL 4.0, just use --var=option on its own:
| Variable Name | Default | Description |
| connect_timeout | 0 | Number of seconds before timeout connection. |
| max_allowed_packet | 16777216 | Max packetlength to send/receive from to server |
| net_buffer_length | 16384 | Buffer for TCP/IP and socket communication |
| select_limit | 1000 | Automatic limit for SELECT when using --i-am-a-dummy |
| max_join_size | 1000000 | Automatic limit for rows in a join when using --i-am-a-dummy. |
If the mysql client loses connection to the server while
sending it a query, it will immediately and automatically try to
reconnect once to the server and send the query again.
Note that even if it succeeds in reconnecting, as your first
connection has ended, all your previous session objects are lost : temporary
tables, user and session variables. Therefore, the above behaviour may
be dangerous for you, like in this example where the server was shut
down and restarted without you knowing it :
mysql> set @a=1; Query OK, 0 rows affected (0.05 sec) mysql> insert into t values(@a); ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test Query OK, 1 row affected (1.30 sec) mysql> select * from t; +------+ | a | +------+ | NULL | +------+ 1 row in set (0.05 sec)
The @a user variable has been lost with the connection, and
after the reconnection it is undefined.
To protect from this risk, you can start the mysql client
with the --disable-reconnect option.
If you type 'help' on the command-line, mysql will print out the
commands that it supports:
mysql> help
MySQL commands:
help (\h) Display this text.
? (\h) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server.
Optional arguments are db and host.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server,
display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager].
Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file.
Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile].
Append everything into given outfile.
use (\u) Use another database.
Takes database name as argument.
The edit, nopager, pager, and system commands
work only in Unix.
The status command gives you some information about the
connection and the server you are using. If you are running in the
--safe-updates mode, status will also print the values for
the mysql variables that affect your queries.
A useful startup option for beginners (introduced in MySQL
Version 3.23.11) is --safe-updates (or --i-am-a-dummy for
users that has at some time done a DELETE FROM table_name but
forgot the WHERE clause). When using this option, mysql
sends the following command to the MySQL server when opening
the connection:
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=#select_limit#,
SQL_MAX_JOIN_SIZE=#max_join_size#"
where #select_limit# and #max_join_size# are variables that
can be set from the mysql command-line. See section 5.5.6 SET Syntax.
The effect of the above is:
UPDATE or DELETE statement
if you don't have a key constraint in the WHERE part. One can,
however, force an UPDATE/DELETE by using LIMIT:
UPDATE table_name SET not_key_column=# WHERE not_key_column=# LIMIT 1;
#select_limit# rows.
SELECTs that will probably need to examine more than
#max_join_size row combinations will be aborted.
Some useful hints about the mysql client:
Some data is much more readable when displayed vertically, instead of the usual horizontal box type output. For example longer text, which includes new lines, is often much easier to be read with vertical output.
mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 lIMIT 300,1\G
*************************** 1. row ***************************
msg_nro: 3068
date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
reply: monty@no.spam.com
mail_to: "Thimble Smith" <tim@no.spam.com>
sbj: UTF-8
txt: >>>>> "Thimble" == Thimble Smith writes:
Thimble> Hi. I think this is a good idea. Is anyone familiar with UTF-8
Thimble> or Unicode? Otherwise, I'll put this on my TODO list and see what
Thimble> happens.
Yes, please do that.
Regards,
Monty
file: inbox-jani-1
hash: 190402944
1 row in set (0.09 sec)
For logging, you can use the tee option. The tee can be
started with option --tee=..., or from the command-line
interactively with command tee. All the data displayed on the
screen will also be appended into a given file. This can be very useful
for debugging purposes also. The tee can be disabled from the
command-line with command notee. Executing tee again
starts logging again. Without a parameter the previous file will be
used. Note that tee will flush the results into the file after
each command, just before the command-line appears again waiting for the
next command.
Browsing, or searching the results in the interactive mode in Unix less,
more, or any other similar program, is now possible with option
--pager[=...]. Without argument, mysql client will look
for environment variable PAGER and set pager to that.
pager can be started from the interactive command-line with
command pager and disabled with command nopager. The
command takes an argument optionally and the pager will be set to
that. Command pager can be called without an argument, but this
requires that the option --pager was used, or the pager
will default to stdout. pager works only in Unix, since it uses
the popen() function, which doesn't exist in Windows. In Windows, the
tee option can be used instead, although it may not be as handy
as pager can be in some situations.
A few tips about pager:
mysql> pager cat > /tmp/log.txtand the results will only go to a file. You can also pass any options for the programs that you want to use with the
pager:
mysql> pager less -n -i -S
mysql> pager cat | tee /dr1/tmp/res.txt | \ tee /dr2/tmp/res2.txt | less -n -i -S
You can also combine the two functions above; have the tee
enabled, pager set to 'less' and you will be able to browse the
results in unix 'less' and still have everything appended into a file
the same time. The difference between Unix tee used with the
pager and the mysql client in-built tee, is that
the in-built tee works even if you don't have the Unix tee
available. The in-built tee also logs everything that is printed
on the screen, where the Unix tee used with pager doesn't
log quite that much. Last, but not least, the interactive tee is
more handy to switch on and off, when you want to log something into a
file, but want to be able to turn the feature off sometimes.
From MySQL version 4.0.2 it is possible to change the prompt in the
mysql command-line client.
You can use the following prompt options:
| Option | Description |
| \v | mysqld version |
| \d | database in use |
| \h | host connected to |
| \p | port connected on |
| \u | username |
| \U | full username@host |
| \\ | `\' |
| \n | new line break |
| \t | tab |
| \ | space |
| \_ | space |
| \R | military hour time (0-23) |
| \r | standard hour time (1-12) |
| \m | minutes |
| \y | two digit year |
| \Y | four digit year |
| \D | full date format |
| \s | seconds |
| \w | day of the week in three letter format (Mon, Tue, ...) |
| \P | am/pm |
| \o | month in number format |
| \O | month in three letter format (Jan, Feb, ...) |
| \c | counter that counts up for each command you do |
`\' followed by any other letter just becomes that letter.
You may set the prompt in the following places:
MYSQL_PS1 environment variable to a prompt string. For
example:
shell> export MYSQL_PS1="(\u@\h) [\d]> "
prompt option in any MySQL configuration file, in the
mysql group. For example:
[mysql] prompt=(\u@\h) [\d]>\_
--prompt option on the command line to mysql.
For example:
shell> mysql --prompt="(\u@\h) [\d]> " (user@host) [database]>
prompt (or \R) command to change your
prompt interactively. For example:
mysql> prompt (\u@\h) [\d]>\_ PROMPT set to '(\u@\h) [\d]>\_' (user@host) [database]> (user@host) [database]> prompt Returning to default PROMPT of mysql> mysql>
mysqladmin, Administrating a MySQL ServerA utility for performing administrative operations. The syntax is:
shell> mysqladmin [OPTIONS] command [command-option] command ...
You can get a list of the options your version of mysqladmin supports
by executing mysqladmin --help.
The current mysqladmin supports the following commands:
create databasename
drop databasename
extended-status
flush-hosts
flush-logs
flush-tables
flush-privileges
kill id,id,...
password
ping
processlist
reload
refresh
shutdown
slave-start
slave-stop
status
variables
version
All commands can be shortened to their unique prefix. For example:
shell> mysqladmin proc stat +----+-------+-----------+----+-------------+------+-------+------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+-----------+----+-------------+------+-------+------+ | 6 | monty | localhost | | Processlist | 0 | | | +----+-------+-----------+----+-------------+------+-------+------+ Uptime: 10077 Threads: 1 Questions: 9 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 2 Memory in use: 1092K Max memory used: 1116K
The mysqladmin status command result has the following columns:
| Column | Description |
| Uptime | Number of seconds the MySQL server has been up. |
| Threads | Number of active threads (clients). |
| Questions | Number of questions from clients since mysqld was started.
|
| Slow queries | Queries that have taken more than long_query_time seconds. See section 4.9.5 The Slow Query Log.
|
| Opens | How many tables mysqld has opened.
|
| Flush tables | Number of flush ..., refresh, and reload commands.
|
| Open tables | Number of tables that are open now. |
| Memory in use | Memory allocated directly by the mysqld code (only available when MySQL is compiled with --with-debug=full).
|
| Max memory used | Maximum memory allocated directly by the mysqld code (only available when MySQL is compiled with --with-debug=full).
|
If you do mysqladmin shutdown on a socket (in other words, on a
the computer where mysqld is running), mysqladmin will
wait until the MySQL pid-file is removed to ensure that
the mysqld server has stopped properly.
mysqlcheck for Table Maintenance and Crash Recovery
Since MySQL version 3.23.38 you will be able to use a new
checking and repairing tool for MyISAM tables. The difference to
myisamchk is that mysqlcheck should be used when the
mysqld server is running, where as myisamchk should be used
when it is not. The benefit is that you no longer have to take the
server down for checking or repairing your tables.
mysqlcheck uses MySQL server commands CHECK,
REPAIR, ANALYZE and OPTIMIZE in a convenient way
for the user.
There are three alternative ways to invoke mysqlcheck:
shell> mysqlcheck [OPTIONS] database [tables] shell> mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...] shell> mysqlcheck [OPTIONS] --all-databases
So it can be used in a similar way as mysqldump when it
comes to what databases and tables you want to choose.
mysqlcheck does have a special feature compared to the other
clients; the default behaviour, checking tables (-c), can be changed by
renaming the binary. So if you want to have a tool that repairs tables
by default, you should just copy mysqlcheck to your harddrive
with a new name, mysqlrepair, or alternatively make a symbolic
link to mysqlrepair and name the symbolic link as
mysqlrepair. If you invoke mysqlrepair now, it will repair
tables by default.
The names that you can use to change mysqlcheck default behaviour
are here:
mysqlrepair: The default option will be -r mysqlanalyze: The default option will be -a mysqloptimize: The default option will be -o
The options available for mysqlcheck are listed here, please
check what your version supports with mysqlcheck --help.
-A, --all-databases
-1, --all-in-1
-a, --analyze
--auto-repair
-#, --debug=...
--character-sets-dir=...
-c, --check
-C, --check-only-changed
--compress
-?, --help
-B, --databases
--default-character-set=...
-F, --fast
-f, --force
-e, --extended
-h, --host=...
-m, --medium-check
-o, --optimize
-p, --password[=...]
-P, --port=...
--protocol=(TCP | SOCKET | PIPE | MEMORY)-q, --quick
-r, --repair
-s, --silent
-S, --socket=...
--tables
-u, --user=#
-v, --verbose
-V, --version
mysqldump, Dumping Table Structure and DataUtility to dump a database or a collection of database for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump will contain SQL statements to create the table and/or populate the table.
If you are doing a backup on the server, you should consider using
the mysqlhotcopy instead. See section 4.8.6 mysqlhotcopy, Copying MySQL Databases and Tables.
shell> mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS]
If you don't give any tables or use the --databases or
--all-databases, the whole database(s) will be dumped.
You can get a list of the options your version of mysqldump supports
by executing mysqldump --help.
Note that if you run mysqldump without --quick or
--opt, mysqldump will load the whole result set into
memory before dumping the result. This will probably be a problem if
you are dumping a big database.
Note that if you are using a new copy of the mysqldump program
and you are going to do a dump that will be read into a very old MySQL
server, you should not use the --opt or -e options.
mysqldump supports the following options:
--add-locks
LOCK TABLES before and UNLOCK TABLE after each table dump.
(To get faster inserts into MySQL.)
--add-drop-table
drop table before each create statement.
-A, --all-databases
--databases with all
databases selected.
-a, --all
--allow-keywords
-c, --complete-insert
-C, --compress
-B, --databases
USE db_name; will be included in the output before each new database.
--delayed
INSERT DELAYED command.
-e, --extended-insert
INSERT syntax. (Gives more compact and
faster inserts statements.)
-#, --debug[=option_string]
--help
--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...
-T option and have the same
meaning as the corresponding clauses for LOAD DATA INFILE.
See section 6.4.9 LOAD DATA INFILE Syntax.
-F, --flush-logs
-f, --force,
-h, --host=..
localhost.
-l, --lock-tables.
READ LOCAL to allow concurrent inserts in the case of MyISAM
tables.
Please note that when dumping multiple databases, --lock-tables
will lock tables for each database separately. So using this option will
not guarantee your tables will be logically consistent between databases.
Tables in different databases may be dumped in completely different
states.
-K, --disable-keys
/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
/*!40000 ALTER TABLE tb_name ENABLE KEYS */;
will be put in the output. This will make loading the data into a MySQL
4.0 server faster as the indexes are created after all data are inserted.
-n, --no-create-db
CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; will not be put in the
output. The above line will be added otherwise, if a --databases or
--all-databases option was given.
-t, --no-create-info
CREATE TABLE statement).
-d, --no-data
--opt
--quick --add-drop-table --add-locks --extended-insert
--lock-tables. Should give you the fastest possible dump for reading
into a MySQL server.
-pyour_pass, --password[=your_pass]
mysqldump you will be prompted for a password.
-P, --port=...
--protocol=(TCP | SOCKET | PIPE | MEMORY)-q, --quick
mysql_use_result()
to do this.
-Q, --quote-names
-r, --result-file=...
--single-transaction
BEGIN SQL command before dumping data from
server. It is mostly useful with InnoDB tables and
READ_COMMITTED transaction isolation level, as in this mode it
will dump the consistent state of the database at the time then
BEGIN was issued without blocking any applications.
When using this option you should keep in mind that only transactional
tables will be dumped in a consistent state, for example, any MyISAM or
HEAP tables dumped while using this option may still change
state.
The --single-transaction option was added in version 4.0.2.
This option is mutually exclusive with the --lock-tables option
as LOCK TABLES already commits a previous transaction internally.
-S /path/to/socket, --socket=/path/to/socket
localhost (which is the
default host).
--tables
-T, --tab=path-to-some-directory
table_name.sql file, that contains the SQL CREATE commands,
and a table_name.txt file, that contains the data, for each give table.
The format of the `.txt' file is made according to the
--fields-xxx and --lines--xxx options.
Note: This option only works if mysqldump is run on the same
machine as the mysqld daemon, and the user/group that mysqld
is running as (normally user mysql, group mysql) needs to have
permission to create/write a file at the location you specify.
-u user_name, --user=user_name
-O var=option, --set-variable var=option
--set-variable
is deprecated since MySQL 4.0, just use --var=option on its own.
-v, --verbose
-V, --version
-w, --where='where-condition'
"--where=user='jimf'" "-wuserid>1" "-wuserid<1"
-X, --xml
-x, --first-slave
--master-data
--first-slave, but also prints some CHANGE MASTER
TO commands which will later make your slave start from the right position
in the master's binlogs, if you have set up your slave using this SQL
dump of the master.
-O net_buffer_length=#, where # < 16M
--extended-insert or --opt), mysqldump will create
rows up to net_buffer_length length. If you increase this
variable, you should also ensure that the max_allowed_packet
variable in the MySQL server is bigger than the
net_buffer_length.
The most normal use of mysqldump is probably for making a backup of
whole databases. See section 4.4.1 Database Backups.
mysqldump --opt database > backup-file.sql
You can read this back into MySQL with:
mysql database < backup-file.sql
or
mysql -e "source /patch-to-backup/backup-file.sql" database
However, it's also very useful to populate another MySQL server with information from a database:
mysqldump --opt database | mysql --host=remote-host -C database
It is possible to dump several databases with one command:
mysqldump --databases database1 [database2 ...] > my_databases.sql
If all the databases are wanted, one can use:
mysqldump --all-databases > all_databases.sql
mysqlhotcopy, Copying MySQL Databases and Tables
mysqlhotcopy is a Perl script that uses LOCK TABLES,
FLUSH TABLES and cp or scp to quickly make a backup
of a database. It's the fastest way to make a backup of the database
or single tables, but it can only be run on the same machine where the
database directories are. mysqlhotcopy works only on Unix.
mysqlhotcopy db_name [/path/to/new_directory] mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory mysqlhotcopy db_name./regex/
mysqlhotcopy supports the following options:
-?, --help
-u, --user=#
-p, --password=#
-P, --port=#
-S, --socket=#
--allowold
--keepold
--noindices
myisamchk -rq..
--method=#
cp or scp).
-q, --quiet
--debug
-n, --dryrun
--regexp=#
--suffix=#
--checkpoint=#
--flushlog
--tmpdir=#
You can use perldoc mysqlhotcopy to get more complete
documentation for mysqlhotcopy.
mysqlhotcopy reads the groups [client] and [mysqlhotcopy]
from the option files.
To be able to execute mysqlhotcopy you need write access to the
backup directory, the SELECT privilege for the tables you are about to
copy and the MySQL RELOAD privilege (to be able to
execute FLUSH TABLES).
mysqlimport, Importing Data from Text Files
mysqlimport provides a command-line interface to the LOAD DATA
INFILE SQL statement. Most options to mysqlimport correspond
directly to the same options to LOAD DATA INFILE.
See section 6.4.9 LOAD DATA INFILE Syntax.
mysqlimport is invoked like this:
shell> mysqlimport [options] database textfile1 [textfile2 ...]
For each text file named on the command-line,
mysqlimport strips any extension from the filename and uses the result
to determine which table to import the file's contents into. For example,
files named `patient.txt', `patient.text', and `patient' would
all be imported into a table named patient.
mysqlimport supports the following options:
-c, --columns=...
LOAD DATA INFILE command,
which is then passed to MySQL. See section 6.4.9 LOAD DATA INFILE Syntax.
-C, --compress
-#, --debug[=option_string]
-d, --delete
--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...
LOAD DATA INFILE. See section 6.4.9 LOAD DATA INFILE Syntax.
-f, --force
--force,
mysqlimport exits if a table doesn't exist.
--help
-h host_name, --host=host_name
localhost.
-i, --ignore
--replace option.
-l, --lock-tables
-L, --local
localhost (which is the default host).
-pyour_pass, --password[=your_pass]
mysqlimport you will be prompted for a password.
-P port_num, --port=port_num
--protocol=(TCP | SOCKET | PIPE | MEMORY)-r, --replace
--replace and --ignore options control handling of input
records that duplicate existing records on unique key values. If you specify
--replace, new rows replace existing rows that have the same unique key
value. If you specify --ignore, input rows that duplicate an existing
row on a unique key value are skipped. If you don't specify either option, an
error occurs when a duplicate key value is found, and the rest of the text
file is ignored.
-s, --silent
-S /path/to/socket, --socket=/path/to/socket
localhost (which is the
default host).
-u user_name, --user=user_name
-v, --verbose
-V, --version
Here is a sample run using mysqlimport:
$ mysql --version mysql Ver 9.33 Distrib 3.22.25, for pc-linux-gnu (i686) $ uname -a Linux xxx.com 2.2.5-15 #1 Mon Apr 19 22:21:09 EDT 1999 i586 unknown $ mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test $ ed a 100 Max Sydow 101 Count Dracula . w imptest.txt 32 q $ od -c imptest.txt 0000000 1 0 0 \t M a x S y d o w \n 1 0 0000020 1 \t C o u n t D r a c u l a \n 0000040 $ mysqlimport --local test imptest.txt test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 $ mysql -e 'SELECT * FROM imptest' test +------+---------------+ | id | n | +------+---------------+ | 100 | Max Sydow | | 101 | Count Dracula | +------+---------------+
mysqlshow, Showing Databases, Tables, and Columns
mysqlshow can be used to quickly look at which databases exist,
their tables, and the table's columns.
With the mysql program you can get the same information with the
SHOW commands. See section 4.5.7 SHOW Syntax.
mysqlshow is invoked like this:
shell> mysqlshow [OPTIONS] [database [table [column]]]
Note that in newer MySQL versions, you only see those database/tables/columns for which you have some privileges.
If the last argument contains a shell or SQL wildcard (*,
?, % or _) then only what's matched by the wildcard
is shown. If a database contains underscore(s), those should be escaped
with backslash (some Unix shells will require two), in order to get
tables / columns properly. '*' are converted into SQL '%' wildcard and
'?' into SQL '_' wildcard. This may cause some confusion when you try
to display the columns for a table with a _ as in this case
mysqlshow only shows you the table names that match the pattern.
This is easily fixed by adding an extra % last on the
command-line (as a separate argument).
mysql_config, Get compile options for compiling clients
mysql_config provides you with useful information how to compile
your MySQL client and connect it to MySQL.
mysql_config supports the following options:
--cflags
--libs
--socket
--port
--version
--libmysqld-libs
If you execute mysql_config without any options it will print
all options it supports plus the value of all options:
shell> mysql_config
sage: /usr/local/mysql/bin/mysql_config [OPTIONS]
Options:
--cflags [-I'/usr/local/mysql/include/mysql']
--libs [-L'/usr/local/mysql/lib/mysql' -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib -lssl -lcrypto]
--socket [/tmp/mysql.sock]
--port [3306]
--version [4.0.8-gamma]
--libmysqld-libs [ -L'/usr/local/mysql/lib/mysql' -lmysqld -lpthread -lz -lcrypt -lnsl -lm -lpthread -lrt]
You can use this to compile a MySQL client by as follows:
CFG=/usr/local/mysql/bin/mysql_config sh -c "gcc -o progname `$CFG --cflags` progname.c `$CFG --libs`"
perror, Explaining Error Codes
For most system errors MySQL will, in addition to a internal text message,
also print the system error code in one of the following styles:
message ... (errno: #) or message ... (Errcode: #).
You can find out what the error code means by either examining the
documentation for your system or use the perror utility.
perror prints a description for a system error code, or an MyISAM/ISAM
storage engine (table handler) error code.
perror is invoked like this:
shell> perror [OPTIONS] [ERRORCODE [ERRORCODE...]] Example: shell> perror 13 64 Error code 13: Permission denied Error code 64: Machine is not on the network
Note that the error messages are mostly system dependent!
The mysql client typically is used interactively, like this:
shell> mysql database
However, it's also possible to put your SQL commands in a file and tell
mysql to read its input from that file. To do so, create a text
file `text_file' that contains the commands you wish to execute.
Then invoke mysql as shown here:
shell> mysql database < text_file
You can also start your text file with a USE db_name statement. In
this case, it is unnecessary to specify the database name on the command
line:
shell> mysql < text_file
If you are already running mysql, you can execute an SQL
script file using the source command:
mysql> source filename;
For more information about batch mode, section 3.6 Using mysql in Batch Mode.
MySQL has several different log files that can help you find
out what's going on inside mysqld:
| Log file | Description |
| The error log | Problems encountering starting, running or stopping mysqld.
|
| The isam log | Logs all changes to the ISAM tables. Used only for debugging the isam code. |
| The query log | Established connections and executed queries. |
| The update log | Deprecated: Stores all statements that changes data |
| The binary log | Stores all statements that changes something. Used also for replication |
| The slow log | Stores all queries that took more than long_query_time to execute or didn't use indexes.
|
All logs can be found in the mysqld data directory. You can
force mysqld to reopen the log files (or in some cases
switch to a new log) by executing FLUSH LOGS. See section 4.5.3 FLUSH Syntax.
The error log file contains information indicating when mysqld
was started and stopped and also any critical errors found when running.
If mysqld dies unexpectedly and mysqld_safe needs to
restart mysqld, mysqld_safe will write a restarted
mysqld row in this file. This log also holds a warning if
mysqld notices a table that needs to be automatically checked or
repaired.
On some operating systems, the error log will contain a stack trace
for where mysqld died. This can be used to find out where
mysqld died. See section E.1.4 Using a Stack Trace.
Beginning with MySQL 4.0.10 you can specify where mysqld stores the
error log file with the option --log-error[=filename]. If no file
name is given mysqld will use mysql-data-dir/'hostname'.err on
Unix and `\mysql\data\mysql.err' on Windows.
If you execute flush logs the old file will be prefixed with
--old and mysqld will create a new empty log file.
In older MySQL versions the error log handling was done by
mysqld_safe which redirected the error file to
'hostname'.err. One could change this file name with the option
--err-log=filename.
If you don't specify --log-error or if you use the --console
option the errors will be written to stderr (the terminal).
On Windows, the output is always written to the .err file if
--console is not given.
If you want to know what happens within mysqld, you should start
it with --log[=file]. This will log all connections and queries
to the log file (by default named `'hostname'.log'). This log can
be very useful when you suspect an error in a client and want to know
exactly what mysqld thought the client sent to it.
Older versions of the mysql.server script (from MySQL 3.23.4 to 3.23.8)
pass safe_mysqld a --log option (enable general query log).
If you need better performance when you start using MySQL in a production
environment, you can remove the --log option from mysql.server
or change it to --log-bin. See section 4.9.4 The Binary Log.
The entries in this log are written as mysqld receives the questions.
This may be different from the order in which the statements are executed.
This is in contrast to the update log and the binary log which are written
after the query is executed, but before any locks are released.
Note: the update log is replaced by the binary log. See section 4.9.4 The Binary Log. With this you can do anything that you can do with the update log. The update log will be removed in MySQL 5.0.
When started with the --log-update[=file_name] option,
mysqld writes a log file containing all SQL commands that update
data. If no filename is given, it defaults to the name of the host
machine. If a filename is given, but it doesn't contain a path, the file
is written in the data directory. If `file_name' doesn't have an
extension, mysqld will create log file names like so:
`file_name.###', where ### is a number that is incremented each
time you execute mysqladmin refresh, execute mysqladmin
flush-logs, execute the FLUSH LOGS statement, or restart the server.
Note: for the above scheme to work, you must not create your own files with the same filename as the update log + some extensions that may be regarded as a number, in the directory used by the update log!
If you use the --log or -l options, mysqld writes a
general log with a filename of `hostname.log', and restarts and
refreshes do not cause a new log file to be generated (although it is closed
and reopened). In this case you can copy it (on Unix) by doing:
mv hostname.log hostname-old.log mysqladmin flush-logs cp hostname-old.log to-backup-directory rm hostname-old.log
Update logging is smart because it logs only statements that really update
data. So an UPDATE or a DELETE with a WHERE that finds no
rows is not written to the log. It even skips UPDATE statements that
set a column to the value it already has.
The update logging is done immediately after a query completes but before any locks are released or any commit is done. This ensures that the log will be logged in the execution order.
If you want to update a database from update log files, you could do the following (assuming your update logs have names of the form `file_name.###'):
shell> ls -1 -t -r file_name.[0-9]* | xargs cat | mysql
ls is used to get all the log files in the right order.
This can be useful if you have to revert to backup files after a crash and you want to redo the updates that occurred between the time of the backup and the crash.
The intention is that the binary log should replace the update log, so we recommend you to switch to this log format as soon as possible! The update log will be removed in MySQL 5.0.
The binary log contains all information that is available in the update log in a more efficient format. It also contains information about how long each query took that updated the database. It doesn't contain queries that don't modify any data. If you want to log all queries (for example to find a problem query) you should use the general query log. See section 4.9.2 The General Query Log.
The binary log is also used when you are replicating a slave from a master. See section 4.10 Replication in MySQL.
When started with the --log-bin[=file_name] option, mysqld
writes a log file containing all SQL commands that update data. If no
file name is given, it defaults to the name of the host machine followed
by -bin. If file name is given, but it doesn't contain a path, the
file is written in the data directory.
If you supply an extension to --log-bin=filename.extension, the
extension will be silenty removed.
To the binary log filename mysqld will append an extension that
is a number that is incremented each time you execute mysqladmin
refresh, execute mysqladmin flush-logs, execute the FLUSH
LOGS statement or restart the server. A new binary log will also
automatically be created when the current one's size reaches
max_binlog_size. Note if you are using
transactions: a transaction is written in one chunk to the binary log,
hence it is never split between several binary logs. Therefore, if you
have big transactions, you may see binlogs bigger than max_binlog_size.
You can delete all binary log files with the RESET MASTER
command (see section 4.5.4 RESET Syntax), or only some of them with
PURGE [MASTER] LOGS (see section 4.10.7 SQL Commands Related to Replication).
You can use the following options to mysqld to affect what is logged
to the binary log:
| Option | Description |
binlog-do-db=database_name |
Tells the master that it should log updates to the binary log if the
current database
(that is, the one selected by USE)
database is 'database_name'. All others
databases which are not explicitly mentioned are ignored.
Note that if you use this you should ensure that you only do updates in
the current database.
(Example: binlog-do-db=some_database)
Example of what does not work as you could expect it: if the server is
started with binlog-do-db=sales, and you do
USE prices; UPDATE sales.january SET amount=amount+1000;,
this query will not be written into the binary log.
|
binlog-ignore-db=database_name |
Tells the master that updates where the current database
(that is, the one selected by USE) is
'database_name' should not be stored in the binary log. Note that if
you use this you should ensure that you only do updates in the current
database.
(Example: binlog-ignore-db=some_database)
Example of what does not work as you could expect it: if the server is
started with binlog-ignore-db=sales, and you do
USE prices; UPDATE sales.january SET amount=amount+1000;,
this query will be written into the binary log.
|
To be able to know which different binary log files have been used,
mysqld will also create a binary log index file that
contains the name of all used binary log files. By default this has the
same name as the binary log file, with the extension '.index'.
You can change the name of the binary log index file with the
--log-bin-index=[filename] option.
You should not manually edit this file while mysqld is running;
doing this would confuse mysqld.
If you are using replication, you should not delete old binary log
files until you are sure that no slave will ever need to use them.
One way to do this is to do mysqladmin flush-logs once a day and then
remove any logs that are more than 3 days old. You can remove them
manually, or preferably using PURGE [MASTER] LOGS
(see section 4.10.7 SQL Commands Related to Replication) which will also safely update the binary
log index file for you (and which can take a date argument since
MySQL 4.1)
A connexion with the SUPER privilege can disable the binary
logging of its queries using SET
SQL_LOG_BIN=0. See section 4.10.7 SQL Commands Related to Replication.
You can examine the binary log file with the mysqlbinlog command.
For example, you can update a MySQL server from the binary log
as follows:
shell> mysqlbinlog log-file | mysql -h server_name
You can also use the mysqlbinlog program to read the binary log
directly from a remote MySQL server!
mysqlbinlog --help will give you more information of how to use
this program!
If you are using BEGIN [WORK] or SET AUTOCOMMIT=0, you must
use the MySQL binary log for backups instead of the old update log,
which will be removed in MySQL 5.0.
The binary logging is done immediately after a query completes but before any locks are released or any commit is done. This ensures that the log will be logged in the execution order.
Updates to non-transactional tables are stored in the binary log
immediately after execution. For transactional tables such as BDB
or InnoDB tables, all updates (UPDATE, DELETE
or INSERT) that change tables are cached until a COMMIT
command is sent to the server. At this point mysqld writes the whole
transaction to the binary log before the COMMIT is executed.
Every thread will, on start, allocate a buffer of binlog_cache_size
to buffer queries. If a query is bigger than this, the thread will open
a temporary file to store the transaction. The temporary file will
be deleted when the thread ends.
The max_binlog_cache_size (default 4G) can be used to restrict the
total size used to cache a multi-query transaction. If a transaction is
bigger than this it will fail and roll back.
If you are using the update or binary log, concurrent inserts will
be converted to normal inserts when using CREATE ... SELECT or
INSERT ... SELECT.
This is to ensure that you can recreate an exact copy of your tables by
applying the log on a backup.
When started with the --log-slow-queries[=file_name] option,
mysqld writes a log file containing all SQL commands that took
more than long_query_time to execute. The time to get the initial
table locks are not counted as execution time.
The slow query log is logged after the query is executed and after all locks has been released. This may be different from the order in which the statements are executed.
If no file name is given, it defaults to the name of the host machine
suffixed with -slow.log. If a filename is given, but doesn't
contain a path, the file is written in the data directory.
The slow query log can be used to find queries that take a long time to
execute and are thus candidates for optimisation. With a large log, that
can become a difficult task. You can pipe the slow query log through the
mysqldumpslow command to get a summary of the queries which
appear in the log.
You are using --log-long-format then also queries that are not
using indexes are printed. See section 4.1.1 mysqld Command-line Options.
The MySQL Server can create a number of different log files, which make it easy to see what is going on. See section 4.9 The MySQL Log Files. One must however regularly clean up these files, to ensure that the logs don't take up too much disk space.
When using MySQL with log files, you will, from time to time, want to remove/backup old log files and tell MySQL to start logging on new files. See section 4.4.1 Database Backups.
On a Linux (Red Hat) installation, you can use the
mysql-log-rotate script for this. If you installed MySQL
from an RPM distribution, the script should have been installed
automatically. Note that you should be careful with this if you are using
the log for replication!
On other systems you must install a short script yourself that you
start from cron to handle log files.
You can force MySQL to start using new log files by using
mysqladmin flush-logs or by using the SQL command FLUSH LOGS.
If you are using MySQL Version 3.21 you must use mysqladmin refresh.
The above command does the following:
--log) or slow query logging
(--log-slow-queries) is used, closes and reopens the log file
(`mysql.log' and ``hostname`-slow.log' as default).
--log-update) is used, closes the update log and
opens a new log file with a higher sequence number.
If you are using only an update log, you only have to flush the logs and then move away the old update log files to a backup. If you are using the normal logging, you can do something like:
shell> cd mysql-data-directory shell> mv mysql.log mysql.old shell> mysqladmin flush-logs
and then take a backup and remove `mysql.old'.
This section describes the various replication features in MySQL. It serves as a reference to the options available with replication. You will be introduced to replication and learn how to implement it. Toward the end, there are some frequently asked questions and descriptions of problems and how to solve them.
We suggest that you visit our website at http://www.mysql.com/ often and read updates to this section. Replication is constantly being improved, and we update the manual frequently with the most current information.
One-way replication can be used is to increase both robustness and speed. For robustness you can have two systems and can switch to the backup if you have problems with the master. The extra speed is achieved by sending a part of the non-updating queries to the replica server. Of course this only works if non-updating queries dominate, but that is the normal case.
Starting in Version 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while the other acts as the slave. Note that one server could play the roles of master in one pair and slave in the other. The master server keeps a binary log of updates (see section 4.9.4 The Binary Log) and an index file to binary logs to keep track of log rotation. The slave, upon connecting, informs the master where it left off since the last successfully propagated update, catches up on the updates, and then blocks and waits for the master to notify it of the new updates.
Note that if you are using replicating all updates to the tables you replicate should be done through the master, unless you are always careful of avoiding conflicts between updates which users issue on the master and those which users issue on the slave.
Another benefit of using replication is that one can get non-disturbing backups of the system by doing a backup on a slave instead of doing it on the master. See section 4.4.1 Database Backups.
MySQL replication is based on the server keeping track of all changes to your database (updates, deletes, etc) in the binary log (see section 4.9.4 The Binary Log) and the slave server(s) reading the saved queries from the master server's binary log so that the slave can execute the same queries on its copy of the data.
It is very important to realise that the binary log is simply a record starting from a fixed point in time (the moment you enable binary logging). Any slaves which you set up will need copies of the data from your master as it existed the moment that you enabled binary logging on the master. If you start your slaves with data that doesn't agree with what was on the master when the binary log was started, your slaves may fail.
Please see the following table for an indication of master-slave compatibility between different versions. With regard to version 4.0, we recommend using same version on both sides.
| Master | Master | Master | Master | ||
| 3.23.33 and up | 4.0.0 | 4.0.1 | 4.0.3 and up | ||
| Slave | 3.23.33 and up | yes | no | no | no |
| Slave | 4.0.0 | no | yes | no | no |
| Slave | 4.0.1 | yes | no | yes | no |
| Slave | 4.0.3 and up | yes | no | no | yes |
Note: MySQL Version 4.0.2 is not recommended for replication. As a general rule, it's always recommended to use recent MySQL versions for replication.
Note that when you upgrade a master from MySQL 3.23 to MySQL 4.0 (or 4.1) you should not restart replication using old 3.23 binary logs, because this will unfortunately confuse the 4.0 slave. The upgrade can be safely done this way:
FLUSH TABLES WITH READ LOCK).
SHOW MASTER STATUS on the master, and SELECT
MASTER_POS_WAIT() on the slaves). Then run SLAVE STOP and
RESET SLAVE on the slaves.
CHANGE
MASTER TO commands on the slaves, unless the slaves already have the
identification of the master in their `my.cnf' files.
Starting from 4.0.0, one can use LOAD DATA FROM MASTER to set up
a slave. Be aware that LOAD DATA FROM MASTER currently works only
if all the tables on the master are MyISAM type, and will acquire a
global read lock, so no writes are possible while the tables are being
transferred from the master. When we implement hot lock-free table
backup (in MySQL 5.0), this global read lock will no longer be necessary.
Due to the above limitation, we recommend that at this point you use
LOAD DATA FROM MASTER only if the dataset on the master is relatively
small, or if a prolonged read lock on the master is acceptable. While the
actual speed of LOAD DATA FROM MASTER may vary from system to system,
a good rule for a rough estimate of how long it is going to take is 1 second
per 1 MB of the datafile. You will get close to the estimate if both master
and slave are equivalent to 700 MHz Pentium, are connected through
100 MBit/s network, and your index file is about half the size of your data
file. Of course, this is only a rough order of magnitude estimate.
Once a slave is properly configured and running, it will simply connect
to the master and wait for updates to process. If the master goes away
or the slave loses connectivity with your master, it will keep trying to
connect every master-connect-retry seconds until it is able to
reconnect and resume listening for updates.
Each slave keeps track of where it left off. The master server has no knowledge of how many slaves there are or which ones are up-to-date at any given time.
Three threads are involved in replication : one on the master and two
on the slave.
When START SLAVE is issued, the I/O thread is created on the
slave. It connects to the master and asks it to send its binlogs. Then
one thread (named Binlog_dump in SHOW PROCESSLIST on the
master) is created on the master to send these binlogs. The I/O thread
reads what Binlog_dump sends and simply copies it to some local
files in the slave's data directory called relay logs.
The last thread, the SQL thread, is created on the slave; it reads the
relay logs and executes the queries it contains.
Here is how the three threads show up in SHOW PROCESSLIST:
| 76 | root | localhost | NULL | Binlog Dump | 42 | Slave: waiting for binlog update | NULL |
| 7 | system user | | NULL | Connect | 3 | Reading master update | NULL | | 8 | system user | | NULL | Connect | 3 | Slave: waiting for binlog update | NULL |
Here thread 76 is on the master. Thread 7 is the I/O thread on the
slave.
Thread 8 the SQL thread on the slave; note that the value in the
Time column can tell how late the slave is compared to the
master (see section 4.10.8 Replication FAQ).
Before MySQL 4.0.2, the I/O and SQL threads were one. The advantage brought by the two separate threads is that it makes the reading job and the execution job independant, thus the reading job is not slowed down by the execution job. As soon as the slave starts, even if it has not been running for a while, the I/O thread can quickly fetch all the binlogs, while the SQL thread lags far behind and may take hours to catch. If the slave stops, though it has not executed everything yet, at least it has fetched everything, so binlogs can be purged on the master, as a safe copy is locally stored on the slave for future use.
Relay logs are by default named as the hostname followed
by -relay-bin plus a numeric extension. A `-relay-bin.index' file
contains the list of all relay logs currently in use.
By default these files are in the slave's data directory.
Relay logs have the same format than binary logs, so they can be read
with mysqlbinlog.
A relay log is automatically deleted by the SQL thread as soon as it
no longer needs it (that is, as soon as it has executed all its
events). The user or DBA has no command to delete relay logs or
even rotate them (FLUSH LOGS has no effect on relay logs),
as the SQL thread does the job.
A new relay log is created when the I/O thread starts and when the
size of the current relay log exceeds max_binlog_size.
Replication also creates two small files in the data directory:
these files are the disk images of the output of SHOW SLAVE
STATUS (see section 4.10.7 SQL Commands Related to Replication for a description of this command);
but as disk images they survive slave's shutdown; this way at restart
time the slave
still knows his master and where the slave is in the master's binlogs,
and where it is in its own relay logs.
SHOW SLAVE STATUS:
| Line# | Description |
| 1 | Master_Log_File
|
| 2 | Read_Master_Log_Pos
|
| 3 | Master_Host
|
| 4 | Master_User
|
| 5 | Password (not in SHOW SLAVE STATUS)
|
| 6 | Master_Port
|
| 7 | Connect_Retry
|
SHOW SLAVE STATUS:
| Line# | Description |
| 1 | Relay_Log_File
|
| 2 | Relay_Log_Pos
|
| 3 | Relay_Master_Log_File
|
| 4 | Exec_master_log_pos
|
Here is a quick description of how to set up complete replication on your current MySQL server. It assumes you want to replicate all your databases and have not configured replication before. You will need to shutdown your master server briefly to complete the steps outlined here.
While this method is the most straightforward way to set up a slave, it is not the only one. For example, if you already have a snapshot of the master, and the master already has server id set and binary logging enabled, you can set up a slave without shutting the master down or even blocking the updates. For more details, please see section 4.10.8 Replication FAQ.
If you want to be able to administrate a MySQL replication setup, we suggest that you read this entire chapter through and try all commands mentioned in section 4.10.7 SQL Commands Related to Replication. You should also familiarise yourself with replication startup options in `my.cnf' in section 4.10.6 Replication Options in `my.cnf'.
FILE
(in MySQL versions older than 4.0.2) or REPLICATION SLAVE
privilege in newer MySQL versions. You must also have given this user
permission to connect from all the slaves. If the user is only doing replication
(which is recommended), you don't need to grant any additional privileges.
For example, to create a user named repl which can access your
master from any host, you might use this command:
mysql> GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY '<password>'; # master < 4.0.2
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@"%" IDENTIFIED BY '<password>'; # master >= 4.0.2If you plan to use the
LOAD TABLE FROM MASTER or
LOAD DATA FROM MASTER commands, you will also need to grant the
REPLICATION CLIENT (or SUPER if the
master is older than 4.0.13) and RELOAD privileges on the
master to the above user.
FLUSH TABLES WITH READ LOCK command.
mysql> FLUSH TABLES WITH READ LOCK;and then take a snapshot of the data on your master server. The easiest way to do this is to simply use an archiving program (
tar on Unix, PowerArchiver, WinRAR,
WinZIP or any similar software on Windows) to
produce an archive of the databases in your master's data directory.
Include all the databases you want to replicate.
tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dirIf you want to replicate only a database called
this_db, you
can do just this:
tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir/this_dbYou may not want to replicate the
mysql database, then you can
exclude it from the archive too. Into the archive you needn't copy the
master's binary logs, error log,
`master.info' / `relay-log.info' / relay logs
(if the master is itself a slave of another machine). You can exclude
all this from the archive.
After or during the process of taking a snapshot, read the value of the
current binary log name and the offset on the master:
mysql > SHOW MASTER STATUS; +---------------+----------+--------------+-------------------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---------------+----------+--------------+-------------------------------+ | mysql-bin.003 | 73 | test,bar | foo,manual,sasha_likes_to_run | +---------------+----------+--------------+-------------------------------+ 1 row in set (0.06 sec)The
File column shows the name of the log, while Position shows
the offset. In the above example, the binary log value is
mysql-bin.003 and the offset is 73. Record the values - you will need
to use them later when you are setting up the slave.
Once you have taken the snapshot and recorded the log name and offset, you can
re-enable write activity on the master:
mysql> UNLOCK TABLES;If you are using InnoDB tables, ideally you should use the InnoDB Hot Backup tool that is available to those who purchase MySQL commercial licenses, support, or the backup tool itself. It will take a consistent snapshot without acquiring any locks on the master server, and record the log name and offset corresponding to the snapshot to be later used on the slave. More information about the tool is avalaible at http://www.innodb.com/hotbackup.html. Without the hot backup tool, the quickest way to take a snapshot of InnoDB tables is to shut the master server down and copy the InnoDB data files and logs, and the table definition files (
.frm). To record the current log file
name and offset, you should do the following before you shut down the server:
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS;And then record the log name and the offset from the output of
SHOW MASTER STATUS as was shown earlier. Once you have recorded the
log name and the offset, shut the server down without unlocking the tables to
make sure it goes down with the snapshot corresponding to the current log file
and offset:
shell> mysqladmin -uroot shutdownAn alternative for both MyISAM and InnoDB tables is taking an SQL dump of the master instead of a binary copy like above; for this you can use
mysqldump --master-data
on your master and later run this SQL dump into your slave. This is
however slower than doing a binary copy.
If the master has been previously running without log-bin enabled,
the values of log name and position displayed by SHOW MASTER
STATUS or mysqldump will be empty. In that case, record empty
string ('') for the log name, and 4 for the offset.
log-bin if it is not there already
and server-id=unique number in the [mysqld] section. If those
options are not present, add them and restart the server.
It is very important that the id of the slave is different from
the id of the master. Think of server-id as something similar
to the IP address - it uniquely identifies the server instance in the
community of replication partners.
[mysqld] log-bin server-id=1
server-id=<some unique number between 1 and 2^32-1 that is different from that of the master>replacing the values in <> with what is relevant to your system.
server-id must be different for each server participating in
replication. If you don't specify a server-id, it will be set to 1 if
you have not defined master-host, else it will be set to 2. Note
that in the case of server-id omission the master will refuse
connections from all slaves, and the slave will refuse to connect to a
master. Thus, omitting server-id is only good for backup with a
binary log.
skip-slave-start.
You may want to start the slave server with option
log-warnings, this way you will get more messages about
network/connection problems for example.
mysqldump into the
mysql). Make
sure that the privileges on the files and directories are correct. The
user which MySQL runs as needs to be able to read and write to
them, just as on the master.
mysql> CHANGE MASTER TO MASTER_HOST='<master host name>', MASTER_USER='<replication user name>', MASTER_PASSWORD='<replication password>', MASTER_LOG_FILE='<recorded log file name>', MASTER_LOG_POS=<recorded log offset>;replacing the values in <> with the actual values relevant to your system. The maximum string length for the above variables are:
| MASTER_HOST | 60 |
| MASTER_USER | 16 |
| MASTER_PASSWORD | 32 |
| MASTER_LOG_FILE | 255 |
mysql> START SLAVE;
After you have done the above, the slave(s) should connect to the master and catch up on any updates which happened since the snapshot was taken.
If you have forgotten to set server-id for the slave you will get
the following error in the error log file:
Warning: one should set server_id to a non-0 value if master_host is set. The server will not act as a slave.
If you have forgotten to do this for the master, the slaves will not be able to connect to the master.
If a slave is not able to replicate for any reason, you will find error messages in the error log on the slave.
Once a slave is replicating, you will find a file called
`master.info' and one called `relay-log.info'
in the data directory. These two files
are used by the slave to keep track of how much
of the master's binary log it has processed. Do not remove or
edit these files, unless you really know what you are doing. Even in that case,
it is preferred that you use CHANGE MASTER TO command.
NOTE: the content of `master.info' overrides some options specified on
the command-line or in `my.cnf' (see section 4.10.6 Replication Options in `my.cnf' for more details).
Now that you have a snapshot, you can use it to set up other slaves. To do so, follow the slave portion of the procedure described above. You do not need to take another snapshot of the master.
Here is an explanation of what is supported and what is not:
AUTO_INCREMENT,
LAST_INSERT_ID(), and TIMESTAMP values.
USER() and LOAD_FILE() functions
are replicated without changes and will thus not work reliably on the
slave. This is also true for CONNECTION_ID() in slave versions
strictly older than 4.1.1.
The new PASSWORD() function in MySQL 4.1, is well
replicated since 4.1.1 masters ; your slaves must be 4.1.0 or above
to replicate it. If you have older slaves and need to replicate
PASSWORD() from your 4.1.x master, you should start your master
with option --old-password.
sql_mode, FOREIGN_KEY_CHECKS and table_type
variables are not replicated.
--default-character-set)
on the master and the slave. If not, you may get duplicate key errors on
the slave, because a key that is regarded as unique in the master character
set may not be unique in the slave character set.
BEGIN/COMMIT block, as
the slave will later start at the beginning of the BEGIN block.
This issue is on our TODO and will be fixed in the near future.
FLUSH, ANALYZE, OPTIMIZE, REPAIR commands
are not stored in the binary log and are because
of this not replicated to the slaves. This is not normally a problem as
these commands don't change anything. This does however mean that if you
update the MySQL privilege tables directly without using the
GRANT statement and you replicate the mysql privilege
database, you must do a FLUSH PRIVILEGES on your slaves to put
the new privileges into effect. Also if you use
FLUSH TABLES when renaming a MyISAM table involved in a
MERGE table, you will have to issue FLUSH TABLES
manually on the slave.
Since MySQL 4.1.1, these commands are written to the binary log
(except FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE,
FLUSH TABLES WITH READ LOCK) unless you specify
NO_WRITE_TO_BINLOG (or its alias LOCAL)
(for a syntax example, see section 4.5.3 FLUSH Syntax).
STOP SLAVE, check Slave_open_temp_tables variable to see
if it is 0, if so issue mysqladmin shutdown. If the number is
not 0, restart the slave threads with START SLAVE and see if
you have better luck next time. We have plans to fix this in the near future.
log-slave-updates enabled.
Note, however, that many queries will not work right in this kind of
setup unless your client code is written to take care of the potential
problems that can happen from updates that occur in different sequence
on different servers.
This means that you can do a setup like the following:
A -> B -> C -> AThanks to server ids, which are encoded in the binary log events, A will know when the event it reads had originally been created by A, so A will not execute it and there will be no infinite loop. But this circular setup will only work if you only do non conflicting updates between the tables. In other words, if you insert data in A and C, you should never insert a row in A that may have a conflicting key with a row insert in C. You should also not update the same rows on two servers if the order in which the updates are applied matters.
START SLAVE.
master-connect-retry (default
60) seconds. Because of this, it is safe to shut down the master, and
then restart it after a while. The slave will also be able to deal with
network connectivity outages. However, the slave will notice the
network outage only after receiving no data from the master for
slave_net_timeout seconds. So if your outages are short, you may want
to decrease slave_net_timeout ; see section 4.5.7.4 SHOW VARIABLES.
slave-skip-errors option starting in Version 3.23.47.
BEGIN/COMMIT segment updates to the binary log may be out of sync
if some thread changes the non-transactional table before the
transaction commits. This is because the transaction is written to the
binary log only when it's commited.
COMMIT or not written at all if you use ROLLBACK. You
have to take this into account when updating both transactional tables
and non-transactional tables in the same transaction and you are using
binary logging for backups or replication.
The following table is about problems in 3.23 that are fixed in 4.0:
LOAD DATA INFILE will be handled properly as long as the file
still resides on the master server at the time of update
propagation.
LOAD LOCAL DATA INFILE will be skipped.
RAND() in updates does not replicate properly.
Use RAND(some_non_rand_expr) if you are replicating updates with
RAND(). You can, for example, use UNIX_TIMESTAMP() for the
argument to RAND(). This is fixed in 4.0.
On both master and slave you need to use the server-id option.
This sets a unique replication id. You should pick a unique value in the
range between 1 to 2^32-1 for each master and slave.
Example: server-id=3
The options you can use on the MASTER are all described there: see section 4.9.4 The Binary Log.
The following table describes the options you can use on the SLAVE. It is recommended to read the following paragraph; these options can help you customize replication to suit your needs.
NOTE: replication handles the following options :
in a special way. If no `master.info' file exists (replication
is used for the very first time or you have run RESET SLAVE
and shutdown/restarted the slave server), the slave uses values
specified on the command-line or in `my.cnf'.
But if `master.info' exists, the slave IGNORES
any values specified on the command-line or in `my.cnf',
and uses instead the values it reads from `master.info'.
For example, if you have
master-host=this_host
in your `my.cnf', are using replication, then want to replicate
from another host, modifying the above line in `my.cnf' will have
no effect. You must use CHANGE MASTER TO instead. This holds
true for master-host, master-user, master-password,
master-port, master-connect-retry.
Therefore, you may decide to put no master-* options in
`my.cnf' and instead use only CHANGE MASTER TO
(see section 4.10.7 SQL Commands Related to Replication).
| Option | Description |
log-slave-updates |
Tells the slave to log the updates done by the slave SQL thread to the
slave's binary log. Off by default.
Of course, it requires that the slave be started with binary
logging enabled (log-bin option).
You have to use log-slave-updates to
chain several slaves ; for example for the following setup to work
A -> B ->C(C is a slave of B which is a slave of A) you need to start B with the log-slave-updates option.
|
log-warnings | Makes the slave print more messages about what it is doing. For example, it will warn you that it succeeded in reconnecting after a network/connection failure, and warn you about how each slave thread started. |
master-host=host |
Master hostname or IP address for replication. If not set, the slave
thread will not be started. Note that the setting of master-host
will be ignored if there exists a valid `master.info' file. Probably a
better name for this options would have been something like
bootstrap-master-host, but it is too late to change now.
Example: master-host=db-master.mycompany.com
|
master-user=username |
The username the slave thread will use for authentication when connecting to
the master. The user must have the FILE privilege. If the master user
is not set, user test is assumed. The value in `master.info' will
take precedence if it can be read.
Example: master-user=scott
|
master-password=password |
The password the slave thread will authenticate with when connecting to
the master. If not set, an empty password is assumed.The value in
`master.info' will take precedence if it can be read.
Example: master-password=tiger
|
master-port=portnumber |
The port the master is listening on. If not set, the compiled setting of
MYSQL_PORT is assumed. If you have not tinkered with
configure options, this should be 3306. The value in
`master.info' will take precedence if it can be read.
Example: master-port=3306
|
master-connect-retry=seconds |
The number of seconds the slave thread will sleep before retrying to
connect to the master in case the master goes down or the connection is
lost. Default is 60. The value in `master.info' will
take precedence if it can be read.
Example: master-connect-retry=60
|
master-ssl |
Planned to enable the slave to connect to the master using SSL.
Does nothing yet!
Example: master-ssl
|
master-ssl-key=filename |
Master SSL keyfile name. Only applies if you have
enabled master-ssl. Does nothing yet.
Example: master-ssl-key=SSL/master-key.pem
|
master-ssl-cert=filename |
Master SSL certificate file name. Only applies if
you have enabled master-ssl. Does nothing yet.
Example: master-ssl-cert=SSL/master-cert.pem
|
master-ssl-capath |
Master SSL CA path. Only applies if
you have enabled master-ssl. Does nothing yet.
|
master-ssl-cipher |
Master SSL cipher. Only applies if
you have enabled master-ssl. Does nothing yet.
|
master-info-file=filename | To give `master.info' another name and/or to put it in another directory than the data directory. |
relay-log=filename |
To specify the location and name that should be used for relay logs.
You can use this to have hostname-independant relay log names, or if
your relay logs tend to be big (and you don't want to decrease
max_binlog_size) and you need to put them on some area
different from the data directory, or if you want to increase speed by
balancing load between disks.
|
relay-log-index=filename | To specify the location and name that should be used for the relay logs index file. |
relay-log-info-file=filename | To give `relay-log.info' another name and/or to put it in another directory than the data directory. |
relay-log-purge=0|1 |
Available since MySQL 4.1.1.
Disables/enables automatic purging of relay logs as soon as they are
not needed anymore. This is a global variable which can be dynamically
changed with SET GLOBAL RELAY_LOG_PURGE=0|1. The default value
is 1.
|
relay-log-space-limit=# |
To put an upper limit on the total size of all relay logs on the
slave (a value of 0 means ``unlimited''). This is useful
if you have a small hard disk on your slave machine. When the limit is
reached, the I/O thread pauses (does not read the master's binlog)
until the SQL thread has catched up and deleted some now unused relay
logs. Note that this limit is not absolute: there are cases where the
SQL thread needs more events to be able to delete; in that case the I/O
thread will overgo the limit until deletion becomes possible. Not
doing so would cause a deadlock (which happens before MySQL 4.0.13).
Users should not set relay-log-space-limit to less than twice
the value of max-binlog-size because in that case there are
chances that when the I/O thread waits for free space because
relay-log-space-limit is exceeded, the SQL thread has no relay
log to purge and so cannot satisfy the I/O thread, forcing the I/O
thread to temporarily ignore relay-log-space-limit.
|
replicate-do-table=db_name.table_name |
Tells the slave thread to restrict replication to the specified table.
To specify more than one table, use the directive multiple times, once
for each table. This will work for cross-database updates, in
contrast to replicate-do-db.
Example: replicate-do-table=some_db.some_table
|
replicate-ignore-table=db_name.table_name |
Tells the slave thread to not replicate any command that updates the
specified table (even if any other tables may be update by the same
command). To specify more than one table to ignore, use the directive
multiple times, once for each table. This will work for cross-database
updates, in contrast to replicate-ignore-db.
Example: replicate-ignore-table=db_name.some_table
|
replicate-wild-do-table=db_name.table_name |
Tells the slave thread to restrict replication to queries where any of
the updated tables match the specified wildcard pattern. To specify
more than one table, use the directive multiple times, once for each
table. This will work for cross-database updates.
Example: replicate-wild-do-table=foo%.bar% will replicate only
updates that uses a table in any databases that start with foo
and whose table names start with bar.
Note that if you do replicate-wild-do-table=foo%.% then the rule
will be propagated to CREATE DATABASE and DROP DATABASE,
that is, these two statements will be replicated if the database name
matches the database pattern ('foo%' here) (this magic is triggered by
'%' being the table pattern).
|
replicate-wild-ignore-table=db_name.table_name |
Tells the slave thread to not replicate a query where any table matches the
given wildcard pattern. To specify more than one table to ignore, use
the directive multiple times, once for each table. This will work for
cross-database updates.
Example: replicate-wild-ignore-table=foo%.bar% will not do updates
to tables in databases that start with foo and whose table names start
with bar.
Note that if you do replicate-wild-ignore-table=foo%.% then the
rule will be propagated to CREATE DATABASE and DROP
DATABASE, that is, these two statements will not be replicated if the
database name matches the database pattern ('foo%' here) (this magic is
triggered by '%' being the table pattern).
|
replicate-do-db=database_name |
Tells the slave to restrict replication to commands where
the current database (that is, the one selected by USE)
is database_name.
To specify more than one database, use the directive multiple
times, once for each database. Note that this will not replicate
cross-database queries such as UPDATE some_db.some_table
SET foo='bar' while having selected a different or no database. If you
need cross database updates to work, make sure you have 3.23.28 or
later, and use replicate-wild-do-table=db_name.%.
Example: replicate-do-db=some_db
Example of what does not work as you could expect it: if the slave is
started with replicate-do-db=sales, and you do
USE prices; UPDATE sales.january SET amount=amount+1000;,
this query will not be replicated.
If you need cross database updates to work,
use replicate-wild-do-table=db_name.% instead.
The main reason for this ``just-check-the-current-database''
behaviour is that it's hard from the command
alone to know if a query should be replicated or not ; for example if you
are using multi-table-delete or multi-table-update commands
that go across multiple databases. It's also very fast to just check
the current database.
|
replicate-ignore-db=database_name |
Tells the slave to not replicate any command where the current
database (that is, the one selected by USE)
is database_name. To specify more than one database to
ignore, use the directive multiple times, once for each database.
You should not use this directive if you are using cross table updates
and you don't want these update to be replicated.
Example: replicate-ignore-db=some_db
Example of what does not work as you could expect it: if the slave is
started with replicate-ignore-db=sales, and you do
USE prices; UPDATE sales.january SET amount=amount+1000;,
this query will be replicated.
If you need cross database updates to work,
use replicate-wild-ignore-table=db_name.% instead.
|
replicate-rewrite-db=from_name->to_name |
Tells the slave to translate the current database
(that is, the one selected by USE)
to to_name if it was from_name on the master.
Only statements involving tables may be affected
(CREATE DATABASE, DROP DATABASE won't),
and only if from_name was the current database on the master.
This will not work for cross-database updates.
Example: replicate-rewrite-db=master_db_name->slave_db_name
|
report-host=host |
Available after 4.0.0. Hostname or IP of the slave to be reported to
the master during slave registration. Will appear in the output of
SHOW SLAVE HOSTS. Leave unset if you do not want the slave to
register itself with the master. Note that it is not sufficient for the
master to simply read the IP of the slave off the socket once the slave
connects. Due to NAT and other routing issues, that IP may not be
valid for connecting to the slave from the master or other hosts. For
the moment this option has no real interest ; it is meant for failover
replication which is not implemented yet.
Example: report-host=slave1.mycompany.com
|
report-port=portnumber | Available after 4.0.0. Port for connecting to slave reported to the master during slave registration. Set it only if the slave is listening on a non-default port or if you have a special tunnel from the master or other clients to the slave. If not sure, leave this option unset. For the moment this option has no real interest ; it is meant for failover replication which is not implemented yet. |
skip-slave-start |
Tells the slave server not to start the slave threads on server startup. The user
can start them later with START SLAVE.
|
slave_compressed_protocol=# | If 1, then use compression on the slave/client protocol if both slave and master support this. |
slave-load-tmpdir=filename |
This option is by default equal to tmpdir.
When the SQL slave replicates a LOAD DATA INFILE command, it
extracts the to-be-loaded file from the relay log into temporary files,
then loads these into the table. If the file loaded on the master was
huge, the temporary files on the slave will be huge too; therefore you
may wish/have to tell the slave to put the temporary files on some
large disk different from tmpdir, using this option. In
that case, you may also use the relay-log option,
as relay logs will be huge too.
|
slave-net-timeout=# |
Number of seconds to wait for more data from the master before aborting
the read, considering the connection broken and retrying to connect,
first time immediately, then every master-connect-retry seconds.
|
slave-skip-errors= [err_code1,err_code2,... | all] |
Tells the slave SQL thread to continue
replication when a query returns an error from the provided
list. Normally, replication will discontinue when an error is
encountered, giving the user a chance to resolve the inconsistency in the
data manually. Do not use this option unless you fully understand why
you are getting the errors. If there are no bugs in your
replication setup and client programs, and no bugs in MySQL itself, you
should never get an abort with error. Indiscriminate use of this option
will result in slaves being hopelessly out of sync with the master and
you having no idea how the problem happened.
For error codes, you should use the numbers provided by the error message in
your slave error log and in the output of SHOW SLAVE STATUS. Full list
of error messages can be found in the source distribution in
`Docs/mysqld_error.txt'.
You can (but should not) also use a very non-recommended value of all
which will ignore all error messages and keep barging along regardless.
Needless to say, if you use it, we make no promises regarding your data
integrity. Please do not complain if your data on the slave is not anywhere
close to what it is on the master in this case -- you have been warned.
Example:
slave-skip-errors=1062,1053 or slave-skip-errors=all
|
Some of these options, like all replicate-* options, can only
be set at the slave server's startup, not on-the-fly. We plan to fix this.
Replication can be controlled through the SQL interface. Here is the summary of commands. Near each command you will find ``(Slave)'', meaning this command is issued on the slave, or ``Master'', meaning it is issued on the master.
START SLAVE (slave)
Starts the slave threads. Was called SLAVE START in MySQL 3.23.
As of MySQL 4.0.2, you can add IO_THREAD or SQL_THREAD
options to the statement to start only the I/O thread or the SQL thread.
The I/O thread reads queries from the master server and stores them in the
relay log. The SQL thread reads the relay log and executes the
queries.
Note that if START SLAVE succeeds in starting the slave threads it
will return without any error. But even in that case it might be that slave
threads start and then later stop (because they don't manage to
connect to the master or read his binlogs or any other
problem). START SLAVE will not warn you about this, you have to
check your slave's `.err' file for error messages generated by
the slave threads, or check that these are running fine with SHOW
SLAVE STATUS.
STOP SLAVE (slave)
Stops the slave threads. Was called SLAVE STOP in MySQL 3.23.
Like SLAVE START, this statement
may be used with IO_THREAD and SQL_THREAD options.
SET SQL_LOG_BIN=0|1 (master)
Disables/enables binary logging for the user's connection
(SQL_LOG_BIN is a session variable)
if the user has the SUPER privilege.
Ignored otherwise.
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n (slave)
Skip the next n events from the master. Only valid when
the slave thread is not running, otherwise, gives an error. Useful for
recovering from replication stops caused by a statement.
RESET MASTER (master)
Deletes all binary logs listed in the index file, resetting the binlog
index file to be empty. Previously named FLUSH MASTER.
RESET SLAVE (slave)
Makes the slave forget its replication position in the master's binlogs,
deletes the `master.info' and
`relay-log.info' files, all relay logs, starts a new relay log.
Connection information (master-host et al.) is not cleared in
memory and
will be reused if you START SLAVE later. But if you shutdown
the slave server between RESET SLAVE and START SLAVE,
then the connection information in memory will be lost and reread from
the command-line or `my.cnf'. Previously named FLUSH SLAVE.
LOAD TABLE tblname FROM MASTER (slave)
Downloads a copy of the table from master to the slave. Implemented
mainly for debugging of LOAD DATA FROM MASTER.
Requires that the replication user which is used to connect to the master has
RELOAD and SUPER privileges on the master.
Please read the timeout notes in the description of LOAD DATA
FROM MASTER below, they apply here too.
LOAD DATA FROM MASTER (slave)
Takes a snapshot of the master and copies
it to the slave.
Requires that the replication user which is used to connect to the master has
RELOAD and SUPER privileges on the master.
Updates the values of MASTER_LOG_FILE and
MASTER_LOG_POS so that the slave will start replicating from the
correct position. Will honor table and database exclusion rules
specified with replicate-* options. So far works only with
MyISAM tables and acquires a global read lock on the master while
taking the snapshot. In the future it is planned to make it work with
InnoDB tables and to remove the need for global read lock using
the non-blocking online backup feature.
If you are loading big tables, you may have to increase the values
of net_read_timeout and net_write_timeout
on both your master and slave ; see section 4.5.7.4 SHOW VARIABLES.
Note that LOAD DATA FROM MASTER does NOT copy any
tables from the mysql database. This is to make it easy to have
different users and privileges on the master and the slave.
CHANGE MASTER TO master_def_list (slave)
Changes the master parameters (connection information)
to the values specified in master_def_list. master_def_list
is a comma-separated list of master_def where master_def is
one of the following: MASTER_HOST, MASTER_USER,
MASTER_PASSWORD, MASTER_PORT, MASTER_CONNECT_RETRY,
MASTER_LOG_FILE, MASTER_LOG_POS,
RELAY_LOG_FILE, RELAY_LOG_POS (these last two only
starting from MySQL 4.0).
For example:
CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10;
CHANGE MASTER TO RELAY_LOG_FILE='slave-relay-bin.006', RELAY_LOG_POS=4025;
You only need to specify the values that need to be changed. The values that you omit will stay the same with the exception of when you specify (not necessarily change) the host or port. In that case, the slave will assume that the master is different. Therefore, the old values of log and position are no longer applicable and will automatically be reset to an empty string and 0, respectively (the start values).
This command is useful for setting up a slave when you have the snapshot of
the master and have recorded the log and the offset on the master that the
snapshot corresponds to. You can run
CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master',
MASTER_LOG_POS=log_offset_on_master on the slave after restoring the
snapshot.
CHANGE MASTER TO deletes all relay logs and starts
a new one, unless you specified RELAY_LOG_FILE or
RELAY_LOG_POS (in that case relay logs will be kept;
since MySQL 4.1.1 the RELAY_LOG_PURGE global variable
will silently be set to 0).
CHANGE MASTER TO updates `master.info' and `relay-log.info'.
The first example above changes the master and master's binlog
coordinates. This is when you want the slave to replicate the master.
The second example, less frequently used, is when the slave has relay logs which, for some
reason, you want the slave to execute again; to do this the master
needn't be reachable, you just have to do CHANGE MASTER TO
and start the SQL thread (START SLAVE SQL_THREAD).
You can even use this out of a replication setup, on a standalone,
slave-of-nobody server, to recover after a crash.
Suppose your server has crashed and you have restored a backup.
You want to replay the server's own binlogs (not relay logs, but regular binary
logs), supposedly named `myhost-bin.*'. First make a backup copy of
these binlogs in some safe place, in case you don't exactly follow the
procedure below and accidentally have the server purge the binlogs.
If using MySQL 4.1.1 or newer, do SET GLOBAL RELAY_LOG_PURGE=0 for additional safety.
Then start the server without log-bin, with a new
(different from before) server id, with relay-log=myhost-bin
(to make the server believe that these regular binlogs are relay
logs) and skip-slave-start,
then issue
CHANGE MASTER TO RELAY_LOG_FILE='myhost-bin.153',RELAY_LOG_POS=410, MASTER_HOST='some_dummy_string'; START SLAVE SQL_THREAD;
Then the server will read and execute its own binlogs, thus achieving
crash recovery.
Once the recovery is finished, run STOP SLAVE, shutdown the
server, delete `master.info' and `relay-log.info',
and restart the server with its original options.
For the moment, specifying MASTER_HOST (even with a dummy value) is compulsory
to make the server think he is a slave, and giving the server a new,
different from before, server id is also compulsory otherwise the
server will see events with its id and think it is in a circular
replication setup and skip the events, which is unwanted. In the
future we plan to add options to get rid of these small constraints.
MASTER_POS_WAIT() (slave)This is not a command but a function, used to ensure that the slave has reached (read and executed up to) a given position in the master's binlog; see section 6.3.6.2 Miscellaneous Functions for a full description.
SHOW MASTER STATUS (master)Provides status information on the binlog of the master.
SHOW SLAVE HOSTS (master)Gives a listing of slaves currently registered with the master.
SHOW SLAVE STATUS (slave)
Provides status information on
essential parameters of the slave threads (Slave). If you type it in the
mysql client, you can put a \G instead of a semi-colon
at the end, to get a vertical, more readable layout:
SLAVE> show slave status\G
*************************** 1. row ***************************
Master_Host: localhost
Master_User: root
Master_Port: 3306
Connect_retry: 3
Master_Log_File: gbichot-bin.005
Read_Master_Log_Pos: 79
Relay_Log_File: gbichot-relay-bin.005
Relay_Log_Pos: 548
Relay_Master_Log_File: gbichot-bin.005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_do_db:
Replicate_ignore_db:
Last_errno: 0
Last_error:
Skip_counter: 0
Exec_master_log_pos: 79
Relay_log_space: 552
1 row in set (0.00 sec)
Master_Host
the current master host.
Master_User
the current user used to connect to the master.
Master_Port
the current master port.
Connect_Retry
the current value of master-connect-retry.
Master_Log_File
the master's binlog in which the I/O thread is currently reading.
Read_Master_Log_Pos
the position which the I/O thread has read up to in this master's binlog.
Relay_Log_File
the relay log which the SQL thread is currently reading and executing.
Relay_Log_Pos
the position which the SQL thread has read and executed up to in this relay log.
Relay_Master_Log_File
the master's binlog which contains the
last event executed by the SQL thread.
Slave_IO_Running
tells whether the I/O thread is started or not.
Slave_SQL_Running
tells whether the SQL thread is started or not.
Replicate_do_db / Replicate_ignore_db
the lists of the databases which have been specified with option
replicate-do-db / replicate-ignore-db;
starting from version 4.1, options replicate_*_table are also
displayed in four more columns.
Last_errno
the error number returned by the lastly executed query (should be 0).
Last_error
the error message returned by the lastly executed query (should be
empty); if not empty, you will find this message in the slave's error
log too. For example:
Last_errno: 1051 Last_error: error 'Unknown table 'z'' on query 'drop table z'Here the table 'z' existed on the master and was dropped there, but it did not exist on the slave (the user had forgotten to copy it to the slave when setting the slave up), so
DROP TABLE failed on the slave.
Skip_counter
the last used value for SQL_SLAVE_SKIP_COUNTER.
Exec_master_log_pos
the position in the master's binlog (Relay_Master_Log_File)
of the last event executed by the SQL thread.
((Relay_Master_Log_File,Exec_master_log_pos) in the
master's binlog corresponds to
(Relay_Log_File,Relay_Log_Pos)
in the relay log).
Relay_log_space
the total size of all existing relay logs.
SHOW MASTER LOGS (master)
Lists the binary logs on the master. You should use this
command prior to PURGE [MASTER] LOGS to find out how far you
should go.
SHOW BINLOG EVENTS (master)
SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ]
Shows the events in the binary log.
If you do not specify 'logname', the first binary log will be displayed.
SHOW NEW MASTER FOR SLAVE (slave)
SHOW NEW MASTER FOR SLAVE
WITH MASTER_LOG_FILE='logfile' AND MASTER_LOG_POS=pos AND
MASTER_LOG_SEQ=log_seq AND MASTER_SERVER_ID=server_id
This command is used when a slave of a possibly dead/unavailable master
needs to be switched to replicate off another slave that has been
replicating the same master. The command will return recalculated
replication coordinates (the slave's current binary log file
name and position within that file). The output can be used in a subsequent
CHANGE MASTER TO command. Normal users should never need to run
this command. It is primarily reserved for internal use by the fail-safe
replication code. We may later change the syntax if we find a more
intuitive way to describe this operation.
PURGE [MASTER] LOGS (master)
PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date'
The BEFORE variant is available in MySQL 4.1; its date argument
can be in format 'YYYY-MM-DD HH:MI:SS'.
MASTER is a useless keyword which can be specified or not.
Deletes all the
binary logs that are listed in the log
index as being strictly prior to the specified log or date, and
removes them from the
log index, so that the given log now becomes the first.
Example:
PURGE LOGS TO 'mysql-bin.010' ; PURGE LOGS BEFORE '2003-04-02 22:46:26' ;
This command will do nothing and fail with an error if you have an active slave that is currently reading one of the logs you are trying to delete. However, if you have a dormant slave, and happen to purge one of the logs it wants to read, the slave will be unable to replicate once it comes up. The command is safe to run while slaves are replicating -- you do not need to stop them.
You must first check all the slaves with SHOW SLAVE STATUS to
see which log they are on, then do a listing of the logs on the
master with SHOW MASTER LOGS, find the earliest log among all
the slaves (if all the slaves are up to date, this will be the
last log on the list), backup all the logs you are about to delete
(optional) and purge up to the target log.
Q: How do I configure a slave if the master is already running and I do not want to stop it?
A: There are several options. If you have taken a backup of the
master at some point and recorded the binlog name and offset ( from the
output of SHOW MASTER STATUS ) corresponding to the snapshot, do
the following:
CHANGE MASTER TO MASTER_HOST='master-host-name',
MASTER_USER='master-user-name', MASTER_PASSWORD='master-pass',
MASTER_LOG_FILE='recorded-log-name', MASTER_LOG_POS=recorded_log_pos
SLAVE START
If you do not have a backup of the master already, here is a quick way to do it consistently:
FLUSH TABLES WITH READ LOCK
gtar zcf /tmp/backup.tar.gz /var/lib/mysql ( or a variation of this)
SHOW MASTER STATUS - make sure to record the output - you will need it
later
UNLOCK TABLES
An alternative is taking an SQL dump of the master instead of a binary
copy like above; for this you can use mysqldump --master-data
on your master and later run this SQL dump into your slave. This is
however slower than doing a binary copy.
No matter which of the two ways you used, afterwards follow the instructions for the case when you have a snapshot and have recorded the log name and offset. You can use the same snapshot to set up several slaves. As long as the binary logs of the master are left intact, you can wait as long as several days or in some cases maybe a month to set up a slave once you have the snapshot of the master. In theory the waiting gap can be infinite. The two practical limitations is the diskspace of the master getting filled with old logs, and the amount of time it will take the slave to catch up.
You can also use LOAD DATA FROM
MASTER. This is a convenient command that will take a snapshot,
restore it to the slave, and adjust the log name and offset on the slave
all at once. In the future, LOAD DATA FROM MASTER will be the
recommended way to set up a slave. Be warned, howerver, that the read
lock may be held for a long time if you use this command. It is not yet
implemented as efficiently as we would like to have it. If you have
large tables, the preferred method at this time is still with a local
tar snapshot after executing FLUSH TABLES WITH READ LOCK.
Q: Does the slave need to be connected to the master all the time?
A: No, it does not. You can have the slave go down or stay disconnected for hours or even days, then reconnect, catch up on the updates, and then disconnect or go down for a while again. So you can, for example, use master-slave setup over a dial-up link that is up only for short periods of time. The implications of that are that at any given time the slave is not guaranteed to be in sync with the master unless you take some special measures. In the future, we will have the option to block the master until at least one slave is in sync.
Q: How do I know how late the slave is compared to the master? In other words, how do I know the date of the last query replicated by the slave?
A: This is possible only if the SQL slave thread exists
(that is, if it shows up in SHOW PROCESSLIST, see section 4.10.3 Replication Implementation Details)
(in MySQL 3.23: if the slave thread exists, that is, shows up in
SHOW PROCESSLIST),
and if it has executed at least one event
from the master. Indeed, when the SQL slave thread executes an event
read from the master, this thread modifies its own time to the event's
timestamp (this is why TIMESTAMP is well replicated). So in the
Time column in the output of SHOW PROCESSLIST, the
number of seconds displayed for the SQL slave thread is the number of
seconds between the timestamp of the last replicated event and the
real time of the slave machine. You can use this to determine the date
of the last replicated event. Note that if your slave has been
disconnected from the master for one hour, then reconnects,
you may immediately see Time values like 3600 for the SQL slave
thread in SHOW PROCESSLIST... This would be because the slave
is executing queries that are one hour old.
Q: How do I force the master to block updates until the slave catches up?
A: Execute the following commands:
FLUSH TABLES WITH READ LOCK
SHOW MASTER STATUS - record the log name and the offset
SELECT MASTER_POS_WAIT('recorded_log_name', recorded_log_offset)
When the select returns, the slave is currently in sync with the master
UNLOCK TABLES - now the master will continue updates.
Q: What issues should I be aware of when setting up two-way replication?
A: MySQL replication currently does not support any locking protocol between master and slave to guarantee the atomicity of a distributed (cross-server) update. In other words, it is possible for client A to make an update to co-master 1, and in the meantime, before it propagates to co-master 2, client B could make an update to co-master 2 that will make the update of client A work differently than it did on co-master 1. Thus when the update of client A will make it to co-master 2, it will produce tables that will be different from what you have on co-master 1, even after all the updates from co-master 2 have also propagated. So you should not co-chain two servers in a two-way replication relationship, unless you are sure that you updates can safely happen in any order, or unless you take care of mis-ordered updates somehow in the client code.
You must also realise that two-way replication actually does not improve performance very much, if at all, as far as updates are concerned. Both servers need to do the same amount of updates each, as you would have one server do. The only difference is that there will be a little less lock contention, because the updates originating on another server will be serialised in one slave thread. This benefit, though, might be offset by network delays.
Q: How can I use replication to improve performance of my system?
A: You should set up one server as the master, and direct all
writes to it, and configure as many slaves as you have the money and
rackspace for, distributing the reads among the master and the slaves.
You can also start the slaves with --skip-bdb,
--low-priority-updates and --delay-key-write=ALL
to get speed improvements for the slave. In this case the slave will
use non-transactional MyISAM tables instead of BDB tables
to get more speed.
Q: What should I do to prepare my client code to use performance-enhancing replication?
A: If the part of your code that is responsible for database access has been properly abstracted/modularised, converting it to run with the replicated setup should be very smooth and easy -- just change the implementation of your database access to read from some slave or the master, and to always write to the master. If your code does not have this level of abstraction, setting up a replicated system will give you an opportunity/motivation to it clean up. You should start by creating a wrapper library /module with the following functions:
safe_writer_connect()
safe_reader_connect()
safe_reader_query()
safe_writer_query()
safe_ means that the function will take care of handling all
the error conditions.
You should then convert your client code to use the wrapper library.
It may be a painful and scary process at first, but it will pay off in
the long run. All applications that follow the above pattern will be
able to take advantage of one-master/many slaves solution. The
code will be a lot easier to maintain, and adding troubleshooting
options will be trivial. You will just need to modify one or two
functions, for example, to log how long each query took, or which
query, among your many thousands, gave you an error. If you have
written a lot of code already, you may want to automate the conversion
task by using Monty's replace utility, which comes with the
standard distribution of MySQL, or just write your own Perl script.
Hopefully, your code follows some recognisable pattern. If not, then
you are probably better off rewriting it anyway, or at least going
through and manually beating it into a pattern.
Note that, of course, you can use different names for the functions. What is important is having unified interface for connecting for reads, connecting for writes, doing a read, and doing a write.
Q: When and how much can MySQL replication improve the performance of my system?
A: MySQL replication is most beneficial for a system with frequent reads and not so frequent writes. In theory, by using a one master/many slaves setup you can scale by adding more slaves until you either run out of network bandwidth, or your update load grows to the point that the master cannot handle it.
In order to determine how many slaves you can get before the added
benefits begin to level out, and how much you can improve performance
of your site, you need to know your query patterns, and empirically
(by benchmarking) determine the relationship between the throughput
on reads (reads per second, or max_reads) and on writes
max_writes) on a typical master and a typical slave. The
example here will show you a rather simplified calculation of what you
can get with replication for our imagined system.
Let's say our system load consists of 10% writes and 90% reads, and we
have determined that max_reads = 1200 - 2 * max_writes,
or in other words, our system can do 1200 reads per second with no
writes, our average write is twice as slow as average read,
and the relationship is
linear. Let us suppose that our master and slave are of the same
capacity, and we have N slaves and 1 master. Then we have for each
server (master or slave):
reads = 1200 - 2 * writes (from bencmarks)
reads = 9* writes / (N + 1) (reads split, but writes go
to all servers)
9*writes/(N+1) + 2 * writes = 1200
writes = 1200/(2 + 9/(N+1)
So if N = 0, which means we have no replication, our system can handle 1200/11, about 109 writes per second (which means we will have 9 times as many reads due to the nature of our application).
If N = 1, we can get up to 184 writes per second.
If N = 8, we get up to 400.
If N = 17, 480 writes.
Eventually as N approaches infinity (and our budget negative infinity), we can get very close to 600 writes per second, increasing system throughput about 5.5 times. However, with only 8 servers, we increased it almost 4 times already.
Note that our computations assumed infinite network bandwidth, and neglected several other factors that could turn out to be significant on your system. In many cases, you may not be able to make a computation similar to the one above that will accurately predict what will happen on your system if you add N replication slaves. However, answering the following questions should help you decided whether and how much, if at all, the replication will improve the performance of your system:
Q: How can I use replication to provide redundancy/high availability?
A: With the currently available features, you would have to set up a master and a slave (or several slaves), and write a script that will monitor the master to see if it is up, and instruct your applications and the slaves of the master change in case of failure. Some suggestions:
CHANGE MASTER TO command.
bind you can use `nsupdate' to dynamically update your DNS.
log-bin option and without
log-slave-updates. This way the slave will be ready to become a
master as soon as you issue STOP SLAVE; RESET MASTER, and
CHANGE MASTER TO on the other slaves.
We are currently working on integrating an automatic master election system into MySQL, but until it is ready, you will have to create your own monitoring tools.
If you have followed the instructions, and your replication setup is not working, first check the following:
SHOW MASTER STATUS.
If it is, Position will be non-zero. If not, verify that you have
given the master log-bin option and have set server-id.
SHOW SLAVE STATUS and check that the
Slave_IO_Running and Slave_SQL_Running are both ``Yes''.
If not, verify slave options
SHOW PROCESSLIST, find the I/O and SQL threads
(see section 4.10.3 Replication Implementation Details to see how they display),
and check their
State column. If it says connecting to master, verify the
privileges for the replication user on the master, master host name, your
DNS setup, whether the master is actually running, whether it is reachable
from the slave.
SLAVE START.
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; to skip a query that
does not use AUTO_INCREMENT or LAST_INSERT_ID(), or
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; SLAVE START; otherwise. The reason
queries that use AUTO_INCREMENT or LAST_INSERT_ID()
are different is that they take two events in the binary log of the master.
When you have determined that there is no user error involved, and replication still either does not work at all or is unstable, it is time to send us a bug report. We need to get as much info as possible from you to be able to track down the bug. Please do spend some time and effort preparing a good bug report.
If you have a repeatable way to demonstrate the bug, use
mysqlbug to prepare a bug report and enter it into our bugs database
at http://bugs.mysql.com/. If you have a phantom -- a problem that
does occur but you cannot duplicate "at will" -- fortunately this
rarely happens:
log-slave-updates and log-bin -- this will keep
a log of all updates on the slave.
SHOW MASTER STATUS on the master at the time
you have discovered the problem
SHOW SLAVE STATUS on the master at the time
you have discovered the problem
mysqlbinlog to examine the binary logs. The following should
be helpful
to find the trouble query, for example:
mysqlbinlog -j pos_from_slave_status /path/to/log_from_slave_status | head
Once you have collected the evidence on the phantom problem, try hard to isolate it into a separate test case first. Then enter the problem into our bugs database at http://bugs.mysql.com/ with as much information as possible.
Go to the first, previous, next, last section, table of contents.