MySQL数据库技术与项目应用教程（微课版）（人邮 ）-教案项目八 维护系统的高可用性.docx

《MySQL数据库技术与项目应用教程（微课版）（人邮 ）-教案项目八 维护系统的高可用性.docx》由会员分享，可在线阅读，更多相关《MySQL数据库技术与项目应用教程（微课版）（人邮 ）-教案项目八 维护系统的高可用性.docx（16页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、适用班级： P.课题工程八维护系统的高可用性课时2+2课时教学目的令会使用数据备份与恢复 数据的高可用性讨论1.哪些因素会引发数据灾难？2.如何保障数据的高可用性？任务1备份和恢复数据知识场景：数据库的备份与恢复是数据库管理最重要的工作之一。系统的意外崩溃或系统硬件的损坏都可能导致数据丧失或损坏，数据库管理员务必定期地备份数据，当数据库中数据出现错误或损坏时，就可能使用已备份的数据进行数据还原。适用班级：P.日志文件M，J*1 11 - i at -3142 #210927 17:11:49 strvtr id 1 nd_log_po 371 CRC32 0x3589c8d4 Tablt.ma

2、p: onlinedb. cart -mapped to number -87*13 # at 3714 #210927 17:11:49 server id 1 end_log_pos 423 CRC32 0xa71032bl Write-rows: table id 87 flags: STMT_END_F5 ttf INSERT INTO *onlinedb.*cart6 ftt SET，7 1#81=198 #-82=19 iit 83=2-10 #84=111 at 423，12 1210927 17:11:49 server id 1 end_log_pos 454 CRC32 0

3、x0elcldb3 Xid = 1113 COMMIT/* !*/;*-解析 第1行：记录了日志文件内的偏移字节值，这里偏移值为314。第2行：包含了事件的日期和时间，MySQL会使用它们来产生时间戳； server记录了服务器的id值.；endog_pos袤示下 事件的偏移面 操作的数 据库和表及表的id值。第3行：同第1行，偏移值为371。 第4行：记录了对id为87的表进行了 wrile_rows操作第5-10行：记录了数据变更操作的语句。该行表示对cart表添加了一条新记录 第13行：提交事务。实例讲解【例8.19】将二进制日志文件logbin.000001的内容，输出到名为mysql

4、_temp.sql文件。mysqlbinlog -vv logbin.000001 mysql_temp.sqi3.从二进制日志中恢复数据mysqlbinlog工具除了可以查看一进制日志文件内容外，还可以将一进制日志文件两个指定时间点之间的所有对数据修改的操作进行恢复。mysqlbinlog 选项二进制日志文件名、 mysql -u用户名-p案例精讲【例8.20】使用mysqlbinlog恢复MySQL数据库到“2019-9-24 09:57:00 n时的状 态。mysqlbinlog -stop-date=,2019-9-24 09:57:00 Hlogbin. 000001 |mysql

5、-root -p适用班级：P.课堂练习【例8.21 使用mysqlbinlog恢复logbin.000001文件中偏移位置从314至454之间的所有操作恢复。学生演示mysqlbinlog -start-position=314 -stop-position=454 -database=onlinedb logbin.000001 | mysql -uroot -p4.删除二进制日志MySQL的二进制日志文件可以配置为自动删除，也可以手动删除(1)使用RESET MASTER语句删除所有二进制日志文件RESET MASTER;(2)使用PUREG MASTER LOGS语句删除指定日志文件PU

6、RGE MASTER | BINARY LOGS TO 二进制日志文件名学习提示RESET MASTER删除所有的二进制日志文件；PURGE MASTER LOGS只删除局部二进制文件案例精讲【例8.22】用PURGE MASTER LOGS删除比logbin.()()()004编号小的日志文件。(1)使用SHOW BINARY LOGS语句查看当前二进制日志文件rvytqi oroot pSHOt bdary logs ：lotjvMB*EncryptedlotbiiL 000001477Xolocbia 0000021798logblA. 000003156Xo3 rors in xt (

7、0.00 w)(2)删除比logbin.00()()()4编号小的日志文件，语句如下PURGE BINARY LOGS TO logbin.000004;(3)再次执行SHOW BINARY LOGS语句查看当前二进制日志文件ygU KKLOGS TO logbitL OOOWr ;Ary 0IC 0 rwc “32 0 02 3) Fl：BIKW LOGS ：Eocrjpted! Mmooow语 f!1 E ：n set SHOW VARIABLES LIKE log_error;I Variable_name|Value|+1 log_errorI.mysql80_error.err |+

8、1 row in set,1warning(0.02sec)xRr) lit) VK(O) .(V)2021 09 27T08 55 19 469443Z 0 (Wrning| |MV 010915NO ZERO.DATE； NO.ZERO_IN_ DATE and ERROR FOR DIVISION,BV ZERO *2021 8 27Tos 55 19 471337Z 0 ISyittm)|MY 010116)|Srv| C Pr09Qm F.IiMySQLMySQL Server 8” (mysqld 8 0 2S)tU2021 09 27To8 55 19 508719Z 1 (Sy

9、stem|MY 013576)IlnnoDBI InnoDB imtialixition has sUrttd2021 09 27T08 55 19 981793Z 1 ISysttmJ|MY 013577)(InnoDei InnoDB imt.alizat.on hat end2021 09 27To8 55 20 197280Z 0 (Systtm) |MY O1B23) |$rv| X Ptuginfor connections Bind address port 330602021 09 27To8 55 20 295001Z 0 (Warning MY 0100681 ($vtr)

10、 CA ctrtifKite p futi2021 09 27T08 55 20 38S968Z 0|MY 010931 |Srv) C XProyam FilsMy5QlMy$Ql Server 8 Obtnmyqld ” rdy for connect2021 09 28T15 03 59 859859Z 0 |Sytn |MY 013105 3 C Pro9r川八MySQlMySQt Server 8 0b.nmyqld txe Normal shutdown 2021 09 28T15 04 00 787872Z 0 (Syitemj |MY 0109101 |Srvtr| C Pro

11、9rm FilsMy$QlMy$Ql Server 8 0bnmyqld ” Shutdown complc3.删除错误日志C:mysqladmin -u root -pPassword flush logs删除错误日志还可以在数据库已登录的客户端重新加载mysqlflush logs;适用班级：P. 通用查询日志查询日志分为通用查询日志和慢查询日志。通用查询日志记载着MySQL的所有用户操作，包括启动和关闭服务、执行查询和更新语句等信息。慢查询日志记载着查询时长超过指定时间的查询信息。1.启动和设置通用查询日志通用查询日志一般是以.log为后缀名的文件在my.ini配置文件中设置如下系统变量

12、来设置mysqldlog_output=none I file I table I file, table general_log=on|offgeneral_log_f ile 1 =日志文件名、实例讲解【例8.24】启用MySQL的通用查询日志，日志文件保存在数据目录下，命名为 mysql80.logomysqldgeneral_log=ongeneral_log_file= *mysql80.log ,保存文件，并重新启动MySQL服务器2.设置通用查询日志输出格式默认情况下通用查询日志输出格式为文本，可以通过设置log_output变量来修改输出类型SET GLOBAL log_out

13、put= none|file|table|file,table实例讲解【例8.27】设置输出通用查询日志格式为table。SET GLOBAL log_output=,tabler;适用班级：P.3.查看通用查询日志实例讲解【例8.28】使用文本编辑器查看MySQL通用查询日志。- 9，*/EZ ，ICAProgram AlMy$QlMy$Ql Server 8.0btnmyxild.exe. Verdon; 8.0.25 (My$(X CommunitySw GPL), sued with: TCP Port: 3306. Mned Pipe: MySQt TimeId Command Ar

14、gument2021-09-28T15204.166911Z 8 Query show variables M grwM咐 2021 09-29T02:4S: 11 923776Z8 Query SELECT DATABASE。2021 -09-29102:45:11 924792Z8 Init DB onbnedb2021 9 29T02:4$-21.27504W8 Query sekct from2021 -09 29T02:46i50.303485Z8 Query SET GLOBAL 够Mputabiec m( a当通用查询日志输出为数据表时，可以通过查询mysql数据库中的mysql

15、80.log表查看数据库的操作情况。课堂练习【例8.29查询mysql数据库的mysq180表的记录信息。(1 )首先使用DESC语句查看mysq180表的结构DESC mysql. mysql80;(2)使用SELECT语句查询日志表中操作类型和操作内容。SELECT command_type ,argument FROM mysql. mysql80;慢查询日志慢查询日志，顾名思义就是记录执行比拟慢查询的日志。1.启动和设置慢查询日志MySQL中慢查询日志默认是关闭的，假设需要开启慢查询日志，同样可以修改系统配置文件my.iniomysqldlog-slow-queries=path/fi

16、lenamelong_query_time=nlog-queries-not-using-indexes=ON|OFF适用班级：P.【例8.30】启用MySQL的慢查询日志，日志文件保存在d盘根目录下，命名为slowog,记录查询时间超过5秒或未使用索引的查询。mysqldlog-slow-queries=d:/sql_slow.logong_query_time=5iog-queries-nol-using-indexes=ON2.查看慢查询日志C:.匕 n f e71ath* Ijb0 - tfbinmysqld.exe. Version: 8.0.25 (MySQL Community

17、STCP Port: 3306, Named Pipe: MySQLTimeId Command Araument Time: 2021 -10-11T08:59:13.922563Z UserHo$t: rootroot localhost (:1 Id: 12# Query time: 0.000465 Lock time: 0.000202 Rows sent: 4 Rows examined: 12 use onlinedb;SET timestamp= 1633942753;select uloqin.uname.uqender from users where ucitv =长沙；

18、Mil Time: 2021-10-11 T09:00:39.227S04Z# User(SHost root(root) localhost (:1 Id: 12# Query time: 10.008542 Lock time: 0.000000 Rows sent: 1 Rows examined: 1SET tim6tamp= 1633942829;一select sleep(IO);3.删除慢查询日志和通用查询日志一样，慢查询日志也可以直接删除。删除后在不重启服务器的情况下，需要执行mysqladmin -u root -pPassword flush-logs重新生成日志文件，或者

19、在客户端登录到服务器执行flush logs语句重建日志文件习题+实战备份和恢复数据(1)分别使用Navicat工具和mysqldump命令备份onlinedb数据库。(2)分别使用Navicat工具和mysql命令恢复onlinedb数据库。(3)使用SELECTINTOOUTFILE语句导出onlinedb.goods表中的数据，导(4)使用 LOAD DATA 语句导入 goods.txt 数据到 onlinedb.goods 1 表。适用班级：MySQL日志(5)设置启动二进制日志,指定文件名为logbin.OOOOOL并使用mysqlbinlog命令查看该文件。(6)为goods 1

20、表添加一条记录，然后删除goods 1表，使用mysqlbinlog工具恢复goods 1表在删除记录之前的数据。P.适用班级：P.本任务讨论如下内容。带着问题进数据备份的类型有哪些？入本任务学 如何进行数据备份？习 如何进行数据恢复？数据备份阐述各备份数据备份就是对应用数据库建立相应副本，包括数据库结构、对象及数据。的策略完全备份比照之间的增量备份优缺点 差异备份1.使用Navicat图形工具备份数据备份步骤略学习提示不同版本的Navicat可视化备份文件的扩展名为不同，本例使用的Navicatl5的扩展名为nb3,不同的操作系统下生成的文件不同，且不能兼容，跨操作系统的备份和恢复不建议使用

21、该方式。2.使用mysqldump命令备份数据库mysqldump命令是MySQL提供的实现数据库备份工具，在windows控制台的命令行窗口中执行。该文件存放在MySQL安装目录的bin文件夹下。 使用mysqldump备份一个数据库使用mysqldump命令可以备份一个数据库，也可以备份多个数据库，还可以 备份一个连接实例中的所有数据库。其语法格式主要有以下三种。# 1.备份单个数据库或指定表mysqldump 1选项 1数据库名表名I表名2. 脚本文件名# 2.备份多个数据库mysqldump 1选项J -databases数据库名1 1数据库名2. 脚本文件名# 3.备份服务器所有数据

22、库mysqldump 1 选项1 all-databases 脚本文件名适用班级：P.备份选项讲选项名称缩写说明解-host-h服务器IP地址，假设为本机，可省略-user-uMySQL登录用户名-password-P登录用户密码port-P服务器商品号，缺省时默认3306-lock-tables备份前锁定所有数据表-force当备份出现错误时，继续执行操作-default-character-set设置默认字符集-add-locks备份表时锁定表-comments添加注释信息实例精讲【例8.2使用root用户备份onlinedb数据库下的goods表和uers表，并将备份好的文件保存到D盘根

23、目录，文件名为users_bak.sqLmysqldump -uroot -p onlinedb goods users D:users_bak.sql【例8.3使用root用户备份onlinedb数据库和mysql数据库。mysqldump -uroot -p -database onlinedb mysql D:dbs_bak.sql课堂练习【例8.4使用root用户备份该服务器下的所有数据库学生演示mysqldump -uroot -p -all-database D:alldb_bak.sql数据恢复数据恢复就是将数据库的副本加载到数据库管理系统中。演示操作1.使用Navicat图形工

24、具恢复数据操作步骤略2.使用mysql命令恢复数据mysql -口用户名-p密码 1数据库名脚本文件名实例讲解【例8.6使用mysql命令将脚本文件“users_bak.sql”还原成数据库test中。mysql -uroot -p test SOURCE d:/users_bak.sql数据导出MySQL中数据可以导出到外部存储文件中，可以导出成文本文件、XML文件或者HTML文件等。演示操作1.使用Navicat图形工具导出数据操作步骤略2.使用SELECT.INTO OUTFILE语句导出数据SELECT列名FROM 表名WHERE条件表达式INTO OUTFILE 4目标文件名， OP

25、TIONS实例讲解例8.8使用SELECT.INTO OUTFILE语句导出onlinedb数据库中的goods表中的数据。其中，字段之间用“、”隔开，字符型数据用双引号分隔。SELECT * FROM GOODSINTO OUTFILE D:goods.txtFIELDS TERMINATED BY 、，OPTIONALLY ENCLOSED BY LINES TERMINATED BY rn*;3.使用mysqldump命令导出数据（1）将数据导出为文本文件mysqldump -u root -pPassword -T 目标目录 dbname table options适用班级：P.实例讲

26、解【例8.9使用mysqldump命令导出onlinedb数据库中users表的数据,要求字段之间使用逗号“，”间隔，字符类型字段值用双引号分隔，记录以回车换行符“rn结尾。mysqldump -uroot -p88888888 -T D: onlinedb users fieldsterminaled-by=,“-fields-oplionally-enclosed-by= linesterminated-by=rn学习提示导出的表脚本及数据默认以表名作为主文件名。（2）将数据导出为XML文件实例讲解【例8.10使用mysqldump命令导出onlinedb数据库中goodstype表的数据

27、，要求输出文件为xml格式。mysqldump -u root -p88888888 -X onlinedb goodstype D:goodstype.xml导出结果1分析乂RVP WWlCIdatabase rwcxc=onlinedb”field Field=*tID* Type=*lnt(lD*Bxtro=*wto increment* Cgfield Field=*tN3xe* Type=*varchar(100)* Hull= ifES* Xey=* Extras* Cement=fkey Table=*eoodstype*Key_n3ne=PRIIARY SeQ_in.ii*ie

28、i=*l* Coluxroptions Haje=*sootetype* En8lne=*InnoDB* Ver8ion=*10* Row_fonQ=CwxQ Roe*pe*l/fielS；tr2/fielfield皿)零食5,59*4/fiel取ieldS,B4.使用mysql命令导出数据实例讲解【例8.11使用mysql命令来导出onlinedb数据库中的orders表中的数据。mysql -u root -p88888888 -e SELECT * FROM orders onlinedbD:orders.txt学习提示使用mysql命令导出的文本文件时，不需要指定数据分隔符。文件中自动

29、使用了制表符分隔数据，并且自动生成了列名。适用班级：P.课堂练习【例8.12使用mysql命令导出onlinedb数据库中orders表的数据，生成HTML文件。mysql -u root -p88888888 -H “SELECT * FROM orders onlinedbD:orders.html数据导入操作演示1 .使用Navicat图形工具导入数据操作步骤略2.使用LOAD DATA INFILE语句导入数据LOAD DATA INFILE语句用于从外部存储文件中读取行，并导入到数据库的某个表中。【例8.14使用LOAD DATA INFILE语句将D盘根目录下goods.txt文件

30、中的数据导入至数据库onlinedb中goods表中。LOAD DATA INFILE D:goods.txtINTO TABLE onlinedb.goodsFIELDS TERMINATED BY 、 OPTIONALLY ENCLOSED BYLINES TERMINATED BY在导入数据时，为了防止主键冲突，可以使用REPLACE INTO TABLE直接将数据进行替换来实现数据的导入或恢复。3.使用mysqlimport命令导入数据mysqlimport用来将外部文件导入到数据库中。mysqlimport -u root -pPassword dbname filename.txt

31、 OPTIONS其中dbname表示目标表所在的数据库；OPTIONS为可选参数，为导入数据指定分隔符，其释译与导出数据相同。适用班级：P.实例讲解【例8.15使用mysqlimport命令将D盘根目录中文件名为users.txt的文件数据导入到数据库onlinedb中的users表中。mysqlimport -u -root -p88888888 onlinedb D:users.txt - fields-terminated-by=,/-fields-optionally-by=n -lines-terminated-by=rn;任务2使用日志备份和恢复数据任务场景数据库日志是数据管理中重

32、要的组成局部，它记录了数据库运行期间发生的任何变化，用来帮助数据库管理员追踪数据库曾经发生的各种事件。本任务讨论如下内容。带着问髅进 MySQL有哪些日志类型，分别担负的功能是什么？入本任务学如何根据日志分析数据库的状态及问题？习 如何使用日志文件进行数据恢复？ MySQL日志概述在数据库领域，日志就是将数据库中的每一个变化和操作时产生的信息记载到一个专用的文件里，这种文件就叫做日志文件。略讲概念 二进制日志：以二进制文件的形式记录数据库中所有更改数据的语句。 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题。 查询日志：又分为通用查询日志和慢查询日志。其中通用查询日志

33、记录建立的客户端连接和记录查询的信息；慢查询日志记录所有执行时间超过long_query_time的所启直询或不使用索引的查询。除二进制日志外，所有日志文件都是文本文件。日志文件通常存储在MySQL数据库的数据目录下。只要日志功能处于启用状态，日志信息就会不断地被写入相应的日志文件中。适用班级：P.学习提示默认情况下，MySQL服务器只启动了错误日志功能，其它日志类型都需要数据库管理员进行配置。二进制日志二进制日志记录了所有的DDL语句和DML语句对数据的更改操作。二进制日志文件主要包括如下两类文件。二进制日志索引文件：用于记录所有的二进制文件，文件名后缀为.index 二进制日志文件：用于记

34、录数据库所有的DDL语句和DML语句的事件，文件名后缀为.OOOOOn, n是从1开始的自然数。1.启动和设置二进制日志在配置文件my.ini中与二进制日志相关的参数在mysqld组中设置，主要参数如下。mysqldlog-bin =path/filename expire_Iogs_days= 10 max_binlog_size= 1 OOM实例精讲【例8.16】启动mysql的二进制日志，二进制日志文件存放在MySQL的安装目录，并查看日志设置。(1 )在my.ini配置文件中的mysqld组下添加如下语句log-bin=logbin(2)重新启动MySQL服务(3)执行SHOW VAR

35、IABLES语句查看日志设置1t1Z，9 lattfus uu；WUbto_MM IOUIrHL how1JMQL let.*UUlFMtCff1,f_fTT*fr. wttlW.rrar.10C.rrcv_wrTtcM!oc_aiat_lMraa!:nuImIffIX rw la wt. 1 muw - wr适用班级：P.当log_bin变量的值为ON,表示二进制日志已经开启。学习提示数据库文件和日志文件最好不要放在同一磁盘驱动器上，当数据库磁盘发生故障时，可以使用日志文件恢复数据。2.读取二进制日志(1)使用SHOW BINARY LOGS语句查看二进制日志个数及文件名实例精讲【例8.17

36、】用SHOW BINARY LOGS语句查看当前二进制日志个数及文件信息SHOW BINARY LOGSnysql SHOW BINARY LOGS； -i -! Log_nanei File_size ! Iogbin.000001 !107 !1 row in set (2)使用mysqlbinlog查看二进制日志内容MySQL提供的mysqlbinlog的工具可以查看二进制日志文件的具体内容实例精讲【例8.18使用mysqlbinlog命令查看二进制日志文件Iogbin.000001的具体内容。mysqlbinlog -v logbin.000001- XC：Vcd C:Progrfta

37、Oata M：r$QLMySCC Server 8.0 DataC: ProfraaDtMy$QlMy$(X. S-rv” 8. O0atysqlbinlog v lotbin 000001?S0S30 SET OtSESSIOS PSEIDO SUE150003 SET 0LD_OMtTWN -YF=taFLTIW_TYre. C(WLni0N_TE=O ;XLIMITER /!/：B At 4R210927 16:55:20 server id I125 C1C32 Ox仪lAf79 Start: binlot v 4. srv” v 8 0.25 crated 210927 16:55:20 a： startup2this binlog is tither ic use or Tas dot closed properly.WLtaACK !/;BIXLOG I ZRY8aAAAA2UAHaAAABAA30C4U j 11 M