首页 > 上网技巧 > 电脑小技巧 > navicat数据库错误2003 Can not connect to Mys...

navicat数据库错误2003 Can not connect to Mysql Server on

时间:2017-12-09 08:23 作者:QQ地带 我要评论

一般这种问题是因为没有授予远程主机访问本地Mysql权限的原因。 
解决方式:
 
第一步,在Mysql数据库的user表中添加一条记录:host字段为%,用户为root(自己修改),后面的权限什么的自己看着给。可以使用Mysql GUI修改,比如navicat之类的,也可通过命令行修改,命令如下:
 
create user user@’%’ identified by ‘password’; 
grant all on . to user@’%’; 
flush privileges;
 
我直接这样就解决了,把报错的用户在mysql user表里面的host改成%就可以了,然后重启数据库。
 
注:user,password自己替换成自己数据库配置的。* .*是所有库,你也可以指定。
 
第二步,修要修改Mysql的配置文件:
 
对于5.6版本及以下的: 
修改配置/etc/mysql/my.cnf:bind-address = xxx.xxx.xxx.xxx(你本地的IP地址,不是127.0.0.1)
 
5.7版本及以上 
修改配置/etc/mysql/mysql.conf.d/mysqld.cnf:bind-address = xxx.xxx.xxx.xxx(你本地的IP地址,不是127.0.0.1) 
或者你不想指定IP地址可以再添加一条配置:bind-address = 0.0.0.0
 
第三步,重启你的Mysql,这样应该就可以了。 
sudo /etc/init.d/mysql restart
 
如果你第一步没有做:你可以按照下面的步骤完成剩余的任务:
 
Then stop and restart MySQL with the new my.cnf entry. Once running go to the terminal and enter the following command.
 
lsof -i -P | grep :3306
 
That should come back something like this with your actual IP in the xxx’s
 
mysqld 1046 mysql 10u IPv4 5203 0t0 TCP xxx.xxx.xxx.xxx:3306 (LISTEN)
 
If the above statement returns correctly you will then be able to accept remote users. However for a remote user to connect with the correct priveleges you need to have that user created in both the localhost and ‘%’ as in.
 
CREATE USER ‘myuser’@’localhost’ IDENTIFIED BY ‘mypass’; 
CREATE USER ‘myuser’@’%’ IDENTIFIED BY ‘mypass’;
 
then,
 
GRANT ALL ON . TO ‘myuser’@’localhost’; 
GRANT ALL ON . TO ‘myuser’@’%’;
 
and finally,
 
FLUSH PRIVILEGES; 
EXIT;
 
If you don’t have the same user created as above, when you logon locally you may inherit base localhost privileges and have access issues. If you want to restrict the access myuser has then you would need to read up on the GRANT statement syntax HERE If you get through all this and still have issues post some additional error output and the my.cnf appropriate lines.
 
NOTE: If lsof does not return or is not found you can install it HERE based on your Linux distribution. You do not need lsof to make things work, but it is extremely handy when things are not working as expected.

标签: Navicat
顶一下
(0)
0%
踩一下
(0)
0%

Google提供的广告