sync-database-mysql.sh

#!/bin/bash
#
# Database Sync Script (MySQL)
# Copies a MySQL database from one environment to another
#
# Usage:
#   ./sync-database-mysql.sh [OPTIONS]
#
# Options:
#   --source-url      Source database URL (direct, overrides dget)
#   --target-url      Target database URL (direct, overrides dget)
#   -p, --project     Project name for dget (default: mtndev)
#   -s, --source      Source environment (e.g., prd, stg) - required if not using --source-url
#   -t, --target      Target environment (e.g., stg, prd) - required if not using --target-url
#   -k, --key         Database URL key in dotenv (default: DATABASE_URL)
#   -d, --database    Database name to drop/recreate (extracted from URL if not provided)
#   -o, --output-dir  Directory for dump files (default: .data)
#   --clean           Clean the dump file (remove DEFINER, SQL SECURITY, etc.) before restoring
#   -y, --yes         Skip confirmation prompt
#   -h, --help        Show this help message
#
# Examples:
#   # Sync production to staging (using dget)
#   ./sync-database-mysql.sh -s prd -t stg
#
#   # Sync using direct URLs with cleaning
#   ./sync-database-mysql.sh --source-url "mysql://user:pass@host:3306/db" --target-url "mysql://user:pass@host:3306/db" --clean
#
#   # Use different project and key
#   ./sync-database-mysql.sh -p myproject -s prd -t stg -k DATABASE_URL

set -e

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# Default values
PROJECT="mtndev"
SOURCE_ENV=""
TARGET_ENV=""
SOURCE_URL_DIRECT=""
TARGET_URL_DIRECT=""
DB_KEY="DATABASE_URL"
OUTPUT_DIR=".data"
SKIP_CONFIRM=false
DATABASE_NAME=""
CLEAN_DUMP=false

# Parse command line arguments
while [[ $# -gt 0 ]]; do
    case $1 in
        --source-url)
            SOURCE_URL_DIRECT="$2"
            shift 2
            ;;
        --target-url)
            TARGET_URL_DIRECT="$2"
            shift 2
            ;;
        -p|--project)
            PROJECT="$2"
            shift 2
            ;;
        -s|--source)
            SOURCE_ENV="$2"
            shift 2
            ;;
        -t|--target)
            TARGET_ENV="$2"
            shift 2
            ;;
        -k|--key)
            DB_KEY="$2"
            shift 2
            ;;
        -d|--database)
            DATABASE_NAME="$2"
            shift 2
            ;;
        -o|--output-dir)
            OUTPUT_DIR="$2"
            shift 2
            ;;
        --clean)
            CLEAN_DUMP=true
            shift
            ;;
        -y|--yes)
            SKIP_CONFIRM=true
            shift
            ;;
        -h|--help)
            head -40 "$0" | tail -37
            exit 0
            ;;
        *)
            echo -e "${RED}Unknown option: $1${NC}"
            exit 1
            ;;
    esac
done

# Function to parse MySQL URL and extract components
parse_mysql_url() {
    local url="$1"
    # Format: mysql://user:pass@host:port/dbname?params
    # Extract components
    if [[ "$url" =~ mysql://([^:]+):([^@]+)@([^:/]+):?([0-9]*)/([^?]+) ]]; then
        MYSQL_USER="${BASH_REMATCH[1]}"
        MYSQL_PASS="${BASH_REMATCH[2]}"
        MYSQL_HOST="${BASH_REMATCH[3]}"
        MYSQL_PORT="${BASH_REMATCH[4]:-3306}"
        MYSQL_DB="${BASH_REMATCH[5]}"
    else
        echo -e "${RED}Error: Invalid MySQL URL format: $url${NC}"
        echo "Expected format: mysql://user:pass@host:port/dbname"
        exit 1
    fi
}

# Determine how to get database URLs
if [[ -n "$SOURCE_URL_DIRECT" ]] && [[ -n "$TARGET_URL_DIRECT" ]]; then
    # Use direct URLs
    SOURCE_URL="$SOURCE_URL_DIRECT"
    TARGET_URL="$TARGET_URL_DIRECT"
    echo -e "${BLUE}Using direct database URLs...${NC}"
elif [[ -z "$SOURCE_URL_DIRECT" ]] && [[ -z "$TARGET_URL_DIRECT" ]]; then
    # Use dget - validate environments are provided
    if [[ -z "$SOURCE_ENV" ]] || [[ -z "$TARGET_ENV" ]]; then
        echo -e "${RED}Error: Either provide --source-url/--target-url OR --source/--target environments${NC}"
        echo "Usage: $0 --source-url <url> --target-url <url>"
        echo "   OR: $0 -s <source_env> -t <target_env>"
        echo "Run '$0 --help' for more information"
        exit 1
    fi
    
    if [[ "$SOURCE_ENV" == "$TARGET_ENV" ]]; then
        echo -e "${RED}Error: Source and target environments cannot be the same${NC}"
        exit 1
    fi
    
    # Get database URLs using dget
    echo -e "${BLUE}Fetching database URLs using dget...${NC}"
    SOURCE_URL=$(dget -p "$PROJECT" "$SOURCE_ENV" "$DB_KEY")
    TARGET_URL=$(dget -p "$PROJECT" "$TARGET_ENV" "$DB_KEY")
    
    if [[ -z "$SOURCE_URL" ]]; then
        echo -e "${RED}Error: Could not get source database URL${NC}"
        exit 1
    fi
    
    if [[ -z "$TARGET_URL" ]]; then
        echo -e "${RED}Error: Could not get target database URL${NC}"
        exit 1
    fi
else
    echo -e "${RED}Error: Both --source-url and --target-url must be provided together, or use --source/--target with dget${NC}"
    exit 1
fi

# Validate URLs are set
if [[ -z "$SOURCE_URL" ]] || [[ -z "$TARGET_URL" ]]; then
    echo -e "${RED}Error: Source and target database URLs are required${NC}"
    exit 1
fi

# Parse source URL
parse_mysql_url "$SOURCE_URL"
SOURCE_USER="$MYSQL_USER"
SOURCE_PASS="$MYSQL_PASS"
SOURCE_HOST="$MYSQL_HOST"
SOURCE_PORT="$MYSQL_PORT"
SOURCE_DB="$MYSQL_DB"

# Parse target URL
parse_mysql_url "$TARGET_URL"
TARGET_USER="$MYSQL_USER"
TARGET_PASS="$MYSQL_PASS"
TARGET_HOST="$MYSQL_HOST"
TARGET_PORT="$MYSQL_PORT"
TARGET_DB="$MYSQL_DB"

# Use provided database name or extract from target URL
if [[ -z "$DATABASE_NAME" ]]; then
    DATABASE_NAME="$TARGET_DB"
fi

# Create output directory if it doesn't exist
mkdir -p "$OUTPUT_DIR"

# Generate dump filename
if [[ -n "$SOURCE_ENV" ]]; then
    DUMP_FILE="$OUTPUT_DIR/${DATABASE_NAME}_${SOURCE_ENV}_$(date '+%Y-%m-%d_%H%M%S').sql"
else
    DUMP_FILE="$OUTPUT_DIR/${DATABASE_NAME}_$(date '+%Y-%m-%d_%H%M%S').sql"
fi

# Show summary
echo ""
echo -e "${YELLOW}════════════════════════════════════════════════════════════${NC}"
echo -e "${YELLOW}                    DATABASE SYNC SUMMARY${NC}"
echo -e "${YELLOW}════════════════════════════════════════════════════════════${NC}"
if [[ -n "$SOURCE_ENV" ]] && [[ -n "$TARGET_ENV" ]]; then
    echo -e "Project:         ${GREEN}$PROJECT${NC}"
    echo -e "Source:          ${GREEN}$SOURCE_ENV${NC}"
    echo -e "Target:          ${RED}$TARGET_ENV${NC}"
else
    # Show connection info (hide password)
    echo -e "Source:          ${GREEN}$SOURCE_USER@$SOURCE_HOST:$SOURCE_PORT/$SOURCE_DB${NC}"
    echo -e "Target:          ${RED}$TARGET_USER@$TARGET_HOST:$TARGET_PORT/$DATABASE_NAME${NC}"
fi
echo -e "Database:        ${GREEN}$DATABASE_NAME${NC}"
echo -e "Dump file:       ${GREEN}$DUMP_FILE${NC}"
echo -e "${YELLOW}════════════════════════════════════════════════════════════${NC}"
echo ""
if [[ -n "$TARGET_ENV" ]]; then
    echo -e "${RED}⚠️  WARNING: This will DROP and recreate the ${TARGET_ENV} database!${NC}"
else
    echo -e "${RED}⚠️  WARNING: This will DROP and recreate the target database!${NC}"
fi
echo ""

# Confirm unless --yes flag is set
if [[ "$SKIP_CONFIRM" != true ]]; then
    read -p "Are you sure you want to proceed? (y/N) " -n 1 -r
    echo
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
        echo -e "${YELLOW}Aborted.${NC}"
        exit 0
    fi
fi

echo ""

# Function to clean SET statements that require SUPER privileges
clean_set_statements() {
    local input_file="$1"
    local output_file="$2"
    
    echo -e "${BLUE}      Removing SET statements requiring SUPER privileges...${NC}"
    
    # Remove SET statements that require SUPER privileges:
    # - SET @@GLOBAL.* (requires SUPER)
    # - SET @@SESSION.SQL_LOG_BIN (requires SUPER)
    # - SET sql_log_bin (requires SUPER)
    # - SET @MYSQLDUMP* (dump-related variables, safe to remove)
    sed -E \
      -e '/^SET @@GLOBAL/d' \
      -e '/^SET @@SESSION\.SQL_LOG_BIN/d' \
      -e '/^SET @@SESSION\.sql_log_bin/d' \
      -e '/^SET sql_log_bin/d' \
      -e '/^SET @MYSQLDUMP/d' \
      -e '/^SET @MYSQLDUMP_TEMP_LOG_BIN/d' \
      "$input_file" > "$output_file"
}

# Function to clean MySQL dump file (DEFINER statements)
clean_dump_file() {
    local input_file="$1"
    local output_file="$2"
    
    echo -e "${BLUE}      Cleaning DEFINER statements...${NC}"
    
    sed -E \
      -e 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' \
      -e 's/SQL SECURITY DEFINER/SQL SECURITY INVOKER/g' \
      -e '/^\/\*!50013 DEFINER=/d' \
      -e '/^\/\*!50017 DEFINER=/d' \
      "$input_file" > "$output_file"
    
    local input_size=$(wc -c < "$input_file" | tr -d ' ')
    local output_size=$(wc -c < "$output_file" | tr -d ' ')
    local removed=$((input_size - output_size))
    
    echo -e "${GREEN}      ✓ Cleaned: removed ~$removed bytes${NC}"
}

# Check if mysqldump is available
if ! command -v mysqldump &> /dev/null; then
    echo -e "${RED}Error: mysqldump is not installed or not in PATH${NC}"
    echo "Install MySQL client tools to use this script"
    exit 1
fi

# Check if mysql is available
if ! command -v mysql &> /dev/null; then
    echo -e "${RED}Error: mysql client is not installed or not in PATH${NC}"
    echo "Install MySQL client tools to use this script"
    exit 1
fi

# Step 1: Dump source database
if [[ -n "$SOURCE_ENV" ]]; then
    echo -e "${BLUE}[1/3] Dumping ${SOURCE_ENV} database...${NC}"
else
    echo -e "${BLUE}[1/3] Dumping source database...${NC}"
fi

# Build mysqldump command (use MYSQL_PWD env var for password security)
# Using --skip-lock-tables to avoid requiring RELOAD/LOCK TABLES privileges on RDS
# --set-gtid-purged=OFF to avoid GTID-related statements that require SUPER privileges
# --quick for faster dumps, --no-tablespaces to avoid issues with tablespace privileges
# Note: Without --single-transaction, dump may not be perfectly consistent for MyISAM tables,
# but this avoids privilege issues on RDS where users often don't have RELOAD privileges
MYSQLDUMP_OPTS="-h${SOURCE_HOST} -P${SOURCE_PORT} -u${SOURCE_USER}"
MYSQLDUMP_OPTS="${MYSQLDUMP_OPTS} --skip-lock-tables --quick --no-tablespaces --set-gtid-purged=OFF"
MYSQLDUMP_OPTS="${MYSQLDUMP_OPTS} --routines --triggers --add-drop-table --default-character-set=utf8mb4"

MYSQL_PWD="${SOURCE_PASS}" mysqldump $MYSQLDUMP_OPTS "$SOURCE_DB" > "$DUMP_FILE"
DUMP_SIZE=$(du -h "$DUMP_FILE" | cut -f1)
echo -e "${GREEN}      ✓ Dump complete: $DUMP_FILE ($DUMP_SIZE)${NC}"

# Always clean SET statements that require SUPER privileges
TEMP_FILE="${DUMP_FILE%.sql}_temp.sql"
clean_set_statements "$DUMP_FILE" "$TEMP_FILE"
mv "$TEMP_FILE" "$DUMP_FILE"

# Clean DEFINER statements if requested
RESTORE_FILE="$DUMP_FILE"
if [[ "$CLEAN_DUMP" == true ]]; then
    CLEANED_DUMP_FILE="${DUMP_FILE%.sql}_cleaned.sql"
    clean_dump_file "$DUMP_FILE" "$CLEANED_DUMP_FILE"
    RESTORE_FILE="$CLEANED_DUMP_FILE"
fi

# Step 2: Drop and recreate target database
if [[ -n "$TARGET_ENV" ]]; then
    echo -e "${BLUE}[2/3] Dropping and recreating ${TARGET_ENV} database...${NC}"
else
    echo -e "${BLUE}[2/3] Dropping and recreating target database...${NC}"
fi

# Build mysql connection string for admin operations (use MYSQL_PWD env var for password security)
MYSQL_ADMIN_OPTS="-h${TARGET_HOST} -P${TARGET_PORT} -u${TARGET_USER}"

# Drop and create database (connect without specifying database)
MYSQL_PWD="${TARGET_PASS}" mysql $MYSQL_ADMIN_OPTS -e "DROP DATABASE IF EXISTS \`$DATABASE_NAME\`;"
MYSQL_PWD="${TARGET_PASS}" mysql $MYSQL_ADMIN_OPTS -e "CREATE DATABASE \`$DATABASE_NAME\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
echo -e "${GREEN}      ✓ Database recreated${NC}"

# Step 3: Restore to target database
if [[ -n "$TARGET_ENV" ]]; then
    echo -e "${BLUE}[3/3] Restoring to ${TARGET_ENV} database...${NC}"
else
    echo -e "${BLUE}[3/3] Restoring to target database...${NC}"
fi

# Build mysql connection string for restore (use MYSQL_PWD env var for password security)
MYSQL_RESTORE_OPTS="-h${TARGET_HOST} -P${TARGET_PORT} -u${TARGET_USER}"
MYSQL_RESTORE_OPTS="${MYSQL_RESTORE_OPTS} --default-character-set=utf8mb4"

MYSQL_PWD="${TARGET_PASS}" mysql $MYSQL_RESTORE_OPTS "$DATABASE_NAME" < "$RESTORE_FILE"
echo -e "${GREEN}      ✓ Restore complete${NC}"

echo ""
echo -e "${GREEN}════════════════════════════════════════════════════════════${NC}"
echo -e "${GREEN}           DATABASE SYNC COMPLETED SUCCESSFULLY${NC}"
echo -e "${GREEN}════════════════════════════════════════════════════════════${NC}"
if [[ -n "$SOURCE_ENV" ]] && [[ -n "$TARGET_ENV" ]]; then
    echo -e "Source:  ${SOURCE_ENV} → Target: ${TARGET_ENV}"
else
    echo -e "Database sync completed"
fi
echo -e "Dump file saved: $DUMP_FILE"
if [[ "$CLEAN_DUMP" == true ]]; then
    echo -e "Cleaned dump file: $CLEANED_DUMP_FILE"
fi
echo ""