Configure MySQL Master-Master replication

Server1: 10.0.0.1
Server2: 10.0.0.2
Both have same version of Mysql server installed

Steps to configure MySQL Master-Master replication:


On Server1:
1. Enable server listens on all Network Interfaces, also enable binary logs and other mysql config:
You should have below entries in /etc/my.cnf

auto_increment_increment = 2

auto_increment_offset = 1

bind-address=0.0.0.0
log-bin=mysql-bin-log
binlog-do-db=<dbName>
expire_logs_days=2
max_binlog_size=100M
innodb_flush_log_at_trx_commit=1
sync_binlog=1
server-id=1
2. Restart mysql server
systemctl restart mysqld
3.Login to mysql and use below commands on mysql prompt:
– Create mysql user for replication
create user ‘replica’@’%’ identified by ‘password’;

– Grant replication permissions:

grant replication slave on *.* to ‘replica’@’%’;
– Get Master status:

show master status;

Please note values in File, Position column.

On Server2:

1. In /etc/my.cnf:

auto_increment_increment = 2

auto_increment_offset = 2

bind-address=0.0.0.0
log-bin=mysql-bin-log
binlog-do-db=<dbName>
expire_logs_days=2
max_binlog_size=100M
innodb_flush_log_at_trx_commit=1
sync_binlog=1
server-id=2

2. Restart Mysql server

3. Login to mysql and use below commands:

– Get data from Server1

stop slave;

CHANGE MASTER TO MASTER_HOST = ‘10.0.0.1’,

MASTER_USER = ‘replica’,

MASTER_PASSWORD = ‘password’,

MASTER_LOG_FILE = ‘<Value in File column>’,

MASTER_LOG_POS = <Value in Position column>;

start slave;

– Check Slave status:

show slave statusG;

If you see below values then slave started successfully.

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

– Create mysql user for replication
create user ‘replica’@’%’ identified by ‘password’;

– Grant replication permissions:

grant replication slave on *.* to ‘replica’@’%’;
– Get Master status:

show master status;

Please note values in File, Position column.


On Server1:

– Get data from Server2 – login to mysql and use below command:

stop slave;

CHANGE MASTER TO MASTER_HOST = ‘10.0.0.2’,

MASTER_USER = ‘replica’,

MASTER_PASSWORD = ‘password’,

MASTER_LOG_FILE = ‘<Value in File column on Server2>’,

MASTER_LOG_POS = <Value in Position column>;

start slave;

– Check Slave status:

show slave statusG;

If you see below values then slave started successfully.

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it



Neelesh Gurjar has written 122 articles