Template for contab job about dumping mysql database for django applications

midnight_db_dump.sh

#!/usr/bin/env bash
# -*- encoding: utf-8 -*-

echo "[$(date -I)] dumping db"

PROJECT_ROOT=<project_root>
DOT_ENV=$PROJECT_ROOT/<project_name>/.env

#: Ensure in existance of file with db connection settings
if [ -f $DOT_ENV ]; then
  echo "[+] '.env' exists"
else
  echo "[-] '.env' exists"
  exit 1
fi

#: Extract database connection infromation from '.env' file
DB_NAME=$(     awk -F '=' '/DB_NAME/ {print $2}'     $DOT_ENV | sed -e "s/'//g" ) 
DB_USER=$(     awk -F '=' '/DB_USER/ {print $2}'     $DOT_ENV | sed -e "s/'//g" )
DB_HOST=$(     awk -F '=' '/DB_HOST/ {print $2}'     $DOT_ENV | sed -e "s/'//g" )
DB_PASSWORD=$( awk -F '=' '/DB_PASSWORD/ {print $2}' $DOT_ENV | sed -e "s/'//g" )

if $( mysql -u $DB_USER -p$DB_PASSWORD -h $DB_HOST -e"quit" 2>/dev/null ); then
  echo "[+] db connection established with following credentials:
    name: $DB_NAME 
    user: $DB_USER 
    pswd: $DB_PASSWORD 
    host: $DB_HOST"
else
  echo "[-] db connection established. Verify credentials."
  exit 1
fi

#: Ensure db existence
#: Make array from whitespace delimeted string of databases
DATABASES_LIST=(
  $( mysql -u $DB_USER -p$DB_PASSWORD -h $DB_HOST -e"SHOW DATABASES" 2>/dev/null )
)
#echo "${DATABASES_LIST[@]}"
#: Perform chech
if [[ " ${DATABASES_LIST[@]} " =~ " ${DB_NAME} " ]] ; then
  echo "[+] db '$DB_NAME' exists"
else
  echo "[-] db '$DB_NAME' exists"
  exit 1
fi

#: Create directory for dump files
DUMP_ROOT=<dump_root>
if $( mkdir -p $DUMP_ROOT 2>/dev/null ); then 
  echo "[+] '$DUMP_ROOT' exists"
else
  echo "[-] '$DUMP_ROOT' exists"
  exit 1
fi
  
#: Make database dump
DUMP_SQL_PATH=$DUMP_ROOT/dump-$(date -I).sql 
dump_result=$( 
  mysqldump -u $DB_USER -p$DB_PASSWORD -h $DB_HOST $DB_NAME > $DUMP_SQL_PATH \
    2>/dev/null 
)
if $dump_result; then
  echo "[+] '$DUMP_SQL_PATH' created"
else
  echo "[-] '$DUMP_SQL_PATH' created"
  exit 1
fi

#: Create test database
DB_NAME_TEST="${DB_NAME}_dump_test"
create_test_db_result=$(
  mysql -u $DB_USER -p$DB_PASSWORD -h $DB_HOST -e"CREATE DATABASE $DB_NAME_TEST" 2>/dev/null 
)
if $create_test_db_result; then
  echo "[+] db '$DB_NAME_TEST' created"
else
  echo "[-] db '$DB_NAME_TEST' created"
  exit 1
fi

#: Load dump to test database
dump_loading_result=$( 
  mysql -u $DB_USER -p$DB_PASSWORD -h $DB_HOST $DB_NAME_TEST < $DUMP_SQL_PATH \
    2>/dev/null 
)
if $dump_loading_result; then
  echo "[+] '$DUMP_SQL_PATH' loaded to '$DB_NAME_TEST'"
else
  echo "[-] '$DUMP_SQL_PATH' loaded to '$DB_NAME_TEST'"
  exit 1
fi

#: Retrieve and compare tables list from both databases
#: Get an array from mysql ouput
DB_TABLES_SOURCE=($( 
  mysql -u $DB_USER -p$DB_PASSWORD -h $DB_HOST $DB_NAME -e"SHOW TABLES;" \
    2>/dev/null
))
#: Slice first element as it just hello string describing commmand
DB_TABLES_SOURCE=("${DB_TABLES_SOURCE[@]:1}")
#: Convert array back to string for string comparision
DB_TABLES_SOURCE="${DB_TABLES_SOURCE[@]}"

DB_TABLES_TEST=($( 
  mysql -u $DB_USER -p$DB_PASSWORD -h $DB_HOST $DB_NAME_TEST -e"SHOW TABLES;" \
    2>/dev/null
))
DB_TABLES_TEST=("${DB_TABLES_TEST[@]:1}")
DB_TABLES_TEST="${DB_TABLES_TEST[@]}"

if [ "$DB_TABLES_SOURCE" == "$DB_TABLES_TEST" ]; then
  echo "[+] dump file verified"
else
  echo "[-] dump file corrupted"
fi

#: Drop test database
drop_test_db_result=$(
  mysql -u $DB_USER -p$DB_PASSWORD -h $DB_HOST -e"DROP DATABASE $DB_NAME_TEST;" 2>/dev/null 
)
if $drop_test_db_result; then
  echo "[+] db '$DB_NAME_TEST' deleted"
else
  echo "[-] db '$DB_NAME_TEST' deleted"
  exit 1
fi