[数据库实验]数据库备份与恢复

数据库备份

物理备份：复制数据库物理文件 逻辑备份：将数据库对象的定义和数据导出到指定文件 增量备份：只备份上次备份以来有变化的数据
完全备份：备份整个数据库

MYSQL保证数据安全的方法

数据库备份：通过导出数据或者表文件的副本来保护数据 二进制日志文件：保存更新数据的所有语句
数据库复制：MySQL内部复制功能，建立在两个或两个以上服务器之间，通过设定主从关系来实现

数据库的备份与恢复有两种方法，一种是使用SQL语句进行备份与恢复，另一种是使用客户端的工具进行。

一、使用SQL语句进行备份与恢复

（使用SQL语句只能对数据进行操作，也就是只能导出和导入表中的数据）

1.1 数据备份（导出）

使用SELECT… INTO … OUTFILE语句将表数据导出到一个文本文件中

具体格式

SELECT * FROM table_nameINTO OUTFILE ‘FILENAME’
[FIELDS
[TERMINATED BY ‘string’]
[[OPTIONALLY] ENCLOSED BY ‘char’]
[ESCAPED BY ‘char’]
]
[LINES TERMINATED BY ‘string’];

参数解释

fields terminated by ‘string’: 指定字段值之间用某个string分隔，默认为’\t’
fields enclosed by ‘char’: 指定包裹文件中字符值的符号，若使用optionally，则所有的值都放在指定符号之间，默认为空

fields escapedby ‘char’: 指定转义字符，默认为’\’
lines terminated by ‘string’: 指定一行结束的标志，默认为’\n’

实例：

假设现在有一个数据库DBEM,里面有employee表，一共有6个字段
（1）按此方法对表employee所有数据进行备份（2）只备份employee表的指定三个字段

SQL代码

/*employee表所有字段导出*/
SELECT * FROM employee
INTO OUTFILE 'D:/test/employee.txt'
CHARACTER SET gbk
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\t\n';

/*employee表employeeID、name、education导出*/
SELECT employeeID,name,education FROM employee
INTO OUTFILE 'D:/test/employee.txt'
CHARACTER SET gbk
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\t\n';

注意：这个方法只能导出数据，不会保存表的相关特征，比如key等

1.2 数据恢复（导入）

使用LOAD DATA INFILE…. INTO …语句将一个文件中的数据导入到数据库中

具体格式

LOAD DATA INFILE ‘FILENAME’ [REPLACE|IGNORE] INTO TABLE table_name
[FIELDS
[TERMINATED BY ‘string’]
[[OPTIONALLY] ENCLOSED BY ‘char’]
[ESCAPED BY ‘char’]
]
[LINES [STARTING BY ‘string’] [TERMINATED BY ‘string’]]
[IGNORE number LINES]
[(列名或用户变量)]
[SET 列名＝表达式]

参数解释

REPLACE:当原表中具有与文件中相同的关键字（主键）时，把原表中的替换掉
IGNORE：若出现相同的关键值，则跳过输入

lines starting by ‘string’: 指定一个前缀，导入数据行时，忽略行中该前缀和前缀之前的内容。如果某行不包括该前缀，则整个行被跳过

IGNORE number LINES: 忽略文件前几行

实例：

假设我们已经得到employee表的备份数据文件employee.txt,先将数据文件导入DBEM的employee表中，这里我们使用REPLACE关键字

/*恢复数据到employee表中*/
LOAD DATA INFILE 'D:/test/employee.txt' REPLACE INTO TABLE employee
CHARACTER SET gbk
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\t\n';

使用客户端工具进行备份和恢复

**

（使用客户端工具mysqldump和mysqlimport能备份整个数据库和表，而且能把数据库或者表的配置全都备份出来，这两个都是mysql中bin目录下的两个可执行文件，在cmd中执行，如果在powershell中执行，在恢复的时候会报错，使用mysqldump备份的数据可以用mysql语句进行恢复）

**

备份：mysqldump

备份表

格式：

mysqldump[OPTIONS] db_name[tables]>filename
OPTIONS：-h[hostname] –u[username] –p[password]

实例：

mysqldump –hlocalhost –uroot –p123456 DBST 
student course>backup_StudentCourse.sql
/*这语句是一个整体，最后的.sql文件可以自己指定路径*/

备份数据库

格式：

mysqldump[OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]>filename
OPTIONS：-h[hostname] –u[username] –p[password]

实例：

/*备份一个数据库*/
mysqldump –hlocalhost –uroot –p123456 --databases DBST>backup_DBST.sql
/*备份全部数据库*/
mysqldump –uroot –p123456 --all-databases>backup_all.sql

恢复表

（1）恢复表可以在mysql界面中使用source filename.sql;命令直接执行
（2）也可以使用mysqlimport工具恢复数据，但两者的区别是使用工具相当于LOAD DATA INFILE命令，只能使用只含数据的文件来进行恢复。
格式：

mysqlimport [options] db_name filename
OPTIONS: -d|–delete 在导入文件前清空表格，–replace|–ignore等同于LOAD DATA INFILE 语句中的关键字
OPTIONS: -h[hostname] –u[username] –p[password]
例子：

mysqlimport –uroot –p123456 -–replace DBEM employee.txt

恢复数据库

格式：

mysql[OPTIONS] db_name<filename

例如上面使用mysqldump进行备份的数据库.sql文件，可以这样恢复

可以在cmd中这样：
mysql –uroot –p123456 DBST<backup_DBST.sql
也可以在mysql中这样：
source filename.sql;