MYSQL---日志

1.日志的概述

日志是MySQL數據庫的重要組成部分。日志文件中記錄著MySQL數據庫運行期間發生的變化；也就是說用來記錄MySQL數據庫的客戶端連接狀況、SQL語句的執行情況和錯誤信息等。當數據庫遭到意外的損壞時，可以通過日志查看文件出錯的原因，并且可以通過日志文件進行數據恢復。

MySQL有不同類型的日志文件，用來存儲不同類型的日志，分為二進制日志、錯誤日志、查詢日志和慢查詢日志，這也是常用的4種。MySQL 8又新增兩種支持的日志：中繼日志和事務日志。使用這些日志文件，可以查看MySQL內部發生的事情

慢查詢日志：記錄所有執行時間超過long_query_time的所有查詢，方便我們對查詢進行優化

查詢日志：記錄所有連接的起始時間和終止時間，以及連接發送給數據庫服務器的所有指令，對我們復原操作的實際場景、發現問題，甚至是對數據庫操作的審計都有很大的幫助

錯誤日志：記錄MySQL服務的啟動、運行或停止MySQL服務時出現的問題，方便我們了解服務器的狀態，從而對服務器進行維護

二進制日志：記錄所有更改數據的語句，可以用于主從服務器之間的數據同步，以及服務器遇到故障時數據的無損失恢復

中繼日志：用于主從服務器架構中，從服務器用來存放主服務器二進制日志內容的一個中間文件。從服務器通過讀取中繼日志的內容，來同步主服務器上的操作

事務日志：記錄數據定義語句執行的元數據操作

除二進制日志外，其他日志都是文本文件。默認情況下，所有日志創建于 MySQL數據目錄中

日志的弊端

日志功能會降低MySQL數據庫的性能?

日志會占用大量的磁盤空間

2.錯誤日志(error log)

它記錄了mysqld啟動和停止時，以及服務器在運行過程中發生任何嚴重錯誤時的相關信息。當數據庫出現任何故障導致無法正常使用時，建議首先查看此日志

在MySQL數據庫中，錯誤日志功能是默認開啟的。而且，錯誤日志無法被禁止。

默認情況下，錯誤日志存儲在MySQL數據庫的數據文件夾下/var/log/，名稱默認為 mysqld.log （Linux系統）

show variables like '%log_err%';

錯誤日志記錄信息

1.服務器啟動和關閉過程中的信息

未必是錯誤信息，比如mysql是如何去初始化存儲引擎的過程記錄在錯誤日志里等等

2.服務器運行過程中的錯誤信息

比如sock文件找不到，無法加載mysql數據庫的數據文件，如果忘記初始化mysql或data dir路徑找不到，或權限不正確等都會記錄在此

3.事件調度器運行一個事件時產生的信息

一旦mysql調度啟動一個計劃任務的時候，它也會將相關信息記錄在錯誤日志中

4.在從服務器上啟動從服務器進程時產生的信息

在復制環境下，從服務器進程的信息也會被記錄進錯誤日志

刪除/刷新日志

在mysql5.5.7之前：數據庫管理員可以刪除很長時間之前的錯誤日志，以保證mysql服務器上的硬盤空間。mysql數據庫中，可以使用mysqladmin命令開啟新的錯誤日志。

mysqladmin –u root –p flush-logs

在mysql5.5.7之后：服務器將關閉此項功能。只能使用重命名原來的錯誤日志文件，手動沖洗日志創建一個新的：

mv stu18.magedu.com.err  stu18.magedu.com.err.old
mysqladmin flush-logs

3.二進制日志(bin log)

MySQL的二進制日志（binary log）是一個二進制文件，主要記錄所有數據庫表結構變更（例如CREATE、ALTER TABLE…）以及表數據修改（INSERT、UPDATE、DELETE…）的所有操作。二進制日志（binary log）中記錄了對MySQL數據庫執行更改的所有操作，并且記錄了語句發生時間、執行時長、操作數據等其它額外信息，但是它不記錄SELECT、SHOW等那些不修改數據的SQL語句

bin log主要應用場景：

一是用于數據恢復

二是用于數據復制

在MYSQL8版本中，默認二進制日志是開啟著的

show variables like '%log_bin%';

日志格式

binlog格式分為: STATEMENT、ROW和MIXED三種?

STATEMENT

STATEMENT格式的binlog記錄的是數據庫上執行的原生SQL語句，對數據進行修改的sql都記錄在日志文件中

ROW

基于行的日志記錄，記錄的是每一行的數據變更（默認）

MIXED

MIXED也是MySQL默認使用的二進制日志記錄方式，但MIXED格式默認采用基于語句的復制，混合了STATEMENT和ROW兩種格式，默認采用STATEMENT，在某些特殊情況下回自動切換為ROW進行記錄

show variables like '%binlog_format%';

查看日志

當MySQL創建二進制日志文件時，先創建一個以“filename”為名稱、以“.index”為后綴的文件，再創建一個以“filename”為名稱、以“.000001”為后綴的文件

MySQL服務重新啟動一次，以“.000001”為后綴的文件就會增加一個，并且后綴名按1遞增。即日志文件的個數與MySQL服務啟動的次數相同；如果日志長度超過了 max_binlog_size 的上限（默認是1GB），就會創建一個新的日志文件

binlog的相關參數?

max_binlog_size

可以通過max_binlog_size參數來限定單個binlog文件的大小（默認1G），如果當前binlog文件的大小達到了參數指定的閾值，會創建一個新的binlog文件作為當前活躍的binlog文件，后續所有對數據庫的修改都會記錄到新的binlog文件中。

對于binlog文件的大小，有個需要注意的地方是，binlog文件可能會大于max_binlog_size參數設定的閾值。由于一個事務所產生的所有事件必須記錄在同一個binlog文件中，所以即使binlog文件的大小達到max_binlog_size參數指定的大小，也要等到當前事務的所有事件全部寫入到binlog文件中才能切換，這樣就會出現binlog文件的大小大于max_binlog_size參數指定的大小的情況。

binlog_cache_size

當使用事務的表存儲引擎（如InnoDB存儲引擎）時，所有未提交（uncommitted）的二進制日志會被記錄到一個緩存中去，等該事務提交（committed）時直接將緩沖中的二進制日志寫入二進制日志文件，而該緩沖的大小由binlog_cache_size決定，默認大小為32K。此外，binlog_cache_size是基于會話（session）的，也就是說，當一個線程開始一個事務時，MySQL會自動分配一個大小為binlog_cache_size的緩存，因此該值的設置需要相當小心，不能設置過大。當一個事務的記錄大于設定的binlog_cache_size時，MySQL會把緩沖中的日志寫入一個臨時文件中，因此該值又不能設得太小。通過SHOW GLOBAL STATUS命令查看binlog_cache_use、binlog_cache_disk_use的狀態，可以判斷當前binlog_cache_size的設置是否合適。binlog_cache_use記錄了使用緩沖寫二進制日志的次數，binlog_cache_disk_use記錄了使用臨時文件寫二進制日志的次數。

sync_binlog

在MySQL 5.7之前版本默認情況下，二進制日志并不是在每次寫的時候同步的磁盤（用戶可以理解為緩沖寫）。因此，當數據庫所在的操作系統發生宕機時，可能會有最后一部分數據沒有寫入二進制文件中，這會給恢復和復制帶來問題。參數sync_binlog=[N]中的N表示每提交多少個事務就進行binlog刷新到磁盤。如果將N設為1，即sync_binlog=1表示采用同步寫磁盤的方式來寫二進制日志，每次事務提交時就會刷新binlog到磁盤；sync_binlog為0表示刷新binlog時間點由操作系統自身來決定，操作系統自身會每隔一段時間就會刷新緩存數據到磁盤；sync_binlog為N表示每N個事務提交會進行一次binlog刷新。如果使用Innodb存儲引擎進行復制，并且想得到最大的高可用性，需要將此值設置為1。不過該值為1時，確時會對數據庫IO系統帶來一定的開銷。

但是，即使將sync_binlog設為1，還是會有一種情況導致問題的發生。當使用InnoDB存儲引擎時，在一個事務發出COMMIT動作之前，由于sync_binlog為1，因此會將二進制日志立即寫入磁盤。如果這時已經寫入了二進制日志，但是提交還沒有發生，并且此時發生了宕機，那么在MySQL數據庫下次啟動時，由于COMMIT操作并沒有發生，這個事務會被回滾掉。但是二進制日志已經記錄了該事務信息，不能被回滾。對于這個問題，MySQL使用了兩階段提交來解決的，簡單說就是對于已經寫入到binlog文件的事務一定會提交成功，而沒有寫入到binlog文件的事務就會進行回滾，從而保證二進制日志和InnoDB存儲引擎數據文件的一致性，保證主從復制的安全。

binlog-do-db&binlog-ignore-db

參數binlog-do-db和binlog-ignore-db表示需要寫入或者忽略寫入哪些庫的二進制日志。默認為空，表示需要同步所有庫的日志到二進制日志。

log-slave-update

如果當前數據庫是復制中的slave角色，則它不會將master取得并執行的二進制日志寫入自己的二進制日志文件中去。如果需要寫入，要設置log-slave-update。如果需要搭建master–>slave–>slave架構的復制，則必須設置該參數。

binlog-format

binlog_format參數十分重要，用來設置二進制日志的記錄格式,詳情參考(6.5 binlog格式)

log_bin_trust_function_creators

默認為OFF，這個參數開啟會限制存儲過程、Function、觸發器的創建

刪除日志

對于比較繁忙的業務系統，每天生成的binlog數據巨大，如果長時間不清除，將占用大量磁盤空間

也可以在mysql的配置文件中配置二進制日志的國企時間，設置了之后，二進制日志過期會自動刪除?

show variables like '%binlog_expire_logs_seconds%';

4.查詢日志(general log)?

查詢日志里的內容不要被"查詢日志"誤導，認為里面只存儲select語句，其實不然，查詢日志里面記錄了數據庫執行的所有命令，不管語句是否正確，都會被記錄

insert查詢為了避免數據沖突，如果此前插入過數據，當前插入的數據如果跟主鍵或唯一鍵的數據重復那肯定會報錯

update時也會查詢因為更新的時候很可能會更新某一塊數據

delete查詢，只刪除符合條件的數據

因此都會產生日志，在并發操作非常多的場景下，查詢信息會非常多，那么如果都記錄下來會導致IO非常大，影響MySQL性能，因此如果不是在調試環境下，是不建議開啟查詢日志功能的

查詢日志配置

參數general_log用來控制開啟、關閉MySQL查詢日志

參數general_log_file用來控制查詢日志的位置

如果你要判斷MySQL數據庫是否開啟了查詢日志，可以使用下面命令。general_log為ON表示開啟查詢日志，OFF表示關閉查詢日志

show variables like '%general%';

打開查詢日志?

如果要開啟查詢日志，可以設置以下配置：

修改MySQL的配置文件/etc/my.cnf文件，添加內容如下

如果開啟了查詢日志，參數log_output控制著查詢日志的存儲方式， log_output可以設置為以下4種值:

FILE : 表示日志存儲在文件中

TABLE : 表示日志存儲在mysql庫中的general_log表中

FILE, TABLE : 表示將日志同時存儲在文件和general_log表中，改值會徒增很多IO壓力，一般不會這樣設置

NONE : 表示不記錄日志，即使general_log設置為ON，如果log_output設置為NONE，也不會記錄查詢日志

log_output不僅控制查詢日志的輸出，也控制著慢查詢日志的輸出，即: log_output設置為FILE，就表示查詢日志和慢查詢日志都存放在文件中，設置為TABLE，查詢日志和慢查詢日志都存放在mysql庫中的general_log表中

查看log_output設置:

show variables like 'log_output';

關閉查詢日志

可以在my.cnf里添加,1開啟（0關閉）,這樣要重啟才能生效

general-log = 0

然后重啟mysql

5.慢查詢日志(slow query log)

慢查詢會導致CPU，IOPS，內存消耗過高。當數據庫遇到性能瓶頸時，大部分時間都是由于慢查詢導致的。開啟慢查詢日志，可以讓MySQL記錄下查詢超過指定時間的語句，之后運維人員通過定位分析，能夠很好的優化數據庫性能

慢查詢日志記錄的慢查詢不僅僅是執行比較慢的SELECT語句，還有INSERT，DELETE，UPDATE，CALL等DML操作，只要超過了指定時間，都可以稱為"慢查詢"，被記錄到慢查詢日志中

默認情況下，慢查詢日志是不開啟的，只有手動開啟了，慢查詢才會被記錄到慢查詢日志中

慢查詢日志記錄了所有執行時間超過參數long_query_time設置值并且掃描記錄數不小于min_examined_row_limit的所有的SQL語句的日志，默認未開啟。long_query_time默認為10秒，最小為0，精度可以到微妙

?show variables like "%slow%";

set global slow_query_log='ON';也可以打開慢查詢日志，只是對當前數據庫有效，如果MySQL數據庫重啟后就會失效。所以如果要永久生效，就要修改配置文件 my.cnf?

默認情況下，不會記錄管理語句，也不會記錄不使用索引進行查找的查詢。可以使用log_slow_admin_statements和更改此行為log_queries_not_using_indexes

6. 中繼日志(relay log)

中繼日志（relay log）只在主從服務器架構的從服務器上存在。從服務器（slave）為了與主服務器(Master)保持一致，要從主服務器讀取二進制日志的內容，并且把讀取到的信息寫入本地的日志文件中，這個從服務器本地的日志文件就叫中繼日志。然后，從服務器讀取中繼日志，并根據中繼日志的內容對從服務器的數據進行更新，完成主從服務器的數據同步

relay log是復制過程中產生的日志，很多方面都跟binary log差不多，區別是: relay log是從庫服務器I/O線程將主庫服務器的二進制日志讀取過來記錄到從庫服務器本地文件，然后從庫的SQL線程會讀取relay-log日志的內容并應用到從庫服務器上

中繼日志作用

中繼日志用于主從服務器架構中，從服務器用來存放主服務器二進制日志內容的一個中間文件。從服務器通過讀取中繼日志的內容，來同步主服務器上的操作

中繼日志是連接mastert(主服務器)和slave(從服務器)的信息，它是復制的核心，I/O線程將來自master的binlog存儲到中繼日志中，中繼日志充當緩沖，這樣master不必等待slave執行完成就可以發送下一個binlog

show variables like '%relay%';

相關參數?

max_relay_log_size

標記relay log 允許的最大值，如果該值為0，則默認值為max_binlog_size(1G)；如果不為0，則max_relay_log_size則為最大的relay_log文件大小；

relay_log

定義relay_log的位置和名稱，如果值為空，則默認位置在數據文件的目錄，文件名為host_name-relay-bin.nnnnnn（By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory）；

relay_log_index

同relay_log，定義relay_log的位置和名稱；

relay_log_info_file

設置http://relay-log.info的位置和名稱（http://relay-log.info記錄MASTER的binary_log的恢復位置和relay_log的位置）

relay_log_purge

是否自動清空不再需要中繼日志時。默認值為1(啟用)。

relay_log_recovery

當slave從庫宕機后，假如relay-log損壞了，導致一部分中繼日志沒有處理，則自動放棄所有未執行的relay-log，并且重新從master上獲取日志，這樣就保證了relay-log的完整性。默認情況下該功能是關閉的，將relay_log_recovery的值設置為 1時，可在slave從庫上開啟該功能，建議開啟。

relay_log_space_limit

防止中繼日志寫滿磁盤，這里設置中繼日志最大限額。但此設置存在主庫崩潰，從庫中繼日志不全的情況，不到萬不得已，不推薦使用；

sync_relay_log

這個參數和sync_binlog是一樣的，當設置為1時，slave的I/O線程每次接收到master發送過來的binlog日志都要寫入系統緩沖區，然后刷入relay log中繼日志里，這樣是最安全的，因為在崩潰的時候，你最多會丟失一個事務，但會造成磁盤的大量I/O。當設置為0時，并不是馬上就刷入中繼日志里，而是由操作系統決定何時來寫入，雖然安全性降低了，但減少了大量的磁盤I/O操作。這個值默認是0，可動態修改。

sync_relay_log_info

這個參數和sync_relay_log參數一樣，當設置為1時，slave的I/O線程每次接收到master發送過來的binlog日志都要寫入系統緩沖區，然后刷入relay-log.info里，這樣是最安全的，因為在崩潰的時候，你最多會丟失一個事務，但會造成磁盤的大量I/O。當設置為0時，并不是馬上就刷入relay-log.info里，而是由操作系統決定何時來寫入，雖然安全性降低了，但減少了大量的磁盤I/O操作。這個值默認是0，可動態修改。

7.事務日志

數據庫數據存放的文件稱為data file；日志文件稱為log file；數據庫數據是有緩存的，如果沒有緩存，每次都寫或者讀物理disk，那性能就太低下了。數據庫數據的緩存稱為data buffer，日志（redo）緩存稱為log buffer；既然數據庫數據有緩存，就很難保證緩存數據（臟數據）與磁盤數據的一致性

為了保證事務的ACID特性，就不得不說MySQL InnoDB引擎的事務日志:?重做日志redo和回滾日志undo

注: 在數據庫的世界里，數據從來都不重要，日志才是最重要的，有了日志就有了一切

看查日志文件設置狀態

show variables like 'innodb_%';

innodb_log_files_in_group:DB 中設置幾組事務日志，默認是2；
innodb_log_group_home_dir 事務日志存放目錄，不設置；?

1.重做日志(redo log)

redo log包括兩部分：一是內存中的日志緩沖(redo log buffer)，該部分日志是易失性的；二是磁盤上的重做日志文件(redo log file)，該部分日志是持久的，并且是事務的記錄是順序追加的，性能非常高(磁盤的順序寫性能比內存的寫性能差不了太多)

InnoDB用日志把隨機IO變成順序IO。一旦日志安全寫到磁盤，事務就持久化了，即使斷電了，InnoDB可以重放日志并且恢復已經提交的事務

持久化

事務被提交，數據一定會被寫入到數據庫中并持久存儲起來，通常來說當事務已經被提交之后，就無法再次回滾了

重做日志實現持久化

與原子性一樣，事務的持久性也是通過日志來實現的，MySQL 使用重做日志（ redo log ）實現事務的持久性，重做日志由兩部分組成，一是內存中的重做日志緩沖區，因為重做日志緩沖區在內存中，所以它是易失的；另一個就是在磁盤上的重做日志文件，它是持久的

重做日志執行

在MySQL中事務執行 commit 提交了之后，但是服務器宕機了，數據還沒有寫入磁盤，在MySQL重啟服務之后會重新執行這個重做日志寫入數據

redo log參數?

innodb_log_files_in_group

redo log 文件的個數，命名方式如：ib_logfile0，iblogfile1... iblogfilen。默認2個，最大100個

innodb_log_file_size

文件設置大小，默認值為 48M，最大值為512G，注意最大值指的是整個 redo log系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大于最大值512G

innodb_log_group_home_dir

文件存放路徑

innodb_log_buffer_size

Redo Log 緩存區，默認8M，可設置1-8M。延遲事務日志寫入磁盤，把redo log 放到該緩沖區，然后根據 innodb_flush_log_at_trx_commit參數的設置，再把日志從buffer 中flush 到磁盤中

2.回滾日志(undo log)

undo log有兩個作用：提供回滾和多個行版本控制(MVCC)

在數據修改的時候，不僅記錄了redo，還記錄了相對應的undo，如果因為某些原因導致事務失敗或回滾了，可以借助該undo進行回滾

undo log和redo log記錄物理日志不一樣，它是邏輯日志。可以認為當delete一條記錄時，undo log中會記錄一條對應的insert記錄，反之亦然，當update一條記錄時，它記錄一條對應相反的update記錄

當執行rollback時，就可以從undo log中的邏輯記錄讀取到相應的內容并進行回滾。有時候應用到行版本控制的時候，也是通過undo log來實現的：當讀取的某一行被其他事務鎖定時，它可以從undo log中分析出該行記錄以前的數據是什么，從而提供該行版本信息，讓用戶實現非鎖定一致性讀取

回滾日志實現原子性

想要保證事務的原子性，就需要在異常發生時，對已經執行的操作進行回滾，而在 MySQL 中，恢復機制是通過回滾日志（ undo log ）實現的，所有事務進行的修改都會先記錄到這個回滾日志中，然后再對數據庫中的對應行進行寫入。注意：系統發生崩潰、數據庫進程直接被殺死后，當用戶再次啟動數據庫進程時，還能夠立刻通過查詢回滾日志將之前未完成的事務進行回滾，這也就需要回滾日志必須先于數據持久化到磁盤上，是我們需要先寫日志后寫數據庫的主要原因。在日志文件中：在事務中使用的每一條 insert into 都對應了一條 delete ，每一條 update 也對應一條相反的 update 語句

回滾日志執行

1.手動執行回滾命令時會執行。

2.如果程序在事務執行之后，提交命令執行之前出現了異常，在下次 MySQL 服務重啟的時候會執行

undo log相關參數

?show global variables like '%undo%';

show global variables like '%truncate%';?

innodb_max_undo_log_size

控制最大undo tablespace文件的大小，當啟動了innodb_undo_log_truncate 時，undo tablespace 超過innodb_max_undo_log_size 閥值時才會去嘗試truncate。該值默認大小為1G，truncate后的大小默認為10M。

innodb_undo_tablespaces

設置undo獨立表空間個數，范圍為0-128，默認為0，0表示表示不開啟獨立undo表空間且 undo日志存儲在ibdata 文件中。該參數只能在最開始初始化MySQL實例的時候指定，如果實例已創建，這個參數是不能變動的，如果在數據庫配置文件 .cnf 中指定innodb_undo_tablespaces 的個數大于實例創建時的指定個數，則會啟動失敗，提示該參數設置有誤。

innodb_undo_log_truncate

InnoDB的purge線程，根據innodb_undo_log_truncate設置開啟或關閉innodb_max_undo_log_size的參數值，以及truncate的頻率來進行空間回收和 undo file 的重新初始化。

innodb_purge_rseg_truncate_frequency

用于控制purge回滾段的頻度，默認為128。假設設置為n，則說明，當Innodb Purge操作的協調線程 purge事務128次時，就會觸發一次History purge，檢查當前的undo log 表空間狀態是否會觸發truncate