#!/usr/bin/perl -wT

# dump_mysql.pl v.0.3
# 
# 2002/04/12
# - updated to fix a duplicate my $dsn and
#   put a semi-colon at end of SHOW TABLE statement
#   as suggested by crazyinsomniac
#
# 2002/03/07 
# - updated to use Getopt::Long as suggested by gmax
#
# To do/wishlist:
#   unbuffer STDIN so password is hidden when entered
#   implement other flags that are also used by the 'mysqldump' utilit
+y
#   make use of default mysql config file as also suggested by gmax
#   use Pod::Usage that can produce help messages/info for 
#    various options now in this script
#

use strict;
use DBI;
use Getopt::Long;

my %dbinfo = (
                'username'      => 'dbuser',
                'password'      => 'dbpass',
                'hostname'      => 'localhost',
                );

#  END of configurable options

$dbinfo{database} = shift @ARGV;

unless( defined($dbinfo{database}) ){
        die &usage($0);
}

my ($verbose, $drop_table, $opt, $username, $password, $hostname, 
    $complete, $lock, $extended, $nodata, $nocreate);

GetOptions (
    'opt' => \$opt,
    'add-drop-table' => \$drop_table,
    'c|complete-insert' => \$complete,
#    'e|extended-insert' => \$extended,
    'add-locks' => \$lock,
    'u|username=s' => \$username,
#    'p|password=s' => \$password,
    'h|host=s' => \$hostname,
    'd|no-data' => \$nodata,
    't|no-create-info' => \$nocreate,
    );


my @tables = @ARGV;

unless( defined($dbinfo{database}) ){
    die &usage($0);
}

unless($hostname){ $hostname = 'localhost'; }
unless( defined($password) ){
    print STDERR "Password: ";
    chomp($password = <STDIN>);
}

my ($dsn) = sprintf("DBI:mysql:%s:%s", $dbinfo{database}, $dbinfo{host
+name});

my $dbh = DBI->connect(
        $dsn, 
        $username, 
        $password, 
        {
            RaiseError => 1,
            AutoCommit => 0,
        }
        ) or die $DBI::errstr;

my ($sth, %userdata, $sql, $table, $listing, %listing_hash, $colnames)
+;

unless( scalar(@tables) > 0){
        @tables = @{GetTableList($dbh)};
}

foreach $table (@tables){
    my $row;

    if(!$nocreate || $opt){
        print "\n#\n# Table structure for table '$table'\n#\n\n";
        if($drop_table || $opt){
            print "\nDROP TABLE IF EXISTS $table;\n";
        }
        print $dbh->selectall_arrayref("SHOW CREATE TABLE $table;")->[
+0][1],"\n\n";
    }

    if(!$nodata || $opt){
        print "\n#\n# Dumping data for table '$table'\n#\n\n";

        if($lock || $opt){ print "LOCK TABLES $table WRITE;\n"; }

        $sth = $dbh->prepare("SELECT * FROM $table");
        $sth->execute();

        while( $row = $sth->fetchrow_hashref ){
            my $insert;
            $insert = GetInsertSQL($row, $dbh, $table);
            print $insert, ";\n";
        }

        if($lock || $opt){ print "UNLOCK TABLES;\n"; }
        $sth->finish();
    }
}
$dbh->disconnect();

exit;

sub GetInsertSQL{
    my ($data, $dbh, $table) = @_;
    my ($sql, $cols, $col);

    $cols = GetTableCols($dbh, $table);
    $sql = "INSERT INTO $table ";
    if($complete){
        $sql.= "( ";
        $sql.= join(', ', @$cols);
        $sql.= " ) ";
    }
    $sql.= "VALUES ( ";
    foreach $col (@$cols){
        $sql.= ' '.$dbh->quote( ${$data}{$col} );
        $sql.= ',';
    }
    $sql =~ s/\,$//; # remove trailing comma
    $sql.= " )";
}

sub GetTableCols{
        my ($dbh, $table) = @_;
        my ($rs);
        $rs = $dbh->selectcol_arrayref("DESCRIBE $table");
        return $rs;
}

sub GetTableList{
        my ($dbh) = @_;
        my ($rs, $sql);
        $rs = $dbh->selectcol_arrayref("SHOW TABLES");
        return $rs;
}

sub usage{
        my ($cmd) = @_;
        die "Usage:\n" .
        " $cmd [options] [database] [tables...]\n" .
        " Example:\n" .
        " $cmd --opt -u userfoo -p phpbb messages users > mybackup.sql
+\n" .
        " Try redirecting it to a file, as shown above.\n\n";
}
[download]