List:General Discussion« Previous MessageNext Message »
From:Tim Gustafson Date:September 25 2009 1:07am
Subject:Master/Slave Replication Question
View as plain text  
Hi,

I'm a big fan of mySQL's multi-master replication, but I've run into gotchas over the
years.  Off the top of my head, I can think of:

- auto_increment complications, 
- if you have a->b->c->a, it's not exactly graceful to insert a "d" server for
a->b->c->d->a
- if you have a->b->c->a and b fails, it's tricky to change your config to
a->c->a
- no one authoritative data set
- problems with certain types of stored procedures/functions

I think I may have asked this question before, but I don't recall, so I thought I'd ask
here:

Assuming all your grant tables are replicated and identical, wouldn't it be possible for a
read-only mySQL slave to pass update queries to its master server, and then return the
response from the master to the client?

I think this approach has several neat advantages:

- your client software doesn't have to know anything about replication (like to connect to
a different server for updates)
- problems with stored functions and procedures go away
- no auto_increment problems - the master would maintain auto_increment consistency
- rebuilding a failed slave in this arrangement is worry-free
- one failed slave doesn't interrupt the replication of all the other servers
- you can do multi-level replication, where you have a->b, b->c, and then send an
update query to c, which would send it to b, which would send it to a, which would
process the query, return the result to b, which would then return the result to c (if
"c" couldn't run the update query on "b", or if "b" couldn't run the update query on "a",
an SQL error could be returned to the client)

I can see a few retorts right off the bat:

- this complicates the replication protocol
- not everyone would want to do it like this
- updates might take a bit longer since they have to be sent to the master

But I think this arrangement could be very useful in certain situations, and it seems to
me that this is something that could totally be implemented as a configurable option,
something like a simple my.cnf setting  that says "slave_pass_upadtes_to_master" or
something.

Has anyone suggested anything like this before?  Any thoughts/comments/flames?

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@stripped
831-459-5354

Thread
Master/Slave Replication QuestionTim Gustafson25 Sep
Re: Master/Slave Replication QuestionTim Gustafson25 Sep
  • RE: Master/Slave Replication QuestionGavin Towey26 Sep
Re: Master/Slave Replication QuestionTim Gustafson26 Sep
  • RE: Master/Slave Replication QuestionGavin Towey26 Sep
    • Re: Master/Slave Replication QuestionTim Gustafson26 Sep
Re: Master/Slave Replication QuestionTim Gustafson26 Sep