>>Are you doing high-availability MySQL yourself? Yes. In Production we're doing extremely read-heavy, write-light MySQL database stuff.
The first thing that springs to mind when you say "High Availability" is "How many 9's do you need" : read Jeremy Z's blog entry @ http://jeremy.zawodny.com/blog/archives/000805.html Speaking for myself in a scenario where we can't afford all those 9's is that the method we employ is to use an expensive piece of hardware for the master MySQL DB (redundant power supplies, redundant fans, redundant nics etc.. And RAID 5 hot-swappable SCSI disk) and loads of cheap slave servers. We do all the DB load balancing at the application layer and if a slave goes BANG! The apps just move on to another slave. (build a nice wrapper in your apps that send selects to the slaves in a load balanced fashion and everything else to the master (you already have by the sounds of it)). You can have a "pseudo-slave/standby-master" standing by (another quality piece of hardware) replicating all the updates and if the production master server goes down you can "promote" the standby server to be the new master, fix the old master server and relegate the old master server to standby status and point all the slaves to the new master server. The above will mean that you will experience some loss of updates on the master whilst you're swapping masters around (either manually or automatically) but if you are a read heavy site (like you say) and your apps are well configured for this system then it won't be a big issue. MySQL say that they'll have automatic master promotion in the event of a failure sometime in the future but it isn't available right now. Another tactic you can use is to employ seriously heavy network/system managing systems that will alert you to potential problems like high CPU load, rapid loss of disk space, temperature, swap usage etc.... (I can recommend MRTG/Nagios as a free one) I have some other ideas on MySQL high availability so if the above doesn't meet your needs then I'll be happy to talk about it. Hope this helps, Cheers, Andrew -----Original Message----- From: Don MacAskill [mailto:[EMAIL PROTECTED] Sent: Tuesday 07 October 2003 17:59 To: Andrew Braithwaite Cc: '[EMAIL PROTECTED]' Subject: Re: [Fwd: MySQL w/dual-master replication?] Hi Andrew, Thanks for the reply. Great question. It's a very read-heavy system. Are you doing high-availability MySQL yourself? I'd love to hear how you're doing it, if so. Thanks! Don Andrew Braithwaite wrote: > Is the system read-heavy or write-heavy? > > Cheers, > > Andrew > > -----Original Message----- > From: Don MacAskill [mailto:[EMAIL PROTECTED] > Sent: Monday 06 October 2003 20:47 > To: [EMAIL PROTECTED] > Subject: [Fwd: MySQL w/dual-master replication?] > > > > Hey all, > > I sent this a few days ago, but it may have gotten lost in the weekend > for many of you. Don't worry, I won't keep re-posting it. :) > > I'm hoping someone out there is doing some sort of high-availability > replication and can give me a few pointers. > > Thanks in advance! > > Don > > > -------- Original Message -------- > Subject: MySQL w/dual-master replication? > Date: Sat, 04 Oct 2003 11:07:43 -0700 > From: Don MacAskill <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > > > Hi there, > > I realize it's not supported, but nonetheless, I need to investigate > how possible it is. The primary goal is high-availability, not > performance scaling. > > It sounds like if I'm careful, it might work out ok. Careful means: > > - No auto_increment columns... handle unique key assignment in my app > > - Update/insert/delete on primary master only except in case of > failure > > - Prevent possibly 'flap' by automatically using secondary master > exclusively after primary master failure. Bring up primary master > manually and manually tell the app when to start using it again after > it's allowed to catch back up. > > Are there any other gotchas I need to worry about? Is anyone actually > doing this in a production environment? I'd love to hear from you. > > > Another option is to use master/slave, and have a monitoring app > change the slave's status to master after the master has died. > There's info about this in the FAQ > (http://www.mysql.com/doc/en/Replication_FAQ.html), but I'm afraid the > documentation on the sequence of events is pretty vague. Does anyone > have any insight as to exactly how it works? > > In particular, one part of the doc says to use 'STOP SLAVE; RESET > MASTER; CHANGE MASTER TO' and another part of the doc says 'STOP SLAVE; > CHANGE MASTER TO' ... which is appropriate? Certainly, I understand > why 'STOP SLAVE', but why 'RESET MASTER'? Would the sequence of events > differ if we're just dealing with 1 master/1 slave instead of 1 > master/multiple slaves? > > Once the old master joins back up, I can let it be a slave for awhile > to catch back up, then swap back, correct? > > Thanks in advance! > > Don MacAskill > http://onethumb.smugmug.com/ > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
