MySQL import / export at the Command Line

Note: This post is over a year old and the content may no longer be accurate.

Like many (most?) developers when I work with a database I tend to manage them using another programme – usually either phpMyAdmin or Navicat. However there are times when you have to get basic again and dig down to the Command Line (usually because of an access issue like a firewall setting you can’t change or because you need to import a file too big for phpMyAdmin). The trouble is if you only use these commands once in a blue moon you soon forget them.

Dump (export your database)

  1. Login to the shell or Command Prompt, navigate to where you would like your file saved.

  2. mysqldump --user=your_username --password=your_password your_database > your_dump_file.sql

Where:
your_username is a mysql user with the correct rights to the database you want to dump.
your_password is the password for your_username.
your_database is the name of your database.
your_dump_file.sql is the name of the file that will be created in the export.

Import

  1. Create the database that you want to import into, but leave it empty or if you are using an existing database remove the tables (make sure you have a backup first).
  2. Login to the shell or Command Prompt

  3. mysql --user=your_username --password=your_password your_database < your_file_to_import.sql