zabbix mysql 3.4_Zabbix3.4使用自带模板监控MySQL

zabbix_agentd客户端设置

在mysql数据上创建一个普通用户zabbix,密码zabbix

[[email protected] scripts]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> grant all PRIVILEGES on . to [email protected] identified by ‘zabbix‘; ###创建一个有权限的访问用户zabbix密码设置zabbix

Query OK, 0 rows affected (0.04 sec)

mysql> update mysql.user set authentication_string=password(‘zabbix‘) where user=‘zabbix‘ and Host = ‘192.168.254.128‘; ###更新下改用户的密码

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

查看用户

mysql> select Host,User,authentication_string from mysql.user;

+-----------------+---------------+-------------------------------------------+

| Host | User | authentication_string |

+-----------------+---------------+-------------------------------------------+

| localhost | root | 182EA09A38F1141B2D7916812BD097D51167C3E4 |

| localhost | mysql.session |THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| localhost | mysql.sys | THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| % | root |182EA09A38F1141B2D7916812BD097D51167C3E4 |

| 192.168.254.% | slave_cp | 196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |

| 192.168.254.128 | zabbix |DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |

+-----------------+---------------+-------------------------------------------+

6 rows in set (0.00 sec)

mysql> exit

Bye

修改/etc/my.cnf文件创建无密码登录(把下面的配置添加进入)

[client]

user=zabbix

password=zabbix

[mysqladmin]

host=192.168.254.128

user=zabbix

password=zabbix

测试是否可以直接访问不需要输入密码，如果输入命令 直接进去说明已OK。

[[email protected] scripts]# mysql -uzabbix -h192.168.254.128

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 189

Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql>

现在数据库端 测试 是否可以查看到相关数据

mysqladmin extended-status |grep -w "Bytes_received" |cut -d"|" -f3 ###有数据返回说明正常

[[email protected] scripts]# mysqladmin extended-status |grep -w "Bytes_received" |cut -d"|" -f3

58691

创建mysql监控脚本在目录/etc/zabbix/scripts/chk_mysql.sh并赋予相关的权限。

#!/bin/bash

-------------------------------------------------------------------------------

FileName: check_mysql.sh

Revision: 1.0

Date: 2018/07/17

Author: 胖虎

Email:

Website:

Description:

Notes: ~

-------------------------------------------------------------------------------

Copyright: 2018 (c)

License: GPL

用户名

MYSQL_USER=‘zabbix‘

密码

MYSQL_PWD=‘zabbix‘

主机地址/IP

MYSQL_HOST=‘192.168.254.128‘

端口

MYSQL_PORT=‘3306‘

数据连接

#MYSQL_CONN="/usr/local/mysql/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"

MYSQL_CONN="/usr/local/mysql/bin/mysqladmin"

参数是否正确

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

;;

Com_update)

result=${MYSQL_CONN} extended-status |grep -w "Com_update"|cut -d"|" -f3

echo $result

;;

Slow_queries)

result=${MYSQL_CONN} status |cut -f5 -d":"|cut -f1 -d"O"

echo $result

;;

Com_select)

result=${MYSQL_CONN} extended-status |grep -w "Com_select"|cut -d"|" -f3

echo $result

;;

Com_rollback)

result=${MYSQL_CONN} extended-status |grep -w "Com_rollback"|cut -d"|" -f3

echo $result

;;

Questions)

result=${MYSQL_CONN} status|cut -f4 -d":"|cut -f1 -d"S"

echo $result

;;

Com_insert)

result=${MYSQL_CONN} extended-status |grep -w "Com_insert"|cut -d"|" -f3

echo $result

;;

Com_delete)

result=${MYSQL_CONN} extended-status |grep -w "Com_delete"|cut -d"|" -f3

echo $result

;;

Com_commit)

result=${MYSQL_CONN} extended-status |grep -w "Com_commit"|cut -d"|" -f3

echo $result

;;

Bytes_sent)

result=${MYSQL_CONN} extended-status |grep -w "Bytes_sent" |cut -d"|" -f3

echo $result

;;

Bytes_received)

result=${MYSQL_CONN} extended-status |grep -w "Bytes_received" |cut -d"|" -f3

echo $result

;;

Com_begin)

result=${MYSQL_CONN} extended-status |grep -w "Com_begin"|cut -d"|" -f3

echo $result

;;

*)

echo "Usage:$0(Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin)"

;;

esac

相关配置路径文件

[[email protected] scripts]# ll

总用量 4

-rwxr-xr-x 1 root root 2696 7月 17 13:51 chk_mysql.sh

[[email protected] scripts]# pwd

/etc/zabbix/scripts

修改zabbix_agentd.conf添加以下参数：

vi /etc/zabbix/zabbix_agentd.conf (没有的就添加)

PidFile=/tmp/zabbix_agentd.pid

LogFile=/var/log/zabbix/zabbix_agentd.log

LogFileSize=0

Server=192.168.254.127

ServerActive=192.168.254.127

Hostname=192.168.254.128

RefreshActiveChecks=120

Timeout=20

Include=/etc/zabbix/zabbix_agentd.d/.conf

UnsafeUserParameters=1

UserParameter=mysql.version,mysql -V

UserParameter=mysql.status[],/etc/zabbix/scripts/chk_mysql.sh $1

UserParameter=mysql.ping,/usr/local/mysql/bin/mysqladmin -uzabbix ping | grep -c alive

重启zabbix_agentd客户端服务，查看有没有报错。

[[email protected] scripts]# /etc/init.d/zabbix-agent restart

Restarting zabbix-agent (via systemctl): [ 确定 ]

在zabbix_server端的浏览器设置

收下在zabbix_server端查看下mysql脚本信息是否可以正常获取

[[email protected] ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.254.128 -p10050 -k mysql.status[Uptime]

48

添加主机。配置--主机填写相关信息。

链接相关模板。点击模板选项卡--选择--选中Templeate DB MySQL模板--添加--更新

等两分钟(数据默认1分钟来获取数据)，就可以获取相关数据了

至此监控mysql完成。

原文：http://blog.51cto.com/10158955/2145808