Importing large .gz SQL dumps into database


#1

I have a very large database dump, which I would like to import into my Dreamhost mySQL database.

phpMyAdmin only allows files of up to 10mb, but my dump is 22mb.

Got any advice?

I could upload the file by FTP, but then what?


#2

“In the beginning was the the command line”
-Neal Stephenson

Check this Dreamhost Wiki Article on restoring MySQL database from the shell command line, and I think you will be good to go.

–rlparker


#3

Yup, I knew it was something like that. It doesn’t have any info on using gunzip though.

I found what I was looking for on http://dev.mysql.com/doc/refman/5.0/en/upgrading-to-arch.html - MySQL’s website.

Thanks for the help. :slight_smile:

Out of curiosity, how long does it take for the Dreamhost bots to activate a new shell account? :stuck_out_tongue:


#4

Oops! I’m sorry about that; the gunzip part was probably what you were actually looking for and I didn’t even address it in my reply. Thanks for following up with the relevant link!

–rlparker


#5

As for “tme it takes DH to activate a shell account”:

I’ve never found it to be completely predictable. When things are going well, it seems almost instantaneous (last night around 0200 PDT) it was a matter of minutes for me, but I’ve had it take from several hours to a whole day. Anecdotally, for me it is usually within an hour or so.

What does sometimes take way to long to “activate” on Dreamhost in my experience, are mailboxes.

On a somewhat related topic, sometimes the speed with which Dreamhost does stuff via the panel is almost instantaneous. Early this morning I created a new domain, pointed Network Slutions registrar data to the Dreamhost servers, and was propagated and surfing the site from the west coast wall in a single 30 minute period. Very Cool!

It would sure be nice if it always worked that way.

–rlparker


#6

I prefer what it’s like now to what it was like a couple of hours ago. :slight_smile:

Hmm… I can’t see any… is there a way to get e-mail confirmation when the change is effected?


#7

<anal_note> This “time to activate” stuff would probably have been more useful to other forum users in it’s own thread. No offense intended. </anal_note>

If memory serves me, the “new user” and the “master account” holder do get an email “announcing” the set-up of the user, and I assume that happens when the process is “complete”

Actually, I don’t pay too much attention to that, as the “clock” icon on the user/domain/mailbox in the panel is the indicator that a change is “pending”, and it “goes away” when the thing is “good to go”. That is usually what I monitor as I work on other things ( I don’t often check my email when I’m “involved” with something - my “old-school single processor” brain has a hard time multi-tasking at times)

–rlparker


#8

Would you reckon it’s worth my trying to execute this command through PHP’s exec()?


#9

I’m sorry, Matt, I lost your train of thought there… are you talking about the “command line MySQL manipulation” "command?

I never even thought of doing it that way with the command line so readily available.

–rlparker


#10

Well, I could get a PHP script to run the gunzip which pipes the output into mysql, instead of waiting for this shell user to be activated.

[/quote]

I can’t tell whether you’re being sarcastic or not. :stuck_out_tongue:


#11

Aha! I don’t see how it could hurt to try it, and I don’t see any obvious reason off hand why it should not work, but I’m not completely sure of all that would involve.

Sorry about that ambiguity! I was most definately not being sarcastic, I was just being “dimwitted”, as I didn’t “associate” the wait for the user to activate with the act of processing the gunzip from the command line…now, of course, I do see the reason to try it from other than the command line… at least, I think I do…PHP as CGI runs under the user assigned at domain creation time (which should include “exec”?)…now you have got me confused (and I amnot being sarcastic, I am just not thinking clearly tonight I suppose).

–rlparker


#12

You’re right… this is complicated. Y’know, I think it’s time I went to bed, heh.


#13

ha ha…now my confusion appears to have infected you…I apologize! Probably not a bad idea though, as your user will likely be activated when you wake up, which will make part of this discussion moot and simplfy your life considerably. G’night!

–rlparker


#14

Alright, I did it without the shell.

Here’s my method:

Make a php script, with a single system() call in it, and keep uploading it and running with each one of these system() calls in turn

system(“gunzip -d backup.sql.gz”);
Un-gz the file containing the queries. This creates backup.sql.

system(“mysql -DDATABASENAME -hHOSTNAME -uUSERNAME -pPASSWORD < backup.sql”);
This connects to the database at the hostname with the username and password and tells it to execute the queries in backup.sql.


#15

Godd deal, and it solved your issue. I guess I’m still “befuddled” a bit though: Why didn’t you just stick both system calls in a single php file and upload/run once? Were there timing issues because of the size of the zip file?

–rlparker


#16

It’s easier to debug if anything goes wrong by doing it one stage at a time. Plus, I didn’t know if PHP was going to throw a timeout tantrum.


#17

That makes perfect sense, and actually was probably considerably quicker/easier than coding around a timeout error condition. You ought to add that technique to the Wiki.

–rlparker


#18

You can see the results of this at http://objection.mrdictionary.net :slight_smile: