mysql如何查看事务⽇记_mysql关于redo事务⽇志ib_logfile的
理解
总结
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位置
sql/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中所有记录的事务对数据的变更已经被持久化到磁盘中，将清空并重⽤之。
sql/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 InnoD
B table data, which result from SQL statements or low-level API calls through NoSQL interfaces. Mod
ifications that did not finish updating the data files before an unexpected shutdown are replayed automatically.
mysql存储文档
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 c
orresponding 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。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。