Backing up a Drupal Database: Redux

Back in November last year, I wrote a script which handled backing up a drupal database. There were quite a few comments and I've taken some on board and developed the script on a little further to be more "generic".

One of the main complaints/suggestions about my previous snippet was the hard coded nature of it. The follow script offers far more configuration through the command line itself.

#!/bin/bash

# Define the options for this command
OPTION_SPEC="help,database:,file:,user:,password:,exclude:,exclude-drupal-tables,prefix:"
PARSED_OPTIONS=$(getopt -n "$0" -a -o h --long $OPTION_SPEC -- "$@")
OPTIONS_RET=$?
eval set -- "$PARSED_OPTIONS"

# Define an array of tables to be exluded
DBEXCLUDE=( )

# Define an array of table prefixes
DBPREFIX=( )



# Define a usage help function
usage() {
  cat < $DBFILE



# Define the username, if it's not already set
if [ ! -n "$DBUSER" ]
then
  DBUSER=""
else
  DBUSER="--user=${DBUSER}"
fi


# Define the password, if it's not already set
if [ ! -n "$DBPASS" ]
then
  DBPASS=""
else
  DBPASS="--password=${DBPASS}"
fi


# Define the default 'structure only' tables
DBEXCLUDE_TABLES=""
ELEMENT_COUNT=${#DBEXCLUDE[@]}
if [ "$ELEMENT_COUNT" -gt 0 ]
then
  INDEX=0

  while [ "$INDEX" -lt "$ELEMENT_COUNT" ]
  do
    DBEXCLUDE_TABLES="${DBEXCLUDE_TABLES}|${DBEXCLUDE[$INDEX]}"
    let "INDEX = $INDEX + 1"
  done

  DBEXCLUDE_TABLES="(${DBEXCLUDE_TABLES:1})"
fi

# Define the structure only optional table prefixes
DBPREFIX_TABLES=""
ELEMENT_COUNT=${#DBPREFIX[@]}
if [ "$ELEMENT_COUNT" -gt 0 ]
then
  INDEX=0

  while [ "$INDEX" -lt "$ELEMENT_COUNT" ]
  do
    DBPREFIX_TABLES="${DBPREFIX_TABLES}|${DBPREFIX[$INDEX]}"
    let "INDEX = $INDEX + 1"
  done

  DBPREFIX_TABLES="(${DBPREFIX_TABLES:1})?"
fi

# Define the structure only regex
STRUCTURE_ONLY="/^${DBPREFIX_TABLES}${DBEXCLUDE_TABLES}$/"

# Get the tables from the database
TABLES=`mysql ${DBUSER} ${DBPASS} --batch --skip-column-names -e 'show tables;' ${DB}`

if [[ $? > 0 ]]
then
  echo "Exiting for some kind of error... $?"
  exit 1
fi


# Status message
echo "  Starting dump of ${DB}"


# Loop over the tables
for T in $TABLES; do
  # Test if the table matches the 'structure only' regex
  RESULT=`echo "$T" | gawk "$STRUCTURE_ONLY"`

  # if a match...
  if [ $RESULT ]
  then
    echo "    STRUCTURE : ${T}"
    NODATA="--no-data"
  else
    echo "    FULL      : ${T}"
    NODATA=""
  fi

  # Dump the table onto the end of the DBFILE with the data or no data option
  mysqldump ${DBUSER} ${DBPASS} --opt --skip-comments ${NODATA} ${DB} ${T} >> $DBFILE
done


# Finish Message
echo "  Done"
exit 0

Essentially this is the same script as in my previous post, however I've added functionality to provide a custom username, password, database, file, exclude (structure only) and optional-table-prefix...

Comments & suggestions welcome :)

Oh - one final thing… You can find the file for download in my Drupal Sandbox…

http://cvs.drupal.org/viewvc.py/drupal/contributions/sandbox/nicholasThompson/drupaldump/