MySQL理论汇总大纲——日志和主备相关

本文链接：https://blog.csdn.net/snippers/article/details/111316254

一、日志系统

redo log（重做日志）：
当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，同时InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面。

WAL(Write-Ahead Logging)：关键点即先写日志，再写磁盘。将磁盘的随机写变成了顺序写。

  WAL 机制主要得益于两个方面：
                   1、redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快
                   2、组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

crash safe：
1、有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。
2、innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。

redo log保证了事务ACID的特性

redo log 的写入机制：
1、InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
2、redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。
3、并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。

binlog（归档日志）
由Server层实现，记录的是语句的原始逻辑，比如给ID=1的行的状态设置成1，追加写，上个文件写完回切换成下一个文件，类似滚动日志。
sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。

           binlog的写入逻辑
               1、事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到磁盘上的binlog 文件中。
               2、调用 fsync 持久化。

binlog 是不能“被打断的”。一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。

       binlog和redolog的不同点有哪些：
           1、redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
           2、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
           3、redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

两阶段提交：
为了解决redo log和binlog不一致问题的，这里的不一致是指redo log写成功binlog写失败了，由于恢复是根据binlog恢复的，这样恢复的时候就会少一条更新操作，导致和线上库的数据不一致。具体而言，两阶段是指prepare阶段和commit阶段，写完redo log进入prepare阶段，写完binlog进入commit阶段。

脏页：内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。

       InnoDB 刷脏页的控制策略：
           1、设置 innodb_io_capacity 参数，建议设置成磁盘的 IOPS。
           2、关注脏页比例，不要让它经常接近 75%。脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的。
           3、如果使用的是 SSD，把 innodb_flush_neighbors 的值设置成 0，因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。

MySQL 的“双 1”配置：
sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

二、主备相关

将备库设置成只读（readonly）模式， readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。

一个事务日志同步的完整过程：
           1、在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
           2、在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
           3、主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
           4、备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
           5、sql_thread 读取中转日志，解析出日志里的命令，并执行。

       binlog 的三种格式对比：
           statement：当 binlog_format=statement 时，binlog 里面记录的就是 SQL 语句的原文
           row：当 binlog_format=statement 时，便于delete、insert 和 update后的数据恢复
           mixed：statement与row格式的混合

实际生产上使用比较多的是双 M 结构，即：节点 A 和 B 之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。

如何解决循环复制问题：
               1、规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
               2、一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
               3、每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

       主备延迟的来源：
           1、有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。
               配置”双1“，redo log和binlog都只write fs page cache
           2、备库的压力大
               一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。（多从库适合做全量备份）
               通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。
           3、大事务。如：大表 DDL
               尽量减少大事务，比如delete操作，使用limit分批删除，可以防止大事务也可以减少锁的范围。
           4、备库的并行复制能力

主备延迟对应策略：

可靠性优先策略

               1、判断备库 B 现在的 seconds_behind_master如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步
               2、把主库 A 改成只读状态，即把 readonly 设置为 true
               3、判断备库 B 的 seconds_behind_master的值，直到这个值变成 0 为止
               4、把备库 B 改成可读写状态，也就是把 readonly 设置为 false
               5、把业务请求切到备库 B