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

| November 15, 2018

Today we continue the topic of database auto-clustering and will cover in more details two most common and frequently used data replication types for MariaDB and MySQL: Master-Slave and Master-Master replication. Let’s see how each of them lets easily solve performance problems, configure backups and eliminate system failures being packaged in a turnkey clustered solution that is installed in a few clicks.

Master-Slave Replication

Master-slave replication type is suitable for scale-out solutions that have a high number of reads and a low number of writes, as well as require distribution of read load across multiple database servers. For such needs, we packaged the following topology with pre-configured replication.simple replication auto clustering

This replication setup can be easily installed right from the Jelastic topology wizard using embedded Auto-Clustering feature that provides a set of benefits, i.e. new nodes autodiscovery, horizontal scaling with no downtime, intuitive management GUI, etc.connection example mysql

Herewith, all of the required configurations are applied automatically, namely:

  • The asynchronous master-slave replication is implemented across all DB instances with the very initially created MariaDB or MySQL container being assigned as master, whilst all the rest of nodes (including any subsequently added ones) will serve as slaves.
  • Environment topology is complemented with the dedicated ProxySQL load balancers (added to the extra layer), that are intended to process an incoming load and distribute it across database nodes. By default, each balancer is assigned 8 cloudlets (which equals to 1 GiB of RAM and 3.2 GHz of CPU) for automatic vertical scaling. If necessary, you may exclude this layer from the cluster topology before installation only.

    mariadb proxy sql
    Keep in mind that you won't be able to add or remove ProxySQL later.

After creation, the cluster topology looks as follows.mysql master master replication

Note: If a master node goes down, the slave should be manually promoted to master role. If a slave node fails, ProxySQL routes the read requests to master and cluster keeps working.

Access an Orchestrator panel (using the credentials from the received email) in order to check a replication state, to observe cluster topology connections and to monitor state-of-health of all nodes.install mysql master slave

As you can see, there is a container assigned as master, and the rest ones (either initially created or as a result of scaling-out) are assigned with slave roles, i.e. the master-slave replication was successfully configured by Jelastic platform.

Master-Master Replication

The master-master replication provides benefits of balancing the writing load, and simplifies recovery upon one of master nodes failure ensuring higher availability. Jelastic implementation of such database clustering is configured bidirectionally with two database nodes set up as masters.

While scaling out, the cluster is extended with extra slaves. To ensure equal replication workload distribution and increase the reading performance of the cluster, the extra slaves are added, in turn, first to one master node, and then to another, equally allocated among them.master-slave replication package

After creation, you can access an Orchestrator panel (using the credentials from the received email) in order to check a replication state.mysql simple replication

As you can see, two containers are assigned with master roles and replication is successfully configured.

The cluster can be scaled out with additional slave database servers via topology wizard.

Please note that, for production environments, it is better to perform scaling manually during the maintenance period or while having low load. If you prefer to activate automatic horizontal scaling for your database, set up higher trigger intervals considering the amount of available data in order to avoid a loop of scale-out actions.master slave auto clustering

Horizontal scaling for both topologies is performed via cloning of an existing slave node. Then, if any difference occurs, the new database catches up data via binlog replay and joins the cluster. Cloning procedure ensures that the binlog will never expire and scaling will be conducted in a short period of time.

That’s it! Enhance your project by integrating an automatically configured MariaDB/MySQL database cluster with the most popular replication type already set up for you. Try it right now absolutely for free on any of our partners' platforms.

Related Articles

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

MariaDB Galera Cluster Replication

PostgreSQL Auto-Clustering with Asynchronous Master-Slave Replication

MongoDB Replica Set Auto-Сlustering for High Availability and Performance