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端口是可以访问的。
如果确认了访问规则设置没有问题，可以按照我的排查步骤一个个尝试。
