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

第8章 备份与恢复,8.1 备份和恢复概述,8.2 数据库备份和恢复的方法,8.3 使用二进制日志文件,第8章 备份与恢复8.1 备份和恢复概述8.2 数据库,8.1 备份和恢复概述,8.1.1 备份和恢复需求分析数据库中的数据丢失或被破坏可能是由于以下原因：（1）计算机硬件故障。由于使用不当或产品质量等原因，计算机硬件可能会出现故障，不能使用。如硬盘损坏会使得存储于其上的数据丢失。（2）软件故障。由于软件设计上的失误或用户使用的不当，软件系统可能会误操作数据引起数据破坏。（3）病毒。破坏性病毒会破坏系统软件、硬件和数据。（4）误操作。如用户误使用了诸如DELETE、UPDATE等命令而引起数据丢失或破坏。（5）自然灾害。如火灾、洪水或地震等，它们会造成极大的破坏，会毁坏计算机系统及其数据。（6）盗窃。一些重要数据可能会遭窃。因此，必须制作数据库的复本，即进行数据库备份，在数据库遭到破坏时能够修复数据库，即进行数据库恢复，数据库恢复就是把数据库从错误状态恢复到某一正确状态。备份和恢复数据库也可以用于其他目的，如可以通过备份与恢复将数据库从一个服务器移动或复制到另一个服务器。,8.1 备份和恢复概述8.1.1 备份和恢复需求分析,8.1.2 数据库备份概念,有多种可能会导致数据表的丢失或者服务器的崩溃，一个简单的DROP TABLE或者DROP DATABASE的语句，就会让数据表化为乌有。更危险的是DELETE * FROM table_name，可以轻易地清空数据表，而这样的错误是很容易发生的。因此，拥有能够恢复的数据对于一个数据库系统来说是非常重要的。MySQL有三种保证数据安全的方法。（1）数据库备份：通过导出数据或者表文件的拷贝来保护数据。（2）二进制日志文件：保存更新数据的所有语句。（3）数据库复制：MySQL内部复制功能建立在两个或两个以上服务器之间，通过设定它们之间的主从关系来实现的。其中一个作为主服务器，其他的作为从服务器。本章主要介绍前两种方法。,8.1.2 数据库备份概念有多种可能会导致数据表的丢失或者,8.1.3 数据库恢复概念,数据库恢复就是当数据库出现故障时，将备份的数据库加载到系统，从而使数据库恢复到备份时的正确状态。恢复是与备份相对应的系统维护和管理操作，系统进行恢复操作时，先执行一些系统安全性的检查，包括检查所要恢复的数据库是否存在、数据库是否变化及数据库文件是否兼容等，然后根据所采用的数据库备份类型采取相应的恢复措施。,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 数据库备份和恢复的方法8.2.1 使用SQL语句,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语句备份和恢复表数据使用OUTFIL,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语句备份和恢复表数据LOAD DAT,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语句备份和恢复表数据 LOCA,8.2.1 使用SQL语句备份和恢复表数据, FIELDS子句：此处的FIELDS子句和SELECT.INTO OUTFILE语句中类似。用于判断字段之间和数据行之间的符号。 LINES子句：TERMINATED BY亚子句用来指定一行结束的标志。STARTING BY亚子句则指定一个前缀，导入数据行时，忽略行中的该前缀和前缀之前的内容。如果某行不包括该前缀，则整个行被跳过。例如，文件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.txtINTO TABLE myfile (学号,姓名,性别);SET子句：SET子句可以在导入数据时修改表中列的值。,8.2.1 使用SQL语句备份和恢复表数据 FIEL,8.2.1 使用SQL语句备份和恢复表数据,【例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 ? ;导出成功后可以查看D盘FILE文件夹下的myfile1.txt文件，文件内容如图8.1所示。,图8.1 备份数据文件内容,8.2.1 使用SQL语句备份和恢复表数据【例8.1】 备,8.2.1 使用SQL语句备份和恢复表数据,文件备份完后可以将文件中的数据导入到COURSE表中，使用以下命令：LOAD DATA INFILE D:/FILE/myfile1.txtINTO TABLE COURSEFIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY ? ;注意：在导入数据时，必须根据文件中数据行的格式指定判断的符号。例如，在myfile1.txt文件中字段值是以逗号隔开的，导入数据时一定要使用“TERMINATED BY ,”子句指定逗号为字段值之间的分隔符，与SELECTINTO OUTFILE语句相对应。因为MySQL表保存为文件形式，所以备份很容易。但是在多个用户使用MySQL的情况下，为了得到一个一致的备份，在相关的表上需要做一个读锁定，防止在备份过程中表被更新；当恢复数据时，需要一个写锁定，以避免冲突。在备份或恢复完以后要对表进行解锁。有关锁定与解锁的内容在第10章中介绍。,8.2.1 使用SQL语句备份和恢复表数据文件备份完后可以,8.2.2 使用客户端程序备份和恢复数据,MySQL提供了很多免费的客户端程序和实用工具，不同的MySQL客户端程序可以连接服务器以访问数据库或执行不同的管理任务。这些程序不与服务器进行通信，但可以执行MySQL相关的操作。在MySQL目录下的BIN子目录中存储着这些客户端程序。本节简单介绍一下mysqldump程序和mysqlimport程序。使用客户端的方法如下。打开DOS终端，进入BIN目录，路径为：C:Program FilesMySQLMySQL Server 5.1bin，后面介绍的客户端命令都在此处输入，界面如图8.2所示。1. 使用mysqldump备份数据 mysqldump客户端也可用于备份数据，它比SQL语句多做的工作是可以在导出的文件中包含表结构的SQL语句，因此可以备份数据库表的结构，而且可以备份一个数据库，甚至整个数据库系统。,图8.2 运行客户端程序,8.2.2 使用客户端程序备份和恢复数据MySQL提供了很,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 使用客户端程序备份和恢复数据（1）备份表,8.2.2 使用客户端程序备份和恢复数据,（2）备份数据库mysqldump程序还可以将一个或多个数据库备份到一个文件中。命令格式：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服务器上的所有数据库。使用如下命令：mysqldump -uroot -p123456 -all-databasesall.sql虽然用mysqldump导出表的结构很有用，但是在恢复数据时，如果数据量很大，众多SQL语句将使恢复的效率降低。可以通过使用-tab=选项，分开数据和创建表的SQL语句。-tab=选项会在选项中“=”后面指定的目录里，分别创建存储数据内容的.txt格式文件和包含创建表结构的SQL语句的.sql格式文件。该选项不能与-databases或-all-databases同时使用，并且mysqldump必须运行在服务器主机上。,8.2.2 使用客户端程序备份和恢复数据（2）备份数据库,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 XSCJXSCJ.sql恢复命令为：mysql -uroot -p123456 XSCJXSCJ.sql如果表的结构损坏，也可以恢复，但是表中原有的数据将全部被清空。【例8.7】 假设XS表结构损坏，备份文件在D盘FILE目录下，现将包含XS表结构的.sql文件恢复到服务器中。命令如下：mysql -uroot -p123456 XSCJD:/FILE/xs.sql如果只恢复表中的数据，就要使用mysqlimport客户端。,8.2.2 使用客户端程序备份和恢复数据【例8.5】 将X,8.2.2 使用客户端程序备份和恢复数据,2. 使用mysqlimport恢复数据 mysqlimport客户端可以用来恢复表中的数据，它提供了LOAD DATA INFILE语句的一个命令行接口，发送一个LOAD DATA INFILE命令到服务器来运作。它大多数选项直接对应LOAD DATA INFILE语句。mysqlimport命令格式为：mysqlimport options db_name filename .说明：options是mysqlimport命令的选项，使用mysqlimport -help即可查看这些选项的内容和作用。常用的选项为：-d，-delete：在导入文本文件前清空表格。-lock-tables：在处理任何文本文件前锁定所有的表。这保证所有的表在服务器上同步。而对于InnoDB类型的表则不必进行锁定。-low-priority，-local，-replace，-ignore：分别对应LOAD DATA INFILE语句的LOW_PRIORITY，LOCAL，REPLACE，IGNORE关键字。对于在命令行上命名的每个文本文件，mysqlimport剥去文件名的扩展名，并使用它决定向哪个表导入文件的内容。例如，“patient.txt”、“patient.sql”和“patient”都会被导入名为patient的表中。所以备份的文件名应根据需要恢复表命名。【例8.8】 恢复XSCJ数据库中表XS的数据，保存数据的文件为XS.txt，命令如下：mysqlimport -uroot -p123456 -low-priority -replace XSCJ XS.txtmysqlimport也需要提供-u、-p选项来连接服务器。值得注意的是，mysqlimport是通过执行LOAD DATA INFILE语句来恢复数据库的，所以上例中备份文件未指定位置的默认是在MySQL的DATA目录中。如果不在则要指定文件的具体路径。,8.2.2 使用客户端程序备份和恢复数据2. 使用mys,8.2.3 使用MySQL界面工具进行备份和恢复,1. 备份数据库（1）登录MySQL Administrator，在MySQL Administrator管理器窗口选中“Backup”菜单选项，在出现的“Backup Project”栏上单击“New Project”按钮，如图8.3所示。,图8.3 在MySQL Administrator中选择备份功能,8.2.3 使用MySQL界面工具进行备份和恢复1. 备,8.2.3 使用MySQL界面工具进行备份和恢复,（2）在Project name栏上填写要创建备份的名称，在“Schemata”选项框中选择需要创建的数据库名称，单击“”按钮，在MySQL Administrator中出现如图8.4所示的备份选项。要取消，单击“”按钮。,图8.4 选择需要备份的数据库,8.2.3 使用MySQL界面工具进行备份和恢复（2）在P,8.2.3 使用MySQL界面工具进行备份和恢复,（3）选择要备份的数据库中的表，单击“Execute Backup Now”按钮，出现保存数据对话框，选择要备份的路径，并单击保存即可。备份完成后可以在指定的目录中找到一个.sql格式的文件，里面保存了与数据库相关的一系列SQL语句。在以上的备份操作中，在图8.4的“Advanced Options”栏中可以对此次备份进行进一步的设定，可以选择锁定表、全部备份等复杂的操作，如图8.5所示。,图8.5 备份操作选项,8.2.3 使用MySQL界面工具进行备份和恢复（3）选择,8.2.3 使用MySQL界面工具进行备份和恢复,2. 恢复数据库（1）登录MySQL Administrator，在MySQL Administrator管理器窗口选中“Restore”菜单栏，在弹出的菜单上单击“Open Backup File”按钮。在弹出的对话框中选择需要恢复的备份，单击打开，如图8.6所示。,图8.6 在MySQL Administrator中选择恢复功能,8.2.3 使用MySQL界面工具进行备份和恢复2. 恢,8.2.3 使用MySQL界面工具进行备份和恢复,（2）在随后的恢复选项中，如图8.7所示，选择要恢复的类型，然后单击“Start Restore”按钮即可。,图8.7 恢复选项,（3）如果提示数据库恢复成功，在Catalogs目录下就能查看数据库恢复的具体情况。,8.2.3 使用MySQL界面工具进行备份和恢复（2）在随,8.2.4 直接复制,根据本章前两节的介绍，由于MySQL的数据库和表是直接通过目录和表文件实现的，因此可以通过直接复制文件的方法来备份数据库。不过，直接复制文件不能够移植到其他机器上，除非要复制的表使用MyISAM 存储格式。如果要把MyISAM类型的表直接复制到另一个服务器使用，首先要求两个服务器必须使用相同的MySQL版本，而且硬件结构必须相同或相似。在复制之前要保证数据表不在被使用，保证复制完整性的最好方法是关闭服务器，复制数据库下的所有表文件（*.frm、*.MYD和*.MYI文件），然后重启服务器。文件复制出来以后，可以将文件放到另外一个服务器的数据库目录下，这样另外一个服务器就可以正常使用这张表了。,8.2.4 直接复制根据本章前两节的介绍，由于MySQL的,8.3 使用二进制日志文件,8.3.1 启用日志 二进制日志可以在启动服务器的时候启用，这需要修改C:Program FilesMySQL文件夹中的my.ini选项文件。打开该文件，找到mysqld所在行，在该行后面加上以下格式的一行：log-bin=filename说明：加入该选项后，服务器启动时就会加载该选项，从而启用二进制日志。如果filename包含扩展名，则扩展名被忽略。MySQL服务器为每个二进制日志名后面添加一个数字扩展名。每次启动服务器或刷新日志时该数字增加1。如果filename未给出，则默认为主机名。假设这里filename取名为bin_log。若不指定目录，则在MySQL的data目录下自动创建二进制日志文件。由于下面使用mysqlbinlog工具处理日志时，日志必须处于bin目录下，所以日志的路径就指定为bin目录，添加的行改为以下一行：log-bin=C:/Program Files/MySQL/MySQL Server 5.1/bin/bin_log,8.3 使用二进制日志文件8.3.1 启用日志,8.3.1 启用日志,保存，重启服务器。重启服务器的方法可以是：先关闭服务器，在如图8.2所示窗口中输入以下命令：net stop mysql再启动服务器：net start mysql此时，MySQL安装目录的bin目录下多出两个文件：bin_log.000001和bin_log.index。bin_log.000001就是二进制日志文件，以二进制形式存储，用于保存数据库更新信息。当这个日志文件大小达到最大，MySQL还会自动创建新的二进制文件。bin_log.index是服务器自动创建的二进制日志索引文件，包含所有使用的二进制日志文件的文件名。,8.3.1 启用日志 保存，重启服务器。重启服务器的方法可,8.3.2 用mysqlbinlog处理日志,使用mysqlbinlog实用工具可以检查二进制日志文件。命令格式为：mysqlbinlog options log-files.说明：log-files是二进制日志的文件名。例如，运行以下命令可以查看bin_log.000001的内容：mysqlbinlog bin_log.000001由于二进制数据可能非常庞大，无法在屏幕上延伸，可以保存到文本文件中：mysqlbinlog bin_log.000001D:/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 XSCJfile.sql,8.3.2 用mysqlbinlog处理日志使用mysql,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是同义词。,8.3.2 用mysqlbinlog处理日志使用以下命令将,