Use Drush to Export/Import a Drupal MySQL Database Dump File
Export Database to File
So today I wanted to learn how to export a Drupal database to a file quickly. Back in the day I would've logged in through cPanel and navigated to PHPMyAdmin, then manually select an export of the database and have to choose where to save the file. Not to mention all the previous steps listed would need to be preceeded by a Drupal cache flush (I don't like the cache inside a database backup). Typically this would've taken me about 5 minutes to complete, well my friends, no longer is that the case. Behold the power of Drush (once you are ssh'd in and navigated to your drupal root directory)
Drupal 6 and 7
drush cc drush sql-dump > ~/my-sql-dump-file-name.sql
drush cr drush sql-dump > ~/my-sql-dump-file-name.sql
Those two commands clear all the Drupal caches and then dump the sql database to a file in your home directory. Awesome sauce!
Download Database (optional)
If the sql dump file is on a remote server, you can use this terminal command to download a copy of the sql file:
scp email@example.com:~/my-sql-dump-file-name.sql ~/Desktop/
You can use this command to import the sql dump back into your drupal database.
drush sql-drop drush sql-cli < ~/my-sql-dump-file-name.sql
A Drush-Less Approach
If you don't have Drush setup (which you should have setup if you want to be cool), you can check out this article to make a backup copy of your Drupal's MySQL database from a terminal: Drupal - How to Make a Backup Copy of MySQL Database