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.
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 [email protected]). 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:
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.
