Re-create a database in MySQL

mysql_drop_create.sh

#!/usr/bin/env bash

# Author: Maciej Bilas (@maciejb)

# Based on http://david-burger.blogspot.com/2010/01/truncate-all-tables-in-mysql-database_31.html
# https://gist.github.com/290596

usage() {
  echo "\
Usage: $0 -u username -d database [-h host] [-o port)] \\
          [[-p] | [-P password]]
       host defaults to localhost
       port defaults to 3306
       -p will cause mysql to prompt for the password, good
       -P password will show the password in your ps list, evil" >&2
  exit 1
}

username=
passwdprompt=no
host=localhost
port=3306
database=
password=

while getopts ":u:pPd:h:" name; do
  case $name in
    u) username=$OPTARG;;
    p) passwdprompt="yes";;
    P) password=$OPTARG;;
    d) database=$OPTARG;;
    h) host=$OPTARG;;
    ?) usage;;
  esac
done

if [ -z "${username}" ] || [ -z "${database}" ]; then
  usage
fi

basecmd="mysql -u ${username} -h ${host} -P ${port}"

if [ "${passwdprompt}" = "yes" ]; then
  basecmd="${basecmd} -p"
elif [ -n "${password}" ]; then
  basecmd="${basecmd} -p${password}"
fi

cmd="${basecmd} -e \"DROP SCHEMA IF EXISTS \\\`${database}\\\` ;\""
echo "Executing ${cmd}..."
eval ${cmd}
cmd="${basecmd} -e \"CREATE SCHEMA \\\`${database}\\\` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;\""
echo "Executing ${cmd}..."
eval ${cmd}