Importing and Exporting Databases with Drush

A few weeks ago, I was pulled into a Non-Drupal project. As I was configuring the site to run on my local computer, I realized that I have been taking advantage of Drupal and Drush. I forgot how easy it was to import and export MYSQL databases for Drupal web development. If you’re a Drupal developer and are not using drush to import and export your databases, you should. It will save you time, and it’s easy.

 

How To Configure Settings.php

Before you attempt to import a new database, make sure you have the database configurations setup properly in settings.php. If you don’t have this specified, drush won’t know where to import your database to.

Database configuration setup in settings.php looks like this;

$databases = array (
 'default' =>
 array (
    'default' =>
    array (
     'database' => 'databasename',
     'username' => 'username',
     'password' => 'password',
     'host' => 'localhost',
     'port' => '',
     'driver' => 'mysql',
     'prefix' => '',
    ),
 ),
);

This will be the database that we import to. You can create this manually using phpmyadmin, or you can create it with drush using the following command:

drush sql-create --db-url="mysql://username:password@localhost/databasename"

Replace “username” and “password” with your proper credentials and replace “databasename” with whatever you want to call your new database.

 

Create a Database Folder

Typically when I setup a new project on my computer, I create a database folder just beneath my Drupal directory (for local development). This folder holds my database backups so I can easily revert to  a previous version if things go bad. It also helps make things more tidy and consistent.

 

How To Import & Export Databases Using Drush

Now that you have a bit of background information, here are the commands to import and export databases quickly using drush.

 

Step1: Before you import your database, make sure you clear out the old database tables using the following command:

drush sql-drop

This will drop all tables and make room for our new database.

 

Step 2: Next, run the following command:

drush sql-cli < /path/to/databasename.sql

Since I usually store my database folder just beneath my drupal directory, it usually looks something like this:

drush sql-cli < ../database/databasename.sql

 

Step 3: Now that we have that part mastered, you will probably want to periodically backup your database during development using the following command:

drush sql-dump > ../database/databasebackupname.sql

This will export your database to the database folder and will name it “databasebackup”.

These commands come in handy and I use them often. They will save you time, and maybe some sanity if you need to restore your development work from a backup.

Is your team working on a big Drupal build that you need help with? Our Cheeky Monkey Drupal Development team works with other agencies in the backend on a regular basis. Get in touch with us if you need some extra help!