path: root/localaddmysqldb

#!/bin/bash
#
# /usr/local/sbin/localaddmysqldb
# Copyright 2001-2006 Jonas Smedegaard <dr@jones.dk>
#
# $Id: localaddmysqldb,v 1.11 2006-07-16 12:34:00 jonas Exp $
#
# Add/update MySQL database
#
# This script makes use of the Debian package wwwconfig-common if installed
#
# TODO: Make script (mostly) work as noon-root
# TODO: Add a --dry-run or --test option showing what would be done (how options are set, and a descriptive text about how they are used)
# TODO: Add option to reset access for all existing databases owned by user:
#   echo $alldb | egrep "^$dbuser(_|$)"
# TODO: Split in init-mysql-user and add-mysql-db
# TODO: Disallow same password for <user> and <user>-admin
# TODO: Only ask for password if it doesn't exist already
# TODO: Creating a new unix user and mysql user sets <user>-admin password equal to unix password
# TODO: Have database administrator default to root when asked for but input is blank
# TODO: Don't ask for admin password (or hint that it may not be needed) if /root/.my.cnf exists

set -e

prg=`basename $0`

TEMP=`getopt -o hu:U:D:Z:i::v::q::f::z:: --long help,user:,dbuser:,dbserver:,dballow:,info::,verbose::,debug::,query::,force::,ez:: -n "$prg" -- "$@"`

# Check for non-GNU getopt
if [ $? != 0 ] ; then echo "Terminating..." >&2 ; exit 1 ; fi

eval set -- "$TEMP"

# Defaults are evaluated at runtime in this order
# (If changing defaults, you might need to change these as well)
opts1="user dbserver dballow ez" # Options with self-contained defaults
opts2="dbuser home" # Options requiring $opts1 to resolve default
optsboolean="debug verbose info query force" # Do not touch these!

# Defaults
defaultuser="www-data"
defaultdbuser='$user'
defaultdbserver="localhost"
defaultdballow="localhost"
defaultinfo="on"
defaultverbose="off"
defaultdebug="off"
defaultquery="on"
defaultforce="off"
defaultez="off"

# Defaults not (yet) user configurable
defaulthome="/var/www"

# Override defaults from config file if available
if [ -e /etc/local/localaddmysqldb.conf ]; then
    . /etc/local/localaddmysqldb.conf
fi

# This needs to exist from before parsing options
debug="off"

function usage() {
    echo "Usage: $prg [OPTION]... DBNAME [DBNAME]..."
    echo "Add/update MySQL database"
    echo
    echo "Mandatory arguments to long options are mandatory for short options too."
    echo "  -h, --help               Show this help"
    echo "  -u, --user=UID           System user id (default: $defaultuser)"
    echo "  -U, --dbuser=UID         MySQL database user (default: $defaultdbuser)"
    echo "  -D, --dbserver=HOST      IP or host of MySQL server (default:"
    echo "                             $defaultdbserver)"
    echo "  -Z, --dballow=HOST       IP or host of application server (default:"
    echo "                             $defaultdballow)"
    echo "  -i, --info[=on|off]      Show info during installation (default: $defaultinfo)"
    echo "  -v, --verbose[=on|off]   Show details during installation (default: $defaultverbose)"
    echo "      --debug[=on|off]     Show debug during installation (default: $defaultdebug)"
    echo "  -q, --query[=on|off]     Ask for missing options (default: $defaultquery)"
    echo "  -f, --force[=on|off]     Replace existing installation (default: $defaultforce)"
    echo "  -z, --ez=[=on|off]       Setup as eZ site (default: $defaultez)"
    echo
    echo "Each DBNAME can contain only alphanumerics."
    echo
    echo "MySQL password for \$dbuser can be stored in \$HOME/.my.cnf of \$user."
}

function setopts () {
    for opt in $@; do
        defaultvalue="`eval echo \$\{default$opt\}`"
        value="`eval echo \$\{$opt:-$defaultvalue\}`"
        if [ "$value" = '[[ASK]]' ]; then
            if [ -n "$query" ]; then
                echo -n "Enter value for $opt: "
                read value
                echo
            else
                echo "ERROR: value for $opt requested, but not running interactively!"
                exit 1
            fi
        fi
        if [ -z "$value" ]; then
            echo "ERROR: Value of \"$opt\" is empty!"
            exit 1
        fi
        eval $opt=\"$value\"
        if [ "$debug" = "1" -o "$debug" = "on" ]; then
            echo "----> $opt=\"$value\" (default: \"$defaultvalue\")"
        fi
    done
}

while true ; do
    case "$1" in
        -h|--help) usage; exit 0;;
        -u|--user) user="$2"; shift 2;;
        -U|--dbuser) dbuser="$2"; shift 2;;
        -D|--dbserver) dbserver="$2"; shift 2;;
        -Z|--dballow) dballow="$2"; shift 2;;
        -i|--info) case "$2" in ""|on) info="on";; *) info="off";; esac; shift 2;;
        -v|--verbose) case "$2" in ""|on) verbose="on";; *) verbose="off";; esac; shift 2;;
        --debug) case "$2" in ""|on) debug="on";; *) debug="off";; esac; shift 2;;
        -q|--query) case "$2" in ""|on) query="on";; *) query="off";; esac; shift 2;;
        -f|--force) case "$2" in ""|on) force="on";; *) force="off";; esac; shift 2;;
        -z|--ez) case "$2" in ""|on) ez="on";; *) ez="off";; esac; shift 2;;
        --) shift; break;;
        *) echo "Internal error!" ; exit 1 ;;
    esac
done

setopts $optsboolean
if [ "$debug" = "1" -o "$debug" = "on" ]; then
    verbose="on" # Debug implies verbose
fi
for boolean in $optsboolean; do
    case `eval echo \$\{$boolean\}` in
        on) eval $boolean="1";;
        off) eval $boolean="";;
        *) echo "ERROR: Value of boolean option \"$boolean\" must be \"on\" or \"off\"!"; exit 1;;
    esac
done
setopts $opts1
setopts $opts2
optdbserver="-h$dbserver"
if [ "$dbserver" = "localhost" ]; then
    optdbserver="" # Do not force TCP/IP access when connecting locally
fi

if [ -n "$verbose" ]; then
    echo "--> Figure out database users and passwords"
fi
if [ -z "$dbpass" ]; then
    if [ -f $home/.my.cnf ]; then
        dbpass=$(grep password $home/.my.cnf | awk -F= '{print $2}' | head -n 1 | sed 's/^ //g')
    fi
    if [ -z "$dbpass" ]; then
        if [ -n "$query" ]; then
            echo -n "Enter database password for MySQL user $dbuser: "
            read -s dbpass
            echo
        else
            echo "ERROR: Database password for MySQL $dbuser not found!"
            exit 1
        fi
    fi
fi
if [ -e /usr/share/wwwconfig-common/mysql-localadmpass.get -a "$dbserver" = "localhost" ] ; then
    status=""
    . /usr/share/wwwconfig-common/mysql-localadmpass.get
    if [ "$status" = "error" ] ; then
        if [ -n "$info" ]; then
            echo "--> $error"
        fi
    fi
fi
if [ -z "$dbadmin" ]; then
    if [ -n "$query" ]; then
        echo -n "Enter database administrator user (usually root): "
        read dbadmin
        echo
    else
        echo "ERROR: Database administrator user not resolved!"
        exit 1
    fi
fi
if [ -z "$dbadmpass" ]; then
    if [ -n "$query" ]; then
        echo -n "Enter database password for administrator $sbadmin: "
        read -s dbadmpass
        echo
    else
        echo "ERROR: Database password for administrator $dbadmin not found!"
        exit 1
    fi
fi
alldb=`mysql -u$dbadmin $optdbserver mysql -e "show databases;" | grep -v '^Database$'`

##############################################
# The rest of the script is dbname-specific... #
##############################################
for dbname do

if [ -n "$info" ]; then
    echo "Creating $dbname..."
fi

if [ -n "$verbose" ]; then
    echo "--> Check for existing installation"
fi
error=""
if echo $alldb | egrep -q "\<$dbname\>"; then
    if [ -n "$verbose" ]; then
        error="Database $dbname already exists."
    else
        error="Database already exists."
    fi
fi
if [ -n "$error" ]; then
    if [ -n "$query" ]; then
        echo "WARNING: $error"
        echo -n "Overwrite existing installation (y/N)?: "
        read ack
        echo
        case $ack in
            y|Y) force="1";;
            *) echo "Installation aborted!"; exit 1;;
        esac
    fi
    if [ -n "$force" ]; then
#       if [ -e /usr/share/wwwconfig-common/mysql-dropdb.sh ] ; then
#           status=""
#           . /usr/share/wwwconfig-common/mysql-dropdb.sh
#           if [ "$status" = "error" ] ; then
#               echo "$error"
#               exit 1
#           fi
#       else
            mysql -u$dbadmin $optdbserver -f -e "DROP DATABASE $dbname;"
#       fi
    else
        echo "ERROR: $error!"
        exit 1
    fi
fi

if [ -n "$verbose" -a "$dbhost" != "localhost" ]; then
    echo "--> Create database $dbname on $dbserver"
elif [ -n "$verbose" ]; then
    echo "--> Create database $dbname"
fi
if [ -e /usr/share/wwwconfig-common/mysql-createdb.sh ] ; then
    status=""
    . /usr/share/wwwconfig-common/mysql-createdb.sh
    if [ "$status" = "error" ] ; then
        echo "$error"
        exit 1
    fi
else
    # TODO: Purge existing database as the wwwconfig-common script does
    mysql -u$dbadmin $optdbserver -e "CREATE DATABASE $dbname;"
fi

if [ -n "$verbose" ]; then
    echo "--> Create/update database user $dbuser"
fi
if [ -e /usr/share/wwwconfig-common/mysql-createuser.sh ] ; then
    status=""
    . /usr/share/wwwconfig-common/mysql-createuser.sh
    if [ "$status" = "error" ] ; then
        echo "$error"
#       exit 1
    fi
    for h in `echo "localhost $dballow" | sort -u`; do
        mysql -u$dbadmin $optdbserver mysql -e "REVOKE CREATE,DROP ON $dbname.* FROM '$dbuser'@'$h';"
    done
else
    for h in `echo "localhost $dballow" | sort -u`; do
        if [ "$ez" = "on" ]; then # eZ Publish 2.2.x requires CREATE,DROP,INDEX for the search routines
            mysql -u$dbadmin $optdbserver mysql -e "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX ON $dbname.* TO '$dbuser'@'$h' IDENTIFIED BY '$dbpass';"
        else
            mysql -u$dbadmin $optdbserver mysql -e "GRANT SELECT,INSERT,UPDATE,DELETE ON $dbname.* TO '$dbuser'@'$h' IDENTIFIED BY '$dbpass';"
        fi
    done
fi

for h in `echo "localhost $dballow" | sort -u`; do
    mysql -u$dbadmin $optdbserver mysql -e "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER ON $dbname.* TO '$dbuser-admin'@'$h' IDENTIFIED BY '$dbpass';"
done

done