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