My SQL数据库管理与应用第11章 MySQL备份与恢复.ppt

1、MySQL备份与恢复,课程目标,存储引擎和表类型 数据库的备份与恢复 完全备份 表备份 增量备份,概述,尽管数据库系统中采取了各种保护措施来保证数据库的安全性和完整性，但是计算机系统中的硬件故障、软件错误、操作失误及恶意破坏仍是不可避免的，因此数据库管理系统必须具有把数据库从错误状态恢复到某一已知的正确状态（亦称为一致状态或完整状态）的功能，这就是数据库的恢复。,概述,恢复子系统是数据库管理系统的一个重要组成部分，而且还相当庞大，常常占整个系统代码的百分之十以上。数据库系统所采用的恢复技术是否行之有效，不仅对系统的可靠程度起着决定性作用，而且对系统的运行效率也有很大音响，是衡量系统性能优劣的重

2、要指标。,1. 存储引擎和表类型,MySQL在保存数据库时有很多种存储方式，不同的存储方式执行同一SQL语句的效率可能会相差很多。考虑到在实际使用中，不同系统对数据库的要求不尽相同，执行的操作及每种操作执行的频率也都不一样，为了进一步提高数据库的处理效率，MySQL将表类型划分成很多种类，并允许对不同的数据库选择不同的存储引擎。,1. 存储引擎和表类型,MySQL插件式存储引擎的体系结构,1. 存储引擎和表类型,MySQL的存储引擎包括以下类型： MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非配置MySQL默

3、认使用另外一个引擎。 MEMORY存储引擎提供“内存中”表。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像MyISAM一样，MEMORY和MERGE存储引擎处理非事务表，这两个引擎也都被默认包含在MySQL中。,1. 存储引擎和表类型,InnoDB和BDB存储引擎提供事务安全表。BDB被包含在为支持它的操作系统发布的MySQL-Max二进制分发版里。InnoDB也默认被包括在所有MySQL 5.1二进制分发版里，可以按照喜好通过配置MySQL来允许或禁止任一引擎。 EXAMPLE存储引擎是一个“存根”引擎，它不做什么。可以用这个引擎创建表，但没有数据被存储于其中或

4、从其中检索。这个引擎的目的是服务。同样，它的主要兴趣是对开发者。,1. 存储引擎和表类型,NDB Cluster是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎。它在MySQL-Max 5.1二进制分发版里提供。这个存储引擎只被Linux, Solaris, 和Mac OS X 支持。 ARCHIVE存储引擎被用来无索引地，非常小地覆盖存储的大量数据。,1. 存储引擎和表类型,CSV存储引擎把数据以逗号分隔的格式存储在文本文件中。 BLACKHOLE存储引擎接受但不存储数据，并且检索总是返回一个空集。 FEDERATED存储引擎把数据存在远程数据库中。在MySQL 5.1

5、中，它只和MySQL一起工作，使用MySQL C Client API。在未来的分发版中，我们想要让它使用其它驱动器或客户端连接方法连接到另外的数据源。,1. 存储引擎和表类型,当创建一个新表的时候，可以通过使用ENGINE 或TYPE 选项到来指定要创建什么类型的表： CREATE TABLE t (i INT) ENGINE = INNODB;,1. 存储引擎和表类型,如果省略掉ENGINE或TYPE选项，默认是使用MyISAM引擎。要想修改默认的引擎可以用-default-storage-engine或-default-table-type服务器启动选项来进行设置，或者通过设置stora

6、ge_engine或table_type系统变量来改变。,1. 存储引擎和表类型,1.1 MyISAM MyISAM是默认存储引擎。它是在早期MySQL的ISAM引擎基础上，经过一定的扩展而产生的。（MySQL 5.1不支持ISAM）。 MyISAM类型的表在磁盘上存储成三个文件。,1. 存储引擎和表类型,这三个文件的名称相同，为该表的表名，仅扩展名不同，各文件作用如下： .frm文件存储表定义。 .MYD (MYData)文件存储表中数据。 .MYI (MYIndex)文件存储表上建立的索引。,1. 存储引擎和表类型,1.2 InnoDB InnoDB给MySQL提供了具有提交，回滚和崩溃恢

7、复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级，这一特点可大大提高多用户部署时的性能。在SQL查询中，可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。,1. 存储引擎和表类型,InnoDB是为处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。 InnoDB常常用于支持需要高性能的大型数据库站点。著名的Internet新闻站点S运行在InnoDB上。Mytrix, Inc.在InnoDB上存储超过1TB的数据，还有一些其它站点在InnoDB上处理平均每秒800

8、次插入/更新的负荷。,2.数据库备份,在MySQL中备份数据库的方法很多，可分为以下几种： 完全备份：将数据库中的数据及所有对象全部进行备份。 表备份：仅将一张或多张表中的数据进行备份。 增量备份：在某一次完全备份的基础，只备份其后数据的变化。,2.数据库备份,2.1 完全备份 完全备份最简单也最快速的方法是拷贝数据库文件夹，当然在拷贝时对MySQL会有些要求。也可以使用mysqldump程序或mysqlhotcopy脚本对数据库进行SQL语句级别的备份，它们速度要稍微慢一些，不过通用性更强。,2.1 完全备份,使用拷贝文件夹的方式备份 因为MySQL表保存为文件方式，所以理论上来说可以通过拷

9、贝文件的方式对数据库进行备份。不过，使用直接备份方法时，必须要保证表在拷贝期间不被使用。如果不能满足这一条件，拷贝的文件就可能存在数据的不一致性，拷贝操作也就失败了。,2.1 完全备份,最可靠的方法是在拷贝以前关闭MySQL服务，拷贝完成后再重新启动服务。但有些应用会要求24小时不间断服务，此时关闭服务器的方法就不很合适，不过MySQL提供了表的锁定和解锁的相关操作，以确保在拷贝文件期间该文件不会被修改。,2.1 完全备份,使用锁定机制备份表的基本步骤为： 使用LOCK TABLES命令锁定某一表或多个表； 拷贝对应的文件； 使用UNLOCK TABLES解锁已拷贝完的表。,2.1 完全备份,

10、Mysqldump Mysqldump可以在MySQL安装目录中找到，该程序用于转储数据库或搜集数据库进行备份或将数据转移到另一个支持SQL语句的服务器(不一定是一个MySQL服务器)。备份后产生的备份文件是一个文本文件，文件内容为创建表和往表中插入数据的SQL语句。,2.1 完全备份,有3种方式来调用mysqldump程序： 1备份一个数据库或一张表，语法如下： shell mysqldump 选项 数据库名表名 2同时备份多个数据库，语法如下： shell mysqldump 选项 -database 数据库1 数据库2 数据库3. 3备份服务器上所有的数据库，语法如下： shell my

11、sqldump 选项 -all-database,2.1 完全备份,mysqldump最常用于备份一个完整的数据库，备份一个数据库的基本语法如下： shell mysqldump -opt 数据库名 备份文件名.sql 可以使用以下语法将备份文件读回到服务器： shell mysql 数据库名 mysql -e 备份文件路径/备份文件名.sql 数据库名,2.1 完全备份,mysqldump也可用于从一个MySQL服务器向另一个服务器复制数据时装载数据库，其语法如下： shell mysqldump -opt 数据库名 | mysql -host=其它服务器主机名 -C 数据库名 一次备份多个

12、数据库的语法如下： shell mysqldump -database 数据库名1 数据库名2 . 备份文件名.sql 使用-all-database选项可以一次备份服务器上所有的数据库： shell mysqldump -all-databases 备份文件名.sql,2.1 完全备份,mysqlhotcopy 使用mysqlhotcopy进行备份是备份数据库或单个表的最快的途径。mysqlhotcopy是一个Perl脚本，最初由Tim Bunce编写并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。,2.1 完全备份,Mysqlhotcopy对使

13、用场合有一定限制，它只能运行在数据库目录所在的机器上，并且只能用于备份MyISAM数据库，最后，它只能运行在Unix和NetWare中。 Mysqlhotcopy使用的基本语法如下： shell mysqlhotcopy 数据库名 /路径/备份目录 shell mysqlhotcopy 数据库名1，数据库名2 . /路径/备份目录,2.2 表备份,如果只想对数据库中的某些表进行备份，可以使用 SELECT INTO .OUTFILE 或BACKUP TABLE语句。,2.2 表备份,SELECT INTO .OUTFILE 此语法是MySQL对SELECT语句的扩展应用，其用法如下： SELE

14、CT 列名列表 INTO OUTFILE|DUMPFILE 文件名 输出选项 FROM表名 其它SELECT子句,2.2 表备份,SELECT.INTO OUTFILE语句的主要作用是快速地把一个表转储到服务器机器上。如果想在服务器主机之外的部分客户主机上创建结果文件，不能使用此语句，此时应该在客户主机上使用如“mysql e SELECT . file_name”的命令，来生成文件。,2.2 表备份,LOAD DATA LOW_PRIORITY | CONCURRENT LOCAL INFILE 文件名 REPLACE | IGNORE INTO TABLE 表名 FIELDS TERMIN

15、ATED BY 字符串 OPTIONALLY ENCLOSED BY 字符 ESCAPED BY 字符 LINES STARTING BY 字符串 TERMINATED BY 字符串 IGNORE number LINES (col_name_or_user_var,.) SET col_name = expr,.,2.2 表备份,LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。也可以通过使用mysqlimport应用程序载入数据文件，它通过向服务器发送一个LOAD DATA INFILE语句实现此功能。,2.2 表备份,确定文件的字符集 载入文件时，My

16、SQL使用character_set_database系统变量所指示的字符集解释文件中的数据。SET NAMES和character_set_client的设置不会影响对输入的解释。 LOCAL 如果指定了LOCAL，则文件会被客户主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定确切的位置。如果给定的是一个相对的路径名称，则此名称会被理解为相对于启动客户端时所在的目录。,2.2 表备份,LOW_PRIORITY 如果使用LOW_PRIORITY，则LOAD DATA语句的执行被延迟，直到没有其它的客户端从表中读取为止。 CONCURRENT 如果一个MyISAM表满

17、足同时插入的条件（即该表在中间有空闲块），并且对这个MyISAM表指定了CONCURRENT，则当LOAD DATA正在执行时，其它线程会从表中重新获取数据。不过，使用本选项也会略微影响LOAD DATA的性能，即使没有其它线程在同时使用本表格。,2.2 表备份,如何定位文件 当在服务器主机上为文件定位时，服务器使用以下规则： 如果给定了一个绝对的路径名称，则服务器使用此路径名称。 如果给定了带有一个或多个引导组件的相对路径名称，则服务器会搜索相对于服务器数据目录的文件。 如果给定了一个不带引导组件的文件名称，则服务器会在默认数据库的数据库目录中寻找文件。,2.2 表备份,REPLACE和IG

18、NORE 有些输入记录把原有的记录复制到唯一关键字值上。REPLACE和IGNORE关键字用于控制这些输入记录的操作。 如果指定了REPLACE，则输入行会替换原有行（换句话说，与原有行一样，对一个主索引或唯一索引具有相同值的行）。,2.2 表备份,如果指定IGNORE，则把原有行复制到唯一关键字值的输入行被跳过。如果这两个选项都不指定，则运行情况根据LOCAL关键词是否被指定而定。不使用LOCAL时，当出现重复关键字值时，会发生错误，并且剩下的文本文件被忽略。使用LOCAL时，默认的运行情况和IGNORE被指定时的情况相同；这是因为在运行中间，服务器没有办法中止文件的传输。,2.2 表备份,

19、FIELDS和LINES FIELDS和LINES子句的语法对于两个语句是一样的。两个子句都是自选的，但是如果两个都被指定了，FIELDS必须位于LINES的前面。,2.2 表备份,IGNORE IGNORE选项用于在文件的开始处忽略指定数量的行。例如，可以使用IGNORE 1 LINES来跳过一个包含列名称的起始标题行： mysql LOAD DATA INFILE /tmp/test.txt - INTO TABLE test IGNORE 1 LINES;,2.2 表备份,BACKUP TABLE BACKUP TABLE 表名1 ,表名2 . TO /文件路径/文件名 本语句提供在线备

20、份能力，但MySQL不推荐使用这种方法，如果可能的话，应尽量使用mysqlhotcopy原本替代本语句。,2.2 表备份,BACKUP TABLE用于在刷新了所有对磁盘的缓冲变更后，把恢复表所需的最少数目的表文件拷贝到备份目录中。本语句只对MyISAM表起作用。它可以拷贝.frm定义文件和.MYD数据文件。.MYI索引文件可以从这两个文件中重建。本目录应被指定为一个完整的路径名。,2.2 表备份,该语句的返回一张表，表中每列的列名及其含义如下： Table：表名称。 Op：进行备份。 Msg_type：状态、错误、信息或警告之一。 Msg_text：消息。,2.3 增量备份,增量备份是在某一次

21、完全备份的基础，只备份其后数据的变化。 如果mysqld在运行则停止，然后用-log-bin=file_name选项来启动。二进制日志文件中提供了执行mysqldump之后对数据库的更改进行复制所需要的信息。,2.3 增量备份,MySQL支持增量备份:需要用-log-bin选项来启动服务器以便启用二进制日志。当想要进行增量备份时(包含上一次完全备份或增量备份之后的所有更改)，应使用FLUSH LOGS回滚二进制日志。然后，需要将从最后的完全或增量备份的某个时刻到最后某个点的所有二进制日志复制到备份位置。这些二进制日志为增量备份；恢复时，按照下面的解释应用。下次进行完全备份时，还应使用FLUSH

22、 LOGS或mysqlhotcopy -flushlogs回滚二进制日志。,2.3 增量备份,如果必须恢复MyISAM表，先使用REPAIR TABLE或myisamchk -r来恢复。99.9%的情况下该方法可以工作。如果myisamchk失败，试试下面的方法。请注意只有用-log-bin选项启动了MySQL从而启用二进制日志它才工作。 恢复原mysqldump备份，或二进制备份。 执行下面的命令重新更新二进制日志： shell mysqlbinlog hostname-bin.0-9* | mysql,2.3 增量备份,二进制日志 二进制日志以一种更有效的格式，并且是事务安全的方式包含更新

23、日志中可用的所有信息。 二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。,2.3 增量备份,mysqlbinlog 要想检查二进制日志文件，应使用mysqlbinlog实用工具。Mysqlbinlog的调用方法如下： shell mysqlbinlog 选项 二进制文件名.,2.3 增量备份,通常情况，可以使用mysqlbinlog直接读取二进制日志文件并将它们用于本地MySQL服务器。也可以使用-read-from-remote-server选项从远程服务器读取二进制日志。 当读取远程二进制日志时，可以通过连接参数选项来指示如何连接服务器，但它们经常被忽略掉，除非还指定了-read-from-remote-server选项。这些选项是-host、-password、-port、-protocol、-socket和-user。,2.3 增量备份,可以将mysq