《数据库原理及应用教程-MySQL8.0》课件 第12章 数据库的备份与恢复

数据库原理与应用教程-MySQL8.0第12章

数据库的备份与恢复第12章

数据库的备份与恢复避免数据丢失是数据库管理员需要面对的最关键的问题之一。尽管在MySQL中采取了许多措施来保证数据库的安全性和完整性，但故障仍不可避免，仍会影响甚至破坏数据库，造成数据丢失。同时还存在其他一些可能造成数据丢失的因素，例如用户的操作失误、蓄意破坏、病毒攻击和自然界不可抗力等。MySQL制定了一个良好的备份和恢复策略，定期将数据库进行备份以保护数据库，以便在事故发生后恢复数据库。12.1数据库备份概述对于计算机用户来说，对一些重要文件、资料定期进行备份是一种良好的习惯。如果出现突发情况，比如系统崩溃、系统遭受病毒攻击等，使得原先的文件遭到破坏以至于全部丢失，启用文件备份，就可以节省大量的时间和精力。数据库备份就是创建整个数据库(或者其中一部分)的副本，其中记录着在进行备份这一操作时数据库中所有数据的状态，以便在数据库遭到破坏时能够恢复数据库。12.1.1数据库备份的类型为了在数据库遭到破坏时能及时地被恢复，对于每一个数据库都要制定备份计划，定期进行数据库的备份。数据库的备份是一个很长的过程，如何进行备份要考虑两个方面：怎样备份（备份策略）和什么情况下备份（备份时间）。12.1.1数据库备份的类型1.按备份策略分类分为完全备份和增量备份。（1）完全备份完全备份将备份整个数据库，它代表备份完成时的数据库。完全备份易于使用，因为完全备份包含数据库中的所有数据，所以对于可以快速备份的小数据库而言，最佳方法就是使用完全备份。大型数据库可以用增量备份来补充完全备份。12.1.1数据库备份的类型（2）增量备份增量备份只备份自上一次完全备份发生改变的内容和在增量备份过程中所发生的所有活动。增量备份基于以前的完全备份，完全备份称为“基准备份”。增量备份比完全备份更小、更快，可以简化频繁的备份操作，减少数据丢失的风险。每次连续的增量备份都大于前一次增量备份，这就需要更长的备份时间、还原时间和更大的空间。因此，可定期执行新的完全备份以提供新的增量基准。12.1.1数据库备份的类型（2）增量备份当使用增量备份时，最好遵循以下原则：在每次完全备份后，定期安排增量备份。例如，可以每五小时执行一次增量备份，对于活动性较高的系统，此频率也可以更高。在确保增量备份不会太大的情况下，定期安排新的完全备份。例如，可以每周备份一次完整数据库。

12.1.1数据库备份的类型2．按备份时间分类即在什么情况下进行备份，分为静态备份和动态备份（1）静态备份静态备份是指系统中没有正在运行的事务时进行的备份，静态备份期间不允许进行对数据库的任何存取、修改活动。优点：实现简单，得到的备份一定能够保证数据的一致性。缺点：降低了数据库的可用性，因为在静态备份开始之前所有事务都必须已经结束，而新的事务必须等到备份结束才能开始运行。12.1.1数据库备份的类型（2）动态备份动态备份是指备份期间允许对数据库进行存取或修改的备份。动态备份提高了数据库的可用性，备份开始之前正在运行的事务不需要结束，备份期间任何新事务也可以随时开始运行。缺点是，得到的备份不能保证数据的一致性。所以必须在备份期间把各事务对数据库中数据的修改活动记录在日志文件中，这样，通过备份和日志文件就可以把数据库恢复到某一时刻的一致状态。12.1.2数据库备份操作MySQL的数据库备份操作可以使用可视化管理工具（比如navicat）通过菜单和界面进行，也可以在CMD命令提示符窗口使用相应的命令语句进行。两种方式都将数据库备份成.sql脚本文件，文件中包含创建表、插入数据、创建视图、创建函数、创建存储过程等信息，执行脚本文件就可以导入备份的表结构和数据以及视图、函数、存储过程等。12.1.2数据库备份操作备份数据库的命令mysqldump：它是MySQL提供的一个非常有用的数据库备份工具，该工具的存储位置上是C:\ProgramFiles\MySQL\MySQLServer8.0\bin文件夹。可以在备份数据库前将此路径添加到系统环境变量PATH中，也可以在CMD窗口进入此文件夹后再进行数据库备份。12.1.2数据库备份操作1．利用navicat备份数据库2.使用mysqldump命令备份数据库（1）备份单个数据库mysqldump–uusername–hhostname–ppassworddatabase_name[table_name[…n]]>pach\filename.sql可以指定一个或多个表进行备份，多个时用空格间隔，如果不指定表则备份整个数据库。【例12-1】备份teaching整个数据库，语句如下：mysqldump–uroot–hlocalhost–pteaching>e:\data\teaching.sql12.1.2数据库备份操作（2）备份多个数据库mysqldump–uusername–hhostname–ppassword--databasesdatabase_namedatabase_name[…n]>pach\filename.sql【例12-2】备份teaching和bankcard数据库。mysqldump–uroot–hlocalhost–p--databasesteachingbankcard>e:\data\t_b1.sql12.1.2数据库备份操作（3）备份所有数据库mysqldump–uusername–hhostname–ppassword--all-databases>pach\filename.sql【例12-3】备份MySQL上的所有数据库。mysqldump–uroot–hlocalhost–p--all-databases>e:\data\all_db.sql12.1.2数据库备份操作3.二进制日志实现增量备份MySQL没有提供直接的增量备份方法，但可以通过

二进制日志(binarylogs)间接实现增量备份。备份过程：（1）查看二进制日志功能是否开启showglobalvariableslike'%log_bin%';默认是开启状态即为“ON”。（2）开启二进制日志功能可以使用vim编辑数据库配置文件“etc/f”进行开启，然后重启MySQL服务器即可。12.1.2数据库备份操作（3）完全备份使用mysqldump命令备份teaching数据库。mysqldump–uroot–hlocalhost–p--single-transaction--flush-logs--source-datateaching>e:\data\teach_backup.sql在文版编辑器中打开teach_ackup.sql文件，可以看到其中的两行信息：第二行信息是指备份后所有的更改都会保存到“DESKTOP-LAI8BRJ-bin.000051”二进制日志文件中，其扩展名000051是按日志文件的顺序编号的。

12.1.2数据库备份操作（4）更改信息在MySQL服务器上执行以下操作：USEteaching;CREATETABLEST(IdintPRIMARYKEY,NameNCHAR(10));

12.1.2数据库备份操作（5）增量备份以teach_backup.sql文件的完全备份为基准，使用mysqladmin命令进行增量备份。mysqladmin–uroot–hlocalhost–pflush-logs完成增量备份后会产生一个名为“DESKTOP-LAI8BRJ-bin.000052”的二进制日志文件，而DESKTOP-LAI8BRJ-bin.000051文件中保存了从完全备份到现在的所有更改（CREATETABLE），即增量备份。

12.2数据库恢复概述恢复数据库是一个装载数据库的备份，然后应用事务日志重建的过程。在数据库备份之前，应该检查数据库中数据的一致性，这样才能保证顺利地恢复数据库备份。在恢复数据库的过程中，用户不能进入数据库，当数据库被恢复后，数据库中的所有数据都被替换掉。数据库备份是在正常情况下进行的，而数据库恢复是在诸如硬件故障、软件故障或误操作等非正常的状态下进行的，因而其工作更加重要和复杂。

12.2.1数据库故障分类常见数据库故障：事务故障、系统故障和介质故障。1.事务故障事务故障又叫做事务内部故障，是指事务内部的程序代码不能正常运行，发生了事务执行时的故障。（1）可预期的事务故障事务内部可预期的故障是指程序中能够预先估计到的错误，比如转账事务导致存款账户余额透支、商品出库导致库存小于0等等。这种错误可以通过程序代码加以解决，比如余额透支问题，可以在事务代码中加入一个if判断，一旦透支则执行ROLLBACK语句，回滚转账事务。12.2.1数据库故障分类（2）非预期的事务故障事务内部的更多的故障是非预期的，是不能由应用程序处理的。如运算溢出、并发事务发生死锁而被选中撤销该事务、违反了某些完整性规则等等。这类非预期的故障才是真正的事务故障，由系统对该事务自动回滚。事务故障是所有故障中最轻的、也是最常见的故障，不会破坏数据库，只会导致一个事务的执行失败。12.2.1数据库故障分类2.系统故障系统故障是指由于某种原因，造成系统停止运转，致使所有正在运行的事务都以非正常方式终止，要求系统重新启动。比如操作系统、DBMS或者应用程序等软件故障，再比如内存、突然停电等外存储器以外的硬件故障。系统故障不会破坏数据库，只会影响正在运行的事务，这时，内存中数据库缓冲区的内容全部丢失，虽然存储在外部存储设备上的数据库并未破坏，但其内容不可靠了。12.2.1数据库故障分类系统故障发生后，可能会导致事务的两种情况：（1）发生系统故障时，一些尚未完成的事务的结果可能已送入物理数据库，从而造成数据库可能处于不正确的状态。为保证数据一致性，需要清除这些事务对数据库的所有修改。（2）有些已完成的事务可能有一部分甚至全部留在缓存区中，尚未写回到磁盘上的物理数据库中，系统故障使得这些事务对数据库的修改部分或全部丢失，这也会使数据库处于不一致的状态，因此应将这些事务已经提交的结果重新写入数据库。在系统重新启动后，恢复子系统需要撤销所有未完成的事务外，重做（REDO）所有已提交的事务。12.2.1数据库故障分类3.介质故障介质故障指外存储器故障，如磁盘损坏、磁头碰撞、瞬间强磁场干扰等。这类故障会对数据库造成破坏，使存储在外存中的数据部分或全部丢失，并影响正在存取这部分数据的所有事务。介质故障比前两类故障发生的可能性小，但破坏性最大，只能通过数据库备份来解决。12.2.2数据库恢复策略数据库恢复策略认为所有的数据库一定会在它们的生命周期的某一时刻需要恢复。数据库管理员职责中很重要的部分就是将数据恢复的频率降到最低，并在数据库遭到破坏之前进行监视，预计各种形式的潜在风险所能造成的破坏，并针对具体情况制定恢复计划，在破坏发生时及时地恢复数据库。在恢复一个失效的数据库之前，调查失效背后的原因是很重要的，原因不同恢复策略也不一样。12.2.2数据库恢复策略1.事务故障的恢复事务故障的恢复称为事务撤销，由系统自动完成。（1）由后向前扫描日志文件，查找该事务的更新操作。（2）对该事务的更新操作执行反操作，即对已经插入的新记录进行删除操作，对己删除的记录进行插入操作，对修改的数据恢复旧值，用旧值代替新值。（3）继续向前逐个扫描该事务已做的所有更新操作，并做同样处理，直到扫描到此事务的开始标记，事务故障恢复完毕为止。一个事务是一个工作单位，也是一个恢复单位，所以，一个事务越短，越便于对它进行撤销操作。12.2.2数据库恢复策略2．系统故障的恢复系统故障的恢复要完成两方面的工作，既要撤销所有未完成的事务，还要重做所有已提交的事务，这样才能将数据库真正恢复到一致的状态。这些操作由系统在重新启动时自动完成，不需要用户干预。（1）正向扫描日志文件，查找未提交的事务，将其事务标识记入撤销队列；同时查找已提交的事务，将其事务标识记入重做队列。（2）对撤销队列中的事务进行撤销处理：反向扫描日志文件，对每个事务的操作执行逆操作。（3）对重做队列中的事务进行重做处理：正向扫描日志文件，按照日志文件中所登记的内容，重新执行操作。12.2.2数据库恢复策略系统发生故障后，由于无法确定哪些未完成的事务已更新过数据库，哪些事务的提交结果尚未写入数据库，因此系统重启后，就要撤销所有未完成的事务，重做所有已经提交的事务。但搜索整个日志文件需耗费太多的时间，而且在故障发生前很多提交的事务已经将更新结果写入了数据库，恢复子系统对它们重做处理，会耗费大量时间。可设立检查点解决这个问题。每隔一段时间（比如10分钟）系统就产生一个检查点，在每个检查点时刻，系统将上一个检查点以来所有已提交事务留在数据库缓冲区中的内容写到物理数据库中，并对正在运行的事务进行记录。12.2.2数据库恢复策略12.2.2数据库恢复策略5类事务T1~T5分别进行以下几种不同的处理：（1）对于T1这类事务不需要重做，因为它们的更新已在检查点time1时刻写入了物理数据库。（2）T2和T4这类事务发生故障前已经成功提交，但因为还没到下一个检查点，所以它们对数据库的更新仍在内存缓冲区中，未写入物理数据库，需要重做。（3）T3和T5这类事务发生故障前还未结束，所以它们对数据库的更新需要撤销。12.2.2数据库恢复策略3.介质故障的恢复介质故障是最严重的一种故障，破坏性很大，磁盘上的物理数据和日志文件可能被破坏，这需要恢复发生介质故障前最新的数据库备份，然后利用日志文件重做该备份后所有已完成的事务。（1）首先装入最新的数据库备份，使数据库恢复到最近一次备份时的可用状态。（2）然后根据最新的数据库备份后日志文件中的内容重做已完成的事务。首先反向扫描日志文件，找出故障发生时己提交的事务，将其记入重做队列，然后正向扫描日志文件，对重做队列中的各个事务进行重做处理。12.2.2数据库恢复策略注意：静态备份的数据库，恢复后数据库即处于一致性状态。动态备份的数据库，还须同时装入备份时刻的日志文件，利用与恢复系统故障相同的方法（即相应事务的重做或撤销），才能将数据库恢复到一致性状态。12.2.3数据库恢复操作1．自动恢复自动恢复是指数据库发生事务故障或系统故障时MySQL服务器都自动运行的带有容错功能的特性。对于事务故障，自动撤销该事务完成的所有操作；对于系统故障，检查所有自最新的检查点以来发生的事务，标识所有已经提交的事务，把它们重新应用于数据库，标识所有未提交的事务将它们回滚。这个过程保证了每个数据库逻辑上的一致性。12.2.3数据库恢复操作2．手动恢复如果数据库发生介质故障或者病毒入侵、自然灾害、人为破坏、用户误操作等造成数据库破坏时，则恢复最新的数据库备份，并根据日志文件重做备份后提交的事务。如果仅使用完全备份来恢复数据库，则恢复最新的备份即可。12.2.3数据库恢复操作2．手动恢复MySQL的数据库恢复操作可以使用可视化管理工具（比如navicat）通过菜单和界面进行，也可以在CMD命令提示符窗口使用相应的命令语句进行。两种方式都是执行数据库备份文件（.sql脚本文件），使数据库恢复到数据库备份时的状态。如果要恢复的是单个数据库，则此数据库必须存在，只是已被破坏，如果数据库已经丢失，需要重新创建此数据库，否则恢复过程中会出错，重建的数据库可以与备份时的数据库不同名；如果恢复的是多个数据库，则数据库存在与否均可以。12.2.3数据库恢复操作（1）利用navicat恢复据库（2）使用mysql命令恢复数据库mysql–uusername–ppassword[database_name]<path\filename.sql【例12-4】通过多数据库备份t_b.sql文件恢复teaching和bankcard数据库。mysql–uroot–p<e:\data\t_b.sql12.2.3数据库恢复操作（3）使用增量备份恢复数据库一般是恢复最新的完全备份后再恢复最新增量备份。因为MySQL是通过二进制日志间接实现的，所以要依次恢复完全备份后的每一个代表增量备份的二进制日志文件。首先恢复完全备份teach_backup.sql，然后恢复增量备份DESKTOP-LAI8BRJ-bin.000051。mysql–uroot–pteaching<e:\data\teach_backup.sqlmysqlbinlog“c:\programdata\mysql\mysqlserver8.0\data\DESKTOP-LAI8BRJ-bin.000051”|mysql–uroot-p12.3数据的导入/导出通过导入和导出数据的操作，可以将MySQL数据库中的表导出成sql脚本文件、txt文本文件、excel文件、html文件、xml文件等等，也可以将这些类型的文件导入到MySQL数据库中。在数据库的日常维护中，经常需要进行数据库表的导入导出。可以采用可视化管理工具的导出向导、也可以采用SQL语句完成表的导入导出。12.3.1导出数据数据的导出是将MySQL数据库中的表导出成文本文件、电子表格或其他格式的数据文件中。1.使用导出向导导出数据【例12-5】将teaching中student表导出至G:\data文件夹下形成st.xls文件。12.3.1导出数据2.使用SQL语句导出数据SELECTcolumn_listFROMtable_name[WHEREconditions]INTOOUTFILE‘filename’[<options>]其中，<options>包含的内容如下：FIELDSTERMINATEDBY‘value’FIELDS[OPTIONALLY]ENCLOSEDBY‘value’FIELDSESCAPEDBY‘value’LINESSTARTINGBY‘value’LINESTERMINATEDBY‘value’12.3.1导出数据【例12-6】将teaching数据库中student表男生数据导出成st.txt文本文件，其中各字段之间的间隔符为“；”，包裹字段值的字符为“%”，转义字符默认，每行开头字符为@，每行结束字符也为@。SELECT*FROMstudentWHEREssex='男'INTOOUTFILE'C:\\ProgramData\\MySQL\\MySQLServer8.0\\