|
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的值，多行事务才能成功执行。该参数值修改后，注意要与配置文件中的值大小一致。
|