Enabling and using the MySQL Instance Manager (IM)

Currently, the IM is not enabled by default, the mysql.server startup script still defaults to using mysqld_safe:

$ pstree | grep mysql
     |-mysqld_safe---mysqld

However, switching to the Instance Manager is quite easy for a default installation (e.g. from the binary tarballs or using the RPMs) - you just have to stop the server, edit /etc/init.d/mysql and change the value for use_mysqld_safe from 1 to 0. Then restart the server again, it should now be started through the instance manager:

$ pstree | grep mysql
     |-mysqlmanager---mysqld

(I've filed BUG#17440 as a request to change the IM being enabled by default for MySQL 5.1 and up.)

In the default configuration, the IM will not behave much differently from the mysqld_safe script: it starts a single mysqld instance and uses the default startup options for it. It will then monitor this instance and will restart it, if it happens to crash. If the IM is being shut down, it will also signal the mysqld instance to perform a clean shutdown. However, there is much more the IM can do for you! It allows you to:

• Monitor multiple MySQL instances from a remote location
• Start and stop individual instances
• Show the status of all or individual instances
• Look at the instances' log files
• Use different mysqld binaries for each instance
• Modify individual instance configuration options

The cool part about the IM is that you can use any MySQL client application (like the mysql command line program or the MySQL Query Browser) to perform these tasks, as it uses the MySQL client/server protocol and can communicate with these clients via a local socket file or a TCP network connection.

In order to be able to make use of these additional features, you need to configure the IM accordingly. This is done in a similar fashion to how you configure the MySQL server and client - you create a /etc/my.cnf configuration file, which contains a [manager] section that configures some general IM options.

To manage the single default instance as it was installed from the RPMs, I created the following minimalistic my.cnf file:

[manager]
socket=/var/lib/mysql/manager.sock

This will make sure the IM creates a local socket file that I can connect to using the mysql commandline client. If I wanted the IM to listen on a network socket fore remote connections as well, all I would have to do is add port=2273 to the [manager] section (2273 is the default TCP port assigned by the IANA for remote MySQL management calls, while 3306 is the default TCP port to connect to the actual MySQL server. Of course, you are free to use any other TCP port). By not defining any instance, the IM takes the default values defined at build time and will use mysqld as the instance name. See the chapter "MySQL Instance Manager Configuration Files" for more options that you can supply to change the IM's default behaviour.

In addition to enabling the Instance Manager in my.cnf and the init script, we also have to create a user account that will be used to connect to the IM. By default, the IM looks for username/password pairs in the file /etc/mysqlmanager.passwd. You can create such a user by using the --passwd option. It will ask you for the user name and password you want to use and will print out the username and encrypted password. You should redirect this output into a file. Here's an example on how to create an admin user account:

# mysqlmanager --passwd > /etc/mysqlmanager.passwd
Creating record for new user.
Enter user name: admin
Enter password: <password>
Re-type password: <password>
# chmod u+rw,g-rwx,o-rwx /etc/mysqlmanager.passwd

Note that the user/password pairs are only used for connecting to the IM, they have no other function or relation to the user accounts used by the individual MySQL instances or the operating system. The user name admin now can be used to connect to the instance manager, once it is started:

# /etc/init.d/mysql start
Starting MySQL...                                                     done
# mysql --socket=/var/lib/mysql/manager.sock -u admin -p
Enter password: <password>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 0.2-alpha

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW INSTANCES;
+---------------+--------+
| instance_name | status |
+---------------+--------+
| mysqld        | online |
+---------------+--------+
1 row in set (0.00 sec)

mysql> SHOW INSTANCE STATUS mysqld;
+---------------+--------+----------------+-------------------------------------------------------------------------+
| instance_name | status | version_number | version                                                                 |
+---------------+--------+----------------+-------------------------------------------------------------------------+
| mysqld        | online | 5.1.7          | Ver 5.1.7-beta for pc-linux-gnu on i686 (MySQL Community Edition (GPL)) |
+---------------+--------+----------------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> STOP INSTANCE mysqld;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW INSTANCE STATUS mysqld;
+---------------+---------+----------------+-------------------------------------------------------------------------+
| instance_name | status  | version_number | version                                                                 |
+---------------+---------+----------------+-------------------------------------------------------------------------+
| mysqld        | offline | 5.1.7          | Ver 5.1.7-beta for pc-linux-gnu on i686 (MySQL Community Edition (GPL)) |
+---------------+---------+----------------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> START INSTANCE mysqld;
Query OK, 0 rows affected (0.00 sec)
Instance started

mysql> SHOW INSTANCE STATUS mysqld;
+---------------+--------+----------------+-------------------------------------------------------------------------+
| instance_name | status | version_number | version                                                                 |
+---------------+--------+----------------+-------------------------------------------------------------------------+
| mysqld        | online | 5.1.7          | Ver 5.1.7-beta for pc-linux-gnu on i686 (MySQL Community Edition (GPL)) |
+---------------+--------+----------------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

That's all that is required for a minimal setup using the Instance manager. In addition to starting and stopping instances, there are commands that allow you to add or change instance configuration options that can not be reconfigured at runtime (e.g. the TCP port or the datadir). You can also use the IM to look at the instances' log files. For more details on how to configure this, please refer to the chapter "The MySQL Instance Manager" in the manual. Have fun!