mysql主從不同步報錯Last_Errno 1197

今天mysql從庫收到一份報錯，從庫死了，不能同步資料了，報錯如下紅色部分：

Last_Errno: 1197

Last_Error: Could not execute Write_rows event on table mbpay.ATTACHMENT_copy; Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again, Error_code: 1197; Writing one row to the row-based binary log failed, Error_code: 1534; handler error HA_ERR_RBR_LOGGING_FAILED; the event's master log fb-bin.001315, end_log_pos 2241781395

解決辦法：根據你的機器的記憶體大小適當增大引數max_binlog_cache_size引數

檢視現在的大小：

1）檢視全域性的引數大小：

mysql> show GLOBAL variables like 'max_binlog_cache_size';

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

| Variable_name | Value |

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

| max_binlog_cache_size | 18446744073709547520 |

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

1 row in set (0.00 sec)

2）檢視當前會話的引數的大小：

mysql> show session variables like 'max_binlog_cache_size';

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

| Variable_name | Value |

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

| max_binlog_cache_size | 18446744073709547520 |

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

1 row in set (0.00 sec)

如果只是當前會話的小，只要

mysql> set session max_binlog_cache_size=18446744073709547520;

Query OK, 0 rows affected (0.00 sec)

否則需要

mysql> set global binlog_cache_size=18446744073709547520;

Query OK, 0 rows affected (0.00 sec)

下面具體分析問題出現的原因：

1）首先學習下mysql 寫binlog的機制：

我們知道mysql的InnoDB儲存引擎是支援事務的，實現事務需要依賴於日誌技術，為了效能，日誌編碼採用二進位制格式，記錄二進位制日誌的時候，資料庫首先把binlog寫進binlog_cache中，然後再從cache中重新整理到底層磁碟（也就是binlog 日誌檔案），由於cache中的資料沒有持久化，於是面臨安全性的問題——因為系統當機時，Cache中可能有殘餘的資料沒來得及寫入磁碟。因此Cache要權衡，要恰到好處：既減少磁碟I/O，滿足效能要求；又保證Cache無殘留，及時持久化，滿足安全要求，也就是說binlog_cache的大小一定要控制好，太大可能會導致異常斷電時，丟失過多binlog；當然太小的話可能會導致使用臨時檔案來填補cache的不足,導致io效能問題，binlog_cache_size和max_binlog_cache_size引數就是控制binlog_cache大小的；

2）binlog_cache_size和max_binlog_cache_size引數：

引數：binlog_cache_size ：一個事務，在沒有提交（uncommitted）的時候，產生的日誌，記錄到Cache中；等到事務提交（committed）需要提交的時候，則把日誌持久化到磁碟。binlog_cache_size就是為每個session 分配的記憶體的大小，在事務過程中用來儲存二進位制日誌的快取；

binlog_cache_size設定太大的話，會比較消耗記憶體資源（Cache本質就是記憶體）； binlog_cache_size 設定太小的話，如果使用者提交一個“長事務（long_transaction）”，比如：批次匯入資料。那麼該事務必然會產生很多binlog，這樣cache可能不夠用（預設binlog_cache_size是32K），不夠用的時候mysql會把uncommitted的部分寫入臨時檔案（臨時檔案cache的效率必然沒有記憶體cache高），等到committed的時候才會寫入正式的持久化日誌檔案。

引數：max_binlog_cache_size ：表示的是所有會話加在一起的binlog 能夠使用的最大cache 記憶體大小，當我們執行多語句事務的時候 ，所有session的binlog使用的記憶體超max_binlog_cache_size的值時就會報錯：“Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage”

那麼既然cache不夠的時候，會使用臨時檔案充當cache，怎麼還會報錯more than 'max_binlog_cache_size' 呢？原來使用臨時檔案充當cache是針對某個會話的，當這個會話使用binlog_cache的大小超過binlog_cache_size的值的時候，就會使用臨時檔案，當所有session的binlog使用的記憶體超max_binlog_cache_size的值時就會報錯，所以超過max_binlog_cache_size的值的原因：1，max_binlog_cache_size這個值設定過小，2，當前會話資料量暴增；

3）如何判斷當前binlog_cache_size設定的是否合理；

binlog_cache_size 設定的大小可以透過狀態變數binlog_cache_use和binlog_cache_disk_use來幫助測試;因為：

binlog_cache_use:使用二進位制日誌快取(也就是binlog_cache)的事務數量；

binlog_cache_disk_use:使用二進位制日誌快取但超過binlog_cache_size值並使用臨時檔案來充當binlog cache儲存的事務數量。

檢視前面狀態變數的大小：

mysql> show status like 'binlog_%';

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

| Variable_name | Value |

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

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 120402264 |

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

2 rows in set (0.00 sec)

執行情況Binlog_cache_use 表示binlog_cache記憶體方式被用上了多少次，Binlog_cache_disk_use表示binlog_cache臨時檔案方式被用上了多少次。Binlog_cache_disk_use現在等於0，表示記憶體cache是夠用的，從來不需要使用到臨時檔案，如果Binlog_cache_disk_use不等於零，則說明當前會話的Binlog_cache_use設定的不夠，需要增大。

4）底層binlog檔案切換的條件：

我們知道binlog file 使用索引來迴圈檔案，在以下條件將迴圈至下一個索引

1.mysql服務重啟的時候

2.日誌達到了最大日誌長度max_binlog_size設定的值時；

3.日誌被重新整理： mysql> flush logs;

如下是我的binlog的目錄，正在使用的是 mysql-bin.000182（也就是編號最大的），mysql-bin.index是用來控制binlog迴圈的檔案；

[root@server02 mysql]# ll

-rw-rw---- 1 mysql mysql 9556 7月 23 20:48 mysql-bin.000181

-rw-rw---- 1 mysql mysql 120 7月 23 20:48 mysql-bin.000182

-rw-rw---- 1 mysql mysql 64 7月 23 20:48 mysql-bin.index

5)重點說說主從同步的過程

mysql主從同步的過程的第一部分就是master記錄二進位制日誌，在每個事務更新資料完成之前，master在二進位制日誌記錄這些改變，MySQL將事務序列的寫入二進位制日誌，即使事務中的語句都是交叉執行的。在事件寫入二進位制日誌完成後，master通知儲存引擎提交事務，salve伺服器會在一定時間間隔內對master二進位制日誌進行探測其是否發生改變，如果發生改變，則開始一個I/OThread請求master二進位制事件，同時主節點為每個I/O執行緒啟動一個dump執行緒，用於向其傳送二進位制事件，之後slave的io執行緒去接收主庫傳送過來的binlog,然後寫進本地binlog cahce中，（值得注意的是master的Binlog Dump程式讀取master庫的binlog cache中的binlog）然後重新整理到底層磁碟的中繼日誌（reley log）檔案中，最後slave的sql程式應用reley log重演變化，實現同步。

那麼為什麼主庫沒有報錯，但是從庫會報錯呢？

按道理講mysql5.6主庫可以並行寫，但是從庫是序列復制（雖然支援多執行緒，但是是一個庫一個執行緒）的，不可能由會話太多導致報錯，只能一個原因就是從庫的max_binlog_cache_size設定比主庫的小，驗證發現果然如此，這個報錯是因為有一個大事務binlog寫到從庫的binlog cache的時候，由於超過了從庫的max_binlog_cache_size，導致報錯；

主從複製的過程（摘自網路）：

　　1. 當在從庫slave執行change的操作之後，Slave 上面的IO執行緒連線上 Master，並請求從指定日誌檔案的指定位置(或者從最開始的日誌)之後的日誌內容;

　 　2. Master 接收到來自 Slave 的 IO 執行緒的請求後，透過負責複製的Binlog Dump執行緒根據請求資訊讀取指定日誌指定位置之後的日誌資訊，返回給 Slave 端的 IO 執行緒。返回資訊中除了日誌所包含的資訊之外，還包括本次返回的資訊在 Master 端的 Binary Log 檔案的名稱以及在 Binary Log 中的位置;

　　3. Slave 的 IO 執行緒接收到資訊後，將接收到的日誌內容依次寫入到 Slave 端的Relay Log檔案(mysql-relay-bin.xxxxxx)的最末端，並將讀取到的Master端的bin-log的檔名和位置記錄到master- info檔案中，以便在下一次讀取的時候能夠清楚的告訴Master“我需要從某個bin-log的哪個位置開始往後的日誌內容，請發給我”，

　 　4. Slave 的 SQL 執行緒檢測到 Relay Log 中新增加了內容後，會馬上解析該 Log 檔案中的內容成為在 Master 端真實執行時候的那些可執行的 Query 語句，並在自身執行這些 Query。這樣，實際上就是在 Master 端和 Slave 端執行了同樣的 Query，所以兩端的資料是完全一樣的。

體外話：mysql5.7之前，雖然從mysql5.6開始支援多執行緒複製，但是是對於每一個庫一個複製執行緒，這樣的話，如果某個例項只有一個庫或者有多個庫但是業務居中在某一個庫上，那麼實際上多執行緒可能會更慢，因為多執行緒排程器需要分配哪個執行緒給這個庫，當然如果這個例項有多個庫，並且業務分佈在多個庫上，這樣開啟多執行緒是有效果的。mysql5.7增強了這個多執行緒複製功能，基於logical_clock的複製方式，真正做到了表級別的多執行緒，真正實現了並行複製。

，