In this article we continue to explore the possibilities of database auto-clustering, covering MySQL Group Replication that is available for automatic installation within Jelastic PaaS.
MySQL Group Replication (MGR) helps to create fault-tolerant systems with redundancy by replicating the state throughout a set of servers. Consequently, even if some of the servers fail (as long it is not a majority), the system still will be available. Herewith, all of the read-write transactions are committed only after being approved by the group, while read-only requests need no coordination and thus are processed immediately.
MySQL Group Replication is a MySQL server plugin that provides distributed state machine replication with strong coordination between servers. Thus servers coordinate themselves automatically when they are part of the same group. The group can operate in a single-primary mode with an automatic primary election, where only one server accepts updates at a time. Alternatively, for more advanced users the group can be deployed in multi-primary mode, where all servers can accept updates, even if they are issued concurrently.
In order to use Group Replication, server instances have to satisfy the following requirements:
- InnoDB Storage Engine – data must be stored in the InnoDB transactional storage engine;
- Primary Keys – every table that is to be replicated by the group must have a defined primary key or primary key equivalent (a non-null unique key);
- IPv4 Network – the group communication engine used by MySQL Group Replication requires a IPv4 network infrastructure;
- Network Performance – Group Replication is designed to be deployed in the cluster environment where server instances are very close to each other, and is impacted by both network latency as well as network bandwidth.
Group Replication can operate in two modes:
- Single-Primary – just one server can accept updates at a time, and if it becomes unavailable the election of a new primary node is called automatically;
- Multi-Primary – all servers can accept updates (even if being issued concurrently).
Now, let’s learn about specifics of running a MySQL Group Replication and differences between Single-Primary and Multi-Primary modes in details.
Group Replication with Single-Primary MySQL Clustering
When operating with MGR, the following specifics should be considered:
- Group can include up to 9 servers max and any subsequent attempt to add server will cause the join request to be refused;
- Internal communication is based on a peer-to-peer TCP connection, which is only used for message passing between group members;
- Additional load (when compared to master-slave and master-master replication) is expected due to the necessity of constant servers interacting for synchronization purposes;
- In order to remain operable, the majority of group members should be up; this directly affect the number of server failures, which can be tolerated.
In single-primary mode, one group-member is issued with read-write permissions (i.e., is configured as primary), while the remaining members are set to read only. In such a way just a single node of your database cluster can accept and process updates. Thus, in case of primary failure, an automatic election will be performed to assign a new one.
In order to create a single-primary group replication, select Single MGR within the topology wizard during environment creation.
After creation, you should get the topology similar to the one displayed above.
Group Replication with Multi-Primary MySQL Clustering
The Multi-Primary Group Replication mode shares the MGR specifics described in the previous section, but all cluster members are provided with read-write permissions and can accept updates. Such implementation provides benefits of simultaneous writing into different MySQL servers, and does not require time to elect and prepare new primary in case of a necessity.
In order to install a Multi-Primary Group Replication cluster, select Multi MGR in the same drop-down list of the topology wizard mentioned above. Default topology for Multi-Primary Group Replication cluster includes 3 nodes (and can be increased during environment creation).
The difference can be observed with the Orchestrator admin panel that can be accessed with the credentials from the appropriate email related to ProxySQL Load Balancer deployment. As shown in the picture below, all cluster members are Writable (i.e., can accept updates).
Now, you know the major possibilities of embedded auto-clustered group replication and how easy it is to activate this function. Start easily and get your automatically configured highly available and reliable database cluster in no time and without any effort at our Jelastic PaaS platform.