mysql-dropuser-and-db

#!/bin/bash

NO_ARGS=0
USAGE="`basename $0` -u username [-h hostname] [-m root-pw]"

ESC_SEQ="\x1b["
COL_RESET=$ESC_SEQ"39;49;00m"
COL_RED=$ESC_SEQ"31;01m"
COL_GREEN=$ESC_SEQ"32;01m"
COL_YELLOW=$ESC_SEQ"33;01m"
COL_BLUE=$ESC_SEQ"34;01m"
COL_MAGENTA=$ESC_SEQ"35;01m"
COL_CYAN=$ESC_SEQ"36;01m"

# hello rob

if [ $# -eq "$NO_ARGS" ]  # Script invoked with no command-line args?
then
    echo $USAGE
    cat <<EOF

Connects to the mysql host specified as root and drops the user how matches the
specified host (or localhost if no host is scpecified.  It then drops any 
database that matches the username.  The DB connection will be made as root 
using the environment variable MYSQL_ROOT_PASSWORD.  If the environment variable
is not set it will prompt for a password on the command line.

-m Can be used to pass the root password in
EOF
    exit 1
fi

while getopts "u:h:m:" options; do
    case $options in
        u)  user="$OPTARG";;
        m)  MYSQL_ROOT_PASSWORD="$OPTARG";;
        h)  host="$OPTARG";;
    esac
done

if [ -z $user ]
then
    echo $USAGE
    exit 1
fi

if [ -z $host ]
then
    host=localhost
fi

if [ -z $MYSQL_ROOT_PASSWORD ]
then
    if [ -f /etc/mysql/mysecret ]; then
        mysqlroot=`cat /etc/mysql/mysecret`
    else
        echo -e $COL_CYAN"Enter the root password for your MySQL instance:"$COL_RESET
        stty -echo
        read mysqlroot
        stty echo
    fi
else
    mysqlroot=$MYSQL_ROOT_PASSWORD
fi

# echo -e "\nuser=$user\npass=$pass\nhost=$host\nname=$name\nroot=$mysqlroot\n"

# check connectivity
dbcheck=`mysql -u root -p$mysqlroot -h $host -e "show databases;"`
if [ $? != "0" ]
then
    echo -e $COL_RED"Cannot connect to database as root"$COL_RESET
    exit 1
fi

mysql -u root -p$mysqlroot -h $host -s -e "GRANT USAGE ON *.* TO '$user'@'$host';"
mysql -u root -p$mysqlroot -h $host -s -e "DROP USER '$user'@'$host';"
if [ $? != "0" ]
then
    echo -e $COL_RED"Failed to drop user"$COL_RESET
    exit 5
else
    echo -e $COL_GREEN"User dropped"$COL_RESET
fi

mysql -u root -p$mysqlroot -h $host -s -e "DROP DATABASE IF EXISTS \`$user\` ;"
if [ $? != "0" ]
then
    echo -e $COL_RED"Failed to drop database"$COL_RESET
    exit 5
else
    echo -e $COL_GREEN"Database dropped (or never existed)"$COL_RESET
fi