Instaload a MySQL database. 1) have a fresh db in reserve, 2) rename fresh db to your current db, 3) reload fresh db in background

instaload.sh

#!/bin/bash

set -euo pipefail
#set -x # for debugging

if [ -z "${2:-}" ]; then
  echo "Usage: $0 PASSWORD DB [reload] [NOTIFY-EMAIL]"
  echo "Check your bash history?"
  exit 1
fi

DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

export MYSQL_PWD="$1"
MYSQL="mysql --skip-auto-rehash --user=topscore --host=localhost --port=3306"

ROOT="ts_nightly"

OLD=$($MYSQL -N -e "SHOW DATABASES LIKE '$ROOT%'" | head -n 1)
NEW="$2"

if [ -z "$OLD" ]; then
  echo "No databases to load"
  exit
fi

$DIR/renamedb.sh "$MYSQL_PWD" "$OLD" "$NEW"

RELOAD="${3:-}"
if [ "$RELOAD" = "reload" ]; then
  echo "Triggering db reload in background"
  if [ -n "${4:-}" ]; then
    NOTIFY="--notify=$4"
  else
    NOTIFY=""
  fi

  "$HOME"/app/symfony fs:snapshot s3 --local-db="rand$RANDOM" --rename-after-load="$OLD" $NOTIFY > /dev/null 2>&1 &
fi

echo "Instaload done"

renamedb.sh

#!/bin/bash

set -euo pipefail

if [ -z "${3:-}" ]; then
  echo "Usage: $0 PASSWORD OLD NEW"
  exit 1
fi

export MYSQL_PWD="$1"
MYSQL="mysql --skip-auto-rehash --user=topscore --host=localhost --port=3306"

OLD="$2"
NEW="$3"

$MYSQL -e "CREATE DATABASE IF NOT EXISTS $NEW"

echo "Dropping existing tables"
DROP=$($MYSQL -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='$NEW'")
for table in $DROP; do
  $MYSQL -e "SET FOREIGN_KEY_CHECKS = 0; DROP TABLE $NEW.$table";
done;

echo "Copying tables"
TABLES=$($MYSQL -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='$OLD'")
for table in $TABLES; do
  $MYSQL -e "SET FOREIGN_KEY_CHECKS = 0; RENAME TABLE $OLD.$table TO $NEW.$table";
done;

$MYSQL -e "DROP DATABASE IF EXISTS $OLD"

echo "Rename done"