一、简介

1.1 内容

实践MySQL的备份与还原。

二、内容

2.1****数据备份

（1） 创建数据库teaching，建表脚本及数据如下，运用前期所学知识，将数据写入对应的表中

学生信息表student

1
2
3
4
5
6
7
8
9
10
11

#student表结构
create table if not exists student (
studentno char(11) not null comment'学号', 
sname char(8) not null comment'姓名', 
sex enum('男', '女') default '男' comment'性别', 
birthdate date not null comment'出生日期', 
entrance int(3) null comment'入学成绩',  
phone varchar(12) not null comment'电话', 
Email varchar(20) not null comment'电子信箱',
primary key (studentno)
);

#student表常用数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

studentno  sname  sex birthdate entrance phone  Email

18122210009 许东山 男 1999/11/5 789 13623456778 qwe@163.com

18122221324 何白露 女 2000/12/4 879 13178978999 heyy@sina.com

18125111109 敬横江 男 2000/3/1 789 15678945623 jing@sina.com

18125121107 梁一苇 女 1999/9/3 777 13145678921 bing@126.com

18135222201 凌浩风 女 2001/10/6 867 15978945645 tang@163.com

18137221508 赵临江 男 2000/2/13 789 12367823453 ping@163.com

19111133071 崔依歌 女 2001/6/6 787 15556845645 cui@126.com

19112100072 宿沧海 男 2002/2/4 658 12545678998 su12@163.com

19112111208 韩山川 男 2001/2/14 666 15878945612 han@163.com

19122203567 封月明 女 2002/9/9 898 13245674564 jiao@126.com

19123567897 赵既白 女 2002/8/4 999 13175689345 pingan@163.com

19126113307 梅惟江 女 2003/9/7 787 13245678543 zhu@163.com

课程信息表course

1
2
3
4
5
6
7
8
9
10

#course表结构
create table if not exists course (
courseno char(6) not null, 
cname char(6) not null, 
type char(8) not null, 
period int(2) not null, 
exp int(2) not null,
term int(2) not null,
primary key (courseno)
); 

#course表常用数据

Courseno cname type period exp term

c05103 电子技术 必修 64 16 2

c05109 C语言 必修 48 16 2

c05127 数据结构 必修 64 16 2

c05138 软件工程 选修 48 8 5

c06108 机械制图 必修 60 8 2

c06127 机械设计 必修 64 8 3

c06172 铸造工艺 选修 42 16 6

c08106 经济法 必修 48 0 7

c08123 金融学 必修 40 0 5

c08171 会计软件 选修 32 8 8

成绩信息表score

1
2
3
4
5
6
7
8

#score表结构
create table if not exists score(
studentno char(11) not null, 
courseno char(6) not null, 
daily float(3,1) default 0, 
final float(3,1) default 0,
primary key (studentno , courseno) 
); 

#score表常用数据

studentno courseno daily final

18122210009 c05103 87 82

18122210009 c05109 77 91

18122221324 c05103 88 62

18122221324 c05109 91 77

18125111109 c08106 79 99

18125111109 c08123 85 92

18125111109 c08171 77 92

18125121107 c05103 74 91

18125121107 c05109 89 62

18135222201 c05109 99 92

18135222201 c08171 95 82

18137221508 c08106 78 95

18137221508 c08123 78 89

18137221508 c08171 88 98

19111133071 c05103 82 69

19111133071 c05109 77 82

19112100072 c05109 87 86

19112100072 c06108 97 97

19112111208 c05109 85 91

19112111208 c06108 89 95

19122111208 c06127 78 67

19122203567 c05103 65 98

19122203567 c05108 88 89

19122203567 c06127 79 88

19123567897 c05103 85 77

19123567897 c06127 99 99

19126113307 c06108 66 82

19126113307 c08171 88 79

教师信息表teacher

1
2
3
4
5
6
7
8
9

#teacher表结构
create table if not exists teacher (
teacherno  char(6) not null comment '教师编号', 
tname char(8) not null comment'教师姓名', 
major char(10) not null comment '专业', 
prof char(10) not null comment '职称',
department char(16) not null comment '部门',
primary key (teacherno)
); 

#teacher表常用数据

teacherno tname major prof department

t05001 苏超然 软件工程 教授 计算机学院

t05002 常杉 会计学 助教 管理学院

t05003 孙释安 网络安全 教授 计算机学院

t05011 卢敖治 软件工程 副教授 计算机学院

t05017 茅佳峰 软件测试 讲师 计算机学院

t06011 夏南望 机械制造 教授 机械学院

t06023 葛庭宇 铸造工艺 副教授 材料学院

t07019 韩既乐 经济管理 讲师 管理学院

t08017 时观 金融管理 副教授 管理学院

纽带表teach_course

1
2
3
4
5
6

#teach_course表结构
create table if not exists teach_course (
teacherno char(6) not null, 
courseno char(6) not null, 
primary key (teacherno,courseno) 
);

#teach_course表常用数据

teacherno courseno

t05001 c05109

t05002 c05127

t05003 c05127

t05011 c05138

t05017 c05127

t06011 c06127

t06023 c06172

t07019 c08123

t08017 c08106

选课信息表se_course

1
2
3
4
5
6
7
8
9

#se_course表结构
create table se_course
(sc_no int(6) not null auto_increment, 
studentno char(11) not null, 
courseno char(6) not null, 
teacherno char(6) not null, 
sc_time timestamp not null default now(), 
primary key (sc_no)
);

#se_course表常用数据

sc_no studentno courseno teacherno score sc_time

1 19120000111 co1236 t01237 NULL 2017-09-01 18:40:23

219120000222co1237t01239NULL2017-09-05 18:24:22 nt:��v��D�

（2） 创建保存备份文件的路径/mysqldata

1

#mkdir /mysqldata

（3） 使用mysqldump命令备份数据库teaching中的所有表

1
2

mysqldump -u root -p teaching > /mysqldata/ teaching.sql 
Enter password:******

（4） 使用mysqldump命令备份数据库中的student表和score表

1

mysqldump -u root -p teaching student score 

1
2

 > /mysqldata/teaching_ss.sql 
Enter password:******

（5） 使用mysqldump命令备份数据库中的course表

1
2

mysqldump -u root -p teaching course > /mysqldata/course.sql 
Enter password:******

（6） 查看备份文件

1

mysqldump -uroot –p123456 --databases teaching > /mysqldata /teach.txt

在文本文件teach.txt中输出了表创建、表数据插入，以及存储过程、存储函数、触发器、事件等对象的创建语句。这些语句可作为输入来创建MySQL数据库

2.2****数据恢复

（1） 使用MySQL命令将备份文件teaching.sql恢复到数据库中

1
2

mysql -u root -p mysqltest < /mysqldata/ teaching.sql
Enter password:******

（2） 删除course表的数据，用source命令恢复。

#尝试删除course表的数据。

1
2

mysql> use teaching;
mysql> delete from course; 

#利用放在“/mysqldata”路径下course的备份文件course.sql，使用source命令把备份好的文件导入进行恢复。

1

mysql> source /mysqldata/course.sql;

（3） 使用source命令将备份文件teaching.sql恢复到数据库中

1
2

mysql> use teaching; 
mysql> source  /mysqldata/teaching.sql

2.3****表的导入与导出

（1） 将表student数据分别备份成.xls和.xml格式

1
2
3
4
5
6

mysql> select * into outfile
-> '/mysqldata/student.xls' 
-> from student;
mysql> select * into outfile
-> '/mysqldata / student.xml'
-> from student;

（2） 使用select…into outfile命令将teaching数据库中的score表中的记录导出到文本文件，使用fields选项和lines选项，要求字段之间使用逗号“，”间隔，所有字段值用双引号括起来，定义转义字符为单引号“\‘”，执行命令如下。

1
2
3
4
5
6

mysql> select * from score into outfile
-> '/mysqldata /score.txt'
-> fields
-> terminated by ','
-> enclosed by '\"'
-> escaped by '\''

（3） 使用MySQL命令将teaching数据库中的teacher表中的记录导出到文本文件。

1
2

mysql -uroot –p --execute="select * from teacher;" teaching >/mysqldata /teach.txt 
Enter password:****** 

或

1
2

mysql -u root -p -e "select * from teacher;" teaching >/mysqldata /teatxt.txt 
Enter password:******

（4） 使用load data infile命令将‘/mysqldata /score.txt’文件中的数据导入到teaching数据库中的score表中，使用fields选项和lines选项，要求字段之间使用逗号“,”间隔，所有字段值用双引号括起来，定义转义字符为单引号“\‘”，执行命令如下。

1
2
3
4
5
6
7
8
9
10

mysql> delete from score;
mysql> load data infile 
-> '/mysqldata /score.txt' 
-> into table score
-> fields
-> terminated by ','
->  enclosed by '\"'
-> escaped by '\''
-> lines
-> terminated by '\r\n‘;