MySQL Backup and Restore Scripts

Couple of scripts for backing up and restoring a MySQL database. May be unnecessarily complicated in some aspects, but it does the job for me. Decided to post it up here mainly for self-reference.

Backup
#!/bin/ksh

# Nazim Merchant
# Last Updated: 14 Feb 2011

# Declaration of Paths
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`

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@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}

Restore
#!/bin/ksh

# Nazim Merchant
# Last Updated: 14 Feb 2011

# Declaration of Paths
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)"

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

Run sqlplus from Textpad

For people who use the product "Textpad". If you wish to run sqlplus scripts direct through Textpad, you can set it up as follows:

In Textpad navigate to:

Configure > Preferences > Tools

Add Program

Browse and select sqlplus

OK


Then navigate to

Configure > Preferences > Tools > sqlplus

and in the right hand pane, in the field marked Parameters add:

/nolog @$File

This will essentially invoke sqlplus /nolog and if the SQL script you have opened in Textpad contains the line connect username/password@database you can use the shortcut key (in my case ctrl-1) to execute the SQL through Textpad.

Foreign Archive Logs

A colleague of mine recently noticed our Flash Recovery Area listed some FOREIGN ARCHIVED LOGS as taking up space in the flash recovery area. We knew from a recent implementation that these were left-overs from a previous incarnation of our database and we needed to remove them. We achieved this task the following way:

Determined the space being used:

SQL> select * from SYS.V_$FLASH_RECOVERY_AREA_USAGE;

Crosschecked the foreign archive logs using rman

RMAN> crosscheck foreign archivelog all;

Removed the foreign archive logs using rman

RMAN> delete expired foreign archivelog all;

Check Bind Variables

Here's a way you can check on what bind variable values have been parsed in a query.

SELECT a.sql_id
         ,  a.sql_text
         ,  b.name
         ,  b.value_string
    from v$sql a
         ,  v$sql_bind_capture b
where a.sql_id = b.sql_id
  and a.sql_id=&SQLID;

Convert Physical Standby to Snapshot Standby

Just some high level steps for converting a 11g Physical Standby Database to a Standby Snapshot Database and back again.

PHYSICAL STANDBY TO SNAPSHOT STANDBY
Turn on Flashback
SQL> ALTER DATABASE FLASHBACK ON;
Stop Redo Log Apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Convert Physical Standby to Snapshot Standby
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Open the Database in Read/Write Mode
SQL> ALTER DATABASE OPEN;
Take a note of the Snapshot if you wish
SQL> SELECT name, scn, time FROM v$restore_point;
SNAPSHOT STANDBY TO PHYSICAL STANDBY
Shutdown the Database
SQL> SHUTDOWN IMMEDIATE;
Mount the Database
SQL> STARTUP MOUNT;
Convert Snapshot Standby to Physical Standby
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Shutdown the Database
SQL> SHUTDOWN IMMEDIATE;
Startup in Nomount mode
SQL> STARTUP NOMOUNT;
Mount the Physical Standby Database
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Start Redo Log Apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;
SQL>exit

Available db_recovery_file_dest

A gr8 script picked up somewhere along the way to check the available amount of db_recovery_file_dest space.

select name,
  floor(space_limit/1024/1024) "Size_MB",
  ceil(space_used/1024/1024) "Used_MB",
  floor(space_limit/1024/1024) - ceil(space_used/1024/1024) "Available_MB",
  round(ceil(space_used/1024/1024) / floor(space_limit/1024/1024) * 100)  || '%' "Percent Used"
  from v$recovery_file_dest
  order by name;

chmod

Only just found out, instead of working out the numeric settings of chmod, the following conventions can be "literally" used:

chmod u+rwx
chmod g+rwx
chmod o+rwx

chmod u-rwx
chmod g-rwx
chmod o-rwx