MySQL数据库技术与项目应用课件 项目8 维护网上商城系统的高可用性

模块概览维护网上商城系统的高可用性MySQL数据库技术与项目应用08引言/FOREWORD数据是信息系统运行的基础和核心。随着信息技术的普及，越来越多的数据都保存到数据库中，数据的高可用性也随之受到人们的高度关注。用户操作错误、存储介质损坏、黑客入侵、服务器故障、计算机病毒或自然灾害等不可抗拒因素都可能导致数据丢失，从而引起灾难性后果。因此必须对数据库系统采取必要的措施，以保证在发生故障时，可以将数据库恢复到最新的状态，将数据损失降低到最小。目录/Contents01备份和恢复数据02使用日志备份和恢复数据任务1

备份和恢复数据数据库的备份与恢复是数据库管理最重要的工作之一。用户误操作、黑客入侵、计算机病毒、服务内部故障或系统硬件的损坏等都可能导致数据丢失或损坏，数据库管理员务必定期地备份数据，当数据库中的数据出现了错误或损坏时，就可以使用已备份的数据进行数据还原，以降低因数据丢失造成了损失。本任务将介绍数据库备份与恢复、数据的导入与导出等操作方法。任务场景备份网上商城系统数据数据备份概述使用NavicatPremium图形工具备份数据使用mysqldump工具备份数据库数据备份概述数据备份就是对应用数据库建立相应副本，包括数据库结构、对象及数据。根据备份数据集合的范围来划分，数据备份分为：完全备份：是指某一个时间点上的所有数据或应用进行的一个完全拷贝，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。增量备份：是备份数据库的部分内容，包含自上一次完整备份或最近一次增量备份后改变的内容。差异备份：是指在一次全备份后到进行差异备份的这段时间内，对那些增加或者修改文件的备份，在进行恢复时，只需对第一次全备份和最后一次差异备份进行恢复。备份类型优

点缺

点完全备份备份数据完整，恢复操作简单各个全备份中数据大量重复，且一次备份所需时间长增量备份没有重复的备份数据，备份所需的时间很短恢复数据较麻烦，操作员必须把每一次增量的结果逐个按顺序进行恢复；每个增量数据构成一个链，恢复时缺一不可。恢复时间长差异备份比完全备份需要时间短、节省磁盘空间；恢复操作比增量备份步骤少、恢复时间短

使用NavicatPremium图形工具备份数据【例8.1】使用Navicat图形工具备份数据库onlinedb。学习提示：Navicat15图形工具生成的备份文件扩展名为nb3，不同的操作系统下生成的文件有可能不同，且不能兼容，通常跨操作系统的备份与恢复不建议使用该方式。

使用mysqldump工具备份数据库使用mysqldump命令可以备份一个数据库，也可以备份多个数据库，还可以备份一个连接实例中的所有数据库。#1.备份单个数据库或指定表mysqldump[选项]数据库名[表名1[表名2…]]>脚本文件名#2.备份多个数据库mysqldump[选项]–-databases数据库名1[数据库名2…]>脚本文件名#1.备份服务器所有数据库mysqldump[选项]--all-databases>脚本文件名选项名称缩写说明--host-h服务器IP地址，若为本机，可省略--user-uMySQL登录用户名--password-p登录用户密码--port-P服务器商品号，缺省时默认3306--lock-tables

备份前锁定所有数据表--force

当备份出现错误时，继续执行操作--default-character-set

设置默认字符集--add-locks

备份表时锁定表--comments

添加注释信息使用mysqldump工具备份数据库【例8.2】使用root用户备份onlinedb数据库下的goods表和users表，并将备份好的文件保存到D盘根目录，文件名为users_bak.sql。【例8.3】使用root用户备份onlinedb数据库和mysql数据库。【例8.4】使用root用户备份该服务器下的所有数据库。mysqldump–uroot–ponlinedbgoodsusers>D:\users_bak.sqlEnterpassword:******mysqldump–uroot–p–databaseonlinedbmysql>D:\dbs_bak.sqlEnterpassword:******mysqldump–uroot–p––all-database>D:\alldb_bak.sqlEnterpassword:******恢复数据数据恢复是数据备份相应的系统维护和管理操作，当数据库出现故障时，需要将备份好的数据库文件加载到MySQL服务器中，从而恢复数据库。在进行数据恢复时，服务器会先执行系统安全检查，包括检查要恢复的数据库是否存在、数据库文件是否兼容等。使用Navicat图形工具恢复数据使用mysql工具恢复数据使用SOURCE语句恢复数据使用Navicat图形工具恢复数据【例8.5】使用Navicat图形工具，还原备份文件onlinedb_bak.nb3到数据库中。使用mysql工具恢复数据当脚本文件中包含CREATE、INSERT语句数据定义和数据操纵语句时，可以使用mysql工具进行数据恢复。【例8.6】使用mysql命令将脚本文件“users_bak.sql”还原成数据库test中。mysql–u用户名–p密码[数据库名]<脚本文件名mysql–uroot–ptest<D:\users_bak.sqlEnterpassword:******使用SOURCE语句恢复数据若用户成功登录MySQL服务器后，也可以使用SOURCE语句恢复数据。【例8.7】使用SOURCE语句实现【例8.6】的操作。SOURCE脚本文件名mysql>USEtestDatabaseschangedmysql>SOURCEd:/users_bak.sql数据导出MySQL数据库中不仅提供数据库的备份和恢复方法，还可以直接通过导出数据实现对数据的迁移。MySQL中数据可以导出到外部存储文件中，可以导出成文本文件、XML文件或者HTML文件等。使用Navicat图形工具导出数据使用SELECT…INTOOUTFILE语句导出数据使用mysql工具导出数据使用Navicat图形工具导出数据【例8.7】导出onlinedb数据库中的goods表中的数据，要求导出文件格式为文本文件。使用SELECT…INTOOUTFILE语句导出数据使用SELECT…INTOOUTFILE语句也可以将查询结果集导出到文本文件。SELECT列名FROM表名[WHERE条件表达式]INTOOUTFILE'目标文件名'[选项]

FIELDSTERMINATEDBY'value'：设置字符串为字段的分隔符，默认值是“\t”。

FIELDS[OPTIONALLY]ENCLOSEDBY'value'：设置字段的分隔字符，只能为单个字符，如果使用了OPTIONALLY则只有CHAR和VERCHAR等字符数据字段被包括。

FIELDSESCAPEDBY'value'：设置转义字符，默认值为“\”。

LINESSTARTINGBY'value'：设置每行数据开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。

LINESTERMINATEDBY'value'：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”。

使用SELECT…INTOOUTFILE语句导出数据【例8.8】使用SELECT…INTOOUTFILE语句导出onlinedb数据库中的goods表中的数据。其中，字段之间用“、”隔开，字符型数据用双引号分隔。mysql>SELECT*FROMgoods->INTOOUTFILE'D:\goods.txt'->FIELDSTERMINATEDBY'\、'OPTIONALLYENCLOSEDBY'\"'->LINESTERMINATEDBY'\r\n';使用mysql工具导出数据mysql工具不仅可以用来登录服务器、还原备份文件，它还可以将查询结果导出为文本文件、XML文件或HTML文件。【例8.9】使用mysql工具导出onlinedb数据库中的users表中的数据。【例8.10】使用mysql命令导出onlinedb数据库中users表的数据，生成HTML文件。mysql–u用户名–p[选项]”SELECT语句”数据库名>目标文件mysql–uroot–p–e”SELECT*FROMusers”onlinedb>D:\users.txtEnterpassword:*******mysql–uroot–p-H”SELECT*FROMusers”onlinedb>D:\users.htmlEnterpassword:*******数据导入MySQL允许将数据导出到外部文件，也可以将符合格式要求的外部文件导入到数据库中。MySQL提供了丰富的导入数据工具，包括图形工具、LOADDATAINFILE语句等。使用Navicat图形工具导入数据使用LOADDATAINFILE语句导入数据使用Navicat图形工具导入数据【例8.11】使用Navicat图形工具，将users.txt文件中的数据导入到onlinedb数据库的users表中。使用LOADDATAINFILE语句导入数据LOADDATAINFILE语句用于从外部存储文件中读取行，并导入到数据库的某个表中。【例8-12】使用LOADDATA语句将例8-6导出的goods.txt文件中的数据导入至数据库onlinedb中goods表中。LOADDATAINFILE文件名INTOTABLE目标表名[选项][IGNOREnLINES]

“选项”为可选参数，为导入数据指定分隔符，其释义与导出数据相同。

IGNOREnLINES：表示忽略文件开始处的行数，n表示忽略的行数mysql>LOADDATAINFILE'D:\goods.txt'->INTOTABLEonlinedb.goods->FIELDSTERMINATEDBY'、'OPTIONALLYENCLOSEDBY'\"'->LINESTERMINATEDBY'\r\n';任务2

使用日志备份和恢复数据数据库日志是数据管理中重要的组成部分，它记录了数据库运行期间发生的任何变化，用来帮助数据库管理员追踪数据库曾经发生的各种事件。当数据库遇到意外损害或是出错时，可以通过对日志文件进行分析查找出错原因，也可以通过日志记录对数据进行恢复。MySQL提供的二进制日志、错误日志和查询日志文件，它们分别记录着MySQL数据库不同操作的痕迹。本任务主要阐述各种日志的作用和使用方法，以及使用二进志日志文件恢复数据。任务场景MySQL日志概述日志就是将数据库中的每一个变化和操作时产生的信息记载到专用的文件中。二进制日志以二进制文件的形式记录数据库中所有更改数据的语句。错误日志记录MySQL服务的启动、运行或停止MySQL服务时出现的问题。查询日志通用查询日志：记录建立的客户端连接和记录查询的信息。慢查询日志：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。使用日志可以帮助用户提高系统的安全性，加强对系统的监控，便于对系统进行优化，建立镜像机制和让事务变得更加安全。二进制日志二进制日志记录了所有的数据定义语句（DDL语句）和数据操纵语句（DML语句）对数据库的更改操作。语句以“事件”的形式保存，它描述了数据的更改过程。二进制日志文件分类二进制日志索引文件用于记录所有的二进制文件，文件名后缀为.index。二进制日志文件用于记录数据库所有的DDL语句和DML（除了SELECT操作）语句的事件，文件名后缀为.00000n，n是从1开始的自然数。启动和设置二进制日志

二进制日志是关闭的，可以通过修改MySQL的配置文件my.ini来启动和设置二进制日志。在配置文件my.ini中与二进制日志相关的参数在[mysqld]组中设置[mysqld]log-bin[=path/[文件名]]expire_logs_days=10max_binlog_size=100Mlog-bin：设置开启二进制日志。path：表明日志文件所在的目录路径，在目录的文件夹命名中不能有空格，否则在访问日志时会报错。expire_logs_days：表示定义了MySQL清除过期日志的时间，即二进制日志自动删除的天数。默认值为0，表示没有自动删除。max_binlog_size：表示定义了单个文件的大小限制，启动和设置二进制日志【例8.13】启动mysql的二进制日志，将日志文件存放在MySQL的数据目录中，并查看日志设置。

步骤1：在my.ini配置文件中的[mysqld]下添加如下语句，并保存。步骤2：重新启动MySQL服务步骤3：执行SHOWVARIABLES语句查看日志设置步骤4：查看二进制日志文件存储路径log-bin="logbin"读取二进制日志使用SHOWBINARYLOGS语句查看二进制日志个数及文件名【例8.14】使用SHOWBINARYLOGS语句查看当前二进制日志个数及文件信息。mysql>SHOWBINARYLOGS;+---------------+-----------+-----------+|Log_name|File_size|Encrypted|+---------------+-----------+-----------+|logbin.000001|156|No|+---------------+-----------+-----------+1rowinset(0.00sec)读取二进制日志使用mysqlbinlog查看二进制日志内容。【例8.15】使用mysqlbinlog命令查看二进制日志文件logbin.000001的具体内容。（1）设置二进制日志文件所在的目录设置为当前目录。（2）使用mysqlbinlog查看日志文件。mysqlbinlog[选项]"二进制日志文件"cdC:\ProgramData\MySQL\MySQLServer8.0\datamysqlbinlog-vlogbin.000001从二进制日志中恢复数据mysqlbinlog工具除了可以查看二进制日志文件内容外，还可以将二进制日志文件两个指定时间点之间的所有对数据修改的操作进行恢复。mysqlbinlog[选项]"二进制日志文件名"|mysql–u用户名–p

--start-date：恢复数据操作的起始时间点。

--stop-date：恢复数据操作的结束时间点。

--start-position：恢复数据操作的起始偏移位置。

--stop-position：恢复数据操作的结束偏移位置从二进制日志中恢复数据【例8.17】使用mysqlbinlog恢复MySQL数据库到“2019-9-2409:57:00”时的状态。（1）首先，在存放二进制日志文件的目录下找到“2019-9-2409:57:00”时间点的日志文件对应为logbin.000001。（2）打开Windows命令行窗口，将二进制日志文件所在的目录设置为当前目录。（3）在命令窗口中输入如下命令。（4）根据提示输入root用户的登录密码。mysqlbinlog--stop-date="2019-9-2409:57:00""logbin.000001"|mysql–uroot–pEnterpassword:******从二进制日志中恢复数据【例8.18】使用mysqlbinlog恢复logbin.000001文件中偏移位置从314至454之间的所有操作恢复。（1）在Windows命令窗口中输入如下命令。（2）根据提示输入root用户的登录密码。Mysqlbinlog--start-position=314--stop-position=454--database=onlinedblogbin.000001|mysql-uroot-pEnterpassword:******删除二进制日志使用RESETMASTER语句删除所有二进制日志文件使用PUREGMASTERLOGS语句删除指定日志文件PURGE{MASTER|BINARY}LOGSTO"二进制日志文件名"PURGE{MASTER|BINARY}LOGSBEFORE'时间点'RESETMASTER;删除二进制日志【例8.19】使用PURGEMASTERLOGS删除比logbin.000003编号小的日志文件。（1）使用SHOWBINARYLOGS语句查看当前二进制日志文件。（2）删除比logbin.000003编号小的日志文件。（3）再次执行SHOWBINARYLOGS语句查看当前二进制日志文件mysql>PURGEBINARYLOGSTO'logbin.000003';错误日志错误日志记载着MySQL服务器数据库系统的诊断和出错信息，包括MySQL服务器启动、运行和停止数据库的信息以及所有服务器出错信息。启动和设置错误日志查看错误日志删除错误日志启动和设置错误日志错误日志文件默认存放在MySQL服务器的data目录下，文件名默认为主机名.err。错误日志的启动和停止及日志文件名，都可以通过修改my.ini来配置。[msqld]log-error=[path/[file_name]]学习提示：若想关闭数据库错误日志功能，只需注释log-error参数行。查看错误日志通过错误日志可以监视系统的运行状态，便于及时发现故障、修复故障。MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看错误日志。【例8.20】查看MySQL的错误日志。mysql>SHOWVARIABLESLIKE'log_error';+---------------+----------------------+|Variable_name|Value|+---------------+---------------------+|log_error|.\mysql80_error.err|+---------------+---------------------+1rowinset,1warning(0.02sec)删除错误日志由于错误日志是以文本格式存储的，所以可以直接删除。在运行状态下删除错误日志文件后，MySQL并不会自动创建日志文件，需要使用flushlogs重新加载。用户可以在服务器端执行mysqladmin命令重新加载。可以在客户端登录MySQL数据库执行flushlogs语句重新加载mysqladmin–uroot–pflushlogsEnterpassword:******mysql>flushlogs;通用查询日志查询日志分类通用查询日志记载着MySQL的所有用户操作，包括启动和关闭服务、执行查询和更新语句等信息；通用查询日志一般是以.log为后缀名的文件，如果没有在my.ini文件中指定文件名，就默认主机名为文件名。慢查询日志记载着查询时长超过指定时间的查询信息。启动和设置通用查询日志设置通用查询日志输出格式查看通用查询日志启动和设置通用查询日志若需要开启通用查询日志，可以通过修改系统配置文件my.ini来开启。与二进制日志和错误日志类似，需要在my.ini文件的[mysqld]组下加入log选项设置。通用查询日志也可以在my.ini配置文件中设置如下系统变量来设置。[mysqld]log=[文件路径/[日志文件名]][mysqld]log_output=[none|file|table|file,table]general_log=[on|off]general_log_file[=日志文件名]启动和设置通用查询日志【例8.21】启用MySQL的通用查询日志，日志文件保存在数据目录下，命名为general_log。【例8.22】使用SHOWVARIABLES语句查看通用查询日志的系统变量。[mysqld]general_log=ongeneral_log_file='mysql80.log'mysql>SHOWVARIABLESLIKE'general%';+------------------+--------------+|Variable_name|Value|+------------------+--------------+|general_log|ON||general_log_file|mysql80.log|+------------------+--------------+2rowsinset,1warning(0.00sec)设置通用查询日志输出格式默认情况下通用查询日志输出格式为文本，可以通过设置log_output变量来修改输出类型。【例8.24】设置输出通用查询日志格式为table。SETGLOBALlog_output=[none|file|table|file,table]mysql>SETGLOBALlog_output='file';QueryOK,0rowsaffected(0.01sec)

mysql>SHOWVARIABLESLIKE'log_output';+---------------+-------+|Variable_name|Value|+---------------+-------+|log_output|FILE|+---------------+-------+1rowinset,1warning(0.01sec)查看通用查询日志查看通用查询日志，数据库管理员可以清楚地知道用户对MySQL进行的所有操作。当通用查询日志输出为文本格式时，只需使用文本编辑器打开相应的日志文件即可。【例8.25】使用文本编辑器查看MySQL通用查询日志。慢查询日志慢查询日志主要用来记录执行较慢查询的日志文件。通过对慢查询日志进行分析，可以找出执行时间较长、执行效率较低的语句，并对其进行优化。启动和设置慢查询日志查看慢查询日志删除慢查询日志启动和设置慢查询日志若需要开启慢查询日志，同样可以修改系统配置文件my.ini。在my.ini文件的[mysqld]组下加入慢查询日志的配置选项，即可以开启慢查询日志。[mysqld]slow-query-log=[ON|OFF]slow_query_log_file=[path/[慢日志文件名]]long_query_time=nlog-queries-not-using-indexes=[ON|OFF]

slow-query-log：值为ON时，开启慢查询日志。

slow_query_log_file：代表MySQL慢查询的日志存储目录，如果不指定目录和文件名，默认存储在数据目录中，文件名hostname-slow.log，hostname是MySQL服务器的主机名。

long_query_time=n：表示查询执行的阈值。n为时间值，单位是s，默认时间为10s。当查询超过执行的阈值时，查询将会被记录。

log-queries-not-using-indexes：值为ON时，将没有使用索引的查询记录在日志中。启动和设置慢查询日志【例8.26】启用MySQL的慢查询日志，日志文件保存MySQL数据目录下，命名为mysql80-slow.log，记录查询时间超过5秒或未使用索引的查询。[mysqld]slow-query-log=ONslow_query_log_file=mysql80-slow.loglong_query_time=5log-queries-not-using-indexes=ON学习提示：日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源。如果需要启用慢查询日志，又想获得更高的系统性能，建议优先将日志记录到文件。查看慢查询日志在慢查询日志中，记录着执行时间较长的查询语句，用户可以从慢查询日志中获取执行效率较低的查询语句，为查询优化提供重要依据。删除慢查询日志慢查询日志可以直接删除。删除后在不重启服务器的情况下，需要在mysql客户端执行flushlogs语句重建日志文件，或者CMD命令提示符下执行下列语句。mysqladmin–u用户名–pflush-logs项目实训实践任务（1）创建索引、查看索引和维护索引。（2）创建视图、管理和维护视图以及使用可更新视图。（3）写出各种高效数据查询的方法。实践内容（1）使用Navicat图形工具在onlinedb.goodstype表的tName列上创建一个为IX_tName的普通索引名。（2）使用SQL语句在onlinedb.goods表的gdCode和gdName列上创建一个名为IX_gdCN的复合索引。（3）分别使用SHOWCREATETABLE命令和SHOWINDEXFROM/SHOWKEYSFROM命令查看（2）中所创建的索引IX_gdCN的相关信息。（4）使用SQL语句删除（1）和（2）创建的索引。AI助学：制定应用系统的数据库维护方案应用场景：网上商城系统承载着用户订单、支付记录等核心业务数据，一旦发生误操作删表、服务器硬件故障或勒索病毒攻击，数据丢失的代价不可估量。数据库管理员不仅要制定科学合理的备份计划，还要能在数据故障发生后快速完成恢复，同时借助各类日志精准定位问题根源。本节将使用AI辅助你设计全量与增量相结合的备份策略，规划基于二进制日志的时间点恢复方案，帮助你建立数据库高可用的系统化运维思路。AI助学：制定应用系统的数据库维护方案AI辅助制定完整备份策略【