Setup MariaDB (MySQL) Master/Master Replication on CentOS 7

In this tutorial we will be setting up an active/active (or master/master) MySQL cluster.

This will provide us to make changes to either server (Server A or Server B) and ensure that the changes are applied to the each server.

For high availability we will be ensuring that each node is hosted on a different subnet / availability zone.

Server A: Availability Zone 1 / 10.1.0.200
Server B: Availability Zone 2 / 10.2.0.200

Lets firstly install the relevant packages on both servers:

yum install epel-release mariadb

Once installed run the following utility (on each server) to ensure the db server security is hardened:

Server1> mysql_secure_installation

mysql -u root -p

create user 'replicator'@'%' identified by 'yourpassword';
grant replication slave on *.* to 'replicator'@'<server-2-ip>';

FLUSH PRIVILEGES;

quit;

Server2> mysql_secure_installation

mysql -u root -p

create user 'replicator'@'%' identified by 'yourpassword';
grant replication slave on *.* to 'replicator'@'<server-1-ip>';

FLUSH PRIVILEGES;

quit;

Note: Ensure that MariaDB is listening externally and not just bound to localhost (as default.) - since the servers will need to communicate with each other!

Now lets setup the replication:

Server1> vi /etc/mysql/mariadb.conf.d/50-server.cnf

# replication settings
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
replicate-do-db = LinOTP2
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = LinOTP2
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

Server2> vi /etc/mysql/mariadb.conf.d/50-server.cnf

# replication settings
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
replicate-do-db = LinOTP2
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = LinOTP2
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

Restart both of the mysql servers with:

sudo systemctl restart mysql

Obtain the master log position for each server:

Server 1> mysql -uroot -p

SHOW MASTER STATUS;

* Note * Copy down the master log position in this output - we will need to use it when running the 'change master' command on Server 2 *


Server 2>SHOW MASTER STATUS;

* Note * Copy down the master log position in this output - we will need to use it when running the 'change master' command on Server 1 *

and now configure replication:

Server 1> 

UNLOCK TABLES;

stop slave;

change master to master_host='SERVER2', master_user='replicator', master_password='yourpassword', master_log_file='mysql-bin.000001', master_log_pos=XXX;

start slave;

// replacing 'XXX' with the appropriate log position int.

Server 2> 

FLUSH TABLES WITH READ LOCK;

UNLOCK TABLES;

stop slave;

change master to master_host='SERVER1', master_user='replicator', master_password='yourpassword', master_log_file='mysql-bin.000001', master_log_pos=XXX;

start slave;

and finally review the slave status on each node with:

show slave status;

Create a test table on each server e.g.:

mysql -u root -p

create database TestDB
use TestDB

CREATE TABLE IF NOT EXISTS test (
test_id int(5) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(test_id)
    );

and verify if the changes is replicated to the other node.