21 - 数据库服务基础（MariaDB / PostgreSQL）

前提条件

已完成 Web 服务配置（Apache2 或 Nginx 已运行）
以 root 身份登录，或 ops 用户执行 sudo -i 切换至 root 环境
系统运行在 Debian 12 Bookworm
/mnt/lvmdata 或 /data 目录可用（建议数据库数据文件存放在独立 LVM 卷上）
防火墙规则：默认不对外开放数据库端口，仅本机或应用层访问

全局参数

主机名 linuxdc，IP 192.168.1.100/24，SSH 端口 2222，运维用户 ops

MariaDB 与 PostgreSQL 对比

维度	MariaDB	PostgreSQL
MySQL 兼容性	高度兼容	不兼容 MySQL 语法
标准 SQL 遵守度	中等	高（严格遵循 SQL 标准）
JSON 支持	基础支持	原生 JSONB，功能完整
全文搜索	支持	支持，功能更强
扩展生态	中等	丰富（PostGIS、TimescaleDB 等）
推荐场景	WordPress、LAMP 栈、中小型 Web 应用	复杂查询、地理数据、企业级应用

详细步骤

1. 安装 MariaDB

apt update
apt install -y mariadb-server mariadb-client
systemctl enable --now mariadb
systemctl status mariadb

安全初始化（首次安装必须执行）：

mysql_secure_installation

按提示操作建议：

提示问题	建议选择	原因
Enter current password for root	直接回车	Debian 默认 root 无密码
Switch to unix_socket authentication	`n`	保留密码认证方式
Change the root password	`y`	设置强密码
Remove anonymous users	`y`	删除匿名账户
Disallow root login remotely	`y`	禁止 root 远程登录
Remove test database	`y`	删除测试数据库
Reload privilege tables now	`y`	立即生效

检查点

mysql -u root -p 能以 root 身份登录 MariaDB 控制台，show databases; 显示系统数据库列表。

创建应用数据库与用户：

mysql -u root -p

-- 创建数据库（UTF8MB4 支持完整 Unicode 含 emoji）
CREATE DATABASE testdb
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- 创建应用用户（只允许本机连接）
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPass123!';

-- 授予最小必要权限
GRANT ALL PRIVILEGES ON testdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

-- 验证
SHOW GRANTS FOR 'appuser'@'localhost';
EXIT;

测试连接：

mysql -u appuser -p testdb -e "SELECT 1 AS test;"

检查点

命令输出 test: 1，无连接错误。

2. 安装 PostgreSQL

apt install -y postgresql postgresql-contrib
systemctl enable --now postgresql
systemctl status postgresql

创建数据库与用户：

# 切换到 postgres 系统用户后进入 psql 控制台
su - postgres -c "psql"

-- 创建应用用户
CREATE USER appuser WITH ENCRYPTED PASSWORD 'StrongPass123!';

-- 创建数据库并指定属主
CREATE DATABASE testdb OWNER appuser;

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE testdb TO appuser;

-- 验证
\du         -- 列出所有用户
\l          -- 列出所有数据库
\q

测试连接：

psql -U appuser -d testdb -h 127.0.0.1 -W -c "SELECT 1;"

检查点

命令输出 ?column?: 1，认证成功。

3. 数据目录迁移至 LVM 卷（生产推荐）

将数据文件从系统盘迁移到独立 LVM 逻辑卷，实现数据与系统的物理隔离。

MariaDB 数据目录迁移：

# 停止服务
systemctl stop mariadb

# 同步数据（保留权限和属主）
rsync -av /var/lib/mysql/ /data/mysql/

# 修改配置
vim /etc/mysql/mariadb.conf.d/50-server.cnf

修改 datadir 行：

datadir = /data/mysql

# 修正权限
chown -R mysql:mysql /data/mysql

# 启动并验证
systemctl start mariadb
mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';"

检查点

SHOW VARIABLES LIKE 'datadir' 输出 /data/mysql/，服务状态 active (running)。

PostgreSQL 数据目录迁移：

systemctl stop postgresql

# 确认当前版本（Debian 12 默认为 15）
pg_lsclusters

rsync -av /var/lib/postgresql/ /data/postgresql/
chown -R postgres:postgres /data/postgresql

vim /etc/postgresql/15/main/postgresql.conf

修改：

data_directory = '/data/postgresql/15/main'

systemctl start postgresql
su - postgres -c "psql -c 'SHOW data_directory;'"

4. 远程访问配置（生产慎用）

安全原则

数据库端口（3306 / 5432）不应对公网开放。如需远程管理，强烈建议通过 SSH 隧道访问，而非直接开放端口。

若确需局域网远程访问，以下为最小化配置：

MariaDB：

vim /etc/mysql/mariadb.conf.d/50-server.cnf
# 将 bind-address 改为：
# bind-address = 192.168.1.100

-- 创建允许从特定 IP 连接的用户
CREATE USER 'appuser'@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'StrongPass123!';
GRANT ALL PRIVILEGES ON testdb.* TO 'appuser'@'192.168.1.0/255.255.255.0';
FLUSH PRIVILEGES;

PostgreSQL：

vim /etc/postgresql/15/main/postgresql.conf
# listen_addresses = '192.168.1.100'

vim /etc/postgresql/15/main/pg_hba.conf
# 在文件末尾添加（md5 = 密码认证）：
# host    testdb    appuser    192.168.1.0/24    md5

# 防火墙放行（仅限内网网段，不对外）
nft add rule inet filter input ip saddr 192.168.1.0/24 tcp dport 3306 ct state new accept
nft list ruleset > /etc/nftables.conf

5. 备份

# MariaDB 全量逻辑备份
mkdir -p /backup/db
mysqldump -u root -p --all-databases --single-transaction \
    > /backup/db/mariadb-full-$(date +%Y%m%d).sql

# PostgreSQL 全量备份
su - postgres -c "pg_dumpall" > /backup/db/postgres-full-$(date +%Y%m%d).sql

# 验证备份文件可读
head -5 /backup/db/mariadb-full-$(date +%Y%m%d).sql

实践任务

安装 MariaDB，执行 mysql_secure_installation，创建 testdb 和 appuser，测试连接
安装 PostgreSQL，创建同名数据库和用户，用 psql 测试连接
将 MariaDB 数据目录迁移至 /data/mysql，重启服务后用 SHOW VARIABLES LIKE 'datadir' 确认
用 mysqldump / pg_dumpall 导出数据库到 /backup/db，验证备份文件头部内容正常

自测问题

1. mysql_secure_installation 脚本主要做了哪些安全加固？

依次执行以下操作：为 root 账户设置强密码（替换空密码或弱密码）；删除安装时自动创建的匿名用户（''@localhost）；禁止 root 账户从远程主机登录（仅保留 root@localhost）；删除 test 测试数据库及其访问权限；刷新权限表使所有变更立即生效。这五项操作消除了 MariaDB 默认安装后最常见的安全弱点。

2. PostgreSQL 中 pg_hba.conf 文件的作用是什么？

pg_hba.conf（Host-Based Authentication）是 PostgreSQL 的客户端认证规则文件，按行定义：连接类型（本地 socket / 主机）、目标数据库、目标用户、来源地址、认证方法（trust / md5 / scram-sha-256 / peer 等）。认证时从上到下逐行匹配，第一条匹配规则生效。例如 host testdb appuser 192.168.1.0/24 md5 表示：来自 192.168.1.0/24 网段的主机，以 appuser 身份连接 testdb 数据库时，采用 MD5 密码认证。

3. 为什么生产环境建议将数据库数据目录迁移到独立 LVM 卷？

独立 LVM 卷提供以下生产优势：磁盘空间独立配额，数据库增长不影响系统盘，系统盘写满也不会导致数据库崩溃；可对数据卷单独创建 LVM 快照，在不停服的情况下获得一致性备份；数据卷可独立扩容（lvextend + xfs_growfs），无需迁移数据；故障时可将数据卷挂载到其他系统快速恢复，缩短 RTO。

4. 如何让 MariaDB / PostgreSQL 只监听本地（不接受远程连接）？

MariaDB 在 /etc/mysql/mariadb.conf.d/50-server.cnf 中确认：

bind-address = 127.0.0.1

PostgreSQL 在 /etc/postgresql/15/main/postgresql.conf 中设置：

listen_addresses = 'localhost'

两者修改后均需重启服务生效。防火墙层面同时确保 3306 / 5432 端口无对外放行规则，形成双重保障。

总结

MariaDB 适合快速部署的 Web 应用，PostgreSQL 适合需要复杂查询和严格事务的业务场景。本章完成了数据库安全初始化、应用用户隔离、数据目录分离和基础备份，为后续 Web 应用联动（如 WordPress、Django）奠定基础。