mysql关于redo事务日志ib_logfile的理解

总结

创新互联建站主营东海网站建设的网络公司,主营网站建设方案,成都App定制开发,东海h5微信小程序开发搭建,东海网站营销推广欢迎东海等地区企业咨询

1、redo事务日志就是ib_logfile,两个ib_logfile开头的文件，它们就是log group中的redo log file，而且它们的大小完全一致且等于变量innodb_log_file_size定义的值

2、redo事务日志的作用就是用于crash recovery，crash recovery是数据库重启时自动的行为，无需为DBA执行任何额外工作

3、MySQL以循环方式写入重做日志文件，如果最后1个 ib_logfile 被写满，而第一个ib_logfile中所有记录的事务对数据的变更已经被持久化到磁盘中，将清空并重用之。

4、redo事务日志的概念类似oracle的online redo log，里面包含commit和uncommit的数据

5、写redo事务日志有几种方式，每隔1秒或每次事务提交，所以里面可以包含没有提交uncommit的数据

6、show engine innodb status可以看到redo log的信息

     Log sequence number：表明当前redo log的最新LSN。

     Log flushed up to：表明当前已经刷新到磁盘上redo log的LSN。

     Last checkpoint at ：redo log记录的更新已经刷新到磁盘上的检查点LSN，该LSN之前的redo log上记录的更新已全部刷新到磁盘上，可以被覆盖重复使用。

7、查看ib_logfile里的内容的方法

[root@mydb ~]# strings /var/lib/mysql/ib_logfile0

相关参数

innodb_log_file_size ：每个redo log文件大小

innodb_log_files_in_group ：redo log日志组成员个数

innodb_log_group_home_dir ：redo log存放目录

innodb_page_size ：InnoDB表空间的页面大小，默认16K

innodb_flush_log_at_timeout ：日志刷新频率，单位秒

Write and flush the logs every N seconds. innodb_flush_log_at_timeout allows the timeout period between flushes to be increased in order to reduce flushing and avoid impacting performance of binary log group commit. The default setting for innodb_flush_log_at_timeout is once per second.

每N秒写入并刷新日志。 innodb_flush_log_at_timeout允许增加刷新之间的超时时间，以减少刷新并避免影响二进制日志组提交的性能。 innodb_flush_log_at_timeout的默认设置是每秒一次。

innodb_flush_log_at_trx_commit ：控制commit动作是否刷新log buffer到磁盘

Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. 

The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit

With a setting of 0, logs are written and flushed to disk once per second

With a setting of 2, logs are written after each transaction commit and flushed to disk once per second

控制提交操作的严格ACID合规性与重新安排和批量完成与 提交 相关的I / O操作时可能实现的更高性能之间的平衡。

默认设置为1。在每次事务提交时，日志都会写入并刷新到磁盘。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。

设置为0时，每秒写入日志并将其刷新到磁盘一次。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。

设置为2时，在每次事务提交后写入日志，然后每秒再刷新一次磁盘。每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。

日志刷新频率由innodb_flush_log_at_timeout控制，允许您将日志刷新频率设置为N秒（其中N为1 ... 2700，默认值为1）。但是，任何mysqld进程崩溃都可以消除最多N秒的事务。

innodb_flush_log_at_timeout很多人误以为是控制innodb_flush_log_at_trx_commit值为0和2时的1秒频率，实际上并非如此。

以下四种方式将innodb日志缓冲区的日志刷新到磁盘

1、每秒一次执行刷新Innodb_log_buffer到重做日志文件。即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。

2、每个事务提交时会将重做日志刷新到重做日志文件。

3、当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件

4、当有checkpoint时，checkpoint在一定程度上代表了刷到磁盘时日志所处的LSN位置

https://dev.mysql.com/doc/refman/5.7/en/innodb-redo-log.html

The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions.

重做日志是在崩溃恢复期间用于纠正由未完成事务写入的数据的基于磁盘的数据结构。

By default, the redo log is physically represented on disk as a set of files, named ib_logfile0 and ib_logfile1. MySQL writes to the redo log files in a circular fashion. 

默认情况下，重做日志在磁盘上物理表示为一组文件，名为ib_logfile0和ib_logfile1。 MySQL以循环方式写入重做日志文件。

备注：innodb_log_files_in_group 确定ib_logfile文件个数，命名从 ib_logfile0 开始。如果最后1个 ib_logfile 被写满，而第一个ib_logfile中所有记录的事务对数据的变更已经被持久化到磁盘中，将清空并重用之。

https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_redo_log

redo

The data, in units of records, recorded in the redo log when DML statements make changes to InnoDB tables. It is used during crash recovery to correct data written by incomplete transactions. The ever-increasing LSN value represents the cumulative amount of redo data that has passed through the redo log.

当DML语句对InnoDB表进行更改时，以记录为单位的数据记录在重做日志中。 它在崩溃恢复期间用于更正由未完成的事务写入的数据。 不断增加的LSN值表示通过重做日志的重做数据的累积量。

redo log

A disk-based data structure used during crash recovery, to correct data written by incomplete transactions. During normal operation, it encodes requests to change InnoDB table data, which result from SQL statements or low-level API calls through NOSQL interfaces. Modifications that did not finish updating the data files before an unexpected shutdown are replayed automatically.

The redo log is physically represented as a set of files, typically named ib_logfile0 and ib_logfile1. The data in the redo log is encoded in terms of records affected; this data is collectively referred to as redo. The passage of data through the redo logs is represented by the ever-increasing LSN value. The original 4GB limit on maximum size for the redo log is raised to 512GB in MySQL 5.6.3.

在崩溃恢复期间使用的基于磁盘的数据结构，用于纠正由未完成的事务写入的数据。 在正常操作期间，它编码更改InnoDB表数据的请求，这些数据来自SQL语句或通过NoSQL接口的低级API调用。 在意外关闭之前未完成更新数据文件的修改会自动重播。

重做日志在物理上表示为一组文件，通常名为ib_logfile0和ib_logfile1。 重做日志中的数据根据受影响的记录进行编码; 这些数据统称为重做。 数据通过重做日志的传递由不断增加的LSN值表示。 在MySQL 5.6.3中，重做日志的最大大小的原始4GB限制被提升到512GB。

crash

MySQL uses the term “crash” to refer generally to any unexpected shutdown operation where the server cannot do its normal cleanup. For example, a crash could happen due to a hardware fault on the database server machine or storage device; a power failure; a potential data mismatch that causes the MySQL server to halt; a fast shutdown initiated by the DBA; or many other reasons. The robust, automatic crash recovery for InnoDB tables ensures that data is made consistent when the server is restarted, without any extra work for the DBA.

MySQL使用术语“崩溃”来指代服务器无法正常清理的任何意外关闭操作。 例如，由于数据库服务器计算机或存储设备上的硬件故障，可能会发生崩溃; 停电; 潜在的数据不匹配导致MySQL服务器停止; 由DBA发起的快速关闭; 或许多其他原因。 InnoDB表的强大自动崩溃恢复功能可确保在重新启动服务器时使数据保持一致，而无需为DBA执行任何额外工作。

crash recovery

The cleanup activities that occur when MySQL is started again after a crash. For InnoDB tables, changes from incomplete transactions are replayed using data from the redo log. Changes that were committed before the crash, but not yet written into the data files, are reconstructed from the doublewrite buffer. When the database is shut down normally, this type of activity is performed during shutdown by the purge operation.

During normal operation, committed data can be stored in the change buffer for a period of time before being written to the data files. There is always a tradeoff between keeping the data files up-to-date, which introduces performance overhead during normal operation, and buffering the data, which can make shutdown and crash recovery take longer.

崩溃后再次启动MySQL时发生的清理活动。 对于InnoDB表，使用重做日志中的数据重放未完成事务的更改。 在崩溃之前提交但尚未写入数据文件的更改将从doublewrite缓冲区重建。 当数据库正常关闭时，在清除操作期间执行此类活动。

在正常操作期间，提交的数据可以在写入数据文件之前存储在更改缓冲区中一段时间。 在保持数据文件最新之间总是需要权衡，这会在正常操作期间引入性能开销，并缓冲数据，这会使关闭和崩溃恢复花费更长时间。

备注：CrashSafe指MySQL服务器宕机重启后能够保证：所有已经提交的事务的数据仍然存在；所有没有提交的事务的数据自动回滚。Innodb通过Redo Log和Undo Log可以保证这两点。

log buffer

The memory area that holds data to be written to the log files that make up the redo log. It is controlled by the innodb_log_buffer_size configuration option.

保存要写入构成重做日志的日志文件的数据的内存区域。 它由innodb_log_buffer_size配置选项控制。

log file

One of the ib_logfileN files that make up the redo log. Data is written to these files from the log buffer memory area.

构成重做日志的ib_logfileN文件之一。 数据从日志缓冲区存储区写入这些文件。

log group

The set of files that make up the redo log, typically named ib_logfile0 and ib_logfile1. (For that reason, sometimes referred to collectively as ib_logfile.)

组成重做日志的文件集，通常名为ib_logfile0和ib_logfile1。（因此，有时统称为ib_logfile。）

LSN

Acronym for “log sequence number”. This arbitrary, ever-increasing value represents a point in time corresponding to operations recorded in the redo log. (This point in time is regardless of transaction boundaries; it can fall in the middle of one or more transactions.) It is used internally by InnoDB during crash recovery and for managing the buffer pool.

Prior to MySQL 5.6.3, the LSN was a 4-byte unsigned integer. The LSN became an 8-byte unsigned integer in MySQL 5.6.3 when the redo log file size limit increased from 4GB to 512GB, as additional bytes were required to store extra size information. Applications built on MySQL 5.6.3 or later that use LSN values should use 64-bit rather than 32-bit variables to store and compare LSN values.

In the MySQL Enterprise Backup product, you can specify an LSN to represent the point in time from which to take an incremental backup. The relevant LSN is displayed by the output of the mysqlbackup command. Once you have the LSN corresponding to the time of a full backup, you can specify that value to take a subsequent incremental backup, whose output contains another LSN for the next incremental backup.

“日志序列号”的缩写。这个任意的，不断增加的值表示与重做日志中记录的操作相对应的时间点。 （此时间点与事务边界无关;它可以落在一个或多个事务的中间。）它在崩溃恢复期间由InnoDB内部使用，用于管理缓冲池。

在MySQL 5.6.3之前，LSN是一个4字节的无符号整数。当重做日志文件大小限制从4GB增加到512GB时，LSN成为MySQL 5.6.3中的8字节无符号整数，因为需要额外的字节来存储额外的大小信息。在MySQL 5.6.3或更高版本上构建的使用LSN值的应用程序应使用64位而不是32位变量来存储和比较LSN值。

在MySQL Enterprise Backup产品中，您可以指定LSN来表示进行增量备份的时间点。相关的LSN由mysqlbackup命令的输出显示。一旦您拥有与完全备份时间相对应的LSN，您就可以指定该值以进行后续增量备份，其输出包含用于下一次增量备份的另一个LSN。