MySQL Forums
Forum List  »  General

mydp.sh - Intelligent mysqldump linux script
Posted by: Phil Williams
Date: April 06, 2006 04:20PM

1. emails on various conditions.
2. Validates input parametes, which is the instance.
Designed for multiple instances on a linux server.
3. Checks for an environment file for each instance (shown below)
4. setup file hides passwords
5. Verifies the instance is up and emails if not.
6. Flushes tables
7. Flushes logs
8. Uses "show databases" and creates a dump for each database in each instance.
9. gzips outout.



#!/bin/sh
# File Name :mydp.sh
# Description :This script can be used to dump MySQL databases
# Author :Phil williams
# Version :1.0
# Released :
# Execution :${TOOLS}/bin/mydp.sh <instancename>
# Company :
# Date :12/10/05
# Parameters :<$1=instancename>
# Calls :${HOME}/${SERVERNAME}.env --Setting environment variables
# Revision History:
# Version Details of Change
# ------- ------- -- ------
#
###############################################################################
umask 022
echo "Starting ${SERVERNAME} mydp.sh ..`date`.."
DATE_STAMP="`date '+%b%d%y:%H:%M:%S'`"

# ========================
# 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
}

# =========================
# Validate input parameters
# =========================
if [ $# -eq 1 ]; then
SERVERNAME=$1
else
echo "Usage: `basename $0` <SERVERNAME>"
exit 1
fi

# ==========================
# Check for environment file
# ==========================
if [ ! -f ${HOME}/${SERVERNAME}.env ]; then
echo "Missing ${HOME}/${SERVERNAME}.env file.."
exit 2
else
. ${HOME}/${SERVERNAME}.env
fi

# ===================================================
# Checking if required DIRECTORIES are present or not
# ===================================================
if [ -d ${TOOLS} ] && [ -d ${DUMPDIR} ]; then
echo ""
else
MSG="Dump can't execute. Check if directories are present or not."
SUBJECT="Failed: MySQL dump. Hostname: `hostname` MySQL Instance: ${SERVERNAME} at `date`"
notify_status
exit 3
fi


# =====================
# Check for .setup file
# =====================
if [ ! -f ${TOOLS}/.setup ]; then
MSG="Dump can't execute. Missing ${TOOLS}/.setup file.."
SUBJECT="Failed: MySQL dump. Hostname: `hostname` MySQL Instance: ${SERVERNAME} at `date`"
notify_status
exit 4
else
. ${TOOLS}/.setup
fi


# =====================
# Check for SOCKET file
# =====================
if [ ! -S ${SOCKET} ]; then
MSG="Dump can't execute, MySQL is down."
SUBJECT="Failed: MySQL dump. Hostname: `hostname` MySQL Instance: ${SERVERNAME} at `date`"
notify_status
exit 5
fi


# ===============================
# Check for database connectivity
# ===============================
q="SELECT count(*) 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="Dump is unable to connect to MySQL."
SUBJECT="Failed: MySQL dump. Hostname: `hostname` MySQL Instance: ${SERVERNAME} at `date`"
notify_status
exit 6
fi


# ==============================
# 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."
SUBJECT="Failed: MySQL dump. Hostname: `hostname` MySQL Instance: ${SERVERNAME} at `date`"
notify_status
exit 7
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."
SUBJECT="Failed: MySQL dump. Hostname: `hostname` MySQL Instance: ${SERVERNAME} at `date`"
notify_status
exit 8
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."
SUBJECT="Failed: MySQL dump. Hostname: `hostname` MySQL Instance: ${SERVERNAME} at `date`"
notify_status
exit 9
fi


# ===================================
# Loop through each database and dump
# ===================================
for dba in ${DB}
do
SQLFILE=${DUMPDIR}/${SERVERNAME}-${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."
SUBJECT="Failed: MySQL dump. DB: ${dba} Hostname: `hostname` MySQL Instance: ${SERVERNAME} at `date`"
notify_status
exit 10
fi


# =============================
# gzip each databases dump file
# =============================
gzip ${SQLFILE}
if [ $? -ne 0 ]; then
MSG="The gzip commmand failed."
SUBJECT="Failed: MySQL dump. Hostname: `hostname` MySQL Instance: ${SERVERNAME} at `date`"
notify_status
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
fi
done

echo "End ${SERVERNAME} mydp.sh ..`date`.."
######################################################################
# End of mydp.sh
######################################################################


#!/bin/sh
# File Name :mysqld1.env
# Description :Setting environment variables
# Author :Phil Williams
# Version :1.0
# Released :
# Execution :
# company :
# date :11/22/05
# parameters :
# Calls :
# Revision History:
# Version Details of Change
# ------- ------- -- ------
#
#----------------------------------------------------------------
PATH=/usr/kerberos/bin:/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:/usr/X11R6/bin:/var/lib/mysql/bin:/app/mysql/admin/TOOLS/bin
export HOME=/var/lib/mysql
export TOOLS=/var/lib/mysql/admin/TOOLS
export INSTANCE_NAME=mysqld1
export DUMPDIR=/app1/mysql/mysqldump
export SOCKET=/var/lib/mysql/mysql.sock1
export DAYS_2_KEEP=7
export EMAIL=Y
export PAGE_ON_FAIL=N
export EMAILID=""
export PAGERID=""
#----------------------------------------------------------------------------
# End of mysqld1.env
#----------------------------------------------------------------------------

Options: ReplyQuote


Subject
Written By
Posted
mydp.sh - Intelligent mysqldump linux script
April 06, 2006 04:20PM


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.