Quickly resetting a local MySQL database from the command line [Updated]

[Update: And, as quickly as I finished writing this post, I thought to myself, "surely, this would be a good thing to have drush do out-of-the-box. And... it already does, making my work on this shell script null and void. I present to you: drush sql-drop! Oh, well.]

When I'm creating or updating an installation profile/distribution for Drupal, I need to reinstall Drupal over and over again. Doing this requires a few simple steps: drop/recreate the database (or drop all db tables), then drush site-install (shortcut: si) with the proper arguments to install the site again.

In the past, I've often had Sequel Pro running in the background on my Mac, and I'd select all the database tables, right-click, choose 'Delete Tables', then have to click again on a button to confirm the deletion. This took maybe 10-20 seconds, depending on whether I already had Sequel Pro running, and how good my mouse muscles were working.

I created a simple shell script that works with MAMP/MAMP Pro on the Mac (but can easily be modified to work in other environments by changing a few variables), which simply drops all tables for a given database:

#!/bin/bash
#
# Drop all tables from a given database.
#

# Some variables.
MYSQL=/Applications/MAMP/Library/bin/mysql
AWK=$(which awk)
GREP=$(which grep)
USER="valid-username-here"
PASSWORD="your-password-here"

# Database (argument provided by user).
DATABASE="$1"

# Require the database argument.
[ $# -eq 0 ] && {
  echo "Please specify a valid MySQL database: $0 [database_goes_here]" ;
  exit 1;
}

# Drop the given database with mysql on the commind line.
TABLES=$($MYSQL -u $USER -p$PASSWORD $DATABASE -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables')
for TABLE in $TABLES
do
  # echo "Deleting $TABLE table from $DATABASE..."
  $MYSQL -u $USER -p$PASSWORD $DATABASE -e "DROP TABLE $TABLE"
done

I named the script wipe-db.sh, and you can call it like so: $ /path/to/wipe-db.sh database-name. I added a symlink to the script inside my /usr/local/bin folder so I can just type in 'wipe-db' in the Terminal instead of entering the full path. To add the symlink:

$ ln -s /path/to/wipe-db.sh /usr/local/bin/wipe-db

Now I can wipe the database tables within a couple seconds, since I always have Terminal running, and I never have to reach for the mouse!

Further reading

drupal
drush
drupal planet
mysql
database
shell

Comments

Thanks for sharing, I didn't know about the drush command either... here's the one-liner I've been using:

mysql -u$DBUSER -p$DBPASS -Nse 'show tables' $DBNAME | while read table; do mysql -u$DBUSER -p$DBPASS -e "drop table $table" $DBNAME; done

I think this is at least the third time I've done something I thought was pretty clever, only to find that drush already did it for me, I just hadn't seen the command for it yet.

I've always done something like

drop database DB_NAME;
create database DB_NAME;

I think this is the same effect and faster. Thoughts?

Sometimes I have custom user permissions set up on a database, so clearing the tables works better for that; however, a drop and re-create would be much faster, depending on the number of tables (on a standard D7 site, it takes about 3 sec for me to run my script on it).

You should check out TablePlus as it's a pretty good alternative to Sequel Pro, plus multiple drivers support.