I need to set up multiple MySQL instances on the same host for one of our customers. As far as I see I could go with:
mysqld_multi
- MySQL Sandbox
This should be a long term solution for an enterprise class production environment.
We need to migrate an already existing environment which currently uses the RedHat Cluster with active/passive nodes to a new VM without the clustering software (VMware HA solutions will be used, it will be different, I know).
There are 3 separate MySQL instance/cluster resources. I don't know the original motivation for this separation, but I suppose that the customer wants to be able to stop/start (patch, upgrade …) the different resources autonomously.
Any comments and suggestions – pros, cons and real world experience are very welcome!
Best Answer
To answer your immediate question on how to set it up, I and Randy Melder addressed this question May 31, 2011.
DTest and Laurynas Biveinis answered this more recently (Sep 20, 2011).
Some swear by its usage these days.
Years ago, mysql used to provide a script called
safe_mysqld
which made it simple to have multiple instances of mysql. All you did was create a my.cnf for the what instance on whatever port you wanted. Then called:MySQL stopped distributing safe_mysqld in favor of mysqld_multi.
However, I have learned how to use mysqld_safe. Believe it or not, because of understanding mysqld_safe, I actually wrote my own mysql multi-instance engine back in Feb 2011. It is in production use right now with many of my employer's clients. Here is how I did it:
First, make this service engine called /etc/init.d/mysqlservice
Next, create mysql instance file for a specfic port.
For example, here is the service mysql3307
Make sure that the mysql instance file has the port number defined in PORT_NUMBER between 3307 and 3399.
As mysql instance file calls mysqlservice, please note that mysqlservice will check for the config file for the specfic port.
In the case of mysql3307, mysqlservice will look for /etc/my3307.cnf.
Make sure you have the following explicitly defined in /etc/my3307.cnf
The datadir and socket can be at places of your choosing.
To create mysql3308 and other services, following the same paradigm but use the exactly port number defined throughout.
Give it a Try !!!