One parameter causes MySQL to crash from vault

Original works, allow reprint, please be sure to use hyperlinks in the form of the original source of the article, author information and this statement. Otherwise, the legal liability will be investigated. http://suifu.blog.51cto.com/9167728/1859252

One parameter causes MySQL to crash from vault

Part1: max_binlog_cache_size

Max_binlog_cache_size represents the maximum cache memory size that can be used by binlog

When we execute a multi-statement transaction, when the memory used by all sessions exceeds the value of Max_binlog_cache_size

will be error: "Multi-statement transaction required more than ' max_binlog_cache_size ' bytes Ofstorage"

Part2: Why it can cause downtime

Warning: Police Report 1

Max_binlog_cache_size in the case of inconsistent master-slave settings, the main library parameter value is greater than the value from the library parameter, in the main library for large transaction operations, the main library smoothly, from the library because the Max_binlog_cache_size value is lower than the object required, from the library will be thrown " Multi-statement transaction required more than ' max_binlog_cache_size ' bytes ofstorage ' ERROR number is 1197.

Warning: Police report 2

Max_binlog_cache_size in the case of master-slave parameter settings, the main library performs large transaction operations, such as the main library prompts to improve the parameters to execute SQL successfully, but the DBA only adjusts the max_binlog_cache_size of the main library and forgets to adjust the max_ from the library. Binlog_cache_size, you will also get 1197 errors from the library, causing the master to never sync.

PART3: What is the setting value?

The specific value is set to how much, not on paper, but also need to see the company's specific business, here in addition to not set the value to use the default value (the default is very large), the personal recommended value is 4G, basic enough to deal with most occasions, but whether or not to specify this value, in the operation of the large table, you need to Avoid the problem that the value is not set unreasonably causing the 1197 to be unable to perform the report from the library. The specific commands are as follows:

Set global max_binlog_cache_size = 4294967296;

--Summary --

The value is a dynamic parameter and can be adjusted at any time using the above command, so don't forget to add the parameter to MY.CNF to prevent it from being invalidated after restarting the database. due to the author's limited level, writing time is also very hasty, the text will inevitably appear some errors or inaccurate places, inappropriate to ask readers to criticize correct.

This article is from the "He Lei" blog, make sure to keep this source http://suifu.blog.51cto.com/9167728/1859252

One parameter causes MySQL to crash from vault