The basic concept of binary and redo log files in Mysql learning tutorial _mysql

Binary log
the binary log records all operations that make changes to the database, and the binary has the following two main functions:

1, restore (recovery) 2, copy (replication)

Binary log Startup: Configuration parameter Log-bin[=name] If name is not specified, the default binary log file name is the hostname, the serial number of the two-level log, and the path is the directory where the database resides.

The file with index suffix is the index file of the binary log, which is used to store the binary logs of the past productions.

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, which specifies the maximum value of a single binary log file, which, if exceeded, produces a new binary log file, suffix +1, and records the. index file, the default value starting from Mysql5.0 is 1073741824, representing 1G.

Default value for Mysql5.5

When using the transaction's storage engine InnoDB, all uncommitted transactions are logged into a cache, waiting for the transaction to be committed, writing the buffered binary log directly to the binary log file, which is determined by the binlog_cache_size, the default size of 32KB, and Binlog_cache_size is based on a reply, that is, when a thread starts a transaction, MySQL automatically assigns a cache size of binlog_cache_size, so the worthwhile setting needs to be quite careful and can be done through show global Status to view the status of the Binlog_cache_use, Binlog_cache_disk_use, to determine whether the current Binlog_cache_size settings are appropriate.

The default size of the Binlog_cache_size is 32KB

Parameter Sync_binlog=[n] Indicates how many times per write cache is synchronized to disk, and if N is set to 1, it means writing the binary log in a synchronous write disk, which is important, and will be mentioned later. It is important to note that when this parameter is set to 1 o'clock, you should also set Innodb_support_xa as a workaround, which ensures that the binary logs and the InnoDB storage engine data files are synchronized.

Parameters binlog-do-db and binlog-ignore-db indicate which libraries to write to or ignore, and the default value is null, which means that all library logs are synchronized to the binary log.

Log-slave-update This parameter needs to be configured when building a Master=>slave=>slave architecture.

Binlog_format parameters are also particularly important. This parameter is introduced from the mysql5.1 version, which can be set to a value of statement\, ROW, and MIXED;

(1) The statement format is the same as the previous MySQL version, and the binary log file records the log's logical lost SQL statement.

(2) in the row format, binary logging is no longer a simple SQL statement, but a record of row changes in the table, at which point InnoDB transaction isolation can be basically set to read committed for better concurrency.

(3) In mixed format, MySQL defaults to the statement format for binary log file records, but in some cases the row format will be used, which may include:

1 The table's storage engine is NDB, at which point the DML operation of the table is recorded in row format

2 The use of the UUID (), User (), Current_User (), Found_rows (), Row_count (), and other uncertain functions

3 using the Insert DELAY statement

4 using user-defined functions

5) Use of temporary tables

Redo Log
MySQL defaults to two files: Ib_logfile0 and Ib_logfile1, the two files are redo log files, or transaction logs.

Purpose of Redo log: if instance or media fails, redo log files can be useful.

Each InnoDB storage engine has at least one redo log file group, with at least 2 redo log files under each filegroup, such as the default IB_LOGFILE0, Ib_logfile1. The InnoDB storage engine writes redo log file 1 First, and when the last file is reached, it switches to redo log file 2, which is then switched to redo log file 1 when redo log file 2 is also filled.

Parameters that affect the redo log:

Innodb_log_file_size, Innodb_log_files_in_group, innodb_log_group_home_dir effects focus on the properties of the log file.

The difference between redo logs and binary logs:

1 The binary log records all mysql-related logging, including other storage engine logs such as InnoDB, while the InnoDB storage engine's redo log records only the transaction log for itself.

2 The content of the record is not allowed, regardless of the format of the binary log file record, it records the specific operational content of a transaction, while the InnoDB storage engine redo log file records the physical condition of each page changes;

3 The time of writing is also different, the binary log file is recorded before the transaction commits, while in the process of the transaction, there are repeated log entries being written to the redo log file.

To write the condition of the Redo log file on disk from the log buffer:

Redo log buffering is written to the disk's redo log file per second in the main thread, regardless of whether the transaction is committed or not. Another trigger this process is controlled by the parameter innodb_flush_log_at_trx_commit, which indicates how the redo log is handled at the time of submission.

Parameter innodb_flush_log_at_trx_commit can be set to a value of 0, 1, 2, 0 means that when a transaction is committed, the redo log of the transaction is not written to the log file on the disk, but rather waits for the main thread to refresh every second. The difference between 1 and 2 is: 1 is to write the redo log buffer synchronously to disk at Commit, 2 is redo log is written to disk asynchronously, that is, there is no guarantee that the commit will be written to the redo log file, only this action.

It is worth noting that the redo log has a capacity variable that represents the last checkpoint that cannot exceed this threshold.