文章目录

  • 小结
  • 问题及解决
    • 连接问题
    • 如何使用本地的Mysql Workbench对RDS进行访问
  • 参考

小结

在AWS私有网络中部署了RDS (Mysql), 尝试通过外网成功地进行了访问.

问题及解决

连接问题

在AWS私有网络中部署了RDS (Mysql), 进行外网进行访问碰到了各种问题.

以下连接超时：
ERROR 2003 (HY000): Can't connect to MySQL server on 'sql1.example.com:3306' (110)

~]# perror 110OS error code 110:Connection timed out

以上问题基本上是由于路由端口不通之类的问题导致的。

首先, 能过外网是无法直接访问AWS私有网络中部署了RDS (Mysql)的, 需要设置一个Jump Host, 注意Jump Host需要与RDS Mysql在同一个VPC中，设置好安全组Security Group，各种公私密钥在此不赘述.

假如10.0.0.106是这个Jump Host，需要在这个机器上安装Mysql:

[ec2-user@ip-10-0-0-106 ~]$ sudo dnf install mysql80-community-release-el9-3.noarch.rpm[ec2-user@ip-10-0-0-106 ~]$ sudo dnf install mysql-community-server

安装Telnet并测试端口是否可访问， 这里spring-petclinic-rest-petclinicdb-lnprknzirs7d.c08lozdhnc5k.ap-southeast-1.rds.amazonaws.com是数据库RDS (Mysql)的访问地址Endpoint：

[ec2-user@ip-10-0-0-106 ~]$ yum install telnetError: This command has to be run with superuser privileges (under the root user on most systems).[ec2-user@ip-10-0-0-106 ~]$ sudo yum install telnet[ec2-user@ip-10-0-0-106 ~]$ telnet spring-petclinic-rest-petclinicdb-lnprknzirs7d.c08lozdhnc5k.ap-southeast-1.rds.amazonaws.com 3306Trying 10.0.20.11...Connected to spring-petclinic-rest-petclinicdb-lnprknzirs7d.c08lozdhnc5k.ap-southeast-1.rds.amazonaws.com.Escape character is '^]'.J8.0.337x|\L|(ÿ66Sak Tjr?mysql_native_password^CConnection closed by foreign host.

Mysql访问成功如下：

[ec2-user@ip-10-0-0-106 ~]$ mysql -h spring-petclinic-rest-petclinicdb-lnprknzirs7d.c08lozdhnc5k.ap-southeast-1.rds.amazonaws.com -P 3306 -u PetClinicDB -pWelcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 312Server version: 8.0.33 Source distributionCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> exitBye

如果不清楚用户名，可以使用AWS指令来获取MasterUsername:

[ec2-user@ip-10-0-3-241 ~]$ aws rds describe-db-instances --filters "Name=engine,Values=mysql" --query "*"[["spring-petclinic-rest-petclinicdb-lnprknzirs7d","spring-petclinic-rest-petclinicdb-lnprknzirs7d.c08lozdhnc5k.ap-southeast-1.rds.amazonaws.com",3306,"PetClinicDB"]][ec2-user@ip-10-0-3-241 ~]$ aws rds describe-db-instances --region ap-south-1{"DBInstances": []}[ec2-user@ip-10-0-3-241 ~]$ aws rds describe-db-instances --region ap-southeast-1{"DBInstances": [{"DBInstanceIdentifier": "spring-petclinic-rest-petclinicdb-lnprknzirs7d","DBInstanceClass": "db.t2.medium","Engine": "mysql","DBInstanceStatus": "available","MasterUsername": "PetClinicDB","DBName": "PetClinicDB","Endpoint": {"Address": "spring-petclinic-rest-petclinicdb-lnprknzirs7d.c08lozdhnc5k.ap-southeast-1.rds.amazonaws.com","Port": 3306,"HostedZoneId": "Z2G0U3KFCY8NZ5"},"AllocatedStorage": 5,"InstanceCreateTime": "2023-12-14T04:01:18.309000+00:00","PreferredBackupWindow": "20:52-21:22","BackupRetentionPeriod": 1,"DBSecurityGroups": [],"VpcSecurityGroups": [{"VpcSecurityGroupId": "sg-089602d25785464ca","Status": "active"}],"DBParameterGroups": [{"DBParameterGroupName": "default.mysql8.0","ParameterApplyStatus": "in-sync"}],........

如何使用本地的Mysql Workbench对RDS进行访问

如果需要用本地的Mysql Workbench对RDS进行访问，那么就需要通过Jump Host建立的SSH连接建立一个隧道Tunnuel进行连接。
这里以Xshell为例进行设置，对SSH连接进行TCP/IP转发进行设置：


Mysql Workbench直接连接本地的3306端口即可访问：

参考

stackoverflow: grant remote access of MySQL database from any IP address
mySQL / MariaDB – Resolve “ERROR 2003 (HY000) Cant connect to MySQL server (110)” (connection timed out)
Using a Jump host to access an RDS database in a private subnet
serverfault: How do you recover you RDS master user username?
AWS user guide: Connecting to a DB instance running the MySQL database engine
Linux Howto: Installing MySQL 8 server & client on Amazon Linux 2023
kinsta: How to Change Your MySQL Password in XAMPP (3 Methods)