High availability mariadb galera cluster

This is an expert-level guide for deploying the VoIPmonitor database in a high-availability, multi-master cluster using MariaDB Galera. This architecture provides exceptional write scalability and automatic failover but introduces significant complexity compared to a standard single-node setup.

Overview: Why Choose a Galera Cluster?

A standard single-node database is a single point of failure. While robust, any hardware or software issue on that server can bring down your entire monitoring platform. A MariaDB Galera Cluster solves this by creating a synchronous, multi-master database.

• How it works: You have three or more database servers (nodes). When you write data to any one node, that data is instantly and automatically replicated to all other nodes in the cluster. If one node fails, the others continue to operate seamlessly, and a load balancer can redirect traffic to the healthy nodes.

Advantages

• High Availability (HA): Automatic failover. If a database node goes offline, the cluster continues to function without data loss.
• Write Scalability: You can write to any node in the cluster, distributing the write load.
• Data Consistency: Synchronous replication ensures that all nodes always have the exact same data.

Disadvantages & Warnings

• Complexity: This is a significantly more complex setup to configure and maintain than a single database.
• Network Sensitivity: Performance is highly dependent on low-latency, high-bandwidth network connections between all nodes. It is not suitable for geographically dispersed nodes over a WAN.
• "Split-Brain" Risk: A cluster requires a "quorum" to function. You must always run an odd number of nodes (3, 5, etc.) to prevent a situation where a network partition could lead to data inconsistency.
• Performance Overhead: Synchronous replication adds a small amount of latency to every write transaction.

Recommendation: This architecture is intended for large-scale, critical deployments where database uptime is paramount. For most users, a simpler replication setup or regular backups are sufficient.

Architecture for this Guide

This guide assumes the following setup:

• Three MariaDB Nodes: `192.168.0.1`, `192.168.0.2`, `192.168.0.3`
• One HAProxy Node: `192.168.0.100`. This will be the single endpoint (load balancer) that VoIPmonitor connects to.

Step 1: Configure All MariaDB Galera Nodes

The following configuration must be applied to each of the three database nodes. It is typically split into two files.

A. Main MySQL/MariaDB Configuration

These are high-performance settings for InnoDB placed in a file like `/etc/mysql/mariadb.conf.d/50-server.cnf`.

# /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
# --- General & Performance Settings ---
default-authentication-plugin = mysql_native_password
performance_schema = 0
skip-external-locking
skip-log-bin
skip-name-resolve
open_files_limit = 200000
max_connections = 100000
max_allowed_packet = 256M

# --- InnoDB High-Performance Settings ---
# See the Scaling guide for details on these parameters
innodb_buffer_pool_size = 120G
innodb_log_file_size = 5G
innodb_log_buffer_size = 2G
innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_timeout = 1800
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_doublewrite = 0
innodb_read_io_threads = 20
innodb_write_io_threads = 20
innodb_purge_threads = 20
innodb_io_capacity = 1000000
innodb_io_capacity_max = 10000000

# --- InnoDB Compression Settings ---
innodb_compression_default = ON
innodb_compression_algorithm = lz4

B. Galera-Specific Configuration

These settings activate the cluster functionality. Place them in a separate file, like `/etc/mysql/mariadb.conf.d/60-galera.cnf`.

# /etc/mysql/mariadb.conf.d/60-galera.cnf

[galera]
# --- Mandatory settings for Galera ---
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/lib64/galera-4/libgalera_smm.so

# --- Galera Cluster Configuration ---
# This name must be identical on all nodes
wsrep_cluster_name="VoIPmonitor_Cluster"
# List all nodes in the cluster. When starting, a node will try to connect to these to sync.
wsrep_cluster_address="gcomm://192.168.0.1,192.168.0.2,192.168.0.3"

# --- Galera Synchronization Method ---
# 'rsync' is a common and reliable choice for state transfers.
wsrep_sst_method=rsync

# --- INDIVIDUAL NODE Configuration ---
# THESE TWO LINES MUST BE UNIQUE ON EACH NODE!
wsrep_node_address="192.168.0.1"  # Use this node's own IP address
wsrep_node_name="Node-01"         # Give each node a unique name

# --- Galera Performance Tuning ---
# gcache stores the replication writeset. 10G is a good starting point.
wsrep_provider_options="gcache.size=10G;gcache.recover=yes;gcs.fc_limit=500000;"

Important: Remember to change `wsrep_node_address` and `wsrep_node_name` on each of your other nodes (e.g., to `192.168.0.2` and `Node-02`).

Step 2: Bootstrap and Start the Cluster

The first node must be started in a special "bootstrap" mode to initialize the cluster.

On your first node (e.g., Node-01)

galera_new_cluster

Once the first node is running, start the MariaDB service normally on the other two nodes. They will automatically connect to the first node and synchronize their data.

On Node-02 and Node-03

systemctl start mariadb

Step 3: Configure the HAProxy Load Balancer

HAProxy will act as a "virtual IP" for your database cluster, directing traffic to healthy nodes.

Install HAProxy

apt-get install haproxy

Edit the configuration file `/etc/haproxy/haproxy.cfg`

# /etc/haproxy/haproxy.cfg
global
    daemon
    maxconn 10000

defaults
    timeout connect 10s
    timeout client  60m
    timeout server  60m
    mode tcp
    option tcplog

# Frontend: This defines the public-facing IP and port that your applications will connect to.
frontend galera_frontend
    bind 192.168.0.100:3307
    default_backend galera_backend

# Backend: This defines the pool of real database servers.
backend galera_backend
    balance roundrobin  # Distribute connections evenly
    option tcp-check    # Use a simple TCP check to verify if a node is alive
    
    # Define your MariaDB nodes.
    # Only the primary node accepts traffic initially. The others are backups.
    server Node1 192.168.0.1:3306 check
    server Node2 192.168.0.2:3306 check backup
    server Node3 192.168.0.3:3306 check backup

Start and enable the HAProxy service

systemctl restart haproxy
systemctl enable haproxy

Step 4: Configure VoIPmonitor to Use the Cluster

Finally, edit the `voipmonitor.conf` file on all your sensors and your GUI server. Point them to the single HAProxy endpoint, not to the individual database nodes.

# /etc/voipmonitor.conf

mysqlhost     = 192.168.0.100  # The HAProxy IP address
mysqlport     = 3307           # The HAProxy port
mysqlusername = your_user
mysqlpassword = your_password
mysqldb       = voipmonitor

With this setup, if your primary database node (`Node1`) fails, HAProxy will automatically detect this and start forwarding traffic to one of the backup nodes, providing seamless database failover for your VoIPmonitor installation.

AI Summary for RAG

Summary: This guide provides an expert-level walkthrough for setting up a high-availability VoIPmonitor database using a three-node MariaDB Galera Cluster with an HAProxy load balancer. It begins by explaining the pros (high availability, write scalability) and cons (complexity, network sensitivity) of this architecture compared to a standard single-node database. The tutorial is structured into four main steps. Step 1 details the required MariaDB configuration, splitting it into general high-performance InnoDB settings and Galera-specific (`wsrep_*`) settings that must be applied to each node. Step 2 explains how to bootstrap the cluster using `galera_new_cluster` on the first node and then starting the service on the others. Step 3 provides a complete `haproxy.cfg` example, configuring a frontend to listen on a virtual IP and a backend pool of the three database nodes with health checks. Finally, Step 4 shows how to configure the VoIPmonitor sensor (`voipmonitor.conf`) to connect to the single HAProxy endpoint, enabling automatic database failover. Keywords: high availability, ha, galera, mariadb cluster, mysql cluster, wsrep, haproxy, load balancer, failover, database cluster, multi-master, synchronous replication, `wsrep_cluster_address`, `gcomm`, `wsrep_sst_method`, `balance roundrobin` Key Questions:

• How can I make my VoIPmonitor database highly available?
• How to set up a MariaDB Galera Cluster for VoIPmonitor?
• What are the `wsrep` options in the MariaDB configuration?
• How do I configure HAProxy as a load balancer for a Galera Cluster?
• What are the advantages and disadvantages of using a Galera Cluster?
• How do I bootstrap a new Galera Cluster?
• Should I point VoIPmonitor to HAProxy or directly to a database node?
• What is a multi-master database setup?