Home / server / Questions / 252531
In Process
David Cournapeau
Asked: 2011-03-28 19:00:19 +0800 CST

High Availability Mysql without virtual IP

  • 772

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 ?

mysql high-availability haproxy

1 Answers

  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.

    • 0