第十一章 - 备份与还原

第十一章 - 备份与还原

数据库的安全性和完整性是确保实现数据的可靠性、精确性和高效性的重要技术手段。为了保证数据的安全，防止意外事件的发生，需要制度化的定期对数据进行备份。

如果数据库系统数据遭到破坏，就可以使用备份好的数据进行数据还原，将损失降低到最小。另外数据表之间的数据导入与导出技术，也为数据管理提供了可靠的备份功能。

本章主要介绍数据损失的原因，以及数据备份和数据恢复的方法，并要求理解数据库迁移、数据的导入与导出的方法。

11.1 备份和恢复概述

数据备份和恢复是数据库管理中最常用的操作。备份和恢复的目的就是将数据库中的数据进行导出，生成副本，然后在系统发生故障后能够恢复全部或部分数据。

数据备份就是制作数据库结构、对象和数据的备份，以便在数据库遭到破坏时，或因需求改变而能够把数据库还原到改变以前时的状态。

数据恢复就是指将数据库备份加载到系统中。数据备份和恢复可以用于保护数据库的关键数据。在系统发生错误或者因需求改变时，利用备份的数据可以恢复数据库中的数据。

1. 平时做好两件事：转储和建立日志

周期地（比如一天一次）对整个数据进行复制，转储到另一个磁盘或磁带一类的存储介质中。

建立日志数据库。记录事务的开始、结束标志，记录事务对数据库的每一次插入、删除和修改前后的值，写到日志库中，以便有案可查。

2. 数据库系统基本的共同恢复方法

优先写日志。任何对数据库中数据元素的变更都必须先写入日志；将变更的数据写入磁盘前，日志中的所有相关记录必须写入磁盘。

REDO（重做）已提交事务的操作。当发生故障而使系统崩溃后，对那些已提交但其结果尚未真写到磁盘上去的事务操作要重做，使数据库恢复到崩溃时所处理状态。

UNDO（撤销）未提交事务的操作。系统崩溃时，那些未提交事务操作所产生的数据库变更必须恢复到原状，使数据库只反映已提交事务的操作结果。

11.1.1 数据丢失的原因

对于生产数据库来说，数据的安全性是至关重要的，任何数据的丢失和危险都可能给生产带来严重的损失。例如，金融行业数据库系统存储着客户账户的重要信息，绝对不允许出现故障和数据破坏。

为了保证数据的安全，需要定期对数据进行备份。

制定各种故障和灾难的恢复计划，应该预计到各种形式的潜在灾难，并针对具体情况制定恢复计划。例如，数据库系统在运行过程中可能出现运行故障，计算机系统由于出现操作失误或系统故障、自然灾害、计算机病毒或者物理介质故障等。

数据库系统生命周期中可能发生的灾难主要分为3类

1. 系统故障

系统故障一般是指由于硬件故障或软件错误。

系统故障可能导致事务的两种情况：

（1）尚未完成的事务。发生系统故障时，一些尚未完成的事务的结果可能已写入到物理数据库，从而造成数据库可能处于不正确的状态。

（2）已提交的事务。发生系统故障时，有些已经完成的事务，它们更改的数据可能有一部分甚至全部留在内存缓冲区，尚未写回到磁盘上的物理数据库中，系统故障使得这些事务对数据库的修改部分或全部丢失，这也会使数据库处于不一致状态。

重新启动时，具体处理分为：

（1）对未完成事务作UNDO处理；

（2）对已提交事务但更新还留在内存缓冲区的事务进行REDO处理。

2. 事务故障

事务故障是指事务运行过程中，没有正常提交就产生的故障。MySQL还可以通过重启服务来处理该故障。

事务故障又可分为两种。

（1）可以预期的事务故障

即在程序中可以预先估计到的错误，例如存款余额透支，商品库存量达到最低量等，此时继续取款或发货就会出现问题。这种情况可以在事务的代码中加入判断和ROLLBACK语句。当事务执行到ROLLBACK语句时，由系统对事务进行撤消操作，即执行UNDO操作。

（2）非预期的事务故障

即在程序中发生的未估计到的错误。例如数据错误（有的错误数据在输入时是无法检验出来的，例如存入银行的钱数“3500”输入成“5300”）、运算溢出、并发事务发生死锁而被选中撤销该事务（使事务不能再执行下去，但系统未崩溃，该事务可在后面的某时间重启动执行）等。此时由系统直接对该事务执行UNDO处理。

3. 介质故障

由于物理介质发生读写错误，或者管理员在操作过程中不慎删除一些重要数据或日志文件，就会产生介质故障。一般来说，介质故障需要数据库管理员手工进行恢复，恢复时需要在发生故障前的数据库备份和日志备份。

介质故障恢复的方法是：

重新装入转储的后备副本到新的磁盘，使数据库恢复到转储时的一致状态。
在日志中找出转储以后所有已提交的事务。对这些已提交的事务进行REDO处理，将数据库恢复到故障前某一时刻的一致状态。

上述的各类故障都是可以采用各种技术与机制来恢复的。，也存在难以恢复的故障，如地震、火灾、爆炸等造成外存（包括日志、数据库、备份等）的严重毁坏。对这类灾难性故障，一般的恢复技术是难以奏效的，采用分布式或远程调用（日志、备份等）技术可能是较好的方法。

11.1.2 数据备份的分类

1. 按备份时服务器是否在线划分

(1) 热备份

热备份是指数据库在线时服务正常运行的情况下进行数据备份。
温备份。温备份是指进行数据备份时数据库服务正常运行，但数据只能读不能写。

(2) 冷备份

冷备份是指数据库已经正常关闭的情况下进行的数据备份。当正常关闭时会提供一个完整的数据库。

2. 按备份的内容划分

(1) 逻辑备份

逻辑备份是指使用软件技术从数据库中导出数据并写入一个输出文件，该文件格式一般与原数据库的文件格式不同，只是原数据库中数据内容的一个映像。逻辑备份支持跨平台，备份的是SQL语句（DDL和insert语句），以文本形式存储。在恢复的时候执行备份的SQL语句实现数据库数据的重现。

(2) 物理备份

物理备份是指直接复制数据库文件进行的备份，与逻辑备份相比，其速度较快，但占用空间比较大。

3. 按备份涉及的数据范围来划分

(1) 完整备份

完整备份是指备份整个数据库。这是任何备份策略中都要求完成的第1种备份类型，因为其他所有备份类型都依赖于完整备份。换句话说，如果没有执行完整备份，就无法执行差异备份和增量备份。

(2) 增量备份

数据库从上一次完全备份或者最近一次的增量备份以来改变的内容的备份。

(3) 差异备份

差异备份是指将从最近一次完整数据库备份以后发生改变的数据进行备份。差异备份仅捕获自该次完整备份后发生更改的数据。

11.1.3 备份的时机

备份数据库的时机和频率取决于可接受的数据丢失量和数据库活动的频繁程度。

需要决定从每种灾难中进行数据还原的合理时间长度，根据灾难类型和数据库的大小不同，所需的最短数据还原时间也会不同。

用户应当定期的备份用户数据库。可以从下列几方面考虑备份的时机。

（1）创建数据库或为数据库填充了数据以后，用户应该备份数据库。

（2）创建索引后备份数据库。

（3）清理事务日志后备份数据库。当执行了清理事务日志的语句后，应该备份数据库。在清理之后，事务日志将不包含数据库的活动记录，也不能用来还原数据库。

（4）执行了无日志操作后也应该备份数据库。

11.1.4 数据恢复需要注意的问题

数据恢复就是在数据库的一定生命周期的某一时刻还原数据。管理员的非法操作和计算机的故障都会破坏数据库文件。当数据库遭到这些意外时，可以通过备份文件将数据库还原到备份时的状态。这样可以将损失降低到最小。

当计划从各种潜在的灾难中恢复时，需要考虑相关的问题，并为各种可能性做准备。例如，一个包含数据文件的磁盘出现故障，就应该考虑下列问题。

（1）关闭数据库会造成什么后果？

（2）替换损坏的数据磁盘并用数据库备份还原数据的时间可否接受？

（3）为了使数据库不会由于单个磁盘的故障而无法使用？

（4）用数据库备份还原数据的实际时间是多少？

（5）更频繁地备份数据库是否会显著地减少还原时间？

11.1.5 数据恢复的方法

数据恢复就是当数据库出现故障时，将备份的数据库加载到系统，从而使数据库恢复到备份时的正确状态。

MySQL有3种保证数据安全的方法。

（1）数据库备份：通过导出数据或者表文件的拷贝来保护数据。

（2）二进制日志文件：保存更新数据的所有语句。

（3）数据库复制：MySQL内部复制功能。建立在两个或两个以上服务器之间，通过设定它们之间的主从关系来实现的。其中一个作为主服务器，其他的作为从服务器。在此主要介绍前两种方法。

恢复是与备份相对应的系统维护和管理操作。

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

数据备份是数据库管理员的工作。系统意外崩溃或者硬件的损坏都可能导致数据库的丢失，因此MySQL管理员应该定期对数据库进行备份，使得在意外情况发生时，尽可能减少损失。

11.2 数据备份

怎样复制数据库 ?

（1）海量转储。每次复制整个数据库。

（2）增量转储。每次只转储上次转储后被更新过的数据。上次转储以后对数据库的更新修改情况记录在日志文件中，利用日志文件，将更新过的那些数据重新写入上次转储的文件中，就完成了转储操作，这与转储整个数据库的效果是一样的，但花的时间要少得多。

怎样（何时或在什么情况下）进行备份转储 ?

（1）静态转储。是指在系统中无运行事务时进行的转储操作。

（2）动态转储。是指转储其间允许对数据库进行存取或修改的转储操作。

11.2.1 使用 mysqldump 命令备份

MySQL提供了很多免费的客户端程序和实用工具，在MySQL目录下的bin子目录中存储着这些客户端程序。不同的MySQL客户端程序可以连接服务器以访问数据库或执行不同的管理任务。

mysqldump 命令就是MySQL提供的一个非常有用的数据库备份工具。该实用程序存储在 C:\Program Files\MySQL\ MySQLServer 5.7\bin 文件夹中。

mysqldump 命令执行时，可以将数据库备份成一个文本文件，该文件中实际上是包含了多个create和insert语句，使用这些语句可以重新创建表和插入数据。表的结构和表中的数据将存储在生成的文本文件中。

mysqldump 命令的工作原理很简单。即先查出需要备份的表的结构，再在文本文件中生成一个create语句。然后，将表中的所有记录转换成一条insert语句。这些create语句和insert语句都是还原时使用的。

还原数据时就可以使用其中的create语句来创建表。使用其中的insert语句来还原数据。

默认mysqldump导出的.sql文件中不但包含了表数据，还包括导出数据库中所有数据表的结构信息。另外，使用mysqldump导出的SQL文件如果不带绝对路径，默认是保存在bin目录下的。

1. 备份数据库或表

mysqldump备份数据库或表的基本语法格式如下：

mysqldump –u user –h host –ppassword
--databasename[all- databases][tablename=, [tablename=…]]>filename.sql; 

【例11.1】 使用mysqldump命令备份数据库mysqltest中的所有表。

mysqldump -u root -p mysqltest > d:/bak/mysqltestbak.sql 
Enter password:****** 

【例11.2】 使用mysqldump命令备份数据库中的student表和score表。

mysqldump -u root -p teaching student  score 
                        > d:/bak/teaching_ss.sql 
Enter password:******

【例11.3】 使用mysqldump命令备份数据库中的course表。

mysqldump -u root -p teaching course > d:/bak/course.sql 
Enter password:******

2. 备份多个数据库

使用mysqldump备份多个数据库，需要使用--databases参数。基本语法格式如下。

mysqldump –u user –h host –p --databases databasename[databasename…]]>filename.sql; 

【例11.4】 使用mysqldump命令备份数据库teaching和mysqltest。

mysqldump -u root -p --databases teaching mysqltest > d:/bak/teach_test.sql 
Enter password:****** 

3. 查看备份文件

mysqldump 能够生成移植到其他机器的文本文件，甚至可移植到那些有不同硬件结构的机器上，mysqldump产生的输出可在以后用作MySQL的输入来重建数据库。

例如：

mysqldump -uroot –p123456 --databases teaching > d:/bak/teach.txt 

在文本文件teach.txt中输出了表创建、表数据插入，以及存储过程、存储函数、触发器、事件等对象的创建语句。这些语句可作为输入来创建MySQL数据库。如图11-2所示。

11.2.2 直接复制整个数据库目录

因为MySQL表保存为文件方式，所以可以直接复制MySQL数据库的存储目录及文件进行备份。这种方法最简单，速度也最快。使用该方法时，最好先将服务器停止，这样可以保证在复制期间数据不会发生变化。

这种方法虽然简单快速，但不是最好的备份方法。因为，实际情况可能不允许停止MySQL服务器。而且，这种方法对InnoDB存储引擎的表不适用。对于MyISAM存储引擎的表，这样备份和还原很方便。但是还原时最好是相同版本的MySQL数据库，否则可能会存在文件类型不同的情况。

11.2.3 使用mysqlhotcopy工具快速备份

如果备份时不能停止MySQL服务器，可以采用mysqlhotcopy工具。mysqlhotcopy工具的备份方式比mysqldump命令快。

mysqlhotcopy工具是一个Perl脚本，主要在Linux操作系统下使用。mysqlhotcopy工具使用lock tables、flush tables和cp来进行快速备份。

mysqlhotcopy工具的工作原理是：先将需要备份的数据库加上一个读操作锁，然后用flush tables将内存中的数据写回到硬盘上的数据库中，最后把需要备份的数据库文件复制到目标目录。

使用mysqlhotcopy的命令如下：

[root@localhost ~]# mysqlhotcopy [option] dbname1 dbname2 …  backupDir/

11.3 数据恢复

恢复数据库，就是让数据库根据备份的数据回到备份时的状态。当数据丢失或意外破坏时，可以通过数据恢复已经备份的数据，尽量减少数据丢失和破坏造成的损失。

11.3.1 恢复策略

1. 事务故障的恢复

引起事务故障，有如下几个原因：

（1）事务无法执行而中止。

（2）用户主动撤销事务。

（3）因系统调度差错而中止。

事务故障恢复步骤是：

（1）从后向前的扫描日志，找到故障事务。

（2）撤消该事务已做的所有更新操作。例如，如果日志记录中是插入操作，则相当于做删除操作（因此时“更新前的值”为空）；若日志记录中是删除操作，则做插入操作；若是修改操作，则相当于用修改前的值代替修改后的值。

（3）从正在运行的事务列表中删除该事务，释放该事务所占资源。
事务故障的恢复由系统自动完成，无须用户干预。

2. 系统故障恢复

系统故障会使数据库处于不一致的状态，其原因如下：

（1）未提交事务对数据库的更新已写入数据库。

（2）已提交事务对数据库的更新还留在内存缓冲区中，没来得及写入数据库。

因此，对系统故障的恢复策略，是撤销故障发生时未提交的事务，重做已提交的事务。

系统故障的恢复步骤是：

（1）重新启动OS和DBMS。

（2）从前向后扫描日志，找到故障前已提交的事务，将其事务唯一标识号记入重做（REDO）队列。同时，找出故障时未提交的事务，将其事务唯一标识号记入撤销（UNDO）队列。

（3）对撤销队列中的各个事务进行撤销处理，具体方法是，反向扫描日志，对每个要撤销的事务进行回退操作。

（4）对重做队列中的各个事务进行重做，具体方法是，正向扫描日志，对每个事务重新执行日志文件登记的操作。

系统故障的恢复是由系统在重新启动时自动完成的，无须用户干预。

3. 介质故障的恢复

（1）修复或更换磁盘系统，并重新启动系统。

（2）装入最近的数据库后备副本，使数据库恢复到最近一次转储时的一致性数据库状态。

（3）装入有关的日志副本，重做（REDO）已提交的事务。具体方法为，扫描日志，找出故障时已提交事务的唯一标识号，记入重做队列；正向扫描日志，对重做队列中的事务重新执行日志文件中登记的操作。

介质故障的恢复需要DBA的干预，但DBA的任务也只是重装最近转储的数据库后备副本和有关的日志副本，发出系统恢复的命令即可，具体的恢复操作仍由DBMS来完成。

11.3.2 利用MySQL命令恢复数据

管理员通常使用mysqldump命令将数据库中的数据备份成一个文本文件。通常这个文件的后缀名是.sql。需要还原时，可以使用MySQL命令来还原备份的数据。

MySQL命令恢复数据的语法格式如下。

mysql –u user –p [databasename]<filename.sql; 

【例11.5】 使用MySQL命令将备份文件mysqltestbak.sql恢复到数据库中。

mysql -u root -p mysqltest <d:\bak\mysqltestbak.sql
Enter password:****** 

11.3.3 用source恢复表和数据库

MySQL最常用的数据库导入命令就是source，source命令的用法非常简单，首先进入MySQL数据库的命令行管理界面，然后选择需要导入的数据库。

使用source命令能够将备份好的.sql文件导入到MySQL数据库中。

1. 恢复表

【例11.6】删除course表的数据，用source命令恢复。

#尝试删除course表的数据。
mysql> use teaching;
mysql> delete  from course; 

#利用放在“d:/ bak”路径下course的备份文件course.sql，使用source命令把备份好的文件导入进行恢复。
mysql> source d:/bak/course.sql; 

2. 恢复数据库

如果已登录MySQL服务器，还可以使用source命令导入.sql文件。

source语句的语法如下。

source filename.sql 

【例11.7】使用source命令将备份文件mysqltestbak.sql恢复到数据库中。

mysql> use mysqltest; 
mysql> source d:\bak\mysqltestbak.sql 

（1）用source语句导入包含已备份好的.sql文件，可以恢复整个数据库或某张表。

（2）使用source命令必须进入MySQL控制台并进入到待恢复的数据库。

（3）如果数据库已删除，由于没办法进入数据库，可以先建一个同名的空数据库，然后用use命令使用该数据库，再用source命令进行恢复。

（4）可以直接用source命令导入备份文件进行恢复。

（5）在导入数据前，可以先确认编码，如果不设置可能会出现乱码。

mysql> set names gb2312;
mysql> source d:/backup/mysqltestbak.sql; 

11.3.4 直接复制到数据库目录

如果数据库通过复制数据库文件备份，可以直接复制备份的文件到MySQL数据目录下实现还原。通过这种方式还原时，必须保证备份数据的数据库和待还原的数据库服务器的主版本号相同。

这种方式只对MyISAM存储引擎的表有效。

执行还原前要关闭MySQL服务，将备份的文件或文件夹覆盖MySQL的data文件夹，然后再启动MySQL服务。对于Linux/Unix操作系统来说，复制完文件需要将文件的用户和组更改为MySQL运行的用户和组，通常用户是MySQL，组也是MySQL。

11.4 数据库迁移

数据库迁移就是指将数据库从一个系统移动到另一个系统上。数据库迁移的原因是多种多样的。可能是因为升级了计算机，或者是部署开发的管理系统，或者升级了MySQL数据库。甚至是换用其他的数据库。根据上述情况，可以将数据迁移大致分为3类。

• 需要安装新的数据库服务器。

• MySQL版本更新。

• 数据库管理系统的变更（如从Microsoft SQL Server迁移到MySQL）

数据库迁移可以使用一些工具，例如在Windows系统下，可以使用MyODBC实现MySQL和SQL Server之间的迁移。MySQL官方提供的工具MySQL Migration Toolkit也可以在不同数据库之间进行数据迁移。

11.4.1 相同版本的MySQL数据库之间的迁移

相同版本的MySQL数据库之间的迁移就是在主版本号相同的MySQL数据库之间进行数据库移动。这种迁移的方式最容易实现。

相同版本的MySQL数据库之间进行数据库迁移的原因很多。通常的原因是换了新的机器，或者是装了新的操作系统。还有一种常见的原因就是将开发的管理系统部署到工作机器上。

因为迁移前后MySQL数据库的主版本号相同，所以可以通过复制数据库目录来实现数据库迁移。但是，只有数据库表都是MyISAM类型的才能使用这种方式。

11.4.2 不同版本的数据库之间的迁移

不同版本的MySQL数据库之间进行数据迁移通常是MySQL升级的原因。例如，原来很多服务器使用5.0版本的MySQL数据库。5.7的版本推出以后，改进了5.0版本的很多缺陷。因此需要将MySQL数据库升级到5.7版本。

高版本的MySQL数据库通常都会兼容低版本，因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库。对于MySIAM类型的表可以直接复制，也可以使用mysqlhotcopy工具。但是InnoDB类型的表不可以使用这两种方法。

最常用的办法是使用mysqldump命令来进行备份，然后通过MySQL命令将备份文件还原到目标MySQL数据库中。但是，高版本的MySQL数据库很难迁移到低版本的MySQL数据库。

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

11.4.3 不同类型数据库之间迁移

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

MySQL以外的数据库也有类似mysqldump这样的备份工具，可以将数据库中的文件备份成sql文件或普通文本。

不同数据库厂商没有完全按照SQL标准来设计数据库。这就造成了不同数据库使用的SQL语句的差异。例如，Oracle数据库软件使用的是PL/ SQL语言，微软的SQL Server软件使用的是Transact-SQL语言。PL/ SQL语言和Transact-SQL中包含了非标准的SQL语句。这就造成了Oracle、SQL Server和MySQL的SQL语句不能兼容。

11.4.4 将数据库转移到新服务器

将数据库转移到新服务器语法格式如下。

mysqldump -uusername -ppassword databasename |mysql –host=hostname -c databasename 

mysqldump还可以支持下列选项，例如：

（1）-add-locks。在每个表导出之前增加lock tables并且之后unlock table。（为了使得更快地插入到MySQL）。

（2）-add-drop-table。在每个create语句之前增加一个drop table。

（3）-allow-keywords。允许创建是关键词的列名字。

（4）-c, –complete-insert。使用完整的insert语句（用列名字）。

（5）-c, –compress。如果客户和服务器均支持压缩，压缩两者间所有的信息。

（6）-delayed。用insert  delayed命令插入行。

（7）-e, –extended-insert。使用全新多行insert语法。（给出更紧缩并且更快的插入语句）

11.5 表的导入与导出

MySQL数据库中的表可以导出成文本文件、xls、xml或者html格式文件。相应的文本文件也可以导入MySQL数据库中。
MySQL数据库中的数据可以导出到外部存储文件中，在数据库的日常维护中，经常需要进行表的导出和导入的操作。
MySQL数据库中的数据可以导出为sql文本文件、xml文件、txt文件、xls文件或html文件。同样，这些导出文件也可以导入到MySQL数据库中。

11.5.1 用select ...into outfile导出文件

MySQL中，可以使用select…into outfile语句将表的内容导出各种格式的文件， select…into outfile语句基本语法形式如下：

select[columnlist]from table[where condition]into outfile'filename' [options];

【例11.8】备份一个单独的表student。

mysql> select  * into outfile 
    -> 'c:/ProgramData/MySQL/MySQL Server5.7/Uploads/student.txt'
    -> from student;

【例11.9】将表student数据分别备份成.xls和.xml格式。

mysql> select * into outfile
    -> 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/student.xls' 
    -> from student;

mysql> select * into outfile
    -> 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/ student.xml'
    -> from student;

说明：

（1）导出的数据可以按照如.txt、.xls、.doc、.xml等规定格式，通常是.txt文件。导出的是纯数据，不存在建表信息，也可以直接导入到另外一个同数据库的不同表中，当然表结构要相同。

（2）备份一个庞大数据库，输出文件也将很庞大，难于管理，可以把数据表进行单独或者几个表一起备份，将备份文件分成较小、更易于管理的文件。

【例11.10】使用select…into outfile命令将teaching数据库中的score表中的记录导出到文本文件，使用fields选项和lines选项，要求字段之间使用逗号“，”间隔，所有字段值用双引号括起来，定义转义字符为单引号“\‘”，执行命令如下。

mysql> select * from score into outfile
    -> 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/score.txt'
    -> fields
    ->     terminated by ','
    ->     enclosed by '\"'
    ->     escaped by '\''
    -> lines
    ->     terminated by '\r\n';

11.5.2 用MySQL命令导出文本文件

MySQL命令可用来登录MySQL服务器和还原备份文件，也可以导出文本文件。

MySQL命令导出文本文件基本语法形式如下：

mysql -u root -pPassword -e|--execute=“select statement” databasename > C:/name.txt ;
    
-- password表示root用户的密码；使用-e|--execute=选项就可以执行SQL语句；“select语句”用来查询记录；“c:/name.txt”表示导出文件的路径。

【例11.11】使用MySQL命令将teaching数据库中的teacher表中的记录导出到文本文件。

mysql -uroot –p --execute="select * from teacher;" teaching >d:/bak/teach.txt 
Enter password:****** 
或
mysql -u root -p -e "select * from teacher;" teaching >d:/bak/teatxt.txt 
Enter password:****** 

语句执行完毕后，会在d盘的bak文件夹中生成文件teach.txt 或teatxt.txt me。

11.5.3 用load data infile方式导入文本文件

LOAD DATA INFILE '文本文件' INTO TABLE 表名
[FIELDS [TERMINATED BY '字符']
                [[OPTIONALLY] ENCLOSED BY '字符']
               [ESCAPED BY '字符']
]
[LINES  [STARTING BY '字符串']
              [TERMINATED BY '字符串'] 
]
[IGNORE n LINES]

说明:

（1） FIELDS和LINES选项功能与“SELECT …INTO OUTFILE”语句中选项的功能相同。

（2）IGNORE n LINES：忽略文本文件中的前n条记录。 

（3）使用“SELECT …INTO OUTFILE”语句将数据从一个数据库表导出到一个文本文件，再使用“LOAD DATA INFILE”语句从文本文件中将数据导入到数据库表时，两个命令的选项参数必须匹配，否则“LOAD DATA INFILE”语句无法解析文本文件的内容。

【例11.12】恢复student表数据。尝试用delete删除student表的某些数据或全部数据。

mysql> delete  from student;
mysql> load data infile
    -> 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/ student.xls'  
    -> into  table  student;

【例11.14】 使用load data infile命令将‘C:/ProgramData/MySQL/MySQL Server 5.7/ Uploads/score.txt’文件中的数据导入到teaching数据库中的score表中，使用fields选项和lines选项，要求字段之间使用逗号“,”间隔，所有字段值用双引号括起来，定义转义字符为单引号“\‘”，执行命令如下。

mysql> delete from score;
mysql> load data infile 
    -> 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/score.txt' 
    -> into table score
    ->       fields
    ->           terminated by ','
    ->           enclosed by '\"'
    ->           escaped by '\''
    ->       lines
    ->           terminated by '\r\n‘;

11.5.4 使用mysqlimport命令导入表数据

mysqlimport -u root -p 数据库名 文本文件名.txt
[--fields-terminated-by=字符]
[--fields-enclose-by=字符]
[--fields-optionally-enclosed-by=字符]
[--fields-escaped-by=字符]
[--lines-terminated-by=字符串]
[--ignore-lines=n]

【说明】

--ignore-lines=n：忽略文本文件的前n行。

【例11.15】使用mysqlimport命令将【例7-8】sc.txt文件中的数据导入到students_db数据库的sc表。

（1）先将sc表中的数据全部删除。

USE students_db;
SET SQL_SAFE_UPDATES=0;
DELETE FROM sc;

（2）从sc.txt文件恢复数据。

mysqlimport -u root -p students_db "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/score.txt" --lines-terminated-by=\r\n

11.6 使用二进制日志文件恢复数据

二进制日志主要记录数据库的变化情况。二进制日志以一种有效的格式，包含了所有更新了的数据或者已经潜在更新了的数据（例如，没有匹配任何行的DELETE）的语句。

二进制日志还包含关于每个更新数据库语句的执行时间信息。

使用二进制日志的主要目的是最大可能地恢复数据，因为二进制日志包含备份后进行的所有更新。

11.6.1 查看二进制日志开启状态

【例11.16】使用SHOW GLOBAL查看二进制日志设置。

SHOW GLOBAL VARIABLES LIKE '%log_bin%';

11.6.2 查看二进制日志

【例11.17】使用SHOW BINAR LOG查看二进制日志文件个数及文件名。

SHOW BINARY LOGS;

【例7-13】使用mysqlbinlog查看二进制日志。

mysqlbinlog 'LAPTOP-GN5TDSBL-bin.000135'

11.6.3 使用二进制日志恢复数据库

mysqlbinlog [option] "日志文件" | mysql -u root -p

【说明】

option选项：
① --start-datetime：指定恢复数据库的起始时间点。
② --stop-datetime：指定恢复数据库的结束时间点。

【例11.18】使用mysqlbinlog恢复MySQL数据库到2018年11月5日0点时的状态。

11.6.4 二进制日志恢复数据库的综合实例

1. 完全备份数据库。

使用mysqldump命令备份所有数据库。

2. 在MySQL服务器上执行如下语句。

USE students_db;
SET SQL_SAFE_UPDATES=0;
DELETE FROM sc;

3. 增量备份。

完全备份整个服务器的数据库后，使用mysqladmin进行增量备份。

4. 恢复fullbackup.sql文件的完全备份。

5. 恢复PC-20170706QEJD-bin.000004的增量备份。