DEV Community

Cover image for Configuring a Virtual IP (VIP) ensures seamless failover and high availability for MariaDB Galera Cluster on Ubuntu 24.04 LTS
Chitt Ranjan Mahto (Chirag)
Chitt Ranjan Mahto (Chirag)

Posted on

1

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

inchirags@gmail.com 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   |
+---------+-------------------+
Enter fullscreen mode Exit fullscreen mode

keepalived installed on all nodes.

Install keepalived:

sudo apt update

sudo apt install keepalived -y

Enter fullscreen mode Exit fullscreen mode

Step 2 : Configure Keepalived on Each Node

On Node 1:

Edit the keepalived configuration:

sudo nano /etc/keepalived/keepalived.conf
Enter fullscreen mode Exit fullscreen mode

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
    }
}
Enter fullscreen mode Exit fullscreen mode

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
    }
}
Enter fullscreen mode Exit fullscreen mode

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
    }
}
Enter fullscreen mode Exit fullscreen mode

Step 3 : Restart Keepalived Service

Restart the keepalived service on all nodes:

sudo systemctl restart keepalived
sudo systemctl enable keepalived
Enter fullscreen mode Exit fullscreen mode

Check the status:

sudo systemctl status keepalived
Enter fullscreen mode Exit fullscreen mode

Step 4 : Verify VIP Assignment

On Node 1, check if the VIP is assigned:

ip addr show ens33
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Step 6 : Testing Failover

Stop keepalived on Node 1:

sudo systemctl stop keepalived
Enter fullscreen mode Exit fullscreen mode

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

ip addr show ens33

Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

Step 8: Start Node1 and Verify Data on All Nodes

On Node1:

sudo systemctl start mariadb
sudo systemctl start keepalived
Enter fullscreen mode Exit fullscreen mode

Connect directly to Node1:

mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

Image of AssemblyAI tool

Transforming Interviews into Publishable Stories with AssemblyAI

Insightview is a modern web application that streamlines the interview workflow for journalists. By leveraging AssemblyAI's LeMUR and Universal-2 technology, it transforms raw interview recordings into structured, actionable content, dramatically reducing the time from recording to publication.

Key Features:
🎥 Audio/video file upload with real-time preview
🗣️ Advanced transcription with speaker identification
⭐ Automatic highlight extraction of key moments
✍️ AI-powered article draft generation
📤 Export interview's subtitles in VTT format

Read full post

Top comments (0)

Image of AssemblyAI

Automatic Speech Recognition with AssemblyAI

Experience near-human accuracy, low-latency performance, and advanced Speech AI capabilities with AssemblyAI's Speech-to-Text API. Sign up today and get $50 in API credit. No credit card required.

Try the API

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay