MySQL8.0无法远程链接

问题

        阿里云上安装MySql8.0，远程（Navicat等）却无法访问？

分析

• 所在网络没有权限

• MySql8.0修改了加密规则，远程工具连接可能报错

解决

• 查询MySql8.0用户信息，并授权指定IP或者任何IP可以访问

  MySQL [mysql]> select host, user, authentication_string, plugin from user;
  +-----------+------------------+------------------------------------------------------------------------+-----------------------+
  | host      | user             | authentication_string                                                  | plugin                |
  +-----------+------------------+------------------------------------------------------------------------+-----------------------+
  | 127.0.0.1 | root             | *8110C8C20E4643B31F8A7DB65664AD516732B8CC                              | mysql_native_password |
  | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
  | localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
  | localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
  | localhost | root             | *8110C8C20E4643B31F8A7DB65664AD516732B8CC                              | mysql_native_password |
  +-----------+------------------+------------------------------------------------------------------------+-----------------------+
  
  #修改指定IP拥有访问权限
  UPDATE mysql.`user` SET host = '指定IP' WHERE user = 'root' AND host = 'localhost';
  
  #修改任何IP拥有访问权限
  UPDATE mysql.`user` SET host = '%' WHERE user = 'root' AND host = 'localhost';
  
  #更新权限，使其生效
  FLUSH PRIVILEGES;
  
  

   

• Mysql8.0修改默认加密规则，修改如下

  #指定IP情况修改
  ALTER mysql.`user` 'root'@'指定IP' IDENTIFIED WITH mysql_native_password BY '你的密码';
  
  #任何IP修改
  ALTER mysql.`user` 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
  
  
  #更新权限，使其生效
  FLUSH PRIVILEGES;