《MySQL数据库应用与管理（第3版）》 课件 鲁大林 第11章 备份和还原

备份和还原

备份/还原概述

本节内容1.备份内容和备份类型2.还原方法备份/还原概述硬软件故障、自然灾害、人为误操作、人为破坏等均可导致数据的灾难性的丢失和破坏。备份和还原是指在发生数据丢失和破坏后，可以尽可能地挽回或减少数据的损失。备份和还原不是万能的，但是没有备份和还原是万万不能的！简单地说，备份和还原就是复制、保存、还原。至于备份周期（频率），则取决于能承受数据损失的时间周期。能承受1天数据损失，则每日进行备份；能承受1小时的数据损失，则每小时进行备份。不定期很少改动的数据，可以在改动后进行备份。备份/还原概述在对数据库做备份时，备份内容主要包括：数据库对象、程序代码、日志文件、配置文件。数据库对象：数据库中的表、视图、函数、事件等数据库对象。程序代码：基本语句、视图、索引、存储过程、触发器等代码。日志文件：记录数据库运行期间发生变化的日志文件。配置文件：服务器和数据库等配置文件。1.备份内容和备份类型备份/还原概述备份类型按照备份内容、是否能在线完成、备份的还原方式的不同分为三种情况。按照备份内容：完全备份、部分备份、增量备份、差异备份按照是否能在线完成：冷备份、温备份、热备份按照备份的还原方式：逻辑备份、物理备份1.备份内容和备份类型备份/还原概述数据库的还原方法可以根据备份脚本、或者二进制日志文件进行还原操作，后续章节中将会做详细介绍。2.还原方法谢谢备份和还原

备份数据库

本节内容1.使用Navicat对话方式备份数据库2.使用mysqldump命令备份数据库备份数据库以备份学生管理数据库（stuInfo）为例，操作步骤如下：打开Navicat控制台，依次展开“LDL->stuInfo->备份”。1.使用Navicat对话方式备份数据库备份数据库以备份学生管理数据库（stuInfo）为例，操作步骤如下：点击工具栏上的“新建备份”按钮，打开一个新建备份对话框。1.使用Navicat对话方式备份数据库备份数据库以备份学生管理数据库（stuInfo）为例，操作步骤如下：选择所需备份的数据库对象，点击“开始”按钮，则开始进行备份。1.使用Navicat对话方式备份数据库备份数据库以备份学生管理数据库（stuInfo）为例，操作步骤如下：点击“关闭”按钮，则本次新建的备份会自动显示在备份列表中。1.使用Navicat对话方式备份数据库备份数据库以备份学生管理数据库（stuInfo）为例，操作步骤如下：选中以上新建的备份，点击工具栏上的“提取SQL”按钮，则可以把所有备份的内容导出为一个脚本文件，以后也可以直接通过这个脚本文件进行数据库的还原。1.使用Navicat对话方式备份数据库备份数据库mysqldump命令是mysql数据库服务器自带的逻辑备份工具，其备份形式是复制原始的数据库对象产生一组可执行的语句脚本。除了可以生成SQL语句格式的脚本外，还可以生成文本、XML等格式的备份脚本。2.使用mysqldump命令备份数据库备份数据库（1）备份一个数据库使用mysqldump命令备份一个数据库，或者是数据库中的某几张表。其语法格式如下：

mysqldump-uusername-pdb[table1table2…]>backup.sql说明：backup.sql是指备份产生的脚本文件，指定一个包含完整路径的文件名。db表示数据库，table1、table2等表示数据表。如果没有指定数据表，则表示备份整个数据库。备份产生的脚本文件中不包含创建数据库的语句。2.使用mysqldump命令备份数据库操作案例知识点概述示例11-1运行结果如下：以root用户身份，使用mysqldump命令备份数据库stuInfo。说明：备份之前，要保证数据库stuInfo存在。命令成功执行以后，可以在C盘根目录中找到一个名为stuInfo.sql的脚本文件。备份数据库（2）备份多个数据库使用mysqldump命令备份多个数据库的语法格式如下：

mysqldump-uusername-p--databasesdb1[db2…]>backup.sql说明：需要加上“--databases”选项，然后在其后面跟一个或多个数据库的名称。备份产生的脚本文件中包含创了建数据库的语句。2.使用mysqldump命令备份数据库操作案例知识点概述示例11-2运行结果如下：以root用户身份，使用mysqldump命令备份数据库stuInfo和sales。备份数据库（3）备份所有数据库使用mysqldump命令备份所有数据库的语法格式如下：

mysqldump-uusername-p--all-databases>backup.sql说明：只要加上--all-databases”选项，就可以备份所有数据库了。备份产生的脚本文件中包含创了建数据库的语句。2.使用mysqldump命令备份数据库操作案例知识点概述示例11-3运行结果如下：以root用户身份，使用mysqldump命令备份所有数据库。谢谢备份和还原

还原数据库

本节内容1.使用Navicat对话方式还原数据库2.使用mysql命令还原数据库3.使用source语句还原数据库还原数据库以使用11.2.1章节的备份内容进行还原学生管理数据库（stuInfo）为例，操作步骤如下：模拟故障发生，删除学生管理数据库（stuInfo）中的成绩表（score）。1.使用Navicat对话方式还原数据库还原数据库以使用11.2.1章节的备份内容进行还原学生管理数据库（stuInfo）为例，操作步骤如下：打开Navicat控制台，依次展开“LDL->stuInfo->备份”，显示出备份列表。1.使用Navicat对话方式还原数据库还原数据库以使用11.2.1章节的备份内容进行还原学生管理数据库（stuInfo）为例，操作步骤如下：选中相应的备份，点击工具栏上的“还原备份”按钮，打开一个还原备份对话框。1.使用Navicat对话方式还原数据库还原数据库以使用11.2.1章节的备份内容进行还原学生管理数据库（stuInfo）为例，操作步骤如下：可以选择备份内容中的数据库对象进行还原，点击“开始”按钮，则开始进行数据库的还原。1.使用Navicat对话方式还原数据库还原数据库以使用11.2.1章节的备份内容进行还原学生管理数据库（stuInfo）为例，操作步骤如下：可以通过在学生管理数据库（stuInfo）中查看数据表及表中数据的方法，来确认数据库的还原是否成功。1.使用Navicat对话方式还原数据库还原数据库对于备份的脚本文件，需要还原时，可以使用mysql命令来还原备份的数据。使用mysql命令还原数据库的语法格式如下：

mysql-uusername-p[db]<backup.sql说明：backup.sql是指需要还原的脚本文件，指定一个包含完整路径的文件名。db是指还原的数据库，可以省略。如果在脚本中包含有创建数据库的语句，则可以省略；如果不包含创建数据库的语句，则需要指定一个已存在的数据库，作为还原的数据库。2.使用mysql命令还原数据库操作案例知识点概述示例11-4运行结果如下：以root用户身份，使用mysql命令通过stuInfo.sql脚本文件还原数据库。说明：由于在stuInfo.sql脚本文件中不包含创建数据库的语句，所以在还原命令中需要指定一个已存在的数据库。即在执行该命令之前，可以事先创建一个空的数据库stuInfo。操作案例知识点概述示例11-5运行结果如下：以root用户身份，使用mysql命令通过stuInfo_sales.sql脚本文件还原数据库。说明：由于在stuInfo_sales.sql脚本文件中已包含有创建数据库的语句，所以在还原命令中不需要指定数据库。还原数据库也可以使用source语句还原数据库，其语法格式如下：

sourcebackup.sql说明：需要首先登录到mysql数据库终端，才可以使用source语句还原数据库。backup.sql是指需要还原的脚本文件，指定一个包含完整路径的文件名。如果脚本中不包含创建数据库的语句，首先要创建数据库，再用use语句指定为默认数据库，然后才可以使用source语句还原数据库。3.使用source语句还原数据库操作案例知识点概述示例11-6运行结果如下：以root用户登录到MySQL控制台，使用source语句通过stuInfo.sql脚本文件还原数据库。说明：必须登录到mysql数据库终端以后，才可以执行source语句；另外，由于在stuInfo.sql脚本文件中不包含创建数据库的语句，所以事先创建一个空的数据库stuInfo，并使用use语句指定其为默认数据库，然后再使用source语句执行还原数据库的操作。谢谢备份和还原

使用日志文件还原数据库本节内容1.日志简介2.启动和设置二进制日志3.查看或导出二进制日志中的内容4.删除二进制日志5.使用二进制日志还原数据库使用日志文件还原数据库日志是MySQL数据库的重要组成部分，日志文件记录着MySQL数据库运行期间发生的变化。当数据库遭到意外的损害时，可以通过日志文件来查询出错原因，并且可以通过日志文件进行数据还原。1.日志简介使用日志文件还原数据库MySQL日志可以分为4种，分别是二进制日志、错误日志、通用查询日志和慢查询日志。二进制日志：以二进制文件的形式记录了数据库中的操作，但不记录查询语句。错误日志：记录MySQL服务器的启动、关闭和运行错误等信息。通用查询日志：记录用户登录和查询的信息。慢查询日志：记录执行时间超过指定时间的操作。1.日志简介使用日志文件还原数据库除二进制日志外，其他日志都是文本文件。日志文件通常存储在MySQL数据库的数据目录下。默认情况下，只启动了错误日志的功能，其他3类日志都需要数据库管理员进行设置。1.日志简介使用日志文件还原数据库二进制日志（binlog）主要用于记录数据库的变化情况。通过二进制日志可以查看MySQL数据库中进行了哪些改变，还可以根据二进制日志中的记录来修复数据库。默认情况下，二进制日志功能是开启的。2.启动和设置二进制日志使用日志文件还原数据库可以使用SHOWVARIABLES语句查看关于binlog相关的设置，其中有一个log_bin选项，如果为ON，则binlog已经开启；如果为OFF，则binlog没有开启。其相关语句如下：

SHOWVARIABLESLIKE'log_bin';2.启动和设置二进制日志使用日志文件还原数据库可以通过执行“SHOWmasterlogs;”或者“SHOWbinarylogs;”语句查看所有日志文件。2.启动和设置二进制日志使用日志文件还原数据库也可以通过执行“FLUSHlogs;”语句生成一个新的二进制日志文件。2.启动和设置二进制日志使用日志文件还原数据库二进制日志将默认存储在数据库的数据目录下，默认的文件名为”主机名-bin.number“。如果需要更改二进制日志的存储路径和文件名，则在mysql的配置文件my.ini中添加如下语句：

log-bin=DIR/filename其中，DIR参数指定二进制日志的存储路径；Filename参数指定二进制日志的文件名。2.启动和设置二进制日志操作案例知识点概述示例11-7打开my.ini文件，添加如下语句：在my.ini文件添加语句，用来更改二进制日志的存储路径为“C:\MySQL_log”文件夹，文件名为“binlog”。说明：重启MySQL服务器后，可以在“C:\MySQL_log”文件夹下看到binlog.000001文件和binlog.index文件。首先要确认“C:\MySQL_log”文件夹是存在的，否则不能成功启动MySQL服务器。log-bin="C:/MySQL_log/binlog"使用日志文件还原数据库可以使用mysqlbinlog命令查看二进制日志中的内容，也可以导出为外部文件。其语法格式如下：

mysqlbinlog[选项]filename.number[>outerFilename|>>outerFilename]3.查看或导出二进制日志中的内容使用日志文件还原数据库说明：“选项”参数的选择项及介绍如下：省略：查看或导出二进制日志中的所有内容。--start-position=n1--stop-position=n2：查看或导出二进制日志中指定位置间隔的内容。--start-datetime="dt1"--stop-datetime="dt2"：查看或导出二进制日志中指定时间间隔的内容，其范围为[dt1,dt2)。“>”符号表示导入到文件中；“>>”符号表示追加到文件中。3.查看或导出二进制日志中的内容操作案例知识点概述示例11-8运行结果如下：使用mysqlbinlog命令，查看二进制日志binlog.000001。说明：通过以上方式查看二进制日志不是很方便，可以把它导出为一个外部文件来进行查看，这样更方便一点。操作案例知识点概述示例11-9运行结果如下：使用mysqlbinlog命令，把“C:\MySQL_log”文件夹下的二进制日志binlog.000001导出为一个位于同一文件夹下的文本文件backuplog.txt。说明，执行成功以后，可以在“C:\MySQL_log”文件夹中查看到已生成的backuplog.txt文件。使用日志文件还原数据库二进制日志会记录大量的信息，虽然可以用来还原MySQL数据库（后面将会做详细介绍），但是如果长时间不进行清理，将会占用大量的磁盘空间，造成很大的浪费。因此，对二进制日志需要进行适当地删除处理，例如，在备份MySQL数据库之后，可以删除备份之前的二进制日志。4.删除二进制日志使用日志文件还原数据库（1）删除所有二进制日志删除所有二进制日志使用RESETmaster语句。4.删除二进制日志操作案例知识点概述示例11-10运行结果如下：删除所有二进制日志。说明：删除所有二进制日志后，MySQL将会重新创建新的二进制日志，新二进制日志的编号从000001开始，例如binlog.000001。使用日志文件还原数据库（2）根据编号删除二进制日志可以使用PURGEmasterlogsTO语句删除指定二进制日志的编号之前的日志。其语法格式如下：

PURGEmasterlogsTO'filename.number'4.删除二进制日志示例11-11：删除binlog.000004之前的二进制日志。PURGEmasterlogsTO'binlog.000004';使用日志文件还原数据库（3）根据创建时间删除二进制日志可以使用PURGEmasterlogsBEFORE语句删除指定时间之前创建的二进制日志。其语法格式如下：

PURGEmasterlogsBEFORE'yyyy-mm-ddhh:MM:ss'4.删除二进制日志示例11-12：删除“2023-09-0108:00:00”之前创建的二进制日志。PURGEmasterlogsBEFORE'2023-09-0108:00:00';使用日志文件还原数据库如果数据库遭到意外损坏，首先应该使用最近的备份文件来还原数据库。但是，在最近的备份以后，数据库还可能进行了一些更新，这时候就可以使用二进制日志来还原。二进制日志还原数据库也是使用mysqlbinlog命令，其语法格式如下：

mysqlbinlog[选项]filename.number|mysql-uroot-p5.使用二进制日志还原数据库使用日志文件还原数据库说明：“选项”参数的选择项及介绍如下：省略：按照二进制日志中的所有内容进行还原数据库。--start-position=n1--stop-position=n2：按照二进制日志中指定的位置间隔进行还原数据库。start-datetime="dt1"--stop-datetime="dt2"：按照二进制日志中指定的时间间隔进行还原数据库，其范围为[dt1,dt2)。“filename.number”表示使用还原的二进制日志。如果需要从多个二进制日志中进行还原，则必须是编号（number）小的先还原。5.使用二进制日志还原数据库操作案例知识点概述示例11-13（1）备份学生管理数据库（stuInfo）。

mysqldump-uroot-p--databasesstuInfo>C:/MySQL_log/stuInfo.sql（2）以root用户登录到MySQL控制台，删除所有二进制日志。

RESETmaster;（3）分别向学生表（student）和课程表（course）中插入一条记录。 INSERTstuInfo.student(sNo,sName,sex,birthday,deptName,remark) VALUES('1309122509','张恒','男','1995-11-19','网络131',''); SELECTSLEEP(5); INSERTstuInfo.course(cNo,cName,credit,remark)

VALUES('01009','Python程序设计',4,'计算机类专业课程');请按照以下步骤执行操作。操作案例知识点概述示例11-13（4）把二进制日志binlog.000001导出为一个文本文件backuplog.txt。mysqlbinlogC:/MySQL_log/binlog.000001>C:/MySQL_log/backuplog.txt请按照以下步骤执行操作。操作案例知识点概述示例11-13（5）模拟故障发生：删除学生管理数据库（stuInfo）。为了防止把删除数据库的操作写入到当前的二进制日志中，在删除之前首先生成一个新的二进制日志文件。FLUSHlogs; DROPDATABASEstuInfo;（6）使用备份的脚本文件stuInfo.sql还原数据库。（此时还原后的数据库中不不包含在最后一次备份以后所插入的两条记录。）

mysql-uroot-p<C:/MySQL_log/stuInfo.sql（7）使用二进制日志binlog.000001继续还原数据库。

mysqlbinlogC:/MySQL_log/binlog.000001|mysql-uroot-p请按照以下步骤执行操作。使用日志文件还原数据库另外，在使用mysqlbinlog命令执行还原操作时，如果需要从多个二进制日志中进行还原，则必须是编号（number）小的先还原。例如：

mysqlbinlogC:/MySQL_log/binlog.000001|mysql-uroot-p mysqlbinlogC:/MySQL_log/binlog.000002|mysql-uroot-p mysqlbinlogC:/MySQL_log/binlog.000003|mysql-uroot-p5.使用二进制日志还原数据库使用日志文件还原数据库示例11-14：把示例11-13中的第（7）部分更改为“使用二进制日志binlog.000001还原数据库，并按照指定的位置间隔进行还原。”（

backuplog.txt文件中使用矩形标记的部分）。

mysqlbinlog--start-position=303--stop-position=455C:/MySQL_log/binlog.000001|mysql-uroot-p5.使用二进制日志还原数据库使用日志文件还原数据库示例11-15：把示例11-13中的第（7）部分更改为“使用二进制日志binlog.000001还原数据库，并按照指定的时间间隔进行还原。”（backuplog.txt文件中使用椭圆形标记的部分）。

mysqlbinlog--start-datetime="2023-09-3012:27:14"--stop-datetime="2023-09-3012:27:15"C:/MySQL_log/binlog.000001|mysql-uroot-p5.使用二进制日志还原数据库谢谢备份和还原

导出导入表中数据

本节内容1.使用SELECT…INTOOUTFILE导出文本文件2.使用LOADDATAINFILE导入文本文件导出导入表中数据MySQL数据库中的表数据可以导出为文本文件、XML文件或者HTML文件，相应的文件也可以导入到MySQL数据库中。可以使用SELECT…INTOOUTFILE语句将表中的数据导出为一个文本文件。其语法格式如下：

SELECT*|字段列表FROM<表名>[WHERE查询条件] INTOOUTFILE<文本文件名>[OPTION]1.使用SELECT…INTOOUTFILE导出文本文件导出导入表中数据说明：文本文件名”参数指定的是将查询记录所导出到的文件。“OPTION”参数有常用的5个选项，分别为：FIELDSTERMINATEDBY'字符'：设置作为字段分隔符的字符，默认值为“\t”。FIELDSENCLOSEDBY'字符'：设置括上字段值的符号，默认不使用任何符号。FIELDSOPTIONALLYENCLOSEDBY'字符'：设置括上CHAR、VARCHAR、和TEXT等字符型字段值的符号，默认不使用任何符号。FIELDSESCAPEDBY'字符'：设置转义字符，默认值为“\”。LINESSTARTINGBY'字符'：设置每行开头的字符，默认无任何字符。LINESTERMINATEDBY'字符'：设置每行结束的字符，默认值为“\n”。1.使用SELECT…INTOOUTFILE导出文本文件操作案例知识点概述示例11-16运行结果如下：以root用户登录到mySQL控制台，使用SELECT…INTOOUTFILE语句导出stuInfo数据库中学生表（student）的女生记录。其中，字段之间用“,”隔开，字符型数据用双引号括起来，每条记录以“>”开头。说明：通过以上执行结果可以发现，将表中数据导出为一个文本文件没有成功。该错误的原因是MySQL不具备向“C:/MySQL_log”文件夹中存放文件的权限。导出导入表中数据MySQL向本地存放数据是由secure_file_priv参数控制的，通过“SHOWVARIABLESLIKE'%secure%';”语句可以查询到该参数的信息。1.使用SELECT…INTOOUTFILE导出文本文件导出导入表中数