On-Demand MySQL Backup Shell Script

Recently I have experienced problems with my WordPress MySQL database becoming damaged resulting in all my Single Blog Posts returning 404 Not Found Errors. DreamHost does have automated MySQL backups that you can restore from with the panel, but oftentimes they were a few days old, so I would lose posts!

---

Solution

Instead of continually complaining to DreamHost support, which is an excellent group of intelligent and polite people, I decided to fix this problem on my own. The solution was a simple sh shell script that saves a backup of the database when run.

Note: I've progressed far beyond this simple shell script, to even simpler versions that can do much more!

The output is very pretty! :)

---

Loading Video

The Shell Script

#!/bin/sh

# SETTINGS
export MYSITES=`pwd -L`
export MYSITE=`basename ${MYSITES}`
export MYSITEBK=${MYSITE%%.*}${MYSITE#*.}
export MYSQLSITEBK=${MYSITE%%.*}sql
export MYUSER=dreamhostusername
export MYGROUP=dreamhostgroup

#======================  GLOBAL VARIABLES  =================================
# FILES #
export MYSQLFNAME=${MYSQLSITEBK}-`date +%mx%dx%y.tgz`
export MYSQLFNAMEX=${MYSQLSITEBK}-`date +%mx%dx%y-%Hx%M.tgz`
export MYSQLBNAME=${HOME}/backup/SITES/${MYSITE}/${MYSQLFNAME};
export MYSQLBNAMEX=${HOME}/backup/SITES/${MYSITE}/${MYSQLFNAMEX};
export MYBDIR=sites/${MYSITE}/

# COLORS #
cR='E[31;1m'
cG='E[32;1m'
cY='E[33;1m'


#======================  GLOBAL FUNCTIONS  =================================
# pheader #
function pheader {
cd ${HOME}
clear
tput sgr0
echo -e "$cGnn"
echo '  _____________________________________________________________________ '
echo ' |                                                                     |'
echo ' |                                                                     |'
echo ' |                                                                     |'
echo " |                       SiteBack v.1.1 ${MYSITE}"
echo ' |                                                                     |'
echo ' |                                                                     |'
echo ' |                                                                     |'
echo '  _____________________________________________________________________ '
echo -e "$cGnn"
tput sgr0
sleep 3
}


# pfooter #
function pfooter {
tput sgr0
echo -e "$cG"
echo '  _____________________________________________________________________ '
echo ' |                                                                     |'
echo ' |                    SiteBack COMPLETED SUCCESSFULLY                  |'
echo '  _____________________________________________________________________ '
echo -e "nnn"
tput sgr0
cd ${OLDPWD}
}

#======================  MAIN  =================================

pheader

echo -e "$cYn   @@@ CHECK FOR PRESENCE OF SQL BACKUP FILE"
if [ -e $MYSQLBNAME ] ; then
        echo -e "$cR      !!! SQL FILE FOUND.. CHANGING DESTINATION"
                MYSQLBNAME=${MYSQLBNAMEX}
else
        tput sgr0
fi
echo -e "$cGn   [ DONE ]nn"
sleep 2


echo -e "$cYn   @@@ SQL BACKUP IN PROGRESS... "
sleep 5

tput sgr0
mysqldump --opt -uusername -ppassword -h mysqlhost.com database > ${MYSQLBNAME}
echo -e "$cGn   [ DONE ]nn"


pfooter
sleep 5
exit 0;