bash Cookbook

Setting Up a Database with MySQL

Problem

You want to create and initialize several databases using MySQL. You want them all to be initialized using the same SQL commands. Each database needs its own name, but each database will have the same contents, at least at initialization. You may need to do this setup over and over, as in the case where these databases are used as part of a test suite that needs to be reset when tests are rerun.

Solution

A simple bash script can help with this administrative task:

#!/usr/bin/env bash
# cookbook filename: dbiniter
#
# initialize databases from a standard file
# creating databases as needed.

DBLIST=$(mysql -e "SHOW DATABASES;" | tail -n +2)
select DB in $DBLIST "new..."
do
    if [[ $DB == "new..." ]]
    then
        printf "%b" "name for new db: "
        read DB rest
        echo creating new database $DB
        mysql -e "CREATE DATABASE IF NOT EXISTS $DB;"
    fi

    if [ "$DB" ]
    then
        echo Initializing database: $DB
        mysql $DB < ourInit.sql
    fi
done

Discussion

The tail+2 is added to remove the heading from the list of databases (see Skipping a Header in a File).

The select creates the menus showing the existing databases. We added the literal "new…" as an additional choice (see Selecting from a List of Options and Creating Simple Menus).

When the user wants to create a new database, we prompt for and read a new name, but we use two fields on the read command as a bit of error handling. If the user types more than one name on the line, we only use the first name—it gets put into the variable ...