3

The setup: multiple MySQL databases at different locations with the same scheme. The databases are in production.

The motivation: we want to present information in these databases in a web interface, clearly showing which database the row originated from. We want to be able to get this data from one single source (for different reasons, one of them is pagination which gets tricky if you use multiple sources).

The problem: how do we collect data from multiple databases, storing it at a central location and clearly marking the origin of each row? We have discussed using a centralized DB that tracks changes to the production DBs, with the same schema and one additional column for origin. If possible, we would like to avoid having to make changes in the production environment.

Since we can't use MySQL's replication (multiple masters to a single slave isn't allowed), what are our other options? Are there any existing solutions for something like this or do we have to code something ourselves? Is the best solution to change the database schemas in production and add a column for origin?

The idea of a centralized database isn't set in stone. If there is a solution to this that solves our other problems without a centralized DB, we can be flexible.

Any help is much appreciated.

Improve this question
1
  • I'm guessing that SQLite and Unison sync'ing aren't an option? Sorry, my wife's a DBA, and I have to suggest this to her at least once a day =)
    – Greeblesnort
    Oct 1, 2009 at 4:01

2 Answers 2

Reset to default
2

You're going to have to write some code somewhere along the line. My opinion is that it would be better to rely on what MySQL is able to do natively and not twisting your existing application too much to adapt.

One option I foresee is to replicate each location back to separate slave processes on a centralised machine using MySQL lazy replication. Now you wouldn't of course be able to happily JOIN the data back together natively. But it would allow you to address the pagination issue in application logic, by making the data geographically close enough to work with and having the origin of all data clearly defined by which slave process it is present on.

Your options for pagination would likely be determined by the size of the dataset. If not very big, then you might be able to simply perform it in application/session memory. Otherwise you could use a small centralised database instance to dump the results temporarily to.

It'll be interesting to read other suggestions.

Improve this answer
1

Make all the mysql slaves log (non-binary) to file. .. and pipe the file into an irc chatroom #feed On the server, run your ircd .. and listen to #feed chatroom .. and pipe the commands into | mysql -D master

If you then have a second, read-only, 'master' which is replicated into by the first, your queries to the second won't lock/block the feeds.

(at least I saw this with earlier versions)

j

Improve this answer

You must log in to answer this question.