Linux第20篇：MySQL/PostgreSQL数据库部署与调优：SaaS应用的数据底座

一句话定义：本文系统讲解在Linux生产环境下部署MySQL 8.0与PostgreSQL 16两大开源数据库的完整流程，涵盖安装配置、安全加固、性能调优与备份恢复策略，帮助Java SaaS团队构建稳定高效的数据存储底座。

一、引言：数据库——SaaS应用的“心脏”

任何一个SaaS应用，无论前端界面多么华丽、业务逻辑多么复杂，最终都离不开一个稳定高效的数据库来承载数据。数据库就是应用的“心脏”——它跳动的节奏决定了整个系统的响应速度，它的稳定性决定了业务能否7×24小时不间断运行。

在Java SaaS部署的完整链路中（第17篇Java环境 → 第18篇应用部署 → 第19篇Nginx入口 → 本篇数据库 → 第21篇Redis缓存 → 第22篇Docker容器化），数据库是承接业务数据的核心枢纽。数据库没部署好，前面搭建的所有环境都失去了意义。

本文将带你同时掌握MySQL 8.0和PostgreSQL 16两套主流数据库的Linux生产环境部署方案。为什么两套都讲？因为在真实的SaaS项目中，你可能会遇到不同的技术选型——有的团队偏爱MySQL的简单易用，有的团队青睐PostgreSQL的功能丰富。掌握两套方案，你就能应对各种场景。

二、数据库选型：MySQL还是PostgreSQL？

在动手部署之前，先花3分钟搞清楚选哪个——这个决策会影响到后续整个技术栈的走向。

选型建议：

• 初创团队/快速迭代项目 → MySQL 8.0：配置简单、文档丰富、社区庞大，能快速启动
• 企业级/分析型/需要复杂查询 → PostgreSQL 16：功能更完整（原生JSONB、分区表、逻辑复制、行级安全）
• 不确定？ 两款都是成熟的开源数据库，选哪个都不会错。本文教你两套都搞定。

三、MySQL 8.0 生产环境部署

3.1 安装前的关键准备

为什么这样写：生产环境安装MySQL和开发环境最大的区别在于——配置先行。很多新手上来就yum install，装完发现数据目录在系统盘、密码策略不对、字符集乱码，最后不得不重装。先做这3件事，能省下至少2小时的折腾时间。

踩过的坑：

• 坑1：之前装过MySQL没卸干净，新安装读取旧/etc/my.cnf导致启动失败
• 坑2：没关防火墙，远程死活连不上，还以为是MySQL坏了
• 坑3：直接用root跑MySQL服务，安全隐患巨大

注意事项：

• 确认服务器内存≥4GB（生产环境建议8GB起步）
• 数据目录建议与系统盘分离，放在独立磁盘或挂载点上

代码块：环境检查与清理

# 1. 检查是否已有MySQL/MariaDB
rpm -qa | grep -E 'mysql|mariadb'

# 2. 如有残留，彻底卸载（以CentOS/Rocky为例）
sudo systemctl stop mysqld 2>/dev/null
sudo dnf remove -y mysql* mariadb* 2>/dev/null

# 3. 清理残留配置文件和数据目录（⚠️ 操作前确认无重要数据）
sudo rm -rf /etc/my.cnf /etc/my.cnf.d/
sudo rm -rf /var/lib/mysql/

# 4. 检查端口是否被占用
sudo ss -tlnp | grep 3306

执行后说明：上述命令执行完后，系统应该处于“干净”状态——没有MySQL进程、没有旧配置文件、3306端口空闲。如果ss -tlnp | grep 3306有输出，说明有其他服务占用了3306端口，需要先处理掉。

3.2 安装MySQL 8.0

为什么这样写：MySQL官方提供了YUM/DNF仓库，这是生产环境最推荐的安装方式——能自动处理依赖关系、方便后续升级、来源可靠。下面以Rocky Linux 9 / CentOS Stream 9为例。

踩过的坑：

• 坑：用dnf install mysql装的是MariaDB不是MySQL——CentOS/Rocky默认仓库里是MariaDB，必须用官方仓库
• 坑：忘记启用MySQL模块，导致装成了5.7版本

注意事项：

• 本文以MySQL 8.0.x系列为核心，这是官方长期支持（LTS）版本
• 8.0系列默认启用validate_password组件，强制密码复杂度

代码块：通过官方YUM仓库安装MySQL 8.0

# 1. 添加MySQL官方YUM仓库（适用于Rocky Linux 9 / CentOS 9）
sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm

# 2. 导入GPG密钥（如第一步自动完成则跳过）
sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023

# 3. 安装MySQL服务器
sudo dnf install -y mysql-community-server

# 4. 启动MySQL服务并设置开机自启
sudo systemctl start mysqld
sudo systemctl enable mysqld

# 5. 查看初始临时密码（很重要！）
sudo grep 'temporary password' /var/log/mysqld.log

执行后说明：第5步输出的临时密码一定要记下来，类似2025-07-02T08:15:23.456789Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: %abc123DEF!。如果没记下这个密码，后续登录会非常麻烦。

3.3 安全初始化与密码设置

为什么这样写：MySQL 8.0安装后的第一件事不是建库建表，而是执行安全初始化。这会强制修改root密码、移除匿名用户、禁止远程root登录——每一步都直接关系到数据库的安全。

踩过的坑：

• 坑：设置的密码不符合密码策略（默认要求：至少8位、含大小写字母+数字+特殊字符），被拒绝
• 坑：用弱密码后忘了改策略，后续应用连接频繁报错

注意事项：

• 生产环境建议使用复杂密码，不要图省事
• 如需调整密码策略，可在/etc/my.cnf中配置validate_password相关参数

代码块：执行安全初始化

# 1. 使用临时密码登录MySQL
mysql -u root -p
# （输入上一步获取的临时密码）

# 2. 登录后立即修改root密码（必须符合密码策略）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Your_Strong_P@ssw0rd_2025!';

# 3. 调整密码策略（可选——如需降低复杂度要求）
SET GLOBAL validate_password.policy = MEDIUM;
SET GLOBAL validate_password.length = 8;

# 4. 执行安全初始化脚本（自动移除匿名用户、禁止远程root等）
mysql_secure_installation
# （按提示操作：输入新密码 → 移除匿名用户 → 禁止远程root登录 → 移除测试数据库 → 重载权限表）

执行后说明：mysql_secure_installation是MySQL官方提供的安全加固工具，一路选Y即可。执行完后，MySQL的安装和基础安全配置就完成了。

3.4 配置文件深度优化

为什么这样写：MySQL的默认配置文件（/etc/my.cnf）是为“能用”设计的，不是为“好用”设计的。生产环境必须根据服务器硬件资源进行精细调整。合理的参数配置可使数据库吞吐量提升3-5倍。

踩过的坑：

• 坑：innodb_buffer_pool_size设得太大（超过物理内存80%），导致系统OOM Kill掉MySQL
• 坑：改了配置文件没重启，参数没生效，还纳闷怎么性能没变化
• 坑：改配置前没备份my.cnf，改崩了启动不了

注意事项：

• 改配置前先备份：sudo cp /etc/my.cnf /etc/my.cnf.bak
• 参数值需要根据实际服务器内存调整，以下示例以16GB内存服务器为基准
• 修改后需重启MySQL：sudo systemctl restart mysqld

代码块：生产环境MySQL 8.0核心配置（/etc/my.cnf）

[mysqld]
# ============ 基础设置 ============
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# ============ 连接与线程 ============
max_connections = 500              # 最大连接数
thread_cache_size = 100            # 线程缓存，建议max_connections的25-50%
back_log = 150                     # 高并发场景下增大

# ============ 内存核心：InnoDB缓冲池 ============
innodb_buffer_pool_size = 12G      # 专用DB服务器设为物理内存70-80%
innodb_buffer_pool_instances = 8   # 每个实例至少1GB

# ============ 排序与连接缓存 ============
sort_buffer_size = 4M              # 每个连接独立
join_buffer_size = 4M              # 多表JOIN时使用
tmp_table_size = 64M               # 临时表大小
max_heap_table_size = 64M

# ============ I/O优化 ============
innodb_io_capacity = 2000          # SSD建议2000-4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_log_at_trx_commit = 2 # 性能优先，可接受最多1秒数据丢失
sync_binlog = 1

# ============ 日志设置 ============
slow_query_log = ON                # 开启慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1                # 超过1秒记录
log_queries_not_using_indexes = ON # 记录未使用索引的查询

# ============ 字符集 ============
[client]
default-character-set = utf8mb4

执行后说明：配置完成后重启MySQL，用SHOW VARIABLES LIKE 'innodb_buffer_pool_size';验证参数是否生效。缓冲池命中率应>99%，低于95%需立即扩容。

四、PostgreSQL 16 生产环境部署

4.1 环境准备与系统优化

为什么这样写：PostgreSQL对系统参数比较敏感，特别是共享内存和文件系统配置。在安装数据库之前先把操作系统层面调优好，能让数据库性能发挥更充分。

踩过的坑：

• 坑：内核参数没调，PostgreSQL启动时报“could not create shared memory segment”错误
• 坑：没创建专用的postgres用户，直接用root初始化数据库，权限全乱套

注意事项：

• 生产环境追求稳定，PostgreSQL 16支持至2028年11月
• 需要内核版本≥3.10以获得最佳兼容性

代码块：系统级优化与用户创建

# 1. 安装编译依赖（如使用源码编译安装）
sudo dnf install -y epel-release
sudo dnf groupinstall -y "Development Tools"
sudo dnf install -y readline-devel zlib-devel libicu-devel \
  libxml2-devel libxslt-devel openssl-devel systemd-devel \
  bison flex gcc-c++ gettext

# 2. 优化内核参数（/etc/sysctl.conf）——适用于8GB内存服务器
cat << EOF | sudo tee -a /etc/sysctl.conf
# 共享内存设置（建议系统内存的25%）
kernel.shmmax = 2147483648
kernel.shmall = 524288
# 网络连接优化
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
# 虚拟内存管理——减少内存交换
vm.swappiness = 10
EOF

# 使内核参数生效
sudo sysctl -p

# 3. 创建专用postgres用户
sudo groupadd -g 2000 postgres
sudo useradd -u 2000 -g postgres -m -s /bin/bash postgres

# 4. 创建数据目录结构（建议与系统盘分离）
sudo mkdir -p /pgdata/{data,archive,backup,logs}
sudo chown -R postgres:postgres /pgdata
sudo chmod 700 /pgdata/data

执行后说明：postgres用户是PostgreSQL的“专属管家”——所有数据库进程都运行在这个用户下，数据文件也归它所有。chmod 700确保只有postgres用户能访问数据目录。

4.2 安装PostgreSQL 16

为什么这样写：PostgreSQL提供两种主流安装方式——YUM/DNF仓库安装（快速便捷）和源码编译安装（性能最优）。生产环境推荐源码编译，实测比二进制包性能提升约15%。以下以源码编译方式为例。

踩过的坑：

• 坑：编译时没加--with-llvm，错过JIT编译带来的30-50%复杂查询性能提升
• 坑：没加--with-systemd，无法用systemd优雅管理PostgreSQL服务

注意事项：

• 源码编译需要gcc等开发工具（已在4.1节安装）
• 编译时间约5-15分钟，取决于服务器配置
• 当前最新稳定版为PostgreSQL 16.2

代码块：源码编译安装PostgreSQL 16

# 1. 切换至postgres用户（或使用sudo）
sudo su - postgres

# 2. 下载源码
cd /tmp
wget https://ftp.postgresql.org/pub/source/v16.2/postgresql-16.2.tar.gz
tar -xzf postgresql-16.2.tar.gz
cd postgresql-16.2

# 3. 配置编译选项（关键！）
./configure --prefix=/usr/local/pgsql/16 \
  --with-icu \
  --with-libxml \
  --with-libxslt \
  --with-ssl=openssl \
  --with-systemd \
  --with-uuid=e2fs \
  --with-llvm \
  --with-lz4 \
  --with-zstd \
  CFLAGS="-O2 -march=native"

# 4. 编译与安装（8核机器用-j8加速）
make -j8 world
sudo make install-world

# 5. 初始化数据库集群（启用数据校验）
/usr/local/pgsql/16/bin/initdb -D /pgdata/data \
  --encoding=UTF8 \
  --locale=C \
  --data-checksums

执行后说明：initdb是PostgreSQL的数据库初始化命令，它会在/pgdata/data目录下创建数据库集群的“骨架”——包括postgresql.conf（主配置文件）、pg_hba.conf（访问控制文件）和系统表。--data-checksums启用数据页校验，能检测硬件层面的数据损坏。

4.3 配置PostgreSQL服务与访问控制

为什么这样写：PostgreSQL安装完成后，需要配置systemd服务实现开机自启，同时配置pg_hba.conf控制哪些客户端可以连接数据库——这两步直接决定了数据库的“可用性”和“安全性”。

踩过的坑：

• 坑：pg_hba.conf默认只允许本地peer认证，远程死活连不上
• 坑：生产环境用了trust认证方式，任何人都能无密码登录

注意事项：

• 生产环境优先使用scram-sha-256认证方式
• 远程访问需在postgresql.conf中修改listen_addresses

代码块：配置systemd服务与访问控制

# 1. 创建systemd服务文件
sudo tee /etc/systemd/system/postgresql-16.service << 'EOF'
[Unit]
Description=PostgreSQL 16 database server
Documentation=https://www.postgresql.org/docs/16/static/
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
User=postgres
Group=postgres
ExecStart=/usr/local/pgsql/16/bin/postgres -D /pgdata/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target
EOF

# 2. 重载systemd并启动服务
sudo systemctl daemon-reload
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

# 3. 配置pg_hba.conf（/pgdata/data/pg_hba.conf）
# 本地连接用scram-sha-256，远程连接根据IP段控制
cat << 'EOF' | sudo tee /pgdata/data/pg_hba.conf
# TYPE  DATABASE  USER  ADDRESS        METHOD
local   all       all                   scram-sha-256
host    all       all   127.0.0.1/32    scram-sha-256
host    all       all   ::1/128         scram-sha-256
# 生产环境按需添加——例如允许内网192.168.1.0/24访问
# host  all       all   192.168.1.0/24  scram-sha-256
EOF

# 4. 修改postgresql.conf允许远程监听
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" /pgdata/data/postgresql.conf

# 5. 重启服务使配置生效
sudo systemctl restart postgresql-16

执行后说明：配置完成后，用sudo systemctl status postgresql-16确认服务正常运行。pg_hba.conf的修改需要重启服务才能生效。

4.4 PostgreSQL核心参数调优

为什么这样写：PostgreSQL的默认配置非常保守（例如shared_buffers默认仅128MB），远远无法发挥现代服务器的性能。必须根据硬件资源进行调整。

踩过的坑：

• 坑：shared_buffers设得太大（超过系统内存40%），导致操作系统缓存不足，性能反而下降
• 坑：改了postgresql.conf忘了用SHOW命令验证是否生效

注意事项：

• 以下配置以16GB内存服务器为基准
• shared_buffers通常设置为系统内存的15%-25%

代码块：PostgreSQL 16生产环境核心配置（/pgdata/data/postgresql.conf）

# ============ 内存配置 ============
shared_buffers = 4GB                # 系统内存的25%（16GB×25%）
work_mem = 64MB                     # 排序/哈希操作内存
maintenance_work_mem = 1GB          # VACUUM等维护操作
effective_cache_size = 12GB         # 告诉优化器可用OS缓存大小

# ============ 查询优化 ============
random_page_cost = 1.1              # SSD设为1.1，HDD设为4
effective_io_concurrency = 200      # SSD并发IO

# ============ WAL（预写日志）配置 ============
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 20GB
min_wal_size = 5GB

# ============ 连接配置 ============
max_connections = 300               # 根据应用并发调整

# ============ 日志配置 ============
logging_collector = on
log_directory = '/pgdata/logs'
log_filename = 'postgresql-%Y-%m-%d.log'
log_statement = 'ddl'               # 记录DDL操作
log_min_duration_statement = 1000   # 记录超过1秒的查询

执行后说明：修改配置后执行sudo systemctl reload postgresql-16使参数生效（无需重启）。用psql -c "SHOW shared_buffers;"验证配置是否生效。

五、数据库安全加固与日常运维

5.1 安全基线检查

为什么这样写：数据库部署到生产环境后，第一件事不是“上线”，而是“加固”。以下5条是最基础也是最致命的安全检查项。

代码块：安全加固检查清单

# 1. 检查默认用户——移除测试用户
# MySQL: 确保没有匿名用户（mysql_secure_installation已处理）
# PostgreSQL: 确保postgres用户密码已修改
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'Strong_P@ssw0rd';"

# 2. 检查端口暴露——只对可信IP开放
# MySQL: 修改bind-address
# 在/etc/my.cnf中添加: bind-address = 内网IP

# 3. 检查日志是否开启
# MySQL: SHOW VARIABLES LIKE 'general_log%';
# PostgreSQL: SHOW logging_collector;

# 4. 检查备份策略——确认有自动备份（见5.2节）

# 5. 检查文件权限——数据目录权限应严格受限
ls -la /var/lib/mysql/   # 应为mysql:mysql 750
ls -la /pgdata/data/     # 应为postgres:postgres 700

5.2 备份恢复策略

为什么这样写：数据库没有备份，就等于把公司的核心资产放在悬崖边上。任何一次误操作、硬件故障或黑客攻击都可能造成不可挽回的损失。

踩过的坑：

• 坑：只用mysqldump做逻辑备份，数据库到TB级别时备份一次要几个小时
• 坑：备份了但从没验证过恢复，真到用的时候发现备份文件损坏

备份策略对比：

代码块：每日自动备份脚本（MySQL示例）

#!/bin/bash
# /opt/scripts/mysql_backup.sh
BACKUP_DIR=/backup/mysql
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER=root
DB_PASS='Your_Strong_P@ssw0rd'

# 创建备份目录
mkdir -p $BACKUP_DIR

# 全量备份（保留最近7天）
mysqldump -u$DB_USER -p$DB_PASS --all-databases \
  --single-transaction --routines --triggers \
  > $BACKUP_DIR/full_$DATE.sql

# 清理7天前的备份
find $BACKUP_DIR -name "full_*.sql" -mtime +7 -delete

# 验证备份文件
if [ -s $BACKUP_DIR/full_$DATE.sql ]; then
  echo "Backup succeeded: $BACKUP_DIR/full_$DATE.sql"
else
  echo "Backup FAILED!" | mail -s "DB Backup Failed" admin@example.com
fi

执行后说明：将脚本加入crontab（第12篇已讲），每天凌晨2点执行。关键：定期在测试环境演练恢复流程，确保备份真正可用。

六、效果验证

部署完成后，用以下命令验证数据库是否正常运行：

MySQL验证：

# 检查服务状态
sudo systemctl status mysqld

# 测试连接
mysql -u root -p -e "SELECT VERSION();"

# 查看关键性能指标
mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G" | grep -E "Buffer pool hit rate|read hits"

PostgreSQL验证：

# 检查服务状态
sudo systemctl status postgresql-16

# 测试连接
sudo -u postgres psql -c "SELECT version();"

# 查看连接数
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

七、常见问题FAQ

Q1：MySQL和PostgreSQL在生产环境应该选哪个？
A：两者都是成熟的开源数据库。MySQL配置简单、文档丰富、适合快速迭代；PostgreSQL功能更完整（原生JSONB、窗口函数、复杂查询支持），适合企业级和复杂业务场景。初创团队可选MySQL快速上线，长期演进可考虑PostgreSQL。

Q2：MySQL的innodb_buffer_pool_size应该设多大？
A：专用数据库服务器建议设为物理内存的70%-80%。例如16GB内存设为12GB，32GB内存设为24GB。需给操作系统预留足够内存，避免OOM。

Q3：PostgreSQL的shared_buffers应该设多大？
A：通常设置为系统内存的15%-25%。16GB内存设为4GB，32GB内存设为8GB。设得过大可能导致操作系统缓存不足，反而降低性能。

Q4：生产环境数据库的备份策略应该怎么做？
A：建议采用“逻辑备份+物理备份”的双轨策略。小库用逻辑备份（mysqldump/pg_dump）灵活方便；大库用物理备份（XtraBackup/pg_basebackup）速度快。配合WAL归档实现时间点恢复（PITR）。最重要：定期在测试环境验证备份可恢复性。

Q5：改了数据库配置后服务起不来怎么办？
A：先在配置文件中找到报错行，用备份文件恢复。MySQL用sudo cp /etc/my.cnf.bak /etc/my.cnf，PostgreSQL用备份的postgresql.conf覆盖。然后重启服务，用journalctl -u mysqld -n 50或tail -f /pgdata/logs/postgresql-*.log查看详细错误日志（第11篇日志系统已讲）。

八、本文小结

💡 一句话记住本篇：数据库部署的黄金法则是“配置先行、安全第一、备份至上”——先调好系统参数再装数据库，装完立刻安全加固，上线前必须配好自动备份。