MySQL之日志管理

日志

事務(wù)日志：transaction log

錯(cuò)誤日志：error log

查詢?nèi)罩荆?/span>query log

慢查詢?nèi)罩荆?/span>slow query log

二進(jìn)制日志：binary log

中繼日志：reley log

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

事務(wù)日志

事務(wù)日志：transaction log

事務(wù)型存儲(chǔ)引擎自行管理和使用

redo log

undo log

Innodb事務(wù)日志相關(guān)配置：

show variables like '%innodb_log%';

innodb_log_file_size 5242880 每個(gè)日志文件大?。ńㄗh按生產(chǎn)情況調(diào)大，如1G，否則執(zhí)行大的事務(wù)可能會(huì)直接提交，無(wú)法回滾）

innodb_log_files_in_group 2 日志組成員個(gè)數(shù)（建議按生產(chǎn)情況調(diào)多，如3個(gè)）

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

中繼日志：relay log

主從復(fù)制架構(gòu)中，從服務(wù)器用于保存從主服務(wù)器的二進(jìn)制日志中讀取到的事件

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

錯(cuò)誤日志

錯(cuò)誤日志

mysqld啟動(dòng)和關(guān)閉過(guò)程中輸出的事件信息

mysqld運(yùn)行中產(chǎn)生的錯(cuò)誤信息

event scheduler運(yùn)行一個(gè)event時(shí)產(chǎn)生的日志信息

在主從復(fù)制架構(gòu)中的從服務(wù)器上啟動(dòng)從服務(wù)器線程時(shí)產(chǎn)生的信息

錯(cuò)誤日志相關(guān)配置

SHOW GLOBAL VARIABLES LIKE 'log_error'

錯(cuò)誤文件路徑：

log_error=/PATH/TO/LOG_ERROR_FILE

是否記錄警告信息至錯(cuò)誤日志文件

log_warnings=1|0 默認(rèn)值1

通用日志

通用日志：記錄查詢操作

文件：file，默認(rèn)值

表：table

通用日志相關(guān)設(shè)置

general_log=ON|OFF（通用日志，誰(shuí)操作都記錄，量會(huì)很大，不建議開(kāi)啟）

general_log_file=HOSTNAME.log

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

慢查詢?nèi)罩?/span>

慢查詢?nèi)罩荆河涗泩?zhí)行查詢時(shí)長(zhǎng)超出指定時(shí)長(zhǎng)的操作

slow_query_log=ON|OFF 開(kāi)啟或關(guān)閉慢查詢

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

slow_query_log_file=HOSTNAME-slow.log 慢查詢?nèi)罩疚募?/span>

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 不使用索引也沒(méi)有達(dá)到慢查詢閥值的語(yǔ)句是否記錄日志，默認(rèn)OFF，即不記錄（可以開(kāi)啟，看哪些命令記錄得多了可以優(yōu)化，例如加上索引）

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

log_slow_verbosity= Query_plan,explain 記錄內(nèi)容

log_slow_queries = OFF 同slow_query_log 新版已廢棄

例：

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

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

二進(jìn)制日志

記錄導(dǎo)致數(shù)據(jù)改變或潛在導(dǎo)致數(shù)據(jù)改變的SQL語(yǔ)句

記錄已提交的日志

不依賴于存儲(chǔ)引擎類型

功能：通過(guò)“重放”日志文件中的事件來(lái)生成數(shù)據(jù)副本

注意：建議二進(jìn)制日志和數(shù)據(jù)文件分開(kāi)存放

二進(jìn)制日志記錄格式

二進(jìn)制日志記錄三種格式

基于“語(yǔ)句”記錄：statement，記錄語(yǔ)句，默認(rèn)模式

基于“行”記錄：row，記錄數(shù)據(jù)，日志量較大（生產(chǎn)中強(qiáng)烈建議基于“行”記錄）

混合模式：mixed, 讓系統(tǒng)自行判定該基于哪種方式進(jìn)行

格式配置

show variables like '%binlog_format%';

二進(jìn)制日志文件的構(gòu)成

有兩類文件

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

如： mysql-bin.000001

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

二進(jìn)制日志相關(guān)的服務(wù)器變量：

sql_log_bin=ON|OFF：是否記錄二進(jìn)制日志，默認(rèn)ON（此項(xiàng)可以動(dòng)態(tài)更改，例如硬盤滿了想臨時(shí)不記錄日志，可以臨時(shí)關(guān)掉）

log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默認(rèn)OFF，表示不啟用二進(jìn)制日志功能，上述兩項(xiàng)都開(kāi)啟才可（此項(xiàng)不支持動(dòng)態(tài)更改，需寫入配置文件中，需重啟服務(wù)）

binlog_format=STATEMENT|ROW|MIXED：二進(jìn)制日志記錄的格式，默認(rèn)STATEMENT（強(qiáng)烈建議改成ROW）

max_binlog_size=1073741824：?jiǎn)蝹€(gè)二進(jìn)制日志文件的最大體積，到達(dá)最大值會(huì)自動(dòng)滾動(dòng)，默認(rèn)為1G

說(shuō)明：文件達(dá)到上限時(shí)的大小未必為指定的精確值

sync_binlog=1|0：設(shè)定是否啟動(dòng)二進(jìn)制日志即時(shí)同步磁盤功能，默認(rèn)0，由操作系統(tǒng)負(fù)責(zé)同步日志到磁盤（即時(shí)的話效率低，好處是不會(huì)丟東西，一般情況0就行）

expire_logs_days=N：二進(jìn)制日志可以自動(dòng)刪除的天數(shù)。 默認(rèn)為0，即不自動(dòng)刪除（可留個(gè)30天）

二進(jìn)制日志相關(guān)配置

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

SHOW {BINARY | MASTER} LOGS

查看使用中的二進(jìn)制日志文件

SHOW MASTER STATUS

查看二進(jìn)制文件中的指定內(nèi)容

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：二進(jìn)制日志的客戶端命令工具

命令格式：

mysqlbinlog [OPTIONS] log_file…

--start-position=# 指定開(kāi)始位置

--stop-position=#

--start-datetime=

--stop-datetime=

時(shí)間格式：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;

二進(jìn)制日志事件的格式：

# 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))

/*!*/;

事件發(fā)生的日期和時(shí)間：151105 16:31:40

事件發(fā)生的服務(wù)器標(biāo)識(shí)：server id 1

事件的結(jié)束位置：end_log_pos 431

事件的類型：Query

事件發(fā)生時(shí)所在服務(wù)器執(zhí)行此事件的線程的ID：thread_id=1

語(yǔ)句的時(shí)間戳與將其寫入二進(jìn)制文件中的時(shí)間差：exec_time=0

錯(cuò)誤代碼：error_code=0

事件內(nèi)容：

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

清除指定二進(jìn)制日志：

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';

刪除所有二進(jìn)制日志，index文件重新記數(shù)

RESET MASTER [TO #]; 日志文件從#開(kāi)始記數(shù)，默認(rèn)從1開(kāi)始，一般是master第一次啟動(dòng)時(shí)執(zhí)行，MariaDB10.1.6開(kāi)始支持TO #

切換二進(jìn)制日志文件：

FLUSH LOGS;