Restore Single Table from MySQL dump

[ch1n0]

Can anyone help me out to figure out why Perl script doesn’t work on MySQL v5.0, but works perfectly on MySQL v4.0. The Perl script supposes to restore a single table from mysqldump.

Here is the error below:
restore_mysql_table: ERROR: Did not find table mysql.products

#! /usr/bin/perl -w

use strict;
$| = 1; # Auto-flush STDOUT

#
# Change these values if needed
#

my $backupdir = '/m/mysqlbackups';
my $socket = '/var/lib/mysql/mysql.sock';
my $user = 'root';
my $password = 'xxxx';
my $mysqlbindir = '/usr/local/mysql/bin';

#
# No changes should be necessary to the following code
#

my $prog = $0; $prog =~ s,^.*/,,;
my $workdir = "$backupdir/${prog}_$$";

# Handle ^C and signals gracefully
$SIG{INT} = $SIG{QUIT} = $SIG{TERM} = sub { die "Cancelled.\n" };

eval {
my ($database, $table) = init();

my $backup = ask_for_backup();
print "Using file '$backup'\n";

my $tablesql = "$workdir/table.sql";
write_table_sql($backup, $database, $table, $tablesql);

my $renamed_table = rename_existing_table($database, $table);
restore_table($database, $table, $tablesql);
drop_old_table($database, $renamed_table);

print "Success.\n";
};

my $error = $@;

if ($error) {
if ($error =~ /^Usage/) {
print STDERR "\n$error\n";
}
else {
print STDERR "\n$prog: ERROR: $error\n";
}
}

cleanup();

exit($error ? 1 : 0);


#
# Subroutines
#

sub show_n_backups {
my ($n) = @_;

opendir BACKUPS, $backupdir or
die "Can't read backups directory '$backupdir' ($!): please check permissions\n";

# Find all directories that contain an 'all-databases.sql.gz' file,
# and reverse-sort them (latest first)
my @backups =
reverse
sort
grep { not /^\./ and -r backup_file($_) }
readdir BACKUPS;

closedir BACKUPS;

die "No backups found in '$backupdir'\n" unless @backups;

print "Found backups for the following dates:\n";
print map { "$_\n" } grep { defined } @backups[0 .. $n - 1];
print "...\n" if @backups > $n;

return @backups;
}

sub ask_for_backup {
my @backups = show_n_backups(8);

print "Which backup should be used [default: $backups[0]]? ";
my $ans;
while ($ans = <STDIN>) {
$ans = clean_string($ans);

$ans = $backups[0] if $ans eq '';
last if (-r backup_file($ans));

print "No backup file found at '", backup_file($ans), "'\n";
print "Which backup should be used [default: $backups[0]]? ";
}

return backup_file($ans);
}

sub write_table_sql {
my ($backup, $database, $table, $tablesql) = @_;

#
# Open the working sql file for writing
#

open TABLESQL, "> $tablesql" or die "Can't write to $tablesql ($!): please check permissions\n";

#
# gunzip the backup file and read from it
#

my $cmd = "gzip -cd $backup |";
open BACKUP, $cmd or die "Can't run gzip: please ensure that 'gzip' is an exexecutable in your PATH\n";

my $in_database = 0;
my $in_table = 0;

#
# Look for the database and table
#

while (<BACKUP>) {
if (m,^CREATE DATABASE /[^/]+/ `([^;]+)`;$,) {
# Quit looking if we're past the target database
last if $in_database;

if (clean_string($1) eq $database) {
$in_database = 1;
print "Found database '$database'\n";
}
}

next unless $in_database;

if (/^DROP TABLE IF EXISTS `([^;]+)`;/) {
# Quit if we've found the next table
last if $in_table;

if (clean_string($1) eq $table) {
$in_table = 1;
print "Found table '$table'\n";
}
}

next unless $in_table;

print TABLESQL $_;
}

close BACKUP;
close TABLESQL;

die "Did not find table ${database}.${table}\n" unless $in_table;
}

sub rename_existing_table {
my ($database, $table) = @_;

my $renamed_table = "${table}_$$";

my $error = do_mysql_command($database,
"rename table \`$table\` to \`$renamed_table\`");

if ($error =~ /^ERROR/) {
if ($error =~ /^ERROR 1017/) {
# Table doesn't exist
$renamed_table = '';
}
else {
# Some error other than 1017 (Can't find .frm file)
die "Error when renaming '$table' to '$renamed_table': $error\n";
}
}
else {
print "Renamed existing table to '$renamed_table'\n";
}

return $renamed_table;
}

sub restore_table {
my ($database, $table, $tablesql) = @_;

print "Restoring table ${database}.${table}\n";
my $error = system("$mysqlbindir/mysql --user='$user' --password='$password' --socket='$socket' $database < $tablesql");

if ($error) {
die "Error while restoring table to '$database' from '$tablesql' ($!)\n";
}
}

sub drop_old_table {
my ($database, $renamed_table) = @_;

return if $renamed_table eq '';

my $error = do_mysql_command($database, "drop table \`$renamed_table\`");

if ($error =~ /^ERROR/) {
die "Error trying to drop '$renamed_table': $error\n";
}
else {
print "Dropped old table '$renamed_table'\n";
}
}

#
# do_mysql_command is very simplistic; be careful not to pass anything
# fancy to it.
#
sub do_mysql_command {
my ($database, $cmd) = @_;
my $result = `$mysqlbindir/mysql --user='$user' --password='$password' --socket='$socket' -e '$cmd' $database 2>&1`;
}

# Ensure that the string is defined, and has no leading/trailing spaces
sub clean_string {
my ($s) = @_;
$s = '' unless defined $s;
$s =~ s,^\s+,,s;
$s =~ s,\s+$,,s;

return $s;
}

sub backup_file {
my ($backup) = @_;
return "$backupdir/$backup/all-databases.sql.gz";
}

sub init {
my $database = clean_string($ARGV[0]);
die "Usage: $prog database-name table-name\n" unless $database =~ /\w/;

my $table = clean_string($ARGV[1]);
die "Usage: $prog database-name table-name\n" unless $table =~ /\w/;

mkdir $workdir or
die "Can't create work directory '$workdir' ($!): please check permissions\n";
chdir $workdir or
die "Can't change to work directory '$workdir'($!): please check permissions\n";

return ($database, $table);
}

sub cleanup {
system('rm', '-rf', $workdir) if -d $workdir;
}