📚 课程目录第4章数据库准备

4. 数据库准备：MySQL/PostgreSQL 安装与配置、创建基线核验专用数据库与用户、初始化表结构

基线核验环境的核心是持久化存储层。Template 生成引擎在运行过程中需要记录模板版本、校验规则、执行日志以及基线快照。本章将指导你完成数据库选型、安装配置、专用库与用户创建，以及核心表结构的初始化。

4.1 数据库选型对比

平台门户支持两种主流关系型数据库：MySQL 8.0+ 与 PostgreSQL 15+。下表从基线核验场景的关键维度进行对比，帮助你根据现有基础设施做出选择。

对比维度	MySQL 8.0+	PostgreSQL 15+
JSON 支持	原生 JSON 类型，索引支持较弱	原生 JSONB 类型，支持 GIN 索引，查询性能更优
全文检索	内置全文索引，适合简单搜索	tsvector/tsquery，支持中文分词扩展
并发写入	MVCC 实现，默认行级锁	MVCC + 快照隔离，高并发下写入更稳定
扩展性	主从复制、InnoDB Cluster	流复制、逻辑复制、Citus 分布式
运维成熟度	社区广泛，工具链丰富	适合复杂查询与数据完整性要求高的场景
基线核验推荐	标准场景，快速部署	复杂校验逻辑、JSON 模板存储场景

选型建议：如果团队已有 MySQL 运维体系且基线校验逻辑相对简单，优先使用 MySQL。如果模板结构复杂、需要频繁进行 JSON 字段内的条件查询，推荐 PostgreSQL + JSONB 组合。

4.2 数据库安装与基础配置

4.2.1 MySQL 安装与配置

以下步骤基于 CentOS 7/8 环境，使用官方 YUM 仓库安装 MySQL 8.0。

# 1. 安装 MySQL 8.0 仓库
rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

# 2. 安装 MySQL 服务
yum install -y mysql-community-server

# 3. 启动并设置开机自启
systemctl start mysqld
systemctl enable mysqld

# 4. 获取临时 root 密码
grep 'temporary password' /var/log/mysqld.log

# 5. 执行安全配置向导
mysql_secure_installation

基础配置项（/etc/my.cnf 关键参数）：

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
max_connections = 500
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

4.2.2 PostgreSQL 安装与配置

以下步骤基于 CentOS 7/8 环境，使用官方 YUM 仓库安装 PostgreSQL 15。

# 1. 安装 PostgreSQL 15 仓库
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 2. 安装 PostgreSQL 15
yum install -y postgresql15-server

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

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

基础配置项（/var/lib/pgsql/15/data/postgresql.conf 关键参数）：

listen_addresses = '*'
max_connections = 500
shared_buffers = 2GB
work_mem = 64MB
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1GB

安全注意：生产环境不要直接使用 root 或 postgres 超级用户连接应用。必须创建专用数据库和用户，并遵循最小权限原则。

4.3 创建基线核验专用数据库与用户

无论选择 MySQL 还是 PostgreSQL，都需要创建独立的数据库和用户。以下分别给出两种数据库的 SQL 脚本。

4.3.1 MySQL 创建脚本

-- 创建基线核验专用数据库
CREATE DATABASE baseline_verify
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- 创建专用用户，密码请替换为强密码
CREATE USER 'bv_user'@'%' IDENTIFIED BY 'YourStrongPassword!2024';

-- 授予数据库全部权限
GRANT ALL PRIVILEGES ON baseline_verify.* TO 'bv_user'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

-- 验证
SHOW DATABASES LIKE 'baseline_verify';
SELECT user, host FROM mysql.user WHERE user = 'bv_user';

4.3.2 PostgreSQL 创建脚本

-- 切换到 postgres 用户执行
sudo -u postgres psql

-- 创建基线核验专用数据库
CREATE DATABASE baseline_verify
  ENCODING 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8'
  TEMPLATE template0;

-- 创建专用用户，密码请替换为强密码
CREATE USER bv_user WITH PASSWORD 'YourStrongPassword!2024';

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE baseline_verify TO bv_user;

-- 授予 schema 使用权限
\c baseline_verify
CREATE SCHEMA IF NOT EXISTS baseline;
GRANT USAGE, CREATE ON SCHEMA baseline TO bv_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA baseline TO bv_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA baseline GRANT ALL ON TABLES TO bv_user;

-- 验证
\l baseline_verify
\du bv_user

  权限说明：PostgreSQL 中 GRANT ALL PRIVILEGES ON DATABASE 并不包含 schema 内部对象的权限，必须额外对 schema 和默认权限进行授权，否则应用无法建表或写入数据。

4.4 初始化表结构

基线核验环境的核心表结构围绕 模板定义、校验规则、执行记录 和 基线快照 四个主题设计。以下给出跨数据库兼容的 DDL 脚本。

4.4.1 模板定义表（template_def）

CREATE TABLE template_def (
    id              BIGSERIAL PRIMARY KEY,
    template_name   VARCHAR(128) NOT NULL,
    template_version VARCHAR(32) NOT NULL,
    template_type   VARCHAR(32) NOT NULL DEFAULT 'standard',
    content_json    JSONB NOT NULL,
    md5_hash        VARCHAR(64) NOT NULL,
    status          VARCHAR(16) NOT NULL DEFAULT 'active',
    created_by      VARCHAR(64) NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (template_name, template_version)
);

COMMENT ON TABLE template_def IS '模板定义表，存储引擎使用的模板元数据与内容';
COMMENT ON COLUMN template_def.content_json IS '模板内容，JSONB 格式存储';
COMMENT ON COLUMN template_def.md5_hash IS '模板内容的 MD5 哈希，用于完整性校验';

4.4.2 校验规则表（verify_rule）

CREATE TABLE verify_rule (
    id              BIGSERIAL PRIMARY KEY,
    rule_code       VARCHAR(64) NOT NULL UNIQUE,
    rule_name       VARCHAR(128) NOT NULL,
    rule_type       VARCHAR(32) NOT NULL DEFAULT 'field_check',
    severity        VARCHAR(16) NOT NULL DEFAULT 'error',
    rule_expression JSONB NOT NULL,
    rule_desc       TEXT,
    is_enabled      BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE verify_rule IS '校验规则表，定义基线核验的检查项';
COMMENT ON COLUMN verify_rule.rule_expression IS '规则表达式，JSONB 格式，支持嵌套条件';

4.4.3 执行记录表（execution_log）

CREATE TABLE execution_log (
    id              BIGSERIAL PRIMARY KEY,
    template_id     BIGINT NOT NULL REFERENCES template_def(id),
    rule_id         BIGINT NOT NULL REFERENCES verify_rule(id),
    exec_uuid       VARCHAR(64) NOT NULL UNIQUE,
    exec_status     VARCHAR(16) NOT NULL DEFAULT 'pending',
    total_checks    INT NOT NULL DEFAULT 0,
    passed_checks   INT NOT NULL DEFAULT 0,
    failed_checks   INT NOT NULL DEFAULT 0,
    error_message   TEXT,
    started_at      TIMESTAMP,
    finished_at     TIMESTAMP,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_exec_log_template ON execution_log(template_id);
CREATE INDEX idx_exec_log_status ON execution_log(exec_status);

COMMENT ON TABLE execution_log IS '执行记录表，记录每次基线核验的执行情况';

4.4.4 基线快照表（baseline_snapshot）

CREATE TABLE baseline_snapshot (
    id              BIGSERIAL PRIMARY KEY,
    exec_id         BIGINT NOT NULL REFERENCES execution_log(id),
    snapshot_data   JSONB NOT NULL,
    snapshot_hash   VARCHAR(64) NOT NULL,
    data_version    INT NOT NULL DEFAULT 1,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_snapshot_exec ON baseline_snapshot(exec_id);

COMMENT ON TABLE baseline_snapshot IS '基线快照表，存储每次核验通过后的完整基线数据';

4.5 数据库初始化流程总览

下图展示了从数据库安装到表结构初始化的完整流程，以及各步骤之间的依赖关系。

4.6 连接验证

表结构初始化完成后，使用以下命令验证数据库是否可正常连接并查询表信息。

4.6.1 MySQL 验证

mysql -h 127.0.0.1 -u bv_user -p baseline_verify -e "SHOW TABLES;"
mysql -h 127.0.0.1 -u bv_user -p baseline_verify -e "DESC template_def;"

4.6.2 PostgreSQL 验证

PGPASSWORD='YourStrongPassword!2024' psql -h 127.0.0.1 -U bv_user -d baseline_verify -c "\dt baseline.*"
PGPASSWORD='YourStrongPassword!2024' psql -h 127.0.0.1 -U bv_user -d baseline_verify -c "\d baseline.template_def"

验证通过标志：成功列出 4 张核心表（template_def、verify_rule、execution_log、baseline_snapshot），且无权限报错，即表示数据库准备阶段完成。