Conrad_von_Soest_Brillenapostel-seeing-double

Making Multiple MySQL Instances on One Server


I’m trying this new idea for backing up our production MySQL servers. I have a backup server that basically runs rdiff-backup in the morning across several servers, but then does nothing for the rest of the day. It’s a pretty decent machine, so I’d like to utilize some resources. Replicating a MySQL server is a good way to ensure High Availability in case of a failure. The backup server acts as a slave to the master (production) server. Basically, the slave is an exact copy of the master. They are two separate instances of MySQL server running on two physical servers. Whatever queries run on the master are sent to the slave so it can do the same. This way they are kept completely in sync. You could also have the slave take over for the master, should the master server happen to fail.

The slave is an ever updating duplicate of the master.

The only problem I face with this set up, though, is that I have multiple production servers out there. So this only works if this backup server could be a slave for multiple machines.

No slave can serve two masters.

This is not possible, though, because, of course, no slave can serve two masters. Fortunately, a server can have multiple instances of MySQL running on it! So, in a sense, we have a server with multiple MySQL instances, to which a master can replicate. More about that set up in an upcoming post.

The slave has multiple instances of MySQL running.

A how to on this blog, shows how this can be done. I’ll replicate the process below.

STEPS TO MULTIPLE MYSQL MADNESS

On the slave server

Step 1. Install MySQL

We’ll be working with CentOS 5.8, but this could really apply for any OS. First we’ll need to install MySQL like normal.

There are plenty of good tutorials out there on how to install the specific version of MySQL you want on the specific OS you’re running.

Step 2. Set up the data area.

You’ll need to have a different folder for each of the MySQL instances, say /dbases/master-a/, /dbases/master-b/, and /dbases/master-c/.

Step 3. Copy the default my.cnf file

This is the default MySQL config file, it may be named differently on other OSes.

Step 4. Edit the new MySQL config files.

For each new config file, you’ll need to specify some unique variables.

The port option sets this MySQL instance on a different port than the default 3306. The datadir, socket, log-bin, log-error, and pid-file options make sure the necessary files are not using the default files.

Step 5. Create new init scripts.

The init script allows the server to start and stop the service at boot time, and allows for easy start up and shutdown (on CentOS/RedHat, at least – with an easy service mysqld start).

Just do one for now. We’ll copy the new one to create the others, then just do a quick search and replace in those files to change the master-a to master-b and master-c.

Step 6. Edit the init script

Step 7. Start each MySQL instance.

Now you can start each instance with the handy service command.

Step 8. Connect to MySQL instances.

Now, to connect to each MySQL instance, you’ll need to specify the port and/or socket file.

Share and Enjoy:
  • Print
  • PDF
  • RSS

Related Posts:


3 thoughts on “Making Multiple MySQL Instances on One Server

  1. Ameya

    Hi ammon,

    that was quite descriptive. I was just wondering, if you have tried out mysqld_multi instead of this approach. Any thoughts?

    Reply
  2. Nazar Revutsky

    action $”Initializing MySQL database: ” /usr/bin/mysql_install_db –datadir=”$datadir” –user=mysql
    should be changed, –defaults-file=”$defaultfile” is required to create proper new mysql instance:
    action $”Initializing MySQL database: ” /usr/bin/mysql_install_db –defaults-file=”$defaultfile” –datadir=”$datadir” –user=mysql

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">