课程 › 第 10 章 › 实验三
第 10 章 · 实验三 · 在第五节之后

错误日志与二进制日志

查看错误日志和二进制日志，删除旧的二进制日志，最后用完整备份加二进制日志，把误删的数据还原到出错之前。

环　境

MySQL 8.0 和 MySQL Workbench。备份、还原的命令在 Windows 的命令提示符中执行，查看结果的语句在 Workbench 中执行。

命令提示符

按 Win+R，输入 cmd，按 Enter 键。不要用 PowerShell，它不支持 < 还原。

执　行

命令一次复制一行，出现 Enter password: 时输入 root 的密码，输入时屏幕上不显示。

本次实验的顺序　后一个实验用到前一个的结果

  1. 实验 3-1查看错误日志
  2. 实验 3-2查看二进制日志的状态
  3. 实验 3-3删除二进制日志
  4. 实验 3-4查看二进制日志的内容
  5. 实验 3-5备份后误删了一张表
  6. 实验 3-6用备份和日志还原
第一步

数据准备（必做）

与实验一相同的脚本，执行后 teaching 库回到初始状态。

Workbench　整段复制，全部执行下载 .sql
-- 第 10 章实验数据：重建教材 teaching 库，删除本章实验建的库
-- 在 Workbench 中用 root 连接整段执行。四次实验共用，想从头再做时重新执行即可。
DROP DATABASE IF EXISTS teaching_copy;
DROP DATABASE IF EXISTS teaching_empty;
DROP DATABASE IF EXISTS teaching;
CREATE DATABASE teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 课程表
CREATE TABLE c (
  cno CHAR(4) NOT NULL,
  cn VARCHAR(30) NOT NULL,
  ct INT,
  PRIMARY KEY (cno)
);
-- 学生表
CREATE TABLE s (
  sno CHAR(4) NOT NULL,
  sn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  maj VARCHAR(30),
  dept VARCHAR(30),
  PRIMARY KEY (sno)
);
-- 教师表
CREATE TABLE t (
  tno CHAR(4) NOT NULL,
  tn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  prof VARCHAR(20),
  maj VARCHAR(30),
  dept VARCHAR(30),
  sal DECIMAL(8,2),
  PRIMARY KEY (tno)
);
-- 选课表
CREATE TABLE sc (
  sno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  score DECIMAL(5,2),
  PRIMARY KEY (sno, cno),
  FOREIGN KEY (sno) REFERENCES s (sno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);
-- 授课表
CREATE TABLE tc (
  tno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  tcdate DATE,
  PRIMARY KEY (tno, cno),
  FOREIGN KEY (tno) REFERENCES t (tno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);

INSERT INTO c VALUES
('c1','Java程序设计',40), ('c2','程序设计基础',48), ('c3','线性代数',48),
('c4','数据结构',64), ('c5','数据库系统',56), ('c6','数据挖掘',32),
('c7','高等数学',60), ('c8','控制理论',32);

INSERT INTO s VALUES
('s1','王彤','女',18,'计算机','信息学院'),
('s2','苏乐','男',20,'信息','信息学院'),
('s3','林欣怡','女',19,'信息','信息学院'),
('s4','陶然','男',19,'自动化','工学院'),
('s5','魏立','男',17,'数学','理学院'),
('s6','何欣荣','女',21,'计算机','信息学院'),
('s7','赵琳琳','女',19,'数学','理学院'),
('s8','李轩','男',18,'自动化','工学院');

INSERT INTO t VALUES
('t1','刘杨','男',38,'教授','计算机','信息学院',3800.00),
('t2','石丽','女',28,'讲师','计算机','信息学院',2800.00),
('t3','顾伟','男',35,'副教授','计算机','信息学院',3200.00),
('t4','赵礼','男',45,'教授','自动化','工学院',4267.90),
('t5','赵希希','女',33,'副教授','数学','理学院',3332.67),
('t6','张刚','男',30,'讲师','计算机','工学院',2600.00);

INSERT INTO sc VALUES
('s1','c1',90.50), ('s1','c2',88.00),
('s2','c4',70.00), ('s2','c5',57.00), ('s2','c6',81.50), ('s2','c7',NULL),
('s3','c1',75.00), ('s3','c2',76.50), ('s3','c4',85.00),
('s4','c1',93.00), ('s4','c2',82.00), ('s4','c6',NULL), ('s4','c7',68.00),
('s5','c2',79.00), ('s5','c7',84.00),
('s7','c3',77.00), ('s7','c5',62.00), ('s7','c7',100.00),
('s8','c2',66.00), ('s8','c3',96.00);

INSERT INTO tc VALUES
('t1','c1','2021-09-03'), ('t1','c2','2021-09-04'),
('t2','c5','2021-03-06'), ('t2','c6','2021-09-06'),
('t3','c2','2021-03-08'), ('t3','c4','2021-09-08'),
('t4','c3','2021-03-10'),
('t5','c7','2021-09-12'), ('t5','c8','2021-03-15');

SHOW TABLES;
3-1

查看错误日志

讲义 · 错误日志
目的
查出错误日志文件的位置，在 Workbench 中查看最近的几条记录。
前提
已执行数据准备，当前库是 teaching。
  1. 查看错误日志的文件名和数据目录的位置
  2. 查询最近的 10 条错误日志

参考答案

可以直接复制到 Workbench 执行
-- 错误日志的文件名，开头的点号表示数据目录
SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'datadir';
-- 最近的 10 条错误日志
SELECT LOGGED, PRIO, DATA
FROM performance_schema.error_log
ORDER BY LOGGED DESC
LIMIT 10;

语句解释

log_error错误日志的文件名，默认是 主机名.err
datadir数据目录，错误日志默认放在这里
performance_schema.error_logMySQL 8.0 把最近的错误日志放在这张表中，可以直接查询
PRIO级别：System 系统信息，Error 错误，Warning 警告，Note 提示
执行后看：log_error 类似 .\DESKTOP-TKC62RP.err。查询结果中有服务启动时记下的 System 信息，也可能有一些 Warning。也可以到数据目录中用记事本打开这个文件。
3-2

查看二进制日志的状态

讲义 · 二进制日志的设置与查看
目的
确认二进制日志已经打开，查看有哪些日志文件，再换一个新的日志文件。
前提
已完成实验 3-1。
  1. 查看二进制日志是否打开
  2. 列出全部二进制日志文件，查看正在写的是哪一个
  3. 换一个新的日志文件，再列出全部文件

参考答案

可以直接复制到 Workbench 执行
SHOW VARIABLES LIKE 'log_bin%';
SHOW BINARY LOGS;
SHOW MASTER STATUS;
-- 关闭当前的二进制日志文件，换一个新文件
FLUSH BINARY LOGS;
SHOW BINARY LOGS;

语句解释

log_binON 表示二进制日志已经打开，MySQL 8.0 默认打开
log_bin_basename日志文件的位置和名称，文件名后面加编号
SHOW BINARY LOGS列出全部日志文件和每个文件的大小
SHOW MASTER STATUSFile 是正在写的文件，Position 是写到的位置
FLUSH BINARY LOGS换一个新的日志文件，编号加 1
执行后看：log_bin 为 ON。执行 FLUSH BINARY LOGS 后，列表中多了一个文件，编号加 1。MySQL 8.4 中 SHOW MASTER STATUS 改为 SHOW BINARY LOG STATUS。
3-3

删除二进制日志

讲义 · 删除二进制日志
目的
用 PURGE 删除旧的日志文件，再用 RESET MASTER 删除全部日志。
前提
已完成实验 3-2。本机练习用的日志可以放心删除。

PURGE 语句中的文件名要换成本机第一句结果中最后一行的文件名，也就是正在写的文件。

  1. 删除正在写的文件之前的全部日志
  2. 删除全部日志，查看剩下的文件

参考答案

可以直接复制到 Workbench 执行
SHOW BINARY LOGS;
-- 文件名换成上一句结果中最后一行的文件名
PURGE BINARY LOGS TO 'DESKTOP-TKC62RP-bin.000003';
SHOW BINARY LOGS;
-- 删除全部日志，从 000001 重新编号
RESET MASTER;
SHOW BINARY LOGS;

语句解释

PURGE BINARY LOGS TO删除编号比它小的全部文件，它本身保留。PURGE MASTER LOGS 作用相同
文件名写错报错 1373，在日志索引中找不到这个文件
RESET MASTER删除全部日志，新文件从 000001 开始。MySQL 8.4 中改为 RESET BINARY LOGS AND GTIDS
执行后看：PURGE 之后只剩正在写的一个文件。RESET MASTER 之后只剩编号 000001 的一个文件。
3-4

查看二进制日志的内容

讲义 · 二进制日志的设置与查看
目的
执行一次查询和一次修改，查看二进制日志记下了什么，再用 mysqlbinlog 把日志转成文本。
前提
已完成实验 3-3，现在只有编号 000001 的日志文件。命令提示符是新打开的，先执行 cd /d D:\backup 和 set path=%path%;C:\Program Files\MySQL\MySQL Server 8.0\bin 两行。
  1. 查询 s1 的信息，再把 s1 的年龄改为 19
  2. 查看日志文件中的事件
  3. 在命令提示符中用 mysqlbinlog 把日志转成文本文件，用记事本打开

参考答案

第 1 步　Workbench
USE teaching;
SELECT * FROM s WHERE sno = 's1';
UPDATE s SET age = 19 WHERE sno = 's1';
-- 不写 IN 文件名时，显示第一个日志文件，现在只有 000001
SHOW BINLOG EVENTS;
第 2 步　命令提示符　文件名换成本机的
mysqlbinlog -R -u root -p -v DESKTOP-TKC62RP-bin.000001 > binlog1.txt
notepad binlog1.txt

语句解释

SHOW BINLOG EVENTS列出日志中的事件，Pos 是事件的开始位置，Info 是事件的内容
Update_rowsUPDATE 修改了一行。前后的 BEGIN 和 COMMIT 表示一个事务
mysqlbinlog -R通过服务器读取日志，只写文件名，不用管文件在哪个文件夹
-v把行的修改显示成以 ### 开头的类似 SQL 的注释
执行后看：除了文件开头的两个事件，只有 UPDATE 留下的 BEGIN、Table_map、Update_rows、COMMIT 等几项，SELECT 没有记录。binlog1.txt 中有 ### UPDATE `teaching`.`s` 几行。
3-5

备份后误删了一张表

讲义 · 用二进制日志还原数据
目的
清空二进制日志后做一次完整备份，接着修改数据，再误删选课表 sc。
前提
已完成实验 3-4。
  1. 清空二进制日志，让备份以后的修改都记在 000001 中
  2. 在命令提示符中用 --databases 备份 teaching 库
  3. 插入学生 s9，把 s2 的年龄改为 21，然后删除 sc 表

参考答案

第 1 步　Workbench
RESET MASTER;
第 2 步　命令提示符
mysqldump -u root -p --databases teaching > teaching_full.sql
第 3 步　Workbench
USE teaching;
INSERT INTO s VALUES ('s9','周末','男',20,'计算机','信息学院');
UPDATE s SET age = 21 WHERE sno = 's2';
-- 误删了选课表
DROP TABLE sc;
SELECT * FROM s;

语句解释

RESET MASTER实验中为了查找方便才清空日志。实际工作中备份时执行 FLUSH LOGS，换一个新的日志文件
teaching_full.sql完整备份，只包含备份那一刻的数据
INSERT、UPDATE备份以后的修改，备份文件中没有，只记在二进制日志中
DROP TABLE sc模拟误操作
执行后看：s 表有 9 行，s2 的年龄是 21。选课表 sc 已经不存在。
3-6

用备份和日志还原

讲义 · 用二进制日志还原数据
目的
先用完整备份还原，再用 mysqlbinlog 重放日志，停在 DROP TABLE 之前，把数据还原到误删之前。
前提
已完成实验 3-5。

第 3 步的 --stop-position 换成本机查到的 Pos，文件名也换成本机的。

  1. 在命令提示符中用 teaching_full.sql 还原
  2. 在 Workbench 中查看 sc 和 s9、s2，再查看日志事件，找到 DROP TABLE `sc` 那一行的 Pos
  3. 重放日志，停在这个位置
  4. 再次查看 sc 和 s9、s2

参考答案

第 1 步　命令提示符
mysql -u root -p < teaching_full.sql
第 2 步　Workbench
SELECT COUNT(*) FROM teaching.sc;
SELECT * FROM teaching.s WHERE sno IN ('s2', 's9');
-- 找到 Info 为 DROP TABLE 的一行，记下它的 Pos
SHOW BINLOG EVENTS;
第 3 步　命令提示符　一次复制一行
mysqlbinlog -R -u root -p --stop-position=958 DESKTOP-TKC62RP-bin.000001 > redo.sql
mysql -u root -p < redo.sql
第 4 步　Workbench
SELECT COUNT(*) FROM teaching.sc;
SELECT * FROM teaching.s WHERE sno IN ('s2', 's9');

语句解释

mysql … < teaching_full.sql还原到备份那一刻：sc 回来了，但 s9 和 s2 的修改也没有了
--stop-position=958读到这个位置就停止，DROP TABLE 从这里开始，不会被重放
> redo.sql把要重放的内容先存成文件，可以用记事本打开看一看
mysql … < redo.sql执行日志中备份以后的修改
执行后看：第 2 步中 sc 有 20 行，但查不到 s9，s2 的年龄是 20。重放日志后 sc 仍是 20 行，s9 回来了，s2 的年龄是 21。
保山学院·人工智能教研室·曹鼎鼎