#!/bin/sh 
# 
# Script to automatically configure a Disaster Recovery slave system 
# 
# Call with -v for verbose output 
# 
# Note: You *MUST* configure MASTER, DBUSER, DBPWD and DB below 
# 
# Note: You *MUST* configure SELECT, LOCK TABLES and RELOAD privilege on the master: 
# 
# GRANT SELECT, LOCK TABLES, RELOAD ON *.* TO 'DBUSER'@'SLAVE' IDENTIFIED BY 'DBPWD'; 
# 
# Where: 
#   SLAVE is the IP address or host name of the slave system where this script is installed 
#   DBUSER is the database user configured below 
#   DBPWD is the database password configured below 
# 

################################################# Start configurable settings 

# Set the defaults up 
DBUSER="repluser" 
DBPWD="replpass" 
MONUSER="monitor" 
MONPWD="monitorpass" 
ROOT="root" 
ROOT_PWD="7layer" 

CLONE_DB="yes" 
DB="ssg" 

################################################# End configurable settings 

clean_up() { 
        rm -f /tmp/mb_*.$$ 
        exit $1 
} 

verbose() { 
        if test $VERBOSE == yes  ; then 
                echo "--> $1" 
        fi 
} 

mysql_fail() { 
        cat <<-EOM 

                ==> $1 

                Message: `cat /tmp/mb_error.$$` 

                Refer to http://dev.mysql.com/doc/refman/4.1/en/error-messages-client.html 
                for more information. 

        EOM 
        clean_up 1 
} 

evaluate_result() { 
        eval `echo "$1" | \ 
                sed 's/^  *//' | \ 
                sed 's/: \(.*\)/="\1"/' | \ 
                grep -v '=""$' | \ 
                grep '='` 
} 

set_grants() { 
        echo "--> Granting access for $1" 
        CMD="GRANT USAGE ON *.* TO $1 IDENTIFIED BY PASSWORD '$PASSWORD'" 
        RESULT=`$SLAVE_MYSQL -e "$CMD" 2>/tmp/mb_error.$$` 

        if test $? -ne 0; then 
                mysql_fail "Error granting USAGE for $1" 
        fi 

        CMD="GRANT ALL PRIVILEGES ON ssg.* TO $1" 
        RESULT=`$SLAVE_MYSQL -e "$CMD" 2>/tmp/mb_error.$$` 

        if test $? -ne 0; then 
                mysql_fail "Error granting PRIVILEGES for $1" 
        fi 
} 

# Check if -v set 
if test "$1" == "-v" ; then 
        VERBOSE="yes" 
else 
        VERBOSE="no" 
        echo "--> For verbose output run with -v" 
        echo "" 
fi 

################ 
# Get the settings 

echo -n "Enter hostname or IP for the secondary DB node in the cluster: " 
read -e MASTER 

if test -z $MASTER ; then 
        echo "You must enter a value for the secondary DB node in the cluster" 
        clean_up 1 
fi 

echo -n "Enter replication user: [$DBUSER] " 
read -e 
if test $REPLY ; then DBUSER=$REPLY ; fi 

echo -n "Enter replication password: [$DBPWD] " 
read -e 
if test $REPLY ; then DBPWD=$REPLY ; fi 

echo -n "Enter monitor user: [$MONUSER] " 
read -e 
if test $REPLY ; then MONUSER=$REPLY ; fi 

echo -n "Enter monitor password: [$MONPWD] " 
read -e 
if test $REPLY ; then MONPWD=$REPLY ; fi 

echo -n "Enter MySQL root user: [$ROOT] " 
read -e 
if test $REPLY ; then ROOT=$REPLY ; fi 

echo -n "Enter MySQL root password: [$ROOT_PWD] " 
read -e 
if test $REPLY ; then ROOT_PWD=$REPLY ; fi 

echo -n "Do you want to clone a database from $MASTER (yes or no)? [yes] " 
read -e 
if test $REPLY ; then CLONE_DB=$REPLY ; fi 

if test "$CLONE_DB" != "yes" -a "$CLONE_DB" != "no" ; then 
        echo "Must answer 'yes' or 'no' (type whole word)" 
        clean_up 1 
fi 

if test "$CLONE_DB" == "yes" ; then 
        echo -n "Enter name of database to clone: [$DB] " 
        read -e 
        if test $REPLY ; then DB=$REPLY ; fi 
fi 

verbose "MASTER = $MASTER" 
verbose "DBUSER = $DBUSER" 
verbose "DBPWD = $DBPWD" 
verbose "MONUSER = $MONUSER" 
verbose "MONPWD = $MONPWD" 
verbose "ROOT = $ROOT" 
verbose "ROOT_PWD = $ROOT_PWD" 
verbose "CLONE_DB = $CLONE_DB" 
verbose "DB = $DB" 

# Massage the user and password args for mysql on slave. 
if test $ROOT_PWD ; then ROOT_PWD="-p$ROOT_PWD" ; fi 
if test $ROOT ; then ROOT="-u$ROOT" ; fi 

# Set commands for slave and master mysql calls 
SLAVE_MYSQL="/usr/bin/mysql $ROOT $ROOT_PWD" 
MASTER_MYSQL="/usr/bin/mysql -h$MASTER -u$DBUSER -p$DBPWD" 

# Initialize values 
File="" 
Position=0 
Slave_IO_Running="No" 
Slave_SQL_Running="No" 

################ 
# Set the master configuration 

# Stop the slave 
verbose "Stopping slave" 
CMD="STOP SLAVE\G" 
RESULT=`$SLAVE_MYSQL -e "$CMD\G" 2>/tmp/mb_error.$$` 

if test $? -ne 0; then 
        mysql_fail "Error stopping slave" 
fi 

# Query the MASTER STATUS from the master database 
CMD="SHOW MASTER STATUS\G" 
RESULT=`$MASTER_MYSQL -e "$CMD" 2>/tmp/mb_error.$$` 

if test $? -ne 0 ; then 
        mysql_fail "Error getting master status" 
fi 

# Get the useful information from SHOW MASTER STATUS\G and convert to variables 
evaluate_result "$RESULT" 

verbose "File = $File" 
verbose "Position = $Position" 

# Change the MASTER settings in slave 
verbose "Changing MASTER settings" 
CMD="CHANGE MASTER TO MASTER_HOST='$MASTER', 
        MASTER_USER='$DBUSER', 
        MASTER_PASSWORD='$DBPWD', 
        MASTER_PORT=3307, 
        MASTER_CONNECT_RETRY=100, 
        MASTER_LOG_FILE='$File', 
        MASTER_LOG_POS=$Position;" 

RESULT=`$SLAVE_MYSQL -e "$CMD" 2>/tmp/mb_error.$$` 

if test $? -ne 0; then 
        mysql_fail "Error changing master settings" 
fi 

################ 
# Clone the database if requested 

if test "$CLONE_DB" == "yes" ; then 

        ################ 
        # Check if the database already exists and drop if so 
        CMD="SHOW DATABASES" 
        RESULT=`$SLAVE_MYSQL -e "$CMD" 2>/tmp/mb_error.$$ | grep "^${DB}$"` 

        if test $RESULT ; then 
                # Database exists, so drop it 
                # This is a drastic procedure. Confirm before doing anything... 
                echo "" 
                echo "W A R N I N G" 
                echo "  About to drop the $DB database on localhost" 
                echo "  and copy from $MASTER" 
                echo "" 
                echo -n "Are you sure you want to do this? [N] " 
                read -e 

                if test -z $REPLY ; then 
                        REPLY="N" 
                fi 

                if test $REPLY != "y" -a $REPLY != "Y" -a $REPLY != "yes" ; then 
                        clean_up 0 
                fi 


                ################ 
                # Drop the database 

                verbose "Dropping database" 
                CMD="DROP DATABASE $DB" 
                RESULT=`$SLAVE_MYSQL -e "$CMD" 2>/tmp/mb_error.$$` 

                if test $? -ne 0 ; then 
                        mysql_fail "Error dropping database" 
                fi 
        fi 

        ################ 
        # Create the database 

        verbose "Creating database: $DB" 
        CMD="CREATE DATABASE $DB" 
        RESULT=`$SLAVE_MYSQL -e "$CMD" 2>/tmp/mb_error.$$` 

        if test $? -ne 0 ; then 
                mysql_fail "Error creating database" 
        fi 

        ################ 
        # pipe in the database 

        verbose "Copying database from $MASTER" 
        mysqldump -h $MASTER -u $DBUSER -p$DBPWD --master-data=1 $DB | $SLAVE_MYSQL $DB 
fi 

################ 
# Start the slave 

verbose "Starting slave" 
CMD="START SLAVE\G" 
RESULT=`$SLAVE_MYSQL -e "$CMD\G" 2>/tmp/mb_error.$$` 

if test $? -ne 0; then 
        mysql_fail "Error starting slave" 
fi 

# Give the slave a chance to start 
sleep 1 

################ 
# Confirm the slave settings on slave 
verbose "Confirming slave startup" 
CMD="SHOW SLAVE STATUS\G" 
RESULT=`$SLAVE_MYSQL -e "$CMD\G" 2>/tmp/mb_error.$$` 

if test $? -ne 0; then 
        mysql_fail "Error querying slave status" 
fi 

evaluate_result "$RESULT" 

verbose "Slave_IO_Running = $Slave_IO_Running" 
verbose "Slave_SQL_Running = $Slave_SQL_Running" 

if test $Slave_IO_Running == "Yes" -a $Slave_SQL_Running == "Yes" ; then 
        echo "Slave successfully created" 
else        cat <<-EOM 

                ==> Error: Slave not started 

                Confirm your settings in $0 

                Result of SLAVE STATUS: 

                $RESULT 

        EOM 
 
        clean_up 1 
fi 

# Assume password is the same for all gateway users 
verbose "Getting password for user gateway" 
CMD="SELECT Password FROM mysql.user WHERE User='gateway' AND Host='%'" 
PASSWORD=`$MASTER_MYSQL -e "$CMD" | tail -1` 

set_grants "'gateway'@'localhost'" 
set_grants "'gateway'@'%'" 
set_grants "'gateway'@'localhost.localdomain'" 

echo "--> Granting monitor rights for $MONUSER@localhost" 
CMD="GRANT REPLICATION CLIENT ON *.* TO $MONUSER@localhost IDENTIFIED BY '$MONPWD'" 
RESULT=`$SLAVE_MYSQL -e "$CMD" 2>/tmp/mb_error.$$` 

if test $? -ne 0; then 
        mysql_fail "Error granting REPLICATION CLIENT for $MONUSER@localhost" 
fi

#!/bin/bash 
# 
# Script to monitor replication on a DR node 
# Periodically run this on the DR node from crontab. 
# 
# Note: You *MUST* configure NOTIFY_TO below 
# 
# Note: You *MUST* configure REPLICATION CLIENT privilege: 
# 
# GRANT REPLICATION CLIENT ON *.* TO 'MONUSER'@'localhost' IDENTIFIED BY 'MONPWD'; 
# 
# That should have been done by create_DR_slave.sh. Make sure MONUSER and MONPWD 
# are the same that were set when create_DR_slave.sh was run. 
# 
# Note: An MTA daemon should not be running on a CA API Gateway. To enable 
# sending of notification by SMTP you must configure a smart relay host in the 
# MTA configuration. Appliances running on RHEL 5 have exim installed. 
# 
# The following sections outline steps to take for each version. 
# 
# Using SNMP Trap for Alerts 
# ~~~~~~~~~~~~~~~~~~~~~~~~~~ 
# 
# To send SNMP trap alerts the net-snmp-utils rpm package must be installed. 
# This is not a default in the Gateway appliances. Use scp to 
# copy the rpm file to the ssgconfig user's home directory then run: 
#   # rpm -Uvh ~ssgconfig/net-snmp-utils-<version info>.rpm 
# 
# There are two possible traps that may be sent: ERROR and INFO. The OID for 
# ERROR is 1.3.6.1.4.1.17304.7.3.50 and indicates that action should be taken. 
# The OID for INFO is 1.3.6.1.4.1.17304.7.3.51. No action needs to be taken 
# for INFO. Layer 7 has reserved the 1.3.6.1.4.1.17304.7.3.* MIB space. To 
# set the OID to something other than .50 or .51 change the values of 
# OID_ERROR and OID_INFO below. 
# 
# Configuring Exim 
# ~~~~~~~~~~~~~~~~ 
# 
# To configure exim to use a smarthost edit /etc/exim/exim.conf and find the 
# line with "begin routers". Add the following lines immediately after "begin 
# routers" line. Change "mailhost.example.com" to your smarthost: 
# 
# # Configure a smart host to route everything but local domain 
# smarthost: 
# driver = manualroute 
# domains = !+local_domains 
# transport = remote_smtp 
# route_data = mailhost.example.com 
# 
# Make sure the MTA_FLAGS below is set to "-t" 
# 
# Configuring Sendmail 
# ~~~~~~~~~~~~~~~~~~~~ 
# 
# Configure the smarthost in sendmail by setting the DS configuration in the 
# /etc/mail.sendmail.cf file and set the MTA_FLAG value below to "-Am -t" 
# 
# Configuring crontab 
# ~~~~~~~~~~~~~~~~~~~ 
# 
# Run the command 'crontab -e' as root and add the following line (note: it 
# opens the file in vi). See the crontab man page for details: 
# 
# to check every hour on the hour: 
#    0 * * * * /usr/local/bin/monitor_replication.sh 
# 
# to check every day at 04:15 
#    15 4 * * * /usr/local/bin/monitor_replication.sh 
# 
# Jay MacDonald - v1 - 20100303 - modified manage_binlogs.sh 

################################################# Configurable settings 

# SLAVE is either IP address or host name 
MONUSER="monitor" 
MONPWD="monitorpass" 

# NOTIFY sends email on error conditions only. If you want notifications on 
# all runs set NOTIFY_PURGE as well 
NOTIFY="yes" 

# Set the notification methods 
NOTIFY_SMTP="yes" 
NOTIFY_SNMP="no" 

if test "$NOTIFY_SMTP" == "yes" ; then 
        # Configure email alert settings. Must set NOTIFY_TO. 
        # NOTIFY_CC and NOTIFY_BCC are optional. 
        NOTIFY_TO="SET ME" 
        NOTIFY_CC="" 
        NOTIFY_BCC="" 

        # For exim set MTA_FLAG to "-t". For sendmail set it to "-Am -t" 
        MTA_FLAG="-t" 

        if test "$NOTIFY_TO" == "SET ME" ; then 
                echo "Error: NOTIFY_TO is not configured" 
                exit 1 
        fi 
fi 

if test "$NOTIFY_SNMP" == "yes" ; then 
        # Configure SNMP Trap alert settings. 
        # Must set SNMP_HOST and COMMUNITY 
        SNMP_HOST="SET ME" 
        COMMUNITY="SET ME" 

        # Change these if you want to use different values 
        OID_ERROR="54" 
        OID_INFO="55" 

        UPTIME=`/bin/cat /proc/uptime | /bin/cut -f 1 -d '.'` 

        if test "$SNMP_HOST" == "SET ME" ; then 
                echo "Error: SNMP_HOST is not configured" 
                exit 1 
        fi 

        if test "$COMMUNITY" == "SET ME" ; then 
                echo "Error: COMMUNITY is not configured" 
                exit 1 
        fi 

        if test ! -x /usr/bin/snmptrap ; then 
                echo "Error: Can't find /usr/bin/snmptrap. Make sure the net-snmp-utils rpm" 
                echo "has been installed." 
                exit 1 
        fi 
fi 

# Set VERBOSE to yes when debugging. Under normal operations it should 
# probably be set to "no" 
VERBOSE="yes" 

########################################################################### 
################################################# End configurable settings 
########################################################################### 

notify () { 
        if test "$NOTIFY" != "yes" ; then 
                return 1; 
        fi 

        if test "$NOTIFY_SMTP" == "yes" ; then 
                echo "From: ${USER}@${HOSTNAME}" > /tmp/mb_notify.$$ 
                echo "To: $NOTIFY_TO" >> /tmp/mb_notify.$$ 
                if test -n $NOTIFY_CC ; then 
                        echo "Cc: $NOTIFY_CC" >> /tmp/mb_notify.$$ 
                fi 
                if test -n $NOTIFY_BCC ; then 
                        echo "Bcc: $NOTIFY_BCC" >> /tmp/mb_notify.$$ 
                fi 
                echo "Subject: $SUBJECT" >> /tmp/mb_notify.$$ 
                echo "" >> /tmp/mb_notify.$$ 
                cat /tmp/mb_message.$$ >> /tmp/mb_notify.$$ 

                if test "$VERBOSE" == "yes"  ; then 
                        echo "Sending notification by email" 
                fi 

                cat /tmp/mb_notify.$$ | /usr/sbin/sendmail $MTA_FLAG 
        fi 

        if test "$NOTIFY_SNMP" == "yes" ; then 
                if test "$VERBOSE" == "yes"  ; then 
                        echo "Sending notification by SNMP trap" 
                fi 

                if test -z $1 ; then 
                        OID=$OID_ERROR 
                else 
                        OID=$1 
                fi 

                # Send the trap. Note: this is a v2c style message. 
                /usr/bin/snmptrap -v 2c -c $COMMUNITY $SNMP_HOST $UPTIME \ 
                1.3.6.1.4.1.17304.7.3.${OID} \ 
                SNMPv2-SMI::enterprises.17304.7.3.0 s "${SUBJECT}" 
        fi 
} 

# Get the timestamp 
DATE=`date` 

# Query the SLAVE STATUS from the slave database 
RESULT=`mysql -u$MONUSER -p$MONPWD -e "SHOW SLAVE STATUS\G" 2>/tmp/mb_error.$$` 

# Check to make sure we got the connection, notify and fail if not 
if test $? -ne 0; then 
        cat > /tmp/mb_message.$$ <<-EOM 

                ==> Error querying database 

                Timestamp: $DATE 
                Message: `cat /tmp/mb_error.$$` 

                Refer to http://dev.mysql.com/doc/refman/4.1/en/error-messages-client.html 
                for more information. 

        EOM 
        if test $VERBOSE == yes ; then 
                cat /tmp/mb_message.$$ 
        fi 

        SUBJECT="WARNING: Error querying replication status" 
        notify $OID_ERROR 

        rm -f /tmp/mb_*.$$ 
        exit 1 
fi 

# Get the useful information from SHOW SLAVE STATUS\G and convert to variables 
eval `echo "$RESULT" | \ 
        sed 's/^  *//' | \ 
        sed 's/: \(.*\)/="\1"/' | \ 
        grep -v '=""$' | \ 
        grep '='` 

if test "$VERBOSE" == "yes"  ; then 
        echo "Master_Host = $Master_Host" 
        echo "Slave_IO_Running = $Slave_IO_Running" 
        echo "Slave_SQL_Running = $Slave_SQL_Running" 
        echo "Master_Log_File = $Master_Log_File" 
        echo "" 
fi 

# Check the status of the slave and send notification if in failed state 
if test "$Slave_IO_Running" == "Yes" -a "$Slave_SQL_Running" == "Yes" ; then 
        if test "$VERBOSE" == "yes"  ; then 
                echo "Slave is functioning properly." 
                echo "" 
        fi 
        rm -f /tmp/mb_*.$$ 
        exit 0 
else 
        if test "$VERBOSE" == "yes" ; then 
                echo "WARNING: Slave is not functioning properly" 
                if test "$NOTIFY" == "yes" ; then 
                        echo " --- This *should* have been sent by SMTP or SNMP ---" 
                else 
                        echo " --- No one was notified! ---" 
                fi 
        fi 

        # Create message and send 

        SUBJECT="WARNING: DR slave on $HOSTNAME is not functioning properly" 

        cat > /tmp/mb_message.$$ <<-EOM 
                WARNING: The database slave at $HOSTNAME is not functioning properly 
                Timestamp: $DATE 

                Here are the results of SHOW SLAVE STATUS\G: 

                $RESULT 
        EOM 

        notify $OID_ERROR 
        rm -f /tmp/mb_*.$$ 
        exit 1 
fi