|
|
Posted by Richard Levasseur on 07/27/06 02:19
mootmail-googlegroups@yahoo.com 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.
Navigation:
[Reply to this message]
|