Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'll be doing a upgrade of a large MySQL 5.0 database to Percona 5.1 and I'm attempting to keep the actual downtime as short as possible during the upgrade, so the regular mysqldump + mysql import procedure doesn't work as well for me as the downtime would be several hours.

Currently I'm running a Master->Slave replication setup (2 servers; 1 master - 1 slave), but the new servers will be master-master-slave (3 servers; 2 masters - 1 slave) managed by MMM.

One approach I'm considering is setting up the new servers to replicate from the old database like this (o-prefix = old server, n-prefix = new server):

oM1 -> oS1
oM1 -> nM1 -> nM2 -> nS1

At the time I'm doing the swap I can then stop the slave on nM1 and shut down oM1 and oS1.

My problem is how do I reliably promote the master-slave setup to master-master so that nM1 changes master to nM2?

share|improve this question
    
You might also want check this site: area51.stackexchange.com/proposals/4260/database-administrators –  Mchl Jan 19 '11 at 19:55
    
Thanks for your suggestion. serverfault.com/questions/224372/… –  bigbluechief Jan 19 '11 at 20:27
add comment

1 Answer

MySQL master-master replication between 3 servers although possible is full of headaches, tried that once, never again.

What I would do if I were you is do it in to steps

oM1 -> nM1 -> nS2

Then once you have nM1 working exactly the way you want

oM1(out) -> nM1 -> nM2 -> nS1

This although a bit more time consuming would make sure that you go through all the right steps, since making a slave master is just a question of pointing the other master to it it's not a big issue, this also accomplishes the lack of downtime you want.

share|improve this answer
    
What is 'nS2'? For the actual change from master-slave to master-master, how would you suggest that I prevent any changes to be done on either nM1 or nM2 while changing master? Assuming that nM2 has caught up to nM1 and there are no changes pending on either nM1 or nM2, is it enough to do "SHOW MASTER STATUS" on nM2 and then on nM1 "CHANGE MASTER ..." with the file and position from nM2. –  bigbluechief Jan 20 '11 at 14:26
    
Kinda like that, what I'm saying is... just have one master for the moment out of your old one, then from that you can spawn a slave (that's why the nS2), then you take the old master out of the picture at some point and promote nS2 to a master, then spawn another slave –  lynxman Jan 20 '11 at 15:09
add comment

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.