Friday, July 24, 2015

MySQL Master to Master Replication

Scenario
Master-Server A: 192.168.213.232
Master-Server B: 192.168.213.235
Database Name: sampledb
Replication user: sampledb
Replication password: sampledbpass

1. Configure MySQL on Master-Server A

a.) vim /etc/my.cnf
#set the following entries:
[mysqld]
#primary master server id
server-id = 1
auto-increment-offset = 1
#local slave replication options
log_bin = /var/lib/mysql/master1-bin.log
log_bin_index = /var/lib/mysql/master1-bin.log.index
expire-logs-days = 10
log-slave-updates = true
#total number of master servers
auto-increment-increment = 2
#remote master replication options
master-host = 192.168.213.235
master-port = 3306
master-user = sampledb
master-password =  sampledbpass
master-connect-retry = 60
#db to replicate
binlog-do-db = sampledb
b.)save and exit
c.) service mysqld restart
d.) mysql -u root -p
Enter password:
create database sampledb;
create user sampledb@'127.0.0.1' identified by 'sampledbpass';
create user sampledb@'localhost' identified by 'sampledbpass';
grant replication slave on *.* to 'sampledb'@'%' identified by 'sampledbpass';
show master status; 
(Take note of the file and position which will be used in the next step(master_log_file & master_log_pos))

2. Configure MySQL on Master-Server B

a.) vi /etc/my.cnf 
#set the following entries:
[mysqld]
#secondary master server id
server-id = 2
auto-increment-offset = 2
log_bin = /var/lib/mysql/master2-bin.log
log_bin_index = /var/lib/mysql/master2-bin.log.index
expire-logs-days = 10
log-slave-updates = true
#total number of master servers
auto-increment-increment = 2
#remote master replication options
master-host = 192.168.213.232
master-port = 3306
master-user = sampledb
master-password =  sampledb
master-connect-retry = 60
#db to replicate
binlog-do-db = sampledb
b.) save and exit
c.) service mysqld restart
d.) mysql -u root -p
Enter password:
create database sampledb;
create user sampledb@'127.0.0.1' identified by 'sampledbpass';
create user sampledb@'localhost' identified by 'sampledbpass';
grant replication slave on *.* to 'sampledb'@'%' identified by 'sampledbpass';
slave stop; 
change master to master_host = '192.168.213.232', master_port = 3306, master_user = 'sampledb', master_password = 'sampledbpass', master_log_file = 'master1-bin.xxxxxx', master_log_pos = xxx; 
slave start;
show master status; 

(take note of the file and position, as we will have to enter those on Master-Server A, to complete the two-way replication(master_log_file & master_log_pos))


2.) Completing Replication on Master-Server A

a.) slave stop; 
change master to master_host = '192.168.213.235', master_port = 3306, master_user = 'sampledb', master_password = 'sampledbpass', master_log_file = 'master2-bin.xxxxxx', master_log_pos = xxx;
slave start; 
show slave status\G;
(take note on the following status below)
SLAVE_IO_STATE: Waiting for master to send event
Slave_IO_Running: Yes 
SLAVE_SQL_RUNNING: Yes

b.) On Master-Server B, repeat the last command. Status must be the same.

show slave status\G;

No comments:

Post a Comment