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.

Benefits of Drush

Here are some key benefits of using Drush:

• Efficient Site Management: Drush enables quick execution of commands, allowing for efficient management of Drupal sites.
• Automation: Automates repetitive tasks like module installation, updates, and database migrations.
• Backup and Restore: Simplifies backup and restoration processes for site databases.
• Scripting Capabilities: Allows scripting of complex or custom workflows, enhancing productivity.
• Developer Friendly: Provides a command line interface tailored for developers, making it easier to work with Drupal’s backend without a GUI.
• Multi-site Management: Manages multiple Drupal sites simultaneously from one command line interface.
• Performance Optimization: Offers commands for cache clearing and other optimizations to improve site performance.

How To Configure Settings.php

Before you attempt to import a new database, make sure you have the database configurations set up 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 set up 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!