Index of Section 8 Manual Pages
| Interix / SUA | mysqlmanager.8 | Interix / SUA |
MYSQLMANAGER(8) MySQL Database System MYSQLMANAGER(8)
NAME
mysqlmanager - the MySQL Instance Manager
SYNOPSIS
mysqlmanager [options]
DESCRIPTION
mysqlmanager is the MySQL Instance Manager (IM). This
program monitors and manages MySQL Database Server
instances. MySQL Instance Manager is available for
Unix-like operating systems, and also on Windows as of
MySQL 5.0.13. It runs as a daemon that listens on a TCP/IP
port. On Unix, it also listens on a Unix socket file.
MySQL Instance Manager is included in MySQL distributions
from version 5.0.3, and can be used in place of the
mysqld_safe script to start and stop one or more instances
of MySQL Server. Because Instance Manager can manage
multiple server instances, it can also be used in place of
the mysqld_multi script. Instance Manager offers these
capabilities:
o Instance Manager can start and stop instances, and
report on the status of instances.
o Server instances can be treated as guarded or
unguarded:
o When Instance Manager starts, it starts each guarded
instance. If the instance crashes, Instance Manager
detects this and restarts it. When Instance Manager
stops, it stops the instance.
o A nonguarded instance is not started when Instance
Manager starts or monitored by it. If the instance
crashes after being started, Instance Manager does
not restart it. When Instance Manager exits, it does
not stop the instance if it is running.
Instances are guarded by default. An instance can be
designated as nonguarded by including the nonguarded
option in the configuration file.
o Instance Manager provides an interactive interface for
configuring instances, so that the need to edit the
configuration file manually is reduced or eliminated.
o Instance Manager provides remote instance management.
That is, it runs on the host where you want to control
MySQL Server instances, but you can connect to it from
a remote host to perform instance-management
operations.
The following sections describe MySQL Instance Manager
operation in more detail.
MYSQL INSTANCE MANAGER COMMAND OPTIONS
The MySQL Instance Manager supports a number of command
options. For a brief listing, invoke mysqlmanager with the
--help option. Options may be given on the command line or
in the Instance Manager configuration file. On Windows,
the standard configuration file is my.ini in the directory
where Instance Manager is installed. On Unix, the standard
file is /etc/my.cnf. To specify a different configuration
file, start Instance Manager with the --defaults-file
option.
mysqlmanager supports the following options:
o --help, -?
Display a help message and exit.
o --angel-pid-file=file_name
The file in which the angel process records its process
ID when mysqlmanager runs in daemon mode (that is, when
the --run-as-service option is given). The default
filename is mysqlmanager.angel.pid.
If the --angel-pid-file option is not given, the
default angel PID file has the same name as the PID
file except that any PID file extension is replaced
with an extension of mysqlmanager.pid becomes
mysqlmanager.angel.pid.)
This option was added in MySQL 5.0.23.
o --bind-address=IP
The IP address to bind to.
o --default-mysqld-path=path
The pathname of the MySQL Server binary. This pathname
is used for all server instance sections in the
configuration file for which no mysqld-path option is
present. The default value of this option is the
compiled-in pathname, which depends on how the MySQL
distribution was configured. Example:
--default-mysqld-path=/usr/sbin/mysqld
o --defaults-file=file_name
Read Instance Manager and MySQL Server settings from
the given file. All configuration changes made by the
Instance Manager will be written to this file. This
must be the first option on the command line if it is
used, and the file must exist.
If this option is not given, Instance Manager uses its
standard configuration file. On Windows, the standard
file is my.ini in the directory where Instance Manager
is installed. On Unix, the standard file is
/etc/my.cnf.
o --install
On Windows, install Instance Manager as a Windows
service. The service name is MySQL Manager. This option
was added in MySQL 5.0.11.
o --log=file_name
The path to the Instance Manager log file. This option
has no effect unless the --run-as-service option is
also given. If the filename specified for the option is
a relative name, the log file is created under the
directory from which Instance Manager is started. To
ensure that the file is created in a specific
directory, specify it as a full pathname.
If --run-as-service is given without --log, the log
file is mysqlmanager.log in the data directory.
If --run-as-service is not given, log messages go to
the standard output. To capture log output, you can
redirect Instance Manager output to a file:
mysqlmanager > im.log
o --monitoring-interval=seconds
The interval in seconds for monitoring server
instances. The default value is 20 seconds. Instance
Manager tries to connect to each monitored (guarded)
instance using the non-existing MySQL_Instance_Manager
user account to check whether it is alive/not hanging.
If the result of the connection attempt indicates that
the instance is unavailable, Instance Manager performs
several attempts to restart the instance.
Normally, the MySQL_Instance_Manager account does not
exist, so the connection attempts by Instance Manager
cause the monitored instance to produce messages in its
general query log similar to the following:
Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES)
The nonguarded option in the appropriate server instance
section disables monitoring for a particular instance. If
the instance dies after being started, Instance Manager
will not restart it. Instance Manager tries to connect to
a nonguarded instance only when you request the instance's
status (for example, with the SHOW INSTANCES status.
See the section called "MYSQL SERVER INSTANCE STATUS
MONITORING", for more information.
o --passwd, -P
Prepare an entry for the password file, print it to the
standard output, and exit. You can redirect the output
from Instance Manager to a file to save the entry in
the file.
o --password-file=file_name
The name of the file where the Instance Manager looks
for users and passwords. On Windows, the default is
mysqlmanager.passwd in the directory where Instance
Manager is installed. On Unix, the default file is
/etc/mysqlmanager.passwd.
o --pid-file=file_name
The process ID file to use. On Windows, the default
file is mysqlmanager.pid in the directory where
Instance Manager is installed. On Unix, the default is
mysqlmanager.pid in the data directory.
o --port=port_num
The port number to use when listening for TCP/IP
connections from clients. The default port number
(assigned by IANA) is 2273.
o --print-defaults
Print the current defaults and exit. This must be the
first option on the command line if it is used.
o --remove
On Windows, removes Instance Manager as a Windows
service. This assumes that Instance Manager has been
run with --install previously. This option was added in
MySQL 5.0.11.
o --run-as-service
On Unix, daemonize and start an angel process. The
angel process monitors Instance Manager and restarts it
if it crashes. (The angel process itself is simple and
unlikely to crash.)
o --socket=path
On Unix, the socket file to use for incoming
connections. The default file is named
/tmp/mysqlmanager.sock. This option has no meaning on
Windows.
o --standalone
This option is used on Windows to run Instance Manager
in standalone mode. You should specify it when you
start Instance Manager from the command line. This
option was added in MySQL 5.0.13.
o --user=user_name
On Unix, the username of the system account to use for
starting and running mysqlmanager. This option
generates a warning and has no effect unless you start
mysqlmanager as root (so that it can change its
effective user ID), or as the named user. It is
recommended that you configure mysqlmanager to run
using the same account used to run the mysqld server.
("User" in this context refers to a system login
account, not a MySQL user listed in the grant tables.)
o --version, -V
Display version information and exit.
o --wait-timeout=N
The number of seconds to wait for activity on an
incoming connection before closing it. The default is
28800 seconds (8 hours).
This option was added in MySQL 5.0.19. Before that, the
timeout is 30 seconds and cannot be changed.
MYSQL INSTANCE MANAGER CONFIGURATION FILES
Instance Manager uses its standard configuration file
unless it is started with a --defaults-file option that
specifies a different file. On Windows, the standard file
is my.ini in the directory where Instance Manager is
installed. On Unix, the standard file is /etc/my.cnf.
(Prior to MySQL 5.0.10, the MySQL Instance Manager read
the same configuration files as the MySQL Server,
including /etc/my.cnf, ~/.my.cnf, and so forth.)
Instance Manager reads options for itself from the
[manager] section of the configuration file, and options
for server instances from [mysqld] or [mysqldN] sections.
The [manager] section contains any of the options listed
in the section called "MYSQL INSTANCE MANAGER COMMAND
OPTIONS", except for those specified as having to be given
as the first option on the command line. Here is a sample
[manager] section:
# MySQL Instance Manager options section
[manager]
default-mysqld-path = /usr/local/mysql/libexec/mysqld
socket=/tmp/manager.sock
pid-file=/tmp/manager.pid
password-file = /home/cps/.mysqlmanager.passwd
monitoring-interval = 2
port = 1999
bind-address = 192.168.1.5
Each [mysqld] or [mysqldN] instance section specifies
options given by Instance Manager to a server instance at
startup. These are mainly common MySQL Server options (see
Section 1.2, "Command Options"). In addition, a [mysqldN]
section can contain the options in the following list,
which are specific to Instance Manager. These options are
interpreted by Instance Manager itself; it does not pass
them to the server when it attempts to start that server.
Warning
The Instance Manager-specific options must not be used in
a [mysqld] section. If a server is started without using
Instance Manager, it will not recognize these options and
will fail to start properly.
o mysqld-path = path
The pathname of the mysqld server binary to use for the
server instance.
o nonguarded
This option disables Instance Manager monitoring
functionality for the server instance. By default, an
instance is guarded: At Instance Manager start time, it
starts the instance. It also monitors the instance
status and attempts to restart it if it fails. At
Instance Manager exit time, it stops the instance. None
of these things happen for nonguarded instances.
o shutdown-delay = seconds
The number of seconds Instance Manager should wait for
the server instance to shut down. The default value is
35 seconds. After the delay expires, Instance Manager
assumes that the instance is hanging and attempts to
terminate it. If you use InnoDB with large tables, you
should increase this value.
Here are some sample instance sections:
[mysqld1]
mysqld-path=/usr/local/mysql/libexec/mysqld
socket=/tmp/mysql.sock
port=3307
server_id=1
skip-stack-trace
core-file
skip-bdb
log-bin
log-error
log=mylog
log-slow-queries
[mysqld2]
nonguarded
port=3308
server_id=2
mysqld-path= /home/cps/mysql/trees/mysql-5.0/sql/mysqld
socket = /tmp/mysql.sock5
pid-file = /tmp/hostname.pid5
datadir= /home/cps/mysql_data/data_dir1
language=/home/cps/mysql/trees/mysql-5.0/sql/share/english
log-bin
log=/tmp/fordel.log
STARTING THE MYSQL SERVER WITH MYSQL INSTANCE MANAGER
This section discusses how Instance Manager starts server
instances when it starts. However, before you start
Instance Manager, you should set up a password file for
it. Otherwise, you will not be able to connect to Instance
Manager to control it after it starts. For details about
creating Instance Manager accounts, see the section called
"INSTANCE MANAGER USER AND PASSWORD MANAGEMENT".
On Unix, the mysqld MySQL database server normally is
started with the mysql.server script, which usually
resides in the /etc/init.d/ directory. In MySQL 5.0.3,
this script invokes mysqlmanager (the MySQL Instance
Manager binary) to start MySQL. (In prior versions of
MySQL the mysqld_safe script is used for this purpose.)
Starting from MySQL 5.0.4, the behavior of the startup
script was changed again to incorporate both setup
schemes. In version 5.0.4, the startup script uses the old
scheme (invoking mysqld_safe) by default, but one can set
the use_mysqld_safe variable in the script to 0 (zero) to
use the MySQL Instance Manager to start a server.
Starting with MySQL 5.0.19, you can use Instance Manager
if you modify the my.cnf configuration file by adding
use-manager to the [mysql.server] section:
[mysql.server]
use-manager
When Instance Manager starts, it reads its configuration
file if it exists to find server instance sections and
prepare a list of instances. Instance sections have names
of the form [mysqld] or [mysqldN], where N is an unsigned
integer (for example, [mysqld1], [mysqld2], and so forth).
After preparing the list of instances, Instance Manager
starts the guarded instances in the list. If there are no
instances, Instance Manager creates an instance named
mysqld and attempts to start it with default (compiled-in)
configuration values. This means that the Instance Manager
cannot find the mysqld program if it is not installed in
the default location. (Section 4.6, "Installation
Layouts", describes default locations for components of
MySQL distributions.) If you have installed the MySQL
server in a non-standard location, you should create the
Instance Manager configuration file.
Instance Manager also stops all guarded server instances
when it shuts down.
The allowable options for [mysqldN] server instance
sections are described in the section called "MYSQL
INSTANCE MANAGER CONFIGURATION FILES". In these sections,
you can use a special mysqld-path=path-to-mysqld-binary
option that is recognized only by Instance Manager. Use
this option to let Instance Manager know where the mysqld
binary resides. If there are multiple instances, it may
also be necessary to set other options such as datadir and
port, to ensure that each instance has a different data
directory and TCP/IP port number. Section 7, "Running
Multiple MySQL Servers on the Same Machine", discusses the
configuration values that must differ for each instance
when you run multiple instance on the same machine.
Warning
The [mysqld] instance section, if it exists, must not
contain any Instance Manager-specific options.
The typical Unix startup/shutdown cycle for a MySQL server
with the MySQL Instance Manager enabled is as follows:
1. The /etc/init.d/mysql script starts MySQL Instance
Manager.
2. Instance Manager starts the guarded server instances
and monitors them.
3. If a server instance fails, Instance Manager restarts
it.
4. If Instance Manager is shut down (for example, with the
/etc/init.d/mysql stop command), it shuts down all
server instances.
INSTANCE MANAGER USER AND PASSWORD MANAGEMENT
The Instance Manager stores its user information in a
password file. On Windows, the default is
mysqlmanager.passwd in the directory where Instance
Manager is installed. On Unix, the default file is
/etc/mysqlmanager.passwd. To specify a different location
for the password file, use the --password-file option.
If the password file does not exist or contains no
password entries, you cannot connect to the Instance
Manager.
Note
Any Instance Manager process that is running to monitor
server instances does not notice changes to the password
file. You must stop it and restart it after making
password entry changes.
Entries in the password file have the following format,
where the two fields are the account username and
encrypted password, separated by a colon:
petr:*35110DC9B4D8140F5DE667E28C72DD2597B5C848
Instance Manager password encryption is the same as that
used by MySQL Server. It is a one-way operation; no means
are provided for decrypting encrypted passwords.
Instance Manager accounts differ somewhat from MySQL
Server accounts:
o MySQL Server accounts are associated with a hostname,
username, and password (see Section 5.1, "MySQL
Usernames and Passwords").
o Instance Manager accounts are associated with a
username and password only.
This means that a client can connect to Instance Manager
with a given username from any host. To limit connections
so that clients can connect only from the local host,
start Instance Manager with the --bind-address=127.0.0.1
option so that it listens only to the local network
interface. Remote clients will not be able to connect.
Local clients can connect like this:
shell> mysql -h 127.0.0.1 -P 2273
To generate a new entry, invoke Instance Manager with the
--passwd option and append the output to the
/etc/mysqlmanager.passwd file. Here is an example:
shell> mysqlmanager --passwd >> /etc/mysqlmanager.passwd
Creating record for new user.
Enter user name: mike
Enter password: mikepass
Re-type password: mikepass
At the prompts, enter the username and password for the
new Instance Manager user. You must enter the password
twice. It does not echo to the screen, so double entry
guards against entering a different password than you
intend (if the two passwords do not match, no entry is
generated).
The preceding command causes the following line to be
added to /etc/mysqlmanager.passwd:
mike:*BBF1F551DD9DD96A01E66EC7DDC073911BAD17BA
MYSQL SERVER INSTANCE STATUS MONITORING
To monitor the status of each guarded server instance, the
MySQL Instance Manager attempts to connect to the instance
at regular intervals using the
MySQL_Instance_Manager@localhost user account with a
password of check_connection.
You are not required to create this account for MySQL
Server; in fact, it is expected that it will not exist.
Instance Manager can tell that a server is operational if
the server accepts the connection attempt but refuses
access for the account by returning a login error.
However, these failed connection attempts are logged by
the server to its general query log (see Section 2.2, "The
General Query Log").
Instance Manager also attempts a connection to nonguarded
server instances when you use the SHOW INSTANCES or SHOW
INSTANCE STATUS command. This is the only status
monitoring done for nonguarded instances.
Instance Manager knows if a server instance fails at
startup because it receives a status from the attempt. For
an instance that starts but later crashes, Instance
Manager receives a signal because it is the parent process
of the instance.
CONNECTING TO MYSQL INSTANCE MANAGER
After you set up a password file for the MySQL Instance
Manager and Instance Manager is running, you can connect
to it. The MySQL client-server protocol is used to
communicate with the Instance Manager. For example, you
can connect to it using the standard mysql client program:
shell> mysql --port=2273 --host=im.example.org --user=mysql --password
Instance Manager supports the version of the MySQL
client-server protocol used by the client tools and
libraries distributed with MySQL 4.1 or later, so other
programs that use the MySQL C API also can connect to it.
MYSQL INSTANCE MANAGER COMMANDS
After you connect to MySQL Instance Manager, you can issue
commands. The following general principles apply to
Instance Manager command execution:
o Commands that take an instance name fail if the name is
not a valid instance name.
o Commands that take an instance name fail if the
instance does not exist.
o Instance Manager maintains information about instance
configuration in an internal (in-memory) cache.
Initially, this information comes from the
configuration file if it exists, but some commands
change the configuration of an instance. Commands that
modify the configuration file fail if the file does not
exist or is not accessible to Instance Manager.
o On Windows, the standard file is my.ini in the
directory where Instance Manager is installed. On Unix,
the standard configuration file is /etc/my.cnf. To
specify a different configuration file, start Instance
Manager with the --defaults-file option.
o If a [mysqld] instance section exists in the
configuration file, it must not contain any Instance
Manager-specific options (see the section called "MYSQL
INSTANCE MANAGER CONFIGURATION FILES"). Therefore, you
must not add any of these options if you change the
configuration for an instance named mysqld.
The following list describes the commands that Instance
Manager accepts, with examples.
o START INSTANCE instance_name
This command attempts to start an offline instance. The
command is asynchronous; it does not wait for the
instance to start.
mysql> START INSTANCE mysqld4;
Query OK, 0 rows affected (0,00 sec)
o STOP INSTANCE instance_name
This command attempts to stop an instance. The command
is synchronous; it waits for the instance to stop.
mysql> STOP INSTANCE mysqld4;
Query OK, 0 rows affected (0,00 sec)
o SHOW INSTANCES
Shows the names and status of all loaded instances.
mysql> SHOW INSTANCES;
+---------------+---------+
| instance_name | status |
+---------------+---------+
| mysqld3 | offline |
| mysqld4 | online |
| mysqld2 | offline |
+---------------+---------+
o SHOW INSTANCE STATUS instance_name
Shows status and version information for an instance.
mysql> SHOW INSTANCE STATUS mysqld3;
+---------------+--------+---------+
| instance_name | status | version |
+---------------+--------+---------+
| mysqld3 | online | unknown |
+---------------+--------+---------+
o SHOW INSTANCE OPTIONS instance_name
Shows the options used by an instance.
mysql> SHOW INSTANCE OPTIONS mysqld3;
+---------------+---------------------------------------------------+
| option_name | value |
+---------------+---------------------------------------------------+
| instance_name | mysqld3 |
| mysqld-path | /home/cps/mysql/trees/mysql-4.1/sql/mysqld |
| port | 3309 |
| socket | /tmp/mysql.sock3 |
| pid-file | hostname.pid3 |
| datadir | /home/cps/mysql_data/data_dir1/ |
| language | /home/cps/mysql/trees/mysql-4.1/sql/share/english |
+---------------+---------------------------------------------------+
o SHOW instance_name LOG FILES
The command lists all log files used by the instance.
The result set contains the path to the log file and
the log file size. If no log file path is specified in
the instance section of the configuration file (for
example, log=/var/mysql.log), the Instance Manager
tries to guess its placement. If Instance Manager is
unable to guess the log file placement you should
specify the log file location explicitly by using a log
option in the appropriate instance section of the
configuration file.
mysql> SHOW mysqld LOG FILES;
+-------------+------------------------------------+----------+
| Logfile | Path | Filesize |
+-------------+------------------------------------+----------+
| ERROR LOG | /home/cps/var/mysql/owlet.err | 9186 |
| GENERAL LOG | /home/cps/var/mysql/owlet.log | 471503 |
| SLOW LOG | /home/cps/var/mysql/owlet-slow.log | 4463 |
+-------------+------------------------------------+----------+
Log options are described in Section 1.2, "Command
Options".
o SHOW instance_name LOG {ERROR | SLOW | GENERAL}
size[,offset_from_end]
This command retrieves a portion of the specified log
file. Because most users are interested in the latest
log messages, the size parameter defines the number of
bytes to retrieve from the end of the log. To retrieve
data from the middle of the log file, specify the
optional offset_from_end parameter. The following
example retrieves 21 bytes of data, starting 23 bytes
before the end of the log file and ending 2 bytes
before the end:
mysql> SHOW mysqld LOG GENERAL 21, 2;
+---------------------+
| Log |
+---------------------+
| using password: YES |
+---------------------+
o SET instance_name.option_name[=option_value]
This command edits the specified instance's
configuration section to change or add instance
options. The option is added to the section is it is
not already present. Otherwise, the new setting
replaces the existing one.
mysql> SET mysqld2.port=3322;
Query OK, 0 rows affected (0.00 sec)
Changes made to the configuration file do not take effect
until the MySQL server is restarted. In addition, these
changes are not stored in the instance manager's local
cache of instance settings until a FLUSH INSTANCES command
is executed.
o UNSET instance_name.option_name
This command removes an option from an instance's
configuration section.
mysql> UNSET mysqld2.port;
Query OK, 0 rows affected (0.00 sec)
Changes made to the configuration file do not take effect
until the MySQL server is restarted. In addition, these
changes are not stored in the instance manager's local
cache of instance settings until a FLUSH INSTANCES command
is executed.
o FLUSH INSTANCES
This command forces Instance Manager reread the
configuration file and to refresh internal structures.
This command should be performed after editing the
configuration file. The command does not restart
instances.
mysql> FLUSH INSTANCES;
Query OK, 0 rows affected (0.04 sec)
FLUSH INSTANCES is deprecated and will be removed in MySQL
5.2.
COPYRIGHT
Copyright 2007-2008 MySQL AB
This documentation is free software; you can redistribute
it and/or modify it under the terms of the GNU General
Public License as published by the Free Software
Foundation; version 2 of the License.
This documentation is distributed in the hope that it will
be useful, but WITHOUT ANY WARRANTY; without even the
implied warranty of MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE. See the GNU General Public License for
more details.
You should have received a copy of the GNU General Public
License along with the program; if not, write to the Free
Software Foundation, Inc., 51 Franklin Street, Fifth
Floor, Boston, MA 02110-1301 USA or see
http://www.gnu.org/licenses/.
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
MySQL AB (http://www.mysql.com/).
MySQL 5.0 01/11/2008 MYSQLMANAGER(8)