Page 1 of 1

Dump all MySQL databases script.

Posted: Fri Nov 11, 2011 6:03 am
by Benjamin
This is just a quick and dirty script I wrote which will dump all MySQL databases to individual compressed files in a folder. It's designed to run from the command line. I am sharing this because I wasn't able to find anything similar.

Code: Select all

<?php
# configuration
define('USER', 'root');
define('PASS', '');
define('HOST', 'localhost');
define('PATH', '/var/www/databases/');

# databases to skip
$skip = array(
    'information_schema',
    'mysql',
);
# end config

try {
    if (!class_exists('mysqli')) {
        throw new Exception("The MySQLi extension is not installed.");
    }

    $db = new mysqli(HOST, USER, PASS);

    if ($db->connect_error) {
        throw new Exception($db->connect_error);
    }

    if (false == $result = $db->query('SHOW DATABASES;')) {
        throw new Exception($db->error);
    }

    while ($row = $result->fetch_object()) {
        $database = $row->Database;

        if (in_array($database, $skip)) {
            continue;
        }

        echo "Dumping database: $database";
        exec("mysqldump -u" . USER . " -p" . PASS . " $database > " . PATH . "$database.sql");
        echo "\r\033[K";
        echo "Compressing database: $database";
        exec("bzip2 " . PATH . "$database.sql");
        echo "\r\033[K";
    }

    echo "All databases exported.\n";
} catch (Exception $e) {
    echo $e->getMessage();
}



Re: Dump all MySQL databases script.

Posted: Fri Nov 11, 2011 2:35 pm
by Weirdan
I'd imagine it's just a bash one-liner:

Code: Select all

mysql -N -uroot -ptoor -e 'show databases'| grep -vi information_schema |grep -vi mysql| while read db; do mysqldump -uroot -ptoor "$db" | bzip2 > "$db.sql.bz2"; done

Re: Dump all MySQL databases script.

Posted: Sun Nov 13, 2011 5:03 pm
by Benjamin
Thanks, Weirdan. Trying to write that would make my head hurt. It may come in handy though :)