25 - 数据库管理
数据库是现代应用的持久化核心。Linux 上最主流的三个关系型数据库系统——MySQL/MariaDB、PostgreSQL 和 SQLite——覆盖了从嵌入式到企业级的完整需求。本章涵盖它们的安装、基本 SQL 操作、用户权限管理、命令行工具使用以及备份恢复策略。
25.1 关系型数据库概览
三大数据库对比
| 特性 | SQLite | MySQL/MariaDB | PostgreSQL |
|---|---|---|---|
| 类型 | 嵌入式（文件库） | 客户端/服务器 | 客户端/服务器 |
| SQL 标准 | 基础 | 扩展（方言） | 最完整 |
| 并发写入 | 单写入者 | 行级锁 | MVCC（多版本） |
| JSON 支持 | 基础 | JSON 类型 | JSON/JSONB（索引） |
| 全文搜索 | FTS5 | InnoDB 全文索引 | 内置 + 扩展 |
| 地理空间 | SpatiaLite | 基础 GIS | PostGIS（功能最强） |
| 扩展性 | 内置函数 | 插件/存储引擎 | 丰富的扩展生态 |
| 适用场景 | 移动端、桌面应用、嵌入式 | Web 应用、CMS（WordPress 等） | 复杂查询、数据分析、GIS |
| 安装大小 | ~1MB | ~200MB+ | ~150MB+ |
| 进程模型 | 库内执行 | 多线程 | 每连接 fork 进程 |
25.2 MySQL / MariaDB
MariaDB 是 MySQL 的社区分支，由原 MySQL 创始人维护。大多数 Linux 发行版默认使用 MariaDB。
安装
# === MariaDB ===
# Debian / Ubuntu
sudo apt install mariadb-server mariadb-client
# RHEL / Fedora
sudo dnf install mariadb-server mariadb
# openSUSE
sudo zypper install mariadb mariadb-client
# Arch
sudo pacman -S mariadb
# Alpine
apk add mariadb mariadb-client
# === MySQL（Oracle 官方版）===
# Ubuntu
# sudo apt install mysql-server mysql-client
# RHEL / Fedora（需要先添加 MySQL 仓库）
# sudo dnf install https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm
# sudo dnf install mysql-server初始化与启动
# 初始化数据库（MariaDB 首次启动前需要）
# Debian/Ubuntu 自动完成，其他发行版手动执行：
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
# 启动服务
sudo systemctl enable --now mariadb # MariaDB
sudo systemctl enable --now mysqld # MySQL
# === 安全初始化向导（设置 root 密码、删除匿名用户等）===
sudo mysql_secure_installation
# 交互过程：
# - Enter current password for root: (首次为空，直接回车)
# - Set root password? [Y/n] Y
# - Remove anonymous users? [Y/n] Y
# - Disallow root login remotely? [Y/n] Y
# - Remove test database? [Y/n] Y
# - Reload privilege tables? [Y/n] Y连接与基本操作
# 连接（本地 socket 认证）
sudo mysql
mysql -u root -p # 密码认证
# === 基本 SQL ===
# 显示所有数据库
SHOW DATABASES;
# 创建数据库
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 选择数据库
USE myapp;
# 创建表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 查看表结构
DESCRIBE users;
SHOW CREATE TABLE users;
# 插入数据
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com');
# 查询数据
SELECT * FROM users;
SELECT id, username FROM users WHERE email LIKE '%example.com';
# 更新数据
UPDATE users SET email = 'alice_new@example.com' WHERE username = 'alice';
# 删除数据
DELETE FROM users WHERE id = 1;
# 聚合查询
SELECT COUNT(*) FROM users;
SELECT DATE(created_at), COUNT(*) FROM users GROUP BY DATE(created_at);用户与权限管理
-- 创建用户
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'strong_password';
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'strong_password'; -- 网段
CREATE USER 'appuser'@'%' IDENTIFIED BY 'strong_password'; -- 任何来源
-- 授权
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'localhost';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost'; -- 全部权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION; -- 超级用户
-- 查看权限
SHOW GRANTS FOR 'appuser'@'localhost';
-- 撤销权限
REVOKE INSERT ON myapp.* FROM 'appuser'@'localhost';
-- 删除用户
DROP USER 'appuser'@'localhost';
-- 刷新权限（修改后生效）
FLUSH PRIVILEGES;配置文件
# MariaDB: /etc/mysql/mariadb.conf.d/50-server.cnf（Debian/Ubuntu）
# MySQL: /etc/mysql/mysql.conf.d/mysqld.cnf（Debian/Ubuntu）
# /etc/my.cnf（RHEL/Fedora）
# /etc/mysql/my.cnf（Arch）
[mysqld]
# 网络
bind-address = 127.0.0.1 # 仅本地监听（安全默认）
# bind-address = 0.0.0.0 # 所有接口
port = 3306
# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# 内存（根据服务器配置调整）
innodb_buffer_pool_size = 1G # InnoDB 缓存（建议物理内存的 50%-70%）
innodb_log_file_size = 256M
max_connections = 151
query_cache_type = 0 # MySQL 8 / MariaDB 10.2+ 默认禁用
# 日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2 # 超过 2 秒的查询记录
# 数据目录
datadir = /var/lib/mysql备份与恢复
# === mysqldump：逻辑备份 ===
# 备份单个数据库
mysqldump -u root -p myapp > myapp_backup.sql
# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql
# 仅备份表结构（不含数据）
mysqldump -u root -p --no-data myapp > myapp_schema.sql
# 备份特定表
mysqldump -u root -p myapp users posts > myapp_tables.sql
# 压缩备份
mysqldump -u root -p myapp | gzip > myapp_backup.sql.gz
# 恢复
mysql -u root -p myapp < myapp_backup.sql
# 从压缩文件恢复
gunzip < myapp_backup.sql.gz | mysql -u root -p myapp
# === MariaDB Backup（物理备份，MariaDB 专有）===
sudo mariabackup --backup --target-dir=/backup/mariadb --user=root
sudo mariabackup --prepare --target-dir=/backup/mariadb # 准备备份
sudo mariabackup --copy-back --target-dir=/backup/mariadb # 恢复
# === 自动备份脚本示例 ===
#!/bin/bash
DB="myapp"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
mysqldump -u root -p"$MYSQL_PASS" "$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"
# 保留最近 7 天的备份
find "$BACKUP_DIR" -name "${DB}_*.sql.gz" -mtime +7 -delete25.3 PostgreSQL
PostgreSQL 以 SQL 标准合规性、扩展性和数据完整性著称，是企业级应用的首选。
安装
# Debian / Ubuntu
sudo apt install postgresql postgresql-client
# RHEL / Fedora
sudo dnf install postgresql-server postgresql
# openSUSE
sudo zypper install postgresql-server
# Arch
sudo pacman -S postgresql
# Alpine
apk add postgresql postgresql-client初始化与启动
# === RHEL / Fedora / openSUSE / Arch ===
# 首次安装后需初始化数据目录
sudo postgresql-setup --initdb # RHEL/Fedora
sudo su - postgres -c "initdb --locale en_US.UTF-8 -D /var/lib/postgres/data" # Arch
# === 启动服务 ===
sudo systemctl enable --now postgresql
# === Debian/Ubuntu 自动初始化和启动 ===
sudo systemctl enable --now postgresqlpsql 命令行基础
# 切换到 postgres 用户
sudo -u postgres psql
sudo -i -u postgres # 或先切换用户再 psql
# 连接特定数据库
psql -U postgres -d myapp
psql -h localhost -U appuser -d myapp
# === psql 基本操作 ===-- 在 psql 中执行的命令（SQL）和元命令（\开头）
-- === 元命令 ===
\l -- 列出所有数据库
\l+ -- 详细列表
\c myapp -- 连接到 myapp 数据库
\dt -- 列出当前数据库的表
\d users -- 查看表结构
\d+ users -- 详细表结构（含索引、约束）
\di -- 列出索引
\du -- 列出角色/用户
\dn -- 列出 schema
\df -- 列出函数
\dv -- 列出视图
\q -- 退出
\timing -- 显示查询执行时间
\e -- 打开编辑器编写查询
\! ls -- 执行 shell 命令
-- === SQL 操作 ===
-- 创建数据库
CREATE DATABASE myapp ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
-- 创建表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(255) NOT NULL,
metadata JSONB DEFAULT '{}'::jsonb, -- JSON 文档
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_metadata ON users USING GIN (metadata); -- JSONB GIN 索引
-- 插入
INSERT INTO users (username, email, metadata)
VALUES ('alice', 'alice@example.com', '{"role": "admin", "age": 30}');
-- JSON 查询
SELECT username, metadata->>'role' AS role FROM users;
SELECT * FROM users WHERE metadata @> '{"role": "admin"}'::jsonb;
-- 窗口函数（PostgreSQL 强项）
SELECT username, created_at,
ROW_NUMBER() OVER (ORDER BY created_at) AS rn
FROM users;
-- CTE（公共表表达式）
WITH recent AS (
SELECT * FROM users WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT COUNT(*) FROM recent;用户与权限管理
PostgreSQL 使用 角色（Role） 概念统一管理用户和组：
-- 创建角色
CREATE ROLE appuser WITH LOGIN PASSWORD 'strong_password';
CREATE ROLE readonly WITH LOGIN PASSWORD 'readonly_pass';
-- 创建角色并授予超级用户权限
CREATE ROLE admin WITH LOGIN SUPERUSER PASSWORD 'admin_pass';
-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
-- 针对特定 schema 和表
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO appuser;
-- 只读权限
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
-- 设置新表的默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;
-- 查看权限
\du
-- 撤销权限
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM appuser;
-- 删除角色
DROP ROLE appuser;postgresql.conf 配置
# 主配置文件位置：
# Debian/Ubuntu: /etc/postgresql/<version>/main/postgresql.conf
# RHEL/Fedora: /var/lib/pgsql/data/postgresql.conf
# Arch: /var/lib/postgres/data/postgresql.conf
# 网络与连接
listen_addresses = 'localhost' # 或 '*' 监听所有接口
port = 5432
max_connections = 100
# 内存
shared_buffers = 256MB # 建议 25% 系统内存
effective_cache_size = 1GB # 建议 50%-75% 系统内存
work_mem = 16MB # 单个操作的排序内存
maintenance_work_mem = 256MB # VACUUM/CREATE INDEX 用
# WAL（预写日志）
wal_level = replica # 或 minimal, logical
max_wal_size = 1GB
min_wal_size = 80MB
# 查询优化器
random_page_cost = 1.1 # SSD 环境降低此值（默认 4.0 针对 HDD）
effective_io_concurrency = 200 # SSD 下的并发 I/O
# 日志
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_min_duration_statement = 1000 # 记录慢于 1 秒的查询基于内存计算推荐配置的工具：https://pgtune.leopard.in.ua/
pg_hba.conf 客户端认证
# 位置：PostgreSQL 数据目录内的 pg_hba.conf
# 格式: TYPE DATABASE USER ADDRESS METHOD
# 本地 socket 连接（默认信任）
local all all peer
# IPv4 本地（密码认证）
host all all 127.0.0.1/32 scram-sha-256
# IPv4 远程（应用服务器）
host myapp appuser 192.168.1.0/24 md5
# IPv6
host all all ::1/128 scram-sha-256
# 修改后重载
# SELECT pg_reload_conf();
# 或 sudo systemctl reload postgresql备份与恢复
# === pg_dump：逻辑备份 ===
# 备份单个数据库
pg_dump -U postgres myapp > myapp_backup.sql
# 自定义格式（可并行恢复、压缩）
pg_dump -U postgres -Fc myapp > myapp_backup.dump
# 仅备份 schema（不含数据）
pg_dump -U postgres --schema-only myapp > myapp_schema.sql
# 仅备份数据
pg_dump -U postgres --data-only myapp > myapp_data.sql
# 备份所有数据库
pg_dumpall -U postgres > all_backup.sql
# 恢复
psql -U postgres myapp < myapp_backup.sql
pg_restore -U postgres -d myapp myapp_backup.dump
# === pg_dump 并行备份（高性能）===
pg_dump -U postgres -Fd -j 4 -f /backup/myapp_dir myapp
# -Fd: 目录格式, -j 4: 4个并行job
# === pg_basebackup：物理备份 ===
pg_basebackup -U postgres -D /backup/basebackup -Ft -z -P
# -Ft: tar 格式, -z: gzip, -P: 显示进度
# === 连续归档（WAL 归档）===
# postgresql.conf 中启用:
# wal_level = replica
# archive_mode = on
# archive_command = 'cp %p /archive/%f'
# === 自动备份脚本 ===
#!/bin/bash
DB="myapp"
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
export PGPASSWORD="your_password"
pg_dump -U postgres -Fc "$DB" > "$BACKUP_DIR/${DB}_${DATE}.dump"
find "$BACKUP_DIR" -name "${DB}_*.dump" -mtime +7 -delete25.4 SQLite
SQLite 是一个文件级嵌入式数据库，无需服务器进程，非常适合桌面应用、移动端和轻量级 Web 应用。
安装与使用
# 几乎所有发行版都预装 sqlite3 命令行工具
# Debian/Ubuntu: sudo apt install sqlite3
# RHEL/Fedora: sudo dnf install sqlite
# Arch: sudo pacman -S sqlite
# 创建/打开数据库文件
sqlite3 myapp.db
# 创建表
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL,
created_at TEXT DEFAULT (datetime('now'))
);
# CRUD 操作（SQL 语法与标准 SQL 高度一致）
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
SELECT * FROM users;
UPDATE users SET email = 'alice_new@example.com' WHERE username = 'alice';
DELETE FROM users WHERE id = 1;
# 元命令（点命令）
.tables # 列出所有表
.schema users # 查看建表语句
.mode column # 列对齐显示
.headers on # 显示列名
.output result.txt # 结果输出到文件
.import data.csv users # CSV 导入
.backup backup.db # 备份数据库
.quitSQLite 备份
# .backup 命令
sqlite3 myapp.db ".backup 'myapp_backup.db'"
# 命令行 dump
sqlite3 myapp.db .dump > myapp_dump.sql
sqlite3 restored.db < myapp_dump.sql # 恢复
# 直接文件复制（需确保无写入连接）
cp myapp.db myapp_backup.db25.5 连接字符串参考
应用连接数据库时需要的连接字符串格式：
# SQLite（本地文件）
sqlite:///path/to/database.db
# MySQL / MariaDB
mysql://username:password@host:3306/database?charset=utf8mb4
# 通过 Unix socket
mysql://username:password@localhost/database?unix_socket=/run/mysqld/mysqld.sock
# PostgreSQL
postgresql://username:password@host:5432/database?sslmode=require
# 通过 Unix socket
postgresql://username:password@/database?host=/run/postgresql
25.6 数据库安全基础
# 1. 限制监听地址
# MySQL: bind-address = 127.0.0.1
# PostgreSQL: listen_addresses = 'localhost'
# 2. 使用强密码和 SCRAM-SHA-256（PostgreSQL）或 caching_sha2_password（MySQL 8+）
# ALTER USER appuser WITH PASSWORD 'new_strong_password';
# 3. 最小权限原则
# - 应用用户只需 CRUD 权限
# - 管理员权限与日常操作分离
# 4. 启用 TLS 加密连接
# PostgreSQL: ssl = on, ssl_cert_file / ssl_key_file
# MySQL: 配置 SSL 证书
# 5. 定期审计用户权限
# MySQL: SELECT user, host FROM mysql.user;
# PostgreSQL: \du
# 6. 检查并删除无密码用户
# MySQL: SELECT user, host FROM mysql.user WHERE authentication_string='';25.7 相关章节
- 58-数据库运维(主从+备份+优化) — 数据库高级运维：主从复制、高可用、性能调优
- 12-存储管理与磁盘操作 — 磁盘与存储管理（数据库存储规划）
- 28-系统安全加固与审计 — 系统安全加固
小结：三个数据库各有定位——SQLite 适合嵌入式和轻量场景，MariaDB/MySQL 是 Web 应用的传统选择，PostgreSQL 在 SQL 标准合规、扩展性和复杂查询方面遥遥领先。无论选择哪个，掌握基本的 SQL 操作、用户权限管理和备份恢复是数据库管理的基本功。