# This script is intended to automate restores and test
# the integrity of those restores by comparing tables
#
# This script will do these things
# 1. prep validation server for restore
# 2. gather data needed for copy of latest full backup
# 3. perform copy of latest full backup to val server
# 4. gather data needed for copy of latest incrementals
# 5. perform copy of latest incrementals to val server
# 6. apply incrementals to full backup
# 7. perform restore
# 8. start mysql service
# 9. gather data on database that will be compared
# 10. read compare tables into variable
# 11. read source counts into variable
# 12. read destination counts into variable
# 13. insert counts into database
# 14. display source and dest counts to compare results
# 15. read initials and comments into variable
# 16. update table with initials and comments given
# 17. view results to verify initials and comments given
#
# Created by Rafael Muniz - 23-Jan-2017
### 1 ###
echo
echo PREPPING THE VALIDATION SERVER FOR THE RESTORE
echo BY STOPPING THE MYSQL SERVICE AND EMPTYING OUT DIRS
sleep 7
### stop mysql service ###
cd /etc/rc.d/init.d/
./mysql.server stop
### delete mysqlbackup.cnf file ###
cd /mysqlbackups
rm -f mysqlbackup.cnf
### empty data dir ###
cd /data01/db
rm -rfv *
### empty binlog dir ###
cd /binlog01/db
rm -rfv *
### empty restobackups dir ###
cd /mysqlbackups/restobackups
rm -rfv *
### empty restoincs dir ###
cd /mysqlbackups/restoincs
rm -rfv *
### 2 ###
echo
echo GATHERING DATA INTO VARIABLES TO DETERMINE
echo THE LATEST FULL BACKUP AVAILABLE
sleep 7
### prompt for input ###
echo
echo "Type the hostname that contains the database you wish to restore (just type the hostname, not the FQDN):"
### command to request input and assign input to variable RHOST ###
read RHOST
### this will convert RHOST variable to all uppercase ###
RHOST=`echo "${RHOST^^}"`
### finds server file that matches input variable ###
RHOSTPATH=`find /mysqlbackups/restoscripts -maxdepth 1 -name $RHOST`
### puts backup and inc dir into a variable ###
RHOSTBACK=`head -n 1 $RHOSTPATH`
RHOSTINC=`tail -n 1 $RHOSTPATH`
### prints the assigned variables ###
echo
echo $RHOST
echo $RHOSTBACK
echo $RHOSTINC
### gets latest full backup directory from remote server ###
RHOSTLBACK=$(ssh mysql@$RHOST "ls -td $RHOSTBACK/2* | head -n1 | cut -d'/' -f4")
### prints the latest full backup directory ###
echo $RHOSTLBACK
### 3 ###
echo
echo COPYING THE LATEST FULL BACKUP TO THE
echo RESTORE DIRECTORY ON THE VALIDATION SERVER
sleep 7
### copies latest full backup dir to validation server ###
scp -r mysql@$RHOST:$RHOSTBACK/$RHOSTLBACK /mysqlbackups/restobackups
### copy the mysqlbackup.cnf file to validation server ###
scp mysql@$RHOST:/mysqlbackups/mysqlbackup.cnf /mysqlbackups
scp mysql@$RHOST:/mysqlbackups/mysqlrestore.cnf /mysqlbackups
### 4 ###
echo
echo DETERMINE THE LATEST INCREMENTAL DIRS TO COPY OVER
echo AND PLACE THE LOCATIONS IN AN ARRAY
sleep 7
### get the modify date of the latest full backup directory ###
LBACKDATE=$(ssh mysql@$RHOST "date -r $RHOSTBACK/$RHOSTLBACK")
### print the modify date of latest full backup dir ###
echo
echo $LBACKDATE
### add any incrementals generated after the latest full backup modify date to an array ###
ARHOSTLINC=(`ssh mysql@$RHOST "find $RHOSTINC -maxdepth 1 -newermt '$LBACKDATE' | sed 1d"`)
### print all incrementals after latest full backup ###
echo
echo ${ARHOSTLINC[*]}
### 5 ###
echo
echo LOOP THROUGH ARRAY TO COPY OVER INCREMENTALS
echo TO THE RESTORE DIRECTORY ON THE VALIDATION SERVER
sleep 7
### loop through array to scp directories over to validation server ###
for incs in "${ARHOSTLINC[@]}"
do scp -r mysql@$RHOST:$incs /mysqlbackups/restoincs
done
echo
echo Incrementals were copied over
### 6 ###
echo
echo APPLY INCREMENTALS TO THE LATEST BACKUP
sleep 7
### add incremental dir names to an array ###
AINCS=(`ls /mysqlbackups/restoincs`)
### print names of incremental dirs that will be applied to full backup ###
echo
echo ${AINCS[*]}
### loop through array to apply incrementals to latest full backup ###
for aincs in "${AINCS[@]}"
do /mysql/meb/opt/mysql/meb-4.0/bin/mysqlbackup --defaults-file=/mysqlbackups/mysqlrestore.cnf --backup-dir=/mysqlbackups/restobackups/$RHOSTLBACK --incremental-backup-dir=/mysqlbackups/restoincs/$aincs apply-incremental-backup
done
echo
echo Incrementals were applied to latest full backup
### 7 ###
echo
echo RESTORE DATABASE FROM THE RESTORE DIR
sleep 7
### restore database from restobackups dir ###
/mysql/meb/opt/mysql/meb-4.0/bin/mysqlbackup --defaults-file=/mysqlbackups/mysqlrestore.cnf --backup-dir=/mysqlbackups/restobackups/$RHOSTLBACK copy-back --skip-binlog
### 8 ###
echo
echo START MYSQL SERVICE
sleep 7
### start mysql service ###
cd /etc/rc.d/init.d/
./mysql.server start
### 9 ###
echo
echo GATHER DATA ON WHAT DATABASE TO COMPARE
sleep 7
### prompt for input ###
echo
echo "Type the name of the database that you wish to validate:"
### command to request input and assign input to variable RESTODB ###
read RESTODB
### this will convert RHOST variable to all uppercase ###
RESTODB=`echo "${RESTODB,,}"`
echo
echo $RESTODB
### 10 ###
echo
echo READ COMPARE TABLES INTO VARIABLE
sleep 7
read TB1 TB2 TB3 <<< $(mysql -umysqlbur -psecretpassword -huhmc-msql-v57d1 mysqlbur -se "SELECT tablename1, tablename2, tablename3 from tablelist where dbname='$RESTODB'")
echo
echo $TB1
echo $TB2
echo $TB3
### 11 ###
echo
echo READ SOURCE COUNTS INTO VARIABLE
sleep 7
read SRCT1 SRCT2 SRCT3 <<< $(mysql -umysqlbur -psecretpassword -h$RHOST $RESTODB -se "SELECT (SELECT COUNT(*) from $TB1),(SELECT COUNT(*) from $TB2),(SELECT COUNT(*) from $TB3)")
echo
echo $SRCT1
echo $SRCT2
echo $SRCT3
### 12 ###
echo
echo READ DESTINATION COUNTS INTO VARIABLE
sleep 7
read DSCT1 DSCT2 DSCT3 <<< $(mysql -umysqlbur -psecretpassword $RESTODB -se "SELECT (SELECT COUNT(*) from $TB1),(SELECT COUNT(*) from $TB2),(SELECT COUNT(*) from $TB3)")
echo
echo $DSCT1
echo $DSCT2
echo $DSCT3
### 13 ###
echo
echo INSERT VALUES INTO VALIDATION SCHEMA
sleep 7
mysql -umysqlbur -psecretpassword -huhmc-msql-v57d1 mysqlbur << EOF
INSERT INTO restoval (dbname,tablename1,sourcecnt1,destcnt1,tablename2,sourcecnt2,destcnt2,tablename3,sourcecnt3,destcnt3,restodate)
VALUES ("$RESTODB","$TB1","$SRCT1","$DSCT1","$TB2","$SRCT2","$DSCT2","$TB3","$SRCT3","$DSCT3",NOW());
EOF
### 14 ###
echo
echo COMPARE TABLE COUNTS ON SOURCE DB TO DESTINATION DB
sleep 7
mysql -umysqlbur -psecretpassword -huhmc-msql-v57d1 mysqlbur -e "SELECT dbname, tablename1, sourcecnt1, destcnt1 from mysqlbur.restoval where dbname='$RESTODB' and valby IS
NULL \G; SELECT dbname, tablename2, sourcecnt2, destcnt2 from mysqlbur.restoval where dbname='$RESTODB' and valby IS NULL \G; SELECT dbname, tablename3, sourcecnt3, destcnt3 from mysqlbur.restoval where dbname='$RESTODB' and valby IS NULL \G;"
### 15 ###
echo
echo READ INITIALS AND COMMENTS INTO VARIABLE
sleep 7
### prompt for input ###
echo
echo "Type in your initials for validation:"
### command to request initials and assign input to variable INIT ###
read INIT
### this will convert INIT variable to all uppercase ###
INIT=`echo "${INIT^^}"`
echo
echo $INIT
### prompt for input ###
echo
echo "Type in your comments regarding the validation results:"
### command to request comments and assign input to variable COMM ###
read COMM
### this will convert COMM variable to all lowercase ###
COMM=`echo "${COMM,,}"`
echo
echo $COMM
### 16 ###
echo
echo UPDATE TABLE WITH INITIALS AND COMMENTS GIVEN
echo REGARDING THE COMPARE RESULTS
sleep 7
### update statement to add initials and comments into database ###
mysql -umysqlbur -psecretpassword -huhmc-msql-v57d1 mysqlbur -e "UPDATE mysqlbur.restoval SET valby='$INIT' , restocomment='$COMM' WHERE dbname='$RESTODB' and valby IS NULL;"
### 17 ###
echo
echo DISPLAY UPDATE TO VERIFY INITIALS AND COMMENTS PROVIDED
sleep 7
### select statement of table to verify the update statement ###
mysql -umysqlbur -psecretpassword -huhmc-msql-v57d1 mysqlbur -e "SELECT dbname, valby, restodate, restocomment FROM restoval WHERE dbname='$RESTODB' ORDER BY restodate DESC LIMIT 1;"
### get you back to restoscripts dir ###
cd /mysqlbackups/restoscripts
import datetime
# title
print("\n\nStony Brook ERS (Employee Retirement System) Retirement Calculator")
print("\n*************************** User Input ***************************")
# capture age
age = int(input("\nWhat is your current age?"))
type(age)
print("You are {} years old".format(age))
# capture year started working
startYear = int(input("\nWhat year did you begin working for NYS?"))
type(startYear)
print("You began working for NYS in {}".format(startYear))
# capture retirement age
retireAge = int(input("\nWhat age are you planning to retire at (55-63)?"))
type(retireAge)
print("You are planning to retire at age {}".format(retireAge))
# capture tier
tier = int(input("\nWhat retirement Tier are you in?"))
type(tier)
print("You are in retirement Tier {}".format(tier))
# dates of tiers
if tier == 3:
print("You were hired between July 27, 1976 - August 31, 1983")
if tier == 4:
print("You were hired between September 1, 1983 - December 31, 2009")
if tier == 5:
print("You were hired between January 1, 2010 - March 31, 2012")
if tier == 6:
print("You were hired between April 1, 2012 - Present")
# how to calculate FAS
if tier == 3:
print("\nFinal Average Salary (FAS) is the average of the wages earned during the three highest consecutive years of service")
if tier == 4:
print("\nFinal Average Salary (FAS) is the average of the wages earned during the three highest consecutive years of service")
if tier == 5:
print("\nFinal Average Salary (FAS) is the average of the wages earned during the three highest consecutive years of service")
if tier == 6:
print("\nFinal Average Salary (FAS) is the average of the wages earned during the five highest consecutive years of service")
# capture FAS
fas = int(input("What is your FAS(no commas, ex. 65000)?"))
print("Your final average salary is {}".format(fas))
print("\n*********************** Generated Results ************************")
# current year
now = datetime.datetime.now()
currentYear = now.year
# calculate age started working for NYS
startAge = age - (currentYear - startYear)
print("\nYou began working for NYS at age {}".format(startAge))
# calculate years of service
yearsService = retireAge - startAge
print("You will have {} years of service by age {}".format(yearsService, retireAge))
# calculate Pre-Reduction Pension
if tier == 3 and yearsService < 20:
preReductPension = (yearsService * .0166 * fas)
if tier == 3 and yearsService > 29:
bonusService = yearsService - 30
preReductPension = (.02 * fas * 30) + (bonusService * .015 * fas)
if tier == 3 and yearsService > 19 < 30:
preReductPension = (.02 * fas * yearsService)
if tier == 4 and yearsService < 20:
preReductPension = (yearsService * .0166 * fas)
if tier == 4 and yearsService > 29:
bonusService = yearsService - 30
preReductPension = (.02 * fas * 30) + (bonusService * .015 * fas)
if tier == 4 and yearsService > 19 < 30:
preReductPension = (.02 * fas * yearsService)
if tier == 5 and yearsService < 20:
preReductPension = (yearsService * .0166 * fas)
if tier == 5 and yearsService > 29:
bonusService = yearsService - 30
preReductPension = (.02 * fas * 30) + (bonusService * .015 * fas)
if tier == 5 and yearsService > 19 < 30:
preReductPension = (.02 * fas * yearsService)
if tier == 6 and yearsService < 20:
preReductPension = (yearsService * .0166 * fas)
if tier == 6 and yearsService > 19:
bonusService = yearsService - 20
preReductPension = (.0175 * fas * 20) + (.02 * fas * bonusService)
print("\nYour Pre-Reduction Pension is {}".format(preReductPension))
# reduction charts
if tier == 3:
tier3reduction = {55: .27, 56: .24, 57: .21, 58: .18, 59: .15, 60: .12, 61: .06, 62: 0, 63: 0}
reductionPercent = tier3reduction[retireAge]
if tier == 3 and yearsService > 29:
reductionPercent = 0
if tier == 4:
tier4reduction = {55: .27, 56: .24, 57: .21, 58: .18, 59: .15, 60: .12, 61: .06, 62: 0, 63: 0}
reductionPercent = tier4reduction[retireAge]
if tier == 4 and yearsService > 29:
reductionPercent = 0
if tier == 5:
tier5reduction = {55: .3833, 56: .3333, 57: .2833, 58: .2333, 59: .1833, 60: .1333, 61: .0667, 62: 0, 63: 0}
reductionPercent = tier5reduction[retireAge]
if tier == 6:
tier6reduction = {55: .52, 56: .455, 57: .39, 58: .325, 59: .26, 60: .195, 61: .13, 62: .065, 63: 0}
reductionPercent = tier6reduction[retireAge]
print("\nYour permanent reduction percentage is {}".format(reductionPercent))
reduction = preReductPension * reductionPercent
print("Your permanent reduction amount is {}".format(reduction))
yearlyPension = preReductPension - reduction
print("\nYour ANNUAL PENSION is {}".format(yearlyPension))
monthlyPension = yearlyPension / 12
print("Your MONTHLY PENSION is {}".format(monthlyPension))
# fun facts about tiers
if tier == 3:
print("\n\n************************************")
print("*** Fun Facts for Tier 3 Members ***")
print("************************************")
print("You are vested after 5 years of service")
print("You contribute 3% of gross salary until 10 years of service")
print("27% permanent reduction at age 55, with less than 30 years of service")
print("Full benefits at age 62 or 55 with 30 years of credited service")
if tier == 4:
print("\n\n************************************")
print("*** Fun Facts for Tier 4 Members ***")
print("************************************")
print("You are vested after 5 years of service")
print("You contribute 3% of gross salary until 10 years of service")
print("27% permanent reduction at age 55, with less than 30 years of service")
print("Full benefits at age 62 or 55 with 30 years of credited service")
if tier == 5:
print("\n\n************************************")
print("*** Fun Facts for Tier 5 Members ***")
print("************************************")
print("You are vested after 10 years of service")
print("You contribute 3% of gross salary for all years of public service")
print("38.3% permanent reduction at age 55")
print("Full benefits at age 62")
if tier == 6:
print("\n\n************************************")
print("*** Fun Facts for Tier 6 Members ***")
print("************************************")
print("Members hired from April 1, 2012 - March 31, 2013, contribute 3% of gross salary for all years of public service")
print("Members hired from April 1, 2013 - Present, contribution rates are based on annualized wage")
print("52% permanent reduction at age 55")
print("Full benefits at age 63")
# CheckCerts.ksh
# Date: June 1, 2020
#
# Description: This job will take in the directory of where the cert is and how long before
# we send out an email to alert us
#. It will check all files in a directory
#. If you are using wild cards you need to put then in quotes
#
# Syn : ./CheckCerts.ksh 30 '/etc/ssl/certs/*'
#
DaysLeft=$1
HostName="`hostname`"
WorkDir=/home/mysql/scripts
rm -f $WorkDir/CheckCert.log
Num=1
CertCount=0
echo "Host Name:" $HostName >> $WorkDir/CheckCert.log
echo -e "\n" >> $WorkDir/CheckCert.log
echo " This Files will Expire:" >> $WorkDir/CheckCert.log
echo -e "\n" >> $WorkDir/CheckCert.log
for dir in $2
do
expire_date=`openssl x509 -enddate -noout -in $dir`
temp="$(cut -d'=' -f 2 <<< $expire_date)"
expire_epoch=$(date +%s -d "$temp")
today_epoch="$(date +%s)"
diff_date="$(($expire_epoch-$today_epoch))"
diff_date_days="$(((((diff_date)/60)/60)/24))"
if [ $diff_date_days -le $DaysLeft ]
then
echo -e $dir "............ Expire Date: " $temp "\n" >> $WorkDir/CheckCert.log
CertCount=$((CertCount+1))
fi
done
if [ $CertCount -ge $Num ]
then
MYDATE="`date +%Y%m%d`"
MSG="Certificate Will Expire in Less than $DaysLeft Days "
mailx -s "$MSG" `cat /home/mysql/scripts/mailrecps.x` < $WorkDir/CheckCert.log
fi
Copyright © 2024 Rafael Muniz - All Rights Reserved.
We use cookies to analyze website traffic and optimize your website experience. By accepting our use of cookies, your data will be aggregated with all other user data.