🐬 MySQL with Compose (Docker or Podman)¶

# Optional global engine switch
ENGINE=${ENGINE:-docker}  # set ENGINE=podman if you use Podman
````

Then every command becomes engine-agnostic:

```bash
$ENGINE compose up -d
$ENGINE compose logs -f mysql
$ENGINE exec -it mysql8 mysql -u root -p

# File: compose.yaml
# Purpose: Run a reusable local MySQL 8 container that your Spring Boot app
# (running on host) and DBeaver can connect to via localhost. Works with Docker or Podman.
services:
  mysql:
    image: mysql:8.0              # Base image. For reproducibility, pin a patch: e.g., mysql:8.0.43
    container_name: mysql8        # Stable handle for exec/logs: `$ENGINE exec -it mysql8 ...`
    restart: unless-stopped       # Auto-start on reboot; won't restart if you stop it manually

    environment:
      MYSQL_ROOT_PASSWORD: rootpass  # Root password (only used on first init). Move to .env for safety.
      MYSQL_DATABASE: appdb          # Auto-create this DB on first init
      MYSQL_USER: appuser            # App user created on first init
      MYSQL_PASSWORD: apppass        # App user password (first init only)
      TZ: Europe/Vilnius             # Align container time with host; helpful for logs/JDBC timestamps

    ports:
      - "3306:3306"               # Host:Container. If host 3306 is busy, use "3307:3306" and set JDBC port=3307

    command:                      # mysqld server flags
      - --default-authentication-plugin=mysql_native_password
        # Use legacy auth for maximum client compatibility (DBeaver/JDBC); drop this if all clients
        # support caching_sha2_password (the modern default).
      - --character-set-server=utf8mb4
        # Full Unicode, including emoji.
      - --collation-server=utf8mb4_0900_ai_ci
        # Modern, case-insensitive, accent-insensitive collation that pairs with utf8mb4.
      - --skip-host-cache
        # Avoid reverse DNS lookups on connect (faster, fewer surprises).
      - --skip-name-resolve
        # Force MySQL to use IPs instead of hostnames (prevents weird grants on hostnames).

    volumes:
      - mysql_data:/var/lib/mysql
        # Named volume for data files (safe default on Linux; survives container recreation).
      - ./initdb:/docker-entrypoint-initdb.d:ro,Z
        # One-time init scripts (.sql/.sh) executed alphabetically on first boot (empty data dir).
        # `:ro` = read-only for safety. `,Z` = SELinux relabel (Fedora/RHEL); harmless elsewhere.

    healthcheck:
      test: ["CMD-SHELL", "mysqladmin ping -h localhost -p$${MYSQL_ROOT_PASSWORD} --silent"]
        # Simple readiness probe. Note the $$ to escape $ in YAML so the container sees $MYSQL_ROOT_PASSWORD.
      interval: 10s               # Probe every 10 seconds
      timeout: 5s                 # Consider it failed if no response within 5 seconds
      retries: 10                 # Require 10 consecutive passes/failures before flipping status

volumes:
  mysql_data:                     # Declare the named volume used above

$ENGINE compose up -d
$ENGINE compose logs -f mysql   # watch for: "ready for connections"
$ENGINE ps

$ENGINE exec -it mysql8 mysql -u root -p
# enter rootpass

CREATE USER 'newuser'@'%' IDENTIFIED BY 'newpass';
GRANT ALL PRIVILEGES ON appdb.* TO 'newuser'@'%';
FLUSH PRIVILEGES;

./initdb/
 ├─ 01-schema.sql
 ├─ 02-seed.sql
 └─ 03-users.sql

USE appdb;

CREATE TABLE IF NOT EXISTS customers (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

USE appdb;

INSERT INTO customers (email, first_name, last_name, status)
VALUES
  ('ada@example.com',  'Ada',  'Lovelace', 'ACTIVE'),
  ('grace@example.com','Grace','Hopper',   'ACTIVE')
ON DUPLICATE KEY UPDATE
  first_name = VALUES(first_name),
  last_name  = VALUES(last_name),
  status     = VALUES(status);

$ENGINE compose down -v
$ENGINE compose up -d

# pick ONE:
spring.jpa.hibernate.ddl-auto=update
# spring.jpa.hibernate.ddl-auto=validate
# spring.jpa.hibernate.ddl-auto=create
# spring.jpa.hibernate.ddl-auto=create-drop