Java配合mysql实时统计业务_zabbix实时监控mysql业务数据

zabbix实时监控mysql业务数据

1. 安装zabbix agent

下载zabbix:过往的软件包都有:https://sourceforge.mirrorservice.org/z/za/zabbix/ZABBIX%20Latest%20Stable/

2. 安装agent

tar xf zabbix-3.4.13.tar.gz

cd zabbix-3.4.13

./configure --prefix=/usr/local/zabbix-3.2.0--with-net-snmp --enable-agent

make

make install

3. 修改zabbix配置文件，开启自定义key

Server=zabbix_server端ip

ServerActive=zabbix_server端ip

Hostname=zabbix_agent端主机名和ip #要和server段web页面创建主机时的主机名一致

UnsafeUserParameters=1 #1为开启自定义key

Include=/etc/zabbix/zabbix_agentd.d/*.conf #在zabbix_agentd.d目录下以.conf结尾的配置文件和nginx是一样的

4. 创建scripts目录 #存放zabbix脚本

#!/bin/bash

HOSTNAME="数据库ip" #需要查询的业务数据库的ip，不是zabbix_server的数据库

PORT="3308" #连接数据库端口

USERNAME="zabbix" #连接数据库的用户名

PASSWORD="zabbix" #连接数据库的密码

DBNAME="数据库名" #需要连接的库名

#统计前一天交易金额

select_sql="select sum(k.order_amount) from rp_trade_payment_order k where k.status='SUCCESS' and date_format(k.create_time,'%Y-%m-%d')= date_format(DATE_SUB(now(),INTERVAL 1 day),'%Y-%m-%d');"

mysql -h${HOSTNAME} -p${PASSWORD} -u${USERNAME} -P${PORT} -D ${DBNAME} -e "${select_sql}"

#统计历史交易金额

select_his_sql="select sum(k.order_amount) from rp_trade_payment_order k where k.status='SUCCESS' and k.create_time<=DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s') and k.create_time>str_to_date('2018-08-05 23:59:59', '%Y-%m-%d %H:%i:%s') and k.id!='f42de3c23807440084d965787c79b885';"

mysql -h${HOSTNAME} -p${PASSWORD} -u${USERNAME} -P${PORT} -D ${DBNAME} -e "${select_his_sql}"

#统计前一天交易笔数

select_num="select count(*) from rp_trade_payment_order k where k.status='SUCCESS' and date_format(k.create_time,'%Y-%m-%d')= date_format(DATE_SUB(now(),INTERVAL 1 day),'%Y-%m-%d');"

mysql -h${HOSTNAME} -p${PASSWORD} -u${USERNAME} -P${PORT} -D ${DBNAME} -e "${select_num}"

#统计历史交易笔数

select_num_his="select count(*) from rp_trade_payment_order k where k.status='SUCCESS' and k.create_time<=DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s');"

mysql -h${HOSTNAME} -p${PASSWORD} -u${USERNAME} -P${PORT} -D ${DBNAME} -e "${select_num_his}"

#线下前一天交易金额

Uline_amount_sql="select sum(k.order_amount) from rp_trade_payment_order k where k.status='SUCCESS' and k.pay_type ='1' and date_format(k.create_time,'%Y-%m-%d')= date_format(DATE_SUB(now(),INTERVAL 1 day),'%Y-%m-%d');"

mysql -h${HOSTNAME} -p${PASSWORD} -u${USERNAME} -P${PORT} -D ${DBNAME} -e "${Uline_amount_sql}"

#线下历史交易金额

Uline_his_amount_sql="select sum(k.order_amount) from rp_trade_payment_order k where k.status='SUCCESS' and k.pay_type ='1' and k.create_time<=DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s') and k.create_time>str_to_date('2018-08-05 23:59:59', '%Y-%m-%d %H:%i:%s') and k.id!='f42de3c23807440084d965787c79b885';"

mysql -h${HOSTNAME} -p${PASSWORD} -u${USERNAME} -P${PORT} -D ${DBNAME} -e "${Uline_his_amount_sql}"

#线下前一天交易笔数

Uline_num="select count(*) from rp_trade_payment_order k where k.status='SUCCESS' and k.pay_type ='1' and date_format(k.create_time,'%Y-%m-%d')= date_format(DATE_SUB(now(),INTERVAL 1 day),'%Y-%m-%d');"

mysql -h${HOSTNAME} -p${PASSWORD} -u${USERNAME} -P${PORT} -D ${DBNAME} -e "${Uline_num}"

#线下历史交易笔数

Uline_num_his="select count(*) from rp_trade_payment_order k where k.status='SUCCESS' and k.pay_type ='1' and k.create_time<=DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s');"

mysql -h${HOSTNAME} -p${PASSWORD} -u${USERNAME} -P${PORT} -D ${DBNAME} -e "${Uline_num_his}"

#线上交易金额

Oline_amount="select sum(k.order_amount) from rp_trade_payment_order k where k.status='SUCCESS' and k.pay_type ='2' and date_format(k.create_time,'%Y-%m-%d')= date_format(DATE_SUB(now(),INTERVAL 1 day),'%Y-%m-%d');"

mysql -h${HOSTNAME} -p${PASSWORD} -u${USERNAME} -P${PORT} -D ${DBNAME} -e "${Oline_amount}"

#线上历史交易金额

Oline_amount_his="select sum(k.order_amount) from rp_trade_payment_order k where k.status='SUCCESS' and k.pay_type ='2' and k.create_time<=DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s');"

mysql -h${HOSTNAME} -p${PASSWORD} -u${USERNAME} -P${PORT} -D ${DBNAME} -e "${Oline_amount_his}"

#线上交易笔数

Oline_num="select count(*) from rp_trade_payment_order k where k.status='SUCCESS' and k.pay_type ='2' and date_format(k.create_time,'%Y-%m-%d')= date_format(DATE_SUB(now(),INTERVAL 1 day),'%Y-%m-%d');"

mysql -h${HOSTNAME} -p${PASSWORD} -u${USERNAME} -P${PORT} -D ${DBNAME} -e "${Oline_num}"

#线上历史交易笔数

Oline_num_his="select count(*) from rp_trade_payment_order k where k.status='SUCCESS' and k.pay_type ='2' and k.create_time<=DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s');"

5. 创建配置文件 #自定义key名，获取脚本执行的结果

#统计前一天交易金额，UserParameter=后面是key名, ","后面命令获取到的值为key值，2>/dev/null输出至回收站，awk 'NR==2{print $1}'抓取需要的值作为key值

UserParameter=bs.9lipay.daily.transaction.amout, /etc/zabbix/scripts/business_monitor.sh 2>/dev/null| awk 'NR==2{print $1}'

#统计历史交易金额

UserParameter=bs.9lipay.his.transaction.amout, /etc/zabbix/scripts/business_monitor.sh 2>/dev/null| awk 'NR==4{print $1}'

#统计前一天交易笔数

UserParameter=bs.9lipay.transaction.num, /etc/zabbix/scripts/business_monitor.sh 2>/dev/null| awk 'NR==6{print $1}'

#统计历史交易笔数

UserParameter=bs.9lipay.his.transaction.num, /etc/zabbix/scripts/business_monitor.sh 2>/dev/null| awk 'NR==8{print $1}'

#线下前一天交易金额

UserParameter=bs.9lipay.Uline.transaction.amout, /etc/zabbix/scripts/business_monitor.sh 2>/dev/null| awk 'NR==10{print $1}'

#线下历史交易金额

UserParameter=bs.9lipay.Uline..his.transaction.amout, /etc/zabbix/scripts/business_monitor.sh 2>/dev/null| awk 'NR==12{print $1}'

#线下前一天交易笔数

UserParameter=bs.9lipay.Uline.transaction.num, /etc/zabbix/scripts/business_monitor.sh 2>/dev/null| awk 'NR==14{print $1}'

#线下历史交易笔数

UserParameter=bs.9lipay.Uline.his.transaction.num, /etc/zabbix/scripts/business_monitor.sh 2>/dev/null| awk 'NR==16{print $1}'

#线上交易金额

UserParameter=bs.9lipay.Oline.transaction.amount, /etc/zabbix/scripts/business_monitor.sh 2>/dev/null| awk 'NR==18{print $1}'

#线上历史交易金额

UserParameter=bs.9lipay.Oline.his.transaction.amount, /etc/zabbix/scripts/business_monitor.sh 2>/dev/null| awk 'NR==20{print $1}'

#线上交易笔数

UserParameter=bs.9lipay.Oline.transaction.num, /etc/zabbix/scripts/business_monitor.sh 2>/dev/null| awk 'NR==22{print $1}'

#线上历史交易笔数

UserParameter=bs.9lipay.Oline.his.transaction.num, /etc/zabbix/scripts/business_monitor.sh 2>/dev/null| awk 'NR==24{print $1}'
在zabbix_server端获取key值:zabbix_get -s zabbix_agent端ip -k 自定义的key值

6. 开始在zabbix_server web上配置自定义监控项

配置--->主机--->创建主机--->监控项--->创建监控项--->将自定义的key名填入键值

7. 将监控项创建好之后，创建图形