Restoring database from .gz


#1

I am hoping that someone will be able to assist. I have just switched to Dreamhost, and I run a very busy forum. I am wanting it online as soon as possible. I have a .gz file which is around 50Mb, which I backed up from my old host.

My question is, how on earth do you restore a database here?

I have logged into the FTP web browser, and I clicked to add a .gz file to transfer to the FTP server. However, that has just hung and not done anything for an hour… I doubt that this is working at all.

Is there another way to restore the file?

Also, do I need to create a new database on the Dreamhost webpanel, or does that create the database when you do the restore?

I’ve put in a ticket, but I’ve not heard anything back yet, any assistance would be great!


#2

I sigguest reading the Migrate Mysql article in the wiki. It should explain everything.

Just as a qucik recap on the article, basicall you’ll have to upload that file via ftp, extract it (you need just the sql file, not a tar ball). Then you’ll have to use SSH to connect to your server to fill in the data to your database.

All database must be created through the panel first, that’s the only way. Also, if that’s a phpbb backup there’s probably instructions in that sql backup to create a new database. If that’s the case you’ll have to remove that part before the import will succecde.

–Matttail
art.googlies.net - personal website


#3

I’m in the same situation as you in this thread - http://discussion.dreamhost.com/showflat.pl?Cat=&Board=forum_troubleshooting&Number=52098&page=0&view=collapsed&sb=5&o=14&part=.

(Read the link which mentions using a shell user to run gunzip, with the output piped into mySQL)

However, I’m kind of in a jam, because DH doesn’t seem to want to activate my shell user.


#4

Thanks all, still no luck. And Dreamhost don’t seem too willing to reply to open tickets… not the best of starts for a new customer.

I am getting this error message whenever I do anything on the SQL query page:

SQL query:

gunzip < phpbb1.gz | mysql secretu_phpbb1

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘gunzip < phpbb1.gz | mysql secretu_phpbb1’ at line 1

On the Manage MYSQL page, I created a database, and I created a username/password. After creation it appears below with “NO hostnames for these databases… they are inaccessible until you add one!” - no idea what that meant, but I created a hostname.

Now I’m totally stuck, and have a site which is useless until I can either fix it with help from users, or Dreamhost actually bother to answer their mails.

Not the greatest of starts with a new host.

Out of curiousity - It’s been 15 hours now, is this the norm? Am I expecting to wait another X hours? It’s remarkable that their sales team answer their mails in 2 minutes.


#5

Ah, I think I see your problem: gunzip is not an SQL query.

What you have to do is create a ‘shell’ user (it’s on the Manage Users tab in the panel). Then, you can log into your account through SSH. I use an external program called PuTTY to do this. You point PuTTY at -server-.dreamhost.com, (my one is powerbar.dreamhost.com), and you are asked to type in the username and password for the user you’ve just made.

Using the shell is a little like the good old MSDOS commandline. If you’re familiar with Linux, then what you’ll get when you SSH is a remote bash shell to the server. (you can pick some another shell type on the Manage User tab, if you’re familiar with it)

If you can navigate to the directory with the .gz in (you may need to look up the bash commands on the Wiki - http://wiki.dreamhost.com/index.php/Shell_Commands - or Google, they’re not dissimilar to MSDOS), you can type in the gunzip command there, and it should gunzip the archive, and pop the results into mySQL. Hopefully.

That’s what I’m trying to do anyway, it’s been over fifteen hours and my shell user is jammed on ‘Pending’, (http://wiki.dreamhost.com/index.php/Enabling_Shell_Access which is fourty times longer than the Wiki would suggest, and counting) but I wish you luck.

You mentioned whether or not you should create a database. You probably should, and you should make sure that it has the same name as your old database (or your old code wont work, unless you change it).

It’s possible for the SQL dump you created to create the database (it probably creates the tables for you, but only if you selected it), but because of Dreamhost’s setup, you may not have the correct priviledges to do it via the shell.


#6

Thankyou so much for your assistance! I am just waiting for the user account to be created before I can continue. I’ve downloaded PuTTY, and attempted to login with the user/pass, but it keeps saying access is denied, so I’m guessing that the user isn’t fully created yet (there is a clock by the side).

Hopefully I’ll move on a little further.

Thankyou once again for your help!


#7

I’ve managed to upload, gunzip and reinitialise my database on DH now.

My shell user was activated a few moments ago (20 hours after the original request), so I found another way to do it: I used PHP’s ‘system()’ function to execute the commands instead of connecting to SSH through PuTTY.

I made a support ticket after eight hours, because I thought it was a bit excessive.

Good luck with your database. If you need any help, just ask. :slight_smile:


#8

Okay, I’ve now moved onto a different error.

When I login using the username/password, I just see Maildir and logs.

So I did cd …

And saw a tonne of other things (not sure if I should have seen them!)

Then I CD’d into secretu

Which is where I located my site

I CD’d into that, and ran the gunzip command:

$ gunzip < phpbb1.gz | mysql secretu_phpbb1

Then I get the following error:

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

arghhhhhhhhhhhhhh

PS - 19 hours and no response from the Dreamhost “support” team. What a nice welcome huh?!


#9

Okay… not sure if this will work, but I’m just running:

gunzip < phpbb1.gz

It is doing something. I should also mention that the forum I’m trying to restore is located at:

http://secretunicornsforum.com/forum/index.php

and the database errors there are:

Warning: mysql_connect(): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2) in /home/.friskey/secretu/secretunicornsforum.com/forum/db/mysql4.php on line 48

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /home/.friskey/secretu/secretunicornsforum.com/forum/db/mysql4.php on line 330

Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource in /home/.friskey/secretu/secretunicornsforum.com/forum/db/mysql4.php on line 331
phpBB : Critical Error

Could not connect to the database

I notice the first line is the same error that I got from when I tried to run the gunzip with all of the DB information.


#10

Try this layout of the command

gunzip < phpbb1.sql.gz | mysql -DDATABASENAME -hHOSTNAME -uUSERNAME -pPASSWORD

Make sure that there’s no space between the -D and the database name, the same for the rest of the options.


#11

Many thanks Matt.

I actually got a response not many minutes after posting my last message, and the message said that it was being restored at the moment. I checked the next morning, and everything was back :slight_smile:

Thanks again for your help!