Fix the character set to be utf8mb4 and utf8mb4_unicode_ci.

Bash - New MySQL DB & User

#!/bin/bash

# create random password
PASSWD="$(openssl rand -base64 30)"
PASSWD_STAGE="$(openssl rand -base64 30)"

echo -e "\033[36mEnter DB name (domain name):\033[0m"
read db_name

echo -e "\033[36mEnter Username:\033[0m"
read user_name

# replace "-" with "_" for database username
MAINDB=${db_name//[^a-zA-Z0-9]/_}
USERNAME=${user_name//[^a-zA-Z0-9]/_}

# If /root/.my.cnf exists then it won't ask for root password
if [ -f /root/.my.cnf ]; then

    mysql -e "CREATE DATABASE ${MAINDB} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
    mysql -e "CREATE DATABASE stage_${MAINDB} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
    mysql -e "CREATE USER ${USERNAME}@localhost IDENTIFIED BY '${PASSWD}';"
    mysql -e "CREATE USER stage_${USERNAME}@localhost IDENTIFIED BY '${PASSWD_STAGE}';"
    mysql -e "GRANT ALL PRIVILEGES ON \`${MAINDB//_/\\_}\`.* TO '${USERNAME}'@'localhost';"
    mysql -e "GRANT ALL PRIVILEGES ON \`stage_${MAINDB//_/\\_}\`.* TO 'stage_${USERNAME}'@'localhost';"
    mysql -e "FLUSH PRIVILEGES;"

# If /root/.my.cnf does not exist then it will ask for root password
else
    echo "Please enter root user MySQL password!"
    echo "Note: password will be hidden when typing"
    read -sp rootpasswd
    mysql -uroot -p${rootpasswd} -e "CREATE DATABASE ${MAINDB} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
    mysql -uroot -p${rootpasswd} -e "CREATE DATABASE stage_${MAINDB} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
    mysql -uroot -p${rootpasswd} -e "CREATE USER ${USERNAME}@localhost IDENTIFIED BY '${PASSWD}';"
    mysql -uroot -p${rootpasswd} -e "CREATE USER stage_${USERNAME}@localhost IDENTIFIED BY '${PASSWD_STAGE}';"
    mysql -uroot -p${rootpasswd} -e "GRANT ALL PRIVILEGES ON \`${MAINDB//_/\\_}\`.* TO '${USERNAME}'@'localhost';"
    mysql -uroot -p${rootpasswd} -e "GRANT ALL PRIVILEGES ON \`stage_${MAINDB//_/\\_}\`.* TO 'stage_${USERNAME}'@'localhost';"
    mysql -uroot -p${rootpasswd} -e "FLUSH PRIVILEGES;"
fi

echo ""
echo -e "\033[93mCredentials are as follows:"
echo -e "---------------------------"
echo -e "\033[92mDB Name:\033[0m ${MAINDB}"
echo -e "\033[92mDB Name (stage):\033[0m stage_${MAINDB}"
echo -e "\033[92mUsername:\033[0m ${USERNAME}"
echo -e "\033[92mPassword:\033[0m ${PASSWD}"
echo -e "\033[92mUsername (stage):\033[0m stage_${USERNAME}"
echo -e "\033[92mPassword (stage):\033[0m ${PASSWD_STAGE}"
echo -e "\033[92mHost:\033[0m localhost"