第 17/30 天
引言
数据库是现代应用的核心基础设施。无论是小型个人博客还是大型企业系统，都离不开稳定高效的数据库服务。在 Ubuntu 上，最流行的三大关系型数据库——MySQL、MariaDB 和 PostgreSQL——各有特色和适用场景。
今天，我们将深入这三款数据库在 Ubuntu 上的安装配置、基础调优和日常运维，帮助你根据实际需求选择最合适的方案。
一、数据库选型对比
在开始安装之前，先了解三者的核心差异：
| 特性 | MySQL | MariaDB | PostgreSQL |
|---|---|---|---|
| 起源 | Oracle 旗下 | MySQL 分支（社区维护） | 独立开源项目 |
| 许可证 | GPL/商业双许可 | GPL | PostgreSQL 许可证 |
| 默认存储引擎 | InnoDB | InnoDB / Aria / MyRocks | 内置多存储引擎 |
| ACID 支持 | ✅（InnoDB） | ✅ | ✅（原生） |
| JSON 支持 | ✅（5.7+） | ✅（10.2+） | ✅（原生 JSONB） |
| 并发性能 | 读密集场景优秀 | 与 MySQL 接近，略有提升 | 写密集场景优秀 |
| 窗口函数 | 8.0+ 支持 | 10.2+ 支持 | 原生支持 |
| 空间数据 | ✅ | ✅ | ✅（PostGIS 更强大） |
| 适用场景 | Web 应用（WordPress、LAMP） | Web 应用、替代 MySQL | 复杂查询、数据分析、GIS |
选择建议：
– 运行 WordPress 等 CMS → MariaDB（兼容 MySQL 且性能略优）
– 需要高并发读取 → MySQL 8.0+
– 复杂查询、数据分析、金融系统 → PostgreSQL
二、MySQL 8.0 安装与配置
2.1 安装 MySQL
# 更新包索引
sudo apt update
# 安装 MySQL 8.0 服务器
sudo apt install -y mysql-server-8.0
# 验证安装版本
mysql --version
# 输出示例: mysql Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))
# 检查服务状态
sudo systemctl status mysql
2.2 安全初始化
安装完成后，MySQL 默认是”宽松”模式——需要立即执行安全配置：
# 运行安全初始化脚本
sudo mysql_secure_installation
# 交互式回答（建议）：
# 1. 设置密码强度策略（建议选择 2 = STRONG）
# 2. 设置 root 密码
# 3. 删除匿名用户 → Y
# 4. 禁止 root 远程登录 → Y
# 5. 删除 test 数据库 → Y
# 6. 重新加载权限表 → Y
非交互式自动化配置（用于脚本部署）：
# 使用预配置方式自动设置
sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourStrongPassword123!';"
sudo mysql -e "FLUSH PRIVILEGES;"
2.3 创建数据库和用户
# 登录 MySQL
sudo mysql -u root -p
# 创建数据库
CREATE DATABASE IF NOT EXISTS myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 创建用户并授权
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'SecurePassword123!';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
# 创建远程访问用户（仅限安全网络环境）
CREATE USER 'remoteuser'@'192.168.1.%' IDENTIFIED BY 'RemotePassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'remoteuser'@'192.168.1.%';
FLUSH PRIVILEGES;
# 退出
EXIT;
2.4 MySQL 性能调优
MySQL 的核心配置文件位于 /etc/mysql/mysql.conf.d/mysqld.cnf：
# /etc/mysql/mysql.conf.d/mysqld.cnf 关键调优参数
[mysqld]
# 基本设置
port = 3306
bind-address = 127.0.0.1 # 安全：仅本地连接
max_connections = 200 # 根据服务器内存调整
# InnoDB 引擎调优
innodb_buffer_pool_size = 2G # 设置为物理内存的 60-70%
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2 # 写入性能优化（安全折中）
innodb_file_per_table = 1
# 查询缓存（MySQL 8.0 已废弃，仅保留兼容）
# 使用 Performance Schema 替代
# 连接超时设置
wait_timeout = 600
interactive_timeout = 600
应用配置后重启：
# 检查配置语法
sudo mysqld --validate-config
# 重启 MySQL
sudo systemctl restart mysql
# 验证参数生效
sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
三、MariaDB 安装与配置
MariaDB 是 MySQL 的社区分支，完全兼容 MySQL 协议，但拥有更多存储引擎和性能优化。
3.1 安装 MariaDB
# 通过 Ubuntu 仓库安装
sudo apt update
sudo apt install -y mariadb-server mariadb-client
# 验证版本
mariadb --version
# 输出示例: mysql Ver 15.1 Distrib 10.6.16-MariaDB
# 查看服务状态
sudo systemctl status mariadb
3.2 MariaDB 的特殊配置
MariaDB 使用与 MySQL 相同的安全初始化脚本：
# 安全配置
sudo mysql_secure_installation
# MariaDB 关键配置差异
# - 默认使用 unix_socket 认证（无需密码即可通过 root 系统用户登录）
# - 默认存储引擎仍为 InnoDB
# - 额外支持 Aria（崩溃安全）、MyRocks（压缩）、Spider（分片）等引擎
3.3 MariaDB 的独特优势——Aria 引擎
# 查看可用的存储引擎
sudo mysql -e "SHOW ENGINES;"
# 创建使用 Aria 引擎的表
sudo mysql -e "
CREATE DATABASE IF NOT EXISTS test_aria;
USE test_aria;
CREATE TABLE aria_test (
id INT AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100)
) ENGINE=Aria TRANSACTIONAL=1;
"
# TRANSACTIONAL=1 启用事务支持，0 为高性能非事务模式
3.4 MariaDB 调优建议
MariaDB 的配置文件位于 /etc/mysql/mariadb.conf.d/50-server.cnf：
# /etc/mysql/mariadb.conf.d/50-server.cnf 关键调优
[mysqld]
# 基础设置
max_connections = 200
thread_cache_size = 64
# InnoDB 调优
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
# MariaDB 特有优化
aria_pagecache_buffer_size = 128M
thread_handling = pool-of-threads # 线程池模式，适合高并发
# 查询优化
join_buffer_size = 4M
tmp_table_size = 64M
max_heap_table_size = 64M
四、PostgreSQL 16 安装与配置
PostgreSQL 以功能丰富、标准符合度高、适合复杂查询而闻名。
4.1 安装 PostgreSQL
# 安装 PostgreSQL 16（Ubuntu 22.04 默认仓库）
sudo apt update
sudo apt install -y postgresql postgresql-contrib
# 验证版本
psql --version
# 输出示例: psql (PostgreSQL) 16.2 (Ubuntu 16.2-1.pgdg22.04+1)
# 检查服务状态
sudo systemctl status postgresql
4.2 PostgreSQL 的用户体系
PostgreSQL 使用操作系统用户和数据库用户分离的认证体系：
# PostgreSQL 默认创建 postgres 系统用户
# 切换并进入 psql 控制台
sudo -u postgres psql
# 在 psql 中创建数据库和用户
CREATE DATABASE myapp;
CREATE USER appuser WITH PASSWORD 'SecurePassword123!';
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
# 连接到新数据库
c myapp
# 创建表并测试
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
# 插入测试数据
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');
INSERT INTO users (name, email) VALUES ('李四', 'lisi@example.com');
# 查询并验证
SELECT * FROM users;
# 退出 psql
q
4.3 配置远程访问
PostgreSQL 默认只监听本地连接，需要修改两个文件：
# 1. 修改 postgresql.conf 监听地址
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = 'localhost, 192.168.1.100'/" /etc/postgresql/16/main/postgresql.conf
# 2. 修改 pg_hba.conf 添加远程访问规则
echo "host myapp appuser 192.168.1.0/24 md5" | sudo tee -a /etc/postgresql/16/main/pg_hba.conf
# 重启生效
sudo systemctl restart postgresql
4.4 PostgreSQL 性能调优
PostgreSQL 的配置文件位于 /etc/postgresql/16/main/postgresql.conf：
# /etc/postgresql/16/main/postgresql.conf 关键调优参数
# 连接设置
max_connections = 200
listen_addresses = 'localhost'
# 内存设置（服务器 8GB 内存为例）
shared_buffers = 2GB # 物理内存的 25%
effective_cache_size = 6GB # 物理内存的 75%
work_mem = 32MB # 每个排序操作的内存（谨慎调大）
maintenance_work_mem = 512MB
# 写入优化
wal_buffers = 16MB
wal_level = replica # 用于流复制
max_wal_size = 2GB
min_wal_size = 512MB
# 查询优化
random_page_cost = 1.1 # SSD 环境下降低此值
effective_io_concurrency = 200 # SSD 环境
default_statistics_target = 100
# 并行查询
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_tuple_cost = 0.1
parallel_setup_cost = 500
五、数据库日常运维
5.1 备份与恢复
MySQL/MariaDB 备份：
# 使用 mysqldump 备份单个数据库
mysqldump -u root -p --single-transaction --routines --triggers myapp > /backup/myapp_$(date +%Y%m%d).sql
# 压缩备份
mysqldump -u root -p --all-databases --single-transaction | gzip > /backup/all_$(date +%Y%m%d).sql.gz
# 恢复备份
mysql -u root -p myapp < /backup/myapp_20240724.sql
gunzip -c /backup/all_20240724.sql.gz | mysql -u root -p
PostgreSQL 备份：
# 使用 pg_dump
pg_dump -U postgres -h localhost myapp > /backup/myapp_$(date +%Y%m%d).sql
# 压缩备份
pg_dump -U postgres -h localhost myapp | gzip > /backup/myapp_$(date +%Y%m%d).sql.gz
# 恢复备份
createdb -U postgres myapp_restored
psql -U postgres -d myapp_restored < /backup/myapp_20240724.sql
gunzip -c /backup/myapp_20240724.sql.gz | psql -U postgres -d myapp_restored
5.2 监控与诊断
# MySQL/MariaDB 进程列表
sudo mysql -e "SHOW FULL PROCESSLIST;"
# 慢查询分析（需要先在配置中启用 slow_query_log）
sudo mysql -e "SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;"
# PostgreSQL 活动查询
sudo -u postgres psql -c "SELECT pid, state, query, age(now(), query_start) FROM pg_stat_activity WHERE state != 'idle' ORDER BY query_start DESC;"
# 查看数据库大小
sudo -u postgres psql -c "
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
"
5.3 日志管理
# MySQL 错误日志
sudo tail -100 /var/log/mysql/error.log
# MariaDB 错误日志
sudo tail -100 /var/log/mysql/error.log
# PostgreSQL 日志
sudo tail -100 /var/log/postgresql/postgresql-16-main.log
# 查看 MySQL 二进制日志状态
sudo mysql -e "SHOW BINARY LOGS;"
六、常见问题与排错
6.1 无法连接数据库
# 检查服务是否在运行
sudo systemctl status mysql
# 检查端口监听
sudo ss -tlnp | grep -E '3306|5432'
# 检查防火墙
sudo ufw status
sudo ufw allow 3306/tcp # MySQL
sudo ufw allow 5432/tcp # PostgreSQL
6.2 密码认证失败
# MySQL/MariaDB 重置 root 密码（免密模式）
sudo systemctl stop mysql
sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld
sudo mysqld_safe --skip-grant-tables &
sleep 5
sudo mysql -u root -e "FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';"
sudo mysqladmin shutdown
sudo systemctl start mysql
6.3 性能问题快速诊断
# MySQL 查询缓存命中率（MySQL 8.0 以下版本）
sudo mysql -e "SHOW STATUS LIKE 'Qcache%';"
# PostgreSQL 缓存命中率
sudo -u postgres psql -c "
SELECT
'index hit rate' AS name,
(sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio
FROM pg_statio_user_indexes
UNION ALL
SELECT 'table hit rate',
sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0)
FROM pg_statio_user_sequences;
"
# 检查数据库连接数
sudo mysql -e "SHOW STATUS LIKE 'Threads_connected';"
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"
七、安全最佳实践
# 1. 数据库端口仅在需要时对外开放
# MySQL 默认监听 127.0.0.1:3306，PostgreSQL 默认 127.0.0.1:5432
# 2. 使用强密码策略
sudo mysql -e "SHOW VARIABLES LIKE 'validate_password%';"
# 3. 定期审计用户权限
sudo mysql -e "SELECT user, host, authentication_string FROM mysql.user;"
sudo -u postgres psql -c "du"
# 4. 启用 SSL 连接（MySQL 8.0+）
sudo mysql -e "SHOW VARIABLES LIKE '%ssl%';"
# 5. 限制数据库用户最小权限原则
# 仅授予应用所需的最小权限，而非 ALL PRIVILEGES
总结
今天我们深入探索了 Ubuntu 上三大主流关系型数据库的安装、配置与调优：
- MySQL 8.0：最适合兼容性要求高的 Web 应用，安装简单，生态成熟
- MariaDB：MySQL 的社区增强版，提供更多存储引擎选择和性能优化
- PostgreSQL 16：功能最丰富，适合复杂查询、数据分析和需要高级特性的场景
关键要点：
1. 安装后立即执行安全初始化脚本
2. 根据服务器内存调整 innodb_buffer_pool_size（MySQL/MariaDB）或 shared_buffers（PostgreSQL）
3. 限制数据库监听地址，不要开放到公网
4. 建立定期备份机制，使用 --single-transaction 确保一致性
5. 使用慢查询日志和系统视图监控性能
📚 系列目录
- 第 1 天：Ubuntu 简介与版本选择
- 第 2 天：手把手安装 Ubuntu
- 第 3 天：Ubuntu 桌面环境初探
- 第 4 天：Ubuntu 终端基础
- 第 5 天：用户与权限管理
- 第 6 天：软件包管理
- 第 7 天：文件与文本操作
- 第 8 天：系统服务管理
- 第 9 天：磁盘与文件系统管理
- 第 10 天：网络配置与管理
- 第 11 天：进程管理与监控
- 第 12 天：计划任务与自动化
- 第 13 天：备份与恢复策略
- 第 14 天：系统更新与升级管理
- 第 15 天：SSH 远程管理与安全加固
- 第 16 天：Web 服务器搭建——Nginx/Apache 安装配置与反向代理
- 第 17 天：数据库服务器部署——MySQL/MariaDB/PostgreSQL 安装与调优 ← 本文
下期预告： 第 18 天，我们将进入容器化时代——Docker 在 Ubuntu 上的安装与容器化管理。从 Docker 引擎安装到镜像管理、容器网络与数据卷，再到 Docker Compose 编排，带你迈入云原生第一步。
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容