Synchronise data in two databases

Hello.

is there a good way of synchronizing two MySQL databases so they hold the
same data? Basically, I've created a family tree in php/MySQL and I'd like
to use it at two locations. Unfortunately my parents don't have broadband,
so using it on-line over the internet is not really practical, it ties up
the telephone for two long.

Master-master replication looks like it might do the trick. However, I'm a
bit concerned, as it would appear that this is intended for databases that
have a permanent or semi-permanent connection between themselves. On of
the databases being run and connected at intervals between daily and
monthly, on an ad-hoc basis is I'm sure not what the mysql developers
intended.

Master-slave replication would not do the trick, as I cannot update the
disconnected slave.

Perhaps I should at the outset forgone databases and stored the data in a
collection of files copied across using rsync.

I've also seen a number of products to synchronise databases, but these seem
to be mandraulic and aimed at updating production databases by comparing
them agaist development code.

Any thoughts? My best plan at the moment would seem to be to rewrite the
databases, lengthening the primary keys with a prefix, say A_<key> and
B_<key> where A and B are the servers. I could then write a script to
compare the contents of both databases and copy any ommissions from one two
the other. I note I could not simply delete records, I would have to keep
a record but mark it deleted to I could be sure that both databases noted
the deletion.

Any thoughs, I am sure this is not the first time this issue has arisen?


Pete


--
http://www.petezilla.co.uk

Peter Chant wrote:
> is there a good way of synchronizing two MySQL databases so they hold the
> same data?

Have you seen this?
http://www.daffodildb.com/replicator/index.html

Regards,
Bill K.

On Thu, 04 May 2006 21:15:22 +0100, Peter Chant wrote:
> Hello.
>
> is there a good way of synchronizing two MySQL databases so they hold the
> same data? Basically, I've created a family tree in php/MySQL and I'd like
> to use it at two locations. Unfortunately my parents don't have broadband,
> so using it on-line over the internet is not really practical, it ties up
> the telephone for two long.
>
> Master-master replication looks like it might do the trick. However, I'm a
> bit concerned, as it would appear that this is intended for databases that
> have a permanent or semi-permanent connection between themselves. On of
> the databases being run and connected at intervals between daily and
> monthly, on an ad-hoc basis is I'm sure not what the mysql developers
> intended.

How many people update this database regularly? If it's just you, a
normal dump/restore will do just fine. You take a snapshot with you, on
your laptop or whatever, then restore it when you return.

--
39. If I absolutely must ride into battle, I will certainly not ride at the
forefront of my Legions of Terror, nor will I seek out my opposite number
among his army.
--Peter Anspach's list of things to do as an Evil Overlord

Peter H. Coffin wrote:

> How many people update this database regularly? If it's just you, a
> normal dump/restore will do just fine. You take a snapshot with you, on
> your laptop or whatever, then restore it when you return.
>

Yes,

just me, but there is a slim possibility of my mother or brother adding to
it as well. You have stated probally the most pragmatic solution, I've
been a bit more brute force about it and simply stopped the database and
put the mysql data directory onto a usb stick. However, I'm a bit
concerned that someday I will copy the data the wrong way and overwrite the
news with the old. Also I'd like something a bit neater.

Pete

--
http://www.petezilla.co.uk

Bill Karwin wrote:

> Peter Chant wrote:
>> is there a good way of synchronizing two MySQL databases so they hold the
>> same data?
>
> Have you seen this?
> http://www.daffodildb.com/replicator/index.html

Cheers Bill, looks just the ticket. I need to do a bit of reading up of
course.

Pete

--
http://www.petezilla.co.uk

On Fri, 05 May 2006 08:18:57 +0100, Peter Chant wrote:
> Peter H. Coffin wrote:
>
>> How many people update this database regularly? If it's just you, a
>> normal dump/restore will do just fine. You take a snapshot with you, on
>> your laptop or whatever, then restore it when you return.
>>
>
> Yes,
>
> just me, but there is a slim possibility of my mother or brother adding to
> it as well. You have stated probally the most pragmatic solution, I've
> been a bit more brute force about it and simply stopped the database and
> put the mysql data directory onto a usb stick. However, I'm a bit
> concerned that someday I will copy the data the wrong way and overwrite the
> news with the old. Also I'd like something a bit neater.

A little fussing with merge(1) would probably resolve Mom's updates as
well...

--
34. I will not turn into a snake. It never helps.
--Peter Anspach's list of things to do as an Evil Overlord