MariaDB/MySQL Auto-Clustering: Scalability, Load Balancing, Failover

AccuWeb.Cloud offers an easy way to set up MariaDB database clusters, which are essential for ensuring your applications stay online and run smoothly, especially when handling lots of users.

Setting up a reliable cluster can be complex, even for experienced developers and system administrators. To solve this, AccuWeb.Cloud has introduced built-in clustering for MariaDB/MySQL, making it simple to achieve high availability and performance.

Here are the key benefits

• High Availability: You can choose from different setups like Master-Slave, Master-Master, or Galera to keep your database running even if one server goes down.
• Scalability and Autodiscovery: Adding more servers as your app grows is easy. The new servers automatically join the cluster and start helping out right away.
• Efficient Load Balancing: Every cluster comes with two ProxySQL nodes that spread out the work of handling requests, so your database stays fast and responsive.
• Automatic Failover: If a server has problems, it’s taken out of the cluster until it’s fixed, and then it joins back in without you having to do anything.

You can activate auto-clustering for your MariaDB and MySQL databases with just a few clicks using the setup wizard in AccuWeb.Cloud PaaS. It’s designed to make your database setup straightforward and reliable, ensuring your applications run smoothly even during busy times.

Step 1. Click on “New Environment” on the AccuWeb.Cloud dashboard, select MariaDB as the database, enable the Auto-Clustering option, and under the Scheme section, choose from the following options: Primary-Primary (Master-Master), Primary-Secondary (Master-Slave), or Galera.

Step 2. Enter the environment name of your choice.

Step 3. Every MariaDB/MySQL auto-clustering configuration comes with two ProxySQL nodes pre-enabled in front of the database cluster.

If you need to exclude them, you can do so before installation by selecting the corresponding option. Note that this cannot be done after the installation.

Step 4. Select the “variables” option.

Step 5. Click on Add to set your custom database credentials using the variables DB_USER and DB_PASS. Enter your chosen username and password. Although AccuWeb.Cloud generates database access credentials automatically during cluster creation, you can replace them by following these steps.

Step 6. After pressing Apply, the custom credentials will be sent to you via email once the cluster is successfully installed. These credentials will grant access to PHPMyAdmin on the Master Node and the database cluster Entry Point.

Step 7. Depending on whether you intend to utilize an external application, you have the option to choose which layers to make accessible externally: either exposing all layers or limiting access to only the entry point proxy layer.

Step 8. Adjust the SLB access switch accordingly for each layer and proceed to click Create.

Step 9. The cluster environment has been established and is operational.

Upon successful installation, you will receive several emails containing cluster information, including credentials to access the PHP MyAdmin web administration interface at the Master Node.

Step 10. These credentials will allow interactive management of the database server.

Step 11.  Access PhpMyAdmin and log in using the username and password that you received via email.

Step 12. After logging in, we can manage and administer the database server, performing tasks such as database creation, modification, querying, and maintenance.

Step 13. Navigate to ‘Web SSH’ from your environment.

Step 13. The hostname and credentials are essential for establishing a connection between your application and the database cluster.

They refer to a set of nodes that serve as a gateway, often termed the entry point, enabling communication with the database cluster. Specifically, the hostname follows this format: `proxy.${envName}.${platformDomain}`.

Step 14. Access the Cluster Orchestrator Panel using the link provided in the email and sign in.

The Cluster Orchestrator Panel offers access credentials to its admin interface, designed for efficient management of your cluster. These credentials allow you to log in to the Orchestrator panel installed on the ProxySQL node.

Through this panel, you can visualize the cluster’s architecture, monitor replication status, manage data distribution between read and write operations, perform health checks, and automatically detect newly added database nodes for seamless integration into your setup.

Replication schemes

1. Master-Slave MariaDB/MySQL Replication

In master-slave replication, one main server (the master) handles all data changes for consistency. However, if the master fails, there’s no automatic backup plan. With asynchronous replication, data is stored quickly on the master before being sent to the slave servers.

This method makes it easy to scale up reading speeds without slowing down data updates. It’s great for:

• Fast reading
• Safe backups without slowing down the main server
• Running analyses without affecting the main server

2. Master-Master MariaDB/MySQL Replication

In master-master asynchronous replication, two main servers work together at the same time. This setup is different from the usual master-slave setup because both servers can handle writing data and can take over if one server stops working.

In AccuWeb.Cloud, when you scale up a master-master cluster, additional servers are added to help out. These new servers are divided evenly between the main servers when they are set up. This helps to spread out the work of copying data and makes the cluster better at handling tasks that read data.

3. MariaDB Galera

A Galera cluster is a special setup where multiple servers can all act as main servers that sync up data in real-time. When you save something, it gets sent to every server at once, making sure they all have the same data right away. This setup is great because:

• Any part of your app can read from or write to any server in the cluster.
• You can easily add more servers whenever you need to, and remove ones that aren’t working.
• Unlike older ways of doing things, you don’t have to worry about splitting up where reads and writes go. Your app can just keep running, even if one of the servers stops working.

Conclusion

AccuWeb.Cloud’s easy-to-use MariaDB clustering options ensure your applications run smoothly with high availability and efficient performance. With automated setup and management features, you can scale your databases seamlessly and handle traffic spikes effortlessly. Explore these capabilities today to streamline your database operations and enhance your application reliability.