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

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,*,第,8,章 备份与恢复,8.1,备份和恢复概述,8.2,数据库备份和恢复的方法,8.3,使用二进制日志文件,8.1,备份和恢复概述,8.1.1,备份和恢复需求分析,数据库中的数据丢失或被破坏可能是由于以下原因：,（,1,）计算机硬件故障。由于使用不当或产品质量等原因，计算机硬件可能会出现故障，不能使用。如硬盘损坏会使得存储于其上的数据丢失。,（,2,）软件故障。由于软件设计上的失误或用户使用的不当，软件系统可能会误操作数据引起数据破坏。,（,3,）病毒。破坏性病毒会破坏系统软件、硬件和数据。,（,4,）误操作。如用户误使用了诸如,DELETE,、,UPDATE,等命令而引起数据丢失或破坏。,（,5,）自然灾害。如火灾、洪水或地震等，它们会造成极大的破坏，会毁坏计算机系统及其数据。,（,6,）盗窃。一些重要数据可能会遭窃。,因此，必须制作数据库的复本，即进行数据库备份，在数据库遭到破坏时能够修复数据库，即进行数据库恢复，数据库恢复就是把数据库从错误状态恢复到某一正确状态。,备份和恢复数据库也可以用于其他目的，如可以通过备份与恢复将数据库从一个服务器移动或复制到另一个服务器。,8.1.2,数据库备份概念,有多种可能会导致数据表的丢失或者服务器的崩溃，一个简单的,DROP TABLE,或者,DROP DATABASE,的语句，就会让数据表化为乌有。更危险的是,DELETE * FROM,table_name,，可以轻易地清空数据表，而这样的错误是很容易发生的。,因此，拥有能够恢复的数据对于一个数据库系统来说是非常重要的。,MySQL,有三种保证数据安全的方法。,（,1,）数据库备份：通过导出数据或者表文件的拷贝来保护数据。,（,2,）二进制日志文件：保存更新数据的所有语句。,（,3,）数据库复制：,MySQL,内部复制功能建立在两个或两个以上服务器之间，通过设定它们之间的主从关系来实现的。其中一个作为主服务器，其他的作为从服务器。,本章主要介绍前两种方法。,8.1.3,数据库恢复概念,数据库恢复就是当数据库出现故障时，将备份的数据库加载到系统，从而使数据库恢复到备份时的正确状态。,恢复是与备份相对应的系统维护和管理操作，系统进行恢复操作时，先执行一些系统安全性的检查，包括检查所要恢复的数据库是否存在、数据库是否变化及数据库文件是否兼容等，然后根据所采用的数据库备份类型采取相应的恢复措施。,8.2,数据库备份和恢复的方法,8.2.1,使用,SQL,语句备份和恢复表数据,用户可以使用,SELECT INTOOUTFILE,语句把表数据导出到一个文本文件中，并用,LOAD DATA INFILE,语句恢复数据。但是这种方法只能导出或导入数据的内容，不包括表的结构，如果表的结构文件损坏，则必须先恢复原来的表的结构。,SELECT INTOOUTFILE,格式：,SELECT * INTO OUTFILE ,file_name,export_options,| DUMPFILE ,file_name,其中，,export_options,为：,FIELDS,TERMINATED BY string,OPTIONALLY ENCLOSED BY char,ESCAPED BY char ,LINES TERMINATED BY string ,说明：,这个语句的作用是将表中,SELECT,语句选中的行写入到一个文件中，,file_name,是文件的名称。文件默认在服务器主机上创建，并且文件名不能是已经存在的（这可能将原文件覆盖）。如果要将该文件写入到一个特定的位置，则要在文件名前加上具体的路径。在文件中，数据行以一定的形式存放，空值用“,N”,表示。,8.2.1,使用,SQL,语句备份和恢复表数据,使用,OUTFILE,时，可以在,export_options,中加入以下两个自选的子句，它们的作用是决定数据行在文件中存放的格式：,FIELDS,子句：在,FIELDS,子句中有三个亚子句：,TERMINATED BY,、,OPTIONALLY ENCLOSED BY,和,ESCAPED BY,。如果指定了,FIELDS,子句，则这三个亚子句中至少要指定一个。,（,1,）,TERMINATED BY,用来指定字段值之间的符号，例如，“,TERMINATED BY ,”,指定了逗号作为两个字段值之间的标志。,（,2,）,ENCLOSED BY,子句用来指定包裹文件中字符值的符号，例如，“,ENCLOSED BY ”,表示文件中字符值放在双引号之间，若加上关键字,OPTIONALLY,表示所有的值都放在双引号之间。,（,3,）,ESCAPED BY,子句用来指定转义字符，例如，“,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,，导出的文件里所有的行都彼此紧挨着放置，值和行之间没有任何标记，成了一个长长的值。,8.2.1,使用,SQL,语句备份和恢复表数据,LOAD DATA INFILE,语句是,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 LINES,(,col_name_or_user_var,.),SET,col_name,=,expr,.),说明：,LOW_PRIORITY | CONCURRENT,：若指定,LOW_PRIORITY,，则延迟语句的执行。若指定,CONCURRENT,，则当,LOAD DATA,正在执行的时候，其他线程可以同时使用该表的数据。,8.2.1,使用,SQL,语句备份和恢复表数据,LOCAL,：若指定了,LOCAL,，则文件会被客户主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定确切的位置。如果给定的是一个相对的路径名称，则此名称会被理解为相对于启动客户端时所在的目录。若未指定,LOCAL,，则文件必须位于服务器主机上，并且被服务器直接读取。与让服务器直接读取文件相比，使用,LOCAL,速度略慢，这是因为文件的内容必须通过客户端发送到服务器上。,file_name,：待载入的文件名，文件中保存了待存入数据库的数据行。输入文件可以手动创建，也可以使用其他的程序创建。载入文件时可以指定文件的绝对路径，如“,D:/file/,myfile.txt,”,，则服务器根据该路径搜索文件。若不指定路径，如“,myfile.txt,”,，则服务器在默认数据库的数据库目录中读取。若文件为“,./,myfile.txt,”,，则服务器直接在数据目录下读取，即,MySQL,的,data,目录。出于安全原因，当读取位于服务器中的文本文件时，文件必须位于数据库目录中，或者是全体可读的。,注意：这里使用正斜杠指定,Windows,路径名称，而不是使用反斜杠。,tb_name,：需要导入数据的表名，该表在数据库中必须存在，表结构必须与导入文件的数据行一致。,REPLACE | IGNORE,：如果指定了,REPLACE,，则当文件中出现与原有行相同的唯一关键字值时，输入行会替换原有行。如果指定了,IGNORE,，则把与原有行有相同的唯一关键字值的输入行跳过。,8.2.1,使用,SQL,语句备份和恢复表数据,FIELDS,子句：此处的,FIELDS,子句和,SELECT.INTO OUTFILE,语句中类似。用于判断字段之间和数据行之间的符号。,LINES,子句：,TERMINATED BY,亚子句用来指定一行结束的标志。,STARTING BY,亚子句则指定一个前缀，导入数据行时，忽略行中的该前缀和前缀之前的内容。如果某行不包括该前缀，则整个行被跳过。例如，文件,myfile.txt,中有以下内容：,xxxrow,1,something xxxrow,2,导入数据时添加以下子句：,STARTING BY xxx,最后只得到数据,(row,1),和,(row,2),。,IGNORE number LINES,：这个选项可以用于忽略文件的前几行。例如，可以使用,IGNORE 1 LINES,来跳过第一行。,col_name_or_user_var,：如果需要载入一个表的部分列或文件中字段值顺序与表中列的顺序不同，就必须指定一个列清单，其中可以包含列名或用户变量。如以下语句：,LOAD DATA INFILE ,myfile.txt,INTO TABLE,myfile,(,学号,姓名,性别,);,SET,子句：,SET,子句可以在导入数据时修改表中列的值。,8.2.1,使用,SQL,语句备份和恢复表数据,【,例,8.1,】,备份,XSCJ,数据库中的,KC,表中数据到,D,盘,FILE,目录中，要求字段值如果是字符就用双引号标注，字段值之间用逗号隔开，每行以“？”为结束标志。最后将备份后的数据导入到一个和,KC,表结构一样的空表,COURSE,表中。,首先导出数据：,USE XSCJ;,SELECT * FROM KC,INTO OUTFILE D:/FILE/myfile1.txt,FIELDS TERMINATED BY , ,OPTIONALLY ENCLOSED BY ,LINES TERMINATED BY ? ;,导出成功后可以查看,D,盘,FILE,文件夹下的,myfile1.txt,文件，文件内容如图,8.1,所示。,图,8.1,备份数据文件内容,8.2.1,使用,SQL,语句备份和恢复表数据,文件备份完后可以将文件中的数据导入到,COURSE,表中，使用以下命令：,LOAD DATA INFILE D:/FILE/myfile1.txt,INTO TABLE COURSE,FIELDS TERMINATED BY , ,OPTIONALLY ENCLOSED BY ,LINES TERMINATED BY ? ;,注意：在导入数据时，必须根据文件中数据行的格式指定判断的符号。例如，在,myfile1.txt,文件中字段值是以逗号隔开的，导入数据时一定要使用“,TERMINATED BY ,”,子句指定逗号为字段值之间的分隔符，与,SELECTINTO OUTFILE,语句相对应。,因为,MySQL,表保存为文件形式，所以备份很容易。但是在多个用户使用,MySQL,的情况下，为了得到一个一致的备份，在相关的表上需要做一个读锁定，防止在备份过程中表被更新；当恢复数据时，需要一个写锁定，以避免冲突。在备份或恢复完以后要对表进行解锁。有关锁定与解锁的内容在第,10,章中介绍。,8.2.2,使用客户端程序备份和恢复数据,MySQL,提供了很多免费的客户端程序和实用工具，不同的,MySQL,客户端程序可以连接服务器以访问数据库或执行不同的管理任务。这些程序不与服务器进行通信，但可以执行,MySQL,相关的操作。在,MySQL,目录下的,BIN,子目录中存储着这些客户端程序。本节简单介绍一下,mysqldump,程序和,mysqlimport,程序。,使用客户端的方法如下。,打开,DOS,终端，进入,BIN,目录，路径为：,C:Program Files,MySQLMySQL,Server 5.1bin,，后面介绍的客户端命令都在此处输入，界面如图,8.2,所示。,1.,使用,mysqldump,备份数据,mysqldump,客户端也可用于备份数据，它比,SQL,语句多做的工作是可以在导出的文件中包含表结构的,SQL,语句，因此可以备份数据库表的结构，而且可以备份一个数据库，甚至整个数据库系统。,图,8.2,运行客户端程序,8.2.2,使用客户端程序备份和恢复数据,（,1,）备份表,命令格式：,mysqldump,options,db_name,tables filename,说明：,options,是,mysqldump,命令支持的选项，可以通过执行,mysqldump,-help,命令得到,mysqldump,选项表及帮助信息，这里不详细列出。,db_name,是数据库名，后面可以跟需要备份的表名。,Filename,为最后备份的文件名，如果该语句中有多个表，则都保存在这个文件中，文件默认的保存地址是,MySQL,的,bin,目录下。如果要保存在特定位置，可以指定其具体路径。注意，文件名在目录中不能已经存在，否则新的备份文件将会将原文件覆盖，造成不必要的麻烦。,同其他客户端程序一样，备份数据时需要使用一个用户账号连接到服务器，这需要用户手工提供参数或在选项文件中修改有关值。参数格式为：,-,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,语句。,注意：若在命令中没有表名，则备份整个数据库。,8.2.2,使用客户端程序备份和恢复数据,（,2,）备份数据库,mysqldump,程序还可以将一个或多个数据库备份到一个文件中。,命令格式：,mysqldump,options -databases DB1 DB2 DB3. filename,【,例,8.3,】,备份,XSCJ,数据库和,mysql,数据库到,D,盘,FILE,文件夹下。命令如下,mysqldump,-,uroot,-p123456 -databases XSCJ,mysql,D:/FILE/,data.sql,说明：命令执行完后，在,FILE,文件夹下的,data.sql,文件被创建了，其中存储了,XSCJ,数据库和,mysql,数据库的全部,SQL,语句。,mysql,还能备份整个数据库系统，即系统中的所有数据库。,【,例,8.4,】,备份,MySQL,服务器上的所有数据库。使用如下命令：,mysqldump,-,uroot,-p123456 -all-databases,all.sql,虽然用,mysqldump,导出表的结构很有用，但是在恢复数据时，如果数据量很大，众多,SQL,语句将使恢复的效率降低。可以通过使用,-tab=,选项，分开数据和创建表的,SQL,语句。,-tab=,选项会在选项中“,=”,后面指定的目录里，分别创建存储数据内容的,.txt,格式文件和包含创建表结构的,SQL,语句的,.,sql,格式文件。该选项不能与,-databases,或,-all-databases,同时使用，并且,mysqldump,必须运行在服务器主机上。,8.2.2,使用客户端程序备份和恢复数据,【,例,8.5,】,将,XSCJ,数据库中所有表的表结构和数据都分别备份到,D,盘,FILE,文件夹下。命令如下：,mysqldump,-,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 XSCJ,XSCJ.sql,恢复命令为：,mysql,-,uroot,-p123456 XSCJ,XSCJ.sql,如果表的结构损坏，也可以恢复，但是表中原有的数据将全部被清空。,【,例,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,。从星期一下午,1,点开始用户启用日志，,bin_log.000001,文件保存了从星期一下午,1,点到星期二下午,1,点的所有更改，在星期二下午,1,点运行一条,SQL,语句：,FLUSH LOGS;,此时创建了,bin_log.000002,文件，在星期三下午,1,点时数据库崩溃。现要将数据库恢复到星期三下午,1,点时的状态。首先将数据库恢复到星期一下午,1,点时的状态，在图,8.2,的,DOS,窗口输入以下命令：,mysqldump,-,uroot,-p123456 XSCJ,file.sql,8.3.2,用,mysqlbinlog,处理日志,使用以下命令将数据库恢复到星期二下午时的状态：,mysqlbinlog,bin_log.000001 |,mysql,-,uroot,-p123456,再使用以下命令即可将数据库恢复到星期三下午,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,是同义词。,