mysql性能状态监控脚本_监控mysql性能脚本

#!/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