This Bash script automates the process of transferring a MySQL/MariaDB database from a remote host to a other machine (local or other server).

migrate-db.sh

#!/bin/bash

set -e

# Function to check for errors and exit
is_err() {
    if [ $? -ne 0 ]; then
        echo "ERROR: $1"
        exit 1
    fi
}

# Prompt user for remote database details
read -rp  "Remote host: "                        REMOTE_HOST
read -rp  "Remote user: "                        REMOTE_USER
read -rp  "Remote database name: "               REMOTE_DB_NAME
read -rp  "Remote database root user: "          REMOTE_DB_ROOT_USER
read -rsp "Remote database root user password: " REMOTE_DB_ROOT_PASSWORD
echo
echo
REMOTE_DUMP_PATH="/tmp/${REMOTE_DB_NAME}.sql"

# Prompt user for local database details
read -rp  "Local database name: "               LOCAL_DB_NAME
read -rp  "Local database non-root user: "      LOCAL_DB_NONROOT_USER
read -rp  "Local database root user: "          LOCAL_DB_ROOT_USER
read -rsp "Local database root user password: " LOCAL_DB_ROOT_PASSWORD
echo
echo
LOCAL_DUMP_PATH="$REMOTE_DUMP_PATH"

# Wait user for confirmation to proceed
(
    read -rp "Are you sure you want to proceed? [y/N] " -n 1 -r
    echo
    echo
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
        echo "Process aborted."
        exit 1
    fi
)

(
    echo "Creating dump of the database '$REMOTE_DB_NAME' on the remote host..."
    ssh "$REMOTE_USER"@"$REMOTE_HOST" "export MYSQL_PWD=$REMOTE_DB_ROOT_PASSWORD; mysqldump -u $REMOTE_DB_ROOT_USER --single-transaction --databases $REMOTE_DB_NAME > $REMOTE_DUMP_PATH"
    is_err "Failed to create dump on the remote host."
)

(
    echo "Downloading the dump to the local machine..."
    scp "$REMOTE_USER"@"$REMOTE_HOST":"$REMOTE_DUMP_PATH" "$LOCAL_DUMP_PATH"
    is_err "Failed to download the dump."
)

(
    echo "Applying the dump to the local '$LOCAL_DB_NAME' database..."
    export MYSQL_PWD=$LOCAL_DB_ROOT_PASSWORD;
    cat <(echo "SET FOREIGN_KEY_CHECKS=0;") "$LOCAL_DUMP_PATH" | mysql -u "$LOCAL_DB_ROOT_USER"
    is_err "Failed to apply the dump to the local database."
)

(
    echo "Updating privileges for the '$LOCAL_DB_NONROOT_USER' user..."
    export MYSQL_PWD=$LOCAL_DB_ROOT_PASSWORD;
    mysql -u "$LOCAL_DB_ROOT_USER" -e "GRANT ALL PRIVILEGES ON $LOCAL_DB_NAME.* TO '$LOCAL_DB_NONROOT_USER'@'%'; FLUSH PRIVILEGES;"
    is_err "Failed to update privileges for the '$LOCAL_DB_NONROOT_USER' user."
)

(
    echo "Removing the dump from the remote and local host..."
    ssh "$REMOTE_USER"@"$REMOTE_HOST" "rm $REMOTE_DUMP_PATH"
    is_err "Failed to remove the dump from the remote host."
    rm "$LOCAL_DUMP_PATH"
    is_err "Failed to remove the dump from the local host."
)

echo "Process completed successfully."