Detailed explanation of issues related to MySQL binary log

Source of this article:
(Retaining the right of the source is not an original work. My humble work is far from it. It is just for linking to the original text, because some errors that may exist will be corrected later. Or supplement, nothing else)

Today I accidentally discovered a MySQL "Season 1: Challenge Xuanxian's Slow SQL Performance Optimization Competition" held by the Yunqi community, on the test server When executing its test script to write data, the error message is as follows,
Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage,increase this mysqld variable and try agagin

Prompt that the max_binlog_cache_size space is insufficient because the binary log is turned on. The default setting was not to have large batches of transactional operations before. This problem was not encountered. This time, a large transactional operation was encountered at the beginning and failed.
After modifying the size of binlog_cache_size, the problem is solved.

Since the default innodb engine is used, the binary log is turned on.
For transactional operations, the binary log must be written when the transaction is completed. Before the transaction is submitted, Write operations performed are cached until the entire transaction is completed, and the mysqld process writes the entire transaction to the binary log.
When something starts, the content space will be allocated according to the value specified by the binlog_cache_size system variable. If the specified binlog_cache_size cache space is not enough, the transactional operation executed will be rolled back and a failure message will be prompted.

By the way, let’s summarize the binary log and its related parameter information

What is binary log?
Used to record write operations (additions, deletions, modifications, but excluding queries) in the MySQL database, which is equivalent to the transaction log file in the complete recovery mode in sqlserver.
What is the role of binary log?
1. Used for replication. When master-slave replication is configured, the master server will send the binary log it generates to the slave. The slave will use the information in the binary log to redo it locally to achieve master-slave synchronization
　2. User recovery, MySQL can use binary logs to perform recovery operations based on time points or transaction IDs based on full backup and differential backup. The principle is similar to the log redo of master-slave replication.

Related parameter information of binary log (binary log)

1, enable binary log
To enable binary log, you need to specify a path to the log-bin parameter. For example: log_bin=/var/lib/mysql/mysql-bin
After starting the binary log, a log_bin_index file that manages the binary log will be automatically generated. The log_bin option is also shown as on, which means the binary log is turned on.

2, the format of the binary log file

The format of the binary log is controlled by the parameter binlog_format. The binary log has three modes, based on Statement (statement), row-based (row), and mixed mode (mixed) that combines the first two
The statement-based binary function has some flaws (in my opinion), such as the current use of the same update statement The now update operation of time also obtains the current time on the master server and the slave server. The results obtained by master-slave replication are different.
The row-based binary log mode solves some of the shortcomings of the statement-based one, but in some cases a large amount of logs will be generated. For example, an update operation updates 1 million rows of data. If it is a row-based binary log, the result will be 100W logs
Based on the hybrid mode, combining the advantages of the above two methods.
Can be set in the configuration file: binlog_format = MIXED

3, binary log recording timing

Binary log recording It can be synchronous, that is, the transaction is written to the binary log after it is submitted, or it can be asynchronous, where the operating system's disk cache determines when to write to the disk.
Controlled by the parameter sync_binlog= n. If sync_binlog = 1 is set, it indicates the highest security level of writing (but there is no guarantee that no transaction log will be lost). It is equivalent to a safe writing mode, but it has a certain impact on performance. Impact.
Personally, I think that if it is a transactional engine, it is to ensure the security of things. There is no reason not to set sync_binlog to 1.

It is said that setting sync_binlog to 1 will also potentially lose a transaction log, but I haven’t figured out why it will be lost, because since it is a transactional engine, there is also a layer of undo or redo logs as backing?

Think about it later, because of the existence of redo and undo logs, the consistency of things can be guaranteed on the master server. It should be that during master-slave replication, a thing that may be lost may not be delivered to the slave server. .

4. The size of a single file of the binary log

The size of the binary log is the maximum limit of a single log file. Normally The set maximum file size limit will not be exceeded. After exceeding the set maximum limit, log rolling will occur, that is, a binary log file will be regenerated.
max_binlog_size = 100M
The unit of 104857600 shown here is bytes, that is, 104857600/1024/1024 = 100M

5, Cleaning up the binary log

After the binary log is rolled, a new file will be generated to store the log. The log file will be automatically deleted after it expires, otherwise a continuous stream of log files will be generated
For example, you can set the expiration time to 2. The configurable value is: expire_logs_days = 2. Binary logs older than two days will be automatically deleted.
You can view the current number of binary log files through the command show master logs

6, scrolling of binary log files

1) Under normal circumstances, after the record is full, it will automatically scroll, and the suffix name will be +1
2) After the mysql service is restarted, it will automatically scroll, regardless of whether the log file is full according to the specified maximum capacity
3) Manual scrolling, Execute the flush logs command. After executing flush logs as follows, a binary log file is regenerated

　

　4) Manually delete the binary log

You can use the commandpurge binary logs to fileNameDelete the file before the specified fileName

　

You can delete it through the command purge binary logs before '2017-03-10 10:10:00' Files before the specified time

　

Delete the specified log purge binary logs before date_sub( now(), interval 7 day);
Xiaoxiang master is purge master logs before date_sub( now( ), interval 7 day), should it be an effect (binary and master keywords)?

7, Binding (or excluding) databases for binary logs

You can set some databases to enable binary logs, or some databases not to enable binary logs
# binlog_do_db: Used when setting master-slave;
# binlog-ignore-db: Set which database does not record logs;
It is set in MySQL5.7.18 (configured in my.cnf), but when querying It seems useless?

　

8, Binary log cache and cache size configuration

The size of binlog_cache_size, mentioned at the beginning Problem, when a transaction starts, the content space will be allocated according to the value specified by the binlog_cache_size system variable. If the specified binlog_cache_size cache space is not enough, an error will be reported and the transaction will be rolled back.
The unit of the record displayed here is also bytes, divided by After two 1024, it is the capacity in MB. The 20971520/1024/1024 here is equivalent to 20MB.
If there are large transactional operations, such as during testing, the cache must be set to a relatively large size, otherwise the statement cannot be successfully executed

　

The difference between max_binlog_cache_size and binlog_cache_size is that the former is an instance-level cache, and the latter is a Session-level cache. If the amount of concurrency is large, you need to consider setting max_binlog_cache_size slightly larger.
The default max_binlog_cache_size is 4GB, and the maximum value is also 4GB. The setting here for testing is 100MB (104857600/1024.0/1024.0)

　

The maximum memory size set by max_binlog_cache_size is 4GB. If the server content is large, such as 128GB or larger, it does not hurt to set max_binlog_cache_size to the maximum by default, because it is necessary to ensure successful concurrent writing.
As for the session-level binlog_cache_size, you can adjust it according to the business situation. Personally, I think it is not a big problem to set it a little larger. After all, in addition to normal OLTP operations, there are some data extraction or merge data such as scheduled jobs. Such operations may generate a large amount of logs.
It is said that you can judge whether binlog_cache_size needs to be adjusted by looking at binlog_cache_disk_use and binlog_cache_use.
　But this parameter was not found in MySQL5.7.18

　

9, other parameters of the binary log

max_binlog_stmt_cache_size is for non- Transaction statements, non-transactional parameters are not concerned about it for the time being
I remember once I saw a master said that the advantage of the innodb engine is not only in transactional support. Compared with the myisam engine caused by non-transactions, reading The performance gap is getting smaller and smaller, so MySQL sets innodb as the default engine.
Giving up myisam and going to innodb is the right way.
　binlog_checksum is used as master-slave checksum for replication. I haven't studied this parameter yet, so I won't care about it for now. For details, please refer to the article of Xiangxiangshen.

Summary:

The MySQL binary log is not only used for functionality (master-slave replication), but also for security (binary Log) and transactional operations when the binary log is turned on, so it can be considered an essential configuration for a production environment.

At the same time, its various parameters will affect certain operations, so the parameters of the binary log should be paid special attention to ensure that the functionality and availability of the database are guaranteed when using it.

Reference:

　《Smear MySQL》

　And various flip books, online materials

Taking action can change your thinking patterns and fear.

The above is the detailed content of Detailed explanation of issues related to MySQL binary log. For more information, please follow other related articles on the PHP Chinese website!