Configuring a Virtual IP (VIP) ensures seamless failover and high availability for MariaDB Galera Cluster on Ubuntu 24.04 LTS

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

sudo apt install keepalived -y

sudo nano /etc/keepalived/keepalived.conf

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
    }
}

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
    }
}

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
    }
}

sudo systemctl restart keepalived
sudo systemctl enable keepalived

sudo systemctl status keepalived

ip addr show ens33

sudo mysql -u root -p

-- 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;

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)

mysql -h 192.168.224.200 -u root -p

sudo systemctl stop keepalived

ip addr show ens33

-- 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');

sudo systemctl start mariadb
sudo systemctl start keepalived

mysql -u root -p

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)

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