mysqlmanager is the MySQL Instance Manager (IM). This program is a daemon running on a TCP/IP port that serves to monitor and manage MySQL Database Server instances. MySQL Instance Manager is available for Unix-like operating systems, and also on Windows as of MySQL 5.0.13.
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 the
MySQL Server, even from a remote host.
MySQL Instance Manager also implements the functionality (and
most of the syntax) of the mysqld_multi
script. A more detailed description of MySQL Instance Manager
follows.
Normally, the mysqld MySQL Database Server
is started with the mysql.server script,
which usually resides in the /etc/init.d/
folder. 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 instead modify the my.cnf
file by adding use-manager
to the
[mysql.server]
section:
[mysql.server] use-manager
The Instance Manager's behavior in this case depends on the
options given in the MySQL configuration file. If there is no
configuration file, the MySQL Instance Manager creates a
server instance named mysqld
and attempts
to start it with default (compiled-in) configuration values.
This means that the IM cannot guess the placement of
mysqld if it is not installed in the
default location. If you have installed the MySQL server in a
non-standard location, you should use a configuration file.
See Section 2.1.5, “Installation Layouts”.
If there is a configuration file, the IM reads it to find
[mysqldN]
sections (for example,
[mysqld1]
, [mysqld2]
,
and so forth). Each such section specifies an instance. When
it starts, the Instance Manager attempts to start all server
instances that it finds. By default, the Instance Manager
stops all server instances when it shuts down.
Warning
The [mysqld]
section name causes
unpredictable results when used in conjunction with the
Instance Manager. When using the Instance Manager, check
that no section is named [mysqld]
.
Note that there is a special
--mysqld-path=
path-to-mysqld-binary
option that is recognized only by the IM. Use this variable to
let the IM know where the mysqld binary
resides. You should also set basedir
and
datadir
options for the server.
The typical startup/shutdown cycle for a MySQL server with the MySQL Instance Manager enabled is as follows:
-
The MySQL Instance Manager is started with /etc/init.d/mysql script.
-
The MySQL Instance Manager starts all instances and monitors them.
-
If a server instance fails the MySQL Instance Manager restarts it.
-
If the MySQL Instance Manager is shut down (for instance with the /etc/init.d/mysql stop command), all instances are shut down by the MySQL Instance Manager.
Communication with the MySQL Instance Manager is handled using the MySQL client-server protocol. As such, you can connect to the IM using the standard mysql client program, as well as the MySQL C API. The IM supports the version of the MySQL client-server protocol used by the client tools and libraries distributed along with MySQL 4.1 or later.
The Instance Manager stores its user information in a
password file. The default name of the password file is
/etc/mysqlmanager.passwd
.
Password entries have the following format:
petr:*35110DC9B4D8140F5DE667E28C72DD2597B5C848
If there are no entries in the
/etc/mysqlmanager.passwd
file, you
cannot connect to the Instance Manager.
To generate a new entry, invoke Instance Manager with the
--passwd option. Then the output can be
appended to the
/etc/mysqlmanager.passwd
file to add a
new user. Here is an example:
shell>mysqlmanager --passwd >> /etc/mysqlmanager.passwd
Creating record for new user. Enter user name:mike
Enter password:password
Re-type password:password
The preceding command causes the following line to be added
to /etc/mysqlmanager.passwd
:
mike:*00A51F3F48415C7D4E8908980D443C29C69B60C9
Note
The Instance Manager must be restarted after adding/changing passwords.
To monitor server status, the MySQL Instance Manager will
attempt to connect to the MySQL server instance at regular
intervals using the
MySQL_Instance_Manager@localhost
user
account with a password of
check_connection
.
You are not required to create a
MySQL_Instance_M@localhost
user account
in order for the MySQL Instance Manager to monitor server
status, as a login failure is sufficient to identify that
the server is operational. However, if the account does not
exist, failed connection attempts are logged by the server
to its general query log (see Section 5.12.2, “The General Query Log”).
The MySQL Instance Manager supports a number of command line
options. For a brief listing, invoke
mysqlmanager with the
--help
option.
mysqlmanager supports the following options:
-
Display a help message and exit.
-
The file in which the angel process records its process ID when mysqlmanager runs in daemon mode. By default, this file is named
mysqlmanager.angel.pid
. If the--pid-file
option is given, the default angel PID file becomes the same except that any extension is replaced with an extension of.angel.pid
. This option was added in MySQL 5.0.23. -
The IP address to bind to.
-
On Unix, the pathname of the MySQL Server binary, if no path was provided in the instance section. Example:
--default-mysqld-path=/usr/sbin/mysqld
-
Read Instance Manager and MySQL Server settings from the given file. All configuration changes by the Instance Manager will be made to this file. This must be the first option on the command line if it is used.
-
On Windows, install Instance Manager as a Windows service. This option was added in MySQL 5.0.11.
-
The path to the IM log file. This is used with the --run-as-service option.
-
The interval in seconds for monitoring instances. The default value is 20 seconds. Instance Manager tries to connect to each monitored instance using the non-existing
MySQL_Instance_Manager
user account to check whether it is alive/not hanging. In the case of a failure to connect, IM performs several attempts to restart the instance. Thenonguarded
option in the appropriate instance section disables this behavior for a particular instance. The monitoring process will produce messages in the general query log similar to the following:Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES)
-
Prepare an entry for the password file and exit.
-
Look for the Instance Manager users and passwords in this file. The default file is
/etc/mysqlmanager.passwd
. -
The process ID file to use. By default, this file is named
mysqlmanager.pid
. -
The TCP/IP port number to use for incoming connections. (The default port number assigned by IANA is 2273).
-
Print the current defaults and exit. This must be the first option on the command line if it is used.
-
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. -
On Unix, daemonize and start the angel process. The angel process is simple and unlikely to crash. It will restart the Instance Manager itself in case of a failure.
-
On Unix, the socket file to use for incoming connections. By default, the file is named
/tmp/mysqlmanager.sock
. -
On Windows, run Instance Manager in standalone mode. This option was added in MySQL 5.0.13.
-
On Unix, the username to start and run the mysqlmanager under. It is recommended to run mysqlmanager under the same user 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.)
-
Output version information and exit.
-
The number of seconds to wait for activity on a connection befoe 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.
Instance Manager uses the standard my.cnf
file. It uses the [manager]
section to read
options for itself and the [mysqldN]
sections to create instances. The [manager]
section contains any of the options listed in
Section 5.5.3, “MySQL Instance Manager Command Options”. Here is an
example [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
Warning
The [mysqld]
section name is deprecated
and should not be used in a configuration file, instead
[mysqldN] sections such as [mysqld1] should be used for
specific instances.
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
, etc. As of MySQL 5.0.10, the
MySQL Instance Manager reads and manages the
/etc/my.cnf
file only on Unix. On
Windows, MySQL Instance Manager reads the
my.ini
file in the directory where
Instance Manager is installed. The default option file
location can be changed with the
--defaults-file=
file_name
option.
Instance sections specify options given to each instance at startup. These are mainly common MySQL server options, but there are some IM-specific options:
-
mysqld-path =
path
The pathname to the mysqld server binary.
-
shutdown-delay =
seconds
The number of seconds IM should wait for the instance to shut down. The default value is 35 seconds. After the delay expires, the IM assumes that the instance is hanging and attempts to terminate it. If you use
InnoDB
with large tables, you should increase this value. -
nonguarded
This option should be specified if you want to disable IM monitoring functionality for a certain instance.
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
Once you've set up a password file for the MySQL Instance Manager and the IM is running, you can connect to it. You can use the mysql client tool connect through a standard MySQL API:
mysql --port=2273 --host=mydomain.org --user=mysql -p
The following list of commands shows the MySQL Instance Manager currently accepts, with samples.
-
START INSTANCE
instance_name
This command attempts to start an instance.
mysql>
START INSTANCE mysqld4;
Query OK, 0 rows affected (0,00 sec) -
STOP INSTANCE
instance_name
This command attempts to stop an instance.
mysql>
STOP INSTANCE mysqld4;
Query OK, 0 rows affected (0,00 sec) -
SHOW INSTANCES
Shows the names of all loaded instances.
mysql>
SHOW INSTANCES;
+---------------+---------+ | instance_name | status | +---------------+---------+ | mysqld3 | offline | | mysqld4 | online | | mysqld2 | offline | +---------------+---------+ 3 rows in set (0,04 sec) -
SHOW INSTANCE STATUS
instance_name
Shows the status and the version information for an instance.
mysql>
SHOW INSTANCE STATUS mysqld3;
+---------------+--------+---------+ | instance_name | status | version | +---------------+--------+---------+ | mysqld3 | online | unknown | +---------------+--------+---------+ 1 row in set (0.00 sec) -
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 | +---------------+---------------------------------------------------+ 7 rows in set (0.01 sec) -
SHOW
instance_name
LOG FILESThe 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 configuration file (for example,
log=/var/mysql.log
), the Instance Manager tries to guess its placement. If the IM is unable to guess the logfile placement you should specify the log file location explicitly by using the appropriate log option in the 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 | +-------------+------------------------------------+----------+ 3 rows in set (0.01 sec) -
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 you would like to retrieve starting from the log end. You can retrieve data from the middle of the log file by specifying the optionaloffset_from_end
parameter. The following example retrieves 21 bytes of data, starting 23 bytes from the end of the log file and ending 2 bytes from the end of the log file:mysql>
SHOW mysqld LOG GENERAL 21, 2;
+---------------------+ | Log | +---------------------+ | using password: YES | +---------------------+ 1 row in set (0.00 sec) -
SET
instance_name
.option_name
=option_value
This command edits the specified instance's configuration file to change or add instance options. The IM assumes that the configuration file is located at
/etc/my.cnf
. You should check that the file exists and has appropriate permissions.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. -
UNSET
instance_name
.option_name
This command removes an option from an instance's configuration file.
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. -
FLUSH INSTANCES
This command forces IM to 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)