Mysql – Is Circular replication recommended among 4 MySQL servers

MySQL

We have 4 MySQL servers and our main motto is that data should replicate across all the 4 servers if one is getting updated and the same changes has to go to all other
3 servers.

And below are the few other requirements

1) Changes need to be on all 4 servers.

2) one should be active at anytime which should be on Read/Write and rest of 3 to be on read-only mode.

3) If switch/Failover happens the new master should only be read/write and other servers has to be on read only

We want to achieve the above explained funtionality through the Circular replication mechanisum? Is it possible?

If not possible then what would be the recommended HA setup solution to meet above said requirement.

Appriciate any help or suggessions on this.

Best Answer

I do not know why trying to fit circular replication in such a setup- a circular replication has multiple single point of failures (one per server, as if any server fails, the "circle" is broken) plus with non-gtid replication solving the failover is not easy at all (what is the binary coordinates equivalent to the binary coordinates of the next server?). While there are some very specific and contained cases where it could make sense, I would say yours is not one of them.

What you described, 1 read/write server and 3 read-only ones is the paradigmatic usage of traditional replication where the master is r/w and it has 3 slaves depending on it. So, a tree-like topology, not a circle.

Of course, replication, up to the most recent versions has its issues (traditionally being asynchronous, no possibility of checking for drift, no paralelization, no automatic resolution of conflicts), so in some cases you may want to check alternative options, like Galera or Tungsten replication. However, many of the shortcomings have been solved in the most recent versions with semisync replication, GTID, parallel apply of logs, and in the upcoming 5.7, multi-master replication.

My recommendation would be to stick with standard replication with one master and 3 slaves, if possible using 5.6 GTID, as it makes the failover easier.

However, replication does not provide HA by itself. There are different approaches, depending on the technology used, here you have some suggestions:

MySQL failover is a small first party utility that can monitor replication and help you change the topology of your setup. If you want a more all-in-one solution, check MySQL Fabric
MHA allows you to check and change the topology of your setup, maybe combined with virtual IPs to guarantee the service
HAProxy allows you to setup a couple of virtual read/write and read-only services that are backed by several MySQL servers, so it can transparently check for failure and switch the active server, or load balance between several servers

Here you have a presentation with some thoughts about different methods of doing HA with MySQL.

Related Solutions

MySQL Replication – Setting Up Circular Replication in Existing Topology

STEP01) Add this to /etc/my.cnf on ServerB

[mysqld]
log-bin=mysql-bin

STEP02) Restart mysql on ServerB without replication running

service mysql restart --skip-slave-start

STEP03) On ServerB, run this in mysql client

SHOW MASTER STATUS;

You should see something like this:

+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 590591464 |              |                  |
+------------------+-----------+--------------+------------------+

STEP04) Record the Log File and Position from STEP03

STEP05) Restart mysql on ServerB as normal

service mysql restart

STEP06) Setup Replication User on ServerB

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ...

STEP07) On ServerA, run the following

CHANGE MASTER TO
MASTER_HOST='IP Address of ServerB',
MASTER_PORT=3306,
MASTER_USER='replication username',
MASTER_PASSWORD='replication password',
MASTER_LOG_FILE='File From STEP04',
MASTER_LOG_POS=Position From STEP04;

Suppose ServerB's IP address is 10.1.2.30, replication user/pass is 'repluser'/'replpass'

CHANGE MASTER TO
MASTER_HOST='10.1.2.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=590591464;

STEP08) Start Replication on ServerA by running

START SLAVE;

STEP09) Run SHOW SLAVE STATUS\G on ServerA

It should look something like this:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.89.147
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000109
          Read_Master_Log_Pos: 636515875
               Relay_Log_File: relay-bin.000038
                Relay_Log_Pos: 636509693
        Relay_Master_Log_File: mysql-bin.000109
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 636515875
              Relay_Log_Space: 636509885
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.01 sec)

If Slave_IO_Running and Slave_SQL_Running both say Yes, CONGRATULATIONS You've done it !!!

Give it a Try !!!

MySQL Replication and High Availability Guide

Given the fact that you mentioned you have Master-Master replication mode, I would not recommend any automatic failover unless you properly account for Replication Lag. After all, MySQL Replication is asynchronous.

It is theoretically possible to have the following:

DBServer1 as Master to DBServer2
DBServer2 as Master to DBServer1
DBVIP pointing at DBServer1
DBServer2 is 180 seconds behind
DBServer1 goes down
Automatic Failover moves DBVIP to DBServer2

With this scenario, DBServer2 could have auto increment keys that do not exist yet. Upon failover, the DBVIP will allow WebServers to connect to DBServer2 and ask for data that does not exist yet.

This would therefore require background processes running on each DBServer.

For the above scenario:

DBVIP is on DBServer1
DBServer1 runs HeartBeat
DBServer2 runs HeartBeat
Background Process on DBServer1 to monitor
- a) Data Mount Availability
- b) Data Mount Writeability
- c) MySQL Connectivity
- Once a,b, or c fail, kill HeartBeat

Background Process on DBServer2 to make sure DBVIP is pingable

What should killing HeartBeat do? Trigger the startup script defined for it.

What should the startup script on DBServer2 look for?

Loop until DBVIP is unreachable via ping
Connect to MySQL and
- Run SHOW SLAVE STATUS\G in a Loop until Seconds_Behind_Master is NULL
- RUn SHOW SLAVE STATUS\G in a Loop until `Exec_Master_Log_Pos stops changing
Assign DBVIP to DBServer2 via ip addr add

This is essentailly the algorithm for failing over safely to a Passive Master in a Master/Master Replication Cluster.

ALTERNATIVE

If ALL your data is InnoDB, I recommend something with less rigor. Perhaps you should look into using DRBD and HeartBeat. Here is why:

DRBD provides network RAID-1 for a Block Device on two servers.

You would essentially do this:

Have DBServer1's DRBD Block Device as Primary
Have DBServer2's DRBD Block Device as Secondary
Mount DBServer1's DRBD Device on /var/lib/mysql
Startup MySQL on DBServer1
Have HeartBeat Monitor Ping Activity Between Servers

What would startup script look like in a DRBD scenario?

Loop until DBVIP is unreachable via ping
Kill HeartBeart
Disconnect DRBD
Promote DRBD to Primary
Mount DRBD on /var/lib/mysql
Start MySQL (InnoDB Crash Recovery Fills in Missing Data)
Assign DBVIP via ip addr add

This is a lot more straightforward because only one side is Active. The Passive side (DRBD Secondary) is a Synchronous Disk Copy of the Active Side (DRBD Primary).

CAVEAT

If all or most of the working set data is MyISAM, do not touch DRBD. Crash scnearios quickly result in MyISAM tables being marked crashed and need auto-repair (which can be paintfully slow to wait for).

UPDATE 2012-12-29 08:00 EDT

Here are my past posts on using DRBD with MySQL

Mar 29, 2011 : MySQL high availability, failover and replication with Latency
Aug 29, 2011 : MySQL Replication : 1 Slave / Multiple Masters
Dec 19, 2011 : Best way to setup master to multi master replication
Jul 25, 2012 : Mysql database replication on different vlan/subnet/another site (I stirred a pretty big pot on this one in someone else's blog)