MySQL 參數“max_binlog_cache_size”過小導致SQL失敗

標籤：mysql binlog   format_row

今天，開發同事在發布一個SQL的時候失敗後，找到我說報告了如下錯誤：

ERROR 1197 (HY000) at line 4: Multi-statement transaction required more than ‘max_binlog_cache_size‘ bytes of storage; increase this mysqld variable and try again

意思是多語句食物請求更大的max_binlog_cache_size，需要增加此參數值後再次嘗試

這個時候接到警示說主從不同步，SQL線程掛掉了

登陸系統後查看主從狀態後，果然和同事的這個SQL有關係

詢問了一下同事的操作的SQL：

首先複製一張表，方式是：create table  table_B like table_A,然後使用insert into  table_B select * from table_A 

總共是四張表這樣的然後只執行成功了一張表，後面就報了如上的錯誤

注意：使用like方式建立的表好處就是可以獲得一張和源表一樣的表結構索引和儲存引擎等

           缺點就是建立的是一張空表，需要再次將資料插入到新表中

但這樣的方式為什會造成一個從庫複製中斷呢？而另外的從庫是正常的

原因是這樣的：複製中斷的這個從庫的角色是備份庫，開啟了binlog 且binlog格式是ROW，其他從庫未開啟binlog

mysql> show variables like ‘binlog_format‘;

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

| Variable_name | Value |

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

| binlog_format | ROW   |

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

row 格式的binlog的特點：在 row 模式下，所有的執行的語句當記錄到日誌中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日誌內容。所以會造成binlog cache因為過小而中斷

知道了錯誤原因就好解決問題了：

首先增加這個參數的大小：set global max_binlog_cache_size=XXXXXXX  （這樣重啟系統後會失效）

然後啟動複製進程 start slave； 

查看複製狀態 show slave status\G

主從複製恢複正常

同事的SQL再次執行沒有出現上述錯誤。

本文出自 “影子騎士” 部落格，請務必保留此出處http://andylhz2009.blog.51cto.com/728703/1852007

MySQL 參數“max_binlog_cache_size”過小導致SQL失敗