Advantages and disadvantages of MySQL replication types and how to run them in the cloud image

Advantages and disadvantages of MySQL replication types and how to run them in the cloud

MySQL replication types

MySQL is one of the most popular open source databases in the world. It is very easy to start using MySQL for your application or website. For example, MySQL is the default option for popular CMS solutions such as WordPress and Magento.

However, it is not trivial to make MySQL fully reliable and available under high load. Database replication and clustering can help you achieve this goal.

As one of the first open source databases, MySQL’s replication capabilities have evolved over time. Here is a list of the most commonly used replication types for MySQL:

Let’s take a closer look at each of them.

MySQL Master-Slave Replication

MySQL master-slave replication was the very first replication option for the MySQL database. It assumes that you have a single master server that accepts reads and writes and one or more read-only slave servers. Data from the master server is replicated asynchronously to the slave servers.

For

  1. It is very fast because it imposes no restrictions on performance.
  2. You can distribute read and write requests to different servers. For example, all analytical requests can be made on slave nodes.

Against

  1. It is not very reliable because of the asynchronous replication. This means that some transactions committed on the master may not be available on the slave if the master fails.
  2. Write requests can hardly be scaled up. The only option to scale write requests is to increase the computational capacity (RAM and CPU) of the master node.
  3. The failover process is manual in a general case. You have to take care of the promotion from the replica node to the master node.

MySQL Master-Master Replication

MySQL master-master replication hasevolved from master-slave replication and solves its main problems. This type of replication assumes that you have at least two master nodes capable of accepting read and write requests. In addition, you can have several slave nodes for each of your masters. Replication between master nodes is asynchronous.

For

  1. You have the possibility to scale up the write requests not only by increasing the computing capacity of a single master node, but also by adding additional master nodes.
  2. The failover is semi-automatic because you have several master nodes. The risk of all master nodes failing simultaneously is very low. If one of the master nodes fails, there is at least one other master node that can handle its requests.

Against

  1. Due to the asynchronous replication between all master nodes, you may lose some transactions if one of the master nodes fails.
  2. Because of asynchronous replication, you cannot be sure that the backups on each master node contain the same data.
  3. Failover is still not fully automated in the event that you need to promote a slave node to a master node.

MySQL MGR Replication

TheMySQL Group Replication feature is implemented by a MySQL Server plugin and provides replication in a completely new way based on a distributed state machine architecture.

MySQL group replication allows you to create fault-tolerant systems with redundancy that ensures that even if some of the servers fail (as long as it is not the majority), the cluster will still be available. The unique feature of MGR replication is that it gives you built-in automatic recovery and conflict resolution.

How can MySQL group replication help you?

For

  1. Automatic failover in case of failure of the master node. Servers that belong to the same cluster automatically elect the new master. Therefore, MySQL MGR replication can be considered as a clustered solution.
  2. This is synchronous replication in general, so you can be sure that you will not lose committed data if the master node fails.
  3. You can scale the reads and writes by adding new master and slave nodes.
  4. It does not impose any major performance restrictions as only 2 master nodes are required for a full-fledged MySQL cluster.

Against

  1. It is only available for MySQL, but not for its forks: MariaDB and Percona.
  2. A group is limited to 9 knots.

Galera Cluster (multi-master MySQL replication)

Galera Cluster is a synchronous database cluster with multiple master nodes, based on synchronous replication. Galera acts as a multi-master and allows you to direct read and write requests to any node at any time. If any of the individual nodes are lost, there is no interruption of operations. The nodes are initialised automatically and there is no need to manually backup the database and copy it to the new node. Galera is a very secure solution because it provides synchronous replication, i.e. there is no visible lag on the slave side, data is not lost when a node is lost.

For

  1. It is reliable because it guarantees the security of the data thanks to the quorum protocol.
  2. Galera gives you true clustering capabilities, including automatic failover.
  3. Reading requests are very fast and can be scaled up efficiently.

Against

  1. It has a significant performance overhead because it assumes that all transactions will be committed on at least three servers. If you have massive write requests, this can cause performance degradation even for read requests.
  2. It is only supported for MariaDB and Percona XtraDB, but not for the MySQL database.

MySQL HA Architecture in Hidora

When we decided to implement the MySQL high availability support site on Hidora, we took into account a number of considerations, including reliability, scalability, ease of installation, ease of configuration and monitoring.

Therefore, our MySQL architecture includes the following components:

The main feature available on Hidora is the automatic clustering and horizontal scaling of MySQL/MariaDB nodes. This means that you can manage these databases via a simple user interface on the PaaS layer, but not only as virtual machines. For example, you can increase the number of slave nodes with one click, and they will automatically join your database cluster. And vice versa, you can safely shrink the cluster and easily remove unnecessary nodes.

In addition to the built-in Hidora user interface, you can access the Orchestrator control panel to view and monitor the MySQL/MariaDB replication process.

How to install MySQL HA solutions on Hidora?

How do I enable automatic clustering for MySQL databases?

  1. Click on NEW ENVIRONMENT in the dashboard and choose the MySQL database.
  2. Next, you need to press the Auto-Clustering button to display different replication options for the MySQL database in the corresponding drop-down list. You can choose one of the following options:
mysql-replication

Your replicated database will be created and configured as soon as you press the “Create” button. All the magic will be done behind the scenes, so you don’t need to perform any additional actions.

mysql

Once you have created the database instance, you can log in to the Orchestrator control panel using the access information in the email you received.

how to run mysql
Start the free trial now
profile picture

Written By

Matthieu ROBIN

01/02/2019

Matthieu Robin is the CEO of Hidora, an experienced strategic leader, a former system administrator who has managed and configured more environments manually than anyone else on the planet and after realising that it could be done with a few clicks created Hidora SA. Follow him on Twitter.

Start your free trial

No credit card required. Free 14 day trial.

We only use your personal data to create your account, promise!

Choose your currency

chf
eur

Read more articles

All Articles
bg

Receive our news