SmugX has asked for the wisdom of the Perl Monks concerning the following question:

Hello all.

I want to mirror a database table from location A to location B. I only require location B to hold a subset of the information in A - that I'd define simply by a query. The mirror is one way, as location B is read-only. I don't need the mirror to be "live", so I plan to run a DBI-based perl script every hour or two that updates location B. So far, so simple. :-)

What I'm looking for is an efficient and easy way to do this. My first thought was to copy the whole table from A to a temporary table in B, and do a rename to make this the current table. However, this seems a tad inefficient (the table has many rows which don't change often, and I'd be doing many INSERTs), and this does leave table B non-existant for a brief period, which isn't ideal with people constantly querying it.

Alternatively, I could query both databases, and do a bunch of comparisons to see which rows would need adding, which deleting, which updating - and then perform the relevant INSERT/DELETE/UPDATE queries. This approach sounds more promising, but so generic that I wondered if there were any existing modules to perform mirroring in this fashion - or, indeed, if there's a better approach. I've tried CPAN searching for "dbi mirror", but haven't really found anything that looks applicable. Maybe I'm being thick, however!

Does anyone have any pointers for me?

Many thanks,
Neil.

P.S. My source database is quite old and doesn't support any flashy replication functionality.
  • Comment on Searching for Module to Mirror Database Table

Replies are listed 'Best First'.
Re: Searching for Module to Mirror Database Table
by john_oshea (Priest) on Jan 19, 2006 at 18:03 UTC

    A pointer to the mighty-useful-looking Data::Sync came up at some point in the last week or so. That might do the trick.

[reply]
Re: Searching for Module to Mirror Database Table
by NiJo (Friar) on Jan 19, 2006 at 19:51 UTC
    1) Dump query result to SQL file in location A. Using the DBs dump tool is prefered.
    2) Add SQL header (Create table ...) and footer (end transaction; create indexes)
    2) Rsync with old file in location B.
    3) Use DB B (un)dump tool into a temporary table.
    4) Do a transaction on DB B: zap old table, rename temporary table.

    This way minimizes network traffic and rsync does the INSERT/DELETE/UPDATE queries job. It becomes inefficient in step 3 if the file size is too large (several dozens of megabytes).

    How many records are involved and which databases?

[reply]

      Thanks for your reply.

      Ah yes, I can see where you're coming from, and this methodology sounds good if you're running the same database in both locations - which would, of course, be sensible, but unfortunately isn't the case for me. (Source: FoxPro 2.6a, Destination: MySQL 3.23). Sorry, I should have made that clear. (I always seem to omit some vital piece of information out of my questions, in an attempt to remove the unnecessary!).

[reply]
Re: Searching for Module to Mirror Database Table
by shiza (Hermit) on Jan 19, 2006 at 19:34 UTC
    What database are you using?

    With MySQL, I've always used the database itself to do any replication/mirroring. You could do something as simple as dumping the information you want from DB A with mysqldump and then loading it into DB B.

    Update: Just noticed your P.S. :)
    You could also setup up MySQL Replication

    If you're not using MySQL, take a peak at the docs for whatever DB you are using.
[reply]

      Thanks for your comments. My destination database is actually MySQL (albeit an old version; 3.23, I believe). My source database is actually FoxPro for DOS 2.6a. (!)

[reply]
Re: Searching for Module to Mirror Database Table
by kulls (Hermit) on Jan 20, 2006 at 08:18 UTC
[reply]
Re: Searching for Module to Mirror Database Table
by radiantmatrix (Parson) on Jan 20, 2006 at 17:11 UTC

    Well, if your target database is reasonably modern, it probably supports LOAD DATA INFILE syntax, which allows you a very fast way to load a CSV file (for example) into the database. You could query your database A and then use Text::CSV_XS to generate an appropraite CSV file.

    Move the CSV to the target (database B) machine and run the LOAD DATA INFILE. All of that should be reasonably scriptable, and probably the fastest route.

    <-radiant.matrix->
    A collection of thoughts and links from the minds of geeks
    The Code that can be seen is not the true Code
    I haven't found a problem yet that can't be solved by a well-placed trebuchet
[reply]
[d/l]
[select]
Re: Searching for Module to Mirror Database Table
by simonm (Vicar) on Jan 23, 2006 at 17:28 UTC
    Consider using a text-file dump to extract data from the old database in a consistent format and order, and then copy that to location B and use the standard "diff" tool to compare it with the previous file copied over on an earlier iteration.

    By reading the diff output, you should be able to spot which lines have been deleted, which modified, and which ones added... From there, it should be relatively easy to generated the INSERT/DELETE/UPDATE queries.

[reply]

Back to Seekers of Perl Wisdom