This chapter covers topics that deal with administering a MySQL installation, such as configuring the server, managing user accounts, and performing backups.
The MySQL server, mysqld, is the main program that does most of the
work in a MySQL installation. The server is accompanied by several related
scripts that perform setup operations when you install MySQL or that are
helper programs to assist you in starting and stopping the server.
All MySQL programs take many different options. However, every
MySQL program provides a --help option that you can use
to get a description of the program's options. For example, try
mysqld --help.
You can override default options for all standard programs by specifying options on the command line or in an option file. section 4.3 Specifying Program Options.
The following list briefly describes the server-related MySQL programs:
mysqld
mysqld-max
mysqld_safe
mysqld_safe attempts to start mysqld-max if it exists, and
mysqld otherwise.
mysql.server
mysqld_safe to start the MySQL server.
mysqld_multi
mysql_install_db
mysql_fix_privilege_tables
There are several other programs that also are run on the server host:
myisamchk
myisamchk is described in
section 5.5.2 Using myisamchk for Table Maintenance and Crash Recovery.
make_binary_distribution
support.mysql.com for the
convenience of other MySQL users.
mysqlbug
mysqld-max, An Extended mysqld Server
A MySQL-Max server is a version of the mysqld MySQL server that is
configured to include additional features.
You can find the MySQL-Max binaries at http://www.mysql.com/downloads/mysql-max-4.0.html.
The MySQL binary distributions Windows include both the
standard server (named mysqld.exe) and the MySQL-Max server (named
mysqld-max.exe). http://www.mysql.com/downloads/mysql-4.0.html.
See section 2.2.1 Installing MySQL on Windows.
If you install MySQL on Linux using RPM distributions, install the
MySQL-server RPM first, and then the MySQL-Max RPM. The
latter presupposes that you have already installed the regular server RPM.
This process installs a standard server named mysqld and a MySQL-Max
server named mysqld-max.
All other MySQL-Max distributions contain a single server that is named
mysqld but that has the additional features.
MySQL-Max servers are built by using the following configure options:
| Option | Comment |
--with-server-suffix=-max | Add a -max suffix to the mysqld version string
|
--with-innodb | Support for InnoDB tables (MySQL 3.23 only)
|
--with-bdb | Support for Berkeley DB (BDB) tables
|
CFLAGS=-DUSE_SYMDIR | Symbolic link support for Windows |
MySQL-Max binary distributions are a convenience for those who wish to install precompiled programs. If you build MySQL using a source distribution, you can build your own Max-like server by enabling the same features at configuration time that the MySQL-Max binary distributions are built with.
MySQL-Max servers always include the InnoDB storage engine.
The --with-innodb option for enabling InnoDB support
is needed only in MySQL 3.23. (In MySQL 4 and up, InnoDB is included by
default. so you do not need a MySQL-Max server to obtain
InnoDB support.)
MySQL-Max servers include the BerkeleyDB (BDB) storage engine
whenever possible, but not all platforms support BDB. The following
table shows which platforms allow MySQL-Max binaries to include BDB:
| System | BDB
|
| Windows/NT | Y |
| AIX 4.3 | N |
| HP-UX 11.0 | N |
| Linux-Alpha | N |
| Linux-Intel | Y |
| Linux-IA-64 | N |
| Solaris-Intel | N |
| Solaris-SPARC | Y |
| SCO OSR5 | Y |
| UnixWare | Y |
| Mac OS X | N |
As of Version 3.23, all MySQL servers support MyISAM tables, because
MyISAM is the default storage engine. To find out which storage engines
your server supports, issue the following statement:
mysql> SHOW VARIABLES LIKE "have_%"; +------------------+----------+ | Variable_name | Value | +------------------+----------+ | have_bdb | NO | | have_crypt | YES | | have_innodb | YES | | have_isam | NO | | have_raid | NO | | have_symlink | DISABLED | | have_openssl | NO | | have_query_cache | YES | +------------------+----------+
The values in the second column indicate the server's level of support for each feature:
| Value | Meaning |
YES | The feature is supported and is active. |
NO | The feature is not supported. |
DISABLED | The feature is supported but has been disabled. |
A value of NO means that the server was compiled without support
for the feature, so it cannot be activated at runtime.
A value of DISABLED occurs either because the server was
started with an option that disables the feature, or because not
all options required to enable it were given. In the latter case, the
hostname.err file should contain a reason indicating why the option
is disabled.
One situation in which you might see DISABLED occurs with MySQL 3.23
when the InnoDB storage engine is compiled in. In MySQL 3.23, you
must supply at least the innodb_data_file_path option at runtime to
set up the InnoDB tablespace. Without the options, InnoDB
disables itself.
See section 14.4.2 InnoDB in MySQL Version 3.23.
(You can specify configuration options for the BDB storage engine, too,
but BDB will not disable itself without them.
See section 14.5.3 BDB Startup Options.)
You may also see DISABLED for the InnoDB, BDB, or
ISAM storage engines if the server was compiled to support them, but
was started with the --skip-innodb, --skip-bdb, or
--skip-isam options at runtime.
mysqld_safe, The Wrapper Around mysqld
mysqld_safe is the recommended way to start a mysqld
server 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.
On Linux, the MySQL-Max RPM uses this mysqld_safe
feature. (It just installs the mysqld-max executable, so
mysqld_safe automatically uses this executable when
mysqld_safe is restarted.)
The preference of mysqld_safe for mysqld-max over
mysqld makes it very easy to test a new 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 existing mysqld binary is located.
On the other hand, this behavior means that if you install a MySQL-Max
distribution that includes a server named mysqld-max, then upgrade
later to a non-Max version of MySQL, mysqld_safe will still
attempt to run the old mysqld-max server. If you perform such an
upgrade, manually remove the old mysqld-max server to ensure that
mysqld_safe runs the new mysqld server.
Normally, you should never edit the mysqld_safe script. Instead,
put the options to mysqld_safe in the [mysqld_safe] section
in a `my.cnf' option file. See section 4.3.2 Using Option Files. 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, though you
should rename such sections to [mysqld_safe] once you begin using
MySQL 4.0 or later.)
Note that all options specified to mysqld_safe on the command-line
are passed to mysqld. If you wants to use any options for
mysqld_safe that mysqld doesn't support, you must specify
them in the option file.
Many of the options to mysqld_safe are the same as the options to
mysqld. See section 5.2.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
--log-error option, to be used before MySQL 4.0.
--log-error=path
--ledir=path
mysqld program.
Use this option to explicitly indicate the location of the server.
--log=path
--mysqld=prog_name
ledir directory) that you
want to start.
--mysqld-version=suffix
--mysqld= but here you only give the suffix for the
server program name. The base name is assumed to be mysqld.
For example, if you use --mysqld-version=max, mysqld_safe
will start the mysqld-max program in the ledir directory.
If the argument to --mysqld-version is empty, mysqld
in the ledir directory is used.
--nice=#
nice program to set the server's scheduling priority to the
given value. This option was 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 distribution of
MySQL, even those these normally install the server in slightly different
locations.
See section 2.1.8 Installation Layouts.
mysqld_safe expects one of the following 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 from the values configured into the
distribution at the time it was built. They should be correct if MySQL
is installed in the location specified at configuration time. @end itemize
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 specify the --ledir and
--datadir options to indicate the directories in which the server and
databases are located on your system.
In rare cases, it may be necessary to edit mysqld_safe to get it to
start the server properly. If you do this, 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.
mysql.server, A Server Startup Script for Run Directories
MySQL distributions on Unix include a script named mysql.server.
It can be used on systems such as Linux and Solaris that use System V-style
run directories to start and stop system services.
On Linux systems, the server RPM distribution installs mysql.server
in `/etc/init.d' automatically under the name mysql.
If you install MySQL on Linux using another distribution format,
or on a System V-style system, you can install the script manually by
copying it to the `/etc/init.d' directory with the name mysql.
Make sure the script is executable. (Use chmod +x mysql.)
The commands needed to activate the script depend on your operating system.
On Linux, you can use chkconfig:
shell> chkconfig --add mysql
For other systems, consult your operating system documentation to see how to install startup scripts.
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 socket files and
TCP/IP ports. It can start or stop servers, or report their current status.
The program searches for groups named [mysqld#] in
`my.cnf' (or in the file named by the --config-file=... option on
the command line).
# can be any positive integer. 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 the status of. Options listed in these groups
are the same as you would use in the usual [mysqld]
group used for starting mysqld. (See, for example, section 2.4.2.2 Starting and Stopping MySQL Automatically.) However, when using multiple servers it is necessary that each one
use its own value for options such as the Unix socket file and TCP/IP port
number. For more information on which options should be specified in a
multiple-server environment, see section 5.8 Running Multiple MySQL Servers on the Same Machine.
To invoke mysqld_multi, use the following syntax:
shell> mysqld_multi [OPTIONS] {start|stop|report} [GNR[,GNR]...]
start, stop, and report indicate the operation you want
to perform. You can perform the operation on a single server or multiple
servers, depending on the GNR list that follows the option name.
If there is no list, mysqld_multi performs the operation for all
servers in the option file.
Each GNR value represents an option group number or range of group numbers.
The value should be the number at the end of the group name in the
option file. For example, the GNR for a group named [mysqld17]
is 17. To specify a range of numbers, separate the first and last
numbers by a dash. The GNR value 10-13 represents groups [mysqld10]
through [mysqld13]. Multiple groups or group ranges can be specified
on the command line, separated by commas. There must be no whitespace
characters (spaces or tabs) in the GNR list. (Anything after a whitespace
character is ignored.)
This command starts a single server using option group [mysqld17]:
shell> mysqld_multi start 17
This command stops serveral servers, using option groups [mysql8]
and [mysqld10] through [mysqld13]:
shell> mysqld_multi start 8,10-13
For an example of how you might set up an option file, use this command:
shell> mysqld_multi --example
mysqld_multi supports the following options:
--config-file=...
mysqld_multi looks for [mysqld#] option groups. It does not
affect where mysqld_multi reads its own options, which are always
taken from the [mysqld_multi] group in the usual `my.cnf' file.
Without this option, all options are read from the usual `my.cnf' file.
--example
--help
--log=...
--mysqladmin=...
mysqladmin binary to be used to stop servers.
--mysqld=...
mysqld binary to be used. Note that you can specify
mysqld_safe as the value for this option also. The options are passed
to mysqld. Just make sure you have the directory where mysqld is
located in your PATH
environment variable setting or fix mysqld_safe.
--no-log
--password=...
mysqladmin.
--tcp-ip
stop and
report operations.
--user=...
mysqladmin.
--version
Some notes about mysqld_multi:
mysqld services (using the mysqladmin program) has 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 5.3.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. 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 $MY_PWD/data/mysql -a -f ./share/mysql/english/errmsg.sys -a \ -x ./bin/mysqld --------------------------------------------------------------------------See section 5.1.3
mysqld_safe, The Wrapper Around mysqld.
The above test should be successful, or you may encounter problems.
mysqld servers with the same data
directory. Use separate data directories, unless you know what
you are doing!
See section 5.8 Running Multiple MySQL Servers on the Same Machine.
mysqld.
--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!
mysqld_multi be sure that you
understand the meanings of
the options that are passed to the mysqld servers and why you
would want to have separate mysqld processes. Starting multiple
servers with the same data directory will not give you extra
performance in a threaded system!
See section 5.8 Running Multiple MySQL Servers on the Same Machine.
The following example shows how you might set up an option file for use with
mysqld_multi.
The first and fifth [mysqld#] group were intentionally left out from
the example. You may have ``gaps'' in the option file. This gives you
more flexibility. The order in which the mysqld programs are started or
stopped depends on the order in which they appear in the option file.
# 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.3.2 Using Option Files.
This section discusses MySQL server configuration topics:
mysqld Command-line Options
When you start the mysqld server, you specify program options
using any of the methods described in section 4.3 Specifying Program Options. The most
common methods are to provide options in an option file or on the command
line. However, in most cases it is desirable to make sure the server uses
the same options each time it runs. The best way to ensure this is to
specify server options in an option file.
See section 4.3.2 Using Option Files.
mysqld reads options from the [mysqld] and [server]
groups. mysqld_safe reads options from the [mysqld],
[server], [mysqld_safe] and [safe_mysqld]
groups. mysql.server reads options from the [mysqld]
and [mysql.server] groups. An embedded MySQL server usually reads
options from the [server], [embedded] and [xxxxx_SERVER]
groups, where xxxxx is the name of the application into which the
server is embedded.
mysqld accepts many command-line options.
For a list, execute mysqld --help. Before MySQL 4.1.1, --help
prints the full help message. As of 4.1.1, it prints a brief message; to see
the full list, use mysqld --help --verbose.
The following list shows some of the most common server options. Options used for replication are listed in a separate section, see section 6.7 Replication Startup Options.
--ansi
--sql-mode
option instead.
--basedir=path, -b 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 server in a closed environment during startup by using the
chroot() system call. This is a recommended security measure as of
MySQL 4.0. (MySQL 3.23 is not able to provide a chroot() jail that is
100% closed.) Note that use of this option somewhat limits LOAD
DATA INFILE and SELECT ... INTO OUTFILE.
--core-file
mysqld dies. For some systems, you must also
specify the --core-file-size option to mysqld_safe.
See section 5.1.3 mysqld_safe, The Wrapper Around mysqld.
Note that on some systems, such as Solaris, you will
not get a core file if you are also using the --user option.
--datadir=path, -h path
--debug[=...]
--with-debug, you can use this
option to get a trace file of what mysqld is doing.
See section D.1.2 Creating Trace Files.
--default-character-set=charset
--default-table-type=type
--delay-key-write[= OFF | ON | ALL]
DELAYED KEYS option should be used.
Delayed key writing causes key buffers not to be flushed between writes for
MyISAM tables.
OFF disables delayed key writes.
ON enables delayed key writes for those tables that were created with
the DELAYED KEYS option.
ALL delays key writes for all MyISAM tables.
Available as of MySQL 4.0.3.
See section 7.5.2 Tuning Server Parameters.
--delay-key-write-for-all-tables
--delay-key-write=ALL for use prior to MySQL 4.0.3.
As of 4.0.3, use --delay-key-write instead.
--des-key-file=filename
DES_ENCRYPT() and DES_DECRYPT()
from this file.
--enable-external-locking
lockd does not fully work (as on Linux), you will easily get
mysqld to deadlock.
This option used to be named --enable-locking.
--enable-named-pipe
mysqld-nt and mysqld-max-nt servers that support
named pipe connections.
--exit-info, -T
mysqld server. Do not use this option unless you know
exactly what it does!
--flush
--help, -?
--help displays the full help message.
As of 4.1.1, it displays an abbreviated message only. Use both the
--verbose and --help options to see the full message.
--init-file=file
--language=lang_name, -L lang_name
lang_name may be given as the
language name or as the full pathname to the directory where the language
files are installed.
See section 5.6.2 Non-English Error Messages.
--log[=file], -l [file]
hostname.log as filename.
--log-bin=[file]
hostname-bin as filename.
--log-bin-index[=file]
hostname-bin.index as
filename.
--log-error[=file]
hostname.err as filename.
--log-isam[=file]
ISAM/MyISAM changes to this file (only used when
debugging ISAM/MyISAM).
--log-long-format
--log-slow-queries and --log-long-format, then
queries that are not using indexes also are logged to the slow query log.
Note that --log-long-format is deprecated as of MySQL version
4.1, when --log-short-format was introduced (the long log format
is the default setting since version 4.1). Also note that starting with
MySQL 4.1 the --log-queries-not-using-indexes option is available
for the purpose of logging queries that do not use indexes to the slow
queries log.
--log-queries-not-using-indexes
--log-slow-queries, then also
queries that are not using indexes are logged to the slow query log. This
option is available as of MySQL 4.1. See section 5.7.5 The Slow Query Log.
--log-short-format
--log-slow-queries[=file]
long_query_time seconds
to execute to file. Note that the default for the amount of information
logged has changed in MySQL 4.1. See the --log-long-format and
--log-long-format options for details. See section 5.7.5 The Slow Query Log.
--log-update[=file]
file.# where # is a unique number if not
given. See section 5.7.3 The Update Log. The update log is deprecated and is
removed in MySQL 5.0.0; you should use the binary log instead
(--log-bin). See section 5.7.4 The Binary Log. Starting from version 5.0.0,
using --log-update will just turn on the binlog instead
(see section C.1.2 Changes in release 5.0.0 (22 Dec 2003: Alpha)).
--log-warnings, -W
Aborted connection... to the
`.err' file. Enabling this option is recommended, for example, if you
use replication (you will get more information about what is happening,
such as messages about network failures and reconnections). See section A.2.10 Communication Errors / Aborted Connection.
This option used to be called --warnings.
--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 7.3.2 Table Locking Issues.
--memlock
mysqld process in memory. This works on systems such as
Solaris that support the mlockall() system call. This
may help if you have a problem where the operating system is causing
mysqld to swap on disk.
Note that use of this option requires that you run the server as root,
which is normally not a good idea for security reasons.
--myisam-recover [=option[,option...]]]
MyISAM storage engine recovery mode.
The option value is any combination of the values
of DEFAULT, BACKUP, FORCE or QUICK.
If you specify multiple values, seprate them by commas.
You can also use a value of "" 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 respects, 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 11 Column Types.
--pid-file=path
mysqld_safe.
--port=num, -P num
--old-protocol, -o
--one-thread
--open-files-limit=
mysqld.
If this is not set or set to 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'.
--set-variable=name=value, -O value
--help to list the
variables. You can find a full description for all variables in the
SHOW VARIABLES section in this manual. See section 13.5.3.4 SHOW VARIABLES. The section on tuning server parameters includes
information on how to optimize them. See section 7.5.2 Tuning Server Parameters.
Please note that --set-variable=name=value and -O name=value
syntax is deprecated as of MySQL 4.0.2. In 4.0.2, you can set variables
directly using --variable-name=value syntax, and --set-variable
is no longer needed.
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 7.5.6 SET Syntax.
Note that when setting a variable to a value, MySQL may automatically
correct it to stay within a given range, or adjust the value to the
closest allowable value if only certain values are allowed.
--safe-mode
--safe-show-database
SHOW DATABASES statement 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 13.5.1.1 GRANT and REVOKE Syntax.
--safe-user-create
GRANT
statement, if the user doesn't have INSERT privilege to the
mysql.user table or any column in this table.
--skip-bdb
BDB storage engine. This saves memory and may speed
up some operations.
Do not use this operation if you require BDB tables.
--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
DELAY_KEY_WRITE option for all tables.
As of MySQL 4.0.3, you should use --delay-key-write=OFF instead.
See section 7.5.2 Tuning Server Parameters.
--skip-external-locking
isamchk or myisamchk you must
shut down the server. See section 1.2.3 MySQL Stability. Note that in MySQL Version
3.23, you can use CHECK TABLE and REPAIR TABLE to check and repairMyISAM tables.
This option used to be named --skip-locking.
--skip-grant-tables
mysqladmin
flush-privileges or mysqladmin reload command, or by issuing a
FLUSH PRIVILEGES statement.)
--skip-host-cache
--skip-innodb
InnoDB storage engine. This saves memory and disk
space and may speed up some operations.
Do not use this operation if you require InnoDB tables.
--skip-isam
ISAM storage engine. As of MySQL 4.1, ISAM is
disabled by default, so this option applies only if the server was configured
with support for ISAM.
This option was added in MySQL 4.1.1.
--skip-name-resolve
Host column values in the
grant tables must be IP numbers or localhost. See section 7.5.5 How MySQL uses DNS.
--skip-networking
mysqld must be made via named pipes (on Windows) or Unix socket files
(on Unix). This option is highly recommended for systems where only local
clients are allowed. See section 7.5.5 How MySQL uses DNS.
--skip-new
--skip-symlink
--skip-symbolic-links, for use before MySQL
4.0.13.
--symbolic-links, --skip-symbolic-links
directory.sym file that contains the path to the real directory.
See section 7.6.1.3 Using Symbolic Links for Databases on Windows.
MyISAM index file or datafile to another directory with
the INDEX DIRECTORY or DATA DIRECTORY options of the
CREATE TABLE statement. If you delete or rename the table,
the files that its symbolic links point to also are deleted or
renamed. See section 13.2.5 CREATE TABLE Syntax.
--skip-safemalloc
--with-debug=full, all MySQL programs
checks for memory overruns during each memory allocation and memory
freeing operation. This checking is very slow, so for the server you
can avoid it when you don't need it by using the --skip-safemalloc
option.
--skip-show-database
SHOW DATABASES statement, unless the user has the
SHOW DATABASES privilege.
--skip-stack-trace
mysqld under a debugger. On some systems, you also must use
this option to get a core file. See section D.1 Debugging a MySQL server.
--skip-thread-priority
--socket=path
MySQL.
--sql-mode=value[,value[,value...]]
--temp-pool
--transaction-isolation=level
READ-UNCOMMITTED,
READ-COMMITTED,
REPEATABLE-READ, or
SERIALIZABLE.
See section 13.4.6 SET TRANSACTION Syntax.
--tmpdir=path, -t path
/tmp directory resides on a partition that
is too small to hold temporary tables. Starting from MySQL 4.1, this
option accepts several paths that are used in round-robin fashion. Paths
should be separated by colon characters (`:') on Unix and semicolon
characters (`;') on Windows. It is possible to set tmpdir
to point to a memory-based filesystem, except if the MySQL server is a
slave replication server. If it is a slave, some of its temporary files
are needed to survive a machine's reboot. (For example, to replicate
temporary tables or LOAD DATA INFILE statements). In this case,
a memory-based tmpdir that is cleared when the machine reboots is
not suitable; a disk-based tmpdir is necessary.
--user={user_name | user_id}, -u {user_name | user_id}
mysqld server as the user having the name user_name or
the numeric user ID user_id.
(``User'' in this context refers to a system login account, not a MySQL user
listed in the grant tables.)
This option is mandatory when starting mysqld as root.
The server will change its user ID during its startup sequence, causing it
to run as that particular user rather than as root.
See section 5.3.2 How to Make MySQL Secure Against Attackers.
Starting from MySQL 3.23.56 and 4.0.12:
To avoid a possible security hole where a user adds a --user=root
option to some `my.cnf' file (thus causing the server to run as
root), mysqld uses only the first
--user option specified and produces a warning if there are multiple
--user options. Options in `/etc/my.cnf' and
`datadir/my.cnf' are processed before
command-line options, so it is recommended that you
put a --user option in `/etc/my.cnf' and specify a value other than
root. The option in `/etc/my.cnf' will be found before any other
--user options, which ensures that the server runs as a user other
than root, and that a warning results if any other --user option
is found.
--version, -V
You can change the values of most system variables for a running server with the
SET statement. See section 7.5.6 SET Syntax.
The MySQL server can operate in different SQL modes, and (as of MySQL 4.1) can apply these modes differentially for different clients. This allows applications to tailor server operation to their own requirements.
Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.
You can set the default SQL mode by starting mysqld with the
--sql-mode="modes" option. Beginning with MySQL 4.1, you can also
change the mode after startup time by setting the sql_mode variable
with a SET [SESSION|GLOBAL] sql_mode='modes' statement.
Setting the GLOBAL variable affects the operation of all clients that
connect from that time on. Setting the SESSION variable affects only
the current client.
modes is a list of different modes separated by comma (`,')
characters.
You can retrieve the current mode by issuing a SELECT @@sql_mode
statement. The default value is empty (no modes set).
The value also can be empty
(--sql-mode="") if you want to reset it.
The following table lists the supported modes. The Version column indicates when each mode value was implemented.
| Value | Version | Meaning |
ANSI_QUOTES | 4.0.0 | Treat `"' as an identifier quote character (like the MySQL Server ``' quote character) and not as a string quote character. You can still use ``' to quote identifers in ANSI mode. With ANSI_QUOTES enabled, you cannot use double quotes to quote a literal string, because it will be intepreted as an identifier.
|
IGNORE_SPACE | 4.0.0 | Allow spaces between a function name and the `(' character. This forces all function names to be treated as reserved words. As a result, if you want to access any database, table, or column name that is a reserved word, you must quote it. For example, because there is a USER() function, the name of the user table in the mysql database and the User column in that table become reserved, so you must quote them:
SELECT "User" FROM mysql."user"; |
NO_AUTO_VALUE_ON_ZERO | 4.1.1 |
NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT
columns. Normally, you generate the next sequence number for the column by
inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO
suppresses this behavior for 0 so that only NULL generates
the next sequence number. This mode can be useful if 0 has been
stored in a table's AUTO_INCREMENT column. (This is not a recommended
practice,
by the way.) For example, if you dump the table with mysqldump and
then reload it, normally MySQL will generate new sequence numbers when it
encounters the 0 values, resulting in a table with different contents
than the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before
reloading the dump file solves this problem. (As of MySQL 4.1.1,
mysqldump automatically includes statements in the dump output to enable
NO_AUTO_VALUE_ON_ZERO.)
|
NO_DIR_IN_CREATE | 4.0.15 | When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers.
|
NO_FIELD_OPTIONS | 4.1.1 | Don't print MySQL field-specific options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.
|
NO_KEY_OPTIONS | 4.1.1 | Don't print MySQL index-specific options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.
|
NO_TABLE_OPTIONS | 4.1.1 | Don't print MySQL table-specific options (such as ENGINE) in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.
|
NO_UNSIGNED_SUBTRACTION | 4.0.2 | In subtraction operations, don't mark the result as UNSIGNED if one of the operands is unsigned. Note that this makes UNSIGNED BIGINT not 100% usable in all contexts. See section 12.5 Cast Functions.
|
ONLY_FULL_GROUP_BY | 4.0.0 | Don't allow queries which in the GROUP BY part refers to a not selected column.
|
PIPES_AS_CONCAT | 4.0.0 | Treat || as a string concatenation operator (same as CONCAT()) rather than as a synonym for OR.
|
REAL_AS_FLOAT | 4.0.0 | Treat REAL as a synonym for FLOAT rather than as a synonym for DOUBLE.
|
The following special modes are provided as shorthand for combinations of mode values from the preceding table:
| Value | Version | Meaning |
ANSI | 4.1.1 | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY. See section 1.8.3 Running MySQL in ANSI Mode.
|
DB2 | 4.1.1 | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
|
DB2 | 4.1.1 | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
|
MAXDB | 4.1.1 | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
|
MSSQL | 4.1.1 | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
|
MYSQL323 | 4.1.1 | NO_FIELD_OPTIONS
|
MYSQL40 | 4.1.1 | NO_FIELD_OPTIONS
|
ORACLE | 4.1.1 | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
|
POSTGRESQL | 4.1.1 | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
|
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 emphasize the necessity of fully protecting the entire server host (not just 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 accounts) 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 statements 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 statement and check to see who has access to
what. Remove those privileges that are not necessary using the REVOKE
statement.
MD5(), SHA1() or
some other one-way hashing function.
nmap. MySQL uses port 3306 by default. This port should
not be accessible 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 host where
your MySQL server runs:
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.
mysql_escape_string() function, which
is based on the function of the same name in the MySQL C API.
Prior to PHP 4.0.3, use addslashes() instead.
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 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 that can sniff the traffic between the client and the server can crack the password. 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 traffic much more difficult to decipher. To make the connection 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/. Using SSH, 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 5.4.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 behavior with client/server
applications that the client may specify any username. 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 username in the user
table, because MySQL usernames have nothing to do with Unix
usernames. To start mysqld as another Unix user, add a user
line that specifies the username 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 7.6.1.2 Using Symbolic Links for Tables on Unix.
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 server activity 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 statement 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 13.5.1.1 GRANT and REVOKE Syntax.
--safe-user-create
GRANT
statement, 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 statement 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 socket files.
This option is unsuitable when using a MySQL version prior to 3.23.27 with
the MIT-pthreads package, because Unix socket files were not supported by
MIT-pthreads at that time.
--skip-show-database
SHOW DATABASES statement, 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 (4.0.13 on Windows),
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 statement:
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 19.1.3.40 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 perform exactly the operations 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 username you specify. The system grants privileges according to your identity and what you want to do.
MySQL considers both your hostname and username in identifying you
because there is little reason to assume that a given username 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 5.4.2 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 | References_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 5.3.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 5.3.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 5.4.2 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 5.3.13 Causes of Access denied Errors. For advice on security issues,
see section 5.3.2 How to Make MySQL Secure Against Attackers.
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 5.4.2 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. Further information about the meaning of
each privilege may be found at section 13.5.1.1 GRANT and REVOKE Syntax.
| 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 13.5.4.3 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 username, 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.3.2 Using 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' option 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.3.2 Using Option Files.
mysql using MYSQL_HOST. The
MySQL username can be specified using USER (this is for
Windows and NetWare only). The password can be specified using MYSQL_PWD
(but this is insecure; see the next section). See section E 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 hostname.
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 username, 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 username
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 shown in the preceding
table. 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 username matches both the
connecting hostname and username. (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 username, 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 username!
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 12.6.4 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 13.5.1.1 GRANT and REVOKE Syntax. (You may find it helpful to refer to
section 5.3.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 username. 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 summarized 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 behavior with pre-4.1 behavior, but the 4.1 behavior 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 behavior
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 5.4.3 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 an incorrect password. See section 5.4.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 have an incorrect password in some my.ini
file. See section 4.3.2 Using 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.
See section 2.5.8 Upgrading the Grant Tables.
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 5.4.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 socket files, 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 username.
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 username
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 7.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.3.2 Using 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 D.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.
There are several distinctions between the way usernames 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 usernames 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 12.6.2 Encryption 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 13.5.1.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 5.3.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 Source Installation Overview.
The mysql_install_db script starts up the mysqld
server, then initializes 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.2.1.8 Running MySQL Client Programs 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');
Replace 'new_password' with the password that you want to use.
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 other 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
UPDATE, 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 13.5.1.1 GRANT and REVOKE Syntax.
There are also several contributed programs (such as phpMyAdmin)
that can be used to create and administer 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.
First, use the mysql program to connect to the server as the
MySQL root user:
shell> mysql --user=root mysql
Then you can add new users by issuing GRANT statements:
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 takes 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-level privileges are granted.
(They can be granted later by issuing additional GRANT statements.)
dummy
USAGE privilege type allows you
to create a user with no privileges. It has the effect of setting all
the global privileges to 'N'. It is assumed that you will grant
specific privileges to the account 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 have
fewer privilege columns, and versions from 4.0.2 on have more.) For the
admin user, the more readable extended INSERT syntax using
SET 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.
In the last INSERT statement (for the dummy user), only the
Host, User, and Password columns in the user
table record are assigned values. None of the privilege columns are set
explicitly, so MySQL assigns them all the default value of 'N'.
This is the same thing that GRANT USAGE does.
The following example adds a user custom who can
access the bankaccount database only from the local host,
the expenses database only from the host whitehouse.gov, and
the customer database only from the host server.domain. He wants
to use the password obscure 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 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON expenses.*
-> TO custom@'whitehouse.gov'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON customer.*
-> TO custom@'server.domain'
-> IDENTIFIED BY 'obscure';
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('obscure'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('whitehouse.gov','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('server.domain','custom',PASSWORD('obscure'));
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('server.domain','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
As in the preceding example that used INSERT statements, you may need
to use a different number of 'Y' values, depending on your version of
MySQL.
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 (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, after you modify the grant
tables directly , you must tell the server 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 (for example, mydomain.com), you can issue a GRANT
statement like the following:
mysql> GRANT ...
-> ON *.*
-> TO myusername@'%.mydomain.com'
-> IDENTIFIED BY 'mypassword';
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user VALUES ('%.mydomain.com', 'myusername',
-> PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;
DROP USER user_name
This command was added to MySQL 4.1.1.
It deletes a user that doesn't have any privileges.
To delete a user from MySQL, you should use the following procedure, performing the steps in the order shown:
SHOW PRIVILEGES.
See section 13.5.3.11 SHOW PRIVILEGES.
REVOKE. See section 13.5.1.1 GRANT and REVOKE Syntax.
DROP USER.
If you are using and older MySQL version you should first revoke the privileges and then delete the user with:
DELETE FROM mysql.user WHERE user='username' and host='hostname'; 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 13.5.1.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 authentication 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 5.4.1 MySQL Usernames 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 initialization 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.3.2 Using 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 E 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.
This section describes how to set up secure connections between MySQL clients and the server using the Secure Sockets Layer (SSL) protocol. It also decribes a way to set up SSH on Windows.
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 recognize 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 favorite 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.
This is necessary if your grant tables date from a version prior to MySQL
4.0.0. The procedure is described in section 2.5.8 Upgrading the Grant Tables.
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.
The following table lists options that are used for specifying the use of SSL, certificate files, and key files. These options are available beginning with MySQL 4.0. They may be given on the command line or in option files.
--ssl
--ssl-ca, --ssl-cert, and --ssl-key options.
Note that this option doesn't require an SSL connection.
For example, if the server or client are compiled without SSL support,
a normal unencrypted connection will be used.
The secure way to ensure that a SSL connection will be used is
to create an account on the server that includes a
REQUIRE SSL clause in the GRANT statement.
Then use this account to connect to the server, with both a server and client
that have SSL support enabled.
You can use this option to indicate that the connection should not use SSL.
Do this by specifying the option as --skip-ssl or --ssl=0.
--ssl-ca=file_name
--ssl-capath=directory_name
--ssl-cert=file_name
--ssl-cipher=cipher_list
cipher_list has the same format as the openssl ciphers
command.
Example: --ssl-cipher=ALL:-AES:-EXP
--ssl-key=file_name
Here is a note about how to connect to get a secure connection to remote MySQL server with SSH (by David Carlson dcarlson@mplcomm.com):
SecureCRT from http://www.vandyke.com/.
Another option is f-secure from http://www.f-secure.com/. You
can also find some free ones on Google at
http://directory.google.com/Top/Computers/Security/Products_and_Tools/Cryptography/SSH/Clients/Windows/.
Host_Name = yourmysqlserver_URL_or_IP.
Set userid=your_userid to log in to your server. This userid
value may not be the same as the username of your MySQL account.
local_port: 3306, remote_host: yourmysqlservername_or_ip, remote_port: 3306 )
or a local forward (Set port: 3306, host: localhost, remote port: 3306).
localhost
for the MySQL host server--not yourmysqlservername.
You should now have an ODBC connection to MySQL, encrypted using SSH.
This section discusses how to make database backups and how to perform table maintenance. The syntax of the SQL statements described here is given in section 13.5 Database Administration Statements.
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 13.4.5 LOCK TABLES and UNLOCK TABLES Syntax.
See section 13.5.4.2 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 13.1.7 SELECT Syntax.
See section 13.5.2.2 BACKUP TABLE Syntax.
Another way to back up a database is to use the mysqldump program or
the mysqlhotcopy script. See section 8.7 mysqldump, Dumping Table Structure and Data.
See section 8.8 mysqlhotcopy, Copying MySQL Databases and Tables.
shell> mysqldump --tab=/path/to/some/dir --opt db_nameor:
shell> mysqlhotcopy db_name /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. (But note that these
methods
will not work if your database contains InnoDB tables. InnoDB
does not store table contents in database directories, and mysqlhotcopy
works only for MyISAM and ISAM tables.)
mysqld if it's running, then start it with the
--log-bin[=file_name] option. See section 5.7.4 The Binary Log. The binary
log files 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 your MySQL server is a slave, whatever backup method you choose,
when you backup your slave's data, you should also backup the
`master.info' and `relay-log.info' files
which are always needed to resume replication after you restore
the slave's data. If your slave is subject to replicating LOAD
DATA INFILE commands, you should also backup the `SQL_LOAD-*' files
which may exist in the directory specified by the --slave-load-tmpdir
option. (This location defaults to the value of the tmpdir variable
if not specified.) The slave will need these files to resume
replication of any interrupted LOAD DATA INFILE operations.
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 5.7.4 The Binary Log):
mysqldump backup, or binary backup.
shell> mysqlbinlog hostname-bin.[0-9]* | mysqlIn your case you may want to re-run only certain binlogs, from certain positions (usually you want to re-run all binlogs from the date of the restored backup, possibly excepted some wrong queries). See section 8.5
mysqlbinlog, Executing the queries from a binary log for more information on the mysqlbinlog
utility and how to use it.
If you are using the update log (which is removed in MySQL 5.0.0)
you can execute the content of the update log like this:
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 6.1 Introduction to Replication.
If you are using a Veritas filesystem, you can do:
FLUSH TABLES WITH READ LOCK.
mount vxfs snapshot.
UNLOCK TABLES.
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 13.5.2.3 CHECK TABLE Syntax. You can
repair tables with the REPAIR TABLE command. See section 13.5.2.6 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 14 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 optimize 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
optimize 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 13.5.2.5 OPTIMIZE TABLE Syntax.
Even though 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
another program (like the mysqld server) that hasn't yet closed
the file or that has died without closing the file properly.
If 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 name=value, --set-variable=name=value
--set-variable=name=value and -O name=value
syntax is deprecated as of MySQL 4.0. Use --name=value instead.
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:
-B or --backup
--correct-checksum
-D # or --data-file-length=#
-e or --extend-check
-f or --force
-k # or --keys-used=#
ISAM, tells the ISAM storage engine to update only the
first # 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.
-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.
-p or --parallel-recover
-r and -n, but creates
all the keys in parallel, in different threads.
This option was added in MySQL 4.0.2.
This is alpha code. Use at your own risk!
-r or --recover
ISAM/MyISAM tables).
If you want to recover a table, this is the option to try first. Only if
myisamchk reports that the table can't be recovered by -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 perform repairs. 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 optimize 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 section 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 14.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 13.5.2.6 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;You can also use this technique for error 136 (no more room in index file).
In the other cases, you must repair your tables. myisamchk
can usually detect and fix most problems that occur.
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 13.5.2.3 CHECK TABLE Syntax. See section 13.5.2.6 REPAIR TABLE Syntax.
The manual section about table maintenance includes the options to
isamchk/myisamchk. See section 5.5.2 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 optimize 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 13.5.2.5 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 5.5.2.1 myisamchk Invocation Syntax.
Starting with MySQL Version 3.23.13, you can check MyISAM
tables with the CHECK TABLE command. See section 13.5.2.3 CHECK TABLE Syntax. You can
repair tables with the REPAIR TABLE command. See section 13.5.2.6 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 down the mysqld server 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 5.5.2.10 Table Optimization.
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 8.11 myisampack, The MySQL Compressed Read-only Table Generator, for an example of this
information and a description of what it means.
By default, MySQL uses the ISO-8859-1 (Latin1) character set with sorting according to Swedish/Finnish rules. These defaults are suitable for the USA and most of 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
strings 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 | none options to configure, and the
character set configuration files listed in
`SHAREDIR/charsets/Index'. See section 2.3.2 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 5.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 the MySQL internals mailing list. See section 1.7.1.1 The MySQL Mailing Lists.
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 5.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.
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 seconds 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 13.5.4.2 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 D.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 5.7.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 has been deprecated and replaced by the binary log. See section 5.7.4 The Binary Log. The binary log can do anything the old update log could do, and more. The update log is removed starting from MySQL 5.0.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 binary log has replaced the old update log. The update log is removed starting from MySQL 5.0. The binary log contains all information that is available in the update log in a more efficient format and in a manner that is transactionally safe.
The binary log, like the old update log, only logs 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 primary purpose of the binary log is to be able to update the database during a restore operation as fully as possible, as the binary log would contain all updates done after a backup was made.
The binary log is also used when you are replicating a slave from a master. See section 6 Replication in MySQL.
The binary log 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 5.7.2 The General Query Log.
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 13.5.4.6 RESET Syntax), or only some of them with
PURGE MASTER LOGS (see section 13.6.1 SQL Statements for Controlling Master Servers).
You can use the following options to mysqld to affect what is logged
to the binary log (please make sure to read the notes which follow
this table):
| 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.
|
The rules are evaluated in the following order, to decide if the query should be written to the binary log or not:
binlog-do-db or binlog-ignore-db rules?
binlog-do-db or
binlog-ignore-db or both). Is there a current database (has any
database been selected by USE?)?
binlog-do-db rules?
binlog-do-db
rules?
binlog-ignore-db rules.
Does the current database match any of the binlog-ignore-db
rules?
So for example, a slave running with only binlog-do-db=sales
will not write to the binlog any query whose current database is
different from sales (in other words, binlog-do-db can
sometimes mean ``ignore other databases'').
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 13.6.1 SQL Statements for Controlling Master Servers) which will also safely update the binary
log index file for you (and which can take a date argument since
MySQL 4.1)
A connection with the SUPER privilege can disable the binary
logging of its queries using SET
SQL_LOG_BIN=0. See section 13.6.1 SQL Statements for Controlling Master Servers.
You can examine the binary log file with the mysqlbinlog utility.
For example, you can update a MySQL server from the binary log
as follows:
shell> mysqlbinlog log-file | mysql -h server_name
See section 8.5 mysqlbinlog, Executing the queries from a binary log for more information on the mysqlbinlog
utility and how to use it.
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 is removed in MySQL 5.0.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.
The binary log format is different in versions 3.23, 4.0, and 5.0.0. Those format changes were required to enhance replication. MySQL 4.1 has the same binary log format as 4.0.
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 seconds 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 optimization. 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 5.2.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 5.7 The MySQL Log Files. However, you must clean up these files regularly, to ensure that the logs don't take up too much disk space.
When using MySQL with log files, you will want to remove/backup old log files from time to time and tell MySQL to start logging to new files. See section 5.5.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 script if you are
using the binary 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'.
In some cases you might want to run multiple mysqld servers
on the same machine. You might want to test a new
MySQL release while leaving your existing production setup undisturbed.
Or you may want to give different users access to different mysqld
servers that they manage themselves. (For example, you might be an
Internet service provider that wants to provide independent MySQL
installations for different customers.)
To run multiple servers on a single machine, each server must have unique values for several operating parameters. These can be set on the command line or in option files. See section 4.3 Specifying Program Options.
At least the following options must be different for each server:
--port=port_num
--socket=path
--shared-memory-base-name=name (Windows only; new in MySQL 4.1)
--pid-file=path (Unix only)
--port controls the port number for TCP/IP connections.
--socket controls the socket file path on Unix and the name of the
named pipe on Windows. (It's necessary to specify distinct pipe names on
Windows only for those servers that support named pipe connections.)
--shared-memory-base-name designates the shared memory name used by a
Windows server to allow clients to connect via shared memory.
--pid-file indicates the name of the file in which a Unix server writes
its process ID.
If you use the following options, they must be different for each server:
--log=path
--log-bin=path
--log-update=path
--log-error=path
--log-isam=path
--bdb-logdir=path
If you want more performance, you can also specify the following options differently for each server, to spread load between several physical disks:
--tmpdir=path
--bdb-tmpdir=path
Having different temporary directories like above is also recommended because it will be easier for you in case you want to know to which MySQL server a certain temporary file belongs.
Generally, each server should also use a different data directory, which is
specified using the --datadir=path option.
Warning: Normally you should never have two servers that update data in the same databases! This may lead to unpleasant surprises if your operating system doesn't support fault-free system locking! If (despite this warning) you run multiple servers using the same data directory and they have logging enabled, you must use the appropriate options to specify log file names that are unique to each server. Otherwise, the servers will try to log to the same files.
This warning against sharing a data directory among servers also applies in an NFS environment. Allowing multiple MySQL servers to access a common data directory over NFS is a bad idea!
lockd
daemon, but at the moment there is no platform that will perform
locking 100% reliably in every situation.
Make it easy for yourself: Forget about sharing a data directory among servers over NFS. A better solution is to have one computer that contains several CPUs and use an operating system that handles threads efficiently.
If you have multiple MySQL installations in different locations, normally
you can specify the base installation directory for each server with the
--basedir=path option to cause each server to use a different data
directory, log files, and PID file. (The defaults for all these values are
determined relative to the base directory.) In that case, the only other
options you need to specify are the --socket and --port
options. For example, suppose you install different versions of MySQL using
`.tar' file binary distributions. These will install in different
locations, so you can start the server for each installation using the
command ./bin/mysqld_safe under its corresponding base directory.
mysqld_safe will determine the proper
--basedir option to pass to mysqld, and you need specify
only the --socket and --port options to mysqld_safe.
As discussed in the following sections, it is possible to start additional servers by setting environment variables or by specifying appropriate command-line options. However, if you need to run multiple servers on a more permanent basis, it will be more convenient to use option files to specify for each server those option values that must be unique to it.
You can run multiple servers on Windows by starting them manually from the command line, each with appropriate operating parameters. On Windows NT-based systems, you also have the option of installing several servers as Windows services and running them that way. General instructions for running MySQL servers from the command line or as services are given in section 2.2.1 Installing MySQL on Windows. This section describes how to make sure you start each server with different values for those startup options that must be unique per server, such as the data directory. (These options are described in section 5.8 Running Multiple MySQL Servers on the Same Machine.)
To start multiple servers manually from the command line, you can specify the appropriate options on
the command line or in an option file. It's more convenient to place the
options in an option file, but it's necessary to make sure that each server
gets its own set of options. To do this, create an option file for each
server and tell the server the filename with a --defaults-file option
when you run it.
Suppose you want to run mysqld on port 3307 with a
data directory of `C:\mydata1', and mysqld-max on port 3308 with a
data directory of `C:\mydata2'. To accomplish this, create two option
files. For example, create one file named `C:\my-opts1.cnf' that looks
like this:
[mysqld] datadir = C:/mydata1 port = 3307
Create a second file named `C:\my-opts2.cnf' that looks like this:
[mysqld] datadir = C:/mydata2 port = 3308
Then start each server with its own option file:
shell> mysqld --defaults-file=C:\my-opts1.cnf shell> mysqld-max --defaults-file=C:\my-opts2.cnf
(On NT, the servers will start in the foreground, so you'll need to issue those two commands in separate console windows.)
To shut down the servers, you must connect to the appropriate port number:
shell> mysqladmin --port=3307 shutdown shell> mysqladmin --port=3308 shutdown
Servers configured as just described will allow clients to connect over
TCP/IP. If you also want to allow named pipe connections,
use the mysqld-nt or mysqld-max-nt servers and specify options
that enable the named pipe and specify its name. (Each server that supports
named pipe connections must use a unique
pipe name.) For example, the `C:\my-opts1.cnf' file might be written
like this:
[mysqld] datadir = C:/mydata1 port = 3307 enable-named-pipe socket = mypipe1
Then start the server this way:
shell> mysqld-nt --defaults-file=C:\my-opts1.cnf
`C:\my-opts2.cnf' would be modified similarly for use by the second server.
On NT-based systems, a MySQL server can be run as a Windows service. The procedures for installing, controlling, and removing a single MySQL service are described in section 2.2.1.7 Starting MySQL as a Windows Service.
As of MySQL 4.0.2, you can install multiple servers as services. In this case, you must make sure that each server uses a different service name in addition to all the other parameters that must be unique per server.
For the following instructions, assume that you want to run the mysqld-nt
server from two different versions of MySQL that are installed at
`C:\mysql-4.0.8' and `C:\mysql-4.0.17', respectively. (This might be
the case if you're running 4.0.8 as your production server, but want to test
4.0.17 before upgrading to it.)
The following principles are relevant when installing a MySQL service with the
--install option:
MySQL and the server reads options from the [mysqld] group in
the standard option files.
--install option, the server ignores the [mysqld] option
group and instead reads options from the group that has the same name as the
service. The server reads options from the standard option files.
--defaults-file option after the service name,
the server ignores the standard option files and reads options only from the
[mysqld] group of the named file.
These principles also apply if you install a server using the
--install-manual option.
Based on the preceding information, you have several ways to set up multiple services. The following instructions describe some examples. Before trying any of them, be sure you shut down and remove any existing MySQL services first.
mysqld-nt using the service
name of mysqld1 and the 4.0.17 mysqld-nt using the service name mysqld2.
In this case, you can use the [mysqld1] group for 4.0.8 and the
[mysqld2] group for 4.0.17.
For example, you can set up `C:\my.cnf' like this:
# options for mysqld1 service [mysqld1] basedir = C:/mysql-4.0.8 port = 3307 enable-named-pipe socket = mypipe1 # options for mysqld2 service [mysqld2] basedir = C:/mysql-4.0.17 port = 3308 enable-named-pipe socket = mypipe2Install the services as follows, using the full server pathnames to ensure that Windows registers the correct executable program for each service:
shell> C:\mysql-4.0.8\bin\mysqld-nt --install mysqld1 shell> C:\mysql-4.0.17\bin\mysqld-nt --install mysqld2To start the services, use the services manager, or use
NET START
with the appropriate service names:
shell> NET START mysqld1 shell> NET START mysqld2To stop the services, use the services manager, or use
NET STOP
with the appropriate service names:
shell> NET STOP mysqld1 shell> NET STOP mysqld2Note: Before MySQL 4.0.17, only a server installed using the default service name (
MySQL) or one installed explicitly with a service name of
mysqld will read the [mysqld] group in the standard option
files. As of 4.0.17, all servers read the [mysqld group if they read
the standard option files, even if they are installed using another service
name. This allows you to use the [mysqld] group for options that should
be used by all MySQL services, and an option group named after each service
for use by the server installed with that service name.
--defaults-file when you install the services to tell each server
what file to use. In this case, each file should list options using a
[mysqld] group.
With this approach, to specify options for the 4.0.8 mysqld-nt,
create a file `C:\my-opts1.cnf' that looks like this:
[mysqld] basedir = C:/mysql-4.0.8 port = 3307 enable-named-pipe socket = mypipe1For the 4.0.17
mysqld-nt, create a file
`C:\my-opts2.cnf' that looks like this:
[mysqld] basedir = C:/mysql-4.0.17 port = 3308 enable-named-pipe socket = mypipe2Install the services as follows (enter each command on a single line):
shell> C:\mysql-4.0.8\bin\mysqld-nt --install mysqld1
--defaults-file=C:\my-opts1.cnf
shell> C:\mysql-4.0.17\bin\mysqld-nt --install mysqld2
--defaults-file=C:\my-opts2.cnf
To use a --defaults-file option when you install a MySQL server as a
service, you must precede the option with the service name.
After installing the services, start and stop them the same way as in the
preceding example.
To remove multiple services, use mysqld --remove for each one,
specifying a service name following the --remove option if the
service to remove has a name different than the default.
The easiest way is to run multiple servers on Unix is to compile them with different TCP/IP ports and socket files so that each one is listening on different network interfaces. Also, by compiling in different base directories for each installation, that automatically results in different compiled-in data directory, log file, and PID file locations for each of your servers.
Assume an existing server is configured for the default port number and
socket file. To configure a new server to have different operating
parameters, use a configure command something like this:
shell> ./configure --with-tcp-port=port_number \
--with-unix-socket-path=file_name \
--prefix=/usr/local/mysql-4.0.17
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 than the one under which
the existing MySQL installation is located.
If you have a MySQL server listening on a given port number, you can use the following command to find out what operating parameters it is using for several important configurable variables, including the base directory and socket name:
shell> mysqladmin --host=host_name --port=port_number variables
With the information displayed by that command, you can tell what option values not to use when configuring an additional server.
Note that if you specify ``localhost'' as a hostname, mysqladmin
will default to using a Unix socket connection rather than TCP/IP.
In MySQL 4.1, you can explicitly specify the connection protocol to use by
using the --protocol={TCP | SOCKET | PIPE | MEMORY} option.
You don't have to compile a new MySQL server just to start with a different socket file and TCP/IP port number. It is also possible to specify those values at runtime. One way to do so is by using command-line options:
shell> /path/to/mysqld_safe --socket=file_name --port=port_number
To use another database directory for the second server, pass
a --datadir=path option to mysqld_safe.
Another way to achieve a similar effect is to use environment variables to set the socket name and port number:
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 &
This is a quick way of starting a second server to use for testing. The nice thing about this method is that the environment variable settings will apply to any client programs that you invoke from the above shell. Thus, connections for those clients automatically will be directed to the second server!
section E Environment Variables includes a list of other environment
variables you can use to affect mysqld.
For automatic server execution, your startup script that is executed at boot time should execute the following command once for each server with an appropriate option file path for each command:
mysqld_safe --defaults-file=path-to-option-file
Each option file should contain option values specific to a given server.
On Unix,
the mysqld_multi script is another way to start multiple servers.
See section 5.1.5 mysqld_multi, A Program for Managing Multiple MySQL Servers.
When you want to connect with a client program to a MySQL server that is listening to different network interfaces than those compiled into your client, you can use one of the following methods:
--host=host_name --port=port_number to connect
via TCP/IP to a remote host, or with --host=localhost --socket=file_name
to connect to a local host via a Unix socket or a Windows named pipe.
--protocol=tcp to connect
via TCP/IP, --protocol=socket to connect via a Unix socket,
--protocol=pipe to connect via a named pipe, or
--protocol=memory to connect via shared memory. For TCP/IP
connections, you may also need to specify --host and --port
options. For the other types of connections, you may need to specify
a --socket option to specify a socket or named pipe name, or a
--shared-memory-base-name option to specify the shared memory name.
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 can place commands
to set these environment variables in your `.login' file so that they
apply each time you log in.
See section E Environment Variables.
[client] group of
an option file. For example, you can use `C:\my.cnf' on Windows, or
the `.my.cnf' file in your home directory on Unix. See section 4.3.2 Using Option Files.
mysql_real_connect() call. You can also have the program read
option files by calling mysql_options().
See section 19.1.3 C API Function Descriptions.
DBD::mysql module, you can read the options
from the MySQL option files. For example:
$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);
See section 19.5 MySQL Perl API.
Go to the first, previous, next, last section, table of contents.