#!/bin/bash
#############################################
# $Name: monitor_mysql.sh
# $Revision: 1.00
# $Function: monitor mysql status
# $Author: shanks
# $organization: baidu_ite
#############################################
LANG=zh_CN.UTF-8

get_mysql_slave_status()
{
#mysql -h${HOST} -u${MYUSER} -p${MYPASS} -P${MYPORT} -s -e ‘show slave status\G;’
SLAVESTAT=`/usr/local/mysql/bin/mysql -h${HOST} -u${MYUSER} -p${MYPASS} -P${MYPORT} -s -e ‘show slave status;’|sed ‘s/\t/xxx/g’`
#echo ${mystat}
#echo ${SLAVESTAT}|awk -Fxxx ‘{printf NF}’
SLAVE_MESSAGE=`echo ${SLAVESTAT}|awk -Fxxx ‘{printf$1}’`
SLAVE_IO_STAT=`echo ${SLAVESTAT}|awk -Fxxx ‘{printf$11}’`
SLAVE_SQL_STAT=`echo ${SLAVESTAT}|awk -Fxxx ‘{printf$12}’`
SLAVE_BEHIND_MASTER=`echo ${SLAVESTAT}|awk -Fxxx ‘{printf$33}’`
SLAVE_IO_ERROR=`echo ${SLAVESTAT}|awk -Fxxx ‘{printf$36}’`
SLAVE_SQL_ERROR=`echo ${SLAVESTAT}|awk -Fxxx ‘{printf$38}’`
#echo ${SLAVE_IO_STAT} ${SLAVE_SQL_STAT} ${SLAVE_MESSAGE}
}

get_mysql_status()
{
MASTER_STATUS=`/usr/local/mysql/bin/mysqladmin -h${HOST} -P${MYPORT} -u${MYUSER} -p${MYPASS} -S /data/mysqldata/3306/mysql.sock status|sed ‘s/ /,/g’|sed ‘s/ //g’`
MASTER_UPTIME=`echo ${MASTER_STATUS}|awk -F, ‘{printf$1}’|awk -F: ‘{printf$2}’`
MASTER_THREADS=`echo ${MASTER_STATUS}|awk -F, ‘{printf$2}’|awk -F: ‘{printf$2}’`
MASTER_QUESTIONS=`echo ${MASTER_STATUS}|awk -F, ‘{printf$3}’|awk -F: ‘{printf$2}’`
MASTER_SLOW_QUERIES=`echo ${MASTER_STATUS}|awk -F, ‘{printf$4}’|awk -F: ‘{printf$2}’`
MASTER_OPENS=`echo ${MASTER_STATUS}|awk -F, ‘{printf$5}’|awk -F: ‘{printf$2}’`
MASTER_FLUSH_TABLES=`echo ${MASTER_STATUS}|awk -F, ‘{printf$6}’|awk -F: ‘{printf$2}’`
MASTER_OPEN_TABLES=`echo ${MASTER_STATUS}|awk -F, ‘{printf$7}’|awk -F: ‘{printf$2}’`
MASTER_QUERY_PER_SEC=`echo ${MASTER_STATUS}|awk -F, ‘{printf$8}’|awk -F: ‘{printf$2}’`
#MASTER_=`echo ${MASTER_STATUS}|awk -F, ‘{printf$}’|awk -F: ‘{printf$2}’`
}

SPLITS=—————————————————————————————————————–
echo ${SPLITS}
echo -e “\033[1m\E[35;40m 主机名 \t端口 运行时间 线程数 总共查询数 慢查询数 打开过的表 FLUSH次数 打开着的表 平均响应\033[0m”
echo ${SPLITS}

#echo Getting MySQL slave statu…
MYUSER=root
MYPASS=verysafe
for HOST in \
172.22.5.120 \

do
MYPORT=3306
get_mysql_status
echo -e “ ${HOST}\t${MYPORT} ${MASTER_UPTIME} \t${MASTER_THREADS} \t${MASTER_QUESTIONS} ${MASTER_SLOW_QUERIES}\t${MASTER_OPENS} \t${MASTER_FLUSH_TABLES}\t${MASTER_OPEN_TABLES} \t${MASTER_QUERY_PER_SEC}”
# echo ${SPLITS}
done

echo ${SPLITS}
for MYPORT in \
3306 \

do
HOST=’172.22.5.120′
get_mysql_status
echo -e “ ${HOST}\t${MYPORT} ${MASTER_UPTIME} \t${MASTER_THREADS} \t${MASTER_QUESTIONS} ${MASTER_SLOW_QUERIES}\t${MASTER_OPENS} \t${MASTER_FLUSH_TABLES}\t${MASTER_OPEN_TABLES} \t${MASTER_QUERY_PER_SEC}”
# echo ${SPLITS}
HOST=’172.22.5.121′
get_mysql_status
echo -e “ ${HOST}\t${MYPORT} ${MASTER_UPTIME} \t${MASTER_THREADS} \t${MASTER_QUESTIONS} ${MASTER_SLOW_QUERIES}\t${MASTER_OPENS} \t${MASTER_FLUSH_TABLES}\t${MASTER_OPEN_TABLES} \t${MASTER_QUERY_PER_SEC}”
# echo ${SPLITS}
HOST=’10.65.23.201′
get_mysql_status
echo -e “ ${HOST}\t${MYPORT} ${MASTER_UPTIME} \t${MASTER_THREADS} \t${MASTER_QUESTIONS} ${MASTER_SLOW_QUERIES}\t${MASTER_OPENS} \t${MASTER_FLUSH_TABLES}\t${MASTER_OPEN_TABLES} \t${MASTER_QUERY_PER_SEC}”
# echo ${SPLITS}
done

#mysql_slave_status
#echo -e “${HOST}\t${MYPORT}\t${SLAVE_IO_STAT}\t${SLAVE_SQL_STAT}\t${SLAVE_MESSAGE}\t${SLAVE_BEHIND_MASTER}\t${SLAVE_IO_ERROR}\t${SLAVE_SQL_ERROR}”
echo ${SPLITS}
echo -e “\033[1m\E[35;40m 主机名 \t端口\tIO\tSQL\t SLAVE_MESSAGE \tBEHIND_MASTER\tIO_ERROR\tSQL_ERROR\033[0m”
echo ${SPLITS}
for MYPORT in \
3306 \

do
# echo ${SPLITS}
HOST=’172.22.5.121′
get_mysql_slave_status
echo -e “ ${HOST}\t${MYPORT}\t${SLAVE_IO_STAT}\t${SLAVE_SQL_STAT}\t${SLAVE_MESSAGE}\t${SLAVE_BEHIND_MASTER}\t${SLAVE_IO_ERROR}\t${SLAVE_SQL_ERROR}”

HOST=’10.65.23.201′
get_mysql_slave_status
echo -e “ ${HOST}\t${MYPORT}\t${SLAVE_IO_STAT}\t${SLAVE_SQL_STAT}\t${SLAVE_MESSAGE}\t${SLAVE_BEHIND_MASTER}\t${SLAVE_IO_ERROR}\t${SLAVE_SQL_ERROR}”
done
echo ${SPLITS}
exit