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)navicat for mysql连接不成功
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$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUST
NEVERBRBEUSED | 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端⼝是可以访问的。如果确认了访问规则设置没有问题，可以按照我的排查步骤⼀个个尝试。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论