How to import and export a database in MySQL using terminal

There are two commands

  • mysqldump: This is the command to use to export your database.
  • mysql: This is the command you will use to import your database.

Exporting

open up a terminal window and issue the following command:

mysqldump -u USER -p DATABASE > FILENAME.sql

Where USER is the MySQL administrator user, DATABASE is the database you want to export, and FILENAME is the file name you want to name the exported file (best just to use the database name for the filename, so to avoid confusion.)

When you issue this command you will be prompted for the MySQL admin password. Enter that password and hit the Enter key. In the directory you issued the command you will now have a file with the .sql extension which is the file you then need to copy to your CD, DVD, or USB flash drive.

Importing

open up a terminal window and issue the command:

mysql -u USER -p DATABASE < FILENAME.sql

Where USER is the MySQL admin username, DATABASE is the name of the database to be imported, and FILENAME.sql is the dump that was exported from the initial machine.

Using host

mysqldump -h HOST -u USER -p DATABASE > FILENAME.sql

Advertisements