zabbix之数据库相关监控分享
一、利用zabbix监控数据库sql得值
- 编辑监控默认脚本模板(只要监控sql都可以用该脚本,如oracle数据库需改sqlplus格式登录)
cat /home/appop/script/mysql-monitor.sh
host=127.0.0.1 #监控主机IP
monitor='password'#监控密码
name=$3
login (){
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.sql
selectcount(*) from information_schema.greatdb_binlog_server_status
4.测试脚本取值
sh /home/appop/script/mysql-monitor.sh greatdb information_schema test.sql
0
# 脚本监控模板 用户 实例 监控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
三、数据库主从复制回流监控
- 监控脚本
hostONE=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/bash
source /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 Uptime
UserParameter=zxw.mysql.Slave_readonly_3306,/usr/bin/mysql_zabbix_3306.sh Slave_readonly
UserParameter=zxw.mysql.Slave_running_3306,/usr/bin/mysql_zabbix_3306.sh Slave_running
UserParameter=zxw.mysql.User_connections_3306,/usr/bin/mysql_zabbix_3306.sh User_connections
UserParameter=zxw.mysql.Master_behind_3306,/usr/bin/mysql_zabbix_3306.sh Master_behind
UserParameter=zxw.mysql.Threads_running_3306,/usr/bin/mysql_zabbix_3306.sh Threads_running
UserParameter=zxw.mysql.Master_logfile_3306,/usr/bin/mysql_zabbix_3306.sh Master_logfile
UserParameter=zxw.mysql.Master_logpos_3306,/usr/bin/mysql_zabbix_3306.sh Master_logpos
# FileName: chk_mysql.sh
MYSQL_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" ];then
echo"arg error!"
fi
case$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
(版权归原作者所有,侵删)
阅读原文 最新评论
推荐文章
作者最新文章
你可能感兴趣的文章
Copyright Disclaimer: The copyright of contents (including texts, images, videos and audios) posted above belong to the User who shared or the third-party website which the User shared from. If you found your copyright have been infringed, please send a DMCA takedown notice to [email protected]. For more detail of the source, please click on the button "Read Original Post" below. For other communications, please send to [email protected].
版权声明:以上内容为用户推荐收藏至CareerEngine平台,其内容(含文字、图片、视频、音频等)及知识版权均属用户或用户转发自的第三方网站,如涉嫌侵权,请通知[email protected]进行信息删除。如需查看信息来源,请点击“查看原文”。如需洽谈其它事宜,请联系[email protected]。
版权声明:以上内容为用户推荐收藏至CareerEngine平台,其内容(含文字、图片、视频、音频等)及知识版权均属用户或用户转发自的第三方网站,如涉嫌侵权,请通知[email protected]进行信息删除。如需查看信息来源,请点击“查看原文”。如需洽谈其它事宜,请联系[email protected]。