Re: Database synchronisation


mootmail-googlegroups@xxxxxxxxx wrote:
Alvaro G. Vicario wrote:
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.


As you mentioned, the obvious problem with the brute force method is
that between dumping the contents of the table and re-inserting, the
table (and anything that references it) is useless. We got around this
by using temporary tables to do the processing, then moving them into
place seamlessly when finished.

For example (in psuedo-mysql, so you'll need to adjust for your DB),
assuming 'mytable' is the table you're trying to update:
DROP TABLE mytable_new // just in case it still exists
CREATE TABLE mytable_new LIKE mytable
//do whatever you need to do for inserting into 'mytable_new'
DROP TABLE mytable_old // just in case it still exists
ALTER TABLE mytable RENAME mytable_old
ALTER TABLE mytable_new RENAME mytable
DROP TABLE mytable_old

As you can see, we create a temporary table just like the original and
do our inserts into that one. Then, when finished, move the old one
out of the way and put the new one in. Depending on your preference,
you can keep the _old table around to have a revolving backup, or just
toss it.

This has the effect of solving two of your potential issues: 1) the
extranet is not 'useless' while the import is taking place because the
old data doesn't leave until the new data is ready, and 2) if your
import crashes mid-execution, the only thing that should be affected is
the _new table and you can just drop it and start the import again
later if that happens. And it even might solve the issue of the speed
of data transfer, depending on how necessary it is to have yesterday's
data by start of business the next day. If that isn't essential, then
since the data import is invisible to the end user, it doesn't really
matter how long it takes (as long as its still < 24 hours, of course).

Hope that gives you some ideas,
Moot


This is a good idea, using temporary tables.

Since you are somewhat bandwidth limited, it may be more efficient if
you dump the database to a text file (either comma seperated or SQL)
and load it locally on the remote machine. This way you can compress
it before transfering (and the plain text will compress very nicely),
and it will execute much faster on the remote machine.

Also, building on rsync from another post, another option would be to
diff the old dump and the new dump, then send that to the remote. This
would be usefull if, even after compression, you are still transfering
a lot of data.

In either event, you should be able to dump an SQL file and send that.
It'd be much faster than reading a row from local, inserting to remote,
rinse, repeat.

.

Relevant Pages

  • Re: Help..can I use DUMP? my drive is dying - VINUM ?
    ... Subject: Help..can I use DUMP? ... > remote machine, install 2 new drives follow the tute to bootstrap ...
    (freebsd-questions)
  • Re: using dump for backups.
    ... Using dump locally to a tape, ... enabling rsh on a remote machine to dump to a device on that machine can ... If you need to dump to a remote device, you can use ssh to make it safer. ... Amanda can use tar as well. ...
    (FreeBSD-Security)
  • Re: remote dump via ssh
    ... > I want to level 0 dump a remote partition onto a large local disk. ... > There's not enough space on the remote machine to dump to a file ...
    (freebsd-questions)
  • Re: remote dump via ssh
    ... > I want to level 0 dump a remote partition onto a large local disk. ... > enough space on the remote machine to dump to a file then sftp over. ...
    (freebsd-questions)