MySQL binary logs (binary log) summary

The source of this article: http://www.cnblogs.com/wy123/p/7182356.html
(It is not the original works right to retain the source, I my book still far to reach, just to link to the original text, because the following may exist some errors to amend or supplement, without him)

Today, I stumbled on a cloud habitat. MySQL "First season: Challenge ashamed's slow SQL performance optimization Race", when the test server executes its test script to write data when 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

Tip max_binlog_cache_size space is insufficient, because the binary log is turned on, before the default setting does not have a large number of transactional operations, the problem is not encountered, this time at the outset of a large transactional operation failed.
After you modify the size of the binlog_cache_size, the problem is resolved.

Since the default InnoDB engine is used, the binary log is turned on,
For transactional operations, a binary log is written when the thing is done, and before the thing is committed, the write operation is cached until the whole thing is done, and the mysqld process writes the whole thing to the binary log.
When things start, the content space is allocated according to the value specified by the Binlog_cache_size system variable, and if the specified binlog_cache_size cache space is insufficient, the transactional operations performed are rolled back and the prompt fails.

By the way, summarize binary logs (binary log) and their associated parameter information

What is a binary log?
Used to record write operations (additions and deletions, but not queries) in the MySQL database, equivalent to the transaction log files under the full recovery model in SQL Server.
What is the role of binary logging?
1, for replication, configuration of the master-slave replication, the primary server will be generated by the binary log sent to the slave side, the slave will take advantage of this binary log information on-premises redo, to achieve master-slave synchronization
2, user recovery, MySQL can be based on the full standby and differential backup, using the binary log to perform a point-in-time or object-ID recovery operations. The principle is identical to the master-slave copy of the log redo.

Parameter information for binary logs (binary log)

1, open binary log
To open the binary log, you need to develop a path to the Log-bin parameter, namely: Log_bin=/var/lib/mysql/mysql-bin
A log_bin_index file that manages binary logs is automatically generated after the binary log is started. The Log_bin is also displayed as 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, is based on the statement (statement), row-based, and mixed-mode (mixed) with the first two.
The statement-based binary functionality has some mishap (personally), such as the now update operation in the same UPDATE statement that takes advantage of the current time, and the result of master-slave replication is not the same on both the primary and slave servers.
Row-based binary solves some of the flaws in statement statements, but in some cases a large number of logs are generated, such as an update operation that updates 100W rows of data, and if it is a row-based binary log, the result is a 100W log
Based on mixed-mode, combining the advantages of both methods.
Can be set in the configuration file: Binlog_format = MIXED

　

3, the recording time of binary log

Binary logging enables synchronization, that is, after a thing is committed, to write a binary log, or it can be asynchronous, when the disk cache of the operating system feels like it is written to disk.
Controlled by the parameter sync_binlog= N, set Sync_binlog = 1, represents the highest security level of write (but also does not guarantee the loss of any thing log), equivalent to a secure write mode, but has a certain impact on performance.
Personally, if it is a transactional engine itself to ensure that things are safe, there is no reason not to set the Sync_binlog to 1.

It is said that setting the Sync_binlog to 1 will also potentially lose the possibility of a transaction log, but it is not yet clear why it is lost, since it is a transactional engine and there is a layer of undo or redo logs backing it up?

　

4, single file size for binary log

The size of the binary log is the maximum limit of a single log file, and under normal circumstances it will not exceed the limit of the maximum file set, and after the maximum limit of the set, log scrolling will occur, which is to regenerate a binary log file.
Max_binlog_size = 100M
The 104857600 units shown here are bytes, or 104857600/1024/1024 = 100M

　

5, binary log cleanup

When the binary log scrolls, a new file is generated to store the log, which is automatically deleted after the log file expires, or a continuous log file is generated
For example, you can set the expiration time to 2, the matching value is: Expire_logs_days = 2, more than two days of binary logs will be automatically deleted.
You can view the current number of binary log files by command show master logs

6. Scrolling of binary log files

1) Under normal circumstances, after the record is full, auto-scroll, suffix name +1
2) After restarting the MySQL service, scroll automatically, regardless of when the log is full
3) Manually scroll, execute the flush logs command, after executing flush logs, regenerate a binary log file

　

4) Manually delete the binary log

You can delete a file before the specified filename by command purge binary logs to filename

　

You can delete files before the specified time by ordering purge binary logs before ' 2017-03-10 10:10:00 '

　

Delete the specified log purge binary logs before date_sub (now (), Interval 7 day);
Xiaoxiang great God is Purge Master logs before date_sub (now (), Interval 7 day), should be an effect (binary and master keywords)?

7, binary logs are bound (or excluded) to the database

You can set some databases to open binary logs, or some databases do not open binary logs
# binlog_do_db: used when setting master-slave;
# binlog-ignore-db: Set which database does not log;
MYSQL5.7.18 is set (configured in MY.CNF), but the query seems useless?

　

8, cache and cache size configuration for binary logs

The size of the binlog_cache_size, the beginning of the question, when things start, the content space is allocated according to the value specified by the binlog_cache_size system variable, if the specified binlog_cache_ Size does not have enough cache space to error and rollback things
The unit of record shown here is also a byte, divided by two 1024 is the capacity in megabytes, where the 20971520/1024/1024 is equivalent to 20MB.
If you have larger transactional operations, such as testing, you must set this cache relatively large, otherwise the statement will not execute successfully

　

Max_binlog_cache_size language binlog_cache_size is the difference between the former is the instance level of the cache, the latter is the session level of the cache, if the concurrency is very large, you need to consider the max_binlog_cache_ Size is set slightly larger.
Max_binlog_cache_size default is 4GB, the maximum value is 4GB, here in order to test the setting is 100MB (104857600/1024.0/1024.0)

　

Max_binlog_cache_size set the maximum memory size of 4GB, if the server content is large, such as 128GB or larger, max_binlog_cache_size default is the maximum also harmless, because to ensure that the concurrent successful write.
As for the session level of the binlog_cache_size size, can be adjusted according to the business situation, the individual feel that the setting is slightly larger is not a problem, after all, there are some timing jobs such as data extraction or merge and other operations may produce a large number of logs.
It is said that Binlog_cache_disk_use and Binlog_cache_use can be viewed to determine whether binlog_cache_size needs to be adjusted.
But this parameter was not found in MySQL5.7.18.

　

9, binary log other parameters

Max_binlog_stmt_cache_size non-transactional arguments for non-transactional statements do not care about it for a while
I remember seeing some master say, InnoDB engine advantage is not only in the transactional support, compared with the non-thing caused by the MyISAM engine, the reading performance gap is getting smaller, MySQL therefore InnoDB set as the default engine.
Abandon MyISAM, go to InnoDB is the right path.
Binlog_checksum is used as a master-slave test for replication. This parameter has not been studied for the time being, whether
For more information, consider the article http://www.cnblogs.com/kerrycode/p/6610874.html of the Great God.

Summarize:

MySQL binary logs work not only on functionality (Master-slave replication), but also on security (binary logging) and transactional operations with binary logging turned on, so it can be considered an essential configuration for production environments.
At the same time, its various parameters will affect some operations, so the parameters of the binary log should be given special attention to ensure that the database is used in the functional and usability are guaranteed.

Reference: http://www.cnblogs.com/kerrycode/p/6610874.html

"Smear MySQL"

and a variety of books, online information

Action, can change the mode of thinking and fear of psychology.

MySQL binary logs (binary log) summary