Database dump and load bash scripts specifically setup to skip common extraneous data from a Drupal DB. This is beta at the moment, I need to fix the "PROCEED" check. For DB load "pv" is used and should be installed which gives you a progress meter.

db-dump.sh

#!/bin/bash

# Simple dump script. Requires a user .my.cnf file that includes the basic default
# username, password and host.

echo 'DB Name to Dump:'
read DBNAME

###################################
# Setup vars
DATE=$(date +%Y-%m-%d)

MYSQL=$(which mysql)
MYSQLDUMP=$(which mysqldump)

###################################

echo "Run dump of '$DBNAME' to '$HOME' (y/n)?"
read PROCEED

if [ $PROCEED = n ] || [ $PROCEED = no ] || [ $PROCEED = '' ]; then
  echo 'Terminating by your request...'
  exit 0
elif [ $PROCEED = y ] || [ $PROCEED = yes ]; then

  # Dump the table schema first.
  echo "Start: $(date +%c)"
  echo 'Dumping schema...'

  # Dump table structure and create a table list to work with.
  TABLES=`$MYSQL --skip-column-names -e 'SHOW TABLES' $DBNAME;`
  OPTIONS=' --opt --single-transaction --no-data '
  $MYSQLDUMP $OPTIONS $DBNAME $TABLES > $HOME/$DBNAME-$DATE.sql

  # Dump data but skip cache and other temporary table data.
  echo 'Dumping data...'
  TABLES2=`echo "$TABLES" | grep -Ev "^(accesslog|cache|cache_.*|flood|semaphore|sessions|votingapi_cache|watchdog)$"`
  OPTIONS2=' --complete-insert --disable-keys --single-transaction --no-create-info '
  $MYSQLDUMP $OPTIONS2 $DBNAME $TABLES2 >> $HOME/$DBNAME-$DATE.sql

  # Gzip everything
  echo 'Compressing dump...'
  gzip -v $HOME/$DBNAME-$DATE.sql;

  echo 'Backup complete, '
  echo "$HOME/$DBNAME-$DATE.sql.gz"
  echo "End: $(date +%c)"
else
  echo "Unknown input for '$PROCEED'"
  echo 'Terminating...'
  exit 0
fi

db-load.sh

#!/bin/bash

# Simple db load script. Requires a user .my.cnf file that includes the basic default
# username, password and host.
# Script user 'pv' which gives you a progress bar.

echo '!!! CAUTION, THIS SCRIPT WILL OVERRITE THE DB!!!'
echo 'DB Name to load dump into:'
read DBNAME

MYSQL=$(which mysql)

# Validate the database name
if [[ ! $($MYSQL -e 'SHOW DATABASES' | grep "^${DBNAME}$") ]]; then
  echo "Database '${DBNAME}' doesn't exist. Please verify and try again."
  echo 'Terminating...'
  exit 0
fi

echo 'Path to your dump file:'
read DBDUMP

# Validate the dump file
if [[ ! -r "$DBDUMP" ]]; then
  echo "The file '$DBDUMP' doesn't exist!"
  echo 'Terminating...'
  exit 1
fi

echo "Load '$DBDUMP' into '$DBNAME' database (y/n)?"
read PROCEED

if [ ${PROCEED} = n ] || [ ${PROCEED} = no ] || [ ${PROCEED} = '' ]; then
  echo 'Terminating by your request...'
  exit 2

elif [ ${PROCEED} = y ] || [ ${PROCEED} = yes ]; then
  echo 'Start: ' $(date +"%c")
  pv $DBDUMP | gunzip | $MYSQL $DBNAME
  echo 'End: ' $(date +"%c")

else
  echo "Unknown input for '$PROCEED'"
  echo 'Terminating...'
  exit 3
fi

db-prod-dump

#!/bin/bash

# Simple dump script. Requires a user .my.cnf file that includes the basic default
# username, password and host plus an additional option group for "client_prod" with
# options to connect to the prod server using: --defaults-group-suffix=_prod

echo 'DB Name to Dump:'
read DBNAME

###################################
# Setup vars
DATE=$(date +%Y-%m-%d)

MYSQL=$(which mysql)
MYSQLDUMP=$(which mysqldump)

###################################

echo "Run dump of '$DBNAME' to '$HOME' (y/n)?"
read PROCEED

if [ $PROCEED = n ] || [ $PROCEED = no ] || [ $PROCEED = '' ]; then
  echo 'Terminating by your request...'
  exit 0
elif [ $PROCEED = y ] || [ $PROCEED = yes ]; then

  # Dump the table schema first.
  echo "Start: $(date +%c)"
  echo 'Dumping schema...'

  # Dump table structure and create a table list to work with.
  TABLES=`$MYSQL --defaults-group-suffix=_prod_v2 --skip-column-names -e 'SHOW TABLES' $DBNAME;`
  OPTIONS=' --defaults-group-suffix=_prod_v2 --opt --single-transaction --no-data '
  touch $HOME/$DBNAME-$DATE.sql
  $MYSQLDUMP $OPTIONS $DBNAME $TABLES > $HOME/$DBNAME-$DATE.sql

  # Dump data but skip cache and other temporary table data.
  echo 'Dumping data...'
  TABLES2=`echo "$TABLES" | grep -Ev "^(accesslog|cache|cache_.*|flood|semaphore|sessions|votingapi_cache|watchdog)$"`
  OPTIONS2=' --defaults-group-suffix=_prod_v2 --complete-insert --disable-keys --single-transaction --no-create-info '
  $MYSQLDUMP $OPTIONS2 $DBNAME $TABLES2 >> $HOME/$DBNAME-$DATE.sql

  # Gzip everything
  echo 'Compressing dump...'
  gzip -v $HOME/$DBNAME-$DATE.sql;

  echo 'Backup complete, '
  echo "$HOME/$DBNAME-$DATE.sql.gz"
  echo "End: $(date +%c)"
else
  echo "Unknown input for '$PROCEED'"
  echo 'Terminating...'
  exit 0
fi