recover_schema.pl

[code type=”perl”]
#!/usr/bin/perl
#
# File: recover_schema.pl
# Author: Mike Hamrick <mikeh@bluegecko.net>
#
# This program lets you generate a number of CREATE TABLE statements
# from a number of .frm files passed in on the command line.
#
# It is required that the .frm files be from InnoDB tables.
#

use strict;
use warnings;
use File::Basename;
use Data::Dumper;
use Getopt::Long;
use DBI;

main ();

sub main {
use constant {
DBUSER => ‘root’,
DBPASS => ‘mysqltrain’,
};

my $options = {
‘help’ => ‘Get program usage’,
‘user=s’ => ‘Specify MySQL user’,
‘password=s’ => ‘Specify MySQL password’,
};

my $opts = {};
GetOptions ($opts, keys %$options);
usage ($options) if $opts->{help};

my @frms;
foreach my $frm (@ARGV) {
fatal ("$frm is not a readable file.") if ! -r $frm;
fatal ("$frm is not an .frm file.") if $frm !~ /.frm$/;
push @frms, $frm;
}

usage ($options) if $#frms == -1;

$opts->{user} = DBUSER unless $opts->{user};
$opts->{password} = DBPASS unless $opts->{password};

my $dbh = connect_to_mysql (‘localhost’, $opts->{user}, $opts->{password});
if (!$dbh) {
fatal (‘Could not connect to %s as %s, err: %s.’,
‘localhost’, DBUSER, $dbh->errstr);
}

if (!$dbh->do (‘CREATE DATABASE IF NOT EXISTS test’)) {
fatal (‘Could not create test database: %s’, $dbh->errstr);
}

my $testdir = get_var ($dbh, ‘datadir’) . ‘test’;
fatal ("The datadir is not writeable: $!") unless -w $testdir;

foreach (@frms) {
my $frm = $_;
$frm = basename ($frm);
$frm =~ s/.frm$//;

my $table = "`test`.`$frm`";

if (!$dbh->do ("DROP TABLE IF EXISTS $table")) {
fatal ("Could not DROP TABLE $table: %s", $dbh->errstr);
}
if (!$dbh->do ("CREATE TABLE test.$frm (id int) ENGINE=InnoDB")) {
fatal ("Could not CREATE TABLE $table: %s", $dbh->errstr);
}
}

$dbh->do ("FLUSH TABLES") || fatal ("Can’t flush tables: %s", $dbh->errstr);

my ($login, $pass, $uid, $gid) = getpwnam (‘mysql’);
foreach my $frm (@frms) {
if (system ("/bin/cp -a $frm $testdir")) {
fatal ("Could not copy $frm to $testdir: $!");
}
my $base = basename ($frm);
if (!chown $uid, $gid, "$testdir/$base") {
fatal ("Could not chown $testdir/$base: $!");
}
}

foreach my $frm (@frms) {
$frm = basename ($frm);
$frm =~ s/.frm$//;

my $table = "`test`.`$frm`";

my $sth = $dbh->prepare ("SHOW CREATE TABLE $table");
fatal (‘Could not prepare query: %s’, $dbh->errstr) unless $sth;
fatal (‘Could not SHOW CREATE TABLE: %s’, $sth->errstr) unless $sth->execute;
print sprintf "%s;nn", ($sth->fetchrow_array)[1];
$sth->finish;

if (!$dbh->do ("DROP TABLE IF EXISTS $table")) {
fatal ("Could not DROP TABLE $table: %s", $dbh->errstr);
}
}
$dbh->disconnect;
}

sub usage {
my $switches = shift;
my $progname = basename ($0);

print STDERR "nUsage: $progname [switches] table1.frm table2.frm …n";
foreach my $switch (sort keys %$switches) {
print STDERR sprintf ("t–%-15s%sn",
$switch,
$switches->{$switch});
}
print "n";
exit 0;
}

sub connect_to_mysql {
my ($host, $user, $pass) = @_;

my $dsn = "dbi:mysql:host=$host";
my $opt = { RaiseError => 0, AutoCommit => 1 };
my $dbh = DBI->connect ($dsn, $user, $pass, $opt);
$dbh->{mysql_auto_reconnect} = 1;
return $dbh;
}

sub get_var {
my $dbh = shift;
my $var = shift;

my $sth = $dbh->prepare ("SHOW VARIABLES LIKE ‘$var’");
fatal (‘Could not prepare query: %s’, $dbh->errstr) unless $sth;
fatal (‘Could not get variable %s: %s’, $var, $sth->errstr) unless $sth->execute;
my $ret = ($sth->fetchrow_array)[1];
$sth->finish;

return $ret;
}

sub fatal {
my $msg = shift;
my $prog = basename ($0);

my $err = "n$prog: [FATAL]" . sprintf ($msg, @_);
print STDERR "$errnn";
exit 1;
}
[/code]