Backing up the CMS Database

The CMS database is the heart of any content management system. It's loss or damage will result in the loss and or damage of all of your hard work, and that of your commentators, posters, and contributors. Below, are two scripts that complement each other, one backs up the database into a SQL file, the other restores it from a SQL file.

This script also allows porting between a testing server and the on-line site. In the script is a test for a folder called testing.server, discussed in the first page of this series, which differentiates between the server on-line and the testing server.

backup_db.sh
#! /bin/bash
# DATABASE BACKUP SCRIPT
# (C) 2010-2012 Happy Cat Technologies
# Default TAR Output File Base Name
# Insure that this script IS NOT visible outside of the hosting account as
# it contains critical security information.
#
# This first test below checks whether we are on the testing server or on the
# on-line server. In the document root of the testing server, one creates a
# directory called testing.server, and then locks it read only.
if [ -d 'path to document root'/testing.server ]; then
  dbhost='localhost'
  # Default Output File Base Name
  backupbasename='sitename.test_backup-'
else
  dbhost='database hostname, not an ip address preferably'
  # Default Output File Base Name
  backupbasename='sitename_backup-';
fi
# Form rest of name as a time stamp.
datestamp=`date +'%Y-%m-%d-%R'`
# Convert colon so that system won't complain.
datestamp=`echo $datestamp | sed "s/://"`
backupname=$backupbasename$datestamp.sql

# Note that sitename and dbname may be the same.
mysqldump -h $dbhost -u sitename -pdbpassword dbname > ./$backupname

restore_db.sh
#! /bin/sh
# DATABASE RESTORE BACKUP SCRIPT
# (C) 2010-2012, Happy Cat Technologies
# Insure that this script IS NOT visible outside of the hosting account as
# it contains critical security information.
#
if [ -z $1 ]; then
  echo "Unable to continue, no restore source file specified."
  echo "Usuage: ./restore_db.sh restore_filename"
  exit
fi
# Make sure you really want to do this!
echo "ARE YOU REALLY SURE YOU WANT TO RESTORE THE DATABASE FROM "$1
echo "Enter 'y' to Continue, anything else to quit."
read -n 1 response
if [ "$response" != "y" ]; then
  exit
fi
echo
# Configure constants.
dbpwd='database password'
dbname='database name, may also be the database user name'
dbuser='database user name, may also be the database name'
# This third test below checks whether we are on the testing server or on the
# onlines server. In the document root of the testing server, one creates a
# directory called testing.server, and then locks it read only.
if [ -d 'path to document root'/testing.server ]; then
  dbhost='localhost'
else
  dbhost='database hostname, not an ip address preferably'
fi

# Restore the database.
echo "RESTORING "$dbname" from "$1
mysql -u $dbuser -p$dbpwd $dbname < $1
if [ "$?" == 0 ]; then
  echo "DONE RESTORING DATABASE!"
else
  echo "AN ERROR OCCURRED AND THE DATABASE WAS NOT RESTORED! ERROR "$err
fi