【MySQL】关于MySQL的日志，你需要知道些什么？

AQin
194 阅读10分钟

分类

不同类型的日志文件可以用来存储不同类型的信息，一般来讲MySQL的日志可以分为如下6种：

  • 慢查询日志 记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化
  • 通用查询日志 记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助
  • 错误日志 记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。
  • 二进制日志（bin log） 记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
  • 中继日志（relay log） 用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作
  • 数据定义语句日志 记录数据定义语句执行的元数据操作。

除二进制日志外，其他日志都是文本文件。默认情况下，所有日志创建于MySQL数据目录中。

优点 vs 缺点

  • 日志功能会降低MySQL数据库的性能 比如在查询非常频繁的MySQL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL数据库会花费很多时间记录日志
  • 日志会占用大量的磁盘空间 对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大。

通用查询日志（general query log）

通用查询日志用来记录用户的所有操作（包括启动/关闭MySQL服务、所有用户的连接开始时间和截止时间、发给MySQL数据库服务器的所有SQL指令等）

当我们的数据发生异常时，查看通用查询日志，可以帮助我们了解操作发生的具体时间和操作的细节，还原具体场景，准确定位问题

查看状态以及文件信息

SHOW VARIABLES LIKE '%general%';

通用查询日志默认是关闭的

错误日志（error log）

错误日志是发现问题、解决故障的首选，通常情况下不需要查看，但是发生异常时可以依据错误日志找出异常原因并进行解决。错误日志记录了MySQL服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等，并且在MySQL数据库中，错误日志功能是默认开启且无法被禁止的，默认名称mysqld.log

二进制日志（bin log ）/归档日志/变更日志（update log）

二进制日志记录了数据库所有执行的DDL和DML等数据库更新事件的语句，但是不包含没有修改任何数据的语句（没有SELECT、SHOW等哦(*≧ω≦)）

它以事件形式记录并保存在二进制文件中，通过这些信息，我们可以再现数据更新操作的全过程

  • 可以通过show binary logs;查看都有哪些bin log的日志文件
  • 可以通过mysqlbinlog -v 日志全路径查看伪SQL
  • 通过show binlog events in 表名 from 时间位置 limit num;查看各个事件

主要应用场景

  • 数据恢复 如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器
    • 需要手动配置（my.cnf里添加路径，如log-bin=/usr/local/mysql/data/binlog/mysql-bin）
    • 有三种
      • statement（SBR）
        • 只记录执行语句本身，效率较高，但可能会产生主从不一致，例如下面的SQL语句
  • 数据复制 由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的

可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。二进制日志可以通过数据库的全量备份和二进制日志中保存的增量信息，完成数据库的无损失恢复。但是，如果遇到数据量大、数据库和数据表很多（比如分库分表的应用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。

在这种情况下，一个有效的解决办法是配置主从数据库服务器，甚至是一主多从的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常等问题。

写入流程

事务开始 -> 写入binlog cache -> 事务结束 -> 从binlog cache写入文件系统的page cache（write操作） -> 文件系统的page cache同步到binlog文件中（sync操作）

可以通过设置sync_binlog参数控制在几次write操作后执行一次sync操作

bin log vs redo log

  • redo log 是物理日志，记录内容是“在某个数据页上做了什么修改”，属于InnoDB存储引擎层产生的；binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给ID=2这一行的c字段加1”，属于MySQL Server层
    • 逻辑日志和物理日志
      • 首先按照日志的记录方式可以分为逻辑日志和物理日志
      • 逻辑日志：可以简单理解为记录的就是sql语句
      • 物理日志：mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更
        • binlog日志和回滚日志undo log日志都属于逻辑日志，记录的是sql语句
        • redo log 重做日志属于物理日志记录的是数据页的变更
  • 虽然它们都属于持久化的保证，但是则重点不同
    • redo log 一般用于服务器异常宕机后事务数据自动恢复使用
    • bin log 一般用于保证MySQL集群架构的数据一致性，bin log没有自动crash-safe的能力
      • crash-safe指MySQL服务器宕机重启后，能够保证：已提交的事务仍然存在；未提交的事务自动回滚
  • redo log 循环覆盖写（write pos（当前写到的位置）和check point（已经刷到磁盘的位置）搭配，日志文件个数确定，形成一个环，最后一个文件写完会再从第一个开始，会覆盖旧的，当write pos追上check point则说明写满了，需要擦掉一些记录再继续）；bin log 追加写（写到一定程度切换到下一个，不会覆盖以前的）

两阶段提交

使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，会发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务

因此一旦redo log中存在，bin log中必定存在，因为写入逻辑如下：

事务开始 -> redo log 的 prepare 阶段 -> 写入 bin log -> redo log 的 commit 阶段（没有 commit 会回滚） -> 事务结束

中继日志（relay log）

介绍

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并目把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成注从服务器的数据同步。

搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。

文件名的格式是：从服务器名-relay-bin.序号。中继日志还有一个索引文件：从服务器名-re1ay

bin.index,用来定位当前正在使用的中继日志。

查看

中继日志与二进制日志的格式相同，可以用mysqlbinlog工具进行查看。下面是中继日志的一个片段：

主从复制

如何提升数据库并发能力

Redis + MySQL

  • 提升查询速度
  • 减少后端数据库压力

主从复制的作用

  1. 读写分离（Master-写库，Slave-读库）
  2. 数据备份（从主数据库备份到从数据库属于热备份）
  3. 具有高可用性（HA）：数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正常运行。关于高可用性的程度，我们可以用一个指标衡量，即正常可用时间/全年时间。比如要达到全年99.999%的时间都可用，就意味着系统在一年中的不可用时间不得超过3652460*(1-99.999%)=5.256分钟（含系统崩溃的时间、日常维护操作导致的停机时间等)，其他时间都需要保持可用的状态。

原理分析

二进制日志转储线程(Binlog dump thread)是一个主库线程，当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件(Event)的时候，会在Binlog上加锁，读取完成之后，再将锁释放掉。

从库I/O线程会连接到主库，向主库发送请求更新Binlog。这时从库的I/O线程就可以读取到主库的二进制日志转储线程发送的Binlog更新部分，并且拷贝到本地的中继日志(Relay log）。从库SQL线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

注意：不是所有版本的MySQL都默认开启服务器的二进制日志。在进行主从同步的时候，我们需要先检查服务器是否已经开启了二进制日志。

Master和Slave的关系
  • 一个Master能有多个Slave
  • 一个Slave只能对应一个Master
  • 每个Slave只能有唯一的服务器id

复制三步骤

  1. Master将写操作记录（二进制日志事件(binary log events)）到二进制日志
  2. Slave将Master的binary log events拷贝到它的中继日志
  3. Slave重做中继日志中的事件，将改变应用到自己的数据库中（MySQL复制是异步的且串行化的，而且重启后从接入点（binlog中event的position）开始复制）

复制的缺点

延时

一致性问题

  • 读/写库最终一致
  • 写数据必须走写库
  • 读数据必须走读库

注意：读写库可能会变化

按照一致性从弱到强（并发性从强到弱），有3种复制方式

你记住了吗 ^ ^~

avatar
文章
阅读
粉丝