mysql5.7.27 无法在远程连接打开

一.阿里云安装ubuntu18.04，mysql5.7.27后，mysql无默认密码，远程连接无法打开。

1.查看mysql监听IP和端口是否正常。

netstat -anpt

root@iZuf63gpxv4kgzve2n8mkqZ:/# netstat -anpt
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      9589/apache2
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN      417/systemd-resolve
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      777/sshd
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      14770/mysqld
tcp        0      0 172.19.190.107:51294    100.100.30.25:80        ESTABLISHED 847/AliYunDun
tcp        0      0 172.19.190.107:3306     180.161.84.139:51106    ESTABLISHED 14770/mysqld
tcp        0      0 172.19.190.107:22       180.161.84.139:64797    ESTABLISHED 841/sshd: root@pts/
root@iZuf63gpxv4kgzve2n8mkqZ:/#

监听得地址如果是:::3306或者是0.0.0.0:3306，表示监听所有IP地址，这监听状态是正常。若出现127.0.0.0:3306，说明监听的本地地址。

2.直接输mysql，从服务器进入数据库，查看mysql下的user表

root@iZuf63gpxv4kgzve2n8mkqZ:/# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, 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> use mysql;
mysql> select host,user,authentication_string,plugin from user;
+-----------+------------------+-------------------------------------------+-----------------------+
| host      | user             | authentication_string                     | plugin                |
+-----------+------------------+-------------------------------------------+-----------------------+
| localhost | root             |                                           | auth_socket           |
| localhost | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | debian-sys-maint | *CE1DA9A6E250643912DD52CF0FFCDD58416D426C | mysql_native_password |
+-----------+------------------+-------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

修改user为root的host为%，表示允许所有服务器访问。
设置authentication_string的值，就是root的密码。
将plugin改为mysql_native_password。

mysql> update user set host='%' , authentication_string=PASSWORD('密码') , plugin='mysql_native_password' where user='root';

3.检查一下控制台安全组规则，是否开启了3306端口，未开启添加安全组。

4.重启mysql服务

root@iZuf63gpxv4kgzve2n8mkqZ:/# service mysql restart

二.当忘记密码时，找到mysql的配置文件

1.在[mysqld]后添加skip-grant-tables（使用 set password for设置密码无效，且此后登录无需键入密码）

root@iZuf63gpxv4kgzve2n8mkqZ:/etc/mysql/mysql.conf.d# vi mysqld.cnf
[mysqld]
#
# * Basic Settings
#
skip-grant-tables
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr

2.重启mysql，直接登入mysql，修改密码

root@iZuf63gpxv4kgzve2n8mkqZ:/# mysql
mysql> use mysql;
mysql> UPDATE user SET authentication_string=PASSWORD('root') WHERE user='root';
mysql> exit

3.重新启动MySQL，查看密码是否修改成功，并把配置文件中的 skip-grant-tables 注释掉。