MySQL之日志管理

日志

事務日志：transaction log

錯誤日志：error log

查詢日志：query log

慢查詢日志：slow query log

二進制日志：binary log

中繼日志：reley log

命令日志：~/.mysql_history，記錄各自終端輸過的mysql命令

事務日志

事務日志：transaction log

事務型存儲引擎自行管理和使用

redo log

undo log

Innodb事務日志相關配置：

show variables like '%innodb_log%';

innodb_log_file_size 5242880 每個日志文件大小（建議按生產情況調大，如1G，否則執行大的事務可能會直接提交，無法回滾）

innodb_log_files_in_group 2 日志組成員個數（建議按生產情況調多，如3個）

innodb_log_group_home_dir ./ 事務文件路徑（建議事務日志跟數據存放在不同目錄中）

中繼日志：relay log

主從復制架構中，從服務器用于保存從主服務器的二進制日志中讀取到的事件

optimize table tbl_name  整理表，例如進行大量修改|刪除操作后，表文件大小并不會減小，執行此命令后可減小

錯誤日志

錯誤日志

mysqld啟動和關閉過程中輸出的事件信息

mysqld運行中產生的錯誤信息

event scheduler運行一個event時產生的日志信息

在主從復制架構中的從服務器上啟動從服務器線程時產生的信息

錯誤日志相關配置

SHOW GLOBAL VARIABLES LIKE 'log_error'

錯誤文件路徑：

log_error=/PATH/TO/LOG_ERROR_FILE

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

log_warnings=1|0 默認值1

通用日志

通用日志：記錄查詢操作

文件：file，默認值

表：table

通用日志相關設置

general_log=ON|OFF（通用日志，誰操作都記錄，量會很大，不建議開啟）

general_log_file=HOSTNAME.log

log_output=TABLE|FILE|NONE（日志記錄在表里/文件里）

慢查詢日志

慢查詢日志：記錄執行查詢時長超出指定時長的操作

slow_query_log=ON|OFF 開啟或關閉慢查詢

long_query_time=N 慢查詢的閥值，單位秒

slow_query_log_file=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

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 新版已廢棄

例：

select sleep(1),name from students; 每查一項休眠1秒

再show profile; 查看命令各階段執行的時間，需先set profiling=1，

二進制日志

記錄導致數據改變或潛在導致數據改變的SQL語句

記錄已提交的日志

不依賴于存儲引擎類型

功能：通過“重放”日志文件中的事件來生成數據副本

注意：建議二進制日志和數據文件分開存放

二進制日志記錄格式

二進制日志記錄三種格式

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

基于“行”記錄：row，記錄數據，日志量較大（生產中強烈建議基于“行”記錄）

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

格式配置

show variables like '%binlog_format%';

二進制日志文件的構成

有兩類文件

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

如： mysql-bin.000001

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

二進制日志相關的服務器變量：

sql_log_bin=ON|OFF：是否記錄二進制日志，默認ON（此項可以動態更改，例如硬盤滿了想臨時不記錄日志，可以臨時關掉）

log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默認OFF，表示不啟用二進制日志功能，上述兩項都開啟才可（此項不支持動態更改，需寫入配置文件中，需重啟服務）

binlog_format=STATEMENT|ROW|MIXED：二進制日志記錄的格式，默認STATEMENT（強烈建議改成ROW）

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

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

sync_binlog=1|0：設定是否啟動二進制日志即時同步磁盤功能，默認0，由操作系統負責同步日志到磁盤（即時的話效率低，好處是不會丟東西，一般情況0就行）

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

二進制日志相關配置

查看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

mysqlbinlog

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" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003;

二進制日志事件的格式：

# 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

清除指定二進制日志：

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;