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

本篇內容主要講解“主從不同步報錯Last_Errno 1197的解決方法”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強。下面就讓小編來(lái)帶大家學(xué)習“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參數

查看現在的大?。?/p>

1）查看全局的參數大?。?/p>

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）查看當前會(huì )話(huà)的參數的大?。?/p>

mysql>  show session  variables like 'max_binlog_cache_size';

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

| Variable_name         | Value                |

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

| max_binlog_cache_size | 18446744073709547520 |

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

1 row in set (0.00 sec)

如果只是當前會(huì )話(huà)的小，只要

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)

下面具體分析問(wèn)題出現的原因：

1）首先學(xué)習下mysql 寫(xiě)binlog的機制：

我們知道mysql的InnoDB存儲引擎是支持事務(wù)的，實(shí)現事務(wù)需要依賴(lài)于日志技術(shù)，為了性能，日志編碼采用二進(jìn)制格式，記錄二進(jìn)制日志的時(shí)候，數據庫首先把binlog寫(xiě)進(jìn)binlog_cache中，然后再從cache中刷新到底層磁盤(pán)（也就是binlog 日志文件），由于cache中的數據沒(méi)有持久化，于是面臨安全性的問(wèn)題——因為系統宕機時(shí)，Cache中可能有殘余的數據沒(méi)來(lái)得及寫(xiě)入磁盤(pán)。因此Cache要權衡，要恰到好處：既減少磁盤(pán)I/O，滿(mǎn)足性能要求；又保證Cache無(wú)殘留，及時(shí)持久化，滿(mǎn)足安全要求，也就是說(shuō)binlog_cache的大小一定要控制好，太大可能會(huì )導致異常斷電時(shí)，丟失過(guò)多binlog；當然太小的話(huà)可能會(huì )導致使用臨時(shí)文件來(lái)填補cache的不足,導致io性能問(wèn)題，binlog_cache_size和max_binlog_cache_size參數就是控制binlog_cache大小的；

2）binlog_cache_size和max_binlog_cache_size參數：

參數：binlog_cache_size ：一個(gè)事務(wù)，在沒(méi)有提交（uncommitted）的時(shí)候，產(chǎn)生的日志，記錄到Cache中；等到事務(wù)提交（committed）需要提交的時(shí)候，則把日志持久化到磁盤(pán)。binlog_cache_size就是為每個(gè)session 分配的內存的大小，在事務(wù)過(guò)程中用來(lái)存儲二進(jìn)制日志的緩存；

binlog_cache_size設置太大的話(huà)，會(huì )比較消耗內存資源（Cache本質(zhì)就是內存）；                 binlog_cache_size 設置太小的話(huà)，如果用戶(hù)提交一個(gè)“長(cháng)事務(wù)（long_transaction）”，比如：批量導入數據。那么該事務(wù)必然會(huì )產(chǎn)生很多binlog，這樣cache可能不夠用（默認binlog_cache_size是32K），不夠用的時(shí)候mysql會(huì )把uncommitted的部分寫(xiě)入臨時(shí)文件（臨時(shí)文件cache的效率必然沒(méi)有內存cache高），等到committed的時(shí)候才會(huì )寫(xiě)入正式的持久化日志文件。

參數：max_binlog_cache_size ：表示的是所有會(huì )話(huà)加在一起的binlog 能夠使用的最大cache 內存大小，當我們執行多語(yǔ)句事務(wù)的時(shí)候 ，所有session的binlog使用的內存超max_binlog_cache_size的值時(shí)就會(huì )報錯：“Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage”

那么既然cache不夠的時(shí)候，會(huì )使用臨時(shí)文件充當cache，怎么還會(huì )報錯more than 'max_binlog_cache_size' 呢？原來(lái)使用臨時(shí)文件充當cache是針對某個(gè)會(huì )話(huà)的，當這個(gè)會(huì )話(huà)使用binlog_cache的大小超過(guò)binlog_cache_size的值的時(shí)候，就會(huì )使用臨時(shí)文件，當所有session的binlog使用的內存超max_binlog_cache_size的值時(shí)就會(huì )報錯，所以超過(guò)max_binlog_cache_size的值的原因：1，max_binlog_cache_size這個(gè)值設置過(guò)小，2，當前會(huì )話(huà)數據量暴增；

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

binlog_cache_size 設置的大小可以通過(guò)狀態(tài)變量binlog_cache_use和binlog_cache_disk_use來(lái)幫助測試;因為：

binlog_cache_use:使用二進(jìn)制日志緩存(也就是binlog_cache)的事務(wù)數量；

binlog_cache_disk_use:使用二進(jìn)制日志緩存但超過(guò)binlog_cache_size值并使用臨時(shí)文件來(lái)充當binlog cache保存的事務(wù)數量。

查看前面狀態(tài)變量的大?。?/p>

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臨時(shí)文件方式被用上了多少次。Binlog_cache_disk_use現在等于0，表示內存cache是夠用的，從來(lái)不需要使用到臨時(shí)文件，如果Binlog_cache_disk_use不等于零，則說(shuō)明當前會(huì )話(huà)的Binlog_cache_use設置的不夠，需要增大。

4）底層binlog文件切換的條件：

我們知道binlog file 使用索引來(lái)循環(huán)文件，在以下條件將循環(huán)至下一個(gè)索引

1.mysql服務(wù)重啟的時(shí)候

2.日志達到了最大日志長(cháng)度max_binlog_size設置的值時(shí)；

3.日志被刷新： mysql> flush logs;

如下是我的binlog的目錄，正在使用的是 mysql-bin.000182（也就是編號最大的），mysql-bin.index是用來(lái)控制binlog循環(huán)的文件；

[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)重點(diǎn)說(shuō)說(shuō)主從同步的過(guò)程

mysql主從同步的過(guò)程的第一部分就是master記錄二進(jìn)制日志，在每個(gè)事務(wù)更新數據完成之前，master在二進(jìn)制日志記錄這些改變，MySQL將事務(wù)串行的寫(xiě)入二進(jìn)制日志，即使事務(wù)中的語(yǔ)句都是交叉執行的。在事件寫(xiě)入二進(jìn)制日志完成后，master通知存儲引擎提交事務(wù)，salve會(huì )在一定時(shí)間間隔內對master二進(jìn)制日志進(jìn)行探測其是否發(fā)生改變，如果發(fā)生改變，則開(kāi)始一個(gè)I/OThread請求master二進(jìn)制事件，同時(shí)主節點(diǎn)為每個(gè)I/O線(xiàn)程啟動(dòng)一個(gè)dump線(xiàn)程，用于向其發(fā)送二進(jìn)制事件，之后slave的io線(xiàn)程去接收主庫發(fā)送過(guò)來(lái)的binlog,然后寫(xiě)進(jìn)本地binlog cahce中，（值得注意的是master的Binlog Dump進(jìn)程讀取master庫的binlog cache中的binlog）然后刷新到底層磁盤(pán)的中繼日志（reley log）文件中，最后slave的sql進(jìn)程應用reley log重演變化，實(shí)現同步。

那么為什么主庫沒(méi)有報錯，但是從庫會(huì )報錯呢？

按道理講mysql5.6主庫可以并行寫(xiě)，但是從庫是串行復制（雖然支持多線(xiàn)程，但是是一個(gè)庫一個(gè)線(xiàn)程）的，不可能由會(huì )話(huà)太多導致報錯，只能一個(gè)原因就是從庫的max_binlog_cache_size設置比主庫的小，驗證發(fā)現果然如此，這個(gè)報錯是因為有一個(gè)大事務(wù)binlog寫(xiě)到從庫的binlog cache的時(shí)候，由于超過(guò)了從庫的max_binlog_cache_size，導致報錯；

主從復制的過(guò)程（摘自網(wǎng)絡(luò )）：

　　1. 當在從庫slave執行change的操作之后，Slave 上面的IO線(xiàn)程連接上 Master，并請求從指定日志文件的指定位置(或者從最開(kāi)始的日志)之后的日志內容;

　 　2. Master 接收到來(lái)自 Slave 的 IO 線(xiàn)程的請求后，通過(guò)負責復制的Binlog Dump線(xiàn)程根據請求信息讀取指定日志指定位置之后的日志信息，返回給 Slave 端的 IO 線(xiàn)程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息在 Master 端的 Binary Log 文件的名稱(chēng)以及在 Binary Log 中的位置;

　　3. Slave 的 IO 線(xiàn)程接收到信息后，將接收到的日志內容依次寫(xiě)入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并將讀取到的Master端的bin-log的文件名和位置記錄到master- info文件中，以便在下一次讀取的時(shí)候能夠清楚的告訴Master“我需要從某個(gè)bin-log的哪個(gè)位置開(kāi)始往后的日志內容，請發(fā)給我”，

　 　4. Slave 的 SQL 線(xiàn)程檢測到 Relay Log 中新增加了內容后，會(huì )馬上解析該 Log 文件中的內容成為在 Master 端真實(shí)執行時(shí)候的那些可執行的 Query 語(yǔ)句，并在自身執行這些 Query。這樣，實(shí)際上就是在 Master 端和 Slave 端執行了同樣的 Query，所以?xún)啥说臄祿峭耆粯拥摹?/p>