Production Database Sync Guide¶

Overview¶

The sync-prod-db.sh script provides an automated way to sync your production database from DigitalOcean to your local development environment.

Quick Start¶

# One command to sync everything
./sync-prod-db.sh

# Or via deploy script
./deploy.sh sync-db

Setup¶

1. Get Production Database Credentials¶

You need your DigitalOcean MySQL cluster credentials. Find them in: - DigitalOcean Dashboard → Databases → Your Cluster → Connection Details - Or from your production server's .env file

2. Add to Your Local `.env` File¶

Add these lines to /home/hannah/CODING/uplink/.env:

# --- PRODUCTION DATABASE (for sync-prod-db.sh) --- #
# Get these from DigitalOcean Database Connection Details
PROD_DATABASE_HOST=your-cluster-name.db.ondigitalocean.com
PROD_DATABASE_PORT=25060
PROD_DATABASE_NAME=uplink
PROD_DATABASE_USER=doadmin
PROD_DATABASE_PASS=your-actual-password-here

# Optional: SSL certificate path (recommended for production connections)
# Download from DigitalOcean: Database → Connection Details → CA Certificate
PROD_DATABASE_SSL_CA=/home/hannah/CODING/uplink/ca-certificate.crt

3. (Optional) Download SSL Certificate¶

For secure connections to DigitalOcean:

# Download from DigitalOcean dashboard
# Save as: /home/hannah/CODING/uplink/ca-certificate.crt

# Or use wget if you have the URL
wget -O ca-certificate.crt "https://your-certificate-url"

Usage¶

Basic Sync¶

./sync-prod-db.sh

The script will: 1. Dump production database from DigitalOcean 2. Ask if you want to sanitize sensitive data (recommended) 3. Confirm before replacing your local database 4. Import the data into local Docker MySQL 5. Run migrations to ensure schema compatibility 6. Clean up temporary files

What Data Gets Sanitized?¶

By default, the script sanitizes: - User passwords (except superusers) → set to test value - Email addresses → converted to user{id}@example.com - API tokens → replaced with dev-token-sanitized

You can customize sanitization rules in the script at /tmp/sanitize.sql section.

When to Sync¶

Recommended times to sync: - ✅ Starting a new feature that requires real data - ✅ Debugging production-specific issues - ✅ Weekly/monthly to keep dev data fresh - ✅ After major production data changes

Don't sync: - ❌ During active development (you'll lose uncommitted test data) - ❌ If you have important local test data unsaved

Manual Sync (If Script Fails)¶

If the automated script doesn't work, you can do it manually:

Option 1: From Production Server¶

# 1. SSH to production server
ssh your-server

# 2. Dump database
mysqldump -h prod-db-host -P 25060 -u doadmin -p uplink > prod_dump.sql

# 3. Download to local
# (from your local machine)
scp your-server:~/prod_dump.sql ~/CODING/uplink/

# 4. Import to local Docker MySQL
cd ~/CODING/uplink
docker compose exec -T db mysql -u uplink -padmin uplink < prod_dump.sql

# 5. Run migrations
docker compose exec web python manage.py migrate

Option 2: Direct Connection (DigitalOcean MySQL)¶

# 1. Install mysql-client locally if needed
sudo apt install mysql-client

# 2. Dump from DigitalOcean directly
mysqldump \
  -h your-cluster.db.ondigitalocean.com \
  -P 25060 \
  -u doadmin \
  -p \
  --ssl-ca=ca-certificate.crt \
  uplink > prod_dump.sql

# 3. Import to local Docker MySQL
docker compose exec -T db mysql -u uplink -padmin uplink < prod_dump.sql

# 4. Run migrations
docker compose exec web python manage.py migrate

Troubleshooting¶

"Production database credentials not configured"¶

Solution: Add the PROD_DATABASE_* variables to your .env file (see Setup above).

"mysqldump: unknown option '--set-gtid-purged'" or GTID Errors¶

Error: mysqldump: unknown option '--set-gtid-purged' or ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s)

Cause: Older MySQL client tools (< 5.6) don't support --set-gtid-purged option

Solution 1: Upgrade MySQL client tools (recommended)

# Ubuntu/Debian
sudo apt update
sudo apt install mysql-client

# Check version (should be 5.6+)
mysqldump --version

Solution 2: Use the updated sync script The script now automatically detects your mysqldump version and: - Uses --set-gtid-purged=OFF if supported (MySQL 5.6+) - Filters GTID statements from the dump if not supported - Should work with any mysqldump version

Solution 3: Manual workaround If you still have issues, you can manually filter the dump:

# After sync fails, filter the dump file manually
sed -e '/SET @@GLOBAL.GTID_PURGED/d' \
    -e '/SET @@SESSION.SQL_LOG_BIN/d' \
    prod_dump.sql > prod_dump_filtered.sql

# Import manually
docker compose exec -T db mysql -u uplink -padmin uplink < prod_dump_filtered.sql

"Access denied" or Authentication Error¶

Possible causes: - Wrong password in .env - IP not whitelisted in DigitalOcean database settings - SSL certificate required but not provided

Solution: 1. Check DigitalOcean → Databases → Trusted Sources 2. Add your IP address or use 0.0.0.0/0 for testing 3. Verify password is correct 4. Download and configure SSL certificate

"Unable to connect" or Timeout¶

Possible causes: - Firewall blocking outbound connections - DigitalOcean database cluster is down - Wrong host/port in .env

Solution: 1. Test connection manually:

mysql -h your-cluster.db.ondigitalocean.com -P 25060 -u doadmin -p

2. Check DigitalOcean database status 3. Verify host and port in .env

Import Fails with "MySQL server has gone away"¶

Cause: Dump file too large or slow connection

Solution:

# Increase timeout and batch size
docker compose exec -T db mysql \
  -u uplink -padmin \
  --max_allowed_packet=512M \
  --net_read_timeout=3600 \
  uplink < prod_dump.sql

"Docker containers not running"¶