mysql binlog參數設定，mysqlbinlog

mysql binlog參數設定，mysqlbinlog
1.mysql有很多系統變數可以設定，系統變數設定不同，會導致系統運行狀態的不同。因此mysql提供兩組命令，分別查看系統設定和運行狀態。

1、系統設定：

SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW VARIABLES shows the values of MySQL system variables.
2、運行狀態：
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW STATUS provides server status information.

備忘：SHOW XXX 可能會顯示很多內容，類似Linux下內容太多了，往往需要grep來過濾，那麼mysql也考慮到了這點，使用LIKE字句可以過濾。

在安裝完MySQL之後，肯定是需要對MySQL的各種參數選項進行一些最佳化調整的。雖然MySQL系統的伸縮性很強，既可以在有很充足的硬體資源環境下高效的運行，也可以在極少資源環境下很好的運行，但不管怎樣，儘可能充足的硬體資源對MySQL的效能提升總是有協助的。在這一節我們主要分析一下MySQL的日誌（主要是Binlog）對系統效能的影響，並根據日誌的相關特性得出相應的最佳化思路。

日誌產生的效能影響

由於日誌的記錄帶來的直接效能損耗就是資料庫系統中最為昂貴的IO資源。

在之前介紹MySQL物理架構的章節中，我們已經瞭解到了MySQL的日誌包括錯誤記錄檔（ErrorLog），更新日誌（UpdateLog），二進位日誌（Binlog），查詢日誌（QueryLog），慢查詢日誌（SlowQueryLog）等。當然，更新日誌是老版本的MySQL才有的，目前已經被二進位日誌替代。

在預設情況下，系統僅僅開啟錯誤記錄檔，關閉了其他所有日誌，以達到儘可能減少IO損耗提高系統效能的目的。但是在一般稍微重要一點的實際應用情境中，都至少需要開啟二進位日誌，因為這是MySQL很多儲存引擎進行增量備份的基礎，也是MySQL實現複製的基本條件。有時候為了進一步的效能最佳化，定位執行較慢的SQL語句，很多系統也會開啟慢查詢日誌來記錄執行時間超過特定數值（由我們自行設定）的SQL語句。

一般情況下，在生產系統中很少有系統會開啟查詢日誌。因為查詢日誌開啟之後會將MySQL中執行的每一條Query都記錄到日誌中，會該系統帶來比較大的IO負擔，而帶來的實際效益卻並不是非常大。一般只有在開發測試環境中，為了定位某些功能具體使用了哪些SQL語句的時候，才會在短時間段內開啟該日誌來做相應的分析。所以，在MySQL系統中，會對效能產生影響的MySQL日誌（不包括各儲存引擎自己的日誌）主要就是Binlog了。

2.Binlog 相關參數及最佳化策略。

binlog_cache_size

Binlog_cache_disk_use 

Binlog_cache_use      

max_binlog_cache_size

max_binlog_size 

sync_binlog

“binlog_cache_size"：在事務過程中容納二進位日誌SQL語句的緩衝大小。二進位日誌緩衝是伺服器支援事務儲存引擎並且伺服器啟用了二進位日誌(—log-bin選項)的前提下為每個用戶端分配的記憶體，注意，是每個Client都可以分配設定大小的binlogcache空間。如果讀者朋友的系統中經常會出現多語句事務的華，可以嘗試增加該值的大小，以獲得更有的效能。當然，我們可以通過MySQL的以下兩個狀態變數來判斷當前的binlog_cache_size的狀況：Binlog_cache_use和Binlog_cache_disk_use。

Binlog_cache_disk_use:表示因為我們binlog_cache_size設計的記憶體不足導致緩衝二進位日誌用到了臨時檔案的次數

Binlog_cache_use :表示 用binlog_cache_size緩衝的次數

當對應的Binlog_cache_disk_use 值比較大的時候 我們可以考慮適當的調高 binlog_cache_size 對應的值

show global status like 'bin%';

上述語句我們可以得到當前 資料庫binlog_cache_size的使用方式

mysql> show status like 'binlog_%';
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Binlog_cache_disk_use | 0         |
| Binlog_cache_use      | 120402264 |
+-----------------------+-----------+

“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 binlog日誌 切換

這個是在mysql的設定檔中設定的~~~設定開啟二進位日誌~~~
 
為何 MySQL的 binlog

The danger is simple: they don't work the way you think they do. Consider the following scenario: you set binlog-ignore-db to "garbage" so data in the garbage database (which doesn't exist on the slave) isn't replicated. (I'll come back to this in a second, so if you already see the problem, don't rush to the comment form.)Now you do the following:現在做下面的事情:$ mysqlmysql delete from garbage.junk;mysql use garbage;mysql update production.users set disabled = 1 where user = "root";You just broke replication, twice. Once, because your slave is going to execute the first query and there's no such table "garbage.junk" on the slave. The second time,silently, because the update to production.users isn't replicated, so now the root user isn't disabled on the slave.複製會broke2次, 第一次，因為 slave嘗試著去之西你給第一條語句，但是slave上並沒有這樣的表"garbage.junk" , 第二次, 隱含的, 因為 對 production.users不會被 複製,因為 root帳號並沒有在slave上被禁用掉.Why? Because binlog-ignore-db doesn't do what you think. The phrase I used earlier, "data in the garbage database isn't replicated," is a fallacy. That's not what it does. In fact, itfilters out binary logging for statements issued from connections whose default database is "garbage."In other words, filtering is not based on the contents of the query -- it is based on what database you USE.The other configuration options I mentioned work similarly. The binlog-do-db and binlog-ignore......餘下全文>>