MySQL的备份与恢复数据库备份和恢复的方法课件.ppt

1、8.1 备份和恢复概述备份和恢复概述8.2 数据库备份和恢复的方法数据库备份和恢复的方法8.3 使用二进制日志文件使用二进制日志文件8.1.1 备份和恢复需求分析备份和恢复需求分析数据库中的数据丢失或被破坏可能是由于以下原因：（1）计算机硬件故障。由于使用不当或产品质量等原因，计算机硬件可能会出现故障，不能使用。如硬盘损坏会使得存储于其上的数据丢失。（2）软件故障。由于软件设计上的失误或用户使用的不当，软件系统可能会误操作数据引起数据破坏。（3）病毒。破坏性病毒会破坏系统软件、硬件和数据。（4）误操作。如用户误使用了诸如DELETE、UPDATE等命令而引起数据丢失或破坏。（5）自然灾害。如火

2、灾、洪水或地震等，它们会造成极大的破坏，会毁坏计算机系统及其数据。（6）盗窃。一些重要数据可能会遭窃。因此，必须制作数据库的复本，即进行数据库备份，在数据库遭到破坏时能够修复数据库，即进行数据库恢复，数据库恢复就是把数据库从错误状态恢复到某一正确状态。备份和恢复数据库也可以用于其他目的，如可以通过备份与恢复将数据库从一个服务器移动或复制到另一个服务器。有多种可能会导致数据表的丢失或者服务器的崩溃，一个简单的DROP TABLE或者DROP DATABASE的语句，就会让数据表化为乌有。更危险的是DELETE*FROM table_name，可以轻易地清空数据表，而这样的错误是很容易发生的。因此

3、，拥有能够恢复的数据对于一个数据库系统来说是非常重要的。MySQL有三种保证数据安全的方法。（1）数据库备份：通过导出数据或者表文件的拷贝来保护数据。（2）二进制日志文件：保存更新数据的所有语句。（3）数据库复制：MySQL内部复制功能建立在两个或两个以上服务器之间，通过设定它们之间的主从关系来实现的。其中一个作为主服务器，其他的作为从服务器。本章主要介绍前两种方法。数据库恢复就是当数据库出现故障时，将备份的数据库加载到系统，从而使数据库恢复到备份时的正确状态。恢复是与备份相对应的系统维护和管理操作，系统进行恢复操作时，先执行一些系统安全性的检查，包括检查所要恢复的数据库是否存在、数据库是否变

4、化及数据库文件是否兼容等，然后根据所采用的数据库备份类型采取相应的恢复措施。8.2.1 使用使用SQL语句备份和恢复表数据语句备份和恢复表数据用户可以使用SELECT INTOOUTFILE语句把表数据导出到一个文本文件中，并用LOAD DATA INFILE语句恢复数据。但是这种方法只能导出或导入数据的内容，不包括表的结构，如果表的结构文件损坏，则必须先恢复原来的表的结构。SELECT INTOOUTFILE格式：格式：SELECT*INTO OUTFILE file_name export_options|DUMPFILE file_name 其中，export_options为：FIEL

5、DS TERMINATED BY string OPTIONALLY ENCLOSED BY char ESCAPED BY char LINES TERMINATED BY string 说明：说明：这个语句的作用是将表中SELECT语句选中的行写入到一个文件中，file_name是文件的名称。文件默认在服务器主机上创建，并且文件名不能是已经存在的（这可能将原文件覆盖）。如果要将该文件写入到一个特定的位置，则要在文件名前加上具体的路径。在文件中，数据行以一定的形式存放，空值用“N”表示。使用OUTFILE时，可以在export_options中加入以下两个自选的子句，它们的作用是决定数据行在

6、文件中存放的格式：FIELDS子句：在FIELDS子句中有三个亚子句：TERMINATED BY、OPTIONALLY ENCLOSED BY和ESCAPED BY。如果指定了FIELDS子句，则这三个亚子句中至少要指定一个。（1）TERMINATED BY用来指定字段值之间的符号，例如，“TERMINATED BY,”指定了逗号作为两个字段值之间的标志。（2）ENCLOSED BY子句用来指定包裹文件中字符值的符号，例如，“ENCLOSED BY ”表示文件中字符值放在双引号之间，若加上关键字OPTIONALLY表示所有的值都放在双引号之间。（3）ESCAPED BY子句用来指定转义字符，例

7、如，“ESCAPED BY*”将“*”指定为转义字符，取代“”，如空格将表示为“*N”。LINES子句：在LINES子句中使用TERMINATED BY指定一行结束的标志，如“LINES TERMINATED BY?”表示一行以“?”作为结束标志。如果FIELDS和LINES子句都不指定，则默认声明以下子句：FIELDS TERMINATED BY t ENCLOSED BY ESCAPED BY LINES TERMINATED BY n如果使用DUMPFILE而不是使用OUTFILE，导出的文件里所有的行都彼此紧挨着放置，值和行之间没有任何标记，成了一个长长的值。LOAD DATA INF

8、ILE语句是SELECT INTOOUTFILE语句的补语，该语句可以将一个文件中的数据导入到数据库中。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 L

9、INES (col_name_or_user_var,.)SET col_name=expr,.)说明：LOW_PRIORITY|CONCURRENT：若指定LOW_PRIORITY，则延迟语句的执行。若指定CONCURRENT，则当LOAD DATA正在执行的时候，其他线程可以同时使用该表的数据。LOCAL：若指定了LOCAL，则文件会被客户主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定确切的位置。如果给定的是一个相对的路径名称，则此名称会被理解为相对于启动客户端时所在的目录。若未指定LOCAL，则文件必须位于服务器主机上，并且被服务器直接读取。与让服务器直接读

10、取文件相比，使用LOCAL速度略慢，这是因为文件的内容必须通过客户端发送到服务器上。file_name：待载入的文件名，文件中保存了待存入数据库的数据行。输入文件可以手动创建，也可以使用其他的程序创建。载入文件时可以指定文件的绝对路径，如“D:/file/myfile.txt”，则服务器根据该路径搜索文件。若不指定路径，如“myfile.txt”，则服务器在默认数据库的数据库目录中读取。若文件为“./myfile.txt”，则服务器直接在数据目录下读取，即MySQL的data目录。出于安全原因，当读取位于服务器中的文本文件时，文件必须位于数据库目录中，或者是全体可读的。注意：这里使用正斜杠指定

11、Windows路径名称，而不是使用反斜杠。tb_name：需要导入数据的表名，该表在数据库中必须存在，表结构必须与导入文件的数据行一致。REPLACE|IGNORE：如果指定了REPLACE，则当文件中出现与原有行相同的唯一关键字值时，输入行会替换原有行。如果指定了IGNORE，则把与原有行有相同的唯一关键字值的输入行跳过。FIELDS子句：此处的FIELDS子句和SELECT.INTO OUTFILE语句中类似。用于判断字段之间和数据行之间的符号。LINES子句：TERMINATED BY亚子句用来指定一行结束的标志。STARTING BY亚子句则指定一个前缀，导入数据行时，忽略行中的该前缀

12、和前缀之前的内容。如果某行不包括该前缀，则整个行被跳过。例如，文件myfile.txt中有以下内容：xxxrow,1something xxxrow,2导入数据时添加以下子句：STARTING BY xxx最后只得到数据(row,1)和(row,2)。IGNORE number LINES：这个选项可以用于忽略文件的前几行。例如，可以使用IGNORE 1 LINES来跳过第一行。col_name_or_user_var：如果需要载入一个表的部分列或文件中字段值顺序与表中列的顺序不同，就必须指定一个列清单，其中可以包含列名或用户变量。如以下语句：LOAD DATA INFILE myfile.t

13、xtINTO TABLE myfile (学号,姓名,性别);SET子句：SET子句可以在导入数据时修改表中列的值。【例8.1】备份XSCJ数据库中的KC表中数据到D盘FILE目录中，要求字段值如果是字符就用双引号标注，字段值之间用逗号隔开，每行以“？”为结束标志。最后将备份后的数据导入到一个和KC表结构一样的空表COURSE表中。首先导出数据：USE XSCJ;SELECT*FROM KC INTO OUTFILE D:/FILE/myfile1.txtFIELDS TERMINATED BY ,OPTIONALLY ENCLOSED BY LINES TERMINATED BY?;导出成功

14、后可以查看D盘FILE文件夹下的myfile1.txt文件，文件内容如图8.1所示。图8.1 备份数据文件内容文件备份完后可以将文件中的数据导入到COURSE表中，使用以下命令：LOAD DATA INFILE D:/FILE/myfile1.txtINTO TABLE COURSEFIELDS TERMINATED BY ,OPTIONALLY ENCLOSED BY LINES TERMINATED BY?;注意：在导入数据时，必须根据文件中数据行的格式指定判断的符号。例如，在myfile1.txt文件中字段值是以逗号隔开的，导入数据时一定要使用“TERMINATED BY,”子句指定逗号

15、为字段值之间的分隔符，与SELECTINTO OUTFILE语句相对应。因为MySQL表保存为文件形式，所以备份很容易。但是在多个用户使用MySQL的情况下，为了得到一个一致的备份，在相关的表上需要做一个读锁定，防止在备份过程中表被更新；当恢复数据时，需要一个写锁定，以避免冲突。在备份或恢复完以后要对表进行解锁。有关锁定与解锁的内容在第10章中介绍。MySQL提供了很多免费的客户端程序和实用工具，不同的MySQL客户端程序可以连接服务器以访问数据库或执行不同的管理任务。这些程序不与服务器进行通信，但可以执行MySQL相关的操作。在MySQL目录下的BIN子目录中存储着这些客户端程序。本节简单介

16、绍一下mysqldump程序和mysqlimport程序。使用客户端的方法如下。打开DOS终端，进入BIN目录，路径为：C:Program FilesMySQLMySQL Server 5.1bin，后面介绍的客户端命令都在此处输入，界面如图8.2所示。1.使用mysqldump备份数据 mysqldump客户端也可用于备份数据，它比SQL语句多做的工作是可以在导出的文件中包含表结构的SQL语句，因此可以备份数据库表的结构，而且可以备份一个数据库，甚至整个数据库系统。此处输 入客户 端命令 图8.2 运行客户端程序（1）备份表命令格式：mysqldump options db_name tab

17、les filename说明：options是mysqldump命令支持的选项，可以通过执行mysqldump-help命令得到mysqldump选项表及帮助信息，这里不详细列出。db_name是数据库名，后面可以跟需要备份的表名。Filename为最后备份的文件名，如果该语句中有多个表，则都保存在这个文件中，文件默认的保存地址是MySQL的bin目录下。如果要保存在特定位置，可以指定其具体路径。注意，文件名在目录中不能已经存在，否则新的备份文件将会将原文件覆盖，造成不必要的麻烦。同其他客户端程序一样，备份数据时需要使用一个用户账号连接到服务器，这需要用户手工提供参数或在选项文件中修改有关值。

18、参数格式为：-hhostname-uusername-ppassword其中，-h后是主机名，-u后是用户名，-p是用户密码，-p选项和密码之间不能有空格。【例8.2】使用mysqldump备份XS表和KC表。具体命令如下：mysqldump-h localhost-u root-p123456 XSCJ XS KC twotables.sql说明：如果是本地服务器，-h选项可以省略。在MySQL的bin目录下可以看到，已经保存了一个.sql格式的文件，文件中存储了创建XS表和KC表的一系列SQL语句。注意：若在命令中没有表名，则备份整个数据库。（2）备份数据库mysqldump程序还可以将一

19、个或多个数据库备份到一个文件中。命令格式：mysqldump options-databases DB1 DB2 DB3.filename【例8.3】备份XSCJ数据库和mysql数据库到D盘FILE文件夹下。命令如下mysqldump-uroot-p123456-databases XSCJ mysqlD:/FILE/data.sql说明：命令执行完后，在FILE文件夹下的data.sql文件被创建了，其中存储了XSCJ数据库和mysql数据库的全部SQL语句。mysql还能备份整个数据库系统，即系统中的所有数据库。【例8.4】备份MySQL服务器上的所有数据库。使用如下命令：mysqldu

20、mp-uroot-p123456-all-databasesall.sql虽然用mysqldump导出表的结构很有用，但是在恢复数据时，如果数据量很大，众多SQL语句将使恢复的效率降低。可以通过使用-tab=选项，分开数据和创建表的SQL语句。-tab=选项会在选项中“=”后面指定的目录里，分别创建存储数据内容的.txt格式文件和包含创建表结构的SQL语句的.sql格式文件。该选项不能与-databases或-all-databases同时使用，并且mysqldump必须运行在服务器主机上。【例8.5】将XSCJ数据库中所有表的表结构和数据都分别备份到D盘FILE文件夹下。命令如下：mysql

21、dump-uroot-p123456-tab=D:/FILE/XSCJ其效果是在目录FILE中生成6个文件，分别是xs.txt、xs.sql、kc.txt、kc.sql、xs_kc.txt和xs_kc.sql。（3）恢复数据库mysqldump程序备份的文件中存储的是SQL语句的集合，用户可以将这些语句还原到服务器中以恢复一个损坏的数据库。【例8.6】假设XSCJ数据库损坏，用备份文件将其恢复。备份XSCJ数据库的命令为：mysqldump-uroot-p123456 XSCJXSCJ.sql恢复命令为：mysql-uroot-p123456 XSCJXSCJ.sql如果表的结构损坏，也可以恢

22、复，但是表中原有的数据将全部被清空。【例8.7】假设XS表结构损坏，备份文件在D盘FILE目录下，现将包含XS表结构的.sql文件恢复到服务器中。命令如下：mysql-uroot-p123456 XSCJ”按钮，在MySQL Administrator中出现如图8.4所示的备份选项。要取消，单击“D:/FILE/lbin-log000001.txt使用日志恢复数据的命令格式如下：mysqlbinlog options log-files|mysql options【例8.9】假设用户在星期一下午1点使用mysqldump工具进行数据库XSCJ的完全备份，备份文件为file.sql。从星期一下午

23、1点开始用户启用日志，bin_log.000001文件保存了从星期一下午1点到星期二下午1点的所有更改，在星期二下午1点运行一条SQL语句：FLUSH LOGS;此时创建了bin_log.000002文件，在星期三下午1点时数据库崩溃。现要将数据库恢复到星期三下午1点时的状态。首先将数据库恢复到星期一下午1点时的状态，在图8.2的DOS窗口输入以下命令：mysqldump-uroot-p123456 XSCJfile.sql使用以下命令将数据库恢复到星期二下午时的状态：mysqlbinlog bin_log.000001|mysql-uroot-p123456再使用以下命令即可将数据库恢复到星

24、期三下午1点时的状态：mysqlbinlog bin_log.000002|mysql-uroot-p123456由于日志文件要占用很大的硬盘资源，所以要及时将没用的日志文件清除掉。以下这条SQL语句用于清除所有的日志文件：RESET MASTER;如果要删除部分日志文件，可以使用PURGE MASTER LOGS语句。语法格式为：PURGE MASTER|BINARY LOGS TO log_name或PURGE MASTER|BINARY LOGS BEFORE date说明：第一个语句用于删除特定的日志文件，log_name为文件名。第二个语句用于删除时间date之前的所有日志文件。MASTER和BINARY是同义词。