Migrating too-large database to DH from other host


#1

I am trying to migrate my DB to DH from Laughing Squid. I have it stored on my computer. Finally learned how to use phpMyAdmin, only to find out my DB was too large.

Can someone explain the “shell” or “ssh” transfer in as non-technical language as possible? WikiDreamhost says to try Chrome extension “Secure Shell” or WinScp or PuTTY. I’ve looked at all three; Secure Shell is a mystery, and the Wiki.dreamhost.com directions for PuTTY don’t seem to match the screen I had (for instance, it said to click on “session” and I didn’t see “session” on my screen)

Thank you.


#2

Desperately need help. Cannot import this database, no matter what means I try. Was able to log onto WinScp and PuTTY as well as the Chrome extension. Keep seeing message that file is too large on phpMyAdmin. Don’t know what to do now.

Dreamhost is not helping. Can SOMEONE help?


#3

You should be able to import this database via SSH and putty.
First of all you need to get the export of your database onto your Dreamhost account using FTP.
Once the file is on your account login to your account via Putty and check you can see the file with the command ls which should list all the files in you account ( and any folders).
Then you need to know the host name of your MySQL server on Dreamhost. You created a sql host name when you created the new empty database.
You also need the username and password.

Then from the ssh command line in Putty type the following

mysql -h {sql hostname} -u {username} -p{password} {database_name} < {export file}

For for example:
My database is called goodwinl_test
My sql username is: goodwinl
My database password is: testimport
My sql hostname is called: sql.goodwinl
My export file is called sql_export

Then I would type:

Notice there is no space after the -p for password and the actual password itself.

After some time you should be returned to the ssh prompt if there were no errors but the database should now be there.

That’s all a bit of a rush summary, let me know if you need any more details, such as how to get the db export in the first place.


#4

Just a couple of questions for the community-at-large about this topic, since this comes up so often (I am just asking to find out what to tell Windows users when this comes up):

-is Putty the easiest application for Windows users to use ssh? I have not used Windows in a long time, but I remember it being clunky for me years ago. I just tried FireFTP (firefox plugin) and it seems pretty easy, but that might be because I know what I am doing.

-are we still telling people to use FTP? I thought that was a big no-no, but I know there are many people who still only think in FTP terms…

-also, what is the easiest Windows application for people to access sFTP with? FireFTP looks okay, but not sure what Windows users like to use.


#5

@goodwinl
–I cannot get to the command line on PuTTY where you are saying to type “1s.” Not sure where to do this.
–Do I need the IP address as “host name” or am I using my sql hostname? if I need IP address, where in Dreamhost do I find this?
–Where do I type mysql -h {sql hostname} -u {username} -p{password} {database_name} < {export file}
–Does the database from my previous host need to be named the same as the one I created in Dreamhost? I created it with a different name, so I went into where my database backup was stored locally, copied it and changed the copy name to match the name I created in Dreamhost.
–I uploaded the backup using FTP on my Dreamhost login.
–Then I went to PuTTY. I get a box that says “PuTTY configuration” on top. On left panel it has:
Session
Logging
Terminal

(more)

On right (main) panel, it says "Basic options for your PuTTY session
Specify the destination you want to connect to"
Host name (or IP address) Port


Connection type
_Raw _Telnet _Rlogin __SSH _Serial {SSH is selected}

Load, save or delete a stored session
Saved Sessions


{In box below it lists 3 items}
Default settings
WinSCP temporary sessions
(mysql.{mydomain}.com (from a previous attempt)

When I put in the host name (the info stored in wp-config file) and click “open” I get a black box that has my hostname on top and “PuTTY” … and I can’t type anything into the box. After a few seconds it times out.

What am I doing wrong?

@sierracircle I will try fireFTP if it will work

@sierracircle and @goodwinl my database is in my folder on FTP. Why can’t Dreamhost help me get this loaded?


#6

Sorry, I meant to say FireSSH same guy who made FireFTP.
If your database dump is already in your FTP folder, you will not need FireFTP

in FireSSH, i was able to create a connection to my web-host and then connect. I just needed:
domain
username
password

click connect…
once connected you should see a black-screen with computer-terminal looking text…and a little blinking line waiting for you to type in your command.


#7

[hr]
@sierracircle I logged onto firefox and added this add-on. Can’t get it to open and work, however. I see a “contribute” button – I’ll contribute gladly if I can get this to work for me – and an options button that does not let me do anything.

I know I’m probably coming across as a moron but I’m not a coder.


#8

I am not on a Windows computer, so it might be different for me, but in Firefox I go to:

Tools>Web Developer>FireSSH

that gives me a box asking for the domain, login, etc…


#9

where do I find “tools”?
[hr]
OK, I did a search and found I must press "ALT-T"
jesus christ.
[hr]

@sierracircle in the box I get in FireSSH, I have these options: (under "Create an Account)
main details
Account name _______________ Category __________ (optional)
Host ____________________ Port ____
ID
Login: ________________________________
Password: _____________________
Private key: ________________________
_anonyous {option to click}

I put my sql host name as the host and the login info from wp-config. Not sure what private key I need to use.
It won’t let me connect.


#10

For putty you need to enter the following details:
Under Host name (or IP address) type in your domain name. This assumes your domain is hosted OK with Dreamhost. If not reply and we need to start off in another direction.
The port should be set to 22
Now you can type something in the Save Sessions box, this will just be a label and click Save to save these settings so you don’t need to enter them manually each time.
Now click on Open down towards the bottom of the screen.
You should see something like this…
Putty security alert… blah blah blah…
Click on Yes.
Then you should see:
login as:
Type your username in Password: your password
The you should get something like this:

[code]Welcome to blahblah.dreamhost.com

Any malicious and/or unauthorized activity is strictly forbidden.
All activity may be logged by DreamHost Web Hosting.[/code]

Then you’re in!


#11

At this point you are not trying to connect to your MySQL server, you are connecting to your web-server.

so your domain would just be something like: greatboots.com
and your username and password are the same as your sFTP username and password

of course, shell/sftp access must be enabled for that user under the DreamHost panel, if you have not already done that


#12

@goodwinl
I tried … tried logging in with the log in info from wp-config (I was in the black screen and it would not let me type in a password.)
tried again with my primary account login info. Again it would not let me type in a password.
[hr]
I have enabled shell access. tried several different user names but none would let me enter a password.


#13

The password is not echoed back to the screen so it looks like you are not typing anything but the password is going in.

The account you need is the username shown for that domain under the “fully hosted” section of the “manage domains” part of your DreamHost control panel.


#14

I never got to type the password.

I used the user name shown for that domain under the fully hosted section. What about the password? Is it the one shown in wp-config for the website in question, or the one I use for FTP? I’ve tried both.

Thing is, I am seeing the database on panel.Dreamhost.com under “mysql databases.” it’s 8 kb and looks like it was placed there on May 7. if it’s THERE, why do I still need to import it? Why can’t I get my site live?


#15

8kb is nothing. That database will be empty.
The password will be your ftp password.

The other tack we could take would be to compress your export file. Can you report back the name of the file you have? Does it have an extension like .zip of .tar or .tar.gz


#16

An empty database can exist without any content. It also might be possible that you did a clean install of WordPress and it filled the database with tables and such, but only bare-bones content.

The thing you have on your computer that you are trying to import should be an SQL file that has all of the data from your old install.

You want to import that into your new database so that your new database is filled with content…this is what WordPress looks at to see what the text is from each page, or where the links all point to, or what your setting where, etc.

The user and password for the DATABASE (which is what you see on your wp-config.php file) is different from the FTP/shell user.

The user you need the password for is the user you use to log-in to your FTP with…same username and password…however, you have to enable shell access for that user under the DreamHost panel (but is sounds like you have done that already)

In FireSSH, you just add the username and password in the box that comes up…click connect and you should be connected.


#17

As @sierracircle said, your backup file is proably a raw .SQL file which are usually pretty large.
If you have pkzip or winzip or similar software try zipping up the export file on your PC to make it smaller. Then use the IMPORT feature on PHPMYADMIN to import that zip file rather than the original backup. Phpmyadmin should detect it is a zip file and handle it automatically. That may get you around your problems.


#18

it is a sql file, 271,331 kb in the backup folder. Zipped, it goes down to 36,696 kb.


#19

Far too big to bring into SQL via phpmyadmin :frowning:
You’re going to have to get that Putty / SSH login sorted.
Do you get to the point where “login:” appears in the Putty window once you have clicked on Open?


#20

I think you will still have issues with trying to upload that size to phpmyadmin…

if you can get that file into the root of your web-folder, and then manage to figure out your FTP/shell username and password, then the rest is just a single line of code you run through your ssh shell…takes less than a minute.