Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been managing mysql db's for some years now, and have gone through several iterations of promoting a slave to take over the role of its former master. My understanding has always been that after you promote a slave to become the new master, you need to basically rebuild the former master from scratch (i.e. from backup) to turn it into a slave of the new master. That 'understanding' has likely been due to all those iterations being due to corruption or some other problem with the original master. It may merely be the misguided assumption of a sysadmin.

I'm in the process of migrating an existing hardware infrastructure into IaaS. I have a slave in IaaS of the existing master, and when the time comes, I'll promote that slave to become the new master for the new infrastructure.

But I've wondered - is there a way to have the promotion be 'non-destructive' of the original master? Say it becomes clear after deployment that there are performance issues, or what have you - and I realize that i need to fall back to the original master, if only for some hours or days until issues can be worked out. Is it possible to re-promote the former master, in a data-consistent fashion with the new master?

I guess a related question in this case would be, can the former master be immediately turned around to become a slave of the newly promoted master? Thus being able to switch between them as needed?

This would all be under CentOS 5.x, Perconal mysql 5.5.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

The reason you have to rebuild the old master from scratch is because as soon as you make one single change to the database on the new master (former slave), then the database on the old master is (by definition) stale, out of date, inconsistent, and obsolete.

Promoting a slave to master, as you likely realize already, is not technically a MySQL concept, since every server has the potential to be both, simultaneously. Promotion of a slave to master is really nothing more than "choosing which server the application will be permitted to write to."

If your intention is to make the old master into a new slave and preserve data integrity, you would configure the two machines to be each other's slaves before you promote the current slave to master... this is master/master a.k.a. circular replication. If no data is being directly manipulated on the original slave, then any binlog events the slave may send back to the master would be those that originated on the original master, which would be stamped with the master's server_id, so the master will skip over and disregard them.

When you switch things around with your application, the old master/new slave will immediately start acting on replication events it sees in the binlog that were generated by writes to the old slave/new master.

You have likely read negative things about master/master in MySQL, but there's nothing inherently problematic about it as long as you understand that there is no provision for handling conflicting writes made simultaneously to both systems. If your application is only ever connecting to 1 of the machines at a time, then conflicting writes cannot occur.

share|improve this answer
    
Thanks for the clear answer. Yes, mysql 'master-master' has always had the stink of 'badness' associated with it. I have good control of network access to the systems, so the circular replication certainly sounds feasible. That said - since this is a do-or-die cutover, the only reason I'd fail back is to give myself a little extra time to fix some issue that's a problem but not a catastrophe. In reality, I really shouldn't keep a crutch like this in my back pocket. Then again, I'm a sysadmin. We live for workarounds. –  anastrophe Aug 26 '13 at 5:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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