数据库服务搭建完全指南：MySQL / PostgreSQL / Redis

# ── Ubuntu / Debian ──────────────────────────────────────────────────────────
sudo apt update && sudo apt install mysql-server -y

# 启动并设置开机自启
sudo systemctl enable --now mysql

# 验证安装（查看版本）
mysql --version

# 运行官方安全初始化脚本（安装后必做！）
# 脚本会引导您设置 root 密码、移除匿名用户、禁止 root 远程登录、删除测试数据库
sudo mysql_secure_installation

# 建议选择：
# VALIDATE PASSWORD component: Y（开启密码强度验证）
# Password level: 2（STRONG，至少 8 位含大小写+数字+特殊字符）
# Remove anonymous users: Y
# Disallow root login remotely: Y（禁止 root 远程登录，最重要！）
# Remove test database: Y
# Reload privilege tables: Y

# 以 root 登录（Ubuntu/Debian 安装后默认无密码，直接 sudo mysql）
sudo mysql

-- 创建应用数据库
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 为每个应用创建独立用户，权限最小化（不用 root！）
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY '高强度密码_至少16位';

-- 只授予该用户访问 myapp 库的权限
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp_user'@'localhost';

-- 刷新权限（立即生效）
FLUSH PRIVILEGES;

-- 验证：查看用户权限
SHOW GRANTS FOR 'myapp_user'@'localhost';

EXIT;

# 文件路径：/etc/mysql/mysql.conf.d/mysqld.cnf
# 在 [mysqld] 区块下添加以下配置

[mysqld]
# ── 字符集（必须设置，避免中文和 emoji 乱码）──────────────────────────────
character-set-server  = utf8mb4
collation-server      = utf8mb4_unicode_ci

# ── 慢查询日志（排查性能瓶颈的必备工具）──────────────────────────────────────
slow_query_log        = 1
slow_query_log_file   = /var/log/mysql/mysql-slow.log
long_query_time       = 1                     # 超过 1 秒的 SQL 都记录
log_queries_not_using_indexes = 1            # 未用索引的查询也记录（非常有价值）

# ── 核心性能参数（根据服务器内存调整）────────────────────────────────────────
# 2GB 内存服务器参考值：
innodb_buffer_pool_size = 1G                  # 物理内存的 50%
max_connections         = 300                 # 最大并发连接数
innodb_log_file_size    = 256M               # Redo Log 大小

# 重启 MySQL 使配置生效
# sudo systemctl restart mysql

# ── Ubuntu / Debian（使用官方源获取最新版本）────────────────────────────────
sudo apt install -y postgresql postgresql-contrib

# 启动并设置开机自启
sudo systemctl enable --now postgresql

# 验证（查看版本）
psql --version

# ── CentOS / AlmaLinux（使用 PostgreSQL 官方 YUM 源）──────────────────────
# 参考：https://www.postgresql.org/download/linux/redhat/
sudo dnf install -y postgresql16-server
sudo postgresql-16-setup initdb
sudo systemctl enable --now postgresql-16

# ── 切换到 postgres 系统用户（PostgreSQL 的管理用户）──────────────────────
sudo -i -u postgres

# 进入数据库命令行（psql）
psql

-- 修改 postgres 超级管理员密码（必做！）
password postgres

-- 创建应用数据库
CREATE DATABASE myapp ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' TEMPLATE template0;

-- 创建专用用户（不使用超级用户 postgres 连接应用！）
CREATE USER myapp_user WITH ENCRYPTED PASSWORD '高强度密码_至少16位';

-- 授予数据库权限
GRANT ALL PRIVILEGES ON DATABASE myapp TO myapp_user;

-- 退出
q

# 退出 postgres 系统用户
exit

# ── 修改 pg_hba.conf 控制访问认证方式 ───────────────────────────────────────
# 文件路径（PostgreSQL 16）：/etc/postgresql/16/main/pg_hba.conf
# 每一行格式：类型  数据库  用户  地址  认证方式

# 默认配置（local peer 允许本地系统用户无密码登录）保留不动
# local   all    postgres    peer

# 修改远程连接认证方式为 scram-sha-256（最安全的密码认证）
# host  all  all  0.0.0.0/0  scram-sha-256   # ← 不推荐开放所有 IP
# host  all  all  1.2.3.4/32  scram-sha-256  # ← 只允许特定 IP（推荐）

# ── 修改 postgresql.conf 绑定地址 ────────────────────────────────────────────
# 文件路径：/etc/postgresql/16/main/postgresql.conf
# 默认只监听本地（推荐保持）
listen_addresses = 'localhost'

# 如需远程访问，改为（并配合防火墙限制 IP）：
# listen_addresses = '*'

# 修改后重启服务
sudo systemctl restart postgresql

# ── Ubuntu / Debian ──────────────────────────────────────────────────────────
sudo apt update && sudo apt install redis-server -y
sudo systemctl enable --now redis-server

# 验证安装（查看版本）
redis-cli --version

# 测试连通性
redis-cli ping   # 应返回：PONG

# 文件路径：/etc/redis/redis.conf
# 修改以下关键配置（使用 sudo nano /etc/redis/redis.conf）

# ── 安全：绑定地址（只允许本地访问）──────────────────────────────────────────
bind 127.0.0.1 ::1

# ── 安全：设置强密码（绝对不能省略！）───────────────────────────────────────
requirepass 高强度密码_至少20位随机字符

# ── 安全：禁用危险命令（防止攻击者执行 FLUSHALL 清空数据）────────────────────
rename-command FLUSHALL ""         # 禁用 FLUSHALL
rename-command FLUSHDB  ""         # 禁用 FLUSHDB
rename-command CONFIG   "CONF_SAFE" # 重命名为难猜的名字（用于管理工具）
rename-command DEBUG    ""

# ── 内存限制（防止 Redis 无限消耗内存导致系统 OOM）────────────────────────────
maxmemory 256mb                    # 最多使用 256MB 内存
maxmemory-policy allkeys-lru       # 内存满时：淘汰最久未使用的 key（推荐缓存场景）

# ── 持久化（见下方说明）──────────────────────────────────────────────────────
appendonly yes                     # 开启 AOF 持久化（数据安全性更高）
appendfsync everysec               # 每秒同步一次（性能与安全的平衡）

# 重启生效
# sudo systemctl restart redis-server

# 文件路径：/opt/databases/docker-compose.yml
# 一次性部署 MySQL + PostgreSQL + Redis 三大数据库
# 所有数据库端口只绑定 127.0.0.1，不对外暴露

services:

  # ── MySQL 8 ────────────────────────────────────────────────────────────────
  mysql:
    image: mysql:8.0
    container_name: db_mysql
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE:      ${MYSQL_DATABASE}
      MYSQL_USER:          ${MYSQL_USER}
      MYSQL_PASSWORD:      ${MYSQL_PASSWORD}
    volumes:
      - mysql_data:/var/lib/mysql                  # 数据目录（命名卷，高 I/O）
      - ./mysql/conf.d:/etc/mysql/conf.d            # 自定义配置文件目录
    ports:
      - "127.0.0.1:3306:3306"                      # 只绑定本地，不对外暴露！
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

  # ── PostgreSQL 16 ──────────────────────────────────────────────────────────
  postgres:
    image: postgres:16-alpine
    container_name: db_postgres
    restart: unless-stopped
    environment:
      POSTGRES_DB:       ${PG_DATABASE}
      POSTGRES_USER:     ${PG_USER}
      POSTGRES_PASSWORD: ${PG_PASSWORD}
    volumes:
      - pg_data:/var/lib/postgresql/data
    ports:
      - "127.0.0.1:5432:5432"

  # ── Redis 7 ────────────────────────────────────────────────────────────────
  redis:
    image: redis:7-alpine
    container_name: db_redis
    restart: unless-stopped
    command: >
      redis-server
      --requirepass ${REDIS_PASSWORD}
      --appendonly yes
      --maxmemory 256mb
      --maxmemory-policy allkeys-lru
    volumes:
      - redis_data:/data
    ports:
      - "127.0.0.1:6379:6379"

volumes:
  mysql_data:
  pg_data:
  redis_data:

# 文件路径：/opt/databases/.env
# 敏感配置统一存放，不提交到 Git（.gitignore 中加入 .env）

MYSQL_ROOT_PASSWORD=MySQL_ROOT强密码_随机20位
MYSQL_DATABASE=myapp_db
MYSQL_USER=myapp_user
MYSQL_PASSWORD=MySQL应用密码_随机16位

PG_DATABASE=myapp_pg
PG_USER=pg_user
PG_PASSWORD=PostgreSQL密码_随机16位

REDIS_PASSWORD=Redis密码_随机20位随机字符

# 启动：cd /opt/databases && docker compose up -d
# 查看状态：docker compose ps
# 进入 MySQL：docker exec -it db_mysql mysql -u root -p

#!/bin/bash
# 文件路径：/usr/local/bin/db-backup.sh
# 功能：自动备份 MySQL 所有数据库，保留 14 天，支持压缩
set -euo pipefail

BACKUP_DIR="/var/backups/mysql"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
LOG="/var/log/db-backup.log"
KEEP_DAYS=14

log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG"; }

mkdir -p "$BACKUP_DIR"
log "开始 MySQL 全量备份..."

# 使用 ~/.my.cnf 存储密码（避免明文出现在命令行）
# ~/.my.cnf 内容：[client]
password=your_password
mysqldump   --single-transaction      # InnoDB 热备，不锁表
  --routines                # 包含存储过程和函数
  --triggers                # 包含触发器
  --events                  # 包含事件调度
  --all-databases           # 备份所有数据库
  | gzip -9 > "${BACKUP_DIR}/mysql_all_${TIMESTAMP}.sql.gz"

log "备份完成：${BACKUP_DIR}/mysql_all_${TIMESTAMP}.sql.gz（$(du -sh ${BACKUP_DIR}/mysql_all_${TIMESTAMP}.sql.gz | cut -f1)）"

# 清理超过保留期的旧备份
find "$BACKUP_DIR" -name "*.sql.gz" -mtime "+${KEEP_DAYS}" -delete
log "已清理 ${KEEP_DAYS} 天前的旧备份"

# 添加到 Crontab（每天凌晨 2:30 自动执行）：
# 30 2 * * * /usr/local/bin/db-backup.sh

# ── pg_dump：备份单个 PostgreSQL 数据库 ──────────────────────────────────────
sudo -u postgres pg_dump myapp | gzip > /var/backups/pg/myapp_$(date +%Y%m%d).sql.gz

# ── pg_dumpall：备份所有数据库（含角色和全局设置）────────────────────────────
sudo -u postgres pg_dumpall | gzip > /var/backups/pg/all_$(date +%Y%m%d).sql.gz

# ── 恢复备份 ──────────────────────────────────────────────────────────────────
# 恢复单库（先创建空数据库，再导入）
sudo -u postgres createdb myapp_restored
gunzip -c /var/backups/pg/myapp_20260316.sql.gz | sudo -u postgres psql myapp_restored

# 恢复全部（pg_dumpall 格式）
gunzip -c /var/backups/pg/all_20260316.sql.gz | sudo -u postgres psql postgres

# ── 验证备份完整性 ────────────────────────────────────────────────────────────
# 检查备份文件是否可以解压（不解压完整文件，只测试头部）
gzip -t /var/backups/pg/myapp_20260316.sql.gz && echo "备份文件完好"