1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

MySQL HA Deployment Architecture

Discussion in 'MySQL Forums' started by prashant, Sep 28, 2012.

  1. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi,

    I am given a task to identify best Fault Tolerant HA topology with 2 MySQL Servers.

    Little background:-

    1. Our application at present runs over Oracle Database. We are moving to MySQL for Lower cost Solution for Emerging Telecom Markets
    2. Performance is not too much of a concern.
    3. Dataset and user base is low.
    4. It is just that solution should be robust when it comes to Fault tolerance and HA.

    I have researched for a few days and have following topologies in mind:-


    1. MySQL Multi-Master replication with Heartbeat and Pacemaker (No DRBD): For such a topology we would have one Floating IP Plumbed on any one node.

    In case of a node/service/network/disk failure this floating IP will shift to the other active node (and send and alarm mail) and process request with minimal service downtime.

    Advantages:-

    a) Slave Promotion not required.
    b) Single IP HA connection
    c) Other master can still be used for DB backups etc.

    Disadvantages:-

    a) Read-only queries cannot be offloaded to the other master.
    b) High speed Network connectivity required for heartbeat
    c) Support fee (LINBIT for Heartbeat and Pacemaker).
    d) We need some sort of Replication monitoring scripts which will raise alarm in case replication is not working or in case both the nodes are out of sync.


    2. MySQL Multi-master replication with ReplicationDriver using J Connector (No Heartbeat/Pacemaker): Our application will always make DB connections via JDBC drivers.

    There is no floating IP in this case. We will be using connector replication driver to manage our connections. In this case we will tell replication driver to use one master as master and the other as a slave (so that we do not run into Update Conflicts).

    Advantages:-

    a) Slave Promotion not required.
    b) Other master can be used for DB backups and read only queries etc.
    c) No LINBIT support fee
    d) No network interconnect required for heartbeat
    e) less complex to implement

    Disadvantages:-

    a) No Single IP - DB access code should be intelligent so as to write only to one master at a time.
    b) We need some sort of Replication monitoring scripts which will raise alarm in case replication is not working or in case both the nodes are out of sync.

    Similarly we have following configurations


    3. MySQL with DRBD, heartbeat and pacemaker (No Replication): In this case MySQL will be running on one node at a time and DRBD will keep the two nodes in Sync.

    Advantages:-

    a) Synchronous – MySQL replication can at most be semi-synchronous
    b) No replication monitoring scripts required.

    Disadvantages:-

    a) LINBIT Support Fee
    b) One node is always down – no offloading possible.
    c) More Failover time as MySQL is required to be brought up.
    d) High Complexity.


    I also considered 2 more topologies – Same as 1 & 2 but instead of Multi-Master it will be master-slave. This would have an overhead of Slave Promotion and Master Conversion to slave.

    Please advice which is the best HA topology to have. Also I have no idea about stability of each of these topologies as my knowledge is primarily theoretical.

    Thank you.

    Best Regards,
    Prashant