mysql无法远程连接10038错误的坑（阿里云ecs）

为什么有这篇博客

　　昨天购买了阿里云的ecs服务器，通过xshell安装了docker,然后在docker环境中安装mysql，版本是8.0.21。开始一切顺利，在服务器环境中能正常执行各种命令。结果在使用navicat远程连接服务器mysql时卡住了，以下就是从卡住到解决的过程，希望能帮助到同样遇到这个问题的人。

问题根源

　　先说结论，由于是阿里云新实例，控制台未设置规则，远程无法访问3306这个端口，导致了客户端远程连接出现10038这个错误。

排查过程

mysql登录

先确定是否能正常在服务器上登录

mysql -uroot -p

Enter password: 
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 9
Server version: 8.0.21 MySQL Community Server - GPL

很显然登录成功，能正常操作show databases;这样的命令

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

创建数据库

然后尝试创建数据库

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

走到这一步也没有用任何问题

root用户远程登录是否授权

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select  User,authentication_string,Host from user;
+------------------+------------------------------------------------------------------------+-----------+
| User             | authentication_string                                                  | Host      |
+------------------+------------------------------------------------------------------------+-----------+
| root             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              | %         |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| root             | $A$005$byLhU
                                 'ict_qEg}A4pO6IUms8wmp1NNgUG2a.27n8HIPq..p5zMeDrtqF3 | localhost |
+------------------+------------------------------------------------------------------------+-----------+
5 rows in set (0.00 sec)

通过图表看到有2个root账户，一个本地连接localhost ,一个%代表可以远程连接。为了保险起见，再次授权

修改用户密码和验证方式

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select  User,authentication_string,Host from user;
+------------------+------------------------------------------------------------------------+-----------+
| User             | authentication_string                                                  | Host      |
+------------------+------------------------------------------------------------------------+-----------+
| root             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              | %         |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| root             | $A$005$byLhU
                                 'ict_qEg}A4pO6IUms8wmp1NNgUG2a.27n8HIPq..p5zMeDrtqF3 | localhost |
+------------------+------------------------------------------------------------------------+-----------+
5 rows in set (0.00 sec)

再次授权

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456'  ；

flush privileges;

还是一点反应也没有，远程连接依然是10038

删除一个root账户，保留一个

delete from user where host="%" and user="root";
删除之后，就修改剩余 root 用户 ，把host修改为%
update user set host = '%' where user = 'root';
FLUSH PRIVILEGES;

其它类型的骚操作

1.新增test用户，再次走授权逻辑，远程连接失败。

2.重启docker,重启mysql容器

3.查看mysql配置 my.conf里面是否禁用远程 bind 127.0.0.1,很显然默认是没有的

4.是否开启了防火墙

5.各种查博客

最终

查到一篇博客，里面提示 如果是ecs主机，需要检查一下规则设置，3306是否被允许访问， 果断的试了试，搞定

ecs设置如下

果断的把常用的几个端口一起设置了 6379,8080,3306,80

结尾

如果你遇到了mysql 远程连接10038 并且是阿里云主机，可以考虑第一时间看看访问规则设置，新实例默认只有一个22端口是可以访问的。

如果确认了访问规则设置没有问题，可以按照我的排查步骤一个个尝试。