一文读懂MySQL所有日志

一、前言

日志文件中记录着MySQL数据库运行期间发生的变化;包括MySQL数据库的客户端连接状况、SQL语句的执行情况和错误信息等,是MySQL数据库的重要组成部分。

MySQL日志分类:

  • 错误日志
  • 查询日志
  • 慢查询日志
  • 事务日志(Redo log)
  • 二进制日志
  • 中继日志

二、错误日志

默认情况下,错误日志是开启的,且无法被禁止。默认情况下,错误日志是存储在数据库的数据文件目录中,名称为hostname.err,其中,hostname为服务器主机名。

2.1 错误日志配置

为了方便管理,用户可以根据自己的需求来配置错误日志存储位置和日志级别,配置参数如下:

  • log_error = on|文件路径 是否启用错误日志,on表示开启,文件路径表示指定自定义日志路径
  • log_warnings = 1|0 是否记录warnings信息到错误日志中

2.2 错误日志记录信息

  1. 服务器启动和关闭过程中的信息

未必是错误信息,比如mysql是如何去初始化存储引擎的过程记录在错误日志里等等

2.服务器运行过程中的错误信息

比如sock文件找不到,无法加载mysql数据库的数据文件,如果忘记初始化mysql或data dir路径找不到,或权限不正确等 都会记录在此

3.事件调度器运行一个事件时产生的信息

一旦mysql调度启动一个计划任务的时候,它也会将相关信息记录在错误日志中

4.在从服务器上启动从服务器进程时产生的信息

在复制环境下,从服务器进程的信息也会被记录进错误日志

2.3 例子

一文读懂MySQL所有日志插图

三、 查询日志

查询日志在MySQL中被称为general log(通用日志),查询日志里的内容不要被”查询日志”误导,认为里面只存储select语句,其实不然,查询日志里面记录了数据库执行的所有命令,不管语句是否正确,都会被记录,具体原因如下:

  • insert查询为了避免数据冲突,如果此前插入过数据,当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错
  • update时也会查询因为更新的时候很可能会更新某一块数据
  • delete查询,只删除符合条件的数据

因此都会产生日志,在并发操作非常多的场景下,查询信息会非常多,那么如果都记录下来会导致IO非常大,影响MySQL性能,因此如果不是在调试环境下,是不建议开启查询日志功能的。

查询日志的开启有助于帮助我们分析哪些语句执行密集,执行密集的select语句对应的数据是否能够被缓存,同时也可以帮助我们分析问题,所以,我们可以根据自己的实际情况来决定是否开启查询日志。

3.1 MySQL查询日志配置

  • 参数general_log用来控制开启、关闭MySQL查询日志
  • 参数general_log_file用来控制查询日志的位置

所以如果你要判断MySQL数据库是否开启了查询日志,可以使用下面命令。general_log为ON表示开启查询日志,OFF表示关闭查询日志。

一文读懂MySQL所有日志插图1

如果开启了查询日志,参数log_output控制着查询日志的存储方式, log_output可以设置为以下4种值:

  • FILE : 表示日志存储在文件中
  • TABLE : 表示日志存储在mysql库中的general_log表中
  • FILE, TABLE : 表示将日志同时存储在文件和general_log表中,改值会徒增很多IO压力,一般不会这样设置
  • NONE : 表示不记录日志,即使general_log设置为ON, 如果log_output设置为NONE,也不会记录查询日志

log_output不仅控制查询日志的输出,也控制着慢查询日志的输出,即: log_output设置为FILE,就表示查询日志和慢查询日志都存放在文件中,设置为TABLE,查询日志和慢查询日志都存放在mysql库中的general_log表中

查看log_output设置:

一文读懂MySQL所有日志插图2

3.2 开启及关闭MySQL查询日志

  • 方法1: 在配置文件中设置(不推荐)
#可以在my.cnf里添加,1开启(0关闭),当然了,这样要重启才能生效,有点多余了
general-log = 1
log_output='table'

然后重启MySQL实例

  • 方法2 : 通过命令设置
#也可以设置变量那样更改,1开启(0关闭),即时生效,不用重启,首选当然是这样的了
set global general_log=1
set global log_output='table';

通过该方式设置,MySQL实例重启后,相关配置又恢复到默认值。如果只是短暂时间内使用,推荐使用命令行方式

3.3 查看查询日志信息

一文读懂MySQL所有日志插图3

3.4 修改查询日志名称或位置

一文读懂MySQL所有日志插图4

四、 慢查询日志

慢日志全称为慢查询日志(Slow Query Log),主要用来记录在 MySQL 中执行时间超过指定时间的 SQL 语句。通过慢查询日志,可以查找出哪些语句的执行效率低,以便进行优化。

默认情况下,MySQL 并没有开启慢日志,可以通过修改 slow_query_log 参数来打开慢日志。

与慢日志相关的参数介绍如下:

  • slow_query_log:是否启用慢查询日志,默认为0,可设置为0、1,1表示开启。
  • slow_query_log_file:指定慢查询日志位置及名称,默认值为host_name-slow.log,可指定绝对路径。
  • long_query_time:慢查询执行时间阈值,超过此时间会记录,默认为10,单位为s。
  • log_output:慢查询日志输出目标,默认为file,即输出到文件。
  • log_timestamps:主要是控制 error log、slow log、genera log 日志文件中的显示时区,默认使用UTC时区,建议改为 SYSTEM 系统时区。
  • log_queries_not_using_indexes:是否记录所有未使用索引的查询语句,默认为off。
  • min_examined_row_limit:对于查询扫描行数小于此参数的SQL,将不会记录到慢查询日志中,默认为0。
  • log_slow_admin_statements:慢速管理语句是否写入慢日志中,管理语句包含 alter table、create index 等,默认为 off 即不写入。

一般情况下,我们只需开启慢日志记录,配置下阈值时间,其余参数可按默认配置。对于阈值时间,可灵活调整,比如说可以设置为 1s 或 3s 。‍

一文读懂MySQL所有日志插图5

慢查询日志分析工具 pt-query-digest

pt-query-digest 是分析MySQL查询日志最有力的工具,该工具功能强大,它可以分析binlog,Generallog,slowlog,也可以通过show processlist或者通过 tcpdump 抓取的MySQL协议数据来进行分析,比 mysqldumpslow 更具体,更完善。以下是使用pt-query-digest的示例:

//直接分析慢查询文件
pt-query-digest slow.log > slow_report.log

该工具可以将查询的剖析报告打印出来,可以分析结果输出到文件中,分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间,次数,占比等,可以借助分析结果找出问题进行优化。

五、事务日志

数据库数据存放的文件称为data file;日志文件称为log file;数据库数据是有缓存的,如果没有缓存,每次都写或者读物理disk,那性能就太低下了。数据库数据的缓存称为data buffer,日志(redo)缓存称为log buffer;既然数据库数据有缓存,就很难保证缓存数据(脏数据)与磁盘数据的一致性。

但是万一数据库发生断电,因为缓存的数据没有写入磁盘,导致缓存在内存中的数据丢失而导致数据不一致怎么办?

为了保证事务的ACID特性,就不得不说MySQL InnoDB引擎的事务日志: 重做日志redo和回滚日志undo

innodb通过force log at commit机制实现事务的持久性,即在事务提交的时候,必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。

注: 在数据库的世界里,数据从来都不重要,日志才是最重要的,有了日志就有了一切

5.1 Redo 日志

redo log参数

  • innodb_log_files_in_group:redo log 文件的个数,命名方式如:ib_logfile0,iblogfile1… iblogfilen。默认2个,最大100个。
  • innodb_log_file_size:文件设置大小,默认值为 48M,最大值为512G,注意最大值指的是整个 redo log系列文件之和。
  • innodb_log_group_home_dir:文件存放路径
  • innodb_log_buffer_size:Redo Log 缓存区,默认8M,可设置1-8M。
  • innodb_flush_log_at_trx_commit
    • innodb_flush_log_at_trx_commit=0,事务发生过程,日志一直激励在redo log buffer中,跟其他设置一样,但是在事务提交时,不产生redo 写操作,而是MySQL内部每秒操作一次,从redo log buffer,把数据写入到系统中去。如果发生crash,即丢失1s内的事务修改操作
    • innodb_flush_log_at_trx_commit=1,每次commit都会把redo log从redo log buffer写入到system,并fsync刷新到磁盘文件中。
    • innodb_flush_log_at_trx_commit=2,每次事务提交时MySQL会把日志从redo log buffer写入到system,但只写入到file system buffer,由系统内部来fsync到磁盘文件。如果数据库实例crash,不会丢失redo log,但是如果服务器crash,由于file system buffer还来不及fsync到磁盘文件,所以会丢失这一部分的数据。

在主从复制结构中,要保证事务的持久性和一致性,需要对日志相关变量设置为如下:

  • 如果启用了二进制日志,则设置sync_binlog=1,即每提交一次事务同步写到磁盘中。
  • 总是设置innodb_flush_log_at_trx_commit=1,即每提交一次事务都写到磁盘中。

上述两项变量的设置保证了:每次提交事务都写入二进制日志和事务日志,并在提交时将它们刷新到磁盘中。

redo log介绍

redo log 属于 MySQL 存储引擎 InnoDB 的事务日志。

MySQL 的数据是存放在磁盘中的,每次读写数据都需做磁盘 IO 操作,如果并发场景下性能就会很差。为此 MySQL 提供了一个优化手段,引入缓存 Buffer Pool。这个缓存中包含了磁盘中部分数据页(page)的映射,以此来缓解数据库的磁盘压力。

当从数据库读数据时,首先从缓存中读取。如果缓存中没有,则从磁盘读取后放入缓存;当向数据库写入数据时,先向缓存写入,此时缓存中的数据页数据变更,这个数据页称为脏页,Buffer Pool 中修改完数据后会按照设定的更新策略,定期刷到磁盘中,这个过程称为刷脏页。

如果刷脏页还未完成,可 MySQL 由于某些原因宕机重启。此时 Buffer Pool 中修改的数据还没有及时的刷到磁盘中,就会导致数据丢失,无法保证事务的持久性。

为了解决这个问题,引入了r edo log。redo Log 如其名侧重于重做!它记录的是数据库中每个页的修改,而不是某一行或某几行修改成怎样,可以用来恢复提交后的物理数据页,且只能恢复到最后一次提交的位置。

redo log 用到了 WAL(Write-Ahead Logging)技术。这个技术的核心就在于修改记录前,一定要先写日志,并保证日志先落盘,才能算事务提交完成。

有了 redo log 再修改数据时,InnoDB 引擎会把更新记录先写在 redo log 中,在修改 Buffer Pool 中的数据。当提交事务时,调用 fsync 把 redo log 刷入磁盘。至于缓存中更新的数据文件何时刷入磁盘,则由后台线程异步处理。

注意:此时 redo log 的事务状态是 prepare,还未真正提交成功,要等 bin log 日志写入磁盘完成才会变更为 commit,事务才算真正提交完成。

这样一来,即使刷脏页之前 MySQL 意外宕机也没关系。只要在重启时解析 redo log 中的更改记录进行重放,重新刷入磁盘即可。

5.2 undo日志

undo log 也是属于 MySQL 存储引擎 InnoDB 的事务日志。

undo log 属于逻辑日志,如其名主要起到回滚的作用,它是保证事务原子性的关键。记录的是数据修改前的状态,在数据修改的流程中,同时会记录一条与当前操作相反的逻辑日志到 undo log 中。

这样当某些原因导致服务异常事务失败,就可以借助 undo log 将数据回滚到事务执行前的状态,保证事务的完整性。

那可能有人会问:同一个事务内的一条记录被多次修改,那是不是每次都要把数据修改前的状态都写入 undo log 呢?

答案是不会的!

undo log 只负责记录事务开始前要修改数据的原始版本,当我们再次对这行数据进行修改,所产生的修改记录会写入到 redo log。undo log 负责完成回滚,redo log 负责完成前滚。

未提交的事务,即事务未执行 commit。但该事务内修改的脏页中,可能有一部分脏块已经刷盘。如果此时数据库实例宕机重启,就需要用回滚来将先前那部分已经刷盘的脏块从磁盘上撤销。

未完全提交的事务,即事务已经执行 commit,但该事务内修改的脏页中只有一部分数据被刷盘,另外一部分还在 buffer pool 缓存上,如果此时数据库实例宕机重启,就需要用前滚来完成未完全提交的事务。将先前那部分由于宕机在内存上的未来得及刷盘数据,从 redo log 中恢复出来并刷入磁盘。

数据库实例恢复时,先做前滚,后做回滚。

undo log、redo log、bin log 三种日志都是在刷脏页之前就已经刷到磁盘了,相互协作最大限度保证了用户提交的数据不丢失。

undo log参数

  • innodb_max_undo_log_size:控制最大undo tablespace文件的大小,该值默认大小为1G,truncate后的大小默认为10M。
  • innodb_undo_tablespaces:设置undo独立表空间个数,范围为0-128, 默认为0,0表示表示不开启独立undo表空间 且 undo日志存储在ibdata 文件中。
  • innodb_undo_log_truncate:InnoDB的purge线程,根据innodb_undo_log_truncate设置开启或关闭、innodb_max_undo_log_size的参数值,以及truncate的频率来进行空间回收和 undo file 的重新初始化。该参数生效的前提是,已设置独立表空间且独立表空间个数大于等于2个。
  • innodb_purge_rseg_truncate_frequency:用于控制purge回滚段的频度,默认为128。

六、二进制日志

6.1 二进制简介

MySQL的二进制日志(binary log)是一个二进制文件,主要记录所有数据库表结构变更(例如CREATE、ALTER TABLE…)以及表数据修改(INSERT、UPDATE、DELETE…)的所有操作。

6.2 binlog的作用

恢复(recovery):某些数据的恢复需要二进制日志。例如,在一个数据库全备文件恢复后,用户可以通过二进制日志进行point-in-time的恢复。

复制(replication):其原理与恢复类似,通过复制和执行二进制日志使一台远程的MySQL数据库(一般称为slave或者standby)与一台MySQL数据库(一般称为master或者primary)进行实时同步。

审计(audit):用户可以通过二进制日志中的信息来进行审计,判断是否有对数据库进行注入攻击。

除了上面介绍的几个作用外,binlog对于事务存储引擎的崩溃恢复也有非常重要的作用。在开启binlog的情况下,为了保证binlog与redo的一致性,MySQL将采用事务的两阶段提交协议。当MySQL系统发生崩溃时,事务在存储引擎内部的状态可能为prepared和commit两种。对于prepared状态的事务,是进行提交操作还是进行回滚操作,这时需要参考binlog:如果事务在binlog中存在,那么将其提交;如果不在binlog中存在,那么将其回滚,这样就保证了数据在主库和从库之间的一致性。

6.3 binlog的开启

如果想开启binlog,默认关闭,可以在MySQL配置文件中通过配置参数log-bin = [base-name]启动二进制日志。如果不指定base-name,则默认二进制日志文件名为主机名,并以自增的数字作为后缀,例如mysql-bin.000001,所在目录为数据库所在目录(datadir)。顺序说一下,对于二进制文件当满足下面三种情况时会创建新的文件,文件后缀会自增。

  • 文件大小达到max_binlog_size参数设置值时。
  • 执行flush logs命令。
  • 重启mysqld进程。

你可能会有顾虑,当文件后缀从000001增长到999999时会怎样?有网友测试过,当文件达到999999时又会回到000001,并不会有什么异常。

6.4 binlog格式

binlog格式分为: STATEMENT、ROW和MIXED三种,详情如下:

  • STATEMENT

Statement 模式只记录执行的 SQL,不需要记录每一行数据的变化,因此极大的减少了 binlog 的日志量,避免了大量的 IO 操作,提升了系统的性能。

但是,正是由于 Statement 模式只记录 SQL,而如果一些 SQL 中包含了函数,那么可能会出现执行结果不一致的情况。比如说 uuid() 函数,每次执行的时候都会生成一个随机字符串,在 master 中记录了 uuid,当同步到 slave 之后,再次执行,就获取到另外一个结果了。

  • ROW

从 MySQL5.1.5 版本开始,binlog 引入了 Row 格式,Row 格式不记录 SQL 语句上下文相关信息,仅仅只需要记录某一条记录被修改成什么样子了。

Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节,这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。

不过 Row 格式也有一个很大的问题,那就是日志量太大了,特别是批量 update、整表 delete、alter 表等操作,由于要记录每一行数据的变化,此时会产生大量的日志,大量的日志也会带来 IO 性能问题。

现在对于ROW格式的二进制日志基本是标配了,主要是因为它的优势远远大于缺点。并且由于ROW格式记录行数据,所以可以基于这种模式做一些DBA工具,比如数据恢复,不同数据库之间数据同步等。

  • MIXED

从 MySQL5.1.8 版开始,MySQL 又推出了 Mixed 格式,这种格式实际上就是 Statement 与 Row 的结合。

在 Mixed 模式下,系统会自动判断该用 Statement 还是 Row:一般的语句修改使用 Statement 格式保存 binlog;对于一些 Statement 无法准确完成主从复制的操作,则采用 Row 格式保存 binlog。

Mixed 模式中,MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式,也就是在 Statement 和 Row 之间选择一种。

6.5 binlog的相关参数

  • max_binlog_size:可以通过max_binlog_size参数来限定单个binlog文件的大小(默认1G)。

对于binlog文件的大小,有个需要注意的地方是,binlog文件可能会大于max_binlog_size参数设定的阈值。由于一个事务所产生的所有事件必须记录在同一个binlog文件中,所以即使binlog文件的大小达到max_binlog_size参数指定的大小,也要等到当前事务的所有事件全部写入到binlog文件中才能切换,这样就会出现binlog文件的大小大于max_binlog_size参数指定的大小的情况。

  • binlog_cache_size

当使用事务的表存储引擎(如InnoDB存储引擎)时,所有未提交(uncommitted)的二进制日志会被记录到一个缓存中去,等该事务提交(committed)时直接将缓冲中的二进制日志写入二进制日志文件,而该缓冲的大小由binlog_cache_size决定,默认大小为32K。此外,binlog_cache_size是基于会话(session)的,也就是说,当一个线程开始一个事务时,MySQL会自动分配一个大小为binlog_cache_size的缓存,因此该值的设置需要相当小心,不能设置过大。当一个事务的记录大于设定的binlog_cache_size时,MySQL会把缓冲中的日志写入一个临时文件中,因此该值又不能设得太小。通过SHOW GLOBAL STATUS命令查看binlog_cache_use、binlog_cache_disk_use的状态,可以判断当前binlog_cache_size的设置是否合适。binlog_cache_use记录了使用缓冲写二进制日志的次数,binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。

  • sync_binlog

在MySQL 5.7之前版本默认情况下,二进制日志并不是在每次写的时候同步的磁盘(用户可以理解为缓冲写)。因此,当数据库所在的操作系统发生宕机时,可能会有最后一部分数据没有写入二进制文件中,这会给恢复和复制带来问题。参数sync_binlog=[N]中的N表示每提交多少个事务就进行binlog刷新到磁盘。如果将N设为1,即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志,每次事务提交时就会刷新binlog到磁盘;sync_binlog为0表示刷新binlog时间点由操作系统自身来决定,操作系统自身会每隔一段时间就会刷新缓存数据到磁盘;sync_binlog为N表示每N个事务提交会进行一次binlog刷新。如果使用Innodb存储引擎进行复制,并且想得到最大的高可用性,需要将此值设置为1。不过该值为1时,确时会对数据库IO系统带来一定的开销。

但是,即使将sync_binlog设为1,还是会有一种情况导致问题的发生。当使用InnoDB存储引擎时,在一个事务发出COMMIT动作之前,由于sync_binlog为1,因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志,但是提交还没有发生,并且此时发生了宕机,那么在MySQL数据库下次启动时,由于COMMIT操作并没有发生,这个事务会被回滚掉。但是二进制日志已经记录了该事务信息,不能被回滚。对于这个问题,MySQL使用了两阶段提交来解决的,简单说就是对于已经写入到binlog文件的事务一定会提交成功, 而没有写入到binlog文件的事务就会进行回滚,从而保证二进制日志和InnoDB存储引擎数据文件的一致性,保证主从复制的安全。

  • binlog-do-db&binlog-ignore-db

参数binlog-do-db和binlog-ignore-db表示需要写入或者忽略写入哪些库的二进制日志。默认为空,表示需要同步所有库的日志到二进制日志。

  • log-slave-update

如果当前数据库是复制中的slave角色,则它不会将master取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入,要设置log-slave-update。如果需要搭建master–>slave–>slave架构的复制,则必须设置该参数。

  • binlog-format

binlog_format参数十分重要,用来设置二进制日志的记录格式,详情参考(6.5 binlog格式)

  • log_bin_trust_function_creators

默认为OFF,这个参数开启会限制存储过程、Function、触发器的创建。

七、中继日志(relay log)

7.1 什么是relay log?

Relay log,一般叫做中继日志,一般情况下它在MySQL主从同步读写分离集群的从节点才开启。主节点一般不需要这个日志。

master主节点的binlog传到slave从节点后,被写道relay log里,从节点的slave sql线程从relaylog里读取日志然后应用到slave从节点本地。从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件,然后SQL线程会读取relay-log日志的内容并应用到从服务器,从而使从服务器和主服务器的数据保持一致。

它的作用可以参考如下图,从图片中可以看出,它是一个中介临时的日志文件,用于存储从master节点同步过来的binlog日志内容,它里面的内容和master节点的binlog日志里面的内容是一致的。然后slave从节点从这个relaylog日志文件中读取数据应用到数据库中,来实现数据的主从复制。

一文读懂MySQL所有日志插图6

7.2 relay log相关参数

  • max_relay_log_size:relaylog日志文件的最大值,默认为0,表示和binlog日志文件大小一样。如果不为0,表示relaylog日志文件的最大值就是设置的对应的大小,该参数的单位为byte。
  • relay_log:设置的relaylog的日志文件路径和名称。如果不设置路径,只是设置文件名,则路径是在datadir参数所指向的磁盘目录。如果设置了目录,则会把relaylog写在对应的目录下。如果设置了目录,则要注意这个目录需要让MySQL有对应的读写的权限才可以,否则启动MySQL数据库会失败。
  • relay_log_basename:relaylog日志文件的基础名称,就是去掉文件后缀之后的文件名称。
  • relay_log_index:relaylog日志的索引文件,里面记录了所有当前有效的relaylog日志文件列表。可以指定这个索引文件的名称和存储目录,不过我们一般配置它和relaylog在同一目录下。
  • relay_log_info_file:这个文件里面,记录当前正在使用到的relaylog的文件名称和日志文件中的位置偏移量,以及master节点上面的binlog日志偏移量。这个文件的名称为relay-log.info,它的目录和relaylog在同级目录下。
  • relay_log_info_repository:这个参数标识着上面的relay_log_info_file中的信息是记录在磁盘目录上,还是记录在表中。它有两个取值范围,分别是FILE或者TABLE。

– END –

更多精彩内容,关注我们▼▼

一文读懂MySQL所有日志插图7

为您推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注