How to import/export a MySQL database

How can I import a MySQL dumpfile into the database ? You can easily restore or import MySQL data with the mysql command itself. First you need to login to your server using ssh or putty (if you're using Windows) or directly from your terminal if you are using a UNIX based operating system.

Import Datafile

mysql -u username -p -h localhost DATABASE_NAME < database_file.sql

Option -h localhost is optional and you may skip it.

If the database does not exists on the server, then you'll need to connect to the database server and create it. You'll do this with the fist part of the previous command:

mysql -u username -p

And after success you should see mysql prompt: mysql>. Now run the following commands to create the database:

# create the database. Semicolon at the end is mangatory
CREATE DATABASE database_name;

# exit database server to return to bash command prompt:
exit;

Export Database

To export a database to a local file you'll have to use mysqldump command which has a syntax similar to mysql command:

mysqldump -u database_username -p database_name > database_file_dump.sql