Shulou(Shulou.com)05/31 Report--

This article mainly explains "the solution of mysql master-slave asynchronous error reporting Last_Errno 1197". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "mysql master-slave asynchronous error reporting Last_Errno 1197 solution" it!

Today, mysql received an error from the database. The database died and the data cannot be synchronized. The error is shown in the red section below:

Last_Errno: 1197

Last_Error: Could not execute Write_rows event on table mbpay.ATTACHMENT_copy; 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 fb-bin.001315, end_log_pos 2241781395

Solution: increase the parameter max_binlog_cache_size parameter appropriately according to the memory size of your machine

View the current size:

1) View the global parameter size:

Mysql > show GLOBAL variables like 'max_binlog_cache_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | max_binlog_cache_size | 18446744073709547520 | |

+-+ +

1 row in set (0.00 sec)

2) View the size of the parameters of the current session:

Mysql > show session variables like 'max_binlog_cache_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | max_binlog_cache_size | 18446744073709547520 | |

+-+ +

1 row in set (0.00 sec)

If it is only a small part of the current session, just

Mysql > set session max_binlog_cache_size=18446744073709547520

Query OK, 0 rows affected (0.00 sec)

Otherwise, you need to.

Mysql > set global binlog_cache_size=18446744073709547520

Query OK, 0 rows affected (0.00 sec)

The following is a specific analysis of the causes of the problem:

1) first learn the mechanism of mysql writing binlog:

We know that mysql's InnoDB storage engine supports transactions, and the implementation of transactions depends on log technology. for performance, log coding is in binary format. When recording binary logs, the database first writes binlog into binlog_cache, and then flushes from cache to the underlying disk (that is, binlog log files). Because the data in cache is not persisted, it faces security problems-- because the system is down. There may be residual data in the Cache that was not written to disk in time. Therefore, the Cache should be balanced and should be just right: not only reduce the disk I / O to meet the performance requirements, but also ensure that the Cache has no residue, persist in time, and meet the security requirements, that is to say, the size of the binlog_cache must be well controlled, too large may lead to the loss of too much binlog in case of abnormal power outage Of course, if it is too small, it may lead to the use of temporary files to fill the deficiency of cache, resulting in io performance problems. Binlog_cache_size and max_binlog_cache_size parameters control the binlog_cache size.

2) binlog_cache_size and max_binlog_cache_size parameters:

Parameter: binlog_cache_size: a transaction, when there is no uncommitted, the generated log is recorded in Cache; when the transaction commit (committed) needs to be committed, the log is persisted to disk. Binlog_cache_size is the amount of memory allocated for each session, the cache used to store binary logs during a transaction.

If the binlog_cache_size setting is too large, it will consume memory resources (Cache is memory in essence); if the binlog_cache_size setting is too small, if the user commits a "long transaction (long_transaction)", such as bulk importing data. Then the transaction will inevitably generate a lot of binlog, so the cache may not be enough (the default binlog_cache_size is 32K). When it is not enough, mysql will write part of the uncommitted to a temporary file (the temporary file cache must not be as efficient as memory cache), and will not write to the formal persistence log file until committed.

Parameter: max_binlog_cache_size: indicates the maximum amount of cache memory that can be used by all sessions added together by binlog. When we execute a multi-statement transaction, the binlog of all session uses more memory than the value of max_binlog_cache_size, an error will be reported: "Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage"

So since temporary files are used as cache when there is not enough cache, how can there be an error of more than 'max_binlog_cache_size'? The original use of temporary files as cache is for a session. When the size of binlog_cache used by this session exceeds the value of binlog_cache_size, temporary files will be used. When the memory used by all session binlog exceeds the value of max_binlog_cache_size, an error will be reported. Therefore, the reason for exceeding the value of max_binlog_cache_size: 1 the setting of the value of max_binlog_cache_size is too small, and 2, the amount of data in the current session has soared.

3) how to judge whether the current binlog_cache_size setting is reasonable

The size set by binlog_cache_size can be used to help with testing through the state variables binlog_cache_use and binlog_cache_disk_use, because:

Binlog_cache_use: the number of transactions that use binary log caching (that is, binlog_cache)

Binlog_cache_disk_use: the number of transactions that use binary log caching but exceed the binlog_cache_ size value and use temporary files to act as binlog cache saves.

Check the size of the previous state variable:

Mysql > show status like 'binlog_%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Binlog_cache_disk_use | 0 | |

| | Binlog_cache_use | 120402264 | |

+-+ +

2 rows in set (0.00 sec)

The operation Binlog_cache_use indicates how many times the binlog_cache memory mode has been used, and Binlog_cache_disk_use indicates how many times the binlog_cache temporary file mode has been used. Binlog_cache_disk_use is now equal to 0, indicating that memory cache is sufficient, and temporary files are never needed. If Binlog_cache_disk_use is not equal to zero, the Binlog_cache_use setting for the current session is not enough and needs to be increased.

4) conditions for switching underlying binlog files:

We know that binlog file uses the index to loop the file, looping to the next index under the following conditions

When the 1.mysql service restarts

two。 When the log reaches the maximum log length set by max_binlog_size

3. Log refreshed: mysql > flush logs

The following is the directory of my binlog, which is using mysql-bin.000182 (that is, the most numbered), and mysql-bin.index is the file used to control the binlog loop

[root@server02 mysql] # ll

-rw-rw---- 1 mysql mysql 9556 July 23 20:48 mysql-bin.000181

-rw-rw---- 1 mysql mysql 120 July 23 20:48 mysql-bin.000182

-rw-rw---- 1 mysql mysql 64 July 23 20:48 mysql-bin.index

5) focus on the process of master-slave synchronization

The first part of the mysql master-slave synchronization process is that master records the binary log. Before the update data of each transaction is completed, master records these changes in the binary log, and MySQL writes the transaction serial to the binary log, even if the statements in the transaction are cross-executed. After the event is written to the binary log, master notifies the storage engine to commit the transaction, and the salve server will detect whether the master binary log has changed within a certain time interval, and if so, start an I/OThread request master binary event, while the master node starts a master thread for each IThread O thread to send it binary events. After that, the iothread of slave receives the binlog sent by the main library, then writes it to the local binlog cahce, (it is worth noting that the Binlog Dump process of master reads the binlog in the binlog cache of the master library) and then flushes it to the relay log (reley log) file of the underlying disk. Finally, the sql process of slave applies the reley log change to achieve synchronization.

So why the master database did not report an error, but the slave database reported an error?

According to reason, the mysql5.6 master library can be written in parallel, but the slave library is serially copied (although multithreading is supported, but it is one thread per library). It is impossible for too many sessions to cause an error. The only reason is that the max_binlog_cache_size setting of the slave library is smaller than that of the master library. Verification found that this is true. This error report is due to the time when a large transaction binlog is written to the binlog cache of the slave library. An error was reported because the max_binlog_cache_size of the slave library was exceeded

The process of master-slave replication (extracted from the network):

1. After performing the change operation from the library slave, the IO thread above the Slave connects to the Master and requests the log contents from the specified location of the specified log file (or from the beginning of the log)

2. After Master receives the request from the IO thread of Slave, the Binlog Dump thread responsible for replication reads the log information after the specified location of the log according to the request information, and returns it to the IO thread on the Slave side. In addition to the information contained in the log, the returned information also includes the name of the Binary Log file on the Master side and the location in the Binary Log.

3. After receiving the information, the IO thread of Slave writes the received log contents to the end of the Relay Log file (mysql-relay-bin.xxxxxx) on the Slave side, and records the file name and location of the read bin-log on the Masterside into the master- info file, so that the next time it is read, it can clearly tell Master, "where do I need to start the log content in a certain bin-log, please send it to me"

4. After Slave's SQL thread detects a new addition to Relay Log, it immediately parses the contents of the Log file into executable Query statements that are actually executed on the Master side, and executes these Query on its own. In this way, the same Query is actually executed on both the Master side and the Slave side, so the data on both sides is exactly the same.

At this point, I believe you have a deeper understanding of the "mysql master-slave asynchronous error reporting Last_Errno 1197 solution". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.