Script to Backup and Restore a MySQL Database

Requirements

• Obtain a simple backup of a Production MySQL database.
• Send backup to TSM Tape Storage Device.
• Copy the files to a Non-Production MySQL database server.
• Restore/replace current Non-Production MySQL database, excluding the information_schema and mysql databases.

The Backup Script

#!/bin/ksh
# Nazim Merchant
# Last Updated: 14 Feb 2011

# Declaration of Paths of Binaries used by this script
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
GZIP="/bin/gzip"
MAIL="/bin/mail"

# Variable: Initial Directory
INIDIR="/backup/mysqldr"

# Variable: Get Date - Used to Create Directories for Backups by Date
DT=`date +%Y%m%d`

# Create Directory based on above variables
mkdir $INIDIR/$DT

# Destination Directory
DEST="$INIDIR/$DT"

# Restore Files
RESTFILE=mysqlrestore.sql
RESTFILE2=mysqlgunzip.ksh

# Log file
LOGFILE=mysqlbackup.log

# Mailing List
MAIL_LIST="nazim at nazimcricket.com"

# Acquire hostname
HOST="$(hostname)"

# Loop through the MySQL Databases
# Backup the Databases via mysqldump
# Validate the Backups
# Append the Hostname to the backups
# Compress the backups
# Append to Restore Scripts

echo "#MySQL Restore File $DT" > ${DEST}/$RESTFILE
echo "#MySQL Gunzip File $DT" > ${DEST}/$RESTFILE2
echo "#!/bin/ksh" >> ${DEST}/$RESTFILE2
echo "GZIP=/bin/gzip" >> ${DEST}/$RESTFILE2
echo "#Note: information_schema and mysql are commented out in the restore script" >> ${DEST}/$RESTFILE
echo "#Note: information_schema and mysql are commented out in the restore script" >> ${DEST}/$RESTFILE2
echo "#MySQL Log File $DT" > ${DEST}/$LOGFILE

SDTTM=`date '+%Y-%m-%d %H:%M'`

DBS="$($MYSQL -Bse 'show databases')"
for db in $DBS
do
  FILE="${DEST}/$db.$HOST.gz"

  echo "Backing Up: $FILE" >> ${DEST}/$LOGFILE
  $MYSQLDUMP $db | $GZIP -9 > $FILE
  validdb=$((validdb+$?))
  echo "Validating DB: $db = $?" >> ${DEST}/$LOGFILE

  if [[ $db == information_schema || $db == mysql ]]
  then
   echo "#drop database $db;" >> ${DEST}/$RESTFILE
   echo "#create database $db;" >> ${DEST}/$RESTFILE
   echo "#gunzip < \"$FILE\" | mysql $db;" >> ${DEST}/$RESTFILE2
   echo "#echo Restore $db = \$?" >> ${DEST}/$RESTFILE2
  else
   echo "drop database $db;" >> ${DEST}/$RESTFILE
   echo "create database $db;" >> ${DEST}/$RESTFILE
   echo "gunzip < \"$FILE\" | mysql $db;" >> ${DEST}/$RESTFILE2
   echo "echo Restore $db = \$?" >> ${DEST}/$RESTFILE2
  fi
done

#Validate gunzip files
for file in ${DEST}/*.gz
do
  gzip -t $file
  echo "Validating Zip: $file = $?" >> ${DEST}/$LOGFILE
  valid=$((valid+$?))
done
echo "Validation (0 = Success  n = Errors)" >> ${DEST}/$LOGFILE
EDTTM=`date '+%Y-%m-%d %H:%M'`

TSM_DESC=`hostname`_$DT

echo `date` "dsmc archive -des=\"${TSM_DESC}\" -subdir=yes -archmc=ARCH14D ${DEST}/ " >> ${DEST}/${LOGFILE}
dsmc archive -des="${TSM_DESC}" -subdir=yes -archmc=ARCH14D ${DEST}/
TSM_ERROR=$?
if [[ ${TSM_ERROR} != 0 ]]; then
  echo "dsmc command returned a non zero exit value of ${TSM_ERROR}" >> ${DEST}/${LOGFILE}
  cat ${DEST}/${LOGFILE} |grep ^ANS | grep 'E '
  if [[ $? = 0 ]]; then
    echo "ERROR: critical error in tsm backup." >> ${DEST}/${LOGFILE}
  else
    echo "WARNING: non-critical error in the tsm backup." >> ${DEST}/${LOGFILE}
  fi
fi

echo "" >> ${DEST}/${LOGFILE}
echo "to retrieve this backup use the following command:" >> ${DEST}/${LOGFILE}
echo "  # dsmc retrieve ${DEST}/ -subdir=yes -replace=no -des=\"${TSM_DESC}\"" >> ${DEST}/${LOGFILE}
echo ""  >> ${DEST}/${LOGFILE}

# Validity is the sum total of $? from both the Database and Gunzip Validation
validity=$validdb:$valid:${TSM_ERROR}

if [ $validity != 0:0:0 ]
then
  cat ${DEST}/$LOGFILE | mail -s "MYSQL - ERRORS:ExportDaily on MYSQL $HOST $SDTTM - $EDTTM" ${MAIL_LIST}
else
  cat ${DEST}/$LOGFILE | mail -s "MYSQL - SUCCESS:ExportDaily on MYSQL $HOST $SDTTM - $EDTTM" ${MAIL_LIST}
fi

echo "#Status of Backup $validity" >> ${DEST}/$RESTFILE
cp /etc/my.cnf ${DEST}/.


# Change ownership to user mysql of backup files.
chown -R mysql:mysql $INIDIR/$DT

# Transfer backup to DR server.
echo "Secure copy of backup started at `date`." >> ${DEST}/${LOGFILE}
sudo -u mysql scp -r $INIDIR/$DT mysql@swfudapb1:$INIDIR
echo "Secure copy of backup completed at `date`." >> ${DEST}/${LOGFILE}

The Restore Script

#!/bin/ksh
# Nazim Merchant
# Last Updated: 14 Feb 2011

# Declaration of Paths of Binaries used by this script
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
GZIP="/bin/gzip"
MAIL="/bin/mail"

# Initial Directory
INIDIR="/backup/mysqldr"

# Get Date
DT=`date +%Y%m%d`

# Destination Directory
DEST="$INIDIR/$DT"

# Restore Files
RESTFILE=mysqlrestore.sql
RESTFILE2=mysqlgunzip.ksh

# Restore Files Permissions
chmod 777 $DEST/$RESTFILE2

# Log file
LOGFILE=mysqlrestore.log

export LOGFILE DEST

date > ${DEST}/${LOGFILE}
echo "#" >> ${DEST}/${LOGFILE}
echo "#Validation (0 = Success n = Errors)" >> ${DEST}/${LOGFILE}
echo "#" >> $DEST/$LOGFILE

# Mailing List
MAIL_LIST="nazim@nazimcricket.com"

# Acquire hostname
HOST="$(hostname)"

# Check in mysqlrestore.sql if the Backup status was Successful
if (grep '#Status of Backup 0:0:0' ${DEST}/${RESTFILE}) then

mysql -v < ${DEST}/${RESTFILE} >> ${DEST}/${LOGFILE}
. ${DEST}/${RESTFILE2} >> ${DEST}/${LOGFILE}
cat ${DEST}/${LOGFILE} | mail -s "MYSQL - Restore of MYSQL DR on $HOST ${DT} Completed - Refer Status in Email" ${MAIL_LIST}

else

echo "Restore Not Attempted due to errors" >> ${DEST}/${LOGFILE}
cat ${DEST}/${LOGFILE} | mail -s "MYSQL - ERRORS:Restore of MYSQL DR on ${HOST} ${DT}" ${MAIL_LIST}

fi