MySQL 8.4 Install — Root Without Password, Bound to 0.0.0.0

# ── Check whether the MySQL server process is running ──────────────────────
# On Linux (systemd-based — Ubuntu, Debian, RHEL, Fedora):
sudo systemctl status mysql
# On some distributions the service name is 'mysqld' instead of 'mysql':
sudo systemctl status mysqld

# On older Linux systems still using 'service':
sudo service mysql status

# On macOS (installed via Homebrew):
brew services list | grep mysql

# On Windows (PowerShell, run as Administrator):
Get-Service -Name 'MySQL*'

# ── Confirm what port MySQL is listening on ────────────────────────────────
# Linux/macOS — ss replaces the older netstat on modern systems:
sudo ss -tlnp | grep 3306

# macOS alternative:
sudo lsof -i :3306

# Windows (PowerShell):
netstat -ano | findstr :3306

# What you're looking for:
# Linux/Mac: a line showing '0.0.0.0:3306' or '127.0.0.1:3306' with mysqld in the process column
# Windows: a line showing '0.0.0.0:3306' with state LISTENING
# If nothing appears on port 3306, the server is not running or is using a non-default port.

# ════════════════════════════════════════════════════════
# OPTION A — macOS (using Homebrew)
# ════════════════════════════════════════════════════════

# Step 1: Install Homebrew if you don't have it
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
# After install on Apple Silicon, follow the prompt to add Homebrew to your PATH.

# Step 2: Install MySQL 8.4 via Homebrew
brew install mysql
# As of 2026, 'brew install mysql' resolves to MySQL 8.4 LTS.
# Homebrew installs under /opt/homebrew/opt/mysql/ on Apple Silicon.

# Step 3: Start the MySQL server now AND on every future boot
brew services start mysql
# 'start' = launch now + register as a login item (auto-start on reboot).
# Use 'brew services run mysql' if you only want it running for this session.

# Verify it started correctly:
brew services list | grep mysql
mysql --version

# ════════════════════════════════════════════════════════
# OPTION B — Ubuntu 22.04 / 24.04 (MySQL 8.4 from Oracle repo)
# ════════════════════════════════════════════════════════

# Step 1: Add Oracle's official MySQL APT repository
# This ensures you get MySQL 8.4 LTS, not 8.0 from Ubuntu's default repo.
wget https://dev.mysql.com/get/mysql-apt-config_0.8.32-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.32-1_all.deb
# A dialog appears — select MySQL 8.4 Server, then OK.

# Step 2: Refresh the package list to include Oracle's repo
sudo apt update

# Step 3: Install MySQL 8.4 Server
sudo apt install mysql-server -y
# The service starts automatically after install.

# Step 4: Verify version and service status
mysql --version
sudo systemctl status mysql

# Step 5: Enable auto-start on system reboot (usually already enabled)
sudo systemctl enable mysql

# ════════════════════════════════════════════════════════
# OPTION C — Windows 10 / 11 (via winget)
# ════════════════════════════════════════════════════════

# Open PowerShell as Administrator:
winget install Oracle.MySQL
# winget downloads MySQL 8.4, installs silently, and registers a Windows Service.
# After install completes, verify:
Get-Service -Name 'MySQL*'
mysql --version

# ════════════════════════════════════════════════════════
# ALL PLATFORMS — Security hardening script (run this next, always)
# ════════════════════════════════════════════════════════

sudo mysql_secure_installation
# On Windows, run this from the MySQL bin directory or add it to your PATH.
# The script will ask you five questions — answer as shown:
# 1. Set up VALIDATE PASSWORD component? → YES (enforces strong passwords)
# 2. Password strength level → 1 (MEDIUM) is fine for local dev, 2 for production
# 3. Set a root password? → YES — pick something strong, write it down
# 4. Remove anonymous users? → YES
# 5. Disallow root login remotely? → YES
# 6. Remove test database? → YES
# 7. Reload privilege tables now? → YES

# After it finishes, verify you can log in with the new password:
mysql -u root -p
# You should reach the 'mysql>' prompt after entering your root password.

-- ── STEP 1: Connect to MySQL as root ──────────────────────────────────────
-- Run this in your terminal (outside MySQL):
-- mysql -u root -p
-- Enter the root password you set during mysql_secure_installation.
-- You'll see the 'mysql>' prompt when you're inside.

-- ── STEP 2: Create a dedicated database ───────────────────────────────────
CREATE DATABASE IF NOT EXISTS bookstore_db
    CHARACTER SET utf8mb4        -- full Unicode including emoji
    COLLATE utf8mb4_unicode_ci;  -- case-insensitive string comparison
-- IF NOT EXISTS prevents an error if you run this script twice.

-- Verify it was created:
SHOW DATABASES;

-- ── STEP 3: Create a dedicated application user ────────────────────────────
-- Replace 'secure_password_here' with a real password (16+ chars, mixed case,
-- numbers, symbols). The user only has access from this machine ('localhost').
CREATE USER IF NOT EXISTS 'bookstore_app'@'localhost'
    IDENTIFIED BY 'secure_password_here';
-- IF NOT EXISTS prevents an error if you accidentally run this twice.

-- ── STEP 4: Grant only the permissions the app actually needs ──────────────
GRANT SELECT, INSERT, UPDATE, DELETE
    ON bookstore_db.*
    TO 'bookstore_app'@'localhost';
-- The app gets no DROP, CREATE, GRANT, or SHOW DATABASES.
-- If the app is ever compromised, the attacker cannot destroy the schema
-- or create backdoor users.

-- Apply the privilege changes immediately:
FLUSH PRIVILEGES;

-- ── STEP 5: Switch into the bookstore database ────────────────────────────
USE bookstore_db;

-- ── STEP 6: Create the books table ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS books (
    book_id      INT           NOT NULL AUTO_INCREMENT,
    title        VARCHAR(255)  NOT NULL,
    author_name  VARCHAR(150)  NOT NULL,
    genre        VARCHAR(80)   NOT NULL,
    price_usd    DECIMAL(8,2)  NOT NULL,   -- exact decimal, never FLOAT for money
    in_stock     BOOLEAN       NOT NULL DEFAULT TRUE,
    created_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
                               ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (book_id),
    INDEX idx_genre (genre),               -- speeds up WHERE genre = '...'
    INDEX idx_price (price_usd)            -- speeds up ORDER BY price_usd
);
-- AUTO_INCREMENT: MySQL assigns book_id automatically — 1, 2, 3, ...
-- DECIMAL(8,2): stores values up to 999999.99 exactly — right for money.
-- ON UPDATE CURRENT_TIMESTAMP: updated_at refreshes automatically on any change.
-- The two INDEX definitions are small now but matter when the table has millions of rows.

-- ── STEP 7: Insert sample books ───────────────────────────────────────────
INSERT INTO books (title, author_name, genre, price_usd) VALUES
    ('The Pragmatic Programmer', 'David Thomas',      'Technology', 49.95),
    ('Clean Code',               'Robert C. Martin',  'Technology', 35.00),
    ('Dune',                     'Frank Herbert',     'Sci-Fi',     14.99),
    ('Project Hail Mary',        'Andy Weir',         'Sci-Fi',     16.50),
    ('The Psychology of Money',  'Morgan Housel',     'Finance',    18.00);
-- We omit book_id, in_stock, created_at, updated_at — MySQL fills these automatically.

-- ── STEP 8: Query the data ────────────────────────────────────────────────
-- All books, cheapest first:
SELECT book_id, title, author_name, price_usd
FROM   books
ORDER  BY price_usd ASC;

-- Only Sci-Fi books:
SELECT title, author_name, price_usd
FROM   books
WHERE  genre = 'Sci-Fi';

-- Count and average price per genre:
SELECT
    genre,
    COUNT(*)              AS total_books,
    AVG(price_usd)        AS avg_price,
    MIN(price_usd)        AS cheapest,
    MAX(price_usd)        AS most_expensive
FROM   books
GROUP  BY genre
ORDER  BY total_books DESC, avg_price DESC;

-- ── STEP 9: Verify the table structure ────────────────────────────────────
DESCRIBE books;
-- Shows column names, types, nullability, defaults, and key assignments.
-- Good habit: always describe a table after creating it to confirm it looks right.

# Install the connector first (run in your terminal, not in Python):
# pip install mysql-connector-python
# Verify you have a 9.x version for MySQL 8.4 compatibility:
# pip show mysql-connector-python

import mysql.connector
import os
from decimal import Decimal

# ── Connection configuration — from environment variables ─────────────────
# Before running locally, set these in your shell:
#   export DB_HOST=127.0.0.1
#   export DB_PASSWORD=secure_password_here
# In production, inject these from a secrets manager or CI/CD secret variables.
db_config = {
    "host":     os.getenv("DB_HOST",     "127.0.0.1"),  # 127.0.0.1 is safer than 'localhost'
    "port":     int(os.getenv("DB_PORT", "3306")),
    "user":     os.getenv("DB_USER",     "bookstore_app"),
    "password": os.getenv("DB_PASSWORD", ""),           # empty default forces explicit env var
    "database": os.getenv("DB_NAME",     "bookstore_db"),
}
# Note: we use 127.0.0.1 rather than 'localhost' deliberately.
# On Linux, 'localhost' routes through a Unix socket, 127.0.0.1 uses TCP.
# The bookstore_app user was created for 'localhost' (socket) but if your
# driver resolves differently, the host spec in mysql.user may not match.
# Using 127.0.0.1 consistently avoids this ambiguity.


def get_connection():
    """
    Returns a new MySQL connection. Call this at the start of each operation.
    In a real application, replace this with a connection pool
    (e.g., mysql.connector.pooling.MySQLConnectionPool).
    """
    return mysql.connector.connect(**db_config)


def fetch_books_under_price(max_price: Decimal) -> list[dict]:
    """
    Returns all books priced strictly below max_price, sorted cheapest first.
    Uses a parameterised query — NEVER concatenate user input into SQL strings.
    """
    with get_connection() as connection:
        # dictionary=True: each row comes back as {'title': 'Dune', 'price_usd': Decimal('14.99')}
        # rather than a plain tuple — much easier to work with in application code.
        with connection.cursor(dictionary=True) as cursor:
            query = """
                SELECT book_id, title, author_name, price_usd
                FROM   books
                WHERE  price_usd < %s
                ORDER  BY price_usd ASC
            """
            # The %s placeholder is replaced safely by the connector library.
            # Parameters are always passed as a tuple — even a single parameter.
            # This prevents SQL injection because user input never becomes SQL syntax.
            cursor.execute(query, (max_price,))
            return cursor.fetchall()


def add_new_book(title: str, author: str, genre: str, price: Decimal) -> int:
    """
    Inserts a new book row and returns the auto-generated book_id.
    Demonstrates the commit() pattern required for all write operations.
    """
    with get_connection() as connection:
        with connection.cursor() as cursor:
            insert_query = """
                INSERT INTO books (title, author_name, genre, price_usd)
                VALUES (%s, %s, %s, %s)
            """
            cursor.execute(insert_query, (title, author, genre, price))

            # ── CRITICAL: commit() writes the transaction to disk ─────────
            # Without this line, the INSERT is held in a pending InnoDB
            # transaction. When the 'with' block exits and the connection
            # closes, MySQL silently rolls back the pending transaction.
            # No error. No warning. The row simply will not exist.
            connection.commit()

            # lastrowid gives us the AUTO_INCREMENT value MySQL assigned.
            return cursor.lastrowid


def update_book_price(book_id: int, new_price: Decimal) -> bool:
    """
    Updates the price of a book by ID.
    Returns True if a row was updated, False if book_id was not found.
    """
    with get_connection() as connection:
        with connection.cursor() as cursor:
            cursor.execute(
                "UPDATE books SET price_usd = %s WHERE book_id = %s",
                (new_price, book_id)
            )
            connection.commit()  # required — this is a write operation
            return cursor.rowcount > 0  # rowcount = number of rows actually changed


# ── Demo: run when the script is executed directly ───────────────────────
if __name__ == "__main__":
    print("=== Books under $20 ===")
    affordable = fetch_books_under_price(max_price=Decimal("20.00"))
    for book in affordable:
        print(f"  [{book['book_id']}] {book['title']} by {book['author_name']} — ${book['price_usd']}")

    print("\n=== Adding a new book ===")
    new_id = add_new_book(
        title="Atomic Habits",
        author="James Clear",
        genre="Self-Help",
        price=Decimal("16.99")
    )
    print(f"  New book inserted — book_id = {new_id}")

    print("\n=== Updating a price ===")
    updated = update_book_price(book_id=new_id, new_price=Decimal("14.99"))
    print(f"  Price update {'succeeded' if updated else 'failed — book_id not found'}")

// io.thecodeforge — database tutorial

CREATE USER IF NOT EXISTS 'app_write'@'10.0.0.%' IDENTIFIED BY 's3cure_p@ss!';  -- production app user

GRANT SELECT, INSERT, UPDATE, DELETE ON shop_analytics.orders TO 'app_write'@'10.0.0.%';
GRANT SELECT ON shop_analytics.products TO 'app_write'@'10.0.0.%';  -- read-only for product catalog

-- read-only analyst
CREATE USER 'analyst_ro'@'%' IDENTIFIED BY 'v1ew_0nly!';
GRANT SELECT ON shop_analytics.* TO 'analyst_ro'@'%';

-- dev user with full access to dev database only
CREATE USER 'dev_jane'@'localhost' IDENTIFIED BY 'd3v_p@ss';
GRANT ALL PRIVILEGES ON shop_analytics_dev.* TO 'dev_jane'@'localhost';

FLUSH PRIVILEGES;

-- cleanup: always revoke before drop
REVOKE ALL PRIVILEGES ON shop_analytics.* FROM 'app_write'@'10.0.0.%';
DROP USER IF EXISTS 'app_write'@'10.0.0.%';

// io.thecodeforge — database tutorial

USE shop_analytics;

CREATE TABLE IF NOT EXISTS inventory (
    sku_id INT AUTO_INCREMENT PRIMARY KEY,  -- surrogate key
    sku_code VARCHAR(50) NOT NULL UNIQUE,   -- business key
    product_name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    reorder_threshold INT NOT NULL DEFAULT 10,
    unit_price DECIMAL(10,2) NOT NULL,
    last_restocked TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_sku_code (sku_code),
    INDEX idx_quantity (quantity, reorder_threshold)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bad table: missing NOT NULL will haunt you
CREATE TABLE legacy_inventory (
    sku_code VARCHAR(50),  -- can be NULL
    quantity INT           -- can be NULL
) ENGINE=MyISAM;   -- no transactions, no FK enforcement

// io.thecodeforge — database tutorial

CREATE TRIGGER block_negative_price
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Price cannot be negative';
    END IF;
END;

-- Test: causes error
INSERT INTO orders (customer_id, price) VALUES (1, -50);

// io.thecodeforge — database tutorial

-- BAD: NULL comparison
SELECT * FROM users WHERE email = NULL;  -- returns nothing

-- FIXED
SELECT * FROM users WHERE email IS NULL;

-- GOOD: JSON path extraction
SELECT
    data->'$.address.city' AS city
FROM customers;

// io.thecodeforge — database tutorial
// 25 lines max
CREATE DATABASE shop;
USE shop;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products (name, price) VALUES ('Widget', 19.99);
INSERT INTO products (name, price) VALUES ('Gadget', 29.99);

SELECT * FROM products;
-- id:1, name:Widget, price:19.99, created_at:2025-03-29 10:00:00
-- id:2, name:Gadget, price:29.99, created_at:2025-03-29 10:00:01

// io.thecodeforge — database tutorial
// 25 lines max
-- MySQL dialect example
SELECT id, name 
FROM users 
WHERE active = 1
ORDER BY created_at DESC
LIMIT 5 OFFSET 10;

-- Equivalent PostgreSQL (also works in MySQL)
SELECT id, name 
FROM users 
WHERE active = true
ORDER BY created_at DESC
LIMIT 5 OFFSET 10;

-- MySQL GROUP BY lenient
SELECT name, COUNT(*) 
FROM orders 
GROUP BY name;
-- PostgreSQL would require: GROUP BY name

-- Key difference: backticks vs double quotes
-- MySQL: SELECT `status` FROM `orders`;
-- PostgreSQL: SELECT "status" FROM "orders";