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)
- Login to the shell or Command Prompt, navigate to where you would like your file saved.
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
- 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).
- Login to the shell or Command Prompt
mysql --user=your_username --password=your_password your_database < your_file_to_import.sql