✅ Day 18 of My Cloud Journey — MySQL on EC2: Setup + SQL Queries 🎯

Today I rolled up my sleeves and dove into MySQL, a powerful relational database used by millions of applications. In this blog, I’ll show you:

• What MySQL is & why it’s essential

• How to install MySQL on EC2

• How to configure MySQL securely

• CRUD operations (Create, Read, Update, Delete)

• Database relationships with JOIN

• Real SQL queries for hands-on practice

---

🔍 What is MySQL?

MySQL is an open-source relational database used to manage structured data in rows and columns. It supports SQL (Structured Query Language) and is often used with PHP, Python, and web apps like WordPress, Laravel, Django, etc.

---

💡 Why MySQL Matters

---

⚙️ Install MySQL on Amazon EC2 (Amazon Linux 2)

🧩 Prerequisites

• Amazon EC2 instance (Amazon Linux 2)

• Port 22 open for SSH

• Optional: Port 3306 open (for remote access)

---

📦 Step-by-Step Installation

# Step 1: Update system packages
sudo yum update -y

# Step 2: Install MySQL server
sudo yum install mysql-server -y

# Step 3: Start the MySQL service
sudo systemctl start mysqld

# Step 4: Enable MySQL to start on boot
sudo systemctl enable mysqld

---

🔐 Secure the MySQL Installation

sudo mysql_secure_installation

✅ Set root password
✅ Remove anonymous users
✅ Disallow root remote login
✅ Remove test database
✅ Reload privileges

---

💻 Accessing the MySQL Shell

mysql -u root -p

Enter the password you set above to access the MySQL command-line interface.

---

🏗️ DATABASE + TABLE CREATION

📁 Create a new database

CREATE DATABASE school;
USE school;

---

📋 Create a table

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  grade INT
);

---

✍️ INSERT — Add Data

INSERT INTO students (name, email, grade)
VALUES 
('Alice Johnson', 'alice@example.com', 85),
('Bob Smith', 'bob@example.com', 90),
('Charlie Brown', 'charlie@example.com', 78);

---

📖 SELECT — Read Data

-- Show all records
SELECT * FROM students;

-- Filtered records
SELECT name, grade FROM students WHERE grade > 80;

-- Count students
SELECT COUNT(*) FROM students;

---

🛠️ UPDATE — Modify Records

-- Update a single student's grade
UPDATE students
SET grade = 95
WHERE name = 'Alice Johnson';

---

❌ DELETE — Remove Records

DELETE FROM students WHERE grade < 80;

---

🔗 Relational Data + JOIN

Let’s create a related table and use a JOIN.

CREATE TABLE courses (
  course_id INT AUTO_INCREMENT PRIMARY KEY,
  course_name VARCHAR(100)
);

CREATE TABLE enrollments (
  enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT,
  course_id INT,
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Add Course Data

INSERT INTO courses (course_name) VALUES ('Math'), ('Science');

Add Enrollment Data

INSERT INTO enrollments (student_id, course_id)
VALUES (1, 1), (2, 2);

JOIN Students + Courses

SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.course_id;

---

👥 Create a New MySQL User

CREATE USER 'clouduser'@'%' IDENTIFIED BY 'StrongPass123';

GRANT ALL PRIVILEGES ON school.* TO 'clouduser'@'%';

FLUSH PRIVILEGES;

---

🌐 Allowing Remote Access (Optional & Risky)

Edit config:

sudo nano /etc/my.cnf

Replace:

bind-address = 127.0.0.1

With:

bind-address = 0.0.0.0

Restart MySQL:

sudo systemctl restart mysqld

Allow port 3306 in Security Group if needed.

---

💭 Final Thoughts

Today’s experience gave me hands-on exposure to:

✅ Setting up a relational DB
✅ Writing SQL for real data
✅ Understanding how MySQL connects to backend apps
✅ Practicing database relationships and queries

These are fundamental skills for hosting WordPress, building full-stack apps, and backend development in general!

---

📅 What’s Next?

Day 19: I’ll explore advanced SQL queries, practice JOINs and constraints, and prepare for deploying WordPress with a real MySQL backend.

🔜 Topics:

• Normalization

• Foreign Keys

• Backup & Restore

• Connecting MySQL to WordPress (LAMP/LEMP)