MySQL 数据库的备份还原与导出

[TOC]

## 1. 数据备份与还原
备份：将当前已有的数据记录保留
还原：将已有保留的数据恢复到对应的表中

__备份还原的意义：__

+ 防止数据丢失，被盗、误操作
+ 保护数据记录

## 2. 数据备份与还原几种方式
### 2.1. 数据表备份还原
不需要通过SQL来备份，直接进入到数据库文件夹复制对应的表结构以及数据文件，之后还原的时候，直接将备份的内容放进去即可，但数据表备份限制条件，更具不同的存储引擎有不同的区别，下面以 innode 和 myisam 存储引擎为例

+ 使用 innode 存储引擎创建的表，数据表备份时，可以查看到表存在，但无法 select 查询数据，因为 innode 存储引擎创建的表对数据库有依赖
+ 使用 myisam 存储引擎创建表，数据表备份即可以查看到表存在，也可以 select 查询到数据
 - 备份还原步骤：找到需要备份的表所在的文件目录，复制\*.frm、\*.MYD、\*.MYI（\*星符号通常是表名）三个文件，到需要还原的数据库文件目录下即可，然后通过 `show tables` 和 `select` 验证是否还原成功

根据上面的对比，只有使用 myisam 存储引擎创建表，适合使用表备份的方式进行备份，而大多数情况下创建的表都是基于 innodb 存储引擎，这就是数据表备份的限制，关于存储引擎介绍[点击查看](/blog/info-md.html/2767397871E8A7AA?type=40#h3-1-4-mysql-)

### 2.2. 单表数据备份还原
每次只能备份一张表，只能备份数据，表结构不能备份，通常的使用是，将表中的数据进行导出到文件

#### 2.2.1. 备份
从表中选出一部分数据保存到外部文件中

__备份语法：__select * into outfile 文件所在路径 from 数据源;，注意这里有限制，需要外部文件不存在方可

```SQL
-- 单表数据备份
select * into outfile 'd:/MySQL/temp/my_student.txt' from my_student;
-- 导出文件的格式如下，下面每行对列的都使用了'\t'，tab键隔开
-- 1  曹操 78
-- 2  孙权 56
-- 3  刘备 56
```

__高级备份语法：__select * into outfile 文件所在路径 fields 字段处理 lines 行处理 from 数据源 ;，可以自定义字段和行的处理方式

__fields字段处理的方式：__

+ enclosed by：字段使用什么内容包裹，默认是''，空字符串
+ terminated by：字段以什么结束，默认是'\t'，tab键
+ escaped by：特殊符号用什么方式处理，默认是'\\'，使用反斜杠转义

__lines行处理的方式：__

+ starting by：每行以什么开始，默认是''，空字符串
+ terminated by：，每行以什么结束，默认是'\r\n'，换行符

```SQL
select * into outfile 'd:/MySQL/temp/my_student.txt'
fields terminated by ','
lines starting by 'newLines:'
from my_student;
-- 导出文件的格式如下，下面每行对字段的都使用了逗号进行分隔，这是因为自定义ields terminated by ','来使用逗号进行分隔
-- newLines:1，曹操，78
-- newLines:2，孙权，56
-- newLines:3，刘备，56
```

#### 2.2.2. 还原
将一个在外部保存的数据重新恢复到表中，需要注意的是，如果表结构不存在了，还原将失败

__还原语法：__load data infile 文件所在路径 into table 表名 [字段列表] fields 字段处理 lines 行处理;，备份时用的什么 lines 行和 fields 字段的处理方式，还原时也要用同样的处理方式还原，字段列表是可选的，备份时用的什么字段，这里一样用还原什么字段

```SQL
-- 上面备份的示例语句没有指定字段，用的全部，所以这里也无需指定字段，如果备份指定了字段，还原也需指定字段
load data infile 'd:/MySQL/temp/my_student.txt' into table my_student fields terminated by ',' lines starting by 'newLines:';
```

### 2.3. SQL备份
备份的是SQL语句，系统会对表结构以及数据进行处理，变成对应的SQL语句，然后进行备份，因此还原的时候，只要执行备份的SQL语句即可

__SQL备份的优缺点：__

+ 优点：可以备份结构
+ 缺点：会浪费空间，额外的添加SQL命令

#### 2.3.1. 备份
mysql没有提供备份指定，需要利用mysql提供的软件 `mysqldump.exe`，`mysqldump.exe` 是客户端，它也需要操作mysql服务器，因此必须连接认证

__备份语法：__mysqldump/mysqldump.exe -hPup 数据库名字 [[数据表名字1] [数据库表名2]] > 外部文件目录，没有指定数据库表名时，表示备份整个库，外部文件目录的文件建议使用 `.sql`

> 注意使用备份时要先推出当前mysql客户端，使用exit;推出，备份语句不是sql语句，这里不需要使用;分号结尾

```SQL
-- -uroot -proot需要替换成正确的账户密码，这里演示使用的账户密码都是root
mysqldump -uroot -proot study > d:/MySQL/temp/study.sql
```

备份成功后在 d:/MySQL/temp 目录下有一个study.sql文件，里面存放的都是关于数据表结构和操作数据的SQL语句

#### 2.3.2. 还原
SQL还原数据有两种方式

+ 使用 `mysqldump.exe` 客户端还原

__备份语法：__`mysqldump/mysqldump.exe -hPup 数据库名字 < 备份文件目录`，备份文件目录指的是上面的 `study.sql` 文件

```SQL
-- -uroot -proot需要替换成正确的账户密码，这里演示使用的账户密码都是root
mysqldump -uroot -proot study < d:/MySQL/temp/study.sql
```
***

+ 使用SQL指令还原，从文件中读取并执行sql命令

__备份语法：__source 备份文件所在路径

```SQL
-- 这里读取的不是sql命令而是文件，所以不需要使用 ; 符号
source d:/MySQL/temp/study.sql；
```

此方法除了可以还原数据库外，也可以执行其它的sql指令，只要将sql指令放到文件中，然后 `source` 执行改文件即可
***

> 乱码时备份和还原可加 default character-set = utf8;

### 2.4. 增量备份
+ 不是针对数据或SQL指令进行备份，是针对mysql服务器的日志文件进行备份
+ 指定时间段开始进行备份，备份数据不会重复，而且所有的操作都会备份，因此大项目都会使用增量备份