.jpg)
本文給大家匯總介紹了一下mysql中的日志系統(tǒng)的詳細(xì)資料，非常的細(xì)致，有需要的小伙伴可以參考下
做過大型系統(tǒng)的都知道，日志的作用不用小覷，往往到了項(xiàng)目中后期，對項(xiàng)目進(jìn)行優(yōu)化升級都是依據(jù)日志做出升級優(yōu)化的決策的。那么學(xué)習(xí)MySQL，日志部分當(dāng)然不能錯過。我們面試中實(shí)際應(yīng)用的所談到的優(yōu)化都是要從日志中得出來的。系統(tǒng)的學(xué)習(xí)mysql的日志，有助于我們準(zhǔn)確的定位問題，提高自己的工作水平。此外，后面的一系列日志會重點(diǎn)從DBA的運(yùn)維方面進(jìn)行著手，系統(tǒng)的去理解MySQL各方面的配置，做到知己知彼，讓MySQL成為自己得心應(yīng)手的數(shù)據(jù)倉庫。
一、MySQL的mysql型
默認(rèn)情況下，所有的MySQL日志以文件的方式存放在數(shù)據(jù)庫根目錄下：
[root@roverliang?data]#?pwd /usr/local/webserver/extend_lib/mysql/data [root@roverliang?data]#?ls auto.cnf?ibdata1?ib_logfile0?ib_logfile1?mysql?mytest?performance_schema?roverliang?roverliang.err?roverliang.pid?test
MySQL的日志類型有以下幾種：
1.? 錯誤日志（error）,MySQL服務(wù)實(shí)例啟動、運(yùn)行或者停止等相關(guān)信息。
2.? 普通查詢?nèi)罩荆╣eneral）,MySQL服務(wù)實(shí)例運(yùn)行的所有SQL語句或者M(jìn)ySQL命令。
3.? 二進(jìn)制日志（binary）,對數(shù)據(jù)庫執(zhí)行的所有更新語句，不包括select 和show語句。
4.? 慢查詢?nèi)罩荆╯low），執(zhí)行時間超過long_query_time 設(shè)置值的SQL語句，或者沒有使用索引的SQL語句。
二、MySQL日志緩存
一個高速、穩(wěn)定、可靠的系統(tǒng)，緩存在其中必定起著至關(guān)重要的作用。MySQL日志處理也使用了緩存機(jī)制。MySQL日志最初存放在MySQL服務(wù)器的內(nèi)存中，若超過指定的存儲容量，內(nèi)存中的日志則寫（或者刷新flush）到外存中，以數(shù)據(jù)庫表或者以文件的方式永遠(yuǎn)的保存在硬盤中。
三、MySQL錯誤日志（error log）
MySQL的錯誤日志主要記錄MySQL服務(wù)實(shí)例每次啟動、停止的詳細(xì)信息，以及MySQL實(shí)例運(yùn)行過程中產(chǎn)生的警告或者mysql。和其他的日志不同，MySQL的error日志必須開啟，無法關(guān)閉。
默認(rèn)情況下，錯誤日志的文件名為： 主機(jī)名.err。 但error 日志并不會記錄所有的錯誤信息，只有MySQL服務(wù)實(shí)例運(yùn)行過程中發(fā)聲的關(guān)鍵錯誤（critical）才會被記錄下來。
mysql>?show?variables?like?'log_error'G ***************************?1.?row?*************************** Variable_name:?log_error Value:?/usr/local/webserver/extend_lib/mysql/data/roverliang.err 1?row?in?set?(0.02?sec)
四、MySQL普通查詢?nèi)罩?general log)
MySQL普通查詢?nèi)罩居涗汳ySQL服務(wù)實(shí)例所有的操作，如select、update、insert、delete等操作，無論該操作是否成功執(zhí)行。還有MySQL客戶機(jī)與MySQL服務(wù)端連接及斷開的相關(guān)信息，無論連接成功還是失敗。與MySQL普通查詢?nèi)罩居嘘P(guān)的參數(shù)有三個。
[]()general_log mysql>?show?variables?like?'general_log'; +---------------+-------+ |?Variable_name?|?Value?| +---------------+-------+ |?general_log??|?OFF??| +---------------+-------+ 1?row?in?set?(0.01?sec)
可以通過 set @@global.general_log = 1 的方式來開啟普通查詢?nèi)罩尽?/p>
mysql>?set?@@global.general_log?=1; mysql>?show?variables?like?'general_log'; +---------------+-------+ |?Variable_name?|?Value?| +---------------+-------+ |?general_log??|?ON??| +---------------+-------+
但是通過這種方式修改MySQL的mysql只會在當(dāng)前的MySQL實(shí)例運(yùn)行期間生效，一旦MySQL重啟，則會重新恢復(fù)到默認(rèn)的mysql。永久生效的方式是修改mysql 的my.cnf 文件。在mysql后添加：
general_log?=?1? general_log_file
普通查詢?nèi)罩疽坏╅_啟，MySQL服務(wù)實(shí)例將自動創(chuàng)建普通查詢?nèi)罩疚募琯eneral_log_file參數(shù)設(shè)置了普通查詢?nèi)罩疚募奈锢砦恢谩Ｈ缦拢?/p>
mysql>?show?variables?like?'general_log_file'; +------------------+-----------------------------------------------------------+ |?Variable_name??|?Value???????????????????????????| +------------------+-----------------------------------------------------------+ |?general_log_file?|?/usr/local/webserver/extend_lib/mysql/data/roverliang.log?| +------------------+-----------------------------------------------------------+
注意：由于普通查詢?nèi)罩編缀跤涗浟薓ySQL的所有操作，對于數(shù)據(jù)訪問頻繁的數(shù)據(jù)庫服務(wù)器而言，如果開啟MySQL的普通查詢?nèi)罩緦蠓鹊慕档蛿?shù)據(jù)庫的性能，因此建議關(guān)閉普通查詢?nèi)罩尽Ｖ挥性谔厥鈺r期，如需要追蹤某些特殊的查詢?nèi)罩荆梢耘R時打開普通的查詢?nèi)罩尽?/p>
log_output
log_output參數(shù)設(shè)置了普通查詢?nèi)罩疽约奥樵內(nèi)罩镜膬?nèi)容存儲到數(shù)據(jù)庫表中。 可以使用 set @@global.log_output=’table’ 將普通查詢?nèi)罩炯奥樵內(nèi)罩敬嫒雖ysql系統(tǒng)數(shù)據(jù)庫中的general 表 以及slow_log 表中。值得注意的是這兩個表的存儲引擎為CSV,此后查看新的普通查詢?nèi)罩緝?nèi)容時便可以使用SQL語句;
set?@@global.log_output?=?'table'; mysql>?show?variables?like?'log_output'; +---------------+-------+ |?Variable_name?|?Value?| +---------------+-------+ |?log_output??|?TABLE?| +---------------+-------+
五、MySQL慢查詢?nèi)罩荆╯low log）
慢查詢?nèi)罩镜南嚓P(guān)問題，面試的時候面試官非常青睞聊這方面的問題。以前只能夠高談闊論MySQL主從架構(gòu)，以及從各個方面優(yōu)化MySQL，但是平時并沒有真正的去了解過慢查詢?nèi)绾稳ラ_啟以及相關(guān)的配置。
使用MySQL慢查詢?nèi)罩究梢杂行У母?執(zhí)行時間過長 或者 沒有使用索引的查詢語句。這種包括select 語句，update語句，delete語句，以及insert語句，為優(yōu)化查詢提供幫助。與普通查詢?nèi)罩静煌牧硪粋€區(qū)別在于，慢查詢?nèi)罩局话晒?zhí)行過的查詢語句。與MySQL慢查詢?nèi)罩居嘘P(guān)的參數(shù)有5個。
1、slow_query_log
slow_query_log 設(shè)置慢查詢?nèi)罩臼欠耖_啟。
mysql>?show?variables?like?'slow_query_log'; +----------------+-------+ |?Variable_name?|?Value?| +----------------+-------+ |?slow_query_log?|?OFF??| +----------------+-------+
2、slow_query_log_file
慢查詢?nèi)罩疽坏╅_啟，MySQL實(shí)例將自動創(chuàng)建慢查詢?nèi)罩疚募lowquerylog_file 所指定的文件，存放慢查詢?nèi)罩緝?nèi)容。 修改方法和上文所示的一致。直接到my.cnf 文件中進(jìn)行編輯。
3、long_query_time
long_query_time 設(shè)置了慢查詢的時間閾值。默認(rèn)閾值是10s。
4、log_quries_not_using_indexes
log_quries_not_using_indexes 是否將不使用索引的查詢語句記錄到慢查詢?nèi)罩局校瑹o論查詢速度有多快。
mysql>?set?@@global.log_queries_not_using_indexes=1; mysql>?show?variables?like?'log_queries_not_using_indexes'; +-------------------------------+-------+ |?Variable_name?????????|?Value?| +-------------------------------+-------+ |?log_queries_not_using_indexes?|?ON??| +-------------------------------+-------+
5、log_output
設(shè)置了普通查詢?nèi)罩疽约奥樵內(nèi)罩镜妮敵鲂问剑涤袃蓚€file、table;
六、MySQL慢查詢?nèi)罩静榭?/strong>
log_output參數(shù)可以設(shè)置慢查詢?nèi)罩镜妮敵鲂问健ＤJ(rèn)為FILE,可以設(shè)置為TABLE;
mysql>?desc?mysql.slow_log; +----------------+---------------------+ |?Field?????|?Type????????| +----------------+---------------------+ |?start_time???|?timestamp??????| |?user_host???|?mediumtext?????| |?query_time???|?time????????| |?lock_time???|?time????????| |?rows_sent???|?int(11)???????| |?rows_examined?|?int(11)???????| |?db???????|?varchar(512)????| |?last_insert_id?|?int(11)???????| |?insert_id???|?int(11)???????| |?server_id???|?int(10)?unsigned??| |?sql_text????|?mediumtext?????| |?thread_id???|?bigint(21)?unsigned?| +----------------+---------------------+
其中: lock_time表示該SQL執(zhí)行時被鎖阻塞的時間。 rows_send表示執(zhí)行SQL后返回的內(nèi)容行數(shù)。 rows_examined表示該SQL執(zhí)行時實(shí)際掃描的記錄條數(shù)。
但是使用TABLE來存儲慢查詢?nèi)罩静⒉怀Ｒ姡瑯I(yè)務(wù)量較大的情況下，對于系統(tǒng)的主服務(wù)會有影響。我們可以使用FILE 的方式來進(jìn)行日志存儲。mysqlMySQL的時候在MySQL的bin目錄下已經(jīng)默認(rèn)安裝了mysqldumpslow.pl工具來進(jìn)行慢查詢的日志分析。 window下使用這個工具，可能需要折騰些配置，這不在本文的介紹范圍內(nèi)，學(xué)系統(tǒng)服務(wù)，還是移步mysql吧。 linux下的命令以及工具都可以使用 命令本身 + –help 的選項(xiàng)來查看幫助文檔。
-s 表示按照何種方式排序
子選項(xiàng): c、t、l、r
c : SQL執(zhí)行的次數(shù)
t : 執(zhí)行時間
l : 鎖等待時間
r : 返回?cái)?shù)據(jù)條數(shù)
at、al、ar 是對應(yīng) t l r 的平均值。 -t :表示返回前 N 條記錄。
-g： grep 縮寫。包含模糊匹配
常用法如下：
//返回訪問次數(shù)最多的20條SQL語句 ./mysqldumpslow?-s?c?-t?20?/usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //返回return記錄數(shù)最多的20條SQL語句 ./mysqldumpslow?-s?r?-t?20?/usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //返回含有l(wèi)ike的SQL語句 ./mysqldumpslow?-g?'like'?20?/usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log
七、二進(jìn)制日志（binary）
二進(jìn)制日志和前面提到的幾種日志不同，二進(jìn)制不能直接通過cat或者less 文本查看器查看。需要借助專業(yè)的工具。二進(jìn)制日志主要記錄數(shù)據(jù)庫的變化情況，因此可以用作主從庫的同步。內(nèi)容主要包括數(shù)據(jù)庫所有的更新操作，use語句、insert語句、delete語句、update語句、create語句、alter語句、drop語句。用一句更簡潔易懂的話概括就是：所有涉及數(shù)據(jù)變動的操作，都要記錄進(jìn)二進(jìn)制日志中。
啟動二進(jìn)制日志 使用 show variables like ‘log_bin’G 來查看二進(jìn)制日志是否開啟。
mysql>?show?variables?like?'log_bin'G ***************************?1.?row?*************************** Variable_name:?log_bin ????Value:?OFF 1?row?in?set?(0.00?sec) mysql>?set?@@global.log_bin=1; ERROR?1238?(HY000):?Variable?'log_bin'?is?a?read?only?variable mysql>
看到log_bin 默認(rèn)是不開啟的，并且是個只讀的變量，需要在my.cnf中配置，然后重啟MySQL。 service mysql restart 重啟MySQL后，在data目錄會發(fā)現(xiàn)生成了一個1.000mysql的文件。實(shí)際上每次MySQL重啟，在目錄下都會生成一個這樣的文件，文件名依次遞增。此外，MySQL還會在該目錄下創(chuàng)建一個二進(jìn)制日志的索引文件，可以通過命令show variables like ‘log_bin_index’G來查看索引文件的位置，然后使用cat命令看下。會發(fā)現(xiàn)，里面記錄著二進(jìn)制文件的相對路徑。
查看二進(jìn)制日志 可以使用MySQL 中自帶的工具。具體位置在mysql的bin目錄下。 mysqlbinlog命令的常用選項(xiàng)：
-s????????????????????????? 以精簡的方式顯示日志內(nèi)容
-v????????????????????????? 以詳細(xì)的方式顯示日志內(nèi)容
-d=數(shù)據(jù)庫名????????????????? 只顯示指定數(shù)據(jù)庫的日志內(nèi)容
-o=n??????????????????????? 忽略日志中前n行MySQL命令
-r=file??????????????????? 將指定內(nèi)容寫入指定文件
–start-datetime?
??????????????????????????? 顯示指定時間范圍內(nèi)的日志內(nèi)容
–stop-datetime????????
–start-position???????
??????????????????????????? 顯示指定位置間隔內(nèi)的日志內(nèi)容
–stop-position????
獲取當(dāng)前使用的二進(jìn)制日志文件
mysql>?show?master?status; +----------+----------+--------------+------------------+-------------------+ |?File???|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?| +----------+----------+--------------+------------------+-------------------+ |?1.000002?|???120?|???????|?????????|??????????| +----------+----------+--------------+------------------+-------------------+ 1?row?in?set?(0.00?sec)
使用二進(jìn)制日志恢復(fù)數(shù)據(jù)
語法很簡單：
mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p
mysqlbinlog 之后可以跟 –start-datetime 、–stop-datetime 、start-position 、stop-position 等參數(shù)。
–start-datetime 、–stop-datetime 這兩個參數(shù)可以基于時間點(diǎn)進(jìn)行數(shù)據(jù)恢復(fù)；
start-position 、stop-position 可以進(jìn)行操作點(diǎn)更加細(xì)化的進(jìn)行數(shù)據(jù)恢復(fù)；
MySQL二進(jìn)制日志相關(guān)參數(shù)
mysql>?show?variables?like?'%binlog%'; +-----------------------------------------+----------------------+ |?Variable_name??????????????|?Value????????| +-----------------------------------------+----------------------+ |?binlog_cache_size????????????|?32768????????| |?binlog_checksum?????????????|?CRC32????????| |?binlog_direct_non_transactional_updates?|?OFF?????????| |?binlog_error_action???????????|?IGNORE_ERROR?????| |?binlog_format??????????????|?STATEMENT??????| |?binlog_gtid_simple_recovery???????|?OFF?????????| |?binlog_max_flush_queue_time???????|?0??????????| |?binlog_order_commits??????????|?ON??????????| |?binlog_row_image????????????|?FULL?????????| |?binlog_rows_query_log_events??????|?OFF?????????| |?binlog_stmt_cache_size?????????|?32768????????| |?binlogging_impossible_mode???????|?IGNORE_ERROR?????| |?innodb_api_enable_binlog????????|?OFF?????????| |?innodb_locks_unsafe_for_binlog?????|?OFF?????????| |?max_binlog_cache_size??????????|?18446744073709547520?| |?max_binlog_size?????????????|?1073741824??????| |?max_binlog_stmt_cache_size???????|?18446744073709547520?| |?simplified_binlog_gtid_recovery?????|?OFF?????????| |?sync_binlog???????????????|?0??????????| +-----------------------------------------+----------------------+
max_binlog_size
maxbinlogsize 單個二進(jìn)制日志文件的大小。如果超過該值，則生成新的文件，后綴名+1；
binlog_cache_size
binlogcachesize 內(nèi)存中存放二進(jìn)制日志的緩存大小
sync_binlog
sync_binlog 緩存中寫入幾次二進(jìn)制日志，開始同步刷新到外存（硬盤）中。
log_slave_updates
logslvaeupdates 用于主從復(fù)制
二進(jìn)制日志的清理
原則上要先將準(zhǔn)備清理的日志通過物理備份的方式備份到其他存儲設(shè)備上，永久留存。 然后推薦使用下面兩種風(fēng)險較小的清理方式：
第一種：
purge?master?logs?before?'2017-02-16?00:00:00';
第二種：
直接在MySQL的配置文件my.cnf 中設(shè)置expire_logs_days參數(shù)來設(shè)置二進(jìn)制文件的過期天數(shù)，過期的二進(jìn)制文件將會被自動刪除。建議在刪除前另啟一個周期計(jì)劃任務(wù)，定期去備份二進(jìn)制任務(wù)。免得一些數(shù)據(jù)若干天才發(fā)現(xiàn)出現(xiàn)了差錯，而二進(jìn)制日志被自動刪除了。
expire_logs_days=90
八 、InnoDB 事務(wù)日志
MySQL會最大程度的利用緩存，從而提高數(shù)據(jù)的訪問效率。那么換一句話來說，任何高性能的系統(tǒng)都必須利用到緩存，從各個層面來講，緩存都發(fā)揮了巨大的作用。再上升到一個高度提煉一下：緩存和mysql是實(shí)現(xiàn)高性能的必走之路。那么對于數(shù)據(jù)庫來說這個卻是個很棘手的問題，要保證數(shù)據(jù)更高效率的讀取和存儲，所以要利用到緩存。但是要保證數(shù)據(jù)的一致性，則必須保證所有的數(shù)據(jù)都必須準(zhǔn)確無誤的存儲到數(shù)據(jù)庫中，及時發(fā)生意外，也要保證數(shù)據(jù)可恢復(fù)。我們知道InnoDB是一個事務(wù)安全的存儲引擎，而一致性是事務(wù)ACID中的一個重要特性。InnoDB存儲引擎主要是通過InnoDB事務(wù)日志實(shí)現(xiàn)數(shù)據(jù)一致性的，InnoDB事務(wù)日志包括重做（redo）日志，以及回滾（undo）日志。
InnoDB事務(wù)日志與前面提高的日志不同，InnoDB事務(wù)日志由InnoDB存儲引擎自行維護(hù)，而且內(nèi)容不能被數(shù)據(jù)庫管理員讀取。
重做日志（redo）
重做日志主要是記錄已經(jīng)全部完成的事務(wù),即執(zhí)行了commit的日志，在默認(rèn)情況下重做日志的值記錄在iblogfile0 以及iblogfile1重做日志中。
[root@roverliang?data]#?pwd /usr/local/webserver/mysql/data [root@roverliang?data]#?ls?ib* ibdata1?ib_logfile0?ib_logfile1
回滾日志（undo）
回滾日志主要記錄已經(jīng)部分完成并且寫入硬盤的未完成事務(wù)，默認(rèn)情況情況下，回滾日志的信息記錄在表空間文件，共享表空間文件ibdata1或者獨(dú)享表空間未見ibd中。
由上圖我們可以知道，回滾日志默認(rèn)是記錄在ibdta1中的。我的mysql系統(tǒng)版本為：5.6.24.
Checkpoint機(jī)制
MySQL服務(wù)器崩潰后，重新啟動MySQL服務(wù)時，由于重做日志（redo）與回滾日志（undo）日志的存在，InnoDB通過回滾日志（undo）日志將所有已部分完成并寫入硬盤的未完成事務(wù)進(jìn)行回滾操作（rollback）。然后將重做日志（undo）日志中的事務(wù)全部重新執(zhí)行一遍即可恢復(fù)所有的數(shù)據(jù)。但是數(shù)據(jù)量過大，為了縮短恢復(fù)的時間InnoDB引入了Checkpoint 機(jī)制。
臟頁（dirty page）
當(dāng)事務(wù)需要修改某條記錄是，InnoDB首先將該數(shù)據(jù)所在的數(shù)據(jù)塊從外存中讀取到硬盤中，事務(wù)提交后，InnoDB修改數(shù)據(jù)頁中的記錄，這時緩存的數(shù)據(jù)頁已經(jīng)和外存中的數(shù)據(jù)塊已經(jīng)不一樣了，此時緩存中的數(shù)據(jù)頁稱為臟頁（dirty page）,臟頁刷新到外存中，變?yōu)楦蓛繇摚╟lean page）。
備注：一個內(nèi)存頁默認(rèn)為4K，或者4K的倍數(shù)。你可以把內(nèi)存想象成一本可以擦洗的書，每次MySQL讀數(shù)據(jù)的時候，向內(nèi)存申請幾張干凈的書頁，然后書寫上去。當(dāng)數(shù)據(jù)刷新到硬盤之后，這些數(shù)據(jù)頁馬上被擦除，供其他程序使用。
日志序列號（log sequence number）
日志序列號（LSN）是日志空間中每條日志的結(jié)束點(diǎn)，用字節(jié)偏移量表示，在Checkpoin和恢復(fù)時使用。
Checkpoint 機(jī)制原理 假設(shè)在某個時間點(diǎn)，所有的臟頁（dirty page）都被刷新到了硬盤上，這個時間點(diǎn)之前的所有重做日志（redo）就不需要重做了.系統(tǒng)就就下這個時間點(diǎn)重做日志的結(jié)尾位置作為Checkpoint,Checkpoint之前的重做日志也就不需要再重做了，可以放心的刪除掉。為了更好的利用重做日志（redo）的空間,InnoDb采用輪循的策略使用重做日志空間，因此InnoDB的重做日志文件至少為2個。通過Checkpoint 機(jī)制，通過重做日志（redo）將數(shù)據(jù)庫崩潰時已經(jīng)完成但還沒有來得及將緩存中已經(jīng)修改但還未完全寫入外存的事務(wù)進(jìn)行重做（undo）操作，即可保證數(shù)據(jù)的一致性，也可以縮短恢復(fù)時間。
InnoDB重做日志(redo)的參數(shù)
innodb_log_buffer_size: 設(shè)置了重做日志緩存的大小。
innodb_log_files_in_group : 設(shè)置了日志文件組中重做日志（redo）日志的數(shù)量。
innodb_log_file_size: 設(shè)置了重做日志文件的大小，文件越大，恢復(fù)起來耗時越長。
innodb_mirrored_log_groups: 重做日志鏡像文件組數(shù)量，只能設(shè)置為1.
innodb_log_group_home_dir： 設(shè)置日志文件組存放的目錄，默認(rèn)在數(shù)據(jù)庫根目錄下。
InnoDB回滾日志（undo）的參數(shù)
innodb_undo_directory: 設(shè)置回滾日志存放的目錄。
innodb_undo_logs :設(shè)置回滾日志的回滾段大小，默認(rèn)為128k
innodb_undo_tablespace:設(shè)置了回滾日志由多少個回滾日志文件組成，默認(rèn)為0.
Warning 特別注意：安裝MySQL后需要在my.cnf中設(shè)置回滾日志的參數(shù)，如果mysql以后再設(shè)置回滾日志的參數(shù)，MySQL就會報錯，并且回滾日志建好后，就不能再次修改或者增加。
九 、日志文件備份
備份的時候可以使用flush logs，關(guān)閉當(dāng)前的所有日志文件，然后產(chǎn)生新的日志文件。關(guān)閉日志文件后，可以采用物理方式備份。 另外flush logs可以添加具體的日志類型：
flush?error?logs flush?general?logs flush?binary?logs flush?slow?logs
.png)
