Hi Greg!
> Due to budget restraints (and the fact that I'm sick of every system > here being a proprietary, closed source system that I can't even > configure without the vendor holding my hand because they refuse to > document anything) I've decided to implement the system using Linux > and mySQL. Good choice! > We'll be > replicating the statements database among several slaves, and there > are several front-end machines serving up the pdfs. I think I've > eliminated almost all the single points of failure I feasibly can... > except for the database master. It's important that I can eliminate > this as a point of failure, because for auditing purposes every > access must be logged, and to make it easier to produce activity > reports and the like, I'd like to store it in the database. > Unfortunately, this means there has to be a write every time a member > or staff look at a statement. Of course you could always write that access log in a DIFFERENT database, which means your pdf database can be read-only or nearly so, and your log database is write-only. This means both can run without locking, and speed things up tremendously. It also means you may be able to use MySQL's MyISAM Pack to pack the read-only database, again speeding it up! Also, does your access log also need to be 99.9% uptime? If not, this might simplify your SPOF considerations... > I've been playing with some ideas on how to have the slaves arbitrate > amongst themselves and take over as master in case the master does go > down. I've been thinking about this too. It is, as you say, scheduled for a later release, but in the meantime it is possible using the existing replication setup. I don't recommend it yet, because if you use a circular replication setup, and something goes wrong (which things do sometimes), it's nearly impossible to be sure you have recovered properly. Instead I recommend for now keeping the same master, and writing your own failover script, there are a few sketchy suggestions in http://www.mysql.com/doc/R/e/Replication_FAQ.html There is more than one way to skin this cat, and most of them are not thoroughly thought through. The basic possibilities for now are: 1) Use circular replication, where all machines are simulataneously master and slave. Problem: recovery from a screwup is really hard. 2) Change roles upon failure: Use CHANGE MASTER TO on all the slaves. Problems: tricky to set up and test. Hints in the FAQ help but are not really tested. Configuration files may need to be changed at failure time. 3) Never change roles, slave becomes temporary master on failure, manual reset. This is easiest to setup and test, but inelegant to reset. For 2) and 3) you also need to have a floating IP address for your master that can be changed upon failure to point to the current master, using BIND or using a gratuitous ARP scheme. Anyway, I'm currently using scheme 3, and I'll be happy to send you my half-scripted solution which you are welcome to improve upon. I was also using scheme 1 before, but gave up after something mis-synchronized the replication logs and it was impossible to recover. If you get scheme 2) to work, let me know, I might want to change. -- Steve Rapaport World Citizen --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
