Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

3 Answers

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

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
add comment

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.

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
add comment
up vote 0 down vote accepted

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);

?>
share|improve this answer
add comment

Your Answer

 
discard

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.