# NDB CLUSTER MONITOR
# script for monitoring mysql-cluster status data/ndb/mgmnodes

NDB_MGM=”ndb_mgm”
ndb_ps=`ps -ef|grep “ndb_mgmd”|grep -v grep |wc -l`
node_count=`$NDB_MGM -e “show” |grep -w “id”|wc -l`

echo ${Log}
> ${Log}

if [ $ndb_ps -ne 1 ] ; then
echo “Looks like NDB_MGMD is not running”
echo “check MANAGEMENT Node status”
exit 1
### need to work on this condition.
else
echo “MANAGEMENT Node is up, proceed with next step”
echo “checking individual node status”
$NDB_MGM -e “show”|grep -w “id”|awk ‘{print $1}’|cut -c 4- |while read node_id
do
# check to see ndbd_mgm daemon is running on $HOST and
# node are running
NODE_STATUS=`$NDB_MGM -e “$node_id status” | egrep -v ‘(Connected to Management Server|^$)’`
case $NODE_STATUS in
*started*)
## echo “for datanode if status is started then ndbd is up”
echo “OK – $NODE_STATUS” >>${Log}
;;
“Node $node_id: connected”*)
## echo “for MGMD & SQL if status is connected then things are fine”
echo “OK – $NODE_STATUS” >>${Log}
;;
*starting*)
echo “WARRING – $NODE_STATUS” >>${Log}
;;
*)
echo “ERROR – $NODE_STATUS” >>${Log}
;;
esac
# done with all nodes
done
fi ## closing if

cnt=`cat ${Log}|grep -vw “OK”|wc -l`
if [ $cnt -eq 0 ] ; then
echo “ALL NODE STATUS is FINE”
cat ${Log}
exit 0
else
echo “${Header.Start}”
echo “CHECK MYSQL-CLUSTER NODE STATUS”
$NDB_MGM -e “show”
echo “${Header.Stop}”
exit 1
fi

Pre-req: The backup dir (taken using the backup script earlier) should be accessible from the target server and untar the bzip2 backup file before starting with restore.

This script will restore all the objects of all the schema(including mysql schema) use filters for selective restore.

#!/bin/sh
#============================================================================
# File:         DV_MySQL_restore.sh
# Description:  restore individual table backup to each database.
# Usage:        sh DV_MySQL_restore.sh >DV_MySQL_restore.log 2>DV_MySQL_restore.err
#============================================================================

MYSQL=”/usr/local/mysql/bin/mysql”
BKP_DATE=$(date ‘+%u’)  ## current format of backup day/time
HOST=`hostname`
DNAME=${HOST}-${BKP_DATE}* ## for latest backupdir based on day.
MUSER=”xxxxxx”
MPASS=”xxxxxx”
SRC_BACKUP_DIR=”/mnt/backup/”$DNAME

##check if the backup dir exist or not..
if [ ! -d $SRC_BACKUP_DIR ]; then
echo Directory NOT found..
echo manually unzip the .tar.bz2 file to make sure we use the correct backupfile.
echo Plz check the target dir for backups.
exit
else
echo Directory found continue with backup
cd $SRC_BACKUP_DIR
fi
### directory found.Proceed with backup

cd $SRC_BACKUP_DIR
### Get all databases name from source host,Assuming we have access to mysql on source from target###
DATABASES=`dir |egrep -vw “information_schema|mysql”`
for db in $DATABASES
do
cd ${SRC_BACKUP_DIR}/${db} ### ON the assumtion that /mnt/backup is a SHARED MOUNT POINT i.e accessible from the TEST SERVER too
echo “starting restore of database — $db”
$MYSQL -u$MUSER -p$MPASS -e “CREATE DATABASE IF NOT EXISTS ${db}”
## database created proceed with each table restore.Get list of tables from source.
TABLES=`ls |grep -v $db”_routines_triggers.sql.gz”`
for table in $TABLES
do
cd $SRC_BACKUP_DIR/$db/
FILE=$table  ##use .gz for compressed files.
echo $table
gunzip < $FILE | $MYSQL -q -u$MUSER -p$MPASS -D ${db} #for .gz files. if compression is done.
###$MYSQL -q -u$MUSER -p$MPASS -D ${db} < $FILE  ## for each table with .sql file
done
## once table restore is done.proceed with routines_triggers restore in each database.
echo “starting restore of routines/triggers to database — $db”
cd ${SRC_BACKUP_DIR}/${db}/
ROUTINE=${db}_routines_triggers.sql.gz #for .gz files. if compression is done.
###$MYSQL -q -u$MUSER -p$MPASS -D ${db} < $ROUTINE  ## for routines/triggers in each db.
gunzip < $ROUTINE | $MYSQL -u$MUSER -p$MPASS -D ${db}
echo “completed restore of database — $db”
done
## restore of triggers/routines is done for each db.
## all database restore is completed.

#!/bin/sh
#============================================================================
# File:         DV_MySQL_backup.sh
# Description:  backup individual table backup from each database.
# Usage:        sh DV_MySQL_backup.sh >DV_MySQL_backup.log 2>DV_MySQL_backup.err
#============================================================================

### MySQL Setup ###
##BKP_DATE=$(date ‘+%m%d%Y’)
BKP_DATE=$(date ‘+%u%H’)  ## current format of backup day/time
HOST=`hostname`
MUSER=”xxxxx”
MPASS=”xxxxx”
BACKUP_DIR=”/mnt/backup/” #${HOST}-${BKP_DATE}
DNAME=${HOST}-${BKP_DATE}
MYSQLDUMP=”/usr/local/mysql/bin/mysqldump”
MYSQL=”/usr/local/mysql/bin/mysql”

cd $BACKUP_DIR
#mkdir ${HOST}-${BKP_DATE}
echo “starting backup job at `date ‘+%m%d%Y %H:%M %Z’` ”

### Get all databases name ###
###$MYSQL -u$MUSER -p$MPASS –skip-column-names -e “show databases” |grep -v “information_schema” |while read db
DATABASES=`$MYSQL -u$MUSER -p$MPASS –skip-column-names -e “show databases”`
for db in $DATABASES
do
### Create dir for each databases(DUE TO FILESYSTEM BUGS), backup tables in individual files ###
#echo ${BACKUP_DIR}
#echo ${BACKUP_DIR}/${db}
mkdir -p -m 777 ${BACKUP_DIR}/$DNAME
cd ${BACKUP_DIR}/$DNAME
mkdir -m 777 ${db}

#### directory creation is done.Proceed with backup
echo “  ”
echo “starting backup of database — $db”
echo “————————————-”
###$MYSQL -u $MUSER -p$MPASS $db -e “show tables” |grep -v “Tables_in_”  |while read table
TABLES=`$MYSQL -u $MUSER -p$MPASS $db -e “show tables” |grep -v “Tables_in_”`
for table in $TABLES
do
FILE=$BACKUP_DIR/$DNAME/$db/$table.sql.gz
echo $table;
## for schema,data/routines  make sure we backup mysql database also.
$MYSQLDUMP -q –skip-triggers –single-transaction -u $MUSER -p$MPASS $db $table | gzip > $FILE  ##dump only table schema and table data
done
## to backup routines/triggers for each db.
ROUTINE=$BACKUP_DIR/$DNAME/${db}/${db}_routines_triggers.sql.gz
$MYSQLDUMP -t -d -q –allow-keywords -R -u $MUSER -p$MPASS $db $table | gzip > $ROUTINE        ##triggers are enabled by default
echo “completed the backup of database — $db”
done

echo “##################################”
echo “backup job completed at `date ‘+%m%d%Y %H:%M %Z’` ”
echo “All database backup complted”
echo ” ”
echo “Deleting old backups”
rm -rf ${BACKUP_DIR}/${DNAME}.tar.bz2
echo “Compressing the backupfiles”
##create   bzipped   tar  archive  of  the  directory
cd ${BACKUP_DIR}
tar -cjf ${DNAME}.tar.bz2 ${DNAME}
##tar -cvf – <directory name> | gzip -c > <directory name>.tar.gz
echo “backup archiving is done.plz verify the files”
ls -lhtr ${BACKUP_DIR}/${DNAME}.tar.bz2
rm -rf ${BACKUP_DIR}/${DNAME}
## done with backup

Script to find the datadiscrepancy across table partitions  b/w mysql servers(Master/slave) . The can also be used(with slight modifications) to find the data discrepancy across 2 standalone servers..

Pre-req: Need to have maatkit checksum tool installed on the server.

create tables checksum and checksum_results

CREATE TABLE checksum (
     db         char(64)     NOT NULL,
     tbl        char(64)     NOT NULL,
     chunk      int          NOT NULL,
     boundaries char(100)    NOT NULL,
     this_crc   char(40)     NOT NULL,
     this_cnt   int          NOT NULL,
     master_crc char(40)         NULL,
     master_cnt int              NULL,
     ts         timestamp    NOT NULL,
     PRIMARY KEY (db, tbl, chunk)
  );

CREATE TABLE checksum_results (
     db         char(64)     NOT NULL,
     tbl        char(64)     NOT NULL,
     chunk      char(64)     NOT NULL,
     boundaries char(100)    NOT NULL,
     this_crc   char(40)     NOT NULL,
     this_cnt   int          NOT NULL,
     master_crc char(40)         NULL,
     master_cnt int              NULL,
     ts         timestamp    NOT NULL,
 );

Usage : checksum_run.sh <MastHost> <SlaveHost>  > checksum_run.log 2>checksum_run.err
#====================================================================================
##### Verify if mysql deamon is running #####
SRC=$1
TRG=$2
USER=’xxxxx’
PWD=’xxxx’
LogDir=/tmp
Log=${LogDir}/Checksum_test.log
MailList=”abc@xyz.com”
Hostname=`hostname`

> ${Log}

mysql_statuschk()
{
process=`ps -ef | egrep -iw ‘mysqld_safe’ | egrep -v egrep | wc -l`
myping=`mysqladmin -u$USER -p$PWD “ping”|egrep -iw ‘alive’|wc -l`
if [ $process -eq 0 ] || [ $myping -eq 0 ] ; then
echo “MySQL Process is Unavailable on ${Hostname}!” > ${Log}
echo “Verify if mysqld is running!” >> ${Log}
echo “Hostname:” $Hostname >> ${Log}
echo “Uptime:” `uptime` >> ${Log}
echo  >> ${Log}
ps -ef|egrep -iw “mysql” >> ${Log}
##### Verify MYSQLADMIN Status #####
echo “MySQL Admin status:” >> ${Log}
echo “——————-” >> ${Log}
mysqladmin -u$USER -p$PWD “ping” >> ${Log}
mysqladmin -u$USER -p$PWD “status” >> ${Log}
echo “If mysqladmin status is not alive! then mysql seems to be down.” >> ${Log}
mailx -s  “SEV1/${Hostname}/MySQL Process is Unavailable.” ${MailList} < ${Log}
return 1
else
## if mysql is up and running.
echo “MySQL is up and running.”
return 0
fi
}

maatkit_chksum()
{
###### MAATKIT RUNNING STATUS CHECK ######
Hostname=`hostname`
echo “Hostname:” $Hostname >> ${Log}
echo “Starting checksum for partitions at `date ‘+%m%d%Y %H:%M %Z’` ” >> ${Log}
MK_STAT=`ps -ef | grep “mk-table-checksum” | grep -v “grep” | wc -l`
seconds=`mysql -h$TRG -u$USER -p$PWD -e “show slave status\G”|egrep Seconds|awk {‘print $2′}`
if [ ${MK_STAT} -eq 0 ] || [ $seconds -eq 0 ]; then
####### MAATKIT CHECKSUM RUN #########
##make sure we truncate the table test.checksum before looping in for each partition.
mysql -u$USER -p$PWD -D test -e “truncate table test.checksum”
mysql -u$USER -p$PWD -D test -e “truncate table test.checksum_results”

## Now get the partition information to loop into.
PLIST=`mysql -u$USER -p$PWD –skip-column-names -e “select partition_name from information_schema.partitions where table_schema=’<dbname>’ and table_name=’<tblname> ‘ “|tr -d ‘p’`
for PART in $PLIST
do
### test for single partition without chunk
echo “Starting for partition — $PART”
echo
mk-table-checksum –replicate=test.checksum –databases=<dbname> –tables=<tblname> h=$SRC –where=”<colname> > date_format($PART,’%Y-%m-%d 00:00:00′) and <colname> < date_format($PART,’%Y-%m-%d 23:59:59′)”
##to insert partition cheksum result into new tables.
mysql -u$USER -p$PWD -D test -e “set @par=’p$PART’; insert into test.checksum_results (db, tbl, chunk, boundaries, this_cnt, this_crc,master_crc,master_cnt,ts) select db, tbl, @par, boundaries, this_cnt, this_crc,master_crc,master_cnt,ts from test.checksum where tbl=’<tblname>’”
done
echo “Completed  checksum for table paritions at `date ‘+%m%d%Y %H:%M %Z’` ” >> ${Log}
######### done with individual partition checksum.Now proceed with reports..
PRC=`ps -ef|grep “mk-table-checksum”|egrep -v “grep”|wc -l`
seconds2=`mysql -h$TRG -u$USER -p$PWD -e “show slave status\G”|egrep Seconds|awk {‘print $2′}`
### To ensure that checksum is replicating to slave after finishing in master ###
sleep 5
### checking for mk-table-checksum running status ###
while [ $PRC -gt 0 ] || [ ${seconds2} -gt 0 ] ;do
echo “The checksum script is still running!”
echo “(OR)”
echo “The slave is still lagging behind the master/replicating the checksum to slave”
echo “Lets wait until the process gets completed.”
sleep ${seconds2} ## sleep 900
seconds2=`mysql -h$TRG -u$USER -p$PWD -e “show slave status\G”|egrep Seconds|awk {‘print $2′}`
PRC=`ps -ef|grep “mk-table-checksum”|egrep -v “grep”|wc -l`
echo `ps -ef|grep “mk-table-checksum”|egrep -v “grep”`
done
##verify checksum results on slave
DIFFER=`mysql -t -h$TRG -u$USER -p$PWD -e “SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc) AS crc_diff FROM test.checksum_results WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)”|wc -l`
if [ $DIFFER -gt 0 ];then
echo “data discrepancy found b/w master and slave.”  >> ${Log}
mysql -t -h$TRG -u$USER -p$PWD -e “SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc) AS crc_diff FROM test.checksum_results WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)” >> ${Log}
echo “  ” >> ${Log}
echo “Checksum report” >> ${Log}
mysql -t -h$TRG -u$USER -p$PWD -e “select * from test.checksum_results” >> ${Log}
mailx -s “${Hostname}-Data discrepancy report b/w Master $SRC and slave $TRG.” ${MailList} < ${Log}

### NEW condition to report errors -Kris ##
else if [ $DIFFER -eq 0 ];then
echo “Data is consistent and in sync b/w master and slave.”  >> ${Log}
mysql -t -h$TRG -u$USER -p$PWD -e “select * from test.checksum_results” >> ${Log}
mailx -s “${Hostname}-Data consistency report b/w Master $SRC and slave $TRG.” ${MailList} < ${Log}
else
echo “Some issues with the checksum run” >> ${Log}
echo “Please check the table test.checksum_results for details” >> ${Log}
ps -ef |grep “checksum” >> ${Log}
echo “Please invistigate the issues” >> ${Log}
mailx -s “${Hostname} – Error!! found with checksum run.Plz Invistigate” ${MailList} < ${Log}
fi ##closing the else condition
fi ## closing for checksum report.
else
echo “The mk-table-checsum is already running” >>  ${Log}
echo “(OR)”  >> ${Log}
echo “The slave is $TRG lagging behind the master $SRC ” >>  ${Log}
mailx -s “${Hostname}-Table checksum is already running.” ${MailList} < ${Log}
exit 2
fi
}

### MAIN PROGRAM STARTS HERE ###
# If no parameters  passed then display script usage message
if [ "$1" = '' ] || [ "$2" = '' ]; then
echo “Usage: $0 <MasterHost> <SlaveHost>”
exit 1
fi

mysql_statuschk
#if [ $? -nt 1 ]
if [ $? -eq 0 ]
then
maatkit_chksum
fi