2

I have replication running between two MySQL instances on two separate servers. Let's say M is the master and S is the slave. It's setup as one way replication M->S. I currently have S as read only and writing binary logs with the --log-bin option. Suppose I have to failover to S because M goes down. What are the steps to recover M such that it is in sync with S again? And so S can become the SLAVE again?

I have seen the following thread but they depend on circular replication:
https://serverfault.com/questions/350756/recover-a-crashed-mysql-master-server-from-the-slave https://serverfault.com/questions/350756/recover-a-crashed-mysql-master-server-from-the-slave

Steps I have so far to promote S to master: 

STOP SLAVE IO_THREAD;
SHOW PROCESSLIST; check 'has read all relay log' status
STOP SLAVE;
SET GLOBAL read_only=0;
UNLOCK TABLES;
RESET MASTER;

UPDATED LIST OF COMMANDS: 

On Master:
CHANGE MASTER TO MASTER_HOST=slave.com, MASTER_LOG_FILE=MASTER_LOG_FILE, MASTER_LOG_POS=MASTER_LOG_POS, ....
START SLAVE;
# Wait till caught up;
STOP SLAVE;
RESET MASTER;

On Slave:
CHANGE MASTER TO <original slave settings>;
START SLAVE;
USE mydb;
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only=1;
Community
  • 1
postelrich
  • 195
  • 2
  • 9

1 Answers1

3

Before you set it to read_only=0 and point connections to the Slave (new master) run a 

show master status;

and take a note with those numbers.

When you recovered your master server you can run

CHANGE MASTER TO MASTER_HOST=myslavehost.example.com, MASTER_LOG_FILE=[take it from your notes],MASTER_LOG_POS=[take it from your notes], MASTER_USER='replication_user', MASTER_PASSWORD='replication_password';
START SLAVE;

Then the old master should start to replicate from the new master and catch up.

A couple of things to keep in mind:

  • Make sure your expire_logs_days settings is set to more days than your recovery takes otherwise you would lose the binlogs and you need to set up the old master from scratch

  • Make sure you know the replication user and password. If you don't you can recover it from:

    • the repository table (in case of master_info_repository = TABLE)

      select User_name, User_password from mysql.slave_master_info;

    • the master.info file (5th and 6th line)

  • If your master has crashed chances are your slave is a bit behind so you will have some autoincrement conflict after you set the master back to replicate from the slave. pt-table-sync can you remedy that situation.
Károly Nagy
  • 3,080
  • 1
  • 14
  • 13