What kind of MySQL replication?

Question

I'm working on an ERP project for an educational institution. They have two branch offices and we need to keep records not only for customers, but also class calendars, class reservations for teachers.

At the beginning I'll start with a cloud based web application. However, in the future I will need to replicate database to two additional physical addresses. Power outage and loss of internet connection are not rare in the city.

What replication model do you recommend? Master-Master, Master-Slave, or Mysql Group Replication? What about MySQL Cluster?

Answer 1

Master-Slave is pretty good, but you must write only to the Master. Inability to write to the Master means inability to do the various tasks. A network outage between M and S leads to delayed updating of the Slave.

Master-Master, and writing to both, has several ways to get into trouble, so I recommend against it.

Master-Master, but writing to only one solves some of the M-M problems, and makes failover easier. That is, if the Master fails, the configuration is ready to promote the Slave in an otherwise M-S topology to be the Master.

Group Replication has a lot of promise, and is likely to be the best in the long run. However, I cannot say much at the moment.

MySQL NDB Cluster is complex to set up, has many moving parts, uses "eventual consistency" model of replication, etc. Some applications can use it; hard to say if it fits your situation. Study its implementation of "eventual consistency"; if that model works for you, then consider putting an instance of the three nodes (data, sql, mgt) on a single server at each of your two sites. (I assume scaling is not an issue.)

Galera (see MariaDB, Percona's PXC, or Codership), which you did not list, is robust, write-anywhere, self-healing, etc. The optimal setup would be to have 3, not 2, "nodes" in 3 separate locations with separate network connections. As long as any 2 nodes can talk to each other, those 2 declare themselves to be online. If the third is alive, but isolated (because of network outages), it understands that is should be "offline". When an offline node comes back online (from network outage or servers crash), the data is automatically repaired with the help of the online nodes.

Answer 2

I've used MySQL binlog(File) based replication and now I'm using GTID based replication on a master master setup. In the file based replication, like Rick James mentioned, fail-overs were headache or invalid SQL executions could cause serious issues.

Since one week, I've been using the new 5.7.17 server with GTID master-master replication and it is much better than the file based replication. (Although not enough data gathered and time will tell but my guts and experience sofar says it's much better. Except i had to rewrite many queries due to limitations and changes)

When you think about Master-Master replication, you need to take care of few things.

1. Both Masters must have different server_id
2. Both Master creates Auto-number on a different Chanel (M1 creates even numbers & M2 odd numbers)(helps to avoid hitting key violations).

use:

auto_increment_increment= 2
auto_increment_offset   = 1

options in the config file to specify your auto-number range.

If you are going to try the GTID replication, make sure you read the limitations: https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-restrictions.html

General tip: have a MySQL monitor for slave status. (I've wrote c# console app which sends out email once a slave goes down. Helps in many ways)

I have to add this too: I've been testing the MySQL cluster and must admit it's impressively easy to set up and I probably will change to cluster at some point.