mysql database synchronisation

[shadow.blue]

Hi ,

I am currently thinking about a way of effectively syncing mysql-databases.

A short introduction:

Right now I program a web-based frontend for a mysql database, where you can
store information, update tables etc, just what you do with a php/mysql database
solution. (I am running Slackware 9.0, Apache 2.0.48, PHP 4.3.4, MySQL 4.0.1.6).

Whenever the client is connected to the network, he works on the server,
http://server/database/index.php and does everything on the remote mysql database.

But whenever he travels, he has a local copy of this web-frontend on his computer,
and works on the local mysql database (which he can copy before he travels, without
a problem to his computer)

---

Now there is an interesting question:
In the time when he is on his journey, he does not connect to the server database via
internet, but works on his own local database, adds new data, changes data etc.
The same happens at the same time on the server, where someone else adds data
and changes something.

Now when he comes back "home" he wants to sync the data of his local database with
the server database.

My question now is: How is it possible to sync data in the way I just described, when you
are working on a mysql database solution?

Simply copying the database to the server would overwrite the other database completely.

I have been thinking about this for some time, but I am still sitting here with nothing...


What to you think would be able to do this job?

Thanks in advance, I hope you can help me cause I am lost in a dead end...

[bwilliam79]

Why not create another PHP script that will do the work for you? Read through all the records on his database, see if they are on the server database, then insert the differences. Or even make it to where he can set as a session variable whether he is running off the server or off a local database. Make any inserts he performs set a flag indicating that the records need to be synced. Then when he is back to working off the server, run a script that will merge all flagged records from his database into the one on the server. May or may not work, but I can always use the excuse that I am not a programmer. ;)

[shadow.blue]

Thanks for sharing your ideas, it sounds interesting.

But I thought that maybe mysql has something like an
implemented syncronisation/replication feature built in?
Or does it not?

Please correct me if I`m wrong...

[retoon]

I will check on it later tonight for you. Im pretty sure it has a feature built in.

[shadow.blue]

Originally posted by retoon
I will check on it later tonight for you. Im pretty sure it has a feature built in.

Thanks, that would be really cool, cause I am not getting anything useful out on that information...

[theN]

Hi

Here's a hypothetical problem based on your situation!

Two sales guys(s1 and s2) selling product X!

s1, using the live server, sells product X and thus decrements the QTY row. s2, using a local copy, buys X and increments the QTY row in the local database. The question would be, whose data goes into the final record?

AFAIK, there's no mysql tool that would allow you do what you want. You would have to use PHP to enforce your logic.

Two possible options -

<< OPTION-1 >>

s1 continues to use live server, his actions are timestamped.

You would end up with something like this in the master table & s1 using it -

id product qty timestamp
1 X 10 10am
2 X 8 10.15.31am //s1 sold 2
3 X 12 10.16.42am //s1 bought 2

s2 comes in from the field with a local table looking like this -

//s2 table//

id product qty timestamp
1 X 10 10am //original record from master table
2 X 12 10.16.33am //bought 2
3 X 9 10.17.55am //sold 3

Scan by timestamps for each of s2's entry and then updating the master!!. The best bet would be a TIMESTAMP(14) on both tables.

<< OPTION-2 >>

If both were operating on temporary tables(mirrors of master) you could do something like -

//Master Table//

id product qty timestamp
1 X 10 10am

//s1 table//

id product qty timestamp
1 X 10 10am
2 X 8 10.15am //sold 2
3 X 12 10.16am //bought 2

//s2 table//

id product qty timestamp
1 X 10 10am //original record from master table
2 X 12 10.16am //bought 2
3 X 9 10.17am //sold 3

Again, increment/decrement master table based on timestamp.

Why maintain records of each transaction instead of just updating the first row in each's table? data integrity.

If both were to come home with something like this -

//s1 table//

id product qty timestamp
1 X 12 14pm

//s2 table//

id product qty timestamp
1 X 9 14pm

Which would you flush to the master table?

IMHO option-2 seems more appealing.

NOTE
I know what a database and a table is. I've never used a database in 'real life'. Whatever I said above is an intuitive take on your problem.

hth and sorry if I wasted your time
akr

[shadow.blue]

Thanks for sharing your ideas everyone.

Sorry for the long time I was not here, but It is due to a huge lack of time, not a lack of interest :)

Well, theN, thanks for giving me some input on it.
It really looks quite good, and I will see if I get some experiment code to work later on based on parts of your thoughts.

Problem ist just time, I think I will be absent for the whole december... :(

So I really just can start at the beginning of the next year to work.

If you have additional comments / ideas /something relating to this problem, just post it here. It is in my bookmarks, and this will be the first thing to check in the new year :)

Thx everyone,
shad

[Pilutak]

Check:
http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#Replicat ion

Good luck
Pilutak
:D

[theN]

Hi

Pilutak how does replication help in shadow.blue's situation? The situation alludes to a possible time-lag between entries on the databases.

If replication does help, could you elaborate in the context of the hypothtical situation I stated previously.

regards
akr