Backup prod MySQL database, sync it down locally, then restore it in local dev

dbbackuprestore

#!/bin/bash

# bin/dbbackuprestore

# Configuration variables for remote server
REMOTE_SERVER=""
REMOTE_SERVER_USER=""  # Leave empty if using SSH config or key-based auth
REMOTE_SERVER_PORT=""  # Default SSH port
REMOTE_SSH_KEY=""  # Path to SSH key file (leave empty if using default)

# Configuration variables for remote database
REMOTE_DB_USER=""
REMOTE_DB_PASSWORD=""
REMOTE_DB_HOST=""
REMOTE_DB_PORT=""
REMOTE_DATABASE=""

# Configuration variables for local database
LOCAL_HOST=""
LOCAL_PORT=""
LOCAL_USER=""
LOCAL_PASSWORD=""
LOCAL_DATABASE=""

# Backup configuration
BACKUP_DIR="database/backups"
TIMESTAMP=$(date +"%Y%m%d-%H%M")
STRIP_GTID=true  # Set to false if you want to keep GUID information
FILENAME="${REMOTE_DATABASE}-${TIMESTAMP}$([ "$STRIP_GTID" = true ] && echo "-noguid" || echo "")"
KEEP_REMOTE_BACKUP=false  # Set to true to keep the backup file on the remote server

# Auto-detect MySQL client path
find_mysql_path() {
    # First try to get it directly
    if command -v mysql &> /dev/null; then
        echo "mysql"
        return
    fi
    
    # Try to use 'which' in interactive mode to catch aliases
    MYSQL_PATH=$(bash -ic "which mysql" 2>/dev/null || echo "")
    
    if [ -n "$MYSQL_PATH" ] && [ -x "$MYSQL_PATH" ]; then
        echo "$MYSQL_PATH"
        return
    fi
    
    # Check common locations
    for path in "/usr/bin/mysql" "/usr/local/bin/mysql" "/usr/local/mysql/bin/mysql" "/opt/homebrew/bin/mysql"; do
        if [ -x "$path" ]; then
            echo "$path"
            return
        fi
    done
    
    # Nothing found
    echo ""
}

# Find MySQL path
MYSQL_CLIENT=$(find_mysql_path)

# Construct SSH command with optional parameters
construct_ssh_cmd() {
    SSH_CMD="ssh"
    
    # Add identity file if specified
    if [ -n "$REMOTE_SSH_KEY" ]; then
        SSH_CMD="$SSH_CMD -i $REMOTE_SSH_KEY"
    fi
    
    # Add port if not default
    if [ "$REMOTE_SERVER_PORT" != "22" ]; then
        SSH_CMD="$SSH_CMD -p $REMOTE_SERVER_PORT"
    fi
    
    # Add username if specified
    if [ -n "$REMOTE_SERVER_USER" ]; then
        SSH_CMD="$SSH_CMD ${REMOTE_SERVER_USER}@${REMOTE_SERVER}"
    else
        SSH_CMD="$SSH_CMD ${REMOTE_SERVER}"
    fi
    
    echo "$SSH_CMD"
}

# Construct SCP command with optional parameters
construct_scp_cmd() {
    SCP_CMD="scp"
    
    # Add identity file if specified
    if [ -n "$REMOTE_SSH_KEY" ]; then
        SCP_CMD="$SCP_CMD -i $REMOTE_SSH_KEY"
    fi
    
    # Add port if not default
    if [ "$REMOTE_SERVER_PORT" != "22" ]; then
        SCP_CMD="$SCP_CMD -P $REMOTE_SERVER_PORT"
    fi
    
    # The actual source path is added by the caller
    if [ -n "$REMOTE_SERVER_USER" ]; then
        SCP_SOURCE="${REMOTE_SERVER_USER}@${REMOTE_SERVER}:${FILENAME}.sql"
    else
        SCP_SOURCE="${REMOTE_SERVER}:${FILENAME}.sql"
    fi
    
    echo "$SCP_CMD $SCP_SOURCE ${BACKUP_DIR}/"
}

# Check required commands
if ! command -v ssh &> /dev/null; then
    echo "Error: ssh command not found. Please install SSH client."
    exit 1
fi

if ! command -v scp &> /dev/null; then
    echo "Error: scp command not found. Please install SCP client."
    exit 1
fi

if [ -z "$MYSQL_CLIENT" ]; then
    echo "Error: MySQL client not found. Please install MySQL client."
    echo "The backup will be downloaded but not imported."
    SKIP_IMPORT=true
else
    echo "Using MySQL client: $MYSQL_CLIENT"
    SKIP_IMPORT=false
fi

# Create backups directory if it doesn't exist
mkdir -p $BACKUP_DIR

echo "Starting database backup and restore process..."

# Build SSH command
SSH_COMMAND=$(construct_ssh_cmd)

# Step 1: SSH to remote server and create a database dump
echo "Connecting to ${REMOTE_SERVER} server and creating database dump..."

# Set GTID purging option based on the STRIP_GTID variable
GTID_OPTION=$([ "$STRIP_GTID" = true ] && echo "--set-gtid-purged=OFF" || echo "")

$SSH_COMMAND "mysqldump -u ${REMOTE_DB_USER} -p${REMOTE_DB_PASSWORD} -h ${REMOTE_DB_HOST} -P ${REMOTE_DB_PORT} ${GTID_OPTION} ${REMOTE_DATABASE} > ${FILENAME}.sql"

if [ $? -ne 0 ]; then
    echo "Error: Database dump on remote server failed."
    exit 1
fi

# Step 2: Download the dump file from remote server to local machine
echo "Downloading database dump to local machine..."
# Build SCP command
SCP_COMMAND=$(construct_scp_cmd)
eval "$SCP_COMMAND"

if [ $? -ne 0 ]; then
    echo "Error: Failed to download database dump from remote server."
    exit 1
fi

# Step 3: Import the dump into local database
if [ "$SKIP_IMPORT" = false ]; then
    echo "Importing database dump to local database..."
    "$MYSQL_CLIENT" -h${LOCAL_HOST} -P${LOCAL_PORT} -u${LOCAL_USER} -p${LOCAL_PASSWORD} ${LOCAL_DATABASE} < ${BACKUP_DIR}/${FILENAME}.sql

    if [ $? -ne 0 ]; then
        echo "Error: Failed to import database dump to local database."
        echo "The backup file is available at: ${BACKUP_DIR}/${FILENAME}.sql"
        exit 1
    fi
else
    echo "Skipping database import as MySQL client was not found."
    echo "The backup file is available at: ${BACKUP_DIR}/${FILENAME}.sql"
    echo "You can manually import it using your preferred method."
fi

# Step 4: Clean up the remote file
if [ "$KEEP_REMOTE_BACKUP" = false ]; then
    # Step 4: Clean up the remote file
    echo "Cleaning up remote file..."
    $SSH_COMMAND "rm ${FILENAME}.sql"
else
    echo "Remote backup file kept at: ${REMOTE_SERVER}:${FILENAME}.sql"
fi

echo "Database backup and restore process completed!"
if [ "$SKIP_IMPORT" = false ]; then
    echo "Database successfully imported to local environment."
fi
echo "Backup saved to: ${BACKUP_DIR}/${FILENAME}.sql"

Good.