sudo nano /usr/local/bin/sync_winnipeg.sh

#!/bin/bash

# Variables
PROD_HOST="erp-rds.cadorath.com"
PROD_USER="dev"
PROD_PASS="kR5bLoLOxsaFIBw"
DEV_USER="apps"
DEV_PASS="2jq63NLhJAFSjXHqyytE"
DATABASES=("winnipeg" "lafayette" "chrome" "specialty" "zinc")
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

# Create backup directory
mkdir -p $BACKUP_DIR

echo "========================================="
echo "Starting sync at $(date)"
echo "========================================="

# Loop through each database
for DB_NAME in "${DATABASES[@]}"; do
    echo ""
    echo "Syncing database: $DB_NAME"
    
    # Dump from production
    mysqldump -h $PROD_HOST -u $PROD_USER -p$PROD_PASS \
      --single-transaction \
      --quick \
      --lock-tables=false \
      --add-drop-table \
      --routines \
      --triggers \
      --events \
      $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.sql
    
    # Check if dump was successful
    if [ $? -eq 0 ]; then
        echo "  ✓ Dump successful for $DB_NAME"
        
        # Import to development
        mysql -u $DEV_USER -p$DEV_PASS $DB_NAME < $BACKUP_DIR/${DB_NAME}_${DATE}.sql
        
        if [ $? -eq 0 ]; then
            echo "  ✓ Import successful for $DB_NAME"
        else
            echo "  ✗ ERROR: Import failed for $DB_NAME"
        fi
    else
        echo "  ✗ ERROR: Dump failed for $DB_NAME"
    fi
done

echo ""
echo "Cleaning up old backups (keeping last 7 days)..."
find $BACKUP_DIR -name "*_${DATE}.sql" -mtime +7 -delete

echo "========================================="
echo "Sync completed at $(date)"
echo "========================================="

Make it Executable:

sudo chmod +x /usr/local/bin/sync_winnipeg.sh

Run manually:

sudo /usr/local/bin/sync_winnipeg.sh

Cron job setting (Sunday at 10PM weekly)

File: sudo crontab -e

0 22 * * 0 /usr/local/bin/sync_winnipeg.sh >> /var/log/winnipeg_sync.log 2>&1

tail -f /var/log/winnipeg_sync.log