MySQL日誌介紹

• 2019 年 11 月 6 日
• 筆記

MySQL日誌簡介

MySQL的日誌主要分為六類：

（1） 錯誤日誌log_error：記錄MySQL服務的啟動、運行或停止MySQL服務時出現的問題

（2） 查詢日誌general_log：記錄簡歷的客戶端連接和執行的語句

（3） 慢查詢日誌slow_query_log：記錄所有執行時間超過long_query_time的所有查詢或不使用索引的查詢

（4）二進制日誌binlog：記錄所有更改數據的語句，可以用於數據複製

（5）事務日誌innodb_log：用來記錄數據庫更新情況的文件，它可以記錄針對數據庫的任何操作，並將記錄的結果保存到獨立的文件中

（6）中繼日誌relay_log:複製中使用

各類日誌簡介

1.二進制日誌binlog

主要記錄MySQL數據庫的變化，二進制日誌以一種有效的格式，並且是事務安全的方式包含更新日誌中可用的所有信息，二進制日誌包含了所有更新了數據或者已經潛在更新了數據的語句，語句以「事件」的形式保存，描述數據更改。

二進制日誌還包含關於每個更新數據庫的語句的執行時間信息，不包含沒有修改任何數據的語句，如果想要記錄所有的語句，需要使用一般查詢日誌，使用二進制日誌的主要目的是最大可能的恢複數據庫，因為二進制日誌包含備份後進行的所有更新。

binlog附加參數

max_binlog_size:

設置binlog的最大存儲上線，當日誌達到該日誌的上限時，mysql會重新創建一個日誌開始記錄，不過偶爾也會超出該設置的binlog，一般都是因為即將達到上限時候，產生了一個比較大的事物，為了保證事物的安全，mysql不會將同一個事物分開記錄到兩個binlog

binlog-do-db=db_name:

明確告訴mysql，需要對某個數據庫記錄binlog，如果有了binlog-do-db=db_name 顯示指定，mysql會忽略正對其他書庫執行query，而僅僅記錄只對指定數據庫執行的query

binlog-ignore-db=db_name:

顯示的指定忽略某個數據庫的binlog記錄。

binlog-do-db 和binlog-ignore-db參數:

有一個共同的概念，參數db_name 不是指query 語句更新的數據所在的數據庫，而是執行query的時候，當前所處的數據庫。不論更新哪個數據庫的數據，mysql僅僅比較當前連接所處的數據庫與參數設置的數據庫名。而不會分析query語句所更新的數據庫所在數據庫。

binlog_cache_size :

當使用事務的存儲引擎InnoDB時，所有未提交的事務會記錄到一個緩存中，等待事務提交時，直接將緩衝中的二進制日誌寫入二進制日誌文件，而該緩衝的大小由binlog_cache_size決定，默認大小為32KB，此外，binlog_cache_size是基於回話的，也就是，當一個線程開始一個事務時，mysql會自動分配一個大小為binlog_cache_size的緩存，因此該值得設置需要相當小心，可以通過show global status 查看binlog_cache_use、binlog_cache_disk_use的狀態，可以判斷當前binlog_cache_size的設置是否合適。

sync_binlog:

參數sync_binlog=[N]表示每寫緩存多少次就同步到磁盤，如果將N設置為1，則表示採用同步寫磁盤的方式來寫二進制日誌，該參數很重要，這個以後還會提到。值得注意的是，在將該參數設置為1時，也應該將innodb_support_xa設為1來解決，這可以確保二進制日誌和InnoDB存儲引擎數據文件的同步

expire_logs_days:

定義了MySQL清楚過期日誌的時間

二進制日誌的開啟方式：

（1）指定日誌路徑

mysqld_safe –user=mysql –log-bin=[path] & 如果沒有指定文件名 默認mysql-bin，默認路徑為datadir目錄

（2）編輯my.cnf [mysqld] log-bin=[path]

log-bin= /var/log/mysql/mysql-bin.log –指定二進制日誌的名稱

log_bin_index= /var/log/mysql/mysql-bin.log.index

–二進制日誌索引的名稱

relay_log= /var/log/mysql/mysql-bin.relay —中繼日誌的名稱

relay_log_index= /var/log/mysql/mysql-bin.relay.index

—中繼日誌索引的名稱

二進制日誌的查看方式：

show binary logs可以查看當前的二進制日誌文件個數以及文件名

+—————–+————+

| Log_name | File_size |

+—————–+————+

| mysqlbin.000001 | 27365 |

| mysqlbin.000002 | 1029074 |

| mysqlbin.000003 | 3457 |

| mysqlbin.000004 | 126 |

| mysqlbin.000005 | 1074144657 |

| mysqlbin.000006 | 1074572441

mysqlbinlog命令可以用來查看當前日誌裏面的內容

如果執行FLUSH LOGS，log-bin 會使用新的二進制日誌編號

2.通用查詢日誌general_log

通用查詢日誌記錄在MySQL上執行過的SQL語句，包含查詢語句與啟動時間。建議不是在調試環境下不要開啟查詢日誌，因為它會不斷佔據磁盤空間，並且產生大量的IO，一般是在需要採樣分析或者調試的時候才開啟。

通用日誌的開啟方法：

(1)執行命令開啟：

set global general_log=1;//=0就是關閉通用查詢日誌

此時在默認在mysql的data目錄中生成了localhost.log文件，該文件就是通用查詢日誌文件

（2）my.cnf中配置的方式，在my.cnf文件的[mysqld]下面任意一行增加或修改配置：

general_log-file[=path/[filename]] //=後面都是可選的，即有默認的保存日誌的文件

general_log=1 //表示開啟通用查詢日誌

推薦使用第一種方式開啟或關閉通用查詢日誌，因為my.cnf的修改要生效需要重啟mysql服務，並且這種通用查詢日誌的開啟不需要一直開啟而是短時間開啟就需要關閉，所以在 my.cnf關閉時又要重啟mysql服務。

3.錯誤日誌err_log

錯誤日誌文件包含了當mysqld啟動和停止時，以及服務器在運行過程中發生嚴重錯誤時候的相關信息，在mysql中，錯誤日誌非常有用，MySQL會將啟動和停止數據庫信息以及一些錯誤信息記錄保存到錯誤日誌文件中。

默認時錯誤日誌的存放位置在數據目錄中，名稱為「server_name.err」 錯誤日誌記錄的事件： a)、服務器啟動關閉過程中的信息 b)、服務器運行過程中的錯誤信息 c)、事件調試器運行一個事件時間生的信息 d)、在從服務器上啟動從服務器進程時產生的信息

查看與日誌相關的變量： mysql> SHOW GLOBAL VARIABLES LIKE '%log_error%';

my.cnf中錯誤日誌開啟： log_error=/PATH/TO/ERROR_LOG_FILENAME 例如：log_error = /mydata/data/hostname.err 定義錯誤日誌文件。作用範圍為全局或會話級別，可用於配置文件，屬非動態變量。

log_warnings=# 設定是否將警告信息記錄進錯誤日誌。默認設定為1，表示啟用；可以將其設置為0以禁用；而其值為大於1的數值時表示將新發起連接時產生的「失敗的連接」和「拒絕訪問」類的錯誤信息也記錄進錯誤日誌。

刪除錯誤日誌之後要想重建日誌：

在運行狀態下刪除錯誤日誌文件後，mysql並不會自動創建日誌文件，flush logs在重建加載日誌的時候，如果文件不存在，則會自動創建，所以在刪除錯誤日誌之後，如果需要重建日誌文件，需要在服務端執行以下命令：

mysqladmin -uroot -p flush-logs

4.慢查詢日誌log-slow-queries

慢查詢日誌是記錄查詢時長超過指定時間的日誌，慢查詢日誌主要用來記錄執行時間較長的查詢語句，

mysql中慢查詢日誌默認是關閉的，

開啟方法如下：

（1）可以通過配置文件my.cnf中的log-slow-queries選項打開，設定是否啟用慢查詢日誌。0或OFF表示禁用，1或ON表示啟用。日誌信息的輸出位置取決於log_output變量的定義，如果其值為NONE，則即便slow_query_log為ON，也不會記錄任何慢查詢信息。作用範圍為全局級別，可用於選項文件，屬動態變量。

（2）也可以在MySQL服務啟動的時候使用–log-slow-queries[=file_name]啟動慢查詢日誌

啟動慢查詢時，需要在my.cnf文件中配置long_query_time選項指定記錄閾值，如果某條查詢語句的查詢時間超過了這個值，這個查詢過程將被記錄到慢查詢日誌文件中。

5.事務日誌

Innodb主要是通過事務日誌實現ACID特性

事務日誌包括：重做日誌redo和回滾日誌undo

事務日誌文件名為"ib_logfile0"和「ib_logfile1」，默認存放在表空間所在目錄，它是用來記錄數據庫更新情況的文件，它可以記錄針對數據庫的任何操作，並將記錄的結果保存到獨立的文件中。對於每一次數據庫更新的過程，事務日誌文件都有非常全面的記錄。根據這些記錄可以恢複數據庫更新前的狀態。

與事務日誌相關變量： innodb_log_group_home_dir=/PATH/TO/DIR： 設定InnoDB重做日誌文件的存儲目錄。在缺省使用InnoDB日誌相關的所有變量時，其默認會在數據目錄中創建兩個大小為5MB的名為ib_logfile0和ib_logfile1的日誌文件。作用範圍為全局級別，可用於選項文件，屬非動態變量。

innodb_log_file_size={108576 .. 4294967295} 設定日誌組中每個日誌文件的大小，單位是位元組，默認值是5MB。較為明智的取值範圍是從1MB到緩存池體積的1/n，其中n表示日誌組中日誌文件的個數。日誌文件越大，在緩存池中需要執行的檢查點刷寫操作就越少，這意味着所需的I/O操作也就越少，然而這也會導致較慢的故障恢復速度。作用範圍為全局級別，可用於選項文件，屬非動態變量。

innodb_log_files_in_group={2 .. 100} 設定日誌組中日誌文件的個數。InnoDB以循環的方式使用這些日誌文件。默認值為2。作用範圍為全局級別，可用於選項文件，屬非動態變量。

innodb_log_buffer_size={262144 .. 4294967295} 設定InnoDB用於輔助完成日誌文件寫操作的日誌緩衝區大小，單位是位元組，默認為8MB。較大的事務可以藉助於更大的日誌緩衝區來避免在事務完成之前將日誌緩衝區的數據寫入日誌文件，以減少I/O操作進而提升系統性能。因此，在有着較大事務的應用場景中，建議為此變量設定一個更大的值。作用範圍為全局級別，可用於選項文件，屬非動態變量。

innodb_flush_log_at_trx_commit = 1 # 表示有事務提交後，不會讓事務先寫進buffer，再同步到事務日誌文件，而是一旦有事務提交就立刻寫進事務日誌，並且還每隔1秒鐘也會把buffer里的數據同步到文件，這樣IO消耗大，默認值是"1"，可修改為「2」

innodb_locks_unsafe_for_binlog OFF #這個變量建議保持OFF狀態，詳細的原理不清楚

innodb_mirrored_log_groups = 1 #事務日誌組保存的鏡像數

6.中繼日誌 在複製環境中產的的日誌信息

與中繼日誌相關的變量： log_slave_updates 用於設定複製場景中的從服務器是否將從主服務器收到的更新操作記錄進本機的二進制日誌中。本參數設定的生效需要在從服務器上啟用二進制日誌功能。

relay_log=file_name 設定中繼日誌的文件名稱，默認為host_name-relay-bin。也可以使用絕對路徑，以指定非數據目錄來存儲中繼日誌。作用範圍為全局級別，可用於選項文件，屬非動態變量。

relay_log_index=file_name 設定中繼日誌的索引文件名，默認為為數據目錄中的host_name-relay-bin.index。作用範圍為全局級別，可用於選項文件，屬非動態變量。

relay-log-info-file=file_name 設定中繼服務用於記錄中繼信息的文件，默認為數據目錄中的relay-log.info。作用範圍為全局級別，可用於選項文件，屬非動態變量。 relay_log_purge={ON|OFF} 設定對不再需要的中繼日誌是否自動進行清理。默認值為ON。作用範圍為全局級別，可用於選項文件，屬動態變量。

relay_log_space_limit=# 設定用於存儲所有中繼日誌文件的可用空間大小。默認為0，表示不限定。最大值取決於系統平台位數。作用範圍為全局級別，可用於選項文件，屬非動態變量。

max_relay_log_size={4096..1073741824} 設定從服務器上中繼日誌的體積上限，到達此限度時其會自動進行中繼日誌滾動。此參數值為0時，mysqld將使用max_binlog_size參數同時為二進制日誌和中繼日誌設定日誌文件體積上限。作用範圍為全局級別，可用於配置文件，屬動態變量。