Zabbix监控mysql主从数据库在脚步出现用户名和密码是会出现如下报错“Warning: Using a password on the command line interface can be insecure”,报错原因是mysql 5.6版本增加了密码安全策略,之前版本可以使用的命令行里加上密码就会强制报错,所以使用zabbix监控mysql的时候,就会由于收到zabbix客户端日志报错信息。结合了网友的解决方案,现将整理出来供大家参考。
一,zabbix被监控端的设置:
1,首先配置mysql数据库,配置mysql的--login-pathde 安全登录:
设置--login-path:
[root@mysql conf]# mysql_config_editor set --login-path=local --host=localhost --user=zabbix -p Enter password: [root@mysql conf]# mysql_config_editor print --all [local] user = zabbix password = ***** host = localhost
命令解释:
--login-path是设置访问的名字,我设置的local;
--host是指定允许访问的host地址,这个地址是你grant的时候配置的;
--user是用户名,也是grant时候配置的;
-p是指定密码,同样是grant配置
2,进入mysql,修改zabbix账号的权限和密码:
mysql> create user 'zabbix' identified by 'zabbix'; #(已创建会显示为0) Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +------------+--------------+ | user | host | +------------+--------------+ | databak | % | | ppt | % | | root | % | | slave_user | % | | zabbix | % | | slave_user | 192.168.1.49 | +------------+--------------+ 6 rows in set (0.00 sec) mysql> GRANT ALL PRIVILEGES ON `zabbix`.* TO 'zabbix'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> update user set password=password('zabbix') where user='zabbix'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit -> ; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
3,测试:mysql --login-path=local
[root@mysql conf]# mysql --login-path=local Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 542 Server version: 5.6.25-log Source distribution Copyright (c) 2000, 2015, 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> quit
以上就配置好了安全访问模式。
二,在客户端里,需要把监控的内容json化展示,然后服务端可以通过正则表达式过滤出结果,脚步的内容如下,然后放到/usr/local/zabbix/bin 文件夹里。
#!/bin/bash #Fucation:mysql low-level discovery #Script_name mysql_low_discovery.sh mysql() { port=($(sudo /bin/netstat -tpln | awk -F "[ :]+" '/[m]ysql/ {print $4}')) printf '{\n' printf '\t"data":[\n' for key in ${!port[@]} do if [[ "${#port[@]}" -gt 1 && "${key}" -ne "$((${#port[@]}-1))" ]];then socket=`ps aux|grep ${port[${key}]}|grep -v grep|awk -F '=' '{print $10}'|cut -d ' ' -f 1` printf '\t {\n' printf "\t\t\t\"{#MYSQLPORT}\":\"${port[${key}]}\"},\n" else [[ "${key}" -eq "((${#port[@]}-1))" ]] socket=`ps aux|grep ${port[${key}]}|grep -v grep|awk -F '=' '{print $10}'|cut -d ' ' -f 1` printf '\t {\n' printf "\t\t\t\"{#MYSQLPORT}\":\"${port[${key}]}\"}\n" fi done printf '\t ]\n' printf '}\n' } $1
结果如下图:
2,给脚本low_level_discovery赋可执行权限
[root@mysql conf]# chmod +x /usr/local/zabbix/bin/low_level_discovery [root@mysql conf]# ll /usr/local/zabbix/bin/low_level_discovery -rwxr-xr-x 1 root root 1055 Nov 11 15:45 /usr/local/zabbix/bin/low_level_discovery
3,允许zabbix用户无密码运行mysql,netstat,/usr/local/mysql/bin是mysql程序地址,可根据情况自由修改。
[root@mysql bin]# echo "zabbix ALL=(root) NOPASSWD:/usr/local/mysql/bin/mysql,/bin/netstat" >> /etc/sudoers
下图所示:
5,修改zabbix_agentd.conf文件的最后添加一下内容:
UserParameter=zabbix_low_discovery[*],/bin/bash /usr/local/zabbix/bin/low_level_discovery $1 UserParameter=mysql_stats_5.6[*],sudo /usr/local/mysql/bin/mysql --login-path=local -P $1 -e "show global status"|grep "\<$2\>"|cut -f2 UserParameter=mysql_stats_slave_5.6[*],sudo /usr/local/mysql/bin/mysql --login-path=local -P $1 -e "show slave status\G"|grep "\<$2\>"|awk '{if($NF=="Yes") {print 1} else {print 0}}'
6,在服务端进行测试,语句为
[root@zabbix bin]# zabbix_get -s192.168.1.244 -p10050 -k zabbix_low_discovery[mysql] { "data":[ { "{#MYSQLPORT}":"3306"} ] }