install-database.sh

#!/bin/bash

# SteerDock MySQL 8.4 Installation Script
set -e

echo "SteerDock MySQL 8.4 Installation"
echo "================================"

if [ "$EUID" -ne 0 ]; then
    echo "Please run as root (use sudo)"
    exit 1
fi

# Read environment variables
if [ -f ".env" ]; then
    # Read .env file variables
    while IFS='=' read -r key value || [ -n "$key" ]; do
        # Skip comments and empty lines
        [[ "$key" =~ ^#.*$ ]] && continue
        [[ -z "$key" ]] && continue
        
        # Remove carriage return from value if present
        value=$(echo "$value" | tr -d '\r')
        
        case "$key" in
            MYSQL_ROOT_PASSWORD) MYSQL_ROOT_PASSWORD="$value" ;;
            MYSQL_PASSWORD) MYSQL_PASSWORD="$value" ;;
            MYSQL_DATABASE) MYSQL_DATABASE="$value" ;;
            MYSQL_USER) MYSQL_USER="$value" ;;
            REDIS_PASSWORD) REDIS_PASSWORD="$value" ;;
        esac
    done < .env
else
    echo ".env file not found"
    exit 1
fi

# Set default values
MYSQL_DATABASE=${MYSQL_DATABASE:-steerdock}
MYSQL_USER=${MYSQL_USER:-steerdock}
MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD:-RootPass123!}
MYSQL_PASSWORD=${MYSQL_PASSWORD:-SteerDock123!}


echo "Using: DB=$MYSQL_DATABASE, User=$MYSQL_USER"

# Check and install MySQL
if ! command -v mysql &> /dev/null; then
    echo "MySQL not found. Installing MySQL 8.4..."
    
    # Try to install MySQL 8.4 Community Release (compatible with RHEL/CentOS 8/9/Linux)
    # First check distribution information
    if [ -f /etc/os-release ]; then
        . /etc/os-release
        OS_ID=$ID
        VERSION_ID=$(echo $VERSION_ID | cut -d. -f1)
    fi

    sudo dnf install -y mysql-server mysql 2>/dev/null || sudo yum install -y mysql-server mysql

    # Check again
    if ! command -v mysql &> /dev/null; then
        echo "Failed to install MySQL. Please install mysql-server manually."
        exit 1
    fi
    
    echo "MySQL installed successfully."
    
    # Start service
    sudo systemctl enable mysqld
    sudo systemctl start mysqld
    
    # Wait for log generation as initial password may change due to new installation
    sleep 5
fi

# Ensure service is started
if ! sudo systemctl is-active --quiet mysqld; then
    sudo systemctl start mysqld
    sleep 3
fi

# Get temporary password
echo "Getting temporary MySQL password..."
TEMP_PASSWORD=$(sudo grep 'temporary password' /var/log/mysqld.log 2>/dev/null | tail -1 | awk '{print $NF}')

if [ -z "$TEMP_PASSWORD" ]; then
    echo "No temporary password found. Checking if MySQL is running..."
    if ! sudo systemctl is-active --quiet mysqld; then
        echo "Starting MySQL..."
        sudo systemctl start mysqld
        sleep 5
        TEMP_PASSWORD=$(sudo grep 'temporary password' /var/log/mysqld.log 2>/dev/null | tail -1 | awk '{print $NF}')
    fi
fi

if [ -z "$TEMP_PASSWORD" ]; then
    echo "ERROR: Cannot find temporary password. Check /var/log/mysqld.log"
    echo "You may need to reset MySQL:"
    echo "sudo systemctl stop mysqld"
    echo "sudo rm -rf /var/lib/mysql/*"
    echo "sudo systemctl start mysqld"
    exit 1
fi

echo "Temporary password found."

# Step 1: Reset root password
echo "Step 1: Resetting root password..."

# In MySQL 8.4, after logging in with temporary password, the first statement must be ALTER USER
# And the new password must meet the default policy (contains uppercase, lowercase, numbers, special characters)
# So we first use a policy-compliant intermediate password
TEMP_STRONG_PASSWORD='TempPass@1234!'

echo "  [1/3] Setting intermediate password (policy-compliant)..."
if mysql -u root -p"$TEMP_PASSWORD" --connect-expired-password <<SQL
ALTER USER 'root'@'localhost' IDENTIFIED BY '${TEMP_STRONG_PASSWORD}';
FLUSH PRIVILEGES;
SQL
then
    echo "  Intermediate password set successfully"
    
    echo "  [2/3] Lowering password policy..."
    mysql -u root -p"${TEMP_STRONG_PASSWORD}" <<SQL
SET GLOBAL validate_password.policy=0;
SET GLOBAL validate_password.mixed_case_count=0;
SET GLOBAL validate_password.number_count=0;
SET GLOBAL validate_password.special_char_count=0;
SET GLOBAL validate_password.length=4;
FLUSH PRIVILEGES;
SQL
    
    echo "  [3/3] Setting final password..."
    mysql -u root -p"${TEMP_STRONG_PASSWORD}" <<SQL
ALTER USER 'root'@'localhost' IDENTIFIED BY '${MYSQL_ROOT_PASSWORD}';
FLUSH PRIVILEGES;
SQL
    
    echo "Root password reset successfully!"
else
    echo "Failed to reset root password with temporary password (this is expected if already reset)"
    echo "Trying alternative method (Safe Mode)..."
    
    # Try safe mode
    sudo systemctl stop mysqld
    
    # Find mysqld binary file
    MYSQLD_BIN=$(command -v mysqld)
    if [ -z "$MYSQLD_BIN" ]; then
        if [ -f "/usr/libexec/mysqld" ]; then
            MYSQLD_BIN="/usr/libexec/mysqld"
        elif [ -f "/usr/sbin/mysqld" ]; then
            MYSQLD_BIN="/usr/sbin/mysqld"
        fi
    fi
    
    if [ -z "$MYSQLD_BIN" ]; then
        echo "ERROR: Could not find mysqld binary to run in safe mode."
        exit 1
    fi
    
    # Manually start mysqld (bind to 127.0.0.1 via TCP to avoid socket issues)
    echo "Starting MySQL daemon ($MYSQLD_BIN) in background (Safe Mode TCP)..."
    # Ensure directory permissions
    sudo mkdir -p /var/run/mysqld
    sudo chown mysql:mysql /var/run/mysqld
    sudo mkdir -p /var/lib/mysql
    sudo chown mysql:mysql /var/lib/mysql
    
    # No longer use --skip-networking, but bind to localhost
    sudo -u mysql "$MYSQLD_BIN" --skip-grant-tables --bind-address=127.0.0.1 --port=3306 --user=mysql &
    PID=$!
    echo "Waiting for MySQL safe mode to start (PID: $PID)..."
    sleep 10
    
    # Use TCP connection
    mysql -h 127.0.0.1 -P 3306 -u root <<SQL
FLUSH PRIVILEGES;
-- Try to lower password policy requirements
SET GLOBAL validate_password.policy=0;
SET GLOBAL validate_password.mixed_case_count=0;
SET GLOBAL validate_password.number_count=0;
SET GLOBAL validate_password.special_char_count=0;
SET GLOBAL validate_password.length=0;

ALTER USER 'root'@'localhost' IDENTIFIED BY '${MYSQL_ROOT_PASSWORD}';
FLUSH PRIVILEGES;
SQL
    
    # Stop mysqld process
    if ps -p $PID > /dev/null; then
        echo "Stopping MySQL safe mode..."
        sudo kill $PID
        wait $PID 2>/dev/null || true
    else
         # If process not found, try pkill
         sudo pkill mysqld || true
    fi

    echo "Restarting MySQL service..."
    sudo systemctl start mysqld
    sleep 5
fi

# Step 2: Create database and user
echo "Step 2: Creating database and user..."
sleep 2

mysql -u root -p"${MYSQL_ROOT_PASSWORD}" <<SQL 2>/dev/null || { echo "Cannot connect with new password"; exit 1; }

-- Ensure password policy is lowered
SET GLOBAL validate_password.policy=0;
SET GLOBAL validate_password.length=4;

CREATE DATABASE IF NOT EXISTS \`${MYSQL_DATABASE}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER IF NOT EXISTS '${MYSQL_USER}'@'localhost' IDENTIFIED BY '${MYSQL_PASSWORD}';
CREATE USER IF NOT EXISTS '${MYSQL_USER}'@'%' IDENTIFIED BY '${MYSQL_PASSWORD}';

-- Allow root remote connection (for development environment, be careful in production)
CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '${MYSQL_ROOT_PASSWORD}';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON \`${MYSQL_DATABASE}\`.* TO '${MYSQL_USER}'@'localhost';
GRANT ALL PRIVILEGES ON \`${MYSQL_DATABASE}\`.* TO '${MYSQL_USER}'@'%';

FLUSH PRIVILEGES;



SELECT 'Database created successfully' AS message;
SELECT user, host FROM mysql.user WHERE user LIKE '%${MYSQL_USER}%' OR user = 'root';
SHOW DATABASES LIKE '${MYSQL_DATABASE}';
SQL

# Configure remote access
echo "Configuring MySQL for remote access..."
sudo tee /etc/my.cnf.d/steerdock.cnf <<CONFIG
[mysqld]
bind-address = 0.0.0.0

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

[client]
default-character-set=utf8mb4
CONFIG

sudo systemctl restart mysqld
sleep 2

# Test connections
echo ""
echo "Testing connections..."

if mysql -u root -p"${MYSQL_ROOT_PASSWORD}" -e "SELECT 1;" 2>/dev/null; then
    echo "✅ MySQL root connection: OK"
else
    echo "❌ MySQL root connection: FAILED"
fi

if mysql -u "${MYSQL_USER}" -p"${MYSQL_PASSWORD}" -e "SELECT 1;" 2>/dev/null; then
    echo "✅ MySQL app user connection: OK"
else
    echo "❌ MySQL app user connection: FAILED"
fi

# Install Redis
echo ""
echo "Installing Redis..."
sudo dnf install -y epel-release redis 2>/dev/null || yum install -y epel-release redis

# Configure Redis
REDIS_PASSWORD=${REDIS_PASSWORD:-RedisPass123!}
echo "Configuring Redis with password..."

# Backup original configuration
sudo cp /etc/redis.conf /etc/redis.conf.backup 2>/dev/null || true

# Update configuration
sudo sed -i "s/# requirepass .*/requirepass ${REDIS_PASSWORD}/g" /etc/redis.conf 2>/dev/null
sudo sed -i "s/^requirepass.*/requirepass ${REDIS_PASSWORD}/g" /etc/redis.conf 2>/dev/null
sudo sed -i "s/^bind 127.0.0.1/bind 0.0.0.0/g" /etc/redis.conf 2>/dev/null

# If requirepass is not in the file, add it
if ! sudo grep -q "^requirepass" /etc/redis.conf 2>/dev/null; then
    echo "requirepass ${REDIS_PASSWORD}" | sudo tee -a /etc/redis.conf
fi

sudo systemctl start redis
sudo systemctl enable redis

echo ""
echo "========================================="
echo "🎉 Installation Complete!"
echo "========================================="
echo ""
echo "MySQL Configuration:"
echo "  Host: localhost:3306"
echo "  Root Password: ${MYSQL_ROOT_PASSWORD}"
echo "  Database: ${MYSQL_DATABASE}"
echo "  App User: ${MYSQL_USER}"
echo "  App Password: ${MYSQL_PASSWORD}"
echo ""
echo "Redis Configuration:"
echo "  Host: localhost:6379"
echo "  Password: ${REDIS_PASSWORD}"
echo ""
echo "Test Commands:"
echo "  mysql -u root -p'${MYSQL_ROOT_PASSWORD}'"
echo "  mysql -u ${MYSQL_USER} -p'${MYSQL_PASSWORD}' -D ${MYSQL_DATABASE}"
echo "  redis-cli -a '${REDIS_PASSWORD}' ping"
echo ""