第7章 MySQL数据库备份与恢复

第7章MySQL数据库备份与恢复——常用的备份方法MySQL教程（第2版）01使用SQL语句导出或导入表数据1.导出表数据2.导入表数据使用SQL语句导出或导入表数据1．导出表数据导出表数据的语法格式如下：SELECT*INTOOUTFILE'文件名1'[FIELDS[TERMINATEDBY'string'][[OPTIONALLY]ENCLOSEDBY'char'][ESCAPEDBY'char']][LINESTERMINATEDBY'string']|DUMPFILE'文件名2'使用SQL语句导出或导入表数据使用OUTFILE时，可以加入两个自选的子句，它们的作用是决定数据行在文件中存放的格式。（1）FIELDS子句：需要指定下列3个中的至少一个。①TERMINATEDBY：指定列值之间的分隔符，例如，“TERMINATEDBY','”表示指定逗号作为两个列值之间的分隔符。②ENCLOSEDBY：指定标示文件中字符值的符号，例如，“ENCLOSEDBY'"'”表示文件中字符值放在双引号之间，若加上关键字OPTIONALLY表示所有的值都放在双引号之间。③ESCAPEDBY：指定转义字符，例如，使用“ESCAPEDBY'*'”将“*”指定为转义字符，取代“\”，如空格将表示为“*N”。（2）LINES子句：使用TERMINATEDBY指定一行结束的标志，如“LINESTERMINATEDBY'?'”表示一行以“?”作为结束标志。如果FIELDS子句和LINES子句都没有指定，则默认声明以下子句：FIELDSTERMINATEDBY'\t'ENCLOSEDBY''ESCAPEDBY'\\'LINESTERMINATEDBY'\n'使用SQL语句导出或导入表数据2．导入表数据导入表数据的语法格式如下：LOADDATA[LOW_PRIORITY|CONCURRENT][LOCAL]INFILE'文件名.txt’ [REPLACE|IGNORE] INTOTABLE表名 [FIELDS [TERMINATEDBY'string’] [[OPTIONALLY]ENCLOSEDBY'char’] [ESCAPEDBY'char’] ] [LINES [STARTINGBY'string’] [TERMINATEDBY'string’] ] [IGNOREnumberLINES] [(列名或用户变量,...)] [SET列名=表达式,...]]使用SQL语句：导出或导入表数据说明：LOW_PRIORITY|CONCURRENT：若指定LOW_PRIORITY，则延迟语句的执行。LOCAL：文件会被客户端读取，并被发送到服务器。文件名.txt：该文件中保存了待存入数据库的数据行，它由SELECTINTO…OUTFILE命令导出产生。表名：该表在数据库中必须存在，表结构必须与导入文件的数据行一致。REPLACE|IGNORE：如果指定了REPLACE，则当文件中出现与原有行相同的唯一关键字值时，输入行会替换原有行。FIELDS子句：和SELECT..INTOOUTFILE语句类似。LINES子句：TERMINATEDBY指定一行结束的标志。IGNOREnumberLINES：这个选项可以用于忽略文件的前几行。列名或用户变量：如果需要载入一个表的部分列或文件中字段值顺序与表中列的顺序不同，就必须指定一个列清单。SET子句：SET子句可以在导入数据时修改表中列的值。使用SQL语句：导出或导入表数据【例7.1】备份xscj数据库的kc表中数据到D盘file目录中，要求列值如果是字符就用双引号标示，列值之间用逗号隔开，每行以“？”为结束标志。最后将备份后的数据导入一个和kc表结构一样的空表course中。首先导出数据（操作前先创建D:\file目录）：USExscj;SELECT*fromkc INTOoutfile'd:/file/myfile1.txt’ FIELDSTERMINATEDBY',’ OPTIONALLYENCLOSEDBY'“’ LINESTERMINATEDBY'?';导出成功后可以查看D盘file文件夹下的myfile1.txt文件，文件内容如图。文件备份完后可以将文件中的数据导入到course表中，使用以下命令：LOADDATAINFILE'd:/file/myfile1.txt’ INTOTABLEcourse FIELDSTERMINATEDBY',’ OPTIONALLYENCLOSEDBY'“’ LINESTERMINATEDBY'?';02使用客户端程序备份数据库1.使用mysqldump程序备份数据2.使用mysqlimport程序恢复数据使用客户端程序备份数据库使用客户端程序的方法如下。打开命令行，进入bin目录：cdE:\MySQL57\bin后面介绍的客户端命令都在此处输入，如图。使用客户端程序备份数据库1．使用mysqldump程序备份数据（1）备份表。备份表的命令格式如下：mysqldump[OPTIONS]数据库名[表名…]>备份的文件名同其他客户端程序一样，备份数据时需要使用一个用户账号连接到服务器，这需要用户手动提供参数或在选项文件中修改有关值。参数格式为：-h[主机名]-u[用户名]-p[密码]其中，-p选项和密码之间不能有空格。【例7.2】使用mysqldump程序备份xs表和kc表。mysqldump-hlocalhost-uroot-pnjnu123456xscjxskc>twotables.sql使用客户端程序备份数据库（2）备份数据库。使用mysqldump程序还可以将一个或多个数据库备份到一个文件中。备份数据库的命令格式如下：mysqldump[OPTIONS]--databases[OPTIONS]数据库名...]>filename【例7.3】备份xscj数据库和mytest数据库到D:/file目录下。mysqldump-uroot-pnjnu123456--databasesxscjmytest>D:/file/data.sql【例7.4】备份MySQL服务器上的所有数据库。使用如下命令：mysqldump-uroot–pnjnu123456--all-databases>all.sql【例7.5】将xscj数据库中所有表的表结构和数据都分别备份到D:/file目录下。mysqldump-uroot-pnjnu123456--tab=D:/file/xscj其效果是在D:/file目录下生成xscj数据库中每个表所对应的.sql文件和.txt文件。使用客户端程序备份数据库（3）恢复数据库使用mysqldump程序备份的文件中存储的是SQL语句的集合，用户可以将这些语句还原到服务器中以恢复一个损坏的数据库。【例7.6】假设xscj数据库损坏，用备份文件将其恢复。备份xscj数据库的命令：mysqldump-uroot–pnjnu123456xscj>xscj.sql恢复命令：mysql-uroot–pnjnu123456xscj<xscj.sql【例7.7】假设xs表结构损坏，备份文件在D盘file目录下，现将包含xs表结构的.sql文件恢复到服务器中。mysql-uroot-pnjnu123456xscj<D:/file/xs.sql使用客户端程序备份数据库2．使用mysqlimport程序恢复数据Mysqlimport程序可以用来恢复表中的数据，它提供了LOADDATAINFILE语句的一个命令行接口，通过发送一条LOADDATAINFILE命令到服务器来运作。它的大多数选项直接对应LOADDATAINFILE语句，其命令格式如下：mysqlimport[options]db_namefilename...对于在命令行上命名的每个文本文件，mysqlimport程序剥去文件名的扩展名，并使用文件名决定向哪个表导入文件的内容。例如，“patient.txt”“patient.sql”“patient”都会被导入名为patient的表中。所以备份的文件名应根据需要恢复的表命名。【例7.8】恢复xscj数据库中表xs的数据，保存数据的文件为xs.txt，命令如下：mysqlimport-uroot–pnjnu123456--low-priority--replacexscjxs.txtmysqlimport也需要提供-u、-p选项来连接服务器。03直接复制直接复制由于MySQL的数据库和表是直接通过目录和表文件实现的，因此可以通过直接复制文件的方法来备份数据库。不过，直接复制文件不能够移植到其他机器上，除非要复制的表使用MyISAM存储格式。如果要把MyISAM类型的表直接复制到另一个服务器使用，首先要求两个服务器必须使用相同的MySQL版本，而且硬件结构必须相同或相似。在复制之前要保证数据表不被使用，保证复制完整性的最好方法是关闭服务器，复制数据库下的所有表文件（*.frm、*.MYD和*.MYI文件），然后重启服务器。第7章MySQL数据库备份与恢复——日志文件01启用日志启用日志二进制日志可以在启动服务器的时候启用，这需要修改MySQL安装目录中的my-default.ini选项文件。打开该文件，找到[mysqld]所在行，在该行后面加上以下格式的一行：log-bin[=filename]假设这里filename为bin_log。若不指定目录，则在MySQL的data目录下自动创建二进制格式日志文件。由于使用mysqlbinlog工具处理日志时，日志必须处于bin目录下，因此日志的路径就被指定为bin目录，添加的行改为以下一行：log-bin=E:/MySQL57/bin/bin_log保存，重启服务器。要重启服务器，可以先关闭服务器，在命令窗口中输入以下命令：netstopmysql再启动服务器：netstartmysqlMySQL安装目录的bin目录下多出两个文件：bin_log.000001和bin_log.index。02用mysqlbinlog工具处理日志用mysqlbinlog工具处理日志使用mysqlbinlog实用工具可以检查二进制日志文件，命令格式为：mysqlbinlog[options]日志文件名...例如，运行以下命令可以查看bin_log.000001的内容：mysqlbinlogbin_log.000001由于二进制数据可能非常庞大，无法在屏幕上延伸，可以保存到文本文件中：mysqlbinlogbin_log.000001>D:/file/lbin-log000001.txt使用日志恢复数据的命令格式如下：mysqlbinlog[options]日志文件名...|mysql[options]【例7.9】假设用户在星期一下午1点使用mysqldump工具进行数据库xscj的完全备份，备份文件为file.sql。从星期一下午1点用户开始启用日志，bin_log.000001文件保存了从星期一下午1点到星期二下午1点的所有更改，在星期二下午1点运行一条SQL语句：flushlogs;创建了bin_log.000002文件，在星期三下午1点时数据库崩溃。现要将数据库恢复到星期三下午1点时的状态。首先将数据库恢复到星期一下午1点时的状态，在命令窗口输入以下命令：mysqldump-uroot–p19830925xscj<file.sql用mysqlbinlog工具处理日志使用以下命令将数据库恢复到星期二下午时的状态：mysqlbinlogbin_log.000001|mysql-uroot–p198