Basic concepts of binary and redo log files in MySQL

Basic concepts of binary and redo log files in MySQL

Binary log
Binary logs record all operations that perform changes to the database. Binary logs have the following functions:

1. recovery 2. replication)

Start of binary log: configure the parameter log-bin [= name]. If no name is specified, the default binary log file name is the host name, And the suffix is the serial number of the second-level log, the path is the database directory.

Index files with the suffix index as binary logs are used to store binary logs generated in the past.

Parameters related to binary logs:

Max_binlog_size, binlog_cache_size, sync_binlog, binlog-do-db, binlog-ignore-db, log-slave-update, binlog_format

Max_binlog_size. This parameter specifies the maximum value of a single binary log file. If this value is exceeded, a new binary log file with a suffix of + 1 is generated and recorded. index file. The default value starting from Mysql5.0 is 1073741824, representing 1G.

Default Value of Mysql5.5

When the transaction storage engine InnoDB is used, all uncommitted transactions are recorded in a cache. when the transaction is committed, the binary logs in the buffer are directly written to the binary log file, the buffer size is determined by binlog_cache_size. The default size is 32 KB. In addition, binlog_cache_size is based on callback, that is, when a thread starts a transaction, mysql automatically allocates a cache with the binlog_cache_size value. Therefore, you must be careful when setting this value. You can view the status of binlog_cache_use and binlog_cache_disk_use through show global status, and check whether the current binlog_cache_size setting is appropriate.

The default Binlog_cache_size is 32 KB.

The sync_binlog parameter [N] indicates the number of times the cache is written to the disk. If N is set to 1, binary logs are written in synchronous mode, this parameter is very important and will be mentioned later. It is worth noting that when setting this parameter to 1, innodb_support_xa should also be set to 1, which ensures the synchronization of binary logs and InnoDB Storage engine data files.

The binlog-do-db and binlog-ignore-db parameters indicate the databases to be written or ignored. If the default value is null, logs of all databases are synchronized to binary logs.

Log-slave-update this parameter needs to be configured when building the master => slave architecture.

The Binlog_format parameter is also important. This parameter has been introduced since MySQL and can be set with STATEMENT \, ROW, and MIXED values;

(1) The STATEMENT format is the same as that of the mysql version. The binary log file records the SQL statements of the log logic loss.

(2) In the ROW format, the binary log records no longer simple SQL statements, but the ROW changes in the table. In this case, InnoDB Transaction isolation can be basically set to READ COMMITTED, to achieve better concurrency.

(3) In MIXED format, mysql uses the STATEMENT format by default to record binary log files. However, in some cases, the ROW format is used. Possible situations include:

1) if the storage engine of a table is NDB, DML operations on the table are recorded in ROW format.

2) Uncertain functions such as UUID (), USER (), CURRENT_USER (), FOUND_ROWS (), and ROW_COUNT () are used.

3) The insert delay statement is used.

4) use User-Defined Functions

5) A temporary table is used.

Redo log
By default, Mysql has two files: ib_logfile0 and ib_logfile1. These two files are redo log files or transaction logs.

Redo log: In case of instance or media failure, redo log files can be used.

Each InnoDB Storage engine has at least one redo log file group, and each file group has at least two redo log files, such as the default ib_logfile0 and ib_logfile1. The InnoDB Storage engine writes redo log file 1 first. When it reaches the end of the file, it switches to redo log file 2. When redo log file 2 is also full, switch to redo log file 1.

Parameters that affect the redo log:

Innodb_log_file_size, innodb_log_files_in_group, and innodb_log_group_home_dir affect the attributes of log files.

Differences between redo logs and binary logs:

1) binary logs record all mysql-related logs, including InnoDB and other storage engine logs, while the InnoDB Storage engine redo logs only record transaction logs related to it,

2) The record content is not acceptable. No matter which format you set the binary log file record, it records the specific operation content of a transaction, the physical information of changes to each page recorded by redo log files of the InnoDB Storage engine;

3) The write time is also different. binary log files are recorded before the transaction is committed. During the transaction, redo log entries are constantly written into the redo log file.

Redo log files written from the log buffer to the disk:

In the main thread, the redo log buffer is written to the redo log file of the disk every second, regardless of whether the transaction is committed. Another process that is triggered is controlled by the innodb_flush_log_at_trx_commit parameter, indicating how to process the redo log when submitting the request.

The innodb_flush_log_at_trx_commit parameter can be set to 0, 1, or 2. 0 indicates that when a transaction is committed, the redo log of the transaction is not written to the log file on the disk, but is waiting for the master thread to refresh every second, the difference between 1 and 2 is that: 1 is to synchronously write the redo log buffer to the disk at commit; 2 is to write the redo log to the disk asynchronously, that is, it cannot be completely guaranteed that the redo log file will be written to the commit.

Note: Because the redo log has a capacity variable, this value indicates that the last checkpoint cannot exceed this threshold value.