🔬 备份还原与日志 · 上机实验

📚 第10章配套实验 ⏱️ 预计 50 分钟 💻 MySQL 8.0+ 🪟 需要"系统命令行 + MySQL 客户端"两个窗口
🎯 本节实验目标
  • 区分"系统命令行"和"MySQL 客户端"，命令在哪里执行不会搞错
  • 动手用 mysqldump 备份一个真实数据库
  • 故意删除数据，再用 mysql 命令还原回来
  • 查看错误日志、二进制日志的实际内容
  • 开启慢查询日志，触发并捕获一个慢 SQL

🛠️ 环境准备第 0 步

本次实验的命令分两类，千万不要搞错执行环境：
🟢 SHELL = 系统命令行（CMD / PowerShell / Linux 终端）
🔵 MYSQL = MySQL 客户端里

⚠️ 学生最爱犯的错

把 mysqldump 写到 MySQL 里去执行 —— 一定会报错！

判断方法：命令是 mysql 开头（mysql / mysqldump / mysqlbinlog）→ 在系统命令行；命令是 SQL 关键字开头（SELECT / SHOW / CREATE）→ 在 MySQL 里。

第 1 步：建一个用来玩的数据库

在 MySQL 客户端里 执行下面的 SQL，建好实验用的 teaching 数据库（教师 + 课程 + 选课）：

MYSQL · 一键建库
DROP DATABASE IF EXISTS teaching;
CREATE DATABASE teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 学生表
CREATE TABLE s (
  sno  CHAR(8)   PRIMARY KEY,
  sn   VARCHAR(20) NOT NULL,
  age  INT,
  dept VARCHAR(30)
);

INSERT INTO s VALUES
('20240001', '张文博', 19, '计算机学院'),
('20240002', '李清扬', 20, '计算机学院'),
('20240003', '王明远', 19, '数学学院'),
('20240004', '赵小晴', 21, '外语学院');

-- 课程表
CREATE TABLE c (
  cno  CHAR(4) PRIMARY KEY,
  cn   VARCHAR(30) NOT NULL,
  credit INT
);

INSERT INTO c VALUES
('C001', '数据库原理', 3),
('C002', '高等数学', 4);

-- 验证
SELECT COUNT(*) AS 学生数 FROM s;
SELECT COUNT(*) AS 课程数 FROM c;
学生数 = 4，课程数 = 2。如果对不上，重新跑一遍上面的 SQL。

第 2 步：找到 mysqldump 命令在哪

本次实验大量使用 mysqldump 命令。如果你打开 系统命令行 直接输 mysqldump 提示"不是内部或外部命令"，说明 MySQL 没加到 PATH 里。

解决办法

🪟 Windows 用户

① 找到 MySQL 安装目录的 bin 文件夹，一般是：
C:\Program Files\MySQL\MySQL Server 8.0\bin

② 在系统命令行（CMD）里先 cd 到这个目录：

CMD
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

③ 然后再执行后面的 mysqldump 命令就 OK 了。

🐧 Linux / macOS 用户

一般 MySQL 安装时已经把 mysqldump 加到 PATH 了，直接打开终端用就行。

第 3 步：准备一个用来存备份的目录

本次实验我们把所有备份文件都存到一个固定目录，方便管理：

📌 后面的命令我会用 Windows 路径示例

Linux/Mac 用户把 D:/db_backup/ 替换成 ~/db_backup/ 就行（注意：命令里用正斜杠 / ，不要用反斜杠）。

📝 第一部分 · 概念巩固不动手 · 10 分钟

先做几道概念题，把讲义里的关键点过一遍。差异 vs 增量、命令辨析 是必考点。

练习 1.1 ⭐ 基础 命令辨析

下列 4 条命令，哪一条是 正确的备份命令？（注意符号方向）

📖 查看答案

✅ 答案：B

速记口诀："备份用 dump 出（>），还原用 mysql 入（<）"

A 用 mysql 不对（mysql 是用来还原的）
C 符号反了（备份是数据"流出"到文件，符号要朝右 >）
D 是 还原命令，但题目问的是"备份"

练习 1.2 ⭐⭐ 中等 差异 vs 增量（必考！）

某公司每周日做完全备份，工作日做增量备份。周四下午数据库损坏，需要恢复到周三晚上的状态。需要用到几个备份文件？

📖 查看答案与分析

✅ 答案：D · 4 个

用 增量备份恢复，需要：完全备份 + 中间所有增量备份。

所以：周日完全 + 周一增量 + 周二增量 + 周三增量 = 4 个文件。

如果题目改成"差异备份"，那就只需要 2 个（周日完全 + 周三差异）—— 这是上一道题的情况，对比记忆。

练习 1.3 ⭐ 基础 备份分类

下列关于 mysqldump 的说法，正确 的是？

📖 查看答案

✅ 答案：B

mysqldump 导出的 .sql 文件就是文本形式的 SQL 语句（CREATE TABLE / INSERT INTO 等），所以是逻辑备份，能用记事本打开看。

A 错误（物理备份是直接复制 .frm/.ibd 等文件），C 错误（在系统命令行执行），D 错误（.sql 文件就是文本）。

练习 1.4 ⭐⭐ 中等 日志功能匹配

下面 4 个场景，应该看哪种日志？请在心里配对。

① MySQL 突然崩了，想知道是什么原因导致的

② 想恢复数据库到昨天下午 3 点的状态

③ 想审计：上周某用户具体执行了哪些 SELECT 查询

④ 系统运行变慢，想找出哪些 SQL 写得不好需要优化

📖 查看答案

① → 错误日志（专门记录启动、停止、崩溃等异常）

② → 二进制日志（记录所有数据修改，配合备份做时间点恢复）

③ → 通用查询日志（记录所有用户操作，包括 SELECT；需要事先开启）

④ → 慢查询日志（记录执行慢的 SQL，是优化的好帮手）

练习 1.5 ⭐ 基础 日志默认状态

关于 4 种日志，下列说法 错误 的是？

📖 查看答案

✅ 答案：C 错误

通用查询日志和慢查询日志 默认都是关闭 的（开了会产生大量日志，影响性能）。

错误日志和二进制日志默认开启。

🔬 第二部分 · 上机实验动手做 · 30 分钟

本部分是 核心！ 你要 亲手 完成一次完整的"备份 → 删除数据 → 还原"流程，并查看日志。

实验 2.1 🔬 实验 备份整个数据库

用 mysqldump 备份 teaching 整个数据库。

1打开系统命令行SHELL

Windows：按 Win+R 输入 cmd 回车。Linux/Mac：打开"终端"。

如果 mysqldump 不在 PATH 里，先 cd 到 MySQL 的 bin 目录（参考"环境准备"第 2 步）。

2执行备份命令SHELL
SHELL
mysqldump -u root -p teaching > D:/db_backup/teaching_full.sql

回车后会要求输入 root 用户的密码，输入即可（密码不会显示，输完回车）。

命令执行后没有任何输出，但是 D:/db_backup/ 文件夹下应该出现了一个 teaching_full.sql 文件。
3用记事本打开 .sql 文件看一眼SHELL

用记事本（或 VS Code）打开 teaching_full.sql，你会看到类似这样的内容：

-- MySQL dump 10.13 Distrib 8.0.32, for Win64 (x86_64) -- Host: localhost Database: teaching ... -- -- Table structure for table `s` -- DROP TABLE IF EXISTS `s`; CREATE TABLE `s` ( `sno` char(8) NOT NULL, `sn` varchar(20) NOT NULL, `age` int DEFAULT NULL, `dept` varchar(30) DEFAULT NULL, PRIMARY KEY (`sno`) ); INSERT INTO `s` VALUES ('20240001','张文博',19,'计算机学院'), ('20240002','李清扬',20,'计算机学院'), ...
💡 验证你的理解

看到了吗？.sql 文件里全是 建表语句 + 插入语句。所谓"还原"，就是把这些 SQL 重新执行一遍，表自然就回来了。这就是 逻辑备份 的本质。

实验 2.2 🔬 实验 备份单张表

只备份 teaching 库里的 s 表（学生表）。

SHELL
mysqldump -u root -p teaching s > D:/db_backup/teaching_s_only.sql

关键区别：在 teaching 后面加了一个 s（表名）。

生成的 teaching_s_only.sql 文件 只包含 s 表 的建表和数据，不包含 c 表。打开看看是不是这样。
💡 思考一下

如果想同时备份 s 和 c 两张表，命令该怎么写？

看答案

用空格分隔多个表名：

mysqldump -u root -p teaching s c > D:/db_backup/teaching_s_c.sql

实验 2.3 🔬 实验 · 重点 事故 + 还原 全流程

这是本次实验最重要的一个：模拟"数据库被删了"，然后用刚才的备份 teaching_full.sql 把它救回来。

1制造一场"事故"：删除整个 teaching 数据库MYSQL
MYSQL
-- 模拟事故：DROP 掉整个数据库（生产环境千万别这么干 ⚠️）
DROP DATABASE teaching;

-- 验证一下：teaching 数据库不见了
SHOW DATABASES;
在 SHOW DATABASES; 的结果里，找不到 teaching 了。数据全部丢了！😱
2还原数据库：第一步先建一个空库MYSQL

因为我们刚才用的是 mysqldump teaching（没加 --databases），备份文件 不包含 CREATE DATABASE 语句，所以要先手动建一个空库：

MYSQL
CREATE DATABASE teaching DEFAULT CHARACTER SET utf8mb4;
3还原数据：用 mysql 命令导入 .sql 文件SHELL

注意：换回系统命令行执行！

SHELL
mysql -u root -p teaching < D:/db_backup/teaching_full.sql

注意 3 个变化：① mysqldump → mysql；② > → <；③ 文件还是同一个。

4验证数据回来了MYSQL
MYSQL
USE teaching;
SHOW TABLES;
SELECT * FROM s;
s 表和 c 表都回来了！ s 表 4 条数据原封不动，c 表 2 条数据也都在。
恭喜你，你完成了一次真实的"灾难恢复"！🎉
💡 总结：备份 + 还原的完整流程

① 备份：mysqldump -u root -p 数据库 > 文件.sql（系统命令行）

② 事故发生：数据丢失 / 损坏

③ 准备：CREATE DATABASE 数据库;（MySQL 里创建空库）

④ 还原：mysql -u root -p 数据库 < 文件.sql（系统命令行）

⑤ 验证：进 MySQL 看数据是否回来了

实验 2.4 🔬 实验 查看错误日志和二进制日志

看看 MySQL 的日志文件实际长什么样。

1查看错误日志的位置MYSQL
MYSQL
SHOW VARIABLES LIKE '%log_error%';

输出里 log_error 那一行的 Value 就是错误日志的路径，比如：.\hostname.err（在 MySQL 数据目录下）。

记下错误日志的完整路径，用记事本打开看一眼。里面是 MySQL 启动 / 停止 / 错误的记录，每行都有时间戳。
2查看二进制日志是否开启MYSQL
MYSQL
-- 查看是否开启
SHOW VARIABLES LIKE 'log_bin';

-- 列出所有二进制日志文件
SHOW BINARY LOGS;

① log_bin 的 Value 应该是 ON（MySQL 8 默认开启）

② SHOW BINARY LOGS 会列出一堆形如 binlog.000001、binlog.000002 的文件 —— 这些就是二进制日志。

⚠️ 注意

二进制日志是二进制的，不能用记事本直接打开看。要用 mysqlbinlog 工具才能转成可读文本。这里只要知道它的存在就行，命令细节不用记。

实验 2.5 🔬 实验 捕获一个慢 SQL

开启慢查询日志，故意写一个慢 SQL，然后看它被记录下来。

1查看慢查询日志当前状态MYSQL
MYSQL
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

默认 slow_query_log = OFF，long_query_time = 10（秒）。

2开启慢查询日志，把阈值改小MYSQL
MYSQL
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 把阈值改成 1 秒（默认是 10 秒，太长不好测试）
SET GLOBAL long_query_time = 1;

-- ⚠️ 关键：long_query_time 在当前会话里不会立刻生效
-- 必须断开重连，或者重新打开一个 MySQL 客户端
⚠️ 容易踩的坑

修改 long_query_time 之后，当前已连上的会话不会立刻生效。请退出 MySQL 客户端再重新登录，新会话才会用 1 秒阈值。

3故意写一条慢 SQL（让它跑超过 1 秒）MYSQL
MYSQL
-- SLEEP(2) 函数让查询睡 2 秒，肯定超过 1 秒阈值
SELECT SLEEP(2);

这条 SQL 会"假装运行" 2 秒后返回，肯定超过 1 秒阈值，会被慢查询日志记录。

4查看慢查询日志的位置和文件MYSQL
MYSQL
SHOW VARIABLES LIKE 'slow_query_log_file';

输出的路径就是慢查询日志文件，类似 ...\hostname-slow.log。

5用记事本打开慢查询日志文件
日志里应该能看到刚才的 SLEEP(2) 这条 SQL，类似： 
# Time: 2024-...
# User@Host: root[root] @ localhost
# Query_time: 2.001234  Lock_time: 0.000012
SET timestamp=...;
SELECT SLEEP(2);
💡 实际工作中

真实环境里，慢查询日志会记录所有没加索引、大表全表扫描等慢 SQL，DBA 定期分析这个日志，给慢 SQL 加索引、改写法 —— 这就是 SQL 性能优化的起点。

6实验完毕，关掉慢查询日志（避免占空间）MYSQL
MYSQL
SET GLOBAL slow_query_log = OFF;

🎯 第三部分 · 综合应用10 分钟

最后做几道综合题，把整章知识连起来。

综合题 1 ⭐⭐ 中等 写命令

写出完成下面 3 个任务的 mysqldump 命令（都备份到 D 盘 db_backup 目录）：

  1. 把 teaching 库里 所有表 备份成 all_tables.sql
  2. 把 teaching 和 bankaccount 两个数据库 备份成 two_dbs.sql
  3. 把 teaching 库里 只有表结构没有数据 备份成 structure_only.sql
📖 查看答案

① mysqldump -u root -p teaching > D:/db_backup/all_tables.sql

② mysqldump -u root -p --databases teaching bankaccount > D:/db_backup/two_dbs.sql

③ mysqldump -u root -p --no-data teaching > D:/db_backup/structure_only.sql

记忆要点：备份多个库 → --databases；只要结构 → --no-data。

综合题 2 ⭐⭐ 中等 日志选择

某天 MySQL 出了 4 个问题，请把每个问题对应到应该查看的日志：

① 早上来公司发现数据库无法启动了

② 用户反映系统响应越来越慢

③ DBA 想恢复到昨天 18:00 的数据状态

④ 发现某用户疑似越权查询了不该查的数据，想审计

📖 查看答案

① → 错误日志（启动失败的原因都在这里）

② → 慢查询日志（找出哪些 SQL 拖慢了系统）

③ → 二进制日志（结合昨天的备份 + 二进制日志，可以恢复到 18:00）

④ → 通用查询日志（记录所有 SELECT；前提是事先开启了）

综合题 3 ⭐⭐⭐ 进阶 备份策略设计

假设你是一家中型电商公司的 DBA，老板要求："数据不能丢，最坏情况能找回最近 1 小时内的数据"。请简要说明你会怎么设计备份策略？

📖 参考答案

这道题没有唯一答案，关键看是否覆盖以下要点：

① 完全备份：每周日凌晨做一次（业务低峰期），用 mysqldump --all-databases。

② 增量 / 差异备份：每天凌晨做一次（差异备份恢复快，增量备份占空间小，二选一）。

③ 二进制日志：保持开启，定期归档。靠它能"基于时间点"恢复，把数据找回到事故前一刻。

④ 异地存储：备份文件不能只放在 MySQL 同一台机器上 —— 机房失火怎么办？要传到另一台服务器、对象存储或 NAS 上。

⑤ 定期演练：备份不是做完就完了，要 定期测试还原，确保备份是真的"能用的"，否则关键时刻才发现备份是坏的就完蛋了。

💡 核心思路：完全 + 增量/差异 + 二进制日志 + 异地，缺一不可。

📝 实验小结

通过本次实验你应该已经：

⭐ 复习重点

下次考试本章一定考的内容：

  1. 差异备份 vs 增量备份：恢复需要几个文件（必考！）
  2. mysqldump 命令格式：可能让你写出备份语句
  3. 备份和还原的命令对比：> vs < 不要搞反
  4. 4 种日志的功能：哪种日志干什么用的
  5. 二进制日志的作用：为什么它在数据恢复中那么重要