How can I split phpmyadmin's server backup to database backups?

Question

I have a big .sql file which i created with phpmyadmin to back up my development databases. is there any way to split this big backup into several files, each file for a separated database? the file is too big to manually deal with

Answer 1

Check here maybe it will help you http://www.rusiczki.net/2007/01/24/sql-dump-file-splitter/

But if you can connect remotely to database or even have access to shell you should use mysqldump to make backup per database or even per table.

this id simple script that will do it for you:

#!/bin/bash
dbuser=
dbpass=
backupdir=
$mysql -e "show databases" --user=$dbuser --password=$dbpass | $grep -v "Database" | while read dbase
    do
            $mkdir -p $backupdir/$dbase/
            $mysqldump  -c -u $dbuser --password=$dbpass $dbase > $backupdir/$dbase/full-$dbase.sql 

            #if dont want to dump one table per sql file then erase next 4 lines then it will dump one database per sql file   
            $mysql -e "show tables" --user=$dbuser --password=$dbpass $dbase | $grep -v "Tables_in_" | while read dbtable
                do
                      $mysqldump -c -u $dbuser --password=$dbpass $dbase $dbtable > $backupdir/$dbase/$dbtable.sql
                done
    done

You must change/put dbuser dbpass and backupdir and script should do full dump every database on server (one database per sql file and and every table in database per sql file). This example should be executed on server but with small modification u can use it to do dump remotely.

Answer 2

If you just want to split file for development, not for the restore - there should be a script/tool for splitting .sql file, but never seen - maybe someone will post.

If you are restoring backup:

With large files - you might always have this problem. You can't be sure that all DB's are small. enough to import data later

If server is in your hands - import it from mysql console: http://dev.mysql.com/doc/refman/5.5/en/mysqlimport.html

phpMyAdmin can handle large files too, but web server and php limitation must be set to higher values. Also, phpMyAdmin supports gzip and bzip2, compress with bzip2 and try again.

For now you can try to import large file from console and then re-export all the db's separately with mysqldump: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html

Answer 3

This is the script I wrote to split the phpmyadmin's server backup to single database files:

#!/usr/bin/php
<?php
define('SOURCE_FILE', './localhost.sql');
define('TARGET_DIR', './DBs');

$fhs = fopen(SOURCE_FILE, 'r') or die("e-1!\n"); # source handler
$fht = null; # file handler for database files
$header = ''; # phpmyadmin settings at the begining of the file

while (($str = fgets($fhs)) !== false) {
    if(preg_match('@^CREATE DATABASE `(.*?)`@', $str, $matches)) {
        @fclose($fht);
        $fht = fopen(TARGET_DIR . '/' . $matches[1] . '.sql', 'w') or die("e-2: {$matches[1]}!\n");
        echo "Database: {$matches[1]}\n";
        fwrite($fht, $header);
    }

    if($fht)
        fwrite($fht, $str);
    else
        $header .= $str;
}

echo (feof($fhs)) ? 'All Done :)' : 'e-3: unexpected end!', "\n";

@fclose($fht);
@fclose($fhs);

?>