Master Master replication tutorial for Mariadb

We have two servers. lets call them mater1 (1.1.1.1) and master2 (2.2.2.2) .
This instruction is tested for mariadb 10.1 on Debian 8.

First set my.cnf on both servers:

on master1:

[mysqld]
server-id=1
bind-address = 0.0.0.0
log_bin=/var/log/mysql/mariadb-bin
expire_logs_days=10
sync_binlog = 1
slave_compressed_protocol = 1
binlog_format = row

#to avoid in PRIMARY / UNIQUE key collisions when we write on both servers
auto-increment-increment=2
auto-increment-offset=1

on master2:

[mysqld]
server-id=2 #server id of each server should be unique
bind-address = 0.0.0.0
log_bin=/var/log/mysql/mariadb-bin
expire_logs_days=10
sync_binlog = 1
slave_compressed_protocol = 1
binlog_format = row


--------------
copy databases form master1 to master2

on master1
mysqldump -uroot -p --opt --routines --triggers --events --single-transaction --master-data=2 -A > alldb.sql

on maste2:
mysql -uroot -p < alldb.sql

--------------

Now setting up the databases


On master1:

MariaDB [(none)]> create user 'replication'@'2.2.2.2' identified by 'MYSECRETPASSWD';
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'2.2.2.2';
MariaDB [(none)]> flush privileges;

On master2:
MariaDB [(none)]> create user 'replication'@'1.1.1.1' identified by 'MYSECRETPASSWD';
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'1.1.1.1';
MariaDB [(none)]> flush privileges;


on Master 1:

MariaDB [(none)]> SELECT @@GLOBAL.gtid_current_pos;
+---------------------------+
| @@GLOBAL.gtid_current_pos |
+---------------------------+
| 0-1-26067388              |
+---------------------------+



on Master 2:
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> stop slave;
MariaDB [(none)]> reset slave;
MariaDB [(none)]> set global gtid_slave_pos = "0-1-26067388";
MariaDB [(none)]> change master to master_host='1.1.1.1', master_user='replication', master_password='MYSECRETPASSWD', master_use_gtid=slave_pos;
MariaDB [(none)]> start slave; 

MariaDB [(none)]> SELECT @@GLOBAL.gtid_current_pos;
+---------------------------+
| @@GLOBAL.gtid_current_pos |
+---------------------------+
| 0-2-26067398              |
+---------------------------+


on Master 1:

MariaDB [(none)]> stop slave;
MariaDB [(none)]> reset slave;
MariaDB [(none)]> set global gtid_slave_pos = "0-2-26067398";
MariaDB [(none)]> change master to master_host='2.2.2.2', master_user='replication', master_password='MYSECRETPASSWD', master_use_gtid=slave_pos;
MariaDB [(none)]> start slave;

Check whether slaves are set properly:

MariaDB [(none)]> show slave status\G;

you should see both:

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes



---------
Test replication by creating a new database on master1:

MariaDB [(none)]> create database replication_test;

On Master2, check whether replication_test in created:

MariaDB [(none)]> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| mysql
|
| performance_schema |
| replication_test
|
+--------------------+

To be absolutely sure, try the same tests the othe way round.

That's it. enjyo Master/Master replication.

Curtesy of the book MariaDB High Performance-Packt Publishing (2014) by Pierre Mavro