Setting up high availability database cluster using MariaDB Galera, ProxySQL, Keepalived and Debian 13

Introduction

As your applications grow in scale and importance, ensuring continuous availability becomes critical. Database downtime can lead to lost revenue, frustrated users, and damage to your reputation. This guide demonstrates how to build a highly available (HA) MariaDB cluster leveraging Galera replication, ProxySQL for intelligent query routing, and Keepalived for virtual IP management on Debian.

This architecture combines the strengths of each technology to deliver a robust and resilient database environment. MariaDB Galera provides synchronous multi-master replication, ensuring data consistency across multiple nodes. ProxySQL acts as a query router and load balancer, providing intelligent traffic management and offloading the database servers. Keepalived manages a virtual IP address, providing a single point of access to the cluster and automating failover in case of node failures.

This guide assumes an experience in Linux system administration, basic networking concepts (TCP/IP, DNS), and familiarity with database administration. We'll focus on the configuration and integration of these technologies within a Debian environment.

Key benefits of this architecture:

• High Availability: Automated failover ensures minimal downtime.
• Data Consistency: Synchronous replication guarantees data consistency across all nodes.
• Improved Performance: ProxySQL optimizes query routing and reduces load on the database servers.
• Simplified Management: A single virtual IP address simplifies application configuration and access.

What you'll need:

• Three Debian 13 VPS or servers.
• Root or sudo access to all servers.
• Familiarity with the command line interface.
• A basic understanding of TCP/IP networking and DNS.

Prerequisites

Nodes interconnection

Proper hostname resolution is essential for the cluster to function correctly. Each server needs to be able to find the other servers by name. We're using hostnames to simplify management. Node IP addresses must be in the same subnet.

Hostname and IP Address Mapping

• galera0 (master node): 192.168.10.10
• galera1 (slave node): 192.168.10.11
• galera2 (slave node): 192.168.10.12

You can achieve hostname resolution using either /etc/hosts or DNS.

• Using /etc/hosts (Simpler Setup): This is suitable for testing and development environments. On each server, add entries mapping the server's hostname to its static IP address in the /etc/hosts file.
• Using DNS (Recommended for Production): For larger or more complex environments, configure DNS records (A records) that map the server hostnames to their respective IP addresses.

Verification

After configuring hostname resolution, verify that each server can resolve the other servers' hostnames using the ping command. For example, from galera0, run ping galera1. You should receive a response.

Installation of Required Packages

Before you can configure the Galera cluster, you'll need to install the necessary MariaDB packages on each node. This section outlines the required steps for Debian-based systems.

Add required repositories

Add required repositories using the following commands:

apt-get update && apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates

wget -nv -O /usr/share/keyrings/proxysql-3.0.x-keyring.gpg 'https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/repo_pub_key.gpg'

echo "deb [signed-by=/usr/share/keyrings/proxysql-3.0.x-keyring.gpg] https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/bookworm/ ./" | tee /etc/apt/sources.list.d/proxysql.list

System update

First, update your package lists and packages. Run the next command as root on each node:

apt update && apt upgrade -y

Install packages

Install the required packages on each node using this command:

apt install rsync mariadb-server mariadb-client galera-4 proxysql keepalived -y

Secure MariaDB installation

Execute the security script on each node:

sudo mariadb-secure-installation

This script performs two crucial steps: it will prompt you to set a strong root password for the MariaDB server, and it will remove any insecure default configurations. Follow the on-screen prompts to complete this process.

Enable remote access to MariaDB

Execute these commands to enable remote access on each node:

sed -i "s/.*bind-address.*/bind-address = 0.0.0.0/" /etc/mysql/mariadb.conf.d/50-server.cnf

systemctl restart mariadb

Galera

Galera provides synchronous replication of data across all nodes in the cluster. This means that every change made to the database on one node is automatically and simultaneously propagated to all other nodes. Key benefits of Galera in this setup:

• High Availability: If one node fails, the other nodes continue to serve data without interruption. The cluster automatically promotes a surviving node to become the primary.
• Data Consistency: Because of the synchronous replication, data is always consistent across all nodes. This eliminates the risk of stale reads – you're always reading the most up-to-date version of the data.
• Read Scalability: Because data is replicated across multiple nodes, you can distribute read queries across the cluster for increased read performance.

In essence, Galera ensures that your MariaDB database remains highly available, consistent, and scalable – crucial requirements for many applications.

Configuration

Create a configuration file /etc/mysql/conf.d/galera.cnf on each node. The content will be identical, with the exception of each node's name and address. Paste this template:

[mysqld]
# Basic MariaDB settings
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="my_galera_cluster"
wsrep_cluster_address="gcomm://galera0,galera1,galera2"

# This node's specific configuration
wsrep_node_name="<node_name>"
wsrep_node_address="<node_address>"

Change <node_name> and <node_address> on each node. Use galera0 for the first node and so on.

Start the cluster

Start the cluster on the first node using this command:

sudo systemctl stop mariadb && sudo galera_new_cluster

Restart MariaDB on other nodes:

sudo systemctl restart mariadb

Verify the setup

Check cluster size

Connect to MariaDB on any node and check the cluster status. Connect to the database:

sudo mariadb -u root -p

Check cluster nodes status in the MariaDB shell:

SHOW STATUS LIKE 'wsrep_cluster_size';

wsrep_cluster_size should be equal to our nodes count.

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.001 sec)

Test Replication

Create a test database on any node and insert a test message. Execute the following commands from the MariaDB shell:

CREATE DATABASE galtest;

USE galtest;

CREATE TABLE messages (id INT AUTO_INCREMENT PRIMARY KEY, text VARCHAR(255));

INSERT INTO messages (text) VALUES ('Test from galera0!');

Then verify data on other nodes using the following SQL query via the MariaDB shell:

USE galtest;

SELECT * FROM messages;

The test message should appear:

+----+--------------------+
| id | text               |
+----+--------------------+
|  7 | Test from galera0! |
+----+--------------------+
1 row in set (0.001 sec)

ProxySQL

ProxySQL within this cluster setup is more than just a simple intermediary; it dramatically enhances the performance, reliability, and manageability of your database. ProxySQL sits as a crucial layer between your application(s) and your Galera cluster. Its primary purposes are:

• Query Caching: ProxySQL aggressively caches frequently executed queries. Instead of repeatedly querying the Galera cluster for the same data, it serves the cached result, significantly reducing database load and improving response times. This is particularly beneficial for read-heavy applications.
• Connection Pooling: ProxySQL maintains a pool of persistent connections to the Galera cluster. Establishing a database connection is a resource-intensive operation. Connection pooling avoids this overhead by reusing existing connections, further boosting performance.
• Load Balancing & Query Routing: ProxySQL can intelligently route queries to different nodes in your Galera cluster based on factors like server load, query type, or data affinity. This allows you to distribute the workload and maximize the performance of your cluster.
• Query Optimization: ProxySQL can rewrite queries to be more efficient, potentially leveraging Galera's capabilities for optimal execution.
• Failure Detection & Routing: ProxySQL continuously monitors the health of your Galera nodes. If a node fails, ProxySQL automatically reroutes queries to healthy nodes, ensuring uninterrupted service.

In essence, ProxySQL acts as a smart traffic manager and performance optimizer for your Galera cluster, significantly improving its efficiency and resilience.

Precautions

Users setup

Monitor user

The monitor user within ProxySQL is a dedicated database account solely for monitoring tools to securely access internal statistics and metrics. It's configured with minimal permissions – just SELECT access – ensuring the integrity and security of your data while enabling comprehensive performance monitoring.

Execute this query in the MariaDB shell on the master node to add the monitor user:

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';

GRANT SELECT ON *.* TO 'monitor'@'%';

FLUSH PRIVILEGES;

Application user

The application user within ProxySQL is a standard database account used by your applications to connect and execute queries against the underlying Galera cluster. It's through this user that your application directly interacts with the database, retrieving and manipulating data.

Execute this query in the MariaDB shell on the master node to add the application user:

GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY 'test' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Verification

Verify user creation on all nodes using this command:

mariadb -u root -e "SELECT user, host FROM mysql.user;"

Configuration

Create a configuration file /etc/proxysql.cnf on each node and paste the following content:

datadir="/var/lib/proxysql"
admin_variables={
        admin_credentials="admin:admin"
        mysql_ifaces="127.0.0.1:6032"
}
mysql_variables={
        threads=4
        max_connections=2048
        monitor_username="monitor"
        monitor_password="monitor"
}
mysql_servers=(
        { address="galera0" , port=3306 , hostgroup=0 },
        { address="galera1" , port=3306 , hostgroup=0 },
        { address="galera2" , port=3306 , hostgroup=0 })
mysql_users=(
        { username = "test" , password = "test" , default_hostgroup = 0 , active = 1 })
mysql_query_rules=(
        {
                rule_id=2
                active=1
                match_pattern="^SELECT.*"
                destination_hostgroup=0
                apply=1
        })

Enable and start ProxySQL using these commands:

sudo systemctl start proxysql

sudo systemctl enable proxysql

sudo proxysql --reload

Check your ProxySQL setup using this command:

mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "SELECT hostname,status FROM mysql_servers;"

All nodes must be online:

+----------+--------+
| hostname | status |
+----------+--------+
| galera0  | ONLINE |
| galera1  | ONLINE |
| galera2  | ONLINE |
+----------+--------+

Keepalived

Keepalived manages a virtual IP address, providing a single point of access to the cluster and automating failover in case of node failures. It monitors the health of the MariaDB nodes and redirects traffic to a healthy node if a failure occurs, maintaining high availability. The configuration defines Virtual Router ID 51 and uses the interface eth0. On the master node, the state is set to MASTER with a priority of 100. Backup nodes are configured with a state of BACKUP, having priorities of 90 and 80 respectively. The virtual_ipaddress is set to 192.168.10.50 on all nodes. VIP and nodes must be in the same subnet. Hosting must support VIP for VPS. Authentication is enabled with a shared password of 1234. The advert_int setting defines how frequently the master node sends VRRP advertisements. A lower value means more frequent advertisements, potentially speeding up failover detection; a higher value means less frequent advertisements, potentially delaying failover but reducing network overhead.

Configuration

Create a configuration file /etc/keepalived/keepalived.conf on each node and paste the following content:

vrrp_instance VI_1 {
    state <NODE_STATE>
    interface eth0
    virtual_router_id 51
    priority <NODE_PRIORITY>
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1234
    }
    virtual_ipaddress {
        192.168.10.50/24
    }
}

Change <NODE_STATE> and <NODE_PRIORITY> as described above. Enable and start the keepalived service:

sudo systemctl enable keepalived && sudo systemctl start keepalived

Verification

To verify the setup, use the VIP configured via Keepalived and the port configured via ProxySQL. Check cluster size for example:

mariadb -u test -ptest -h 192.168.10.50 -P6033 -D galtest -e "SHOW STATUS LIKE 'wsrep_cluster_size';"

Expected response must match the Galera verification:

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

Now you can restart nodes at random, check cluster size and data availability.

Conclusion

This guide has demonstrated how to build a highly available MariaDB cluster utilizing Galera replication, ProxySQL for query routing, and Keepalived for virtual IP management on Debian. This architecture provides several key benefits, including high availability with automated failover, data consistency through synchronous replication, and improved performance via ProxySQL. By combining these technologies, you can build a robust and resilient database environment capable of handling increased workloads and minimizing downtime.