Download latest database backup archive (*.sql file) from a remote server

readme.md

Getting started with DB backup downloader:

Setup:

1. Download the latest version of the download utils:

curl --remote-name-all https://gist.githubusercontent.com/JonathanWillitts/727df8d991c194f20419be4de4328c22/raw/{download_latest_db.sh,env_variables.conf}

2. Edit env_variables.conf, and specify values for $REMOTE_USER, $REMOTE_HOST and $REMOTE_BACKUP_ROOT (the path to the backup directory root, excluding the database directory name).

Usage:

To download the latest backup of a database:

1. Run:

bash download_latest_db.sh <replace_with_db_dir_name>

2. Script will search remote server and display latest version of specified database (if prompted, enter ssh key passphrase to allow connection) .

3. Script will download latest database backup archive it finds (if prompted, enter ssh key passphrase a second time to allow download) .

download_latest_db.sh

#!/bin/bash
################################################################################
# Searches remote directory for newest *.sql file and downloads it to current 
# directory
# 
# Usage: bash download_latest_db.sh <db_dir_name>
#
# Note: requires values for $REMOTE_USER, $REMOTE_HOST and $REMOTE_BACKUP_ROOT
#       to be set in corresponding env_variables.conf file
################################################################################

# On error, fail early
set -e

# Pull in required env variables
source env_variables.conf

# Function to present error message to console and exit with error code 1
function error_and_exit {
    local error_message=$1
    echo -e "Error: $error_message\nExiting ..."
    exit 1
}

# Ensure env variables set
if [[ -z $REMOTE_USER || -z $REMOTE_HOST || -z $REMOTE_BACKUP_ROOT ]]
then
    error_and_exit "Missing environmental variable.\nPlease check/set env_variables.conf and re-run."
fi

# Ensure DB directory name has been specified 
db_dir_name=$1
if [[ -z $db_dir_name ]]
then
    error_and_exit "Database name argument is missing. 
Please re-run script with an argument containing the database directory name \
(e.g. bash download_latest_db.sh db_dir_name)
"
fi

# Determine latest (*.sql) file for specified database directory name
remote_dir=$REMOTE_BACKUP_ROOT/$db_dir_name
echo "Searching '$remote_dir' on '$REMOTE_USER@$REMOTE_HOST' ..."
latest_remote_file=$(
    ssh -qx $REMOTE_USER@$REMOTE_HOST \
        "find '$remote_dir' -maxdepth 1 -name '*.sql' -print0 \
        | xargs --no-run-if-empty --null ls -1 -t --almost-all \
        | head -n 1"
)

# User info
if [[ -z $latest_remote_file ]]
then
    error_and_exit "No matching files found."
else
    echo "
================================================================================
Latest available archive is: '$latest_remote_file'

Downloading... (if prompted, re-enter key passphrase to continue)
--------------------------------------------------------------------------------
"

    # Download/sync the file
    rsync --progress --archive --compress --verbose --sparse \
        $REMOTE_USER@$REMOTE_HOST:$latest_remote_file .
fi

env_variables.conf

# Define remote username here
export REMOTE_USER=

# Define remote host address here
export REMOTE_HOST=

# Define the path to the backup directory root 
# (excluding the database directory name) here
export REMOTE_BACKUP_ROOT=