Automatic MySQL database setup for Roots projects from .env file contents.

README.md

About

This script is a fork which is tweaked to work with a typical Trellis + Bedrock project. It 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. This is useful in a development environment where you're using Trellis to provision and deploy to staging and production, but using something like Valet to serve your site locally.

Installation

1. Download setup_mysql.sh to your site/scripts/ 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 (if you've set these in trellis/group_vars/ or you used the trellis-cli package to create your project, you can use https://github.com/ItinerisLtd/enveigle by running npx @itinerisltd/enveigle in the trellis/ directory to create your .env file).
   • DB_HOST='localhost'
   • DB_NAME='my_db_name'
   • DB_USER='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: ./setup_mysql.sh

Revisions

• Updated variables to match those used in Bedrock .env file.
• Set location of .env file and updated readme.md to assume this script is placed in site/scripts/.
• Strip out single quotes in .env variables

setup_mysql.sh

#!/usr/bin/env bash -x

# 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="password"

######## 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_NAME]="" [DB_USER]="" [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 "Preparing 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}=//;s/^'//;s/'$//p"`
    fi
done

# Validate env variable values
for i in "${!REQUIRED_ENV_VARS[@]}"
do
    :
    if [ ${i} == "DB_NAME" ]; 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_USER" ]; then
        #echo "Checking DB_USER length..."
        if [ ${#REQUIRED_ENV_VARS[$i]} -gt ${MAX_MYSQL_USERNAME_LENGTH} ]; then
            printf "${RED}Error:${NC} The length of DB_USER 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_NAME]}\`;" >/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_NAME]}\`;" >/dev/null 2>&1
printf "Created MySQL database ${GREEN}${REQUIRED_ENV_VARS[DB_NAME]}${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_USER]}'@'${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_USER]}'@'${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_NAME]}.* TO '${REQUIRED_ENV_VARS[DB_USER]}'@'${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_USER]}${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_NAME]}\`;" >/dev/null 2>&1
if [ $? == "0" ]; then
    printf "${GREEN}Database verified!${NC} -- Verified database ${GREEN}${REQUIRED_ENV_VARS[DB_NAME]}${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_NAME]}${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_USER]}" == "$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_NAME]}\`; 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_USER]}${NC} was created on database ${GREEN}${REQUIRED_ENV_VARS[DB_NAME]}${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_USER]}${NC} was created on database ${RED}${REQUIRED_ENV_VARS[DB_NAME]}${NC} on host ${RED}${REQUIRED_ENV_VARS[DB_HOST]}${NC}\n"
fi