数据库是现代业务系统的核心，几乎所有的 Web 应用、API 服务与数据分析平台都建立在数据库之上。作为一名 Ubuntu 运维工程师，掌握 MySQL 与 PostgreSQL 这两大主流开源关系型数据库的部署、优化与备份，是绕不开的基本功。本篇将从安装部署讲起，逐步深入到参数调优与备份恢复，帮助你建立一套可落地的数据库运维方法。
为什么选择 MySQL 与 PostgreSQL
MySQL 以简单易用、生态成熟著称，是中小型 Web 应用与 LAMP/LNMP 技术栈的标配；PostgreSQL 则以功能强大、SQL 标准遵从度高、扩展能力强见长，在处理复杂查询、地理信息、JSON 与事务一致性方面表现突出。两者都拥有活跃的社区与丰富的运维工具链。在实际生产环境中，运维人员往往需要同时维护这两种数据库，因此掌握它们的共性与差异至关重要。
环境准备与安装部署
在 Ubuntu 上安装数据库并不复杂，关键在于选择正确的版本来源与初始化方式。以 Ubuntu 24.04 为例，我们既可以直接使用系统自带的软件源，也可以添加官方仓库获取更新版本。
安装 MySQL
Ubuntu 官方源中默认提供的是 MySQL 8.0，安装命令如下：
sudo apt update
sudo apt install -y mysql-server
安装完成后，启动并检查服务状态：
sudo systemctl enable --now mysql
sudo systemctl status mysql
首次安装的 MySQL 默认启用了 auth_socket 认证，root 用户通过 sudo 即可直接登录，无需密码。生产环境建议先运行安全初始化脚本，设置 root 密码并移除匿名用户：
sudo mysql_secure_installation
安装 PostgreSQL
PostgreSQL 的安装同样简单，Ubuntu 官方源提供了稳定的版本：
sudo apt update
sudo apt install -y postgresql postgresql-contrib
安装完成后，PostgreSQL 会自动创建一个名为 postgres 的系统用户与同名数据库超级用户，并启动服务：
sudo systemctl enable --now postgresql
sudo -u postgres psql -c "SELECT version();"
基础配置与访问控制
数据库安装完成后，默认只监听本地回环地址，生产环境需要根据实际网络拓扑调整监听与访问控制。
MySQL 配置调整
MySQL 主配置文件位于 /etc/mysql/mysql.conf.d/mysqld.cnf，核心配置如下：
[mysqld]
bind-address = 127.0.0.1
port = 3306
max_connections = 200
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
default-authentication-plugin = caching_sha2_password
修改后重启服务使配置生效：
sudo systemctl restart mysql
PostgreSQL 配置调整
PostgreSQL 的主配置文件位于 /etc/postgresql/16/main/postgresql.conf，客户端认证规则在 pg_hba.conf 中维护：
listen_addresses = 'localhost'
port = 5432
max_connections = 200
shared_buffers = 256MB
认证规则示例，允许本地与同网段客户端使用密码登录：
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
host all all 127.0.0.1/32 scram-sha-256
host all all 192.168.1.0/24 scram-sha-256
性能优化核心思路
数据库优化不是一蹴而就的，需要结合硬件资源与业务负载逐步调优。下面从几个关键维度给出通用建议。
内存与缓存
MySQL 的 InnoDB 缓冲池是最重要的内存参数，通常建议设置为物理内存的 50% 到 70%。在配置文件中可以这样调整：
[mysqld]
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
query_cache_type = 0
PostgreSQL 则通过 shared_buffers 控制共享缓冲区，同时配合 effective_cache_size 让查询优化器更准确地估算缓存命中率：
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB
慢查询分析
慢查询日志是定位性能瓶颈的第一手资料。MySQL 开启慢查询：
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
PostgreSQL 可以使用 pg_stat_statements 扩展统计耗时最高的 SQL：
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
索引与查询优化
合理的索引能显著提升查询性能。创建索引前应使用 EXPLAIN 分析执行计划：
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
数据备份与恢复
备份是数据库运维的生命线。MySQL 与 PostgreSQL 都支持逻辑备份与物理备份两种方式，生产环境建议两者结合，并定期演练恢复流程。
MySQL 逻辑备份
使用 mysqldump 导出单个数据库：
sudo mysqldump -u root -p --single-transaction --routines --triggers mydb > mydb_backup.sql
恢复时直接将备份文件导入：
sudo mysql -u root -p mydb < mydb_backup.sql
PostgreSQL 逻辑备份
使用 pg_dump 备份单个数据库，pg_dumpall 用于备份整个实例：
sudo -u postgres pg_dump -Fc mydb > mydb.dump
sudo -u postgres pg_restore -d mydb mydb.dump
定时备份脚本
将备份命令封装为脚本，配合 cron 实现每日自动备份，并保留最近 7 天的历史：
#!/bin/bash
BACKUP_DIR=/backup/mysql
DB_NAME=mydb
DATE=$(date +%F)
mkdir -p "$BACKUP_DIR"
mysqldump -u root --single-transaction "$DB_NAME" | gzip > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete
将脚本加入 crontab，每天凌晨 2 点执行：
0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1
常见问题与排障
在实际运维中，数据库最常见的几类问题集中在连接、磁盘与锁等待上。
无法远程连接：先检查 bind-address 是否监听正确地址，再检查防火墙是否放行 3306/5432 端口，最后确认用户是否允许从对应主机登录。
磁盘空间耗尽：二进制日志与 WAL 文件长期不清理会占满磁盘。定期清理过期 binlog，或合理设置 PostgreSQL 的 WAL 保留策略。
锁等待与慢查询：通过 SHOW PROCESSLIST 或 pg_stat_activity 查看当前活跃连接，定位长时间运行的事务并及时处理，避免阻塞其他请求。
总结
数据库运维的核心在于「部署规范、优化有据、备份可靠」。MySQL 与 PostgreSQL 虽然各有特点，但在配置管理、性能调优与备份恢复上的方法论是相通的。掌握本篇文章所介绍的命令与思路，你便能在日常工作中快速部署一套可用的数据库环境，并为后续的高可用与监控体系建设打下坚实基础。
下期预告：第 19 天我们将进入 Web 服务器运维，学习 Nginx 的配置、反向代理与性能调优，敬请期待。
暂无评论内容