Scrubbing Drupal databases for development

A couple of weeks ago I delivered a webinar on how to manage the process of moving Drupal websites from development to staging and production. The presentation gives an overview of the process including a few key points for the key pieces. Here are the key points:

  • Manage sites in code (keep configuration and updates in code)
  • Have an effective version control system
  • Test, test and run more Tests

In the presentation I covered a pair of scripts that form the basis of creating a scrubbed database for use on development machines. This is a good practice for several reasons including making sure that a development server doesn't send emails or data to Google Analytics for example.

The first piece is a shell script like this:

# Use as ./ local_db_user local_db_pass dump_file.gz

# Creates a scrubbed version of a database dump using the local database defined below as the intermediary.
# note: the dump file will be gzip compressed, as will te resulting file.


function db_query() {
    mysql -u${local_user} -p${local_pass} -e "$sql" ${local_db}


mysql -u${1} -p${2} -e "DROP DATABASE IF EXISTS $local_db"
mysql -u${1} -p${2} -e "CREATE DATABASE $local_db"
gunzip -c $dump_file | mysql -u${1} -p${2} $local_db

mysql -u${1} -p${2} $local_db < scrub.sql
mysql -u${1} -p${2} $local_db < live_to_dev_scrub.sql

# Here we make sure that all the tables are InnoDB.

mysql -u${1} -p${2} -e "SHOW TABLES" $local_db \
  | grep --invert-match '^Tables' \
  | sed -e 's/\(.*\)/ALTER TABLE `\1` ENGINE = innodb;/' \
  | mysql -u${1} -p${2} $local_db

# Dump the scrubbed version and drop the temporary DB.

mysqldump -u${1} -p${2} $local_db | gzip > $new_dump_file
mysql -u${1} -p${2} -e "DROP DATABASE IF EXISTS $local_db"

If you look through what this shell script does it dumps a copy of the database and creates a new database from that dump. In the new, temporary, copy of the database transformations are performed to clean up the data. The cleaning actually happens in this line:

Kicking off Drupalcon with APIs

Drupalcon is off to a great start. Yesterday afternoon and this morning we met all kinds of great Drupal folks while passing out the free Acquia t-shirts and sharing the news about several recent releases. If you haven't had the chance to check out the new Acquia Drupal Stack Installers. The installers bring a Windows and Mac OS X installer to the desktop that install and setup all of the components necessary (Apache, MySQL, PHP and Acquia Drupal) to get started running a Drupal site.

Last day to get Acquia subscriptions for free

Sometimes we get so busy doing the good work and solving problems for users that we forget to step back and note some milestones. Tomorrow marks one of those milestones so I took the time to post to Twitter but hadn't thought to blog about it. Fortunately fellow Drupal twitterer Boris Mann reminded me to take a blog post about it.

As the last hours of 2008 wind down it's your last chance to get a free Acquia Network community subscription.

In addition to support in the Acquia forums and access to our site monitoring and cron services there are cool new features coming in the next year. If you're still not sure you can always take a look at Boris Mann's upgrade story while thinking about those sites that need to be upgraded and where you might get some extra help.

Using Acquia Drupal as an image processor?

pipeline_in_trench.jpgCatching up on some blog reading today I picked up on a thread Dave Winer's been writing about. He's using ImageMagick to resize images and produce thumbnails. Throughout the thread I kept thinking this sounds a lot like a standalone version of the Drupal imagecache module. Perhaps, I thought, the answer could be setting up a Drupal site to do the conversion. Nah that's too much I said to myself.

The magical hour, Acquia launches Acquia Drupal and Acquia Network

Like many in the community my first exposure to Acquia came back in the first part of the year. It was an exciting time for the Drupal community and what we learned at Drupalcon in Boston suggested many great things to come. At the time I was running an independent Drupal development shop. The news that an organization like Acquia was going to come to the Drupal ecosystem was great news. Some had early concerns that Acquia might compete in the site development space. These fears turned out to be ill-founded and the entry of Acquia into the marketspace a great boon for small shops like ours. Now the future held the promise that we could continue to work on the development we were doing and help customers build great sites, while having a solid organization we could hand off day-to-day support on the back end.

What's Next? Acquia and Drupal social publishing

A year ago we were about to set out on our family version of Travels with Charlie. At the time our thinking was that we would be looking to move out of Southern Nevada in a couple of years. On our return to Southern Nevada we talked and chewed on the options. Finding no great reason to wait we packed up and moved north. Along with the move came a change in my focus. I made a great move from the world of running an information technology department to running a small Drupal shop. Along the way I've had many great experiences. From traveling to Drupal camps to having visited Boston for the first time for Drupalcon last spring.

Subscribe to Acquia