Making and breaking mirrors

I have an application that is going to be required to go on the road. At
present, my client has a MySQL 4.0.1 database with more than a million
records in it. Their business is changing so that the users are looking
at taking portable computers (laptops and/or tablets) into the field to
do their work. They need to take a significant portion of the database
with them, since sometimes the work takes place in remote areas where
there is no internet connectivity, and they need to refer to data in the
database. They also need to add new records and update existing records
while they are away, and have them merged back into the main database
when they return.

I was thinking that I could have each machine that is running locally
act as a replication-style slave to keep in sync with the main server,
and then when they need to go roaming, they could have the local (slave)
server become their main one, and they will then already have all the
data they need. If I do this, is it going to flood the network keeping
things up to date? There aren't *that* many updates, but there are
something like 10 machines that might have to go out into the field. It
sounds somehow wasteful to be doing this sort of thing on the off-chance
that some machine (call it PC00) has to go out. Then there is the
problem of another machine (PC09) that spends very little time in the
office, because it's always out gallivanting around the country-side. I
think it's fairly reasonable to expect this sort of variation. Then
comes the problem of bringing things back up to date on the main server
when they get back into the office. Can the local machine be easily
turned from being the only server back into a slave, and have the
changes merged back into the main server? Remember I'm dealing with
4.0.1 here.

I implemented a scheme to allow this in a similar application in
PostgreSQL. In this case, each of the important tables had a timestamp
field updated by insert and update triggers. When the user started
roaming, the whole database was backed up and dumped onto the local
machine, and the triggers were changed on the local machine to keep an
audit trail of inserts, updates and deletes. The audit trail table kept
table names, record IDs, the event type, and the event timestamp. Then
when the users returned, they ran a program that read the audit trail
table from the local database, and applied changes to the main server
where appropriate. Where there were conflicts (the roaming user had
changed the record whilst out, and a local user had also changed it on
the main server since the dump), I simply presented a dialog box with
the field names, local values, main server values and "merged" values,
and got the user to approve the change. It made for slow-ish departures,
but very quick returns.

I saw the posting by Peter Chant that asked a similar question, and
looked at daffodil. However, I'm using C++ with native MySQL access.
Only Windows machines are used in this application.

TIA for any advice or suggestions,
Phil.

Phil Cairns wrote:
> I have an application that is going to be required to go on the road. At
> present, my client has a MySQL 4.0.1 database with more than a million
> records in it. Their business is changing so that the users are looking
> at taking portable computers (laptops and/or tablets) into the field to
> do their work. They need to take a significant portion of the database
> with them, since sometimes the work takes place in remote areas where
> there is no internet connectivity, and they need to refer to data in the
> database. They also need to add new records and update existing records
> while they are away, and have them merged back into the main database
> when they return.

[snip]

Can I assume that no-one has any experience in dealing with this sort of
thing? Or was the posting just too big?

Phil