MariaDB Master/Master Replication: A Beginner's Guide

Are you looking to make your database faster and always available? MariaDB master/master replication can help! This guide will show you how to set up and manage master/master replication in MariaDB.

What is MariaDB Master/Master Replication?

MariaDB master/master replication is like making copies of your database on different computers. These copies can all accept changes and share them with each other. This keeps your data safe and makes your system faster.

Benefits of Master/Master Replication:

1. Always available (high availability)
2. Spreads the work (load balancing)
3. Faster performance
4. Quick recovery if something goes wrong

Before You Start

Make sure you have:

• Two MariaDB servers running
• Access to both servers as the main user (root)
• Basic knowledge of SQL and Linux commands

How to Set Up MariaDB Master/Master Replication

Step 1: Set Up MariaDB Servers

On both servers, open the MariaDB settings file:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add these lines:

server-id = 1  # Use 2 for the second server
log-bin = /var/log/mysql/mysql-bin.log
binlog_format = row
log-slave-updates
auto_increment_increment = 2
auto_increment_offset = 1  # Use 2 for the second server

Save and restart MariaDB:

sudo systemctl restart mariadb

Step 2: Create a Special User

On both servers, make a user for replication:

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

Step 3: Get Important Information

On Server 1, run:

SHOW MASTER STATUS;

Write down the File and Position values.

Step 4: Connect the Servers

On Server 2, run:

CHANGE MASTER TO
MASTER_HOST='server1_ip',
MASTER_USER='repl_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=123;

START SLAVE;

Replace server1_ip, mysql-bin.000001, and 123 with your actual values.

Do the same on Server 1, using Server 2's information.

Step 5: Check if It's Working

On both servers, check the status:

SHOW SLAVE STATUS\G

Look for Slave_IO_Running and Slave_SQL_Running. Both should say "Yes".

Tips for Using MariaDB Master/Master Replication

1. Make Backups: Always keep extra copies of your data.
2. Watch Your Setup: Use tools like MariaDB MaxScale to keep an eye on things.
3. Handle Conflicts: Decide what to do if both servers change the same data.
4. Use More Servers: Having 3 or more servers can be better.
5. Check Your Network: Make sure your servers can talk to each other quickly.
6. Check Your Data: Regularly make sure all servers have the same information.

Common Problems and How to Fix Them

Problem 1: One Server Falls Behind

If one server is slow:

1. Check if servers can talk to each other
2. Make the slow server faster
3. Give the server more power (CPU, memory, or disk space)

Problem 2: Data is Different on Servers

If data doesn't match:

1. Use special tools to find differences
2. Fix the differences
3. If it's really bad, start over with a backup

Problem 3: Numbers Getting Mixed Up

To prevent number mix-ups:

1. Make sure servers use different number ranges
2. Use different types of IDs for your data

Watching Your Setup

To keep everything healthy:

1. Set up alerts for problems
2. Watch how hard your servers are working
3. Regularly check if replication is working
4. Use special tools to watch everything at once

Making Your Setup Bigger

As you grow, think about:

1. Adding more servers to handle more reads
2. Using tools to split reads and writes
3. Using special MariaDB setups for automatic failover
4. Splitting your data across many servers

Advanced Topics

Circular Replication

You can set up three or more servers in a circle for extra safety.

Replication Filters

You can choose which parts of your data to copy:

replicate-do-db = database1
replicate-do-table = database2.table1
replicate-ignore-db = database3

Semi-Synchronous Replication

For better data safety, try semi-synchronous replication:

plugin-load-add = semisync_master.so
plugin-load-add = semisync_slave.so
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1
rpl_semi_sync_master_timeout = 10000

Wrap-Up

MariaDB master/master replication helps keep your database fast and always available. By following this guide, you've learned how to set it up. Remember to watch your system, handle problems, and grow when needed.

With practice, you'll become really good at managing MariaDB replication. Keep learning and trying new things to find the best setup for you.

Happy database managing!