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

本篇內(nèi)容主要講解“mysql主從不同步報錯Last_Errno 1197的解決方法”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“mysql主從不同步報錯Last_Errno 1197的解決方法”吧!

今天mysql從庫收到一份報錯，從庫死了，不能同步數(shù)據(jù)了，報錯如下紅色部分：

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

解決辦法：根據(jù)你的機(jī)器的內(nèi)存大小適當(dāng)增大參數(shù)max_binlog_cache_size參數(shù)

查看現(xiàn)在的大?。?/p>

1）查看全局的參數(shù)大小：

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）查看當(dāng)前會話的參數(shù)的大?。?/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)

如果只是當(dāng)前會話的小，只要

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)

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

1）首先學(xué)習(xí)下mysql 寫binlog的機(jī)制：

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

2）binlog_cache_size和max_binlog_cache_size參數(shù)：

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

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

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

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

3）如何判斷當(dāng)前binlog_cache_size設(shè)置的是否合理；

binlog_cache_size 設(shè)置的大小可以通過狀態(tài)變量binlog_cache_use和binlog_cache_disk_use來幫助測試;因?yàn)椋?/p>

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

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

查看前面狀態(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)

運(yùn)行情況Binlog_cache_use 表示binlog_cache內(nèi)存方式被用上了多少次，Binlog_cache_disk_use表示binlog_cache臨時文件方式被用上了多少次。Binlog_cache_disk_use現(xiàn)在等于0，表示內(nèi)存cache是夠用的，從來不需要使用到臨時文件，如果Binlog_cache_disk_use不等于零，則說明當(dāng)前會話的Binlog_cache_use設(shè)置的不夠，需要增大。

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

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

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

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

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

如下是我的binlog的目錄，正在使用的是 mysql-bin.000182（也就是編號最大的），mysql-bin.index是用來控制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)說說主從同步的過程

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

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

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

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

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

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

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

　 　4. Slave 的 SQL 線程檢測到 Relay Log 中新增加了內(nèi)容后，會馬上解析該 Log 文件中的內(nèi)容成為在 Master 端真實(shí)執(zhí)行時候的那些可執(zhí)行的 Query 語句，并在自身執(zhí)行這些 Query。這樣，實(shí)際上就是在 Master 端和 Slave 端執(zhí)行了同樣的 Query，所以兩端的數(shù)據(jù)是完全一樣的。

到此，相信大家對“mysql主從不同步報錯Last_Errno 1197的解決方法”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！