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

In this article, the editor introduces in detail "what is the process of Binary Log in MySql", the content is detailed, the steps are clear, and the details are handled properly. I hope this article "what is the process of Binary Log in MySql" can help you solve your doubts.

Binary Log write proc

Let's first take a look at the official documentation description of the sync_binlog configuration.

Sync_binlog

Command line format-sync-binlog=# system variable sync_binlog range of influence Global dynamic YesSET_VAR prompt applies to No type Integer default value 1 minimum 0 maximum 2 ^ 32 = 4294967295

Controls how often the MySQL server synchronizes binary logs to disk.

Sync_binlog=0: disable the MySQL server to synchronize binary logs to disk. Instead, the MySQL server relies on the operating system to flush binary logs to disk from time to time, just as it does for any other file. This setting provides the best performance, but in the event of a power failure or operating system crash, the server may have committed transactions that have not yet been flushed.

Sync_binlog=1: enables synchronization of binary logs to disk before committing the transaction. This is the safest setting, but may have a negative impact on performance due to increased disk writes. In the case of a power failure or an operating system crash, the transactions lost in the binary log are only ready. This allows regular automatic recovery of rollback transactions to ensure that transactions are not lost from the binary log.

Sync_binlog=N, where is a value other than 0 or 1: after N binary log commit groups are collected, the binary log is synchronized to disk. In the case of a power failure or operating system crash, the server may have committed a transaction that has not yet been flushed to the binary log. This setting may have a negative impact on performance due to an increase in disk writes. Higher values improve performance, but increase the risk of data loss.

InnoDB to achieve the maximum possible persistence and consistency in replication settings used with transactions, use the following settings:

Sync_binlog=1.

Innodb_flush_log_at_trx_commit=1.

Warning

Many operating systems and some disk hardware spoof flush to disk operations. They might tell mysqld that a refresh has taken place, even if it hasn't happened yet. In this case, even using the recommended settings does not guarantee the durability of the transaction, and in the worst case, a power outage can corrupt InnoDB data. Using a battery-supported disk cache in the SCSI disk controller or the disk itself can speed up file refreshes and make operations more secure. You can also try to disable the caching of disk writes in the hardware cache.

Summary

The sync_binlog setting type is unsigned Integer.

It is generally not set to 0 fsync 0 depending on the system to operate irregularly, which is dangerous in the event of a power failure or system crash-the transaction is committed but the Binary Log is missing.

Set to 1 is more secure, get the maximum possible persistence and consistency, and can ensure the subsequent master-slave replication and recovery. But it is disadvantageous to the performance, when the IOPS required by the business is not high, it can be set.

Setting a value greater than 1 to improve performance is not a transaction commit on fsync, which is equivalent to batch flushing, which is a smart way, but there will be more Binary Log missing in the event of a power failure or system crash. It is safer if the disk itself uses a disk cache supported by the battery. Therefore, when the IOPS required by the business is relatively high, it can be set, but generally it will not be set too large, which can be set in the range of [100 ~ 1000].

In addition, we can probably feel it through the description of sync_binlog=0. In fact, although there is no immediate fsync when the transaction is committed, it is actually write into the page cache of the file system. In fact, mysql will also have a cache cache in the Binary Log generated in the transaction when the transaction is running.

Let's move on to look at the cache-related configuration of Binary Log when the transaction is running.

Binlog_cache_size

Command format-binlog-cache-size=# system variable binlog_cache_size range Golbal dynamic YesSET_VAR prompt applies No type Integer default 32768 minimum 4096 maximum (64-bit platform) 2 ^ 64 = 18446744073709547520 maximum (32-bit platform) 2 ^ 32 = 4294967295 block size 4096

The size of the memory buffer that holds binary log changes during the transaction. The value must be a multiple of 4096.

When binary logging is enabled on the server (the log_bin system variable is set to ON), if the server supports any transaction storage engine, a binary log cache is assigned to each client. If the data of the transaction exceeds the space in the memory buffer, the excess data is stored in a temporary file. When binary log encryption on the server is active, the memory buffer is not encrypted, but (since MySQL 8.0.17) any temporary files used to hold the binary log cache are encrypted. After each transaction is committed, reset the binary log cache by clearing the memory buffer and truncating temporary files (if used).

If you use large transactions frequently, you can increase this cache size to achieve better performance by reducing or eliminating the need to write temporary files. Binlog_cache_use (service state variable-number of transactions using Binary Log cache) and Binlog_cache_disk_use (service state variable-the number of transactions that use temporary binary log caching but exceed the binlog_cache_ size value and use temporary files to store transaction statements. The state variable can be used to resize this variable See Section 5.4.4, "binary Log".

Binlog_cache_size only sets the size of the transaction cache; the size of the statement cache is controlled by the binlog_stmt_cache_size system variable.

Summary

The binlog_cache_size setting type is unsigned Integer.

Used to indicate the size of the Binary Log used to cache during each transaction, which defaults to 32k and must be a multiple of 4096. If this value is exceeded, temporary file storage will be used.

Try not to use large transactions in the business, and consider whether it is reasonable if the transaction is too large. Generally, there is no need to modify the binlog_cache_size, 32k is enough.

When binlog_cache_size is not enough, temporary files will be used for storage, but the performance will be slow. We can set max_binlog_cache_size=binlog_cache_size so that temporary files are not used, as described below.

Max_binlog_cache_size

Command format-max-binlog-cache-size=# system variable max_binlog_cache_size range Golbal dynamic YesSET_VAR prompt applies to No type Integer default value 2 ^ 64 = 18446744073709547520 minimum 4096 maximum value 2 ^ 64 = 18446744073709547520 block size 4096

If a transaction requires more than so many bytes of memory, the server generates a storage error that requires more than 'max_binlog_cache_size' bytes for a multi-statement transaction. The minimum value is 4096. The maximum possible value is 16EiB (exbibytes). The maximum recommended value is 4GB; this is because MySQL cannot currently handle binary log locations greater than 4GB. The value must be a multiple of 4096.

Max_binlog_cache_size only sets the size of the transaction cache; the upper limit of the statement cache is controlled by the max_binlog_stmt_cache_size system variable.

The visibility max_binlog_cache_size of the session matches the visibility of the binlog_cache_size system variable; in other words, changing its value only affects the new session that starts after the value is changed.

Summary

Max_binlog_cache_size is a security value that is generally set according to the memory available to the server.

Overview

From the above configuration, we can see that the general writing process of Binary Log:

Transactions are placed in the Binary Log cache of each transaction at run time.

After the transaction is committed, the transaction is carried out according to the configuration. In the case of sync_binlog=1, the cache will be released each time fsync is performed. In the case of sync_binlog=0, it is written directly to the page cache of the system file, depending on the operating system flushing the binary log from time to time. If sync_binlog=N (N > 1), it is equivalent to batch flushing, of course, the binlog cache held by each transaction will be released.

So the general process is as follows:

After reading this, the article "what is the process of Binary Log in MySql" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself. If you want to know more about related articles, welcome to follow the industry information channel.

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.