数据库服务搭建
本指南将深入介绍如何在您的VPS上选择、安装、配置和维护主流的关系型数据库服务，如MySQL和PostgreSQL。您将学会安全加固、性能调优、备份恢复以及主从复制等高级操作。
数据库选择
选择合适的数据库是应用架构的基石。MySQL 和 PostgreSQL 是最受欢迎的两个开源关系型数据库，各有千秋。
MySQL vs. PostgreSQL
下面是两者在几个关键维度的对比，帮助您做出选择：
| 特性 | MySQL / MariaDB | PostgreSQL |
|---|---|---|
| 流行度与社区 | 全球最流行，社区庞大，文档丰富 | 流行度快速上升，社区活跃，技术氛围浓厚 |
| 数据一致性 | 默认REPEATABLE READ，InnoDB引擎支持ACID | 严格遵守ACID，数据一致性极佳 |
| 功能与扩展 | 功能全面，适合通用Web应用 | 支持更复杂查询、JSONB、GIS等高级特性，扩展性强 |
| 性能 | 在简单读写场景下性能优异 | 在复杂查询和高并发写入下表现出色 |
| 许可证 | GPL，由Oracle公司主导 | PostgreSQL License (类似BSD)，完全开源 |
如何选择？
选择MySQL/MariaDB：如果您的应用是典型的Web应用（如WordPress、LAMP架构），对读操作要求高，需要快速上手和广泛的社区支持。
选择PostgreSQL：如果您的应用需要处理复杂查询、地理空间数据，要求严格的数据完整性和事务一致性，或者未来可能需要强大的扩展功能。
MySQL安装与配置
以Ubuntu 22.04系统为例，介绍如何安装和配置MySQL服务器。
1. 安装MySQL服务器
更新软件包列表并安装：
sudo apt update sudo apt install mysql-server -y 2. 运行安全脚本
新安装的MySQL需要运行一个安全脚本来设置密码、移除匿名用户等。
sudo mysql_secure_installation 💡 安全脚本提示
脚本会引导您完成以下设置：
- VALIDATE PASSWORD COMPONENT：密码强度验证插件，建议开启。
- 设置root密码：为MySQL的root用户设置一个强密码。
- 移除匿名用户：建议移除。
- 禁止root远程登录：为了安全，强烈建议禁止。
- 移除测试数据库：建议移除。
- 重新加载权限表：使配置生效。
3. 登录和验证
使用设置的密码登录MySQL：
sudo mysql -u root -p 查看MySQL服务状态：
sudo systemctl status mysql PostgreSQL安装与配置
以Ubuntu 22.04系统为例，介绍如何安装和配置PostgreSQL。
1. 安装PostgreSQL
安装PostgreSQL及其社区扩展包：
sudo apt update sudo apt install postgresql postgresql-contrib -y 2. 切换用户与登录
PostgreSQL安装后会创建一个名为 `postgres` 的Linux用户，您需要切换到该用户来操作数据库。
切换到postgres用户：
sudo -i -u postgres 进入PostgreSQL命令行：
psql 在psql内，您可以设置 `postgres` 用户的密码：
\password postgres 输入 `\q` 退出psql，输入 `exit` 退回到您的普通用户。
3. 远程访问配置 (可选)
默认情况下，PostgreSQL只允许本地连接。要开启远程访问，需要修改两个配置文件。
- 编辑 `postgresql.conf`：
sudo nano /etc/postgresql/<version>/main/postgresql.conf找到 `listen_addresses`，取消注释并修改为：
listen_addresses = '*'
- 编辑 `pg_hba.conf`：
sudo nano /etc/postgresql/<version>/main/pg_hba.conf在文件末尾添加一行，允许特定IP或IP段访问：
# 格式: TYPE DATABASE USER ADDRESS METHOD host all all your_client_ip/32 scram-sha-256
- 重启服务：
sudo systemctl restart postgresql
安全加固
除了初始安全设置，持续的安全加固至关重要。
🔒 创建专用用户
避免使用root或postgres账户连接应用。为每个应用创建权限受限的专用用户。
MySQL:
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'password'; GRANT SELECT, INSERT, UPDATE ON appdb.* TO 'appuser'@'localhost'; 🌐 限制网络访问
使用防火墙（如UFW）只对必要的IP开放数据库端口（MySQL: 3306, PostgreSQL: 5432）。
sudo ufw allow from your_app_server_ip to any port 3306 🔄 定期更新
定期更新数据库软件，修复已知的安全漏洞。
sudo apt update && sudo apt upgrade 🔑 使用SSL/TLS加密
为客户端和服务器之间的连接配置SSL/TLS加密，防止数据被窃听。
性能调优基础
合理的配置可以显著提升数据库性能。调优是一个复杂的过程，这里介绍几个最关键的参数。
MySQL (my.cnf)
配置文件通常在 /etc/mysql/mysql.conf.d/mysqld.cnf。
| 参数 | 说明 |
|---|---|
innodb_buffer_pool_size | InnoDB最重要的参数，缓存数据和索引。建议设为物理内存的50%-70%。 |
innodb_log_file_size | 重做日志文件大小，影响写入性能和恢复速度。 |
max_connections | 最大并发连接数。根据应用需求和服务器内存调整。 |
query_cache_size | (在MySQL 8.0中已废弃) 查询缓存，对读多写少的应用有益，但高并发下可能成为瓶颈。 |
PostgreSQL (postgresql.conf)
配置文件通常在 /etc/postgresql/<version>/main/postgresql.conf。
| 参数 | 说明 |
|---|---|
shared_buffers | 最重要的参数，数据库服务器使用的共享内存。建议设为物理内存的25%。 |
work_mem | 每个数据库连接进行排序、哈希等操作时可使用的内存。 |
maintenance_work_mem | 维护操作（如VACUUM, CREATE INDEX）可使用的内存。 |
effective_cache_size | 优化器用于估算可用缓存的参数，通常设为物理内存的50%-75%。 |
备份与恢复
数据是核心资产，必须建立可靠的备份和恢复策略。
MySQL: mysqldump
逻辑备份工具，生成SQL文件。
备份单个数据库：
mysqldump -u root -p db_name > db_name.sql 备份所有数据库：
mysqldump -u root -p --all-databases > all_databases.sql 恢复数据库：
mysql -u root -p db_name < db_name.sql PostgreSQL: pg_dump
逻辑备份工具，可以生成多种格式的备份。
备份为SQL文件：
pg_dump -U postgres -d db_name > db_name.sql 使用自定义格式备份（推荐）：
pg_dump -U postgres -d db_name -F c -f db_name.dump 恢复 (SQL / 自定义格式)：
psql -U postgres -d db_name < db_name.sql pg_restore -U postgres -d db_name db_name.dump 自动化备份
使用Cron Job可以轻松实现自动化定时备份。例如，每天凌晨2点备份MySQL数据库：
0 2 * * * mysqldump -u root -p'YourPassword' --all-databases | gzip > /path/to/backups/db_backup_$(date +\%Y\%m\%d).sql.gz 主从复制简介
主从复制是将主数据库的更改实时同步到一个或多个从数据库的过程。这是实现读写分离、高可用和数据备份的重要技术。
读写分离
主库负责写操作，从库负责读操作，分散负载
高可用性
主库故障时，可快速切换到从库，减少停机时间
数据分析
可以在从库上执行耗时的分析查询，不影响主库
MySQL主从复制基本流程
主库配置 (Master)
- 修改 `my.cnf`，启用二进制日志 (`log_bin`) 并设置 `server-id`。
- 创建一个用于复制的专用用户 (`REPLICATION SLAVE` 权限)。
- 记录当前的二进制日志文件名和位置。
从库配置 (Slave)
- 修改 `my.cnf`，设置与主库不同的 `server-id`。
- 在从库上使用 `CHANGE MASTER TO` 命令，指向主库的地址、复制用户名、密码以及日志文件和位置。
- 启动复制进程 (`START SLAVE`)。
验证
- 在从库上使用 `SHOW SLAVE STATUS\G` 查看 `Slave_IO_Running` 和 `Slave_SQL_Running` 是否都为 `Yes`。
注意
主从复制的配置较为复杂，涉及多个步骤和权限设置，需要非常小心。建议在测试环境中充分演练。
常见问题排查
❌ 无法连接到数据库
可能原因：
- 数据库服务未运行。
- 防火墙阻止了端口。
- 配置错误 (如 `bind-address` / `listen_addresses`)。
- 用户名或密码错误。
- 用户主机限制 (e.g., 'user'@'localhost' vs 'user'@'%')。
解决方法：
- 检查服务状态: `systemctl status mysql/postgresql`
- 检查防火墙规则: `ufw status`
- 检查数据库日志: `/var/log/mysql/error.log` 或 `/var/log/postgresql/...`
❌ 主从复制中断
可能原因：
- 网络问题导致主从连接中断。
- 主库上的二进制日志被清理。
- 在从库上执行了写操作，导致数据不一致。
- 主键冲突。
解决方法：
- 查看 `SHOW SLAVE STATUS\G` 中的 `Last_Error` 信息。
- 根据错误信息决定是跳过错误 (`SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1`) 还是重新同步。