Project 5 – Enterprise Data Platform

1. Data Platform Architecture

A complete cloud data platform requires multiple types of storage, each optimized for different use cases. ABC Retail needs structured data storage (relational database), unstructured data storage (object storage for images and files), key-value caching, and a robust backup and disaster recovery strategy. You will implement each component and document how they work together.

2. Step-by-Step Action Items

PHASE 1 · STEP 1 Design and Implement the ABC Retail Database Schema

A database schema is the blueprint that defines the structure of all tables, columns, data types, and relationships. A well-designed schema is critical — poor database design causes performance problems, data inconsistencies, and security vulnerabilities that are very difficult to fix after data is in production.

Log in to the DB server VM and connect to MariaDB:

sudo mysql -u root -p # Enter your root password

Select the ABC Retail database and create all tables:

USE abc_retail; -- ============================================================ -- TABLE 1: customers -- Stores all customer account information -- ============================================================ CREATE TABLE customers ( customer_id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, phone VARCHAR(20), address TEXT, city VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, is_active BOOLEAN DEFAULT TRUE ); -- ============================================================ -- TABLE 2: products -- Product catalog -- ============================================================ CREATE TABLE products ( product_id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(200) NOT NULL, description TEXT, price DECIMAL(10, 2) NOT NULL, stock_qty INT DEFAULT 0, category VARCHAR(100), image_url VARCHAR(500), -- Links to MinIO object storage created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- ============================================================ -- TABLE 3: orders -- Customer orders (references customers table) -- ============================================================ CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, total_amount DECIMAL(10, 2) NOT NULL, status ENUM('pending','processing','shipped','delivered','cancelled') DEFAULT 'pending', FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); -- ============================================================ -- TABLE 4: order_items -- Individual items within each order -- ============================================================ CREATE TABLE order_items ( item_id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, unit_price DECIMAL(10, 2) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (product_id) REFERENCES products(product_id) );

This schema demonstrates several important database concepts: AUTO_INCREMENT PRIMARY KEY — each row gets a unique numeric ID automatically. VARCHAR vs TEXT — VARCHAR has a maximum length (efficient for short strings), TEXT for unlimited content. DECIMAL(10,2) for prices — floating-point numbers (FLOAT, DOUBLE) are not precise enough for financial calculations. FOREIGN KEY — links tables together, enforcing data integrity (you cannot create an order for a customer_id that doesn't exist in the customers table). ENUM — restricts values to a predefined list, preventing invalid order statuses. The image_url column pointing to MinIO demonstrates the cloud pattern of storing binary files in object storage while keeping references in the relational database.

Insert test data to populate the database:

-- Insert sample customers INSERT INTO customers (first_name, last_name, email, phone, city) VALUES ('Arjun', 'Sharma', 'arjun.sharma@email.com', '+91-9876543210', 'Mumbai'), ('Priya', 'Patel', 'priya.patel@email.com', '+91-9876543211', 'Delhi'), ('Rahul', 'Kumar', 'rahul.kumar@email.com', '+91-9876543212', 'Bangalore'); -- Insert sample products INSERT INTO products (product_name, price, stock_qty, category) VALUES ('Laptop Stand - Ergonomic', 1299.00, 150, 'Electronics'), ('Wireless Keyboard', 899.00, 200, 'Electronics'), ('Desk Organizer', 299.00, 500, 'Office Supplies'), ('Coffee Mug Set', 499.00, 300, 'Kitchen'); -- Insert sample orders INSERT INTO orders (customer_id, total_amount, status) VALUES (1, 2198.00, 'delivered'), (2, 299.00, 'processing'), (3, 1399.00, 'shipped'); -- Verify data was inserted SELECT c.first_name, c.last_name, o.order_id, o.total_amount, o.status FROM customers c JOIN orders o ON c.customer_id = o.customer_id; EXIT;

The final SELECT statement uses a JOIN — connecting two tables on their shared customer_id field. This demonstrates the power of relational databases: data stored separately in normalized tables can be combined in queries without duplicating data. The output should show each customer's name alongside their orders, proving the foreign key relationships work correctly.

Verify database tables were created correctly:

sudo mysql -u root -p abc_retail -e "SHOW TABLES; DESCRIBE customers;"

The -e flag allows you to pass SQL commands directly from the shell without entering interactive mode. This is useful for automation scripts that need to query databases. You should see a list of all 4 tables and the column definitions of the customers table. Take a screenshot — this is a documentation deliverable.

PHASE 1 · STEP 2 Set Up MinIO Object Storage (AWS S3 Equivalent)

MinIO is an open-source object storage server that is 100% compatible with the Amazon S3 API. This means code written for MinIO works with AWS S3 without modification — it's the perfect local simulator. Object storage is used to store unstructured data like images, videos, documents, and backups.

On the Web Server VM, run MinIO as a Docker container:

docker run -d \ --name minio \ -p 9000:9000 \ -p 9001:9001 \ --restart unless-stopped \ -e MINIO_ROOT_USER=minioadmin \ -e MINIO_ROOT_PASSWORD=MinioPass@2024 \ -v minio-data:/data \ minio/minio server /data --console-address ":9001"

This starts MinIO with: port 9000 for the S3 API (used by applications), port 9001 for the web console (used by administrators), a Docker volume for persistent data storage, environment variables for the admin credentials, and the command server /data --console-address ":9001" telling MinIO to store data in /data and run the web console on port 9001. The console allows you to create buckets and upload files through a graphical interface — just like the AWS S3 console.

Open the MinIO Console in your host browser: http://[VM-IP]:9001
Username: minioadmin
Password: MinioPass@2024

Create a bucket for product images:
• Click "Buckets" in the left sidebar
• Click "Create Bucket"
• Bucket Name: abc-retail-products
• Click "Create Bucket"

Buckets are the top-level containers in object storage — think of them like a single flat directory that can hold unlimited files. Bucket names must be globally unique in AWS S3 (so companies use names like "company-name-purpose-environment"). In MinIO they only need to be unique on your instance. Each file stored in a bucket is called an "object" and has a unique "key" (path) within the bucket.

Upload a test file via the command line using MinIO Client (mc):

# Install MinIO client wget https://dl.min.io/client/mc/release/linux-amd64/mc -O /tmp/mc chmod +x /tmp/mc sudo mv /tmp/mc /usr/local/bin/mc # Configure mc to connect to your MinIO instance mc alias set local http://localhost:9000 minioadmin MinioPass@2024 # Create a test file (simulating a product image) echo "Product Image: ABC Retail Laptop Stand SKU-001" > /tmp/product-001.txt # Upload it to the bucket mc cp /tmp/product-001.txt local/abc-retail-products/images/product-001.txt # List the uploaded file mc ls local/abc-retail-products/

The MinIO Client (mc) is the command-line equivalent of the AWS CLI for S3. The mc alias set command stores the connection details (URL + credentials) under the alias "local". The mc cp command uploads a file to the bucket at the specified path. The path images/product-001.txt creates a "virtual folder" structure — object storage doesn't actually have folders, but the key (path) can contain slashes that tools display as folder hierarchies.

PHASE 1 · STEP 3 Deploy Redis for In-Memory Caching

Redis is an in-memory key-value database used for caching frequently accessed data. Instead of querying MariaDB (which reads from disk) for every page load, the application checks Redis first. Cache hits are 100x faster than database queries. Redis is the cloud equivalent of services like AWS ElastiCache or Azure Cache for Redis.

Run Redis as a Docker container:

docker run -d \ --name redis-cache \ -p 6379:6379 \ --restart unless-stopped \ -v redis-data:/data \ redis:7-alpine \ redis-server --appendonly yes

We run Redis on the standard port 6379, using the Alpine variant (smallest image size). The --appendonly yes flag enables AOF (Append-Only File) persistence — without this, all cached data would be lost if Redis restarts. While cache data is by definition ephemeral, for session storage (user login tokens), persistence ensures users aren't logged out unexpectedly when Redis restarts.

Connect to Redis and practice key-value operations:

docker exec -it redis-cache redis-cli # Inside redis-cli: # Store a product price in cache (with 1-hour expiry) SET product:1:price "1299.00" EX 3600 # Store a user session token SET session:user123 "{'id':1,'name':'Arjun','cart':[]}" EX 86400 # Retrieve values GET product:1:price GET session:user123 # Check how long until key expires TTL product:1:price # List all keys KEYS * # Delete a key DEL session:user123 # Exit redis-cli EXIT

Breaking down Redis commands: SET key value EX seconds stores a value with automatic expiration (TTL). GET key retrieves the value. TTL key returns seconds until the key expires (-1 means no expiry, -2 means the key doesn't exist). KEYS * lists all keys (use with caution in production — on a large Redis instance this can be slow). The key naming convention product:1:price uses colons as namespace separators — a standard Redis convention that keeps key names organized and prevents collisions between different types of data.

PHASE 2 · STEP 1 Implement Automated Database Backup Strategy

Backups are the last line of defense against data loss. In cloud environments, managed database services perform automated backups — but you must configure them and regularly test restores. This step builds an automated backup system from scratch, teaching you how managed cloud backup services actually work behind the scenes.

Create the backup script:

sudo nano /usr/local/bin/abc-retail-backup.sh

Type the complete backup script:

#!/bin/bash # ============================================================ # ABC Retail Automated Database Backup Script # Schedule: Run via cron daily at 2:00 AM # ============================================================ set -euo pipefail # Exit on error, undefined vars, and pipe failures # ---- Configuration ---- DB_USER="root" DB_PASS="YourRootPasswordHere" DB_NAME="abc_retail" BACKUP_DIR="/data/abc-retail/backups" DATE=$(date +%Y%m%d_%H%M%S) BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz" RETENTION_DAYS=7 LOG_FILE="/var/log/abc-retail-backup.log" # ---- Logging function ---- log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE" } log "=== Starting ABC Retail database backup ===" # ---- Create backup directory ---- mkdir -p "$BACKUP_DIR" # ---- Perform the database dump ---- log "Dumping database: $DB_NAME" mysqldump \ --user="$DB_USER" \ --password="$DB_PASS" \ --single-transaction \ --routines \ --triggers \ "$DB_NAME" | gzip > "$BACKUP_FILE" # ---- Verify backup was created and has data ---- if [ -f "$BACKUP_FILE" ] && [ -s "$BACKUP_FILE" ]; then BACKUP_SIZE=$(du -sh "$BACKUP_FILE" | cut -f1) log "SUCCESS: Backup created at $BACKUP_FILE (Size: $BACKUP_SIZE)" else log "ERROR: Backup failed or empty file created!" exit 1 fi # ---- Delete backups older than retention period ---- log "Cleaning up backups older than $RETENTION_DAYS days..." find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime "+$RETENTION_DAYS" -delete REMAINING=$(ls "$BACKUP_DIR" | wc -l) log "Cleanup complete. Remaining backups: $REMAINING" log "=== Backup completed successfully ==="

This production-grade backup script implements several best practices: set -euo pipefail ensures the script exits immediately if any command fails (preventing silent failures). mysqldump --single-transaction creates a consistent snapshot without locking tables (critical for production databases serving live traffic). gzip compresses the SQL dump — database backups can be 10x smaller when compressed. The retention policy (delete backups older than 7 days) implements cloud storage lifecycle management. The logging function records every action with timestamps — essential for auditing backup success/failure.

Make the script executable and run it once manually to test:

sudo chmod +x /usr/local/bin/abc-retail-backup.sh # Update the password in the script first! sudo nano /usr/local/bin/abc-retail-backup.sh # Change: DB_PASS="YourRootPasswordHere" to your actual password # Run manually to test sudo /usr/local/bin/abc-retail-backup.sh # Verify backup was created ls -lh /data/abc-retail/backups/

Always test your backup script manually before scheduling it with cron. A backup script that fails silently gives you a false sense of security — you discover your backups were failing only when you need to restore data, which is the worst possible time to find out. The -lh flags on ls show file details in human-readable format including the backup file size.

Schedule the backup with cron (Linux's built-in task scheduler):

sudo crontab -e # Select editor option 1 (nano) if prompted

Add this line to the crontab file (run backup at 2 AM daily):

0 2 * * * /usr/local/bin/abc-retail-backup.sh >> /var/log/abc-retail-backup.log 2>&1

Cron uses a 5-field time format: minute hour day-of-month month day-of-week. 0 2 * * * means "at minute 0 of hour 2, every day, every month, every weekday" — so every day at 2:00 AM. The >> appends script output to the log file. The 2>&1 redirects error output (stderr) to the same file as normal output (stdout) — so errors are captured in the log. Save and exit nano to activate the cron job.

Verify the cron job is registered:

sudo crontab -l

The crontab -l command lists all currently scheduled cron jobs for the root user. You should see the backup entry you just added. In cloud environments, scheduled tasks like this are handled by services like AWS EventBridge Scheduler, Azure Logic Apps, or Google Cloud Scheduler — but they all work on the same cron time expression syntax.

PHASE 2 · STEP 2 Test Backup Restoration – The Most Important Step

A backup you have never restored is not a backup — it's a hope. Regularly testing restore procedures is mandatory in any professional environment. This step simulates a database disaster and proves you can recover.

Simulate a disaster — accidentally drop the orders table:

sudo mysql -u root -p abc_retail -e "DROP TABLE order_items; DROP TABLE orders;" # Verify the tables are gone: sudo mysql -u root -p abc_retail -e "SHOW TABLES;"

Restore from the backup file you created:

# Find the most recent backup ls -lt /data/abc-retail/backups/ | head -5 # Restore the database (replace backup filename with your actual file) gunzip -c /data/abc-retail/backups/abc_retail_YYYYMMDD_HHMMSS.sql.gz | \ sudo mysql -u root -p abc_retail

The restore process uses gunzip -c to decompress the backup file and pipe it directly to mysql. The -c flag decompresses to stdout (the terminal) rather than creating a file — which feeds directly into mysql via the pipe. This avoids creating a large uncompressed SQL file just to restore. The mysql command reads the SQL statements from stdin and executes them, recreating all tables and data.

Verify data was restored:

sudo mysql -u root -p abc_retail -e "SHOW TABLES; SELECT COUNT(*) as order_count FROM orders;"

After restore, you should see all 4 tables back and the order count matching what was in the backup. Document the time it took to restore — this is your actual Recovery Time Objective (RTO). In a production environment, a slow restore that takes 8 hours to recover a 500 GB database when management expected a 2-hour recovery is a serious problem. Cloud managed databases (AWS RDS, Azure SQL) can restore to a point-in-time in minutes because they use log-based recovery rather than full dumps.

PHASE 3 · DOCUMENT Write the Disaster Recovery Plan

A Disaster Recovery Plan (DRP) is a documented process for restoring operations after a catastrophic failure. Every enterprise company has one. Without a tested DRP, an outage turns into a chaotic emergency where everyone argues about what to do while the system is down.

Create a document titled: "ABC Retail – Disaster Recovery Plan v1.0"

Include these sections in the document:

DISASTER RECOVERY PLAN — ABC RETAIL PVT. LTD. SECTION 1: RECOVERY OBJECTIVES Recovery Time Objective (RTO): 4 hours (Maximum acceptable downtime before business operations are critically affected) Recovery Point Objective (RPO): 24 hours (Maximum acceptable data loss — we can lose up to 24 hours of data) SECTION 2: DISASTER SCENARIOS & RESPONSE Scenario A: Database Server Disk Failure 1. Alert: Monitoring detects database unavailability 2. Assess: Identify failure type (disk, OS, network) 3. Restore: Spin up replacement VM 4. Recover: Restore from last nightly backup (gunzip | mysql) 5. Verify: Test data integrity with row count checks 6. Reconnect: Update web server config to point to new DB IP Estimated Time: 2-3 hours Scenario B: Web Server Complete Failure 1. Alert: Load balancer health check fails 2. Restore: Launch new web server VM from Project 2 setup script 3. Deploy: Pull Docker image from Docker Hub and run compose 4. Test: Verify website accessible through load balancer Estimated Time: 30-60 minutes (key advantage of containerization) Scenario C: Data Corruption (Accidental DELETE) 1. Stop: Immediately put application in maintenance mode 2. Identify: Determine timestamp of corruption 3. Restore: Use backup from before corruption event 4. Verify: Confirm affected records are restored Estimated Time: 1-2 hours SECTION 3: BACKUP INVENTORY Database: Daily full backup at 02:00 AM → /data/abc-retail/backups/ Object Store: MinIO bucket replication (manual monthly copy) Config Files: Git repository (haproxy.cfg, docker-compose.yml, etc.) Retention: 7 daily backups (168 hours of history) SECTION 4: RECOVERY CONTACT MATRIX Incident Commander: [Database Administrator Name] Cloud Engineer: [Cloud Team Lead] Business Sponsor: [IT Manager] Communication: Status updates every 30 minutes during outage SECTION 5: RESTORE PROCEDURE (Quick Reference) Full restore: gunzip -c [backup.sql.gz] | mysql -u root -p abc_retail Verify: mysql abc_retail -e "SELECT COUNT(*) FROM orders;" Notify: Send email to stakeholders with ETA

This DR plan includes the two most important metrics in disaster recovery: RTO (Recovery Time Objective) — how long you have before the business is critically impacted. An online store that is down for 8 hours loses significant revenue and customer trust. RPO (Recovery Point Objective) — how much data loss is acceptable. With daily backups, you risk losing up to 24 hours of orders. For a high-value business, you might implement hourly backups or streaming replication to reduce RPO to minutes. These trade-offs have direct cost implications — more frequent backups cost more in storage and computing.

3. Data Lifecycle Pipeline

4. Configuration Files & Templates

abc-retail-backup.sh — Production Database Backup Script

#!/bin/bash # ABC Retail Database Backup — runs daily at 02:00 AM via cron # Cron entry: 0 2 * * * /usr/local/bin/abc-retail-backup.sh set -euo pipefail DB_NAME="abc_retail" BACKUP_DIR="/data/abc-retail/backups" DATE=$(date +%Y%m%d_%H%M%S) BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz" RETENTION=7 mkdir -p "$BACKUP_DIR" # Create compressed dump mysqldump --single-transaction --routines --triggers \ "$DB_NAME" | gzip > "$BACKUP_FILE" # Verify and log [ -s "$BACKUP_FILE" ] && \ echo "[$(date)] BACKUP OK: $BACKUP_FILE ($(du -sh $BACKUP_FILE | cut -f1))" || \ { echo "BACKUP FAILED"; exit 1; } # Retention cleanup find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION" -delete

5. Deliverables Summary

📄 Files to Submit

Database schema SQL file (all 4 tables)
abc-retail-backup.sh script
Disaster Recovery Plan document (PDF)
Screenshot: SHOW TABLES + DESCRIBE customers
Screenshot: MinIO console showing bucket + uploaded file
Screenshot: Redis-cli showing SET/GET operations
Screenshot: Backup file in /data/abc-retail/backups/
Screenshot: Database tables restored after simulate-drop test

✅ Verification Checklist

All 4 database tables created with correct schema
Sample data inserted (3 customers, 4 products, 3 orders)
JOIN query returns correct results
MinIO running and accessible at port 9001
Bucket created and file uploaded via mc CLI
Redis running and SET/GET operations work
Backup script runs without errors
Backup file created and is non-empty
Cron job registered (crontab -l shows the entry)
Restore test: tables dropped then recovered from backup
DR Plan document is complete and professional

6. Why We Did This & What It Accomplishes

Strategic Intent & Operational Impact

Why We Did This

Most cloud data breaches and outages involve database misconfiguration or missing backups. Building a backup system from scratch teaches you what cloud database services (AWS RDS automated backups, Azure SQL point-in-time restore) do automatically under the hood.
MinIO provides hands-on experience with the S3 API without any AWS cost. The same mc commands work with a simple URL change to target real AWS S3. Every cloud provider offers object storage because it is the most cost-effective and scalable way to store unstructured data.
Redis caching reduces database load dramatically — a production e-commerce site without caching can experience 10x the database queries of one with proper caching, leading to expensive database scaling costs.
Documenting RTO and RPO forces business conversations about acceptable risk — these are executive decisions, not technical ones. Cloud engineers must understand both the technical implementation AND the business implications.

What This Accomplishes

ABC Retail now has a complete data platform: structured data (MariaDB), object storage (MinIO), caching (Redis), automated daily backups, a tested restore procedure, and a formal DR plan.
The database schema you designed becomes the persistent data layer for all remaining projects. The same MariaDB schema will be used when deploying on Kubernetes in Project 9.
Your backup automation script is a primitive version of what Terraform and Ansible (Project 8) will manage at scale — automated, version-controlled, and repeatable operations.