MySQL远程连接失败(错误码:2003) 短命女 2023-10-18 15:40 84阅读 0赞 1).确定mysql服务是否已经正确开启 用“ps aux|grep mysql”命令查看,MySQL服务已启动: 2) 用"vim my.cnf"命令查看my.cnf文件(在MySQL安装目录下,我的位置是/usr/local/mysql/my.cnf),修改其对应的值并重启MySQL。对应内容如下:(whereis my.cnf查找也可) [mysqld] bind-address = 0.0.0.0 # 表示允许任何主机登陆 MySQL port=3306 # 表示MySQL运行端口为3306 (3)用“mysql -u admin -p”命令,回车后输入密码“adminpsw”能正常登陆服务器MySQL mysql> show global variables like 'port'; # 查看MySQL运行的实际端口 +---------------+------- | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.01 sec) mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select host,user from user; +-----------+---------------+ | host | user | +-----------+---------------+ | % | admin | | % | root | | localhost | mysql.session | | localhost | mysql.sys | +-----------+---------------+ 5 rows in set (0.00 sec) \# 如果上述查询结果,admin用户对应的host不为%,则修改用户权限 \# 此处需注意的是,修改权限时要带上密码(IDENTIFIED BY 'adminpwd'),虽然不知道具体原理,但是没加密码之前客户端还是不能远程访问MySQL。 mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'adminpwd' WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) (4) 至此,我的本地Navicat客户端已经能都正常访问服务器端,但是运行上述python连接MySQL代码还是报一样的错误。 (5)查网上资料说可能是防火墙屏蔽了3306端口,本地cmd"ping 192.168.1.166"能够Ping通,再用”telnet 192.168.1.166 3306“命令检查端口是否被屏蔽,结果为"正在连接192.168.1.166...无法打开到主机的连接。 在端口 3306: 连接失败",说明是防火墙的问题 (如果Win10 telnet不是内部或外部命令,决解方法参考连接:https://jingyan.baidu.com/article/1e5468f9033a71484961b7d7.html) (6) 起初我以为是指我本地防火墙的问题,于是把本地防火墙关了,结果问题并没有解决。 (7) 其实应该是远程服务器的防火墙问题。远程登陆服务器(我用root用户登录的),检查防火墙状态 systemctl start firewalld # 开启防火墙 systemctl stop firewalld # 关闭防火墙 systemctl status firewalld #检查防火墙状态 8)关闭远端服务器防火墙后,运行本地运行本地python连接MySQL代码,MySQL连接成功 [root@db sysconfig]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: active (running) since 三 2018-02-28 17:18:10 CST; 7s ago Docs: man:firewalld(1) Main PID: 5452 (firewalld) CGroup: /system.slice/firewalld.service └─5452 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid [root@db sysconfig]# systemctl stop firewalld [root@db sysconfig]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: inactive (dead) Docs: man:firewalld(1) (9)如果你觉得关闭防火墙不安全,可打开远端服务器的iptables(安装或升级命令“yum install iptables”)(我用root用户登录的),并用“vi /etc/sysconfig/iptables”检查3306端口是否打开,如没有,在文件中加入“-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT”(如下所示),保存文件并用“service iptables restart”命令重启iptables # Generated by iptables-save v1.4.21 on Wed Feb 28 12:19:33 2018 *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [34:3136] -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT ## 加上此行 -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibite -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT # Completed on Wed Feb 28 12:19:33 2018 ~ ~ "/etc/sysconfig/iptables" 17L, 654C
还没有评论,来说两句吧...