📚 课程目录第4章数据库环境配置

4. 数据库环境配置：MySQL/PostgreSQL安装、主从复制配置、数据库初始化与权限管理

数据库是服务目录平台的持久化核心。本章围绕 MySQL 与 PostgreSQL 两种主流关系型数据库，讲解从安装部署、主从复制搭建到初始化建库与权限管控的完整流程。生产环境要求数据库具备高可用、数据一致性与最小权限原则，本章内容直接支撑后续服务目录元数据存储与业务数据读写。

4.1 数据库选型对比与安装准备

服务目录平台对数据库的核心需求包括：事务支持、SQL 标准兼容、主从复制能力、运维工具链成熟度。MySQL 与 PostgreSQL 在以下维度存在差异：

对比维度	MySQL 8.0+	PostgreSQL 15+
事务隔离级别	REPEATABLE READ（默认）	READ COMMITTED（默认）
复制方式	异步/半同步/组复制	流复制（同步/异步）
JSON 支持	原生 JSON 类型 + 索引	JSONB 类型 + GIN 索引
扩展性	分区表、InnoDB 集群	表继承、分区表、逻辑复制
运维工具	MySQL Shell、Orchestrator	pgAdmin、Patroni

选型建议：若平台已有 MySQL 生态或对读写性能要求极高，优先选择 MySQL 8.0；若需要复杂查询、地理空间数据或严格 ACID 合规，推荐 PostgreSQL。本章两种数据库的安装与配置均以 CentOS 7/8 环境为例。

4.2 MySQL 安装与基础配置

4.2.1 二进制包安装（以 MySQL 8.0.33 为例）

# 下载并解压
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
ln -s /usr/local/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql

# 创建 mysql 用户和组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

# 初始化数据目录
mkdir -p /data/mysql/data
chown -R mysql:mysql /data/mysql
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data

# 启动并设置开机自启
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start

初始化密码：初始化完成后，日志中会输出临时 root 密码，务必立即记录并登录后修改。使用 grep 'temporary password' /data/mysql/data/error.log 查看。

4.2.2 安全加固与参数调优

# 执行安全安装脚本
/usr/local/mysql/bin/mysql_secure_installation

# 核心参数配置（/etc/my.cnf）
[mysqld]
innodb_buffer_pool_size = 2G          # 物理内存的 60%-70%
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1    # 双1模式保障数据安全
sync_binlog = 1
binlog_format = ROW
server-id = 1
log_bin = /data/mysql/binlog/mysql-bin

4.3 PostgreSQL 安装与基础配置

4.3.1 YUM 源安装（以 PostgreSQL 15 为例）

# 安装官方 YUM 源
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 安装服务端与客户端
yum install -y postgresql15-server postgresql15-contrib

# 初始化数据库
/usr/pgsql-15/bin/postgresql-15-setup initdb

# 启动并设置开机自启
systemctl enable postgresql-15
systemctl start postgresql-15

4.3.2 访问控制与参数调整

# 修改 postgres 用户密码
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'StrongPass123!';"

# 核心参数配置（/var/lib/pgsql/15/data/postgresql.conf）
listen_addresses = '*'
max_connections = 200
shared_buffers = 1GB                  # 物理内存的 25%
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1024                  # MB

# 客户端认证配置（pg_hba.conf）
host    all             all             0.0.0.0/0               scram-sha-256

关键差异：PostgreSQL 使用 pg_hba.conf 控制客户端认证，修改后需执行 pg_ctl reload 或 systemctl reload postgresql-15 生效，无需重启服务。

4.4 主从复制配置

主从复制是数据库高可用架构的基础。以下分别给出 MySQL 异步复制与 PostgreSQL 流复制的配置步骤。

4.4.1 MySQL 主从复制

主库配置（server-id=1）：

-- 创建复制用户
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

-- 查看主库状态
SHOW MASTER STATUS\G

从库配置（server-id=2）：

CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='ReplPass123!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;

-- 检查复制状态
SHOW SLAVE STATUS\G
-- 确保 Slave_IO_Running: Yes 且 Slave_SQL_Running: Yes

4.4.2 PostgreSQL 流复制

主库配置：

# postgresql.conf
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1024

# pg_hba.conf 添加从库复制权限
host    replication     repl            192.168.1.20/32        scram-sha-256

# 创建复制用户
CREATE USER repl WITH REPLICATION PASSWORD 'ReplPass123!';

从库配置（使用 pg_basebackup 同步）：

# 停止从库服务
systemctl stop postgresql-15

# 清空从库数据目录
rm -rf /var/lib/pgsql/15/data/*

# 从主库拉取基础备份
pg_basebackup -h 192.168.1.10 -U repl -D /var/lib/pgsql/15/data/ -P -v -R

# 启动从库
systemctl start postgresql-15

# 检查复制状态
SELECT * FROM pg_stat_replication;

复制验证：在主库创建测试表并插入数据，在从库执行 SELECT * FROM test_table; 确认数据已同步。MySQL 使用 SHOW SLAVE STATUS\G 查看延迟秒数（Seconds_Behind_Master），PostgreSQL 使用 pg_stat_replication 视图查看 write_lag/flush_lag/replay_lag。

4.5 数据库初始化与权限管理

服务目录平台需要独立的数据库、业务用户以及按需分配的权限。以下给出标准初始化脚本与权限模型。

4.5.1 MySQL 初始化

-- 创建数据库与业务用户
CREATE DATABASE service_catalog
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE USER 'sc_app'@'192.168.1.%' IDENTIFIED BY 'AppPass456!';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX ON service_catalog.* TO 'sc_app'@'192.168.1.%';

-- 创建只读用户供监控使用
CREATE USER 'sc_readonly'@'192.168.1.%' IDENTIFIED BY 'ReadOnly789!';
GRANT SELECT ON service_catalog.* TO 'sc_readonly'@'192.168.1.%';

FLUSH PRIVILEGES;

4.5.2 PostgreSQL 初始化

-- 创建数据库与业务用户
CREATE DATABASE service_catalog;
CREATE USER sc_app WITH PASSWORD 'AppPass456!';
GRANT CONNECT ON DATABASE service_catalog TO sc_app;
GRANT USAGE ON SCHEMA public TO sc_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO sc_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO sc_app;

-- 创建只读用户
CREATE USER sc_readonly WITH PASSWORD 'ReadOnly789!';
GRANT CONNECT ON DATABASE service_catalog TO sc_readonly;
GRANT USAGE ON SCHEMA public TO sc_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sc_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO sc_readonly;

权限最小化原则：业务应用账户仅授予 DML 权限（SELECT/INSERT/UPDATE/DELETE），不授予 DDL 权限（如 DROP/ALTER）。数据库迁移或表结构变更应使用专用的管理员账户，避免应用账户误操作。PostgreSQL 中务必使用 ALTER DEFAULT PRIVILEGES 确保未来新建的表自动继承权限。

4.6 数据库环境配置总览

下图展示了数据库环境配置的完整流程，从安装部署到主从复制，再到初始化与权限管理，形成可复用的自动化基线。

4.7 常见故障排查

故障现象	可能原因	排查命令与解决
MySQL 从库 IO 线程为 No	网络不通 / 复制用户权限不足 / binlog 位置错误	`SHOW SLAVE STATUS\G` 查看 Last_IO_Error；`ping` 测试连通性；重新执行 CHANGE MASTER
PostgreSQL 流复制延迟高	网络带宽不足 / 从库磁盘 I/O 瓶颈 / wal_keep_size 过小	`pg_stat_replication` 查看 replay_lag；`iostat -x 1` 检查磁盘；增大 wal_keep_size
应用连接报权限拒绝	用户密码错误 / host 限制 / 未执行 FLUSH PRIVILEGES	MySQL: `SELECT user,host FROM mysql.user;`；PostgreSQL: 检查 pg_hba.conf 条目顺序
数据库初始化后无法启动	数据目录权限错误 / 配置文件语法错误 / 端口冲突	检查 error.log；`mysqld --validate-config`；`netstat -tlnp \| grep 3306`

自动化故障自愈思路：在自动化脚本中加入复制状态巡检逻辑，当检测到 Slave_IO_Running 或 Slave_SQL_Running 不为 Yes 时，自动触发重连或重建从库流程。PostgreSQL 可结合 Patroni 实现自动故障转移，无需人工介入。