第六章 MYSQL备份与恢复

数据库技术与应用第六章MYSQL备份与恢复第六章MYSQL备份与恢复★数据库技术与应用★第2页本章主要内容6.1MYSQL日志٭6.1.1错误日志٭6.1.2二进制日志٭6.1.3查询日志٭6.1.4慢日志6.2MYSQL备份与恢复٭6.2.1备份/恢复策略٭6.2.2逻辑备份和恢复٭6.2.3物理备份和恢复٭6.2.4表的导入导出任何数据库系统都会由于各种原因出现错误，甚至崩溃，因此数据库必须要有一套完备的灾难恢复机制。一般通过数据库日志、数据备份、数据恢复相关技术来实现第六章MYSQL备份与恢复★数据库技术与应用★第3页MYSQL日志概述MySQL日志记录了数据库工作的各种信息，以帮助数据库管理员追踪数据库曾经发生过的各种事件，从而实现对数据库系统的各种维护和优化。MYSQL日志类型如下表所示：日志文件记入文件中的信息类型错误日志记录启动、运行或停止mysqld时出现的问题。二进制日志记录所有更改、复制数据的语句。如DDL、DML语句，不包括数据查询语句查询日志记录建立的客户端连接和执行的所有语句。慢日志记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。MYSQL安装后默认仅启动错误日志（且不能被禁止），其它日志则需要通过配置服务器来启动。MYSQL的日志文件一般放在默认的数据目录中（datadir）。默认数据目录通过查看配置文件my.ini进行查看。第六章MYSQL备份与恢复★数据库技术与应用★第4页6.1.1错误日志MySQL错误日志记录了MYSQL服务开启、停止、以及运行过程中出现的错误信息。设置与查看错误日志٭MYSQL错误日志默认开启，且不能禁止。٭错误日志文件通常名称为hostname.err。٭在my.ini文件的[mysqld]组中加入log-error选项来指定错误文件的存放位置及文件名。删除错误日志٭Mysqladmin开启新错误文件：mysqladmin–uroot–pflush-logs٭FLUSHLOGS开启新错误文件（必须先登录MYSQL服务器）第六章MYSQL备份与恢复★数据库技术与应用★第5页6.1.2二进制日志二进制日志（更新日志）记录所有更改、复制数据的语句。如DDL、DML语句，不包括数据查询语句。服务器启动二进制日志性能大约慢1%。启动与设置二进制日志٭在my.ini文件的[mysqld]组中加入log-bin[=dir\[filename]]选项来指定二进制文件存放位置及文件名。٭MYSQL服务每次重启都会生成一个新的二进制日志文件，文件为filename.number，其中number逐次递增。٭启动二进制日志后，还会生成一个filename.index二进制清单文件。查看二进制日志文件٭Mysqlbinlogfilename.number第六章MYSQL备份与恢复★数据库技术与应用★第6页6.1.2二进制日志删除二进制日志٭删除所有二进制日志：Resetmaster;删除后系统自动建立一个序号为000001的新日志٭删除指定number序号前的所有日志文件：purgemasterlogsto‘zzhpad-bin.000003’;٭删除指定时间之前的所有日志文件：purgemasterlogsbefore‘2011-09-2710:51:00’;٭设置过去天数，从而自动删除过期日志：my.ini文件的[mysqld]组中加入expire_logs_days=3第六章MYSQL备份与恢复★数据库技术与应用★第7页6.1.3查询日志通用查询日志记录了所有连接和查询语句。查询日志能够全面的反应MYSQL服务器中发生的事件，但是查询日志需占用较大的系统资源。启动与设置查询日志٭在my.ini文件的[mysqld]组中加入log[=dir\[filename]]选项来指定二进制文件存放位置及文件名。٭在没有指定filename时，默认文件名为host_name.log。查看查询日志文件٭查询日志为纯文本文件（例zzhpad.log），可以直接打开查看。删除日志文件٭mysqladmin–uroot–pflush-logs重置覆盖原来日志。٭直接通过文件系统进行删除第六章MYSQL备份与恢复★数据库技术与应用★第8页6.1.4慢查询日志慢查询日志记录了所有执行时间超过指定时间（默认为10秒）的SQL查询语句，其中获得表锁定的时间不算作执行时间。可用于找出效率低下的查询，从而进行优化。启动与设置查询日志٭my.ini文件的[mysqld]组中加入如右所示的参数。٭在没指定日志文件名时，默认文件名为host_name-slow.log。查看查询日志文件٭慢日志为纯文本文件（例zzhpad-slow.log），可以直接打开查看。٭Mysqldumpslowzzhpad-slow.log可对慢日志进行分类汇总，从而从众多记录中快速找到感性趣的记录。第六章MYSQL备份与恢复★数据库技术与应用★第9页MYSQL日志小结日志文件记入文件中的信息类型错误日志记录启动、运行或停止mysqld时出现的问题。二进制日志记录所有更改、复制数据的语句。如DDL、DML语句，不包括数据查询语句查询日志记录建立的客户端连接和执行的所有语句。慢日志记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。第六章MYSQL备份与恢复★数据库技术与应用★第10页6.2MYSQL备份与恢复٭6.2.1备份/恢复策略٭6.2.2逻辑备份和恢复٭6.2.3物理备份和恢复٭6.2.4表的导入导出操作系统崩溃、电源故障、文件系统崩溃、硬件问题(硬盘、母板等等)等问题，会导致数据库服务中断或数据丢失，在此情况下，必须通过一组有效地策略来保证数据库服务器完好如初。第六章MYSQL备份与恢复★数据库技术与应用★第11页6.2.1备份/恢复策略确定要备份表的存储引擎是事务型还是非事务型，不同存储引擎备份方式在数据一致性方面是不同的。确定使用完全备份还是增量备份。可以采取复制数据库文件的方法进行异地备份，但复制不能代替备份，复制对数据库的误操作无能为力。按一定周期定期做备份。备份要在系统负载较小的时候进行。确保MYSQL打开了log-bin选项，有了二进制日志，MYSQL才可以在必要的时候做完整恢复，或基于时间点的恢复，或基于位置的恢复。注意做备份恢复测试，确保备份是有效、且可以恢复的。第六章MYSQL备份与恢复★数据库技术与应用★第12页6.2.2逻辑备份和恢复MYSQL逻辑备份使用mysqldump工具实现，将数据库的数据备份为一个文本文件。逻辑备份适用于各种存储引擎。٭Mysqldump–uroot–p--all-databaseall.sql٭Mysqldump–uroot–p--single-transactionstst.sql٭Mysqldump–uroot–p--databasesttestmydatabases.sql٭Mysqldump–uroot–p–lststudentcoursescs_c_sc.sql注意：mysqldump的选项参数非常多，详情可以参考MYSQL手册。在此介绍其中三个参数以保证数据一致性：--lock-tables，-l：备份时锁定数据表，对于MyISAM表而言适用--single-transaction：产生一个数据快照，对于InnoDB表而言适用--master-data：将二进制日志的位置和文件名写入到输出中。对主从结构适用第六章MYSQL备份与恢复★数据库技术与应用★第13页6.2.3物理备份和恢复物理备份分为冷备份和热备份两种，与逻辑备份相比，物理备份最大的优点是备份和恢复得速度更快，其原因是物理备份都是基于文件的拷贝。冷备份：停止数据库服务，拷贝数据文件。该方法适用于MyISAM和InnoDB引擎。基本步骤：٭第一步备份：停止服务，将MYSQL数据库文件和日志文件拷贝到备份目录。٭第二步：恢复：停止服务，将备份文件覆盖当前MYSQL数据文件；启动数据服务，适用mysqlbinlog工具从二进制日志中恢复自备份以来的所有日志。第六章MYSQL备份与恢复★数据库技术与应用★第14页6.2.3物理备份和恢复热备份：不必中断数据库服务进行备份。但对于MyISAM和InnoDB引擎热备份的方法是不同的。٭MyISAM存储引擎：▪LINUX、UNIX下可以使用MYSQL自带工具mysqlhotcopy第六章MYSQL备份与恢复★数据库技术与应用★第15页5.1.3.1创建账号—GRANT语句授予的权限可以分为多个层级：٭全局层级：全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANTALLON*.*和REVOKEALLON*.*只授予和撤销全局权限。٭表层级：表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANTALLONdb_name.tbl_name和REVOKEALLONdb_name.tbl_name只授予和撤销表权限。٭列层级：列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。٭子程序层级：CREATEROUTINE,ALTERROUTINE,EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。除了CREATEROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。第六章MYSQL备份与恢复★数据库技术与应用★第16页5.1.3.1创建账号—GRANT语句٭对于全局、数据库、表和子程序层级，GRANTALL只能赋予正在授权的层级中存在的权限。例如GRANTALLONdb_name.*，这是一个数据库层级语句。٭MySQL允许您对不存在的数据库目标授予权限。目的是允许数据库管理员为将在此后被创建的数据库目标预备用户账户和权限。٭删除一个表或数据库时，MySQL不会自动撤销任何权限。但是，删除一个子程序，则被赋予该子程序的所有子程序层级的权限都被撤销。各层级权限表第六章MYSQL备份与恢复★数据库技术与应用★第17页5.1.3.1创建账号—GRANT语句授予各层级权限示例：٭GRANTALLPRIVILEGESON*.*TO'zzh1'@'localhost'IDENTIFIEDBY'123456‘;—全局层级٭GRANTALLPRIVILEGESONtest.*TO'zzh1'@'localhost'IDENTIFIEDBY'123456‘;—表层级٭GRANTSELECT,CREATE,DROPONst.studentTO'zzh1'@'localhost'IDENTIFIEDBY'123456‘;—表层级٭GRANTSELECT,UPDATE(sname)ONst.studentTO'zzh1'@'localhost'IDENTIFIEDBY'123456‘;—列层级٭GRANTEXECUTEONPROCEDUREst.getrecordTO'zzh1'@'localhost'IDENTIFIEDBY'123456‘;—子程序层级第六章MYSQL备份与恢复★数据库技术与应用★第18页5.1.3.1创建账号—GRANT语句权限时限制用户资源：GRANTALLONcustomer.*TOfrancis'@'localhost'IDENTIFIEDBY'frank'WITHMAX_QUERIES_PER_HOUR20每小时执行查询次数MAX_UPDATES_PER_HOUR10每小时执行更新次数MAX_CONNECTIONS_PER_HOUR5每小时连接服务器次数MAX_USER_CONNECTIONS2;同时最大连接数第六章MYSQL备份与恢复★数据库技术与应用★第19页5.1.3.1创建账号—GRANT语句