Got it, Nikki!

Let's go through the entire process **in detail**, step-by-step,

without code boxes, to help you **connect and manage a private database** hosted on
a **private EC2** using a **bastion host (public EC2)**.

---

### **Goal:**
You have two EC2 instances:
1. **Public EC2 (Bastion Host)** – Can be accessed via SSH from the internet.
2. **Private EC2** – Not directly accessible from the internet, running a database
like MySQL or PostgreSQL.

We will use the **Public EC2** as a gateway (bastion) to SSH into the **Private
EC2** and then manage the database.

---

### **Step 1: Set Up the VPC and Subnets (If Not Already Done)**

1. **Create a VPC**:
- Go to the **VPC dashboard** in AWS.
- Click on **Create VPC**.
- Provide a name (e.g., "custom-vpc") and set the CIDR block as **10.0.0.0/16**
(this will create a private IP range).
- Click **Create VPC**.

2. **Create Public and Private Subnets**:

- Go to **Subnets** under your VPC.
- Click **Create subnet**, choose your VPC (custom-vpc), and select Availability
Zone.
- For public subnet, use CIDR like **10.0.1.0/24**.
- For private subnet, use CIDR like **10.0.2.0/24**.
- Click **Create subnet**.

3. **Create an Internet Gateway**:

- Go to **Internet Gateways** and click **Create internet gateway**.
- Name it, then **Attach** it to your VPC.

4. **Update Route Tables**:

- Go to **Route Tables**.
- Create a new route table for the public subnet and add a route with
destination **0.0.0.0/0** and target as the internet gateway.
- Associate the public subnet with this route table.

---

### **Step 2: Launch EC2 Instances (Public and Private)**

1. **Launch the Public EC2 (Bastion Host)**:

- Go to **EC2 Dashboard** and click on **Launch Instance**.
- Choose the **Amazon Linux 2 AMI**.
- Select **t2.micro** instance type (free tier eligible).
- Choose **VPC (custom-vpc)** and **public subnet**.
- Enable **Auto-assign Public IP** to allow internet access.
- Create a new **security group** allowing inbound SSH (port 22) from your IP.
- Create a **key pair** (e.g., `nikki-key.pem`) for SSH access.
- Launch the instance.

2. **Launch the Private EC2**:

 - Follow similar steps for launching the private instance.
- Select the **private subnet** in the VPC.
- **Do not enable auto-assign public IP** (so it won’t be directly accessible).
- Create a new **security group** allowing SSH (port 22) only from the **private
IP range** of the public EC2.

---

### **Step 3: SSH into the Public EC2 (Bastion Host)**

1. Use the terminal to SSH into your public EC2 instance (bastion):
- Example command:
`ssh -i "nikki-key.pem" ec2-user@<public-ec2-public-ip>`
- Ensure you have the correct `.pem` key file for this connection.

---

### **Step 4: SSH into the Private EC2 from the Public EC2**

1. From the **public EC2** (bastion), SSH into the **private EC2** using its
private IP:
- Example:
`ssh -i "nikki-key.pem" ec2-user@<private-ec2-private-ip>`
- You can use the **private EC2’s private IP address** because both instances
are in the same VPC, so they can communicate.

2. If you haven't yet uploaded the `.pem` key to the public EC2 instance, use
**SCP** (secure copy) to transfer the key from your local machine to the public
EC2:
- Example:
`scp -i "nikki-key.pem" nikki-key.pem ec2-user@<public-ec2-public-ip>:~`

---

### **Step 5: Install and Configure the Database (MySQL/PostgreSQL) on the Private
EC2**

1. Once inside the **private EC2** instance, you can install MySQL or PostgreSQL
based on your needs.

- **For MySQL**:
- Install MySQL using `sudo yum install mysql-server -y`.
- Start MySQL using `sudo systemctl start mysqld`.
- Enable it to start on boot: `sudo systemctl enable mysqld`.

- **For PostgreSQL**:
- Install PostgreSQL using `sudo yum install postgresql-server -y`.
- Initialize the database using `sudo postgresql-setup initdb`.
- Start PostgreSQL using `sudo systemctl start postgresql`.
- Enable it to start on boot: `sudo systemctl enable postgresql`.

---

### **Step 6: Configure Database to Allow Remote Connections (From Bastion Host)**

1. You need to **configure the database** to allow remote connections from your
**public EC2** (bastion).

- **For MySQL**:
 1. Edit the MySQL configuration file:
`sudo vi /etc/my.cnf`
2. Under the `[mysqld]` section, ensure the `bind-address` is set to your
private EC2’s IP address. This restricts connections to only your internal network
(private IP range).
3. Restart MySQL:
`sudo systemctl restart mysqld`.
4. Create a new user for remote access (from the bastion EC2):
`mysql -u root -p`
`CREATE USER 'username'@'10.0.1.%' IDENTIFIED BY 'password';`
`GRANT ALL PRIVILEGES ON *.* TO 'username'@'10.0.1.%';`
`FLUSH PRIVILEGES;`.

- **For PostgreSQL**:
1. Edit PostgreSQL’s `pg_hba.conf` file to allow connections from the public
EC2’s IP or the entire subnet.
- File location: `/var/lib/pgsql/data/pg_hba.conf`
2. Add an entry allowing your public EC2's private IP or subnet to connect,
such as:
`host all all 10.0.1.0/24 md5`.
3. Edit the `postgresql.conf` file to ensure it is listening on all IP
addresses:
`sudo vi /var/lib/pgsql/data/postgresql.conf`
Change `listen_addresses = 'localhost'` to `listen_addresses = '*'`.
4. Restart PostgreSQL:
`sudo systemctl restart postgresql`.

---

### **Step 7: From Bastion EC2, Connect to the Private Database**

1. From the **public EC2 (bastion)**, use the MySQL or PostgreSQL client to connect
to the database running on the private EC2 instance.

- **For MySQL**:
- Install MySQL client:
`sudo yum install mysql -y`.
- Connect to the database using the command:
`mysql -h <private-ec2-private-ip> -u username -p`.
- Enter the password when prompted.

- **For PostgreSQL**:
- Install PostgreSQL client:
`sudo yum install postgresql -y`.
- Connect to the database using the command:
`psql -h <private-ec2-private-ip> -U username`.
- Enter the password when prompted.

---

### **Step 8: Manage the Database (Perform Queries, Updates, etc.)**

1. Now that you're connected, you can perform typical database operations like:
- **Viewing databases**:
`SHOW DATABASES;` (MySQL)
`\l` (PostgreSQL).
- **Selecting a database**:
`USE <database_name>;` (MySQL)
`\c <database_name>` (PostgreSQL).
 - **Running queries**:
`SELECT * FROM <table_name>;`.
- **Updating records**:
`UPDATE <table_name> SET column='value' WHERE condition;`.
- **Inserting new records**:
`INSERT INTO <table_name> (column1, column2) VALUES (value1, value2);`.

---

### **Step 9: Secure Your Setup**

1. Always ensure that your **private EC2** is only accessible through the **public
EC2** (bastion).
2. Limit SSH access to your **bastion EC2** to only your IP address using security
groups.
3. For added security, consider using **SSH keys** instead of passwords for DB
access.

---

That's it! You've now securely set up a way to access and manage a **private
database** using a **public EC2** as a bastion host. You've learned how to:
1. Configure your network,
2. Install and configure the database,
3. SSH between the EC2 instances,
4. And perform database operations.

Let me know if you need more information on any specific part of the process!