MySQL-日志淺析

1、啟動(dòng)日志(host.err)慢日志(host-slow.log)二進(jìn)制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log） 啟動(dòng)日志記錄著mysqld啟動(dòng)和停止,以及服務(wù)器在運(yùn)行過程中發(fā)生的錯(cuò)誤的相關(guān)信息。配置方式：在f配置文件中加入：mysqldlog-error = /path/錯(cuò)誤名稱.err如不加入該項(xiàng)配置，系統(tǒng)會(huì)自動(dòng)在msyql目錄下生成以host名稱命名的錯(cuò)誤日志。查看方式：mysql show global variables like log_error;該日志在服務(wù)異常、尤其斷電重啟之后出現(xiàn)問題，具有很大參考價(jià)值。啟動(dòng)日志(h

2、ost.err)慢日志(host-slow.log)二進(jìn)制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log） 顯示MySQL 服務(wù)器的普通查詢?nèi)罩荆╣eneral query log）。該日志將記錄所有請(qǐng)求的信息，所以在現(xiàn)網(wǎng)上除非萬不得已排查問題，千萬不要開啟。配置方式：mysql set global general_log = ON;mysql set global general_log_file = /var/lib/mysql/DB-test-96.log;MySQL服務(wù)器默認(rèn)關(guān)閉該日志，該參數(shù)可動(dòng)態(tài)配置，為全局參數(shù)。查看方式：my

3、sql show global variables like %general%;啟動(dòng)日志(host.err)慢日志(host-slow.log)二進(jìn)制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log） 優(yōu)化MySQL最重要的一部分工作是先確定”有問題”的查詢語句。只有先找出這些查詢較慢的sql查詢(執(zhí)行時(shí)間較長)，我們才能進(jìn)一步分析原因并且優(yōu)化它。MySQL為我們提供了Slow Query Log記錄功能，它能記錄執(zhí)行時(shí)間超過了特定時(shí)長的查詢。分析Slow Query Log有助于幫我們找到”問題”查詢。配置方式：在f中添加：mysqldl

4、og_slow_queries = ONlong_query_time = 1log-queries-not-using-indexes該參數(shù)可動(dòng)態(tài)配置，為全局參數(shù)。該日志記錄了慢日志發(fā)生時(shí)間、連接用戶、IP、執(zhí)行時(shí)間、鎖定時(shí)間、最終發(fā)送行數(shù)、總計(jì)掃描行數(shù)、SQL語句等相關(guān)信息可通過mysqldumpslow工具進(jìn)行匯總、排序，以便找出耗時(shí)最高、請(qǐng)求次數(shù)最多的慢查詢?nèi)罩境薽ysql自帶mysqldumpslow工具外，還有很多第三方優(yōu)秀的慢日志分析工具，如：mysqlsla、MyProfi、mysql-log-filter啟動(dòng)日志(host.err)慢日志(host-slow.log)二進(jìn)制

5、日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log）-binlog 以一種更有效的格式，并且是事務(wù)安全的方式包含更新日志中可用的所有信息。-binlog 包含了所有更新了數(shù)據(jù)或者已經(jīng)潛在更新了數(shù)據(jù)（例如，沒有匹配任何行的一個(gè)DELETE）的所有語句。語句以“事件”的形式保存，它描述數(shù)據(jù)更改。-binlog 還包含關(guān)于每個(gè)更新數(shù)據(jù)庫的語句的執(zhí)行時(shí)間信息。它不包含沒有修改任何數(shù)據(jù)的語句。如果你想要記錄所有語句（例如，為了識(shí)別有問題的查詢），你應(yīng)使用一般查詢?nèi)罩尽?binlog 的主要目的是在恢復(fù)使能夠最大可能地更新數(shù)據(jù)庫，因?yàn)?binlog 包含備

6、份后進(jìn)行的所有更新。-binlog 還用于在主復(fù)制服務(wù)器上記錄所有將發(fā)送給從服務(wù)器的語句。配置方式：在f配置文件中加入：mysqldserver-id = 96log-bin = mysql-binbinlog-do-db= *輔助配置log-bin=log-slave-updatesexpire_logs_days = 3sync_binlog = 0binlog_cache_size = 4Mreplicate-do-db=*replicate-ignore-db=*輔助配置選項(xiàng)與其他配置有依賴關(guān)系、后續(xù)將陸續(xù)介紹statement 每一條會(huì)修改數(shù)據(jù)的 SQL 都會(huì)記錄到 master 的

7、 bin-log 中。slave 在復(fù)制的時(shí)候 SQL 進(jìn)程會(huì)解析成和原來 master 端執(zhí)行過的相同的 SQL 再次執(zhí)行。優(yōu)點(diǎn)優(yōu)點(diǎn)：在 statement 模式下，首先就是解決了row 模式的缺點(diǎn)，不需要記錄每一行數(shù)據(jù)的變化，減少了 bin-log 日志量，節(jié)省 I/O 以及存儲(chǔ)資源，提高性能。因?yàn)樗恍枰涗浽?master 上所執(zhí)行的語句的細(xì)節(jié)，以及執(zhí)行語句時(shí)候的上下文的信息。缺點(diǎn)缺點(diǎn)：在 statement 模式下，由于他是記錄的執(zhí)行語句，所以，為了讓這些語句在 slave 端也能正確執(zhí)行，那么他還必須記錄每條語句在執(zhí)行的時(shí)候的一些相關(guān)信息，也就是上下文信息，以保證所有語句在 sla

8、ve 端杯執(zhí)行的時(shí)候能夠得到和在 master 端執(zhí)行時(shí)候相同的結(jié)果。另外就是，由于 MySQL 現(xiàn)在發(fā)展比較快，很多的新功能不斷的加入，使 MySQL 的復(fù)制遇到了不小的挑戰(zhàn)，自然復(fù)制的時(shí)候涉及到越復(fù)雜的內(nèi)容，bug 也就越容易出現(xiàn)。在 statement 中，目前已經(jīng)發(fā)現(xiàn)的就有不少情況會(huì)造成 MySQL 的復(fù)制出現(xiàn)問題，主要是修改數(shù)據(jù)的時(shí)候使用了某些特定的函數(shù)或者功能的時(shí)候會(huì)出現(xiàn)，比如：sleep() 函數(shù)在有些版本中就不能被正確復(fù)制，在存儲(chǔ)過程中使用了 last_insert_id() 函數(shù)，可能會(huì)使 slave 和 master 上得到不一致的 id 等等。由于 row 是基于每一行來

9、記錄的變化，所以不會(huì)出現(xiàn)類似的問題。row 日志中會(huì)記錄成每一行數(shù)據(jù)被修改的形式，然后在 slave 端再對(duì)相同的數(shù)據(jù)進(jìn)行修改。優(yōu)點(diǎn)優(yōu)點(diǎn)：在 row 模式下，bin-log 中可以不記錄執(zhí)行的 SQL 語句的上下文相關(guān)的信息，僅僅只需要記錄那一條記錄被修改了，修改成什么樣了。所以 row 的日志內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)，非常容易理解。而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過程或 function ，以及 trigger 的調(diào)用和觸發(fā)無法被正確復(fù)制的問題。缺點(diǎn)缺點(diǎn)：在 row 模式下，所有的執(zhí)行的語句當(dāng)記錄到日志中的時(shí)候，都將以每行記錄的修改來記錄，這樣可能會(huì)產(chǎn)生大量的日志內(nèi)容，比如

10、有這樣一條 update 語句：Mixed 從官方文檔中看到，之前的 MySQL 一直都只有基于 statement 的復(fù)制模式，直到 5.1.5 版本的 MySQL 才開始支持 row 復(fù)制。 從 5.0 開始，MySQL 的復(fù)制已經(jīng)解決了大量老版本中出現(xiàn)的無法正確復(fù)制的問題。但是由于存儲(chǔ)過程的出現(xiàn)，給 MySQL Replication 又帶來了更大的新挑戰(zhàn)。另外，看到官方文檔說，從 5.1.8 版本開始，MySQL 提供了除 Statement 和 Row 之外的第三種復(fù)制模式：Mixed，實(shí)際上就是前兩種模式的結(jié)合。 在 Mixed 模式下，MySQL 會(huì)根據(jù)執(zhí)行的每一條具體的 SQL

11、 語句來區(qū)分對(duì)待記錄的日志形式，也就是在 statement 和 row 之間選擇一種。新版本中的 statment 還是和以前一樣，僅僅記錄執(zhí)行的語句。而新版本的 MySQL 中對(duì) row 模式也被做了優(yōu)化，并不是所有的修改都會(huì)以 row 模式來記錄，比如遇到表結(jié)構(gòu)變更的時(shí)候就會(huì)以 statement 模式來記錄，如果 SQL 語句確實(shí)就是 update 或者 delete 等修改數(shù)據(jù)的語句，那么還是會(huì)記錄所有行的變更。statementmixedrowREAD-UNCOMMITTEDREAD-COMMITTEDfalsetruetrueREPEATABLE-READtruetruetrue

12、SERIERLIZED未提交讀 & 序列化 兩種事物隔離級(jí)別基本不可能在現(xiàn)網(wǎng)中使用，這里暫不討論。阿 里選用：提交讀+row模式語音云選用：一致性讀+mixed模式利用MySQL自帶同步機(jī)制，我們可以實(shí)現(xiàn)：主備、主主、環(huán)形、級(jí)聯(lián)方式的MySQL集群，并利用開源的讀寫分離、心跳檢查工具實(shí)現(xiàn)小規(guī)模負(fù)載。啟動(dòng)日志(host.err)慢日志(host-slow.log)二進(jìn)制日志(binlog/relaylog)引擎日志(redo/undolog)通用日志（host.log）Undo Log Undo Log 是為了實(shí)現(xiàn)事務(wù)的原子性，在MySQL數(shù)據(jù)庫InnoDB存儲(chǔ)引擎中，還用Undo Log來實(shí)現(xiàn)

13、多版本并發(fā)控制(簡稱：MVCC)。-事務(wù)事務(wù)的原子性的原子性(Atomicity)事務(wù)中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在執(zhí)行的過程中發(fā)生了錯(cuò)誤，要回滾(Rollback)到事務(wù)開始前的狀態(tài)，就像這個(gè)事務(wù)從來沒有執(zhí)行過。-原理原理-Undo Log的原理很簡單，為了滿足事務(wù)的原子性，在操作任何數(shù)據(jù)之前，首先將數(shù)據(jù)備份到一個(gè)地方（這個(gè)存儲(chǔ)數(shù)據(jù)備份的地方稱為Undo Log）。然后進(jìn)行數(shù)據(jù)的修改。如果出現(xiàn)了錯(cuò)誤或者用戶執(zhí)行了 ROLLBACK語句，系統(tǒng)可以利用Undo Log中的備份將數(shù)據(jù)恢復(fù)到事務(wù)開始之前的狀態(tài)。-除了可以保證事務(wù)的原子性，Undo Log也可以

14、用來輔助完成事務(wù)的持久化。-事務(wù)事務(wù)的持久性的持久性(Durability)-事務(wù)一旦完成，該事務(wù)對(duì)數(shù)據(jù)庫所做的所有修改都會(huì)持久的保存到數(shù)據(jù)庫中。為了保證持久性，數(shù)據(jù)庫系統(tǒng)會(huì)將修改后的數(shù)據(jù)完全的記錄到持久的存儲(chǔ)上。-用用Undo Log實(shí)現(xiàn)原子性和持久化的事務(wù)的簡化過程實(shí)現(xiàn)原子性和持久化的事務(wù)的簡化過程 假設(shè)有A、B兩個(gè)數(shù)據(jù)，值分別為1,2。 A.事務(wù)開始. B.記錄A=1到undo log. C.修改A=3. D.記錄B=2到undo log. E.修改B=4. F.將undo log寫到磁盤。 G.將數(shù)據(jù)寫到磁盤。 H.事務(wù)提交-這里有一個(gè)隱含的前提條件：數(shù)據(jù)都是先讀到內(nèi)存中，然后修改內(nèi)存

15、中的數(shù)據(jù)，最后將數(shù)據(jù)寫回磁盤。Undo Log之所以能同時(shí)保證原子性和持久化，是因?yàn)橐韵绿攸c(diǎn)： A. 更新數(shù)據(jù)前記錄Undo log。 B. 為了保證持久性，必須將數(shù)據(jù)（undolog也是數(shù)據(jù)）在事務(wù)提交前寫到磁盤。只要事務(wù)成功提交，數(shù)據(jù)必然已經(jīng)持久化。 C. Undo log必須先于數(shù)據(jù)持久化到磁盤。如果在G,H之間系統(tǒng)崩潰，undo log是完整的，可以用來回滾事務(wù)。 D. 如果在A-F之間系統(tǒng)崩潰,因?yàn)閿?shù)據(jù)沒有持久化到磁盤。所以磁盤上的數(shù)據(jù)還是保持在事務(wù)開始前的狀態(tài)。缺陷：缺陷：每個(gè)事務(wù)提交前將數(shù)據(jù)和Undo Log寫入磁盤，這樣會(huì)導(dǎo)致大量的磁盤IO，因此性能很低。如果能夠?qū)?shù)據(jù)緩存一段

16、時(shí)間，就能減少IO提高性能。但是這樣就會(huì)喪失事務(wù)的持久性。因此引入了另外一種機(jī)制來實(shí)現(xiàn)持久化，即Redo Log.redo Log-原理原理-和Undo Log相反，Redo Log記錄的是新數(shù)據(jù)的備份。在事務(wù)提交前，只要將Redo Log持久化即可， 不需要將數(shù)據(jù)持久化。當(dāng)系統(tǒng)崩潰時(shí)，雖然數(shù)據(jù)沒有持久化，但是Redo Log已經(jīng)持久化。系統(tǒng)可以根據(jù) Redo Log的內(nèi)容，將所有數(shù)據(jù)恢復(fù)到最新的狀態(tài)。- Undo + Redo事務(wù)的簡化過程事務(wù)的簡化過程 假設(shè)有A、B兩個(gè)數(shù)據(jù)，值分別為1,2. A.事務(wù)開始. B.記錄A=1到undo log. C.修改A=3. D.記錄A=3到redo l

17、og. E.記錄B=2到undo log. F.修改B=4. G.記錄B=4到redo log. H.將redo log寫入磁盤。 I.事務(wù)提交redo Log-Undo + Redo事務(wù)的特點(diǎn)事務(wù)的特點(diǎn) A. 為了保證持久性，必須在事務(wù)提交前將Redo Log持久化。 B. 數(shù)據(jù)不需要在事務(wù)提交前寫入磁盤，而是緩存在內(nèi)存中。 C. Redo Log 保證事務(wù)的持久性。 D. Undo Log 保證事務(wù)的原子性。 E. 有一個(gè)隱含的特點(diǎn)，數(shù)據(jù)必須要晚于redo log寫入持久存儲(chǔ)。sync_binlog、innodb_flush_log_at_trx_commit兩個(gè)參數(shù)對(duì)應(yīng)的 binlog和

18、redolog協(xié)同的問題。這兩個(gè)log都影響數(shù)據(jù)丟失，但是他們分別在Server 和InnoDB層維護(hù)。由于一個(gè)事務(wù)可能使用兩種事務(wù)引擎，所以MySQL用兩段式事務(wù)提交來協(xié)調(diào)事務(wù)提交。我們先簡單了解一下兩段式事務(wù)提交的過程。第一階段第一階段：首先，協(xié)調(diào)者在自身節(jié)點(diǎn)的日志中寫入一條的日志記錄，然后所有參與者發(fā)送消息prepare ，詢問這些參與者（包括自身），是否能夠提交這個(gè)事務(wù)；參與者在接受到這個(gè)prepare 消息以后，會(huì)根據(jù)自身的情況，進(jìn)行事務(wù)的預(yù)處理，如果參與者能夠提交該事務(wù)，則會(huì)將日志寫入磁盤，并返回給協(xié)調(diào)者一個(gè)ready 信息，同時(shí)自身進(jìn)入預(yù)提交狀態(tài)狀態(tài)；如果不能提交該事務(wù)，則記錄日志，并返回一個(gè)not commit 信息給協(xié)調(diào)者，同時(shí)撤銷在自身上所做的數(shù)據(jù)修改；第二階段第二階段：協(xié)調(diào)者會(huì)收集所有參與者的意見，如果收到參與者發(fā)來的not commit 信息，則標(biāo)識(shí)著該事務(wù)不能提交，協(xié)調(diào)者會(huì)將Abort 記錄到日志中，并向所有參與者發(fā)送一個(gè)Abort T信息，讓所有參與者撤銷在自身上所有的預(yù)操作；如果協(xié)調(diào)者收到所有參與者發(fā)來prepare 信息，那么協(xié)調(diào)者會(huì)