On-Demand MySQL Backup Shell Script

FREE THOUGHT · FREE SOFTWARE · FREE WORLD

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 "$cG\n\n"
echo '  _____________________________________________________________________ '
echo ' |                                                                     |'
echo ' |                                                                     |'
echo ' |                                                                     |'
echo " |                       SiteBack v.1.1 ${MYSITE}"
echo ' |                                                                     |'
echo ' |                                                                     |'
echo ' |                                                                     |'
echo '  _____________________________________________________________________ '
echo -e "$cG\n\n"
tput sgr0
sleep 3
}
 
# pfooter #
function pfooter {
tput sgr0
echo -e "$cG"
echo '  _____________________________________________________________________ '
echo ' |                                                                     |'
echo ' |                    SiteBack COMPLETED SUCCESSFULLY                  |'
echo '  _____________________________________________________________________ '
echo -e "\n\n\n"
tput sgr0
cd ${OLDPWD}
}
 
#======================  MAIN  =================================
 
pheader
 
echo -e "$cY\n   @@@ 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 "$cG\n   [ DONE ]\n\n"
sleep 2
 
echo -e "$cY\n   @@@ SQL BACKUP IN PROGRESS... "
sleep 5
 
tput sgr0
mysqldump --opt -uusername -ppassword -h mysqlhost.com database > ${MYSQLBNAME}
echo -e "$cG\n   [ DONE ]\n\n"
 
pfooter
sleep 5
exit 0;

«
»

Skip to Comments

Add Your Opinion

Reader Comments

  1. Manu ~

    This is real Cool Stuff

    Thanks Man

  2. Lee ~

    This seems a little over the top for the job required, I have a script which I’ve setup through dreamhost’s cron manager to run daily, and dump all databases, here is what it is:

    #!/bin/bash
     
    DATE1=`date | awk '{print $3}'`
    DATE2=`date | awk '{print $2}'`
    DATE3=`date | awk '{print $6}'`
     
    THEDATE="${DATE1}${DATE2}${DATE3}"
     
    ########################### CONFIGURATION START
     
    ORIGDIR="/home/stuffatschool/mysql/"  # original directory to put
     
    MYSQLHOST=dbhost  #MySQL Host
    MYSQLUSER=dbuser      # MySQL user for dump
    MYSQLPASS=dbpass    # MySQL password for dump
     
    ########################### CONFIGURATION DONE
    cd ${ORIGDIR} # chdir to the right dir
     
    mysqldump -u ${MYSQLUSER} -p${MYSQLPASS} -h${MYSQLHOST} -A > "./MySQL_${THEDATE}.sql" # dump db
  3. Jon ~

    this sounds intersting and practical. nice blog btw.

    i understand it’s a shell file but how is it used … ‘where do you put it on server, or where do you save it’? will you add documentation for this, readme?

    thanks for great tutorials!


It's very simple - you read the protocol and write the code. -Bill Joy

HTML | DCMI | GRDDL | XOXO | XDMP | XFN | DOM | XML | XHTML 1.1 Strict | CSS 2.1 | W3C | TLDP | WAI | DISA | ICSI | GIAC | SANS RR | GHOST | DEFCON | NIST | DHS CYBER | NIST

↑ TOPExcept where otherwise noted, content on this site is licensed under a Creative Commons Attribution 3.0 License, just credit with a link.
This site is not supported or endorsed by The Apache Software Foundation (ASF). All software and documentation produced by The ASF is licensed. "Apache" is a trademark of The ASF. HTTPD based on NCSA HTTPd

Site Map | Contact Webmaster | Email AskApache | Glossary | License and Disclaimer | Terms of Service