S2｜数据库入门：SQLite 和 MySQL | AKHH · AI 时代的学习与实践

适用：所有 Linux 耗时：约 35 分钟前置：E1（权限）、S1（会起服务）

数据要存下来、要能查。 这一篇只讲两个数据库：SQLite（文件级，零配置）和 MySQL（服务级，多用户）。

一、先选型：你需要哪一个

	SQLite	MySQL / MariaDB
形态	一个文件	后台服务
安装	系统自带或一行装	装服务 + 初始化
并发	单写入（够小项目）	多用户并发
网络访问	❌ 不支持	✅ 支持
备份	复制文件	导出 SQL
适合	个人工具、小网站、原型	多用户系统、正式业务

判断标准一句话：

只有你一个人用一个程序 → SQLite。需要多个程序/多台机器同时连 → MySQL。

别一上来就 MySQL。 SQLite 能撑住绝大多数个人项目，而且备份只需要 cp。

二、SQLite：三分钟上手

安装

sudo apt install sqlite3      # Debian 系
sudo dnf install sqlite        # RPM 系

很多系统已经自带。

打开/创建数据库

sqlite3 ~/test.db

文件不存在就创建。 进去后是 sqlite> 提示符。

建表和插入

-- 建表
CREATE TABLE notes (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    title   TEXT NOT NULL,
    body    TEXT,
    created TEXT DEFAULT (datetime('now', 'localtime'))
);

-- 插入
INSERT INTO notes (title, body) VALUES ('第一条', '这是内容');
INSERT INTO notes (title, body) VALUES ('第二条', '又一条');

-- 查询
SELECT * FROM notes;
SELECT id, title FROM notes WHERE id > 1;

常用命令（sqlite 内部）

命令	作用
`.tables`	列出所有表
`.schema 表名`	看表结构
`.mode column`	输出对齐
`.headers on`	显示列名
`.quit` / `.exit`	退出
`.dump`	导出整个库为 SQL

退出

.quit

备份：就一个文件

cp ~/test.db ~/test.db.bak-$(date +%Y%m%d)

这就是全部。 没有"导出导入"的复杂度。

但注意：如果程序正在写数据库，直接 cp 可能拷到不一致的状态。安全做法：
sqlite3 test.db ".backup backup.db"
.backup 是 SQLite 内置的在线备份，会处理锁。

一个实用技巧：从命令行直接执行

sqlite3 ~/test.db "SELECT count(*) FROM notes;"
sqlite3 -header -column ~/test.db "SELECT * FROM notes LIMIT 5;"

不用进交互界面，适合写脚本。

三、MySQL / MariaDB：装起来

MariaDB 还是 MySQL

MariaDB 是 MySQL 的分支，命令完全兼容，开源协议更宽松。

新项目建议 MariaDB（大部分 Linux 发行版默认就是它）。

安装

# Debian 系
sudo apt install mariadb-server

# RPM 系
sudo dnf install mariadb-server

启动并设开机自启

sudo systemctl enable --now mariadb
systemctl status mariadb

⚠️ 必须做的安全初始化

sudo mysql_secure_installation

会问你几个问题：

问题	建议
Enter current password for root	直接回车（新装没有）
Switch to unix_socket authentication?	N（保持密码登录，更方便）
Change the root password?	Y，设一个强密码
Remove anonymous users?	Y
Disallow root login remotely?	Y（除非你确实要远程 root）
Remove test database?	Y
Reload privilege tables now?	Y

这一步不做，数据库等于敞着门。

四、MySQL 基本操作

建库建表

-- 建数据库（指定字符集，中文必须）
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE myapp;

-- 建表
CREATE TABLE notes (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    title   VARCHAR(200) NOT NULL,
    body    TEXT,
    created DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入、查询（和 SQLite 基本一样）
INSERT INTO notes (title, body) VALUES ('第一条', '内容');
SELECT * FROM notes;

utf8mb4 而不是 utf8： MySQL 的 utf8 是个历史坑——它只支持最多 3 字节，存不了 emoji 和部分生僻字。utf8mb4 才是真 UTF-8。建库建表一律用 utf8mb4。

常用管理命令

SHOW DATABASES;              -- 列出所有库
SHOW TABLES;                 -- 列出当前库的表
DESC notes;                  -- 看表结构
SHOW CREATE TABLE notes;     -- 看建表语句
SELECT VERSION();            -- 版本
SHOW PROCESSLIST;            -- 当前连接
EXIT;                        -- 退出

五、建一个应用专用账号（别用 root）

应用连数据库不该用 root。 建个只能操作自己库的账号：

-- 本地连接（推荐）
CREATE USER 'appuser'@'localhost' IDENTIFIED BY '强密码这里';

-- 只给 myapp 库的权限
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'localhost';

FLUSH PRIVILEGES;

权限只给需要的——不给 DROP、不给 ALL。这就是 E1 的最小权限原则，在数据库里同样适用。

验证：

mysql -u appuser -p myapp

如果应用在别的机器上

CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY '密码';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'192.168.1.%';

'192.168.1.%' 限定网段，比 '%'（任何地方）安全得多。

⚠️ 监听地址默认只绑 127.0.0.1（好事）。要让别的机器连，需要改配置：
# /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address = 0.0.0.0
但直接暴露 MySQL 到公网极其危险（暴力破解、漏洞扫描）。 正确做法：保持 127.0.0.1，用 SSH 隧道访问（见 T3 的 -L）。

六、备份与恢复

MySQL 备份（用 mysqldump）

# 备份单个库
mysqldump -u root -p myapp > myapp-$(date +%Y%m%d).sql

# 备份所有库
mysqldump -u root -p --all-databases > all-$(date +%Y%m%d).sql

# 带压缩
mysqldump -u root -p myapp | gzip > myapp-$(date +%Y%m%d).sql.gz

恢复

# 先建库（如果没有）
mysql -u root -p -e "CREATE DATABASE myapp CHARACTER SET utf8mb4;"

# 导入
mysql -u root -p myapp < myapp-20261020.sql

# 压缩的
gunzip < myapp-20261020.sql.gz | mysql -u root -p myapp

自动备份脚本

#!/bin/bash
set -euo pipefail

DB="myapp"
USER="root"
DEST="/mnt/backup/mysql"
KEEP=7

mkdir -p "$DEST"
FILE="$DEST/$DB-$(date +%Y%m%d-%H%M).sql.gz"

mysqldump -u "$USER" -p"$MYSQL_PWD" "$DB" | gzip > "$FILE"

echo "✅ $FILE ($(du -h "$FILE" | cut -f1))"

# 只留最近 7 份
ls -1t "$DEST"/*.sql.gz | tail -n +$((KEEP + 1)) | xargs -r rm -f

密码不要写死在命令行里（ps aux 能看到）。用 MYSQL_PWD 环境变量，或者 ~/.my.cnf：
[client]
user=root
password=你的密码
chmod 600 ~/.my.cnf

七、三个常见问题

问题 1：中文变成问号 `????`

原因：字符集不是 utf8mb4。

-- 查当前设置
SHOW VARIABLES LIKE 'character_set%';

-- 改库和表
ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE notes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

连接时也要指定：

mysql --default-character-set=utf8mb4 -u root -p

问题 2：连不上（`Can't connect`）

# 服务在跑吗
systemctl status mariadb

# 在监听吗
sudo ss -ltnp | grep 3306

# 本机能连吗
mysql -u root -p -h 127.0.0.1

本机能、远程不能 → bind-address 还是 127.0.0.1（这是默认且正确的）。

问题 3：忘记 root 密码

需要停服务，用 --skip-grant-tables 进去改。这个操作有风险，建议查官方文档按版本操作（MariaDB 和 MySQL 步骤不同）。

预防：把密码记在密码管理器里。

八、动手：五个练习

练习 1：SQLite 建库、插入、查询

sqlite3 /tmp/practice.db << 'EOF'
CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO t (name) VALUES ('张三'), ('李四');
SELECT * FROM t;
EOF

练习 2：用 `.schema` 和 `.dump`

sqlite3 /tmp/practice.db ".schema"
sqlite3 /tmp/practice.db ".dump" | head -10

练习 3：备份 SQLite（两种方式）

cp /tmp/practice.db /tmp/practice-cp.db
sqlite3 /tmp/practice.db ".backup /tmp/practice-bk.db"
ls -lh /tmp/practice*.db

练习 4：装 MariaDB 并安全初始化

sudo apt install mariadb-server
sudo mysql_secure_installation
sudo mysql -e "SELECT VERSION();"

练习 5：建库建表建用户

CREATE DATABASE testdb CHARACTER SET utf8mb4;
USE testdb;
CREATE TABLE demo (id INT AUTO_INCREMENT PRIMARY KEY, txt VARCHAR(100)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO demo (txt) VALUES ('中文测试');
SELECT * FROM demo;

CREATE USER 'demo'@'localhost' IDENTIFIED BY 'Demo@12345';
GRANT SELECT, INSERT ON testdb.* TO 'demo'@'localhost';
FLUSH PRIVILEGES;

验证新用户：

mysql -u demo -p testdb -e "SELECT * FROM demo;"

九、验证：你过关了吗

能说清 SQLite 和 MySQL 各适合什么
会用 sqlite3 建表、插入、查询
知道 SQLite 备份就是复制文件（或 .backup）
会装 MariaDB 并跑 mysql_secure_installation
建库建表用 utf8mb4
会给应用建专用账号，只授必要权限
会用 mysqldump 备份、用 mysql < 恢复
知道 MySQL 不该直接暴露公网

十、不行怎么办

Q1：ERROR 1698 (28000): Access denied for user 'root'@'localhost' MariaDB/MySQL 新版 root 用 unix_socket 认证，必须 sudo mysql：

sudo mysql

Q2：ERROR 1045 (28000): Access denied 用户名密码错，或该用户没有从该主机连接的权限。检查：

SELECT user, host FROM mysql.user;

Q3：Can't connect to local MySQL server through socket 服务没起来：

sudo systemctl start mariadb
sudo journalctl -u mariadb -n 30

Q4：mysqldump: Got error: 1044: Access denied 备份用户没有 SELECT 或 LOCK TABLES 权限。用 root 备份，或给足权限。

Q5：表里中文是 ???? 见第七节问题 1，改 utf8mb4。

Q6：磁盘被 MySQL 占满了

SHOW BINARY LOGS;        -- 二进制日志（主从复制用，单机可以清）
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

或用 df -h 找到具体文件，见 E5。

Q7：SQLite 报 database is locked 有别的进程在写。SQLite 同一时间只允许一个写入。

# 看谁在占
fuser ~/test.db

要么等，要么程序里加超时重试。

十一、一句话总结

一个人用 SQLite（一个文件，备份靠 cp），多人用 MySQL（一个服务，备份靠 mysqldump）。
建库建表一律 utf8mb4，应用账号别用 root。
MySQL 别暴露公网——要用就 SSH 隧道。

← 上一篇 S1｜搭一个静态网站 下一篇 → S3｜Docker：装一次，到处跑

一、先选型：你需要哪一个#

二、SQLite：三分钟上手#

安装#

打开/创建数据库#

建表和插入#

常用命令（sqlite 内部）#

退出#

备份：就一个文件#

一个实用技巧：从命令行直接执行#

三、MySQL / MariaDB：装起来#

MariaDB 还是 MySQL#

安装#

启动并设开机自启#

⚠️ 必须做的安全初始化#

登录#

四、MySQL 基本操作#

建库建表#

常用管理命令#

五、建一个应用专用账号（别用 root）#

如果应用在别的机器上#

六、备份与恢复#

MySQL 备份（用 mysqldump）#

恢复#

自动备份脚本#

七、三个常见问题#

问题 1：中文变成问号 ????#

问题 2：连不上（Can't connect）#

问题 3：忘记 root 密码#

八、动手：五个练习#

练习 1：SQLite 建库、插入、查询#

练习 2：用 .schema 和 .dump#

练习 3：备份 SQLite（两种方式）#

练习 4：装 MariaDB 并安全初始化#

练习 5：建库建表建用户#

九、验证：你过关了吗#

十、不行怎么办#

十一、一句话总结#