数据库实验：数据库备份与恢复

一、实验内容
1、用不同的存放格式（自由设计）备份DBEM数据库中的 employee, salary两个基本表，其中employee表要求只备份 employeeID, name, education等三个字段
2、根据上述任务所保存的文件，将相关数据恢复到基本表中，其中 要求employee表在恢复之前事先随机删除几条记录，SQL语句中要求指定replace功能
3、使用mysqldump命令备份DBEM数据库中的employee表
4、使用mysqldump命令备份整个DBEM数据库
5、删除employee表，然后使用mysql命令，利用上述保存的文件 恢复employee表
6、删除salary表的部分数据，然后使用mysqlimport命令及-- replace功能恢复salary表，其中salary表的数据备份文件使用实验4.1中的文件

二、实验过程
1、use dbem;
select * from salary into outfile ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/salary.txt’
fields terminated by ‘\t’
enclosed by ‘’
escaped by ‘,’
lines terminated by ‘\r’;
select employeeID,name,education from employee into outfile ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee.txt’
fields terminated by ‘\t’
enclosed by ‘’
escaped by ‘,’
lines terminated by ‘\r’;

2、delete from employee where employeeID=‘000001’;
load data infile ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee.txt’ replace into table employee
FIELDS TERMINATED BY ‘\t’
enclosed by ‘’
escaped by ‘,’
LINES TERMINATED BY ‘\r’;
load data infile ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/salary.txt’ replace into table salary
FIELDS TERMINATED BY ‘\t’
enclosed by ‘’
escaped by ‘,’
LINES TERMINATED BY ‘\r’;
删除一条记录后的employee表：

恢复后的salary表：

恢复后的employee表：

3、C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -hlocalhost -uroot -pqwe123123 dbem employee>C:\Users\pc\Desktop\employee.sql

4、C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -hlocalhost -uroot -pqwe123123 dbem >C:\Users\pc\Desktop\dbem.sql

5、drop table employee;
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlimport -uroot -pqwe123123 dbem C:\Users\pc\Desktop\employee.sql

6、delete from salary where employeeID=504209;
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlimport -uroot -pqwe123123 -replace dbem C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\salary.txt


三、实验分析
1、用sql语句备份文件时，路径用的\导致报错，改为/后成功运行
2、用sql语句备份文件时，一开始随意写了一个路径，发现不行，用show variables like ‘%secure%’;语句找到安全路径后解决问题
3、一开始在mysql里输入mysqldump命令，一直都不能执行，改为在cmd中方可执行