zabbix之数据库相关监控

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

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

cat /home/appop/script/mysql-monitor.sh

#!/bin/bash
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
select count(*) 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状态

1.连不上实例，说明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"select count(*) from information_schema.greatdb_binlog_server_status"

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

/greatdb/svr/greatdb/bin/greatsql -ugreatdb -p -h127.0.0.1 -P3310 -e"select count(*) 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"select count(*) 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

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

1.监控脚本

#!/bin/bash
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历史数据清理脚本

1.监控脚本

#!/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;
DELETE FROM history WHERE clock < $Date;
optimize table history;
DELETE FROM history_str WHERE clock < $Date;
optimize table history_str;
DELETE FROM history_uint WHERE clock < $Date;
optimize table history_uint;
DELETE FROM  trends WHERE clock < $Date;
optimize table  trends;
DELETE FROM trends_uint WHERE clock < $Date;
optimize table trends_uint;
DELETE FROM events WHERE clock < $Date;
optimize table events;
DELETE FROM history_text WHERE clock < $Date;
optimize table history_text;
DELETE FROM history_log WHERE clock < $Date;
optimize table history_log;
"

五、数据库状态监控脚本

1.监控脚本

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

#!/bin/bash
# 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 $1 in 
    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