MySQL Forums
Forum List  »  General

Linux script - multi_instance backup integrity report
Posted by: Phil Williams
Date: April 06, 2006 04:42PM

Does database reports, md5checks , ls reports, etc


common;
preparing_db_reports;
instance_status;
db_reports;
query_instance_for_variable_processing;
os_reports;

shutmysql;
mysqlstart;

instance_status;
checkndump;
shutmysql;
Now execute file a system backup.




#!/bin/ksh
# File Name :prebkup.sh
# Description :This script is used for pre cold backups
# Author :Phil Williams
# Version :1.0
# Released :
# Execution :As Root
# Company :
# Date :03/27/06
# Parameters :
# Calls :/etc/qdx/backup.d/mysql/{INSTANCE_NAME}.cfg --Set variaables for each instance
# Revision History:
# Version Details of Change
# ------- ------- -- ------
#
###############################################################################
echo "Starting mysql prebkup.sh `date`.."
DATE_STAMP="`date '+%b%d%y%H%M%S'`"
# temp location, will be /etc/qdx/backup.d/mysql
BK_CFG_DIR=/var/lib/mysql

# ====================
# Email common message
# ====================
SUBJ_COMMON="Warning: MySQL pre cold backup on `hostname` at `date`"


common()
{
# ===================================
# Check for common configuration file
# ===================================
if [ ! -f ${BK_CFG_DIR}/mysqlcommon.drv ]; then
echo "Missing ${BK_CFG_DIR}/mysqlcommon.drv file.."
else
. ${BK_CFG_DIR}/mysqlcommon.drv
fi

# =======================================
# Check for the backup base log directory
# =======================================
if [ -d ${BKUP_BASE_LOG_DIR} ] ; then
echo ""
else
MSG="The backup base log directory does not exist."
SUBJECT=${SUBJ_COMMON}
notify_status
fi

# ==============================
# Create today's dated directory
# ==============================
BKUP_TODAY_LOG=${BKUP_BASE_LOG_DIR}/${DATE_STAMP}
if [ -d ${BKUP_TODAY_LOG} ] ; then
MSG="Today's dated directory already exists. Please investigate."
SUBJECT=${SUBJ_COMMON}
notify_status
else
mkdir ${BKUP_TODAY_LOG}
fi

if [ -d ${BKUP_TODAY_LOG} ] ; then
echo ""
else
MSG="Today's dated directory could not be created."
SUBJECT=${SUBJ_COMMON}
notify_status
fi

ThisHost=`hostname`
COMMON_LOG_FILE=${BKUP_TODAY_LOG}/${ThisHost}_prebkup.log

echo "-----------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Starting prebkup cold backup on `hostname` at `date`." >> ${COMMON_LOG_FILE}
echo "-----------------------------------------------------------------" >> ${COMMON_LOG_FILE}
# =========================================
# Copy the flat files not Instance specific
# =========================================
echo "---------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Starting flat file not Instance specific copies on `hostname` at `date`." >> ${COMMON_LOG_FILE}
echo "---------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "---------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "my.cnf at on `hostname` at `date`." >> ${COMMON_LOG_FILE}
echo "---------------------------------------------------------------" >> ${COMMON_LOG_FILE}
cat /etc/my.cnf >> ${COMMON_LOG_FILE}
if [ $? -ne 0 ]; then
MSG="The my.cnf copy failed."
SUBJECT=${SUBJ_COMMON}
notify_status
fi

echo "---------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Linux users on `hostname` at `date`." >> ${COMMON_LOG_FILE}
echo "---------------------------------------------------------------" >> ${COMMON_LOG_FILE}
cat /etc/passwd >> ${COMMON_LOG_FILE}
if [ $? -ne 0 ]; then
MSG="The passwd file copy failed."
SUBJECT=${SUBJ_COMMON}
notify_status
fi

echo "---------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "MySQL cron jobs on `hostname` at `date`." >> ${COMMON_LOG_FILE}
echo "---------------------------------------------------------------" >> ${COMMON_LOG_FILE}
KIT=`ls ${TOOLS}/bin/*.sh`
for scrpt in ${KIT}
do
cat ${scrpt} >> ${COMMON_LOG_FILE}
if [ $? -ne 0 ]; then
MSG="DBA toolkit script copy failed."
SUBJECT=${SUBJ_COMMON}
notify_status
fi
done

# ===================================
# Capture currenct MySQL cron entries
# ===================================
echo "---------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "MySQL cron entries on `hostname` at `date`." >> ${COMMON_LOG_FILE}
echo "---------------------------------------------------------------" >> ${COMMON_LOG_FILE}
crontab -l >> ${COMMON_LOG_FILE}
if [ $? -ne 0 ]; then
MSG="Crontab entries could not be written to the logfile."
SUBJECT=${SUBJ_COMMON}
notify_status
fi
echo "--------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Ending flat file not Instance specific copies on `hostname` at `date`." >> ${COMMON_LOG_FILE}
echo "--------------------------------------------------------------" >> ${COMMON_LOG_FILE}
}

# ========================
# Pager/Mail notifications
# ========================
notify_status()
{
for Email in `echo "${EMAILID}"`
do
echo -e "From: MySql Server\nTo:${Email}\nSubject: ${SUBJECT}\n\n${MSG}" | /usr/sbin/sendmail ${Email}
done
}

notify_dba()
{
for Email in `echo "${NOTIFY_DBA}"`
do
echo -e "From: MySql Server\nTo:${Email}\nSubject: ${SUBJECT}\n\n${MSG}" | /usr/sbin/sendmail ${Email}
done
}

instance_status()
{
echo "----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Starting Instance status checks for MySQL Instance: ${INSTANCE_NAME} `date`." >> ${COMMON_LOG_FILE}
echo "----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}

# =====================
# Check for .setup file
# =====================
if [ ! -f ${TOOLS}/.setup ]; then
MSG="Missing ${TOOLS}/.setup file.."
SUBJECT=${SUBJ_COMMON}
notify_status
else
. ${TOOLS}/.setup
fi


# ===========================================
# Check for SOCKET file - Is the instance up?
# ===========================================
if [ ! -S ${SOCKET} ]; then
MSG="MSQL is down."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi

# ==========================================
# Instance Connectivity test - Run any query
# ==========================================
q="SELECT user from user"
/usr/bin/mysql -u${USR} -p${CRED} -e"$q" mysql --socket="${SOCKET}"
Rtn=`echo $?`
Rtn=${Rtn:=1}
if [ "${Rtn}" -ne 0 ]; then
MSG="Unable to connect to MySQL."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi
echo "--------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Ending Instance status checks for MySQL Instance: ${INSTANCE_NAME} `date`." >> ${COMMON_LOG_FILE}
echo "--------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
}


db_reports()
{
echo "--------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Starting database reports for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "--------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
# =============
# Innodb status
# =============
echo "----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo " Innodb status for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
bb="show innodb status \G"
/usr/bin/mysql -u${USR} -p${CRED} -e"$bb" mysql --socket="${SOCKET}" >> ${COMMON_LOG_FILE}
Rtn=`echo $?`
Rtn=${Rtn:=1}
if [ "${Rtn}" -ne 0 ]; then
MSG="Unable to write the show innodb status report to the logfile."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi

# ==============
# Show databases
# ==============
echo "----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Databases for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
cc="show databases"
/usr/bin/mysql -u${USR} -p${CRED} -e"$cc" mysql --socket="${SOCKET}" >> ${COMMON_LOG_FILE}
Rtn=`echo $?`
Rtn=${Rtn:=1}
if [ "${Rtn}" -ne 0 ]; then
MSG="Unable to write the show databases report to the logfile."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi

# =======================
# Show [FULL] processlist
# =======================
echo "-----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Processes for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "-----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
dd="show processlist"
/usr/bin/mysql -u${USR} -p${CRED} -e"$dd" mysql --socket="${SOCKET}" >> ${COMMON_LOG_FILE}
Rtn=`echo $?`
Rtn=${Rtn:=1}
if [ "${Rtn}" -ne 0 ]; then
MSG="Unable to write the show process list report to the logfile."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi
echo "-----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Ending database reports for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "-----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
}


query_instance_for_variable_processing()
{
echo "-----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Starting variable processing for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "-----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
#=============================
# Socket. Check for mismatch.
#=============================
x="Show variables like 'socket'"
SOCK=`/usr/bin/mysql -u${USR} -p${CRED} --column-names=FALSE -e"$x" --socket="${SOCKET}"`
Rtn=`echo $?`
Rtn=${Rtn:=1}
if [ "${Rtn}" -ne 0 ]; then
MSG="Backup job is unable to extract the socket from the Instance."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
else
SOCK_TEST=`echo ${SOCK} |awk -F" " '{print $2}'`
fi

if [ ${SOCKET} != ${SOCK_TEST} ]; then
MSG="Socket mismatch. This should not be possible."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi

#====================================================================
# MyIsam Data Dir. Use this as a base for OS reporting (minus one dir)
#=====================================================================
x="Show variables like 'datadir'"
DATA_DIR=`/usr/bin/mysql -u${USR} -p${CRED} --column-names=FALSE -e"$x" mysql --socket="${SOCKET}"`
Rtn=`echo $?`
Rtn=${Rtn:=1}
if [ "${Rtn}" -ne 0 ]; then
MSG="Backup job is unable to extract the Data directory from the instance."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
else
DATA_DIR_PATH=`echo ${DATA_DIR} |awk -F" " '{print $2}'`
fi
echo ${DATA_DIR_PATH}

# ===================================================================================================
# Check for the bin log direcory defined in the instance*.cfg file and see if it has any logs present
# Note: The instance can't be queried for this file location
# ===================================================================================================
if [ -d ${LOG_BIN} ]; then
echo ""
else
MSG="The transaction log destination defined in the instance*.cfg does not exist."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi

LOGS_EXIST=`ls -tr ${LOG_BIN}/*bin-log.* | wc -l`
if [ ${LOGS_EXIST} -gt 5 ]; then
echo ""
else
MSG="The number of transaction logs is less than 5. Please investigate."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi


# =======================================================================================================
# Check for the innodb log directory defined in the instance*.cfg file and see if it has any logs present
# Note: The instance can't be queried for this file location
# =======================================================================================================
if [ -d ${INNODB_LOG_ARCH_DIR} ]; then
echo ""
else
MSG="The innodb log destination defined in the instance*.cfg does not exist."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi

INNODB_LOGS_EXIST=`ls -tr ${INNODB_LOG_ARCH_DIR}/ib_logfile* | wc -l`
INNODB_LOGS_EXIST="` expr ${INNODB_LOGS_EXIST}`"
if [ ${INNODB_LOGS_EXIST} -lt 2 ]; then
MSG="The number of Innodb logs is less than 2. Please investigate."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi


# =========================================================================================
# Extract the first 2 directories from the DATA_DIR to form the os reporting base directory
# =========================================================================================
OS_RPT_DIR=`/usr/bin/dirname ${DATA_DIR_PATH}`
echo "-------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Ending variable processing for MySQL Instance: ${INSTANCE_NAME} at`date`." >> ${COMMON_LOG_FILE}
echo "-------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
}


os_reports()
{
echo "------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Starting OS reports for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
# ==========
# md5 report
# ==========
echo "------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo " md5sum report for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
/usr/bin/find $OS_RPT_DIR -type f ! -name "*gz" -print0 | xargs --no-run-if-empty -0 md5sum >> ${COMMON_LOG_FILE}
if [ $? -ne 0 ]; then
MSG="The md5 report generation failed."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi
echo $OS_RPT_DIR
# ==============
# ls -ltr report
# ==============

echo "--------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo " ls -ltr report for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "--------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo $OS_RPT_DIR
cd $OS_RPT_DIR
/usr/bin/find . -type f ! -name "*gz" -print0 | xargs --no-run-if-empty -0 ls -ltr >> ${COMMON_LOG_FILE}
if [ $? -ne 0 ]; then
MSG="The ls -ltr report generation failed."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi

#============================================================
# Error log. Copy it to the backup directory
# Note: The instance can't be queried for this file location
#============================================================
echo "------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo " Error log for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
cat ${DUMPDIR}/app*.err >> ${COMMON_LOG_FILE}
if [ $? -ne 0 ]; then
MSG="The MSQL error log copy failed."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi
echo "-------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Ending OS reports for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "-------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
}


preparing_db_reports()
{
echo "------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Starting to prepare for db_reports for MySQL Instance ${INSTANCE_NAME} at `date`. " >> ${COMMON_LOG_FILE}
echo "------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}

#COMMON_LOG_FILE=${BKUP_TODAY_LOG}/prebkup_${INSTANCE_NAME}.log

# =========================================
# Copy the env file for a specific instance
# =========================================
cat ${MYSQL_HOME}/${INSTANCE_NAME}.env >> ${COMMON_LOG_FILE}
if [ $? -ne 0 ]; then
MSG="${INSTANCE_NAME} environment file was not found."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi

# ==============================================================
# Check for the dump directory defined in the instance*.cfg file
# ==============================================================
if [ -d ${DUMPDIR} ] ; then
echo ""
else
MSG="The dump directory for instance: ${INSTANCE_NAME} defined in the instance*cfg file does not exist."
SUBJECT="Warning: MySQL pre cold backup on `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
fi
echo "--------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Ending preparation of db reports for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "--------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
}


checkndump()
{
# =============================
# Execute mysqlcheck to DUMPDIR
# =============================
echo "-------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Starting mysqlcheck for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "-------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}

CK_LOG_FILE=${DUMPDIR}/${INSTANCE_NAME}-check-${DATE_STAMP}.rpt
echo $LOG_FILE
/usr/bin/mysqlcheck --all-databases -1 --analyze --check --force --medium-check --optimize --socket=${SOCKET} --password=${CRED} --user=${USR} > ${CK_LOG_FILE}
EXIT_CODE=`echo $?`
wait
if [ "${EXIT_CODE}" -ge 1 ]; then
MSG="mysqlcheck in pre cold backup did not run successfully."
SUBJECT="Failed: MySQL check. Hostname: `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
notify_dba
fi

# ==================================================
# Search for errors and report final status of check
# ==================================================
NO_OF_ERRS=0
NO_OF_ERRS=`cat $CK_LOG_FILE | awk '$2 !~ /OK/' | awk '!/Table is already up to date/{print}' | wc -l`

if [ "${NO_OF_ERRS}" -eq 0 ] && [ "${EXIT_CODE}" -eq 0 ]; then
MSG="Success for MySQL check from pre cold backup. Logfile = $CK_LOG_FILE"
SUBJECT="Success: MySQL check. Hostname: `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
notify_dba
fi
if [ "${NO_OF_ERRS}" -gt 0 ] || [ "${EXIT_CODE}" -gt 0 ]; then
MSG="mysqlcheck in pre cold backup did not run successfully. Logfile = $CK_LOG_FILE"
SUBJECT="Failed: MySQL check. Hostname: `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
notify_dba
fi

echo "------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Ending mysqlcheck for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}

# ============================
# Execute mysqldump to DUMPDIR
# ============================
echo "-----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Starting mysqldump for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "-----------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}

# ==============================
# Flush tables for this instance
# ==============================
x="FLUSH TABLES"
/usr/bin/mysql -u${USR} -p${CRED} -e"$x" --socket="${SOCKET}"
Rtn=`echo $?`
Rtn=${Rtn:=1}
if [ "${Rtn}" -ne 0 ]; then
MSG="Dump is unable to Flush tables in the pre cold backup script."
SUBJECT="Failed: MySQL dump. Hostname: `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
notify_dba
fi

# ==========================
# Flush this instance's logs
# ==========================
y="FLUSH LOGS"
/usr/bin/mysql -u${USR} -p${CRED} -e"$y" --socket="${SOCKET}"
Rtn=`echo $?`
Rtn=${Rtn:=1}
if [ "${Rtn}" -ne 0 ]; then
MSG="Dump is unable to Flush logs in the pre cold backup script."
SUBJECT="Failed: MySQL dump. Hostname: `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
notify_dba
fi
# =============================
# Extract the databases to dump
# =============================
z="SHOW DATABASES"
DB=`/usr/bin/mysql -u${USR} -p${CRED} --column-names=FALSE -e"$z" --socket="${SOCKET}"`
if [ $? -ne 0 ]; then
MSG="Dump is unable to extract the databases through the show database command in the pre cold backup script."
SUBJECT="Failed: MySQL dump. Hostname: `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
nofity_dba
fi


# ===================================
# Loop through each database and dump
# ===================================
for dba in ${DB}
do
SQLFILE=${DUMPDIR}/${INSTANCE_NAME}-${dba}-dump-${DATE_STAMP}.sql
/usr/bin/mysqldump -u${USR} -p${CRED} --opt -e ${dba} -v --allow-keywords --single-transaction --skip-lock-tables --socket=${SOCKET} > ${SQLFILE}
if [ $? -ne 0 ]; then
MSG="The mysqldump command failed in the pre cold backup script."
SUBJECT="Failed: MySQL dump. DB: ${dba} Hostname: `hostname` MySQL Instance: ${INSTANCE_NAME} at `date`"
notify_status
notify_dba
fi

# =============================
# gzip each databases dump file
# =============================
gzip ${SQLFILE}
if [ $? -ne 0 ]; then
MSG="The gzip commmand failed in the pre cold bakckup script."
SUBJECT="Failed: MySQL dump. Hostname: `hostname` MySQL Instance: ${SERVERNAME} at `date`"
notify_status
notify_dba
else
MSG="The dump was successful. gzip was successful. Periodically scan the log file or test a restore."
SUBJECT="Probable Success: MySQL dump. DB: ${dba} Hostname: `hostname` MySQL Instance: ${SERVERNAME} at `date`"
notify_status
notify_dba
fi
done

echo "-------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Ending mysqldump for MySQL Instance: ${INSTANCE_NAME} at `date`." >> ${COMMON_LOG_FILE}
echo "-------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
}

shutmysql()
{
# =======================================
# Switch to mysql user and stop instances
# =======================================
echo "-------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Starting shutdown and phantom check on `hostname` at `date`." >> ${COMMON_LOG_FILE}
echo "-------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
/usr/bin/mysqld_multi stop 10 -u ${USR} -p ${CRED} >> ${COMMON_LOG_FILE}
#/usr/bin/sudo /bin/su - mysql -c '/usr/bin/mysqld_multi stop -u ${USR} -p ${CRED}' >> ${COMMON_LOG_FILE}
# need shutdown enviroments to test effectively here
#sleep 300
sleep 30
PROCS=`ps -eaf | grep mysqld | grep -v grep | wc -l`
if [ $? -ne 0 ]; then
MSG="Process check failed. Please investigate."
SUBJECT="Warning: MySQL pre cold backup on `hostname` at `date`"
notify_status
fi

#if [ ${PROCS} -ne 0 ]; then
if [ ${PROCS} -gt 1000 ]; then
MSG="Phantom processes exist, shutdown is still processing or shutdown failed. Please investigate."
SUBJECT="Warning: MySQL pre cold backup on `hostname` at `date`"
notify_status
fi

echo "--------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Ending shutdown and phantom check on `hostname` at `date`." >> ${COMMON_LOG_FILE}
echo "--------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
}


mysqlstart()
{
# ============================================================================================
# Switch to mysql user and start instances
# This should be restricted mode but we don't know how to do that yet
# This clears active transactions and prepares for clean dumps, checks and file system backups
# There are 2 shutdowns for each EDM backup exected
# Startups are checked instance by instance. No actual check here.
# ============================================================================================
echo "-------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "Executing startup on `hostname` at `date`." >> ${COMMON_LOG_FILE}
echo "-------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
/usr/bin/mysqld_multi start 10 -u ${USR} -p ${CRED} >> ${COMMON_LOG_FILE}
sleep 90
#/usr/bin/sudo /bin/su - mysql -c '/usr/bin/mysqld_multi start -u ${USR} -p ${CRED}' >> ${COMMON_LOG_FILE}
}


# ============
# Start Loop 1
# ============
common;
for i in ${BK_CFG_DIR}/*.cfg ; do
if [ -r "$i" ]; then
# ==========
# SET VALUES
# ==========
for line in $(< $i)
do
names=("${names[@]}" $line)
export $line
echo $line
done
preparing_db_reports;
instance_status;
db_reports;
query_instance_for_variable_processing;
os_reports;

# ============
# UNSET VALUES
# ============
for name in ${names[@]}
do
name2=${name%=*}
unset $name2
done
unset names
fi
done
unset i
shutmysql;
mysqlstart;

# =============
# Start Loop 2
# Switch back to root user?
# =============
for i in ${BK_CFG_DIR}/*.cfg ; do
if [ -r "$i" ]; then
# ==========
# SET VALUES
# ==========
for line in $(< $i)
do
names=("${names[@]}" $line)
export $line
done
instance_status;
checkndump;

# ============
# UNSET VALUES
# =============
for name in ${names[@]}
do
name2=${name%=*}
unset $name2
done
unset names
fi
done
unset i
#shutmysql;

echo "-------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
echo "End prebkup cold backup on `hostname` at `date`." >> ${COMMON_LOG_FILE}
echo "-------------------------------------------------------------------------------" >> ${COMMON_LOG_FILE}
MSG="`cat -s ${COMMON_LOG_FILE}`"
SUBJECT="End prebkup cold backup on `hostname` at `date`"
notify_status

echo "End prebkup cold backup at `date`."

######################################################################


#!/bin/sh
# File Name :mysqlcommon.drv
# Description :Backup driver
# Author :Phil Williams
# Version :1.0
# Released :
# Execution :
# company :
# date :03/29/065
# parameters :
# Calls :
# Location :${BK_CFG_DIR} Defined below
# Revision History:
# Version Details of Change
# ------- ------- -- ------
#
#----------------------------------------------------------------
TOOLS=/var/lib/mysql/admin/TOOLS
MYSQL_HOME=/var/lib/mysql
DAYS_2_KEEP=14
BKUP_BASE_LOG_DIR=/var/lib/mysql/test
EMAILID=""
#-----------------------------------------------------------------
# End of Backup driver
#-----------------------------------------------------------------


Configuration file looks like this.
INSTANCE_NAME=mysqld1
DUMPDIR=/app1/mysql/mysqldump
SOCKET=/var/lib/mysql/mysql.sock1
LOG_BIN=/app1/mysql/blogs
INNODB_LOG_ARCH_DIR=/app1/mysql/logs
NOTIFY_DBA=""

Options: ReplyQuote


Subject
Written By
Posted
Linux script - multi_instance backup integrity report
April 06, 2006 04:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.