无法远程访问数据库原因
原因1:用户无远程访问主机配置
在MySQL官方文档6.2.6 Access Control, Stage 1: Connection Verification中指出,用户访问数据库需要进行用户主机验证,当用户远程访问主机配置为localhost
时,无法实现远程访问
原因2:操作系统默认防火墙配置禁止数据库3306端口
以CentOS7为例,在安装操作系统后,Firewall属于默认安装且随开机自启,默认配置禁用3306端口
原因3:MySQL配置文件中设置本地IP/localhost绑定
[root@localhost ~]# vi /etc/my.cnf
对应解决方法
原因1解决方法:
使用root用户本地登录数据库,使用UPDATE语句修改mysql数据中user表对应用户的host字段为%
或指定域名/IP
,如下操作:
mysql> use mysql
Database changed
mysql> select user,host from user where user='jack';
+------------------+-----------+
| user | host |
+------------------+-----------+
| jack | localhost |
+------------------+-----------+
mysql> update user set host='192.168.107.1' where user='jack';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select user,host from user where user='jack';
+------+---------------+
| user | host |
+------+---------------+
| jack | 192.168.107.1 |
+------+---------------+
1 row in set (0.01 sec)
查网上的文档,发现有使用如下语句:
GRANT ALL PRIVILEGES ON *.* TO 'jack'@’192.168.107.1’ IDENTIFIED BY 'password' WITH GRANT OPTION;
经过实操发现并不能正常执行,应该是该语句已经在MySQL8中被废弃,感兴趣的小伙伴可以去官网查询相关信息,欢迎留言指证,谢谢!
原因2解决方法:
方法一:配置防火墙开放3306端口【安全性有保障】
Step 1:查询防火墙当前默认zone:
[root@localhost ~]# firewall-cmd --get-active-zone
public
interfaces: ens33
Step 2:向默认zone添加永久生效放行端口3306:
[root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
Step 3:重新加载规则使生效
[root@localhost ~]# firewall-cmd --reload
success
[root@localhost ~]# firewall-cmd --zone=public --list-ports
3306/tcp
对Linux系统firewall感兴趣的小伙伴可参看此站点:
linux实验室->CentOS7防火墙firewalld命令大全
方法二:关闭防火墙并禁用服务自启动【高可用但不安全】
Step 1:关闭防火墙:
[root@localhost ~]# systemctl stop firewalld
Step 2:禁用防火墙服务自启动
[root@localhost ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
原因3解决方法:
编辑MySQL配置文件my.cnf
[Linux]或my.ini
[Windows],注释IP地址绑定配置并重启MySQL服务
[root@localhost ~]# vi /etc/my.cnf
#配置文件编辑注释如下配置
#bind-address=127.0.0.1
#保存并退出
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 六 2019-10-26 03:17:27 CST; 9s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 15443 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 15466 (mysqld)
Status: "Server is operational"
CGroup: /system.slice/mysqld.service
└─15466 /usr/sbin/mysqld
10月 26 03:17:21 localhost.localdomain systemd[1]: Stopped MySQL Server.
10月 26 03:17:21 localhost.localdomain systemd[1]: Starting MySQL Server...
10月 26 03:17:27 localhost.localdomain systemd[1]: Started MySQL Server.
参考文档:https://www.cnblogs.com/beanmoon/p/3173924.html
非常感谢!