zabbix之数据库相关监控分享

一、利用zabbix监控数据库sql得值

编辑监控默认脚本模板（只要监控sql都可以用该脚本，如oracle数据库需改sqlplus格式登录）

cat /home/appop/script/mysql-monitor.sh#!/bin/bashhost=127.0.0.1 #监控主机IPmonitor='password'#监控密码name=$3login (){ user=$1 database=$2 sql="$(cat /home/appop/script/MYSQL-MONITOR-SQL/${name}.sql)"#echo ${sql} /greatdb/svr/greatdb/bin/greatsql -u$user -h$host -p$monitor -P3306 $database -sN -e "${sql}" 2>/dev/null} login $1$2====================$1=监控用户:monitor$2=数据库用户实例：如：information_schema$3=MYSQL-MONITOR-SQL目录下的sql文件（注意监控中不需要加.sql）

2.创建监控sql存放目录

mkdir -p /home/appop/script/MYSQL-MONITOR-SQL/

3.编辑sql文件到步骤3中

cat /home/appop/script/MYSQL-MONITOR-SQL/test.sqlselectcount(*) from information_schema.greatdb_binlog_server_status

4.测试脚本取值

sh /home/appop/script/mysql-monitor.sh greatdb information_schema test.sql0# 脚本监控模板用户实例监控sql

5.zabbix键值及命令格式

system.run[sh /home/appop/script/mysql-monitor.sh greatdb information_schema test.sql]

二、监控数据库binlog状态

连不上实例，说明binlog server实例有问题

以使用greatdb用户查询为例：/greatdb/svr/greatdb/bin/greatsql -uXXXXX -p -h127.0.0.1 -P3310

2.输出结果为0，说明binlog server同步复制服务没有启动

/greatdb/svr/greatdb/bin/greatsql -ugreatdb -p -h127.0.0.1 -P3310 -e"selectcount(*) from information_schema.greatdb_binlog_server_status"

3.输出结果大于0，说明binlog server同步中断，有报错

/greatdb/svr/greatdb/bin/greatsql -ugreatdb -p -h127.0.0.1 -P3310 -e"selectcount(*) from information_schema.greatdb_binlog_server_status where LAST_IO_ERROR!=''or LAST_SQL_ERROR!=''"

4.输出结果大于0，说明binlog server同步有延迟900S

/greatdb/svr/greatdb/bin/greatsql -ugreatdb -p -h127.0.0.1 -P3310 -e"selectcount(*) from information_schema.greatdb_binlog_server_status where SECONDS_BEHIND_SOURCE>900"

5.clickhouse数据库连接监控(ck.sql中放sql语句)

clickhouse-client -h 127.0.0.1 --port 9000 --user "default" --password "password" -m < ~/ck.sql

三、数据库主从复制回流监控

监控脚本

#!/bin/bashhostONE=127.0.0.1 #A主机hostTWO=127.0.0.2 #B主机 monitor= #密码 user= #用户 database= #数据库

 ONE= `greatdb -u$user -h$hostONE -p$user -P6310 $database -sN -e "show master status\G"|grep -o "seconds behind master:"`

 TWO= `greatdb -u$user -h$hostTWO -p$user -P6310 $database -sN -e "show master status\G"|grep -o "seconds behind master:"`

if [ $ONE = $TWO ]&& echo 1|| echo 0=1回流正常=0回流异常

四、zabbix历史数据清理脚本

监控脚本

#!/bin/bashsource /rdata1/encrypt/decrypt.sh #脚本里放监控密码，可避免明文密码#############################清理90天前zabbix历史数据#yeahzxw#20230309############################Date=`date -d $(date -d "-90 day" +%Y%m%d) +%s` #取90天之前的时间戳mysql -uzabbix -p$db_pwd_mysql -h127.0.0.1 -e "use zabbix;DELETEFROM history WHERE clock < $Date;optimizetable history;DELETEFROM history_str WHERE clock < $Date;optimizetable history_str;DELETEFROM history_uint WHERE clock < $Date;optimizetable history_uint;DELETEFROM trends WHERE clock < $Date;optimizetable trends;DELETEFROM trends_uint WHERE clock < $Date;optimizetable trends_uint;DELETEFROMeventsWHERE clock < $Date;optimizetableevents;DELETEFROM history_text WHERE clock < $Date;optimizetable history_text;DELETEFROM history_log WHERE clock < $Date;optimizetable history_log;"

五、数据库状态监控脚本

监控脚本

UserParameter=zxw.mysql.uptime_3306,/usr/bin/mysql_zabbix_3306.sh UptimeUserParameter=zxw.mysql.Slave_readonly_3306,/usr/bin/mysql_zabbix_3306.sh Slave_readonlyUserParameter=zxw.mysql.Slave_running_3306,/usr/bin/mysql_zabbix_3306.sh Slave_runningUserParameter=zxw.mysql.User_connections_3306,/usr/bin/mysql_zabbix_3306.sh User_connectionsUserParameter=zxw.mysql.Master_behind_3306,/usr/bin/mysql_zabbix_3306.sh Master_behindUserParameter=zxw.mysql.Threads_running_3306,/usr/bin/mysql_zabbix_3306.sh Threads_runningUserParameter=zxw.mysql.Master_logfile_3306,/usr/bin/mysql_zabbix_3306.sh Master_logfileUserParameter=zxw.mysql.Master_logpos_3306,/usr/bin/mysql_zabbix_3306.sh Master_logpos#!/bin/bash# FileName: chk_mysql.shMYSQL_USER='user'MYSQL_PWD='password'MYSQL_HOST='127.0.0.1'MYSQL_PORT='3306'MYSQL_CONN="/usr/bin/mysqladmin -u ${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"MYSQL_CMD="/usr/bin/mysql -u ${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"if [ $# -ne "1" ];thenecho"arg error!"ficase$1in Uptime) result=`${MYSQL_CONN} status|cut -f2 -d":"|cut -f1 -d"T"`echo$result ;; Slave_readonly) result=`${MYSQL_CMD} -e 'show variables like "read_only"'|grep "read_only"|grep "ON"|wc -l`echo$result ;; Slave_running)

 result=`${MYSQL_CMD} -e "show slave status\G"|grep -E "Slave_IO_Running|Slave_SQL_Running"|awk '{print $2}'|grep -c Yes`

echo$result ;; User_connections) result=`${MYSQL_CMD} -e "show variables like 'max_user_connections'" |grep connections|awk '{print $2}'`echo$result ;; Master_behind) result=`${MYSQL_CMD} -e "show slave status\G" |grep -E "Seconds_Behind_Master"|awk '{print $2}'`echo$result ;; Threads_running) result=`${MYSQL_CMD} -e "show global status like '%threads_running%'"|grep running |awk '{print $2}'`echo$result# echo 120 ;; Master_logfile) result=`${MYSQL_CMD} -e "show slave status\G" |grep Master_Log_File|awk -F"bin."'{print$2}'|uniq|wc -l`echo$result ;; Master_logpos) result=`${MYSQL_CMD} -e "show slave status\G" |grep Master_Log_Pos|awk '{print$2}'|uniq|wc -l`echo$result ;; *) echo"Usage:$0(Uptime|Slave_readonly|Slave_running|User_connections|Master_behind|Threads_running)" ;; esac

链接：https://blog.51cto.com/1362336072/9510717

（版权归原作者所有，侵删）

继续阅读

阅读原文