Monday, July 27, 2015

Recovering MySQL Master to Master Replication

Scenario
Master-Server A: Good Server(192.168.213.232) – Data is updated
Master-Server B: Bad Server(192.168.213.235) – Data is not updated
Database: sampledb

1.) On Master-Server B

mysql -u root -p
Enter password:
stop slave;
exit;

2.) On Master-Server A

mysql -u root -p
Enter password:
stop slave;
flush tables with read lock;
show master status;
(take note of the file and position which will be use on Master-Server B(master_log_file &master_log_pos))
quit;
mysqldump -u root -p sampledb  --quick  --lock-all-tables > sampledb.sql
scp sampledb.sql root@192.168.213.235:~
Enter password:

3.) On Master-Server B

Optional: You can create first a dump file on Bad Server before executing below commands.
mysql -u root -p
Enter password:
drop database sampledb;
create database sampledb;
exit;
mysql -u root -p sampledb < sampledb.sql
Enter password:

4.) On Master-Server A:

mysql -u root -p
Enter password:
unlock tables;
On Master-Server B:
mysql -u root -p
Enter password:
change master to master_log_file='master1-bin.xxxxxx', master_log_pos=xx;
start slave;
show slave status\G;
(Note on the ff. status) 
SLAVE_IO_STATE: Waiting for master to send event
Slave_IO_Running: Yes 
SLAVE_SQL_RUNNING: Yes
show master status;
(take note of the file and position which will be use on Master-Server A(master_log_file &master_log_pos))

5.) On Master-Server A:

mysql -u root -p
Enter password:
change master to master_log_file='master2-bin.xxxxxx', master_log_pos=xx;
start slave;
show slave status\G;
(Note on the ff. status) 
SLAVE_IO_STATE: Waiting for master to send event
Slave_IO_Running: Yes 
SLAVE_SQL_RUNNING: Yes

No comments:

Post a Comment