800mb sql fiile import taking FOREVER

efeezy

New member
Oct 5, 2007
5,250
136
0
50
Is there a way to speed up an sql import? At only 800mb I don't see why it's taking an hour or more. Yeah i'm an impatient mother fucker, but c'mon, there must be a quicker way. Anyone got a trick o the trade.
 


800MB should only take around 30 secs. No reason for it to take longer.

There's a problem besides the import. I have no idea what, as it could be 1 of 500 things. mySQL itself would have no problem importing an 800MB file though.

I've imported say 3GB databases before in a minute or two, so 800MB is a non-issue. There's something else stalling it.
 
If you're sure the data is sane, put this at the top of the import file:
Code:
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;

then put this at the bottom:
Code:
SET FOREIGN_KEY_CHECKS=1;
SET UNIQUE_CHECKS=1;
 
Cool thanks. Will try those options and see if one speeds this thing up.
 
Stupid question:

Are you doing it through myphpadmin (fuck forget it) or bash?
 
Ok it's obvious I'm a damned fool..I don't mind admitting that.

Now that we've cleared that up, instead of doing an upload/import what's the absolute easiest way to get an 800mb sql file into myphpadmin? Pretend for a second you were going to teach a compete retard how to do it, that's how you have to deal with me on this one. :thumbsup:
 
ssh or telnet to server.
do:

mysql -u USER -pPASSWORD

at mysql prompt do:

use yourdatabase

source filename.sql;

That'll suck in the sql file at the max speed your server can handle.

???

Profit?
 
I've tried putty but it will only let me enter a username, no password. I see Filezilla has an SSH option, but there's no command prompt...or is there?
 
1.) Login via FTP, and upload the .sql file into the first directory you land in. Don't change directories.

2.) Open up Putty, and connect to your server. No username needed.

3.) Once connected, you'll be prompted to enter your username & password.

4.) Once logged in, type:
mysql -u USERNAME -p PASSWORD DBNAME < filename.sql

5.) Delete the .sql file from your server.

6.) Enjoy your new database!
 
1.) Login via FTP, and upload the .sql file into the first directory you land in. Don't change directories.

2.) Open up Putty, and connect to your server. No username needed.

3.) Once connected, you'll be prompted to enter your username & password.

4.) Once logged in, type:
mysql -u USERNAME -p PASSWORD DBNAME < filename.sql

5.) Delete the .sql file from your server.

6.) Enjoy your new database!

#3 is giving me the trouble. Putty prompts me for username, which I enter, but then when it the password prompt comes up on the next line, it won't let me type anything and it just shows a green box. So I have to exit out and can't go any farther. Something's fouled up.
 
LOL... it's supposed to do that. Just keep typing your password, and push enter. It's not supposed to show any characters. Security feature, so you can't even see the length of the password.

Oh, and after inputting your password, there's about an 80% chance you're going to get told you don't have shell access. In that case, contact your web host, and ask them to open up SSH on your account.
 
Lul'z, ok I managed to get in. I'll upload the sql file to the server and give it a try. Thanks for holding my hand on this. I'll post back WHEN it works.
 
Never never never never never ever ever ever ever use phpadmin for importing databases. It blows.

You can spend days trying to make it work through phpadmin um.. *cough* not that it ever happened to me.