mysql开发和 与实践第12章备份和 与恢复.pdf

本章要求:第12章备份与恢复 熟悉每种备份与还原的使用 掌握用命令导出文本文件 掌握数据备份的使用 掌握数据恢复 掌握数据库迁移 掌握导出和导入文本文件主要内容第12章备份与恢复1.数据备份2.数据恢复3.数据库迁移4.表的导出和导入5.综合实例一一将表中的内容导出到文件中12.1数据备份12.1.1 使用mysqldump命令备份12.1.2直接复制整个数据库目录12.1.3使用mysqlhotcopy工具快速备份多二二二12.1.1 使用my sql dump命令备份mysqldump命令可以将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生 成的文本文件中。本小节将为读者介绍mysqldunip命令的工作原理和使用方法。Mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生存一个 C REATE语句。然后，将表中的所有记录转换成一条INSERT语句。这些C REATE语句和INSERT语句都是 还原时使用的。还原数据时就可以使用其中的C REATE语句来创建表。使用其中的INSERT语句来还原 数据。1.备份一个数据库使用mysqldump命令备份一个数据库的基本语法如下：mysqldump-u username-p dbname table!table2 BackupName.sql其中，dbname参数表示数据库的名称；tablel和table2参数表示表的名称，没有该参数时将备 份整个数据库；BackupName.sql参数表示备份文件的名称，文件名前面可以加上一个绝对路径。通 常将数据库备份成一个后缀名为sql的文件。说明：mysqldump命令备份的文件并非一定要求后缀名为.sql,备份成其他格式的文件也是可以的，例 如，后缀名为.txt的文件。但是，通常情况下是备份成后缀名为.sql的文件。因为，后缀名为.sql 的文件给人第一感觉就是与数据库有关的文件。仁【例12T】下面使用root用户备份test数据库下的order表。命令如下：mysqldump u root-p test order D:order.sql在DOS命令窗口中执行上面的命令时，将提示输入连接数据库的密码，输入密码后将完成数据备 份，这时可以在D:找到student,sql文件。order,sql文件中的部分内容如下:student.sql-MR-N.J(T.master(sa(51)1|-MySQL dump 10.13 Distrib 5.5.12z for jH Win32(x86)3一 Host:localhostDatabase:testI 13.-Server version 5.5.12/*140101 SET 0OLD_CHARACTER_SET_CLIENT=-CHARACTER_SET_CLIENT*/;/*140101 SET 0OLD_CHARACTER_SET_RESULTS=0CHARACTER_SET_RESULTS*/；/*!40101 SET 0OLD_COLLATION_CONNECTION=00COLLATION_CONNECTION*/;/*!40101 SET NAMES ut8*/；/*140103 SET 0OLD_TIME_ZONE=00TIME_ZONE*/;/*140103 SET TIME_ZONE=+00:00*/；/*!40014 SET 6OLD_UNIQUE_CHECKS=0图12T备份一个数据库文件开头记录了MySQL的版本、备份的主机名和数据库名。文件中，以“一”开头的都是SQL语 言的注释。以“/*!40101”等形式开头的内容是只有MySQL版本大于或等于指定的版4.1.1才执行的 语句。下面的/*!40103、“/*!40014”也是这个作用。注意：上面student,sql文件中没有创建数据库的语句，因此，student,sql文件中的所有表和记录 必须还原到一个已经存在的数据库中。还原数据时，C REATE TABLE语句会在数据库中创建表，然后 执行INSERT语句向表中插入记录。仁口2.备份多个数据库mysqldump命令备份多个数据库的语法如下：mysqldump-u username-p databases dbname 1 dbname2 BackupName.sql这里要加上“databases”这个选项，然后后面跟多个数据库的名称。【例12-2下面使用root用户备份test数据库和mysql数据库。命令如下：mysqldump-u root-p databases test mysql D:backup.sql在DOS命令窗口中执行上面的命令时，将提示输入连接数据库的密码，输入密码后将完成数据备 份，这时可以在D:下面看到名为backup,sql的文件。这个文件中存储着这两个数据库的所有信息。名称tai不用的牛_1第6章列子k聂喜婷企ikERP莒理统M网站图 backup.sql串 MingRi.log.LDFjP MingRi3j ndf炉 StudenLmdf图12-2备份多个数据库仁二二 U-3.备份所有数据库.mysqldump命令备份所有数据库的语法如下：mysqldump-u username-p-all-databases BackupName.sql.使用“一all-databases选项就可以备份所有数据库了。【例12-3 下面使用root用户备份所有数据库。命令如下：mysqldump-u root-p-all-databases D:alLsql 在DOS命令窗口中执行上面的命令时，将提示输入连接数据库的密码，输入密码后将完成数据备.份，这时可以在D:下面看到名为all.sql的文件。这个文件存储着所有数据库的所有信息。名称网站岛 all.sql上 backup.sql 岸 MingRi.log.LDF/MingRi3.mdf了 Studentundf Jj studentql小 Student logdf图12-3备份所有数据库仁4 4.J:;:4 4 44 4 J二二三12.1.2直接复制整个数据库目录MySQL有一种最简单的备份方法，就是将MySQL中的数据库文件直接复制出来。这种方法最简单,速度也最快。使用这种方法时，最好将服务器先停止。这样，可以保证在复制期间数据库中的数 据不会发生变化。如果在复制数据库的过程中还有数据写入，就会造成数据不一致。,这种方法虽然简单快捷，但不是最好的备份方法。因为，实际情况可能不允许停止MySQL服务器.o而且，这种方法对INNODB存储引擎的表不适用。对于MylSAM存储引擎的表，这样备份和还原很方 便。但是还原时最好是相同版本的MySQL数据库，否则可能会存储文件类型不同的情况。.说明：在MySQL的版本号中，第一个数字表示主版本号。主版本号相同的MySQL数据库的文件类型会相.同。例如，MySQL5.1.39和MySQL5.1.40这两个版本的主版本号都是5.那么这两个数据库的数据文件 拥有相同的文件格式。仁口12.1.3使用mysqlhotcopy工具快速备份如果备份时不能停止MySQL服务器，可以采用mysqlhotcopy工具。mysqlhotcopy工具的备份方 式比mvsaldump命令快:。下面为读者介绍mysalhotcopy工具的工作原理和彳吏用方法。mysqlhotcopy工具是一个Perl脚本，主要在Linux操作系统下使用。mysqlhotcopy工具使用 LOC K TABLES、FLUSH TABLES和cp来进行快速备份。其工作原理是，先将需要备份的数据库加 上一个读操作锁，然后，用FLUSH TABLES将内存中的数据写回到硬盘上的数据库中，最后，把 需要备份的数据库文件复制到目标目录。使用mysqlhotcopy的命令如下：rootlocalhost-#mysqlhotcopyoption dbnamel dbname2.backupDir/其中，dbnamel等表示需要备份的数据库的名称；backupDir参数指出备份到哪个文件夹下。这 个命令的含义就是将dbnamel、dbname2等数据库备份到backDir目录下。mysqlhotcopy工具有 一些常用的选项，这些选项的介绍如下：-help：用来查看mysqlhotcopy的帮助；-allowold：如果备份目录下存在相同的备份文件，将旧的备份文件名加上_old;-keepold：如果备份目录下存在相同的备份文件，不删除旧的备份文件，而是将旧文件更名-flushlog：本次备份之后，将对数据库的更新记录到日志中；口 noindices：只备份数据文件，不备份索引文件；-user二用户名：用来指定用户名，可以用-u代替；一password二密码：用来指定密码，可以用-p代替。使用-p时，密码与-p紧挨着。或者只使用一.P，然后用交换的方式输入密码。这与登录数据库时的情况是一样的；-port二端口号：用来指定访问端口，可以用-P代替；.-socket二socket文件：用来指定socket文件，可以用-S代替。注意;mysqlhotcopy工具不是MySQL自带的，需要安装Perl的数据接口包，Perl的数据库接口包可以在MySQL官方网站下载，网址是http: mysqlhotcopyX 具的工作原理是讲数据库文件拷贝至阳桶目希逊匕mysqlhotcopy工具只能备份MylSAM类型的 表，不能用来备份InnoDB类型的表。12.2数据恢复12.2.1 使用mysql命令还原12.2.2直接复制到数据库目录 12.2.1使用mysql命令还原 通常使用mysqldump命令将数据库的数据备份成一个文本文件。通常这个文件的后缀名是.sql.o需要还原时，可以使用mysql命令来还原备份的数据。Uj.备份文件中通常包含C REATE语句和INSERT语句。mysql命令可以执行备份文件中的C REATE语句和.INSERT语句。通过C REATE语句来创建数据库和表。通过INSERT语句来插入备份的数据。mysql命令的 十.基本语法如下：.mysql-uroot-pdbname backup.sqlK|其中，dbname参数表示数据库名称。该参数时可选参数，可以指定数据库名，也可以不指定。.指定数据库名时，表示还原该数据库下的表。不指定数据库名时，表示还原特定的一个数据库。二 备份文件中有创建数据库的语句。_*【例12-4 下面使用root用户备份所用数据库。命令如下：mysql-u root p”开头。命令如下：SELECT*FROM test.order INTO OUTFILE D:order.txtFIELDS TERMINATED BY 2 OPTIONALLY ENCLOSED BY V LINES STARTING BY TERMINATED BY rn;uTERMINATED BY rn 可以保证每条记录占一行。因为Windows操作系统下“rn”才是 回车换行。如果不加这个选项，默认情况只是“n。用root用户登录至MySQL数据库中，然后执行 上述命令。执行完后，可以在D:下看到一个名为order.txt的文本文件。order,txt中的内容如下：order.txt 一记事本文件(B 编本 格式9)M(V)帮助(H)1、2、3、4x5、6、1200、10 0 0 s120 0,800、950、780、2 3 1 4 5 6图12-4 用selectinto outfile导出文本文件这些记录都是以“”开头，每个字段之间以“、”隔开。而且，字符数据都加上了引号。12.4.2用mysqldump命令导出文本文件.mysqldump命令可以备份数据库中的数据。但是，备份时是在备份文件中保存了C REATE语句和.INSERT语句。不仅如此，mysqldunip命令还可以导出文本文件。其基本的语法形式如下：彳 mysqldump-u root-pPassword-T 目标目录 dbname table option;.其市，Password参数表示root用户的密码，密码紧挨着-p选项；目标目录参数时指导出的文本 文件的路径；dbname参数表示数据库的名称；table参数表示表的名称；option表示附件选项。.这些选项介绍如下：fields-terminated-by二字符串：设置字符串为字段的分隔符，默认值是“t”；.fields-enclosed-by二字符：设置字符来括上字段的值；fields-optionally-enclosed-by=字符：设置字符括上C H AR、VARC H AR和TEXT等字符型字段；fields-escaped-by=字符：设置转义字符；-lines-terminated-by二字符串：设置每行的结束符。注意:；_ 这些选项必须用双引号括起来，否则，MySQL数据库系统将不能识别这几个参数。仁口【例12-6】用mysqldump语句来导出test数据库下order表的记录。其中，字段之间用“、”隔开，字符型数据用双引号括起来。命令如下：mysqldump-u root-p-T D:test order lines-terminated-by=rn-fields-terminated-by=fields-optionally-enclosed-by=其中，root用户的密码%111,密码紧赛着-p选项。-fields-terminated-by等选项都用双引号 括起来。命令执行完后，可以在D：下看到一个名为order,txt的文本文件和order.sql文件。order.txt中的内容如下：_ _ _ _order.txt-记，事本文件(E)编本但 侬 查看保)智助(Wo o O0 5 88 9 7图12-5用mysqldump命令导出文本文件这些记录都是以”隔开。而且，字符数据都是加上了引号。其实，mysqldump命令也是调用 SELEC T-INTO OUTFILE语句来导出文本文件的。除此之外，mysqldump命令同时还生成了 student,sql文件。这个文件中有表的结构和表中的记录。说明：导出数据时，一定要注意数据的格式。通常每个字段之间都必须用分隔符隔开，可以使用逗号(,)、空格或者制表符(Tab键)。每条记录占用一行，新记录要从下一行开始。字符串数据要使 用双引号括起来。mysqldump命令还可以导出xml格式的文件，其基本语法如下：mysqldump-u root-pPassword xml|X dbname table D:name.xml;其由，Password表示root用户的密码；使用-xml或者-X选项就可以导出xml格式的文件；dbname表示数据库的名称；table表示表的名称；D:name.xml表示导出的xml文件的路径。图12-6 mysql命令导出文本文件j j student2.txt盼 企业ERP管理髭统.rar12.4.3用mysql命令导出文本文件名称身 backup.sqlMingRi_log.LDFMingRi3.mdf手 Student.mdf student.sql Studentj og.ldfysql命令可以用来登录MySQL服务器，也可以用来还原备份文件。同时，mysql命令也可以导 出文本文件。其基本巧法形式如下：mysql-u root-pPassword-e SELECT 语句“dbname D:/name.txt;其中，Password表示root用户的密码；使用-e选项就可以执行SQL语句：SELEC T语句”用来 查询记录；D:/name.txt表示导出文件的路径。【例12-7】下面用mysql命令来导出text数据库下student表的记录。命令如下：mysql-u root-pl 11-eSELECT*FROM student test D:/student2.txt仁口在DOS命令窗口中执行上述命令，可以将student表中的所用记录查询出来，然后写入到 student2.txt文档中。student2.txt中的内容如下：student2.txt-记事本.i文件球(E)格式(0)查看(V)帮助(H)id123456燕丽九三四五也 张李张李王章se女女男男女男age198519861990199019911988zhuanye address计算机系北京市海淀区中文系 中文系 英语系 英语系北京市昌平区 湖南省永州市 辽宁省阜新市 福建省厦门市计算机系湖南省衡阳市图12-7文档内容mysql命令还可以导出XML文件和H TML文件。mysql命令导出XML文件的语法如下：mysql-u root pPassword xml|X-e SELECT 语句“dbname D:/filename.xml其中，Password表示root用户的密码；使用-xml或者-X选项就可以导出xml格式的文件；dbname表示数据库的名称；D:/name.xml表示导出的XML文件的路径。例如，下面的命令可以将test数据库中的student表的数据导出到名称为student.xml的XML文件中。mysql-u root-pill xml-e SELECT*from student test D:/student.xmlmysql命令导出H TML文件的语法如下：mysql-u root pPassword html|-H-e SELECT 语句dbname D:/filename.html其中，使用一html或者-H选项就可以导出H TML格式的文件。例如，下面的命令可以将test数据库中的student表的数据导出到名称为student,html的H TML文件中Omysql-u root-pill html-e SELECT*from student test D:/student.html12.5综合实例将表中的内容 导出到文件中本实例将实现将数据表student中的内容导出到文本文件中，在生成文本文件时中，每个字段之 间用逗号隔开。每个字符型的数据用双引号括起来。而且，每条记录占一行。实例执行效果如图12-8所示。一一mysql SELECT*FROM student INTO OUTFILE,pD：/stu.txtFIELDS TERMINATED BY-OPTIONALL?ENCLOSED BY 7“LINES TERMINATED B；Query OK,6 rows affected mysql)图12-8在命令行窗口中的执行效果口 仁执行如图12-8所示的命令后，将在D盘根目录下创建一个名称为stu.txt的文件，效果如图12-9 所示。名称修改日期型大小,stu.txt2013/12/20 15:33义本文档1 KBmysql_data2013/12/20 14:41文件夹eclipse 4.2Jee2013/12/20 08:38文件夹temp2013/12/19 14:56文件夹图12-9将表中的内容导出到文件中在MySQL的命令行窗口中，使用root用户登录到MySQL服务器后，执行SELEC TINTO OUTFILE命 令来导出文本文件。关键代码如下：SELECT*FROM student INTO OUTFILE D:/stu.txtFIELDS TERMINATED BY OPTIONALLY ENCLOSED BY LINES TERMINATED BY W;知拥点提炼(1)mysqldump命令可以将数据库中的数据备份成一个文本文件。(2)MySQL有一种最简单的备份方法，就是将MySQL中的数据库文件直接复制出来。(3)mysqlhotcopy工具是一个Perl脚本，主要在Linux操作系统下使用。(4)使用mysqlhotcopy命令备份的数据也是通过这种方式来还原的。(5)数据库迁移就是指将数据库从一个系统移动到另一个系统上。(6)MySQL数据库中的表可以导出成文本文件、XML文件或者H TML文件。f 3：灯如何备份所有数据库。如何备份多个数据库。如何用mysql命令将数据表内容导出到文本文件。仁口