Do It Yourself

One important reason for the success of MySQL [1] is asynchronous replication, introduced in the 2001 release 3.23, which allows admins to set up replica instances (slaves) that receive all data changes from the master system. When MySQL only needs to read data, it can do so on a slave to reduce the load on the master.

Many admins were quick to use this replication method to promote one of the slaves to the master if the master server failed. In practice, users often suffered data loss because the slave was not up to date and the asynchronous transmission of changes caused backlogs of anything from a few seconds to several minutes.

MySQL v5.5 saw the introduction of semi-synchronous replication, in which the master waits until a change reaches one of the slaves (but not until it is applied). In the case of multiple concurrent write access, however, it comes at the expense of server throughput and is therefore rarely used.

Highly Available

MySQL has thus not been able to achieve true high availability with its on-board tools. In the community, Galera Cluster by Codership Oy, released under the GPLv2 [2], has enjoyed increasing popularity in recent years. It is based on a MySQL server and supports writing to each of the nodes – given that you have at least three servers.

Galera uses a group protocol to ensure that changes can be made on all nodes. If this is impossible because another user has changed the same data at the same time on another node, MySQL rolls back the transaction. In this case, the client needs to handle the error (e.g., by resending the queries).

Prompted by the popularity of Galera Cluster, Oracle reacted, publishing a stable release of the Group Replication plugin in September 2016 with MySQL Server 5.7.17 after no fewer than eight prereleases. Group replication is a "shared nothing" architecture: All nodes of a group keep all data. Communication between the nodes relies on the Mencius protocol [3] using a variant of the Paxos algorithm.

The system generates a unique order for all writes so that a winner is always found in the case of concurrent access to the same data. A new node can be added to the group at any time – all you need is a reasonably recent backup of an existing node. The plugin then completes final synchronization using the classic MySQL binary logs.

Requirements

Some requirements need to be met if you want to use the Group Replication plugin. Because a majority of nodes is always necessary to make decisions, each group must have at least three nodes. Group replication works only with tables that use InnoDB as the storage engine; the legacy MyISAM engine is not supported. Moreover, it is imperative for each table to have a primary key.

Surprisingly, IPv6 is still lacking – in 2017! Currently, network communication between the replicating nodes relies on IPv4, but improvements have been promised in one of the upcoming releases. Also, Oracle requires a fast network. Transatlantic WAN links are thus ruled out, for the time being, as potential Group Replication applications.

One Master, or Many

Group replication is primarily a high-availability feature. Higher capacities for read access is a side effect, whereas write capacity does not benefit. The default operation mode for a group is single-primary mode , in which the group defines one node as the primary. The database can write to this node only. The remainder are available for read queries and can assume the role of the primary if it happens to fail.

A second operating mode known as multi-primary allows writing to every node. If you want to use this mode, you need to take a closer look at its limitations [4]. Make sure there are no concurrent data definition language (DDL) queries. Simultaneous changes to the structure of a single table from multiple nodes can lead to data loss and the failure of the entire group. Therefore, the use of multi-primary mode is not currently recommended.