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
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:
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.
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
This is the script I wrote to split the phpmyadmin's server backup to single database files: