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

mariadb/mysql auto-сlustering with load balancing and replication for high availability and performance

These days high availability and database clustering are very important for highly loaded production applications. If your server is down for a fraction of time, you are losing customers and money. Therefore making a database environment highly available has typically one of the highest priorities. Set up a highly available cluster is not easier for any developers and system administrators.

RoseHosting cloud platform provides MariaDB/MySQL clustering out-of-the-box to make your application highly available.

Features of MariaDB/MySQL Clustering

high availability with pre-configured replication options: You have three options for replication, Master-Slave, Master-Master, and Galera.
scalability and autodiscovery: New nodes are added automatically during horizontal scaling with all required configurations. So you don’t need to do anything manually.
efficient load balancing: Each cluster comes with two ProxySQL nodes that are used for load balancing and also enables automatic splitting of read/write requests,
automated failover: Exclude those nodes automatically from the cluster which are temporarily unavailable. Once the connections are restored, they have added automatically.

Setup Clustering for MariaDB/MySQL Databases

In this section, we will show you how to enable auto-clustering for your MariaDB and MySQL databases in RoseHosting Cloud.

First, log in to RoseHosting cloud platform as shown below:

how to setup mariadb/mysql auto-сlustering with load balancing and replication

Now, click on the NEW ENVIRONMENT to create a new environment for MariaDB clustering. You should see the following screen:

setting up mariadb mysql auto сlustering with load balancing and replication

Now, select the MariaDB or MySQL database, define your Scaling limit, Reserved cloudlets, enable the Auto-Clustering option and select Master-Slave.

There are different replication schemes for MariaDB and MySQL:

MariaDB: You can choose Master-Slave, Master-Master or Galera.
MySQL: You can choose Master-Slave or Master-Master.

Create Database Cluster Access Credentials

By default, RoseHosting cloud creates database access credentials automatically. But, you can also create your own database credentials.

ultimate guide to mariadb mysql auto сlustering with load balancing and replication

To create database credentials, click on the Variables button. You should see the following page:

mariadb/mysql auto-сlustering with load balancing and replication setting up layers
layer settings for mariadb/mysql auto-сlustering with load balancing and replication

Now, click on the Add button and provide your database username, password then click on the Apply button.

container layer settings for mariadb/mysql auto-сlustering with load balancing and replication


Next, click on the Create button to deploy the cluster. You should see your MariaDB cluster in the following page:

cluster components for mariadb/mysql auto-сlustering with load balancing and replication

Access Cluster Components

Once the cluster has been deployed successfully, you should receive an email with all credentials required to access phpMyAdmin at Master Node, Cluster Orchestrator Panel and database cluster Entry Point.

setup components for mariadb/mysql auto-сlustering with load balancing and replication

phpMyAdmin Interface

You can access the phpMyAdmin as shown below.

php myadmin for mariadb/mysql auto-сlustering with load balancing and replication

Here, you can create, delete and manage your databases.

Entry Point for Connections to MySQL Cluster

On the MariaDB cluster dashboard, click on the Web SSH button to access the MariaDB console as shown below:

dashboard for mariadb/mysql auto-сlustering with load balancing and replication

Here, you can run host command followed by proxy.mariadb.rosehosting.us to check the cluster nodes IP address.

Cluster Orchestrator Panel

You can access the Cluster Orchestrator Panel using the credentials received in the mail.

orchestrator panel for mariadb/mysql auto-сlustering with load balancing and replication
orchestrator panel settings for mariadb mysql auto сlustering with load balancing and replication

From here, you can manage your cluster, review the cluster topology information and replication problems. You can also check the health state and newly added database node using the panel.

What Replication Type to Choose?

In RoseHosting cloud platform, there are three replication schemes available for databases. A brief explanation of each is shown below:

Master-Slave MariaDB/MySQL Replication

If you want good consistency, but no automatic failover then Master-slave replication is the best option. In Master-slave replication, data from one database server to be replicated to one or more database nodes. The master node is responsible for write operations while all read operations are performed by the Slave node.

Features

  • Backups
  • Scale-out
  • Spreading the load
  • Increasing the performance
  • Security

Master-Master MariaDB/MySQL Replication

Master-master replication is a method of database replication that allows data to be stored by a group of computers, and updated by any member of the group. Compared to Master-Slave replication, Master-Master replication provides load balancing and failover. In Master-Master replication, data to be copied from either server to the other one. This will allows us to perform MySQL read or writes from either server.

MariaDB Galera

MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB and supports the XtraDB/InnoDB storage engines. It has the ability to scale-out read and write transactions. Nodes can be added automatically in the Galera cluster. One of the greatest advantages of Galera is to provide protection against data loss without any delay in replication.

Of course, you don’t have to install and configure all of the above if you use our Managed Cloud Hosting, in which case you can ask our support technicians to install and configure MariaDB/MySQL Auto-Сlustering with Load Balancing and Replication for High Availability and Performance.

If you liked this tutorial, please share it with your friends on social medias.

Leave a Comment

To prove you are human please solve the following *