up vote 1 down vote favorite
share [g+] share [fb]

I have a 2 master and 6 slave MySQL database environment. One master is hot and the other master is considered cold and is just running as a slave. I don't want to do a real master - master where both are replicating from each other. During fail over I want the cold master to start taking the write hits and the failed master to just stop until we can fix and make it a cold master/slave. Is this possible and/or correct?

Thanks

ref - more info about MMM

link|improve this question
40% accept rate
feedback

3 Answers

up vote 1 down vote

I prefer dual master with in an active passive setup using LinuxHA (heartbeat). Passive can be used for a different database than the active, so as that the box is not idle. Infrastructure depends on your resources and budget. You can replicate readonly slaves off of either or both of the servers.

But to be quite frank, it sounds like you need to do more research before someone can provide any suitable recommendations. I'd suggest reading "High Performance MySQL," which is one of the best MySQL books out there for advanced architecture.

link|improve this answer
feedback
up vote 0 down vote

You could code this logic into your application. For example in your "write" database connect routine, you could attempt to connect to the hot master and if that fails, connect to the cold master.

link|improve this answer
The manager is a set of scripts that moves an virtual IP so you do not have to have the application worry about it. I'll add a link to the question. – Clutch Feb 19 '10 at 22:57
feedback
up vote 0 down vote

This should be a comment on your question - but I don't have enough rep to do that yet.

I don't quite understand what your question means. Are you asking "how do I automatically failover from the the failed master to the cold master" ?

If so, what you want is to use either Red Hat Cluster Suite or Pacemaker to create a failover cluster. These clusters consist of two main components: cluster membership services, and resource management. In the resource management component, you would specify which hosts can run your master database. If the current master failed, the survivor would take over.

For your environment, the only actual resource that the cluster would manage would be your VIP.

link|improve this answer
My question refers more about if it is absolutely necessary to have a true Dual Master Master setup. What I have right now is not a true dual master but a slave that will act as a master. The MMM will do the fail over for me so I don't need the the Cluster environment. – Clutch Feb 23 '10 at 19:49
Why are you not running in a true Dual Master Master setup ? If writes are only being directed to one of the Masters, then there should be no consistency problems. – Martin Feb 25 '10 at 11:22
Good question. This is a legacy system with a number of large but questionable written apps using the DB system. I don't want to do the auto-increment-offset just in case the apps are doing something weird. I'm trying to add some robustness to the system without being intrusive. – Clutch Feb 26 '10 at 20:01
You wouldn't need to do the auto-increment-offset unless you write to both master servers at the same time. – Warner Feb 28 '10 at 1:30
You should be OK with just one-way replication. It just means that there is a bit more work involved in turning your retired primary into a replica of the new primary. This isn't an issue for unplanned failovers (where the retired primary is suspect in any case), but will add work to planned failovers - which you might do to perform lengthy alter tables etc. Still no big deal as long if you know the replication point in the new master's log when the switchover happened. – Martin Feb 28 '10 at 15:06
feedback

Your Answer

 
or
required, but never shown
discard

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