数据库备份还原和日志管理

数据库的"保险" —— 怎么把数据存好、怎么在出事时找回来

🎯 本节课你需要掌握的（按重要程度排序）
  • 为什么要备份、备份的 3 种分类方式 必考
  • 用 mysqldump 备份数据库的命令 必考
  • 用 mysql 命令还原数据库 必考
  • MySQL 4 种日志各是干什么的 必考
  • 导入导出文件、数据库迁移的基本思路 了解

💾 先想象一个场景：

你花了一个月做毕业设计，文件存在 D 盘里。今天开机，D 盘损坏了 ——一个月的心血一夜归零。

这时你才后悔："为什么我没有把它复制一份到 U 盘 / 网盘？"

👉 这就是"备份"的意义。数据库里存的是公司的核心资产（订单、用户、账号余额……），万一服务器硬盘坏了，没备份就 = 公司直接破产。

本章就讲：怎么给数据库做备份、出事了怎么还原、日常怎么记录数据库的所有操作。

一、备份与还原是什么？重点

1.1 两个最基本的概念必考

📥 数据库备份（Backup）

把数据库里的数据"复制一份"保存起来，以防万一。

比如把数据库导出成一个 .sql 文件，保存到另一台电脑或网盘上。

📤 数据库还原（Restore）

当数据库出事了，用之前的备份把数据"恢复回来"。

也叫"恢复"。一般是把 .sql 文件重新导入到 MySQL 中。

🔑 一句话总结

备份 = 存档（把数据存起来，多份保险）
还原 = 读档（用存档把数据找回来）

两者是 互为一体 的：备份是基础，还原是目的。

🌰 通俗类比

就像玩游戏的 "存档 / 读档" —— 存档是为了万一打 boss 失败，能从存档点重来。

备份的两个作用了解

  • ① 防止数据丢失（硬盘坏、误删、被攻击……）
  • ② 把数据库"恢复到某个之前的状态"（比如发现今天的操作搞错了，想回到昨天的样子）

二、备份的 3 种分类方式核心重点

同一种东西，可以从 3 个不同角度 来分类。必考点：能说出每种类型的定义和区别。

2.1 按内容分：物理备份 vs 逻辑备份

💾 物理备份

直接 复制数据库的物理文件（硬盘上的文件）。

就像直接 Ctrl+C 文件夹一样，复制的是 .frm、.ibd 这些底层文件。

📝 逻辑备份

导出 SQL 语句（CREATE TABLE、INSERT INTO 等），把"怎么建表+怎么插数据"用文字写下来。

还原时重新执行这些 SQL 就行了。

💡 通俗对比

物理备份 = 直接拷贝 Word 文件本身（.docx）
逻辑备份 = 把文档内容用文字描述出来，让别人重新打一遍

本章重点学的 mysqldump 就是 逻辑备份工具。

2.2 按"是否在线"分：冷 / 温 / 热必考

区别就一句话：备份的时候，数据库是 关 的、半开 的、还是 全开 的？

🧊 冷备份

关闭数据库再备份。最稳妥，但服务要中断。

🌤️ 温备份

数据库 正在运行，但只允许 读，不允许 写。

🔥 热备份

数据库 正常运行，读写都可以。技术难度最高。

🌰 用饭店类比

给厨房做卫生大扫除：

冷备份= 关门停业大扫除 ✅ 干净彻底，但当天不营业

温备份= 营业但只接老顾客，不收新客 ⚠️ 部分受影响

热备份= 边营业边打扫 ✅ 不影响生意，但需要技术好的清洁工

2.3 按"备份范围"分：完全 / 差异 / 增量必考

这三种的区别是 "每次备份多少东西"。学生最容易混淆 差异备份 和 增量备份，老师最爱考。

📦 完全备份

每次都备份 全部数据。简单粗暴，但占空间大、慢。

📊 差异备份

只备份"自上一次完全备份以来"修改过的部分。

➕ 增量备份

只备份"自上一次任何备份（完全或增量都算）以来"修改过的部分。

⭐ 易混点：差异 vs 增量

它们都不是"全部备份"，区别就在 "参照物"：

👉 差异备份：参照物 永远是上一次"完全备份"。所以越往后备份的内容越多。

👉 增量备份：参照物是 上一次"任何备份"（前一次差异 / 前一次增量都算）。所以每次只备份"昨天到今天"的变化，每次都很小。

用一周时间举例理解（这是考点）

假设周日做完全备份，每天数据有变化：

周次 策略：完全 + 差异 策略：完全 + 增量
周日完全备份（全部）完全备份（全部）
周一差异：备份 周一变化增量：备份 周一变化
周二差异：备份 周一+周二变化增量：只备份 周二变化
周三差异：备份 周一+周二+周三变化增量：只备份 周三变化
💡 一句话区别

差异备份每次都越来越胖（累积自完全备份后所有变化）
增量备份每次都很苗条（只备份"自上一次"的变化）

⚠️ 还原时的差异

差异恢复：只需"完全备份 + 最近一次差异" = 2 个文件 ✅ 简单

增量恢复：需要"完全备份 + 中间所有增量备份" = n+1 个文件 ❌ 复杂、慢

所以策略选择是：差异备份占空间多但还原快，增量备份占空间少但还原慢。

2.4 备份策略简介了解

实际生产中，常见的备份策略组合是：

📋 典型策略
  • 完全 + 增量 + 二进制日志（占空间小，恢复需要二进制日志补差）
  • 完全 + 差异 + 二进制日志（恢复快，空间稍多）
  • 异地备份（防止机房失火/停电导致全部丢失）

💡 在做完整还原或基于时间点的还原时，都需要 二进制日志（BINLOG） 配合。所以 MySQL 一般要打开 log-bin 选项。后面讲日志时会再讲。

三、用 mysqldump 备份核心重点

3.1 备份的两个原则了解

🎯 备份的 8 字真言

① 尽早、经常 —— 越早开始备份越好，要养成定期备份的习惯

② 多地、多份 —— 不要只放一个地方，要存到多个不同位置

3.2 mysqldump 命令格式必考

mysqldump 是 MySQL 自带的 逻辑备份工具，把数据库导出成一个 SQL 文件。

$ mysqldump -u 用户名 -p 数据库名 [表名...] > 备份文件.sql
  • -u 用户名：登录 MySQL 的用户（通常是 root）
  • -p：要求输入密码（注意 -p 后不要带空格就直接写密码也行）
  • 数据库名：要备份的数据库
  • [表名...]：要备份的表（省略 = 备份整个数据库）
  • > 备份文件.sql：把结果重定向到这个 .sql 文件里（这是 Linux/Windows shell 的语法）
⚠️ 重要提醒

这条命令是在 系统命令行 里执行的（CMD / PowerShell / Linux 终端），不是在 MySQL 里执行！很多同学会搞错。

简单判断：以 mysqldump 开头 → 系统命令行；以 SELECT / SHOW 等开头 → MySQL 里。

3.3 mysqldump 的几种用法必考

① 备份单张表

SHELL
# 备份 teaching 库中的 s 表到 D 盘
mysqldump -u root -p teaching s > d:/teaching_s_backup.sql

② 备份多张表

SHELL
# 用空格分隔多个表名
mysqldump -u root -p teaching s c > d:/teaching_s_c.sql

③ 备份整个数据库（最常用）

SHELL
# 不写表名 = 备份这个库的全部表
mysqldump -u root -p teaching > d:/teaching.sql

# 也可以加 --databases 参数，备份文件里会包含 CREATE DATABASE 语句
mysqldump -u root -p --databases teaching > d:/teaching.sql

④ 备份多个数据库

SHELL
# 用 --databases，多个库名用空格隔开
mysqldump -u root -p --databases teaching bankaccount > d:/two_dbs.sql

⑤ 备份所有数据库

SHELL
# --all-databases 备份服务器上所有数据库
mysqldump -u root -p --all-databases > d:/all.sql

⑥ 只备份表结构（没有数据）了解

SHELL
# --no-data 表示不要数据，只要 CREATE TABLE 这些"表的骨架"
mysqldump -u root -p --no-data teaching > d:/teaching_structure.sql

# 反过来，--no-create-info 只要数据，不要建表语句
mysqldump -u root -p --no-create-info teaching > d:/teaching_data.sql
⭐ 必背记忆模板

考试遇到"写出备份命令"，记住骨架就行：

mysqldump -u 用户 -p 数据库名 [表名] > 文件路径

只有多个库、所有库、只要结构这几种特殊情况要加特殊参数：--databases / --all-databases / --no-data。

四、用 mysql 命令还原核心重点

4.1 还原的本质 = 重新执行备份文件里的 SQL必考

🔑 核心思路

之前 mysqldump 备份出来的 .sql 文件里，全是 CREATE TABLE、INSERT INTO 等 SQL 语句。

所谓"还原"，就是把这些 SQL 再执行一遍 —— 表自然就建好了，数据也插回去了。

还原命令格式

$ mysql -u 用户名 -p [数据库名] < 备份文件.sql
  • 注意 是 mysql 不是 mysqldump（备份用 dump，还原用 mysql）
  • 用 < 把文件 "输入"给 mysql 命令（备份用 >，还原用 <，符号反过来）
  • [数据库名]：如果备份文件里 已经包含 CREATE DATABASE（用了 --databases 或 --all-databases），可以省略

例子：还原 teaching 数据库

SHELL
# 第一步：先在 MySQL 里建一个空库（如果备份文件不含 CREATE DATABASE）
# 在 MySQL 里执行：CREATE DATABASE teachingback;

# 第二步：在系统命令行执行还原
mysql -u root -p teachingback < d:/teaching_backup.sql
💡 一句话对比

备份：mysqldump ... > 文件.sql（数据"流出"到文件）

还原：mysql ... < 文件.sql（文件"流入"数据库）

命令换了、符号反了 —— 就这两点区别。

五、数据导入和导出了解

5.1 导入导出 vs 备份还原

💬 它们的区别

备份还原：得到的是 SQL 文件（包含建表+数据），主要给 MySQL 自己用。

导入导出：得到的是 普通文本文件（如 .txt、.csv、.xls），可以给 Excel、其他程序用。

简单说：备份还原是"MySQL 内部的事"，导入导出是"和外部世界打交道"。

5.2 导出：SELECT ... INTO OUTFILE

用一条 SQL 把查询结果导出成文件：

SQL · 导出 s 表到文本
SELECT * FROM teaching.s
  INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/teaching_s.txt'
  FIELDS TERMINATED BY ','          -- 字段之间用逗号分隔
  OPTIONALLY ENCLOSED BY '"';     -- 字符串字段用双引号包起来
⚠️ 一个常见的坑：secure-file-priv

MySQL 出于安全考虑，只允许导出到指定的目录。如果你写 'd:/xxx.txt' 可能会报错。

解决办法：

① 先在 MySQL 里查看允许的目录：SHOW VARIABLES LIKE '%secure%';

② 把文件路径改成查询结果显示的目录（一般是 C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/）。

💡 导出的文件可以是 .txt、.xls、.xml 等格式，里面就是 纯数据（没有建表语句）。

5.3 导入：LOAD DATA INFILE

从文本文件高速导入数据到表里。前提：表必须已经存在，且字段要和文件中的列对应。

SQL · 从文本导入
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/teaching_s.txt'
  INTO TABLE teaching.s
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n';       -- Windows 换行符
💡 一对相反操作

SELECT ... INTO OUTFILE = 导出（表 → 文件）

LOAD DATA INFILE = 导入（文件 → 表）

它俩的 FIELDS、LINES 选项要一致，不然导回来格式会乱。

六、数据库迁移了解

6.1 什么时候需要迁移？

简单说：数据库从一个地方搬到另一个地方。常见三种情况：

  • ① 换服务器（旧机器淘汰、买了新机器）
  • ② 升级版本（MySQL 5.7 → 8.0）
  • ③ 换数据库系统（MySQL → Oracle 之类，最复杂）

6.2 三种迁移场景

① 同版本迁移（最简单）

停掉 MySQL → 直接复制数据目录到新服务器 → 启动 MySQL。

数据目录位置：Linux 下一般是 /var/lib/mysql，Windows 下一般是 installpath/mysql/data。

② 不同 MySQL 版本迁移

四步走：备份原数据库 → 卸载旧版本 → 安装新版本 → 用 mysql 命令还原。

高版本一般兼容低版本，所以可以直接从低版本"升"到高版本。注意 字符集 可能不一样。

③ 跨数据库系统迁移（最难）

比如 MySQL → Oracle。麻烦的地方在于：

  • 不同数据库的数据类型不一样（MySQL 有 DATE 和 TIME 分开，Oracle 只有 DATE）
  • 不同数据库的SQL 方言不一样（Oracle 是 PL/SQL，SQL Server 是 T-SQL）
  • 需要专门工具：MySQL Migration Toolkit、MyODBC 等
💡 实际工作中

大多数情况下，"迁移"= 用 mysqldump 备份出来，再到新服务器上 mysql 还原回去。这种方法最简单可靠。

七、MySQL 日志管理核心重点

7.1 什么是日志？

📖 一句话定义

日志就是 MySQL 在 "运行过程中产生的记录" —— 谁登录了？执行了什么 SQL？出了什么错？这些都会被记下来。

🌰 通俗类比

就像 飞机的"黑匣子" —— 飞行过程中所有重要事件都被记录，万一出事可以靠它复盘。

7.2 4 种日志全览必考

MySQL 有 4 种日志，每种记录的内容不一样。必考点：每种日志是干什么用的、默认开还是关。

❌
错误日志log-err

记录 MySQL 启动 / 停止过程中的 错误和异常信息。

📌 用途：MySQL 突然崩了？看错误日志找原因。

✓ 默认开启（不能关）
📜
二进制日志log-bin

记录所有 修改数据（INSERT/UPDATE/DELETE）的操作。

📌 用途：用于数据恢复！结合备份能恢复到任意时间点。

✓ 默认开启（很重要）
📋
通用查询日志log

记录 所有用户操作（包括 SELECT 和增删改查全部）。

📌 用途：审计、追溯用户做了什么。

✗ 默认关闭（数据量太大）
🐌
慢查询日志log-slow-queries

记录 执行时间超过阈值（默认 10 秒）的查询。

📌 用途：找出"拖慢系统的 SQL"，进行优化。

✗ 默认关闭（手动开启）
⭐ 速记口诀

"错误自动记，二进制助还原，通用记一切，慢查找瓶颈"

记住每种日志的"主要功能"，比记英文名更重要。

7.3 二进制日志：最重要的一种必考

4 种日志里，二进制日志（binlog） 最重要，因为它是 数据恢复的核心工具。

怎么查看是否开启？

SQL · 在 MySQL 里执行
SHOW VARIABLES LIKE 'log_bin';
-- Value 是 ON 表示开启，OFF 表示关闭

SHOW BINARY LOGS;     -- 查看有哪些二进制日志文件

用二进制日志做"基于时间点的恢复"

💬 真实场景

假设：周日做了完全备份，周三上午 10 点有人误删了重要数据。怎么办？

步骤：

① 先用周日的备份还原 → 数据回到周日的状态。

② 然后用 mysqlbinlog 工具，把"周日 → 周三 10:00"之间的二进制日志重放一遍。

③ 这样就能恢复到周三 10:00（误删之前）的状态！

SHELL · 用日志恢复到指定时间
# 恢复到 2024-03-10 10:00:00 之前的状态
mysqlbinlog --stop-date="2024-03-10 10:00:00" binlog.000005 | mysql -u root -p
⚠️ 注意事项

① 二进制日志 不能直接 rm 删除！会让数据库崩溃。

② 删除要用 PURGE 命令或 RESET MASTER（命令细节不要求记忆，知道就行）。

③ 还原时必须 按编号从小到大 顺序还原（000001 → 000002 → ...）。

7.4 慢查询日志：性能优化的好帮手了解

慢查询日志 默认关闭，需要手动开启：

SQL · 开启慢查询日志
SET GLOBAL slow_query_log = ON;             -- 开启
SET GLOBAL long_query_time = 2;             -- 阈值改为 2 秒

-- 查看当前阈值
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
💡 实际用途

开启慢查询日志后，所有 执行时间超过 2 秒 的 SQL 都会被记下来。运维人员定期分析这个日志，能找出 需要加索引、优化的 SQL。

八、本章小结

📋 三句话总结整章

🎯 核心逻辑
  • 备份 是"存档"，还原 是"读档"。备份是基础，还原是目的。
  • 用 mysqldump 备份，用 mysql ... < 文件.sql 还原。
  • 4 种日志各有用途，二进制日志最重要 —— 它能配合备份做"任意时间点恢复"。

本章必考点回顾

⭐ 期末考点（按出现频率排）

  1. 3 种备份分类：物理/逻辑、冷/温/热、完全/差异/增量 —— 区别要说清
  2. 差异备份 vs 增量备份：考过最多次的易混点
  3. mysqldump 备份命令：能写出基本格式
  4. mysql 还原命令：注意是 < 不是 >
  5. 4 种日志：每种是干嘛的、默认开还是关
  6. 二进制日志的作用：用于数据恢复

课堂综合测验

第 1 题

某公司周日做完全备份，每天做差异备份。周三晚上发现数据库损坏，需要恢复。需要用到几个备份文件？

A. 1 个（最近的差异备份）
B. 2 个（周日完全 + 周三差异）
C. 3 个（周日完全 + 周一二三的差异）
D. 4 个（周日 + 周一 + 周二 + 周三）

✅ 正确：B。

差异备份 每次都是相对于"上一次完全备份"，所以"周三差异"里已经包含了周一+周二+周三的所有变化。只需要"周日完全 + 周三差异"两个文件。

如果是增量备份，那就需要 4 个文件了（C 是增量备份的答案）。

第 2 题

下列命令哪个是 正确的备份命令？

A. mysql -u root -p teaching > backup.sql
B. mysqldump -u root -p teaching > backup.sql
C. mysqldump -u root -p teaching < backup.sql
D. mysql -u root -p teaching < backup.sql

✅ 正确：B。

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

A 用 mysql 不对（mysql 是还原），C 符号反了，D 是还原命令（也是对的，但题问"备份"）。

第 3 题

数据库突然崩溃，想找出原因，应该看哪个日志？

A. 错误日志
B. 二进制日志
C. 通用查询日志
D. 慢查询日志

✅ 正确：A · 错误日志。

错误日志专门记录 MySQL 启动/停止/异常信息，崩溃原因一定在这里。

二进制日志记录数据修改，用于恢复；通用日志记录所有操作；慢查询日志记录慢 SQL。都不是查崩溃原因用的。

第 4 题

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

A. 错误日志默认是开启的，不能关闭
B. 二进制日志可以用于数据库恢复
C. 慢查询日志默认就是开启的
D. 通用日志会记录所有操作（包括查询）

✅ 正确：C 错误。

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

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

🚀 下节课：上机实验

下节实验课，我们会动手做以下事情：

  • 💾 用 mysqldump 备份一个真实的数据库
  • 🔄 故意删除数据，再用 mysql 命令把它还原回来
  • 📋 查看错误日志、二进制日志的实际内容
  • 🐌 开启慢查询日志，写一个故意慢的 SQL 看看会被记下来
  • 📝 综合应用题

请提前确认：你的电脑上 MySQL 安装好了，并且能找到 mysqldump 命令（一般在 MySQL 的 bin 目录下）。