Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am trying to design a simple high availability system for our production system at the mysql level. From what I have read so far, the best solution is to set up master-master replication. Unfortunately, we cannot use virtual IP, so scripts like MMM cannot be used as is I believe.

A suggested solution is to connect to mysql through e.g. haproxy, which will "guarantee" that only one master will be written to at a time. I could not find much information for this configuration - what are the pros/cons compared to the usual virtual ip-based configuration ?

share|improve this question
    
Just wondering why you can't use a Virtual IP? Because HAProxy will basically replace your Virtual IP (except as a "real" IP of course), but the end effect is the same. –  Mark Henderson Mar 28 '11 at 4:20
    
We use xen for virtualization, and I have been told that it was not possible to use virtual IP on domU. I cannot change the virtualization infrastructure at my disposition, for better or worse –  David Cournapeau Mar 28 '11 at 5:01
    
that sounds quite odd. I've got access to (a very small) Xen cluster and I'm fairly sure they use Virtual IPs for DRDB, but I'm not a Xen expert so I better say anything concrete. –  Mark Henderson Mar 28 '11 at 5:08
    
If you could give me some more information on your configuration, I would be very glad to use it. I would rather use virtual IP if possible given that most information I have found so far use this solution. Of course, pointing me to some documentation on how to set this up would work as well (I did not find much information when looking for "heartbeat xen" or "virtual up xen") –  David Cournapeau Mar 28 '11 at 5:37
    
the virtual IP is assigned to the network adapter that's been assigned to the Xen guest. It's just an extra IP address sitting on the adapter. –  Mark Henderson Mar 28 '11 at 7:39

1 Answer 1

If you are using a Load Balancer such as Zeus, you could create a DBVIP in the Load Balancer and assign it to the IPs of the MySQL Masters. Then, assign the scheduling the DBVIP to round robin weighted round robin or other methods the load balancers has defined.

Since we are talking about MySQL, please make sure you truly use the multimaster frame of mind.

For starters, make sure you use

/etc/my.cnf for Master1

server_id=1
auto_increment_increment=10
auto_increment_offset=1

/etc/my.cnf for Master2

server_id=2
auto_increment_increment=10
auto_increment_offset=2

Then, make absolutely sure when you query for records in either master, that you do not use the auto_increment value to query the data. Why?

From the given config above, Master1 will have tables that have 1 as the last digit for auto_increment values. Master2 will have tables that have 2 as the last digit for auto_increment values.

If your SELECT clause has WHERE id = 11, that SELECT can only retrieve from Master1.

You will have to establish ground rules as follows:

GROUND RULE #1
All tables with auto_increment values as the PRIMARY KEY will have an additional UNIQUE KEY to retrieve needed data. That UNIQUE key must be identical on both Master1 and Master2.

GROUND RULE #2
All tables with auto_increment values as the PRIMARY KEY that cannot have an additional UNIQUE KEY to retrieve needed data must have the application form the SELECT statement to set the WHERE clause to negotiate the value chosen as the PRIMARY KEY to be able to retrieve the needed row (Example: if the server_id on the server that you inserted the data was 2, then you must take the ID in the code, subtract MOD(ID,10), and add the auto_increment_offset. Then you can call the SELECT).

If neither of these rules can be followed, you must switch from multimaster to another replication topology.

share|improve this answer
    
thanks for the comment, but as I mentionned, we only use master<->master topology for high availability, so a load balancer is not exactly appropriate. As for auto increment, we do have them, but they are only used "implicitely" as the relation between tables, e.g. "SELECT ... FROM tbl1 WHERE cust_id = (SELECT id FROM tbl2 WHERE name = 'some name')", so the auto-incremented values can be different between masters. –  David Cournapeau May 6 '11 at 2:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.