Dump all MySQL databases script.

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Dump all MySQL databases script.

Post 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();
}


User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Dump all MySQL databases script.

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Dump all MySQL databases script.

Post by Benjamin »

Thanks, Weirdan. Trying to write that would make my head hurt. It may come in handy though :)
Post Reply