Is MySQL Replication Appropriate in this case?
- by MJB
I have a series of databases, each of which is basically standalone. It initially seemed like I needed a replication solution, but the more I researched it, the more it felt like replication was overkill and not useful anyway. I have not done MySQL replication before, so I have been reading up on the online docs, googling, and searching SO for relevant questions, but I can't find a scenario quite like mine. Here is a brief description of my issue:
The various databases almost never have a live connection to each other.
They need to be able to "sync" by copying files to a thumb drive and then moving them to the proper destination.
It is OK for the data to not match exactly, but they should have the same parent-child relationships. That is, if a generated key differs between databases, no big deal. But the visible data must match.
Timing is not critical. Updates can
be done a week later, or even a
month later, as long as they are
done eventually.
Updates cannot be guaranteed to be
in the proper order, or in any order for that matter. They will be in order from each database; just not between databases.
Rather than a set of master-slave relationships, it is more like a central database (R/W) and multiple remote databases (also R/W).
I won't know how many remote
databases I have until they are
created. And the central DB won't know that a database exists until data arrives from it. (To me, this implies I cannot use the method of giving each its own unique identity range to guarantee uniqueness in the central database.)
It appears to me that the bottom line is that I don't want "replication" so much as I want "awareness". I want the central database to know what happened in the remote databases, but there is no time requirement. I want the remote databases to be aware of the central database, but they don't need to know about each other.
WTH is my question? It is this: Does this scenario sound like any of the typical replication scenarios, or does it sound like I have to roll my own? Perhaps #7 above is the only one that matters, and given that requirement, out-of-the-box replication is impossible.
EDIT: I realize that this question might be more suited to ServerFault. I also searched there and found no answers to my questions. And based on the replication questions I did find both on SO and SF, it seemed that the decision was 50-50 over where to put my question. Sorry if I guessed wrong.