"MySQL Fault handling" seconds_behind_master= NULL error_code:1197

The process of handling master-slave failures is documented here:

• Fault 1,error_code:1197 (max_binlog_cache_size)

Database version: 5.7.9

Error message:

[ERROR] Slave SQL for Channel ": Worker 1 failed executing transaction ' bea4612c-4828-11e7-90b3-a0423f31cad6:716212 ' at Master Lo G mysql.135371, End_log_pos 103016490; Could not execute Write_rows event on table ******; 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 first, End_log_pos 103016490, error_code:1197

Processing process:

According to the error message, know that the max_binlog_cache_size is small,

Workaround: Temporarily increase the value after restart slave to continue copying the thread.

Stop Slave;set global max_binlog_cache_size= 201326592;start slave;

For max_binlog_cache_size please see here:

If a transaction needs more memory than the set value, it will report the error above. Minimum 4096 bytes, maximum support 4GB, because Binlog location point maximum 4GB, support dynamic modification .

Resolves the problem and continues to find the cause of the problem, which is set to 64M in the production environment, and an error occurs when the amount of data affected by a transaction exceeds the value.

View the details of the logs in the main library:

The database log format is mixed, the file is recorded in the row format, use the following command to view

/BIN/MYSQLBINLOG-VV--base64-output=decode-rows mysql.135371 | More

What's streamlined: #  13:43:59 server id *******  end_log_pos 10538 crc32   Write_rows: table id 2776# at 10538                                            #   13:43:59 server id *******  end_log_pos 18175 CRC32   write_rows: table id 2776# at 18175                                           #   13:43:59 server id *******  end_log_pos 25789 crc32  write_rows:  table id 2776# at 25789                                             #  13:43:59 server id ***   end_log_pos 33424 crc32  write_rows: table id 2776# at  33424                                              #  13:43:59 server id ***   end_log_pos 40961 crc32  write_rows: table id 2776# at  40961                                             #  13:43:59 server id *******  end_log_pos  48553 CRC32  Write_rows: table id 2776# at 48553                                             #  13:43:59 server id *******  end_log_pos  56126 crc32  write_rows: table id 2776# at 56126                                             #  13:43:59 server id *******  end_log_pos 63671 crc32  write_rows:  table id 2776# at 63671                                            #  13:43:59  server id *******  end_log_pos 71205 CRC32  Write_rows:  table id 2776# at 71205                                             #  13:43:59  server id *******  end_log_pos 78753 CRC32  Write_rows:  Table id 2776# at 78753                                             #  13:43:59 server id *******   end_log_pos 86322 crc32  write_rows: table id 2776# at  86322                                             #  13:43:59 server id *******   end_log_pos 93925 crc32  write_rows: table id 2776

Not surprisingly, the SQL executed is

INSERT INTO table1 select * from table2 where time > ' 2016-10-01 '

form of multi-statement transactions, view the size of the Binlog file, up to a maximum of 200M (Max_binlog_size set to 50M).

As a result of large transactions, large transactions can easily cause the problem of master-slave delay, it is recommended to split the large transaction into small transaction execution

107m  14:07 mysql.135380101m  14:08  mysql.135381112m  14:08 mysql.135382110m  14:09 mysql.135383124m   14:09 mysql.135384 31m  14:10 mysql.135385226m  14:10 mysql.135386120m   14:12 mysql.135387111M  14:13 mysql.135388102M  14:14  mysql.135389 ... 126m  14:16 mysql.135394 ... 118m  14:23 mysql.135404 ... 110m  14:28 mysql.135409 63m  14:29 mysql.135410104m  14:29  mysql.135411 281  14:30 mysql.135412115m  14:30 mysql.135413112m   14:30 mysql.135414127m  14:30 mysql.135415119m  14:31 mysql.135416  85m  14:32 mysql.135417 60m  14:32 mysql.135418151m  14:33  mysql.135419 

Welcome to Criticize

This article is from the "Amnesiasun" blog, make sure to keep this source http://amnesiasun.blog.51cto.com/10965283/1931957

"MySQL Fault handling" seconds_behind_master= NULL error_code:1197