Open source software security

Dump all data from a MySQL database into a tab delimited file

30 November -0001

This is a simple script that will dump all the data out of a MySQL database. The results are written to a file called output.tab. The script is written in Perl and requires the DBI package to be installed.

The file is run from the command line using. You must have perl installed on the machine running the script.

!/usr/bin/perl
use DBI;

$database = 'db_name';
$hostname = 'localhost';
$port = 3306;
$user = 'user_name';
$password = 'password';

$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dbh = DBI->connect($dsn, $user, $password)
    or die "Couldn't connect to databse: " . DBI->errstr;

my $sth = $dbh->prepare('show tables');
$sth->execute();

my $filename = 'output.tab';
open FILE, "> $filename"  or die "Can't open $filename : $!";
while (my ($id) = $sth->fetchrow_array()) {
    my $sql_query = $dbh->prepare("select * from $id");
    $sql_query->execute();
    print FILE "\n" . $id  . "\n";
    while (my (@row) = $sql_query->fetchrow_array()) {
        foreach (@row) {
            print FILE $_ . "\t";
        }
        print FILE "\n";
    }
}
close(FILE);