 |
From: Jay Janssen <jay.jans...@percona.com>
Date: Tue, 18 Sep 2012 07:49:59 -0400
Local: Tues, Sep 18 2012 7:49 am
Subject: Re: Consolodating into Master DB from multiple Geographically Distributed DBs
Coincidentally, I'm doing a talk on this underlying question at our conference in a few weeks: http://www.percona.com/live/nyc-2012/sessions/hazards-multi-writing-d..., and I spent a good amount of time yesterday thinking about it.
Technically what you are suggesting is possible, but it's impossible to say for sure that it's bullet proof because so much depends on the actual schema, and how the data inside it is used.
One of the points I'll be making in the talk is that the data we store in and RDBMS tends to be, well, relational. Writing across multiple masters simultaneously can be done if all the modifications are completely isolated from the other masters until at least replication occurs. By "isolated", I mean not modified *or* read elsewhere, or if read then an out of date version of the data doesn't matter to associated writes (this is probably a crude definition).
Because relational data implies relationships and multi-writing depends on isolation (read: the absence of relationships), then whether or not this architecture will "work" depends entirely on where on the continuum your data falls between relational and non-relational.
That's my take on the subject (whether useful or not, I'm not sure).
On Sep 17, 2012, at 6:10 PM, KTWalrus <ktwal...@gmail.com> wrote:
> I have 4 geographically distributed DBs. The DBs only need to be sync'd once a night with all changes made to each DB during the day.
Jay Janssen, Senior MySQL Consultant, Percona Inc.
> I'm thinking of keeping a Master DB up to date and rsync'ing to each of the geographically distributed DBs in the early morning. Each DB would record its changes to its binlog and I would apply the changes to the Master DB every few hours using mysqlbinlog piped to mysql.
> Any problems with this approach?
> I would do the autoincrement trick and I don't think the same rows would be updated in more than one of the distributed DBs (so out of sequence updates should be okay). In the middle of the night, I would have a cron job sync all of the distributed DBs to the Master DB and then perform some maintenance updates on the master, and rsync the master to each distributed DB. All DBs may be taken offline during this synchronization time.
> I've been thinking, even if I think it is okay to not keep the temporal order of the updates when applying to the master, that I might need to modify mysqlbinlog to open all the input binlog files on start up and replay them in timestamp order (or implement some sort of binlog timestamp merge so the transactions are stored in chronological order).
> -- http://about.me/jay.janssen Percona Live in NYC Oct 1-2nd: http://www.percona.com/live/nyc-2012/ You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||