DB backup remote to local

db_remote_to_local.sh

#!/bin/bash

set -euo pipefail  # Exit on error, undefined variables, and pipe failures

####
# REMOTE MySQL DATABASE LOCAL IMPORT SCRIPT 
# 
# This script copies a remote database to a local machine.
# It connects to a remote server, dumps the database, and imports it locally.
#
# Usage:
#   ./db_remote_to_local.sh [[email protected]] [dbUser] [dbName]
#
# The script will prompt for:
#   - Remote database password (hidden input)
#   - Local database name, username, and password (hidden input)
#   - Local socket path if needed (e.g. /tmp/mysql_3306.sock)
# 
# Note: Uses --no-tablespaces flag to avoid PROCESS privilege requirements.
# If you need tablespaces, ensure the remote user has PROCESS privileges.
#
# Requirements:
#   - SSH access to the remote server
#   - `mysqldump` installed on the remote server
#   - `mysql` & `rsync` installed locally
#

WORKING_DIR="$HOME/workingDB/"
KEEP_REMOTE_FILE=false

# Colours for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Colour

# Print functions
print_error() {
  printf "${RED}ERROR: %s${NC}\n" "$1" >&2
}

print_success() {
  printf "${GREEN}✓ %s${NC}\n" "$1"
}

print_info() {
  printf "${YELLOW}→ %s${NC}\n" "$1"
}

# Create a temporary directory
create_temp_dir() {
  print_info "Creating temp directory..."
  local dir=$1
  if [ -d "$dir" ]; then
    print_error "Temp directory already exists, cleaning up..."
    rm -rf "$dir"
  fi
  
  if ! mkdir -p "$dir"; then
    print_error "Unable to create temp directory"
    exit 1
  fi
  print_success "Temp directory created"
}

# Delete a directory
delete_temp_dir() {
  print_info "Deleting temp directory..."
  local dir=$1
  if [ -d "$dir" ]; then
    rm -rf "$dir"
    print_success "Temp directory deleted"
  fi
}

# Delete remote file
delete_remote_file() {
  print_info "Deleting remote file..."
  local file=$1
  if ssh "${REMOTE_HOST}" "rm -f ${file}" 2>/dev/null; then
    print_success "Remote file deleted"
  else
    print_error "Failed to delete remote file (may not exist)"
  fi
}

# Validate database name (alphanumeric, underscores, hyphens only)
validate_db_name() {
  local name=$1
  if [[ ! "$name" =~ ^[a-zA-Z0-9_-]+$ ]]; then
    print_error "Invalid database name. Use only letters, numbers, underscores, and hyphens."
    return 1
  fi
  return 0
}

# Validate database username (alphanumeric, underscores, hyphens, dots)
validate_db_user() {
  local user=$1
  if [[ ! "$user" =~ ^[a-zA-Z0-9._-]+$ ]]; then
    print_error "Invalid database username. Use only letters, numbers, underscores, hyphens, and dots."
    return 1
  fi
  return 0
}

# Validate remote host (user@hostname format)
validate_remote_host() {
  local host=$1
  if [[ ! "$host" =~ ^[a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+$ ]]; then
    print_error "Invalid remote host format. Use: user@hostname"
    return 1
  fi
  return 0
}

# Cleanup function for trap
cleanup() {
  local exit_code=$?
  if [ $exit_code -ne 0 ]; then
    print_error "Script failed, cleaning up..."
  fi
  
  # Clear passwords from memory
  unset REMOTE_DB_PASSWORD
  unset LOCAL_DB_PASSWORD
  
  if [ -n "${DB_FILENAME:-}" ] && [ "$KEEP_REMOTE_FILE" = false ]; then
    delete_remote_file "$DB_FILENAME" || true
  fi
  
  if [ -n "${WORKING_DIR:-}" ]; then
    delete_temp_dir "$WORKING_DIR" || true
  fi
  
  exit $exit_code
}

trap cleanup EXIT INT TERM

# Get remote host
if [ -z "${1:-}" ]; then
  read -p "Enter the remote host (e.g. [email protected]): " REMOTE_HOST
else
  REMOTE_HOST=$1
fi

# Validate remote host
if ! validate_remote_host "$REMOTE_HOST"; then
  exit 1
fi

# Test SSH connection
print_info "Testing connection to ${REMOTE_HOST}..."
if ! ssh -o ConnectTimeout=10 -T "${REMOTE_HOST}" true 2>/dev/null; then
  print_error "Unable to connect to remote server"
  exit 1
fi
print_success "Connected to remote server"

# Get database credentials
if [ -z "${2:-}" ]; then
  read -p "Enter the database user for the remote server: " REMOTE_DB_USER
else
  REMOTE_DB_USER=$2
fi

# Validate database user
if ! validate_db_user "$REMOTE_DB_USER"; then
  exit 1
fi

if [ -z "${3:-}" ]; then
  read -p "Enter the database name for the remote server: " REMOTE_DB_NAME
else
  REMOTE_DB_NAME=$3
fi

# Validate database name
if ! validate_db_name "$REMOTE_DB_NAME"; then
  exit 1
fi

# Get remote database password
read -sp "Enter the password for ${REMOTE_DB_USER}@${REMOTE_HOST}: " REMOTE_DB_PASSWORD
echo ""  # New line after hidden input

if [ -z "$REMOTE_DB_PASSWORD" ]; then
  print_error "Password cannot be empty"
  exit 1
fi

# Generate filename
dateNow=$(date +%Y-%m-%d-%H%M%S)
DB_FILENAME="db--${REMOTE_DB_NAME}--${dateNow}.sql"

# Create database export on remote host
print_info "Creating database export on remote server..."
print_info "This may take a while for large databases..."

# Capture stderr to show actual error messages
DUMP_ERROR=$(mktemp)
if ! ssh "${REMOTE_HOST}" "mysqldump --add-drop-table --single-transaction --skip-lock-tables --no-tablespaces -u'${REMOTE_DB_USER}' -p'${REMOTE_DB_PASSWORD}' '${REMOTE_DB_NAME}' > ${DB_FILENAME}" 2>"$DUMP_ERROR"; then
  echo ""
  print_error "Failed to create remote database export"
  
  # Show the actual error if we captured it
  if [ -s "$DUMP_ERROR" ]; then
    echo ""
    echo "Error details:"
    cat "$DUMP_ERROR"
    echo ""
  fi
  
  rm -f "$DUMP_ERROR"
  exit 1
fi
rm -f "$DUMP_ERROR"
print_success "Database exported on remote server"

# Create local working directory
create_temp_dir "$WORKING_DIR"

# Download the export
print_info "Downloading database from remote host..."
if ! rsync -azP "$REMOTE_HOST:$DB_FILENAME" "$WORKING_DIR/$DB_FILENAME"; then
  print_error "Failed to download remote database export"
  exit 1
fi
print_success "Database downloaded"

# Check for local mysql
if ! command -v mysql >/dev/null 2>&1; then
  print_error "mysql command not found locally"
  exit 1
fi

# Get local database details
echo ""
read -p "Enter the name for the local database: " LOCAL_DB_NAME
validate_db_name "$LOCAL_DB_NAME" || exit 1

read -p "Enter the username for the local database: " LOCAL_DB_USER
validate_db_user "$LOCAL_DB_USER" || exit 1

read -sp "Enter the password for ${LOCAL_DB_USER} (local): " LOCAL_DB_PASSWORD
echo ""  # New line after hidden input

read -p "Enter the socket path (leave blank for default): " LOCAL_SOCKET
SOCKET_ARG=""
if [ -n "$LOCAL_SOCKET" ]; then
  if [ ! -S "$LOCAL_SOCKET" ]; then
    print_error "Socket file does not exist: $LOCAL_SOCKET"
    exit 1
  fi
  SOCKET_ARG="--socket=$LOCAL_SOCKET"
  print_info "Using socket: $LOCAL_SOCKET"
fi

# Confirmation prompt
echo ""
print_info "Ready to import:"
echo "  Remote: ${REMOTE_DB_NAME} from ${REMOTE_HOST}"
echo "  Local:  ${LOCAL_DB_NAME} as ${LOCAL_DB_USER}"
if [ -n "$SOCKET_ARG" ]; then
  echo "  Socket: ${LOCAL_SOCKET}"
fi
echo ""
read -p "Continue with import? (y/N): " -n 1 -r
echo ""

if [[ ! $REPLY =~ ^[Yy]$ ]]; then
  print_info "Import cancelled"
  exit 0
fi

# Import the database
print_info "Importing database..."

# Build mysql command with optional password
MYSQL_CMD="mysql $SOCKET_ARG -u'$LOCAL_DB_USER'"
if [ -n "$LOCAL_DB_PASSWORD" ]; then
  MYSQL_CMD="${MYSQL_CMD} -p'$LOCAL_DB_PASSWORD'"
fi
MYSQL_CMD="${MYSQL_CMD} '$LOCAL_DB_NAME'"

if ! eval "$MYSQL_CMD" < "$WORKING_DIR/$DB_FILENAME"; then
  print_error "Failed to import database"
  exit 1
fi
print_success "Database imported successfully!"

# Ask about keeping remote file
echo ""
read -p "Delete the remote database file? (Y/n): " -n 1 -r
echo ""
if [[ $REPLY =~ ^[Nn]$ ]]; then
  KEEP_REMOTE_FILE=true
  print_info "Remote file kept at: ${REMOTE_HOST}:${DB_FILENAME}"
fi

print_success "All done!"