Multitenant migration

TKA

Hi,

We have been using flyway for the past couple of years. It is working great!
However; our company has grown a lot in size over the last year, thus resulting in steep increase of databases.
We now have arond 1700 databases to migrate with lots more to come.

Our current flyway strategy involves fetching each database and migrating them one by one, resulting in a migration time of 1.5 hour. When doing this parallel we come down to around 50 minutes, however we would like to have it down to around 15 minutes.

First of all; Is this possible? Second of all; if so, is it possible from within the flyway-cli or do we need to optimize our script?

An example of our script is below:

#!/bin/bash

###############################
# Usage: sh ./_deployscripts/script.sh ENV db_user db_password [database_name]
#
# - ENV -> PROD or ACC or DEV
# - db_user - user which has access for all db's
# - db_password - corresponding db password
# - if database_name is provided, only for one database is the update performed, else for ALL applicable databases the action is performed
###############################

# Exit immediately if a command exits with a non-zero status
set -e

# Configuration
EXCLUDED_DBS=(
    "example_db_1"
    "example_db_2"
    "example_db_3"
)

MAX_WORKERS=20   # Maximum number of parallel Flyway processes
FORCE_UPDATE=0
SHOW_DEBUG_INFO=0
START_TIME=$(date +%s)

# Directories and Paths
CURRENT_WORKING_DIR=$(pwd)
CURRENT_FLYWAY_SQL_DIR="./_documents/flyway-sql/"

MYSQL_HOST="localhost"
MYSQL_PORT=3306
FLYWAY_PATH="/usr/local/bin/flyway"
FLYWAY_GET_PARAMS="?useSSL=false&serverTimezone=Europe/Amsterdam"
TEST_DB="example_test_db"
FLYWAY_ALLOW_OUT_OF_ORDER=""

# Environment Configuration
case "$1" in
    PROD)
        MYSQL_HOST="localhost"
        ;;
    DEV)
        MYSQL_HOST="host.docker.internal"
        MYSQL_PORT=3307
        TEST_DB="test_db"
        SHOW_DEBUG_INFO=1
        FORCE_UPDATE=1
        ;;
    *)
        echo "NO VALID environment: $1"
        exit 1
        ;;
esac

# Flyway Options
OUTPUT_FLYWAY_OPTIONS="-q $FLYWAY_ALLOW_OUT_OF_ORDER"
if [[ $SHOW_DEBUG_INFO -eq 1 ]]; then
    # Uncomment the next line for full debug
    # OUTPUT_FLYWAY_OPTIONS="-X $FLYWAY_ALLOW_OUT_OF_ORDER"
    OUTPUT_FLYWAY_OPTIONS="$FLYWAY_ALLOW_OUT_OF_ORDER"
fi

# Logging and Debug Info
echo "0. Prerequisites"
echo "----------------------------------------"
echo "Maximum number of parallel processes: $MAX_WORKERS"
echo "Environment: $1"
echo "MySQL host: $MYSQL_HOST"
echo "MySQL port: $MYSQL_PORT"
echo "MySQL user: $2"
echo "Force update: $FORCE_UPDATE"
echo "Show debug info: $SHOW_DEBUG_INFO (output options: $OUTPUT_FLYWAY_OPTIONS)"
echo "CURRENT_FLYWAY_SQL_DIR: $CURRENT_FLYWAY_SQL_DIR"
echo "Flyway version:"
$FLYWAY_PATH -v
echo "----------------------------------------"

# Function to perform Flyway migration for a single database
perform_flyway_migration() {
    local db_name="$1"
    local db_user="$2"
    local db_password="$3"
    local log_file="./flyway/flyway_${db_name}.log"

    echo "[$(date +'%Y-%m-%dT%H:%M:%S%z')]: Starting Flyway migration for DB: $db_name"

    if $FLYWAY_PATH $OUTPUT_FLYWAY_OPTIONS \
        -skipCheckForUpdate \
        -user="$db_user" \
        -password="$db_password" \
        -url="jdbc:mysql://$MYSQL_HOST:$MYSQL_PORT/$db_name$FLYWAY_GET_PARAMS" \
        -locations="filesystem:$CURRENT_FLYWAY_SQL_DIR" migrate > "$log_file" 2>&1; then
        echo "[$(date +'%Y-%m-%dT%H:%M:%S%z')]: Flyway migration SUCCEEDED for DB: $db_name."
    else
        echo "[$(date +'%Y-%m-%dT%H:%M:%S%z')]: Flyway migration FAILED for DB: $db_name. Check $log_file for details."
        echo "$db_name" >> failed_migrations.txt
    fi
}

# Function to check if a database is excluded
is_excluded() {
    local db="$1"
    for excl in "${EXCLUDED_DBS[@]}"; do
        if [[ "$excl" == "$db" ]]; then
            return 0
        fi
    done
    return 1
}

echo "1. Get DB's and perform Flyway updates"
echo "----------------------------------------"

# Ensure the log directory exists
mkdir -p ./flyway

if [[ -n "$4" ]]; then
    # Single database specified
    DB_NAME="$4"
    echo "Single DB specified: $DB_NAME"

                perform_flyway_migration "$DB_NAME" "$2" "$3"

else
    # Perform Flyway on the test DB first
    echo "Performing Flyway migration on test DB: $TEST_DB"
    testflyway_output=$($FLYWAY_PATH $FLYWAY_ALLOW_OUT_OF_ORDER \
        -user="$2" \
        -password="$3" \
        -url="jdbc:mysql://$MYSQL_HOST:$MYSQL_PORT/$TEST_DB$FLYWAY_GET_PARAMS" \
        -locations="filesystem:$CURRENT_FLYWAY_SQL_DIR" migrate 2>&1)

    echo "Test DB Flyway result:"
    echo "$testflyway_output"

    if [[ "$testflyway_output" == *"ERROR:"* ]] || [[ "$testflyway_output" == *"Error Code:"* ]]; then
        echo "Flyway encountered an error on the test DB. Exiting."
        exit 1
    fi

    nomigration="No migration necessary."
    PERFORM_UPDATE=1

    if [[ "$FORCE_UPDATE" -eq 0 && "$testflyway_output" =~ "$nomigration" ]]; then
        echo "No migration necessary on test DB. Skipping updates."
        PERFORM_UPDATE=0
    elif [[ "$FORCE_UPDATE" -eq 1 ]]; then
        echo "Force update enabled. Proceeding with migrations."
    fi

    if [[ "$PERFORM_UPDATE" -eq 1 ]]; then
        echo "2. Starting Flyway migrations on all applicable databases."

        DATABASES=$(mysql -u "$2" -h "$MYSQL_HOST" -p"$3" -P"$MYSQL_PORT" -e 'SHOW DATABASES;' | awk '{ print $1 }' | grep '^example_')

        # Filter out excluded databases
        APPLICABLE_DATABASES=()
        for DB in $DATABASES; do
            if is_excluded "$DB"; then
                echo "Skipping excluded DB: $DB"
                continue
            fi
            APPLICABLE_DATABASES+=("$DB")
        done

        # Export the function and variables for GNU Parallel
        export -f perform_flyway_migration
        export FLYWAY_PATH
        export OUTPUT_FLYWAY_OPTIONS
        export MYSQL_HOST
        export MYSQL_PORT

        # Run migrations in parallel using GNU Parallel
        printf "%s\n" "${APPLICABLE_DATABASES[@]}" | parallel -j "$MAX_WORKERS" perform_flyway_migration {} "$2" "$3"

        echo "All Flyway migrations completed."
    fi
fi

END_TIME=$(date +%s)
TOTAL_DURATION=$((END_TIME - START_TIME))

# Verification
echo "----------------------------------------"
echo "TOTAL DURATION IN SECONDS: $TOTAL_DURATION"

# Count the number of processed databases
if [[ -n "$4" ]]; then
    counter=1
else
    counter=${#APPLICABLE_DATABASES[@]}
fi
echo "VERIFICATION: Number of databases processed: $counter"

if [[ -f failed_migrations.txt ]]; then
    echo "The following databases failed to migrate:"
    cat failed_migrations.txt
    rm failed_migrations.txt
    exit 1
else
    echo "All migrations completed successfully."
    exit 0
fi

AlistairW

Hi there - glad that Flyway has been working for you so well. There isn't currently a way to get Flyway to target multiple DBs in a single call, you have to do as you have done and run multiple instances of Flyway so if you want to bring down the time more then increase the number of parallel instances.
That said, you will need adequate resources (memory/CPU) on the devices running Flyway -  given that 1 thread took 90 minutes to perform the whole task then having 20 threads take 50 minutes suggests you've got a bottleneck somewhere.
I suppose you *could* write your own java application that uses the Flyway libraries to do specific parallel migrations more efficiently than the CLI .... but .... that is a lot of work when you could use AWS/Azure/GCP to spin up the instances you need on demand.