Zabbix3.4使用自带模板监控MySQL

原创

年華似水 2018-07-17 14:45:44 博主文章分类：监控 ©著作权

©著作权归作者所有：来自51CTO博客作者年華似水的原创作品，请联系作者获取转载授权，否则将追究法律责任

192.168.254.127 （zabbix） 192.168.254.128 (mysql)

zabbix_agentd客户端设置在mysql数据上创建一个普通用户zabbix,密码zabbix [root@mysql1 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)

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 zabbix@'192.168.254.128' 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> exit Bye 修改/etc/my.cnf文件创建无密码登录(把下面的配置添加进入) [client] user=zabbix password=zabbix

[mysqladmin] host=192.168.254.128 user=zabbix password=zabbix

测试是否可以直接访问不需要输入密码，如果输入命令直接进去说明已OK。 [root@mysql1 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)

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>

创建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

相关配置路径文件 [root@mysql1 scripts]# ll 总用量 4 -rwxr-xr-x 1 root root 2696 7月 17 13:51 chk_mysql.sh [root@mysql1 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客户端服务，查看有没有报错。 [root@mysql1 scripts]# /etc/init.d/zabbix-agent restart Restarting zabbix-agent (via systemctl): [ 确定 ]

在zabbix_server端的浏览器设置收下在zabbix_server端查看下mysql脚本信息是否可以正常获取 [root@zabbix ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.254.128 -p10050 -k mysql.status[Uptime]
48 添加主机。配置--主机填写相关信息。链接相关模板。点击模板选项卡--选择--选中Templeate DB MySQL模板--添加--更新等两分钟(数据默认1分钟来获取数据)，就可以获取相关数据了至此监控mysql完成。