MySQL 的日志种类学习

Binlog

show binary logs;  // 查看binlog列表
show master status;  // 查看最新 binlog 文件
show binlog events in 'mysql-bin.000599';  // 查看当前 binlog 文件内容

• Log_name，当前日志名称
• Pos，事件开始位置
• Event_type，事件类型
• Server_id，服务器 ID
• End_log_pos，事件结束位置
• Info，具体事件信息

BingLog 格式

• statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。

• row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。

• mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。
此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

relay log

在 MySQL 主从同步中，从服务会将主服务发来的 Binlog 记录到 relay log 中。

relay-log的结构和binlog非常相似，只不过他多了一个master.info和relay-log.info的文件。

master.info记录了上一次读取到master同步过来的binlog的位置，以及连接master和启动复制必须的所有信息。

relay-log.info记录了文件复制的进度，下一个事件从什么位置开始，由sql线程负责更新。

undo log

记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

当系统里没有比这个回滚日志更早的read-view的时候 回滚日志会被删除

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

redo log

当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log，redo log 大小是固定的

redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。

redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。binlog 是二进制的，根据 binlog_format 可以分为 row，stattement，mix。

redo log 用于 crash recovery；binlog 用于 point-in-time recovery 基于时间点恢复。

redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

写入时机，redo log 写入时机不固定；binlog 在事物提交时写入。

innodb_flush_log_at_trx_commit = 0，表示事务提交时，不进行 redo log buffer 的刷盘操作

innodb_flush_log_at_trx_commit = 1 表示将此时事务所对应的 redo log 所在的 redo log block 从内存写入到磁盘，同时调用 fysnc，确保数据落入到磁盘,可以保证MySQL异常重启之后数据不丢失。

innodb_flush_log_at_trx_commit = 2 表示只是将日志写入到操作系统的缓存，而不进行 fysnc 操作。（进程在向磁盘写入数据时，是先将数据写入到操作系统的缓存中：os cache，再调用 fsync 方法，才会将数据从 os cache 中刷新到磁盘上）

slow query log

日志两阶段提交

日志的两阶段提交，保障了崩溃恢复时， MySQL 主备数据不一致问题，沿用《MySQL 实战 45 讲》 的图：

binlog 日志中，当 binlog_format = statement 时，完整的事物结尾会有 commit 标志；当 binlog_format = ROW 时，完整的事物结尾有 XID event。

当 redo log 只有 prepare 没有 commit 时，就需要根据 redo log 里面的 xid 关联 binlog 查看事物是否完整。

1. 若 redo log 事物时完整的，并且有 commit 标志，那么直接恢复
2. 若 redo log 只有 prepare，那么需要判断 binlog 是否完整
   • 若 binlog 完整，则直接提交事物
   • 若 binlog 不完整，回滚事物

主从同步

一个事务日志同步的完整过程：

• 在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量。

• 在备库B上执行start slave命令，这时候备库会启动两个线程，就是图中的io_thread和sql_thread。其中io_thread负责与主库建立连接。

• 主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B。

• 备库B拿到binlog后，写到本地文件，称为中转日志（relay log）。

• sql_thread读取中转日志，解析出日志里的命令，并执行。

博客参考

再深入一点|binlog和relay-log到底长啥样？