Automatic MySQL database setup from .env file contents

README.md

About

This script will automatically create a new MySQL database for you by reading your MySQL database name, host name, and user credentials from your .env file. This allows you to keep your database details safe, in a single location. Ideal for deployment and security.

Installation

1. Download setup_mysql.sh to your project root directory 2. You can modify setup_mysql.sh to set the following: - ENV_FILE (the relative path to your .env file -- e.g. ENV_FILE="./.env.local") - DEFAULT_MYSQL_HOST (the host name of your MySQL server -- e.g. "localhost") - DEFAULT_MYSQL_ADMIN_USERNAME (the administrator username of your mysql server -- e.g. "root" (note: this is typically NOT the MySQL username of your app). This should be no longer than 16 characters. Some versions of MySQL allow for longer lengths. You can modify the MAX_MYSQL_USERNAME_LENGTH variable to change this length. - DEFAULT_MYSQL_ADMIN_PASSWORD (the administrator password of your mysql admin user -- e.g. "secret") - Note: You can optionally remove these lines to be prompted to enter them during the process instead. 3. Ensure you have the following entries in your .env file (modify as needed). - DB_HOST=localhost - DB_DATABASE=my_db_name - DB_USERNAME=db_app_user - DB_PASSWORD=my_db_password 4. Ensure the setup_mysql.sh file has executable permissions. You can set this by running: sudo chmod a+x setup_mysql.sh 5. Execute the script by running: sudo /bin/bash setup_mysql.sh 6. If you use git for version control, ensure an entry for .env is stored in your .gitignore file. This will keep your usernames and passwords separate from your repository.

Issues

If you discover any issues with this script, please leave a comment to let me know. I've tested it on Laravel Homestead (version 0.3.0) with bash running MySQL version 5.6.27.

Revisions

- Fixed an issue where it was checking the default mysql variables instead of the new values

setup_mysql.sh

#!/usr/bin/env bash

# Variables to set.
# You can set remove these lines (or set them to empty strings) and you will then be prompted to enter new values during the process.
# **** NOTE: The DB admin credentials are not the same as the DB user credentials provided in the .env file (unless defined the same). ****
ENV_FILE=".env"
DEFAULT_MYSQL_HOST="localhost"
DEFAULT_MYSQL_ADMIN_USERNAME="root"
DEFAULT_MYSQL_ADMIN_PASSWORD="secret"

######## DO NOT EDIT BELOW THIS LINE (unless you know what you're doing :) ##########
MAX_MYSQL_DATABASE_NAME_LENGTH=64
MAX_MYSQL_USERNAME_LENGTH=16

declare -A REQUIRED_ENV_VARS=( [DB_HOST]="" [DB_DATABASE]="" [DB_USERNAME]="" [DB_PASSWORD]="" )

# Exit script on first error
set -e

# Pretty colors!
RED='\033[0;31m'
GREEN='\033[0;32m'
NC='\033[0m' # No Color

echo "Prepeating database..."

# Verify .env file exists
confirm_env_file_exists() {
    while [ ! -f "${ENV_FILE}" ]; do
        if [ -z "${ENV_FILE}" ]; then
            printf "${RED}Error:${NC} Environment file not specified.\n"
        else
            printf "${RED}Error:${NC} Environment file \"$ENV_FILE\" does not exist.\n"
        fi
        echo -n "Enter the environment file name (e.g. \".env\"): "
        read ENV_FILE
        echo ""
    done
}

if [[ -z "$ENV_FILE" || ! -f "${ENV_FILE}" ]]; then
    confirm_env_file_exists
fi

# Verify MySQL admin credentials.
# The user will be re-prompted to enter correct details if the default credentials fail, or if any subsequent attempt fails.
MYSQL_HOST="${DEFAULT_MYSQL_HOST}"
MYSQL_ADMIN_USERNAME="${DEFAULT_MYSQL_ADMIN_USERNAME}"
MYSQL_ADMIN_PASSWORD="${DEFAULT_MYSQL_ADMIN_PASSWORD}"

get_mysql_credentials() {
    MYSQL_HOST="${DEFAULT_MYSQL_HOST}"
    MYSQL_ADMIN_USERNAME="${DEFAULT_MYSQL_ADMIN_USERNAME}"
    MYSQL_ADMIN_PASSWORD="${DEFAULT_MYSQL_ADMIN_PASSWORD}"

    # Host name
    if [ -z "${DEFAULT_MYSQL_HOST}" ]; then
        while [ -z "${MYSQL_HOST}" ]; do
            echo -n "Enter the MySQL host name (e.g. localhost): "
            read MYSQL_HOST
        done
    else
        echo -n "Enter the MySQL host name (press [ENTER] for \"${DEFAULT_MYSQL_HOST}\"): "
        read MYSQL_HOST
        if [ -z "${MYSQL_HOST}" ]; then
            MYSQL_HOST="$DEFAULT_MYSQL_HOST"
        fi
    fi

    # Admin username
    if [ -z "${DEFAULT_MYSQL_ADMIN_USERNAME}" ]; then
        while [ -z "${MYSQL_ADMIN_USERNAME}" ]; do
            echo -n "Enter the MySQL admin username (e.g. root): "
            read MYSQL_ADMIN_USERNAME
        done
    else
        echo -n "Enter the MySQL admin username (press [ENTER] for \"${DEFAULT_MYSQL_ADMIN_USERNAME}\"): "
        read MYSQL_ADMIN_USERNAME
        if [ -z "${MYSQL_ADMIN_USERNAME}" ]; then
            MYSQL_ADMIN_USERNAME="$DEFAULT_MYSQL_ADMIN_USERNAME"
        fi
    fi

    # Admin password
    if [ -z "${DEFAULT_MYSQL_ADMIN_PASSWORD}" ]; then
        while [ -z "${MYSQL_ADMIN_PASSWORD}" ]; do
            echo -n "Enter the MySQL admin password (e.g. secret): "
            read MYSQL_ADMIN_PASSWORD
        done
    else
        echo -n "Enter the MySQL admin password (press [ENTER] for \"${DEFAULT_MYSQL_ADMIN_PASSWORD}\"): "
        read MYSQL_ADMIN_PASSWORD
        if [ -z "${MYSQL_ADMIN_PASSWORD}" ]; then
            MYSQL_ADMIN_PASSWORD="$DEFAULT_MYSQL_ADMIN_PASSWORD"
        fi
    fi
}

if [[ -z "${MYSQL_HOST}" || -z "${MYSQL_ADMIN_USERNAME}" || -z "${MYSQL_ADMIN_PASSWORD}" ]]; then
    get_mysql_credentials
fi

while ! mysql -h "${MYSQL_HOST}" -u "${MYSQL_ADMIN_USERNAME}" -p"${MYSQL_ADMIN_PASSWORD}" -e ";" >/dev/null 2>&1; do
    echo ""
    echo "*** Your default MySQL admin credentials failed verification (or unable to connect to the default host name). ***"
    echo ""

    get_mysql_credentials
done

ERRORS_EXIST=false

# Verify that the required entries in the .env file exist (and are not set to empty strings)
for i in "${!REQUIRED_ENV_VARS[@]}"
do
    :
    if ! grep -qoEx "${i}=.+" "${ENV_FILE}"; then
        printf "${RED}Error:${NC} Missing required line from your .env file: \"$i={your_value}\"\n"
        ERRORS_EXIST=true
    else
        REQUIRED_ENV_VARS[$i]=`grep -oEx "${i}=.+" "${ENV_FILE}" | sed -n -e "s/^.*${i}=//p"`
    fi
done

# Validate env variable values
for i in "${!REQUIRED_ENV_VARS[@]}"
do
    :
    if [ ${i} == "DB_DATABASE" ]; then
        #echo "Checking DB_DATABASE length..."
        if [ ${#REQUIRED_ENV_VARS[$i]} -gt ${MAX_MYSQL_DATABASE_NAME_LENGTH} ]; then
            printf "${RED}Error:${NC} The length of DB_DATABASE set in file \"${ENV_FILE}\" must not exceed ${MAX_MYSQL_DATABASE_NAME_LENGTH} characters in length.\n"
            ERRORS_EXIST=true
        fi
    elif [ ${i} == "DB_USERNAME" ]; then
        #echo "Checking DB_USERNAME length..."
        if [ ${#REQUIRED_ENV_VARS[$i]} -gt ${MAX_MYSQL_USERNAME_LENGTH} ]; then
            printf "${RED}Error:${NC} The length of DB_USERNAME set in file \"${ENV_FILE}\" must not exceed ${MAX_MYSQL_USERNAME_LENGTH} characters in length.\n"
            ERRORS_EXIST=true
        fi
    fi
done

if [ ${ERRORS_EXIST} == true ]; then
    exit 0;
fi

# Create the database
#todo: if database already exists, prompt for delete & continue
mysql -h "${MYSQL_HOST}" -u "${MYSQL_ADMIN_USERNAME}" -p${MYSQL_ADMIN_PASSWORD} -e "DROP DATABASE IF EXISTS \`${REQUIRED_ENV_VARS[DB_DATABASE]}\`;" >/dev/null 2>&1;
mysql -h "${MYSQL_HOST}" -u "${MYSQL_ADMIN_USERNAME}" -p${MYSQL_ADMIN_PASSWORD} -e "CREATE DATABASE IF NOT EXISTS \`${REQUIRED_ENV_VARS[DB_DATABASE]}\`;" >/dev/null 2>&1
printf "Created MySQL database ${GREEN}${REQUIRED_ENV_VARS[DB_DATABASE]}${NC}.\n"

# Create the MySQL user
mysql -h "${MYSQL_HOST}" -u "${MYSQL_ADMIN_USERNAME}" -p${MYSQL_ADMIN_PASSWORD} -e "GRANT USAGE ON *.* TO '${REQUIRED_ENV_VARS[DB_USERNAME]}'@'${REQUIRED_ENV_VARS[DB_HOST]}' IDENTIFIED BY '${REQUIRED_ENV_VARS[DB_PASSWORD]}';" >/dev/null 2>&1;
mysql -h "${MYSQL_HOST}" -u "${MYSQL_ADMIN_USERNAME}" -p${MYSQL_ADMIN_PASSWORD} -e "DROP USER '${REQUIRED_ENV_VARS[DB_USERNAME]}'@'${REQUIRED_ENV_VARS[DB_HOST]}';" >/dev/null 2>&1;
mysql -h "${MYSQL_HOST}" -u "${MYSQL_ADMIN_USERNAME}" -p${MYSQL_ADMIN_PASSWORD} -e "GRANT ALL PRIVILEGES ON ${REQUIRED_ENV_VARS[DB_DATABASE]}.* TO '${REQUIRED_ENV_VARS[DB_USERNAME]}'@'${REQUIRED_ENV_VARS[DB_HOST]}' IDENTIFIED BY '${REQUIRED_ENV_VARS[DB_PASSWORD]}';" >/dev/null 2>&1
mysql -h "${MYSQL_HOST}" -u "${MYSQL_ADMIN_USERNAME}" -p${MYSQL_ADMIN_PASSWORD} -e "FLUSH PRIVILEGES;" >/dev/null 2>&1
printf "Created MySQL user ${GREEN}${REQUIRED_ENV_VARS[DB_USERNAME]}${NC} with password ${GREEN}${REQUIRED_ENV_VARS[DB_PASSWORD]}${NC}.\n"

# Verify MySQL database was created (mysql exit code 0)
mysql -h "${MYSQL_HOST}" -u "${MYSQL_ADMIN_USERNAME}" -p${MYSQL_ADMIN_PASSWORD} -e "use \`${REQUIRED_ENV_VARS[DB_DATABASE]}\`;" >/dev/null 2>&1
if [ $? == "0" ]; then
    printf "${GREEN}Database verified!${NC} -- Verified database ${GREEN}${REQUIRED_ENV_VARS[DB_DATABASE]}${NC} exists on host ${GREEN}${REQUIRED_ENV_VARS[DB_HOST]}${NC}\n"
else
    printf "${RED}Warning: Database could not be verified!${NC} -- Unable to verify that database ${RED}${REQUIRED_ENV_VARS[DB_DATABASE]}${NC} was created on host ${RED}${REQUIRED_ENV_VARS[DB_HOST]}${NC}\n"
fi

# Verify MySQL user was created
MYSQL_USER_EXISTS=false
{
    while read User; do
        if [[ "${REQUIRED_ENV_VARS[DB_USERNAME]}" == "$User" ]]; then
            MYSQL_USER_EXISTS=true
            break
        fi
    done < <(mysql -h "${MYSQL_HOST}" -u "${MYSQL_ADMIN_USERNAME}" -p${MYSQL_ADMIN_PASSWORD} -B -N -e "use \`${REQUIRED_ENV_VARS[DB_DATABASE]}\`; SELECT User FROM mysql.user;")
} &> /dev/null

if [ ${MYSQL_USER_EXISTS} == true ]; then
    printf "${GREEN}User verified!${NC} -- Verified user ${GREEN}${REQUIRED_ENV_VARS[DB_USERNAME]}${NC} was created on database ${GREEN}${REQUIRED_ENV_VARS[DB_DATABASE]}${NC} on host ${GREEN}${REQUIRED_ENV_VARS[DB_HOST]}${NC}\n"
else
    printf "${RED}Warning: User could not be verified!${NC} -- Unable to verify if ${RED}${REQUIRED_ENV_VARS[DB_USERNAME]}${NC} was created on database ${RED}${REQUIRED_ENV_VARS[DB_DATABASE]}${NC} on host ${RED}${REQUIRED_ENV_VARS[DB_HOST]}${NC}\n"
fi