Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about how to understand redo log in mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Redo log (redo log)

It has been difficult to figure out the difference between binlog and innodb log files in mysql, and there has always been a doubt in mind that binlog log files can be used for

The log backup of the database has been restored, how come there is another redo log file. I believe there are many people who have this doubt. I hope I can help you by sorting out the documents of the whole process.

If you know a lot about Oracle, you can connect with innoDB log throughout the study.

The theory of machine redo log is almost the same in learning innoDB log.

1. Basic knowledge of innodb log

Innodb log as its name implies: logs generated by the innodb storage engine, also known as

Redo log files. There are two files under innodb_data_home_dir by default.

Ib_logfile0 and ib_logfile1. These two documents are called texts in the official manual of MySQL.

Log files for the InnoDB storage engine

View the log file location:

[root@rh65 ~] # cat / etc/my.cnf

# Uncomment the following if you are 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 25% 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

[root@rh65 ~] # cd / usr/local/mysql/data/

[root@rh65 data] # ls-l

Total 28732

-rw-rw---- 1 mysql mysql 18874368 Jun 10 16:52 ibdata1

-rw-rw---- 1 mysql mysql 5242880 Jun 10 16:52 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 Jun 10 16:52 ib_logfile1

Drwxr-xr-x 5 mysql mysql 4096 Jun 19 14:12 mysql

Drwx- 2 mysql mysql 4096 Jun 10 16:52

Performance_schema

Drwxr-xr-x 2 mysql mysql 4096 Jun 10 16:18 test

The role of innodb log: when instances and media of MySQL fail, the Innodb storage engine will use innodb log files for recovery to ensure the integrity of the database

The writing principle of innodb log: (please allow me to put down the schematic diagram of InnoDB again, and suggest that I see this picture and vomit)

Each InnDB storage engine has at least one redo log filegroup (group) and at least two redo log files under each filegroup. The default values are ib_logfile0 and ib_logfile1.

Each redo log in the log group is of the same size and recycled

The InnoDB storage engine first writes the redo log file. When the file is full, it will automatically switch to log file 2. When the redo log file 2 is also full, it will switch to the redo day.

Log file 1; to ensure security and performance, set up the image of each redo log file and assign it to a different disk

(it is found that the above features are almost the same as ORACLE's connection redo log files.)

2. Related parameters of innodb log

Run the script: show variables like 'innodb%log%'; to view the parameters of the redo log

Mysql > show variables like 'innodb%log%'

The parameters commonly set are:

Number of innodb_mirrored_log_groups mirror groups. Default is 1. No mirrors.

The path where the innodb_log_group_home_dir log group is located, which defaults to the home directory of data

Number of innodb_log_files_in_group log groups, default is 2

The size of the innodb_log_file_size log group, default is 5m

The size of the innodb_log_buffer_size log buffer pool, 30m on the figure

3. Relevant tuning of parameters

The size setting of redo log files is the same as that of ORACLE, and the problems are similar.

When the innodb log setting is too large, it may take a long time to recover after the system crashes

When the innodb log setting is too small, when a transaction generates a large number of logs, you need to switch the redo log file several times, resulting in 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 the

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

4. The difference between redo log and binary log

The scope of logging varies: the binary log records the logging of all MySQL storage engines (including InnoDB, MyISAM, etc.), while the InnoDB storage engine's redo log records only its own transaction log.

The format of the binary log file can be STATEMENT or ROW or MIXED, which records the specific operation of a transaction.

The InnoDB storage engine's redo log file records the physical information about the changes to each page.

4.3 the timing of writing is also different: binary log files are recorded before the transaction is committed, and during the transaction, redo log entries are constantly written to the redo log file.

After reading the above, do you have any further understanding of how to understand redo log in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.