MariaDB/MySQL Auto-Clustering Replication

Today, we’ll continue discussing database auto-clustering and focus on two common data replication types for MariaDB and MySQL: Master-Slave and Master-Master replication. We’ll see how each helps solve performance issues, set up backups, and reduce system failures, all in a simple clustered solution that can be installed in just a few clicks.

Master-Slave Replication

Master-Slave replication is ideal for situations with many read requests and few write requests, as it helps share the read load across multiple database servers. We have created a setup with pre-configured replication for these needs.

You can easily install this replication setup using the AccuWeb.Cloud topology wizard, which includes the Auto-Clustering feature. This offers benefits like automatic discovery of new nodes, horizontal scaling without downtime, and an easy-to-use management interface.

All necessary configurations are applied automatically, including:

– Asynchronous master-slave replication is set up across all database instances, with the first created MariaDB or MySQL container acting as the master, while all other nodes (including any added later) serve as slaves.

– The environment includes dedicated ProxySQL load balancers, which handle incoming traffic and distribute it across the database nodes.

By default, each balancer has 8 cloudlets (equal to 1 GiB of RAM and 3.2 GHz of CPU) for automatic scaling. You can choose to exclude this layer from the cluster before installation.

Remember, you cannot add or remove ProxySQL after setup. After creation, the cluster will look like this:

Note: If the master node goes down, you must manually promote a slave to become the master. If a slave node fails, ProxySQL will redirect read requests to the master, and the cluster will continue to function.

You can access the Orchestrator panel (using the credentials from the email you received) to check the replication status, view the cluster topology, and monitor the health of all nodes.

As shown, there is one container assigned as master, and the others are assigned as slaves, meaning the master-slave replication has been successfully configured by AccuWeb.Cloud platform.

Master-Master Replication

Master-master replication helps balance the writing load and makes recovery easier if one master node fails, ensuring better availability. In AccuWeb.Cloud, this setup is configured so that two database nodes act as masters.

When scaling out, extra slave nodes are added to improve reading performance and evenly distribute the replication workload. These slaves are first added to one master node, and then to the other, so they are balanced.

After setup, you can access the Orchestrator panel (using the credentials from the email you received) to check the replication status.

As shown, there are two containers assigned as masters, and replication is successfully configured.

You can also scale out the cluster by adding more slave database servers using the topology wizard.

Please note that for production environments, it’s best to scale manually during maintenance periods or when there’s low load.

If you want to enable automatic horizontal scaling for your database, set higher trigger intervals based on the amount of data to avoid continuous scaling actions.

Horizontal scaling for both setups is done by cloning an existing slave node. If there are any differences, the new database will catch up on data using binlog replay and join the cluster.

This cloning process ensures that the binlog won’t expire, allowing for quick scaling.

That’s it! Improve your project by integrating an automatically configured MariaDB/MySQL database cluster with the most popular replication type already set up for you.