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 |
+---------+-------------------+
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.
