How to Create MariaDB Replication on AlmaLinux

This guide provides a step-by-step walkthrough to configure MariaDB replication on AlmaLinux.

By İbrahim Korucuoğlu ( @siberoloji) | Sunday, December 22, 2024

Categories:

MariaDB, an open-source relational database management system, provides powerful replication features that allow you to maintain copies of your databases on separate servers. Replication is crucial for ensuring high availability, load balancing, and disaster recovery in production environments. By using AlmaLinux, a robust and secure RHEL-based Linux distribution, you can set up MariaDB replication for an efficient and resilient database infrastructure.

This guide provides a step-by-step walkthrough to configure MariaDB replication on AlmaLinux, helping you create a Main-Replica setup where changes on the Main database are mirrored on one or more Replica servers.

What is MariaDB Replication?
Prerequisites
Understanding Main-Replica Replication
Installing MariaDB on AlmaLinux
Configuring the Main Server
Configuring the Replica Server
Testing the Replication Setup
Monitoring and Managing Replication
Troubleshooting Common Issues
Conclusion

1. What is MariaDB Replication?

MariaDB replication is a process that enables one database server (the Main) to replicate its data to one or more other servers (the Replicas). Common use cases include:

High Availability: Minimize downtime by using Replicas as failover systems.
Load Balancing: Distribute read operations to Replica servers to reduce the Main server’s load.
Data Backup: Maintain an up-to-date copy of the database for backup or recovery.

2. Prerequisites

Before setting up MariaDB replication on AlmaLinux, ensure the following:

AlmaLinux Installed: At least two servers (Main and Replica) running AlmaLinux.
MariaDB Installed: MariaDB installed on both the Main and Replica servers.
Network Connectivity: Both servers can communicate with each other over the network.
User Privileges: Access to root or sudo privileges on both servers.
Firewall Configured: Allow MariaDB traffic on port 3306.

3. Understanding Main-Replica Replication

Main: Handles all write operations and logs changes in a binary log file.
Replica: Reads the binary log from the Main and applies the changes to its own database.

Replication can be asynchronous (default) or semi-synchronous, depending on the configuration.

4. Installing MariaDB on AlmaLinux

Install MariaDB on both the Main and Replica servers:

Add the MariaDB Repository:

sudo dnf install -y https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
sudo mariadb_repo_setup --mariadb-server-version=10.11

Install MariaDB:

sudo dnf install -y mariadb-server mariadb

Enable and Start MariaDB:

sudo systemctl enable mariadb
sudo systemctl start mariadb

Secure MariaDB: Run the security script:
```
sudo mysql_secure_installation
```
Follow the prompts to set a root password, remove anonymous users, and disallow remote root login.

5. Configuring the Main Server

Step 1: Enable Binary Logging

Open the MariaDB configuration file:
```
sudo nano /etc/my.cnf
```
Add the following lines under the [mysqld] section:
```
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW
```
- server-id=1: Assigns a unique ID to the Main server.
- log-bin: Enables binary logging for replication.
- binlog-format=ROW: Recommended format for replication.
Save and exit the file, then restart MariaDB:
```
sudo systemctl restart mariadb
```

Step 2: Create a Replication User

Create a replication user with appropriate privileges:

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

Check the binary log position:

SHOW MASTER STATUS;

Output example:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      120 |              |                  |
+------------------+----------+--------------+------------------+

Note the File and Position values; they will be used in the Replica configuration.

6. Configuring the Replica Server

Step 1: Set Up Replica Configuration

Open the MariaDB configuration file:
```
sudo nano /etc/my.cnf
```
Add the following lines under the [mysqld] section:
```
[mysqld]
server-id=2
relay-log=mysql-relay-bin
```
- server-id=2: Assigns a unique ID to the Replica server.
- relay-log: Stores the relay logs for replication.
Save and exit the file, then restart MariaDB:
```
sudo systemctl restart mariadb
```

Step 2: Connect the Replica to the Main

Configure the replication parameters:

CHANGE MASTER TO
    MASTER_HOST='master_server_ip',
    MASTER_USER='replicator',
    MASTER_PASSWORD='secure_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=120;

Replace:

master_server_ip with the IP of the main server.
MASTER_LOG_FILE and MASTER_LOG_POS with the values from the Main.

Start the replication process:
```
START SLAVE;
```
Verify the replication status:
```
SHOW SLAVE STATUS\G;
```
Look for Slave_IO_Running: Yes and Slave_SQL_Running: Yes.

7. Testing the Replication Setup

Create a Test Database on the Main:
```
CREATE DATABASE replication_test;
```
Verify on the Replica: Check if the database appears on the Replica:
```
SHOW DATABASES;
```
The replication_test database should be present.

8. Monitoring and Managing Replication

Monitor Replication Status

On the Replica server, check the replication status:
```
SHOW SLAVE STATUS\G;
```

Pause or Resume Replication

Pause replication:
```
STOP SLAVE;
```
Resume replication:
```
START SLAVE;
```

Resynchronize a Replica

Rebuild the Replica by copying the Main’s data using mysqldump or mariabackup and reconfigure replication.

9. Troubleshooting Common Issues

Replica Not Connecting to Main

Check Firewall Rules: Ensure the Main allows MariaDB traffic on port 3306:

sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

Replication Lag

Monitor the Seconds_Behind_Master value in the Replica status and optimize the Main’s workload if needed.

Binary Log Not Enabled

Verify the log-bin parameter is set in the Main’s configuration file.

10. Conclusion

MariaDB replication on AlmaLinux is a powerful way to enhance database performance, scalability, and reliability. By setting up a Main-Replica replication, you can distribute database operations efficiently, ensure high availability, and prepare for disaster recovery scenarios. Regular monitoring and maintenance of the replication setup will keep your database infrastructure robust and resilient.

With this guide, you’re equipped to implement MariaDB replication on AlmaLinux, enabling a reliable and scalable database system for your organization.

Last modified 09.01.2025: msfconsoel translations ongoing... (f7e18da)