How to configure MariaDB for high availability with MHA

This guide sets up a MariaDB high availability cluster using the Master-Master or Master-Slave replication model managed by MHA (Master High Availability). The steps target MariaDB 10.11 on Ubuntu 24.04 and require the MHA manager to be installed on a separate control node.

Prerequisites

• Three Ubuntu 24.04 LTS servers with at least 4GB RAM.
• MariaDB 10.11 installed and configured on two nodes (Master and Slave).
• A third Ubuntu 24.04 server acting as the MHA manager node.
• SSH access configured between all nodes without password prompts.
• Root or sudo privileges on all servers.
• Network connectivity between all nodes on port 3306 and 22.

Step 1: Configure MariaDB replication on the Master

Edit the MariaDB configuration file to enable binary logging, which is required for replication. Ensure the server is set to act as the primary node for the cluster.

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add the following parameters to the configuration file to enable binary logging and set the server ID. Replace the IP address with the actual master server IP.

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 3
max_binlog_size = 100M
relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
relay_log_info_file = relay-bin.info
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

Restart the MariaDB service to apply the changes. Verify that the binary log is active and the server ID is set correctly.

sudo systemctl restart mariadb

sudo mysql -e "SHOW VARIABLES LIKE 'server_id';"

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| server_id       | 1     |
+-----------------+-------+

Step 2: Configure MariaDB replication on the Slave

Configure the second MariaDB server to act as a slave by setting a unique server ID and enabling the necessary replication variables. This node will read the binary logs from the master and apply them to maintain data consistency.

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Update the configuration with the slave-specific settings. Replace the master IP address with the actual master server IP.

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 3
max_binlog_size = 100M
relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
relay_log_info_file = relay-bin.info
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
read_only = 1

Restart the MariaDB service on the slave node. Connect to the master server using the root user to generate a replication user and configure the slave to connect.

sudo mysql -u root -p

Execute the following SQL commands on the master server to create a replication user and start the slave.

CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

On the slave server, run the CHANGE MASTER TO command to point to the master. Replace the IP and password with the actual values.

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_password',
  MASTER_AUTO_POSITION=1;
START SLAVE;

Verify that the slave status shows "Slave_IO_Running: Yes" and "Slave_SQL_Running: Yes".

sudo mysql -u root -p -e "SHOW SLAVE STATUS\G"

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Retrieved_Gtid_Set: 1-4

Step 3: Install MHA on the Manager Node

Install the MHA software on the third Ubuntu server. This node will monitor the cluster and perform automatic failover if the master becomes unavailable. Use the official MHA repository to install the latest stable version.

sudo apt update

sudo apt install mha4mysql-node mha4mysql-manager

Create the MHA configuration file to define the cluster topology. This file tells MHA which servers are masters and which are slaves, along with their roles and connection details.

sudo nano /etc/mha/node.conf

Add the following configuration block. Replace the IP addresses and passwords with the actual values from your cluster.

[default]
external_pid_file = /var/run/mha_node.pid

[server 1]
hostname = 192.168.1.10
port = 3306
user = root
password = root_password
manager_port = 22
ssh_user = root
canonical_name = 192.168.1.10
master = 1

[server 2]
hostname = 192.168.1.11
port = 3306
user = root
password = root_password
manager_port = 22
ssh_user = root
canonical_name = 192.168.1.11
master = 0

Step 4: Configure MHA Manager

Create the manager configuration file to define the master and slave nodes for the cluster. This configuration allows MHA to manage the failover process and ensure data consistency during a switch.

sudo nano /etc/mha/app1.cnf

Enter the following configuration details. Replace the IP addresses and passwords with the actual values from your cluster.

[server default]
manager_port = 22
ssh_user = root
user = root
password = root_password
port = 3306
replication = 1

[server 1]
hostname = 192.168.1.10
canonical_name = 192.168.1.10
master = 1

[server 2]
hostname = 192.168.1.11
canonical_name = 192.168.1.11
master = 0

[server 3]
hostname = 192.168.1.12
canonical_name = 192.168.1.12
master = 0

Start the MHA manager service to begin monitoring the cluster. MHA will now check the status of the master and slave nodes and prepare for automatic failover.

sudo systemctl start mha4mysql-manager

sudo systemctl enable mha4mysql-manager

Verify the installation

Check the status of the MHA manager service to ensure it is running correctly. Verify that the manager can connect to the master and slave nodes and that the cluster is healthy.

sudo systemctl status mha4mysql-manager

● mha4mysql-manager.service - MHA Manager
   Loaded: loaded (/lib/systemd/system/mha4mysql-manager.service; enabled)
   Active: active (running) since Mon 2023-10-23 10:00:00 UTC; 5min ago

Run the MHA check script to verify the cluster status. This command displays the current state of the master and slave nodes, including their replication status and any potential issues.

sudo mha_check_node.pl --conf=/etc/mha/app1.cnf

Expected output should show "OK" for both master and slave nodes.

OK: Master node (192.168.1.10) is running
OK: Slave node (192.168.1.11) is running
OK: Replication is working correctly

Troubleshooting

If MHA fails to detect the master or slave nodes, check the SSH connectivity and firewall rules. Ensure that port 22 (SSH) and port 3306 (MariaDB) are open between all nodes. Verify that the SSH keys are correctly configured for passwordless login.

ssh -i ~/.ssh/id_rsa root@192.168.1.10

If replication is broken, check the slave status for errors. Look for "Last_IO_Error" or "Last_SQL_Error" in the slave status output. If the error persists, reset the slave connection and restart replication.

STOP SLAVE;
RESET SLAVE ALL;
START SLAVE;

Review the MHA logs for detailed error messages. The logs are located in /var/log/mha/ and contain information about the failover process and any issues encountered.

sudo tail -f /var/log/mha/manager.log

Ensure that the MariaDB binary logs are not expiring before MHA can process them. Adjust the expire_logs_days setting if necessary to maintain a sufficient log window for failover.