How can I restore an sql dump file to a mysql server?

Question

How can I restore an sql dump file to a mysql server?

Answer 1

Hi There!

First, you need an ssh client. PuTTY is an easy one to use and works well. Search for it on google and download it.

1. Under session, put your website name into the host name box. Use port 22, and make sure SSH is selected. Under SSH in the left menu, you may have to select a different protocol. I use 2. Click open.

2. Input your site administration username into the shell prompt. It will prompt you for a password. Enter this too. Use the cd command to change directories to where you want your backup to be stored.
To confirm the directory, type "pwd" without the quotes.
Input "mysqldump -u [username] -p --opt [databasename] > [backupfilename.sql]" substituting the correct username for your database, database name, and filename for the backup. It will prompt you for a password. Enter your SQL password. If it returns with a prompt and no errors, you successfully made a backup.

3. To restore the database, login as usual. Use the cd command to change directories to where your sql backup is stored. Use the ls command to verifty that the file is there and you have the correct name. Type "mysql -u[username] -p[password] [databasename] < [backupfilename.sql]" and press enter. do not put a space between u and your username, or p and your password. If it returns with a prompt, you successfully restored your database.

--------------------------------

BACKING UP YOUR MYSQL DATABASE:
To make a backup copy of your mysql database via a SSH session:

%/usr/local/nf/bin/mysqldump --force --quick --quote-names -h mysqlhost -u Username -pPassword DatabseID > dbBackupFile.sql

(You will then be prompted to enter your database password.)

DUMPING A TABLE FROM YOUR MYSQL DATABASE:
/usr/local/nf/bin/mysqldump --force --quick --quote-names -h mysqlhost -u Username -pPassword DatabaseID tablename > tables.sql

IMPORTING DATA INTO YOUR MYSQL DATABASE:
To import a sql file to an existing mysql database hosted on your account:

%/usr/local/nf/bin/mysql --force --quick -h mysqlhost -u Username -pPassword DatabaseID < File.sql

IMPORTING TABLES INTO YOUR MYSQL DATABASE:
To import a table to an existing mysql database hosted on your account:

% /usr/local/nf/bin/mysql --force --quick --quote-names -h mysqlhost -u Username -pPassword DatabaseUD tablename < tables.sql


Hope this helps~