一文读懂MySQL所有日志

一文读懂MySQL所有日志

一、前言

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

MySQL日志分类:

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

二、错误日志

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

2.1 错误日志配置

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

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

2.2 错误日志记录信息

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

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

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

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

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

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

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

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

2.3 例子

三、 查询日志

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

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

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

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

3.1 MySQL查询日志配置

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

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

如果开启了查询日志，参数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设置:

3.2 开启及关闭MySQL查询日志

方法1: 在配置文件中设置(不推荐)

#可以在my-f里添加,1开启（0关闭）,当然了,这样要重启才能生效,有点多余了general-log = 1log_output='table'

然后重启MySQL实例

方法2 : 通过命令设置

#也可以设置变量那样更改,1开启（0关闭）,即时生效,不用重启,首选当然是这样的了set global general_log=1set global log_output='table';

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

3.3 查看查询日志信息

3.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 。‍

慢查询日志分析工具 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日志文件中读取数据应用到数据库中，来实现数据的主从复制。

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 –

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。