MySQL事务日志 [英] MySQL transaction journaling
问题描述
我正在一个项目上，我们需要在它的DBMS(MySQL)中使用事务日志".我们已经切换到使用InnoDB以便将事务用于另一个需求.我试图了解什么是交易日记.我已经搜索了整整一天，包括阅读MySQL文档.我不确定，也许我只是在搜索不正确的关键字.也许交易日记"是不合适的术语.
I am working on a project for which we are required to use "transaction journaling" in our DBMS (MySQL). We have already switched to using InnoDB in order to use transactions for another requirement. I am trying to understand what transaction journaling is. I have been searching for over a day now, including reading through MySQL documentation. Maybe I am just not searching for the right keywords, I am not sure. Or maybe "transaction journaling" is inappropriate terminology.
据我了解，数据库事务日志类似于日志文件系统，因为在将日志提交到文件系统之前先对其进行更改.从我读过的内容来看，听起来好像InnoDB引擎在将事务提交到磁盘之前将它们存储在某种日记中.这听起来准确吗?如果是这样，交易日志在哪里?是ib_logfile0和ib_logfile1吗?
From what I understand, database transaction journaling is similar to a journaling file system in that changes are made to a journal before they are committed to the file system. From what I've read, it sounds like the InnoDB engine stores transactions in some kind of journal before they are committed to disk. Does this sound accurate? If so, where is the transaction journal? Is it ib_logfile0 and ib_logfile1?
推荐答案
您在这里肯定是正确的轨道.
You are definitely on the right track here.
每当InnoDB执行必须提交的事务时，它都将作为两阶段提交来完成.事务首先写入这些日志中.然后，从那里提交它们.
Whenever InnoDB does a transaction that has to be committed, it is done as a two-phase commit. Transaction is written in these logs first. Then, they are committed from there.
这在发生MySQL崩溃或服务器崩溃时非常有帮助.
This helps a great deal in the event of the MySQL crash or server crash.
重新启动mysql时，将ib_logfile0和ib_logfile1中所有未提交的条目作为InnoDB崩溃恢复的一部分进行重放，以使InnoDB进入和谐状态(这是
When you restart mysql, all uncommitted entries in ib_logfile0 and ib_logfile1 are replayed as part of the crash recovery of InnoDB to bring InnoDB to a harmonious state (This is Consistent and Durable parts of ACID Compliance)
如果删除ib_logfile0和ib_logfile1并启动mysql，则包含那些文件的所有未提交的事务都将丢失.在崩溃恢复周期中，如果缺少日志文件，则会根据 innodb_log_file_size 设置.
If you delete ib_logfile0 and ib_logfile1 and start mysql, any uncommitted transaction that those files contained are lost. During the crash recovery cycle, if the log files are missing, they are regenerated based on the innodb_log_file_size setting.
请参见 MySQL文档，以了解InnoDB的详细说明 .
@ karatedog，InnoDB的MVCC部分发生在系统表空间(通常称为ibdata1)内.记录事务开始之前出现的所有数据，以允许访问所需行的其他人在强制执行任何更新之前可以查看数据.这允许所谓的REPEATABLE-READ.这属于ACID合规性I，即隔离.我在DBA StackExchange中写了有关事务隔离好坏，丑陋的各种情况的文章.
@karatedog the MVCC part of InnoDB happens within the system tablespace, better known as ibdata1. Whatever data appear to be before the start of a transaction is recorded to allow others who access the needed rows to have a view of the data before any updates were imposed. This is allows for what is called a REPEATABLE-READ. This falls under the I of ACID compliance, I meaning Isolation. I wrote posts about this in the DBA StackExchange in regard to various scenarios where transaction isolation is good, bad, or ugly.
- read before write transaction
- Will these two queries result in a deadlock if executed in sequence?
- Is raid 5 suitable for mysql installation?
对于MyISAM，崩溃恢复不是自动进行的.它相当容易崩溃.这就是存在SQL命令REPAIR TABLE的原因.这也是MySQL实用程序myisamchk具有-r选项对未在线的MyISAM表执行REPAIR TABLE的原因.
As for MyISAM, crash recovery is not automatic. It crashes rather easily. That's why the SQL command REPAIR TABLE exists. That's is also why the MySQL utility myisamchk has the -r option to perform REPAIR TABLE for MyISAM tables that are not online.
MariaDB和Aria 已在尝试一个崩溃安全的存储引擎来替代MyISAM.
MariaDB and Aria have been attempts to make a crash-safe storage engine as a replacement for MyISAM.
这篇关于MySQL事务日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
