Aug 11

Links for 2009-08-11

Linux No Comments »

written by phi.mic \\ tags: , ,

Nov 14

Manage multiple MySQL instances on the same machine

MySQL No Comments »

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

 

written by phi.mic \\ tags: