Also search for mysql proxy. It is a new project from the mysql folks that can load-balance multiple instances with failover.
On 11/9/07, Jonathan Share <[EMAIL PROTECTED]> wrote: > > > Mike Orr wrote: > > On Nov 8, 2007 8:06 AM, Jonathan Share <[EMAIL PROTECTED]> wrote: > >> I currently have 2 database servers set up in a master-master > >> replication set up and wish for the pylons app to fail over to the > >> second database server when the first goes down. > > > > By master-master you mean each database updates the other when a > > change comes in? How did you structure that? I wanted to do that for > > my sites with two database servers in different states (each talking > > to a local web application), but it looked like it would require major > > database restructuring to keep track of which record was "more > > accurate" in case one of the servers went down and then had to > > automatically synched with the other. I gave up because I didn't want > > it accidentally synching the wrong way and overwriting correct data. > > This is a potential problem with a master-master set up. Fortunately my > app has the advantage that there are very few update queries, the > primary content on my site is versioned so to change it you add a new > record rather than update an existing one. Past that it's user comments, > user ratings and download statistics which are all inserts so we have > very few updates going on. The updates that do happen are either things > that are very unlikely to happen at the same time on different servers > (eg. user editing a comment) so I'm happy that "last write wins" or are > aggregate fields that are denormalised (download count, total rating) > and so are recalculated from the normalised table so is harmless if it > "synch"s the wrong way. > > > > > When I looked at the MySQL docs, it only talked about master-slave > > replication, which wouldn't help if the master goes down and the slave > > has to become the master temporarily, which was the whole point of > > having a second database in the first place. > > FWIW this was the article I used for beginning with Master-Master > replication. > > > http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html > > > > >> I hope this is a stupid question but I'm trying to work out how to > >> configure a database connection in pylons to use a database failover. > > > > It's not stupid, it's just... the only thing about SQLAlchemy we > > maintain is the Pylons-SQLAlchemy interface. This is not an interface > > issue (did the query get to SQLAlchemy? are concurrent requests > > stomping on each other's connections? how do you access the model > > from a unittest or standalone script?), it's an advanced database > > question that requires the experts on the sqlalchemy list, not this > > one. Not putting you down for asking, just point out that you'll get > > a better answer there. > > Good point, my bad. I'll sign up to the SA mailing list and fire up the > thread there next week instead. > > > > >> I'm running Pylons 0.9.5 with SqlAlchemy 0.3 and mysqldb but a solution > >> for 0.9.6 or SA 0.4 would be sufficient for pointing me in the right > >> direction. > >> > >> In Java I would achieve this with a connection string that looks like > >> the following, > >> > >> jdbc:mysql://db1.example.com, > db2.example.com/dbname?failOverReadOnly=false&useUnicode=true&characterEncoding=UTF-8 > > > > You can't set up a double engine in SQLAlchemy. You could set up two > > separate engines and bind to one or the other at any time -- the > > SQLAlchemy manual and "SQLAlchemy 0.4 for people in a hurry" have some > > thoughts on distributed databases (parallel/sharding) -- but it's not > > 'automatic' so it's not quite what you want. > > > > However, your idea for a double engine that automatically falls back > > to the second one might be a worthwhile enhancement for SQLAlchemy, > > especially if you point out that Java does it and has a reference > > implementation. > > > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~----------~----~----~----~------~----~------~--~---
