MySQL/MariaDB Cluster as a Service with Automatic Replication, Load Balancing and Scaling
For High Availability and Maximum Performance.
Enterprise Database Cluster for your Cloud Applications
Database clustering is a mandatory requirement for highly loaded applications to ensure high data protection, availability and performance. However, the configuration of a reliable cluster is not a trivial task even for experienced developers and system administrators.
To solve this problem, Buyucloud introduces out-of-the-box clustering from MariaDB/MySQL to make your database multiple available by default.
- High Availability with pre-configured options replication
Master-Slave, Master-Master, or Galera Cluster. - Scalability Autodiscovery
New nodes, added during horizontal scaling, will be connected to the cluster with all necessary adjustments applied automatically. - Efficient Load Balancing
Added two ProxySQL nodes in each cluster to maintain load balance by splitting up read/write requests. - Automated Failover
Database nodes that are temporarily unavailable or have high latencies will be automatically removed from the cluster and reinserted when the connection is restored.
What Replication Type to Choose?
Let’s consider each replication scheme available for databases in Dewacloud to find out which database is most suitable for specific needs and certain cases. To solve the problem, Dewacloud introduces out-of-the-box clustering for MariaDB/MySQL to make applications always available by default.
Master-Slave MySQL/MariaDB Replication
Master-slave replication is the most frequently used topology that provides stable performance and consistency (eg, one exact node to change data), but no automatic failover on master failure.
Write latency in asynchronous replication is low because the activity has been recorded locally by the master server before being transferred to the slave server. This makes it possible to increase the readability, giving the best performance, because adding replicas will not affect the replication latency.
Slave servers can perform read activity without impacting the master server, providing clear advantages such as:
- High performance for read requests
- Database backups can be done without impacting the master instance
- Analytical requests can load only on the slave server instantly with no effect on the master server.
Master-Slave MySQL/MariaDB Replication
Master-master asynchronous replication operates with 2 master nodes simultaneously, compared to the default master-slave solution, this gives the advantage of balancing load writing and ease of recovery on one of the problematic master nodes.
Unlike the default setting, in Dewacloud scaling a master-master cluster will lead to adding slaves to the cluster.
On creation, the instances of the slaves are evenly distributed between the master nodes allowing for smooth distribution of the replication workload.
MariaDB Galera
Galera clusters are a type of multi-master synchronous replication that will be executed at transaction commit time, by broadcasting, transaction writes changed to all cluster nodes to be applied and ensuring writes are sent to all nodes in the cluster before these writes are actually performed.
User applications can send reads and writes to any node in the cluster which provides a feature to scale-out read and write transactions. The addition of nodes to the cluster is entirely done automatically. Excluding nodes from the cluster is just like removing nodes you don’t need or fail. There is no longer a need to implement thick logic to separate reads and writes, the potential scaling can be implemented immediately without having to change the application logic.
Galera offers one of the best protection against lost data and inconsistent databases because there is no delay in data replication. If one of the nodes in the cluster is not working, application users will not see it and will continue to serve users using other nodes that may be located in other data centers.
Find out the steps to enable auto-clustering for your MariaDB/MySQL database.
Explore the steps below to activate auto-clustering for your MariaDB/MySQL databases.
Click on NEW ENVIRONMENT at the dashboard and choose MariaDB or MySQL database.
Activate the Auto-Clustering button. As a result, a dropdown with different replication schemes will appear:
- MariaDB – Master-Slave, Master-Master, and Galera
- MySQL – Master-Slave and Master-Master
Choose the replication type you prefer, add the necessary number of nodes.
Highly Available ProxySQL Load Balancer
Each MariaDB/MySQL auto-clustering solution has two ProxySQL nodes enabled by default in front of the database cluster. If required you can exclude them from the cluster topology before installation with the respective switch. Keep in mind that you won’t be able to do that afterward.
During cluster creation, Jelastic automatically generates database access credentials, but you may override them with your own ones in three simple steps:
Step 1:
Click on the Variables button.
Step 2:
Then press Add to provide custom user’s database credentials via two variables DB_USER and DB_PASS as follows:
Step 3:
Press Apply and custom credentials will be mailed to you upon cluster successful installation as for access to the PHP MyAdmin at Master Node and database cluster Entry Point.
Cluster Horizontal Scaling:
If you decide to scale the master-slave/master-master topologies with an extra database node, it will be created via cloning an existing slave node. Once the cloning procedure is completed the database on the new cluster member catches up data via binlog replay. Such an algorithm guarantees the binlog will never expire and horizontal scaling takes a short period of time.
Cluster Layers Isolation
Depending on whether you are going to use an external application or not, you may decide what layers you will expose outside – all or the entry point proxy layer only. Turn the SLB access switch into the required position for each layer and click Create.
That’s all! No configurations required, the cluster is ready to work with.
Database Cluster Access Information
After the installation process is successful, you will get a number of emails with cluster information.
PHP MyAdmin at Master Node
Web administration interface with credentials to access the database server for interactive management.
Entry Point for Connections to MySQL Cluster
hostname and credentials for connecting an application to the database cluster.These nodes form a proxy layer referred to as the entry point for the database cluster with hostname as follows: proxy.${envName}.${platformDomain}.
Note:
In case you are going to create and use the custom user accounts after cluster installation, you should add them to the mysql_users table on each ProxySQL node, otherwise you won’t be able to establish connection to the database via the proxy layer. To do this issue commands below:
$ MYSQL_PWD=admin mysql -h 127.0.0.1 -P6032 -uadmin -e “INSERT INTO mysql_users (username,password,default_hostgroup) VALUES (‘<username>’, ‘<password>’, 2);”
$ MYSQL_PWD=admin mysql -h 127.0.0.1 -P6032 -uadmin -e “LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;”
Substitute <username> and <password> with credentials of a newly created database account.
Cluster Orchestrator Panel
Credentials to access the Orchestrator panel, intended for convenient cluster management. Use the received credentials to access admin panel of cluster Orchestrator installed on ProxySQL node, that provides a possibility to review the cluster topology information: slick visualization of topologies, replication problems if there are any, read/write distribution, state of health check-ups and autodiscovery of newly added DB nodes, etc.
Install MySQL/MariaDB in One Click!
Buyucloud provides MySQL/MariaDB which can be installed quickly and easily. Free for 14 days, after that the cost of hosting the WordPress cloud application is calculated hourly based on resource usage.