《MySQL數(shù)據(jù)庫應(yīng)用案例教程》651-5（馬潔）教案 第20課 MySQL日志管理

PAGE10PAGE10PAGE9PAGE9

課題MySQL日志管理課時2課時（90min）教學(xué)目標(biāo)知識技能目標(biāo)：（1）了解什么是MySQL日志（2）掌握設(shè)置、查看和刪除錯誤日志的方法（3）掌握開啟、設(shè)置、查看和刪除二進(jìn)制日志的方法（4）掌握開啟、設(shè)置、查看和刪除通用查詢?nèi)罩镜姆椒ǎ?）掌握開啟、設(shè)置、查看和刪除慢查詢?nèi)罩镜姆椒ㄋ刭|(zhì)目標(biāo)：（1）培養(yǎng)定期自我復(fù)盤總結(jié)的習(xí)慣，保持積極向上的學(xué)習(xí)態(tài)度（2）懂得“害人之心不可有，防人之心不可無”的道理教學(xué)重難點(diǎn)教學(xué)重點(diǎn)：了解什么是MySQL日志教學(xué)難點(diǎn)：掌握設(shè)置、查看和刪除錯誤日志的方法教學(xué)方法案例分析法、問答法、討論法、講授法、實踐法教學(xué)用具電腦、投影儀、多媒體課件、教材教學(xué)設(shè)計第1節(jié)課：→→→傳授新知（38min）第2節(jié)課：→傳授新知（20min）→課堂實訓(xùn)（15min）→課堂小結(jié)（3min）→作業(yè)布置（2min）教學(xué)過程主要教學(xué)內(nèi)容及步驟設(shè)計意圖第一節(jié)課課前任務(wù)【教師】布置課前任務(wù)，和學(xué)生負(fù)責(zé)人取得聯(lián)系，讓其提醒同學(xué)通過文旌課堂APP或其他學(xué)習(xí)軟件，預(yù)習(xí)本節(jié)課要學(xué)習(xí)的知識【學(xué)生】完成課前任務(wù)通過課前任務(wù)，使學(xué)生預(yù)習(xí)本節(jié)課要學(xué)習(xí)的知識，增加學(xué)生的學(xué)習(xí)興趣考勤（2min）【教師】使用文旌課堂APP進(jìn)行簽到【學(xué)生】班干部報請假人員及原因培養(yǎng)學(xué)生的組織紀(jì)律性，掌握學(xué)生的出勤情況問題導(dǎo)入（5min）【教師】提出以下問題：MySQL什么情況下會產(chǎn)生錯誤日志，如何進(jìn)行查看？通過問題導(dǎo)入，引導(dǎo)學(xué)生主動思考，激發(fā)學(xué)生的學(xué)習(xí)興趣傳授新知（38min）【教師】通過學(xué)生的回答引入要講的知識，講解錯誤日志和二進(jìn)制日志的相關(guān)知識15.1錯誤日志?【教師】組織學(xué)生掃碼觀看“錯誤日志”視頻（詳見教材），讓學(xué)生對錯誤日志有一個大致了解錯誤日志是MySQL最重要的日志之一，一般記錄在MySQL服務(wù)器啟動和停止時，以及MySQL在運(yùn)行過程中發(fā)生任何嚴(yán)重錯誤時的相關(guān)信息。當(dāng)數(shù)據(jù)庫發(fā)生故障導(dǎo)致無法正常使用時，可以首先查看此日志。?【教師】講解“知類通達(dá)”的案例（詳見教材），引導(dǎo)學(xué)生養(yǎng)成良好習(xí)慣15.1.1啟動和設(shè)置錯誤日志在MySQL中，錯誤日志默認(rèn)是開啟的，并且該類型日志也無法被禁止。?【教師】隨機(jī)邀請學(xué)生回答以下問題：MySQL的錯誤日志一般被保存在哪個位置？?【學(xué)生】聆聽、思考、回答?【教師】總結(jié)學(xué)生的回答默認(rèn)情況下，錯誤日志文件一般放在MySQL服務(wù)器的數(shù)據(jù)文件夾（data）下，文件名為host_name.err，host_name表示服務(wù)器主機(jī)名。例如，MySQL所在的服務(wù)器主機(jī)名為ccy，則錯誤日志文件名為ccy.err。可以通過修改配置文件my.ini來自定義日志文件的名稱和存儲位置，具體方法為，在[mysqld]組下添加內(nèi)容修改參數(shù)值，形式如下：[mysqld]#錯誤日志文件log_error=[path/[filename]]上述語句中，path表示錯誤日志文件的存儲路徑，filename表示錯誤日志文件的名稱。15.1.2查看錯誤日志?【教師】隨機(jī)邀請學(xué)生回答以下問題：為什么要查看錯誤日志？如果忘記錯誤日志的存儲路徑，要如何查看？?【學(xué)生】聆聽、思考、回答?【教師】總結(jié)學(xué)生的回答通過查看錯誤日志文件，可以監(jiān)視系統(tǒng)運(yùn)行狀態(tài)，便于及時發(fā)現(xiàn)和修復(fù)故障。MySQL中的錯誤日志文件以文本文件形式存儲，可以使用文本編輯器直接查看MySQL錯誤日志。如果不知道日志文件的存儲路徑，可以使用SHOWVARIABLES語句查詢錯誤日志的存儲路徑，語法形式如下：SHOWVARIABLESLIKE'log_error';【實例15-1】執(zhí)行SQL語句，查詢錯誤日志文件的存儲路徑，并用記事本查看錯誤日志。首先，執(zhí)行SHOWVARIABLES語句查詢錯誤日志的存儲路徑和文件名。SQL語句及其執(zhí)行結(jié)果如下：mysql>SHOWVARIABLESLIKE'log_error';+++|Variable_name|Value|+++|log_error|F:\mysql-5.7.18-winx64\data\ccy.err|+++1rowinset,1warning(0.00sec)可以看到錯誤日志文件位于F:\mysql-5.7.18-winx64\data\目錄下，文件名為ccy.err，使用記事本打開該文件，可以看到MySQL錯誤日志圖只顯示了錯誤日志的一部分內(nèi)容，如果要查看其他較新的內(nèi)容，可以向下拖動右側(cè)滾動條進(jìn)行查看。15.1.3刪除錯誤日志MySQL中的錯誤日志文件可以直接刪除，但在運(yùn)行狀態(tài)下刪除日志文件后，MySQL并不會自動創(chuàng)建新的日志文件，此時需要執(zhí)行以下命令重新創(chuàng)建日志文件：FLUSHERRORLOGS;【提示】在MySQL5.5.7版本之前，執(zhí)行FLUSHERRORLOGS;命令可將錯誤日志文件重命名為file_name.err_old，并創(chuàng)建新的日志文件。從MySQL5.5.7版本開始，在日志文件存在的情況下，執(zhí)行FLUSHERRORLOGS;命令只是重新打開日志文件。15.2二進(jìn)制日志?【教師】組織學(xué)生掃碼觀看“二進(jìn)制日志”視頻（詳見教材），讓學(xué)生對二進(jìn)制日志有一個大致了解二進(jìn)制日志（BINLOG）記錄了所有DDL（對數(shù)據(jù)庫內(nèi)部對象進(jìn)行創(chuàng)建、刪除、修改等操作）和DML（對數(shù)據(jù)庫中表記錄的插入、更新、刪除等操作）語句，但不包括數(shù)據(jù)查詢語句。語句以“事件”形式存儲，并且記錄了語句發(fā)生時間、執(zhí)行時長、操作的數(shù)據(jù)等。二進(jìn)制日志對于數(shù)據(jù)損壞后的恢復(fù)起著至關(guān)重要的作用，第16章將會具體介紹使用二進(jìn)制日志恢復(fù)數(shù)據(jù)的方法。本節(jié)主要介紹二進(jìn)制日志的啟動、設(shè)置、查看和刪除方法。15.2.1啟動和設(shè)置二進(jìn)制日志默認(rèn)情況下，二進(jìn)制日志是關(guān)閉的，可以通過在配置文件my.ini中[mysqld]組下添加以下內(nèi)容，來啟動二進(jìn)制日志：#開啟二進(jìn)制日志log_bin[=path/[filename]]#此處必須指定server_id，這是MySQL5.7.3版本之后的要求server_id=1上述語句中，path表示二進(jìn)制日志文件的存儲路徑，默認(rèn)位于數(shù)據(jù)文件夾下；filename表示二進(jìn)制日志文件名，具體格式為hostname-bin.number，其中number的格式可以為000001、000002、000003等。另外，通過在配置文件my.ini中[mysqld]組下添加以下內(nèi)容，可以設(shè)置二進(jìn)制日志的過期天數(shù)，以及單個日志文件的大小。具體形式如下：expire_logs_days=10max_binlog_size=100M其中，expire_logs_days用于設(shè)定二進(jìn)制日志的過期天數(shù)，超出此天數(shù)的二進(jìn)制日志文件將被自動刪除。默認(rèn)為0，表示不啟用過期自動刪除功能。如果啟用該功能，自動刪除工作通常發(fā)生在啟動MySQL或刷新二進(jìn)制日志時。max_binlog_size用于定義二進(jìn)制日志文件的大小，默認(rèn)為1GB，如果當(dāng)前二進(jìn)制文件的大小達(dá)到了參數(shù)指定的值，系統(tǒng)會自動創(chuàng)建一個新的日志文件?！咎崾尽啃枰⒁獾氖牵承┒M(jìn)制文件的大小可能會超出max_binlog_size值，因為一個事務(wù)所產(chǎn)生的所有事件都必須要記錄在同一個二進(jìn)制文件中。這種情況下，即便二進(jìn)制文件的大小超出max_binlog_size的值，也會等到當(dāng)前事務(wù)的所有操作全部寫入當(dāng)前日志文件中后才會重新創(chuàng)建文件。配置完成后，需要重啟MySQL服務(wù)器才會生效。執(zhí)行SHOWVARIABLES語句可查詢二進(jìn)制日志的相關(guān)配置，具體語法形式如下：SHOWVARIABLESLIKE'%bin%';【實例15-2】啟動并設(shè)置二進(jìn)制日志文件，之后使用SHOWVARIABLES語句查詢?nèi)罩驹O(shè)置。步驟

1打開配置文件my.ini，并在其中的[mysqld]組下添加以下語句，啟動和設(shè)置二進(jìn)制日志：log_binserver_id=1expire_logs_days=10max_binlog_size=100M步驟

2重新啟動MySQL服務(wù)器，然后登錄MySQL，并輸入SHOWVARIABLES語句查詢?nèi)罩驹O(shè)置，結(jié)果如下：mysql>SHOWVARIABLESLIKE'%bin%';+++|Variable_name|Value|+++|bind_address|*||binlog_cache_size|32768||binlog_checksum|CRC32||binlog_direct_non_transactional_updates|OFF||binlog_error_action|ABORT_SERVER||binlog_format|ROW||binlog_group_commit_sync_delay|0||binlog_group_commit_sync_no_delay_count|0||binlog_gtid_simple_recovery|ON||binlog_max_flush_queue_time|0||binlog_order_commits|ON||binlog_row_image|FULL||binlog_rows_query_log_events|OFF||binlog_stmt_cache_size|32768||innodb_api_enable_binlog|OFF||innodb_locks_unsafe_for_binlog|OFF||log_bin|ON||log_bin_basename|F:\mysql-5.7.18-winx64\data\ccy-bin||log_bin_index|F:\mysql-5.7.18-winx64\data\ccy-bin.index||log_bin_trust_function_creators|OFF||log_bin_use_v1_row_events|OFF||log_statements_unsafe_for_binlog|ON||max_binlog_cache_size|18446744073709547520||max_binlog_size|104857600||max_binlog_stmt_cache_size|18446744073709547520||sql_log_bin|ON||sync_binlog|1|+++27rowsinset,1warning(0.00sec)由查詢結(jié)果可以看出，log_bin的變量值為ON，表明二進(jìn)制日志已經(jīng)打開。max_binlog_size值為104857600，正好為前面設(shè)置的100M。另外，重新啟動MySQL后，可以在MySQL服務(wù)器的數(shù)據(jù)文件夾下（此處為F:\mysql-5.7.18-winx64\data\）看到新生成的后綴名為.000001和.index的兩個文件，文件前綴名為hostname-bin。【提示】最好將二進(jìn)制日志文件與數(shù)據(jù)庫文件放在不同的兩個磁盤上，這樣可以在數(shù)據(jù)庫所屬磁盤發(fā)生故障時，使用二進(jìn)制日志文件恢復(fù)數(shù)據(jù)。15.2.2查看二進(jìn)制日志MySQL二進(jìn)制日志存儲了數(shù)據(jù)庫的所有變更信息，經(jīng)常會被用到。啟動二進(jìn)制日志后，系統(tǒng)會自動創(chuàng)建兩個文件，就是前面提到的hostname_bin.index和hostname_bin.000001文件。?【教師】通過多媒體展示“二進(jìn)制日志開啟后自動創(chuàng)建的文件”圖片（詳見教材），然后進(jìn)行講解MySQL服務(wù)每重新啟動一次，或者日志文件大小超過參數(shù)max_binlog_size的上限，以“.000001”為后綴的文件就會增加一個，并且后綴名加1遞增。由于二進(jìn)制日志以二進(jìn)制方式存儲，不能直接讀取，需要使用mysqlbinlog命令查看，語法形式如下：mysqlbinloglog-file;其中l(wèi)og-file表示日志文件目錄?！緦嵗?5-3】使用mysqlbinlog命令查看二進(jìn)制日志。步驟

1打開命令窗口，在其中輸入mysqlbinlog命令，查看最新的二進(jìn)制日志，執(zhí)行結(jié)果如下：PSC:\Users\ccy>mysqlbinlogF:\mysql-5.7.18-winx64\data\ccy-bin.000002/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at4#1806228:09:02serverid1end_log_pos123CRC320xe134181cStart:binlogv4,serverv5.7.18-logcreated1806228:09:02atstartup#Warning:thisbinlogiseitherinuseorwasnotclosedproperly.ROLLBACK/*!*/;BINLOG'Hj4sWw8BAAAAdwAAAHsAAAABAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAePixbEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAARwYNOE='/*!*/;#at123#1806228:09:02serverid1end_log_pos154CRC320xab9e47e0Previous-GTIDs#[empty]SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#Endoflogfile/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;PSC:\Users\ccy>【提示】使用mysqlbinlog命令時不需要登錄MySQL。步驟

2登錄MySQL，并對數(shù)據(jù)庫中任意一張表執(zhí)行數(shù)據(jù)修改操作。例如，將company數(shù)據(jù)庫中tb_department表id值為4的記錄中work_num值修改為8，SQL語句及其執(zhí)行結(jié)果如下：mysql>USEcompany;Databasechangedmysql>UPDATEtb_departmentSETwork_num=8WHEREid=4;QueryOK,1rowaffected(0.09sec)Rowsmatched:1Changed:1Warnings:0步驟3再次查看二進(jìn)制日志文件，由以下語句可以看出日志文件記錄了該修改過程。#1806229:11:27serverid1end_log_pos360CRC320xa4bc86d7Table_map:`company`.`tb_department`mappedtonumber11415.2.3刪除二進(jìn)制日志二進(jìn)制日志會記錄大量信息（其中包含一些無用的信息），如果長時間不清理，會浪費(fèi)許多磁盤空間。用戶可以在配置文件中設(shè)置參數(shù)expire_log_days值，使系統(tǒng)自動刪除過期日志文件，也可以手動刪除。本節(jié)將介紹兩種手動刪除二進(jìn)制日志的方法。1．刪除所有日志文件登錄MySQL后，執(zhí)行RESETMASTER語句可以刪除所有二進(jìn)制日志文件，其語法形式如下：RESETMASTER;執(zhí)行該語句后，系統(tǒng)會將所有二進(jìn)制日志刪除，MySQL會重新創(chuàng)建二進(jìn)制日志，新的日志文件后綴名將重新從“000001”開始編號。2．刪除指定日志文件實際應(yīng)用中，通常不會一次性刪除所有日志文件，使用PURGEMASTERLOGS語句可以刪除指定的日志文件。其基本語法形式如下：PURGE{MASTER|BINARY}LOGS{TO'log_name'|BEFORE'date'};上述語句中，MASTER和BINARY為同義詞，TO'log_name'表示刪除比指定文件名編號小的日志文件，BEFORE'date'表示刪除指定時間之前的日志文件?！緦嵗?5-4】使用PURGEMASTERLOGS語句刪除指定的日志文件。步驟

1為了實現(xiàn)操作過程，用戶可以多次重啟MySQL服務(wù)，創(chuàng)建多個二進(jìn)制日志文件，然后執(zhí)行以下命令查看所有的二進(jìn)制日志文件，結(jié)果如下：mysql>SHOWBINARYLOGS;+++|Log_name|File_size|+++|ccy-bin.000001|177||ccy-bin.000002|502||ccy-bin.000003|769|+++3rowsinset(0.02sec)步驟

2執(zhí)行PURGEMASTERLOGS語句，刪除ccy-bin.000003之前的日志文件，結(jié)果如下：mysql>PURGEBINARYLOGSTO'ccy-bin.000003';QueryOK,0rowsaffected(0.07sec)步驟

3再次查看所有日志文件，結(jié)果如下：mysql>SHOWBINARYLOGS;+++|Log_name|File_size|+++|ccy-bin.000003|769|+++1rowinset(0.00sec)【提示】刪除二進(jìn)制日志文件后，可能導(dǎo)致數(shù)據(jù)庫崩潰后無法恢復(fù)。因此，刪除之前應(yīng)將其和數(shù)據(jù)庫備份?！緦W(xué)生】聆聽、思考、記錄通過教師的講解和演示，使學(xué)生了解錯誤日志和二進(jìn)制日志的相關(guān)知識第二節(jié)課問題導(dǎo)入（5min）【教師】提出以下問題：通用日志與二進(jìn)制日志有什么區(qū)別？【學(xué)生】思考、舉手回答通過問題導(dǎo)入，引導(dǎo)學(xué)生主動思考，激發(fā)學(xué)生的學(xué)習(xí)興趣傳授新知（20min）【教師】通過學(xué)生的回答引入新知，介紹通用查詢?nèi)罩竞吐樵內(nèi)罩镜南嚓P(guān)知識15.3通用查詢?nèi)罩九c二進(jìn)制日志不同，通用查詢?nèi)罩緯涗浻脩舻乃胁僮鳌?5.3.1啟動和設(shè)置通用查詢?nèi)罩就ㄓ貌樵內(nèi)罩居涗浟朔?wù)器接收到的每一個查詢或命令，而不管這些查詢或命令是否返回結(jié)果，甚至是否包含語法錯誤。因此，開啟通用查詢?nèi)罩緯a(chǎn)生很大的系統(tǒng)開銷，一般在需要采樣分析或跟蹤某些特殊的SQL性能問題時才會開啟。默認(rèn)情況下，通用查詢?nèi)罩臼顷P(guān)閉的，可以通過在配置文件中[mysqld]組下添加以下內(nèi)容開啟通用查詢?nèi)罩荆?開啟通用查詢?nèi)罩緂eneral_log=1如要關(guān)閉通用查詢?nèi)罩?，可將general_log值設(shè)置為0。默認(rèn)情況下，通用查詢?nèi)罩疚挥赿ata文件夾下，文件名為hostname.log。如要更改其位置和文件名，可在配置文件中[mysqld]組下設(shè)置general_log_file參數(shù)：general_log_file[=path/[filename]]上述語句中，path為日志文件所在路徑，filename為日志文件名。使用SHOWVARIABLES語句，可以查看通用查詢?nèi)罩臼欠耖_啟，以及日志文件的路徑，基本語法形式如下：SHOWVARIABLESLIKE'%general%';【實例15-5】啟動并設(shè)置通用查詢?nèi)罩?。步驟1在配置文件中[mysqld]組下添加以下語句，設(shè)置通用查詢?nèi)罩尽eneral_log=1步驟2重啟并登錄MySQL服務(wù)，然后使用SHOWVARIABLES語句查看通用查詢?nèi)罩緺顟B(tài)，結(jié)果如下：mysql>SHOWVARIABLESLIKE'%general%';+++|Variable_name|Value|+++|general_log|ON||general_log_file|F:\mysql-5.7.18-winx64\data\ccy.log|+++2rowsinset,1warning(0.00sec)15.3.2查看通用查詢?nèi)罩就ㄓ貌樵內(nèi)罩臼且晕谋疚募男问酱鎯υ谖募到y(tǒng)中的，用戶可以使用文本編輯器直接打開進(jìn)行查看?！緦嵗?5-6】使用記事本打開MySQL通用查詢?nèi)罩?。用記事本打開F:\mysql-5.7.18-winx64\data目錄下的ccy.log文件。15.3.3刪除通用查詢?nèi)罩居捎谕ㄓ貌樵內(nèi)罩緯涗浻脩舻乃胁僮?，日志文件的大小會快速增長。數(shù)據(jù)庫用戶可以通過直接刪除文本文件的方式，定期刪除通用查詢?nèi)罩?，以?jié)省磁盤空間。數(shù)據(jù)庫用戶刪除日志文件后，執(zhí)行以下命令或重啟MySQL服務(wù)，可以生成新的通用查詢?nèi)罩疚募LUSHGENERALLOGS;15.4慢查詢?nèi)罩韭樵內(nèi)罩居涗洸樵儠r長超過指定時間的日志。通過慢查詢?nèi)罩?，可以找出哪些語句執(zhí)行時間較長、執(zhí)行效率較低，以便進(jìn)行優(yōu)化。15.4.1啟動和設(shè)置慢查詢?nèi)罩綧YSQL中的慢查詢?nèi)罩灸J(rèn)是關(guān)閉的，一般建議開啟，它對服務(wù)器性能的影響微乎其微?？梢酝ㄟ^在配置文件中[mysqld]組下添加以下內(nèi)容啟動慢查詢?nèi)罩荆?開啟慢查詢?nèi)罩緎low_query_log=1如要關(guān)閉慢查詢?nèi)罩?，可將slow_query_log值設(shè)置為0。另外，啟動慢查詢?nèi)罩緯r，還需要在配置文件中設(shè)置long_query_time項指定記錄閾值。該值默認(rèn)為10，以秒為單位，可以精確到微秒。如果一個查詢語句執(zhí)行時間超過閾值，該查詢語句將被記錄到慢查詢?nèi)罩局小ＤJ(rèn)情況下，慢查詢?nèi)罩疚募挥赿ata文件夾下，文件名為hostname-slow.log，也可以在配置文件中設(shè)置slow_query_log_file項，為日志文件指定存儲路徑和文件名，語法形式如下：slow_query_log_file[=path[/filename]]其中path指定慢查詢?nèi)罩疚募拇鎯β窂?，filename指定慢查詢?nèi)罩疚募?5.4.2查看慢查詢?nèi)罩灸J(rèn)情況下，慢查詢?nèi)罩就瑯右晕谋疚募男问酱鎯υ谖募到y(tǒng)中，可以使用文本編輯器直接打開查看?！緦嵗?5-7】設(shè)置慢查詢?nèi)罩荆⑹褂糜浭卤敬蜷_MySQL慢查詢?nèi)罩?。步驟

1