第八章系统构架及备份和恢复

第八章系统构架及备份和恢复第一页，共四十九页，2022年，8月28日本章重点掌握MySQL的构架备份和恢复方法第二页，共四十九页，2022年，8月28日一系统构架MySQL物理文件组成日志文件MySQL日志是记录MySQL数据库的日常操作和错误信息的文件。记录着MySQL数据库运行期间发生的变化。当数据库遭到意外的损害时，可以通过日志文件来查询出错原因，并且可以通过日志文件进行数据恢复。MySQL中，日志可以分为二进制日志、错误日志、通用查询日志和慢查询日志。分析这些日志文件，可以了解MySQL数据库的运行情况、日常操作、错误信息和哪些地方需要进行优化。第三页，共四十九页，2022年，8月28日错误日志：Errorlog错误日志记录了运行过程中所有较为严重的警告和错误信息，以及数据库每次启动和关闭的详细信息。在默认的情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。第四页，共四十九页，2022年，8月28日启动和设置错误日志文件通过f或者my.ini文件的log-bin选项可以开启错误日志文件日志。将log-bin选项加入到f或者my.ini文件的[mysqld]组中，形式如下：#f（Linux操作系统下）或者my.ini（Windows操作系统下）[mysqld]log-error[=DIR\[filename]]#Errorloglog-error="C:/MySQL/ErrLog/local.err"第五页，共四十九页，2022年，8月28日2二进制日志BinaryLog是MySQL中最重要的日志之一，开始记录功能后，MySQL会将所有修改数据库数据的Query以二进制形式记录到日志文件中，还包括每一条Query执行的时间，所耗费的资源，以及相关的事务信息，所以binlog是事务安全的。第六页，共四十九页，2022年，8月28日启动和设置二进制日志默认情况下，二进制日志功能是关闭的。通过f或者my.ini文件的log-bin选项可以开启二进制日志。将log-bin选项加入到f或者my.ini文件的[mysqld]组中，形式如下：#f（Linux操作系统下）或者my.ini（Windows操作系统下）[mysqld]log-bin=DIR\[filename]]Max-binlog-size设置二进制文件的最大存储上限。#BinLoglog-bin="C:/MySQL/BinLog/binlog."max-binlog-size=1M第七页，共四十九页，2022年，8月28日查看二进制日志使用二进制格式可以存储更多的信息，并且可以使写入二进制日志的效率更高。但是，不能直接打开并查看二进制日志。如果需要查看二进制日志，必须使用mysqlbinlog命令。mysqlbinlog命令的语法形式如下：（cmd下输入）mysqlbinlogfilename.number如：mysqlbinlogc:\mysql\binlog\binlog.000001第八页，共四十九页，2022年，8月28日删除二进制日志二进制日记会记录大量的信息。如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。删除二进制日志的方法很多。1．删除所有二进制日志2．根据编号来删除二进制日志3．根据创建时间来删除二进制日志第九页，共四十九页，2022年，8月28日数据库管理员可以删除很长时间之前的日志，以保证MySQL服务器上的硬盘空间。MySQL数据库中，可以使用mysqladmin命令来开启新的日志。mysqladmin命令的语法如下：mysqladmin-uroot-pflush-logs执行该命令后，数据库系统会自动创建一个新的日志。第十页，共四十九页，2022年，8月28日使用二进制日志还原数据库二进制日志记录了用户对数据库中数据的改变。如INSERT语句、UPDATE语句、CREATE语句等都会记录到二进制日志中。一旦数据库遭到破坏，可以使用二进制日志来还原数据库。如果数据库遭到意外损坏，首先应该使用最近的备份文件来还原数据库。备份之后，数据库可能进行了一些更新。这可以使用二进制日志来还原。因为二进制日志中存储了更新数据库的语句，如UPDATE语句、INSERT语句等。二进制日志还原数据库的命令如下：mysqlbinlogfilename.number|mysql-uroot–p–Ddbname第十一页，共四十九页，2022年，8月28日暂时停止二进制日志功能在配置文件中设置了log-bin选项以后，MySQL服务器将会一直开启二进制日志功能。删除该选项后就可以停止二进制日志功能。如果需要再次启动这个功能，又需要重新添加log-bin选项。MySQL中提供了暂时停止二进制日志功能的语句。如果用户不希望自己执行的某些SQL语句记录在二进制日志中，那么需要在执行这些SQL语句之前暂停二进制日志功能。用户可以使用SET语句来暂停二进制日志功能，SET语句的代码如下：SETSQL_LOG_BIN=0;第十二页，共四十九页，2022年，8月28日3查询日志通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、更新语句、查询语句等。由于记录了所有的Query,包括所有的select,体积比较大，开启后对性能也有比较大的影响，一般在跟踪某些特殊的查询问题时才会短暂打开该功能。第十三页，共四十九页，2022年，8月28日启动和设置通用查询日志默认情况下，通用查询日志功能是关闭的。通过f或者my.ini文件的log选项可以开启通用查询日志。将log选项加入到f或者my.ini文件的[mysqld]组中，形式如下：#f（Linux操作系统下）或者my.ini（Windows操作系统下）[mysqld]log=DIR\[filename]]log="C:/MySQL/Log/local."第十四页，共四十九页，2022年，8月28日查看通用查询日志用户的所有操作都会记录到通用查询日志中。如果希望了解某个用户最近的操作，可以查看通用查询日志。通用查询日志是以文本文件的形式存储的。Windows操作系统可以使用文本文件查看器查看。Linux操作系统下，可以使用vi工具或者使用gedit工具来查看。第十五页，共四十九页，2022年，8月28日删除通用查询日志通用查询日志会记录用户的所有操作。如果数据库的使用非常频繁，那么通用查询日志将会占用非常大的磁盘空间。数据库管理员可以删除很长时间之前的通用查询日志，以保证MySQL服务器上的硬盘空间。本小节将介绍删除通用查询日志的方法。MySQL数据库中，也可以使用mysqladmin命令来开启新的通用查询日志。新的通用查询日志会直接覆盖旧的查询日志，不需要再手动删除了。mysqladmin命令的语法如下：mysqladmin-uroot-pflush-logs第十六页，共四十九页，2022年，8月28日4慢查询日志慢查询日志用来记录执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。第十七页，共四十九页，2022年，8月28日启动和设置慢查询日志默认情况下，慢查询日志功能是关闭的。通过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第十八页，共四十九页，2022年，8月28日查看慢查询日志执行时间超过指定时间的查询语句会被记录到慢查询日志中。如果用户希望查询哪些查询语句的执行效率低，可以从慢查询日志中获得想要的信息。慢查询日志也是以文本文件的形式存储的。可以使用普通的文本文件查看工具来查看。第十九页，共四十九页，2022年，8月28日删除慢查询日志慢查询日志的删除方法与通用查询日志的删除方法是一样的。可以使用mysqladmin命令来删除。也可以使用手工方式来删除。mysqladmin命令的语法如下：mysqladmin-uroot-pflush-logs执行该命令后，命令行会提示输入密码。输入正确密码后，将执行删除操作。新的慢查询日志会直接覆盖旧的查询日志，不需要再手动删除了。数据库管理员也可以手工删除慢查询日志。删除之后需要重新启动MySQL服务。重启之后就会生成新的慢查询日志。如果希望备份旧的慢查询日志文件，可以将旧的日志文件改名。然后重启MySQL服务。第二十页，共四十九页，2022年，8月28日实例（1）启动二进制日志功能，并且将二进制日志存储到C:\目录下。二进制日志文件命名为binlog。（2）启动服务后，查看二进制日志。（3）然后向cookbook数据库下的stu_info表中插入两条记录。（4）暂停二进制日志功能，然后再次删除stu_info表中的两条记录。（5）重新开启二进制日志功能。（6）使用二进制日志来恢复stu_info表。（7）删除二进制日志。第二十一页，共四十九页，2022年，8月28日实践要求（1）将错误日志的存储位置设置为C:\LOG目录下。（2）开启通用查询日志，并设置该日志存储在C:\LOG目录下。（3）开启慢查询日志，并设置该日志存储在C:\LOG目录下。设置时间值为5秒。（4）查看错误日志、通用查询日志和慢查询日志。（5）删除错误日志。（6）删除通用查询日志和慢查询日志。第二十二页，共四十九页，2022年，8月28日小结介绍了日志的含义、作用和优缺点，然后介绍了二进制日志、错误日志、通用查询日志和慢查询日志的内容。重点内容是二进制日志、错误日志和查询日志，因为这几种日志的使用频率比较高。二进制日志是难点。二进制日志的查询方法与其它日志不同，需要特别注意。而且，二进制日志可以还原数据库。通过本节学习，大家对MySQL日志会有深入的了解。第二十三页，共四十九页，2022年，8月28日习题1.练习启动和设置二进制日志、查看二进制日志、暂停二进制日志功能等操作。2.练习使用二进制日志的内容还原数据库。3.练习使用三种方式删除二进制日志。4.练习设置错误日志的存储路径、查看错误日志、删除错误日志。5.练习启动和设置通用查询日志、查看通用查询日志。6.练习启动和设置慢查询日志、查看慢查询日志。7.练习删除通用查询日志和慢查询日志。第二十四页，共四十九页，2022年，8月28日备份和恢复数据库备份备份是DBA工作中非常重要的一部分，任何正式的环境的数据库都必须有备份计划和恢复系统。第二十五页，共四十九页，2022年，8月28日数据库中的数据丢失或被破坏可能是由于以下原因：（1）计算机硬件故障。由于使用不当或产品质量等原因，计算机硬件可能会出现故障，不能使用。如硬盘损坏会使得存储于其上的数据丢失。（2）软件故障。由于软件设计上的失误或用户使用的不当，软件系统可能会误操作数据引起数据破坏。（3）病毒。破坏性病毒会破坏系统软件、硬件和数据。（4）误操作。如用户误使用了诸如DELETE、UPDATE等命令而引起数据丢失或破坏。（5）自然灾害。如火灾、洪水或地震等，它们会造成极大的破坏，会毁坏计算机系统及其数据。（6）盗窃。一些重要数据可能会遭窃。因此，必须制作数据库的复本，即进行数据库备份，在数据库遭到破坏时能够修复数据库，即进行数据库恢复，数据库恢复就是把数据库从错误状态恢复到某一正确状态。备份和恢复数据库也可以用于其他目的，如可以通过备份与恢复将数据库从一个服务器移动或复制到另一个服务器。第二十六页，共四十九页，2022年，8月28日有多种可能会导致数据表的丢失或者服务器的崩溃，一个简单的DROPTABLE或者DROPDATABASE的语句，就会让数据表化为乌有。更危险的是DELETE*FROMtable_name，可以轻易地清空数据表，而这样的错误是很容易发生的。因此，拥有能够恢复的数据对于一个数据库系统来说是非常重要的。MySQL有三种保证数据安全的方法。（1）完全备份：通过导出数据或者表文件的拷贝来保护数据。（2）二进制日志文件(增量备份)：保存更新数据的所有语句。（3）数据库复制：MySQL内部复制功能建立在两个或两个以上服务器之间，通过设定它们之间的主从关系来实现的。其中一个作为主服务器，其他的作为从服务器。第二十七页，共四十九页，2022年，8月28日完全备份完全备份最简单也最快速的方法是拷贝数据库文件夹，当然在拷贝时对MySQL会有些要求。也可以使用mysqldump程序或mysqlhotcopy脚本对数据库进行SQL语句级别的备份，它们速度要稍微慢一些，不过通用性更强。第二十八页，共四十九页，2022年，8月28日完全备份使用拷贝文件夹的方式备份因为MySQL表保存为文件方式，所以理论上来说可以通过拷贝文件的方式对数据库进行备份。不过，使用直接备份方法时，必须要保证表在拷贝期间不被使用。如果不能满足这一条件，拷贝的文件就可能存在数据的不一致性，拷贝操作也就失败了。第二十九页，共四十九页，2022年，8月28日最可靠的方法是在拷贝以前关闭MySQL服务，拷贝完成后再重新启动服务。但有些应用会要求24小时不间断服务，此时关闭服务器的方法就不很合适，不过MySQL提供了表的锁定和解锁的相关操作，以确保在拷贝文件期间该文件不会被修改。第三十页，共四十九页，2022年，8月28日使用锁定机制备份表的基本步骤为：使用LOCKTABLES命令锁定某一表或多个表；拷贝对应的文件；使用UNLOCKTABLES解锁已拷贝完的表。第三十一页，共四十九页，2022年，8月28日MysqldumpMysqldump可以在MySQL安装目录中找到，该程序用于转储数据库或搜集数据库进行备份或将数据转移到另一个支持SQL语句的服务器(不一定是一个MySQL服务器)。备份后产生的备份文件是一个文本文件，文件内容为创建表和往表中插入数据的SQL语句。第三十二页，共四十九页，2022年，8月28日有3种方式来调用mysqldump程序：1．备份一个数据库或一张表，语法如下：shell>mysqldump[选项]数据库名[表名]2．同时备份多个数据库，语法如下：shell>mysqldump[选项]---database数据库1[数据库2数据库3...]3．备份服务器上所有的数据库，语法如下：shell>mysqldump[选项]--all--database第三十三页，共四十九页，2022年，8月28日mysqldump最常用于备份一个完整的数据库，备份一个数据库的基本语法如下：shell>mysqldump--opt数据库名>备份文件名.sqlC:\Users\zp>mysqldump—default-character-set=gb2312-uroot-psamples>d:\samples.sql

Enterpassword:mysqladmin-uroot-pflush-logsINSERTINTO`samples`.`stu_info`(`id`,`name`)VALUES('200801010','韩晓龙');INSERTINTO`samples`.`stu_info`(`id`,`name`)VALUES('200801011','杨振军');mysqladmin-uroot-pflush-logs第三十四页，共四十九页，2022年，8月28日Dropdatabasesamples;Createdatabasesamples;可以使用以下语法将备份文件读回到服务器：shell>mysql数据库名<备份文件名.sqlC:\mysql–hlocalhost–uroot–p–Dsamples<d:\samples.sqlMysqlbinlogc:\mysql\binlog\binlog.000021mysqlbinlogc:\mysql\binlog\binlog.000021|mysql-uroot–p–Dsamples以上为完整的备份恢复过程第三十五页，共四十九页，2022年，8月28日mysqldump也可用于从一个MySQL服务器向另一个服务器复制数据时装载数据库，其语法如下：shell>mysqldump--opt数据库名|mysql--host=其它服务器主机名-C数据库名一次备份多个数据库的语法如下：shell>mysqldump---database数据库名1[数据库名2...]>备份文件名.sql使用--all--database选项可以一次备份服务器上所有的数据库：shell>mysqldump--all-databases>备份文件名.sql第三十六页，共四十九页，2022年，8月28日mysqlhotcopy使用mysqlhotcopy进行备份是备份数据库或单个表的最快的途径。mysqlhotcopy是一个Perl脚本，最初由TimBunce编写并提供。它使用LOCKTABLES、FLUSHTABLES和cp或scp来快速备份数据库。第三十七页，共四十九页，2022年，8月28日Mysqlhotcopy对使用场合有一定限制，它只能运行在数据库目录所在的机器上，并且只能用于备份MyISAM数据库，最后，它只能运行在Unix和NetWare中。Mysqlhotcopy使用的基本语法如下：shell>mysqlhotcopy数据库名[/路径/备份目录]shell>mysqlhotcopy数据库名1，数据库名2...[/路径/备份目录]第三十八页，共四十九页，2022年，8月28日增量备份增量备份是在某一次完全备份的基础，只备份其后数据的变化。如果mysqld在运行则停止，然后用--log-bin[=file_name]选项来启动。二进制日志文件中提供了执行mysqldump之后对数据库的更改进行复制所需要的信息。第三十九页，共四十九页，2022年，8月28日MySQL支持增量备份:需要用--log-bin选项来启动服务器以便启用二进制日志。当想要进行增量备份时(包含上一次完全备份或增量备份之后的所有更改)，应使用FLUSHLOGS回滚二进制日志。然后，需要将从最后的完全或增量备份的某个时刻到最后某个点的所有二进制日志复制到备份位置。这些二进制日志为增量备份；恢复时，按照下面的解释应用。下次进行完全备份时，还应使用FLUSHLOGS或mysqlhotcopy--flushlogs回滚二进制日志。第四十页，共四十九页，2022年，8月28日恢复原mysqldump备份，或二进制备份。执行下面的命令重新更新二进制日志：shell>mysqlbinloghostname-bin.[0-9]*|mysql第四十一页，共四十九页，2022年，8月28日二进制日志二进制日志以一种更有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。第四十二页，共四十九页，2022年，8月28日mysqlbinlog要想检查二进制日志文件，应使用mysqlbinlog实用工具。Mysqlbinlog的调用方法如下：shell>mysqlbinlog[选项]二进制文件名...第四十三页，共四十九页，2022年，8月28日通常情况，可以使用mysqlbinlog直接读取二进制日志文件并将它们用于本地MySQL服务器。也可以使用--read-