MySQL Installation and Setup: A Complete Beginner's Guide (2024)

# ── Check whether the MySQL server process is running ──────────────────────
# On Linux/Mac (systemd-based systems):
sudo systemctl status mysql

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

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

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

# What you're looking for in the output: 'active (running)' on Linux,
# 'started' in Homebrew output, or 'Running' on Windows.
# If you see 'inactive', 'stopped', or nothing at all, the server isn't up yet.

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

# Step 1: Install Homebrew if you don't have it yet
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# Step 2: Install MySQL via Homebrew
brew install mysql
# Homebrew downloads MySQL, installs it under /opt/homebrew/,
# and sets up a launchd service entry.

# Step 3: Start the MySQL server right now AND on every future boot
brew services start mysql
# 'start' means: launch it now + auto-start on login.
# Use 'brew services run mysql' if you only want it running right now.

# ════════════════════════════════════════════════════════
# OPTION B — Ubuntu / Debian Linux
# ════════════════════════════════════════════════════════

# Step 1: Refresh the package list so apt knows about the latest versions
sudo apt update

# Step 2: Install the MySQL server package
# This also installs the client tools as dependencies.
sudo apt install mysql-server -y
# The '-y' flag auto-confirms the install without prompting.

# Step 3: The service starts automatically after install. Verify it:
sudo systemctl status mysql

# Step 4: Enable auto-start on system reboot (may already be on)
sudo systemctl enable mysql

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

# Open PowerShell as Administrator, then:
winget install Oracle.MySQL
# winget handles download, silent install, and Windows Service registration.
# Reboot after install, then verify in Services (services.msc).

# ════════════════════════════════════════════════════════
# ALL PLATFORMS — Run the security hardening script NEXT
# ════════════════════════════════════════════════════════

sudo mysql_secure_installation
# This interactive script will ask you:
#  1. Set a root password? → YES, pick a strong one
#  2. Remove anonymous users? → YES
#  3. Disallow root login remotely? → YES (use a named user for remote access)
#  4. Remove test database? → YES
#  5. Reload privilege tables now? → YES

-- ── STEP 1: Log into MySQL as root ────────────────────────────────────────
-- Run this in your terminal (not inside MySQL yet):
-- mysql -u root -p
-- You'll be prompted for the root password you set during secure installation.
-- Once inside you'll see the 'mysql>' prompt.

-- ── STEP 2: Create a dedicated database ───────────────────────────────────
CREATE DATABASE bookstore_db
  CHARACTER SET utf8mb4       -- utf8mb4 supports ALL Unicode including emoji
  COLLATE utf8mb4_unicode_ci; -- ci = case-insensitive string comparisons

-- Verify it was created:
SHOW DATABASES;

-- ── STEP 3: Create a dedicated app user (never use root for apps) ─────────
-- Replace 'secure_password_here' with a real strong password.
CREATE USER 'bookstore_app'@'localhost' IDENTIFIED BY 'secure_password_here';
-- 'bookstore_app'@'localhost' means this user can ONLY connect from this machine.
-- To allow connections from any host, use '%' instead of 'localhost' —
-- but only do that when you actually need remote access.

-- ── STEP 4: Grant the app user rights on the bookstore database only ──────
GRANT SELECT, INSERT, UPDATE, DELETE ON bookstore_db.* TO 'bookstore_app'@'localhost';
-- We're NOT granting DROP, CREATE, or GRANT — the app doesn't need those.
-- Least privilege = if the app is ever compromised, damage is limited.

-- 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 books (
  book_id       INT           NOT NULL AUTO_INCREMENT, -- unique ID, auto-counted
  title         VARCHAR(255)  NOT NULL,                -- up to 255 characters
  author_name   VARCHAR(150)  NOT NULL,
  genre         VARCHAR(80)   NOT NULL,
  price_usd     DECIMAL(8,2)  NOT NULL,                -- e.g. 12.99 (2 decimal places)
  in_stock      BOOLEAN       NOT NULL DEFAULT TRUE,   -- TRUE = 1, FALSE = 0
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (book_id)      -- book_id uniquely identifies every row
);

-- ── STEP 7: Insert some 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);
-- Notice: we don't insert book_id or created_at — MySQL fills those automatically.

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

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

-- Count how many books exist per genre:
SELECT genre, COUNT(*) AS total_books, AVG(price_usd) AS avg_price
FROM   books
GROUP BY genre
ORDER BY total_books DESC;

# Install the connector first (run this in your terminal, not in Python):
# pip install mysql-connector-python

import mysql.connector
import os
from decimal import Decimal

# ── Build the connection config from environment variables ────────────────
# In production: export DB_PASSWORD='your_password' before running this script.
# For local dev you can use the literal string, but swap it before committing.
db_config = {
    "host":     os.getenv("DB_HOST",     "127.0.0.1"),  # 127.0.0.1 = this machine
    "port":     int(os.getenv("DB_PORT", "3306")),       # MySQL default port
    "user":     os.getenv("DB_USER",     "bookstore_app"),
    "password": os.getenv("DB_PASSWORD", "secure_password_here"),  # ← swap in prod
    "database": os.getenv("DB_NAME",     "bookstore_db"),
}

def fetch_books_under_price(max_price: Decimal) -> list[dict]:
    """
    Returns all books priced below max_price, sorted cheapest first.
    Uses a parameterised query — NEVER concatenate user input into SQL strings.
    """
    # Using a context manager (with) ensures the connection closes even if an error occurs
    with mysql.connector.connect(**db_config) as connection:
        # dictionary=True makes each row a dict like {'title': 'Dune', 'price_usd': 14.99}
        # instead of a plain tuple, which is much easier to work with
        with connection.cursor(dictionary=True) as cursor:

            # The %s placeholder is replaced safely by the connector,
            # preventing SQL injection attacks
            query = """
                SELECT book_id, title, author_name, price_usd
                FROM   books
                WHERE  price_usd < %s
                ORDER BY price_usd ASC
            """
            cursor.execute(query, (max_price,))  # always pass params as a tuple

            results = cursor.fetchall()          # fetch every matching row
            return results


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.
    """
    with mysql.connector.connect(**db_config) 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))
            connection.commit()   # ← CRITICAL: write changes to disk
            new_book_id = cursor.lastrowid  # MySQL returns the new AUTO_INCREMENT id
            return new_book_id


# ── Main: run a demo when the script is executed directly ────────────────
if __name__ == "__main__":
    print("=== Books under $20 ===")
    affordable_books = fetch_books_under_price(max_price=Decimal("20.00"))
    for book in affordable_books:
        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 with book_id = {new_id}")