3.6 MySQL/MariaDB 数据库安装与管理

预计阅读时间：17 分钟

学习目标

学完本章后，你将能够：

• 安装并配置 MySQL/MariaDB 数据库服务器
• 创建数据库、用户和权限体系
• 执行建表、增删改查（CRUD）基本操作
• 使用 mysqldump 备份和恢复数据
• 配置慢查询日志和基本性能调优

核心知识

• 关系型数据库——以表（Table）为基本存储单元，表之间通过外键关联
• MySQL——最流行的开源关系型数据库管理系统，使用 SQL 作为查询语言
• MariaDB——MySQL 的社区分支，完全兼容 MySQL，Ubuntu 默认使用
• SQL（Structured Query Language）——用于管理和操作关系型数据库的标准语言
• CRUD——Create（INSERT）、Read（SELECT）、Update（UPDATE）、Delete（DELETE）四种基本数据操作
• 索引（Index）——加速数据检索的数据结构，类似书的目录
• 事务（Transaction）——一组要么全部执行要么全部回滚的数据库操作，保证数据一致性
• mysqldump——MySQL 官方备份工具，将数据库导出为 SQL 文件

知识关联

• 前置知识：1.8：软件包管理 软件包管理、1.9：重定向与管道 重定向与管道
• 后续影响：MySQL 是 3.14：LAMP/LEMP 环境 LAMP/LEMP 环境和 3.3：实战：搭建个人网站 搭建个人网站的重要组件
• 对比技术：3.7：PostgreSQL 数据库 PostgreSQL（另一主流关系型数据库）、3.10：Redis 缓存服务 Redis（非关系型缓存数据库）

原理讲解

关系型数据库的核心概念

关系型数据库以"表"为核心，数据按行（记录）和列（字段）组织。表之间可以通过主键（Primary Key）和外键（Foreign Key）建立关联。SQL 语句分为三类：DDL（数据定义语言，如 CREATE、ALTER）、DML（数据操作语言，如 INSERT、SELECT、UPDATE、DELETE）、DCL（数据控制语言，如 GRANT、REVOKE）。

MySQL vs MariaDB

MySQL 最初由 MySQL AB 开发，2008 年被 Sun 收购，2010 年 Sun 被 Oracle 收购。MariaDB 是 MySQL 原作者 Monty Widenius 因担忧 Oracle 对 MySQL 的控制而创建的分支。两者高度兼容，命令行操作方式几乎一致。Ubuntu 默认安装的是 MariaDB，但 mysql 命令仍然可用。

InnoDB 引擎

InnoDB 是 MySQL 默认的存储引擎，支持事务、行级锁、外键和崩溃恢复。innodb_buffer_pool_size 是最重要的性能参数，表示 InnoDB 用于缓存数据和索引的内存大小，建议设为可用内存的 60-80%。

索引原理：B+Tree 与聚簇索引

InnoDB 的索引底层是 B+Tree：叶子节点存放实际数据（或主键），非叶子节点只存索引键和指针。B+Tree 的特点：所有数据都在叶子节点、叶子节点之间用链表串联，因此范围查询（WHERE id BETWEEN 100 AND 200）只需定位起点后顺序遍历，非常高效。三层 B+Tree 即可容纳约千万级数据，意味着哪怕表有一千万行，定位一条记录也只需 3 次磁盘 I/O。

-- 用 EXPLAIN 判断索引是否生效
EXPLAIN SELECT * FROM posts WHERE author = 'Alice'\G
-- 输出关键行: type=ref  key=idx_author  rows=2  Extra=(空)
-- type=ALL 表示全表扫描；type=ref/range/const 表示走索引

-- 创建索引
CREATE INDEX idx_author ON posts (author);
CREATE UNIQUE INDEX idx_email ON users (email);

-- 复合索引（最左前缀原则：name 必须出现在查询条件最左）
CREATE INDEX idx_name_age ON users (name, age);
-- 生效: WHERE name='A' / WHERE name='A' AND age=18
-- 失效: WHERE age=18（跳过最左列）

-- 删除索引
DROP INDEX idx_author ON posts;

为什么 MySQL 用 B+Tree 而不是 B-Tree 或哈希？

关系型数据库的核心操作是范围查询（WHERE age BETWEEN 20 AND 30）和排序（ORDER BY）。B+Tree 的叶子节点用链表串联，范围查询只需找到起点后顺序扫描即可，时间复杂度 O(log N + K)（K 是结果数）。B-Tree 的数据分散在所有节点，范围查询需要中序遍历整棵树，效率低得多。

哈希索引虽然单点查询更快（O(1)），但完全不支持范围查询和排序——你无法用哈希索引回答"年龄大于 20 的所有记录"。因此哈希索引只适合等值查询的场景（如 Redis 的核心数据结构），不适合通用关系型数据库。

还有一点：B+Tree 的非叶子节点只存键不存数据，一个磁盘页（16KB）能容纳更多索引条目，树的高度更低，磁盘 I/O 次数更少。对于磁盘存储的数据库来说，减少 I/O 次数比减少 CPU 计算更重要。

MySQL vs PostgreSQL：选哪个？

一句话结论：业务简单、吞吐优先选 MySQL；数据完整性、复杂查询、扩展性优先选 PostgreSQL。两边都熟练是最好的状态。

InnoDB 为什么选择聚簇索引？

聚簇索引意味着数据物理上按主键顺序存储。好处是：按主键查询时，一次 B+Tree 查找就能拿到完整行数据，无需"回表"。对于自增主键的顺序插入，聚簇索引还能减少页分裂（新数据总是追加到最后一个页）。代价是：非主键查询需要两次查找（先查二级索引拿主键，再查聚簇索引拿数据），以及主键修改会导致整行数据移动。因此 InnoDB 建议使用自增整数做主键，避免用 UUID（随机插入导致频繁页分裂）。

示例代码

1. 安装与安全初始化

# 安装 MySQL/MariaDB
sudo apt update && sudo apt install mariadb-server -y

# 检查服务状态
sudo systemctl status mariadb
# 输出: ● mariadb.service - MariaDB 10.11.7 database server
#        Active: active (running)

# 运行安全配置向导
sudo mysql_secure_installation
# 交互流程：设置 root 密码 → 删除匿名用户 → 禁止 root 远程登录 → 删除 test 数据库 → 重载权限表

2. 数据库与用户管理

# 登录 MySQL（root）
sudo mysql

# 创建数据库
CREATE DATABASE myblog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 创建用户并授权
CREATE USER 'bloguser'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON myblog.* TO 'bloguser'@'localhost';
FLUSH PRIVILEGES;

# 查看用户权限
SHOW GRANTS FOR 'bloguser'@'localhost';

# 退出
EXIT;

3. 建表与 CRUD 操作

# 使用数据库
USE myblog;

# 创建文章表
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 查看表结构
DESC posts;
# 输出:
# +------------+--------------+------+-----+-------------------+----------------+
# | Field      | Type         | Null | Key | Default           | Extra          |
# +------------+--------------+------+-----+-------------------+----------------+
# | id         | int(11)      | NO   | PRI | NULL              | auto_increment |
# | title      | varchar(200) | NO   |     | NULL              |                |
# | content    | text         | YES  |     | NULL              |                |
# | author     | varchar(100) | YES  |     | NULL              |                |
# | created_at | timestamp    | NO   |     | current_timestamp |                |
# +------------+--------------+------+-----+-------------------+----------------+

# 插入数据（Create）
INSERT INTO posts (title, content, author) VALUES
    ('Hello World', '这是我的第一篇博客', 'Alice'),
    ('Linux 入门', '学习 Linux 的必备知识', 'Bob');

# 查询数据（Read）
SELECT * FROM posts;
SELECT id, title FROM posts WHERE author = 'Alice';
SELECT COUNT(*) FROM posts;

# 更新数据（Update）
UPDATE posts SET content = '更新后的内容' WHERE id = 1;

# 删除数据（Delete）
DELETE FROM posts WHERE id = 2;

4. 备份与恢复

# 备份单个数据库
mysqldump -u root -p myblog > myblog_backup.sql

# 备份所有数据库
mysqldump --all-databases > all_databases.sql

# 一致性备份（InnoDB）
mysqldump --single-transaction mydb > consistent.sql

# 备份压缩（推荐）
mysqldump -u root -p myblog | gzip > myblog_$(date +%Y%m%d).sql.gz

# 恢复数据库
mysql -u root -p myblog < myblog_backup.sql

# 从压缩文件恢复
gunzip < myblog_20260730.sql.gz | mysql -u root -p myblog

5. 性能优化与慢查询

-- 查看 InnoDB 缓冲池大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 输出: 134217728 (128MB，默认值)

-- 设置建议值（假设服务器有 4GB 内存，设为 2GB）
-- 编辑 /etc/mysql/mariadb.conf.d/50-server.cnf:
-- [mysqld]
-- innodb_buffer_pool_size = 2G

-- 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;   -- 超过 2 秒的查询

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';
-- 输出: /var/log/mysql/mariadb-slow.log

-- 命令行查看慢查询
sudo tail -50 /var/log/mysql/mariadb-slow.log

-- 获取优化建议
sudo apt install mysqltuner && sudo mysqltuner

6. 约束与联表查询

-- 带完整约束的建表语句
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE          -- 唯一约束
) ENGINE=InnoDB;

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author VARCHAR(100),
    category_id INT,
    views INT DEFAULT 0 CHECK (views >= 0),   -- 检查约束（8.0+/MariaDB 支持）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_category                        -- 外键约束
        FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE SET NULL
) ENGINE=InnoDB;

-- 内连接：只返回两表都匹配的行
SELECT p.title, c.name
FROM posts p
INNER JOIN categories c ON p.category_id = c.id;

-- 左连接：左侧表全保留，无匹配显示 NULL
SELECT p.title, c.name
FROM posts p
LEFT JOIN categories c ON p.category_id = c.id;

-- 子查询
SELECT * FROM posts
WHERE category_id IN (SELECT id FROM categories WHERE name LIKE 'L%');

-- 聚合 + 分组 + 过滤
SELECT author, COUNT(*) AS cnt, AVG(views) AS avg_views
FROM posts
GROUP BY author
HAVING cnt > 1
ORDER BY avg_views DESC;

7. 用户与权限管理深入

-- 权限粒度：从大到小依次为全局/库/表/列级
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;   -- 超级用户（慎用）
GRANT SELECT, INSERT, UPDATE, DELETE ON myblog.* TO 'writer'@'%';
GRANT SELECT ON myblog.posts TO 'reader'@'%';                    -- 表级
GRANT SELECT (title, content) ON myblog.posts TO 'auditor'@'%';  -- 列级

-- 修改密码（MariaDB 10.4+ 与 MySQL 8.0 均支持）
ALTER USER 'bloguser'@'localhost' IDENTIFIED BY 'new_password';

-- 收回权限
REVOKE DELETE ON myblog.* FROM 'writer'@'%';

-- 删除用户
DROP USER 'olduser'@'localhost';

-- 查看所有用户
SELECT user, host, plugin FROM mysql.user;

-- 生产安全基线：禁止远程 root
-- 1. 删除远程 root 或改为仅本机:
-- DELETE FROM mysql.user WHERE user='root' AND host NOT IN ('localhost','127.0.0.1');
-- FLUSH PRIVILEGES;
-- 2. 只放行应用所在网段:
-- CREATE USER 'app'@'10.0.1.%' IDENTIFIED BY '...';

8. 事务与隔离级别

事务（Transaction）保证一组操作要么全部成功、要么全部回滚，通过 ACID 四个特性定义：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。隔离级别决定多个并发事务之间互相可见的程度：

-- 事务基本用法
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;    -- 或 ROLLBACK;

-- 查看/设置隔离级别
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 实际演示脏读：会话 A 未提交，会话 B 能否看到
-- 会话 A: START TRANSACTION; UPDATE posts SET views = 999 WHERE id = 1;
-- 会话 B: SELECT views FROM posts WHERE id = 1;   -- 默认级别下读到旧值

9. 备份恢复完整流程

# 每日全量备份脚本 /usr/local/bin/db-backup.sh
#!/bin/bash
BACKUP_DIR=/backup/mysql
DATE=$(date +%Y%m%d_%H%M)
mkdir -p $BACKUP_DIR

# 提前创建 ~/.my.cnf 写入 [client] 段密码，避免 -p 在进程列表暴露密码
# 示例：echo -e "[client]\nuser=backup\npassword=实际密码" > ~/.my.cnf && chmod 600 ~/.my.cnf
# --single-transaction 保证 InnoDB 一致性快照，不锁业务表
mysqldump --single-transaction --quick --routines --triggers \
    -u backup myblog | gzip > $BACKUP_DIR/myblog_$DATE.sql.gz

# 删除 7 天前的备份
find $BACKUP_DIR -name '*.sql.gz' -mtime +7 -delete

# crontab 调度：每天凌晨 2 点执行
# 0 2 * * * /usr/local/bin/db-backup.sh >> /var/log/db-backup.log 2>&1

# 恢复演练（每季度一次，验证备份可用）
gunzip < /backup/mysql/myblog_latest.sql.gz | mysql -u root -p myblog_restore_test

# binlog 增量恢复：全量备份之后、误操作之前的变更
# 先找到误操作时间点（MariaDB 的 binlog 文件名前缀是 mariadb-bin）
mysqlbinlog --start-datetime="2026-07-30 10:00:00" \
    --stop-datetime="2026-07-30 10:30:00" /var/log/mysql/mariadb-bin.000123 | less

# 从指定位置重放 binlog（跳过误操作）
mysqlbinlog --start-position=12345 --stop-position=45678 \
    /var/log/mysql/mariadb-bin.000123 | mysql -u root -p myblog

10. 日常运维常用命令

# mysqladmin：轻量管理工具
mysqladmin -u root -p ping            # 检查服务存活（输出: mysqld is alive）
mysqladmin -u root -p status          # 查看运行状态（uptime、线程数、QPS）
mysqladmin -u root -p processlist     # 查看当前会话
mysqladmin -u root -p kill 42         # 杀掉指定会话（排查长事务）
mysqladmin -u root -p extended-status | grep -E "Threads_connected|Slow_queries"
mysqladmin -u root -p flush-logs      # 切换 binlog 日志文件

# 常用状态查询
SHOW STATUS LIKE 'Threads_connected';    -- 当前连接数
SHOW STATUS LIKE 'Max_used_connections'; -- 历史最高连接数
SHOW FULL PROCESSLIST;                   -- 查看正在执行的语句
SHOW ENGINE INNODB STATUS\G              -- InnoDB 详细状态（含死锁信息）

# 慢查询配置（配置文件 /etc/mysql/mariadb.conf.d/50-server.cnf）
# [mysqld]
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/mariadb-slow.log
# long_query_time = 2
# log_queries_not_using_indexes = 1

# 分析慢查询日志 Top SQL
sudo mysqldumpslow /var/log/mysql/mariadb-slow.log | head -20
# 输出: Count: 15  Time=3.21s (48s)  Lock=0.00s ... SELECT * FROM orders WHERE ...

常见错误

最佳实践

练习题

1. （概念）什么是 SQL 中的 DDL、DML 和 DCL？CREATE TABLE、INSERT、GRANT 分别属于哪一类？
2. （概念）innodb_buffer_pool_size 是什么？为什么它是 MySQL 最重要的性能参数？
3. （实操）安装 MariaDB，创建一个名为 inventory 的数据库，创建一个 products 表（字段：id、name、price、quantity、created_at），插入 5 条产品数据，然后查询价格大于 50 的所有产品。
4. （实操）为 inventory 数据库创建一个只读用户（只能执行 SELECT），和一个读写用户（能 INSERT/UPDATE/DELETE）。验证只读用户执行删除操作时会被拒绝。
5. （🔍 挑战）在数据库中创建一张包含 100 万条测试数据的表，分别用 MyISAM 和 InnoDB 引擎建同结构表，对比 SELECT COUNT(*) 的性能差异（MyISAM 直接读计数器、InnoDB 全表扫描），用 EXPLAIN SELECT 分析查询计划。

学习检查点

学完本章后，请检验自己是否掌握以下内容：

本章总结

MySQL/MariaDB 是 Linux 服务端生态中最常用的关系型数据库。掌握 CREATE/USE/GRANT 做权限管理、INSERT/SELECT/UPDATE/DELETE 做数据操作、mysqldump 做备份恢复，是后端开发和运维的基本功。InnoDB 引擎的事务支持和缓冲池调优是性能保障的关键。安全方面始终坚持最小权限原则：每个应用独立数据库和用户，禁用 root 远程登录，定期备份并验证。

速查表

学习路径建议

• 学完本章后建议阅读 3.14：LAMP/LEMP 环境 LAMP/LEMP 环境（LNMP 完整栈搭建）
• 学完本章后建议阅读 db01 SQL 优化与索引调优（EXPLAIN、慢查询、连接池）
• 进阶可看官方 MySQL 文档或《高性能 MySQL》
• NoSQL 对比可看 3.10：Redis 缓存服务 Redis 缓存服务

延伸阅读