本地Navicat连接远程数据库

之所以写这篇文章是因为今天在本地想通过Navicat去连接远程服务器的mysql数据库时出现了问题,一直连接不上,试了很多种解决方法,阿里云服务器上的3306端口也开放了,还是不行,最终找到了解决方案,在此进行分享!

首先,我的问题是:想在本地去连接远程服务器mysql数据库,并且是以root用户连接,但是被拒绝访问了,报错的提示是:Access denied for user 'root'@'我本机的公网ip(using password:YES) 当时非常郁闷,报错提示信息显示的ip地址不是我远程阿里云服务器的公网ip,后来查了一下,是我本机的公网ip(通过在百度搜索"ip"显示的那个ip地址)

最终,找到了问题所在,之所以我本机无法以root用户去连接远程服务器的mysql数据库,是因为没有授权给网络上其他主机访问数据库root用户的权限,需要进行以下设置即可解决问题:

  1. create user ‘root’@’%’ identified by ‘123456’;
  2. GRANT ALL PRIVILEGES ON * . * TO ‘root’@’%’ IDENTIFIED BY ‘123456’;
  3. FLUSH PRIVILEGES;

对于以上三步操作的解释:

  1. 创建一个允许网络上其他主机访问数据库的root用户,并将密码设置成123456(%指的就是除了localhost之外的网络上其它主机)
  2. 给刚刚创建的root用户赋予操作所有数据库的权限,没有这个授权操作那么刚刚创建的root用户就看不到mysql的数据库,也就无法执行对数据库的相关操作
  3. 刷新系统权限表

之所以会出现上面的问题,并且也花费了不少的时间,根本原因就是因为我对mysql的用户操作这块掌握还不够扎实,为了加深自己对用户操作相关知识的掌握,在此对常见的用户操作进行总结:

  1. 查看用户

关于mysql数据库用户的信息都是存放在mysql数据库的user表里面的,我们可以查看一下这个表,只列出对我们有用的字段属性

host字段表示允许登录的主机,其中的"localhost"是指该用户只能在本地登录,不能在另外一台机器上远程登录。而"%"表示在任何一台电脑上都可以登录。也可以仅仅指定某台机器可以远程登录。所以根据上面的表查询出来的结果可知,用户a、root、shoesshop既可以在本地登录,也可以在任何一台机器上远程登录,而用户mysql.session、mysql.sys只能在本地登录。

  1. 创建用户

用户的信息都是存放在mysql数据库user表中,那么从理论上来说我们可以给user表插入一条新语句代表新增一个用户

insert into mysql.user(Host,User,Password) values("localhost","test",password("1234"));

上面的语句会出现两个问题:一是如果我们的数据库版本是5.7,那么会提示:Unknown column 'password' in 'field list',这是因为mysql5.7开始user表已经没有password这个字段了,password字段改成了authentication_string,当我们改成authentication_string执行插入又会提示:Field 'ssl_cipher' doesn't have a default value 出现错误的原因是mysql默认配置严格模式,该模式禁止通过insert的方式直接修改mysql库中的user表进行添加新用户。解决方法是修改my.ini(Windows系统)或my.conf(Linux系统)配置文件。

但是为了数据库的安全,不推荐使用上面的方式进行创建用户,正确的创建用户的方式是 create user 'test'@'localhost' identified by '1234' 执行该语句之后,创建了一个名为test,密码为1234的用户,并且只能在本地登录,不支持远程登录;我们可以验证一下,在本机使用navicat连接远程服务器的mysql,并且以刚刚创建的test用户连接:
在这里插入图片描述
可以看到,连接被拒绝了,我们再查看下mysql数据库的user表,确实test用户的host字段只有localhost,并没有%
在这里插入图片描述
所以我们再次执行语句create user 'test'@'%' identified by '1234' 设置允许任一台机器以test用户远程登录,查看下mysql数据库的user表,test用户的host字段既有localhost也有%,也就是既支持本地登录也支持远程登录了,当再次连接,即可连接成功
在这里插入图片描述
3. 授予用户权限

对于新建的用户,是没有操作任何数据库的权限的,我们以新建的test用户登录之后,可以使用show databases; 命令查看所有数据库

①本地以test用户登录mysql,查看所有数据库只能看到information_schema,是因为我们并没有给host字段为localhost的test用户赋予任何数据库的权限,所以无权操作数据库,包括查看数据库
在这里插入图片描述
②远程以test用户登录mysql,查看所有数据库也是只能看到information_schema,是因为我们没有给host字段为%的test用户赋予任何数据库的权限,所以无权操作数据库,包括查看数据库
在这里插入图片描述
给用户授权格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by ‘密码’;

①我们设置本地登录的test用户具有所有数据库的权限:GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost'IDENTIFIED BY'1234'; 再次查看数据库,就可以看到所有的数据库,也能对数据库执行增删改操作
在这里插入图片描述
②我们设置远程登录的test用户具有所有数据库的权限:GRANT ALL PRIVILEGES ON *.* TO 'test'@'%'IDENTIFIED BY'1234'; 再次查看数据库,就可以看到所有的数据库,也能对数据库执行增删改操作
在这里插入图片描述
4. 修改用户密码(mysql 5.7 为例)

# 修改本地登录的test用户密码
update mysql.user set authentication_string=password(“新密码”) where User=”test” and Host=”localhost”;
# 修改远程登录的test用户密码
update mysql.user set authentication_string=password(“新密码”) where User=”test” and Host=”%”;
# 修改密码之后需要刷新系统权限表才能生效
flush privileges;
  1. 删除用户
# 删除用户
Delete FROM mysql.user Where User='用户名' and Host='localhost或者%';
flush privileges;

# 删除账户及权限:
drop user 用户名@’%’;
drop user 用户名@ localhost;
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 1024 设计师:上身试试 返回首页