zabbix监控mysql_zabbix mysqladmin -u$1 -p$2 status 2>/dev/null | a

1） 监控mysql主从状态
授权(可以不用)

MariaDB [(none)]> grant all on *.* to check@localhost identified by '123';
version for the right syntax to use near 'check@localhost identified by '123'' at line 1
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4

客户端编辑配置文件

[root@localhost zabbix_agentd.d]# pwd
/etc/zabbix/zabbix_agentd.d
[root@localhost zabbix_agentd.d]# cat check_mysql.conf
UserParameter=mysql_status[*],/etc/zabbix/scripts/check_mysql.sh $1
1
2
3
4

监控mysql主从状态

[root@localhost scripts]# cat /etc/zabbix/scripts/check_mysql.sh
#!/bin/bash
case $1 in
        slave_status)
        mysql -ucheck -p123 -e "show slave status \G;" | grep "Running" | grep "Yes" | awk '{print $2}' | wc -l
        ;;
esac
1
2
3
4
5
6
7

重启一下

[root@localhost scripts]# systemctl restart zabbix-agent
1

serevr 端get 一下值

[root@localhost ~]# zabbix_get -s 192.168.88.17 -k mysql_status[slave_status]
2
1
2

[root@agent zabbix_agentd.d]# pwd
/etc/zabbix/zabbix_agentd.d
[root@agent zabbix_agentd.d]# ls
userparameter_mysql.conf         userparameter_pv_uv.conf
userparameter_mysql_status.conf
[root@agent zabbix_agentd.d]# cat userparameter_mysql_status.conf
UserParameter=mysql_status[*],/etc/zabbix/scripts/mysql.sh $1

[root@localhost scripts]# cat /etc/zabbix/scripts/mysql.sh
#!/bin/bash
uptime=`mysqladmin status | awk '{print $2}'`
case $1 in
        Bytes_sent)
        mysqladmin extended-status | grep "Bytes_sent" | awk '{print $4}'
        ;;
        Bytes_received)
        mysqladmin extended-status | grep "Bytes_received" | awk '{print $4}'
        ;;
        Com_insert)
        mysqladmin extended-status | grep -w  "Com_insert" | awk '{print $4}'
        ;;
        Com_delete)
        mysqladmin extended-status | grep -w  "Com_delete" | awk '{print $4}'
        ;;
        Com_update)
        mysqladmin extended-status | grep -w  "Com_update" | awk '{print $4}'
        ;;
        Com_select)
        mysqladmin extended-status | grep -w  "Com_select" | awk '{print $4}'
        ;;
        Com_commit)
        mysqladmin extended-status | grep -w  "Com_commit" | awk '{print $4}'
        ;;
        Com_rollback)
        mysqladmin extended-status | grep -w  "Com_rollback" | awk '{print $4}'
        ;;
        qps)
        question=`mysqladmin status | awk '{print $6}'`
        qps=$(($question/$uptime))
        echo $qps
        ;;
        tps)
        commit=`mysqladmin extended-status | grep -w 'Com_commit' | awk '{print $4}'`
        rollback=`mysqladmin extended-status | grep -w 'Com_rollback' | awk '{print $4}'`
        tps=$[($commit+$rollback)/$uptime]
        echo $tps
        ;;
        db_size)
        mysql -D information_schema -e "select sum(DATA_LENGTH) from tables where table_schema='mysql'" | sed -n '2p'
        ;;
        table_size)
        mysql -D information_schema -e "select sum(DATA_LENGTH) from tables where table_schema='mysql' and table_name='user'" | sed -n '2p'
        ;;
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

server 端get

[root@localhost ~]# zabbix_get -s 192.168.88.17 -k mysql_status[Bytes_sent]
45471333
[root@localhost ~]# zabbix_get -s 192.168.88.17 -k mysql_status[Bytes_received]
90213020
[root@localhost ~]# zabbix_get -s 192.168.88.17 -k mysql_status[Com_insert]
1645
[root@localhost ~]# zabbix_get -s 192.168.88.17 -k mysql_status[Com_delete]
12
[root@localhost ~]# zabbix_get -s 192.168.88.17 -k mysql_status[Com_update]
383
[root@localhost ~]# zabbix_get -s 192.168.88.17 -k mysql_status[Com_select]
1792
[root@localhost ~]# zabbix_get -s 192.168.88.17 -k mysql_status[Com_commit]
1039
[root@localhost ~]# zabbix_get -s 192.168.88.17 -k mysql_status[Com_rollback]
0
[root@localhost ~]# zabbix_get -s 192.168.88.8 -k mysql.status[table_size]
420
[root@localhost ~]# zabbix_get -s 192.168.88.8 -k mysql.status[db_size]
555558
[root@localhost ~]# zabbix_get -s 192.168.88.8 -k mysql.status[qps]
15
[root@localhost ~]# zabbix_get -s 192.168.88.8 -k mysql.status[tps]
2

服务端get 不到值
[root@server ~]# zabbix_get -s 10.211.55.5 -k mysql_status[Bytes_sent]
ZBX_NOTSUPPORTED: Unsupported item key.

客户端重启agent
[root@agent zabbix_agentd.d]# systemctl restart zabbix-agent
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

出现NULL

去做visudo错误
去web