问题描述
今天计划在zabbix后台增加item监控项,于是到数据库中查看相关表值,发现Navicat 软件无法连接MySQL数据库,并提示报错信息:
2003 - Can’t connect to MySQL server on ‘192.1 68.245.132’ (10060 “Unknown error”)
经过不断分析排查,终于找到问题所在,并得以解决。
问题所在
最终发现问题为以下3点:
① MySQL密码为空 (因为是从官网直接下载安装的zabbix_server 虚拟机,数据库默认密码为空);
② 没有授权root从其他计算机远程登录;
③ 没有将数据库端口3306在防火墙上放行;
解决办法
- MySQL设置密码:
mysql> SET PASSWORD = 'zabbix'; //设置密码,注意一定大写
- 授权root允许从其他计算机远程登录;
mysql>use mysql;
mysql>select host, user from user; //查看主机、用户关系表
mysql>update user set host = '%' where user ='root'; //更新root登录主机权限
mysql> FLUSH PRIVILEGES; //保存生效
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; //赋予任何主机访问数据的权限
mysql> FLUSH PRIVILEGES; //再次保存生效
授权前后user和host关系表;
# 授权root前
mysql> select host, user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
| localhost | zabbix_srv |
| localhost | zabbix_web |
+-----------+------------------+
## 授权root后
mysql> select host, user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | zabbix_srv |
| localhost | zabbix_web |
+-----------+------------------+
- 在防火墙上将数据库端口3306放行;
/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
service iptables save ##保存防火墙策略
补充说明
在通过 service iptables save 保存防火墙策略时,提示如下错误:
iptables: Saving firewall rules to /etc/sysconfig/iptables: /usr/libexec/iptables/iptables.init: line 237: restorecon: command not found
[FAILED]
可通过安装policycoreutils 解决此问题;
yum install policycoreutils
安装完毕之后,再次保存防火墙策略,发现可以正常保存;
service iptables save
iptables: Saving firewall rules to /etc/sysconfig/iptables:[ OK ]
- 通过Navicat再次连接MySQL数据库,发现已经可以正常连接访问;