MK9aRCuC

1#!/usr/bin/perl
2#
3# File: recover_schema.pl
4# Author: Mike Hamrick <mikeh@bluegecko.net>
5#
6# This program lets you generate a number of CREATE TABLE statements
7# from a number of .frm files passed in on the command line.
8#
9# It is required that the .frm files be from InnoDB tables.
10#
11
12use strict;
13use warnings;
14use File::Basename;
15use Data::Dumper;
16use Getopt::Long;
17use DBI;
18
19main ();
20
21sub main {
22use constant {
23DBUSER => ‘root’,
24DBPASS => ‘mysqltrain’,
25};
26
27my $options = {
28‘help’ => ‘Get program usage’,
29‘user=s’ => ‘Specify MySQL user’,
30‘password=s’ => ‘Specify MySQL password’,
31};
32
33my $opts = {};
34GetOptions ($opts, keys %$options);
35usage ($options) if $opts->{help};
36
37my @frms;
38foreach my $frm (@ARGV) {
39fatal ("$frm is not a readable file.") if ! -r $frm;
40fatal ("$frm is not an .frm file.") if $frm !~ /.frm$/;
41push @frms, $frm;
42}
43
44usage ($options) if $#frms == -1;
45
46$opts->{user} = DBUSER unless $opts->{user};
47$opts->{password} = DBPASS unless $opts->{password};
48
49my $dbh = connect_to_mysql (‘localhost’, $opts->{user}, $opts->{password});
50if (!$dbh) {
51fatal (‘Could not connect to %s as %s, err: %s.’,
52‘localhost’, DBUSER, $dbh->errstr);
53}
54
55if (!$dbh->do (‘CREATE DATABASE IF NOT EXISTS test’)) {
56fatal (‘Could not create test database: %s’, $dbh->errstr);
57}
58
59my $testdir = get_var ($dbh, ‘datadir’) . ‘test’;
60fatal ("The datadir is not writeable: $!") unless -w $testdir;
61
62foreach (@frms) {
63my $frm = $_;
64$frm = basename ($frm);
65$frm =~ s/.frm$//;
66
67my $table = "`test`.`$frm`";
68
69if (!$dbh->do ("DROP TABLE IF EXISTS $table")) {
70fatal ("Could not DROP TABLE $table: %s", $dbh->errstr);
71}
72if (!$dbh->do ("CREATE TABLE test.$frm (id int) ENGINE=InnoDB")) {
73fatal ("Could not CREATE TABLE $table: %s", $dbh->errstr);
74}
75}
76
77$dbh->do ("FLUSH TABLES") || fatal ("Can’t flush tables: %s", $dbh->errstr);
78
79my ($login, $pass, $uid, $gid) = getpwnam (‘mysql’);
80foreach my $frm (@frms) {
81if (system ("/bin/cp -a $frm $testdir")) {
82fatal ("Could not copy $frm to $testdir: $!");
83}
84my $base = basename ($frm);
85if (!chown $uid, $gid, "$testdir/$base") {
86fatal ("Could not chown $testdir/$base: $!");
87}
88}
89
90foreach my $frm (@frms) {
91$frm = basename ($frm);
92$frm =~ s/.frm$//;
93
94my $table = "`test`.`$frm`";
95
96my $sth = $dbh->prepare ("SHOW CREATE TABLE $table");
97fatal (‘Could not prepare query: %s’, $dbh->errstr) unless $sth;
98fatal (‘Could not SHOW CREATE TABLE: %s’, $sth->errstr) unless $sth->execute;
99print sprintf "%s;nn", ($sth->fetchrow_array)[1];
100$sth->finish;
101
102if (!$dbh->do ("DROP TABLE IF EXISTS $table")) {
103fatal ("Could not DROP TABLE $table: %s", $dbh->errstr);
104}
105}
106$dbh->disconnect;
107}
108
109sub usage {
110my $switches = shift;
111my $progname = basename ($0);
112
113print STDERR "nUsage: $progname [switches] table1.frm table2.frm …n";
114foreach my $switch (sort keys %$switches) {
115print STDERR sprintf ("t–%-15s%sn",
116$switch,
117$switches->{$switch});
118}
119print "n";
120exit 0;
121}
122
123sub connect_to_mysql {
124my ($host, $user, $pass) = @_;
125
126my $dsn = "dbi:mysql:host=$host";
127my $opt = { RaiseError => 0, AutoCommit => 1 };
128my $dbh = DBI->connect ($dsn, $user, $pass, $opt);
129$dbh->{mysql_auto_reconnect} = 1;
130return $dbh;
131}
132
133sub get_var {
134my $dbh = shift;
135my $var = shift;
136
137my $sth = $dbh->prepare ("SHOW VARIABLES LIKE ‘$var’");
138fatal (‘Could not prepare query: %s’, $dbh->errstr) unless $sth;
139fatal (‘Could not get variable %s: %s’, $var, $sth->errstr) unless $sth->execute;
140my $ret = ($sth->fetchrow_array)[1];
141$sth->finish;
142
143return $ret;
144}
145
146sub fatal {
147my $msg = shift;
148my $prog = basename ($0);
149
150my $err = "n$prog: [FATAL]" . sprintf ($msg, @_);
151print STDERR "$errnn";
152exit 1;
153}