15. Amazon RDS for MySQL: Create, Connect, Back Up and Keep It Private
15.4 Connecting from EC2 and Creating the Application User
RDS la SSH nahi – aapan EC2 (jyala web-sg aahe) varun mysql client ne connect karto.
# Amazon Linux 2023 – client only (no server needed)
sudo yum install -y mariadb105
# Ubuntu
sudo apt update && sudo apt install -y mysql-client
nc -zv reels-db.abcdefgh1234.ap-south-1.rds.amazonaws.com 3306 # "succeeded" = network OK
mysql -h reels-db.abcdefgh1234.ap-south-1.rds.amazonaws.com -u admin -p
If you chose Secrets Manager for the master password, read it once from the console (Secrets Manager → the rds!db-... secret → Retrieve secret value).
Use TLS for the connection. Download the AWS RDS CA bundle and require verification:
curl -o global-bundle.pem https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
mysql -h <endpoint> -u admin -p --ssl-ca=global-bundle.pem --ssl-verify-server-cert
Now create a dedicated application user – the app must never use admin:
CREATE DATABASE IF NOT EXISTS reelsdb;
CREATE USER 'reels_app'@'%' IDENTIFIED BY 'Use-A-Long-Random-Password-Here!';
GRANT SELECT, INSERT, UPDATE, DELETE ON reelsdb.* TO 'reels_app'@'%';
-- optional: force TLS for this user
ALTER USER 'reels_app'@'%' REQUIRE SSL;
SHOW GRANTS FOR 'reels_app'@'%';
'%' means "from any host" at the MySQL level – that is acceptable here only because the security group already allows nothing except web-sg. Network and database permissions work together.
From your laptop, safely: open an SSH tunnel through the EC2 instance instead of making RDS public.
ssh -i mykey.pem -N -L 3307:reels-db.abcdefgh1234.ap-south-1.rds.amazonaws.com:3306 ec2-user@203.0.113.10
# in another terminal on the laptop:
mysql -h 127.0.0.1 -P 3307 -u admin -p
| Error | Likely cause | Fix |
|---|---|---|
| Hangs, then Can't connect ... (110) | Security group or wrong VPC | db-sg inbound 3306 from web-sg; same VPC |
| Access denied for user | Wrong user/password or host part | Check SHOW GRANTS, reset password from RDS Modify |
| Unknown database 'reelsdb' | Initial DB name left empty | CREATE DATABASE reelsdb; |
| Unknown MySQL server host | Typo in endpoint | Copy endpoint again from the console |
Why this matters for security
Least privilege (किमान विशेषाधिकार) limits the damage of SQL injection: if the app user has only SELECT, INSERT, UPDATE, DELETE on one database, an attacker who injects SQL cannot DROP other databases, create users or read mysql.user. TLS stops anyone on the path from reading passwords and data in transit.
Ravindra Bagale's Tip
When they "can't connect", students change the password again and again. First run nc -zv <endpoint> 3306: if it hangs, it's a network/security group problem; if it says "succeeded" but login fails, it's a user/password problem. Two different problems – two different fixes. Very simple!
Ravindra Bagale's Tip – मराठी
"Connect होत नाही" तेव्हा students password पुन्हा पुन्हा बदलतात. आधी nc -zv <endpoint> 3306 चालवा: hang झाला तर network/security group चा problem, "succeeded" आलं पण login fail झालं तर user/password चा problem. दोन वेगळे problems – दोन वेगळे उपाय. एकदम simple!
Ravindra Bagale's Tip – हिंदी
"Connect नहीं हो रहा" तो students password बार-बार बदलते हैं. पहले nc -zv <endpoint> 3306 चलाओ: hang हुआ तो network/security group की problem, "succeeded" आया पर login fail हुआ तो user/password की problem. दो अलग problems – दो अलग उपाय. बहुत आसान!
Lab
From your EC2 instance, connect to RDS with TLS, create reels_app with the four privileges, log in as reels_app and confirm CREATE TABLE is denied until you use admin for schema changes. Then connect from your laptop through an SSH tunnel.