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.
Table of Contents
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:
Now, click on the NEW ENVIRONMENT to create a new environment for MariaDB clustering. You should see the following screen:
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.
To create database credentials, click on the Variables button. You should see the following page:
Now, click on the Add button and provide your database username, password then click on the Apply button.
Next, click on the Create button to deploy the cluster. You should see your MariaDB cluster in the following page:
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.
You can access the phpMyAdmin as shown below.
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:
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.
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.
- Spreading the load
- Increasing the performance
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 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.