Linux MySQL --- 数据库备份与恢复

备份就是为了防止原数据丢失，保证数据的安全。当数据库因为某些原因造成部分或者全部数据丢失后，备份文件可以帮我们找回丢失的数据。因此，数据备份是很重要的工作。

数据库备份常见的应用场景

* 数据丢失应用场景：

1）人为操作失误造成某些数据被误操作

2）软件 BUG 造成部分数据或全部数据丢失

3）硬件故障造成数据库部分数据或全部数据丢失

4）安全漏洞被入侵数据恶意破坏

* 非数据丢失应用场景：

1）特殊应用场景下基于时间点的数据恢复

2）开发测试环境数据库搭建

3）相同数据库的新环境搭建

4）数据库或者数据迁移

磁盘故障导致整个数据库所有数据丢失，并且无法从已经出现故障的硬盘上面恢复出来时，可以通过最近时间的整个数据库的物理或逻辑备份数据文件，尽可能的将数据恢复到故障之前最近的时间点。

操作失误造成数据被误操作后，我们需要有一个能恢复到错误操作时间点之前的瞬间的备份文件存在，当然这个备份可能是整个数据库的备份，也可以仅仅只是被误操作的表的备份。

备份类型

备份是以防万一的一种必要手段，在出现硬件损坏或非人为的因素而导致数据丢失时，可以使用备份恢复数据，以将损失降低到最小程度，因此备份是必须的。备份可以分为以下几个类型：

* 根据备份的方法（是否需要数据库离线）可以将备份分为：

热备（Hot Backup）

冷备（Cold Backup）

温备（Warm Backup）

1）热备份可以在数据库运行中直接备份，对正在运行的数据库操作没有任何的影响，数据库的读写操作可以正常执行。这种方式在MySQL官方手册中称为Online Backup（在线备份）。

2）冷备份必须在数据库停止的情况下进行备份，数据库的读写操作不能执行。这种备份最为简单，一般只需要复制相关的数据库物理文件即可。这种方式在MySQL 官方手册中称为Offline Backup（离线备份）。

3）温备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，备份时仅支持读操作，不支持写操作。

* 按照备份后文件的内容，热备份又可以分为：

逻辑备份

裸文件备份

1）逻辑备份是指备份出的文件内容是可读的，一般是文本内容。内容一般是由一条条 SQL 语句，或者是表内实际数据组成。如mysqldump和SELECT * INTO OUTFILE 的方法。这类方法的好处是可以观察导出文件的内容，一般适用于数据库的升级、迁移等工作。但其缺点是恢复的时间较长。

2) 裸文件备份是指复制数据库的物理文件，既可以在数据库运行中进行复制（如 ibbackup、xtrabackup 这类工具），也可以在数据库停止运行时直接复制数据文件。这类备份的恢复时间往往比逻辑备份短很多。

* 按照备份数据库的内容来分，备份又可以分为：

完全备份

部分备份

1)完全备份是指对数据库进行一个完整的备份，即备份整个数据库，如果数据较多会占用较大的时间和空间。

2)部分备份是指备份部分数据库（例如，只备份一个表）。

* 部分备份又分为：

增量备份

差异备份

1) 增量备份需要使用专业的备份工具。指的是在上次完全备份的基础上，对更改的数据进行备份。也就是说每次备份只会备份自上次备份之后到备份时间之内产生的数据。因此每次备份都比差异备份节约空间，但是恢复数据麻烦。

2) 差异备份指的是自上一次完全备份以来变化的数据。和增量备份相比，浪费空间，但恢复数据比增量备份简单。

* MySQL中进行不同方式的备份还要考虑存储引擎是否支持，如MyISAM不支持热备，支持温备和冷备。而InnoDB支持热备、温备和冷备。

* 一般情况下，我们需要备份的数据分为以下几种：

1)表数据

2)二进制日志、InnoDB事务日志

3)代码（存储过程、存储函数、触发器、事件调度器）

4)服务器配置文件

* 下面是几种常用的备份工具：

1) mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备。

2) cp、tar 等归档复制工具：物理备份工具，适用于所有的存储引擎、冷备、完全备份、部分备份。

3) lvm2 snapshot：借助文件系统管理工具进行备份。

4) mysqlhotcopy：名不副实的一个工具，仅支持MyISAM 存储引擎。

5) xtrabackup：一款由percona提供的非常强大的InnoDB/XtraDB热备工具，支持完全备份、增量备份。

MySQL mysqldump备份数据库

备份一个数据库

mysqldump命令执行时，可以将数据库中的数据备份成一个文本文件。数据表的结构和数据将存储在生成的文本文件中。

使用mysqldump命令备份一个数据库

语法格式：mysqldump -u username -p dbname [tbname ...]> filename.sql

* 语法说明：

1）username：表示用户名称；

2）dbname：表示需要备份的数据库名称；

3）tbname：表示数据库中需要备份的数据表，可以指定多个数据表。省略该参数时，会备份整个数据库；

4）右箭头“>”：用来告诉mysqldump将备份数据表的定义和数据写入备份文件；

5）filename.sql：表示备份文件的名称，文件名前面可以加绝对路径。通常将数据库备份成一个后缀名为.sql的文件。

* mysqldump 命令备份的文件并非一定要求后缀名为.sql，备份成其他格式的文件也是可以的。例如，后缀名为.txt的文件。通常情况下，建议备份成后缀名为.sql的文件。因为，后缀名为.sql的文件给人第一感觉就是与数据库有关的文件。

[root@master /]# touch stu_mysql.sql    # 创建MySQL备份文件

# 使用root用户备份mysql_chuid数据库下的students表

[root@master /]# mysqldump -uroot -p mysql_chuid students>/stu_mysql.sql  

Enter password:

[root@master /]#

*  1) stu_mysql.sql文件开头记录了MySQL版本、备份的主机名和数据库名。

*  2) 文件中，以“--”开头的都是 SQL 语言的注释。以“/*!40101”等形式开头的是与MySQL有关的注释。40101是MySQL数据库的版本号，这里就表示 MySQL 4.1.1。如果恢复数据时，MySQL 的版本比 4.1.1 高，“/*!40101”和“*/”之间的内容被当作 SQL 命令来执行。如果比4.1.1低，“/*!40101”和“*/”之间的内容被当作注释。“/*!”和“*/”中的内容在其它数据库中将被作为注释忽略，这可以提高数据库的可移植性。

*  3) DROP语句、CREATE语句和INSERT语句都是数据库恢复时使用的；“DROP TABLE IF EXISTS 'student' ”语句用来判断数据库中是否还有名为student的表，如果存在，就删除这个表；CREATE语句用来创建student表；INSERT语句用来恢复所有数据。文件的最后记录了备份的时间。

*  4) 上面stu_mysql.sql文件中没有创建数据库的语句，因此，stu_mysql.sql文件中的所有表和记录必须恢复到一个已经存在的数据库中。恢复数据时，CREATE TABLE 语句会在数据库中创建表，然后执行INSERT语句向表中插入记录。

# 查看备份文件stu_mysql.sql的部分内容

[root@master /]# cat stu_mysql.sql

-- MySQL dump 10.14  Distrib 5.5.60-MariaDB, for Linux (x86_64)

--

-- Host: localhost    Database: mysql_chuid

-- ------------------------------------------------------

-- Server version       5.6.19-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Table structure for table `students`

--

DROP TABLE IF EXISTS `students`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `students` (

  `id` int(5) NOT NULL DEFAULT '0',

  `name` char(10) DEFAULT NULL,

  `sex` char(6) DEFAULT 'man',

  `weight` int(5) DEFAULT '130',

  `height` int(5) DEFAULT '171',

  `course_name` varchar(15) DEFAULT NULL,

  `course_ID` char(10) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `name_list` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `students`

--

LOCK TABLES `students` WRITE;

/*!40000 ALTER TABLE `students` DISABLE KEYS */;

INSERT INTO `students` VALUES (1,'chd','man',130,171,'python','1'),(2,'wang','woman',105,165,'java','2'),(3,'li','man',132,169,'linux','5'),(4,'liu','woman',132,169,NULL,'6'),(5,'anivd','man',130,171,'python','2'),(6,'luo','woman',118,162,'linux','8'),(7,'zhang','woman',98,162,'java/linux','9'),(8,'chen','man',130,170,'python','7'),(9,'chenhuid','woman',102,168,'python','3'),(10,'chenhuahua','woman',96,158,'python','6'),(12,'chh','man',132,171,'python','8');

/*!40000 ALTER TABLE `students` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

-- Dump completed on 2021-03-13 14:54:27

备份多个数据库

如果要使用mysqldump命令备份多个数据库，需要使用--databases参数。

加上“--databases”参数后，必须指定至少一个数据库名称，多个数据库名称之间用空格隔开。

备份多个数据库的语法格式：mysqldump -u username -P --databases dbname1 dbname2 ... > filename.sql

[root@master /]# mysql -u root -p

Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MySQL connection id is 40

Server version: 5.6.19-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| chuid              |

| mysql              |

| mysql_chuid        |

| performance_schema |

+--------------------+

5 rows in set (0.00 sec)

MySQL [(none)]> quit

Bye

[root@master /]# touch chd_mysql.sql    # 创建数据库备份文件

# 使用root用户备份chuid数据库和mysql_chuid数据库

[root@master /]# mysqldump -uroot -p --databases chuid mysql_chuid >/chd_mysql.sql 

Enter password:

[root@master /]#

备份所有数据库

mysqldump命令备份所有数据库的语法格式：

mysqldump -u username -P --all-databases>filename.sql

使用“--all-databases”参数时，不需要指定数据库名称。

[root@master /]# touch all_mysql.sql    # 创建数据库备份文件

[root@master /]# mysqldump -uroot -p --all-databases >/all_mysql.sql    # 使用root用户备份所有数据库

Enter password:

[root@master /]#

MySQL恢复数据库

当数据丢失或意外损坏时，可以通过恢复已经备份的数据来尽量减少数据的丢失和破坏造成的损失。

使用mysql命令来恢复备份的数据。mysql命令可以执行备份文件中的CREATE语句和INSERT语句，也就是说，mysql命令可以通过CREATE语句来创建数据库和表，通过INSERT语句来插入备份的数据。

语法格式：mysql -u username -P [dbname] < filename.sql

* 语法说明：

1）username 表示用户名称；

2）dbname 表示数据库名称，该参数是可选参数。如果 filename.sql 文件为 mysqldump 命令创建的包含创建数据库语句的文件，则执行时不需要指定数据库名。如果指定的数据库名不存在将会报错；

3）filename.sql 表示备份文件的名称。

# 使用root用户恢复所有数据库

[root@master /]# mysql -u root -p < /all_mysql.sql

Enter password:

[root@master /]#

* 如果使用--all-databases参数备份了所有的数据库，那么恢复时不需要指定数据库。因为，其对应的sql文件中含有CREATE DATABASE语句，可以通过该语句创建数据库。创建数据库之后，可以执行sql文件中的USE语句选择数据库，然后在数据库中创建表并且插入记录。

MySQL导出表数据

通过对数据表的导入导出，可以实现MySQL数据库服务器与其它数据库服务器间移动数据。导出是指将MySQL数据表的数据复制到文本文件。

使用SELECTI...INTO OUTFILE语句将表的内容导出成一个文本文件。

用SELECT来查询所需要的数据，用INTO OUTFILE来导出数据。其中，目标文件用来指定将查询的记录导出到哪个文件。目标文件不能是一个已经存在的文件。

SELECT...INTO OUTFILE语句基本格式：SELECT 列名 FROM table [WHERE 语句] INTO OUTFILE '目标文件'[OPTIONS]

* 语法说明

1）[OPTIONS] 为可选参数选项，OPTIONS部分的语法包括FIELDS和LINES子句，其常用的取值有：

2）FIELDS TERMINATED BY '字符串'：设置字符串为字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符‘\t’。

3）FIELDS [OPTIONALLY] ENCLOSED BY '字符'：设置字符来括上CHAR、VARCHAR和TEXT等字符型字段。如果使用了OPTIONALLY则只能用来括上CHAR和VARCHAR等字符型字段。

4）FIELDS ESCAPED BY '字符'：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为‘\’。

5）LINES STARTING BY '字符串'：设置每行开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。

6）LINES TERMINATED BY '字符串'：设置每行结尾的字符，可以为单个或多个字符，默认值为‘\n’ 。

* FIELDS和LINES两个子句都是自选的，但是如果两个都被指定了，FIELDS必须位于LINES的前面。

# 使用SELECT...INTO OUTFILE 语句来导出mysql_chuid数据库中的students表中的数据

[root@master /]# mkdir /mysql/stu_mysql –p             # 创建文件路径

[root@master /]# chown mysql:mysql /mysql/stu_mysql      # 授予mysql用户所有权

[root@master /]# /etc/init.d/mysqld restart              # 重启MySQL服务

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

[root@master /]# ls -l /mysql       # 查看mysql文件

total 0

drwxr-xr-x. 2 mysql mysql 6 Mar 13 16:47 stu_mysql

[root@master /]# mysql -uroot –p           # 登录MySQL服务器

Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.19-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# 将students表中的数据导出到students.txt文件中

MySQL [(none)]> SELECT * FROM mysql_chuid.students INTO OUTFILE '/mysql/stu_mysql/students.txt';   

Query OK, 11 rows affected (0.00 sec)

MySQL [(none)]> quit        # 退出数据库

Bye

[root@master /]# cd /mysql/stu_mysql       # 切换到/mysql/stu_mysql目录下

[root@master stu_mysql]# ls       # 查看stu_mysql目录下的文件

students.txt

[root@master stu_mysql]# cat students.txt      # 查看从MySQL服务器导入到students.txt的内容

1           chd         man         130         171         python      1

2           wang        woman       105         165         java        2

3           li          man         132         169         linux       5

4           liu         woman       132         169         \N          6

5           anivd       man         130         171         python      2

6           luo         woman       118         162         linux       8

7           zhang       woman       98          162         java/linux  9

8           chen        man         130         170         python      7

9           chenhuid    woman       102         168         python      3

10          chenhuahua  woman       96          158         python      6

12          chh         man         132         171         python      8

[root@master stu_mysql]#

使用SELECT...INTO OUTFILE语句将mysql_chuid数据库中的students表中的记录导出到文本文件，使用FIELDS选项和LINES选项，要求字段之间用*隔开，字符型数据用双引号括起来。每条记录以-开头。

语法格式：SELECT * FROM mysql_chuid.students_5 INTO OUTFILE '/mysql/stu_mysql/students_5.txt' FIELDS TERMINATED BY '\*' OPTIONALLY ENCLOSED BY '\"' LINES STARTING BY '\-' TERMINATED BY '\r\n';

* 语法说明：

1）FIELDS TERMINATED BY '*’：表示字段之间用*分隔；

2）ENCLOSED BY '\"'：表示每个字段都用双引号括起来；

3）LINES STARTING BY '\-'：表示每行以-开头；

4）TERMINATED BY '\r\n' 表示每行以回车换行符结尾，保证每一条记录占一行。

[root@master stu_mysql]# mysql -uroot –p     # 登录MySQL数据库

Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.19-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> SELECT * FROM mysql_chuid.students_5 INTO OUTFILE '/mysql/stu_mysql/students_5.txt' FIELDS TERMINATED BY '\*' OPTIONALLY ENCLOSED BY '\"' LINES STARTING BY '\-' TERMINATED BY '\r\n';

Query OK, 7 rows affected (0.18 sec)

MySQL [(none)]> quit

Bye

[root@master stu_mysql]# ls

students_5.txt  students.txt

[root@master stu_mysql]# cat students_5.txt

-1*"chd"*27*129*170*"10-16"*"hubei"*"python"*\N

-2*"chuid"*25*129*170*"10-8"*"jiayu"*"linux"*\N

-3*"chen"*26*128*171*"5-3"*"shenzhen"*"java"*\N

-5*"zhang"*26*105*162*"2-14"*"shenzhen"*"java/linux"*\N

-6*"wang"*27*103*159*"6-5"*\N*"java/c++"*\N

-7*"liu"*27*102*158*"8-6"*\N*"python"*\N

-8*"guo"*28*98*156*"11-30"*"hunan"*"linux"*\N

[root@master stu_mysql]#

# 可以看到：每条记录都以-开头，每个数据之间以都以*隔开，所有的字段值都被双引号包括

MySQL数据库恢复

数据库恢复是指以备份为基础，与备份相对应的系统维护和管理操作。

系统进行恢复操作时，先执行一些系统安全性的检查，包括检查所要恢复的数据库是否存在、数据库是否变化及数据库文件是否兼容等，然后根据所采用的数据库备份类型采取相应的恢复措施。

* 数据库恢复机制设计的两个关键问题是：

1）如何建立冗余数据；

2）如何利用这些冗余数据实施数据库恢复。

建立冗余数据最常用的技术是数据转储和登录日志文件。通常在一个数据库系统中，这两种方法是一起使用的。

数据转储是DBA定期地将整个数据库复制到磁带或另一个磁盘上保存起来的过程。这些备用的版本成为后备副本或后援副本。

使用 LOAD DATA…INFILE 语句来恢复先前备份的数据。

# 将上面导出的数据备份文件students_5.txt导入数据库mysql_chuid的表stu5_backup中，其中students_5与stu5_backup的表结构相同

LOAD DATA INFILE是SELECT ... INTO OUTFILE的相对语句。把表的数据备份到文件使用SELECT ... INTO OUTFILE，从备份文件恢复表数据，使用LOAD DATA INFILE。

语法格式：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'

    [REPLACE | IGNORE]

    INTO TABLE tbl_name

    [FIELDS

        [TERMINATED BY 'string']

        [[OPTIONALLY] ENCLOSED BY 'char']

        [ESCAPED BY 'char' ]

    ]

    [LINES

        [STARTING BY 'string']

        [TERMINATED BY 'string']

    ]

    [IGNORE number LINES]

    [(col_name_or_user_var,...)]

    [SET col_name = expr,...)]

* 示例：

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE tbl_name

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'

* 语句说明：

LOW_PRIORITY | CONCURRENT 关键字

LOW_PRIORITY  该参数适用于表锁存储引擎，比如MyISAM, MEMORY, 和 MERGE，在写入过程中如果有客户端程序读表，写入将会延后，直至没有任何客户端程序读表再继续写入。

CONCURRENT   使用该参数，允许在写入过程中其它客户端程序读取表内容。

L0CAL关键字

L0CAL关键字影响数据文件定位和错误处理。只有当mysql-server和mysql-client同时在配置中指定允许使用,L0CAL关键字才会生效。如果 mysqld 的 local_infile系统变量设置为 disabled，L0CAL关键字将不会生效。

LOCAL load operations

如果指定了LOCAL,数据文将被客户端程序从客户端主机读取，然后发送给服务器主机。文件路径可以使用绝对路径或相对路径。如果使用相对路径，数据文件实际路径相对于客户端程序启动时的当前路径。

使用LOCAL,将在服务器主机的临时目录创建一个数据文件的副本（linux 使用 /tmp,windows 使用 C:\WINDOWS\TEMP），如果临时目录剩余空间不足，将导致语句执行失败。

Non-LOCAL load operations

* 如果没有指定LOCAL，数据五年间必须位于服务器值机上，直接由mysql-server读取。mysql-serve使用如下规则来定位文件:

1. 数据文件使用绝对路径，直接使用

2. 数据文件使用相对路径并且有前导的部分，将相对于mysql-server的数据目录查找，例如 ./myfile.txt

3. 数据文件使用相对路径并且没有前导的部分，将相对于默认数据库的数据文件目录查找,例如myfile.txt

* 根据上面的规则，./myfile.txt将被定位到mysql-server的data directory,而myfile.txt将被定位到default database的database directory

REPLACE | IGNORE 关键字

REPLACE和IGNORE关键词控制对现有的唯一键记录的重复的处理。如果你指定REPLACE，新行将代替有相同的唯一键值的现有行。如果你指定IGNORE，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键键时，出现一个错误，并且文本文件的余下部分被忽略时。

FIELDS 子句

如果你指定一个FIELDS子句，它的每一个子句(TERMINATED BY, [OPTIONALLY] ENCLOSED BY和ESCAPED BY)也是可选的，但是你必须至少指定一个。

如果你不指定 FIELDS 或 LINES ，缺省值为：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

LINES TERMINATED BY '\n' STARTING BY ''

* 缺省值导致读取输入时，LOAD DATA INFILE表现如下：

1）在 \n 处寻找行边界

2）在 \t 处将行分进字段

3）不要期望字段由任何引号字符封装

4）将由“\”开头的定位符、换行符或“\”解释成转义序列。例如 \t, \n, and \ 分别解释成 定位符，换行，反斜杠。

* 相反，缺省值导致在写入输出时，SELECT ... INTO OUTFILE表现如下：

1）1.在字段之间写 \t    2.不用任何引号字符封装字段

2）使用“\”转义出现在字段中的 定位符、换行符或“\”字符

3）在行尾处写换行符

FIELDS [OPTIONALLY] ENCLOSED BY

FIELDS [OPTIONALLY] ENCLOSED BY 控制哪些字段应该包裹在引号里面。

1)对于SELECT ... INTO OUTFILE 输出，如果不包含OPTIONALLY选项，所有的字段将会被ENCLOSED BY指定的字符包裹。

2)如果我们指定OPTIONALLY，只有string数据类型（如 CHAR, BINARY, TEXT, 或 ENUM）的字段才会被ENCLOSED BY指定的字符包裹。

3)如果在字段值内出现ENCLOSED BY字符，则通过使用ESCAPED BY字符作为前缀，对ENCLOSED BY字符进行转义。

如果指定了一个空ESCAPED BY值，则可能会生成不能被LOAD DATAINFILE 正确读取的输出值。

FIELDS ESCAPED BY

用来控制如何对特殊字符进行读写，如上面一个例子，导出和导入时指定FIELDS ESCAPED BY为双引号["]才能被正确的导入，导出的格式如下，对字段内的双引号["]进行了转义。

对于输入：

假如FIELDS ESCAPED BY指定字符非空，则输入时该字符被移除，后续的内容被添加到字段里。一些两个字符的字符串序列且第一个字符是转义字符的例外 。

假如FIELDS ESCAPED BY指定字符为空，将不会发生转义序列的解释。

对于输出：

如果FIELDS ESCAPED BY指定字符非空，字符作为以下输出的前缀。

1. FIELDS ESCAPED BY 字符，例如 \

2. FIELDS [OPTIONALLY] ENCLOSED BY 字符，例如 "

3. FIELDS TERMINATED BY and LINES TERMINATED BY 的 value 的第一个字符,例如\n

4. ASCII 0

如果LINES TERMINATED BY是空字符串，FIELDS TERMINATED BY非空,字符将不会被转义，NULL 将输出为 NULL而不是 \N。指定LINES TERMINATED BY为空字符串并不是个好主意，特别当内容中包含上表列出的特殊字符时。

LINES STARTING BY

如果所有希望读入的行都含有一个我们希望忽略的共用前缀，则可以使用 LINES STARTING BY 'prefix_string' 来跳过前缀（以及该前缀前的所有字符）。如果某行不包括前缀，则整个行被跳过。

IGNORE number LINES

该选项可以被用于在文件的开始处忽略行。例如，我们可以使用IGNORE 1 LINES来跳过一个包含列名称的起始标题行。

在特定情况下，field-和line-handling选项相互影响

1）如果LINES TERMINATED BY是空字符串，FIELDS TERMINATED BY非空,行以FIELDS TERMINATED BY指定的字符串作为结尾。

2）如果FIELDS TERMINATED BY与FIELDS ENCLOSED BY值均为空('')，将使用固定行（无分割）格式。使用固定行格式，字段之间将没有分隔符（行终止符依然可使用），列字段数据的读取和写入均按照字段定义的宽度去操作，如TINYINT, SMALLINT, MEDIUMINT, INT,和BIGINT,字段宽度分别为4, 6, 8, 11,和20。

不适合使用 LOAD DATA INFILE 的情况

1. 使用固定行格式（即FIELDS TERMINATED BY 和 FIELDS ENCLOSED BY 均为空）,列字段类型为BLOB或TEXT。

2. 指定分隔符与其它选项前缀一样，LOAD DATA INFILE不能对输入做正确的解释。

3. 如果 FIELDS ESCAPED BY 为空，字段值包含 FIELDS ENCLOSED BY 指定字符，或者 LINES TERMINATED BY 的字符在 FIELDS TERMINATED BY 之前，都会导致过早的停止 LOAD DATA INFILE操作。因为LOAD DATA INFILE不能准确的确定行或列的结束。

选择导入的列

下面的语句会导入文件的所有列：

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

如果我们想导入表的某些列，需要指定列的列表：

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

如果输入文件与表的列顺序不同，我们必须指定一个列清单，否则mysql不能把输入文件的字段与表的列匹配起来。

mysql> LOAD DATA INFILE '/mysql/stu3_mysql/students.txt' INTO TABLE students_backup

    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '#' LINES TERMINATED BY '\n' (c1, c2, c4, c3);  

将之前导出的数据备份文件students.txt导入mysql_chuid数据库的students_backup表中，其中students_backup的表结构和students相同

MySQL [mysql_chuid]> SELECT * FROM mysql_chuid.students INTO OUTFILE '/mysql/stu3_mysql/students.txt';

Query OK, 11 rows affected (0.10 sec)

[root@master ~]# cd /mysql/stu3_mysql/      # 在Linux服务器上切换到stu3_mysql目录下

[root@master stu3_mysql]# ls       # 查看stu3_mysql目录下的文件

students_5.txt  students.txt

[root@master stu3_mysql]# cat students.txt      # 查看students.txt文件中的数据

1           chd         man         130         171         python      1

2           wang        woman       105         165         java        2

3           li          man         132         169         linux       5

4           liu         woman       132         169         \N          6

5           anivd       man         130         171         python      2

6           luo         woman       118         162         linux       8

7           zhang       woman       98          162         java/linux  9

8           chen        man         130         170         python      7

9           chenhuid    woman       102         168         python      3

10          chenhuahua  woman       96          158         python      6

12          chh         man         132         171         python      8

[root@master stu3_mysql]#

MySQL [mysql_chuid]> CREATE TABLE students_backup LIKE students;   # 创建表students_backup（与students表结构相同）

Query OK, 0 rows affected (0.02 sec)

MySQL [mysql_chuid]> SELECT * FROM students_backup;    # 查看students_backup表中的所有内容

Empty set (0.00 sec)

MySQL [mysql_chuid]> alter table students_backup modify id varchar(10);   # 修改id字段的数据类型（可忽略）

Query OK, 0 rows affected (0.07 sec)

Records: 0  Duplicates: 0  Warnings: 0

# 导入数据与查询表students_backup

MySQL [mysql_chuid]> load data infile '/mysql/stu3_mysql/students.txt' into table mysql_chuid.students_backup fields terminated by '\t';

Query OK, 11 rows affected (0.01 sec)

Records: 11  Deleted: 0  Skipped: 0  Warnings: 0

MySQL [mysql_chuid]> select * from students_backup;

+----+------------+-------+--------+--------+-------------+-----------+

| id | name       | sex   | weight | height | course_name | course_ID |

+----+------------+-------+--------+--------+-------------+-----------+

| 1  | chd        | man   |    130 |    171 | python      | 1         |

| 10 | chenhuahua | woman |     96 |    158 | python      | 6         |

| 12 | chh        | man   |    132 |    171 | python      | 8         |

| 2  | wang       | woman |    105 |    165 | java        | 2         |

| 3  | li         | man   |    132 |    169 | linux       | 5         |

| 4  | liu        | woman |    132 |    169 | NULL        | 6         |

| 5  | anivd      | man   |    130 |    171 | python      | 2         |

| 6  | luo        | woman |    118 |    162 | linux       | 8         |

| 7  | zhang      | woman |     98 |    162 | java/linux  | 9         |

| 8  | chen       | man   |    130 |    170 | python      | 7         |

| 9  | chenhuid   | woman |    102 |    168 | python      | 3         |

+----+------------+-------+--------+--------+-------------+-----------+

11 rows in set (0.00 sec)