十一、MySQL日志

十一、MySQL日志

    1、概述

        (1)、MySQL日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中比较重要的就是二进制日志：bin log（归档日志）、事务日志：redo log（重做日志）和undo log（回滚日志）。

        (2)、MyISAM引擎只支持bin log，而InnoDB支持bin log、redo log、undo log。

        (3)、MySQL InnoDB引擎使用redo log日志保证事务的持久性，使用undo log日志保证事务的原子性。

        (4)、MySQL数据库的数据备份、主备、主主、主从离不开bin log，需要依赖bin log来同步数据，保证数据的一致性。

    2、redo log

        (1)、概述

            ①、redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL有了崩溃恢复的能力。当MySQL实例挂了或者宕机了，重启的时候InnoDB存储引擎会使用rede log日志恢复数据，保证事务的持久性和完整性。

            ②、redo log是物理日志，记录的是“在某个数据页做了什么修改”，属于Innodb存储引擎。

        (2)、操作流程

            ①、MySQL中数据是以页为单数存储，当查询一条记录时，硬盘会把一整页的数据加载出来，加载出来的数据叫做数据页，会放到Buffer Pool中。后续的查询都是先从Buffer Pool中找，没有找到再去硬盘加载其他的数据页直到命中，这样子可以减少磁盘IO的次数，提高性能。

            ②、更新数据的时候也是一样，优先去Buffer Pool中找，如果存在需要更新的数据就直接更新。然后会把“在某个数据页做了什么修改”记录到重做日志缓存（redo log buffer）里，在刷盘的时候会写入redo log日志文件里。

                注：每条redo记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成。

        (3)、刷盘时机

            ①、概述

                a、刷盘的时机是根据策略来决定的，InnoDB存储引擎为redo log的刷盘策略提供了innodb_flush_log_at_trx_commit参数，它支持三种刷盘策略。

                b、innodb_flush_log_at_trx_commit参数默认为1，当事务提交的时候会调用fsync对redo log进行刷盘，将redo log buffer写入redo log文件中。另外，Innodb存储引擎有一个后台线程，每隔1秒，就会把会redo log buffer中的内容写入到文件系统缓存page cache，然后调用fsync刷盘。所以说一个没有提交事务的redo log记录，也会被刷盘。

            ②、刷盘策略

                innodb_flush_log_at_trx_commit参数默认为1。

                a、0：设置为0的时候，每次提交事务时都不调用fsync刷盘。

                b、1：设置为1的时候，每次提交事务时调用fsync刷盘。

                c、2：设置为2的时候，每次提交事务时都只把redo log buffer写入page cache。

            ③、innodb_flush_log_at_trx_commit = 0

                a、每次提交事务时不刷盘，如果宕机了或者MySQL挂了可能造成1秒内的数据丢失。

                b、Innodb存储引擎有一个后台线程，每隔1秒，就会把会redo log buffer中的内容写入到文件系统缓存page cache，然后调用fsync刷盘。

            ④、innodb_flush_log_at_trx_commit = 1

                a、只要事务提交成功，redo log记录就一定在磁盘里，不会有任务数据丢失。

                b、如果执行事务的时候MySQL挂了或者宕机了，这部分日志丢失了，但是因为事务没有提交，所以日志丢了也不会有损失。

            ⑤、innodb_flush_log_at_trx_commit = 2

                a、当事务提交成功时，redo log buffer日志会被写入page cache。

                b、Innodb存储引擎的一个后台线程，每隔1秒会刷盘写入redo log，由于后台线程是1秒执行一次所以宕机或者MySQL挂了可能造成1秒内的数据丢失。

        (4)、日志文件组

            ①、概述

                a、硬盘上存储的redo log日志文件不止一个，而是一个日志文件组的形式出现的，每个的redo log文件大小都是一样的。它采用的是环形数组形式，从头开始写，写到末尾回到头循环写。

            ②、属性设置

                在日志文件组中有两个重要的属性，分别是witre pos、checkpoint。

                a、wirte pos：是当前记录的位置，一边写一边后移。

                b、checkpoint：是当前要擦除的位置，也是后台推移。

            ③、刷盘过程

                a、每次刷盘redo log记录到日志文件组中，wirte log位置就会后移更新。

                b、如果witre pos追上checkpoint，表示日志文件组满了，这时候不能再写入新的redo log记录，MySQL得停下来，清空一些记录，把checkpoint推移一下。

                c、每次MySQL加载日志文件组恢复数据时，会清空加载过的redo log，并把checkpoint后移更新。

                d、write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。

            ④、为什么用redo log刷盘而不是直接用修改后的数据页刷盘？

                a、数据页大小是16KB，刷盘比较耗时，可能就修改了数据页的几byte数据，没有必要把整页的数据刷盘。

                b、如果是写redo log，一行记录就占了几十byte，只要包含了表空间号、数据页号、磁盘文件偏移量、修改值，再加上是顺序写，所以刷盘效率很高。

                c、而且数据页刷盘都是随机写，因为一个数据页对应的位置可能是在硬盘文件的随机位置，所以性能很差。

                d、所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

    3、bin log

        (1)、概述

            ①、bin log日志是逻辑日志，记录内容是语句的原始逻辑，属于MySQL Server层。所有的存储引擎只要发生了数据更新，都会产生binlog日志。

            ②、MySQL数据库的数据备份：主备、主主、主从都离不开binlog，需要依赖binlog来同步数据，保证数据一致性。

            ③、bin log会记录所有涉及更新数据的逻辑规则，并且按顺序写。

            

        (2)、记录格式

            bin log日志有三种格式，可以通过binlog_format参数设置。

            ①、statement

                a、设置statement记录的内容是SQL语句原文，比如执行一条update T set update_time = now() where id = 1，记录内容如下。

                b、同步数据时，会执行记录的SQL语句，但是有个问题update_time = now()这里会获取到当前系统问题，直接执行会导致与原库数据不一致。需要将binlog_format设置成row，解决该问题。

            ②、row

                a、设置row记录的不再是简单的SQL语句了，还包含了操作的具体数据。row格式记录的内容看不到详细信息，通过mysql bin log工具解析出来。

                b、因为记录的是具体数据，update_time = now()变成了具体的时间，条件后面的@1、@2都是该行数据第1个~2个字段的原始值（假设这张表只有2个字段）。

                c、设置成row带来的好处就是同步数据的一致性，通常情况都设置成row，这样可以为数据库的恢复与同步带来更好的可靠性。但是这种格式需要大量的容量来记录，比较占用空间，恢复与同步时会更消耗IO资源，影响执行速度。

                d、所以又有了一种折中方案，设置为mixed，记录的内容是前两者的混合。

            ③、mixed

                a、从MySQL 5.1.8版开始，MySQL又推出了mixed格式，这种格式实际上就是statement与row的结合。

                b、MySQL会判断这条SQL语句是否会引起数据不一致，一般的语句修改使用statement格式保存binlog；对于一些statement无法准确完成主从复制的操作，也就是会引起数据不一致的问题，则采用row格式保存binlog。

                c、mixed模式中，MySQL会根据执行的每一条具体的SQL语句来区别对待记录的日志格式，也就是在statement和row之间选择一种。

        (3)、写入机制

            ①、概述

                a、bin log的写入时机为事务执行过程中，先把日志写到bin log cache，事务提交的时候再把bin log cache，再写入page cache，然后再由fsync写入bin log文件中。

                b、因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一块内存作为bin log cache。可以通过binlog_cache_size参数控制单线程binlog_cache大小，如果存储内容超过了这个参数，就要暂存到磁盘。

            ②、bin log日志刷盘流程

                a、write：从bin log cache写入到文件系统的page cache，并没有把数据持久化硬盘，所以速度比较快。

                b、fsync：将page cache写入到bin log文件中，是将数据库持久化到硬盘的操作。

            ③、sync_binlog属性设置

                sync_binlog配置可以空置write和fsync的时机，可以配置成0、1、N(N>1)。

                a、设置成0时：表示每次提交事务都只会write，由系统自行判断什么时候执行fsync。

                b、设置成1时：表示每次提交事务都会执行fsync，就和redo log日志刷盘流程一样。

                c、设置成N时：表示每次提交事务都会write，但是积累N个事务后才fsync。

            ④、sync_binlog = 0

                sync_binlog设置成0，只把日志写入page cache虽然性能得到了提高，但是事务提交了fsync的时候宕机了，可能造成bin log日志的丢失。

            ⑤、sync_binlog = 2

                在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，会丢失最近N个事务的bin log日志。

    4、undo log

        (1)、概述

            ①、MySQL中恢复机制是通过undo log（回滚日志）实现，保证事务的原子性。

            ②、所有事务进行的修改都会先被记录到这个回滚日志，然后再执行其他相关的操作。

            ③、如果执行过程中遇到异常的话，我们直接利用回滚日志中的信息将数据回滚到修改之前的样子。

            ④、回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

        (2)、作用

            ①、回滚

                在进行数据更新操作的时候，不仅会记录redo log，还会记录undo log，如果因为某些原因导致事务回滚，那么这个时候MySQL就要执行回滚（rollback）操作，利用undo log将数据恢复到事务开始之前的状态。

                a、update

                    undo log会记录一条操作前的update语句。

update user set name = "李四" where id = 1; ---修改之前name=张三

update user set name = "张三" where id = 1; ---undo log记录之前的name=张三

                b、delete

                    undo log会记录一条对应的insert 语句（反向操作的语句），以保证在事务回滚。

delete from user where id = 1;

            ②、MVCC

                    MVCC实现依赖：undo log、隐藏字段、Read View。在底层实现中，InnoDB通过数据行的DB_TRX_ID和Read View来判断数据的可见性，如不可见，则通过数据行DB_ROLL_PTR找到undo log中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务里，用户只能看到该事务创建Read View之前已经提交的修改和该事务本身做的修改。 

                a、快照读

                    当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据版本是怎样的，从而让用户能够读取到当前事务操作之前的数据快照读。SQL读取的数据是快照版本可见版本，也就是历史版本，不用加锁，普通的SELECT就是快照读。

                b、当前读

                    SQL读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE都是当前读。

        (3)、存储机制

            ①、回滚段（rollback segment）

                a、undo log的存储由InnoDB存储引擎实现，数据保存在InnoDB的数据文件中。

                b、在InnoDB存储引擎中，undo log是采用分段(segment)的方式进行存储的。rollback segment称为回滚段，每个回滚段中有1024个undo log segment。

                c、在MySQL5.5之前，只支持1个rollback segment，也就是只能记录1024个undo操作。在MySQL5.5之后，可以支持128个rollback segment，总共可以记录128 * 1024个undo操作。

                d、undo log日志里面不仅存放着数据更新前的记录，还记录着RowID、事务ID、回滚指针。

            ②、回滚过程

                a、事务ID每次递增。

                b、若回滚指针第一次如果是insert语句，回滚指针为NULL。

                c、第二次update之后的undo log的回滚指针就会指向刚刚那一条undo log日志。

                d、依次类推，就会形成一条undo log的回滚链，方便找到该条记录的历史版本。

        (3)、工作原理

            ①、概述

                a、在更新数据之前，MySQL会提前生成undo log日志，当事务提交的时候，并不会立即删除undo log，因为后面可能需要进行回滚操作，要执行回滚（rollback）操作时，从缓存中读取数据。undo log日志的删除是通过通过后台purge线程进行回收处理的。

            ②、流程

                a、事务A执行update操作，此时事务还没提交。

                b、先将数据进行备份到对应的undo buffer，如果要回滚（rollback）事务，是不读磁盘的，先直接从undo buffer缓存读取。

                c、然后由undo buffer持久化到磁盘中的undo log文件中，此时undo log保存了未提交之前的操作日志。

                d、接着将操作的数据，也就是Teacher表的数据持久保存到InnoDB的数据文件IBD。

                e、如果此时事务B进行查询操作，直接从undo buffer缓存中进行读取。

            ③、特点

                a、更新数据前记录undo log，undo log必须先于数据持久化到磁盘。

                b、如果在数据持久化，写入磁盘到事务提交过程之间发生异常，因为undo log是完整的，可以用来回滚。

                c、如果在undo log持久化到磁盘之前发生异常，因为最终数据没有持久化到磁盘，所以磁盘上的数据还是保持在事务开始前的状态，不需要回滚。

                d、缺陷：每个事务提交前将数据和undo log写入磁盘，这样会导致大量的磁盘IO，因此性能较差。 如果能够将数据缓存一段时间，就能减少IO提高性能，但是这样就会失去事务的持久性。

                e、undo log日志属于逻辑日志，redo log是物理日志，所谓逻辑日志是undo log是记录一个操作过程，不会物理删除undo log，sql执行delete或者update操作都会记录一条undo日志。

    5、两阶段提交

        (1)、概述

            ①、redo log（重做日志）让InnoDB存储引擎有了崩溃恢复的能力，侧重于原库。

            ②、binlog（归档日志）保证了MySQL集群架构数据的一致性，侧重于从库或备份库。

            ③、InnoDB存储引擎使用两阶段提交方案解决redo log和bin log数据不一致的问题，将redo log日志的写入拆分成两个步骤prepare和commit。

        (2)、问题描述

            ①、假设有这么一条语句update T set c = 1 where id = 2（c原值为0），假如执行过程中写完redo log日志后，在写入binlog的时候发生了异常。

            ②、由于binlog日志没写完就异常，这个时候binlog日志里面没有对应的修改记录，之后使用binlog同步的数据的时候就会少这一次的更新，这一行数据c = 0，而原库使用redo log日志恢复，这一行数据c = 1 ，最终数据不一致。

        (3)、操作步骤

            针对以上问题，InnoDB存储引擎使用两阶段提交方案解决redo log和bin log数据不一致的问题。

            ①、两阶段流程

                a、先写入redo log日志，并且设置为prepare阶段。

                b、提交事务同时，写入bin log日志，并将redo log日志设置为commit阶段。

            ②、如果写入bin log时发生异常，因为MySQL根据redo log日志恢复数据时，发现redo log日志处于prepare阶段，并且没有对应bin log日志（根据事务id对应），所以就会回滚事务。

            ③、如果redo lgo设置commit阶段发生异常，并不会回滚事务，虽然redo log是处于prepare阶段，但是存在对应的事务binlog日志，所以MySQL认为是完整的，所以不会回滚事务。

 参考资料：MySQL三大日志(binlog,redolog,undolog)详解_晴天的空间的博客-CSDN博客_mysql 三个log