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;
2

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.
  • Do I not need the RESET MASTER commands on both slave and master since neither are logging slave updates? – postelrich Sep 17 '15 at 13:58
  • 1
    There is no need to RESET MASTER. You only have to start replication at the correct coordinates -- assuming -- no data was lost on the master and no binlogs have been purged from the slave from the entire time since the old master went away. In fact, if you do RESET MASTER on the old slave, you'll need to completely rebuild the old master from a dump of the old slave, because you've lost data in the slave binlogs at that point. – Michael - sqlbot Sep 18 '15 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.