系统构架及备份和恢复.pptx

生物信息学数据库设计生物信息学数据库设计第八章第八章 系统构架及备份和恢复系统构架及备份和恢复本章重点q掌握MySQL的构架q备份和恢复方法一 系统构架q MySQL物理文件组成1.日志文件MySQL日志是记录MySQL数据库的日常操作和错误信息的文件。记录着MySQL数据库运行期间发生的变化。当数据库遭到意外的损害时，可以通过日志文件来查询出错原因，并且可以通过日志文件进行数据恢复。MySQL中，日志可以分为二进制日志、错误日志、通用查询日志和慢查询日志。分析这些日志文件，可以了解MySQL数据库的运行情况、日常操作、错误信息和哪些地方需要进行优化。q错误日志：Error log错误日志记录了运行过程中所有较为严重的警告和错误信息，以及数据库每次启动和关闭的详细信息。在默认的情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。启动和设置错误日志文件q通过f或者my.ini文件的log-bin选项可以开启错误日志文件日志。将log-bin选项加入到f或者my.ini文件的mysqld组中，形式如下：#f（Linux操作系统下）或者my.ini（Windows操作系统下）mysqldlog-error=DIR filename q#Errorlogqlog-error=C:/MySQL/ErrLog/local.err2 二进制日志qBinary Log 是MySQL中最重要的日志之一，开始记录功能后，MySQL会将所有修改数据库数据的Query以二进制形式记录到日志文件中，还包括每一条Query执行的时间，所耗费的资源，以及相关的事务信息，所以binlog是事务安全的。启动和设置二进制日志q默认情况下，二进制日志功能是关闭的。通过f或者my.ini文件的log-bin选项可以开启二进制日志。将log-bin选项加入到f或者my.ini文件的mysqld组中，形式如下：#f（Linux操作系统下）或者my.ini（Windows操作系统下）mysqldlog-bin=DIR filename qMax-binlog-size设置二进制文件的最大存储上限。设置二进制文件的最大存储上限。#BinLogqlog-bin=C:/MySQL/BinLog/binlog.qmax-binlog-size=1M查看二进制日志q使用二进制格式可以存储更多的信息，并且可以使写入二进制日志的效率更高。但是，不能直接打开并查看二进制日志。如果需要查看二进制日志，必须使用mysqlbinlog命令。mysqlbinlog命令的语法形式如下：（cmd下输入）mysqlbinlog filename.number如：mysqlbinlog c:mysqlbinlogbinlog.000001删除二进制日志q二进制日记会记录大量的信息。如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。删除二进制日志的方法很多。1删除所有二进制日志2根据编号来删除二进制日志3根据创建时间来删除二进制日志q数据库管理员可以删除很长时间之前的日志，以保证MySQL服务器上的硬盘空间。MySQL数据库中，可以使用mysqladmin命令来开启新的日志。mysqladmin命令的语法如下：mysqladmin-u root-p flush-logsq执行该命令后，数据库系统会自动创建一个新的日志。使用二进制日志还原数据库q二进制日志记录了用户对数据库中数据的改变。如INSERT语句、UPDATE语句、CREATE语句等都会记录到二进制日志中。一旦数据库遭到破坏，可以使用二进制日志来还原数据库。q如果数据库遭到意外损坏，首先应该使用最近的备份文件来还原数据库。备份之后，数据库可能进行了一些更新。这可以使用二进制日志来还原。因为二进制日志中存储了更新数据库的语句，如UPDATE语句、INSERT语句等。二进制日志还原数据库的命令如下：qmysqlbinlog filename.number|mysql-u root p D dbname暂时停止二进制日志功能q在配置文件中设置了log-bin选项以后，MySQL服务器将会一直开启二进制日志功能。删除该选项后就可以停止二进制日志功能。如果需要再次启动这个功能，又需要重新添加log-bin选项。MySQL中提供了暂时停止二进制日志功能的语句。q如果用户不希望自己执行的某些SQL语句记录在二进制日志中，那么需要在执行这些SQL语句之前暂停二进制日志功能。用户可以使用SET语句来暂停二进制日志功能，SET语句的代码如下：qSET SQL_LOG_BIN=0;3 查询日志q通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、更新语句、查询语句等。由于记录了所有的Query,包括所有的select,体积比较大，开启后对性能也有比较大的影响，一般在跟踪某些特殊的查询问题时才会短暂打开该功能。启动和设置通用查询日志q默认情况下，通用查询日志功能是关闭的。通过f或者my.ini文件的log选项可以开启通用查询日志。将log选项加入到f或者my.ini文件的mysqld组中，形式如下：#f（Linux操作系统下）或者my.ini（Windows操作系统下）mysqld log=DIR filename log=C:/MySQL/Log/local.查看通用查询日志q用户的所有操作都会记录到通用查询日志中。如果希望了解某个用户最近的操作，可以查看通用查询日志。通用查询日志是以文本文件的形式存储的。Windows操作系统可以使用文本文件查看器查看。Linux操作系统下，可以使用vi工具或者使用gedit工具来查看。删除通用查询日志q通用查询日志会记录用户的所有操作。如果数据库的使用非常频繁，那么通用查询日志将会占用非常大的磁盘空间。数据库管理员可以删除很长时间之前的通用查询日志，以保证MySQL服务器上的硬盘空间。本小节将介绍删除通用查询日志的方法。qMySQL数据库中，也可以使用mysqladmin命令来开启新的通用查询日志。新的通用查询日志会直接覆盖旧的查询日志，不需要再手动删除了。mysqladmin命令的语法如下：mysqladmin-u root-p flush-logs4慢查询日志q慢查询日志用来记录执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。启动和设置慢查询日志q默认情况下，慢查询日志功能是关闭的。通过f或者my.ini文件的log-slow-queries选项可以开启慢查询日志。通过long_query_time选项来设置时间值，时间以秒为单位。如果查询时间超过了这个时间值，这个查询语句将被记录到慢查询日志。将log-slow-queries选项和long_query_time选项加入到f或者my.ini文件的mysqld组中，形式如下：#f（Linux操作系统下）或者my.ini（Windows操作系统下）mysqld log-slow-queries=DIR filename long_query_time=n查看慢查询日志q执行时间超过指定时间的查询语句会被记录到慢查询日志中。如果用户希望查询哪些查询语句的执行效率低，可以从慢查询日志中获得想要的信息。慢查询日志也是以文本文件的形式存储的。可以使用普通的文本文件查看工具来查看。删除慢查询日志q慢查询日志的删除方法与通用查询日志的删除方法是一样的。可以使用mysqladmin命令来删除。也可以使用手工方式来删除。mysqladmin命令的语法如下：qmysqladmin-u root-p flush-logsq执行该命令后，命令行会提示输入密码。输入正确密码后，将执行删除操作。新的慢查询日志会直接覆盖旧的查询日志，不需要再手动删除了。数据库管理员也可以手工删除慢查询日志。删除之后需要重新启动MySQL服务。重启之后就会生成新的慢查询日志。如果希望备份旧的慢查询日志文件，可以将旧的日志文件改名。然后重启MySQL服务。实例（1）启动二进制日志功能，并且将二进制日志存储到C:目录下。二进制日志文件命名为binlog。（2）启动服务后，查看二进制日志。（3）然后向cookbook数据库下的stu_info表中插入两条记录。（4）暂停二进制日志功能，然后再次删除stu_info表中的两条记录。（5）重新开启二进制日志功能。（6）使用二进制日志来恢复stu_info表。（7）删除二进制日志。实践要求（1）将错误日志的存储位置设置为C:LOG目录下。（2）开启通用查询日志，并设置该日志存储在C:LOG目录下。（3）开启慢查询日志，并设置该日志存储在C:LOG目录下。设置时间值为5秒。（4）查看错误日志、通用查询日志和慢查询日志。（5）删除错误日志。（6）删除通用查询日志和慢查询日志。小结q介绍了日志的含义、作用和优缺点，然后介绍了二进制日志、错误日志、通用查询日志和慢查询日志的内容。重点内容是二进制日志、错误日志和查询日志，因为这几种日志的使用频率比较高。二进制日志是难点。二进制日志的查询方法与其它日志不同，需要特别注意。而且，二进制日志可以还原数据库。通过本节学习，大家对MySQL日志会有深入的了解。习题q1.练习启动和设置二进制日志、查看二进制日志、暂停二进制日志功能等操作。q2.练习使用二进制日志的内容还原数据库。q3.练习使用三种方式删除二进制日志。q4.练习设置错误日志的存储路径、查看错误日志、删除错误日志。q5.练习启动和设置通用查询日志、查看通用查询日志。q6.练习启动和设置慢查询日志、查看慢查询日志。q7.练习删除通用查询日志和慢查询日志。备份和恢复q数据库备份备份是DBA工作中非常重要的一部分，任何正式的环境的数据库都必须有备份计划和恢复系统。q数据库中的数据丢失或被破坏可能是由于以下原因：（1）计算机硬件故障。由于使用不当或产品质量等原因，计算机硬件可能会出现故障，不能使用。如硬盘损坏会使得存储于其上的数据丢失。（2）软件故障。由于软件设计上的失误或用户使用的不当，软件系统可能会误操作数据引起数据破坏。（3）病毒。破坏性病毒会破坏系统软件、硬件和数据。（4）误操作。如用户误使用了诸如DELETE、UPDATE等命令而引起数据丢失或破坏。（5）自然灾害。如火灾、洪水或地震等，它们会造成极大的破坏，会毁坏计算机系统及其数据。（6）盗窃。一些重要数据可能会遭窃。q因此，必须制作数据库的复本，即进行数据库备份，在数据库遭到破坏时能够修复数据库，即进行数据库恢复，数据库恢复就是把数据库从错误状态恢复到某一正确状态。q备份和恢复数据库也可以用于其他目的，如可以通过备份与恢复将数据库从一个服务器移动或复制到另一个服务器。q有多种可能会导致数据表的丢失或者服务器的崩溃，一个简单的DROP TABLE或者DROP DATABASE的语句，就会让数据表化为乌有。更危险的是DELETE*FROM table_name，可以轻易地清空数据表，而这样的错误是很容易发生的。q因此，拥有能够恢复的数据对于一个数据库系统来说是非常重要的。MySQL有三种保证数据安全的方法。（1）完全备份：通过导出数据或者表文件的拷贝来保护数据。（2）二进制日志文件(增量备份)：保存更新数据的所有语句。（3）数据库复制：MySQL内部复制功能建立在两个或两个以上服务器之间，通过设定它们之间的主从关系来实现的。其中一个作为主服务器，其他的作为从服务器。q完全备份完全备份q完全备份最简单也最快速的方法是拷贝数据库文件夹，当然在拷贝时对MySQL会有些要求。也可以使用mysqldump程序或mysqlhotcopy脚本对数据库进行SQL语句级别的备份，它们速度要稍微慢一些，不过通用性更强。完全备份完全备份q使用拷贝文件夹的方式备份q因为因为MySQL表保存为文件方式，所以理论上来说表保存为文件方式，所以理论上来说可以通过拷贝文件的方式对数据库进行备份。不可以通过拷贝文件的方式对数据库进行备份。不过，使用直接备份方法时，必须要保证表在拷贝过，使用直接备份方法时，必须要保证表在拷贝期间不被使用。如果不能满足这一条件，拷贝的期间不被使用。如果不能满足这一条件，拷贝的文件就可能存在数据的不一致性，拷贝操作也就文件就可能存在数据的不一致性，拷贝操作也就失败了。失败了。q最可靠的方法是在拷贝以前关闭最可靠的方法是在拷贝以前关闭MySQL服务，拷服务，拷贝完成后再重新启动服务。但有些应用会要求贝完成后再重新启动服务。但有些应用会要求24小时不间断服务，此时关闭服务器的方法就不很小时不间断服务，此时关闭服务器的方法就不很合适，不过合适，不过MySQL提供了表的锁定和解锁的相关提供了表的锁定和解锁的相关操作，以确保在拷贝文件期间该文件不会被修改。操作，以确保在拷贝文件期间该文件不会被修改。使用锁定机制备份表的基本步骤为：使用LOCK TABLES命令锁定某一表或多个表；拷贝对应的文件；使用UNLOCK TABLES解锁已拷贝完的表。qMysqldumpqMysqldump可以在MySQL安装目录中找到，该程序用于转储数据库或搜集数据库进行备份或将数据转移到另一个支持SQL语句的服务器(不一定是一个MySQL服务器)。备份后产生的备份文件是一个文本文件，文件内容为创建表和往表中插入数据的SQL语句。q有3种方式来调用mysqldump程序：1备份一个数据库或一张表，语法如下：shell mysqldump 选项 数据库名表名2同时备份多个数据库，语法如下：shell mysqldump 选项-database 数据库1数据库2 数据库3.3备份服务器上所有的数据库，语法如下：shell mysqldump 选项-all-databaseqmysqldump最常用于备份一个完整的数据库，备份一个数据库的基本语法如下：shell mysqldump-opt 数据库名 备份文件名.sql C:Userszpmysqldump default-character-set=gb2312-u root-p samples d:samples.sqlEnter password:mysqladmin-u root-p flush-logsINSERT INTO samples.stu_info(id,name)VALUES(200801010,韩晓龙);INSERT INTO samples.stu_info(id,name)VALUES(200801011,杨振军);mysqladmin-u root-p flush-logsDrop database samples;Create database samples;可以使用以下语法将备份文件读回到服务器：shell mysql 数据库名 备份文件名.sqlC:mysql h localhost u root p D samples mysqldump-opt 数据库名|mysql-host=其它服务器主机名-C 数据库名一次备份多个数据库的语法如下：shell mysqldump-database 数据库名1 数据库名2.备份文件名.sql使用-all-database选项可以一次备份服务器上所有的数据库：shell mysqldump-all-databases 备份文件名.sqlqmysqlhotcopyq使用mysqlhotcopy进行备份是备份数据库或单个表的最快的途径。mysqlhotcopy是一个Perl脚本，最初由Tim Bunce编写并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。qMysqlhotcopy对使用场合有一定限制，它只能运行在数据库目录所在的机器上，并且只能用于备份MyISAM数据库，最后，它只能运行在Unix和NetWare中。Mysqlhotcopy使用的基本语法如下：shell mysqlhotcopy 数据库名/路径/备份目录shell mysqlhotcopy 数据库名1，数据库名2./路径/备份目录 增量备份q增量备份是在某一次完全备份的基础，只备份其后数据的变化。q如果mysqld在运行则停止，然后用-log-bin=file_name选项来启动。二进制日志文件中提供了执行mysqldump之后对数据库的更改进行复制所需要的信息。qMySQL支持增量备份:需要用-log-bin选项来启动服务器以便启用二进制日志。当想要进行增量备份时(包含上一次完全备份或增量备份之后的所有更改)，应使用FLUSH LOGS回滚二进制日志。然后，需要将从最后的完全或增量备份的某个时刻到最后某个点的所有二进制日志复制到备份位置。这些二进制日志为增量备份；恢复时，按照下面的解释应用。下次进行完全备份时，还应使用FLUSH LOGS或mysqlhotcopy-flushlogs回滚二进制日志。1.恢复原mysqldump备份，或二进制备份。2.执行下面的命令重新更新二进制日志：shell mysqlbinlog hostname-bin.0-9*|mysqlq二进制日志二进制日志以一种更有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。qmysqlbinlog 要想检查二进制日志文件，应使用mysqlbinlog实用工具。Mysqlbinlog的调用方法如下：shell mysqlbinlog 选项 二进制文件名.q通常情况，可以使用mysqlbinlog直接读取二进制日志文件并将它们用于本地MySQL服务器。也可以使用-read-from-remote-server选项从远程服务器读取二进制日志。q当读取远程二进制日志时，可以通过连接参数选项来指示如何连接服务器，但它们经常被忽略掉，除非还指定了-read-from-remote-server选项。这些选项是-host、-password、-port、-protocol、-socket和-user。q可以将mysqlbinlog的输出传到mysql客户端以执行包含在二进制日志中的语句。如果有一个旧的备份，该选项在崩溃恢复时也很有用：shell mysqlbinlog hostname-bin.000001|mysql或：shell mysqlbinlog hostname-bin.0-9*|mysqlqmysqlbinlog有一个-position选项，只打印那些在二进制日志中的偏移量大于或等于某个给定位置的语句(给出的位置必须匹配一个事件的开始)。它还有在看见给定日期和时间的事件后停止或启动的选项。这样可以使用-stop-datetime选项进行点对点恢复(例如，能够说“将数据库前滚动到今天10:30 AM的位置”)。qmysqlbinlog产生的输出可以不需要原数据文件即可重新生成一个LOAD DATA INFILE操作。mysqlbinlog将数据复制到一个临时文件并写一个引用该文件的LOAD DATA LOCAL INFILE语句。由系统确定写入这些文件的目录的默认位置。要想显式指定一个目录，使用-local-load选项。q因为mysqlbinlog可以将LOAD DATA INFILE语句转换为LOAD DATA LOCAL INFILE语句(也就是说，它添加了LOCAL)，用于处理语句的客户端和服务器必须配置为允许LOCAL操作。q本章介绍了MySQL中数据的备份和恢复，它是数它是数据库管理系统的一个重要组成部分。据库管理系统的一个重要组成部分。大巧不工大巧不工 重剑无锋重剑无锋