epic-download/cpanel_deployment/MIGRATION_GUIDE.md

Epic Travel & Expeditions - MongoDB to MySQL Migration Guide

Overview

This guide helps you migrate the Epic Travel & Expeditions application from MongoDB to MySQL for cPanel deployment.

Key Differences

Database Structure

• MongoDB: Document-based, collections, flexible schema
• MySQL: Table-based, structured schema, relationships

Data Type Mapping

MongoDB	MySQL
_id (ObjectId)	id VARCHAR(36) - UUID
String	VARCHAR or TEXT
Number	INT, DECIMAL, NUMERIC
Date	DATETIME
Array	JSON column
Object	JSON column

Migration Steps

1. Export Data from MongoDB

# Export destinations
mongoexport --db=test_database --collection=destinations --out=destinations.json

# Export specials
mongoexport --db=test_database --collection=specials --out=specials.json

# Export admin_users
mongoexport --db=test_database --collection=admin_users --out=admin_users.json

# Export contacts
mongoexport --db=test_database --collection=contacts --out=contacts.json

# Export newsletter_subscribers
mongoexport --db=test_database --collection=newsletter_subscribers --out=newsletter.json

2. Transform Data for MySQL

MongoDB documents need to be transformed to match MySQL schema:

MongoDB Document:

{
  "_id": {"$oid": "507f1f77bcf86cd799439011"},
  "name": "Paris",
  "rating": 4.9
}

MySQL INSERT:

INSERT INTO destinations (id, name, rating) 
VALUES ('507f1f77bcf86cd799439011', 'Paris', 4.9);

3. Code Changes Required

Backend Changes

Old (MongoDB with Motor):

from motor.motor_asyncio import AsyncIOMotorClient

client = AsyncIOMotorClient(mongo_url)
db = client[os.environ['DB_NAME']]

# Query
destinations = await db.destinations.find().to_list(100)

New (MySQL with SQLAlchemy):

from sqlalchemy.orm import Session
from database import SessionLocal, Destination

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# Query
destinations = db.query(Destination).limit(100).all()

API Route Changes

Old (Async MongoDB):

@router.get("/destinations")
async def get_destinations():
    destinations = await db.destinations.find().to_list(100)
    return destinations

New (Sync MySQL):

@router.get("/destinations")
def get_destinations(db: Session = Depends(get_db)):
    destinations = db.query(Destination).limit(100).all()
    return destinations

4. Environment Variables

Old (.env for MongoDB):

MONGO_URL=mongodb://localhost:27017
DB_NAME=test_database

New (.env for MySQL):

MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_DATABASE=epic_travel
MYSQL_USER=dbuser
MYSQL_PASSWORD=password

5. Dependencies

Remove:

motor==3.3.1
pymongo==4.5.0

Add:

PyMySQL>=1.1.0
SQLAlchemy>=2.0.23

Automated Migration Script

#!/usr/bin/env python3
"""
Migrate data from MongoDB to MySQL
"""
from pymongo import MongoClient
from sqlalchemy.orm import Session
from database import engine, SessionLocal, Destination, Special
import uuid

def migrate_destinations():
    # Connect to MongoDB
    mongo_client = MongoClient('mongodb://localhost:27017')
    mongo_db = mongo_client['test_database']
    
    # Connect to MySQL
    mysql_db = SessionLocal()
    
    try:
        # Get all destinations from MongoDB
        mongo_destinations = mongo_db.destinations.find()
        
        for doc in mongo_destinations:
            # Transform MongoDB document to SQLAlchemy model
            destination = Destination(
                id=str(doc.get('id', uuid.uuid4())),
                name=doc['name'],
                location=doc['location'],
                description=doc['description'],
                image=doc['image'],
                category=doc['category'],
                rating=float(doc['rating']),
                price=float(doc['price']),
                currency=doc.get('currency', 'USD'),
                created_at=doc.get('created_at')
            )
            mysql_db.add(destination)
        
        mysql_db.commit()
        print(f"Migrated {mongo_destinations.count()} destinations")
        
    except Exception as e:
        mysql_db.rollback()
        print(f"Error: {e}")
    finally:
        mysql_db.close()
        mongo_client.close()

if __name__ == "__main__":
    migrate_destinations()

Testing Migration

1. Compare Counts

-- MySQL
SELECT COUNT(*) FROM destinations;
SELECT COUNT(*) FROM specials;
SELECT COUNT(*) FROM admin_users;

// MongoDB
db.destinations.count()
db.specials.count()
db.admin_users.count()

2. Sample Data Verification

-- Check a specific destination
SELECT * FROM destinations WHERE name = 'Paris';

3. Test Relationships

-- Check specials with destinations
SELECT d.name, s.discount, s.end_date
FROM destinations d
JOIN specials s ON d.id = s.destination_id;

Performance Considerations

Indexing

MySQL indexes are already defined in schema:

• Primary keys on id columns
• Indexes on frequently queried columns (name, location, category, email)
• Foreign keys for relationships

Connection Pooling

SQLAlchemy provides built-in connection pooling:

engine = create_engine(
    DATABASE_URL,
    pool_size=10,
    max_overflow=20,
    pool_recycle=3600
)

Query Optimization

• Use LIMIT for pagination
• Use indexes for WHERE clauses
• Use JOIN instead of multiple queries
• Cache frequently accessed data

Rollback Plan

If migration fails:

1. Keep MongoDB running alongside MySQL initially
2. Test thoroughly before switching
3. Keep MongoDB backups for 30 days
4. Have both versions of code ready

Post-Migration Checklist

• All collections migrated to tables
• Data counts match between MongoDB and MySQL
• All relationships working correctly
• Authentication still working
• API endpoints returning correct data
• Frontend displaying data correctly
• Image uploads working
• Admin dashboard functional
• Contact forms saving to MySQL
• Newsletter subscriptions working

Common Issues

Issue: Date format differences

Solution: Convert MongoDB ISODate to MySQL DATETIME

created_at = datetime.fromisoformat(doc['created_at'])

Issue: JSON array in specials.highlights

Solution: MySQL JSON column handles this automatically

highlights = json.dumps(['item1', 'item2'])  # Store as JSON string
highlights = json.loads(row.highlights)      # Retrieve and parse

Issue: UUID vs ObjectId

Solution: Use UUID strings in MySQL

import uuid
id = str(uuid.uuid4())

Support

For migration assistance:

• Check logs for specific errors
• Verify MySQL credentials
• Test database connection independently
• Review SQLAlchemy documentation
• Contact: advisor@epictravelexpeditions.com