Restore SQL backup

From DreamHost

Howto: Restore a mysql backup

If you are moving to a new host, or simply want to restore a backup of your MySQL database. These are the steps you should follow. We're assuming you already have a backup.sql file.

• Upload backup.sql to your server via FTP. This doesn't have to be in a web accesible place. Do remember where you put it.
• If you haven't created a database yet, do that first in your DreamHost panel. If you're restoring your backup to an existing database, it might be recommended to empty it first.
• Connect to your server using SSH.
• Now, go to the Panel, click Goodies, then MySQL. You will see a mysql command line for your database. Run that command with < /path/to/backup.sql appended, for instance:

mysql -u dbuser -p -h dbhost.yoursite.com dbname < /path/to/backup.sql

Alternate Instructions

(If you find the above a little hard to follow..try these instructions instead)

If you have just moved to DreamHost and wish to restore a database from your previous hosting arrangement, then the following instructions will allow you to do this with databases of any size. If your database is smaller than 10mb, then you can use the phpMyAdmin Import function to complete this task. Its a lot easier!

Preperation:

• Make sure you have a Dreamhost account! ;) (In this example, oursite.com)
• Create the destination database(Where we're going to put the data) by going to the DreamHost panel and going to 'Goodies' and 'manage MySQL'.(In this example, database1)
• Make sure you have a shell enabled user(In this case, user1). If you havn't done so already, you can give any of your users shell access by loging into your DreamHost panel and going to 'Users' and then 'Manage Users'

(In this example, i'm assuming our shell user, user1, also has full access to the database we want to edit)

• Make sure you have an SSH client. MacOS and Linux users probably already have one in the terminal. Windows users can use a program like Putty.

Steps:

• 1:Upload the database your wish to restore(In this example restore1.sql) to your DreamHost account(In this example, ftp://oursite.com/oursite.com) via FTP and make a note of the path(In this example /oursite.com/restore1.sql)
• 2:Use your SSH client to connect to your site(In this example, oursite.com).(Use your shell enabled user, in this example user1, to log in)
• 3: Now you can use the following command to restore your database:

mysql -u dbuser -p -h dbhost.yoursite.com dbname < /path/to/backup.sql

In our example, the command would be:

mysql -u user1 -p -h mysql.oursite.com database1 < oursite.com/oursite.com

It's very good habit to restoring database in screen session. To create new screen session type

screen

then enter command to run the whole process. After that (while command is already running) press Crtl+A and D after that (first: Ctrl+A , second: only D). You'll get

[detached]

message. Now you can logout completely and script will be still running.

See also

• Backup MySQL tells how to create a backup of your MySQL database, which you can restore using these instructions.
• Migrate MySQL gives alternate instructions for the restore step