Configuring a Virtual IP (VIP) ensures seamless Failover and High Availability for MariaDB Galera Cluster on Ubuntu 24.04 LTS

[email protected] ? MariaDB DBA Tutorial? ? ? ? ?https://www.chirags.in

*****************************************************************************************

? ?Configuring a Virtual IP (VIP) with MariaDB Galera Cluster on Ubuntu 24.04 LTS

*****************************************************************************************

YouTube Video in English:

YouTube Video in Hindi:

Step-by-Step Guide for Configuring a Virtual IP (VIP) with MariaDB Galera Cluster on Ubuntu 24.04 LTS

Using a Virtual IP (VIP) ensures seamless failover and high availability in a MariaDB Galera Cluster. Here’s the detailed process:

Step 1 : Prerequisites

A working MariaDB Galera Cluster (at least 3 nodes).

VIP (e.g., 192.168.224.200) to be shared among the nodes.

Server Details:

+---------+-------------------+
| Server  |   Server IP       |
+---------+-------------------+
| Server1 | 192.168.224.129   |
| Server2 | 192.168.224.130   |
| Server3 | 192.168.224.131   |
| VIP     | 192.168.224.200   |
+---------+-------------------+        

keepalived installed on all nodes.

Install keepalived:

sudo apt update

sudo apt install keepalived -y        

Step 2 : Configure Keepalived on Each Node

On Node 1:

Edit the keepalived configuration:

sudo nano /etc/keepalived/keepalived.conf        

Add the following configuration:

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1234
    }
    virtual_ipaddress {
        192.168.224.200/24
    }
}        

Replace ens33 with your network interface.

Adjust the VIP and authentication password as needed.

On Node 2:

Use the same configuration but change state to BACKUP and priority to a lower value:

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1234
    }
    virtual_ipaddress {
        192.168.224.200/24
    }
}        

On Node 3:

Use the same configuration but change state to BACKUP and priority to a lower value:

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1234
    }
    virtual_ipaddress {
        192.168.224.200/24
    }
}        

Step 3 : Restart Keepalived Service

Restart the keepalived service on all nodes:

sudo systemctl restart keepalived
sudo systemctl enable keepalived        

Check the status:

sudo systemctl status keepalived        

Step 4 : Verify VIP Assignment

On Node 1, check if the VIP is assigned:

ip addr show ens33        

Look for:

inet 192.168.224.200/24        

If Node 1 goes down, the VIP will automatically move to the next available node.

Step 5 : Grant Remote Access Privileges

Log in to MariaDB locally (on the node itself):

sudo mysql -u root -p        

Grant Remote Access for Each Node IP and VIP

Run the following SQL commands:

-- Grant access to the VIP

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.224.200' IDENTIFIED BY 'admin@123' WITH GRANT OPTION;        

-- Grant access to Node1

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.224.129' IDENTIFIED BY 'admin@123' WITH GRANT OPTION;        

-- Grant access to Node2

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.224.130' IDENTIFIED BY 'admin@123' WITH GRANT OPTION;        

-- Grant access to Node3

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.224.131' IDENTIFIED BY 'admin@123' WITH GRANT OPTION;        

-- Grant access to the subnet (optional)

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.224.%' IDENTIFIED BY 'admin@123' WITH GRANT OPTION;        

-- Apply changes

FLUSH PRIVILEGES;        

Explanation:

192.168.224.200 explicitly allows the VIP.

Step 6 : Verify User Permissions

Check the list of users and hosts:

MariaDB [(none)]> SELECT User, Host FROM mysql.user;
+-------------+-----------------+
| User        | Host            |
+-------------+-----------------+
| root        | 192.168.224.%   |
| root        | 192.168.224.129 |
| root        | 192.168.224.130 |
| root        | 192.168.224.131 |
| root        | 192.168.224.200 |
| mariadb.sys | localhost       |
| mysql       | localhost       |
| root        | localhost       |
+-------------+-----------------+
8 rows in set (0.002 sec)        

Step 7 : Configure MariaDB to Use VIP

Update your applications and clients to connect to the VIP:

mysql -h 192.168.224.200 -u root -p        

Step 6 : Testing Failover

Stop keepalived on Node 1:

sudo systemctl stop keepalived        

Check if Node 2 or Node 3 takes over the VIP:

ip addr show ens33        

Step 7: Create a New Test Database

On any node:

Run the following SQL commands:

-- Create a Test Database

CREATE DATABASE galera_vip;        

-- Use the Test Database

USE galera_vip;        

-- Create a Test Table

CREATE TABLE employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);        

-- Insert Sample Data

INSERT INTO employee (name) VALUES ('Chirag'), ('Purab'), ('Sanju');        

Step 8: Start Node1 and Verify Data on All Nodes

On Node1:

sudo systemctl start mariadb
sudo systemctl start keepalived        

Connect directly to Node1:

mysql -u root -p        

Check the data:

MariaDB [(none)]> USE galera_vip;

MariaDB [galera_vip]> SELECT * FROM employee;
+----+--------+---------------------+
| id | name   | created_at          |
+----+--------+---------------------+
|  2 | Chirag | 2025-01-01 16:41:09 |
|  4 | Purab  | 2025-01-01 16:41:09 |
|  6 | Sanju  | 2025-01-01 16:41:09 |
+----+--------+---------------------+
3 rows in set (0.001 sec)        

Your VIP is now successfully configured and failover-tested with MariaDB Galera Cluster on Ubuntu 24.04 LTS! Let me know if you encounter any issues.

For any doubts and query, please write on YouTube video comments section.

Note : Flow the Process shown in video.

??Please, Subscribe and like for more videos:

https://www.youtube.com/@chiragstutorial

??Don't forget to, ??Follow, ??Like, ??Share ??&, Comment

Thanks & Regards,

Chitt Ranjan Mahto "Chirag"

_________________________________________________________________________________________

Note: All scripts used in this demo will be available in our website.

Link will be available in description.