Zabbix使用自带模板监控MySQL

共计 7684 个字符，预计需要花费 20 分钟才能阅读完成。

（一）简介

Zabbix 在监控 MySQL 数据库时，会使用自带的模板 Template App MySQL，是不能直接使用的，因为没有 key, 而获取不到数据，前端会出现如下报错“Warning: Using a password on the command line interface can be insecure.”报错原因是 mysql 5.6 以后的版本增加了密码安全策略，在命令行里加上密码就会强制报错，而 5.6 之前版本可以直接使用的。

前边有篇文章是通过 mysql –login-path=local 来进行无密码验证登录，而本文是通过另一种方法通过修改 /etc/my.cnf 来进行无密码验证登录。

错误探究：这个提示在 mysql 5.5之后会出现。而且，这个提示，会被 zabbix-servre 捕捉到，在 zabbix-server.log 日志中会出现

24123:20160826:101433.609 error reason for "110:mysql.status[Bytes_sent]" changed: Received value [mysqladmin: [Warning] Using a password on the command line interface can be insecure.8991074] is not suitable for value type [Numeric (float)]
提示参数不符合
在 zabbix-server 服务器上面，使用 zabbix-get 命令时

[root@localhost ~]# /usr/local/zabbix/bin/zabbix_get -s 172.21.100.12 -p10050 -k mysql.status[Uptime]

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

522345
也就是说 zabbix_key 获取的 key 值是“mysqladmin: [Warning] Using a password on the command line interface can be insecure.”而不是522345, 造成服务端的数值类型不对，为解决这个问题必须使用无密码登录才可以，为了安全，只允许 localhost 进行登录。

（二）背景简介

（三）具体实施步骤

一，zabbix_agentd 客户端设置
1. 在 mysql 数据上创建一个普通用户 lqb

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 756
Server version: 5.5.58-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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 lqb@'localhost' identified by '123456';  ### 创建一个有权限的访问用户 lqb 密码设置 123456
Query OK, 0 rows affected (0.04 sec)

mysql> update mysql.user set authentication_string=password('123456') where user='lqb' and Host = 'localhost';     ### 更新下改用户的密码
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> exit
Bye

2. 修改 /etc/my.cnf 文件创建无密码登录

[root@localhost ~]# vim /etc/my.cnf
[client]
user=lqb
password=123456


[mysqladmin]
host=localhost
user=lqb
password=123456

3. 测试是否可以直接访问，如果输入命令 mysql -ulqb 直接进去说明已 OK。

[root@localhost ~]# mysql -ulqb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 761
Server version: 5.5.58-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> select Host,User,authentication_string from mysql.user;
+-----------------------+--------+-------------------------------------------+
| Host                  | User   | authentication_string                     |
+-----------------------+--------+-------------------------------------------+
| localhost             | root   |                                           |
| localhost.localdomain | root   |                                           |
| 127.0.0.1             | root   |                                           |
| ::1                   | root   |                                           |
| %                     | root   | NULL                                      |
| %                     | zabbix | NULL                                      |
| localhost             | lqb    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 172.21.100.12         | zabbix | NULL                                      |
+-----------------------+--------+-------------------------------------------+
8 rows in set (0.01 sec)

mysql> exit;
Bye

[root@localhost scripts]# mysqladmin extended-status |grep -w "Bytes_received" |cut -d"|" -f3   ### 有数据返回说明正常
 10792596272

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

[root@localhost scripts]# cat /usr/local/zabbix/scripts/chk_mysql.sh 
#!/bin/bash
# -------------------------------------------------------------------------------
# FileName:    check_mysql.sh
# Revision:    1.0
# Date:        2018/01/04
# Author:      lqb
# Email:       
# Website:     
# Description: 
# Notes:       ~
# -------------------------------------------------------------------------------
# Copyright:   2015 (c) DengYun
# License:     GPL
# 用户 MYSQL_USER='zabbix'
MYSQL_USER='lqb'
# 密码
MYSQL_PWD='123456'
# 主机地址 /IP
MYSQL_HOST='localhost'
# 端口
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@localhost zabbix]# chmod +x scripts/chk_mysql.sh 
[root@localhost zabbix]# chown -R zabbix.zabbix scripts/chk_mysql.sh 
[root@localhost zabbix]# ll scripts/
total 4
-rwxr-xr-x 1 zabbix zabbix 2675 Jan  4 04:01 chk_mysql.sh
null

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

UserParameter=mysql.version,mysql -V
UserParameter=mysql.status[*], /usr/local/zabbix/scripts/chk_mysql.sh $1
UserParameter=mysql.ping,/usr/local/mysql/bin/mysqladmin -ulqb ping | grep -c alive

[root@localhost scripts]# grep '^[a-Z]' /usr/local/zabbix/etc/zabbix_agentd.conf
LogFile=/tmp/zabbix_agentd.log
Server=172.20.66.110
Hostname=172.21.100.12
RefreshActiveChecks=120
Timeout=20
UserParameter=mysql.version,mysql -V
UserParameter=mysql.status[*], /usr/local/zabbix/scripts/chk_mysql.sh $1
UserParameter=mysql.ping,/usr/local/mysql/bin/mysqladmin -ulqb ping | grep -c alive

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

[root@localhost scripts]# /etc/init.d/zabbix_agentd restart
Restarting zabbix_agentd (via systemctl):                  [OK]
[root@localhost scripts]# tail -f /tmp/zabbix_agentd.log 
 24025:20180104:041135.331 **** Enabled features ****
 24025:20180104:041135.331 IPv6 support:           NO
 24025:20180104:041135.331 TLS support:            NO
 24025:20180104:041135.331 **************************
 24025:20180104:041135.331 using configuration file: /usr/local/zabbix/etc/zabbix_agentd.conf
 24025:20180104:041135.331 agent #0 started [main process]
 24030:20180104:041135.332 agent #4 started [listener #3]
 24028:20180104:041135.332 agent #2 started [listener #1]
 24027:20180104:041135.332 agent #1 started [collector]
 24029:20180104:041135.333 agent #3 started [listener #2]

二. 在 zabbix_server 端的浏览器设置
1. 收下在 zabbix_server 端查看下 mysql 脚本信息是否可以正常获取

[root@localhost slow]# /usr/local/zabbix/bin/zabbix_get -s 172.21.100.12 -p10050 -k mysql.status[Uptime]
2165105

2. 添加主机。配置 – 主机填写相关信息。

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

4. 查看监控项有没有红色告警，没有就算正常的。

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

至此监控 mysql 完成。

更多 Zabbix 相关教程集合：

在 Ubuntu 16.04 服务器上安装 Zabbix 3.2  http://www.linuxidc.com/Linux/2017-07/145519.htm

CentOS 7 LNMP 环境搭建 Zabbix3.0  http://www.linuxidc.com/Linux/2017-02/140134.htm

Ubuntu 16.04 安装部署监控系统 Zabbix2.4  http://www.linuxidc.com/Linux/2017-03/141436.htm

Zabbix 监控安装部署及警报配置  http://www.linuxidc.com/Linux/2017-03/141611.htm

Zabbix 触发器表达式详解 http://www.linuxidc.com/Linux/2017-03/141921.htm

Ubuntu 16.04 下安装部署 Zabbix3.0  http://www.linuxidc.com/Linux/2017-02/140395.htm

CentOS 6.3 下 Zabbix 监控 apache server-status http://www.linuxidc.com/Linux/2013-05/84740.htm

CentOS 7 下 Zabbix 3.0 安装详解 http://www.linuxidc.com/Linux/2017-03/141716.htm

64 位 CentOS 6.2 下安装 Zabbix 2.0.6  http://www.linuxidc.com/Linux/2014-11/109541.htm

Zabbix 3.2.6 通过 Orabbix 监控 Oracle 数据库  http://www.linuxidc.com/Linux/2017-10/147224.htm

ZABBIX 的详细介绍：请点这里
ZABBIX 的下载地址：请点这里

本文永久更新链接地址：http://www.linuxidc.com/Linux/2018-01/150088.htm