MySQL数据库原理与应用(微课版)ch14数据备份与恢复

第14章

数据备份与恢复本章概述当数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复，尽可能地降低了意外原因导致的数据丢失。MySQL提供了多种方法对数据进行备份和恢复。本章学习目标了解什么是数据备份；掌握各种数据备份的方法；掌握各种数据恢复的方法；掌握数据库迁移的方法；掌握表导入和导出的方法；熟练掌握综合案例中数据备份与恢复的方法和技巧。目录01020304数据库迁移数据恢复表的导出数据备份05表的导入06数据的备份与恢复示例07本章小结数据备份MySQLdump命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包含多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。语法格式如下：mysqldump–uuser–hhost–ppassworddbname[tbname,[tbname...]]>filename.sqluser表示用户名称；host表示登录用户的主机名称；password为登录密码；dbname为需要备份的数据库名称；tbname为dbname数据库中需要备份的数据表，可以指定多个需要备份的表；右箭头符号“>”告诉MySQLdump将备份数据表的定义和数据写入备份文件；filename.sql为备份文件的名称。例如：打开命令提示符窗口，输入如下备份命令：mysqldump-uroot-P3307-pdb_library>C:/backup/db_library_20240322.sql使用MySQLdump命令备份数据备份因为MySQL表保存为文件方式，所以可以直接复制MySQL数据库的存储目录及文件进行备份。MySQL的数据库目录位置不一定相同，在Windows平台下，MySQL8.0存放数据库的目录通常默认为“C:\DocumentsandSettings\AllUsers\ApplicationData\MySQL\MySQLServer8.0\data”或者其他用户自定义目录；在Linux平台下，数据库目录位置通常为/var/lib/MySQL/直接复制整个数据库目录数据备份MySQLhotcopy在UNIX系统中运行。MySQLhotcopy是一个Perl脚本，最初由TimBunce编写并提供。它使用LOCKTABLES、FLUSHTABLES和cp或scp来快速备份数据库。MySQLhotcopy命令语法格式如下：mysqlhotcopydb_name_1,...db_name_n/path/to/new_directorydb_name_1,…,db_name_n分别为需要备份的数据库的名称；/path/to/new_directory指定备份文件目录。使用MySQLhotcopy工具快速备份目录数据库迁移数据恢复表的导出数据备份表的导入数据的备份与恢复示例本章小结01020304050607数据恢复备份的SQL文件中包含CREATE、INSERT语句（有时也会有DROP语句）。MySQL命令可以直接执行文件中的这些语句。其语法如下：mysql–uuser–p[dbname]<filename.sql使用MySQL命令将C:\backup\tb_test_20240322.sql.sql文件中的备份导入数据库中，输入语句如下：mysql–uroot–p3307–pdb_library<C:/backup/tb_test_20240322.sql使用MySQL命令恢复数据恢复如果数据库通过复制数据库文件备份，就可以直接复制备份的文件到MySQL数据目录下实现恢复。通过这种方式恢复时，必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。而且这种方式只对MyISAM引擎的表有效，对于InnoDB引擎的表不可用。执行恢复以前关闭MySQL服务，将备份的文件或目录覆盖MySQL的data目录，启动MySQL服务。直接复制到数据库目录数据恢复MySQLhotcopy备份后的文件也可以用来恢复数据库，在MySQL服务器停止运行时，将备份的数据库文件复制到MySQL存放数据的位置（MySQL的data文件夹），重新启动MySQL服务即可。例如：从MySQLhotcopy复制的备份恢复数据库，输入语句如下：cp-R/usr/backup/testusr/local/mysql/dataMySQLhotcopy快速恢复目录数据库迁移数据恢复表的导出数据备份表的导入数据的备份与恢复示例本章小结01040506070302数据库迁移相同版本的MySQL数据库之间的迁移就是在主版本号相同的MySQL数据库之间进行数据库移动。迁移过程其实就是源数据库备份和目标数据库恢复过程的组合。例如，将主机上的MySQL数据库全部迁移到主机上。在主机上执行的命令格式如下：mysqldump–h–uroot–Pport–ppassworddbname|mysql–h–uroot–Pport–ppassword相同版本的MySQL数据库之间的迁移数据库迁移从旧版本的MySQL向新版本的MySQL迁移时，对于MyISAM引擎的表，可以直接复制数据库文件，也可以使用MySQLhotcopy工具、MySQLdump工具。对于InnoDB引擎的表，一般只能使用MySQLdump将数据导出，然后使用MySQL命令导入目标服务器上。从新版本向旧版本MySQL迁移数据时要特别小心，最好使用MySQLdump命令导出，然后导入目标数据库中。不同版本的MySQL数据库之间的迁移数据库迁移迁移之前，需要了解不同数据库的架构，比较它们之间的差异。不同数据库中定义相同类型的数据的关键字可能会不同。另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的SQL语句有差别。例如，MySQL几乎完全支持标准SQL语言，而MicrosoftSQLServer使用的是T-SQL语言，T-SQL中有一些非标准的SQL语句，因此在迁移时必须对这些语句进行语句映射处理。数据库迁移可以使用一些工具，例如在Windows系统下，可以使用MyODBC实现MySQL和SQLServer之间的迁移。MySQL官方提供的工具MySQLMigrationToolkit也可以在不同数据库间进行数据迁移。不同数据库之间的迁移目录数据库迁移数据恢复表的导出数据备份表的导入数据的备份与恢复示例本章小结01050607020403表的导出MySQL数据库导出数据时，允许使用包含导出定义的SELECT语句进行数据的导出操作。该文件被创建到服务器主机上，因此必须拥有文件写入权限（FILE权限），才能使用此语法。“SELECT...INTOOUTFILE'filename'”形式的SELECT语句可以把被选择的行写入一个文件中，filename不能是一个已经存在的文件。SELECT...INTOOUTFILE语句基本格式如下：SELECTcolumnlistFROMtableconditionINTOOUTFILE'filename'[OPTION]---OPTIONS选项FIELDSTERMINATEDBY'value'FIELDS[OPTIONALLY]ENCLOSEDBY'value'FIELDSESCAPEDBY'value'LINESSTARTINGBY'value'LINESTERMINATEDBY'value'使用SELECTI…NTOOUTFILE导出文本文件表的导出MySQLdump创建一个包含创建表的CREATETABLE语句的tablename.sql文件和一个包含其数据的tablename.txt文件。基本语法格式如下：mysqldump–Tpath-uroot–Pport–pdbname[tables][OPTIONS]--OPTIONS选项--fields-terminated-by=value--fields-enclosed-by=value--fields-optionally-enclosed-by=value--fields-escaped-by=value--lines-terminated-by=value使用MySQLdump导出文本文件表的导出如果MySQL服务器是单独的机器，用户就是在一个client上进行操作，用户要把数据结果导入client机器上，可以使用MySQL-e语句。使用MySQL导出数据文本文件语句的基本格式如下：mysql-uroot–Pport-p--execute="SELECT语句"dbname>filename.txt使用MySQL导出文本文件目录数据库迁移数据恢复表的导出数据备份表的导入数据的备份与恢复示例本章小结01060702030504表的导出LOADDATAINFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为文字字符串。本节将介绍LOADDATA语句的用法。LOADDATA语句的基本格式如下：LOADDATAINFILE'filename.txt'INTOTABLEtablename[OPTIONS][IGNOREnumberLINES]--OPTIONS选项FIELDSTERMINATEDBY'value'FIELDS[OPTIONALLY]ENCLOSEDBY'value'FIELDSESCAPEDBY'valueLINESSTARTINGBY'value'LINESTERMINATEDBY'value'使用LOADDATAINFILE方式导入文本文件表的导出使用MySQLimport可以导入文本文件，并且不需要登录MySQL客户端。MySQLimport命令的基本语法格式如下：mysqlimport–uroot–Pport–pdbnamefilename.txt[OPTIONS]--OPTIONS选项--fields-terminated-by=value--fields-encloased-by=value--fields-optionally-enclosed-by=value--fields-escaped-by=value--lines-terminated-by=value--ignore-lines=n使用MySQLimport导入文本文件目录数据库迁移数据恢复表的导出数据备份表的导入数据的备份与恢复示例本章小结01070203040605数据的备份与恢复示例示例目的按照操作过程完成对db_library数据库的备份和恢复。（1）使用MySQLdump命令将tb_borrow表备份到文件C:\backup\tb_borrow_bk.sql。（2）使用MySQL命令恢复tb_borrow表到db_library数据库中。（3）使用SELECT…INTOOUTFILE语句导出tb_borrow表中的记录，导出文件位于目录C:\backup下，名称为tb_borrow_out.txt。（4）使用LOADDATAINFILE语句导入tb_borrow_out.txt数据到tb_borrow表。（5）使用MySQLdump命令将tb_borrow表中的记录导出到文件C:\backup\tb_borrow_html.html。目录数据库迁移数据恢复表的导出数据备份表的导入数据的备份与恢复示例本章小结01020304050706本章小结数据备份，本章介绍了3种备份方法，分别是使用MYSQLdump命令、直接复制数据库目录、使用MYSQLhotcopy工具。数据恢复，对应3种备份方法，也有3种恢复数据的方法，使用MYSQLdump命令、直接复制到数据库目录、使用MYSQLhotcopy工具。数据迁移，主要介绍了相同版本的MySQL数据库之间的迁移，不同版本的MySQL数据库之间的迁移，以及不同数据库之间的迁移。表的导出和导入操作，主要介绍了MySQL命令方式、MySQLdump方式、LOADDATAINFILE方式的