binlog_cache_size: 日志缓存区大小。在事务未提交前，所有的 binlog 日志都会记录到内存缓存，等到事务提交的时候再写入日志文件，该参数即是设置该缓存区的大小。此外，日志缓存是基于会话的，也就是说 每个线程都有一块 binlog_cache_size 大小的内存缓存，因此，该值不宜设置太大；另一方面，如果某事务占用缓存超过设置值，就需要将日志写入临时文件，因此，该值也不能设置太小。那多大比较合适？可以通过 SHOW GLOBAL STATUS 查看 binlog_cache_disk_use （使用临时文件写日志次数）来判定，如果该值很大，说明缓存区过小，需要经常写临时文件，此时需要适当调大该参数。

sync_binlog: 同步磁盘策略。我们平时写文件调用的 write() 函数其实并没有真正的将内容写到磁盘，而是写到文件系统的 page cache 里，真正将内容同步到磁盘的是 fsync() 函数。sync_binlog 就是用来设置 fsync() 函数的执行时机的：

sync_binlog = 0 : 不调用 fsync() 函数，由操作系统决定何时调用；
sync_binlog = 1 : 每个事务调用 write() 后立马执行 fsync() 函数；
sync_binlog = N (N>1) : 累计 N 个事务调用 write() 后，执行 fsync() 函数；

binlog_format: 日志格式，有三种格式选择：

STATEMENT: 基于 SQL 语句记录（可以理解为记录的就是更新数据的 sql 语句）。这种格式的日志存在一个问题，动态函数在重放时可能产生不一致的结果，比如记录日志时调用一个动态函数生成 uuid，那使用该 binlog 日志重放时可能会得到一个不同的 uuid 值。
ROW: 记录数据行更改后的值，这样就避免了日志重放时因为动态函数带来的不一致的问题，但是这种格式的日志文件 相较 STATEMENT 要大很多，因为它记录的是每一行数据的变化值，比如执行一个 update 语句更新了 1w 条数据，采用 STATEMENT 格式就是记录这个 update 语句即可，而 ROW 则要记录这 1w 条数据更新后的值。
MIXED: 包含以上两个格式，默认采用 STATEMENT 记录，而对于使用 STATEMENT 记录可能产生问题的 SQL 语句则使用 ROW 记录。

无论采用哪种格式，它记录的都是 关于一个事务的具体操作内容，因此 binlog 日志也被称为 逻辑日志。

主从复制

主从复制是 mysql 提供的一种 高可用高性能 的解决方案。具体来说，在主从复制架构下，如果主库发生意外，可以切换到从库继续提供服务（高可用）；此外，主从库可以实现读写分离，主库可接收读写请求，从库只提供读请求能力，可以大大提高数据库整体的查询能力（高性能）。

主从复制原理很简单，包含以下三个步骤：

1、主库记录二进制日志文件；

2、从库将主库的日志文件复制到自身的中继日志(relay log)；

3、从库对中继日志进行重放。

复制具体过程涉及三个线程操作：

1、主库的 log dump 线程 负责将二进制文件发送到从库；

2、从库的 I/O 线程 负责读取二进制文件并保存到从库的 relay log 中；

3、从库的 SQL 线程 负责读取 relay log 在本地进行重放。

redo log 日志

Innodb 基于磁盘存储，同时按页的方式来管理记录，一个页的大小默认为 16KB。如果每次查询或修改都要按页和磁盘进行 IO 交互会严重影响数据库的性能，因此引入了 内存缓存；

有了内存缓存，在对数据进行查询时，先查缓存，如果数据存在直接返回，如果不存在则去磁盘读取并将读取到的页放到缓存池中，然后再返回数据；对数据的修改也是 先修改缓存池的页，而后异步的将页刷新回磁盘。但是异步刷新磁盘也带来一个新问题：在刷新磁盘前如果意外宕机，重启后内存数据已经没有了，就会导致数据丢失。

为了避免数据丢失，Innodb 采用了 Write Ahead Log(WAL) 策略，即 当事务提交时，先写日志，再修改页，当发生意外宕机时，可以通过日志来恢复数据，这个日志就叫做 redo log，它保证了事务的 持久性。

区别于 binlog 日志，redo log 是 Innodb 引擎独有的日志模块，它只记录有关 Innodb 引擎的事务日志，记录内容为 对数据页的物理操作（比如偏移量 500，写 ‘jianbijian’）。

redo log 参数配置

innodb_log_file_size
innodb_log_files_in_group
innodb_mirrored_log_groups
innodb_flush_log_at_trx_commit

每个 Innodb 引擎都 至少有 1 个重做日志文件组，而 每个重做日志组下又至少有 2 个重做日志文件；参数 innodb_mirrored_log_groups 和 innodb_log_files_in_group 分别用来设定重做文件组的个数和每个组内文件的个数，日志组中每个日志文件大小一致，并以循环写入的方式运行。

innodb_log_file_size: 设定每个重做日志文件的大小，这个参数很重要。首先它不能设置的太小，因为重做日志文件是 循环写入 的，如果设置的太小会导致频繁的 async/sync checkpoint，当然也不能设置的超大，这样做数据恢复的时候耗时就很长。

async/sync checkpoint：重做日志不可用时，需要强制将一些页刷新回磁盘。

重做日志是对日志组内的几个日志文件 循环写入 的（比如有两个重做日志文件 logfile1 和 logfile2，先写 logfile1, logfile1 写满后开始写 logfile2, logfile2 写满后又重新开始写 logfile1），将日志组内的 N 个文件组合起来想象成一个圆，此时总的文件大小 total_redo_log_file_size = N * innodb_log_file_size ，有两个指针，一个表示 redo log 新增时的写入指针 redo_lsn，一个表示异步刷新回磁盘最新页后的记录指针 checkpoint_lsn，再定义一个变量 checkpoint_age = redo_lsn - checkpoint_lsn，正常情况下都有 0 <= checkpoint_age < total_redo_log_file_size，而如果事务比较频繁，innodb_log_file_size 又比较小的，就可能出现 checkpoint_age 的大小达到 total_redo_log_file_size，即 redo_lsn 绕了一圈追上了 checkpoint_lsn，这时不能让 redo_lsn 直接覆写 checkpoint_lsn 位置的值，因为 checkpoint_lsn 位置的记录还没同步到磁盘，即 重做日志不可用。

innodb 其实不会等到 redo_lsn 追上 checkpoint_lsn，而是定义了两个水位线 async_water_mark 和 sync_water_mark：

async_water_mark = 0.75 * total_redo_log_file_size

sync_water_mark= 0.9 * total_redo_log_file_size

遵循以下规则刷新脏页：

1、checkpoint_age < async_water_mark 时，不需要刷新页回磁盘，直接追加 redo log 日志即可；

2、async_water_mark < checkpoint_age < sync_water_mark 时触发 Async Flush 将脏页刷新回磁盘，直到满足 checkpoint_age < async_water_mark；

3、sync_water_mark < checkpoint_age 时触发 Sync Flush 将脏页刷新回磁盘，直到满足 checkpoint_age < async_water_mark

需要注意的是，在 Innodb 1.2.x 之前，Async Flush 会阻塞发现问题的用户线程， Sync Flush 则会阻塞所有线程；但在 Mysql 5.6 之后，刷新脏页由单独的 Page cleaner thread 完成，都不再阻塞用户线程。

innodb_flush_log_at_trx_commit：提交事务时的重做日志同步磁盘策略。

redo log 也有对应的缓存 redo log buffer，写日志时先写 redo log buffer，然后再按一定条件 顺序地 写入日志文件；再考虑到文件系统的缓存，一条 redo log 日志从生成到同步磁盘的路径为:

redo log buffer --> page cache --> redo log file；

innodb_flush_log_at_trx_commit 有 0,1,2 三个有效值：

0：提交事务时，不同步重做日志到磁盘，即日志仅记录在 redo log buffer 中；
1：提交事务时，将缓存同步到磁盘；
2：提交事务时，将缓存写到 page cache，但不会同步到磁盘，即没有 fsync() 的调用。

可以看到，如果想要确保事务的持久性，必须将该参数设置为 1；

设置为 0 的时候，redo log buffer 中的内容要等到后台主线程每秒的任务才会刷新到磁盘，如果中途 mysql 实例宕机，可能丢失 1 秒的日志；

设置为 2 的时候也是等待主线程每秒的任务刷新磁盘，但是在事务提交时，已经将日志缓存写到了操作系统的 page cache，所以只要操作系统不重启，内容也不会丢失。

为什么需要 redo log

我们已经知道 redo log 的目的是防止数据丢失，保证事务的持久性。但是下面两个问题还是值得思考的：

1、在事务提交时，要先同步重做日志到磁盘再修改数据页，为什么不直接将数据页同步到磁盘？

如果去掉 redo log，在页数据发生变更后直接将页同步到磁盘当然也可以保证事务的持久性，但是效率极低。默认情况下，一个页的大小为16KB，可以记录多条记录，而一个变更可能就涉及到一条或几条记录，它就要将记录所在的整个页同步到磁盘，属实浪费；其次，将页同步到磁盘涉及对 I/O 的 随机写 操作，效率低下。重做日志是对日志文件的追加操作，属于 顺序写，顺序写毫无疑问要比随机写快；此外，即使事务还没有提交，mysql 后台主线程每秒都会将重做日志同步到磁盘，因此，即使是很大的事务提交时间也很短。

2、binlog 日志也是事务提交前的日志，为啥不直接用 binlog 日志做恢复？

这个问题网上有诸多答案，归纳几点：

从日志格式来看，STATEMENT 格式的日志因为动态函数的原因，肯定不能用作数据恢复；
从恢复效率来看，首先，binlog 日志是逻辑日志，而 redo log 日志是物理日志，恢复更快；其次，binlog 日志是追加写文件，而 redo log 日志是循环写的，在脏页刷新回磁盘后即可被覆盖，因此在做数据恢复时，redo log 记录的内容本身就会比 binlog 更少。

binlog 和 redo log 区别

	binlog	redo log
适用对象不同	mysql server 层	Innodb 存储引擎层
写入方式不同	追加写，一个文件满了写新文件	循环写固定文件
文件格式不同	逻辑日志，一个事务具体操作内容	物理日志，页的修改情况
写入磁盘时间不同	提交事务前一次写入	在事务进行中有后台线程不断同步
用途不同	主从复制、数据备份	数据恢复

两阶段提交

在主从复制模式下，会开启 binlog 日志。此时，主库提交事务时，既要写二进制日志，还要写重做日志，mysql 需要保证两个操作的原子性。假如它们不满足原子性，比如先写完二进制日志后，如果 mysql 实例发生宕机，重启后，由于 redo log 日志没有记录的原因，在做数据恢复后主库将丢失这个数据更新，而从库却根据 binlog 日志进行了重放，最终导致主从不一致。

mysql 结合 内部 XA 事务 和 两阶段提交方案 来实现这两个操作的原子性，看看具体流程：

1、prepare 阶段：事务提交时，先写 redo log，同时记录 XA 事务的 ID (XID)，标记为 prepare 状态；

2、写入 binlog 日志，同时记录 XID；

3、commit 阶段：再次写 redo log，标记为 commit 状态。

我们看看它是如果通过以上步骤实现原子性的：

在 mysql 使用 redo log 做数据恢复时，如果发现 redo log 处于 commit 状态，则表示 binlog 一定落盘了，可以直接恢复；

如果发现 redo log 处于 prepare 状态，就要根据 XID 和 binlog 日志来判断：

如果在 binlog 找不到这个 XID，说明还没有同步 binlog，回滚事务；
反之说明两个日志都已同步成功，提交事务。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/212746