External Access to MySQL

Contents

• Introduction
• Step 1. Create a Self Referencing Security Group in the Main VPC
• Step 2. Create a MySQL RDS Database
• Step 3. Create an EC2 (bastion) Instance
• Step 4. Connect to MySQL via MySQL Workbench from my local machine

Introduction

When OIT provision an account in AWS, we create a Main VPC (virtual private cloud) divided into a number of subnets. We categorize those subnets as follows:

• Public - resources placed in these subnets are accessible from the Internet.
• Private - resources placed in these subnets have Internet access via NAT (network address translation) but are not visible from the Internet.
• Data - resources placed in these subnets have no access to or from the Internet.

We recommend when creating a Database in AWS that you place it in one of the Data subnets for security purposes.

To connect to your database from an external client, you will need to set up a virtual machine (EC2) as a bastion host. This machine will only need to run while you are accessing your database; it is recommended you shut it down while it is not needed to save money.

Step 1. Create a Self Referencing Security Group in the Main VPC

In order for our EC2 (bastion) Instance to connect to our RDS (MySQL) Instance, we need to create a Security Group to allow this traffic.

1. Go to the EC2 Dashboard
   
2. Select "Security Groups" (on the left hand side).
   
3. Select "Create Security Group".
   
4. Give the security group a name and description. Make sure you add it to the Main VPC.
   
5. Edit the "Inbound" rules of your Security Group - note the Id of the Security Group (eg. sg-0966cb13056887638).
   
6. Add an inbound rule for MySQL/Aurora traffic allowing access from the security group id of this security group.
   

Security Group inbound rules can be thought of as 1) WHAT are we giving access to, and 2) WHO are we giving that access to.

We will add this Self Referencing group to our DB Instance because it has the WHAT. We will add it to our EC2 instance to make it the WHO.

Step 2. Create a MySQL RDS Database

1. Go to RDS
   
2. Click "Create Database"
   
3. Select MySQL
   
4. Select the Production Level
   
5. Choose the Instance Class
   
6. Add the Instance Identifier, Username, and Password
   
7. Place it in the Main VPC
   
8. Create the Database
   
9. Click "View DB Instance Details ""
   
10. Wait
    
11. Once the database is created, note the endpoint (eg. my-rds.crzc9xds3qkq.us-west-2.rds.amazonaws.com)
    
12. To add our Self Referencing Security Group, click "Modify"
    
13. Add the Self Referencing Security Group (select it from the drop-down)
    
14. Choose "Apply Immediately" before clicking "Modify DB Instance"
    

Step 3. Create an EC2 (bastion) Instance

As we are creating our EC2 Instance in a Public Subnet we will have SSH (secure shell) access to it. We will need to use a Security Group to limit who has access.

1. Go to the EC2 Dashboard
   
2. Click "Launch Instance"
   
3. Select the "Amazon Linux 2 AMI"
   
4. Use the "t2.micro" then Click "Next: Configure Instance Details"
   
5. Add it to a Public Subnet in the Main VPC then Click "Next: Add Storage"
   
6. Click "Next: Add Tags"
   
7. Give your instance a "Name" tag (and other tags as needed) then Click "Next: Configure Security Group"
   
8. "Create a new security group" with access to SSH (port 22) only from BYU Campus IP Addresses (128.187.0.0/16) then Click "Review and Launch"
   
9. Click "Launch"
   
10. Create and Download a Key Pair - then Click "Launch Instance"
    
11. Click "View Instances"
    
12. Note the Public DNS (IPv4) (eg. ec2-34-209-142-107.us-west-2.compute.amazonaws.com)
    
13. Click the "Connect" button to get information on how to SSH to your instance
    
14. Open a terminal window (linux/mac), navigate to the directory into which you saved your key pair, and connect to your EC2 instance.
    
15. Update (as needed)
    
16. Install the mysql client (for testing purposes) - Logging into your mysql server will currently fail; we need to connect the Self Referencing Security Group to our instance.
    
17. In the EC2 Dashboard, select your instance. Click "Actions" -> "Networking" -> "Change Security Groups"
    
18. Make sure the ssh security group and the self referencing security group are selected.
    
19. From your terminal window, test connecting to your database. (eg. mysql -h my-rds.crzc9xds3qkq.us-west-2.rds.amazonaws.com -u rootuser -p )
    

Step 4. Connect to MySQL via MySQL Workbench from my local machine

1. On your local machine, open a terminal window and navigate to the directory of your key pair. Create a tunnel to your MySQL DB through your EC2 Instance:
   
   ssh -N -L 3307:my-rds.crzc9xds3qkq.us-west-2.rds.amazonaws.com:3306 ec2-user@ec2-34-209-142-107.us-west-2.compute.amazonaws.com -i "my-bastion-keypair.pem"
   
   The above example sets up an ssh tunnel listening on port 3307, which relays all traffic you your MySQL database on port 3306 via your EC2 Instance. Use Ctrl+C to close the connection.
   
2. With the tunnel still open, go to a new terminal window on your local machine, test the connection to your MySQL instance:
   
   mysql -u rootuser -p -h 127.0.0.1 -P 3307
   
   The tunnel allows us to connect to our database as if it were running on our machine; 127.0.0.1 (or localhost) on port 3307
   
3. Once we know our tunnel is working, we can set up our client - MySQL Workbench
   
4. Add a new connection. Host: 127.0.0.1, Port: 3307, Username: rootuser (or whatever you used)
   
5. Enter the Password
   
6. It Works!!
   
7. Open the new connection. (Any time you access this, you must have the tunnel up. To save cost, shut down the EC2 instance while you are not using the client.)
   
8. GUI Database Access