MariaDB 10.6 Master-Slave Replication and Load Balancing on Ubuntu 20.04/22.04: Complete Guide

Prerequisites and MariaDB Installation

Before we begin, make sure you have 2-4 Ubuntu 20.04 or 22.04 servers ready. Here's our sample setup:

• Master Database: 192.168.1.10
• Slave Database 1: 192.168.1.11
• Slave Database 2: 192.168.1.12 (optional)
• Load Balancer (HAProxy): 192.168.1.13

Install MariaDB 10.6 on all database servers:

sudo apt update
sudo apt install software-properties-common
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mirrors.aliyun.com/mariadb/repo/10.6/ubuntu focal main'
sudo apt update
sudo apt install mariadb-server

After installation, run the security script: sudo mysql_secure_installation. This will set root password, remove anonymous users, and disable remote root login for security.

Configuring MariaDB Master-Slave Replication

1. Master Server Configuration

Edit the master server configuration file /etc/mysql/mariadb.conf.d/50-server.cnf and add/modify the following under [mysqld] section:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog-format = ROW
log-slave-updates = True
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
binlog-rows-query-log-events = 1
expire_logs_days = 10
max_binlog_size = 100M
# Specify database to replicate (optional)
# binlog_do_db = your_database_name

Restart MariaDB service: sudo systemctl restart mariadb

Login to MySQL and create replication user:

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

Check master status and note the binary log position:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Record the File and Position values for slave configuration. Unlock tables: UNLOCK TABLES;

2. Slave Server Configuration

Edit slave server configuration file /etc/mysql/mariadb.conf.d/50-server.cnf. Ensure each slave has unique server-id:

[mysqld]
server-id = 2  # Set to 3 for second slave, etc.
relay_log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
read_only = 1
log-slave-updates = True
master-info-repository = TABLE
relay-log-info-repository = TABLE

Restart slave MariaDB service: sudo systemctl restart mariadb

If master database has existing data, sync it to slaves using mysqldump:

# On master server
mysqldump -u root -p --master-data=2 --single-transaction --databases your_database_name > db_dump.sql

# Copy db_dump.sql to slave and import
mysql -u root -p < db_dump.sql

Configure replication connection on slave:

CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='replicator',
MASTER_PASSWORD='your_strong_password',
MASTER_LOG_FILE='recorded_filename',
MASTER_LOG_POS=recorded_position;

Start replication: START SLAVE;

Check replication status: SHOW SLAVE STATUS\G

Look for Slave_IO_Running: Yes and Slave_SQL_Running: Yes to confirm replication is working.

3. Verify Replication

Create a test database or table on master and verify it appears on slaves. This is the moment of truth - if data syncs correctly, you've nailed the configuration!

HAProxy Load Balancer Setup

Now that we have replication working, we need to distribute database requests across our servers. HAProxy will act as the traffic cop.

Install HAProxy on load balancer server:

sudo apt update
sudo apt install haproxy

Edit HAProxy configuration file /etc/haproxy/haproxy.cfg:

global
    log /dev/log local0
    log /dev/log local1 notice
    chroot /var/lib/haproxy
    stats timeout 30s
    user haproxy
    group haproxy
    daemon

defaults
    log global
    mode tcp
    option tcplog
    option dontlognull
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms

# Monitoring interface
listen stats
    bind *:9999
    mode http
    stats enable
    stats uri /haproxy-status
    stats realm Haproxy\ Statistics
    stats auth admin:your_monitoring_password

# Database load balancing
frontend mysql_frontend
    bind *:3306
    mode tcp
    option clitcpka
    default_backend mysql_backend

backend mysql_backend
    mode tcp
    balance roundrobin
    option tcp-check
    server mysql-master 192.168.1.10:3306 check port 3306 inter 2000 rise 2 fall 3
    server mysql-slave1 192.168.1.11:3306 check port 3306 inter 2000 rise 2 fall 3
    server mysql-slave2 192.168.1.12:3306 check port 3306 inter 2000 rise 2 fall 3

Start HAProxy service:

sudo systemctl enable haproxy
sudo systemctl start haproxy

Your application can now connect to HAProxy server (192.168.1.13) on port 3306, and requests will be distributed to backend database servers.

Troubleshooting Common Issues

Here are some common problems and solutions:

1. Replication Breaks

If SHOW SLAVE STATUS shows errors, try skipping specific errors (use carefully):

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  # Skip one transaction
START SLAVE;

2. Network Connectivity

Ensure port 3306 is accessible between all servers:

telnet 192.168.1.10 3306

3. Time Synchronization

Keep all servers time-synced with NTP:

sudo apt install chrony
sudo systemctl enable chrony
sudo systemctl start chrony

4. Firewall Configuration

Ubuntu uses ufw firewall by default. Open required ports:

sudo ufw allow 3306
sudo ufw allow 9999  # HAProxy monitoring port

Performance Optimization Tips

After setup, consider these optimizations:

• Read-Write Splitting: Configure HAProxy to send writes to master and reads to slaves
• Connection Pooling: Use database connection pools in your application
• Monitoring & Alerting: Set up monitoring to detect issues early
• Regular Backups: Even with replication, maintain regular backups

Conclusion

You've now successfully built a MariaDB master-slave replication cluster with load balancing on Ubuntu. This architecture significantly improves database availability and performance while providing a solid foundation for future scaling. Remember, there's no one-size-fits-all solution - tailor this setup to your specific needs.

If you run into issues, feel free to reach out. Regular monitoring and maintenance will keep your database cluster running smoothly!