電工與電子技術(shù)電子商務(wù)電子課件數(shù)據(jù)庫原理及應(yīng)用教程（基于Linux的MySQL和NoSQL應(yīng)用）第16章ppt

1、第16章MySQL日志管理數(shù)據(jù)庫原理及應(yīng)用教程（基于Linux的MySQL和NoSQL應(yīng)用）CONTENTSMySQL支持的日志1二進制日志2慢查詢?nèi)罩?通用查詢?nèi)罩?MySQL支持的日志1MySQL支持的日志錯誤日志：記錄MySQL服務(wù)器的啟動、關(guān)閉、運行錯誤等信息。二進制日志：以二進制文件的形式記錄了數(shù)據(jù)庫中的操作，但不記錄查詢語句。通用查詢?nèi)罩荆河涗浻脩舻卿浐陀涗洸樵兊男畔?。MySQL日志是用來記錄MySQL數(shù)據(jù)庫的客戶端連接情況、SQL語句的執(zhí)行情況、錯誤信息等。MySQL日志可以分為4種：慢查詢?nèi)罩荆河涗泩?zhí)行時間超過指定時間的操作。16.1.2啟動在MySQL數(shù)據(jù)庫中，錯誤日志功能是

2、默認開啟的。而且，錯誤日志無法被禁止。默認情況下，錯誤日志存儲在MySQL數(shù)據(jù)庫的數(shù)據(jù)文件夾下。錯誤日志文件通常的名稱為hostname.err。其中，hostname表示MySQL服務(wù)器的主機名。錯誤日志的存儲位置可以通過log-error選項來設(shè)置。將log-error選項加入到/etc/f文件的MySQLd組中，形式如下:log-error=DIR / filename其中，DIR參數(shù)指定錯誤日志的路徑，filename參數(shù)是錯誤日志的名稱，沒有該參數(shù)時默認為主機名。重啟MySQL服務(wù)后，這個參數(shù)開始生效。可以在指定路徑下看到filename.err文件。如果沒有指定filename，那

3、么錯誤日志將直接默認為hostname.err。16.1.2啟動通過cat /etc/f查看與錯誤日志配置的相關(guān)信息。由圖可知，筆者的錯誤日志位于/var/log目錄下的MySQLd.log文件中。16.1.3查看【例16-1】下面是筆者MySQL服務(wù)器的錯誤日志的部分內(nèi)容。錯誤日志中記錄著開啟和關(guān)閉MySQL服務(wù)的時間，以及服務(wù)運行過程中出現(xiàn)哪些異常等信息。如果MySQL服務(wù)出現(xiàn)異常，可以到錯誤日志中查找原因。錯誤日志是以文本文件的形式存儲的，可以直接使用普通文本工具就可以查看。用cat/var/log/MySQLd.log查看錯誤日志。16.1.4刪除數(shù)據(jù)庫管理員可以刪除很長時間之前的錯誤

4、日志，以保證MySQL服務(wù)器上的硬盤空間。MySQL數(shù)據(jù)庫中，可以使用MySQLadmin命令來開啟新的錯誤日志。MySQLadmin -u root -p flush-logs執(zhí)行該命令后，數(shù)據(jù)庫系統(tǒng)會自動創(chuàng)建一個新的錯誤日志。舊的錯誤日志仍然保留著，只是已經(jīng)更名為filename.err-old。除了MySQLadmin命令外，也可以使用flush logs語句來開啟新的錯誤日志。使用該語句之前必須先登錄到MySQL數(shù)據(jù)庫中。創(chuàng)建好新的錯誤日志之后，數(shù)據(jù)庫管理員可以將舊的錯誤日志備份到其他的硬盤上。二進制日志2二進制日志也叫作變更日志（update log），主要用于記錄數(shù)據(jù)庫的變化情況。

5、通過二進制日志可以查詢MySQL數(shù)據(jù)庫中進行了哪些改變。16.2.1啟動默認情況下，二進制日志功能是關(guān)閉的。通過my.ini的log-bin選項可以開啟二進制日志。將log-bin選項加入到my.ini文件的MySQLd組中，形式如下：log-bin =DIR filename其中，DIR參數(shù)指定二進制文件的存儲路徑；filename參數(shù)指定二進制文件的文件名，其形式為filename.Number，number的形式為000001、000002等。每次重啟MySQL服務(wù)后，都會生成一個新的二進制日志文件。還會生出一個名為filename.Index的文件，這個文件中存儲所有二進制日志文件的清

6、單。如果沒有DIR參數(shù)和filename參數(shù)，二進制日志將默認存儲在數(shù)據(jù)庫的數(shù)據(jù)目錄下。默認的文件名為hostname-bin.Number，其中hostname表示主機名。二進制日志與數(shù)據(jù)庫的數(shù)據(jù)文件最好不要放在同一塊硬盤上16.2.2查看使用二進制格式可以存儲更多的信息，并且可以使寫入二進制日志的效率更高。但是，不能直接打開并查看二進制日志。如果需要查看二進制日志，必須使用MySQLbinlog命令。MySQLbinlog命令的語法形式如下：MySQLbinlog filename.numberMySQLbinlog命令將在當前文件夾下查找指定的二進制日志。因此需要在二進制日志filena

7、me.number所在的目錄下運行該命令，否則將會找不到指定的二進制日志文件。16.2.3刪除1刪除所有二進制日志reset master ;刪除所有二進制日志后，MySQL將會重新創(chuàng)建新的二進制日志。新二進制日志的編號從000001開始，如主機名-bin.0000012. 根據(jù)編號來刪除二進制日志purge master logs to filename.number;16.2.3刪除3.根據(jù)創(chuàng)建時間來刪除二進制日志purge master logs before yyyy-mm-dd hh:mm;ss ; 使用purge master logs to語句可以刪除指定時間之前創(chuàng)建的二進制日志

8、16.2.4二進制日志還原數(shù)據(jù)庫如果數(shù)據(jù)庫遭到意外損壞，首先應(yīng)該使用最近的備份文件來還原數(shù)據(jù)庫。備份之后，數(shù)據(jù)庫可能進行了一些更新，這可以使用二進制日志來還原。因為二進制日志中存儲了更新數(shù)據(jù)庫的語句，如update語句、insert語句等。二進制日志還原數(shù)據(jù)庫的命令如下：MySQLbinlog filename.number | MySQL -u root -p即用MySQLbinlog命令來讀取filename.Number中的內(nèi)容，然后使用MySQL命令將這些內(nèi)容還原到數(shù)據(jù)庫中。技巧：二進制日志雖然可以用來還原MySQL數(shù)據(jù)庫，但是其占用的磁盤空間也是非常大的。因此，在備份MySQL數(shù)據(jù)庫

9、之后，應(yīng)該刪除備份之前的二進制日志。16.2.4二進制日志還原數(shù)據(jù)庫使用MySQLbinlog命令進行還原操作時，必須是編號（number）小的先還原。如果用戶不希望自己執(zhí)行的某些SQL語句記錄在二進制日志中，那么需要在執(zhí)行這些SQL語句之前暫停二進制日志功能。用戶可以使用set語句來暫停二進制日志功能set sql log bin=0;執(zhí)行該語句后，MySQL服務(wù)器會暫停二進制日志功能。但是，只有擁有super權(quán)限的用戶才可以執(zhí)行該語句。如果用戶希望重新開啟二進制日志功能，可以使用下面的set語句：set sql log bin=1 ;10.2.1 創(chuàng)建視圖【例10-2】下面在student

10、表上創(chuàng)建一個名為student_view2的視圖,包含學(xué)生的姓名、課程名以及對應(yīng)的成績。CREATE VIEW student_view2 (sname, cname, grade) AS SELECT sname,cname,gradeFROM student s,course c,scWHERE s.sno = sc.sno AND o = o;10.2.1 創(chuàng)建視圖【例10-3】當我們在查詢王琴雪的所有已修課程的成績時，就可以借助視圖很方便地完成查詢 SELECT*FROM student_view2 WHERE sname = 王琴雪;10.2.1 創(chuàng)建視圖創(chuàng)建視圖時需要注意以下幾點：

11、1.運行創(chuàng)建視圖的語句需要用戶具有創(chuàng)建視圖(crate view)的權(quán)限，若加了or replace，則還需要用戶具有刪除視圖(drop view)的權(quán)限。2.select語句不能包含from子句中的子查詢。3.select語句不能引用系統(tǒng)或用戶變量。4.select語句不能引用預(yù)處理語句參數(shù)。5.在存儲子程序內(nèi)，定義不能引用子程序參數(shù)或局部變量。6.在定義中引用的表或視圖必須存在，但是，創(chuàng)建了視圖后，能夠舍棄定義引用的表或視圖。要想檢查視圖定義是否存在這類問題，可使用check table語句。7.在定義中不能引用temporary表，不能創(chuàng)建temporary視圖。8.在視圖定義中命名的表

12、必須已存在。9.不能將觸發(fā)程序與視圖關(guān)聯(lián)在一起。10.在視圖定義中允許使用order by，但是，如果從特定視圖進行了選擇，而該視圖使用了具有自己order by的語句，它將被忽略。10.2.2 刪除視圖刪除視圖時，只能刪除視圖的定義，不會刪除數(shù)據(jù)。其次用戶必須擁有drop權(quán)限。drop view if existsview_name1，view_name2restrict | cascade 其中，view_name是視圖名，聲明了if exists，若視圖不存在的話，也不會出現(xiàn)錯誤信息。也可以聲明restrict和cascade，但沒什么影響。使用drop view 可以一次刪除多個視圖。

13、10.2.2 刪除視圖【例10-4】下面將刪除視圖student_view1。DROP VIEW IF EXISTS student_view1 ; 10.2.3 查看視圖定義查看視圖是指查看數(shù)據(jù)庫中已經(jīng)存在的視圖的定義。查看視圖必須要有SHOW VIEW的權(quán)限。查看視圖的方法包括以下幾條語句，他們從不同的角度顯示視圖的相關(guān)信息1)DESCRIBE語句，語法格式：DESCRIBE 視圖名稱; 或者DESC視圖名稱;2)SHOW TABLE STATUS語句,語法格式:SHOW TABLE STATUS LIKE 視圖名3)SHOW CREATE VIEW語句，語法格式：SHOW CREATE

14、VIEW 視圖名4)查詢information_schem數(shù)據(jù)庫下的views表語法格式：SELECT * FROM information_schema.views WHERE table_name =視圖名10.2.3 查看視圖定義【例10-5】查看student_view2視圖的信息 方式1：DESCRIBE student_view2;10.2.3 查看視圖定義方式2：SHOW TABLE STATUS LIKE student_view2 方式3：SHOW CREATE VIEW student_view2;10.2.3 查看視圖定義方式4：SELECT * FROM informa

15、tion_schema.views WHERE table_name = student_view2;10.2.4 修改視圖定義修改視圖是指修改數(shù)據(jù)庫中已經(jīng)存在表的定義。當基本表的某些字段發(fā)生改變時，可以通過修改視圖來保持視圖和基本表之間的一致。MySQL中通過CREATE OR REPLACE VIEW 語句或者ALTER語句來修改視圖。(1) CREATE OR REPLACE VIEW語句格式CREATE OR REPLACE ALGORITH=UNDEFINDE|MERGE|TEMPTABLEVIEW 視圖名 屬性清單 AS SELECT 語句WITHCACADED|LOCALCHEC

16、K OPTION;10.2.4 修改視圖定義【例10-6】使用CREATE OR REPLACE VIEW修改視圖student_view2的列名為姓名、選修課、成績。CREATE OR REPLACE VIEW student_view2(姓名,選修課,成績)AS SELECT sname,cname,gradeFROM student s, course c,scWHERE s.sno=sc.sno AND o = o;10.2.4 修改視圖定義用DESC再查看student_view2的描述如下；10.2.4 修改視圖定義 (1) ALTER語句格式ALTER algorithm = u

17、ndefined | merge | temptableVIEW 視圖名 屬性清單 AS SELECT 語句 with cascaded | local check option;10.2.4 修改視圖定義【例10-7】我們把student_view2 用alter命令 把列的名稱再改為sname,cname,gradeALTER VIEW student_view2(sname,cname,grade)AS SELECT sname,cname,gradeFROM student s, course c,scWHERE s.sno=sc.sno AND o = o;執(zhí)行完，我們在用DESC命

18、令查看如下更新視圖數(shù)據(jù)對視圖的更新其實就是對表的更新，更新視圖是指通過視圖來插入（insert）、更新（update）和刪除（delete）表中的數(shù)據(jù)。因為視圖是一個虛擬表，其中沒有數(shù)據(jù)。通過視圖更新時，都是轉(zhuǎn)換到基本表來更新。更新視圖時，只能更新權(quán)限范圍內(nèi)的數(shù)據(jù)。超出了范圍，就不能更新。 【例10-8】下面在視圖student_view3中對視圖進行更新。CREATE VIEW student_view3(sno,sname,ssex,sbirth) AS SELECT sno,sname,ssex,sbirthFROM studentWHERE sno=1114070116 ; 更新視圖數(shù)

19、據(jù)更新視圖數(shù)據(jù)查看視圖會查詢到一條數(shù)據(jù)如下：通過視圖對student表進行更新，如下：UPDATE student_view3SET sno=1114070118,sname=張三,ssex=男,sbirth=1997-02-09;更新視圖數(shù)據(jù)更新視圖數(shù)據(jù)更新完后，用select * from student_view3進行查詢，會發(fā)現(xiàn)沒有查詢到任何數(shù)據(jù)慢查詢?nèi)罩?16.3.1啟動默認情況下，慢查詢?nèi)罩竟δ苁顷P(guān)閉的。在Linux下，通過修改f文件的slow-query-log選項可以開啟慢查詢?nèi)罩?。在MySQLd組，把slow-query-log的值設(shè)置為1（默認是0），slow_query_

20、log_file設(shè)置慢查詢?nèi)罩韭窂?，long_query_time設(shè)置時間值，超過這個時間值就被記錄到慢查詢?nèi)罩?。重新啟動MySQL服務(wù)即可開啟慢查詢?nèi)罩?。slow_query_log_file格式如下:slow_query_log_file=DIRfilename其中，DIR參數(shù)指定慢查詢?nèi)罩镜拇鎯β窂?；filename參數(shù)指定日志的文件名，生成日志文件的完整名稱為filename-slow.log。16.3.2查看執(zhí)行時間超過指定時間的查詢語句會被記錄到慢查詢?nèi)罩局?。如果用戶希望查詢哪些查詢語句的執(zhí)行效率低，可以從慢查詢?nèi)罩局蝎@得想要的信息。慢查詢?nèi)罩疽彩且晕谋疚募男问酱鎯Φ摹？梢允褂闷胀ǖ奈谋疚募榭垂ぞ邅聿榭础?6.3.5刪除慢查詢?nèi)罩镜膭h除方法可以使用MySQLadmin命令來刪除，也可以使用手工方式來刪除。MySQLadmin命令的語法如下：MySQLadmin u root p flush-logs執(zhí)行該命令后，命令行會提示輸入密碼。輸入正確密碼后，將執(zhí)行刪除操作。新的慢查詢?nèi)罩緯苯痈采w舊的查詢?nèi)罩?，不需要再手動刪除了。通用查詢?nèi)罩?16.4.1啟動默認情