Shulou(Shulou.com)06/01 Report--

Record the process of handling the master-slave failure here:

Fault 1D 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-90b3mura0423f31cad6Vera 716212' at master log 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

Process:

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

Solution: after temporarily increasing this value, restart slave can continue to copy threads.

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

For more information about max_binlog_cache_size, please see here:

If a transaction requires more memory than the set value, the above error is reported. The minimum is 4096 bytes and the maximum is 4GB, because the binlog location point is the largest 4GB, which supports dynamic modification.

After the problem is solved, continue to find the cause of the problem, the parameter in the production environment is set to 64m, when the amount of data affected by a transaction exceeds this value, an error will be reported.

View the details of the log in the main library:

The database log format is mixed, and what is recorded in the file is row format. Use the following command to view

/ bin/mysqlbinlog-vv-- base64-output=decode-rows mysql.135371 | streamlined content of more: # 13:43:59 server id * end_log_pos 10538 CRC32 Write_rows: table id 277 simplified at 10538 # 13:43:59 server id * end_log_pos 18175 CRC32 Write_rows: table id 277 simplified at 18175 # 13:43:59 server id * end_log_pos 25789 CRC32 Write_rows: table id 277 areas at 25789 # 13:43:59 server id * end_log_pos 33424 CRC32 Write_rows: table id 277 cities at 33424 # 13:43:59 server id * end_log_pos 40961 CRC32 Write_rows: table id 277 clients at 40961 # 13:43:59 server id * end_log_pos 48553 CRC32 Write_rows: table id 277 clients at 48553 # 13: 43:59 server id * end_log_pos 56126 CRC32 Write_rows: table id 277 floor at 56126 # 13:43:59 server id * end_log_pos 63671 CRC32 Write_rows: table id 277 floor at 63671 # 13:43:59 server id * * * end_log_pos 71205 CRC32 Write_rows: table id 277 floor at 71205 # 13:43:59 server id * end_log_pos 78753 CRC32 Write_rows: table id 277 floor at 78753 # 13:43:59 server id * end_log_pos 86322 CRC32 Write_rows: table id 277 permanent at 86322 # 13:43:59 server id * end_log_pos 93925 CRC32 Write_rows: table id 2776

Without accident, the sql executed is

Insert into table1 select * from table2 where time > '2016-10-01'

In the form of a multi-statement transaction, check the size of the binlog file, the maximum is more than 200m (max_binlog_size is set to 50m).

Therefore, it is caused by large transactions, and large transactions are also easy to cause master-slave delay. It is recommended to split large transactions into small transactions.

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

Fault 2dint Errno: 1872je error: Slave failed to initialize relay log info structure from the repository

The database reported the error after the'in place' upgrade, and the problem was resolved by rebooting the master and slave.

Check the problem some people say that the reason is caused by changes in library information, the solution can be reset slave, re-change master.

Welcome criticism and correction

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.