Snapshot a mariadb database for quick restore

snapshotdb.sh

#!/bin/sh

if [ $# -ne 2 ]; then
  echo "usage: $0 [database] [snapshot-name]"
  exit 1
fi

DIR=~/databases/backup
DB=$1
SNAPSHOT=$2
DEST="$DIR/$DB/$SNAPSHOT"

rm -fr "$DEST"
mkdir -p "$DEST"
/opt/homebrew/bin/mariabackup --rsync --backup --databases=$1 --target-dir="$DEST"

PRE="$DIR/$DB/$SNAPSHOT-pre.sql"
POST="$DIR/$DB/$SNAPSHOT-post.sql"
> $PRE
> $POST
echo "/* DROP, CREATE, and USE added by $0 */" >> $PRE
echo "DROP DATABASE $DB; CREATE DATABASE $DB; USE $DB;" >> $PRE
echo >> $PRE
mysqldump -d $DB >> $PRE
echo >> $PRE
echo "/* Discard tablespaces added by $0 */" >> $PRE
echo "/* Import tablespaces added by $0 */" >> $POST
echo "USE $DB;" >> $POST

for table in `mysql -A -E -e "select table_name FROM information_schema.tables where table_schema = '$DB';" | grep table_name | sed -e 's/table_name://g'`; do
  echo "ALTER TABLE $table DISCARD TABLESPACE;" >> $PRE
  echo "ALTER TABLE $table IMPORT TABLESPACE;" >> $POST
done

exit 0