MySQL binary log (binary log) Summary, mysqlbinary

MySQL binary log (binary log) Summary, mysqlbinary

 

Source: http://www.cnblogs.com/wy123/p/7182356.html
(The Source retained is not the right of original works. My work is far from reaching this level, just to link to the original article, because some possible errors will be corrected or supplemented later, without him)

 

Today, I accidentally discovered a MySQL "first season: Challenge xuanjicang slow SQL Performance Optimization competition" held in the yunqi community. the following error message is displayed when I run the test script on the test server to write data,
Multi-statement transaction required more than 'max _ binlog_cache_size 'bytes of storage, increase this mysqld variable and try agagin

The error message "max_binlog_cache_size space is insufficient. Because binary logs are enabled, a large number of transaction operations are not set by default. This problem is not encountered, at the beginning, a large transaction operation failed.
Then modify the binlog_cache_size to solve the problem.

 

Because the default innodb engine is used, binary logs are enabled,
Transaction operations write binary logs when the transaction is completed. before the transaction is committed, the write operations executed will be cached until the entire transaction is completed, the mysqld process writes the entire transaction to the binary log.
When a transaction starts, content space is allocated according to the value specified by the binlog_cache_size system variable. If the specified binlog_cache_size cache space is insufficient, the transaction operation is rolled back and a message indicating a failure is displayed.

 

 

By the way, we will summarize the binary log and its related parameter information.

What is binary log?
It is used to record write operations (add, delete, modify, but not query) in the MySQL database. It is equivalent to the transaction log file in full recovery mode in sqlserver.
What is the role of binary logs?
1. Used for replication. When the master-slave replication is configured, the master server will send the binary log generated by the master server to the slave end. The slave end will use the binary log information to redo it locally, implement master-slave Synchronization
2. User recovery. MySQL can use binary logs to recover data based on time points or transaction IDs on the basis of full backup and differential backup. Similar to the log redo of master-slave replication.
 

Binary log Parameters

1. Enable binary log
To enable binary log, you must specify the path of the log-bin parameter, for example, log_bin =/var/lib/mysql-bin.
After binary logs are started, a log_bin_index File Managing binary logs is automatically generated. The log_bin option is also displayed as on, that is, binary logs are enabled.

　

 

2. Binary Log File Format

The format of binary logs is controlled by the binlog_format parameter. Binary logs are in three modes: statement-based, row-based, and mixed)
The statement-based binary function has some hard damage (I personally think). For example, the current time is also obtained on the master server and slave server by using the now update operation in the same update statement, the results of master-slave replication are different.
The row-based binary log mode solves some statement-based defects. However, in some cases, a large number of logs are generated. For example, an update operation updates rows of data, for row-Based Binary logs, logs are generated.
Based on the hybrid mode, combined with the advantages of the above two methods.
You can set binlog_format = MIXED in the configuration file.

　

 

3. Timing of recording binary logs

Binary log records can be synchronized, that is, binary logs can be written after a transaction is committed, or asynchronously. the disk cache of the operating system determines when to write data to the disk.
It is controlled by the sync_binlog = n parameter. If sync_binlog = 1 is set, it indicates writing at the highest security level (but it cannot guarantee that no transaction log is lost). This is equivalent to a safe writing mode, however, it has a certain impact on performance.
I personally think that if it is a transactional engine, it is designed 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 May also potentially lose a transaction log, but I still have no idea why it will be lost because it is a transaction engine, what is the backing of an undo or redo log?

Think about it later, because there are redo and undo logs, the consistency of things can be ensured on the master server, it should be when the master-slave replication, one thing that may be lost may not be transmitted to the slave server.

　

 

4. Size of a single binary log file

The maximum size of a binary log is the maximum size of a single log file. Normally, the maximum size of the set file is not exceeded. If the maximum size of the set is exceeded, log scrolling occurs, that is, a binary log file is regenerated.
Max_binlog_size = 100 M
The unit of 104857600 shown here is byte, that is, 104857600/1024/1024 = 100 M.

　

 

5. binary log cleanup

After binary logs are rolled, new files are generated to store logs. log files are automatically deleted after they are overdue. Otherwise, a continuous stream of log files will be generated.
For example, you can set the expiration time to 2 and the value to expire_logs_days = 2. Binary logs that exceed two days will be automatically deleted.
You can run the show master logs command to view the current number of binary log files.

 

6. binary log file scrolling

1) under normal circumstances, when the record is full, it will automatically scroll, with the suffix + 1
2) After the mysql service is restarted, it will automatically scroll, regardless of whether the log file is fully written according to the specified maximum capacity.
3) manually scroll and execute the flush logs command. After executing the flush logs command, a binary log file is regenerated.

　

4) manually delete binary logs

You can run the purge binary logs to fileName command to delete files before the specified fileName.

　

You can run the purge binary logs before '2017-03-10 10:10:00 'command to delete a file earlier than the specified time.

　

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

 

7. BIND (or exclude) binary logs to the database

You can enable binary logs for some databases, or disable binary logs for some databases.
# Binlog_do_db: Used to set master-slave;
# Binlog-ignore-db: set which database does not record logs;
I set it in MySQL5.7.18 (my. cnf is configured), but it seems useless during query?

　

 

8. binary log cache and cache size Configuration

The size of binlog_cache_size is a problem mentioned at the beginning. When a transaction starts, content space is allocated according to the value specified by the binlog_cache_size system variable, if the specified binlog_cache_size cache space is insufficient, an error is reported and the transaction is rolled back.
The unit of the record shown here is also the byte. After dividing two 1024 bytes, It is the capacity in MB. The 20971520/1024/1024 here is equivalent to 20 MB.
If a large transaction operation is performed, for example, during the test, the cache must be relatively large; 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 concurrency is large, you need to consider setting max_binlog_cache_size slightly larger.
Max_binlog_cache_size is 4 GB by default, and the maximum value is 4 GB. Here we set 100 MB (104857600/1024. 0/1024. 0) for testing)

　

Max_binlog_cache_size sets the maximum memory size to 4 GB. If the server content is large, such as GB or larger, max_binlog_cache_size is set to the maximum by default, which is harmless because concurrent writing is required.
As for the binlog_cache_size of the Session level, you can adjust it based on your business needs. I personally think it is not a big problem to set it up. After all, except for normal OLTP operations, some operations such as data extraction from scheduled jobs or merge data may generate a large number of logs.
It is said that you can check binlog_cache_disk_use and binlog_cache_use to determine whether binlog_cache_size needs to be adjusted.
However, this parameter is not found in MySQL5.7.18.

　

 

9. Other binary log parameters

Max_binlog_stmt_cache_size is applicable to non-transaction statements. Non-transactional parameters do not care about it.
I remember a master once said that the innodb engine not only supports transactional services, but also has a lower and lower read performance gap compared with the myisam engine caused by non-transactional services, mySQL sets innodb as the default engine.
Abandon myisam and direct to innodb.
Binlog_checksum is used as the master-slave checksum for replication. This parameter has not been studied.
Please refer to the article http://www.cnblogs.com/kerrycode/p/6610874.html.

 

 

Summary:

MySQL binary logs not only act on functions (master-slave replication), but also on Security (Binary logs) and transactional operations when binary logs are enabled. Therefore, for the production environment, it can be considered as an essential configuration.
At the same time, its various parameters will affect some operations, so the parameters of binary logs should be paid special attention to ensure that the database is functional and availability in use.

 

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

Smear MySQL

And a variety of books and online materials

 

Action can change the mindset and fear.