The MySQL 5 release comes with the MySQL Instance Manager (IM) which allows to manage multiple MySQL database instances on the same host on different ports. IM is a replacement for the mysqld_safe wrapper and the mysqld_multi script. The IM starts for each instance a mysqld kind process with the parameters defined in the configuration. In addition it allows you to:
- monitor multiple mysql instances
- automatically restart mysql instances if they crash
- show status of instances (eg. offline vs. online)
- modify configuration options of specific instances
- watch the log files
In the default configuration the IM is disabled. To enable it you have put a new section in the my.cnf like this:
[manager] default-mysqld-path = /usr/sbin/mysqld socket = /var/lib/mysql/manager.sock pid-file = /var/run/mysql/manager.pid password-file = /etc/mysqlmanager.passwd monitoring-interval = 3600 user = mysql log = /var/log/mysql/mysql-man.log run-as-service [mysql.server] use-manager
Now we copy the existing mysql instance to create a new one. I recommend to do this after a fresh mysql installation.
sudo cp -a /var/lib/mysql /var/lib/mysql1 sudo chown -R mysql:mysql /var/lib/mysql1
Ok lets configure this two mysql instances in /etc/my.cnf. Each instance has a own section and a seperate port e.g.
[mysqld] log-bin = mysql-bin log-error = /var/log/mysql/mysqld-err.log log-slow-queries = /var/log/mysql/mysqld-slow.log long-query-time = 3 expire_logs_days = 7 pid-file = /var/lib/mysql/mysqld.pid socket = /var/lib/mysql/mysqld.sock port = 3306 [mysqld1] log-bin = mysql-bin log-error = /var/log/mysql1/mysqld-err.log log-slow-queries = /var/log/mysql1/mysqld-slow.log long-query-time = 3 expire_logs_days = 7 pid-file = /var/lib/mysql1/mysqld.pid socket = /var/lib/mysql1/mysqld.sock port = 3307
This is a very simple configuration with the two instances mysqld and mysqld1. It is very importaint that you not use safe-updates, otherwise you will get the following error message when you try to connect to the IM:
ERROR 1149 (42000): You have an error in your command syntax. Check the manual that corresponds to your MySQL Instance Manager version for the right syntax to use
Finally we have to create a special user to connect to the instance manager with the command:
sudo mysqlmanager --passwd >>/etc/mysqlmanager.passwd Creating record for new user. Enter user name: admin Enter password: ******** Re-type password: ******** sudo chown mysql:mysql /etc/mysqlmanager.passwd sudo chmod 600 /etc/mysqlmanager.passwd
Congratulation you have setup MySQL Instance Manager! Now you can connect to the MySQL Instance Manager console with the following command
mysql --socket=/var/lib/mysql/manager.sock -u admin -p
