🔐 How to Connect to Amazon RDS MySQL with IAM Authentication

Amazon RDS supports IAM-based authentication for MySQL. This means you no longer need to hardcode passwords in your code or scripts — instead, you generate a temporary IAM token securely.

But setting it up can get tricky. In this guide, you’ll learn:

✅ How to configure IAM auth for MySQL
✅ How to grant user access
✅ How to import a .sql file securely
✅ How to fix common errors like ERROR 1045 and plugin not enabled

---

✅ Step 1: Enable IAM DB Authentication on Your RDS Instance

1. Go to RDS Console > Your DB > Modify

2. Scroll to "IAM DB Authentication"

3. Set to ✅ Enabled

4. Apply changes (Apply immediately)

This allows your MySQL instance to accept login using IAM tokens.

---

✅ Step 2: Create a MySQL User with IAM Plugin

Connect using the master user:

mysql -h <rds-endpoint> -u <master-user> -p

Create a new user (e.g. wordpress_db_user) with IAM support:

CREATE USER 'wordpress_db_user'@'%' IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpress_db_user'@'%';
FLUSH PRIVILEGES;

Check it’s correct:

SELECT user, host, plugin FROM mysql.user WHERE user = 'wordpress_db_user';

Should return:

wordpress_db_user | % | AWSAuthenticationPlugin

---

✅ Step 3: Add IAM Policy to Your EC2 Role

Your EC2 instance must have an IAM role that allows connecting as that user.

Attach this policy:

{
  "Effect": "Allow",
  "Action": "rds-db:connect",
  "Resource": "arn:aws:rds-db:<region>:<account-id>:dbuser:<db-resource-id>/wordpress_db_user"
}

Find the db-resource-id with:

aws rds describe-db-instances \
  --region ap-southeast-1 \
  --query "DBInstances[?DBInstanceIdentifier=='<db-name>'].DbiResourceId" \
  --output text

---

✅ Step 4: Generate IAM Token and Connect

Run this on your EC2 instance:

export REGION="ap-southeast-1"
export RDS_HOST="your-db-endpoint.rds.amazonaws.com"
export DB_USER="wordpress_db_user"
export DB_NAME="wordpress"

export DB_TOKEN=$(aws rds generate-db-auth-token \
  --hostname "$RDS_HOST" \
  --port 3306 \
  --region "$REGION" \
  --username "$DB_USER")

Then connect:

mysql \
  --host="$RDS_HOST" \
  --port=3306 \
  --user="$DB_USER" \
  --password="$DB_TOKEN" \
  --ssl-mode=REQUIRED \
  --enable-cleartext-plugin \
  "$DB_NAME"

✅ Step 5: Import SQL File Using IAM Token

mysql \
  --host="$RDS_HOST" \
  --port=3306 \
  --user="$DB_USER" \
  --password="$DB_TOKEN" \
  --ssl-mode=REQUIRED \
  --enable-cleartext-plugin \

  --database=wordpress \

  "$DB_NAME" < wordpress_backup.sql

---

🧨 Common Errors & How to Fix

❌ Error	💡 Cause & Fix
ERROR 1045 (28000): Access denied	Token not passed correctly, or user not created with AWSAuthenticationPlugin. Use --password="$DB_TOKEN" and --enable-cleartext-plugin.
ERROR 2059 (HY000): plugin not enabled	You forgot --enable-cleartext-plugin. Must be included for IAM login.
Can't connect to MySQL server (110)	EC2 can't reach RDS. Check security group, VPC, and port 3306 rules.
Using a password on the command line...	Just a warning — you’re fine if you use $DB_TOKEN safely.
Unknown database	Make sure the target DB (e.g. wordpress) exists before importing.

---

✅ Final Sanity Check

Run this to confirm you can connect:

mysql \
  --host="$RDS_HOST" \
  --port=3306 \
  --user="$DB_USER" \
  --password="$DB_TOKEN" \
  --ssl-mode=REQUIRED \
  --enable-cleartext-plugin \
  -e "SELECT NOW();" "$DB_NAME"

---

✅ Example Restore DB Bash Script

#!/bin/bash

# ========== INPUT PARAMETERS ==========

REGION="$1"          # e.g. ap-southeast-1

INSTANCE_ID="$2"     # RDS instance identifier (e.g. database-1)

SQL_FILE="$3"        # SQL dump file (e.g. wordpress_backup.sql)

DB_USER="$4"         # DB user (e.g. admin)

DB_NAME="$5"         # DB name

# ========== VALIDATE INPUT ==========

if [[ -z "$REGION" || -z "$INSTANCE_ID" || -z "$SQL_FILE" || -z "$DB_USER" || -z "$DB_NAME"  ]]; then

  echo "Usage: $0 <region> <instance-identifier> <sql-file> <db-username>"

  exit 1

fi

# ========== GET ENDPOINT ==========

RDS_ENDPOINT=$(aws rds describe-db-instances \

  --region "$REGION" \

  --query "DBInstances[?DBInstanceIdentifier=='$INSTANCE_ID'].Endpoint.Address" \

  --output text)

if [[ -z "$RDS_ENDPOINT" ]]; then

  echo "❌ RDS endpoint not found for instance: $INSTANCE_ID"

  exit 1

fi

echo "✅ RDS endpoint found: $RDS_ENDPOINT"

# ========== GENERATE IAM AUTH TOKEN ==========

echo "🔐 Generating IAM auth token..."

DB_TOKEN=$(aws rds generate-db-auth-token \

  --hostname "$RDS_ENDPOINT" \

  --port 3306 \

  --region "$REGION" \

  --username "$DB_USER")

# ========== IMPORT SQL ==========

echo "📦 Importing $SQL_FILE into database '$DB_NAME'..."

mysql --host="$RDS_ENDPOINT" --port=3306 --user="$DB_USER" --password="$DB_TOKEN"  --database="$DB_NAME"  --ssl-mode=REQUIRED --enable-cleartext-plugin < "$SQL_FILE"

if [[ $? -eq 0 ]]; then

  echo "✅ Import successful!"

else

  echo "❌ Import failed. Please check the error messages above."

fi

🧠 Summary

Task	Command
Generate IAM token	aws rds generate-db-auth-token ...
Connect to MySQL	Use token with --password="$DB_TOKEN" and --enable-cleartext-plugin
Create IAM user	CREATE USER ... IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS'
Grant access	GRANT ALL PRIVILEGES ON db.* TO 'user'@'%'
IAM policy	Must allow rds-db:connect to dbuser/<user>