Backing up the CMS Database

Member for

1 year 5 months
Submitted by AlReaud on Wed, 01/18/2012 - 19:51

Revised: 2016-09-12, Al Reaud, Happy Cat Technologies

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.

The shell script is available here for your convenience, demo.backup_db.sh.txt and demo.restore_db.sh.txt.


backup_db.sh
#! /bin/bash
#
# DATABASE BACKUP SCRIPT V1.2.20160912
# (C) 2010-2016, Happy Cat Technologies, Al Reaud
# Insure that this script IS NOT visible outside of the hosting account as it contains critical security information.

echo ""
echo "DATABASE BACKUP SCRIPT V1.2.20160912"
echo "(C) 2010-2016, Happy Cat Technologies, Al Reaud"
echo ""

# Configuration
_RED=`tput setaf 1`
_GREEN=`tput setaf 2`
_RESET=`tput sgr0`
_dbname='database name, may be the database user name also' # (e.g.: _dbname='drupaldb')
_dbuser='database user name, may be the database name also' # (e.g.: _dbuser='drupaluser')
_dbhostlocal='localhost'    # (domain name of the local database host, or IP address. e.g. _dbhostlocal='localhost')
_dbhostremote='site.db.username.hostingprovider.com' # ( domain name of the remote database host, or IP address. e.g.: _dbremotehost='site.db.username.hostingprovider.com')
_dbpwd='database password'
_docrootlocal='html'     # (e.g.: _docroot=public_html)
_docrootremote='html'
_sitenamelocal='sitename.test_backup-'        # (site names for file name prefixes. e.g.: _sitenamelocal='sitename.test_backup-' for the local site)
_sitenameremote='sitename_backup-'
# Include trailing slash, /, for _wwwbaselocal and _wwwbaseremote.
_wwwbaselocal='/var/www/site.directory/'     # (directory on the local host where the site document root is. e.g.: _wwwbaselocal='/var/www/site.directory/')
_wwwbaseremote='/var/chroot/username/'        # (directory on the remote host where the site document root is. e.g.: _wwwbaseremote='/var/chroot/username/')

# This first test below checks whether we are on the local server or on the production server. In the document root of the local server, one creates an empty file called testing.server, such as with the bash command "touch testing.server" and then locks it read only.
if [ -e $_wwwbaselocal$_docrootlocal/testing.server ]; then
  _dbhost=$_dbhostlocal
  # Default Output File Base Name
  _backupbasename=$_sitenamelocal
else
  _dbhost=$_dbhostremote
  # Default Output File Base Name
  _backupbasename=$_sitenameremote;
fi
# Form rest of name as a time stamp.
_datestamp=`date +'%Y%m%d-%H%M'`
_backupname=$_backupbasename$_datestamp.sql

echo "Backing up $_dbname to $_backupname..."
mysqldump -h $_dbhost -u $_dbuser -p$_dbpwd $_dbname > ./$_backupname
if [ $? -eq 0 ]; then
    echo "${_GREEN}DONE!${_RESET}"
else
    echo "{_RED}FAILED, DATABASE BACKUP DID NOT COMPLETE CORRECTLY!${_RESET}"
fi

 



restore_db.sh
#! /bin/sh
#
# DATABASE RESTORE SCRIPT V1.2.20160912
# (C) 2010-2016, Happy Cat Technologies, Al Reaud
# Insure that this script IS NOT visible outside of the hosting account
# as it contains critical security information.

echo ""
echo "DATABASE RESTORE SCRIPT V1.2.20160912"
echo "(C) 2010-2016, Happy Cat Technologies, Al Reaud"
echo ""

# Configuration
_RED=`tput setaf 1`
_GREEN=`tput setaf 2`
_RESET=`tput sgr0`
_dbname='database name, may be the database user name also' # (e.g.: _dbname='drupaldb')
_dbuser='database user name, may be the database name also' # (e.g.: _dbuser='drupaluser')
_dbhostlocal='localhost'    # (domain name of the local database host, or IP address. e.g. _dbhostlocal='localhost')
_dbhostremote='site.db.username.hostingprovider.com' # ( domain name of the remote database host, or IP address. e.g.: _dbremotehost='site.db.username.hostingprovider.com')
_dbpwd=_dbpwd='database password'
_docrootlocal='html'         # (e.g.: _docroot=public_html)
_docrootremote='html'
# Include trailing slash, /, for _wwwbaselocal and _wwwbaseremote.
_wwwbaselocal='/var/www/site.directory/'     # (directory on the local host where the site document root is. e.g.: _wwwbaselocal='/var/www/site.directory/')
_wwwbaseremote='/var/chroot/username/'        # (directory on the remote host where the site document root is. e.g.: _wwwbaseremote='/var/chroot/username/')

# Make sure a filename was specified!
if [ -z $1 ]; then
    echo "${_RED}Unable to continue, no restore source file specified.${_RESET}"
    echo "Usuage: ./restore_db.sh restore_filename.sql"
    exit 1
fi

# Make sure the file is there and isn't empty, but we can't check that it is actually SQL.
if [ -e $1 ]; then
    if [ ! -s $1 ]; then
        echo "${_RED}Unable to continue, restore source file${_RESET} $1 ${_RED}is empty.${_RESET}"
        echo "Usuage: ./restore_db.sh restore_filename.sql"
        exit 1    
    fi
else
    echo "${_RED}Unable to continue, restore source file${_RESET} $1 ${_RED}doesn't exist.${_RESET}"
    echo "Usuage: ./restore_db.sh restore_filename.sql"
    exit 1
fi

# Make sure we 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 _response
if [ "$_response" != "y" ]; then
    echo "${_GREEN}User quit!${_RESET}"
    exit 3
fi
echo ""

# This first test below checks whether we are on the local server or on
# the production server. In the document root of the local server, one
# creates an empty file called testing.server, such as with the bash
# command "touch testing.server" and then locks it read only.
if [ -e $_wwwbaselocal$_docrootlocal/testing.server ]; then
  _dbhost=$_dbhostlocal
  # Default Output File Base Name
  _backupbasename=$_sitenamelocal
else
  _dbhost=$_dbhostremote
  # Default Output File Base Name
  _backupbasename=$_sitenameremote;
fi

echo "RESTORING $_dbname from $1 ..."
# Restore the database.
mysql -u$_dbuser -p$_dbpwd $_dbname < $1
if [ $? -eq 0 ]; then
    echo "${_GREEN}DONE RESTORING DATABASE!${_RESET}"
else
    echo "${_RED}AN ERROR OCCURRED AND THE DATABASE WAS NOT RESTORED!${_RESET}"
fi

Add new comment

The content of this field is kept private and will not be shown publicly.

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol type start> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id> <u> <s> <sup> <sub> <hr>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
Image CAPTCHA
Enter the characters shown in the image.