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.

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

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 $DB while the rest of the input is put into $rest and ignored. (We could add an error check to see if $rest is null.)

Whether created anew or chosen from the list of extant databases, if the $DB variable is not empty, it will invoke mysql one more time to feed it the set of SQL statements that we’ve put into the file ourInit.sql as our standardized initialization sequence.

If you’re going to use a script like this, you might need to add parameters to your mysql command, such as -u and -p to prompt for username and password. It will depend on how your database and its permissions are configured or whether you have a file named .my.cnf with your MySQL defaults.

We could also have added an error check after the creation of the new database to see if it succeeded; if it did not succeed, we could unset DB thereby bypassing the initialization. However, as many a math textbook has said, “we leave that as an exercise for the reader.”