MySQL multi master replication

Environment:

Masters:

192.168.1.201 master1.localdomain master1
192.168.1.202 master2.localdomain master2

Slave:

192.168.1.203 slave.localdomain slave

Create MySQL user in master servers that will be used as mysql replication slave. Execute following commands on master server.

create user 'replicator'@'%' identified by 'password'; 

grant replication slave on *.* to 'replicator'@'%';

Setup mysql master on hosts master1 and master2 two node circular replication. Add following parameters on my.cnf of master1 and master2

Master1:

server-id = 1

log-bin = master1-bin

log-slave-updates

log-bin-index = /var/lib/mysql/log-bin.index

log-error = /var/lib/mysql/error.log

relay-log = /var/lib/mysql/relay.log

relay-log-info-file = /var/lib/mysql/relay-log.info

relay-log-index = /var/lib/mysql/relay-log.index

auto_increment_increment = 2

auto_increment_offset = 1

Master2:

server-id = 2

log-bin = master2-bin

log-slave-updates

log-bin-index = /var/lib/mysql/log-bin.index

log-error = /var/lib/mysql/error.log

relay-log = /var/lib/mysql/relay.log

relay-log-info-file = /var/lib/mysql/relay-log.info

relay-log-index = /var/lib/mysql/relay-log.index

auto_increment_increment = 2

auto_increment_offset = 2

Restart mysql services on both master servers. Login to masters as user root and execute following command.

Master1:

CHANGE MASTER TO MASTER_HOST='192.168.1.202',MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='master2-bin.000001', MASTER_LOG_POS=120;

start slave;

show slave status\G

Master2:

CHANGE MASTER TO MASTER_HOST='192.168.1.201',MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG_POS=120;

start slave;

show slave status\G

Verify two node circular replication.

From master1 create database db1 and verify database exists on master2.

Create database db2 on master2 and verify database exists on master1.

Master1:

CREATE TABLE db1.animals (

     id MEDIUMINT NOT NULL AUTO_INCREMENT,

     name CHAR(30) NOT NULL,

     PRIMARY KEY (id)

) ;

INSERT INTO db1.animals (name) VALUES

    ('dog1'),('cat1'),('penguin1'),

    ('lax1'),('whale1'),('ostrich1');

Master2:

INSERT INTO db1.animals (name) VALUES

    ('dog'),('cat'),('penguin'),

    ('lax'),('whale'),('ostrich');

Execute following command on both master1 and master2 and verify the output.

select * from db1.animals;

Configure Slave.

Add following configuration parameters on my.cnf of slave.

server-id=3

log-bin-index = /var/lib/mysql/log-bin.index

log-error = /var/lib/mysql/error.log

relay-log = /var/lib/mysql/relay.log

relay-log-info-file = /var/lib/mysql/relay-log.info

relay-log-index = /var/lib/mysql/relay-log.index

Execute following command to configure master and you may point to any one of the master.

CHANGE MASTER TO MASTER_HOST='192.168.1.202',MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='master2-bin.000001', MASTER_LOG_POS=120;

start slave;

Execute following command on slave and verify the output.

select * from db1.animals;