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