MySQL記錄檔(5.1)

MySQL記錄檔

錯誤記錄檔
通用查詢日誌
二進位日誌
慢速查詢日誌
記錄檔維護

MySQL有幾個不同的記錄檔，可以協助你找出mysqld內部發生的事情：

記錄檔	記入檔案中的資訊類型
錯誤記錄檔	記錄啟動、運行或停止mysqld時出現的問題。
查詢日誌	記錄建立的用戶端串連和執行的語句。
更新日誌	記錄更改資料的語句。不贊成使用該日誌。
二進位日誌	記錄所有更改資料的語句。還用於複製。
慢日誌	記錄所有執行時間超過long_query_time秒的所有查詢或不使用索引的查詢。

預設情況下，所有日誌建立於mysqld資料目錄中。通過重新整理日誌，你可以強制 mysqld來關閉和重新開啟記錄檔（或者在某些情況下切換到一個新的日誌）。當你執行一個FLUSH LOGS語句或執行mysqladmin flush-logs或mysqladmin refresh時，出現日誌重新整理。如果你正使用MySQL複製功能，從複製伺服器將維護更多記錄檔，被稱為接替日誌。

錯誤記錄檔檔案包含了當mysqld啟動和停止時，以及伺服器在運行過程中發生任何嚴重錯誤時的相關資訊。

如果mysqld莫名其妙地死掉並且mysqld_safe需要重新啟動它，mysqld_safe在錯誤記錄檔中寫入一條restarted mysqld訊息。如果mysqld注意到需要自動檢查或著修複一個表，則錯誤記錄檔中寫入一條訊息。

在一些作業系統中，如果mysqld死掉，錯誤記錄檔包含堆疊追蹤資訊。跟蹤資訊可以用來確定mysqld死掉的地方。

可以用--log-error[=file_name]選項來指定mysqld儲存錯誤記錄檔檔案的位置。如果沒有給定file_name值，mysqld使用錯誤記錄檔名host_name.err 並在資料目錄中寫入記錄檔。如果你執行FLUSH LOGS，錯誤記錄檔用-old重新命名尾碼並且mysqld建立一個新的空記錄檔。(如果未給出--log-error選項，則不會重新命名）。

如果不指定--log-error，或者(在Windows中)如果你使用--console選項，錯誤被寫入標準錯誤輸出stderr。通常標準輸出為你的終端。

在Windows中，如果未給出--console選項，錯誤輸出總是寫入.err檔案。

通用查詢日誌

如果你想要知道mysqld內部發生了什麼，你應該用--log[=file_name]或-l [file_name]選項啟動它。如果沒有給定file_name的值， 預設名是host_name.log。所有串連和語句被記錄到記錄檔。當你懷疑在用戶端發生了錯誤並想確切地知道該用戶端發送給mysqld的語句時，該日誌可能非常有用。

mysqld按照它接收的順序記錄語句到查詢日誌。這可能與執行的順序不同。這與更新日誌和二進位日誌不同，它們在查詢執行後，但是任何一個鎖釋放之前記錄日誌。(查詢日誌還包含所有語句，而二進位日誌不包含只查詢資料的語句）。

伺服器重新啟動和日誌重新整理不會產生新的一般查詢記錄檔(儘管重新整理關閉並重新開啟一般查詢記錄檔)。在Unix中，你可以通過下面的命令重新命名檔案並建立一個新檔案：

shell> mv hostname.log hostname-old.log

shell> mysqladmin flush-logs

shell> cp hostname-old.log to-backup-directory

shell> rm hostname-old.log

在Windows中，伺服器開啟記錄檔期間你不能重新命名記錄檔。你必須先停止伺服器然後重新命名記錄檔。然後，重啟伺服器來建立新的記錄檔。

二進位日誌

二進位日誌以一種更有效格式，並且是事務安全的方式包含更新日誌中可用的所有資訊。

二進位日誌包含了所有更新了資料或者已經潛在更新了資料（例如，沒有匹配任何行的一個DELETE）的所有語句。語句以 “事件”的形式儲存，它描述資料更改。

注釋：二進位日誌已經代替了老的更新日誌，更新日誌在MySQL 5.1中不再使用。

二進位日誌還包含關於每個更新資料庫的語句的執行時間資訊。它不包含沒有修改任何資料的語句。如果你想要記錄所有語句（例如，為了識別有問題的查詢），你應使用一般查詢日誌。

二進位日誌的主要目的是在恢複使能夠最大可能地更新資料庫，因為二進位日誌包含備份後進行的所有更新。

二進位日誌還用於在主複製伺服器上記錄所有將發送給從伺服器的語句。

運行伺服器時若啟用二進位日誌則效能大約慢1%。但是，二進位日誌的好處，即用於恢複並允許設定複製超過了這個小小的效能損失。

當用--log-bin[=file_name]選項啟動時，mysqld寫入包含所有更新資料的SQL命令的記錄檔。如果未給出file_name值， 預設名為-bin後面所跟的主機名稱。如果給出了檔案名稱，但沒有包含路徑，則檔案被寫入資料目錄。

如果你在日誌名中提供了副檔名(例如，--log-bin=file_name.extension)，則副檔名被悄悄除掉並忽略。

mysqld在每個二進位日誌名後面添加一個數字副檔名。每次你啟動伺服器或重新整理日誌時該數字則增加。如果當前的日誌大小達到max_binlog_size，還會自動建立新的二進位日誌。如果你正使用大的事務，二進位日誌還會超過max_binlog_size：事務全寫入一個二進位日誌中，絕對不要寫入不同的二進位日誌中。

為了能夠知道還使用了哪個不同的二進位記錄檔，mysqld還建立一個二進位日誌索引檔案，包含所有使用的二進位記錄檔的檔案名稱。預設情況下與二進位記錄檔的檔案名稱相同，副檔名為'.index'。你可以用--log-bin-index[=file_name]選項更改二進位日誌索引檔案的檔案名稱。當mysqld在運行時，不應手動編輯該檔案；如果這樣做將會使mysqld變得混亂。

可以用RESET MASTER語句刪除所有二進位記錄檔，或用PURGE MASTER LOGS只刪除部分二進位檔案。

二進位日誌格式有一些已知限制，會影響從備份恢複。

可以使用下面的mysqld選項來影響記錄到二進位日誌知的內容。又見選項後面的討論。

·         --binlog-do-db=db_name

告訴主伺服器，如果當前的資料庫(即USE選定的資料庫)是db_name，應將更新記錄到二進位日誌中。其它所有沒有明顯指定的資料庫  被忽略。如果使用該選項，你應確保只對當前的資料庫進行更新。

對於CREATE DATABASE、ALTER DATABASE和DROP DATABASE語句，有一個例外，即通過操作的資料庫來決定是否應記錄語句，而不是用當前的資料庫。

一個不能按照期望執行的例子：如果用binlog-do-db=sales啟動伺服器，並且執行USE prices; UPDATE sales.january SET amount=amount+1000；，該語句不寫入二進位日誌。

·         --binlog-ignore-db=db_name

告訴主伺服器，如果當前的資料庫(即USE選定的資料庫)是db_name，不應將更新儲存到二進位日誌中。如果你使用該選項，你應確保只對當前的資料庫進行更新。

一個不能按照你期望的執行的例子：如果伺服器用binlog-ignore-db=sales啟動，並且執行USE prices; UPDATE sales.january SET amount=amount+1000；，該語句不寫入二進位日誌。

類似於--binlog-do-db，對於CREATE DATABASE、ALTER DATABASE和DROP DATABASE語句，有一個例外，即通過操作的資料庫來決定是否應記錄語句，而不是用當前的資料庫。

要想記錄或忽視多個資料庫，使用多個選項，為每個資料庫指定相應的選項。

伺服器根據下面的規則對選項進行評估，以便將更新記錄到二進位日誌中或忽視。請注意對於CREATE/ALTER/DROP DATABASE語句有一個例外。在這些情況下，根據以下規則，所建立、修改或刪除的資料庫將代替當前的資料庫。

1.    是否有binlog-do-db或binlog-ignore-db規則?

         沒有：將語句寫入二進位日誌並退出。

         有：執行下一步。

2.    有一些規則(binlog-do-db或binlog-ignore-db或二者都有)。當前有一個資料庫(USE是否選擇了資料庫?)?

        沒有：不要寫入語句，並退出。

        有：執行下一步。

3.    有當前的資料庫。是否有binlog-do-db規則?

        有：當前的資料庫是否匹配binlog-do-db規則?

        有：寫入語句並退出。

       沒有：不要寫入語句，退出。

        No：執行下一步。

4.    有一些binlog-ignore-db規則。當前的資料庫是否匹配binlog-ignore-db規則?

        有：不要寫入語句，並退出。

        沒有：寫入查詢並退出。

例如，只用binlog-do-db=sales啟動並執行伺服器不將當前資料庫不為sales的語句寫入二進位日誌(換句話說，binlog-do-db有時可以表示“忽視其它資料庫”)。

如果你正進行複製，應確保沒有從伺服器在使用舊的二進位記錄檔，方可刪除它們。一種方法是每天一次執行mysqladmin flush-logs並刪除三天前的所有日誌。可以手動刪除，或最好使用PURGE MASTER LOGS該語句還會安全地更新二進位日誌索引檔案(可以採用日期參數)。

具有SUPER許可權的用戶端可以通過SET SQL_LOG_BIN=0語句禁止將自己的語句記入二進位記錄。

你可以用mysqlbinlog工具 + 生產力檢查二進位記錄檔。如果你想要重新處理日誌止的語句，這很有用。例如，可以從二進位日誌更新MySQL伺服器，方法如下：

shell> mysqlbinlog log-file | mysql -h server_name

關於mysqlbinlog:用於處理二進位記錄檔的工具 + 生產力

如果你正使用事務，必須使用MySQL二進位日誌進行備份，而不能使用舊的更新日誌。

查詢結束後、鎖定被釋放前或提交完成後則立即記入二進位日誌。這樣可以確保按執行順序記入日誌。

對非事務表的更新執行完畢後立即儲存到二進位日誌中。對於事務表，例如BDB或InnoDB表，所有更改表的更新(UPDATE、DELETE或INSERT) 被緩衝起來，直到伺服器接收到COMMIT語句。在該點，執行完COMMIT之前，mysqld將整個事務寫入二進位日誌。當處理事務的線程啟動時，它為緩衝查詢分配binlog_cache_size大小的記憶體。如果語句大於該值，線程則開啟臨時檔案來儲存事務。線程結束後臨時檔案被刪除。

Binlog_cache_use狀態變數顯示了使用該緩衝區(也可能是臨時檔案)儲存語句的事務的數量。Binlog_cache_disk_use狀態變數顯示了這些事務中實際上有多少必須使用臨時檔案。這兩個變數可以用於將binlog_cache_size調節到足夠大的值，以避免使用臨時檔案。

max_binlog_cache_size(預設4GB)可以用來限制用來緩衝多語句事務的緩衝區總大小。如果某個事務大於該值，將會失敗並 復原。

如果你正使用更新日誌或二進位日誌，當使用CREATE ... SELECT or INSERT ... SELECT時，並行插入被轉換為普通插入。這樣通過在備份時使用日誌可以確保重新建立表的備份。

請注意MySQL 5.1值的二進位日誌格式與以前版本的MySQL不同，因為複製改進了。

預設情況下，並不是每次寫入時都將二進位日誌與硬碟同步。因此如果作業系統或機器(不僅僅是MySQL伺服器)崩潰，有可能二進位日誌中最後的語句丟失了。要想防止這種情況，你可以使用sync_binlog全域變數(1是最安全的值，但也是最慢的)，使二進位日誌在每N次二進位日誌寫入後與硬碟同步。即使sync_binlog設定為1,出現崩潰時，也有可能表內容和二進位日誌內容之間存在不一致性。例如，如果使用InnoDB表，MySQL伺服器處理COMMIT語句，它將整個事務寫入二進位日誌並將事務提交到InnoDB中。如果在兩次操作之間出現崩潰，重啟時，事務被InnoDB復原，但仍然存在二進位日誌中。可以用--innodb-safe-binlog選項解決該問題，可以增加InnoDB表內容和二進位日誌之間的一致性。(注釋：在MySQL 5.1中不需要--innodb-safe-binlog；由於引入了XA事務支援，該選項作廢了）。

該選項可以提供更大程度的安全，還應對MySQL伺服器進行配置，使每個事務的二進位日誌(sync_binlog =1)和(預設情況為真)InnoDB日誌與硬碟同步。該選項的效果是崩潰後重啟時，在滾回事務後，MySQL伺服器從二進位日誌剪下 復原的InnoDB事務。這樣可以確保二進位日誌反饋InnoDB表的確切資料等，並使從伺服器保持與主伺服器保持同步(不接收 復原的語句)。

請注意即使MySQL伺服器更新其它儲存引擎而不是InnoDB，也可以使用--innodb-safe-binlog。在InnoDB崩潰恢複時，只從二進位日誌中刪除影響InnoDB表的語句/事務。如果崩潰恢複時MySQL伺服器發現二進位日誌變短了(即至少缺少一個成功提交的InnoDB事務)，如果sync_binlog =1並且硬碟/檔案系統的確能根據需要進行同步(有些不需要)則不會發生，則輸出錯誤訊息 ("二進位日誌<名>比期望的要小")。在這種情況下，二進位日誌不準確，複製應從主伺服器的資料快照開始。

寫入二進位記錄檔和二進位日誌索引檔案的方法與寫入MyISAM表相同。參

慢速查詢日誌

用--log-slow-queries[=file_name]選項啟動時，mysqld寫一個包含所有執行時間超過long_query_time秒的SQL語句的記錄檔。獲得初使表鎖定的時間不算作執行時間。

如果沒有給出file_name值， 預設未主機名稱，尾碼為-slow.log。如果給出了檔案名稱，但不是絕對路徑名，檔案則寫入資料目錄。

語句執行完並且所有鎖釋放後記入慢查詢日誌。記錄順序可以與執行順序不相同。

慢查詢日誌可以用來找到執行時間長的查詢，可以用於最佳化。但是，檢查又長又慢的查詢日誌會很困難。要想容易些，你可以使用mysqldumpslow命令獲得日誌中顯示的查詢摘要來處理慢查詢日誌。

在MySQL 5.1的慢查詢日誌中，不使用索引的慢查詢同使用索引的查詢一樣記錄。要想防止不使用索引的慢查詢記入慢查詢日誌，使用--log-short-format選項。

在MySQL 5.1中,通過--log-slow-admin-statements伺服器選項，你可以請求將慢管理語句，例如OPTIMIZE TABLE、ANALYZE TABLE和 ALTER TABLE寫入慢查詢日誌。

用查詢快取處理的查詢不加到慢查詢日誌中，因為表有零行或一行而不能從索引中受益的查詢也不寫入慢查詢日誌。

記錄檔維護

MySQL伺服器可以建立各種不同的記錄檔，從而可以很容易地看見所進行的操作。但是，你必須定期清理這些檔案，確保日誌不會佔用太多的硬碟空間。

當啟用日誌使用MySQL時，你可能想要不時地備份並刪除舊的記錄檔，並告訴MySQL開始記入新檔案。

在 Linux (Redhat)的安裝上，你可為此使用mysql-log-rotate指令碼。如果你從RPM分發安裝MySQL，指令碼應該自動被安裝了。

在其它系統上，你必須自己安裝短指令碼，你可從cron等入手處理記錄檔。

你可以通過mysqladmin flush-logs或SQL語句FLUSH LOGS來強制MySQL開始使用新的記錄檔。

日誌清空操作做下列事情：

• 如果使用標準日誌(--log)或慢查詢日誌(--log-slow-queries)，關閉並重新開啟記錄檔。(預設為mysql.log和`hostname`-slow.log)。
• 如果使用更新日誌(--log-update)或二進位日誌(--log-bin)，關閉日誌並且開啟有更高序號的新記錄檔。

如果你只使用更新日誌，你只需要重新命名記錄檔，然後在備份前清空日誌。例如，你可以這樣做：

shell> cd mysql-data-directory

shell> mv mysql.log mysql.old

shell> mysqladmin flush-logs

然後做備份並刪除“mysql.old”。