mysql 1197_1197多行事务要求更大的max_binlog_cache_size处理与优化

1 mysql> set @@global.max_binlog_cache_size=500000000;2 Query OK, 0 rows affected, 1 warning (0.00sec)3

4 mysql>show slave status \G;5 *************************** 1. row ***************************

6 Slave_IO_State: Waiting for master tosend event7 Master_Host: 172.16.3.190

8 Master_User: repl9 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: Yes17 Slave_SQL_Running: Yes18 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 26860210726 Skip_Counter: 027 Exec_Master_Log_Pos: 1140828 Relay_Log_Space: 33352698129 Until_Condition: None30 Until_Log_File:31 Until_Log_Pos: 032 Master_SSL_Allowed: No33 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: 20839 Master_SSL_Verify_Server_Cert: No40 Last_IO_Errno: 041 Last_IO_Error:42 Last_SQL_Errno: 119743 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-525400f4ecb247 Master_Info_File: /opt/app/mysql_3309/logs/master.info48 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.00sec)61

62 mysql>stop slave;63 Query OK, 0 rows affected (1 min 10.64 sec)