Re: [OT] Database synchronisation
- From: Colin McKinnon <colin.thisisnotmysurname@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 26 Jul 2006 21:11:37 GMT
Alvaro G. Vicario wrote:
I have a database called LOCAL_DB in a local computer. This DB is managed
by third party software so I have no control over it. I have another
database called INET_DB in an Internet server. I design and maintain this
one (it's an extranet written in PHP).
There's a thing on phpclassess which claims to replicate MySQL schemas (you
didn't say what DBMS) but it doesn't understand about indices and doesn't
replicate data.
I need to write an app that dumps certain fields and tables from LOCAL_DB
into INET_DB every night, using a DSL connection, so all changes made in
the office during workday are available at the extranet the following day.
Both DBMS are different so I’m accessing data through ODBC drivers.
The first simple bold algorithm I can think of is:
1) Empty INET_DB
2) Get records from LOCAL_DB
3) For each record : Insert record into INET_DB
Assuming we're talking MySQL and you are just publishing the data, then the
bestway to do it would be to slave the remote DB from the local - but that
asssumes you can reconfigure the remote DB and connect across the MySQL
port.
An alternative approach would be to run rsync - which is supposed to just
update the bits of a large file which have changed. But you'd need to to
shutdown the DBMS while it ran.
Failing that why not just shutdown the DBMS and transfer the files across -
it'll probably be a lot quicker than rebuilding the table one row at a
time.
The best solution though would be a custom script which copies over any
changes however you need to make sure that:
1) you never delete data (well you can - you just mark it as inactive then
run a housekeeping job to delete things which have been inactive for, say 2
successful update cycles).
2) you need a timestamp and primary key field on every table.
(BTW - why bother to check if a record exists before you try and insert it -
if its already there and you've got a primary key, the DBM<S will tell you
at the time of insertion).
HTH
C.
.
- References:
- [OT] Database synchronisation
- From: Alvaro G. Vicario
- [OT] Database synchronisation
- Prev by Date: Re: phpMailer error
- Next by Date: Re: What's a good program for managing a mixed html-PHP site?
- Previous by thread: Re: Database synchronisation
- Next by thread: Locate an image file on the server, extract data, resize and then output new image - can this be done?
- Index(es):
Relevant Pages
|
|
