程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 經驗分享:MySQL日志維護策略總結

經驗分享:MySQL日志維護策略總結

編輯:MySQL綜合教程

經驗分享:MySQL日志維護策略總結


這幾天要折騰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%';

  1. +------------------+----------------------------+ 
  2.  
  3. | Variable_name | Value | 
  4.  
  5. +------------------+----------------------------+ 
  6.  
  7. | general_log | ON | #是否啟用了通用查詢日志 
  8.  
  9. | general_log_file | /var/run/mysqld/mysqld.log | #日志路徑 
  10.  
  11. +------------------+----------------------------+ 

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

  1. +--------------------------------+------------+ | Variable_name | Value | +--------------------------------+------------+ 
  2.  
  3. | binlog_cache_size | 1048576 | 
  4.  
  5. | innodb_locks_unsafe_for_binlog | OFF | 
  6.  
  7. | max_binlog_cache_size| 4294967295 | 
  8.  
  9. | max_binlog_size| 1073741824 | 
  10.  
  11. | sync_binlog| 0| 
  12.  
  13. +--------------------------------+------------+

博文出處:http://my.oschina.net/u/1759688/blog/384530



  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved