Drush is the ultimate tool and companion for Drupal and it does a great job in situations where we want to import or export our Drupal database with sql-cli and sql-dump commands.
Below is the detail of how to use Drush to import and export the database.
Run "drush sql-cli" to Import Drupal database
Drush command "drush sql-cli" can be used to import a database from an SQL dump file to a Drupal database.
Make sure to use the sql-drop command to drop the current database first and then run the sql-cli command to import data to Drupal's current database.
drush sql-drop -y
drush sql-cli < ~/path/to/db-file.sql
Run "drush sql-dump" to Export Drupal database
The best way to export or download the Drupal database is first to rebuild/clear the cache and then export the database with the "drush sql-dump" command.
1. First clear the cache
To export the Drupal database, the first step is to rebuild your cache before the database export
drush cr // For Drupal 8 and above
drush cc // For Drupal 6 & 7
Clearing the cache first will empty the cache tables in the database and significantly decrease the database size.
2. Now export the database with sql-dump command
Now run the drush sql-dump command with the destination argument to export the Drupal database to the desired destination. This will dump the database into a gzip file without cache table content.
drush sql-dump --gzip --result-file=/path/to/db-file.sql
More resources:
- Drush sql-dump command - https://drushcommands.com/drush-8x/sql/sql-dump/
- Drush sql-cli command - https://drushcommands.com/drush-8x/sql/sql-cli/
- Drush sql-drop command - https://drushcommands.com/drush-8x/sql/sql-drop/