MySQL Dump Files

gav on 2004-03-20T21:59:57

MySQL Tip of the Day: make sure you're keeping regular backups of your data (with mysqldump) in case you do something stupid and delete a whole bunch of data.

This little script takes a dump file and extracts the SQL INSERT statements into a new file for the table you specify. It's handy if you want to restore just a single table without deleting the existing data in it.

#!/usr/bin/perl -w
use strict;
use Getopt::Long;
GetOptions(
    'file=s' => \my $fn_in, 'out=s' => \my $fn_out, 'table=s' => \my $table
);
unless ($fn_in && $table) {
    die "Usage: $0 --file= --table= [--out=]\n"
}
$fn_out ||= "$fn_in.$table";
open my $in, '<', $fn_in or die "Fatal: cannot open '$fn_in': $!\n";
open my $out, '>', $fn_out or die "Fatal: cannot write to '$fn_out': $!\n";
my $write;
while (<$in>) {
    if ($write) {
        last if /^UNLOCK TABLES/;
        print {$out} $_;
    } else {
        $write = 1 if /^LOCK TABLES $table/;
    }
}
close $in;
close $out;