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

备份与还原

在命令提示符中用 mysqldump 备份表和数据库，再用 mysql 命令和 Workbench 把备份还原。

环　境

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

命令提示符

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

执　行

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

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

  1. 实验 1-1准备命令提示符
  2. 实验 1-2备份一张表和两张表
  3. 实验 1-3备份整个数据库
  4. 实验 1-4只备份结构、数据或部分行
  5. 实验 1-5还原到另一个数据库
  6. 实验 1-6删除数据库后还原
  7. 实验 1-7用 Workbench 运行备份文件
第一步

数据准备（必做）

删除后重建教材 teaching 库的五张表，同时删除本章实验建的 teaching_copy、teaching_empty 两个库。四次实验共用这份脚本，每次实验前执行一次，数据回到初始状态。

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;
1-1

准备命令提示符

讲义 · mysqldump 命令的语法
目的
打开命令提示符，建立存放备份文件的文件夹，确认能执行 mysqldump。
前提
已在 Workbench 中执行数据准备的脚本。
  1. 建立文件夹 D:\backup，并进入这个文件夹
  2. 把 MySQL 的 bin 文件夹加入命令的搜索路径
  3. 查看 mysqldump 的版本

参考答案

命令提示符
mkdir D:\backup
cd /d D:\backup
set path=%path%;C:\Program Files\MySQL\MySQL Server 8.0\bin
mysqldump --version

语句解释

mkdir D:\backup新建文件夹 backup，本章的备份文件都放在这里。电脑没有 D 盘时，改为 C:\backup
cd /d D:\backup进入这个文件夹，/d 表示同时切换到 D 盘
set path=…让命令提示符能找到 mysqldump、mysql 等程序。只对当前窗口有效，关掉后要重新执行
mysqldump --version显示 mysqldump 的版本，能显示就说明前面的设置正确
执行后看：最后一行显示 mysqldump Ver 8.0.xx for Win64。如果提示“不是内部或外部命令”，检查 bin 文件夹的路径，以本机的安装位置为准。
1-2

备份一张表和两张表

讲义 · mysqldump 命令的语法讲义 · 备份表和数据库
目的
用 mysqldump 备份学生表 s，再把 s 和 c 两张表备份到一个文件中，用记事本查看文件内容。
前提
已完成实验 1-1，命令提示符停在 D:\backup 文件夹中。
  1. 把 teaching 库的 s 表备份到 teaching_s.sql
  2. 把 s 表和 c 表备份到 teaching_s_c.sql
  3. 用记事本打开 teaching_s.sql

参考答案

命令提示符　一次复制一行
mysqldump -u root -p teaching s > teaching_s.sql
mysqldump -u root -p teaching s c > teaching_s_c.sql
notepad teaching_s.sql

语句解释

mysqldump备份程序，把表导出为 SQL 语句
-u root -p用 root 登录。-p 后面不写密码，按 Enter 键后再输入
teaching s库名后面跟表名，只备份这张表
teaching s c多个表名用空格隔开，写成 s,c 会报错，找不到表
> teaching_s.sql把结果写入这个文件。没写路径，文件就放在当前文件夹 D:\backup 中
notepad用记事本打开文件
执行后看：备份成功时不显示任何信息。文件中是 DROP TABLE、CREATE TABLE 和 INSERT 语句，没有 CREATE DATABASE。
1-3

备份整个数据库

讲义 · 备份表和数据库
目的
分别用不带和带 --databases 的写法备份 teaching 库，比较两个文件的开头。
前提
已完成实验 1-2。
  1. 只写库名，把 teaching 库备份到 teaching_1.sql
  2. 加 --databases，把 teaching 库备份到 teaching_2.sql
  3. 用记事本打开两个文件，比较开头的几行

参考答案

命令提示符　一次复制一行
mysqldump -u root -p teaching > teaching_1.sql
mysqldump -u root -p --databases teaching > teaching_2.sql
notepad teaching_1.sql
notepad teaching_2.sql

语句解释

teaching只写库名，备份库中的全部表
--databases teaching也是备份整个库，文件中多了建库和选择库的语句
CREATE DATABASE 和 USE有这两句，还原时会自动建库，不用事先建好
--all-databases备份服务器上的全部数据库，包括系统库，这里不做
执行后看：teaching_2.sql 在 CREATE TABLE 之前多了 CREATE DATABASE … `teaching` 和 USE `teaching` 两句，teaching_1.sql 中没有。
1-4

只备份结构、数据或部分行

讲义 · 只备份结构、数据或部分数据
目的
用 --no-data、--no-create-info 和 --where 备份 s 表的一部分内容。
前提
已完成实验 1-3。
  1. 只备份 s 表的结构，文件名 s_struct.sql
  2. 只备份 s 表的数据，文件名 s_data.sql
  3. 只备份 s 表中年龄小于 18 的学生，文件名 s_young.sql
  4. 用记事本打开三个文件

参考答案

命令提示符　一次复制一行
mysqldump -u root -p --no-data teaching s > s_struct.sql
mysqldump -u root -p --no-create-info teaching s > s_data.sql
mysqldump -u root -p teaching s --where="age<18" > s_young.sql
notepad s_struct.sql
notepad s_data.sql
notepad s_young.sql

语句解释

--no-data不导出数据，文件中只有 DROP TABLE 和 CREATE TABLE
--no-create-info不导出建表语句，文件中没有 DROP TABLE 和 CREATE TABLE，只有插入数据的语句
--where="age<18"只导出满足条件的行。条件要用英文双引号括起来，否则 < 会被命令提示符当成还原用的符号
执行后看：s_struct.sql 中没有 INSERT，s_data.sql 中没有 CREATE TABLE，s_young.sql 的 INSERT 中只有 s5 魏立一行。
1-5

还原到另一个数据库

讲义 · 用 mysql 命令还原讲义 · 三种迁移的做法
目的
新建数据库 teaching_copy，用 mysql 命令把 teaching_1.sql 还原到这个库中。把数据库搬到另一台服务器，做法也是这样。
前提
已完成实验 1-3，D:\backup 中有 teaching_1.sql。
  1. 在 Workbench 中新建数据库 teaching_copy
  2. 在命令提示符中用 mysql 命令把 teaching_1.sql 还原到 teaching_copy
  3. 在 Workbench 中查看 teaching_copy 中的表和 sc 表的行数

参考答案

第 1 步　Workbench
CREATE DATABASE teaching_copy;
第 2 步　命令提示符
mysql -u root -p teaching_copy < teaching_1.sql
第 3 步　Workbench
SHOW TABLES FROM teaching_copy;
SELECT COUNT(*) FROM teaching_copy.sc;

语句解释

mysql客户端程序，执行文件中的 SQL 语句。还原用 mysql，不是 mysqldump
teaching_copy还原到哪个库。teaching_1.sql 中没有 USE，命令中必须写库名，库也要事先建好
< teaching_1.sql从文件中读入语句，方向与备份时的 > 相反
执行后看：teaching_copy 中有 c、s、sc、t、tc 五张表，sc 有 20 行。命令中不写库名会报错 1046，库不存在会报错 1049。
1-6

删除数据库后还原

讲义 · 用 mysql 命令还原
目的
删除 teaching 库，再用带 --databases 备份的 teaching_2.sql 把它还原。
前提
已完成实验 1-3，D:\backup 中有 teaching_2.sql。
  1. 在 Workbench 中删除 teaching 库，查看数据库列表
  2. 在命令提示符中用 teaching_2.sql 还原，命令中不写库名
  3. 在 Workbench 中查看 teaching 库 sc 表的行数

参考答案

第 1 步　Workbench
DROP DATABASE teaching;
SHOW DATABASES;
第 2 步　命令提示符
mysql -u root -p < teaching_2.sql
第 3 步　Workbench
USE teaching;
SELECT COUNT(*) FROM sc;

语句解释

DROP DATABASE teaching删除整个库，模拟数据丢失。万一还原失败，重新执行数据准备的脚本即可
mysql -u root -p < teaching_2.sql文件中有 CREATE DATABASE 和 USE，命令中不用写库名
USE teaching库删除后 Workbench 没有当前库了，重新选择
执行后看：删除后数据库列表中没有 teaching。还原后 teaching 又出现了，sc 有 20 行。
1-7

用 Workbench 运行备份文件

讲义 · 登录服务器后还原
目的
连接服务器以后，在 Workbench 中运行备份文件，先还原 s 表的结构，再还原数据。
前提
已完成实验 1-4，D:\backup 中有 s_struct.sql 和 s_data.sql。

运行备份文件：菜单 File → Run SQL Script…，选择文件，Default Schema Name 选 teaching_empty，点 Run。

  1. 新建数据库 teaching_empty
  2. 运行 s_struct.sql，查看 s 表的行数
  3. 再运行 s_data.sql，查看 s 表的行数

参考答案

第 1 步　Workbench
CREATE DATABASE teaching_empty;
第 2 步　运行 s_struct.sql 之后
SELECT COUNT(*) FROM teaching_empty.s;
第 3 步　运行 s_data.sql 之后
SELECT COUNT(*) FROM teaching_empty.s;

语句解释

Run SQL Script执行一个 .sql 文件，效果与用 mysql 命令还原相同
Default Schema Name文件中没有 USE 语句，要指定在哪个库中执行
s_struct.sql只有建表语句，还原后是一张空表
s_data.sql只有 INSERT 语句，表要先存在
执行后看：运行 s_struct.sql 后 s 表有 0 行，运行 s_data.sql 后有 8 行。两个文件顺序反过来，会报错 1146，表不存在。
保山学院·人工智能教研室·曹鼎鼎