1

I have a master(M)-slave(S) replication on MySQL. During a failover test, the master was brought down and the slave was made the master by doing STOP SLAVE; RESET MASTER;. Since there are only 2 servers (master and slave) I didn't have any other slave to change master to. On the master server the MySQL data files (ibdata files and bin logs) were deleted by mistake. Can someone please tell me how to bring the master up to speed and change it back to being the master server?
Thanks.

0

STOP SLAVE; RESET MASTER; is not the right way to do a failover. You can stop a slave if you want, but RESET MASTER will delete your own binary logs. You may be looking for RESET SLAVE; CHANGE MASTER TO MASTER_HOST=''; (RESET SLAVE ALL; since 5.5). The actual failover is not performed by mysql, but by an external entity, be it a proxy or a connector.

As you have resetted the master data and the slave binary logs, you need to perform a clone of the slave (current master) with its binary logs coordinates (with mysqldump or Percona XtraBackup) and set the replication in the other direction.

If you intend to do manual failovers, a master-master replication with one of the nodes in read-only mode is suggested. You also have software like MHA that can simplify the process.

  • Thanks for your quick response. MySQL documentation (dev.mysql.com/doc/refman/5.0/en/…) says "On the slave Slave 1 being promoted to become the master, issue STOP SLAVE and RESET MASTER.". But that apart yes I'll try out the master master configuration with 1 read only node. Thanks for your help. – mahesh Sep 17 '14 at 10:25
  • @mahesh That is in the context of a switchover (not a failover), where you can be sure that no transactions are lost. Also, so that other slave can change its master. I sincerily do not like that, and mysql recommends on the header of that page using GTID+mysqlfailover, which is a better, more reliable way. Or you can use MHA, which will handle the failover better. This is one of the many places in which not following the manual is probably ok unless you know what you are doing. – jynus Sep 17 '14 at 10:30

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.