考研-數據庫復試133eaabd-專業(yè)級

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