<background>
One of my sites is heavily reliant upon a custom MySQL interface. I've had no problems with my provider to this point, but I like to have a local backup of everything just in case the unthinkable occurs. I have a backup of the filesystem, but I can't get a copy of the database. My first thought was using SELECT INTO, but it's been disabled and the admins aren't inclined to allow it (part of the motivation for my paranoia).
</background>

The solution I've been pursuing is creating a pair of DBI scripts with the dumper useing SHOW TABLES, DESCRIBE, and SELECT to retrieve the data and the restorer using CREATE DATABASE, CREATE TABLE and INSERT to put it back. I looked at DBIx::Copy, but that assumes the table structure exists already; I'd like the scripts to be able to handle an entire arbitrarily shaped database without command-line handholding. The structure returned by DESCRIBE looks somewhat painful and perhaps even insufficient for correctly recreating the table. I've written some code to manage the dump (posted in a reply under this thread), but I'm scared of writing the restorer :-). So, fellow Monks, I submit to you there might be an easier way to do this, hopefully using non-MySQL-specific commands, and if so, I'd love to hear it before doing unnecessary coding.

In reply to Dumping a Database via DBI by athomason

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.