Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a user who will be away from the office for a while, but still wants to be able to insert a load of records to our MySQL database. The user will have a laptop, but no reliable internet connection so no VPN etc.

I'm aware that MySQL has a Master/Slave Replication feature, but unless I've misunderstood, this only provides read-only synchronization for the slaves.

I'm thinking that I can take a snapshot of the master database and copy it to a local server on the user's laptop. From this point onwards the local and master database will of course be out-of-sync. However, because the user only intends to add records while away and not delete or update records I'm thinking, perhaps naively, that a re-sync should be relatively easy to do with some SQL scripting.

Is this the right way to go? Are there any hidden dangers to be aware of?

share|improve this question
add comment

2 Answers 2

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
share|improve this answer
add comment

If your tables contain any autoincrement primary keys, then any inserts this user does will cause a blinding headache for synchronizing at a later time.

In this case, his autoincremented inserts will not match anything that your master database has, and cause potential issues with foreign-key constraints.

I can't think of any other pitfalls without knowing more about your table design, except to say this is really probably not recommended!

An alternative would be to modify his inserts to generate a .sql file (instead of actually inserting into the database...again, not sure how you're application is set up) and upload it to your server to be run by someone in the office....with his laptop set up as a slave. Of course, this would cause a bit of turnaround time, unless you set it up to be automatically parsed when the file was uploaded. Just trying to brainstorm another idea.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.