As long as the traveling user will be adding, updating, and deleting records and can guarantee that no one else is touching in the master, there is something you can try.
What you want to do is setup MySQL Circular Replication (Master/Master) between Master and LapTop. Once you have this esatblished and the time has come for the traveling user (TVU) to leave for the week, here is what to do:
Step 1 : Run STOP SLAVE; on the Master
Step 2 : Run STOP SLAVE; on the LapTop
Step 3 : TVU inserts, updates, and deletes on the LapTop
Step 4 : Make sure TVU does not delete or update data that the Master will have worked on during the week.
Step 5 : When the TVU returns, run START SLAVE; on the Master to reconnect the Master to the LapTop. All inserts, updates, and deletes that occurred on the LapTop during the week will transmit over to the Master. Run SHOW SLAVE STATUS\G every couple of minutes on the Master and look for Seconds_Behind_Master going to 0. When it does, the data from the LapTop will be sync'd over to the Master.
Step 6 : (OPTIONAL) Run START SLAVE; on the LapTop to bring all the inserts, updates, and deletes the Master did during the week. Run SHOW SLAVE STATUS\G every couple of minutes on the LapTop and look for Seconds_Behind_Master going to 0. When it does, the data from the Master will be sync'd over to the LapTop.
The only real danger is if you delete or update data on the LapTop that still needs to be on the Master. If you forget that, step 5 will make that data change or disappear unintentionally. As long as you work with a new set of data on the LapTop only, there should be no problem.
You should test this between a DevServer and a LapTop you are using for Development only.
UPDATE 2011-07-24 21:42 EDT
Here is another thing you can try:
Step 1 : Setup mysql on the LapTop with no initial data on the laptop and no binary logging
Step 2 : mysqldump the data out of the Master and into the LapTop
Step 3 : Add this to my.ini on the LapTop
[mysqld]
log-bin=mysql-bin
Step 4 : Activate binary logging without a mysql restart on the LapTop
SET GLOBAL SQL_LOG_BIN = 0;
or just restart mysql
Step 5 : Perform inserts, updates, and deletes on the LapTop (Make sure they do not conflict with needed data on the Master)
Step 6 : run this
mysqlbinlog mysql-bin.0* > ThisWeeksChanges.sql
Step 7 : Run this on the Master
mysql> source ThisWeeksChanges.sql
If you have three or more Slaves, I have a nice suggestion: Use one of the Slaves as a Volunteer to be cloned.
Here is a Topology
MAS --+--> SLV1
|
+--> SLV2
|
+--> SLV0
Say you want to Spawn SLV3
. You could use SLV0
as a Clone
- STEP01) Install MySQL Binaries on
SLV3
(Same version as all other Slaves)
- STEP02) On SLV0,
STOP SLAVE;
- STEP03) On SLV0,
service mysql stop
- STEP04)
scp -r SLV0:/etc/my.cnf SLV3:/etc/my.cnf
- STEP05)
scp -r SLV0:/var/lib/mysql SLV3:/var/lib/mysql
- STEP06) On SLV0,
service mysql start
- STEP07) On SLV3,
chown -R mysql:mysql /var/lib/mysql
- STEP08) On SLV3, change
server_id
in /etc/my.cnf
to be unique from all other Slaves
- STEP09) On SLV3,
service mysql start
That's it.
This is the same paradigm followed by Percona XtraDB Cluster (PXC). When it comes to PXC, introducing a New Slave in PXC is as simple as adding the MasterIP to my.cnf and starting MySQL. All of the above steps are executed internally by PXC using Quorom Selection to choose which Slave becomes the Donor (a.k.a. Volunteer to be Cloned) as well as one of three methods for copying data (xtrabackup, rsync, mysqldump) This copying method is known as SST
(State Snapshot Transfer).
If all of the Application-Level Data are stored in InnoDB only, you should look into using PXC. If you have a mix of InnoDB/MyISAM or all MyISAM, the above 9 steps are to be scripted by you.
Best Answer
If MySQL isn't a hard requirement, consider FoundationDB Key-Value Store + SQL Layer.
FoundationDB is a distributed Key-Value database with ACID guarantees and strong fault-tolerance. You can setup a cluster spread across the different municipalities, and if a server in the cluster goes down, the rest of the cluster continues to operate and accept writes. Changes are automatically synced to the downed node once it comes back online.
Here's a video where you can see this in action: Dave is killing the power to different nodes in the cluster, and demonstrating the cluster remains up and accepts writes. This blog post walks through what happens to the database in the case of a network partition and adds more technical context.
SQL Layer is a process that maps SQL statements to the Key-Value Store. It which presents a full, ANSI-compliant SQL API to your application.
Full disclosure: I'm an engineer at FoundationDB