Trying to run multiple Mysql servers on a single machine

kashani

I'm building a nice little database backup machine for a number of my database server. Since Mysql can only have one master I need to run three or four separate Mysql servers, not threads or processes, but actual separate servers. Each Mysql server on the backup machine will have a different master so I can replicate each production database to a single machine.

Ideally the would each run on a different port 3306, 3307, etc and have data in different dirs like /var/lib/mysql, /var/lib/mysql2, etc.

Unfortunately it appears that the ebuild hardcodes a number of things into the binary or I haven't found the right config setting to get two instances running at the same time. I'd prefer to handling everything via separate configs and not have to recompile mysql a number of times. Something like this:

cp -a /var/lib/mysql/ /var/lib/mysql2/
cp -a /var/log/mysql/ /var/log/mysql2
cp -a /var/run/mysqld/ /var/run/mysqld2/
cp -a /etc/mysql/ /etc/mysql2/

and then editting config files as needed. Has anyone done something like this? Or am I going to have to mess with the ebuild and install in /usr/local/mysql1, /usr/local/mysql2, and so on?

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.

hds

kashani · Posted: Fri Oct 29, 2004 2:26 pm Post subject:

It's actually not that hard to do from the ebuild, but you have to be careful. Assuming I'm stuck doing it that way, I imagine this might work.

1. uninstall mysql
2. modify the ebuild to use ../mysql5/, run the emerge
3. modify the ebuild to use ../mysql4/, run the emerge
...
6. run sync again, emerge mysql

This worked for qmail when I needed multiple instances, though mysql src isn't near the mess qmail is with the 10 odd patches I would have manually had to apply. Hmmm, well I guess I know how I'm burning the next few hours.

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.

EnricoHorn · Posted: Fri Oct 29, 2004 7:26 pm Post subject:

Did you look at mysqld_multi? Did not try it, but seems a possible solution.

hds · Posted: Sun Oct 31, 2004 9:51 pm Post subject:

kashani · Posted: Mon Nov 01, 2004 4:21 pm Post subject:

You're assuming I'd touch Mysql again on this box. If I were to get everything to work at any point, I'm slapping a package.mask in and never touching Mysql again. Since it's internal only and just needs to run Mysql reasonably well, doing an emerge -u world is just asking for trouble.

mysqld_multi looks exactly like what I want, but I can't figure out how to make it work. All the config examples are the same and using them doesn't appear to actually do anything on my system. Oddly I can't find anyone who actually seems to be using it.

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.