SQLyog客户端无法连接MySQL服务器案例分析-创新互联
实验环境:
操作系统:Redhat 6.4
IP:10.1.1.99(虚拟机)
数据库:MYSQL5.1
在Linux上安装好MYSQL之后,进行本地登录
[root@wjq2 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.66 Source distribution
Copyright (c) 2000, 2012, 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>
登陆成功;
下面通过SQLyog客户端来远程连接MYSQL服务器所遇问题进行分析:
问题一:
问题分析:
[root@wjq2 ~]# service mysqld status
mysqld (pid 2364) is running...
[root@wjq2 ~]# netstat -nalp | grep "3306"
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2364/mysqld
tcp 0 0 10.1.1.99:3306 10.1.1.123:55569 ESTABLISHED 2364/mysqld
tcp 0 0 10.1.1.99:3306 10.1.1.123:55568 ESTABLISHED 2364/mysqld
本地用户可以用3306端口连接,说明网络没有问题,3306端口也开启着,其实问题可能出在iptables,查看iptables的运行状态
[root@wjq2 ~]# service iptables status
iptables: Firewall is not running.
发现iptables是禁用状态,所以问题并不是出现在iptables这里。
注意:
1、如果你的iptables没有禁用,可以禁用iptables;
2、如果不想禁用iptables也可以,把-A INPUT -m state --state NEW -m tcp -p tcp --dport
3306 -j ACCEPT添加到/etc/sysconfig/iptables即可,表示允许3306端口通过防火墙。
[root@wjq2 ~]# cat
/etc/sysconfig/iptables
# Firewall configuration written by system-config-firewall
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
-A
RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
COMMIT
修改完之后,需要重新启动iptables方可生效
禁用iptables或修改完配置文件之后,再次远程连接MYSQL,又出现如下的错误:
问题二:
问题分析:
根据错误提示,10.1.1.123是我本地主机的网卡IP,远程连接时不允许通过该主机连接到MYSQL服务器;可以初步判断可能是应为缺少权限;
下面我将允许用户root使用密码XXX从任何主机连接到MYSQL服务器
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'XXX' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
或者是允许用户root从IP为10.1.1.123的主机连接到MYSQL服务器,并使用XXX作为登录密码
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.1.1.123' IDENTIFIED BY 'XXX' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
授权成功之后,再次尝试进行远程连接,可以发现连接成功;
作者:SEian.G(苦练七十二变,笑对八十一难)
本文标题:SQLyog客户端无法连接MySQL服务器案例分析-创新互联
分享链接:http://scyanting.com/article/dojcsd.html