《Centos7——zabbix监控mysql的常规操作》
最后发布:2020-09-22 21:21:01
首次发布:2020-09-22 21:21:01
zabbix监控平台监控mysql的各项数据指标
1. 搭建zabbix
http://blog.csdn.net/weixin_45842014/article/details/108723881
2. 监控数据
192.168.179.131(客户端)
vim /etc/zabbix/zabbix_agentd.d/mysql.conf
UserParameter=mysql[*],/etc/zabbix/scripts/mysql.sh $1
- 1
- 2
vim /etc/zabbix/scripts/mysql.sh
#!/bin/bash
mysql="mysql -ucheck -p123 -h 192.168.179.131"
case $1 in
port)
##端口
ss -lptnu|grep 3306|wc -l
;;
process)
##进程
$mysql -e "show processlist"|wc -l
;;
status)
mysql -uroot -e "show slave status\G;" 2>/dev/null |egrep "Running|Yes" |head -n2|awk -F ':' '{print $2}'|wc -l
;;
#每秒可以处理的请求
qps)
question=`mysql -uroot -s -e 'show global status like "Question%";'|awk '{print $NF}'`
time=`uptime|awk '{print $3}'|sed "s/,//g"|awk -F ':' '{print $1*3600+$2*60}'`
qps=`echo "scale=4;$question/$time"|bc`
echo $qps
;;
#每秒可以处理的事务量
#事务提交(commit)与回滚(rollback)
tps)
commit=`mysql -uroot -s -e "show global status like 'Com_commit';"|awk '{print $NF}'`
rollback=`mysql -uroot -s -e "show global status like 'Com_rollback';"|awk '{print $NF}'`
time=time=`uptime |awk '{print $3*60}'`
count=$((commit+rollback))
tps=`echo "scale=4;$count/$time"|bc`
echo $tps
;;
#监控MySQL的流量
#发送
Bytes_sent)
mysqladmin -uzabbix -pzabbix -S /var/lib/mysql/mysql.sock extended-status|grep -w "Bytes_sent"|awk '{print $4}'
;;
#接收
Bytes_received)
mysqladmin -uroot -S /var/lib/mysql/mysql.sock extended-status|grep -w "Bytes_received"|awk '{print $4}'
;;
#mysql的吞吐量
#增加
Com_insert)
mysqladmin -uroot extended-status|grep -w Com_insert|awk '{print $4}'
;;
#删除
Com_delete)
mysqladmin -uroot extended-status|grep -w Com_delete|awk '{print $4}'
;;
Com_update)
#修改
mysqladmin -uroot extended-status|grep -w Com_update|awk '{print $4}'
;;
#查看
Com_select)
mysqladmin -uroot extended-status|grep -w Com_select|awk '{print $4}'
;;
#数据库和指定表的大小
db_size)
mysql -uroot -e "select sum(data_length) from information_schema.tables where table_schema='mysql';"|sed -n "2p"
;;
tb_size)
mysql -uroot -e "select sum(data_length) from information_schema.tables where table_schema='mysql' and table_name='user';"|awk 'NR==2'
;;
esac
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
3. 测试能否获取数据
服务端
yum -y install zabbix-get
- 1
[root@localhost ~]# zabbix_get -s 192.168.194.130 -k "mysql[qps]"
13.9324
[root@localhost ~]# zabbix_get -s 192.168.194.130 -k "mysql[tps]"
246.5666
[root@localhost ~]# zabbix_get -s 192.168.194.130 -k "mysql[Bytes_sent]"
86896767
[root@localhost ~]# zabbix_get -s 192.168.194.130 -k "mysql[Bytes_received]"
27729800
[root@localhost ~]# zabbix_get -s 192.168.194.130 -k "mysql[Com_insert]"
37217
[root@localhost ~]# zabbix_get -s 192.168.194.130 -k "mysql[Com_delete]"
305
[root@localhost ~]# zabbix_get -s 192.168.194.130 -k "mysql[Com_update]"
1096
[root@localhost ~]# zabbix_get -s 192.168.194.130 -k "mysql[Com_select]"
73931
[root@localhost ~]# zabbix_get -s 192.168.194.130 -k "mysql[db_size]"
555558
[root@localhost ~]# zabbix_get -s 192.168.194.130 -k "mysql[tb_size]"
420
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
流量监测
数据库的增删改查
监测库和表的大小
每秒可以处理的请求