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
3 Answers
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.
![](../../users/profiles/71114.webp)
- 5,110
- 13
- 58
- 82
-
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: 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
![](../../users/profiles/102979.webp)
- 4,444
- 3
- 24
- 41
-
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:
#!/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);
?>
![](../../users/profiles/123424.webp)
- 95
- 2
- 8