🔐 MySQL Backup and Restore on AWS EC2 (Day 21 of 90 Days of Cloud)

Whether you’re running a personal blog or a production-grade application, backing up your MySQL databases is non-negotiable. Today, I focused entirely on learning and mastering MySQL backup and restore using both logical and physical methods.

---

☁️ Why Backups Matter

Databases are the heart of dynamic applications. A single error, crash, or vulnerability can result in data loss. With proper backup strategies:

• You protect against accidental deletions or updates.

• You can quickly recover from system crashes.

• You simplify migrations or cloning of databases.

---

🛠️ My Setup

• EC2 instance running Amazon Linux 2

• MySQL 8.x or MariaDB

• Database name: cloudapp_db

• User: admin, Password: securepass123

---

✅ Types of MySQL Backups

---

🔹 1. Backup Using mysqldump (Logical Backup)

This method generates .sql files you can use to recreate the DB schema and data.

✅ Full Database Backup

mysqldump -u admin -p cloudapp_db > cloudapp_backup.sql

You’ll be prompted for the password. This exports everything: tables, structure, and data.

---

✅ Backup Specific Tables

mysqldump -u admin -p cloudapp_db users orders > selected_tables.sql

This is helpful if you're only interested in a subset of data.

---

✅ Backup All Databases

mysqldump -u admin -p --all-databases > all_databases_backup.sql

---

✅ Add Timestamps for Automation

mysqldump -u admin -p cloudapp_db > backup_$(date +%F).sql

This appends the current date to your filename (e.g., backup_2025-07-23.sql).

---

🔄 2. Restore a Backup

✅ Restore from a .sql file

mysql -u admin -p cloudapp_db < cloudapp_backup.sql

This re-imports the full dump into the existing database. Ensure the DB exists before restoring:

mysql -u admin -p -e "CREATE DATABASE IF NOT EXISTS cloudapp_db;"

---

🔁 Backup + Restore via Compression

Compressing backups can save storage, especially with large datasets.

🔸 Compressed Backup

mysqldump -u admin -p cloudapp_db | gzip > cloudapp_backup.sql.gz

🔸 Restore From Compressed File

gunzip < cloudapp_backup.sql.gz | mysql -u admin -p cloudapp_db

---

🧱 Physical Backup (Advanced)

Physical backups involve copying the MySQL data directory, typically:

Edit/var/lib/mysql

⚠️ You must stop MySQL before copying these files to avoid corruption.

sudo systemctl stop mysqld
sudo cp -r /var/lib/mysql /backup/mysql_raw/
sudo systemctl start mysqld

Only use this method when MySQL is down or in cold backup situations.

---

🔐 Automating Backups with Cron

Schedule automatic daily backups:

crontab -e

Add this line to backup every night at 2 AM:

Edit0 2 * * * mysqldump -u admin -pYourPassword cloudapp_db > /home/ec2-user/backups/backup_$(date +\%F).sql

✅ Tip: Don’t hard-code passwords — use .my.cnf or env variables!

---

☁️ Uploading Backups to S3 (Bonus)

aws s3 cp backup_2025-07-23.sql s3://my-db-backups/cloudapp/

Make sure your EC2 has IAM role permissions to upload to S3.

---

🧠 Best Practices

• Always test your restore after taking a backup.

• Use compression to save space.

• Automate daily/weekly backups.

• Store copies in multiple locations (e.g., S3, EBS, or RDS snapshots).

• Use versioning or timestamps to avoid overwrites.

---

✅ Summary

Today’s goal was to gain confidence in MySQL data protection. I now understand how to:

• Take full and partial logical backups

• Restore databases from dumps

• Use compression for efficient storage

• Schedule and automate the process

• Push backups to cloud storage like S3

---

📅 What’s Next?

Day 22 → Deploying a Node.js App on AWS EC2

Tomorrow, I’ll shift gears and deploy a custom Node.js application on an EC2 instance. This will help me understand:

🔹 How modern backend apps run in the cloud
🔹 Installing Node.js and setting up a production-ready environment
🔹 Using PM2 to keep the app alive
🔹 Exposing the app via NGINX as a reverse proxy

We’re now stepping into real-world cloud app deployment — dynamic, scalable, and custom-built!