Master-master replication (MariaDB on Manjaro)

Sometimes it’s very useful to skip well-known master-slave replication scenario and focus on master-master possibility 🙂

1) Prerequisities

2 linux distributions with functional mysql server installed. In my case double Manjaro 0.8.11 Openbox will be working fine. Manjaro is riding modern wave of replacement MySQL->MariaDB + using systemd (please don’t kill me, sysvinit fanatics).

VirtualBox is set to network with host only =>

M1 =, source database (sakila in this case), primary, The One 🙂

M2 =, new master with no data, secondary master.


2) Setup

2.1 Configuration settings

Main MySQL/MariaDB configuration file is /etc/mysql/my.cnf  (path can differ, indeed). Find a section [mysqld], check if there are old value – if it is so, rewrite them into new values and add new variables. Values for M1:

Just very quick info about tricky part – server_id must be unique within replication environment; auto-increment-increment + auto-increment-offset = auto-increment values are starting with 1, step is 2 = 1,3,5,7,…..  As you can imagine, collision with another master will be critical 🙂 Yup, odd and even numbers will be sufficient in 2 masters envrionment.

Similar to M1 we will setup my.cnf for M2 server:

Server_id is set to 2, increment values starting with 2, increment 2 = 2,4,6,8,….


2.2 Data transfer

As I mentioned, i have sakila database on M1 already. As we are not running any replication now, data are still just on M1. As whole this setup we made can’t run „in a past“, we will move data by mysqldump and every following action will be recorded by our replication environment. Sounds fair enough. All actions performed in M1 environment:

Small explanation – first command is saying that 4th binary log file, 326th position is having the last transaction in sakila database. Followed by next locking command we are ensured that data will be consistent and continuing (dump + binary log). This number + file name will be used at the end of setting.

Mysqldump command is well documented.

There are a lot of ways how this sql server is transfered to M2 machine such as scp, ftp, etc.

Following actions are done in M2 environment:

Ok now we have same data on M1 and M2. In next part we will….


2.2 Setup mysql internal permissions

Now we create user named „replication“ with „eagle“ password. Please beware while writing ip address – M1 is linked to M2 account and reversed.

M1 command:

M2 command:


3) Run, Forrest, run!

3.1 M1(Master)->M2(Slave) setup

Ok, now we need to tell M2 to behave as slave for M1. So in M2 environment run:

Ok, setup is done, we can start slave:

And check its status:

As you can see from the first line, slave is waiting for master actions = now we can check master status on M2 to check log position (for finishing crossing and having 2 masters):


3.2 M2(Master)->M1(Slave) setup

Ok, now we need to tell M1 to behave as slave for M2. So in M1 environment run:

Ok, setup is done, we can start slave:

Replication setup is done. Now we can verify our environment.


4) Verification

We can reuse sakila code on M2:

And now we can query within M1 machine:

You can verify that it´s working also other way = you can drop database sakila2 from M1:

And now on M2 you can verify that connection is working:

And that´s all. I can recommend MySQL High Availability book, full of descripiton, recipes and very valuable information.


This entry was posted in MySQL. Bookmark the permalink.

Comments are closed.