MySQL日誌維護的7大策略詳解

這幾天要折騰mysql伺服器，所以在網上搜羅了一些維護策略，然後自己總結實驗，下面是我的總結經驗和別人的一些建議。

日誌型別：

MySQL有幾個不同的日誌檔案，可以幫助你找出mysqld內部發生的事情：

日誌檔案	記入檔案中的資訊型別
錯誤日誌	記錄啟動、執行或停止時出現的問題
查詢日誌	記錄建立的客戶端連線和執行的語句
二進位制日誌	記錄所有更改資料的語句。主要用於複製和即時點恢復
慢日誌	記錄所有執行時間超過long_query_time秒的所有查詢或不使用索引的查詢
事務日誌	記錄InnoDB等支援事務的儲存引擎執行事務時產生的日誌

1.啟動慢查詢日誌：

MySQL 如果啟用了slow_query_log=ON選項，就會記錄執行時間超過long_query_time(預設10s)的查詢(初使表鎖定的時間不算作 執行 時間)。日誌記錄檔案為slow_query_log_file[=file_name]，如果沒有給出file_name值， 預設為主機名，字尾為-slow.log。如果給出了檔名，但不是絕對路徑名，檔案則寫入資料目錄。

【這個可以在除錯mysql效能的時候啟用,可以找出是哪個sql指令最浪費時間。生產環境中建議關閉】

2.生產環境中關閉通用查詢日誌：

由 於開啟通用查詢日誌是記錄使用者的所有操作，在生產環境中這個日誌的量是非常大的，所以一般情況下都是不開啟的，myslq預設的該日誌功能也是關閉的，在 特殊情況下才進行開啟【一般只有在開發測試環境中，為了定位某些功能具體使用了哪些SQL語句的時候，才會在短時間段內開啟該日誌來做相應的分析。】

mysql> set global general_log = 1; #1：啟動通用查詢日誌，0：關閉通用查詢日誌

mysql> show global variables like ‘%general_log%’;

+------------------+----------------------------+ 

| Variable_name | Value | 

+------------------+----------------------------+ 

| general_log | ON | #是否啟用了通用查詢日誌 

| general_log_file | /var/run/mysqld/mysqld.log | #日誌路徑 

+------------------+----------------------------+

2 rows in set (0.00 sec)

3.定期備份二進位制日誌和sql資料：【本地一份，遠端日誌主機一份，儲存主機一份】

在 my.cnf中log-bin = [filename]是啟用二進位制日誌，預設以[filename].000001往上記錄的，從啟用log-bin之後【此時最好用mysqldump 儲存當前的mysql某個庫的資料，因為二進位制日誌只是記錄了從現在起到最近一次mysql當機重啟中的所有sql語句】，mysql就會開始記錄每一個 sql語句，一旦mysql因各種原因需要重啟，則會產生新的二進位制日誌，000001的字尾名會不斷往上自加。若是在mysql當機期間mysql的數 據遭到了破壞(如磁碟損壞)，之前的資料全部都被破壞了，這時候這個備份策略就可以幫你挽回損失。你可以從二進位制日誌中恢復從開始到最近一次mysql重 啟這段時間的資料。【二進位制日誌中記錄的是每一個sql語句，可以用mysqlbinlog [filename]檢視日誌內容】

4.sync_binlog全域性變數的取值一定要合適：

默 認情況下，並不是每次寫入時都將二進位制日誌與硬碟同步。因此如果作業系統或機器(不僅僅是MySQL伺服器)崩潰，有可能二進位制日誌中最後的語句丟失了。 要想防止這種情況，你可以使用sync_binlog全域性變數(1是最安全的值，但也是最慢的)，使二進位制日誌在每N次二進位制日誌寫入後與硬碟同步。對非 事務表的更新執行完畢後立即儲存到二進位制日誌中。

下面解釋下sync_binlog:

“sync_binlog”：這個引數是對於MySQL系統來說是至關重要的，他不僅影響到Binlog對MySQL所帶來的效能損耗，而且還影響到MySQL中資料的完整性。對於“sync_binlog”引數的各種設定的說明如下：

sync_binlog=0，當事務提交之後，MySQL不做fsync之類的磁碟同步指令重新整理binlog_cache中的資訊到磁碟，而讓Filesystem自行決定什麼時候來做同步，或者cache滿了之後才同步到磁碟。

sync_binlog=n，當每進行n次事務提交之後，MySQL將進行一次fsync之類的磁碟同步指令來將binlog_cache中的資料強制寫入磁碟。

在 MySQL中系統預設的設定是sync_binlog=0，也就是不做任何強制性的磁碟重新整理指令，這時候的效能是最好的，但是風險也是最大的。因為一旦系 統Crash，在binlog_cache中的所有binlog資訊都會被丟失。而當設定為“1”的時候，是最安全但是效能損耗最大的設定。因為當設定為 1的時候，即使系統Crash，也最多丟失binlog_cache中未完成的一個事務，對實際資料沒有任何實質性影響。從以往經驗和相關測試來看,對於 高併發事務的系統來說,“sync_binlog”設定為0和設定為1的系統寫入效能差距可能高達5倍甚至更多。

5.如果資料庫有很多的事務型操作，則建議把二進位制日誌的回滾上限設定大一些：

對於事務表，例如BDB或InnoDB表，所有更改表的更新(UPDATE、DELETE或INSERT)被快取起來，直到伺服器接收到 COMMIT語句。在該點，執行完COMMIT之前，mysqld將整個事務寫入二進位制日誌。當處理事務的執行緒啟動時，它為 緩衝查詢分配binlog_cache_size大小的記憶體。如果語句大於該值，執行緒則開啟臨時檔案來儲存事務【所以如果 bunlog_cache_size足夠大，就避免了過多的磁碟的I/O操作,可以把資料全部快取在記憶體中】。執行緒結束後臨時檔案被刪除。 【“max_binlog_cache_size”：和”binlog_cache_size”相對應，但是所代表的是binlog能夠使用的最大 cache記憶體大小。當我們執行多語句事務的時候，max_binlog_cache_size如果不夠大的話，系統可能會報出“Multi- statementtransactionrequiredmorethan’max_binlog_cache_size’bytesofstorage” 的錯誤。所以最好也把max_binlog_cache_size也調大些(具體多大看你的伺服器了)】

6.儘量把max_binlog_size設定大些

Binlog日誌最大值，一般來說設定為512M或者1G，但不能超過1G。該大小並不能非常嚴格控制Binlog大小，尤其是當到達Binlog 比較靠近尾部而又遇到一個較大事務的時候，系統為了保證事務的完整性，不可能做切換日誌的動作，只能將該事務的所有SQL都記錄進入當前日誌，直到該事務 結束。

7.下面是mysql環境的情況：

mysql> show variables like ‘%binlog%’;

+--------------------------------+------------+ | Variable_name | Value | +--------------------------------+------------+ 

| binlog_cache_size | 1048576 | 

| innodb_locks_unsafe_for_binlog | OFF | 

| max_binlog_cache_size| 4294967295 | 

| max_binlog_size| 1073741824 | 

| sync_binlog| 0| 

+--------------------------------+------------+