MySQL数据库备份与恢复

尽管采取了一些管理措施来保证数据库的安全，但是在不确定的意外情况下，总是有可能造成数据的损失。

• 例如，意外的停电，不小心的操作失误等都可能造成数据的丢失。

所以为了保证数据的安全，我们需要定期对数据进行备份。如果数据库中的数据出现了错误，就需要使用备份好的数据进行数据还原，这样可以将损失降至最低。

MySQL 提供了多种方法对数据进行备份和恢复。

数据库为什么需要备份

任何数据库都需要备份，备份数据是维护数据库必不可少的操作。

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

因此，数据备份是很重要的工作。

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

数据丢失应用场景：

• 人为操作失误造成某些数据被误操作
• 软件 BUG 造成部分数据或全部数据丢失
• 硬件故障造成数据库部分数据或全部数据丢失
• 安全漏洞被入侵数据恶意破坏

非数据丢失应用场景：

• 特殊应用场景下基于时间点的数据恢复
• 开发测试环境数据库搭建
• 相同数据库的新环境搭建
• 数据库或者数据迁移

以上列出的是一些数据库备份常见的应用场景，数据库备份还有其它应用场景，这里就不一一列举了。

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

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

MySQL备份类型

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

备份可以分为以下几个类型。根据备份的方法（是否需要数据库离线）可以将备份分为：

• 热备（Hot Backup）
  • 热备份可以在数据库运行中直接备份，对正在运行的数据库操作没有任何的影响，数据库的读写操作可以正常执行。这种方式在 MySQL 官方手册中称为 Online Backup（在线备份）。
  • 按照备份后文件的内容，热备份又可以分为：
    • 逻辑备份：在 MySQL 数据库中，逻辑备份是指备份出的文件内容是可读的，一般是文本内容。内容一般是由一条条 SQL 语句，或者是表内实际数据组成。如 mysqldump 和 SELECT * INTO OUTFILE 的方法。这类方法的好处是可以观察导出文件的内容，一般适用于数据库的升级、迁移等工作。但其缺点是恢复的时间较长。
    • 裸文件备份：裸文件备份是指复制数据库的物理文件，既可以在数据库运行中进行复制（如 ibbackup、xtrabackup 这类工具），也可以在数据库停止运行时直接复制数据文件。这类备份的恢复时间往往比逻辑备份短很多。
  • 按照备份数据库的内容来分，备份又可以分为：
    • 完全备份：是指对数据库进行一个完整的备份，即备份整个数据库，如果数据较多会占用较大的时间和空间。
    • 部分备份：是指备份部分数据库（例如，只备份一个表）。部分备份又分为：
      • 增量备份需要使用专业的备份工具。指的是在上次完全备份的基础上，对更改的数据进行备份。也就是说每次备份只会备份自上次备份之后到备份时间之内产生的数据。因此每次备份都比差异备份节约空间，但是恢复数据麻烦。
      • 差异备份指的是自上一次完全备份以来变化的数据。和增量备份相比，浪费空间，但恢复数据比增量备份简单。
• 冷备（Cold Backup）
  • 冷备份必须在数据库停止的情况下进行备份，数据库的读写操作不能执行。这种备份最为简单，一般只需要复制相关的数据库物理文件即可。这种方式在 MySQL 官方手册中称为 Offline Backup（离线备份）。
• 温备（Warm Backup）
  • 温备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，备份时仅支持读操作，不支持写操作。

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

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

• 表数据
• 二进制日志、InnoDB 事务日志
• 代码（存储过程、存储函数、触发器、事件调度器）
• 服务器配置文件

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

• mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备。
• cp、tar 等归档复制工具：物理备份工具，适用于所有的存储引擎、冷备、完全备份、部分备份。
• lvm2 snapshot：借助文件系统管理工具进行备份。
• mysqlhotcopy：名不副实的一个工具，仅支持 MyISAM 存储引擎。
• xtrabackup：一款由 percona 提供的非常强大的 InnoDB/XtraDB 热备工具，支持完全备份、增量备份。

MySQL热备份及恢复

热备份是在数据库处于运行状态时直接备份，不影响现有业务的正常进行。热备份又细分为逻辑备份和裸文件备份，

逻辑备份

逻辑备份的最大优点就是对于各种存储引擎，都可以用同样的方法来备份。而冷备份则不同，不同的存储引擎的备份方法也各不相同。因此，对于不同存储引擎混合的数据库，用逻辑备份会更简单一些。

逻辑备份可以说是最简单，也是目前中小型系统最常用的备份方法。逻辑备份主要有以下几种方法：

mysqldump

mysqldump 是 MySQL 自带的逻辑备份工具。它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，然后将查询出的数据转换成对应的 INSERT 语句。当我们需要还原恢复这些数据时，只要执行这些 INSERT 语句，就能将对应的数据还原。所以有的资料也将这种备份方式称为 INSERT 备份。

恢复数据时可以使用 mysql -uroot -p <backup.sql 直接调用备份文件执行所有命令，将数据完全恢复到备份时候的状态。如果已经连接上了 MySQL 服务器，那么可以通过 source /path/backup.sql 来进行恢复。

SELECT INTO…OUTFILE

SELECT INTO…OUTFILE 语句可以把表数据导出到一个文本文件中，且能将数据库中的表数据以特定分隔符进行分隔后记录在文本文件中，以达到逻辑备份的效果。

这种备份方式与 mysqldump 方法相比，使用的存储空间更小，数据格式更加清晰明确，编辑方便。但是这种方法只能导出或导入数据的内容，不包括表的结构，如果表的结构文件损坏，则必须先恢复原来的表的结构。而且这种方法不能在同一个备份文件中存在多个表的备份数据，增加了文件维护和恢复的成本。

这种备份方法恢复起来会稍微麻烦一点，需要一个表一个表通过相关命令来进行恢复。当然如果是通过脚本来实现自动多表恢复也是比较方便的。恢复方法有 2 个，一个是通过 MySQL 的 LOAD DATA INFILE 命令来恢复数据，另一种方法就是通过 MySQL 提供的使用工具 mysqlimport 来进行恢复。

mydumper

mydumper 是针对 MySQL 数据库备份的一个轻量级第三方的开源工具，备份方式为逻辑备份。它支持多线程，备份速度远高于原生态的 mysqldump 以及其它众多优异特性。与其配套的相应恢复数据为 myloader 工具。DBA 称 mydumper 和 myloader 为备份界的“小钢炮”。

我们可以看出所谓的逻辑备份就是备份 SQL 语句，然后恢复数据时执行备份 SQL，从而实现数据库数据的重现。逻辑备份完成后所形成的文件都可以直接编辑。

逻辑备份的作用如下：

• 通过逻辑备份，我们可以仅仅恢复备份中的部分数据而不需要全部恢复。不会影响不相关的数据；
• 通过全库的逻辑备份，我们可以在新的 MySQL 环境下完全重建出一个与备份时完全一样的数据库，并且不受平台类型限制；
• 通过特定条件的逻辑备份，我们可以将某些特定数据轻松迁移（或者同步）到其它的 MySQL 或另外的数据库环境。

裸文件备份

裸文件备份主要在底层复制数据文件，所以备份速度要比逻辑备份更快。

我们利用 Percona 公司发布的一个 XtraBackup 热备份工具来完成裸文件备份，它是 Percona 公司的开源项目，据官方介绍它是世界上唯一 一款开源的能够对 InnoDB 和 XtraDB 数据库进行热备的工具。

它的优点就是备份与恢复过程的速度很快，安全可靠，而且在备份过程中不会缩表，不影响现有业务。但它目前还是不能对表结构文件和其它非事务类型的表进行备份。

MySQL冷备份及恢复

冷备份可以称为 Offline Backup（离线备份）。这种备份最为简单，一般只需要复制相关的数据库物理文件到另外的位置即可。

由于 MySQL 服务器中的数据文件是基于磁盘的文本文件，所以最简单、最直接的备份操作，就是将数据文件直接复制出来。但是由于 MySQL 服务器的数据文件在运行时期，总是处于打开和使用状态，因此备份文件不一定有效。为了解决该问题，在复制数据文件时，需要先停止 MySQL 服务器。

这样做的好处是可以保证数据库的完整性，备份过程简单且恢复速度相对快一些，但是关闭数据库会影响现有业务的进行。服务器停止运行期间，用户不能再继续访问网站。例如，一些电商网站店庆促销时，如果为了备份要停库，那么带来的代价损失将不可估量。所以冷备一般用于不是很重要、非核心的业务上面。

冷备份的优点如下：

• 备份简单、快速，只要复制相关文件即可。
• 备份文件易于在不同操作系统，不同 MySQL 版本上进行恢复。
• 恢复相当简单，只需要把文件恢复到指定位置即可。
• 恢复速度快，不需要执行任何 SQL 语句，也不需要重建索引。
• 低度维护，高度安全。

冷备份的缺点如下：

• 备份过程中，数据库不能做其它的工作，且必须是关闭状态。
• InnoDB 存储引擎冷备的文件通常比逻辑文件大很多，因为表空间存放着很多其它的数据，如 undo 段，插入缓冲等信息。
• 若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。
• 冷备也不总是可以轻易的跨平台。操作系统、MySQL 的版本、文件大小写敏感和浮点数格式都会成为问题。

数据库的物理文件主要由数据库的数据文件、日志文件以及配置文件等组成。MySQL 系统有一些共有的日志文件和系统表的数据文件。每种存储引擎的物理文件也不一样，

冷备的备份与恢复过程也很简单。仅仅需要如下几步：

1. 为了保证所备份数据的完整性，在停止 MySQL 数据库服务器之前，需要先执行 FLUSH TABLES 语句将所有数据写入到数据文件的文本文件里。
2. 停掉 MySQL 服务，命令（2种方式）如下：
   • mysqladmin -uroot -proot shutdown
   • NET START mysql
3. 备份过程就是复制整个数据目录到远程备份机或者本地磁盘上，Linux 和 Windows 命令如下：
   • Scp -r /data/mysql/ root@远程备份机ip:/新的目录
   • Copy -r /data/mysql/ 本地新目录
     • 备份到本次磁盘也可以手动复制上述相关目录里的数据文件。
4. 恢复过程就更简单了，仅仅需要把已备份的数据目录替换原有的目录就可以了，最后重启 MySQL 服务。
   • 恢复数据是数据库维护中最常用的操作，利用备份文件可以将 MySQL 数据库服务器恢复到备份时的状态，这样就可以将管理员的非常操作和计算机的故障造成的相关损失降到最小。

需要注意的是，通过复制数据文件这种方式实现数据恢复时，必须保证两个 MySQL 数据库的主版本号一致。只有两个 MySQL 数据库主版本号相同时，才能保证它们的数据文件类型是相同的。

MySQL冷备份所需物理文件

MyISAM存储引擎

MyISAM 存储引擎的所有数据默认存放在 C:/ProgramData/MySQL/MySQL Server 5.7/Data 路径下，即配置文件（my.ini或my.cnf）中 datadir 参数的值。

实际上不管我们使用的是 MyISAM 存储引擎还是其他存储引擎，每一个数据库都会在“datadir”目录下有一个文件夹（包括系统信息的数据库 mysql 也是一样）。

在各个数据库中每一个 MyISAM 存储引擎的表都会有 3 个文件存在，即记录表结构元数据的“.frm”文件，存储表数据的“.MYD”文件，存储索引数据的“.MYI”文件。

MyISAM 属于非事务性存储引擎，它没有自己的日志文件。所以 MyISAM 存储引擎的物理备份除了需要备份 MySQL 系统共有的物理文件之外，还需要备份上面的 3 种文件。

Innodb 存储引擎

Innodb 存储引擎属于事务性存储引擎，存放数据的位置也可能与 MyISAM 存储引擎有所不同，这主要取决于 Innodb 的相关配置。

指定 Innodb 存放数据和日志文件的位置参数为 innodb_data_home_dir、innodb_data_file_path 和 innodb_log_group_home_dir。以及决定 Innodb 的表空间存储方式参数 innodb_file_per_table，它决定 Innodb 是以共享表空间存放数据还是以独享表空间方式存储数据。

如果使用的是共享表空间的存储方式，那么需要备份 innodb_data_home_dir 和 innodb_data_file_path 参数设定的所有数据文件，以及 datadir 中相应数据库目录下的所有 Innodb 存储引擎表的 .frm 文件。

而如果使用的是独享表空间，那么除了需要备份上面共享表空间方式所需要备份的所有文件之外，我们还需要备份 datadir 中相应数据库目录下的所有 .idb 文件，该文件中存放的才是独享表空间方式下 Innodb 存储引擎表的数据。

那么既然是使用独享表空间，为什么还要备份共享表空间“才使用到”的数据文件呢？其实这是很多人的一个共性误区，以为使用独享表空间的时候 Innodb 的所有信息就都存放在“datadir”所设定数据库目录下的“.ibd”文件中。实际上并不是这样的，“.ibd”文件中所存放的仅仅只是我们的表数据而已。

大家都很清楚，Innodb 是事务性存储引擎，它需要 undo 和 redo 信息，而不管 Innodb 使用的是共享还是独享表空间的方式来存储数据。与事务相关的 undo 信息以及其他的一些元数据信息，都是存放在 innodb_data_home_dir 和 innodb_data_file_path 这两个参数所设定的数据文件中的。所以要想 Innodb 的物理备份有效，就必须备份 innodb_data_home_dir 和 innodb_data_file_path 参数所设定的数据文件。

此外，除了上面所说的数据文件之外，Innodb 还有存放自己的 redo 信息和相关事务信息的日志文件在 innodb_log_group_home_dir 参数所设定的位置。所以要想 Innodb 物理备份能够有效使用，我们还必须要备份 innodb_log_group_home_dir 参数所设定的位置的所有日志文件。

MySQL数据库迁移

数据库迁移就是把数据从一个系统移动到另一个系统上，迁移过程其实就是在源数据库备份和目标数据库恢复的过程组合。迁移的原因是多种多样的，比如：

• 需要安装新的数据库服务器
• MySQL 版本更新
• 数据库管理系统的变更（如从 SQL Server 迁移到 MySQL）

根据实际操作等情况，可以将数据库迁移操作分成以下 3 种形式。

• 相同版本 MySQL 数据库之间的迁移。
• 不同版本 MySQL 数据库之间的迁移。
• 不同数据库间的迁移。

相同版本的迁移

相同版本的 MySQL 数据库是指主版本号一致的数据库。主版本号一致的数据库迁移最容易实现。由于迁移前后 MySQL 数据库的主版本号相同，所以可以通过复制数据库目录来实现数据库迁移。

最安全和最常用的方式是通过使用 mysqldump 命令进行数据库备份，然后使用 mysql 命令将备份文件还原到新的 MySQL 数据库。迁移时的备份和还原操作可以同时执行。

假设从一个名为 hostname1 的机器中备份出所有数据库，然后将这些数据库迁移到名为 hostname2 的机器上，具体语法形式如下：

mysqldump -h hostname1 -u root -password=password1 -all-databases
|
mysql -h hostname2 -u root -password=password2

其中：

• 符号“|”用来实现将命令 mysqldump 备份的文件送给 mysql 命令；
• password1 为 hostname1 主机上 root 用户的密码；
• password2 为 hostname2 主机上 root 用户的密码；
• -all-databases 表示迁移全部的数据库，可省略。

不用版本的迁移

不同版本的 MySQL 数据库之间的数据迁移通常是 MySQL 升级的原因。例如，服务器使用 4.0 版本的 MySQL 数据库，现在要升级为 5.7 版本的。这样就需要不同版本的 MySQL 数据库之间进行数据迁移。

不同版本下的数据库迁移，分为 2 种方式：

• 低版本数据库向高版本数据库进行迁移
• 高版本数据库向低版本数据库进行迁移

低版本数据库向高版本数据库进行迁移时，由于高版本会兼容低版本，所以该种方式也是最容易实现的操作。对于存储类型为 MyISAM 的表，最安全和最常用的操作是直接复制数据文件。对于存储类型为 InnoDB 的表，最安全和最常用的操作是执行 mysqldump 命令进行备份和执行 mysql 命令还原恢复数据。

但是高版本数据库向低版本数据库进行迁移时，因为高版本数据库可能有一些新的特性，这些特性是低版本数据库所不具有的，所以数据库迁移时要特别小心，最好使用 mysqldump 命令来进行备份，避免迁移时造成数据丢失。

不同数据库的迁移

不同数据库之间的迁移是指从其它类型的数据库迁移到 MySQL 数据库，或者从 MySQL 数据库迁移到其他类型的数据库。例如，某个网站原来使用 Oracle 数据库，因为运营成本太高等诸多原因，希望改用 MySQL 数据库。或者，某个管理系统原来使用 MySQL 数据库，因为某种特殊性能的要求，希望改用 Oracle 数据库。这样的不同数据库之间的迁移也经常会发生。但是这种迁移没有普通适用的解决办法。

其它数据库也有类似 mysqldump 这样的备份工具，可以将数据库中的文件备份成 sql 文件或普通文本。但是，不同的数据库厂商并没有完全按照 SQL 标准来设计数据库，这就造成了不同数据库使用的 SQL 语句的差异。例如，微软的 SQL Server 软件使用的是 T-SQL 语言。T-SQL 中包含了非标准的 SQL 语句。这就造成了 SQL Server 和 MySQL 的 SQL 语句不能兼容。

除了 SQL 语句存在不兼容的情况外，不同的数据库之间的数据类型也有差异。例如，MySQL 不支持 SQL Server 中的 ntext、 Image 等数据类型。同样，SQL Server 也不支持 MySQL 中的 ENUM 和 SET 等数据类型。数据类型的差异也造成了迁移的困难。

从某种意义上说，这种差异是商业数据库公司故意造成的壁垒，这种行为是阻碍数据库市场健康发展的。

但是不同数据库服务器间的迁移并不是完全不可能。在 Windows 操作系统下，如果要实现从 MySQL 数据库服务器向 SQL SERVER 数据库服务器迁移，可以通过 MyODBC 来实现；如果要实现从 MySQL 数据库服务器向 ORACLE 数据库服务器迁移，可以先通过执行 mysqldump 命令导出 sql 文件，然后手动修改 sql 文件中的 CREATE 语句。

MySQL mysqldump备份数据库

数据库的主要作用就是对数据进行保存和维护，所以备份数据是数据库管理中最常用的操作。为了防止数据库意外崩溃或硬件损伤而导致的数据丢失，数据库系统提供了备份和恢复策略。

保证数据安全的最重要的一个措施就是定期的对数据库进行备份。这样即使发生了意外，也会把损失降到最低。

数据库备份是指通过导出数据或者复制表文件的方式来制作数据库的副本。当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。

MySQL 中提供了两种备份方式，即 mysqldump 命令以及 mysqlhotcopy 脚本。由于 mysqlhotcopy 只能用于 MyISAM 表，所以 MySQL 5.7 移除了 mysqlhotcopy 脚本。

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

备份一个数据库

使用 mysqldump 命令备份一个数据库的语法格式如下：

mysqldump -u username -p dbname [tbname ...]> filename.sql

对上述语法参数说明如下：

• username：表示用户名称；
• dbname：表示需要备份的数据库名称；
• tbname：表示数据库中需要备份的数据表，可以指定多个数据表。省略该参数时，会备份整个数据库；
• 右箭头“>”：用来告诉 mysqldump 将备份数据表的定义和数据写入备份文件；
• filename.sql：表示备份文件的名称，文件名前面可以加绝对路径。通常将数据库备份成一个后缀名为.sql的文件。

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

备份多个数据库

如果要使用 mysqldump 命令备份多个数据库，需要使用 --databases 参数。备份多个数据库的语法格式如下：

mysqldump -u username -P --databases dbname1 dbname2 ... > filename.sql

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

备份所有数据库

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

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

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

MySQL恢复数据库

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

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

mysql 命令语法格式如下：

mysql -u username -P [dbname] < filename.sql

其中：

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

注意：mysql 命令和 mysqldump 命令一样，都直接在命令行（cmd）窗口下执行。

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

MySQL导出表数据

通过对数据表的导入导出，可以实现 MySQL 数据库服务器与其它数据库服务器间移动数据。导出是指将 MySQL 数据表的数据复制到文本文件。数据导出的方式有多种，本节主要介绍使用 SELECTI...INTO OUTFILE 语句导出数据。

在 MySQL 中，可以使用 SELECTI...INTO OUTFILE 语句将表的内容导出成一个文本文件。SELECT...INTO OUTFILE 语句基本格式如下:

SELECT 列名 FROM table [WHERE 语句] INTO OUTFILE ‘目标文件‘[OPTIONS]

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

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

• FIELDS TERMINATED BY ‘字符串‘：设置字符串为字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符‘\t’。
• FIELDS [OPTIONALLY] ENCLOSED BY ‘字符‘：设置字符来括上 CHAR、VARCHAR 和 TEXT 等字符型字段。如果使用了 OPTIONALLY 则只能用来括上 CHAR 和 VARCHAR 等字符型字段。
• FIELDS ESCAPED BY ‘字符‘：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为‘\’。
• LINES STARTING BY ‘字符串‘：设置每行开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。
• LINES TERMINATED BY ‘字符串‘：设置每行结尾的字符，可以为单个或多个字符，默认值为‘\n’ 。

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

注意：导出时可能会出现下面的错误：

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

这是因为MySQL 限制了数据的导出路径。MySQL 导入导出文件只能在 secure-file-priv 变量的指定路径下的文件才可以导入导出。

有以下 2 种解决办法：

首先使用show variables like ‘%secure%‘;语句查看 secure-file-priv 变量配置。

mysql> show variables like ‘%secure%‘ \G
*************************** 1. row ***************************
Variable_name: require_secure_transport
        Value: OFF
*************************** 2. row ***************************
Variable_name: secure_auth
        Value: ON
*************************** 3. row ***************************
Variable_name: secure_file_priv
        Value: C:\ProgramData\MySQL\MySQL Server 5.7\Uploads3 rows in set, 1 warning (0.04 sec)

secure_file_priv 的值指定的是 MySQL 导入导出文件的路径。将 SQL 语句中的导出文件路径修改为该变量的指定路径，再执行导入导出操作即可。也可以在 my.ini 配置文件中修改 secure-file-priv 的值，然后重启服务即可。

如果 secure_file_priv 值为 NULL，则为禁止导出，可以在 MySQL 安装路径下的 my.ini 文件中添加secure_file_priv=设置路径语句，然后重启服务即可。

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

SELECT * FROM test.person INTO OUTFILE ‘C:/person.txt‘
    FIELDS TERMINATED BY ‘\、‘ OPTIONALLY ENCLOSED BY ‘\"‘ LINES STARTING BY ‘\-‘
TERMINATED BY ‘\r\n‘;

其中：

• FIELDS TERMINATED BY ‘、’：表示字段之间用、分隔；
• ENCLOSED BY ‘"‘：表示每个字段都用双引号括起来；
• LINES STARTING BY ‘-‘：表示每行以-开头；
• TERMINATED BY ‘\r\n‘ 表示每行以回车换行符结尾，保证每一条记录占一行。

person.txt 文件内容如下：

-1、"Java"、12
-2、"MySQL"、13
-3、"C"、15
-4、"C++"、22
-5、"Python"、18

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

MySQL数据库恢复

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

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

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

• 第一，如何建立冗余数据；
• 第二，如何利用这些冗余数据实施数据库恢复。

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

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

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

例子：将之前导出的数据备份文件 file.txt 导入数据库 test_db 的表 tb_students_copy 中，其中 tb_students_copy 的表结构和 tb_students_info 相同。

首先创建表 tb_students_copy，输入的 SQL 语句和执行结果如下所示。

mysql> CREATE TABLE tb_students_copy
    -> LIKE tb_students_info;
Query OK, 0 rows affected (0.52 sec)
mysql> SELECT * FROM tb_students_copy;
Empty set (0.00 sec)

导入数据与查询表 tb_students_copy 的过程如下所示。mysql> LOAD DATA INFILE ‘C:/ProgramData/MySQL/MySQL Server 5.7/

Uploads/file.txt‘
    -> INTO TABLE test_db.tb_students_copy
    -> FIELDS TERMINATED BY ‘,‘
    -> OPTIONALLY ENCLOSED BY ‘"‘
    -> LINES TERMINATED BY ‘?‘;
Query OK, 10 rows affected (0.14 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
mysql> SELECT * FROM test_db.tb_students_copy;
+----+--------+---------+------+------+--------+------------+
| id | name   | dept_id | age  | sex  | height | login_date |
+----+--------+---------+------+------+--------+------------+
|  1 | Dany   |       1 |   25 | F    |    160 | 2015-09-10 |
|  2 | Green  |       3 |   23 | F    |    158 | 2016-10-22 |
|  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 |
|  4 | Jane   |       1 |   22 | F    |    162 | 2016-12-20 |
|  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 |
|  6 | John   |       2 |   21 | M    |    172 | 2015-11-11 |
|  7 | Lily   |       6 |   22 | F    |    165 | 2016-02-26 |
|  8 | Susan  |       4 |   23 | F    |    170 | 2015-10-01 |
|  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 |
| 10 | Tom    |       4 |   23 | M    |    165 | 2016-08-05 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.00 sec)


资源链接