mysql主從不同步報錯Last_Errno 1197的解決方法

本篇內容主要講解“mysql主從不同步報錯Last_Errno 1197的解決方法”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“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，所以兩端的數據是完全一樣的。

到此，相信大家對“mysql主從不同步報錯Last_Errno 1197的解決方法”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！