MySQL数据库管理与应用任务式教程（微课版）单元8 MySQL数据库备份与恢复

单元8MySQL数据库备份与恢复《MySQL数据库管理与应用任务式教程（慕课版）》学习内容任务1实现学生成绩管理数据库的备份与恢复

2任务2使用日志备份和恢复数据数据库系统的组成任务4卸载MySQL1.数据备份和恢复的概述数据库备份是指通过复制表文件或者导出数据的方式来生成数据库的副本。备份的目的是当数据库出现故障或遭到破坏时，能将备份的数据库加载到系统中，使数据库从错误状态恢复到备份时的正确状态。一般情况下，数据库备份需要备份的数据包括表数据，二进制日志、InnoDB事务日志，代码(存储过程、存储函数、触发器、事件调度器)，服务器配置文件等。数据库恢复是与备份对应的系统维护和管理操作。数据恢复是指当数据库出现故障或者出于某种需要时，将已备份的数据加载到数据库中，使数据库恢复到备份时的状态。任务1

实现学生成绩管理数据库的备份与恢复8.1.1数据备份和数据恢复的概述2.数据备份的分类(1)根据备份时数据库服务器是否在线，数据备份可以分为以下3类。冷备份：冷备份也叫脱机备份，即在数据库已经正常关闭的状态下进行的备份。热备份：也称联机备份，即在数据库正常运行时进行的备份，备份时数据库的读写操作可以正常执行。温备份：也是在数据库正常运行时进行的备份，但备份时数据库仅支持读操作，不支持写操作。(2)按照备份后文件的内容，数据备份可以分为以下两类。物理备份：是指复制数据库的物理结构文件的操作。逻辑备份：是指将数据库中的数据导出到一个可读的文件中，一般是文本文件，实际上导出的是SQL语句文件或表数据。(3)按照备份涉及的数据集合的范围，数据备份可以分为完全备份和部分备份，部分备份又分为增量备份和差异备份。完全备份增量备份差异备份任务1

实现学生成绩管理数据库的备份与恢复3.数据备份的策略对于数据库管理员来说，定制合理的备份策略非常重要，数据库管理员应该根据自身的业务要求设计出损失最小、对数据库影响最小的备份策略。在进行备份或恢复操作时需要考虑以下因素。确定要备份的表的存储引擎是事务型还是非事务型，不同的存储引擎备份方式在处理数据一致性方面是不同的。确定使用完全备份还是增量备份。可以考虑采取数据库复制的方法来做异地备份，这是容灾的防范。要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候进行。确保MySQL打开log-bin选项，有了binarylog，MySQL才可以在必要的时候做完整恢复、基于时间点的恢复，或基于位置的恢复。要经常做备份恢复测试，确保备份是有效的，并且是可以恢复的。任务1

实现学生成绩管理数据库的备份与恢复1.冷备份与恢复停止MySQL数据库服务。在操作系统级别复制默认存放在MySQL数据目

C:\ProgramData\MySQL\MySQLServer5.7\Data下的相应数据库的数据文件和日志文件到备份目录。任务1

实现学生成绩管理数据库的备份与恢复8.1.2物理备份和恢复2.热备份与恢复对于MyISAM存储引擎，热备份方法虽然有很多，但实质都是将要备份的表加读锁，然后再复制数据文件到备份目录。操作方法如下。给数据库中所有表加读锁，可执行如下语句。mysql>flushtablesforread;复制相应数据库的数据文件到备份目录。对于lnnoDB存储引擎，可用Percona公司开发的开源热备工具xtrabackup，对MySQLinnodb表进行热备份、增量备份。热备份方法相对复杂些，在此不做介绍。任务1

实现学生成绩管理数据库的备份与恢复1.使用mysqldump工具备份数据库mysqldump程序在目录C:\ProgramFiles\MySQL\MySQLServer5.7\bin下，mysqldump命令是在cmd命令行窗口中执行的。（1）备份一个数据库。使用mysqldump命令备份一个数据库的语法格式如下。mysqldump-hhost-uusername-ppassworddbname>filename.sql任务1

实现学生成绩管理数据库的备份与恢复8.1.3逻辑备份与恢复【例题8.1】使用root用户备份学生成绩管理数据库cjgl，备份文件名为“cjgl.sql”C:\Users\Administrator>mysqldump-uroot-pmysqlcjgl>D:\cjgl.sqlmysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.也可以在命令行中不写出密码，执行时输入密码。执行如下语句。C:\Users\Administrator>mysqldump-uroot-pcjgl>D:\cjgl1.sqlEnterpassword:*****可以用文本编辑器查看和编辑刚才备份的文件。文件cjgl.sql包含MySQL的版本，备份的主机名，数据库名，备份的时间，各种注释，多个DROP、CREATE和INSERT等语句，如图8-1所示。恢复操作时将使用这些语句重新创建数据库和表、插入表数据等。任务1

实现学生成绩管理数据库的备份与恢复（2）备份多个数据库。使用mysqldump命令备份多个数据库的语法格式如下。mysqldump–uusername-hhost–ppassword--databasesdbname1dbname2>filename.sql【例题8.2】使用root用户备份样本数据库world和sakila，备份文件名为“sample.sql”。C:\Users\Administrator>mysqldump-uroot-pmysql--databasesworldsakila>D:\sample.sql（3）备份所有数据库。使用mysqldump命令备份所有数据库的语法格式如下。mysqldump–uusername–ppassword--all-databases>filename.sql任务1

实现学生成绩管理数据库的备份与恢复【例题8.3】使用root用户备份所有数据库。

执行如下语句。C:\Users\Administrator>mysqldump-uroot-pmysql--all-databases>D:\all.sql2.使用mysql命令恢复数据库mysql程序在目录C:\ProgramFiles\MySQL\MySQLServer5.7\bin下，mysql命令是在cmd命令行窗口中执行的。mysql命令的语法格式如下。mysql–uusername–ppassword[dbname]<filename.sql其中，参数dbname表示要还原的数据库名，是可选参数。如果指定的数据库名不存在，系统将会报错。任务1

实现学生成绩管理数据库的备份与恢复【例8.4】使用root用户删除学生成绩管理数据库cjgl，再使用例题8.1生成的备份文件cjgl.sql还原该数据库。执行如下语句。mysql>SHOWDATABASES;mysql>DROPDATABASEcjgl;验证该数据库是否被删除。mysql>SHOWDATABASES;使用mysql命令恢复数据库。mysql>CREATEDATABASEcjgl;mysql>SHOWDATABASES;C:\Users\Administrator>mysql-uroot-pmysqlcjgl<D:\cjgl.sql用SELECT语句查看学生表中的记录来验证数据库是否已经恢复成功。mysql>USEcjgl;mysql>SELECT*FROMxs;任务1

实现学生成绩管理数据库的备份与恢复3.使用命令导出数据导出是指将MySQL数据库中的数据复制到外部存储文件(如文本文件、XML文件或者HTML文件)中，而导入则是将这些导出文件中的数据恢复到MySQL数据库中。数据导出的方式有多种，常用方法有使用mysqldump命令、使用SELECT...INTOOUTFILE语句、使用mysql命令以及使用一些图形化工具等。（1）使用mysqldump命令导出表。使用mysqldump命令不仅可以备份数据库，还能备份数据表。任务1

实现学生成绩管理数据库的备份与恢复用mysqldump命令导出sql文件。mysqldump命令导出SQL文件的语法格式如下。mysqldump-hhost-uusername-ppassworddbname[tbname...]>filename.sql【例题8.5】使用mysqldump命令以root用户的身份，导出学生成绩管理数据库cjgl中学生表xs和成绩表cj的数据，导出文件名为xs_cj.sql;导出课程表kc的数据，导出文件名为kc.sqlC:\Users\Administrator>mysqldump-uroot-pmysqlcjglxscj>E:\xs_cj.sqlC:\Users\Administrator>mysqldump-uroot–pmysqlcjglkc>E:\kc.sql【例题8.6】使用mysqldump命令以root用户的身份，将学生成绩管理数据库cjgl中课程表kc的记录导出到E:\下的文本文件中。然后将表中的记录导出到F:\下的文本文件中，要求列名之间使用英文逗号“,”隔开，所有字符类型的值用英文双引号引起来，定义转义字符为问号“?”，每行记录以回车换行符“\r\n”结尾。执行如下语句。

C:\Users\Administrator>mysqldump-uroot-pmysql-TE:\cjglkc任务1

实现学生成绩管理数据库的备份与恢复②用mysqldump命令导出纯文本文件。在某些情况下，需要将表中的数据导出为某些符号分割的纯文本，而不是SQL语句。mysqldump命令导出纯文本文件的语法格式如下。mysqldump-uusername-hhost-ppassword-Tpathdbname[tbname...][OPTIONS]（2）使用SELECT...INTOOUTFILE语句导出表。在MySQL中，可以使用SELECT...INTOOUTFILE语句将表的数据导出到一个文本文件中。

该语句的基本格式如下。

SELECT列名FROMtable[WHERE条件表达式]INTOOUTFILE'目标文件'[OPTIONS]

【例题8.7】使用SELECT...INTOOUTFILE语句以root用户的身份导出学生成绩管理数据库cjgl中的学生表xs中的数据。执行如下语句。mysql>SELECT*FROMcjgl.xsINTOOUTFILE'E:\xs.txt';【例题8.8】使用SELECT...INTOOUTFILE语句以root用户的身份导出学生成绩管理数据库cjgl中学生表xs中的记录，要求列名之间用“,”隔开，字符型数据用双引号引起来，每条记录以“-”开头，以回车换行符“\r\n”结尾。执行如下语句。mysql>SELECT*FROMcjgl.xsINTOOUTFILE'F:\xs.txt'FIELDSTERMINATEDBY'\,'OPTIONALLYENCLOSEDBY'\"'LINESSTARTINGBY'\-'TERMINATEDBY'\r\n';任务1

实现学生成绩管理数据库的备份与恢复4.使用命令导入数据（1）使用mysqlimport命令导入数据在MySQL中，mysqlimport命令主要用来向数据库中导入数据。如果使用mysqldump命令导出数据时使用了-T参数，则可以使用mysqlimport命将mysqldump导出的文件内容导入数据库中。mysqlimport–uroot–ppassworddbnamefilename.txt[OPTIONS]任务1

实现学生成绩管理数据库的备份与恢复【例题8.9】使用mysqlimport命令以root用户的身份，将例题8.6中导出的文件F:\kc.txt中的数据导入学生成绩管理数据库cjgl的课程表kc中。导入数据前，先删除kc表中的数据，语句如下。mysql>DELETEFROMkc;C:\Users\Administrator>mysqlimport-uroot-pmysqlcjglE:\kc.txt--fields-terminated-by=,--fields-optionally-enclosed-by=\"--fields-escaped-by=?--lines-terminated-by=\r\n语句执行成功后，用SELECT语句查看kc表中的记录，验证表数据是否已经导入成功mysql>USEcjgl;mysql>SELECT*FROMkc;（2）使用LOADDATA...INFILE语句导入数据LOAD

DATA

语句主要用于快速地从一个文本文件中读取行，并装入一个表中，LOAD

DATA的加载速度比普通的SQL语句要快20倍以上。文件名称必须是文字字符串。LOADDATAINFILE'filename.txt'INTOTABLEtablename[OPTIONS][IGNOREnumber

LINES]【例题8.10】使用LOADDATA语句将[例题8.6]中导出的文件F:\kc.txt中的数据导入学生成绩管理数据库cjgl的课程表kc中。导入数据前，先删除kc表中的数据。

mysql>DELETE

FROM

kc;导入数据，执行如下语句。mysql>LOADDATAINFILE‘F:\kc.txt

’

INTOTABLEcjgl.kcFIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'\"'ESCAPEDBY'?'LINESTERMINATEDBY'\r\n';以上语句执行成功后，用SELECT语句查看kc表中的记录，验证表数据是否已经导入成功。mysql>USEcjgl;mysql>SELECT*FROMkc;任务1

实现学生成绩管理数据库的备份与恢复1.使用MySQLWorkbench图形化工具备份数据库使用MySQLWorkbench图形化工具能很方便地备份指定的数据库，也可以导出指定的数据表或视图。【例题8.11】使用MySQLWorkbench图形化工具，通过root用户备份学生成绩管理数据库cjgl，备份文件名为cjgl.sql。导出学生表xs的结构与数据，文件名为cjgl_xs.sql任务1

实现学生成绩管理数据库的备份与恢复8.1.4使用MySQLWorkbench图形化工具备份和恢复数据库任务1

实现学生成绩管理数据库的备份与恢复任务1

实现学生成绩管理数据库的备份与恢复任务1

实现学生成绩管理数据库的备份与恢复2.使用MySQLWorkbench图形化工具恢复数据库【例题8.12】使用MySQLWorkbench图形化工具，通过root用户恢复学生成绩管理数据库cjgl。任务1

实现学生成绩管理数据库的备份与恢复任务1

实现学生成绩管理数据库的备份与恢复数据库的日志主要用来记录数据库的运行情况、日常操作和错误等信息。日志是数据库的重要组成部分，通过分析日志，可以了解MySQL数据库的运行情况、诊断数据库出现的各种问题，为MySQL的管理和优化提供必要的信息。MySQL有以下4种不同类型的日志，各自存储在不同类型的日志文件中。(1)二进制日志:以二进制的形式记录数据库的所有更改数据的操作，可以用于数据恢复和数据复制。(2)错误日志:记录MySQL服务的启动、运行和停止时出现的问题。(3)查询日志:记录MySQL服务器的启动和关闭信息，客户端的连接信息，更新、查询数据记录的SQL语句等;通过查询日志可以了解所有用户的操作。(4)慢查询日志:记录执行时间超过long_query_time指定时间的所有查询或不使用索引的查询;通过工具分析慢查询日志可以定位MySQL服务器性能的瓶颈所在。任务2

使用日志备份和恢复数据8.2.1MySQL日志概述1.启动和设置错误日志功能通过错误日志可以监视系统的运行状态，便于及时发现故障并加以排除。在MySQL数据库中，默认开启错误日志功能。错误日志的启动和停止，都可以通过修改MySQL配置文件my.ini来实现。错误日志记录的信息可以通过log-error和log-warnings来定义，前者用于定义是否启用错误日志功能和错误日志的存储位置，后者用于定义是否将警告信息也记录到错误日志中。配置项如下。[mysqld]log-error=dir/{filename}其中，dir用于指定错误日志的存储路径;filename用于指定错误日志的文件名;省略参数时错误日志文件名默认为hostname.err，存储在MySQL的数据目录下。在my.ini文件中，将log_error选项改为注释行，则会停止错误日志功能。任务2

使用日志备份和恢复数据8.2.2错误日志【例题8.13】将学习成绩管理数据库cjgl的错误日志存放在D:\下，文件名为errorlog.err，警告信息不记录到错误日志中。用文本编辑器打开MySQL配置文件my.ini，在[mysqld]组中添加如下配置信息。[mysqld]#log-error=D:\errorlog.errlog-warnings=02.查看错误日志如果MySQL服务出现异常，可以通过查看错误日志找原因。在MySQL中，可以先通过SHOW命令查看错误日志文件所在的目录及文件名信息，如下所示。mysql>SHOWVARIABLESLIKE'log_error';然后通过文本编辑器打开错误日志文件，找到发生异常的时间和原因，再根据这些信息来解决异常问题。任务2

使用日志备份和恢复数据3.删除错误日志MySQL的错误日志可以直接删除。在运行状态下删除错误日志文件后，MySQL并不会自动创建日志文件，MySQL启动或者执行flushlogs命令时会创建新的日志文件。在服务器端执行mysqladmin命令重新加载日志文件，如下所示。C:\Users\Administrator>mysqladmin-uroot-pflush-logs在客户端登录MySQL数据库，执行flushlogs命令也可以重新加载日志文件，如下所示mysql>flushlogs;任务2

使用日志备份和恢复数据1.启动和设置二进制日志功能可以通过SHOWVARIABLES语句来查看二进制日志功能是否开启。可以通过在MySQL配置文件my.ini中添加log-bin选项来设置和启动二进制日志功能。[mysqld]log-bin=dir/[filename]expire_logs_days=10max_binlog_size=50M其中，dir用于指定二进制日志文件的存储路径，filename用于指定文件名。如果不指定dir和filename参数，二进制日志将默认存储到MySQL数据目录中的hostname-bin.number文件中。文件名后缀.number为.00000n，n是从1开始的自然数。任务2

使用日志备份和恢复数据8.2.3二进制日志2.查看二进制日志(1)查看二进制日志文件列表。(2)查看当前正在写入的二进制日志文件。(3)查看二进制日志文件内容。可以使用MySQL提供的mysqlbinlog命令查看其内容。该命令的语法格式如下。mysqlbinlogfilename.number任务2

使用日志备份和恢复数据【例题8.14】查询成绩管理数据库cigl中的学生表xs，使用mysqlbinlog命令查看数据目录下的二进制日志文件LAPTOP-QM9UJHD3-bin.000001的内容，并将其输出到名为LAPTOP-QM9UJHD3-bin.000001.sql的文件中。查看二进制日志文件LAPTOP-QM9UJHD3-bin.000001的内容，执行如下语句。C:\ProgramData\MySQL\MySQLServer5.7\Data>mysqlbinlogLAPTOP-QM9UJHD3-bin.000001任务2

使用日志备份和恢复数据C:\ProgramData\MySQL\MySQLServer5.7\Data>mysqlbinlogLAPTOP-QM9UJHD3-bin.000001>LAPTOP-QM9UJHD3-bin.000001.sql3.使用二进制日志恢复数据库用户可以使用mysqlbinlog命令利用二进制日志完成基于时间点的恢复工作。其语法格式如下。mysqlbinlog[option]filename|mysql–uroot-p其中，filename为二进制日志文件名。可以指定二进制日志文件的存储路径。option为可选参数，部分参数如下。①--start-date：用于恢复数据库操作的起始时间点。②--stop-date：用于恢复数据库操作的结束时间点。③--start-position：用于恢复数据库操作的起始偏移位置。④--stop-position：用于恢复数据库操作的结束偏移位置。任务2

使用日志备份和恢复数据【例题8.15】使用mysqlbinlog命令恢复MySQL数据库到2022年6月20日10:00:00时的状态。在MySQL的数据目录下找到2022年6月20日10:00:00这个时间点的二进制日志文件LAPTOP-QM9UJHD3-bin.000003，执行如下语句。C:\ProgramData\MySQL\MySQLServer5.7\Data>mysqlbinlog--stop-date=“2022-06-2010:00:00""C:\ProgramFiles\LAPTOP-QM9UJHD3-bin.000003"|mysql–uroot–p任务2

使用日志备份和恢复数据4.停止二进制日志功能在配置文件中设置了log_bin选项之后，MySQL服务器将会一直开启二进制日志功能。可以通过以下两种方法停止该日志功能。(1)注释掉MySQL配置文件中的log_bin选项，重启服务器。(2)通过SETSQL_LOG_BIN语句暂停MySQL二进制日志功能。命令如下。SETSQL_LOG_BIN=0/1;其中，0表示暂停二进制日志功能，1表示启动二进制日志功能。5.删除二进制日志二进制日志中记录着大量的信息，如果长时间不清理，将会占用很大的磁盘空间。在备份MySQL数据库之后，应该删除备份数据库之前的二进制日志。MySQL可以通过指定expire_logs_days的值来设置自动删除二进制日志的天数，也可以手动删除。(1)删除所有二进制日志文件。使用RESETMASTER语句删除所有二进制日志文件，其语法格式如下。RESETMASTER;(2)根据编号删除二进制日志。使用PURGEMASTERLOGS语句可删除指定的二进制日志编号之前的日志文件，其语法格式如下。PURGE{MASTERIBINARY}LOGSTO'filename.number';(3)根据时间删除二进制日志。使用PURGEMASTERLOGS语句可删除指定日期以前的所有二进制日志文件，其语法格式如下。PURGE{MASTERIBINARY}LOGSBEFORE'date';任务2

使用日志备份和恢复数据【例题8.16】使用PURGEMASTERLOGS语句删除比LAPTOP-QM9UJHD3-bin.000003编号小的二进制日志;删除2022-6-2010:00:00之前创建的二进制日志。执行如下语句。PURGEMASTERLOGSTO'LAPTOP-QM9UJHD3-bin.000003';以上语句执行后，比LAPTOP-QM9UJHD3-bin.000003编号小的二进制日志被删除。执行如下语句PURGEMASTERLOGSTO'2022-6-2010:00:00";以上语句执行后，2022-6-2010:00:00之前创建的所有二进制日志被删除。任务2

使用日志备份和恢复数据查询日志用来记录MySQL的所有用户操作，包括启动和关闭MySQL服务、执行查询和更新语句等。1.启动和设置查询日志功能默认情况下，MySQL服务器的查询日志功能是关闭的。可以通过以下命令查看查询日志是否开启。mysql>SHOWVARIABLESLIKE'%general%';如果需要开启查询日志功能，可以通过修改my.ini配置文件来实现。在[mysqld]组中添加log选项来开启查询日志功能，语法格式如下。[mysqld]general_log=ON|OFFlog=dir/filename其中，dir用于指定查询日志文件的存储路径，filename用于指定文件名。如果不指定存储路径和文件名，查询日志将默认存储到MySQL数据目录中的hostname.log文件中。任务2

使用日志备份和恢复数据8.2.4查询日志2.查看查询日志通过查看查询日志，可以了解用户对MySQL进行的操作。查询日志以文本文件的形式存储，可以使用文本编辑器直接打开查看。任务2

使用日志备份和恢复数据3.停止查询日志查询日志功能启动后，可以通过两种方法停止该日志功能。一种是注释掉MySQL配置文件中的相关配置，重启MySQL服务器。另一种是设置MySQL的环境变量general_log为关闭状态，具体语句如下。mysql>SETGLOBALgeneral_log=OFF;mysql>SHOWVARIABLESLIKE'%general_log%'\G;4.删除查询日志在用户频繁使用数据库的情况下，查询日志会增加得很快，这将会占用较大的磁盘空间，影响系统性能。数据库管理员可以定期删除比较早的查询日志以节省磁盘空间。在服务器端执行mysqladmin命令可以创建新的查询日志，新的查询日志会直接覆盖旧的查询日志。该命令的语法格式如下。mysqladmin-uroot-pflush-logs或在客户端登录到MySQL服务器，执行flushlogs语句重新加载日志。mysql>flushlogs;任务2

使用日志备份和恢复数据【例题8.18】备份现有的MySQL查询日志，然后生成新的查询日志文件。先将现有的MySQL查询日志文件复制出来或者改名，再执行mysqladmin命令生成新的查询日志。C:\Users\Administrator>mysqladmin-uroot-pflush-logs除了上述方法之外，还可以手动删除查询日志文件，重启MySQL服务后就会生成新的查询日志文件。1.启动和设置慢查询日志默认情况下，MySQL服务器的慢查询日志功能是关闭的。如果不需要调优，不建议开启慢查询日志功能，以免影响性能。可以通过以下语句查看慢查询日志功