mysql（8.0.27）本地启动没有问题，远程无法连接解决方案+开启远程连接+问题排查路径

mysql安装

mysql8.0.27 免安装教程连接：安装教程连接

现象

1. mysql本地启动正常

C:\Users\youc>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

2. 数据库远程连接失败
错误代码：

java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up

数据库链接：jdbc:mysql://192.168.53.30:3306/test?characterEncoding=utf8&connectTimeout=1000&socketTimeout=30000&autoReconnect=true&useUnicode=true&useSSL=false&useTimezone=true&serverTimezone=Asia/Shanghai
备注：192.168.53.30:3306 本机地址：端口号 / 数据库名？连接配置

错误排查

1. 开启远程连接

   ##开启root用户远程访问权限
   CREATE USER 'root'@'%' IDENTIFIED BY '你的密码';
   ##如果报错,执行下一句
   DROP USER 'root'@'%'
   
   GRANT ALL ON *.* TO 'root'@'%';
   
   ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
   
   FLUSH PRIVILEGES; 
   

2. 检查是否开启远程连接

   mysql> use mysql;
   Database changed
   ## 如果结果中存在 host：% user：root 结果，可以肯定远程连接开启了
   mysql> select host,user,password from user;
   +--------------+------+-------------------------------------------+
   | host         | user | password                                  |
   +--------------+------+-------------------------------------------+
   | %    | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
   | 192.168.1.1 | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |
   +--------------+------+-------------------------------------------+
   2 rows in set (0.00 sec)
   

3. 检查防火墙，最坑的window设置，就是这个小问题，困扰了我一整天
   操作：关闭 window防火墙，直接关了，不要想着只开发一个端口，直接关掉，不要幻想，关掉，重要的事情说三遍！！！
   附端口查询命令

   #查看端口
   firewall-cmd --list-ports
   #开放3306端口
   firewall-cmd --zone=public --add-port=3306/tcp --permanent
   #重启防火墙
   firewall-cmd --reload
   

4. 使用上面的数据连接地址进行测试