mysql remote synchronization

[cpb]

I need to do a two way synchronization between my web server and a laptop. The laptop is located in eastern africa and does not always have an internet connection. The web server needs to be the master, (located in the U.S.) and the laptop needs to be the slave. Whenever the web server makes changes the laptop needs to be updated and visa-versa. Does anyone know of program or some code that could help me with this? If so is there a way to save the code so the client (on the laptop) could dial-up and press a 'Synchronize' button and have the code run?
Thanks in advance
-chuck

[raf]

Welcome here.

I think we need a bit more information on your db-design.
db-synchonisation is always dificult and aspecially in a db like mySQL that doesn't support triggers. If there isn't a persistent connection between the two, it can be inpossible --> if you use classical primary key-foreign key relations with autoincrement PK's.

So if it is possible for your setup, will depend on your db-design and what sort of modifications you make when they aren't connected.

If the db-design is quite straightformward without to much relationships, using a simple 'replace' will go a long way. Replace updates records if they already exist, and inserts them if they don't already exist. But the problem then is that you need a composit key where you have a variable that identifyes the server that created the record, and then use a multiple field index on this variable and another counter variable to ge a unique index for each record.

[cpb]

Thanks for the reply
My database is not very complex about ten tables and no relationships. The database design would never change so the only thing that needs to sync is insert of data and update of data. I've never synch sql databases before so i really dont know what exactly I'm doing. Do you know of a very detailed tutorial? Or could you explain how to do this throughly.
-chuck

[Kiwi]

My recommendation would be to do a double synchronisation. By this I mean you would archive your database on your server, then synchronise the archived database with your laptop (then, perhaps, do a reverse from the archive on your laptop to a working database on your laptop).

This is a slightly cumbersome approach -- requiring effectively three synchronisations. The advantage of this is it gets around the problem of unreliable connections. The server -> server-archive and the laptop-archive -> laptop synchronisations can happen independantly of any connection and if you have any prolems with your server-archive -> laptop archive synchronisation, your main applications are unaffected (albeit unsynchronised).

As to how to do it, you need to start by deciding what data needs to be copied. The easiest, but least efficient way, is to simply copy the entire database -- the raw data files. A second alternative would be to copy any records since the last synchronisation (which would require a last-update timestamp in each record -- something that I would recommend in virtually any table design).

Part of this decision will involve the two synchronised databases -- are they going to be logically identical (exactly the same table definitions and application logic), functionally identical (same application logic, but some differences in table defintions) or functionally equivalent (same business logic, but different application logic)? Each creates some problems, and makes other things quite simple. Do you want to copy the entire dataset, or just a relevant subset of the dataset? That is, just copy the important things. Do you want to have options for synchronisation? For example, once a week, you could copy the entire database; during the week, you might just copy updates.

[cpb]

I dont think i would need to synch the whole database, only the info that is there after the last synch (Insert and update). I cant really have options for synching because the only time the laptop would be online is when the user dials up. I'm using .cfm pages for the server and the laptop, I was thinking I could have a button on one of the .cfm pages that when pressed would run the synchronization (after the user dials up). What do you think
-chuck

[Kiwi]

The options I talked about are all possible through a simple web-page -- you would merely have several buttons instead of just one (although my CFM knowledge is nowhere near enough to give you the details of how to do it -- but I can get you through the background analysis easily enough).

There are a lot of questions here, but the most important are:
Do you have a 'last-update' field in all of your tables that you want syncronised? This should be a timestamp, entered automatically whenever that record is added or updated. What basic functions do you need to be able to perform? At the record level, we're talking about add, edit/update and delete. Delete is the one that makes things the trickiest for synchronisation. Is making changes to the server database an option? There will have to be chances to the database applications at both ends, as well as the laptop database; the ability to change the server database will be very helpful.

[cpb]

Question 1:
No I do not have a 'Last-update' field. How do I make one that is filled out automatically?
Question 2:
The only operations I need to perform is add and update. No deleting
Question 3:
Yes
-chuck

[Kiwi]

Well, the simplest approach is to add a last_update field to each table. This should be type 'TIMESTAMP'. You should be able to do this withALTER TABLE table_name ADD last_update TIMESTAMPThis column will now contain the time that you make an insert/undate on a record. You can specify a datetime value to enter in a timestamp field, which overcomes a couple of problems for you.

To get the records that have changed since you last synchronised, you need to select from each table the records with a timestamp greater than the last synchronisation (SELECT * FROM table_name WHERE last_update >> last synchronisation).

[raf]

Originally posted by raf
using a simple 'replace' will go a long way. Replace updates records if they already exist, and inserts them if they don't already exist.

You can reinvent it all yourself of course ...