Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a 250 MB backup SQL file but the limit on the new hosting is only 100 MB ...

Is there a program that let's you split an SQL file into multiple SQL files?

It seems like people are answering the wrong question ... so I will clarify more:

I ONLY have the 250 MB file and only have the new hosting using phpMyAdmin which currently has no data in the database. I need to take the 250 MB file and upload it to the new host but there is a 100 MB SQL backup file upload size limit. I simply need to take one file that is too large and split it out into multiple files each containing only full valid SQL statements (no statements can be split between two files).

share|improve this question
    
Does it have to be multiple valid SQL files? I'd zip the file into several archives in 100MB chunks and upload that. –  Nifle Oct 1 '10 at 16:37
    
Yes, it has to be multiple valid SQL files ... as far as not having half of one statement in one file and the other half in another file. –  Brian T Hannan Oct 1 '10 at 17:11
add comment

5 Answers

From How do I split the output from mysqldump into smaller files?

First dump the schema (it surely fits in 2Mb, no?)

mysqldump -d --all-databases

and restore it.

Afterwards dump only the data in separate insert statements, so you can split the files and restore them without having to concatenate them on the remote server

mysqldump --all-databases --extended-insert=FALSE --no-create-info=TRUE
share|improve this answer
    
Good advice! Makes perfect sense. Really good when there are complex dependencies between tables so that a splitting of the export of both schema and data doesn't work. –  Reed Richards Nov 24 '11 at 10:57
add comment

This code will do exactly what you want (and it's open source):

http://rodo.nl/index.php?page=mysql-splitter

It allows you to split any SQL file into several smaller files (you can define the maximum size) SQL syntax will be kept correct and it works with 'multiple insert' query syntax.

Hope this helps!

share|improve this answer
add comment

1) Do you have the option to upload the file by another method eg: scp or ftp and then restore it from the local file?

2) Will your ISP take the file on CD and load it for you?

3) Can you restore the file to a local server and then make a series of backup files from it using specific criteria to keep the individual sizes down?

4) You could split the file manually then tidy up the SQL commands at the end of the files?

share|improve this answer
    
I was thinking about option 2, 3, and 4. 2 would be last resort. 3 is actually probably the next thing I'm going to try to do. 4 was my first thought, but I can't even open the large file in notepad or any editor b/c it takes hours to open a 250 MB file of all text ... all notepad-like apps freeze and never return. –  Brian T Hannan Oct 1 '10 at 18:55
    
@Brian: 4 was my first thought, but I can't even open the large file in notepad or any editor - Notepad++ can handle them fine. else look at superuser.com/questions/34749/… –  Sathya Oct 1 '10 at 20:27
    
If you want to split the file AUTOMATICALLY and then tidy up the smaller pieces, have a look at the split option of the Swiss File Knife utility (stahlworks.com/dev/swiss-file-knife.html) –  Linker3000 Oct 5 '10 at 8:20
add comment

Instead of splitting the file, you could use a MySQL client on your local machine and connect it to the remote MySQL DB. I use HeidiSQL and have found it very good.

Of course it may take a while to send the 250MB of SQL statements across the Internet.

You could also try BigDump

share|improve this answer
add comment

There's a couple of options if you can run a bash or perl script. Try this one from yoodey.com

#!/usr/bin/perl -w
#
# splitmysqldump - split mysqldump file into per-database dump files.
use strict;
use warnings;
my $dbfile;
my $dbname = q{};
my $header = q{};
while (<>) {    

# Beginning of a new database section:
    # close currently open file and start a new one
    if (m/-- Current Database\: \`([-\w]+)\`/) {
    if (defined $dbfile && tell $dbfile != -1) {
        close $dbfile or die "Could not close file!"
    }
    $dbname = $1;
    open $dbfile, ">>", "$1_dump.sql" or die "Could not create file!";
    print $dbfile $header;
    print "Writing file $1_dump.sql ...\n";
    }
    if (defined

$dbfile && tell $dbfile != -1) {
    print $dbfile $_;
    }
    # Catch dump file header in the beginning
    # to be printed to each separate dump file.
    if (!

$dbname) { $header .= $_; }
}
close $dbfile or die "Could not close file!"
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.