Script to import backups from databases made in a remote server into a local database connection over ssh

db-importer

#!/bin/sh
set -ef -o pipefail

# Script to import backups from databases made in a remote server into a local database connection over ssh
# Designed to be used together with the mariadb-backup script (https://gist.github.com/Eihen/53be043c217009d5705da28e07a1b1f4)

# This program is free software. It comes without any warranty, to the extent permitted by applicable law.
# You can redistribute it and/or modify it under the terms of the Do What The Fuck You Want To Public License, Version 2,
# as published by Sam Hocevar. See http://www.wtfpl.net/ for more details.

# Default Remote server
# The connection configuration should be set in ~/.ssh/config
SSH_HOST=remotehost

# Default database connection
# The connection configuration should be set in ~/.my.cnf
DATABASE=dbname

while builtin getopts "h:g:b:is:edc" opt; do
    case ${opt} in
        \?) builtin echo '-h <host>: SSH host to use (defined in .ssh/config)'
            builtin echo '-g <group>: MySQL group to use (defined in my.cnf)'
            builtin echo '-b <path>: Backup local database'
            builtin echo '-i: Import to local database'
            builtin echo '-s <path>: Save backup to another location'
            builtin echo '-e: Use existing backup file if there is one'
            builtin echo '-d: Delete temp files after finishing'
            builtin echo '-c: Backup the current database of the remote server'
            builtin exit 1;;

        h)  SSH_HOST="${OPTARG}";;

        g)  DATABASE="${OPTARG}";;

        b)  if [ -d "$OPTARG" ]; then
                builtin echo "$OPTARG is not a valid directory."
                builtin exit 1
            fi
            BACKUP=$OPTARG;;

        i)  IMPORT=1;;

        s)  if [ -d "$OPTARG" ]; then
                builtin echo "$OPTARG is not a valid directory."
                builtin exit 1
            fi
            SAVE=$OPTARG;;

        e)  EXISTING=1;;

        d)  DELETE=1;;

        c)  CURRENT="/usr/bin/sudo /bin/systemctl start mariadb-backup;";;

        :)  builtin echo "Invalid option: $OPTARG requires an argument." 1>&2
            builtin exit 1;;
    esac
done

GROUP_ARG="--defaults-group-suffix=_${DATABASE}"

# Dump file
FILE="${DATABASE}.sql"
TEMP_PATH="/var/tmp/${DATABASE}.sql"


if [ "$BACKUP" ]; then
    builtin echo "Backupping database to ${BACKUP}/${FILE}"
    /usr/bin/mysqldump $GROUP_ARG > "${BACKUP}/${FILE}"
fi

if [ ! "${EXISTING}" ] || [ ! -r "${TEMP_PATH}" ]; then
    # Copy compressed database backup from server
    builtin echo "Copying backup from server"
    /usr/bin/ssh -t ${SSH_HOST} "${CURRENT}" \
        "/usr/bin/sudo /bin/cp /var/lib/mysql/dumps/latest/${FILE}.gz ${TEMP_PATH}.gz;" \
        '/usr/bin/sudo /bin/chown $(whoami):$(whoami) '"${TEMP_PATH}.gz;"
    /usr/bin/scp "${SSH_HOST}:${TEMP_PATH}.gz" "${TEMP_PATH}.gz"
    /usr/bin/ssh -t ${SSH_HOST} "/bin/rm ${TEMP_PATH}.gz"

    # Extract compressed database backup
    builtin echo "Extracting backup..."
    /usr/bin/gzip -f -d "${TEMP_PATH}.gz"
fi

if [ "$SAVE" ]; then
    # If a valid directory is supplied as first argument
    builtin echo "Copying backup file to ${SAVE}"
    /usr/bin/cp "${TEMP_PATH}" "${SAVE}"
fi

if [ "$IMPORT" ]; then
    # Import backup file on local database
    builtin echo "Importing backup on local database"
    /usr/bin/mysql $GROUP_ARG --execute="DROP DATABASE IF EXISTS $DATABASE; CREATE DATABASE $DATABASE;"
    /usr/bin/mysql $GROUP_ARG < "${TEMP_PATH}"
fi


if [ "$DELETE" ]; then
    /usr/rm "${TEMP_PATH}"
fi