MySQL's redo log

Redo log (redo log)

Preface: The difference between the bin log and the InnoDB log file in MySQL has been unclear, and there is always a question in the mind binlog log files can be used for
Log backups of the database have been restored, and a redo log file has been added. I believe there are a lot of people have this doubt, now the whole process of documentation, I hope to have some help.

If you are familiar with Oracle, then the entire learning InnoDB log, you can
Machine Redo log of the set of theories on learning InnoDB log above, almost the same;

1. Basic knowledge of InnoDB log
InnoDB log, as its name implies, is the log generated by the InnoDB storage engine, which can also be called
Redo log file, by default there are two files under Innodb_data_home_dir
Ib_logfile0 and Ib_logfile1. The two files are called in the official MySQL manual.
InnoDB The log file of the storage engine;

To view the log file location:
[Email protected] ~]# CAT/ETC/MY.CNF
# Uncomment the following if you is using InnoDB tables
#innodb_data_home_dir =/usr/local/mysql/data
#innodb_data_file_path = Ibdata1:10m:autoextend
#innodb_log_group_home_dir =/usr/local/mysql/data
# you can set: _buffer_pool_size up to 50-80%
# of RAM But beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set: _log_file_size to% of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[Email protected] ~]# cd/usr/local/mysql/data/
[Email protected] data]# ls-l
Total 28732
-RW-RW----1 MySQL mysql 18874368 June 16:52 ibdata1
-RW-RW----1 MySQL mysql 5242880 June 16:52 Ib_logfile0
-RW-RW----1 MySQL mysql 5242880 June 16:52 Ib_logfile1
Drwxr-xr-x 5 mysql mysql 4096 June 14:12 MySQL
drwx------2 MySQL mysql 4096 June 10 16:52
Performance_schema
Drwxr-xr-x 2 mysql mysql 4096 June 16:18 test

InnoDB log: When the MySQL instance and media fail, the InnoDB storage engine will use InnoDB log file to restore, ensure the integrity of the database;

InnoDB Log Writing principle: (Please allow me to put down the INNODB schematic diagram, and recommend this picture to see spit)

Each INNDB storage engine has at least 1 redo log file groups (group), with at least two redo log files under each filegroup, the default is Ib_logfile0, Ib_logfile1;
The size of each redo log in the log group is the same, and it is recycled;

InnoDB Storage Engine First write redo log file, when the file is full, will automatically switch to log file 2, when redo log file 2 is also full, will switch to Redo day
Log file 1; To ensure security and performance, set the image for each redo journal file and assign it to a different disk;

(The above features are found to be the same as ORACLE's connection redo log files)

2, InnoDB log of the relevant parameters

Run script: Show variables like ' innodb%log% '; View related parameters for redo logs
Mysql> Show variables like ' innodb%log% ';

The parameters for common settings are:
Innodb_mirrored_log_groups number of mirror groups, default is 1, no mirror;
Innodb_log_group_home_dir the path of the log group, which defaults to the home directory of data;
Innodb_log_files_in_group number of log groups, default is 2;
Innodb_log_file_size the size of the log group, the default is 5M;
Innodb_log_buffer_size the size of the log buffer pool, as shown in 30M

3, parameters of the relevant tuning
3.1 Redo log file size settings As with ORACLE, the problem is similar.

When the InnoDB log is set too large, it may take a long time to recover after the system crashes;
When the InnoDB log is set too small, when a transaction generates a large number of logs, the need to switch the redo log files more than once, will produce a similar following alarm;
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 is using big BLOB or TEXT rows, you must set the
innodb:combined size of log files at least times bigger than the

4. The difference between redo log and binary log
4.1 The range of records is different: the binary log records all of MySQL's storage engine's logging (including InnoDB, MyISAM, and so on), while the InnoDB storage engine's redo log records only its own transaction logs.
4.2 The contents of the record are different: The format of the binary log file can be statement or ROW or MIXED, and its record is about the specific operation of a transaction.
The InnoDB storage engine's redo log files record the physical condition of each page change.
4.3 Write time is also different: The binary log file is logged before the transaction commits, and during the process of the transaction, there are continually redo log entries written to the redo log file.

MySQL's redo log