Splitting huge SQL Dump

JackJack

I need to upload a SQL dump to a new database. It is 20.3 MB, and my maximum file size upload allowed is about 2,000 KB. Are there any programs that can help me split this, and if it's not too much trouble, could I have a link (and instructions, if possible) to a program that could do this? Thanks.

devinemke

you could do this by hand, by opening up the resulting SQL file (which is plain ASCII text) and copy/pasting into smaller files.

or you could write your own "dump" script (which is simply a bunch of INSERT sql statements) that automagically writes several smaller files, perhaps a few 1000 records at a time.

JackJack

I can't do it manually... I brought it into a word document to see exactly how long it was, and it was close to 8,000 pages.

onion2k

If its a dump from PHPmyadmin, I wrote a script that will split it coz I had this exact problem some time ago:

<?php

$maxsize = 1887436;

$file = fopen("dump.sql", "r");
$tables = fopen("tables.sql", "w");
$data = fopen("data_".++$d.".sql", "w");

while (!feof($file)) {

	if ($count >= $maxsize) {
		fclose($data);
		$data = fopen("data_".++$d.".sql", "w");
		$count = 0;
	}

	$line = fgets($file, 100000);
	if (substr($line,0,6) == "INSERT") {
		fwrite($data,$line);
		$count += strlen($line);
	} elseif (substr($line,0,1) == "#") {

	} else {
		fwrite($tables,$line);
	}

}

echo "Completed: ".$d." data files created.";

?>

It splits any table structure SQL into one file called tables.sql, and all the data inserts into a series of 1.8meg files called data_1.sql, data_2.sql and so on. Plus it strips out any comments. Obviously you'll need to change "dump.sql" to be the name of your SQL file. Its dead useful.

JackJack

Thanks, I'll try it out. But how exactly does it work, like how would I run it? Sorry, I'm kind of clueless when it comes to stuff like this.

onion2k

Copy that script and your mysql dump into a directory that is PHP enabled, and that PHP is able to create files in, change "dump.sql" to whatever your dump file is called, and then call it from a web browser like any other PHP script.

JackJack

Okay. Thanks.

JackJack

Okay, I executed it. It said like "Completed, 12 files were created". Now where were these files placed?

onion2k

Originally posted by JackJack
Okay, I executed it. It said like "Completed, 12 files were created". Now where were these files placed?

In the same directory as the PHP script and SQL dump.

JackJack

Ok, I see it now. Thank you very much.

JackJack

Ok, I tried importing one of the splits. Now, it should work. But I got this error:

Error
SQL-query:

INSERT INTO ats_etc
VALUES ( 5, 26, 38, '4d89adea75f20c67cca1026ec2669963' )
MySQL said:

#1046 - No Database Selected

um...?

onion2k

The sql dump is just a list of SQL commands. You'll need to choose a database to run them on before you do anything..

JackJack

I'm now getting this message...

SQL-query:

INSERT INTO ats_etc
VALUES ( 5, 26, 38, '4d89adea75f20c67cca1026ec2669963' )
MySQL said:

#1146 - Table 'aoa91_aoadb.ats_etc' doesn't exist

Should I not be inserting into ats_etc?

JackJack

I found the problem with the ATS thing. However, I'm trying to upload the backup to an Invision Powerboard Forum.

I get this message when I try to upload parts:

SQL-query:

INSERT INTO ibf_admin_logs
VALUES ( 1, 'forum', 'donew', 1, 1090252103, 'Forum \'News\' created', 'IP blocked out')
MySQL said:

#1062 - Duplicate entry '1' for key 1

Then the actual line in the dump would read:

INSERT INTO ibf_admin_logs VALUES (1,'forum','donew',1,1090252103,'Forum \'News\' created','IP blocked out');

(I blocked out my IP in the last part)