> from what i've read and seen geographical load balancer > works as: multiple DNS A records routes to multiple > Apache Servers(mod_php tucks in as a module under Apache) > each web servers would connect to MySQL on their own > localhost 1.2.3.4-WebServer would communicate directly to > 1.2.3.4-MySQL (localhost) master-slave configurations > imply A1-MySql master replicate to A2-MySql slave > how should this new replication be handled?
You are correct about the DNS load balancing and connecting to localhost. Here is the actual set-up I'm working with: We have three LAMP servers: www-01, www-02 and www-03. Currently, mySQL on www-01 replicates to www-02, which replicates to www-03, which replicates back to www-01. What I'd like to do is have www-01 be the authoritative server, and have www-02 and www-03 both be replication slaves to www-01. When a user connect to www-01 and makes an update, nothing special - the update gets recorded, written to the binary log and replicated to www-02 and www-03. When a users attempts to update/insert/delete a record on www-02, I want www-02 to: 1. open a connection to www-01 (or have one connection that's already open for this purpose). if the connection to www-01 fails because of a TCP problem, www-02 should return an error along the lines of "replication master is unavailable; query not executed" to the client 2. authenticate against www-01 as the user the is currently authenticated on www-02. again, if this fails, return an error along the lines of "could not authenticate on replication master; query not executed" 3. pass the exact same query that www-02 received upstream to www-01 4. wait for www-01 to respond "ok" or "query error" 5. pass the response from www-01 back to the client connected to www-02 Is that clear? Normal replication would then pass the update from www-01 back to www-02 and www-03 so that all three servers are in sync. Since the update happened on www-01, auto_increments would not need to use the auto_increment_increment and auto_increment_offset, stored functions and stored procedures would not have to worry about their respective replication problems (certain types of functions/procedures aren't safe to use with normal mySQL replication), and locking would work (because lock statements would also be passed up the chain to the master server). Yes, this means that updates wouldn't be as fast as multi-master replication, but I think dealing with almost all the other problems with replication would become much more manageable. This set-up makes the most sense in read-heavy environments. As I mentioned before, my main goal here is geographic redundancy, load balancing and the ability to use applications that aren't really designed to be used with master/slave replication (Drupal, WordPress, etc) without having to modify those applications. Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
