Gonzalo Rivero
I am Gonzalo, I live in Salta, a city located in the NW of Argentina. I play the guitar and a little harmonica. I also like to bike.

Master-Master replication with MariaDB

A couple of days ago I wrote a tutorial on how to do replication with MariaDB (or MySQL). This is a one-way replication: from master to slave; this means that the slave server receives the transactions form the master. But my client needs bidireccional replication, because we don’t know which server will receive the transaction and the same data has to be on both. Here I will show you how to configure master-master replication with MariaDB to achieve this.

My Setup

Just in case you missed the previous article those are the features of the virtual machines where I’m running this replication.

  • OS: Debian testing ‘Bookworm’
  • MariaDB version: 10.5.12
  • Master1: 192.168.122.13
  • Master2: 192.168.122.223

Masters configuration

Both masters gets the same configuration, edit /etc/mysql/mariadb.conf.d/50-server.cnf and replace the following values:

bind-address  = <master.X.ip.address>
server-id        = <X>
report_host    = masterX
log_bin          = /var/log/mysql/mysql-bin.log
relay_log       = /var/lib/mysql/relay-bin
relay_log_index = /var/lib/mysql/relay-bin.index

log-slave-updates
auto_increment_increment=2
auto_increment_offset=X

Where X is 1 for the first master, and 2 for the second.
The autoincrement values means that in id-kind fields one of the masters will get always even numbers and the other will get always odd numbers. This is to prevent that the exact same id number is assigned in both masters at the same time.

master-master replication with mariadb. The config files
server.cnf from master1

Now restart mariadb service and log into mysql to create an user (esclavo means slave in spanish) with replication privileges:

CREATE USER 'esclavo1'@'ip.mysql.master.2' IDENTIFIED BY 'SlavePass' ;
GRANT REPLICATION SLAVE ON *.* TO 'esclavo1'@'ip.mysql.master.2';
FLUSH PRIVILEGES;

Repeat on both masters changing the information accordingly (i.e. on master2 create a user esclavo2@master1.ip). But first, get a sql dump of your first master and load it in the second one:

master1: mysqldump -u root --all-databases -p --master-data > data.sql
master2: mysql -uroot < data.sql

Now get some information about this master that we need to enable the slave mode on the other host.

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003|      358 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
STOP SLAVE;

Slaves configuration

Now is time to configure slave mode on both mariadb/mysql hosts:

CHANGE MASTER TO MASTER_HOST='ip.mysql.master.X', MASTER_USER='esclavoX',MASTER_PASSWORD='SlavePass MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS = 358';
START SLAVE;

The master_host vaule is the ip of the other master, master_user value is the user you’ve created on the other master, and finally you get master_log_file and master_log_post values from the show master status command.

Checking everything

To see if I was able to enable master-master replication with MariaDB, I’ve runned some very simple test:

I’ve created a database on one master, then a simple table (just 1 field) and inserted a couple of records. Then I’ve checked with a select that data appears on both hosts. Finally I’ve added an id auto incremental column on one host and inserted some more data to see how it appears:

working in master2
working on master1.

Just to add more confusion to those screenshots: I’ve added the auto_increment configuration (increment and offset) to my.cnf during this test and not from the beginning, because I didn’t knew about that until I’ve started the tests.

Anyway, this is how I enabled master-master replication with MariaDB, that is also slave-slave replication. Or master-slave and slave-master replication, you name it.

I think the next logical step is to research and use some Galera Cluster.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest articles

x