mysql日志简述

Z先生09
62 阅读9分钟

mysql日志简述

分类

Redo Log（重做日志）

简述

  • Redo log （重做日志），是物理日志，日志记录的内容是数据页的更改，这个页"做了什么改动"
  • Redo Log属于InnoDB存储引擎的日志
  • Redo Log记录的日志会放入Redo Log Buffer中（缓存中），然后通过指定的刷盘策略，将Buffer中的日志刷入到磁盘，即Redo Log File（磁盘中）
  • 以环形循环写入，会覆盖

作用

  • 可以处理宕机重启时丢失数据问题（Redo Log恢复，默认启动）
  • 日志用来减少提交事务时的开销
  • 将IOS随机写改为IO顺序写

日志刷盘策略

innodb_flush_log_at_trx_commit

  • 当设置为0的时候（延迟写）

    • 写入Log Buffer，然后每秒写入OS Buffer并调用fsync刷到Log File中

  • 当设置为1的时候(实时写，实时刷

    • 写入Log Buffer，同时写入OS Buffer并调用fsync刷到Log File中；
    • 这种方式即使系统崩溃也不会丢失任何数据，是最安全的，同时也是默认值

  • 当设置为2-n的时候（实时写，延迟刷）

    • 写入Log Buffer，并写入OS Buffer，然后每秒刷到Log File中

刷盘三阶段

  1. 写入Log Buffer
  2. 写入 OS Buffer
  3. 写入Log File（磁盘）

Redo log两阶段提交

  • prepare阶段
    • 当有数据修改时，将修改从Redo Log Buffer刷入到磁盘后，会标记为prepare（准备完成）状态
  • commit阶段
    • 当数据修改时，将BingLog Buffer刷到到磁盘，并生成XID后，且Redo Log处于prepare状态时，将prepare状态改为commit状态

相关配置

  • innodb_log_files_in_group。redo log 文件的个数
  • innodb_log_file_size。文件设置大小，默认值为 48M，最大值为512G
  • innodb_log_group_home_dir。文件存放路径
  • innodb_log_buffer_size。Redo Log 缓存区，默认8M，可设置1-8M

相关指令

show global variables like '%redo%';

Undo Log（回滚日志）

简述

  • InnoDB把这些为了回滚而记录的这些东西称之为undo log
  • undo log是属于innodb 引擎的日志，以链表的形式存在
  • 在进行事务回滚的时候，通过DB_ROLL_PTR（回滚指针）找到undo log日志中的记录行，进行相应的回滚逻辑

明细分析

  • Insert undo log。插入一条记录时，记录这条数据的主键值，在回滚的时候把这个主键值对应的记录删掉
  • Update undo log。修改一条记录时，记录修改前的旧值，在回滚时把这条记录更新为旧值
  • Delete undo log。删除一条记录时，记录这条数据的内容，在回滚时再把内容重新插入到表中
  • 由于查询操作（SELECT）并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo log

相关配置

  • innodb_max_undo_log_size。控制最大undo tablespace文件的大小
  • innodb_undo_tablespaces。设置undo独立表空间个数，范围为0-128， 默认为0
  • innodb_undo_log_truncate。InnoDB的purge线程，根据innodb_undo_log_truncate设置开启或关闭

相关指令

show global variables like '%undo%'

show global variables like '%truncate%';

刷盘策略

参考Redo Log的刷盘策略

Binary Log（二进制日志）

简述

  • 记录了对数据库执行更改的所有操作，以二进制的形式保存在磁盘中。不包括SELECT 和 SHOW这类操作。
  • BinaryLog是逻辑日志，通过追加的方式进行写入。可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到max_binlog_size后，会生成新的二进制日志文件来保存
  • BinaryLog属于Server层面的记录。任何存储引擎的 mysql 数据库都会记录binlog日志

使用场景

  • 主从复制
    • 在 Master 端开启 binlog，然后将 binlog 推送到各个 Slave 从端，Slave 端重放 binlog 从而达到主从数据一致
  • 数据恢复
    • 通过使用 mysqlbinlog 工具来恢复数据

刷盘策略

sync_binlog

  • 当设置为0时

    • 由系统自行判断何时写入磁盘

  • 当设置为1时

    • 每次事务提交(committed)的时候都要将 binlog 写入磁盘
    • 数据是最安全的，5.7之后的版本的默认值

  • 当设置为2-n时

    • 每提交 N 个事务，才会将 binlog 写入磁盘

日志格式

binlog-format
  • STATMENT
    • 记录的是数据库上执行的原生SQL语句
  • ROW
    • 基于数据的复制，基于行的更改
  • MIXED
    • 是MySQL默认使用的二进制日志记录方式。但MIXED格式默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制

Relay Log（中继日志）

简述

  • relay log是复制过程中产生的日志，很多方面都跟binary log差不多
  • relay log是从库服务器I/O线程，将主库服务器的二进制日志读取过来，并记录到从库服务器本地文件，然后从库的SQL线程会读取relay-log日志的内容并应用到从库服务器上

相关配置

  • max_relay_log_size
    • 标记relay log 允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小
  • relay_log
    • 定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录
  • relay_log_purge
    • 是否自动清空不再需要中继日志时。默认值为1(启用)
  • relay_log_recovery
    • relay-log故障损坏后，未处理的中继日志是否自动放弃，并重新获取
    • 默认情况下该功能是关闭的，建议开启
  • relay_log_space_limit
    • 防止中继日志写满磁盘
    • 不到万不得已，不推荐使用
  • sync_relay_log
    • 刷盘策略，参考BinLog的刷盘策略

Slow Query Log（慢查询日志）

简述

  • 慢查询日志记录的慢查询不仅仅是执行比较慢的SELECT语句，还有INSERT，DELETE，UPDATE，CALL等DML操作，只要超过了指定时间，都可以称为"慢查询"，被记录到慢查询日志中
  • 默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中

相关配置

  • slow_query_log
    • 慢查询开关
  • long_query_time
    • 慢查询指定时间设置，表示"多长时间的查询"被认定为"慢查询"，单位是秒(s)，默认是10s，即超过10s的查询都被认定为慢查询
  • log_queries_not_using_indexes
    • 表示如果运行的SQL语句没有使用到索引，是否也被当作慢查询语句记录到慢查询记录中
  • slow_query_log_file
    • 慢查询日志存储方式，FILE（文件），TABLE（表）

常用命令

show variables like "%slow%";
show variables like 'long_query_time'

慢查询日志分析工具

  • pt-query-digest
    • 业界著名的慢查询分析工具pt-query-digest，通过正则表达式实现这个功能但是这类处理办法Bug较多

Error Log（错误日志）

简述

  • 默认情况下，错误日志是开启的，且无法被禁止
  • 错误日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息

日志内容分析

  • 服务器启动和关闭过程中的信息
    • 未必是错误信息，比如mysql是如何去初始化存储引擎的过程记录在错误日志里等等
  • 服务器运行过程中的错误信息
    • 比如sock文件找不到，无法加载mysql数据库的数据文件，如果忘记初始化mysql或data dir路径找不到，或权限不正确等 都会记录在此
  • 事件调度器运行一个事件时产生的信息
    • 一旦mysql调度启动一个计划任务的时候，它也会将相关信息记录在错误日志中
  • 在从服务器上启动从服务器进程时产生的信息
    • 在复制环境下，从服务器进程的信息也会被记录进错误日志

相关配置

  • log-erro。文件路径
  • log_warnings。是否记录warnings信息到错误日志中

相关指令

  • 查看错误日志配置

    show variables like '%err%';

  • 删除错误日志

    mysql5.5.7之前，使用mysqladmin命令开启新的错误日志
mysqladmin –u root –pflush-logs

mysql5.5.7之后
使用重命名原来的错误日志文件

General Log（查询日志）

简述

  • 查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录
  • 默认是把General log关闭的
  • 如果不是在调试环境下，是不建议开启查询日志功能的

相关配置

  • general_log
    • 开启、关闭MySQL查询日志
  • general_log_file
    • 查询日志的位置
  • log_output
    • FILE 表示日志存储在文件中
    • TABLE 表示日志存储在mysql库中的general_log表中（ select * from mysql.general_log; ）
    • FILE, TABLE 表示将日志同时存储在文件和general_log表中，改值会徒增很多IO压力，一般不会这样设置
    • NONE 不会记录查询日志

常用指令

show variables like '%general_log%';
show variables like 'log_output';

常见问题

  • 什么是脏页

    • Innodb在更新数据时，只更新了内存的数据页，但并没有更新磁盘

  • 什么是刷脏

    • 将内存中的数据页保存到磁盘

  • 为什么要刷脏

    • 内存中的脏页太多，占用太多内存
    • redo log文件写满了，不能接收数据日志更新了，写不进去所以卡住
    • 系统空闲的时候提前刷脏，预防上述情况
    • mysql正常关闭前，保存数据，非kill

  • 怎么合理设置刷脏

    • 预防被迫刷脏
    • 服务器IO配置，innodb_io_capacity
    • 合理配置脏页比例上限，innodb_max_dirty_pages_pct
    • 控制”顺便刷脏“策略，innodb_flush_neighbors

  • 什么是随机IO与顺序IO

  • 逻辑日志与物理日志

    • 逻辑日志。可以简单理解为记录的就是sql语句
    • 物理日志。mysql 数据最终是保存在数据页的，物理日志记录的就是数据页变更

日志分析工具

pt-query-digest

它可以分析binlog，Generallog，slowlog，也可以通过show processlist或者通过 tcpdump 抓取的MySQL协议数据来进行分析，比 mysqldumpslow 更具体，更完善

mysqldumpslow

avatar
文章
阅读
粉丝