Multi-Server | Workspaces | Database |
Relational Database Service (RDS) Setup
Introduction
Amazon Web Services (AWS) Provides a Relational Database Service (RDS). For our Kasm project we use RDS as the primary database for users and group definitions. By so doing, the RDS doubles as be a classroom management database.
This document describes RDS setup and management.
RDS Security Group
A database starts with security. This process sets up Inbound and Outbound access.
Step 1: Login to AWS Management Console
- Go to the AWS Management Console.
- Log in with AWS credentials.
Step 2: Navigate to EC2 Security Groups
- In the AWS Management Console, navigate to the EC2 Dashboard.
- In the left-hand menu, scroll down and select Security Groups under Network & Security.
Step 3: Create a New Security Group
- Click on the Create security group button.
- Enter a name for security group. Name it something that reflects purpose ex. ‘rds_access’ or
RDS_Security_Group
. - Select the VPC : vpc-faea2491 (tester)
This is the screen you should see
Step 4: Configure Inbound and Outbound Rules
Note : Apply the rules that match YOUR requirements
- Inbound Rules: These rules define the incoming traffic allowed to reach your AWS resource. ex. if you have a web server, you might allow inbound HTTP and HTTPS traffic from the internet.
- Outbound Rules: These rules specify the outgoing traffic allowed from your AWS resource to other destinations. ex. if your web server needs to access an external API, you would set up outbound rules to permit this traffic.
Inbound :
- Click on the Inbound rules tab.
- Click on Add Rule to add the necessary inbound rules for your RDS instance:
- Type: Custom TCP
- Protocol: TCP
- Port range: 3306
- Source Type: Custom
- Source : Specify the IP range that will be allowed to connect.
OR
- Type: Custom TCP
- Protocol: TCP
- Port range: 3306
- Source Type: My IP
Outbound :
- Click on the Outbound rules tab.
- Click on Add Rule
- Add outbound rule that allows all traffic (or restrict as per your requirement).
Step 6: Review and Create
- Review security group settings.
- Click on the Create security group button to finalize.
RDS Instance
A database instance is the endpoint of all transactions to store and retrieve data.
Step 1: Navigate to RDS Dashboard
- In the AWS Management Console, navigate to the RDS Dashboard by selecting RDS from the services menu.
Step 2: Launch DB Instance
- Click on the Create database button.
- Select the Standard Create option.
Step 3: Choose a Database Engine
- Select the database engine you want to use : MySQL
Step 4: Configure Database Settings
-
Specify the template as Free teir
-
DB instance identifier, Master username, and Master password. NOTE : SAVE THE USERNAME AND PASSWORD INFORMATION FOR A LATER STEP
-
Choose the DB instance size : db.t3.micro
Step 5: Configure Storage
- Choose the allocated storage size for your database.
- Enable storage auto-scaling if needed.
Step 6: Configure Connectivity
- In the Connectivity section, choose the VPC default.
- Select the Subnet group default
- Under Public access, choose YES.
- For VPC security group, choose the security group you created earlier (
RDS_Security_Group
).
Step 7: Authentication Configuration
- Configure database authentication as password only
Step 8: Review and Launch
- Review all your settings.
- Click on the Create database button to launch your RDS instance.
Once your RDS instance is created, it will appear in the RDS dashboard, and you can connect to it using the endpoint provided.
RDS Connection
A connection to a database is how all the relational database transactions occur.
Step 1: Obtain the Endpoint
-
In the RDS Dashboard, click on your database instance to view its details.
-
Copy the Endpoint and Port information.
Step 2. Connect to RDS
-
Use SQL client or use terminal to connect to RDS instance. You will be asked for the password set earlier for authentication.
mysql -h your-rds-endpoint -P 3306 -u your-master-username -p
-
Optional. Perform SQL operations directly on your RDS instance. Through connection you typically perform adminstrative commands like Creating a database or Reviewing Schema. For our Kasm project these elements have been built into Python.
RDS Python usage
The Kasm project is using Python with the Flask framework to manage the database. The GitHub project nighthawkcoders/flask_2005
contains all the model definitions for the RDS instance defined above.
Python Requirements
-
Observe the requirements.txt file, it requires:
pymysql
-
Update python environment to include pymysql:
pip install -r requirements.txt
Python Database Connecton
- Navigate to
__init__.py
file -
Review or adjust the following configuration (for RDS use) :
# Database settings dbName = 'user_management' DB_ENDPOINT = os.environ.get('DB_ENDPOINT') or None DB_USERNAME = os.environ.get('DB_USERNAME') or None DB_PASSWORD = os.environ.get('DB_PASSWORD') or None if DB_ENDPOINT and DB_USERNAME and DB_PASSWORD: # Production - Use MySQL DB_PORT = '3306' DB_NAME = dbName dbString = f'mysql+pymysql://{DB_USERNAME}:{DB_PASSWORD}@{DB_ENDPOINT}:{DB_PORT}' dbURI = dbString + '/' + dbName backupURI = None # MySQL backup would require a different approach else: # Development - Use SQLite dbString = 'sqlite:///volumes/' dbURI = dbString + dbName + '.db' backupURI = dbString + dbName + '_bak.db'
RDS vs SQLite connection
By default, the developer will use an SQLite connection for adjusting and managing schema. The RDS (production) schema is only activated by defining DB environment variables.
Create a .env
in your project directory with environment variables as shown to activate production database.
# Sample Database configuration, REPLACE with VALID strings
DB_ENDPOINT='users-database.ckvjXXXXXXXX.us-west-2.rds.amazonaws.com'
DB_USERNAME='admin'
DB_PASSWORD='z7JgA8zUXXXXXXXX'
RDS migration
The fask_2025
project contains scripts
in python to enable the setup and migration of data. These scripts can and will destroy data.
scripts/db_init.py:
used to create database schema and add couple of test records to the users tables.scripts/db_migrate.py
: used to create database schema and migrate data from existing or previous instance.
Adminstrator Notes
Notes for database adminstrators.
-
Initializing schema is destructive
. Great care should be taken to make sure schema initialization is reviewed. When developing scripts for schema initialization make sure the come with warning and confirmation. -
Data migration requires business logic
. Bulk uploading data into a system requires the information to pass through the buiness logic that is performed for CRUD through the application. It is near impossible to think that you can create appropriate data validation and relations through standard SQL.