Database Administration
***********************
Configuring MySQL
=================
`mysqld' Command-line Options
-----------------------------
In most cases you should manage `mysqld' options through option files.
*Note Option files::.
`mysqld' and `mysqld.server' read options from the `mysqld' and
`server' groups. `mysqld_safe' read options from the `mysqld',
`server', `mysqld_safe' and `safe_mysqld' groups. An embedded MySQL
server usually reads options from the `server', `embedded' and
`xxxxx_SERVER', where `xxxxx' is the name of the application.
`mysqld' accepts a lot of command-line options. Here follows some of
the most common ones. For a full list execute `mysqld --help'. Options
used for replication are listed in a separate section, see *Note
Replication Options::.
`--ansi'
Use SQL-99 syntax instead of MySQL syntax. *Note ANSI mode::.
`-b, --basedir=path'
Path to installation directory. All paths are usually resolved
relative to this.
`--big-tables'
Allow big result sets by saving all temporary sets on file. It
solves most 'table full' errors, but also slows down the queries
where in-memory tables would suffice. Since Version 3.23.2, MySQL
is able to solve it automatically by using memory for small
temporary tables and switching to disk tables where necessary.
`--bind-address=IP'
IP address to bind to.
`--console'
Write the error log messages to stderr/stdout even if `--log-error'
is specified. On Windows, `mysqld' will not close the console
screen if this option is used.
`--character-sets-dir=path'
Directory where character sets are. *Note Character sets::.
`--chroot=path'
Put `mysqld' daemon in chroot environment at startup. Recommended
security measure since MySQL 4.0 (MySQL 3.23 is not able to provide
100% closed chroot jail). It somewhat limits `LOAD DATA INFILE'
and `SELECT ... INTO OUTFILE' though.
`--core-file'
Write a core file if `mysqld' dies. For some systems you must also
specify `--core-file-size' to `mysqld_safe'. *Note `mysqld_safe':
mysqld_safe. Note that on some systems, like Solaris, you will
not get a core file if you are also using the `--user' option.
`-h, --datadir=path'
Path to the database root.
`--debug[...]='
If MySQL is configured with `--with-debug', you can use this
option to get a trace file of what `mysqld' is doing. *Note
Making trace files::.
`--default-character-set=charset'
Set the default character set. *Note Character sets::.
`--default-table-type=type'
Set the default table type for tables. *Note Table types::.
`--delay-key-write[= OFF | ON | ALL]'
How MyISAM `DELAYED KEYS' should be used. *Note Server
parameters::.
`--des-key-file=filename'
Read the default keys used by `DES_ENCRYPT()' and `DES_DECRYPT()'
from this file.
`--enable-external-locking (was --enable-locking)'
Enable system locking. Note that if you use this option on a
system on which `lockd' does not fully work (as on Linux), you
will easily get mysqld to deadlock.
`--enable-named-pipe'
Enable support for named pipes (only on NT/Win2000/XP).
`-T, --exit-info'
This is a bit mask of different flags one can use for debugging the
mysqld server; one should not use this option if one doesn't know
exactly what it does!
`--flush'
Flush all changes to disk after each SQL command. Normally MySQL
only does a write of all changes to disk after each SQL command
and lets the operating system handle the syncing to disk. *Note
Crashing::.
`-?, --help'
Display short help and exit.
`--init-file=file'
Read SQL commands from this file at startup.
`-L, --language=...'
Client error messages in given language. May be given as a full
path. *Note Languages::.
`-l, --log[=file]'
Log connections and queries to file. *Note Query log::.
`--log-bin=[file]'
Log all queries that change data to the file. Used for backup and
replication. *Note Binary log::.
`--log-bin-index[=file]'
Index file for binary log file names. *Note Binary log::.
`--log-error[=file]'
Log errors and startup messages to this file. *Note Error log::.
`--log-isam[=file]'
Log all ISAM/MyISAM changes to file (only used when debugging
ISAM/MyISAM).
are logged for
queries. If you are using `--log-slow-queries' and
`--log-long-format', then also queries that are not using indexes
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'
If you are using this option with `--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. *Note Slow query
log::.
`--log-short-format'
Log less information to the logfiles (update log, binary update
log, and slow queries log, whatever log has been activated). For
example, username and timestamp are not logged for queries. This
options was introduced in MySQL 4.1.
`--log-slow-queries[=file]'
Log all queries that have taken more than `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.
*Note Slow query log::.
`--log-update[=file]'
Log updates to `file.#' where `#' is a unique number if not given.
*Note Update log::. The update log is deprecated and will be
removed in MySQL 5.0; you should use the binary log instead
(`--log-bin'). *Note Binary log::. Starting from version 5.0,
using `--log-update' will just turn on the binlog instead.
`--low-priority-updates'
Table-modifying operations (`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. *Note Table locking::.
`--memlock'
Lock the `mysqld' process in memory. This works only if your
system supports the `mlockall()' system call (like Solaris). This
may help if you have a problem where the operating system is
causing `mysqld' to swap on disk. 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...]]]'
Option is any combination of `DEFAULT', `BACKUP', `FORCE' or
`QUICK'. You can also set this explicitly to `""' if you want to
disable this option. If this option is used, `mysqld' will on open
check if the table is marked as crashed or if the table wasn't
closed properly. (The last option only works if you are running
with `--skip-external-locking'.) If this is the case `mysqld'
will run check on the table. If the table was corrupted, `mysqld'
will attempt to repair it.
The following options affects how the repair works.
*Option* *Description*
DEFAULT The same as not giving any option to
`--myisam-recover'.
BACKUP If the data table was changed during recover,
save a backup of the
`table_name.MYD' datafile as
`table_name-datetime.BAK'.
FORCE Run recover even if we will lose more than one
row from the .MYD file.
QUICK Don't check the rows in the table if there
aren't any delete blocks.
options `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'
From version 4.0.12, the `--new' option can be used to make the
server behave as 4.1 in certain aspects, easing a 4.0 to 4.1
upgrade:
* `TIMESTAMP' is returned as a string with the format
`'YYYY-MM-DD HH:MM:SS''. *Note Column types::.
`--pid-file=path'
Path to pid file used by `mysqld_safe'.
`-P, --port=...'
Port number to listen for TCP/IP connections.
`-o, --old-protocol'
Use the 3.20 protocol for compatibility with some very old clients.
*Note Upgrading-from-3.20::.
`--one-thread'
Only use one thread (for debugging under Linux). *Note Debugging
server::.
`--open-files-limit='
To change the number of file descriptors available to `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'.
`-O, --set-variable var=option'
Give a variable a value. `--help' lists variables. You can find a
full description for all variables in the `SHOW VARIABLES' section
in this manual. *Note SHOW VARIABLES::. The tuning server
parameters section includes information of how to optimise these.
Please note that `--set-variable' is deprecated since MySQL 4.0,
just use `--var=option' on its own. *Note Server parameters::.
In MySQL 4.0.2 one can set a variable directly with
`--variable-name=option' and `set-variable' is no longer needed in
option files.
Note that when setting a variable to a value, MySQL may
automatically correct it to stay within a given range and also
adjusts the value a little to fix for the used algorithm.
`--safe-mode'
Skip some optimise stages.
`--safe-show-database'
With this option, the `SHOW DATABASES' command returns only those
databases for which the user has some kind of privilege. From
version 4.0.2 this option is deprecated and doesn't do anything
(the option is enabled by default) as we now have the `SHOW
DATABASES' privilege. *Note GRANT::.
`--safe-user-create'
If this is enabled, a user can't create new users with the GRANT
command, if the user doesn't have `INSERT' privilege to the
`mysql.user' table or any column in this table.
`--skip-bdb'
Disable usage of BDB tables. This will save memory and may speed
up some things.
`--skip-concurrent-insert'
Turn off the ability to select and insert at the same time on
`MyISAM' tables. (This is only to be used if you think you have
found a bug in this feature.)
`--skip-delay-key-write'
In MySQL 4.0.3 you should use -delay-key-write=OFF instead.
Ignore the `DELAY_KEY_WRITE' option for all tables. *Note Server
parameters::.
using the grant tables again by
executing `mysqladmin flush-privileges' or `mysqladmin reload'.)
`--skip-host-cache'
Never use host name cache for faster name-ip resolution, but query
DNS server on every connect instead. *Note DNS::.
`--skip-innodb'
Disable usage of Innodb tables. This will save memory and disk
space and speed up some things.
`--skip-external-locking (was --skip-locking)'
Don't use system locking. To use `isamchk' or `myisamchk' you must
shut down the server. *Note Stability::. Note that in MySQL
Version 3.23 you can use `REPAIR' and `CHECK' to repair/check
`MyISAM' tables.
`--skip-name-resolve'
Hostnames are not resolved. All `Host' column values in the grant
tables must be IP numbers or `localhost'. *Note DNS::.
`--skip-networking'
Don't listen for TCP/IP connections at all. All interaction with
`mysqld' must be made via named pipes or Unix sockets. This
option is highly recommended for systems where only local requests
are allowed. *Note DNS::.
`--skip-new'
Don't use new, possibly wrong routines.
`--skip-symlink'
Deprecated option in 4.0.13; use `--skip-symbolic-links' instead.
`--symbolic-links, --skip-symbolic-links'
Enable or disable symbolic link support. This option has different
effects on Windows and Unix.
Windows symbolic links::.
On Unix, enabling symbolic links means that you can link a
`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 will be deleted or renamed.
`--skip-safemalloc'
If MySQL is configured with `--with-debug=full', all programs
check memory for overruns for every memory allocation and memory
freeing operations. 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'
Don't allow the `SHOW DATABASES' command, unless the user has the
`SHOW DATABASES' privilege.
*Note Debugging server::.
`--skip-thread-priority'
Disable using thread priorities for faster response time.
`--socket=path'
On Unix, the socket file to use for local connections (default
`/tmp/mysql.sock'). On Windows, the pipe name to use for local
connections that use a named pipe (default `MySql').
`--sql-mode=value[,value[,value...]]'
The option values can be any combination of: `REAL_AS_FLOAT',
`PIPES_AS_CONCAT', `ANSI_QUOTES', `IGNORE_SPACE',
`ONLY_FULL_GROUP_BY', `NO_UNSIGNED_SUBTRACTION',
`NO_AUTO_VALUE_ON_ZERO', `NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS',
`NO_KEY_OPTIONS', `NO_DIR_IN_CREATE', `MYSQL323', `MYSQL40', `DB2',
`MAXDB', `MSSQL', `ORACLE', `POSTGRESQL', or `ANSI'. The value
also can be empty (`--sql-mode=""') if you want to reset it.
`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 behaviour 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 recommended, 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, when this option
value became available, `mysqldump' automatically includes dump
output to enable `NO_AUTO_VALUE_ON_ZERO'.)
Several of the option values are used for compatibility with other
servers. If specified, they cause the server to omit from the
output of `SHOW CREATE TABLE' those parts of the statement that
are not understood by earlier versions of MySQL or other database
servers. Using these option values results in `CREATE TABLE'
statements that are more portable for use with other servers:
* The `NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS',
`NO_DIR_IN_CREATE', and `NO_KEY_OPTIONS' values cause
omission of table options, or options pertaining to column or
index definitions.
* The values `MYSQL323' and `MYSQL40' are for compatibility with
MySQL 3.23 and MySQL 4.0.
* The values used for compatibility with other servers are
`DB2', `MAXDB', `MSSQL', `ORACLE', and `POSTGRESQL'.
These options also affect the output of `mysqldump', because that
program uses `SHOW CREATE TABLE' to obtain the table-creation
statements that it includes in its own output.
Several of the option values have a complex effect because they
are shorthand for a group or set of values. For example, you can
tell the server to run in ANSI mode by using the `--sql-mode=ANSI'
(or `--ansi') option, which is equivalent to specifying both of
the following command-line options:
--sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY
--transaction-isolation=SERIALIZABLE
Note that specifying ANSI mode in this way also has the effect of
setting the transaction isolation level. For more information
about running the server in ANSI mode, see *Note ANSI mode::.
Other "group" values are `DB2', `MAXDB', `MSSQL', `ORACLE', and
`POSTGRESQL'. Specifying any of them turns on the
`PIPES_AS_CONCAT', `ANSI_QUOTES', `IGNORE_SPACE',
`NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS', and `NO_KEY_OPTIONS'
values.
The `--sql-mode' option was added in MySQL 3.23.41. The
`NO_UNSIGNED_SUBTRACTION' value was added in 4.0.0.
`NO_DIR_IN_CREATE' was added in 4.0.15. `NO_AUTO_VALUE_ON_ZERO',
`NO_TABLE_OPTIONS', `NO_FIELD_OPTIONS', `NO_KEY_OPTIONS',
`MYSQL323', `MYSQL40', `DB2', `MAXDB', `MSSQL', `ORACLE',
`POSTGRESQL', and `ANSI' were added in 4.1.1.
`--temp-pool'
Using this option will cause most temporary files created by the
server to use a small set of names, rather than a unique name for
each new file. This is to work around a problem in the Linux kernel
dealing with creating many new files with different names. With
the old behaviour, Linux seems to "leak" memory, as it's being
allocated to the directory entry cache rather than to the disk
cache.
`-t, --tmpdir=path'
Path of the directory to use for creating temporary files. It may
be useful if your default `/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. If it is a slave, it needs
some of its temporary files (for replication of temporary tables
or of `LOAD DATA INFILE') to survive a machine's reboot, so a
memory-based `tmpdir' which is cleared when the machine reboots is
not suitable; a disk-based `tmpdir' is necessary.
`-u, --user={user_name | user_id}'
Run the `mysqld' server as the user having the name `user_name' or
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'.
*Note Security::.
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.
`-V, --version'
Display version information and exit.
`-W, --log-warnings'
Print out warnings like `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). *Note
Communication errors::.
This option used to be called `--warnings'.
You can change most values for a running server with the `SET' command.
*Note `SET OPTION': SET OPTION.
`my.cnf' Option Files
---------------------
MySQL can, since Version 3.22, read default startup options for the
server and for clients from option files.
On Windows, MySQL reads default options from the following files:
*Filename* *Purpose*
`windows-directory\my.ini'Global options
`C:\my.cnf' Global options
`windows-directory' is the location of your Windows directory.
On Unix, MySQL reads default options from the following files:
`--defaults-extra-file=path'
`~/.my.cnf' User-specific options
at configuration time, not the one specified with `--datadir'
when `mysqld' starts up! (`--datadir' has no effect on where the
server looks for option files, because the server looks for files
before it processes any command-line arguments.)
Note that on Windows you should specify all paths in option files with
`/' instead of `\'. If you use `\', you need to specify it twice,
because `\' is the escape character in MySQL.
MySQL tries to read option files in the order listed above. If
multiple option files exist, an option specified in a file read later
takes precedence over the same option specified in a file read earlier.
Options specified on the command-line take precedence over options
specified in any option file. Some options can be specified using
environment variables. Options specified on the command-line or in
option files take precedence over environment variable values. *Note
Environment variables::.
The following programs support option files: `mysql', `mysqladmin',
`mysqld', `mysqld_safe', `mysql.server', `mysqldump', `mysqlimport',
`mysqlshow', `mysqlcheck', `myisamchk', and `myisampack'.
Since Version 4.0.2, you can use the `loose' prefix for command-line
options (or options in my.cnf). If an option is prefixed by `loose', the
program reading it will not exit with an error if an option is unknown,
but will rather only issue a warning:
shell> mysql --loose-no-such-option
Any long option that may be given on the command-line when running a
MySQL program can be given in an option file as well (without the
leading double dash). Run the program with the `--help' option to get
a list of available options.
An option file can contain lines of the following forms:
`#comment'
Comment lines start with `#' or `;'. Empty lines are ignored.
of the option file or
another group line is given.
`option'
This is equivalent to `--option' on the command-line.
`option=value'
This is equivalent to `--option=value' on the command-line.
`set-variable = variable=value'
This is equivalent to `--set-variable variable=value' on the
command-line. Please note that `--set-variable' is deprecated
since MySQL 4.0; as of that version, program variable names can be
used as option names. On the command line, just use
`--variable=value'. In an option file, use `variable=value'.
The `[client]' group allows you to specify options that apply to all
MySQL clients (not `mysqld'). This is the perfect group to use to
specify the password that you use to connect to the server. (But make
sure the option file is readable and writable only by yourself.)
Note that for options and values, all leading and trailing blanks are
automatically deleted. You may use the escape sequences `\b', `\t',
`\n', `\r', `\\', and `\s' in your value string (`\s' == blank).
Here is a typical global option file:
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock
set-variable = key_buffer_size=16M
set-variable = max_allowed_packet=1M
[mysqldump]
quick
Here is typical user option file:
[client]
# The following password will be sent to all standard MySQL clients
password=my_password
[mysql]
no-auto-rehash
set-variable = connect_timeout=2
[mysqlhotcopy]
interactive-timeout
where `DIR' is the pathname to the MySQL installation directory
(typically `C:\mysql' or `/usr/local/mysql'). Currently there are
sample configuration files for small, medium, large, and very large
systems. You can copy `my-xxxx.cnf' to your home directory (rename the
copy to `.my.cnf') to experiment with this.
All MySQL programs that support option files support the following
options:
that it will get.
`--defaults-file=full-path-to-default-file'Only use the given configuration file.
`--defaults-extra-file=full-path-to-default-file'Read this configuration file after the
global configuration file but before
the user configuration file.
Note for developers: Option file handling is implemented simply by
processing all matching options (that is, options in the appropriate
group) before any command-line arguments. This works nicely for
programs that use the last instance of an option that is specified
multiple times. If you have an old program that handles multiply
specified options this way but doesn't read option files, you need add
only two lines to give it that capability. Check the source code of
any of the standard MySQL clients to see how to do this.
In shell scripts, you can use the `my_print_defaults' command to parse
the option files. The following example shows the output that
`my_print_defaults' might produce when asked to show the options found
in the `[client]' and `[mysql]' groups:
shell> my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash
Running Multiple MySQL Servers on the Same Machine
--------------------------------------------------
In some cases you might want to run multiple `mysqld' servers on the
same machine. For example, you might want to test a new MySQL release
while leaving your existing production setup undisturbed. Or you 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.)
When you run multiple servers on a single machine, each server must have
unique values for several operating parameters. 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'
*Note Command-line options::.
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.
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
binary MySQL versions (`.tar' files) in different locations and start
them using the command `./bin/mysqld_safe' under the corresponding base
directory of each installation. `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. *Note Option files::.
*Warning*: Normally you should never have two servers that update data
in the same databases! If your OS doesn't support fault-free system
locking, this may lead to unpleasant surprises! If (despite this
warning) you run multiple servers using the same data directory and
they have logging enabled, you must specify the names of the log files
using the appropriate options. Otherwise, the servers may try to log
to the same files.
* The primary problem is that NFS will become the speed bottleneck.
It is not meant for such use.
* You also will have to come up with a solution how to make sure
that two or more servers do not interfere with each other. At the
moment there is no platform that will 100% reliably do the file
locking in every situation (usually this is handled by the `lockd'
daemon). Yet there would be one more possible risk with NFS; it
would make the work even more complicated for `lockd' daemon to
handle.
Make it easy for yourself: Forget about sharing a data directory among
servers over NFS. A better solution is to have one computer with an
operating system that efficiently handles threads and have several CPUs
in it.
Running Multiple Servers on Windows
...................................
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 services and running them that way. General
instructions for running MySQL servers from the command line or as
services are given in *Note 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 *Note Multiple servers::.)
Starting Multiple Windows Servers at the Command Line
.....................................................
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 `C:\my-opts1.cnf' that looks like this:
[mysqld]
datadir = C:/mydata1
port = 3307
Create a second file `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
server
must have 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.
Starting Multiple Windows Servers as Services
.............................................
all the
other parameters that must be unique per server.
For the following instructions, assume that you want to run `mysqld-nt'
servers from two different versions of MySQL that are installed at
`C:\mysql-4.0.8' and `C:\mysql-4.0.14', respectively. (This might be
the case if you're running 4.0.8 as your production server, but want to
test 4.0.14 before upgrading to it.)
The following principles are relevant when installing a MySQL service
with the `--install' (or `--install-manual') option:
* If you specify no service name, the server uses the default
service name of `MySql' and the server reads options from the
`[mysqld]' group in the standard option files.
These principles give you 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.
* Specify the options for all services in one of the standard option
files. To do this, use the `[mysqld]' group for the server that
is installed under the default service name (`MySql'). For other
servers, use a group name that is the same as the service name.
Suppose you want to run the 4.0.8 `mysqld-nt' using the default
service name and the 4.0.14 `mysqld-nt' using the service name
`mysqld2'. In this case, you can use the `[mysqld]' group for
4.0.8 and the `[mysqld2]' group for 4.0.14. For example, you can
set up `C:\my.cnf' like this:
# options for default service (MySql)
[mysqld]
basedir = C:/mysql-4.0.8
port = 3307
enable-named-pipe
socket = mypipe1
# options for mysqld2 service
[mysqld2]
basedir = C:/mysql-4.0.14
port = 3308
enable-named-pipe
socket = mypipe2
Install the services like this:
shell> C:\mysql-4.0.8\bin\mysqld-nt --install
shell> C:\mysql-4.0.14\bin\mysqld-nt --install mysqld2
To start the services, use the services manager, or use `NET START'
with the appropriate service names:
shell> NET START MySql
shell> NET START mysqld2
To stop the services, use the services manager, or use `NET STOP'
with the same service names.
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 = mypipe1
For the 4.0.14 `mysqld-nt', create a file `C:\my-opts2.cnf' that
looks like this:
[mysqld]
basedir = C:/mysql-4.0.14
port = 3308
enable-named-pipe
socket = mypipe2
Install the services as follows (enter each command on a single
line):
shell> C:\mysql-4.0.8\bin\mysqld-nt --install MySql
--defaults-file=C:\my-opts1.cnf
shell> C:\mysql-4.0.14\bin\mysqld-nt --install mysqld2
--defaults-file=C:\my-opts2.cnf
explicitly, even though that is the default service name.
Start and stop the services 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.
Running Multiple Servers on Unix
................................
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.14
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.
to use by using
the `--protocol={TCP | SOCKET | PIPE | MEMORY}' option.
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 &
invoke from
the above shell. Thus, connections for those clients automatically will
be directed to the second server!
*Note 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. *Note `mysqld_multi': mysqld_multi.
Using Client Programs in a Multiple-Server Environment
......................................................
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:
* Start the client with `--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.
* As of MySQL 4.1, start the client with `--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.
* On Unix, set the `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. *Note Environment variables::.
* Specify the default socket and TCP/IP port in the `[client]' group
of an option file. Foe example, you can use `C:\my.cnf' on
Windows, or the `.my.cnf' file in your home directory on Unix.
*Note Option files::.
* In a C program, you can specify the port or socket arguments in the
`mysql_real_connect()' call. You can also have the program read
option files by calling `mysql_options()'. *Note C API
functions::.
* If you are using the Perl `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);
*Note Perl DBI Class::.
[Назад] [Содержание] [Вперед]
| Главная |