binlog二進制日志對於mysql數據庫的重要性有多大，在此就不多說了。下面根據本人的日常操作經歷，並結合網上參考資料，對binlog日志使用做一梳理：
一、binlog日志介紹
1）什麼是binlog
binlog日志用於記錄所有更新了數據或者已經潛在更新了數據（例如，沒有匹配任何行的一個DELETE）的所有語句。語句以“事件”的形式保存，它描述數據更改。
2）binlog作用
因為有了數據更新的binlog，所以可以用於實時備份，與master/slave主從復制結合。
3）和binlog有關參數
log_bin
設置此參數表示啟用binlog功能，並指定路徑名稱
log_bin_index
設置此參數是指定二進制索引文件的路徑與名稱
binlog_do_db
此參數表示只記錄指定數據庫的二進制日志
binlog_ignore_db
此參數表示不記錄指定的數據庫的二進制日志
max_binlog_cache_size
此參數表示binlog使用的內存最大的尺寸
binlog_cache_size
此參數表示binlog使用的內存大小，可以通過狀態變量binlog_cache_use和binlog_cache_disk_use來幫助測試。
binlog_cache_use：使用二進制日志緩存的事務數量
binlog_cache_disk_use:使用二進制日志緩存但超過binlog_cache_size值並使用臨時文件來保存事務中的語句的事務數量
max_binlog_size
Binlog最大值，最大和默認值是1GB，該設置並不能嚴格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性，不可能做切換日志的動作，只能將該事務的所有SQL都記錄進當前日志，直到事務結束
sync_binlog
這個參數直接影響mysql的性能和完整性
sync_binlog=0
當事務提交後，Mysql僅僅是將binlog_cache中的數據寫入Binlog文件，但不執行fsync之類的磁盤 同步指令通知文件系統將緩存刷新到磁盤，而讓Filesystem自行決定什麼時候來做同步，這個是性能最好的。
sync_binlog=n，在進行n次事務提交以後，Mysql將執行一次fsync之類的磁盤同步指令，同志文件系統將Binlog文件緩存刷新到磁盤。
Mysql中默認的設置是sync_binlog=0，即不作任何強制性的磁盤刷新指令，這時性能是最好的，但風險也是最大的。一旦系統繃Crash，在文件系統緩存中的所有Binlog信息都會丟失
4）binlog的刪除
binlog的刪除可以手工刪除或自動刪除：
a）自動刪除binlog
通過binlog參數（expire_logs_days ）來實現mysql自動刪除binlog
mysql> show binary logs;
mysql> show variables like 'expire_logs_days'; //該參數表示binlog日志自動刪除/過期的天數，默認值為0，表示不自動刪除
mysql> set global expire_logs_days=3; //表示日志保留3天，3天後就自動過期。
b）手工刪除binlog
mysql> reset master; //刪除master的binlog，即手動刪除所有的binlog日志
mysql> reset slave; //刪除slave的中繼日志
mysql> purge master logs before '2012-03-30 17:20:00'; //刪除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000002'; //刪除指定日志文件的日志索引中binlog日志文件
mysql> set sql_log_bin=1/0; //如果用戶有super權限，可以啟用或禁用當前會話的binlog記錄
mysql> show master logs; //查看master的binlog日志列表
mysql> show binary logs; //查看master的binlog日志文件大小
mysql> show master status; //用於提供master二進制日志文件的狀態信息
mysql> show slave hosts; //顯示當前注冊的slave的列表。不以--report-host=slave_name選項為開頭的slave不會顯示在本列表中
mysql> flush logs; //產生一個新的binlog日志文件
mysql binlog日志自動清理及手動刪除案例說明：
當開啟MySQL數據庫主從時，會產生大量如mysql-bin.00000* log的文件，這會大量耗費您的硬盤空間。
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
…
刪除這些binlog日志有三種解決方法：
1.關閉mysql主從，關閉binlog；
實例操作如下：
[root@huqniupc ~]# vim /etc/my.cnf //注釋掉log-bin和binlog_format
# Replication Master Server (default)
# binary logging is required for replication
# log-bin=mysql-bin
# binary logging format - mixed recommended
# binlog_format=mixed
然後重啟數據庫
2.開啟mysql主從，設置expire_logs_days；
實例操作如下：
[root@huqniupc ~]# vim /etc/my.cnf //修改expire_logs_days,x是自動刪除的天數，一般將x設置為短點，如10
expire_logs_days = x //二進制日志自動刪除的天數。默認值為0,表示“沒有自動刪除”
此方法需要重啟mysql
當然也可以不重啟mysql,開啟mysql主從，直接在mysql裡設置expire_logs_days
> show binary logs;
> show variables like '%log%';
> set global expire_logs_days = 10;
3.手動清除binlog文件，(比如Mysql> PURGE MASTER LOGS TO ‘MySQL-bin.010′;）
實例操作如下：
[root@huqniupc ~]# /usr/local/mysql/bin/mysql -u root -p
> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY); //刪除10天前的MySQL binlog日志,附錄2有關於PURGE MASTER LOGS手動刪除用法及示例
> show master logs;
也可以重置master，刪除所有binlog文件：
# /usr/local/mysql/bin/mysql -u root -p
> reset master; //附錄3有清除binlog時，對從mysql的影響說明
---------------------------------------------------------------
PURGE MASTER LOGS手動刪除用法及示例,MASTER和BINARY是同義詞
> PURGE {MASTER | BINARY} LOGS TO 'log_name'
> PURGE {MASTER | BINARY} LOGS BEFORE 'date'
刪除指定的日志或日期之前的日志索引中的所有二進制日志。這些日志也會從記錄在日志索引文件中的清單中被刪除MySQL BIN-LOG 日志，這樣被給定的日志成為第一個。
實例：
> PURGE MASTER LOGS TO 'MySQL-bin.010'; //清除MySQL-bin.010日志
> PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00'; //清除2008-06-22 13:00:00前binlog日志
> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); //清除3天前binlog日志BEFORE，變量的date自變量可以為'YYYY-MM-DD hh:mm:ss'格式。
-----------------------------------------------------
5）清除binlog時，對從mysql的影響
如果有一個活躍的slave從屬服務器，該服務器當前正在讀取您正在試圖刪除的日志之一，則本語句不會起作用，而是會失敗，並伴隨一個錯誤；不過如果slave從屬服務器是關閉的（或master-slave主從關系關閉），並且碰巧清理了其想要讀取的日志之一，則slave從屬服務器啟動後不能復制；當從屬服務器正在復制時，本語句可以安全運行，不需要停止它們。
6）binglog的查看
通過mysqlbinlog命令可以查看binlog的內容
[root@localhost ~]# mysqlbinlog /home/mysql/binlog/binlog.000003 | more
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#120330 16:51:46 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.45-log created 120330 1
6:51:46
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
# at 196
#120330 17:54:15 server id 1 end_log_pos 294 Query thread_id=3 exec_time=2 error_code=0
SET TIMESTAMP=1333101255/*!*/;
insert into tt7 select * from tt7/*!*/;
# at 294
#120330 17:54:46 server id 1 end_log_pos 388 Query thread_id=3 exec_time=28 error_code=0
SET TIMESTAMP=1333101286/*!*/;
alter table tt7 engine=innodb/*!*/;
解析binlog格式：
位置
位於文件中的位置，“at 196”說明“事件”的起點，是以第196字節開始；“end_log_pos 294”說明以第294字節結束
時間戳
事件發生的時間戳：“120330 17:54:46”
事件執行時間
事件執行花費的
1.開始事物的
“binlog_cache_size"：在事務過程中容納二進制日志SQL語句的緩存大小。二進制日志緩存是服務器支持事務存儲引擎並且服務器啟用了二進制日志(—log-bin選項)的前提下為每個客戶端分配的內存，注意，是每個Client都可以分配設置大小的binlogcache空間。如果讀者朋友的系統中經常會出現多語句事務的華，可以嘗試增加該值的大小，以獲得更有的性能。當然，我們可以通過MySQL的以下兩個狀態變量來判斷當前的binlog_cache_size的狀況：Binlog_cache_use和Binlog_cache_disk_use。
“max_binlog_cache_size”：和"binlog_cache_size"相對應，但是所代表的是binlog能夠使用的最大cache內存大小。當我們執行多語句事務的時候，max_binlog_cache_size如果不夠大的話，系統可能會報出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage”的錯誤。
“max_binlog_size”：Binlog日志最大值，一般來說設置為512M或者1G，但不能超過1G。該大小並不能非常嚴格控制Binlog大小，尤其是當到達Binlog比較靠近尾部而又遇到一個較大事務的時候，系統為了保證事務的完整性，不可能做切換日志的動作，只能將該事務的所有SQL都記錄進入當前日志，直到該事務結束。這一點和Oracle的Redo日志有點不一樣，因為Oracle的Redo日志所記錄的是數據文件的物理位置的變化，而且裡面同時記錄了Redo和Undo相關的信息，所以同一個事務是否在一個日志中對Oracle來說並不關鍵。而MySQL在Binlog中所記錄的是數據庫邏輯變化信息，MySQL稱之為Event，實際上就是帶來數據庫變化的DML之類的Query語句。
“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倍甚至更多。
另：
MySQL的復制（Replication），實際上就是通過將Master端的Binlog通過利用IO線程通過網絡復制到Slave端，然後再通過SQL線程解析Binlog中的日志再應用到數據庫中來實現的。所以，Binlog量的大小對IO線程以及Msater和Slave端之間的網絡都會產生直接的影響。
MySQL中Binlog的產生量是沒辦法改變的，只要我們的Query改變了數據庫中的數據，那麼就必須將該Query所對應的Event記錄到Binlog中。那我們是不是就沒有辦法優化復制了呢？當然不是，在MySQL復制環境中，實際上是是有8個參數可以讓我們控制需要復制或者需要忽略而不進行復制的DB或者Table的，分別為：
Binlog_Do_DB：設定哪些數據庫（Schema）需要記錄Binlog；
Binlog_Ignore_DB：設定哪些數據庫（Schema）不要記錄Binlog；
Replicate_Do_DB：設定需要復制的數據庫（Schema），多個DB用逗號（“,”）分隔；
Replicate_Ignore_DB：設定可以忽略的數據庫（Schema）；
Replicate_Do_Table：設定需要復制的Table；
Replicate_Ignore_Table：設定可以忽略的Table；
Replicate_Wild_Do_Table：功能同Replicate_Do_Table，但可以帶通配符來進行設置；
Replicate_Wild_Ignore_Table：功能同Replicate_Ignore_Table，可帶通配符設置；
通過上面這八個參數，我們就可以非常方便按照實際需求，控制從Master端到Slave端的Binlog量盡可能的少，從而減小Master端到Slave端的網絡流量，減少IO線程的IO量，還能減少SQL線程的解析與應用SQL的數量，最終達到改善Slave上的數據延時問題。
實際上，上面這八個參數中的前面兩個是設置在Master端的，而後面六個參數則是設置在Slave端的。雖然前面兩個參數和後面六個參數在功能上並沒有非常直接的關系，但是對於優化MySQL的Replication來說都可以啟到相似的功能。當然也有一定的區別，其主要區別如下：
如果在Master端設置前面兩個參數，不僅僅會讓Master端的Binlog記錄所帶來的IO量減少，還會讓Master端的IO線程就可以減少Binlog的讀取量，傳遞給Slave端的IO線程的Binlog量自然就會較少。這樣做的好處是可以減少網絡IO，減少Slave端IO線程的IO量，減少Slave端的SQL線程的工作量，從而最大幅度的優化復制性能。當然，在Master端設置也存在一定的弊端，因為MySQL的判斷是否需要復制某個Event不是根據產生該Event的Query所更改的數據
所在的DB，而是根據執行Query時刻所在的默認Schema，也就是我們登錄時候指定的DB或者運行“USEDATABASE”中所指定的DB。只有當前默認DB和配置中所設定的DB完全吻合的時候IO線程才會將該Event讀取給Slave的IO線程。所以如果在系統中出現在默認DB和設定需要復制的DB不一樣的情況下改變了需要復制的DB中某個Table的數據的時候，該Event是不會被復制到Slave中去的，這樣就會造成Slave端的數據和Master的數據不一致的情況出現。同樣，如果在默認Schema下更改了不需要復制的Schema中的數據，則會被復制到Slave端，當Slave端並沒有該Schema的時候，則會造成復制出錯而停止。
而如果是在Slave端設置後面的六個參數，在性能優化方面可能比在Master端要稍微遜色一點，因為不管是需要還是不需要復制的Event都被會被IO線程讀取到Slave端，這樣不僅僅增加了網絡IO量，也給Slave端的IO線程增加了RelayLog的寫入量。但是仍然可以減少Slave的SQL線程在Slave端的日志應用量。雖然性能方面稍有遜色，但是在Slave端設置復制過濾機制，可以保證不會出現因為默認Schema的問題而造成Slave和Master數據不一致或者復制出錯的問題。
3）慢查詢日志Query Log 相關參數及使用建議
再來看看SlowQueryLog的相關參數配置。有些時候，我們為了定位系統中效率比較地下的Query語句，則需要打開慢查詢日志，也就是SlowQueryLog。我們可以如下查看系統慢查詢日志的相關設置：
mysql> show variables like 'log_slow%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | log_slow_queries | ON | +------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'long_query%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | long_query_time | 1 | +-----------------+-------+ 1 row in set (0.01 sec)
“log_slow_queries”參數顯示了系統是否已經打開SlowQueryLog功能，而“long_query_time”參數則告訴我們當前系統設置的SlowQuery記錄執行時間超過多長的Query。在MySQLAB發行的MySQL版本中SlowQueryLog可以設置的最短慢查詢時間為1秒，這在有些時候可能沒辦法完全滿足我們的要求，如果希望能夠進一步縮短慢查詢的時間限制，可以使用Percona提供的microslow-patch（件成為mslPatch）來突破該限制。mslpatch不僅僅能將慢查詢時間減小到毫秒級別，同時還能通過一些特定的規則來過濾記錄的SQL，如僅記錄涉及到某個表的SlowQuery等等附加功能。
打開SlowQueryLog功能對系統性能的整體影響沒有Binlog那麼大，畢竟SlowQueryLog的數據量比較小，帶來的IO損耗也就較小，但是，系統需要計算每一條Query的執行時間，所以消耗總是會有一些的，主要是CPU方面的消耗。如果大家的系統在CPU資源足夠豐富的時候，可以不必在乎這一點點損耗，畢竟他可能會給我們帶來更大性能優化的收獲。但如果我們的CPU資源也比較緊張的時候，也完全可以在大部分時候關閉該功能，而只需要間斷性的打開SlowQueryLog功能來定位可能存在的慢查詢。
MySQL的其他日志由於使用很少（QueryLog）或者性能影響很少，在此就不做過多分析了。
以上這篇Mysql數據庫之Binlog日志使用總結(必看篇)就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持。
