1197多语句事务要求更大的max_binlog_cache_size报错
binlog_cache_size:为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存,提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。
max_binlog_cache_size设置的参考标准
Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数；Binlog_cache_use 表示用binlog_cache_size缓存的次数，当对应的Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值
【故障情景】
通过脚本以load的方式导入数据时，出现多行事务需要的max_binlog_cache_size空间不足。该数据文件HAOHUAN.txt只包含以逗号分隔的500万行左右的数据，每行四列，文件大小为270M。
1 [root@172-16-3-190 shells]# bash +x load_data_into.sh 2 文件的总数为:1 3 文件名为:/tmp/load/HAOHUAN.txt 4 当前正在处理的文件是:/tmp/load/HAOHUAN.txt 5 load data infile '/tmp/load/HAOHUAN.txt' into table practice.temp_baofoo_unbind fields terminated by ',' lines terminated by '\n' (merchant_no,bank_code,bank_card,protocol_no) 6 Warning: Using a password on the command line interface can be insecure. 7 ERROR 1197 (HY000) at line 1: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
【故障排查】
查看max_binlog_cache_size的大小，发现数据文件的大小确实较max_binlog_cache_size的值要小，如果max_binlog_cache_size的大小不足以存放事务的binlog，那么会临时使用磁盘临时文件来存放binlog，通过查看Binlog_cache_disk_use发现使用临时文件存放的次数为1。因此增大max_binlog_cache_size的值到300M，再次执行脚本发现还是报相同的错误。且使用临时文件的次数为2，使用临时文件的存放binlog的总次数也相应由15增加到了16次。
1 mysql> show global variables like '%binlog_cache%'; 2 +-----------------------+-----------+ 3 | Variable_name | Value | 4 +-----------------------+-----------+ 5 | binlog_cache_size | 16777216 | 6 | max_binlog_cache_size | 268435456 | 7 +-----------------------+-----------+ 8 2 rows in set (0.00 sec) 9 10 mysql> show global status like '%binlog_cache%'; 11 +-----------------------+-------+ 12 | Variable_name | Value | 13 +-----------------------+-------+ 14 | Binlog_cache_disk_use | 1 | 15 | Binlog_cache_use | 15 | 16 +-----------------------+-------+ 17 2 rows in set (0.00 sec) 18 19 mysql> set @@global.max_binlog_cache_size=300000000; 20 Query OK, 0 rows affected, 1 warning (0.00 sec) 21 22 [root@172-16-3-190 shells]# bash +x load_data_into.sh 23 文件的总数为:1 24 文件名为:/tmp/load/HAOHUAN.txt 25 当前正在处理的文件是:/tmp/load/HAOHUAN.txt 26 load data infile '/tmp/load/HAOHUAN.txt' into table practice.temp_baofoo_unbind fields terminated by ',' lines terminated by '\n' (merchant_no,bank_code,bank_card,protocol_no) 27 Warning: Using a password on the command line interface can be insecure. 28 ERROR 1197 (HY000) at line 1: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again 29 30 mysql> show global status like '%binlog_cache%'; 31 +-----------------------+-------+ 32 | Variable_name | Value | 33 +-----------------------+-------+ 34 | Binlog_cache_disk_use | 2 | 35 | Binlog_cache_use | 16 | 36 +-----------------------+-------+ 37 2 rows in set (0.00 sec)
无奈直接增加max_binlog_cache_size的值到500M时问题才解决（后经test实际给到400M也可以load成功），但是slave上的值没有及时改动，因而SQL同步线程报错，stop同步线程，同master一样的更改后，同步才算正常
1 mysql> set @@global.max_binlog_cache_size=500000000; 2 Query OK, 0 rows affected, 1 warning (0.00 sec) 3 4 mysql> show slave status \G; 5 *************************** 1. row *************************** 6 Slave_IO_State: Waiting for master to send event 7 Master_Host: 172.16.3.190 8 Master_User: repl 9 Master_Port: 3309 10 Connect_Retry: 30 11 Master_Log_File: binlog.000018 12 Read_Master_Log_Pos: 120 13 Relay_Log_File: relay_bin.000006 14 Relay_Log_Pos: 6973 15 Relay_Master_Log_File: binlog.000017 16 Slave_IO_Running: Yes 17 Slave_SQL_Running: Yes 18 Replicate_Do_DB: 19 Replicate_Ignore_DB: 20 Replicate_Do_Table: 21 Replicate_Ignore_Table: 22 Replicate_Wild_Do_Table: 23 Replicate_Wild_Ignore_Table: 24 Last_Errno: 1197 25 Last_Error: Could not execute Write_rows event on table practice.temp_baofoo_unbind; 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 binlog.000017, end_log_pos 268602107 26 Skip_Counter: 0 27 Exec_Master_Log_Pos: 11408 28 Relay_Log_Space: 333526981 29 Until_Condition: None 30 Until_Log_File: 31 Until_Log_Pos: 0 32 Master_SSL_Allowed: No 33 Master_SSL_CA_File: 34 Master_SSL_CA_Path: 35 Master_SSL_Cert: 36 Master_SSL_Cipher: 37 Master_SSL_Key: 38 Seconds_Behind_Master: 208 39 Master_SSL_Verify_Server_Cert: No 40 Last_IO_Errno: 0 41 Last_IO_Error: 42 Last_SQL_Errno: 1197 43 Last_SQL_Error: Could not execute Write_rows event on table practice.temp_baofoo_unbind; 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 binlog.000017, end_log_pos 268602107 44 Replicate_Ignore_Server_Ids: 45 Master_Server_Id: 1903309 46 Master_UUID: 1b589d80-f450-11e7-9150-525400f4ecb2 47 Master_Info_File: /opt/app/mysql_3309/logs/master.info 48 SQL_Delay: 0 49 SQL_Remaining_Delay: NULL 50 Slave_SQL_Running_State: Reading event from the relay log 51 Master_Retry_Count: 86400 52 Master_Bind: 53 Last_IO_Error_Timestamp: 54 Last_SQL_Error_Timestamp: 180803 17:39:08 55 Master_SSL_Crl: 56 Master_SSL_Crlpath: 57 Retrieved_Gtid_Set: 58 Executed_Gtid_Set: 59 Auto_Position: 0 60 1 row in set (0.00 sec) 61 62 mysql> stop slave; 63 Query OK, 0 rows affected (1 min 10.64 sec)
【故障总结】
max_binlog_cache_size参数时动态参数，该值的设置可以参考binlog_cache_use的大小来相应增加。load导入或者delete数据的大小必须要大于max_binlog_cache_size的值，多行事务才能成功执行。该参数值修改后，注意要与配置文件中的值大小一致。