Help importing database through ssh

#1

I’m trying to copy a database from one user to another, and it’s been a little bit of a nightmare…
The database is larger than 8MB, so I couldn’t use phpmyadmin, which is what I’m used to.

I’ve been following this guide here:
http://wiki.dreamhost.com/Restore_SQL_backup - Alternate Instruction section.

Using Putty, I managed to dump the data out alright, but couldn’t manage to import the database back into mysql.
The error message I see : "No such file or directory"
I think it’s probably coz I’ve not put the right path for the backup file.

Here’s what I’ve done: (not real data, obviously)

  • create a user (to login to ftp, shell, etc) called “ftpuser”

  • create a new mysql database user called “dbuser”

  • create a new mysql database called “db”

  • log in to ftp as “ftpuser”

  • put a copy of the sql file to be imported, dbdump.sql on the root (of the user)

  • run putty, log in as “ftpuser”

  • type the command:
    mysql -u dbuser -p dbpassword -h mysqlhost db < dbdump.sql
    Error: No such file or directory
    (I’ve confirmed that the file name is correct)

  • tried moving the dbdump to a folder (ftpuser/foldername) and use this command
    mysql -u dbuser -p dbpassword -h mysqlhost db < ftpuser/dbdump.sql
    Same error

I’ve googled and read other instructions, but still couldn’t work it out.

Any help would be appreciated.

p.s I have very very basic understanding of mysql and ssh, so a more detailed explanation would be great.

Thanks in advance

#2

That command should be fine except no space between the -p and the password
mysql -u dbuser -pdbpassword -h mysqlhost db < ./dbdump.sql

This assumes you are in the same folder as the sql file
The sql file can’t have any create database directives or views etc…
Your password doesn’t have any special shell characters, if it does you need to single quote it -p’dbpassword’

You can also use mysqlimport and mysqldump which are wrappers

http://dev.mysql.com/doc/refman/5.5/en/mysqlimport.html
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Jw

As an alternative you can:

  1. cut the sql file into smaller chunks so phpMyAdmin will handle it.
  2. Use MySQL workbench
  3. Use Eclipse database tools after you setup the database in the DH cpanel for remote connections as outlined in #2
#3

Jwwicks,

Thanks very much for this. I removed the space between -p and the password, and it worked like a charm.
Again, thank you very much for taking the time to read and respond.
I can finally sleep easy tonight

#4

Thank you sooo much! I kept getting errors while trying to do this and using the commands the way you listed them along with including the ADD DROP TABLE; I finally got my database imported.