In today’s data-driven world, application availability is paramount. Any downtime can significantly impact your business, leading to revenue loss and reputational damage. To combat this, this article guides you through building a highly available database infrastructure using MariaDB Galera Cluster for synchronous replication.
Table of Contents
Why Galera Cluster?
Galera Cluster is a popular choice for synchronous database replication, offering several advantages:
- Synchronous replication: All nodes in the cluster receive data writes simultaneously, ensuring data consistency and eliminating data loss in case of a node failure.
- No single point of failure: Data exists on all nodes, making the system resilient to hardware failures.
- Read scalability: You can distribute read operations across all nodes in the cluster, improving performance for read-heavy workloads.
- Open source and vendor-neutral: Galera works with various databases like MariaDB and Percona Server, providing flexibility and avoiding vendor lockin.
Prerequisites
Three servers running Ubuntu/Debian with static IPs (e.g., 192.168.16.171, 192.168.16.172, 192.168.16.173)
Installing Required Packages For MariaDB Galera Cluster
On all three servers, install MariaDB, Galera, and rsync:
sudo apt update
sudo apt install mariadb-server mariadb-client galera-4 rsync -y
Configure MariaDB for Galera
On all servers, edit the MariaDB configuration file:
nano /etc/mysql/mariadb.conf.d/50-server.cnfAdd or modify the following lines within the [mysqld] section, ensuring to adjust wsrep_node_address and wsrep_node_name according to each server’s IP and a unique name:
[mysqld]
binlog_format=row
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://192.168.16.171,192.168.16.172,192.168.16.173"
# Galera Synchronization Configuration
wsrep_node_address="192.168.16.171"
wsrep_node_name="node1"
# Galera State Transfer
wsrep_sst_method=rsyncAlso on each server, run the mysql_secure_installation command to secure your MariaDB installation.
sudo mysql_secure_installationBootstrapping the Galera Cluster
On Node 1 (192.168.16.171): Start MariaDB and initialize the new Galera cluster:
sudo systemctl enable --now mariadb
sudo galera_new_clusterOn Node 2 & 3: Start MariaDB. They will join the existing cluster:
sudo systemctl enable --now mariadb
sudo systemctl start mariadbVerify Cluster Status
On any node, connect to MariaDB and check the cluster size:
sudo mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size';"You should see wsrep_cluster_size with a value of 3, indicating all nodes have joined the cluster. You can further examine cluster status with:
sudo mysql -u root -p -e "SHOW STATUS LIKE 'wsrep%';"
If the cluster size isn’t displaying as expected, try restarting the MariaDB service on each node in your cluster. You can do this with the following command:
sudo systemctl restart mariadbSetting up HAProxy as Load Balancer
HAProxy is a high-availability load balancer that will distribute traffic among our Cluster nodes. On a separate server, you can install HAProxy with following command:
sudo apt install haproxy -yOnce the installation is complete, you can edit the HAProxy configuration file to suit your needs.
sudo nano /etc/haproxy/haproxy.cfgHere is an example configuration for HAProxy:
global
log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
stats timeout 30s
user haproxy
group haproxy
daemon
defaults
log global
mode tcp
option tcplog
option dontlognull
timeout connect 5000ms
timeout client 50000ms
timeout server 50000ms
frontend mysql_frontend
bind *:3306
mode tcp
default_backend mysql_backend
option mysql-check user haproxy_check
backend mysql_backend
mode tcp
balance roundrobin
server node1 192.168.16.171:3306 check
server node2 192.168.16.172:3306 check
server node3 192.168.16.173:3306 checkTest HAProxy configuration for syntax errors:
sudo haproxy -c -f /etc/haproxy/haproxy.cfgIf there are no syntax errors, restart the HAProxy service for the changes to take effect:
sudo systemctl restart haproxyTo configure HAProxy to start automatically during system boot, you can enable the HAProxy service using the following command:
sudo systemctl enable haproxyHAProxy is now configured to listen for database connections on port 3306 and will distribute them across your Galera nodes.
Congratulations! You have successfully set up a highly available database infrastructure with MariaDB Galera Cluster and HAProxy. You can now enjoy synchronous replication, automatic failover, and improved performance for your applications.
Advanced Production Considerations
While this setup is great for learning and testing, a production environment often requires more redundancy to avoid any single point of failure.
In a production setup, you would typically have multiple HAProxy instances to distribute the load and multiple application servers to handle requests. This ensures that even if one HAProxy or application server goes down, the system remains functional.
Conclusion
In conclusion, Galera Cluster is an excellent choice for applications requiring high availability, data consistency, and scalability within a single region. However, its complexity and sensitivity to network latency warrant careful consideration before deployment.
Consider the specific needs and constraints of your application to determine if Galera Cluster is the right fit.
This guide provides a foundation for building resilient database infrastructures.
