mysql-db-setup

#!/bin/bash

# Define the path to your .env file
ENV_PATH="./demo-website/backend/.env"

# Load variables from the specific .env location
if [ -f "$ENV_PATH" ]; then
    echo -e "\e[34mLoading environment variables from $ENV_PATH...\e[0m"
    # This reads the file, ignores comments, and exports variables
    export $(grep -v '^#' "$ENV_PATH" | xargs -d '\n')
else
    echo -e "\e[31mError: .env file not found at $ENV_PATH\e[0m"
    echo "Please ensure you have created the .env file in demo-website/backend/"
    exit 1
fi

# installing mysql
echo -e "\e[34mUpdating apt & system and installing MYSQL\e[0m"
sudo apt update && sudo apt upgrade -y
sudo apt install mysql-server -y
# start running mysql service
sudo systemctl start mysql
sudo systemctl enable --now mysql

# updating MySQL to listen on all interfaces
echo -e "\e[34mConfiguring MySQL to allow connections from both webservers...\e[0m"
mysql_config_file="/etc/mysql/mysql.conf.d/mysqld.cnf"
# Checking if MYSQL is currently set to listen to localhost; if so, swapping it to 0.0.0.0
if grep -q "bind-address\s*=\s*127.0.0.1" "$mysql_config_file"; then
    sudo sed -i 's/bind-address\s*=\s*127.0.0.1/bind-address = 0.0.0.0/' "$mysql_config_file"
    echo -e "✅ \e[32mMYSQL bind-address updated to 0.0.0.0\e[0m"
    sudo systemctl restart mysql
else
    echo -e "ℹ️ \e[1mBind-address already configured or not found.\e[0m"
fi
sudo systemctl status mysql

# waiting for MYSQL to initialize, before writing data into it
# prevents errors where data was not created since MYSQL wasn't ready yet
# 'mysqladmin ping' checks if MYSQL is ready
echo -e "\e[34mWaiting for MySQL to be ready...\e[0m"
until sudo mysqladmin ping >/dev/null 2>&1; do
  echo -n "."
  sleep 1
done
echo "MySQL is up!"

# creating nginx-facts webapp data
# using'192.168.10.%' to restrict access of the DB from the 2 VMs in the internal LAN
sudo mysql <<EOF
CREATE DATABASE IF NOT EXISTS ${DB_NAME};
CREATE USER IF NOT EXISTS '${DB_USER}'@'192.168.10.%' IDENTIFIED BY '${DB_PASSWORD}';
GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO '${DB_USER}'@'192.168.10.%';
FLUSH PRIVILEGES;
USE ${DB_NAME};
CREATE TABLE IF NOT EXISTS facts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    detail TEXT NOT NULL
);
INSERT INTO facts (title, detail) VALUES 
('The C10k Problem', 'NGINX was born to handle 10,000+ concurrent connections.'),
('The Swiss Army Knife', 'It acts as a load balancer, content cache, and reverse proxy.'),
('High Performance', 'Event-driven architecture keeps RAM usage extremely low.'),
('SSL Termination', 'NGINX handles HTTPS encryption so your app doesn''t have to.');
EOF
echo -e "✅ \e[32mMYSQL successfully installed MYSQL. The database is up and running\e[0m"