Okay, our principle database is hosted by our web service company. (Using Perl and mySQL).

What I'd like to do is mirror that database to our development environment without having to dump the database from the server and import it to the development environment. Instead, I'd like only that days changes somehow reflected in the development database. Here are the catches.

1) The mySQL database on the live server can only be accessed by the localhost, so I can't simply log in remotely.

2)The local development platform isn't up all the time, or even most of the time, so I can't have the remote Perl scripts simply access the development database as a seperate call on-the-fly

3)I'd like it to update in batches. i.e. have the live database generate a set of the changes as they occur (all the SQL commands) and then send that set of commands to the development server when it is available to perform the mirror.

My first inclination is to have it so that every call to the database via DBI is recorded to a file. For example 

UPDATE sales SET qty = 12, price = 10 WHERE trans_id = 13
INSERT customers SET cust_name = 'Bob', address = '1234 Main Str.'
....etc.

SELECT commands would not be included as they don't modify the databas
+e

[download]
Later that file could be either e-mailed to the development platform or retrieved via FTP when the development platform is available for such activities. The retrieved file would be executed to the development mySQL database, and in theory, a mirror would be created. Thus, the development mySQL database should be up-to-date as of it's last retrieval.

I'd be interested in anybody's ideas or suggestions as to how to implement such a system. (or if it's already been done and I'm reinventing the wheel ;)

I'm also starting to get my fingers wet with XML, so if somebody has a nifty XML fix for this problem I'd be interested to learn it.
--
Filmo the Klown

In reply to mirroring mySQL database by filmo

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.