程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 自動清理MySQL binlog日志與手動刪除的設置

自動清理MySQL binlog日志與手動刪除的設置

編輯:MySQL綜合教程

以下的文章主要講述的是對自動清理MySQL binlog日志與手動刪除的實際解決方案的設置, 我們大家都知道MySQL數據庫從復制(replication)采用了RBR 模式之後,binlog 的格式為"ROW",其主要作用是解決很多原先出現的主鍵重復問題。

在一個繁忙的master db server上,MySQL binlog日志文件增長速度很快,如果不定時清除,硬盤空間很快就會被充滿。

設置自動清理MySQL binlog日志,配置my.cnf:

expire_logs_days = 10

在運行時修改:

  1. show binary logs;   
  2. show variables like '%log%';   
  3. set global expire_logs_days = 10; 

清除之前可以采用相應的備份策略。

手動刪除10天前的MySQL binlog日志:

  1. PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);  
  2. show master logs; 

MASTER和BINARY是同義詞。

一般情況下,推薦使用MIXED binlog的復制。http://dev.MySQL.com/doc/refman/5.1/en/open-bugs-general.html中的說明:Replication uses query-level logging: The master writes the executed queries to the binary logThis is a very fast, compact, and efficient logging method that works perfectly in most cases

附:關於MySQL復制的幾種模式

從 MySQL 5.1.12 開始,可以用以下三種模式來實現:

基於SQL語句的復制(statement-based replication, SBR),

基於行的復制(row-based replication, RBR),

混合模式復制(mixed-based replication, MBR)。

相應地,binlog的格式也有三種:STATEMENT,ROW,MIXED。 MBR 模式中,SBR 模式是默認的。

在運行時可以動態改動 binlog的格式,除了以下幾種情況:

存儲流程或者觸發器中間

啟用了NDB

當前會話試用 RBR 模式,並且已打開了臨時表

如果binlog采用了 MIXED 模式,那麼在以下幾種情況下會自動將MySQL binlog的模式由 SBR 模式改成 RBR 模式。

當DML語句更新一個NDB表時

當函數中包含 UUID() 時

2個及以上包含 AUTO_INCREMENT 字段的表被更新時

行任何 INSERT DELAYED 語句時

用 UDF 時

視圖中必須要求運用 RBR 時,例如建立視圖是運用了 UUID() 函數

設定主從復制模式:

  1. log-bin=MySQL-bin  
  2. #binlog_format="STATEMENT" 
  3. #binlog_format="ROW" 
  4. binlog_format="MIXED" 

也可以在運行時動態修改binlog的格式。例如

  1. MySQL> SET SESSION binlog_format = 'STATEMENT';  
  2. MySQL> SET SESSION binlog_format = 'ROW';  
  3. MySQL> SET SESSION binlog_format = 'MIXED';  
  4. MySQL> SET GLOBAL binlog_format = 'STATEMENT';  
  5. MySQL> SET GLOBAL binlog_format = 'ROW';  
  6. MySQL> SET GLOBAL binlog_format = 'MIXED'; 

兩種模式各自的優缺點:

SBR 的優點:

歷史悠久,技能成熟

binlog文件較小

binlog中包含了所有數據庫修改信息,可以據此來審核數據庫的安全等情況

MySQL binlog可以用於實時的還原,而不僅僅用於復制

主從版本可以不一樣,從服務器版本可以比主服務器版本高

SBR 的缺點:

不是所有的UPDATE語句都能被復制,尤其是包含不確定操作的時候。

調用具有不確定因素的 UDF 時復制也可能出疑問

運用以下函數的語句也不能被復制:

LOAD_FILE()

UUID()

USER()

FOUND_ROWS()

SYSDATE() (除非啟動時啟用了 –sysdate-is-now 選項)

INSERT … SELECT 會產生比 RBR 更多的行級鎖

復制須要執行 全表掃描(WHERE 語句中沒有運用到索引)的 UPDATE 時,須要比 RBR 請求更多的行級鎖

對於有 AUTO_INCREMENT 字段的 InnoDB表而言,INSERT 語句會阻塞其他 INSERT 語句

對於一些復雜的語句,在從服務器上的耗資源情況會更嚴重,而 RBR 模式下,只會對那個發生變化的記錄產生影響

存儲函數(不是存儲流程 )在被調用的同時也會執行一次 NOW() 函數,這個可以說是壞事也可能是好事

確定了的 UDF 也須要在從服務器上執行

數據表必須幾乎和主服務器保持一致才行,否則可能會導致復制出錯

執行復雜語句如果出錯的話,會消耗更多資源

RBR 的優點:

任何情況都可以被復制,這對復制來說是最安全可靠的

和其他大多數數據庫系統的復制技能一樣

多數情況下,從服務器上的表如果有主鍵的話,復制就會快了很多

復制以下幾種語句時的行鎖更少:

INSERT … SELECT

包含 AUTO_INCREMENT 字段的 INSERT

沒有附帶條件或者並沒有修改很多記錄的 UPDATE 或 DELETE 語句

執行 INSERT,UPDATE,DELETE 語句時鎖更少

從服務器上采用多線程來執行復制成為可能

RBR 的缺點:

binlog 大了很多

復雜的回滾時 binlog 中會包含大量的數據

主服務器上執行 UPDATE 語句時,所有發生變化的記錄都會寫到 binlog 中,而 SBR 只會寫一次,這會導致頻繁發生 binlog 的並發寫疑問

UDF 產生的大 BLOB 值會導致復制變慢

不能從 binlog 中看到都復制了寫什麼語句(加密過的)

當在非事務表上執行一段堆積的SQL語句時,最好采用 SBR 模式,否則很容易導致主從服務器的數據不一致情況發生

另外,針對系統庫 MySQL 裡面的表發生變化時的處理准則如下:

如果是采用 INSERT,UPDATE,DELETE 直接操作表的情況,則日志格式根據 MySQL binlog_format 的設定而記錄

如果是采用 GRANT,REVOKE,SET PASSWORD 等管理語句來做的話,那麼無論如何 都采用 SBR 模式記錄。

注:采用 RBR 模式後,能處理很多原先出現的主鍵重復問題。實例:

對於insert into db_allot_ids select from db_allot_ids 這個語句:

在BINLOG_FORMAT=STATEMENT 模式下:

BINLOG日志信息為:

  1. BEGIN  
  2. /*!*/;  
  3. # at 173  
  4. #090612 16:05:42 server id 1 end_log_pos 288 Query thread_id=4 exec_time=0 error_code=0 
  5. SET TIMESTAMP=1244793942/*!*/;  
  6. insert into db_allot_ids select * from db_allot_ids  
  7. /*!*/; 

在BINLOG_FORMAT=ROW 模式下:

BINLOG日志信息為:

  1. BINLOG '  
  2. hA0yShMBAAAAMwAAAOAAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRzAAIBAwAA  
  3. hA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA=  
  4. '/*!*/; 

以上的相關內容就是對設置自動清理MySQL binlog日志和手動刪除的方法的介紹,望你能有所收獲。

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