At my company we have lots of MySQL databases(usually one for each virtual host) and each database has its unique user. Adding a new database and a user with proper permissions can be quite tedious, that’s why I hacked up the following script which saves some of my precious time

create_1db_mysql_user

#!/bin/sh

if [ "$1" = "" ]; then
  read -p "Username: " USER
else
  USER="$1"
fi

if [ $USER = 'root' ]; then
  echo "User can't be root!"
  exit 1;
fi

if [ "$2" = "" ]; then
  read -p "DB:[$USER]" DB
  if [ -z $DB ]; then
    DB="$USER"
    echo "using $DB"
  fi
else
  DB="$2"
fi

if [ "$3" = "" ]; then
  HOST="localhost"
else
  HOST="$3"
fi

stty -echo
read -p "DB root password: " ROOT
echo

RAND_PASSWD=`dd if=/dev/urandom count=1 2> /dev/null | uuencode -m - | head -n 2 | tail -n 1 | cut -c-8`
read -p "DB user password:[$RAND_PASSWD]" PASSWD
echo
if [ -z $PASSWD ]; then
  PASSWD="$RAND_PASSWD"
  echo "using $PASSWD"
fi
stty echo

MYSQL="mysql -u root -p$ROOT"

$MYSQL -e"SHOW DATABASES" | fgrep "$DB" > /dev/null

if [ "$?" != 0 ]; then
  echo "No such database!"
  read -p "Create one?[yn]" ANSWER
  if [ "$ANSWER" != "n" ]; then
    echo "Creating database $DB..."
    $MYSQL -e"CREATE DATABASE $DB"
  fi
fi  

#$MYSQL -e"GRANT INSERT,SELECT,DELETE,UPDATE on `$DB`.* to '$USER'@'$HOST' IDENTIFIED BY '$PASSWD'"
$MYSQL -e"GRANT ALL PRIVILEGES on `$DB`.* to '$USER'@'$HOST' IDENTIFIED BY '$PASSWD'"
$MYSQL -e"FLUSH PRIVILEGES"

echo "done!"

Here’s the typical dialog with the script:

$ create_1db_mysql_user
Username: foobar
DB:[foobar]
using foobar
DB root password:
DB user password:[JG25HBUn]
using JG25HBUn
No such database!
Create one?[yn]y
Creating database foobar...
done!

First it asks for the user name and it assumes that the name of the user will be equal to the name of the database, however you can change it(in the example above I left it as is). Then it asks for the root password and asks for the user password as well(which if not specified is generated automatically). If the database doesn’t exist yet it asks if it should create one. And that’s it.

This script requires uuencode installed for automatic passwords generation. In Gentoo it can be installed as follows:

$ sudo emerge app-arch/sharutils

This entry was posted on Saturday, October 20th, 2007 at 10:21 am and is filed under mysql, *nix, administration. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.