Top Nav

MySQL Multi Master HA Cluster

The goal of this article is to setup an HA Linux cluster on RackSpace Cloud Servers for MySQL with multi master replication and HAProxy for load balancing.

Begin by creating the HA Linux client as described here:

HA Linux Cluster On RackSpace Cloud Servers

Once this is complete then return to these instructions.

1. Begin by logging into each of the servers and creating a replication slave user with the following SQL commands:

Where [PASSWORD] is a strong random string. Use the same password on each of the servers.

2. Stop mysqld on both servers:

3. On each server, edit the /etc/my.cnf file. Look through the my.cnf file for an existing section of replication options. If none is found then just add to the end of the file.

a. Find the “server-id” line and make sure that each server has a unique server-id. It does not matter what the id number is … it just has to be different on each server.

b. Set replicate-same-server-id to 0. This tells each server to ignore replication logs that refer to it’s on id number.

c. Set auto-increment-increment to 2. If you have more then two servers in your cluster then set auto-increment-increment to the number of servers. If you think that you might increase the number of servers in the near future then set auto-increment-increment to the likely maximum number of servers. This parameter controls the span between auto-increment values. So for example a setting of 2 causes a sequence like 2,4,6,6, etc. A value of 5 would cause a sequence of 5,10,15,20, etc.

d. Set auto-increment-offset to 1 on the first server and 2 on the second server. auto-increment-offset works in conjunction with auto-increment-increment to control the generation of auto-increment values. Each server needs a different auto-increment-offset to avoid conflicts. Assuming two servers, with auto-increment-increment set to 2 and auto-increment-offset set to 1 and 2 on the first and second server respectively will result in the following sequences:

e. Now on each server, add master-host, master-user, master-password, and master-connect-retry settings. Remember that the master-host should be set to the other server. I like to use hostnames as long as they’ve been defined in /etc/hosts so that there is no dependency on external name-resolution.

Here’s the first server:

And here’s the second server:

f. Uncomment or add a log-bin line:

g. Setup expire_logs_days and max_binlog_size so that the binary logs don’t grow in an uncontrolled fashion.

h. Uncomment or add relay-log and relay-log-index lines:

4. Next start the mysqld service on both servers:

5. Now login to the mysql command line on each server and run “SHOW SLAVE STATUS”. Examine the output and verify that “Slave_IO_Running” and “Slave_SQL_Running” are both “YES”.

Congratulations, you now have a high-availability MySQL cluster!

Special thanks to Richard Benson at Dixcart whose similar articles for Debian inspired this effort.

  • Pingback: MySQL Load Balancing With HAProxy - Reliable Penguin Blog()

  • Andrew Hallman

    What happens if data gets currupted on one machine? Won’t that just replicate to the other machine?

    • Generally yes … This config is intended for high availability and load. You still need data protection with backups.

      Sent from my iPad

      • Ali Jawad

        not sure how you would distribute load, IMHO writing to both masters at the same time is dangerous, at best you can read from the second master. Apart from that if you setup HAproxy you will be only using one of both masters anyway.

  • Charles

    I’m assuming the auto increment of 2 would only apply if client hosts are able to hit both mysql servers at the same time as opposed to only one being available and then failing over if needed?

    • If you just want failover then do master-slave. The downside is it’s difficult to resync to the master after a failover. If you want failover with easy restore then do the multi-master as described. I would still do the 2 on auto-increment to make sure there was no chance of collision between the auto increment columns.

    • If you just want failover then do master-slave. The downside is it’s difficult to resync to the master after a failover. If you want failover with easy restore then do the multi-master as described. I would still do the 2 on auto-increment to make sure there was no chance of collision between the auto increment columns.

  • Clement

    Are you serious ? Only unidirectionnal replication is supported. And there is some reasons ! It’s very difficult to handle concurrent transactions on same objects on both node. Never do that unless you can handle corrupted data. Use mysql HA instead.

Email
Print