21. 数据库服务基础

本章介绍 RHEL 9 上 MariaDB 和 PostgreSQL 两大主流关系型数据库的安装、安全初始化、用户与权限管理、性能参数调优，以及备份恢复与自动化脚本配置。数据库是业务应用数据持久化的核心组件，其安全加固与性能优化直接影响业务可用性。

21.1 前提条件

已完成第三阶段全部配置（第 15–19 章）。
通过 SecureCRT（SSH 端口 2222）以 ops 用户、密钥认证方式登录 192.168.1.100，执行 sudo -i 切换至 root 环境。
SELinux 处于 Enforcing 模式，firewalld 已启用（第 12 章）。
备份目录已存在（第 17 章）：/backup。

本章全局参数

参数	值
主机名 / IP	`linuxdc` / `192.168.1.100`
允许远程访问网段	`192.168.1.0/24`
应用数据库名	`appdb`
应用数据库用户	`appuser`
备份目录	`/backup`
自动备份执行时间	每日 01:30

21.2 MariaDB 配置

21.2.1 安装与启动

dnf install -y mariadb-server
systemctl enable --now mariadb
systemctl status mariadb

防火墙（仅在需要远程访问时开放，生产环境应限制源 IP）：

firewall-cmd --permanent --add-rich-rule='
  rule family="ipv4"
  source address="192.168.1.0/24"
  service name="mysql"
  accept'
firewall-cmd --reload

21.2.2 安全初始化

mariadb-secure-installation

交互式向导依次完成以下安全加固项：

项目	推荐选择	说明
设置 root 密码	是	设置强密码（≥12 位，含大小写、数字、符号）
移除匿名用户	是（Y）	消除无需认证的访问入口
禁止 root 远程登录	是（Y）	root 账户仅允许本机访问
移除测试数据库	是（Y）	删除默认 `test` 库及其匿名权限
刷新权限表	是（Y）	使上述变更立即生效

检查点

mariadb -u root -p 输入密码后成功进入交互式 shell。

21.2.3 用户与权限管理

mariadb -u root -p

在 MariaDB 提示符下执行：

-- 创建应用数据库（UTF-8 全字符集，支持 Emoji）
CREATE DATABASE appdb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- 创建本机应用用户
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'App@Pass2025!';

-- 授予数据库完整权限
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';

-- 若需允许内网远程连接（按需配置）
-- CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'App@Pass2025!';
-- GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'192.168.1.%';

FLUSH PRIVILEGES;

-- 验证权限
SHOW GRANTS FOR 'appuser'@'localhost';

EXIT;

验证新用户可正常登录：

mariadb -u appuser -p appdb -e "SHOW TABLES;"

21.2.4 性能参数调优

vim /etc/my.cnf.d/mariadb-server.cnf

在 [mysqld] 节中添加（根据实际内存调整，以 4 GB 内存服务器为例）：

[mysqld]
# InnoDB 缓冲池（建议为可用内存的 50–70%）
innodb_buffer_pool_size     = 2G
innodb_buffer_pool_instances = 2

# Redo 日志
innodb_log_file_size        = 256M

# 事务提交刷盘策略
# 1=每次提交均刷盘（最安全，默认推荐）
# 2=每秒刷盘（性能高，断电可能丢失 1 秒内事务）
innodb_flush_log_at_trx_commit = 1

# 连接数限制
max_connections             = 200
wait_timeout                = 300
interactive_timeout         = 300

# 慢查询日志（生产必备，用于排查性能问题）
slow_query_log              = ON
slow_query_log_file         = /var/log/mariadb/slow.log
long_query_time             = 2
log_queries_not_using_indexes = ON

systemctl restart mariadb

# 验证关键参数已生效
mariadb -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mariadb -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log';"

21.2.5 备份与恢复

配置凭证文件（避免命令行密码暴露）：

tee /root/.my.cnf << 'EOF'
[client]
user=root
password=<root密码>
EOF

chmod 600 /root/.my.cnf

执行备份：

# 备份单个数据库（--single-transaction 对 InnoDB 无锁热备）
mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  appdb > /backup/appdb-$(date +%F).sql

# 备份所有数据库
mysqldump \
  --all-databases \
  --single-transaction \
  > /backup/mariadb-all-$(date +%F).sql

# 验证备份文件完整性
ls -lh /backup/appdb-$(date +%F).sql
head -5 /backup/appdb-$(date +%F).sql

恢复：

# 恢复到指定数据库
mariadb -u root -p -e "CREATE DATABASE appdb_restore;"
mariadb -u root -p appdb_restore < /backup/appdb-$(date +%F).sql

# 验证恢复结果
mariadb -u root -p appdb_restore -e "SHOW TABLES;"

mysqldump 密码安全规范

禁止在命令行或脚本中直接写入 -p密码（如 -psecret），密码会暴露在进程列表（ps aux）和 shell 历史文件（~/.bash_history）中。正确做法是使用 /root/.my.cnf 文件存储凭证，权限设为 600。

21.3 PostgreSQL 配置

21.3.1 安装与初始化

dnf install -y postgresql-server postgresql-contrib

# 初始化数据库集群（首次安装必须执行，创建基础数据目录）
postgresql-setup --initdb

systemctl enable --now postgresql
systemctl status postgresql

防火墙（按需开放，同样建议限制源 IP）：

firewall-cmd --permanent --add-rich-rule='
  rule family="ipv4"
  source address="192.168.1.0/24"
  service name="postgresql"
  accept'
firewall-cmd --reload

21.3.2 设置管理员密码

# 以 postgres 系统用户身份设置数据库密码
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'Pg@Admin2025!';"

21.3.3 配置客户端认证（pg_hba.conf）

vim /var/lib/pgsql/data/pg_hba.conf

将本地连接和远程连接的认证方式由 ident 改为 md5（或更安全的 scram-sha-256）：

# TYPE  DATABASE  USER      ADDRESS             METHOD
local   all       all                           md5
host    all       all       127.0.0.1/32        md5
host    all       all       ::1/128             md5
host    all       all       192.168.1.0/24      md5

systemctl restart postgresql

# 验证密码认证可正常登录
psql -U postgres -h 127.0.0.1 -W -c "\l"

21.3.4 用户与权限管理

sudo -u postgres psql

在 psql 提示符下执行：

-- 创建应用数据库
CREATE DATABASE appdb
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE   'en_US.UTF-8';

-- 创建应用用户
CREATE USER appuser WITH PASSWORD 'App@Pass2025!';

-- 授予数据库完整权限
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

-- 查看用户列表
\du

\q

21.3.5 性能参数调优

vim /var/lib/pgsql/data/postgresql.conf

关键参数（以 4 GB 内存服务器为例）：

# 共享缓冲区（建议为总内存的 25%）
shared_buffers           = 1GB

# 单个排序 / 哈希操作可用内存
work_mem                 = 64MB

# 维护操作内存（VACUUM、CREATE INDEX 等）
maintenance_work_mem     = 256MB

# 有效缓存大小（用于查询规划器估算，设为总内存的 50–75%）
effective_cache_size     = 3GB

# WAL 提交策略
synchronous_commit       = on    # on=最安全，off=高性能但可能丢失少量数据

# 最大连接数
max_connections          = 200

# 慢查询日志（记录超过 2 秒的查询，单位 ms）
log_min_duration_statement = 2000

# 预写日志（WAL）配置
wal_level                = replica
max_wal_size             = 1GB

systemctl restart postgresql

# 验证参数
sudo -u postgres psql -c "SHOW shared_buffers;"
sudo -u postgres psql -c "SHOW work_mem;"

21.3.6 备份与恢复

# 备份单个数据库
sudo -u postgres pg_dump appdb > /backup/pgdb-$(date +%F).sql

# 备份所有数据库（含角色、表空间等全局对象）
sudo -u postgres pg_dumpall > /backup/pgdb-all-$(date +%F).sql

# 验证备份文件
head -5 /backup/pgdb-$(date +%F).sql

恢复：

sudo -u postgres createdb appdb_restore
sudo -u postgres psql appdb_restore < /backup/pgdb-$(date +%F).sql

# 验证恢复结果
sudo -u postgres psql appdb_restore -c "\dt"

21.4 自动化数据库备份

tee /usr/local/bin/db-backup.sh << 'EOF'
#!/bin/bash
# 数据库自动备份脚本
# MariaDB 凭证通过 /root/.my.cnf 读取（无需明文密码）
set -euo pipefail

BACKUP_DIR="/backup"
DATE=$(date +%F)
LOG="/var/log/db-backup.log"

echo "$(date '+%F %T'): 开始数据库备份" >> "$LOG"

# MariaDB 全库备份
mysqldump --single-transaction --all-databases \
  > "${BACKUP_DIR}/mariadb-all-${DATE}.sql" 2>> "$LOG"
echo "$(date '+%F %T'): MariaDB 备份完成" >> "$LOG"

# PostgreSQL 全库备份
sudo -u postgres pg_dumpall \
  > "${BACKUP_DIR}/pgdb-all-${DATE}.sql" 2>> "$LOG"
echo "$(date '+%F %T'): PostgreSQL 备份完成" >> "$LOG"

# 清理 14 天前的旧备份
find "${BACKUP_DIR}" -name "*db*-*.sql" -mtime +14 -delete

echo "$(date '+%F %T'): 清理完成，备份目录: ${BACKUP_DIR}" >> "$LOG"
EOF

chmod +x /usr/local/bin/db-backup.sh

注册 systemd timer（每日凌晨 1:30 执行）：

tee /etc/systemd/system/db-backup.service << 'EOF'
[Unit]
Description=Database Automated Backup
After=mariadb.service postgresql.service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/db-backup.sh
StandardOutput=journal
StandardError=journal
EOF

tee /etc/systemd/system/db-backup.timer << 'EOF'
[Unit]
Description=Database Backup Daily Timer
Requires=db-backup.service

[Timer]
OnCalendar=*-*-* 01:30:00
Persistent=true

[Install]
WantedBy=timers.target
EOF

systemctl daemon-reload
systemctl enable --now db-backup.timer

# 验证 timer 已注册
systemctl list-timers | grep db-backup

手动触发测试：

systemctl start db-backup.service
journalctl -u db-backup.service -n 20
tail -20 /var/log/db-backup.log

21.5 常见问题与排查

问题现象	排查步骤
MariaDB 启动失败	`journalctl -u mariadb -n 30`；检查 `/var/log/mariadb/mariadb.log`
PostgreSQL 认证失败（`Peer authentication failed`）	检查 `pg_hba.conf` 对应行的认证方式是否已改为 `md5`；重启服务
远程连接被拒绝	MariaDB 检查 `bind-address`；PostgreSQL 检查 `listen_addresses`；确认防火墙规则和 SELinux
`mysqldump` 执行失败	确认 `/root/.my.cnf` 权限为 `600`；检查磁盘空间 `df -hT /backup`
慢查询日志未生成	确认日志目录存在且 mariadb 有写入权限：`ls -ld /var/log/mariadb/`

# MariaDB 状态诊断
mariadb -u root -p -e "SHOW STATUS LIKE 'Threads_connected';"
mariadb -u root -p -e "SHOW FULL PROCESSLIST;"
mariadb -u root -p -e "SHOW ENGINE INNODB STATUS\G" | head -40

# PostgreSQL 状态诊断
sudo -u postgres psql -c "SELECT pid, usename, state, query FROM pg_stat_activity LIMIT 10;"
sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('appdb'));"

21.6 实践任务

登录 linuxdc（SecureCRT 端口 2222，ops 用户，密钥认证，sudo -i 切换 root）。
安装 MariaDB，完成 mariadb-secure-installation，创建 /root/.my.cnf 凭证文件，验证无密码参数可登录。
创建数据库 appdb 和用户 appuser，验证 appuser 可登录并操作 appdb。
配置 MariaDB 性能参数（innodb_buffer_pool_size=2G），重启并验证生效。
安装 PostgreSQL，初始化，修改 pg_hba.conf 启用密码认证，设置管理员密码，验证登录。

对 MariaDB 和 PostgreSQL 各执行一次备份，验证文件存在且格式正确：

head -5 /backup/mariadb-all-$(date +%F).sql
head -5 /backup/pgdb-all-$(date +%F).sql

创建并启用 db-backup.timer，手动触发一次，查看执行日志。

21.7 自测问题

Q1：MariaDB 的 --single-transaction 参数如何实现热备份？

--single-transaction 在备份开始时开启一个可重复读事务，InnoDB 通过 MVCC 机制读取事务开始时刻的一致性快照，整个备份过程不加表锁，业务可以继续正常读写。这是 InnoDB 引擎在线热备的标准方式。注意：该参数仅对 InnoDB 引擎有效，MyISAM 表仍需加锁（--lock-tables）。

Q2：PostgreSQL pg_hba.conf 中 ident 和 md5 认证有何本质区别？

ident 通过查询操作系统的 identd 服务或映射文件匹配系统用户名与数据库用户名，只适用于本地连接，无需密码（依赖操作系统账户安全性）；md5 要求客户端提供经 MD5 哈希（或更新版本使用 scram-sha-256）处理后的密码，适用于本地和网络连接，是生产环境的安全标准选择。

Q3：为什么不能在脚本或命令行中直接写入数据库密码？

密码以明文出现在以下位置均构成安全风险：~/.bash_history（历史记录）、/proc/<pid>/cmdline（其他用户可读取进程参数）、ps aux 输出（其他用户可见）、脚本文件本身（若权限配置不当）。正确做法是 MariaDB 使用 ~/.my.cnf（权限 600），PostgreSQL 使用 ~/.pgpass 文件（权限 600），将凭证从命令行彻底分离。

21.8 章节总结

本章完成了 RHEL 9 上 MariaDB 和 PostgreSQL 的企业级部署：安全初始化（消除匿名用户、测试库等默认风险点）、最小权限原则用户授权、面向实际内存容量的性能关键参数调优，以及基于 systemd timer 的全自动化每日备份（含 14 天轮转清理）。密码安全（凭证文件隔离）和 PostgreSQL 认证配置（pg_hba.conf）是两个最易出错的环节，务必重点掌握。