Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
#!/usr/bin/perl -wT # 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"; }

In reply to - when you don't have mysqldump by Kozz

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others cooling their heels in the Monastery: (7)
    As of 2017-01-19 17:39 GMT
    Find Nodes?
      Voting Booth?
      Do you watch meteor showers?

      Results (170 votes). Check out past polls.