Unlocking the Power of AWS RDS: Accessing from Both Lambda and Locally

Step 2: Update RDS Instance Settings

Now, let’s update the RDS instance settings to allow connections from both environments:

1. Go back to the RDS instance settings page and scroll down to the “DB instance” section.
2. Click on “Edit” next to “DB instance settings”.
3. In the “DB instance settings” page, make sure the “Publicly accessible” option is set to “Yes”.
4. Set the “Security group” option to the one you created in Step 1 (e.g., “rds-access”).
5. Click “Apply immediately” to save the changes.

Step 3: Set up Lambda Function Environment Variables

To access your RDS instance from a Lambda function, you need to configure environment variables:

1. Navigate to the Lambda function page and click on “Configuration” in the top-right corner.
2. Scroll down to the “Environment variables” section and click on “Edit”.
3. Add the following environment variables:

Step 4: Access RDS Instance from Lambda Function

Now that you’ve set up the environment variables, you can access your RDS instance from your Lambda function:

import boto3
import MySQLdb

# Initialize RDS connection
rds_host = os.environ['RDS_HOSTNAME']
rds_db = os.environ['RDS_DB_NAME']
rds_user = os.environ['RDS_USERNAME']
rds_password = os.environ['RDS_PASSWORD']

conn = MySQLdb.connect(
    host=rds_host,
    user=rds_user,
    passwd=rds_password,
    db=rds_db
)

# Perform database operations
cursor = conn.cursor()
cursor.execute("SELECT * FROM mytable")
results = cursor.fetchall()

# Close the connection
conn.close()

Step 5: Access RDS Instance Locally

To access your RDS instance from your local machine, you’ll need to create a new user with the necessary permissions:

1. Log in to your RDS instance using a tool like HeidiSQL or SQL Workbench/J.
2. Create a new user with the following command:

CREATE USER 'local_user'@'%' IDENTIFIED BY 'local_password';

NB:** Replace ‘local_user’ and ‘local_password’ with your desired username and password.

GRANT ALL PRIVILEGES ON *.* TO 'local_user'@'%';

set RDS_HOSTNAME=my-rds-instance.abcdefg.us-east-1.rds.amazonaws.com
set RDS_DB_NAME=mydb
set RDS_USERNAME=local_user
set RDS_PASSWORD=local_password

export RDS_HOSTNAME=my-rds-instance.abcdefg.us-east-1.rds.amazonaws.com
export RDS_DB_NAME=mydb
export RDS_USERNAME=local_user
export RDS_PASSWORD=local_password

import mysql.connector

# Initialize RDS connection
rds_host = os.environ['RDS_HOSTNAME']
rds_db = os.environ['RDS_DB_NAME']
rds_user = os.environ['RDS_USERNAME']
rds_password = os.environ['RDS_PASSWORD']

conn = mysql.connector.connect(
    host=rds_host,
    user=rds_user,
    password=rds_password,
    database=rds_db
)

# Perform database operations
cursor = conn.cursor()
cursor.execute("SELECT * FROM mytable")
results = cursor.fetchall()

# Close the connection
conn.close()