MySQL之日志管理

本節索引

一、事務日志

二、錯誤日志

三、通用日志

四、慢查詢日志

五、二進制日志

六、性能分析工具profile

MySQL數據庫中支持多種日志類型，通過分析日志，我們可以優化數據庫性能，排除故障，甚至能夠還原

數據，本節內容將帶你了解MySQL數據庫中的日志管理

一、事務日志

作用：

用來記錄數據庫更新情況的文件，它可以記錄針對數據庫的任何操作，并將記錄的結果保存到獨立的文件

中。對于每一次數據庫更新的過程，事務日志文件都有非常全面的記錄。根據這些記錄可以恢復數據庫更

新前的狀態。

日志文件：

事務型存儲引擎自行管理和使用，建議和數據文件分開存放

事務日志包括

Redo log ???? 重做日志

Undo log????? 回滾日志

Redo記錄的是已經全部完成的事務，就是執行了commit的事務，記錄文件是：

ib_logfile0，ib_logfile1……

默認路徑：

/var/lib/mysql/

相關變量：

日志管理：

調整事務日志文件大小及數量

服務器選項中指定

vim /etc/my.cnf

[mysqld]

innodb_log_file_size=10240000

innodb_log_files_in_group=5

注：調整事務日志數量后，必須將原有事務日志文件刪除，否則服務無法重啟成功

生產環境建議：

(1) 生產環境中一般根據具體情況將文件大小調大并增加日志文件數量

(2) 由于事務日志會重復交替覆蓋，所以利用事務日志僅僅可以避免數據庫突然崩潰，如掉電的情況，而不

可用做恢復數據用

 

 

二、錯誤日志

功能：

在MySQL數據庫中，錯誤日志時默認開啟的。用于記錄MySQL 運行過程中較為嚴重的警告和錯誤信息，

以及MySQL每次啟動和關閉的詳細信息。

日志文件：

logerror=/PATH/TO/LOGERRORFILE

默認存放路徑：/var/log/mariadb/mariadb.log

日志管理：

是否記錄警告信息至錯誤日志文件

logwarnings=1|0 ???1表示開啟，0表示關閉，默認值為1

生產環境建議：

數據庫管理員可以刪除很長時間之前的錯誤日志，以保證mysql服務器上的硬盤空間。

可使用重命名原來的錯誤日志文件，手動沖洗日志創建一個新的錯誤日志，方法如下：

[root@CentOS7 mysql]#mv? mariadb.log? mariadb.log.data

[root@CentOS7 mysql]#mysqladmin -pcentos flush-logs

 

 

三、通用日志

功能：

記錄對數據庫的通用操作，包括錯誤的SQL語句，MySQL數據庫默認不啟用通用日志

文件：file，默認值

表：table

日志相關設置：

日志管理：

general_log=ON|OFF

general_log_file=HOSTNAME.log

log_output=TABLE|FILE|NONE

 

optimize table testlog；??????? 整理數據庫，釋放磁盤數據庫碎片

 

生產環境建議：

由于通用日志在并發操作大的環境下會產生大量的信息從而導致不必要的磁盤IO，會影響mysql的性能。

如若不是為了調試數據庫的目的建議不要開啟查詢日志。

 

 

四、慢查詢日志

功能：

記錄執行查詢時長超出指定時長的操作，可以幫助我們定位性能問題。

日志文件：

/var/lib/mysql/Hostname-slow.log

日志管理：

log_slow_filter = admin,filesort,filesort_on_disk,full_join,

full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

上述查詢類型且查詢時長超過long_query_time，則記錄日志

 

log_queries_not_using_indexes=ON 不使用索引或使用全索引掃描，不論是否達到慢查詢閥值的語句是否

記錄日志，默認OFF，即不記錄,生產環境中可監控此項優化數據庫環境

 

log_slow_rate_limit = 1 多少次查詢才記錄，mariadb特有

log_slow_verbosity= Query_plan,explain 記錄內容

log_slow_queries = OFF 同slow_query_log 新版已廢棄

 

生產環境建議：

通過慢查詢日志，可以查找出哪些查詢語句的執行效率很低，以便進行優化。一般建議開啟，它對服務器

性能的影響微乎其微，但是可以記錄mysql服務器上執行了很長時間的查詢語句。

 

示例：模擬慢查詢

打開慢查詢日志：

 

五、二進制日志

作用：

主要用于記錄修改數據或有可能引起數據改變的mysql語句，并且記錄了語句發生時間、執行時長、操作

的數據等等。所以說通過二進制日志可以查詢mysql數據庫中進行了哪些變化，通過“重放”日志文件中的事

件來恢復數據副本。

日志文件的構成：

有兩類文件

(1) 日志文件：mysql|mariadb-bin.文件名后綴，二進制格式

如： mysql-bin.000001

(2) 索引文件：mysql|mariadb-bin.index，文本格式

 

二進制日志記錄有三種格式：

(1) 基于“語句”記錄：statement，記錄語句，默認模式

(2) 基于“行”記錄：row，記錄數據，日志量較大

(3) 混合模式：mixed, 讓系統自行判定該基于哪種方式進行

相關變量：

sql_log_bin=ON|OFF：是否記錄二進制日志，默認ON，需配合log_bin，支持動態修改

log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默認OFF，表示不啟用二進制日志功能，上述兩

項都開啟才可，不寫路徑默認在/var/lib/mysql目錄下，生產中建議分開存放

兩項都是ON狀態才開啟二進制日志功能，缺一不可！

 

binlog_format=STATEMENT|ROW|MIXED：二進制日志記錄的格式，默認STATEMENT

max_binlog_size=1073741824：單個二進制日志文件的最大體積，到達最大值會自動滾動，默認為

1G?????? 說明：文件達到上限時的大小未必為指定的精確值

sync_binlog=1|0：設定是否啟動二進制日志即時同步磁盤功能，默認0，由操作系統負責同步日志到

磁盤

expire_logs_days=N：二進制日志可以自動刪除的天數。 默認為0，即不自動刪除

 

日志管理：

二進制日志相關配置

查看mariadb自行管理使用中的二進制日志文件列表，及大小

SHOW {BINARY | MASTER} LOGS

查看使用中的二進制日志文件

SHOW MASTER STATUS

查看二進制文件中的指定內容

SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]

show binlog events in ‘mysql-bin.000001′ from 6516 limit 2,3

 

清除指定二進制日志：

???????? PURGE { BINARY | MASTER } LOGS

????????????????? { TO ‘log_name’ | BEFORE datetime_expr }

示例：

? PURGE BINARY LOGS TO ‘mariadb-bin.000003’;刪除3前日志

? PURGE BINARY LOGS BEFORE ‘2017-01-23’;

? PURGE BINARY LOGS BEFORE ‘2017-03-22 09:25:30’;

 

刪除所有二進制日志，index文件重新記數

??????? RESET MASTER [TO #]; 日志文件從#開始記數，默認從1開始，一般是

master第一次啟動時執行，MariaDB10.1.6開始支持TO #

切換日志文件：

????? FLUSH LOGS;

 

二進制日志事件格式：

# at 328

#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0

error_code=0

use `mydb`/*!*/;

SET TIMESTAMP=1446712300/*!*/;

CREATE TABLE tb1 (id int, name char(30))

/*!*/;

事件發生的日期和時間：151105 16:31:40

事件發生的服務器標識：server id 1

事件的結束位置：end_log_pos 431

事件的類型：Query

事件發生時所在服務器執行此事件的線程的ID：thread_id=1

語句的時間戳與將其寫入二進制文件中的時間差：exec_time=0

錯誤代碼：error_code=0

事件內容：

GTID：Global Transaction ID，mysql5.6以mariadb10以上版本專屬屬性：GTID

 

生產環境建議：

(1) 強烈建議開啟二進制日志功能！

(2) 強烈建議以基于“行”的格式記錄二進制日志，條件不允許可采用混合模式

 

mysqlbinlog命令

功能：二進制日志的客戶端命令工具

格式：mysqlbinlog [OPTIONS] log_file…

–start-position=# 指定開始位置

–stop-position=#

–start-datetime=

–stop-datetime=

時間格式：YYYY-MM-DD hh:mm:ss

–base64-output[=name]

示例：

mysqlbinlog –start-position=6787 –stop-position=7527 /var/lib/mysql/mariadb-bin.000003

mysqlbinlog –start-datetime=”2018-01-30 20:30:10″ –stopdatetime=”2018-01-30 20:35:22″ mariadb-bin.000003

mysqlbinlog? –start-position=647 –stop-position=797? -v /data/mysqllogs/mysql-bin.000003

 

 

六、性能分析工具profile

功能：

用于跟蹤執行過的sql語句的資源消耗信息，可以幫助查看sql語句的執行情況，可以在做性能分析或者問題

診斷的時候作為參考。

相關變量：

Profiling功能默認關閉，開啟profiling：

MariaDB [hellodb]> set profiling=ON;

顯示每條SQL語句所消耗的時間：

MariaDB [hellodb]> show profiles;

顯示指定Query_id的SQL語句執行過程各階段消耗的時間：

MariaDB [hellodb]> show profile for query 3;