Mysql之日志

2021-10-16 01:04:23

WAL

了解MySQL日志前，先要了解WAL(Write-Ahead Loggin)，即预写式日志，其关键点在于先写日志再写磁盘。

在对数据页进行修改时, 通过将"修改了什么"这个操作记录在日志中, 而不必马上将更改内容刷新到磁盘上, 从而将随机写转换为顺序写, 提高了性能。

binlog

归档日志/逻辑日志，binlog 是 MySQL 的 Server 层实现的，通过追加写入的方式记录，所有引擎都可以使用。

binlog格式

  • statement：记录的是变更的SQL语句
  • row：记录的是每行实际数据的变更，8.0下默认选项，建议使用
  • mixed：statement和row模式的混合，根据执行的每一条具体的sql语句来区分对待记录的日志形式，也是在statement和row之间选择一种

MySQL 5.0以前，binlog只支持statement格式，这种格式在读已提交(Read Commited)隔离级别下主从复制是有bug的，因此Mysql将可重复读(Repeatable Read)作为默认的隔离级别。

-- 查看格式配置
show variables like "%binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

-- 查看binlog日志，位于data目录下
show binlog events in 'binlog.xxx';

-- 借助 mysqlbinlog 工具解析和查看 binlog 中的内容，start-position用于指定起始位置
mysqlbinlog -vv data/binlog.xxx --start-position=8900 --stop-position=8910;

写入机制

Mysql之日志

每个线程都会分配一块内存binlog cache，参数 binlog_cache_size控制内存的大小。

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中，并清空 binlog cache。如果事务提交前binlog cache空间不足，还是会暂存到磁盘的。

sync_binlog

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

  • 0：默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志
  • 1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存
  • N：每写N次操作系统缓冲就执行一次刷新操作

sync_binlog建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失

-- 查看当前参数设置
show variables like '%log_bin%';

主要作用

  • 备份，用于数据恢复，可使用官方自带工具mysqlbinlog解析binlog日志，进行数据回放
  • 复制，用于主从复制
  • 第三方场景：解析binlog，增量获取数据库数据，实现数据的订阅&消费

redo log

redo log称为重做日志，是一种物理日志，记录的是数据页的物理修改。

redo log 是 InnoDB 引擎特有的日志，大小固定，比如可以配置为一组 4 个文件，每个文件的大小是 1GB。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示：

Mysql之日志

好处

  • 高效：数据变更记录在redo log中（在某个数据页上做了什么修改），并更新内存即可，空闲时刷新磁盘
  • crash-safe：有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失（设置innodb_flush_log_at_trx_commit=1，表示每次事务的 redo log 都直接持久化到磁盘）

执行流程

Mysql之日志

redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

  • 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
  • 如果碰到只有 prepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务

崩溃恢复时的判断规则？

  • 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交

  • 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整

    • 如果是，则提交事务
    • 否则，回滚事务

prepare 和 commit两阶段提交的好处？

保持两份日志之间的逻辑一致性，binlog用于备库的同步与恢复时，不一致会造成主从同步一致性问题。例如：

  • 先写redo log，后写binlog：binlog可能存在丢失，造成从库数据少于主库
  • 先写binlog，后写redo log：redo log可能存在丢失，造成从库数据多于主库

innodb_flush_log_at_trx_commit

Mysql之日志

Redo Log Buffer用于事务提交前存放redo log，提交一个事务，会根据一定的策略把 redo 日志从 redo log buffer 里刷入到磁盘文件里去，通过innodb_flush_log_at_trx_commit 来配置，选项如下：

  • 值为0：提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。提交事务时 mysql 宕机，可能造成内存里的数据丢失
  • 值为1 : 提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。
  • 值为2 : 提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

因此，只有1才能真正地保证事务的持久性，但是由于刷新操作 fsync() 是阻塞的，直到完成后才返回，我们知道写磁盘的速度是很慢的，因此 MySQL 的性能会明显地下降。如果不在乎事务丢失，0和2能获得更高的性能。

select @@innodb_flush_log_at_trx_commit;

真实数据落盘与redo log无关，写redo log前数据已写入buffer poll中，崩溃恢复也是先写入buffer poll，真正的落盘是从buffer poll中发起的

undo log

回滚日志，是一种逻辑日志，提供回滚和多个行版本控制(MVCC)。

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

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录（反向记录）

Mysql之日志

当系统里没有比这个回滚日志更早的 read-view 的时候，即没有事务再需要用到这些回滚日志时，回滚日志会被删除。因此，建议尽量不要使用长事务。

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。

在MySQL5.6中开始支持把undo log分离到独立的表空间，并放到单独的文件目录下。可通过innodb_undo_tablespaces设定创建的undo表空间的个数，在mysql_install_db时初始化后，就再也不能被改动了，修改该值会导致MySQL无法启动。默认值为0，表示不独立设置undo的tablespace，默认记录到ibdata中；否则，则在undo目录下创建多个undo文件(每个文件的默认大小为10M)，最多可以设置到126。

relay log

Mysql之日志

relay log，称为中转日志，sql_thread 读取中转日志，解析出日志里的命令，并执行

主从同步机制有：

  • 全同步复制：在主节点上写入的数据，在从服务器上都同步完了以后才会给客户端返回成功消息，相对来说比较安全，但是耗时较长
  • 异步复制：master不需要保证slave接收并执行了binlog，能够保证master最大性能。但是slave可能存在延迟，主备数据无法保证一致性，在不停服务的前提下如果master宕机，提升slave为新的主库，就会丢失数据。
  • 半同步复制：存在主从延迟，开启并行复制（库级别并行，并行读取relay log中不同库的日志，然后并行重放不同库的日志）
    • 主库写入binlog，强制立即将数据同步到从库
    • 从库将日志写入自己本地的relay log后返回一个ack给主库
    • 主库收到至少一个从库ack之后认为写操作成功

general log

开启 general log 会将所有到达MySQL Server的SQL语句记录下来。一般不会开启开功能，因为log的量会非常庞大。但个别情况下可能会临时的开一会儿general log以供排障使用。 相关参数一共有：general_log、log_output、general_log_file

show variables like 'general_log'; -- 查看日志是否开启

set global general_log=on; -- 开启日志功能

show variables like 'general_log_file'; -- 看看日志文件保存位置

set global general_log_file='tmp/general.lg'; -- 设置日志文件保存位置

show variables like 'log_output'; -- 看看日志输出类型 table或file

set global log_output='table'; -- 设置输出类型为table，对应表mysql.slow_log

set global log_output='file'; -- 设置输出类型为file，默认类型

慢日志

mysql> show VARIABLES like 'slow_query%';
+---------------------+----------------------------------------------------+
| Variable_name       | Value                                              |
+---------------------+----------------------------------------------------+
| slow_query_log      | OFF                                                |
| slow_query_log_file | D:\tools\mysql-8.0.11-winx64\data\user-PC-slow.log |
+---------------------+----------------------------------------------------+

-- 开启慢日志(重启失效)
set GLOBAL slow_query_log=on

-- 查看当前慢日志阈值
select @@long_query_time; -- 等价于：show variables like '%long_query_time%';

-- 设置慢日志阈值
set long_query_time=0;

mysqldumpslow：mysql官方提供的慢查询日志分析工具，统计不同慢sql的

  • 出现次数(Count)
  • 执行最长时间(Time)
  • 累计总耗费时间(Time)
  • 等待锁的时间(Lock)
  • 发送给客户端的行总数(Rows)
  • 扫描的行总数(Rows)
  • 用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示)
上一篇：【React】生命周期


下一篇：innodb数据文件存储结构 undo redo