数据基础及应用- 6

第2章MySQL数据库系统基础第1章数据库系统概述第3章MySQL数据库操作第5章使用SQL语句查询数据第4章MySQL数据表第6章视图和索引的使用第8章数据库的安全性管理第7章存储过程和触发器第9章数据库的备份与恢复目录目标导航重点和难点1.备份与恢复数据库;2.数据导入导出。知识目标1.掌握数据库备份与恢复的方法;2.掌握数据导入导出。技能目标1.具备使用Navicat图形化管理工具和SQL语句进行数据备份与恢复的能力;2.具备使用Navicat图形化管理工具和SQL语句进行数据导入导出的能力。数据库备份是服务器维护中的一项重要日常工作。系统意外崩溃或硬件损坏都可能导致数据库丢失,因此管理员应定期对数据库进行备份,以便在意外情况发生时,尽可能减少损失。。9.1数据库备份9.1.1数据库备份概述1.造成数据丢失的因素用户使用数据库是为了管理和操作数据,而数据对用户来说是最宝贵的资产。尽管硬件与软件已尽可能可靠,系统故障或意外仍可能发生。因此,必须在意外发生前做好充分准备,以便事后能快速恢复数据库,并把数据损失降到最低。可能导致数据损失的因素大致可分为以下几类:(1)存储介质故障即外存储介质损坏,如磁盘损坏、磁头碰撞、瞬时强磁场干扰等。这类故障会破坏数据库,并影响正在访问这部分数据的事务。介质故障概率较低,但破坏性强,有时会导致数据库无法恢复。9.1数据库备份9.1.1数据库备份概述1.造成数据丢失的因素(1)存储介质故障(2)系统故障通常称为软故障,指任何导致系统停止运行、必须重新启动的事件,如突然停电、CPU故障、操作系统崩溃、误操作等。(3)用户的错误操作用户无意或恶意执行大量非法操作,如删除重要数据甚至整个数据库,会使数据库陷入难以使用和管理的混乱状态。恢复条理的最佳方法是利用备份将数据库恢复到可靠、稳定、一致的状态。9.1数据库备份9.1.1数据库备份概述1.造成数据丢失的因素(1)存储介质故障(2)系统故障(3)用户的错误操作(4)服务器彻底崩溃再稳定的计算机和软件也存在漏洞。一旦数据库服务彻底瘫痪,用户将面临重建系统的艰巨任务。若事先做过完善而彻底的备份,便可迅速重建系统,将损失降到最低。(5)自然灾难无论硬件性能多么出色,遇到台风、水灾、火灾、地震等自然灾害时,仍可能造成毁灭性损失。(6)计算机病毒属于人为故障,轻则导致部分数据错误,重则破坏整个数据库。9.1数据库备份9.1.1数据库备份概述2.数据备份的分类(1)按备份时服务器是否在线划分①冷备份(脱机备份):在数据库关闭状态下进行备份。②热备份(联机备份):在数据库运行状态下进行备份,依赖数据库的日志文件。③温备份:在数据库锁定表(可读不可写)的状态下进行备份。(2)按备份的内容划分①物理备份:对数据库操作系统的物理文件(数据文件、日志文件等)进行备份,适用于需要快速恢复的大型关键数据库。②逻辑备份:对数据库逻辑组件(如表等数据库对象)进行备份,适用于可以编辑数据值或表结构较小的数据量,或者在不同的机器体系结构上重新创建数据。9.1数据库备份9.1.1数据库备份概述2.数据备份的分类(1)按备份时服务器是否在线划分(2)按备份的内容划分(3)按备份涉及的数据范围划分：可分为完全备份、差异备份和增量备份。①完全备份:每次对数据进行完整的备份,即对整个数据库、数据库结构和文件结构的备份,保存的是备份完成时刻的数据库,是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便,但数据存在大量的重复,并且会占用大量的磁盘空间,备份时间也很长。②差异备份:备份那些从上一次完全备份之后被修改过的所有文件,备份的时间节点是从上次完全备份起,备份数据量会越来越大。恢复数据时,只需恢复上次的完全备份与最近一次的差异备份。③增量备份:只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完全备份或上次增量备份的时间为节点,仅备份这之间的数据变化,因而备份的数据量小,占用空间小,备份速度快。但恢复时,需要从上一次的完全备份开始到最后一次增量备份之间的所有增量依次恢复,如中间某次的备份数据损坏,将导致数据的丢失。9.1数据库备份9.1.2数据库备份的方法数据库备份常见的方法包括:使用Navicat图形化管理工具备份数据库和使用SQL语句的mysqldump命令备份数据库或数据表的数据。1.使用Navicat图形化管理工具备份数据库9.1数据库备份例1

使用Navicat图形化管理工具对gradem数据库进行备份,备份文件名为gradem_2024。(1)在NavicatforMySQL平台下单击工具栏中“备份”按钮。(2)在“对象”区域的工具栏中单击“新建备份”按钮,如图9-1所示。图9-1为数据库gradem新建备份9.1.2数据库备份的方法9.1数据库备份例1

使用Navicat图形化管理工具对gradem数据库进行备份,备份文件名为gradem_2024。(3)在“新建备份”窗口,切换到“常规”选项卡,在注释框中输入“备份gradem数据库”,如图9-2所示。图9-2设置备份数据库注释9.1.2数据库备份的方法9.1数据库备份例1

使用Navicat图形化管理工具对gradem数据库进行备份,备份文件名为gradem_2024。(4)切换到“对象选择”选项卡,选择备份数据库对象,如图9-3所示。图9-3选择备份数据库对象9.1.2数据库备份的方法9.1数据库备份例1

使用Navicat图形化管理工具对gradem数据库进行备份,备份文件名为gradem_2024。(5)切换到“高级”选项卡,选择“锁定所有表”选项,在“使用指定的文件名称”输入框中输入备份文件名“gradem_2024”,如图9-4所示。图9-4指定备份文件名9.1.2数据库备份的方法9.1数据库备份例1

使用Navicat图形化管理工具对gradem数据库进行备份,备份文件名为gradem_2024。(6)单击“保存”下拉按钮,选择“保存”命令,弹出“另存为”对话框,在“配置文件名称:”。下方文本框中输入名“gradem-backup2024”,如图9-5所示,单击“保存”按钮,关闭对话框。单击“备份”按钮,完成数据库备份,并关闭“新建备份”窗口。图9-5指定配置文件名9.1.2数据库备份的方法9.1数据库备份例1

使用Navicat图形化管理工具对gradem数据库进行备份,备份文件名为gradem_2024。(7)备份操作完成后,在NavicatforMySQL平台下,主窗口右侧区域将会显示备份文件列表,如图9-6所示。选中备份文件“gradem_2024”,单击右键,在弹出的快捷菜单中选择“打开所在的文件夹”命令,即可打开备份文件所在的文件夹。图9-6数据库gradem备份文件列表9.1.2数据库备份的方法9.1数据库备份2.使用SQL语句的mysqldump命令备份数据库或数据表的数据在MySQL中使用mysqldump命令备份数据库的基本语法格式如下:mysqldump-uuser-hhost-ppassworddbname[tbname,[…]]>filename.sql;参数说明:user:登录用户名称。host:登录用户的主机名称。password:登录密码。注意:在使用此参数时-p和password之间不能有空格。dbname:需要备份的数据库名称。tbname:dbname数据库中需要备份的数据表,可以指定多个需要备份的表。若缺少该参数,则表示备份整个数据库。>:将表结构与数据写入备份文件。filename.sql:备份文件名,其中包括该文件所在路径。mysqldump是MySQL数据库系统的外部命令,需在命令提示符下执行。数据库恢复是数据库备份的逆过程,在数据丢失或损坏时利用备份把数据库恢复到备份时的状态,最大限度减少损失。9.2.1数据库恢复概述当数据库出现故障时,将备份的数据库加载到系统,从而使数据库恢复到备份时的正确状态。MySQL提供了三种保证数据安全的方法,即数据恢复的手段:1.数据库备份通过导出数据或拷贝表文件的方式来保护数据。2.二进制日志保存更新数据的所有语句。3.数据库复制MySQL内部复制功能建立在两个或两个以上服务器之间,通过设定服务器之间的主从关系来实现,其中一个作为主服务器,其他的作为从服务器。9.2数据库恢复9.2.2数据库恢复的方法常用数据恢复的方法有两

种,一

种是使用Navicat图形化管理工具,另一种是使用mysql命令。1.使用Navicat图形化管理工具恢复数据库例2使用Navicat图形化管理工具利用备份文件gradem_2024对数据库进行恢复。(1)在NavicatforMySQL平台下单击工具栏中的“备份”按钮。(2)在“对象区域”中会显示备份的数据库文件,选择数据库备份文件“gradem_2024”,单击“还原备份”命令,如图9-7所示。(3)在“gradem_2024-还原备份”窗口,单击“还原”按钮,弹出提示信息对话框,如图9-8所示,单击“确定”按钮,进行还原备份,完成时如图9-9所示。9.2数据库恢复9.2.2数据库恢复的方法1.使用Navicat图形化管理工具恢复数据库例2使用Navicat图形化管理工具利用备份文件gradem_2024对数据库进行恢复。9.2数据库恢复图9-7数据库gradem备份文件列表图9-8还原数据库gradem图9-9数据库gradem还原备份完成9.2.2数据库恢复的方法2.使用SQL语句的mysql命令恢复数据库或数据表的数据。在MySQL中使用mysql命令恢复数据库的基本语法格式如下:mysql

-u

user

-p

password[dbname]<filename.sql;参数说明:dbname:数据库名称,该参数为可选参数,如果指定数据库名称表示恢复该数据库中的表。若不指定,则表示恢复特定的一个数据库。如果filename.sql文件为mysqldump命令创建的备份文件,则执行时无须指定数据库名称。mysql是MySQL数据库系统的外部命令,需在命令提示符下执行。9.2数据库恢复有时需要将MySQL数据库中的数据导出到外部存储文件中。MySQL数据库中的数据可以导出为SQL文本文件、XML文件、TXT文件、XLS文件和HTML文件。导出的文件也可以重新导入MySQL数据库中。在MySQL中,实现数据导出和导入的方式包括:使用Navicat图形化管理工具;使用SELECT…INTOOUTFILE语句和LOADDATAINFILE语句;使用mysqldump命令和mysqlimport命令;使用mysql命令。9.3数据导出与导入9.3.1导出数据1.使用Navicat图形化管理工具导出数据例3

将gradem数据库的表student导出到XLS文件中,保存在D:\,文件名为student.xls。9.3数据导出与导入(1)在NavicatforMySQL平台下,打开连接“MySQL90”,打开数据库“gradem”并展开表,右键单击表student,在弹出的快捷菜单中选择“导出向导”命令,如图9-10所示。图9-10表student“导出向导”菜单项9.3.1导出数据1.使用Navicat图形化管理工具导出数据例3

将gradem数据库的表student导出到XLS文件中,保存在D:\,文件名为student.xls。9.3数据导出与导入(2)在“导出向导”窗口下面的“导出格式”中选择“Excel数据表(*.xls)”,如图9-11所示。图9-11表student导出格式选择9.3.1导出数据1.使用Navicat图形化管理工具导出数据例3

将gradem数据库的表student导出到XLS文件中,保存在D:\,文件名为student.xls。9.3数据导出与导入(3)单击“下一步”按钮,在当前窗口,选取需要导出的数据表student,并设置导出路径为“D:\”,如图9-12所示。图9-12表student导出路径设置9.3.1导出数据1.使用Navicat图形化管理工具导出数据例3

将gradem数据库的表student导出到XLS文件中,保存在D:\,文件名为student.xls。9.3数据导出与导入(4)单击“下一步”按钮,在当前窗口,选取表中需要导出列,如图9-13所示。图9-13表student中导出的列9.3.1导出数据1.使用Navicat图形化管理工具导出数据例3

将gradem数据库的表student导出到XLS文件中,保存在D:\,文件名为student.xls。9.3数据导出与导入(5)单击“下一步”按钮,在当前窗口,选取“遇到错误时继续”和“包含列的标题”两个选项,如图9-14所示,单击“下一步”按钮,在当前窗口单击“开始”,完成表student的导出,如图9-15所示,关闭导出向导窗口。图9-14表student导出附加选项图9-15表student导出完成9.3.1导出数据1.使用Navicat图形化管理工具导出数据例4

将表teacher中的数据导出为文本文件teacher.txt。9.3数据导出与导入(1)展开“gradem”数据库,右键单击表teacher,在弹出的快捷菜单中选择“导出向导”命令,如图9-16所示。图9-16表teacher“导出向导”菜单项9.3.1导出数据1.使用Navicat图形化管理工具导出数据例4

将表teacher中的数据导出为文本文件teacher.txt。9.3数据导出与导入(2)执行“导出向导”命令,打开“导出向导”窗口,如图9-17所示。图9-17表teacher导出格式选择9.3.1导出数据1.使用Navicat图形化管理工具导出数据例4

将表teacher中的数据导出为文本文件teacher.txt。9.3数据导出与导入(3)在“导出向导”对话框中选择“文本文件(*.txt)”,然后单击“下一步”按钮。选中“teacher”表,然后在“导出到”位置选择导出路径及导出后文件名,本例题将导出位置设置为“D:\”,导出文件名设置为“teacher.txt”,如图9-18所示。图9-18表teacher导出路径设置9.3.1导出数据1.使用Navicat图形化管理工具导出数据例4

将表teacher中的数据导出为文本文件teacher.txt。9.3数据导出与导入(4)单击“下一步”按钮,选择列,默认是全部选中,也可手动选择,如图9-19所示。(5)单击“下一步”按钮,可以选择导出到文本文件中的记录分隔符、字段分隔符及格式设置。(6)单击“下一步”按钮,之后单击“开始”按钮,完成导出,最后单击“关闭”按钮。图9-19表teacher中导出的列9.3.1导出数据2.使用SELECT…INTOOUTFILE语句导出文本文件MySQL数据库导出数据时,允许使用包含导出定义的SELECT语句进行数据导出操作。该文件被创建在服务器主机上,因此必须拥有文件写入权限(FILE权限)才能使用此方法导出数据。该语句导出的文件不能是已经存在的文件。SELECT…INTOOUTFILE语句的基本语法格式如下:SELECT<filefileds>FROM<tablename>[WHERE<expers>]INTOOUTFILE'[文件路径]文件名'[OPTIONS];9.3数据导出与导入9.3.1导出数据2.使用SELECT…INTOOUTFILE语句导出文本文件SELECT<filefileds>FROM<tablename>[WHERE<expers>]INTOOUTFILE'[文件路径]文件名'[OPTIONS];OPTIONS选项有以下参数:①FIELDSTERMINATEDBY'value':设置字段之间的分隔字符,可以为单个或多个字符,默认情况下为制表符“\t”。②FIELDS[OPTIONALLY]ENCLOSEDBY'value':设置字段的包围字符,只能为单个字符。若使用OPTIONALLY,则只能包括CHAR和VARCHAR字符数据字段。③FIELDSESCAPEDBY'value':设置如何写入或读取特殊字符,只能为单个字符,即设置黑底字符,默认值为反斜杠“\”。④LINESSTARTINGBY'value':设置每行数据开头的字符,可以为单个或多个字符,默认情况下不使用任何字符。⑤LINESTERMINATEDBY'value':设置每行数据结尾的字符,可以为单个或多个字符,默认值为“\n”。FIELDS和LINES两个

子

句

均

为

可

选

项,但

若

同

时

指

定,FIELDS必

须

位

于LINES之前。9.3数据导出与导入9.3.1导出数据3.使用mysqldump命令导出纯文本文件除了

使

用SELECT…INTOOUTFILE语

句

导

出

文

本

文

件

外,还

可

以

使

用mysqldump命令。mysqldump命

令

不

仅

可

以

将

数

据

导

出

为

包

含CREATE、INSERT的SQL文件,还可以导出为纯文本文件。mysqldump导出纯文本文件的语法格式如下:mysqldump-uuser-p-Tpathdbname[tables][OPTIONS];9.3数据导出与导入9.3.1导出数据3.使用mysqldump命令导出纯文本文件mysqldump-uuser-p-Tpathdbname[tables][OPTIONS];参数说明:-T:表示导出为纯文本文件。path:表示导出文件的存储路径。tables:表示指定要导出的表名。如果不指定,将导出数据库dbname中的所有表。OPTIONS:可选参数,需要结合-T选项使用,常用的选项如下:①--fields-terminated-by=value:设置字段分隔符,可以为单个或多个字符,默认情况下为制表符“\t”。②--fields-enclosed-by=value:设置字段的包围字符。③--fields-optionally-enclosed-by=value:设置字段的包围字符,只能为单个字符。若使用OPTIONALLY,则只能包括CHAR和VARCHAR字符数据字段。④--fields-escaped-by=value:设置如何写入或读取特殊字符,只能为单个字符,即设置黑底字符,默认值为反斜杠“\”。⑤--lines-terminated-by=value:设置每行数据结尾的字符,可以为单个或多个字符,默认值为“\n”。注意:与SELECT…INTOOUTFILE语句中的OPTIONS各个参数设置不同,这里的OPTIONS各个选项等号后面的value值不要用引号括起来。9.3数据导出与导入9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例5

将D:\student.xls数据导入到new_student表中。(1)在NavicatforMySQL平台下,打开连接“MySQL90”,打开数据库“gradem”并展开表,右键单击表,在弹出菜单中选择“导入向导”命令,如图9-20所示。图9-20数据导入向导菜单项9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例5

将D:\student.xls数据导入到new_student表中。(2)在“导入向导”窗口下面的“导入类型”中选择“Excel文件(*.xls;*.xlsx)”,如图9-21所示。图9-21导入文件格式选择9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例5

将D:\student.xls数据导入到new_student表中。(3)单

击“下

一

步”按钮,在当前窗口,选

取D盘上需要导

的文件student.xls,如图9-22所示。图9-22选取导入文件9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例5

将D:\student.xls数据导入到new_student表中。(4)单击“下

一

步”按

钮,在

当

前

窗

口,根

据

需

要

设

置

附

加

选

项,此

处

选

择

默

认

设

置,如图9-23所示。图9-23定义附加选项设置9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例5

将D:\student.xls数据导入到new_student表中。(5)单击“下一步”按钮,在当前窗口,输入新的表名“new_student”,如图9-24所示。图9-24设置新表名new_student9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例5

将D:\student.xls数据导入到new_student表中。(6)单击“下一步”按钮,在当前窗口,设置新表的结构,即新表所包含的列,此处选取所有列,如图9-25所示。图9-25新表结构设置9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例5

将D:\student.xls数据导入到new_student表中。(7)单击“下一步”按钮,在当前窗口,设置导入模式,默认选择“追加:添加记录到目标表”,如图9-26所示。图9-26导入模式设置9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例5

将D:\student.xls数据导入到new_student表中。(8)单击“下一步”按钮,在当前窗口,单击“开始”命令,完成数据的导入,如图9-27所示,关闭导入向导。图9-27导入完成9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例5

将D:\student.xls数据导入到new_student表中。(9)在NavicatforMySQL平台下,打开连接“MySQL90”,打开数据库“gradem”并展开表,新表new_student已成功导入数据库gradem中,如图9-28所示。图9-28表new_student导入数据库9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例6将“teacher.txt”文本文件中的数据导入新表“teacher_info”中。①展开“gradem”数据库,右键单击表,在弹出的快捷菜单中选择“导入向导”,打开“导入向导”窗口。②在“导入向导”窗口中选择“文本文件(*.txt)”,然后单击“下一步”按钮,打开选择数据源窗口,选择“D:\teacher.txt”,编码格式选择65001(UTF-8),如图9-29所示。图9-29选择数据源9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例6将“teacher.txt”文本文件中的数据导入新表“teacher_info”中。③单击“下一步”按钮,使用默认选项,在当前窗口再单击“下一步”按钮,继续单击“下一步”按钮,在当前窗口的目标表处输入新表名“teacher_info”,如图9-30所示。图9-30输入新表名9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例6将“teacher.txt”文本文件中的数据导入新表“teacher_info”中。④单击“下一步”按钮,打开如图9-31所示窗口。在该窗口中可以对新表的数据结构进行调整。图9-31对新表数据结构进行调整9.3.2导入数据1.使用Navicat图形化管理工具导入数据9.3数据导出与导入例6将“teacher.txt”文本文件中的数据导入新表“teacher_info”中。⑤单击“下一步”按钮,打开如图9-32所示的窗口。图9-32选择导入模式9.3.2导入数据1.