Importing large .gz SQL dumps into database.

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?

"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

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. :)

Out of curiosity, how long does it take for the Dreamhost bots to activate a new shell account? :P
Edited by Matt_kam on 07/31/06 04:53 PM (server time).

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

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 S*lutio*ns 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

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

Hmm... I can't see any... is there a way to get e-mail confirmation when the change is effected?Edited by Matt_kam on 07/31/06 05:09 PM (server time).

In reply to:

---

is there a way to get e-mail confirmation when the change is effected?

---

<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

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

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

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.

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

I can't tell whether you're being sarcastic or not. :P

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.

In reply to:

---

I can't tell whether you're being sarcastic or not. :P

---

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 am*not* being sarcastic, I am just not thinking clearly tonight I suppose).

--rlparker

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

Edited by Matt_kam on 07/31/06 08:38 PM (server time).

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

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.

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

Edited by rlparker on 08/01/06 04:38 AM (server time).

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.

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

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