• IGW
• Subnet1 Private in AZ1
• Subnet2 Private in AZ2 (different from AZ1)
• Subnet3 Public
  • Route table: RT-table -> add 0.0.0.0/0 to IGW

• See create database instance below

• belongs to Subnet3

• Public IP

• SG1:

  • allow Inbound SSH from local
  • allow Outbound All traffic

• Create database (example MariaDB)

• Give db instance_name, username and password

• select VPC / Create new security group, give name

• set a name for database (if needed/wanted)

To be able to connect, it needs to be changed:

• Connect to EC2 instance via ssh:

ssh -i key.pem ec2-user@<public IP>

• On EC2, if needed, Install MySQL Client to connect remotely

sudo yum install mysql

• find RDS database endpoint:

  

• connect to database

mysql -h <endpoint> -u username -p

• looks like this (in MariaDB you can use status to get information)

• Connect to a database:

  connect DataBaseName

• Useful commands:

  SHOW TABLES;
  DESCRIBE table;
  DROP TABLE table;

• Create table (SQL):

  CREATE TABLE table (id int NOT NULL AUTO_INCREMENT, fristname VARCHAR(20), lastname VARCHAR(20));

• Insert:

  INSERT INTO mytable (firstname,lastname) VALUES ("Anthony","Jourdan");

• IGW
• Subnet1 in AZ1
• Subnet2 in AZ2 (different from AZ1)

• Enable DNS resolution and DNS hostnames for your VPC

  

• Same as not publicly accessible except:

• Be careful with SG !!!

  

• Subnet1 and 2 (for RDS) should be public

  • Route table: RT-table -> add 0.0.0.0/0 to IGW

• HostName = , and insert credentials.

• Install SQLAlchemy, pymysql packages

   pip install sqlalchemy 
   pip install sqlalchemy-utils
   pip install pymysql 

• Test with simple python script

  # Imports
  from sqlalchemy import create_engine
  from sqlalchemy_utils import database_exists, create_database
  
  # Create connection string
  connection_string = "mysql+pymysql://<user>:<passwd>@<endpoint>:3306/<DB Name>"
  
  # Create an engine to <DB Name>
  engine = create_engine(connection_string)
  
  # Check if DB exists otherwise create it.
  if not database_exists(engine.url):
      create_database(engine.url)
  
  # Get the table names
  print(engine.table_names())
  
  # Create a connection on engine
  connection = engine.connect()
  
  # Build select statement
  stmt = 'SELECT * FROM mytable2'
  
  # Execute the statement and fetch the results: results
  results = connection.execute(stmt).fetchall()
  
  # Print results
  print(results)