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 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.
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.
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
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.
port option sets this MySQL instance on a different port than the default 3306. The
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
Step 8. Connect to MySQL instances.
Now, to connect to each MySQL instance, you’ll need to specify the port and/or socket file.