MySQL innoDB redo log file

MySQL innoDB redo log file

I have been confused about the difference between bin log and innodb log files in mysql. I have been wondering in my mind that the binlog log file can be used for Database log backup and recovery, why is there an additional redo log file. I believe many people may have this question. Now I want to sort out the entire process document and hope to help you.

If you are familiar with Oracle, you can set the theory of online redo logs on innoDB logs during the entire innoDB log learning process, which is almost the same;

I. Basic innodb log knowledge

• Innodb log, as its name implies, is the log generated by the innodb Storage engine. It can also be called a redo log file. By default, there are two files under innodb_data_home_dir: ib_logfile0 and ib_logfile1. In the MySQL official manual, these two files are called log files of the InnoDB Storage engine;
• Innodb log function: when MySQL instances and media fail, the Innodb Storage engine uses the innodb log file for restoration to ensure database integrity;
• Innodb log writing principle: (Please allow me to put down the InnoDB schematic, and we recommend that you see this figure)

Look at the section in the red box.

• Each InnDB storage engine has at least one redo log file group. Each file group has at least two redo log files. The default values are ib_logfile0 and ib_logfile1;
• The size of each redo log in the log group is consistent and is used cyclically;
• The InnoDB Storage engine writes the redo log file first. When the file is full, it automatically switches to log file 2. When redo log file 2 is full, it switches to redo log file 1;
• To ensure security and performance, set an image for each redo log file and assign it to a different disk;

(We found that the above features are the same as the ORACLE connection redo log files)

2. innodb log Parameters

Run the Script: show variables like 'innodb % log % '; view parameters related to the redo log

Mysql> show variables like 'innodb % log % ';

Common parameters include:

Innodb_mirrored_log_groups: Number of image groups. The default value is 1. No image is available;

The path of the innodb_log_group_home_dir log group. The default value is the home directory of data;

The number of innodb_log_files_in_group log groups. The default value is 2;

Innodb_log_file_size the size of the log group. The default value is 5 MB;

Innodb_log_buffer_size the size of the log buffer pool, which is 30 mb;

3. Parameter Optimization

3.1 The size of the redo log file is the same as that of ORACLE, and the problem is similar.

When the innodb log settings are too large, it may take a long time to recover the system from a crash;

When the innodb log settings are too small, when a transaction generates a large number of logs, you need to switch the redo log file multiple times to generate an alarm similar to the following;

130702 12:53:13 InnoDB: ERROR: the age of the last checkpoint is 2863217109,

InnoDB: which exceeds the log group capacity 566222311.

InnoDB: If you are using big BLOB or TEXT rows, you must set

InnoDB: combined size of log files at least 10 times bigger than

Iv. Differences between redo logs and binary logs

4.1 The record range is different: Binary logs record all the log records of MySQL storage engines (including InnoDB and MyISAM ),

The redo log of the InnoDB Storage engine only records its own transaction logs.

4.2 The Record Content is different: the format of the binary log file record can be STATEMENT or ROW or MIXED, and the record is the specific operation content of a transaction.

Physical information about changes to each page recorded by redo log files of the InnoDB Storage engine.

4.3 The write time is also different: binary log files are recorded before the transaction is committed, while some redo log entries are constantly written to the redo log file during the transaction.

Summary: theoretical guidance and practice. Although the theoretical knowledge is boring, learning is the same as building a house. Looking at beautiful tall buildings, there is always a solid foundation in the ground, high Buildings can also be built steadily.

MySQL InnoDB Storage engine lock mechanism Experiment

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration

This article permanently updates the link address: