考研-數(shù)據(jù)庫(kù)復(fù)試133eaabd-專(zhuān)業(yè)級(jí)

引，在計(jì)算機(jī)系統(tǒng)、等等地方，我們經(jīng)?？梢圆榭吹絣og文件，也就是我們所說(shuō)的日志文件比如說(shuō)日志是記錄web服務(wù)器接收處理請(qǐng)求以及運(yùn)行時(shí)錯(cuò)誤等各種原始信息的log，這次我選擇學(xué)習(xí)和研究的課題就是：MySQL日志以及其作用一、介紹MySQL數(shù)據(jù)1、案例2003年淘寶網(wǎng)成立之初使用的是中小建站的首選技術(shù)：LAMP架2003年底隨著業(yè)務(wù)超預(yù)期增長(zhǎng)，MySQL（MyISAM引擎）高并發(fā)大壓力下缺點(diǎn)。隨后，淘寶改用商業(yè)的Oracle數(shù)據(jù)庫(kù)隨后幾年Oracle加小型機(jī)和高端的數(shù)據(jù)庫(kù)架構(gòu)支撐了淘寶網(wǎng)爆發(fā)式2、3個(gè)庫(kù)增長(zhǎng)到十幾個(gè)庫(kù)，每個(gè)庫(kù)的硬件已經(jīng)頂配PCMySQLDBA團(tuán)隊(duì)的選擇。數(shù)據(jù)庫(kù)架構(gòu)逐漸從垂直拆分水平拆分，MySQL很好地應(yīng)用在大規(guī)模水平集群2009年，淘寶網(wǎng)開(kāi)始去“IOE”進(jìn)程。2、案例謂IOE這個(gè)說(shuō)法來(lái)自阿里技術(shù)團(tuán)隊(duì)內(nèi)部的稱(chēng)謂，然后才在整個(gè)業(yè)界流傳開(kāi)來(lái)。IOEIT三大件，指以IBM、Oracle、EMC為代表的小型機(jī)、集中式數(shù)據(jù)庫(kù)和高端的技術(shù)架構(gòu)。I指IBMpAIX（IBMUnix系統(tǒng)OOracle數(shù)據(jù)庫(kù)E指EMC中高端SAN“IOE”ITIBM小型機(jī)、OracleEMC的過(guò)度依賴(lài)什么要去供高性能的數(shù)據(jù)處理和服務(wù)。傳統(tǒng)的商業(yè)數(shù)據(jù)庫(kù)軟件(Oracle,DB2)，多以集性主要采用向上擴(kuò)展(Scaleup)的方式，通過(guò)增加CPU，內(nèi)存，磁盤(pán)等方式提高處在阿里看來(lái)，“IOE”實(shí)際上代表了一種高成本、高費(fèi)、很不互聯(lián)網(wǎng)(不擅長(zhǎng)處理大規(guī)模高并發(fā)的互聯(lián)網(wǎng)行為)的數(shù)據(jù)庫(kù)系統(tǒng)，阿里巴巴采用數(shù)據(jù)切分(sharding)Oracle切換到分布式MySQL集3、總結(jié)MySQL越來(lái)越多的企業(yè)使用MySQL以及MySQLMySQL但是同時(shí)也會(huì)帶來(lái)一點(diǎn)選擇的。MySQL很靈活，能夠適應(yīng)高要求的環(huán)境，如：Web應(yīng)用（QPS很高M(jìn)ySQL4、MySQL介紹：MySQL最重要最與眾不同的特性引擎架構(gòu)將處理和相分離，可以根據(jù)性能、特性、需求來(lái)選擇數(shù)據(jù)的方式。：如上圖是MySQL②中間層是MySQL的，包括查詢(xún)解析、分析、優(yōu)化和緩存等。同時(shí)它還提供跨引擎的功能，包括過(guò)程、觸發(fā)器和視圖等。③最下面是引擎，它負(fù)責(zé)存取數(shù)據(jù)。服務(wù)器通過(guò)引擎API可以和據(jù)的方式，MySQL擁有不同的引擎，每個(gè)引擎都有各自的二、MySQL錯(cuò)誤日從下面的小節(jié)我開(kāi)始學(xué)習(xí)MySQL日志首先我們研究和使用的是MySQL1、錯(cuò)誤在mysql數(shù)據(jù)庫(kù)中，錯(cuò)誤日志功能是默認(rèn)開(kāi)啟的。并且，錯(cuò)誤日志無(wú)法被禁默認(rèn)情況下，錯(cuò)誤日志在mysql數(shù)據(jù)庫(kù)的數(shù)據(jù)文件（FILE）中。錯(cuò)誤日志文件通常的名稱(chēng)為hostname.err。其中，hostname表示服務(wù)器主機(jī)名。錯(cuò)誤日志信息可以自己進(jìn)行配置的，錯(cuò)誤日志所記錄的信息是可以通過(guò)log-errorlog-warningslog-err是定義是否啟用錯(cuò)誤日志的功能和log-（mysqlInnoDB的表空間文件的、如何初始化自己的引擎的等等2、關(guān)于SHOWVARIABLESMySQL服務(wù)器關(guān)于錯(cuò)誤日志的設(shè)log_errorlog_output：日志的方式。FILE代表文件②我們cd到相應(yīng) ，可以查看到錯(cuò)誤日志文件③當(dāng)我們需要查看服務(wù)器啟動(dòng)和關(guān)閉過(guò)程中的信息以及錯(cuò)誤信息等等記錄的時(shí)候，可以打開(kāi)error.log查看到按照時(shí)間點(diǎn)記錄的內(nèi)容。DBA定位到MySQL服務(wù)器的警告以及錯(cuò)誤，及三、MySQL查詢(xún)?nèi)?、查詢(xún)IO影響mysql的性能的。日志的輸出位置一般有三種方式：file(文件)，table(表)，none(不保存信息。file就是通過(guò)log方式定義的，而輸出位置定義為表時(shí)查看日志的內(nèi)容方式為：查看mysql數(shù)據(jù)庫(kù)下面的general_log表。2、關(guān)于①使用SHOWVARIABLESMySQLgeneral_log_file：查詢(xún)?nèi)罩镜奈募?。②?dāng)general_logOFF的時(shí)候，代表查詢(xún)?nèi)罩镜墓δ軟](méi)有被開(kāi)啟：此時(shí)再次查詢(xún)，general_logON③我們cd到相應(yīng) ，可以查看到查詢(xún)?nèi)罩疚募瑯拥奈覀兛梢允褂梦谋揪庉嬈骰蛘遲ail命令等方式查看general_log3、完成查詢(xún)?nèi)罩竟ぞ撷鄄荒軐?shí)時(shí)查看需要每次使用文本編輯器打開(kāi)或者使用s命令來(lái)查看。SQLMySQL工具是實(shí)時(shí)的，當(dāng)?shù)腗ySQL服務(wù)器接收了新的SQL操作或者M(jìn)ySQL命令，會(huì)在最下面新增行進(jìn)行展示，可以讓用戶(hù)實(shí)時(shí)MySQL服務(wù)器中查詢(xún)?nèi)罩镜那闆r，方便用戶(hù)進(jìn)行。代碼見(jiàn)附件：watch_query_log.py（使用正則表達(dá)式解析字符串四、MySQL事務(wù)日1、事務(wù)事務(wù)日志（InnoDB特有的日志）I/O，而不像隨機(jī)I/O需要在磁盤(pán)的多個(gè)地方移動(dòng)磁頭所以采用事務(wù)日志的方式，此時(shí)系統(tǒng)引擎在重啟時(shí)能夠自動(dòng)恢復(fù)這部分修改的數(shù)據(jù)。具有的恢復(fù)方式則視引擎而定。，2、關(guān)于 使用SHOWVARIABLES命令，查看MySQL服務(wù)器關(guān)于事務(wù)日志的設(shè)innodb_log_buffer_size：事務(wù)日志緩存區(qū),可設(shè)置1M~8M，默認(rèn)8M，延遲事 innodb_log_file_size：控制事務(wù)日志ib_logfile5MB~4G。所有事務(wù)日志ib_logfile0+ib_logfile1+..累加大小過(guò)4G，事務(wù)日志大，引入問(wèn)題ib_logfile文件的大小和之前存在的文件大innodb_log_files_in_group：DB中設(shè)置幾組事務(wù)日志，默認(rèn)是2。 mit（0,2,1務(wù)提交時(shí)，不會(huì)觸發(fā)log_buffer到文件系統(tǒng)同步；業(yè)務(wù)，mysql1s事務(wù)數(shù)據(jù)；2：數(shù)據(jù)安全性有要求，可以丟失一點(diǎn)事務(wù)日志，延遲也可以接受，OS1：數(shù)據(jù)安全性要求非常高，且磁盤(pán)IO能力足夠支持業(yè)務(wù)，如充值消費(fèi)，敏②我們cd到相應(yīng) ，可以查看到事務(wù)日志文件3InnoDBInnoDB的介紹InnoDB是一個(gè)高性能的事務(wù)引擎（默認(rèn)的事務(wù)型引擎，也是最重要、使用最廣泛的引擎。InnoDB給MySQL提供了具有事務(wù)(commit)、回滾(rollback)和修復(fù)能力(crashrecoverycapabilities)的事務(wù)安全(transaction-safe(ACIDcompliant))型表。ySL當(dāng)前默認(rèn)的引擎為InnoDInnoBySL存InnoDB特性，保證數(shù)據(jù)的可靠。ACID性如InnoDB務(wù)日ib_logfileMySQLib_logfile0,ib_logfile1名稱(chēng)存在,mysql數(shù)據(jù)庫(kù),mysql作用:在系統(tǒng)重啟時(shí)，作事務(wù)重做；在系統(tǒng)正常時(shí)，每次checkpoint時(shí)MySQL事務(wù)的自動(dòng)提交MySQL默認(rèn)采用自動(dòng)提交方式，如果不是顯示地開(kāi)始一個(gè)事務(wù)，每一個(gè)查MySQL當(dāng)前為自動(dòng)提交，保證每一次查詢(xún)和操作都是一個(gè)事務(wù)，如果設(shè) mit=0，所有的查詢(xún)都在一個(gè)事務(wù)中，直到COMMIT五、MySQL慢查詢(xún)?nèi)?、慢查一般建議開(kāi)啟，它對(duì)服務(wù)器性能的影響微乎其微，但是卻可以記錄MySQL最重要的應(yīng)用：滿(mǎn)查詢(xún)?nèi)罩究梢詭椭覀兌ㄎ恍阅軉?wèn)題的2、關(guān)于慢查詢(xún)?nèi)罩镜膶?shí)①使用SHOWVARIABLES命令，查看MySQLslow_query_log_file：慢查詢(xún)?nèi)罩镜奈募?。②?dāng)slow_query_logOFF的時(shí)候，代表慢查詢(xún)?nèi)罩镜墓δ軟](méi)有被開(kāi)啟：此時(shí)再次查詢(xún)，slow_query_logON③通過(guò)配置文 f中的slow_query_log選項(xiàng)可以開(kāi)啟慢查詢(xún)?nèi)罩? f文件，設(shè)置slow_query_log=1log-slow-queries[=DIR/[filename]其中，DIR參數(shù)指定慢查詢(xún)?nèi)罩镜穆窂?；filename參數(shù)指定日志的文件名，生成日志文件的完成名稱(chēng)為filename-slow.log。如果不指定路徑慢查詢(xún)?nèi)罩灸J(rèn)到mysql數(shù)據(jù)庫(kù)的數(shù)據(jù)文件下如果不指定文件名，默認(rèn)文件名為hostname-slow.log。④⑤同樣的，我們可以使用SETGLOBALlong_query_time⑥我們cd到相應(yīng) ，可以查看到慢查詢(xún)?nèi)罩疚募inux工具，查看相應(yīng)的慢查詢(xún)?nèi)?、完成慢查詢(xún)?nèi)罩痉治鲈贛ySQL中，我們可以使用慢查詢(xún)分析mysqldumpslow來(lái)進(jìn)行慢查詢(xún)?nèi)罩緈ysqldumpslow-sc-t1010條SQLmysqldumpslow-srt1010mysqldumpslowstt10g'leftjoin'10條里面含有左連接-s,c記錄次數(shù)、t時(shí)間、l查詢(xún)時(shí)間、r返回的記錄數(shù)，ac、at、al、ar，表示相-t,是topn-g,如上圖是mysqldumpslow析，有一些缺點(diǎn)，比如命令復(fù)雜、沒(méi)有的查詢(xún)條件、不可視化。工具原理：將慢查詢(xún)?nèi)罩具M(jìn)行解析，使用保存到db文件中，進(jìn)而可sqlsqlmysqldumpslow參數(shù)，包括：執(zhí)行時(shí)間，執(zhí)行時(shí)長(zhǎng)，檢查數(shù)據(jù)行數(shù)，結(jié)果行數(shù)，sql語(yǔ)句等等參①用戶(hù)可以在第一行選擇不同的dbsqlSELECT*FROMslow_logsLIMIT1000，SELECTCOUNT(*)FROMslow_logs，SELECT*FROMslow_logsORDERBYquery_timeDESCLIMIT100，SELECT*FROMslow_logsWHEREtimestamp>strftime(\'%s\',\'now\')-2*24*3600ORDERBYquery_timeDESCLIMIT200,SELECT*FROMWHEREtimestamp>strftime(\'%s\',\'now\')-15*24*3600ANDquery_time>10ORDERBYtimestampDESCLIMIT③以上sql定義的sql語(yǔ)句，來(lái)進(jìn)行特定的慢查詢(xún)?nèi)罩窘y(tǒng)計(jì)。4、數(shù)據(jù)庫(kù)性能優(yōu)化最佳①M(fèi)ySQLMySQL的性能瓶頸通常包括兩類(lèi)：硬件瓶頸和自身瓶頸。數(shù)據(jù)庫(kù)是常見(jiàn)的兩個(gè)硬件瓶頸是CPU和I/O的瓶頸：CPU在飽和的時(shí)候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤(pán)上數(shù)據(jù)時(shí)候。磁盤(pán)I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時(shí)候。絡(luò)上，我們可以用mpstat,iostat,sar和vmstat來(lái)查看系統(tǒng)的性能狀態(tài)。MySQL系統(tǒng)本身的瓶頸，我們可以使用使用索引，使用慢查詢(xún)?nèi)罩荆▋?yōu)化查詢(xún)）以及調(diào)整MySQL的內(nèi)部配置。在優(yōu)化MySQL時(shí)，通常需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行分析，常見(jiàn)的分析有：慢查詢(xún)?nèi)罩?，EXPLAIN分析查詢(xún)，profiling分析以及show命令查詢(xún)系統(tǒng)狀Show命令我們可以通過(guò)show命令查看MySQL狀態(tài)及變量，找到系統(tǒng)的瓶頸：‘XXXshowvariables—顯示系統(tǒng)變量（showvariableslike‘XXXMysql>showprocesslist—查看當(dāng)前SQL執(zhí)行，包括執(zhí)行狀態(tài)、是否鎖表等S>mysqladminvariables-uusername-ppassword——顯示系統(tǒng)變量S>mysqladminextended-status-uusername-ppassword——顯示狀態(tài)信息滿(mǎn)查詢(xún)?nèi)罩疚覀兛梢允褂胢ysqldumpslow來(lái)進(jìn)行分析，此時(shí)我們就可以使用工具explain分析查詢(xún)EXPLAINSQL查詢(xún)語(yǔ)句，從而知道MySQL是如何處理你的SQL語(yǔ)句的。這可以幫你分析你的查詢(xún)語(yǔ)句或是表如下圖，我們使用explain來(lái)分析selectEXPLAIN字段：keyNULL，則沒(méi)有使用索引。rows：MySQL認(rèn)為必須檢索的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù)type：這是最重要的字段之一，顯示查詢(xún)使用了何種類(lèi)型ALLExtra：關(guān)于MYSQLprofiling分析查SQLexplain我們可以得知SQLshow命令如果覺(jué)得explainprofilingSQLprofilingmysql>set執(zhí)行需要測(cè)試的sqlmysql>showprofiles\G;可以得到被執(zhí)行的SQL語(yǔ)句的時(shí)間和mysql>showprofileforquery1;得到對(duì)應(yīng)SQL有的行和字段，比如convertingHEAPtoMyISAM、Copyingtotmptable過(guò)profiling資源耗費(fèi)信息，我們可以采取針對(duì)性的優(yōu)化措施。mysql>set通過(guò)showstatusSQL定位執(zhí)行效率較低的SQL語(yǔ)句（慢查詢(xún)?nèi)罩綞XPLAINSQL通常的優(yōu)化措施包括：建立合理的索引和編寫(xiě)性能優(yōu)異的查詢(xún)六、MySQL二進(jìn)制日1、二進(jìn)的mysql語(yǔ)句，并且記錄了語(yǔ)句發(fā)生時(shí)間、執(zhí)行時(shí)長(zhǎng)、操作的數(shù)據(jù)等等。所以說(shuō)通過(guò)二進(jìn)制日志可以查詢(xún)mysql數(shù)據(jù)庫(kù)中進(jìn)行了哪些變化。一般大小1G。其一、log_bin可以直接定義為文件路徑，也可以為ON|OFF； f中的log-bin選項(xiàng)可以開(kāi)啟二進(jìn)制日志；3、混合模式(mixed)：交替使用行和語(yǔ)句、由mysql服務(wù)器自行判斷。2、關(guān)于二進(jìn)制日志的實(shí)showbinary showmasterstatus;每一個(gè)日志文件本身也有自己的元數(shù)據(jù)所以說(shuō)對(duì)于當(dāng)前版本的mysql來(lái)說(shuō)二進(jìn)107；SHOWBINLOGEVENTS[IN'log_name'][FROMpos][LIMIT[offset,]由于無(wú)法使用cat等方式直接打開(kāi)并查看二進(jìn)制日志；所以必須使用mysqlbinlog但是當(dāng)正在執(zhí)行mysql讀寫(xiě)操作時(shí)建議不要使用此打開(kāi)正在使用的二進(jìn)制日志文件；若非要打開(kāi)可flushlogs。mysqlbinlogmysqlbinlogmysql- #必須在數(shù) mysqlbinlogmysql-bin.000017>mysql<也不可在關(guān)閉mysql服務(wù)器之后直接刪除因?yàn)檫@樣可能會(huì)給數(shù)據(jù)庫(kù)帶來(lái)錯(cuò)誤的。壓縮歸檔。刪除二進(jìn)制文件的方法如下：刪除所有的二進(jìn)制日志（不可效仿RESETMASTERmysql>QueryOK,0rowsaffected(0.17sec)mysql>showbinarylogs; | |File_size 107 1rowinsetMysql000001mysql>PURGE{BINARY|MASTER}LOGS{TO'log_name'|BEFOREdatetime_expr其中TO'log_name表示把這個(gè)文件之前的其他文件都刪除掉，也可使用BEFOREdatetime_expr>'';#3、因?yàn)槎M(jìn)制記錄修改數(shù)據(jù)或有可能引起數(shù)據(jù)改變的mysql語(yǔ)句，并且記錄了當(dāng)binlog_format為ROW的時(shí)候，定義的并非數(shù)據(jù)本身而是這一行的數(shù)MySQL記錄的二進(jìn)ROW格式的時(shí)候，我們可以得到詳細(xì)的變更記錄以及變更的user5、6③查看bin-insertupdatedelete操作類(lèi)似。詳細(xì)請(qǐng)見(jiàn)代4MySQL份mysqlmysqllog-binbinarylog，mysql才可以在必要的時(shí)候做完②冷備份（MySQLmysql服務(wù)，在操作系統(tǒng)級(jí)別備份mysqlmysqlbinlog。mysql服務(wù)，在操作系統(tǒng)級(jí)別恢復(fù)mysqlmysqlmysqlbinlog恢復(fù)自備份以來(lái)的binlogmysqldump–F(flush-logs)備份數(shù)據(jù)mysqldump–uroot–p***pointcard–F>mysqldumpbinlog。mysql導(dǎo)入備份文件mysql–uroot–p***pointcard<mysqlbinlogmysqld