Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 1 down vote accepted

Check here maybe it will help you

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:

$mysql -e "show databases" --user=$dbuser --password=$dbpass | $grep -v "Database" | while read dbase
            $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
                      $mysqldump -c -u $dbuser --password=$dbpass $dbase $dbtable > $backupdir/$dbase/$dbtable.sql

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.

share|improve this answer
Thanks for your answer. I think the sql dump file splitter is what I want but I'm on linux and this script seems to be for windows systems – yaser Jun 15 '12 at 11:17
If you are using linux then script that i posted with mysqldump will do what you want. Add --host parameter in mysql connection and it will write dump localy on your machine. How big is now your sql file? You must remember that big database will need time to dump from remote database. Best way if you have access to shell will be do the dump on server localy. – B14D3 Jun 15 '12 at 11:51

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:

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:

share|improve this answer
the filesize is no problem because it's a development machine and I have full access to it. the problem is I don't want to import the whole file and then remove extra databases! even when I try, it won't work because the backup file happens to have "mysql" and "information_scheme" databases that I can't overwrite. – yaser Jun 15 '12 at 11:14

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

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)) {
        $fht = fopen(TARGET_DIR . '/' . $matches[1] . '.sql', 'w') or die("e-2: {$matches[1]}!\n");
        echo "Database: {$matches[1]}\n";
        fwrite($fht, $header);

        fwrite($fht, $str);
        $header .= $str;

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


share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.