# Deploy MariaDB LXC in HA with Galera / HAProxy on Proxmox with OPNSense
[TOC]
## Galera Cluster - what is it and why is it good for your home set up
- A MariaDB Galera Cluster is a type of database setup that keeps your data synchronized across multiple servers (nodes) in real-time. Its main purpose is to provide **high availability** and prevent data loss.
- Think of it like a team of scribes writing the same book simultaneously. When one scribe adds a sentence, they all instantly add the exact same sentence to their copy, ensuring every book is always identical.
A Galera Cluster for MariaDB is a **synchronous multi-master** database solution - to break it down:
- **Multi-Master:** Unlike traditional setups where you have one main database and several read-only copies, a Galera cluster allows you to read *and* write to **any node** in the cluster. This distributes the workload and eliminates a single bottleneck.
- **Synchronous Replication:** When you run a transaction (like `INSERT`, `UPDATE`, or `DELETE`) on one node, it doesn't complete until it has been successfully replicated and confirmed by **all other nodes** in the cluster. This guarantees that all nodes have the exact same data at the exact same time, preventing data inconsistencies.
### Advantages of having a Galera cluster
- **Redundancy:** Each Proxmox node holds a full, independent copy of the database. If one entire Proxmox server crashes, goes down for maintenance, or loses power, your database service continues to operate - there is **no single point of failure.**
- **Automatic Failover:** Applications connected to the database can simply redirect their queries to the remaining online nodes. Since all nodes are masters and have identical data, the transition is seamless with no downtime for the application and no data loss.
- **Easy Maintenance:** You can perform maintenance on one Proxmox host (like kernel updates or hardware upgrades) by shutting down its MariaDB VM. The rest of the cluster continues to run without issue. Once the maintenance is done and the node rejoins, Galera automatically synchronizes it with any changes that occurred while it was offline.
### The role of an Arbitrator
- Imagine that you have two proxmox nodes each with two galera nodes and one Proxmox node goes down. That means that 50% of the nodes are down and there is no majority.
- This would result in the Galera cluster failing to operate as it requires a majority. An Arbitrator is a separate device that holds no data but helps determine which part of the cluster is healthy, preventing a split-brain scenario.
- We will use an Arbitrator later in our set up using a simple Raspberry Pi.
### Benefits for Home or Hybrid Setups
- **At Home:** If you have two or more physical machines running Proxmox, you can create an enterprise-grade, fault-tolerant database for critical home services like Home Assistant, Nextcloud, your websites and other development projects. If one server fails, your smart home or personal cloud stays online.
- **Hybrid Mode:** This is where it gets really interesting. You can run one Proxmox node at home and another one on a cloud server (like a cheap VPS or dedicated server) or it could be someone else’s home who has a decent connection (or two). The Galera Cluster can span both locations over a VPN (like WireGuard). This gives you **geographic redundancy**. If your home loses internet or power, the cloud node takes over, ensuring your database is always accessible. This is a cost-effective way to achieve disaster recovery without owning multiple physical locations.
In this guide, we will focus on the home set up but can cover a multi-site set up in the near future.
### Pre-requisites
- You have one or more Proxmox nodes (or another type 1 hypervisor such as VMWare, Harvester, etc.).
- You have one or more OPNSense units (can be virtualized but then it is best deployed with CARP on two or more Proxmox nodes)
- You have downloaded an LXC container of your favorite Linux distro. This guide covers Debian 13 (Trixie). Preferably, give the container a name that indicates it will be used as a template, such as ‘galera-template’. We can then have a cluster ‘A’ with numbers 1-4, for example ‘galera-A1, galera-A2, etc.’.
- Optional: A Raspberry Pi used as the fifth member of a quorum (no data stored).
- Here is the full diagram for our set up:
## Create your first container as a template on Proxmox
- This will save us work: we will update the LXC to the newest version and install services that we will need on it without necessarily configuring them in detail.
- We will then turn the container into a template and create additional nodes from it.
- We will leave the network settings on DHCP and use our firewall (OPNsense in my case) to set up a static lease for each container later on.
- Start the container and log in. You can be fancy and do so from the Proxmox node itself by connecting to its Shell/SSH.
```shell
# Start the LXC if not already running
pct start 120
# Log into its shell
pct enter 120
# Perform a system update & upgrade
apt update && apt upgrade
apt upgrade apt -y
# Install prerequisite packages
sudo apt install -y dirmngr ca-certificates apt-transport-https curl
# Download and install a combo of MariaDB server + client + galera with a backup client + firewall + ssh + cron
apt install mariadb-server mariadb-client mariadb-backup galera-4 rsync nano ssh ufw cron fail2ban ncdu -y
```
- In case you are still using a root account, let’s create another user that we can use instead:
```bash
# Create a user
adduser
# Add the user into the list of admins
usermod -aG sudo jan
# Switch into that user
su jan
```
### Create an SST user on your LXC template
- Relevant documentation: [mariadb-backup SST method](https://mariadb.com/docs/galera-cluster/galera-management/state-snapshot-transfers-ssts-in-galera-cluster/mariadb-backup-sst-method)
- Previously, `rsync` was the method used for syncing between nodes. The challenge with this method is that while the update is being sent by the ‘donor’ node, the receiving ‘joiner’ node is not accepting other traffic.
- With the mariadb-backup method, traffic is still being accepted even while the sync is in progress. This is achieved by using a `socat` utility, which must be installed on both ‘donor’ and the ‘joiner’ nodes.
- There are two types of syncs:
- **IST** is used when a node has only been offline for a short time. It just asks for the missing transactions from the donor's cache (`gcache`).
- **SST** is a full copy of the entire dataset, which is what invokes the `wsrep_sst_method` script (`mariabackup` or `rsync`).
- We will need to create a user in MariaDB with a password on all future nodes in our cluster, so let’s do it on the template.
```sql
# Log into your MariaDB instance
mariadb -u root -p
CREATE USER 'sst_user'@'localhost' IDENTIFIED BY 'YourSSTPassword';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sst_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;
```
### Security Hardening & Logging on Galera template LXC
#### Change the default SSH port
To modify the default SSH port, edit the following file (do not accidentally swap it with ‘ssh_config’, as that one will not lead to the desired change).
```bash
nano /etc/ssh/sshd_config # In case you do not have sudoers (above) installed, use 'su -' and then run this command without sudo
sudo systemctl restart ssh
ss -tulpn | grep 22 # You should see a line that includes [::]:2222
```
- The output should be similar to the one following:
- Then try connecting to the container via SSH again.
#### Configure MariaDB Logging
- Even if we launch the mariadb service (daemon), there will be no logs by default, unless we allow them specifically. And we will need them - not just for troubleshooting, but for fail2ban as well.
```bash
# Create a folder for logs and set up permissions
mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql
chmod 2750 /var/log/mysql
nano /etc/mysql/mariadb.conf.d/50-server.cnf
# Change the bind address from 127.0.0.1
bind-address = 0.0.0.0
# Un-comment these two lines
general_log_file = /var/log/mysql/mysql.log
log_error = /var/log/mysql/error.log
```
- Save and exit and restart the mariadb service:
```shell
# Restart the mariadb service
systemctl restart mariadb
# Simulate an error:
mysql -u someuser
# Check that an error was produced - this is important for fail2ban
tail -n 5 /var/log/mysql/error.log
```
#### Configure Fail2Ban
Set up fail2ban for repeated failed login attempts (just in case).
```bash
# Create your own jail file
nano /etc/fail2ban/jail.local
# Copy paste the following (adjust as you see fit)
[mariadb]
enabled = true
port = 3306
filter = mysqld-auth
logpath = /var/log/mysql/error.log
findtime = 3600
maxretry = 5
bantime = 360
ignoreip = 127.0.0.1/8 ::1
```
- It would be good to verify that it works, as the template will be used for all other instances:
```bash
systemctl reload fail2ban
systemctl restart mysql
# Allow temporary access to your instance using user 'root' - the password
mariadb -u root -p
# Replace the IP below with your testing host ip
GRANT ALL PRIVILEGES ON *.* TO 'randomuser'@'192.168.8.73'
IDENTIFIED BY 'some-long-random-password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;
# Now grab another host that has mariadb-client package installed and run a few of these:
mysql -h -u blablah -plalala
# You should expect an error saying 'Access denied for user ...'
# Now hop on the galera template container and run the following to see the stats:
fail2ban-client status mariadb
```
- In case you hit the limit, the error will change from ‘Access denied for user..’ to ‘Can’t connect to server on…’. That is how you know that machine was banned.
- You can access all banned IP addresses from different monitored services using this command: `fail2ban-client banned`
- To un-ban an IP address, run the following:
```bash
fail2ban-client set mariadb unbanip
```
- Lastly, let’s remove that unlimited access from the testing host:
```bash
mysql -u root -p
DROP USER 'root'@'192.168.8.73';
FLUSH PRIVILEGES;
EXIT;
```
#### Configure ufw (firewall)
- Let's configure allowed ports on the firewall for SSH, rsync, MySQL and deny HTTP/HTTPS
```bash
ufw allow proto tcp from 192.168.8.0/24 to any port 2222 # Allow comms for SSH from your subnet
ufw allow proto tcp from 192.168.8.0/24 to any port 3306 # Allow HAProxy to reach out for health checks and forward traffic
ufw allow proto tcp from 192.168.8.0/24 to any port 4567 # Handles galera replication traffic (TCP)
ufw allow proto udp from 192.168.8.0/24 to any port 4567 # Handles galera replication traffic (UDP)
ufw allow proto tcp from 192.168.8.0/24 to any port 4568 # Used for Incremental State Transfer (IST)
ufw allow proto tcp from 192.168.8.0/24 to any port 4444 # Rsyncd to transfer data for SST (mysqldump)
ufw deny http # We are not running a web server here so no need
ufw deny https # Same as above
ufw deny ftp # No FTP server needed here
ufw reload # Reload firewall rules
ufw enable # Enforce the firewall
```
- You may also wish to add rules for your monitoring service, such as Uptime Kuma / Zabbix. While it is out of scope of this article, here are some examples:
```bash
ufw allow proto tcp from 192.168.8.0/24 to any port 10050 # Port for Zabbix no.1
ufw allow proto tcp from 192.168.8.0/24 to any port 10051 # Port for Zabbix no.2
ufw allow proto tcp from 192.168.8.0/24 to any port 3001 # Port for UptimeKuma
```
- In case you make a mistake and need to revert it, once ufw is enabled, you can run the following:
```bash
# Display all rules by number
ufw status numbered
# See the output of all the rules and choose which one you want to delete. Example:
# ufw delete 1
```
- The end result can be something like this:
- Just in case you are deploying the MariaDB instance as a VM instead of a container, then remember to install the qemu agent (not applicable for LXCs).
#### MariaDB Secure Installation script
- Let’s run the pre-packaged ‘[`mariadb-secure-installation`](https://mariadb.com/docs/server/clients-and-utilities/deployment-tools/mariadb-secure-installation)’ script from the shell of any of the MariaDB container instances:
- You will be asked to provide the root password
- ‘Switch to unix socket authentication’: Choose ‘n’
- ‘Change the root password?’: change it if it is not already secure / different from the others. Preferably use a long password saved in your trusted password vault.
- ‘Remove anonymous users’: Choose ‘y’
- ‘Disallow root login remotely’: Choose ‘y’
- ‘Remove the test database and access to it’: Choose ‘y’
- ‘Reload privilege tables’: Choose ‘y’
- For more explanation and screenshots of each step, [**check out Linuxteck's guide**](https://www.linuxteck.com/how-to-set-up-mariadb-on-rocky-linux-8/).
- This should be all for security hardening.
```bash
# Leave the container
exit
# Shut the container down (if you are connecting from Proxmox - if not, switch it off from the GUI).
pct stop
```
### Turn the MariaDB container to a template
So now we have a container prepared and need to turn it into a template and then create the actual MariaDB galera nodes from the template.
- In Proxmox GUI, right click on the CT template and select ‘Convert to template’.
- Then right click on the newly created template and choose ‘Clone’.
- Choose an adequate hostname, such as ‘galera-A1’, which indicates it will be the first node in cluster A (since you may have more clusters in the future).
- Storage is typically local-lvm unless you have shared storage between Proxmox nodes set up.
- Mode: Full Clone
In the future, we can keep this template updated by creating an instance from it, updating it and then turning it back into a template.
#### What is the difference between a Full vs Linked Clone in Proxmox?
Just a little stop in case you have not had to deal with this one before, and why I would strongly recommend ‘Full Clone’ for a DB container.
- A Full Clone is a full independent copy of the template’s disk image:
- **Disk operation:** It's a byte-for-byte copy. If your template's disk is 8GB, a full clone will immediately occupy 8GB of space on your storage.
- **Independence:** Once created, it has no connection to the original template. You can delete the template, and the full clone will continue to run without issues.
- **Creation Speed:** This is the slowest method, as Proxmox must read every block from the template and write it to the new location.
- A linked clone is a **lightweight, dependent copy** that shares the template's disk image:
- **Disk operation**: While light because it works as snapshots (starting with zero difference), it uses a "copy-on-write" mechanism. There is therefore a performance penalty on the first write to a block, as that data block must first be copied from the template to the clone's own storage area before it can be modified. This is the main reason for not using linked clones for database servers (unless you do not need many write operations at all).
- **Independence:** A linked clone requires the original template to exist. One cannot delete the template if it has dependent (linked) clones.
- **Creation Speed:** Nearly instantaneous. Since no data is copied initially, a linked clone can be created in just a couple of seconds.
## Deploy 4x LXC containers on 2x Proxmox nodes
Let’s say that you created your first container. Good! Let’s assign it a static IP.
- Since we are using OPNSense, let’s copy the MAC address in Proxmox and find it in OPNSense to assign it something meaningful for our set up.
- In OPNSense GUI, head to ‘Services’ > ‘ISC DHCPv4’ > ‘[LAN]’ interface and scroll down to ‘DHCP Static Mappings for this interface.’. Click on the + sign there to add a new MAC address.
- Paste the MAC address and fill in our details like hostname / description, as you prefer. Click on the ‘Save’ button at the bottom of the page and then on the ‘Apply’ button to kick the change into effect.
- Proceed with creating other containers in Proxmox and assigning them static IPs in the same way.
- If you have more Proxmox nodes, move an even number to another one - so for 4 containers, you can have two in proxmox1 and two on proxmox2. Simply right click on the container and click on the ‘Migrate’ button.
- Then switch the node on (or restart it if it was on already) and SSH into each using an SSH key or password using the newly assigned IP address, as per your set up.
- On node galera-A1:
```shell
systemctl stop mariadb
nano /etc/mysql/mariadb.conf.d/60-galera.cnf
[mysqld]
# MySQL-related settings
bind-address = 0.0.0.0
binlog_format = ROW
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
# This setting provides better write performance at a small risk of data
# loss on OS crash (not just DB crash). In a multi-node cluster, this is an acceptable
# and common setting, as data exists on other nodes.
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
# A shorter lock wait timeout fails faster in a cluster environment,
# which is generally preferred over long waits.
innodb_lock_wait_timeout = 60
# The InnoDB buffer pool is the most critical performance setting.
# Set this to ~70% of the server's available RAM if it's a dedicated database server.
innodb_buffer_pool_size = 800M
# Skip trying to resolve names since we are using IP addresses
skip-name-resolve
# Allow for more attempts to prevent HAProxy from being blocked
max_connect_errors = 1000
# Logs not only errors but warnings, too (older more compatible method from log_error_verbosity)):
log_warnings = 1
wait_timeout = 1800
interactive_timeout = 1800
max_allowed_packet = 1G
[galera]
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Note: The gcache stores writesets for nodes that briefly disconnect.
# A larger gcache allows for a longer disconnect time before a full SST is required.
wsrep_provider_options="gcache.size=512M;gcs.fc_limit=128;gcs.fc_factor=0.8"
# Galera Cluster Configuration (ADJUST - same on each node)
wsrep_cluster_name="clusterA"
# Info about each node (ADJUST - same on each node + add a witness if you have one)
wsrep_cluster_address="gcomm://192.168.8.71,192.168.8.72,192.168.8.73,192.168.8.74"
# The newer non-blocking method is mariadb, the older one is rsync (ports need to be opened!)
wsrep_sst_method = mariabackup
# SST user for syncinging, same for each node. UPDATE!
wsrep_sst_auth = sst_user:YourSSTPassword
# Galera Node Configuration (ADJUST FOR EACH NODE)
wsrep_node_address = "192.168.8.71" # <-- CHANGE THIS ON EACH NODE
wsrep_node_name = "galera-a1" # <-- CHANGE THIS ON EACH NODE
wsrep_sst_receive_address = "192.168.8.71" # <-- CHANGE THIS ON EACH NODE
```
- Once done, do not yet try starting the mysql service, because it will fail due to not seeing any node in the cluster yet (not even itself). Instead, initiate the new cluster and check that you can see one unit (**do this only once!**):
```bash
# Initiate the galera cluster from the first node:
galera_new_cluster
# Check that the output shows '1'
mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
# Now start the MariaDB service!
systemctl start mariadb
```
- The result of the mysql query should look like this:
- Now let’s ensure that there is an SST user set up (on each of your nodes, no need to have that on the witness node):
```sql
# This ensures the user can connect from the subnet used for the Galera nodes:
CREATE USER IF NOT EXISTS 'sst_user'@'192.168.%' IDENTIFIED BY 'YourPassword';
# These are the standard "Donor" permissions for mariabackup
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst_user'@'192.168.%';
# MariaDB 11.x also likes BINLOG MONITOR
GRANT BINLOG MONITOR ON *.* TO 'sst_user'@'192.168.%';
FLUSH PRIVILEGES;
```
- Let’s head to the ‘galera-A2’ node and set it up in a similar way - just ensure to change the last few rows.
```bash
systemctl stop mariadb
nano /etc/mysql/mariadb.conf.d/60-galera.cnf
# Copy the same config file as above with the difference in the last two rows:
# Galera Node Configuration (ADJUST FOR EACH NODE)
wsrep_node_address = "192.168.8.72" # <-- CHANGE THIS ON EACH NODE
wsrep_node_name = "galera-a2" # <-- CHANGE THIS ON EACH NODE
wsrep_sst_receive_address = "192.168.8.72" # <-- CHANGE THIS ON EACH NODE
# Exit with save. Do not start a new cluster by running 'galera_new_cluster'.
# Start the MariaDB service:
systemctl start mariadb
# Verify that the service started and the node joined the cluster
systemctl status mariadb
# Check how many nodes are in the cluster
mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
```
- Here is how the output may look like:
- Rinse and repeat with ‘galera-A3’ and ‘galera-A4’, each time:
- Stop the MariaDB service
- Edit the `60-galera.conf` file
- Copy paste the full config that we used on the ‘galera-A1’ node.
- Edit the last two rows to capture the node’s IP and hostname.
- Start the MariaDB service again (or reboot the container).
- Configure the `sst_user` to ensure snapshot-based backups work during short outages.
- Once all four nodes have been added to the cluster, you can run `mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size';"` on any of them and should get the value of 4.
- **Note:** This user is not used for the health check itself, because HAProxy does not support health checks that require a password.
- We can also create a user that would be used in the future to run validation tests to confirm that the connection works to different containers from each OPNSense firewall.
```sql
mariadb -u root -p
-- Create the user for the first and second OPNsense nodes (or if you have more)
CREATE USER 'haproxy_check'@'opnsense_node1_ip_here' IDENTIFIED BY 'your_password_here';
CREATE USER 'haproxy_check'@'opnsense_node2_ip_here' IDENTIFIED BY 'your_password_here';
-- Grant the ability to connect to the server and run commands for health checks
GRANT USAGE ON *.* TO 'haproxy_check'@'opnsense_node1_ip_here';
GRANT REPLICATION CLIENT ON *.* TO 'haproxy_check'@'opnsense_node1_ip';
GRANT USAGE ON *.* TO 'haproxy_check'@'opnsense_node2_ip_here';
GRANT REPLICATION CLIENT ON *.* TO 'haproxy_check'@'opnsense_node2_ip';
-- Apply the new permissions
FLUSH PRIVILEGES;
```
- To ensure that you have got it right, run the following:
```sql
SELECT User, Host FROM mysql.user;
```
- The output should look similar to:
- In case you made an error and need to remove something, then run the following (edited as required):
```sql
mariadb -u root -p
DROP USER 'haproxy_check'@'opnsense_node1_ip_here';
DROP USER 'haproxy_check'@'opnsense_node2_ip_here';
FLUSH PRIVILEGES;
EXIT;
```
## Configure HAProxy for your Galera Cluster
- In this scenario, we are setting up HAProxy on OPNSense, where it is available as a plugin. If you have a different reverse proxy or have HAProxy deployed in a different way, you can try following the steps before, although it may differ.
- In case you do not yet have HAProxy installed/enabled on your OPNSense, go ahead to ‘System’ → ‘Firmware’ → ‘Plugins’ and download it.
- Log into OPNSense and go to ‘Services’ → ‘HAProxy’ → ‘Settings’. Then again to ‘Settings’ → ‘Service’ to ensure that the service is running.
- Create a health check for the MySQL service that MariaDB needs:
- In the top menu, go to ‘Rules & Checks’ → ‘Health Monitors’
- Click on the + sign to add a new one (this button is a bit less visible).
- Name/Description: as you like, such as mariadb_health_check
- Check type: MySQL
- Check internal 3s (or as you prefer)
- Port: 3306
- Now let’s make HAProxy aware of each container’s existence. While still in ‘Services’ → ‘HAProxy’ → ‘Settings’ in the left menu, go to ‘Real servers’ → ‘Real servers’ drop-down option. Click on the + sign to add a new one for each instance.
- Name: name of each galera instance, such as galera_a1
- Type: static
- IP: the real IP of each instance
- Port: 3306 (optional, since our health check has it configured already)
- Mode: active
- In the end, you should have all four listed:
- The next step is to create a backend pool. Go to ‘Virtual Services’ → ‘Backends’ and click on the + sign again.
- Name/Description: As per your preference, such as mariadb_galeraA_pool
- Mode: TCP
- Algorithm: Round robin
- Servers: list all your MariaDB containers that you want in there
- Tick box for Health Checking
- Tick ‘Log status Changes’ if you would like to.
- At this point, it would be good after saving this change to click on the ‘Test syntax’ button to ensure that there are no errors and then you can click on the ‘Apply’ button.
- So now HAProxy knows what to check and where and how often. Yet, for our web apps, we would want a single point of contact. We will need a virtual IP address.
- In the left menu of your OPNSense, go to ‘Interfaces’ → ‘Virtual IPs’ → ‘Settings’ and click on the + sign to add a new one.
- Mode: CARP (as we have OPNSense in a HA cluster already and want them to take over in case one fails)
- Interface: LAN
- Network address: Choose an address on your LAN that is not on your DHCP range (mine starts from .101 onwards). The subnet should be specific to that IP only, i.e. /32.
- Set up a custom password - if you have more OPNSense units, it needs to match on all instances.
- VHID: needs to be unique from others and match with other OPNSense units in the cluster (if you have one).
- advbase: same as above
- advskew: The main unit should have a lower value, the secondary (backup) unit(s) higher value(s).
- Check out my previous article on [**how CARP and HA works on OPNSense**](https://bachelor-tech.com/opnsense-high-availability/configure-carp-on-lan-wan-interfaces/) for more details.
- You might object why to add another CARP interface when you already have your OPNSense in a cluster (and if not, why not - just [**follow this guide**](https://bachelor-tech.com/detailed-guides/opnsense-in-ha-with-carp-with-dual-wans/)). This is the cleanest way of separating our network's gateway IP from our database service IP. If we ever need to add firewall or NAT rules in the future, we can simply refer to this virtual IP.
- This is how it looks on my end after configuring the virtual IP for the Galera DB service:
- Finally, we can head back to HAProxy via ‘Services’ → ‘HAProxy’ → ‘Settings’ and find the ‘Virtual Services’ in the top menu and select ‘Public Services’ from the drop-down. Click on the + sign.
- Name/Description: As per your preference, something like ‘galera_db_cluster_listener’ may do.
- Listen address: your virtual IP
- Type: TCP (not HTTP!)
- Default back-end pool: our previously configured pool, e.g. ‘mariadb_galeraA_pool’.
- After saving it, testing the syntax and applying, it would be good to ensure that this new config gets replicated to the other OPNSense instance in HA that we have configured in our previous guide.
- Head to ‘System’ → ‘High Availability’ → ‘Status’.
- Click on the button to the right of ‘Synchronize config to backup’.
- Then it would be good to log into your backup instance(s) to confirm that all the HAProxy and virtual IP config is in there. Esp. in HAProxy, it is often necessary to apply the new configuration:
- Now we are talking! Let’s run some tests to confirm it is working as expected.
## Testing your Galera MariaDB cluster & Troubleshooting
- **Note:** the `haproxy_check` user created earlier is scoped to only log in from the OPNSense nodes' own IPs (`opnsense_node1_ip_here` / `opnsense_node2_ip_here`), that's deliberate, it's a health-check account, not a general-purpose one. Run the test below from one of those OPNSense nodes, not from an arbitrary machine, or it will fail to authenticate.
- Connect to any Linux machine that has MariaDB client installed, run the following (using the user we created earlier for this type of tests):
```sql
mariadb -h 192.168.8.70 -u haproxy_check -p -e "SELECT @@wsrep_node_name;"
```
- The output will reveal which instance is being queried. If you run it repeatedly, you will get the same result due to stick-table persistence.
- If you try connecting from another instance, you will likely reach another one, instead:
- This confirms that the round-robin works for each new client connecting.
---
Hardware Set up for the Pi
- Grab a Raspberry Pi (any version will do if it's not already busy with other tasks).
- The Pi should be connected ideally via the same switch as the Proxmox nodes on the same network for low latency. I would not recommend WiFi connection here.
- The OS is up to your choice, a basic headless Raspberry Pi OS Lite would do. In this guide, I assume it is a Debian-based distro.
- Ideally, have it connected to UPS. Either have one that covers several devices (such as your Proxmox nodes + router(s) + the Pi) or one that is dedicated to the Pi, that is a power bank with UPS capabilities. Ping me for tips if you are struggling to find some 🙂
What to install
- Connect to the Pi via SSH and run the following:
```bash
# Update / Upgrade the OS to its newest version
sudo apt update && sudo apt upgrade -y
# Install MariaDB client only (no need for a server)
sudo apt install galera-arbitrator-4 -y
```
- Let’s configure the Arbitrator:
```bash
sudo nano /etc/default/garb
# A comma-separated list of other node addresses (IPs) in your Galera cluster.
# At least one of these must be contactable at startup.
GALERA_NODES=":4567,:4567,:4567,:4567"
# The Galera cluster name such as ClusterA
GALERA_GROUP="my_galera_cluster"
# Optional: log file for garbd. Leave it commented out or
# you will run into permission issues.
LOG_FILE="/var/log/garbd.log"
```
- Save and exit and then enable the garbd service:
```bash
# Set the owner of the log file:
chown _galera /var/log/garbd.log
# Set up logrotate
sudo nano /etc/logrotate.d/garbd
/var/log/garbd.log {
weekly
rotate 4
compress
delaycompress
missingok
notifempty
create 644 _galera mysql
}
# Test it
sudo logrotate -d /etc/logrotate.conf
# Start the garbd service
sudo systemctl start garbd
# Verify the service is up and running
sudo systemctl status garbd
# Set it up to auto-start on boot
sudo systemctl enable garbd
# Monitor the log
# tail -f /var/log/garbd.log
```
- Log into any of your actual MariaDB nodes and check the cluster size.
```bash
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';"
```
- Now our cluster is **resilient to a split-brain scenario** in case one of the two Proxmox nodes are down.
- In my case, I [**use the Pi also as a Proxmox backup server**](https://bachelor-tech.com/detailed-guides/turn-your-raspberry-pi-into-a-proxmox-backup-server/) with a large 1 TB micro SD A2 card + as a quorum member for the Proxmox nodes + a quorum member for the Galera cluster. Quite neat!
## Set up Monitoring - UpTime Kuma
In one of the first steps in this tutorial, we allowed traffic for a network monitoring system. Yet up to this point, we have not set it up. It is important to be aware when one of your cluster nodes (or your Arbitrator) goes down. In this case, I have included a few steps for UpTime Kuma. If you prefer Zabbix or another monitoring system, let me know in the comments and I may include it as well.
- Connect to one of your nodes in the cluster and add a user with sufficient privileges (here we assume that your UpTime Kuma runs on the 192.168.8.x subnet - change it to your own!):
```sql
mysql -u root -p
CREATE USER 'uptimekuma'@'192.168.8.0/24' IDENTIFIED BY 'YourUptimeKumaPassword';
GRANT PROCESS ON *.* TO 'uptimekuma'@'192.168.8.0/24';
FLUSH PRIVILEGES;
EXIT;
```
### Why are we scripting it from Uptime Kuma? Isn’t there an easier way?
- Technically, we could just create a TCP monitor for each node and monitor that the MariaDB service is up this way. Yet if a node got out of sync, such a check would not really reveal it.
- You might also argue why not to use the ‘MySQL / MariaDB’ service monitor. The issue is that depending on your level of MariaDB, the driver layer in UpTimeKuma may have compatibility issues: in my testing, I have come across several combinations when it did not work. In addition, it will only work if you are using TLS, as at the time of writing this article, there is no way to tick a box to trust self-signed certs.
- So the script below covers two things: firstly, it runs a TCP scan just like the built-in TCP scan would. And only if it passes, it will use the ‘mariadb-client’ package to connect to each node to check that it reports as in sync. This way, you will just have one monitor per node (and thus only one notification instead of multiple) and this notification will contain the required detail on what is not working.
- Connect to your UpTimeKuma instance via SSH and run the following commands:
```sql
sudo apt update
sudo apt install -y mariadb-client
nano /usr/local/bin/check_galera.sh
#!/bin/bash
# --- ARGUMENTS ---
# The Galera node IP to check is passed as the first argument
DB_HOST=$1
# The Uptime Kuma push code is passed as the second argument
KUMA_CODE=$2
# --- CONFIGURATION ---
# The base URL for your Uptime Kuma instance
UPTIME_KUMA_BASE_URL="http://192.168.8.60:3001"
DB_USER="uptimekuma"
DB_PASS="YourUptimeKumaPassword"
# ---------------------
if [ -z "$DB_HOST" ] || [ -z "$KUMA_CODE" ]; then
echo "Usage: $0 "
exit 1
fi
# Construct the full push URL dynamically
UPTIME_KUMA_URL="${UPTIME_KUMA_BASE_URL}/api/push/${KUMA_CODE}"
# --- STEP 1: Check if the TCP port is open ---
if ! nc -z -w 3 "$DB_HOST" 3306 > /dev/null 2>&1; then
# If nc fails, the port is down. Report and exit.
curl -fsS --retry 3 "${UPTIME_KUMA_URL}?status=down&msg=Port_3306_Down&ping=" > /dev/null
exit 0 # Exit cleanly since we successfully reported the status
fi
# --- STEP 2: If the port is open, proceed to check the Galera status ---
export MYSQL_PWD="$DB_PASS"
if mysql --skip-ssl -h "$DB_HOST" -u "$DB_USER" -e "SHOW STATUS LIKE 'wsrep_local_state_comment';" | grep -q "Synced"; then
# If grep finds "Synced", the node is healthy. Push an UP status.
curl -fsS --retry 3 "${UPTIME_KUMA_URL}?status=up&msg=Synced&ping=" > /dev/null
else
# If grep does not find "Synced", the node is not healthy. Push a DOWN status.
curl -fsS --retry 3 "${UPTIME_KUMA_URL}?status=down&msg=Not_Synced&ping=" > /dev/null
fi
unset MYSQL_PWD
# Make the script executable
sudo chmod +x /usr/local/bin/check_galera.sh
```
- Then create a separate "Push" monitor in Uptime Kuma for each node to get a unique URL for each cron line.
- And lastly, edit your crontab and re-use the same script to run for each node, passing its IP address and the unique push code.
```sql
# Edit crontab
sudo crontab -e
# Check Galera nodes every minute (replace your IP addresses with yours or hostnames
# + add the respective Uptime Kuma passive push code from the generated URL.
* * * * * /usr/local/bin/check_galera.sh 192.168.8.71 uptime_kuma_push_code1
* * * * * /usr/local/bin/check_galera.sh 192.168.8.72 uptime_kuma_push_code2
* * * * * /usr/local/bin/check_galera.sh 192.168.8.73 uptime_kuma_push_code3
* * * * * /usr/local/bin/check_galera.sh 192.168.8.74 uptime_kuma_push_code4
```
- The result in UpTimeKuma is as follows:
### **Test that the alerts work**
- Connect to one of your nodes and simulate a failure, such as by running sudo **`systemctl stop mariadb`**.
- When I disabled mariadb service on node2, here is the error specifying what went wrong:
- When I switched it back on (by running **`systemctl start mariadb`**), I got another notification:
- So now that we have a working cluster, ideally with an Arbitrator that is separate from the other physical servers, and we have monitoring in place, it is time to start adding some data to our cluster and entrust it with data!
---
## Entrust your Galera Cluster with data
- Let’s export data from your existing database (assuming you have a single MariaDB instance and want to migrate a database or more into this HA galera cluster).
- In this case, we will consider migrating a WordPress instance, as those are quite common still.
- Log into the web server via SSH and find the wp-config.php file.
```bash
# The exact path may differ
cd /opt/www/html/bachelor-tech
nano wp-config.php
```
- Copy paste information about the DB:
- DB user
- DB password
- DB name
- Head to the new galera cluster and SSH into ANY of the nodes.
```sql
mysql -u root -p
-- Creates a new database for WordPress. You can skip this if the import file creates it.
CREATE DATABASE wordpress_db;
-- This is the main command. It grants a user full rights to the wordpress_db
-- ONLY when connecting from your app's specific IP address. Replace the IP with your web server's.
GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wordpress_user'@'X.Y.I.Z' IDENTIFIED BY 'strong_db_password';
# GRANT ALL PRIVILEGES ON iriskayan_com_db.* TO 'iriskayan_com_user'@'192.168.%' IDENTIFIED BY 'YourWordPressDbPassword';
-- Applies the new permissions immediately.
FLUSH PRIVILEGES;
```
- Connect via SSH to your original MariaDB database and run an MySQL dump command to export its data.
```sql
mysqldump -u root -p wordpress_db > /tmp/wordpress_backup.sql
```
- Then from that machine, assuming you have rsync installed, you can rsync that file to one of the nodes in the cluster. For a quick transfer, you can temporarily disable ufw (or you can temporarily allowlist it).
- We do not need to switch off the firewall on the Galera cluster, since we have previously allowed it for the same subnet (unless you set it up differently). If you do get blocked and need to quickly copy it over, then on the Galera node, you can run `sudo systemctl stop ufw` and then start it back on afterwards.
```
# On the original MariaDB single instance, change the following to suit your SSH port, file names and IP:
rsync -rvz -e 'ssh -p 2222' --progress /tmp/your_exported_db.sql your_usename@A.B.C.D:/tmp
```
Data copying process from a single node to a cluster node
```sql
rsync -rvz -e 'ssh -p 2222' --progress /tmp/yourfile.sql your_user@A.B.C.D:/tmp
```
- Let’s import it into your new cluster - connect to the node in your cluster where you copied the data over and import it.
```
mysql -u root -p your_galera_db < /path/to/db.sql
```
- **Advanced cases only:** for DBs that are larger than 50 GB in size and if your cluster does not contain much data otherwise, you could consider running `mysql -u root -p -e "SET wsrep_on=OFF; SOURCE /path/to/your/db.sql;" db_dump.sql`. The additional command turns off Galera’s replication for each INSERT command, as Galera must replicate and get approval (certify) for each of these transactions across all nodes in the cluster. So essentially, we say ‘just execute all of the following commands locally without replicating them one-by-one’ and the replication will start only after it is all done. However, then we would need to perform a full State Snapshot Transfer (SST), such as by logging into each node, switching mariadb off, removing the content of `/var/lib/mysql/` and then switching it back on. In most cases, this approach is not needed.
- Let's verify that the data is available on other nodes. Connect to another node and run this command:
```sql
# On another Galera node:
mariadb -u root -p
-- OPTION 1 - list the size of all the DBs in your instance:
SELECT
table_schema 'DB Name',
SUM(data_length + index_length) 'Size in Bytes',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB'
FROM information_schema.tables
GROUP BY table_schema;
-- OPTION 2 - Examine the size of a particular DB
SELECT
SUM(data_length + index_length) 'Size in Bytes',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB'
FROM information_schema.tables
WHERE table_schema = 'your_db'
# WHERE table_schema = 'bachelor_tech_com_db'
GROUP BY table_schema;
```
### Troubleshooting Import
- If they differ in size - slight variations are ok. However, if we are talking about several or more MBs of difference, then be on alert. Re-importing will not help.
**Option 1** - InnoDB tables are being used
- Likely, you have some `MyISAM` or `MEMORY` tables while Galera transfers only `InnoDB` tables. Let’s verify that by comparing the fully imported DB versus another one that the DB was supposed to replicate to but did not fully:
```bash
# On the original node where you imported the DB into:
mysql -u root -p
USE your_db;
SELECT
TABLE_NAME,
ENGINE,
TABLE_ROWS AS 'Rows',
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Total MiB'
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'iriskayan_com_db'
AND ENGINE IN ('MyISAM', 'MEMORY');
```
- The output will show the size of each table for engines `MyISAM` and `MEMORY`. See below for a comparison between the original and another node to which it was supposed to replicate:
- As you can see, the tables of this engine type were not replicated. This is by design. Galera's "synchronous" replication (its certification-based replication) relies on the storage engine being **transactional**. This means the engine must support ACID properties, especially the ability to **rollback** a transaction.
- **InnoDB:** Is fully transactional and supports rollback. This makes it suitable for Galera's replication mechanism.
- **MyISAM:** Is **not** transactional. It doesn't support rollbacks. Once a change is made, it's final. If a conflict occurs during certification, Galera has no way to undo the changes already made locally on the MyISAM table, leading to data inconsistency. See [**MariaDB’s official guide**](https://mariadb.com/docs/galera-cluster/readme/mariadb-galera-cluster-guide).
- **MEMORY:** Is also **not** transactional and shares the same limitations as MyISAM regarding replication consistency.
- So how do we remedy the situation? Thankfully, it is relatively simple. We will need to change the engine type in the dump and re-import it back. Let’s start by removing the DB from our cluster:
```sql
# Login and remove the DB from the cluster:
mysql -u root -p
# Remove it and create a fresh new one:
DROP DATABASE your_db;
CREATE DATABASE your_db;
# Grant privileges as before:
GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wordpress_user'@'X.Y.I.Z' IDENTIFIED BY 'strong_db_password';
FLUSH PRIVILEGES;
EXIT;
# Modify the table properties from the MySQL dump:
sed \
-e 's/ENGINE=MyISAM/ENGINE=InnoDB/g' \
-e 's/ENGINE=MEMORY/ENGINE=InnoDB/g' \
/tmp/mydb_backup.sql > /tmp/mydb_fixed.sql
# Re-import it:
mysql -u root -p your_db < /tmp/your_db_fixed.sql
#mysql -u root -p iriskayan_com_db < /tmp/iriskayan_db_fixed.sql
```
**Option 2** - low memory limit in the MariaDB configuration (less likely the case). How to check? On any Galera cluster node:
```bash
mysql -u root -p
SELECT
VARIABLE_NAME,
VARIABLE_VALUE AS 'Value in Bytes',
ROUND(VARIABLE_VALUE / 1024 / 1024, 2) AS 'Value in MB'
FROM information_schema.GLOBAL_VARIABLES
WHERE VARIABLE_NAME IN ('wsrep_max_ws_size', 'max_allowed_packet');
```
- This will reveal the values in megabytes. We have configured the `max_allowed_packet` before, but in case you skipped this step or stuck to your own config - if the value is anything smaller than 256 MB, you could change it in the MariaDB config file:
```bash
nano /etc/mysql/mariadb.conf.d/60-galera.cnf
# Locate or add these rows:
max_allowed_packet = 512M
wsrep_max_ws_size = 512M
```
- Congratulations on your data import! Now you just need to change the configuration in your web application to point to the virtual IP address that your load balancer operates with. That is also where you can observe where the traffic is going.
- Once all done, verify that your cluster is fully operational with these commands:
```sql
SHOW STATUS LIKE 'wsrep_cluster_size';
SHOW STATUS LIKE 'wsrep_local_state_comment';
SHOW STATUS LIKE 'wsrep_connected';
SHOW STATUS LIKE 'wsrep_ready';
```
- What you want to see is:
- `wsrep_cluster_size`: **5+** (whatever number makes sense)
- `wsrep_local_state_comment`: **Synced**
- `wsrep_connected`: **ON**
- `wsrep_ready`: **ON**
- Once you exit mariadb’s SQL environment, you can also run this from your command line to see the segment and weights. For example, in my case, I have a Site 2 with just two MariaDB nodes, so it is set up as ‘segment: 2’ with more weights to balance it out (4 nodes on Site 1, 2 nodes on Site 2):
```sql
mysql -u root -p -e "SHOW VARIABLES LIKE 'wsrep_provider_options';" | tr ';' '\n' | grep -E 'segment|weight'
gmcast.segment = 2
pc.weight = 2
```
## Beyond the set up - recovery & self-healing options
What are the most common scenarios when a quorum is lost and thus the Galera cluster fails?
- **Case 1**: No Arbitrator and half of the nodes go down: Imagine a case where you do not have an Arbitrator (as mentioned in the previous chapter when we use the Raspberry Pi for it) and one of the two Proxmox nodes where we run the Galera containers goes down. Then we would not have a majority of votes and the cluster would stop serving traffic.
- **Case 2**: Power outage - all nodes go down: This can also happen (or more like will happen at some point whether you like it or not). When you start them up, you will notice that the cluster is not working anyway!
In either case, the cluster has to be recovered manually to prevent data loss. So **it is a feature, not a bug**! Please refer to this article if you have been affected by the cluster shutdown (or are just simulating it).
In a nutshell, the idea is to SSH into each node and verify which one is safe to bootstrap (the result should be 1 on one of them), then you can start a new cluster on that node. Once it is back up, you will need to restart MariaDB on each cluster node.
```javascript
cat /var/lib/mysql/grastate.dat | grep safe_to_bootstrap
# Run this on whichever node results in 1:
sudo galera_new_cluster
# Watch the logs for changes
tail -n 50 /var/log/mysql/error.log
# Restart MariaDB on OTHER nodes
sudo systemctl restart mariadb
```
### Self-healing options
- Automation would be the key here. Firstly, we would run an Ansible playbook that connects to each node, verifies which node went down the last and attempts to recover it, restarting each MariaDB instance or even the entire container.
- If that fails, we could use a tool like Terraform to start new MariaDB instances from our container template and then Ansible to trigger a playbook to run updates on each container, connect them into a cluster and to check if it can extract the dumps from a node and if not, to download them from a last known backup and import them.
- We would also need to automatically update items in the back-end pool in our Load Balancer (such as by leveraging OPNSense API for HAProxy).
> Such an approach definitely warrants its own article - let me know in the comments below if you are interested!
This concludes our rather comprehensive guide on how to deploy a Galera Cluster using technologies like Proxmox, OPNSense, HAProxy and LXC containers. Feel free to share your own experience and stack, perhaps this guide could be expanded to account for more options in the set up.
## Self-Healing Automation of your Galera Cluster
You might argue that the process we have been through related to setting up a new Galera node is rather very manual. You are correct! Yet we have options for a faster recovery in case a node goes down. From the point of having a template ready, we can utilize Ansible, create a playbook that would do the following:
- The trigger: Your monitoring tool (such as Zabbix) detects failure of a node and will trigger a webhook of our Ansible playbook.
- Step 1: The playbook’s first step is to leverage the `community.general.proxmox_lxc` module to force-stop that node identified by its IP address or hostname.
- Step 2: The same module can be used to create a new container from template, setting our desired parameters for CPU, RAM and to assign it the same MAC address, which would result in DHCP giving it the same IP address. Boot it up.
- Step 3: As soon as it boots up, connect in via SSH using a key (that we set up previously in the template), we stop the mariadb service and using Ansible’s `template module`, we modify the `60-galera.cnf` file located in `/etc/mysql/mariadb.conf`. An example is below:
```sql
# ...
wsrep_cluster_name="{{ cluster_name }}"
wsrep_cluster_address="gcomm://{{ cluster_ips }}"
wsrep_sst_auth = "{{ sst_username }}:{{ sst_password }}"
wsrep_node_address = "{{ node_ip }}"
wsrep_node_name = "{{ node_name }}"
wsrep_sst_receive_address = "{{ node_ip }}"
```
- Step 4: We start the mariadb service and tail the logs to confirm that a successful SST took place. If errors are found, a notification is shot out requesting further manual intervention (this could be for cases when a quorum is lost or if something in the network is preventing the traffic to get through).
Would you like a step-by-step guide? Leave a comment and some magic may happen 😇