mysqld_multi
manage multiple MySQL servers
Synopsis
mysqld_multi
[options] {start|stop|report}
[GNR[,GNR] ...]
add an example, a script, a trick and tips
examples
no example yet ...
... Feel free to add your own example above to help other Linux-lovers !
description
mysqld_multi
is designed to manage several mysqld processes that
listen for connections on different Unix socket files and
TCP/IP ports. It can start or stop servers, or report their
current status.
mysqld_multi
searches for groups named [mysqldN] in my.cnf (or in
the file named by the --config-file
option). N can be any positive integer. This number
is referred to in the following discussion as the option
group number, or GNR. Group numbers distinguish
option groups from one another and are used as arguments to
mysqld_multi to specify which servers you want to
start, stop, or obtain a status report for. Options listed
in these groups are the same that you would use in the
[mysqld] group used for starting mysqld. (See, for
example, Section 2.11.1.2, “Starting and Stopping
MySQL Automatically”.) However, when using multiple
servers, it is necessary that each one use its own value for
options such as the Unix socket file and TCP/IP port number.
For more information on which options must be unique per
server in a multiple-server environment, see
Section 5.3, “Running Multiple MySQL Instances on
One Machine”.
To invoke
mysqld_multi, use the following syntax:
shell>
mysqld_multi [options] {start|stop|report}
[GNR[,GNR] ...]
start, stop,
and report indicate which operation to perform. You can
perform the designated operation for a single server or
multiple servers, depending on the GNR list that
follows the option name. If there is no list,
mysqld_multi performs the operation for all servers
in the option file.
Each GNR
value represents an option group number or range of group
numbers. The value should be the number at the end of the
group name in the option file. For example, the GNR
for a group named [mysqld17] is 17. To specify a range of
numbers, separate the first and last numbers by a dash. The
GNR value 10-13 represents groups [mysqld10]
through [mysqld13]. Multiple groups or group ranges can be
specified on the command line, separated by commas. There
must be no whitespace characters (spaces or tabs) in the
GNR list; anything after a whitespace character is
ignored.
This command
starts a single server using option group [mysqld17]:
shell>
mysqld_multi start 17
This command
stops several servers, using option groups [mysqld8] and
[mysqld10] through [mysqld13]:
shell>
mysqld_multi stop 8,10-13
For an example
of how you might set up an option file, use this
command:
shell>
mysqld_multi --example
mysqld_multi
searches for option files as follows:
• With
--no-defaults, no option files are
read.
• With
--defaults-file=file_name,
only the named file is read.
•
Otherwise, option files in the standard list of locations
are read, including any file named by the
--defaults-extra-file=file_name
option, if one is given. (If the option is given multiple
times, the last value is used.)
Option files
read are searched for [mysqld_multi] and [mysqldN]
option groups. The [mysqld_multi] group can be used for
options to mysqld_multi itself. [mysqldN]
groups can be used for options passed to specific
mysqld instances.
The [mysqld] or
[mysqld_safe] groups can be used for common options read by
all instances of mysqld or mysqld_safe. You
can specify a
--defaults-file=file_name
option to use a different configuration file for that
instance, in which case the [mysqld] or [mysqld_safe] groups
from that file will be used for that instance.
mysqld_multi
supports the following options.
•
--help
Display a help
message and exit.
•
--config-file=file_name
This option is
deprecated. If given, it is treated the same way as
--defaults-extra-file,
described earlier. --config-file
was removed in MySQL 5.5.3.
•
--example
Display a
sample option file.
•
--log=file_name
Specify the
name of the log file. If the file exists, log output is
appended to it.
•
--mysqladmin=prog_name
The
mysqladmin binary to be used to stop servers.
•
--mysqld=prog_name
The
mysqld binary to be used. Note that you can specify
mysqld_safe as the value for this option also. If you
use mysqld_safe to start the server, you can include
the mysqld or ledir options in the corresponding
[mysqldN] option group. These options indicate the
name of the server that mysqld_safe should start and
the path name of the directory where the server is located.
(See the descriptions for these options in
mysqld_safe(1).) Example:
[mysqld38]
mysqld = mysqld-debug
ledir = /opt/local/mysql/libexec
•
--no-log
Print log
information to stdout rather than to the log file. By
default, output goes to the log file.
•
--password=password
The password of
the MySQL account to use when invoking mysqladmin.
Note that the password value is not optional for this
option, unlike for other MySQL programs.
•
--silent
Silent mode;
disable warnings.
•
--tcp-ip
Connect to each
MySQL server through the TCP/IP port instead of the Unix
socket file. (If a socket file is missing, the server might
still be running, but accessible only through the TCP/IP
port.) By default, connections are made using the Unix
socket file. This option affects stop and report
operations.
•
--user=user_name
The user name
of the MySQL account to use when invoking
mysqladmin.
•
--verbose
Be more
verbose.
•
--version
Display version
information and exit.
Some notes
about mysqld_multi:
• Most
important: Before using mysqld_multi be sure that
you understand the meanings of the options that are passed
to the mysqld servers and why you would want
to have separate mysqld processes. Beware of the
dangers of using multiple mysqld servers with the
same data directory. Use separate data directories, unless
you know what you are doing. Starting multiple
servers with the same data directory does not give
you extra performance in a threaded system. See
Section 5.3, “Running Multiple MySQL Instances on
One Machine”.
•
Important
Make sure that the data directory for each server is fully
accessible to the Unix account that the specific
mysqld process is started as. Do not use the
Unix root account for this, unless you know
what you are doing. See Section 6.1.5, “How to
Run MySQL as a Normal User”.
• Make
sure that the MySQL account used for stopping the
mysqld servers (with the mysqladmin program)
has the same user name and password for each server. Also,
make sure that the account has the SHUTDOWN privilege. If
the servers that you want to manage have different user
names or passwords for the administrative accounts, you
might want to create an account on each server that has the
same user name and password. For example, you might set up a
common multi_admin account by executing the following
commands for each server:
shell>
mysql -u root -S /tmp/mysql.sock -p
Enter password:
mysql> GRANT SHUTDOWN ON *.*
-> TO 'multi_admin'@'localhost' IDENTIFIED BY
'multipass';
See
Section 6.2, “The MySQL Access Privilege
System”. You have to do this for each mysqld
server. Change the connection parameters appropriately when
connecting to each one. Note that the host name part of the
account name must permit you to connect as multi_admin from
the host where you want to run mysqld_multi.
• The Unix
socket file and the TCP/IP port number must be different for
every mysqld. (Alternatively, if the host has
multiple network addresses, you can use
--bind-address to cause different
servers to listen to different interfaces.)
• The
--pid-file option is very important
if you are using mysqld_safe to start mysqld
(for example, --mysqld=mysqld_safe) Every
mysqld should have its own process ID file. The
advantage of using mysqld_safe instead of
mysqld is that mysqld_safe monitors its
mysqld process and restarts it if the process
terminates due to a signal sent using kill -9 or for
other reasons, such as a segmentation fault. Please note
that the mysqld_safe script might require that you
start it from a certain place. This means that you might
have to change location to a certain directory before
running mysqld_multi. If you have problems starting,
please see the mysqld_safe script. Check especially
the lines:
----------------------------------------------------------------
MY_PWD=’pwd’
# Check if we are starting this relative (for the binary
release)
if test -d $MY_PWD/data/mysql -a \
-f ./share/mysql/english/errmsg.sys -a \
-x ./bin/mysqld
----------------------------------------------------------------
The test
performed by these lines should be successful, or you might
encounter problems. See mysqld_safe(1).
• You
might want to use the --user option for
mysqld, but to do this you need to run the
mysqld_multi script as the Unix superuser (root).
Having the option in the option file doesn't matter; you
just get a warning if you are not the superuser and the
mysqld processes are started under your own Unix
account.
The
following example shows how you might set up an option file
for use with mysqld_multi. The order in which the
mysqld programs are started or stopped depends on the
order in which they appear in the option file. Group numbers
need not form an unbroken sequence. The first and fifth
[mysqldN] groups were intentionally omitted from the
example to illustrate that you can have “gaps”
in the option file. This gives you more
flexibility.
# This
file should probably be in your home dir (~/.my.cnf)
# or /etc/my.cnf
# Version 2.1 by Jani Tolonen
[mysqld_multi]
mysqld = /usr/local/bin/mysqld_safe
mysqladmin = /usr/local/bin/mysqladmin
user = multi_admin
password = multipass
[mysqld2]
socket = /tmp/mysql.sock2
port = 3307
pid-file = /usr/local/mysql/var2/hostname.pid2
datadir = /usr/local/mysql/var2
language = /usr/local/share/mysql/english
user = john
[mysqld3]
socket = /tmp/mysql.sock3
port = 3308
pid-file = /usr/local/mysql/var3/hostname.pid3
datadir = /usr/local/mysql/var3
language = /usr/local/share/mysql/swedish
user = monty
[mysqld4]
socket = /tmp/mysql.sock4
port = 3309
pid-file = /usr/local/mysql/var4/hostname.pid4
datadir = /usr/local/mysql/var4
language = /usr/local/share/mysql/estonia
user = tonu
[mysqld6]
socket = /tmp/mysql.sock6
port = 3311
pid-file = /usr/local/mysql/var6/hostname.pid6
datadir = /usr/local/mysql/var6
language = /usr/local/share/mysql/japanese
user = jani
See
Section 4.2.3.3, “Using Option
Files”.
copyright
Copyright © 1997, 2013, Oracle and/or its affiliates. All rights
reserved.
This software and related documentation are provided under a
license agreement containing restrictions on use and disclosure
and are protected by intellectual property laws. Except as
expressly permitted in your license agreement or allowed by law,
you may not use, copy, reproduce, translate, broadcast, modify,
license, transmit, distribute, exhibit, perform, publish, or
display any part, in any form, or by any means. Reverse
engineering, disassembly, or decompilation of this software,
unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without
notice and is not warranted to be error-free. If you find any
errors, please report them to us in writing.
If this software or related documentation is delivered to the
U.S. Government or anyone licensing it on behalf of the U.S.
Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related
documentation and technical data delivered to U.S. Government
customers are "commercial computer software" or "commercial
technical data" pursuant to the applicable Federal Acquisition
Regulation and agency-specific supplemental regulations. As such,
the use, duplication, disclosure, modification, and adaptation
shall be subject to the restrictions and license terms set forth
in the applicable Government contract, and, to the extent
applicable by the terms of the Government contract, the
additional rights set forth in FAR 52.227-19, Commercial Computer
Software License (December 2007). Oracle USA, Inc., 500 Oracle
Parkway, Redwood City, CA 94065.
This software is developed for general use in a variety of
information management applications. It is not developed or
intended for use in any inherently dangerous applications,
including applications which may create a risk of personal
injury. If you use this software in dangerous applications, then
you shall be responsible to take all appropriate fail-safe,
backup, redundancy, and other measures to ensure the safe use of
this software. Oracle Corporation and its affiliates disclaim any
liability for any damages caused by use of this software in
dangerous applications.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. MySQL is a trademark of Oracle Corporation and/or its
affiliates, and shall not be used without Oracle’s express
written authorization. Other names may be trademarks of their
respective owners.
This software and documentation may provide access to or
information on content, products, and services from third
parties. Oracle Corporation and its affiliates are not
responsible for and expressly disclaim all warranties of any kind
with respect to third-party content, products, and services.
Oracle Corporation and its affiliates will not be responsible for
any loss, costs, or damages incurred due to your access to or use
of third-party content, products, or services.
This document in any form, software or printed matter, contains
proprietary information that is the exclusive property of Oracle.
Your access to and use of this material is subject to the terms
and conditions of your Oracle Software License and Service
Agreement, which has been executed and with which you agree to
comply. This document and information contained herein may not be
disclosed, copied, reproduced, or distributed to anyone outside
Oracle without prior written consent of Oracle or as specifically
provided below. This document is not part of your license
agreement nor can it be incorporated into any contractual
agreement with Oracle or its subsidiaries or affiliates.
This documentation is NOT distributed under a GPL license. Use of
this documentation is subject to the following terms:
You may create a printed copy of this documentation solely for
your own personal use. Conversion to other formats is allowed as
long as the actual content is not altered or edited in any way.
You shall not publish or distribute this documentation in any
form or on any media, except if you distribute the documentation
in a manner similar to how Oracle disseminates it (that is,
electronically for download on a Web site with the software) or
on a CD-ROM or similar medium, provided however that the
documentation is disseminated together with the software on the
same medium. Any other use, such as any dissemination of printed
copies or use of this documentation, in whole or in part, in
another publication, requires the prior written consent from an
authorized representative of Oracle. Oracle and/or its affiliates
reserve any and all rights to this documentation not expressly
granted above.
For more information on the terms of this license, or for details
on how the MySQL documentation is built and produced, please
visit MySQL Contact &
Questions.
For additional licensing information,
including licenses for third-party libraries used by MySQL
products, see Preface and Legal Notices.
For help with using MySQL, please visit
either the MySQL
Forums or MySQL Mailing Lists where you can discuss your issues with other MySQL
users.
For additional documentation on MySQL
products, including translations of the documentation into other
languages, and downloadable versions in variety of formats,
including HTML and PDF formats, see the MySQL Documentation Library.
mysqld multi
[
GNR[,GNR] ...]
see also
For
more information, please refer to the MySQL Reference
Manual, which may already be installed locally and which is
also available online at
http://dev.mysql.com/doc/.
author
Oracle
Corporation (http://dev.mysql.com/).