Automate MySQL database & user creation

Nov

14

2012

Continuing from the Apache virtualhost article, this is another script that automates the creation of a MySQL database and a user with all permissions to it.

In the last post, a script for automating the creation of Apache virtual hosts was covered. It was a long post, mainly because virtual hosts is a complicated concept, both in configuration and execution. Quite sensitive to small bugs too, since it can potentially bring all your hosts down. This script is much smaller and quite straight forward, since it just invoking MySQL commands. That also means that it works under all Linux flavors, but it has only been tested with MySQL 5.1.

Without further ado, here's the script :

#!/bin/bash
###################################################################
### CONFIGURATION AREA - ALL PATHS ARE ABSOLUTE, NO TRAILING /  ###
###################################################################

# Define the username and password of a user that can create dbs 
# and grant permissions.
user=an-admin-user
password=admin-user-password

###################################################################
### WORK AREA - DONT EDIT BELOW THIS LINE UNLESS YOU CAN FIX IT ###
###################################################################

die () {
    echo >&2 "$@"
    exit 1
}

# Check if we have an argument, exit if not
[ "$#" -eq 1 ] || die "Need a db name as argument (only)"

# Create database
mysql -u$user -p$password -e "CREATE DATABASE $1"
mysql -u$user -p$password -e "GRANT ALL PRIVILEGES ON $1.* TO $1_user@localhost IDENTIFIED BY '$1.Pass!'"


# Let the user know something good just happened
echo "Database creation of $1 complete."
echo "Access with username $1_user and password $1.Pass!"

As with the previous example, invocation is meant to be simple --

# newdb mysitedb

This will (as you are informed at the end of execution)

create a new database named with the string you provided (in the example, mysitedb).
create a salted username (in the example, mysitedb_user) and password (mysitedb.Pass!), with full access to the newly created database (without GRANT permission) from localhost

Some salting is done to the username and password, to keep them from being simple repetitions of the string. This way you can easily derive your usernames and passwords without resorting to keeping notes, while being relatively safe (to be honest, if a hacker ever got to the point where MySQL credentials from localhost mattered, then you have tons of problems elsewhere!)

NOTE: While in most aspects localhost and 127.0.0.1 are considered equivalents, there is a significant difference in MySQL -- localhost implies a socket connection, whereas 127.0.0.1 a connection via TCP. The (quite common) situation where this is important is if you're using a tunneled connection (such as SQLyog or Navicat or any other tool, using SSH), localhost will fail to authenticate. If you want to offer this as a feature, you need to edit the above script to permit connections to 127.0.0.1 instead of localhost.

It is recommended that you change the salting parameters -- something as simple as changing the case of the concatenated strings in the above example should do.

So there you have it -- a simple one liner approach to database and user creation. Enjoy!

Image from here