How to import/export a MySQL database

Posted on 27-Mar-2016

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 -hDB_HOST -uDB_USERNAME -pDB_PASS DB_NAME < database_file.sql

Option -hDB_HOST 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 -uusername -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 mandatory
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 -hDB_HOST -uDB_USER -pDB_PASS DB_NAME > database_file_dump.sql

If the database is on the same server where you execute the command, then you can skip the -hDB_HOST parameter