MySQL Single-Primary and Multi-Primary Group Replication

| November 22, 2018

NOTE: These clustering options are available by request. Please contact Jelastic or your hosting service provider to get this configuration.

In this article we continue to explore the possibilities of the 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 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 automaticallysingle-primary auto-clustering
  • multi-primary - all servers can accept updates (even if being issued concurrently)mysql group replication package

Now, let’s learn about specifics of running a MySQL Group Replication and differences between Single- and Multi-Primary modes in details.

Group Replication with Single-Primary MySQL Clustering

When operating with the 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 the 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 DB cluster can accept and process updates. Thus, in case of the 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 topology wizard while environment creation.check group replication

Note: The Single-Primary Group Replication is configured by default for the database cluster of 3 nodes but the number can be increased by pressing “+” in the Horizontal Scaling block of the topology wizard.

single-primary mysql clustering

After creation, you should get the topology similar to the one displayed above.

MySQL Cluster Multi-Primary Group Replication

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 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 servers (and can be increased while environment creation).multi primary mgr

The difference can be observed at the Orchestrator admin panel that can be accessed with the credentials from the appropriate email related to ProxySQL Load Balancer deployment. As is shown on this composite picture all cluster members are Writable(i.e. can accept updates).mysql cluster auto installer

Now, you know the major possibilities of embedded auto-clustered group replication and how easy it is to activate this function. So get your automatically configured highly available and reliable database cluster in no time and without any effort with Jelastic PaaS.

Related Articles

Jelastic Released Automatic Clustering for Databases and Application Servers Out-of-the-Box

Master-Slave and Master-Master Replication with MariaDB/MySQL Auto-Сlustering

MariaDB/MySQL Auto-Сlustering with Load Balancing and Replication for High Availability and Performance