（一）简介
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进行登录。
（二）背景简介
| IP | 主机名 | 功能 |
|---|---|---|
| 172.20.66.110 | zabbix_server | zabbix 服务端 |
| 172. 21.100.12 | zabbix_agent | zabbix客户端数据库 |
（三）具体实施步骤
一，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完成。
