likes
comments
collection
share

MySQL - 日志系统

作者站长头像
站长
· 阅读数 21

MySQL日志分类:

  • 错误日志
  • 查询日志
  • 慢查询日志
  • 二进制日志(binary_log)
  • 中继日志(relay_log)
  • 事务日志：重做日志(redo_log)、回滚日志(undo_log)

1. 错误日志 - log_error

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

1.1 错误日志配置

我们可以在 MySQL 的配置文件“my.ini”中配置它：

# Error Logging
log_error="GJTECH-PC.err"
# 是否记录warnings信息到错误日志中
log_warnings=1

1.2 错误日志记录信息

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

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

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

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

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

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

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

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

2. 查询日志 - general_log

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

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

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

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

2.1 查询MySQL查询日志

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

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

show variables like '%general%';
# 或者
show variables like 'general_log%';

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

show variables like 'log_output';

2.2 配置MySQL查询日志

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

然后重启MySQL实例

  1. 通过命令设置
# 1开启（0关闭）,即时生效,不用重启
set global general_log=1
set global log_output='table';

# 修改查询日志名称或位置
set global general_log_file='/u02/mysql_log.log';

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

3. 慢查询日志 - slow_query_log

慢查询会导致CPU，IOPS，内存消耗过高。当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。 开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。

慢查询日志记录的慢查询不仅仅是执行比较慢的SELECT语句，还有INSERT，DELETE，UPDATE，CALL等DML操作， 只要超过了指定时间，都可以称为"慢查询"，被记录到慢查询日志中。

默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中。

3.1 慢查询日志相关参数

  1. slow_query_log

慢查询开关，表示是否打开慢查询日志。

# 查询
show variables like '%slow%';
show variables like 'slow_query_log%';

# 设置慢查询
set global slow_query_log=1;

使用 set global slow_query_log='ON' 开启慢查询日志，只是对当前数据库有效，如果MySQL数据库重启后就会失效。 所以如果要永久生效，就要修改配置文件 my.cnf (其他系统变量也是如此)，如下：

[mysqld]
slow_query_log=1
  1. long_query_time

慢查询指定时间设置，表示"多长时间的查询"被认定为"慢查询"，单位是秒(s)，默认是10s，即超过10s的查询都被认定为慢查询。

show variables like 'long_query_time';

# 设置慢查询时间
set global long_query_time=0.05;

设置long_query_time这个阈值之后，MySQL数据库会记录运行时间超过该值的所有SQL语句， 但对于运行时间正好等于 long_query_time 的情况，并不会被记录下。 而设置 long_query_time为0来捕获所有的查询

  1. min_examined_row_limit

这个变量的意思是，查询扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。 如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过 long_query_time 的值，那么，这个查询就被记录到慢查询日志中； 反之，则不被记录到慢查询日志中。

show variables like 'min%';
  1. log_queries_not_using_indexes

表示如果运行的SQL语句没有使用到索引，是否也被当作慢查询语句记录到慢查询记录中，OFF表示不记录，ON表示记录。

show variables like 'log_queries_not_using_indexes';
  1. slow_query_log_file

当使用文件存储慢查询日志时（log_output设置为"FILE"或者"FILE,TABLE"时），制定慢查询日志存储在哪个文件中， 默认的文件名是"主机名-slow.log"，存储目录为数据目录

show variables like 'log_output';

MySQL 5.1 将慢查询记录到文件中时已经支持微秒级别，如果将慢查询记录到表中会导致时间粒度退化为只能到秒级

  1. log_throttle_queries_not_using_indexes

MySQL5.6.5版本新引入的参数，用来限制没有使用索引的语句每分钟记录到慢查询日志中的次数。 在生产环境中，有可能有很多没有使用索引的语句，可能会导致慢查询日志快速增长。

4. 回滚日志 - Undo Log

4.2 Undo Log介绍

用来存储事务中数据更新前的状态，以便回滚和保持其他事务的数据一致性。

undo log 是 mysql 中比较重要的事务日志之一，顾名思义，undo log 是一种用于撤销回退的日志，在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log 来进行回退。

Undo Log产生和销毁：Undo Log在事务开始前产生；事务在提交时，并不会立刻删除undo log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。Undo Log属于逻辑日志，记录一个变化过程。例如执行一个delete，undolog会记录一个insert；执行一个update，undolog会记录一个相反的update。

Undo Log存储：undo log采用段的方式管理和记录。在innodb数据文件中包含一种rollback segment回滚段，内部包含1024个undo log segment。可以通过下面一组参数来控制Undo log存储。

show variables like '%innodb_undo%';

4.2 undo log的作用

4.2.1 提供回滚操作【undo log实现事务的原子性】

我们在进行数据更新操作的时候，不仅会记录redo log，还会记录undo log，如果因为某些原因导致事务回滚，那么这个时候MySQL就要执行回滚（rollback）操作，利用undo log将数据恢复到事务开始之前的状态。

如我们执行下面一条删除语句：

delete from system_user where id=1

那么此时undo log会记录一条对应的insert 语句【反向操作的语句】，以保证在事务回滚时，将数据还原回去。

再比如我们执行一条update语句：

update user set name = "李四" where id = 1;   ---修改之前name=张三

此时undo log会记录一条相反的update语句，如下：

update user set name = "张三" where id = 1;

如果这个修改出现异常，可以使用undo log日志来实现回滚操作，以保证事务的一致性。

4.2.2 提供多版本并发控制(MVCC)

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。

MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。 引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。 在内部实现中，与Postgres在数据行上实现多版本不同，InnoDB是在undo log中实现的，通过undo log可以找回数据的历史版本。 找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。 在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。 MVCC是一种多版本并发控制机制。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

MySQL - 日志系统 事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。 事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读

5. 重做日志 - Redo log

5.1 Redo Log介绍

redo log则记录的是事务执行过程中的修改情况，redo log主要保证事务的持久性。

Redo： 顾名思义就是重做。以恢复操作为目的，在数据库发生意外时重现操作。

Redo Log： 指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志。

Redo Log 的生成和释放： 随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）。

5.2 redo log的作用

5.2.1 保证事务的持久性

如果buffer pool缓冲池中的脏页【脏数据】还没有进行刷盘的时候，此时数据库发生crash， 重启服务后，我们可以通过redo log日志找到需要重放到磁盘文件的那些数据记录。

5.2.2 提高事务提交的速度

buffer pool缓冲池中的数据直接刷新到磁盘，是一个随机IO，效率较差，而把buffer pool中的数据记录到redo log，是一个顺序IO，可以提高事务提交的速度。例如：

我们执行了一条更新语句：

update user set name = 'lisi' where id = 1   --更新前name = 'zhangsan'

此时redo log就会用来存在name = 'lisi'这条更新后的新纪录，如果在刷盘时发生异常， 我们可以通过redo log找到这条记录，然后进行重放操作，以保证事务的持久性。

5.3 Redo Log工作原理

Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

MySQL - 日志系统

5.4 Redo Log写入机制

Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

MySQL - 日志系统

如图所示：

  • write pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头；
  • checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；
  • write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint推进一下。

5.5 Redo Log相关配置参数

每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组至少有2个重做日志文件，默认为ib_logfile0和ib_logfile1。可以通过下面一组参数控制Redo Log存储：

show variables like '%innodb_log%';

Redo Buffer 持久化到 Redo Log 的策略，可通过 Innodb_flush_log_at_trx_commit 设置：

  • 0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数 据。由后台Master线程每隔 1秒执行一次操作。
  • 1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安 全，性能最差的方式。
  • 2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OS cache ->flush cache to disk 的操作。

一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据。 MySQL - 日志系统

6. 二进制日志 - binary_log

MySQL的二进制日志（binary log）是一个二进制文件， 主要记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的所有操作。 二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息， 但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。

默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看：

mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000001

6.1 binlog的使用场景

开启Binlog日志有以下重要使用场景

6.1.1 数据恢复（recovery）：

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

通过mysqlbinlog工具来恢复数据。

6.1.2 主从复制（replication）：

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

在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。

6.1.3 审计（audit）：

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

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

6.2 binlog配置

参考：dev.mysql.com/doc/refman/…

# 这个参数表示启用 binlog 功能，并指定 binlog 的存储目录
log-bin=/var/lib/mysql/mysql-bin.log
# 日志过期时间，设置为0则永不过期
expire_logs_days=7
# 复制模式
binlog_format=ROW
# 超过max_binlog_size或超过6小时会切换到下一序号文件
max_binlog_size=100M
# 二进制日志缓冲大小，通过show status like 'binlog_%'；查看调整写入磁盘的次数，写入磁盘为0最好
binlog_cache_size=16M
max_binlog_cache_size=256M
# 当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，
# 并且重新从master上获取日志，这样就保证了relay-log的完整性。
relay_log_recovery=1
# 控制 MySQL 服务器将二进制日志同步到磁盘的频率 - 在提交事务之前启用二进制日志到磁盘的同步
sync_binlog=1
# 每次事务提交将日志缓冲区写入log file，并同时flush到磁盘。
innodb_flush_log_at_trx_commit =1


# 为当前服务取一个唯一的 id（MySQL5.7 开始需要配置）
server-id=1

以下2个参数可以减少网络问题导致的主从数据同步延迟：

# 当slave从主数据库读取log数据失败后，等待多久重新建立连接并获取数据,默认3600秒
slave_net_timeout=5
#此选项为从库 change master to 的参数 - 当重新建立主从连接时，如果连接建立失败，间隔多久后重试
master-connect-retry=60

6.3 binlog日志记录格式 - binlog_format

binlog 有三种格式：

  • Statement（Statement-Based Replication, SBR）：基于语句的复制，每一条会修改数据的 SQL 都会记录在 binlog 中。

  • Row（Row-Based Replication, RBR）：基于行的复制，不记录 SQL 语句上下文信息，仅保存哪条记录被修改。

  • Mixed（Mixed-Based Replication, MBR）：Statement 和 Row 的混合体。

6.3.1 STATEMENT - SBR

在 MySQL 5.7.7 之前，默认格式是 STATEMENT。

Statement 模式下的 binlog 记录的是数据库执行的原生SQL语句。采用该格式的二进制日志文件最小，性能最高，主从复制网络带宽小，但很容易出现主从不一致的问题。

  • 优点

  1. 成熟的技术。

  2. 不需要记录每一行的变化，所以bin-log日志量相对较小，节约IO，提高性能。因为它只需要记录所执行的语句的细节，以及执行语句时的上下文信息。这也意味着可以更快地完成从备份中获取和恢复的工作。

  3. 日志文件包含进行任何更改的所有语句，因此它们可用于审计数据库。

  • 缺点
  1. 数据不一致问题 正是因为Statement只记录SQL，而不记录每一行的变换，而如果一些 SQL 中包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。

使用 Statement 的复制无法正确复制的语句会记录如下警告：

[Warning] Statement is not safe to log in statement format.

6.3.2 ROW - RBR

在 MySQL 5.7.7 及更高版本中，默认值为ROW。

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

  • 优点
  1. 所有更改都可以复制，这是最安全的复制形式。

由于 Row 格式下会清楚的记录每一行数据的修改细节，不会存在 Statement 格式下数据无法复制的情况。

  1. 对于以下类型的语句，从库需要更少的行锁，从而实现更高的并发性：

insert into ... select

使用了auto_increment（自增字段）的 insert 语句（这里指的是insert语句在对定义了自增字段的表执行插入数据时，不指定自增字段名和自增字段值，让其自动分配）。

在update或delete语句中，where条件字段未使用索引时，可能导致全表扫描，但大多数被扫描的行实际上都不会被修改，只有满足where条件值的行才会真正被修改。采用statement格式的二进制日志中记录的是原始sql语句，这时如果该语句无法使用索引，则会扫描并锁定全表的所有数据；而如果采用row格式，则二进制日志中记录的是逐行数据变更，从库在回放这些二进制日志时也逐行回放，不会锁住所有行。

这得益于从MySQL 5.6开始引入的一个新特性：在row格式下，如果表存在主键或唯一索引，那么可以通过特殊的优化算法找到能够唯一标志行的主键值或唯一索引值，从而避免对不需要修改的行加锁。查找算法由系统变量slave_rows_search_algorithms进行设置。

  • 缺点

生成更多的二进制日志数据，因为基于row的复制会将每行数据的变更都写入二进制日志。利用二进制日志进行备份和恢复的时间也会更长。此外，二进制日志的文件锁也会因为需要更长的时间来写入数据而被持有更久的时间，这可能会影响数据库的并发能力。可以使用系统变量binlog_row_image = minimal来减少二进制日志的写入量。

如果要生成大字段的BLOB值，使用基于row的复制比使用基于statement的复制耗费的时间更长，因为前者记录了BLOB字段的具体值，而不是生成数据的语句。

无法直接看到从库中执行的语句，但是可以使用mysqlbinlog工具的--base64-output= decode-rows和--verbose选项进行查看，或者在主库中启用系统变量binlog_rows_ query_log_events，它会在二进制日志中写入一个Rows_query_log_event类型的事件来记录原始的语句文本，可以使用mysqlbinlog工具的-vv选项来查看。

对于使用MyISAM存储引擎的表，当INSERT语句操作多行数据，在从库中重放该INSERT语句时，可能需要更多的表级锁，即在基于row的复制中，MyISAM引擎的并发性能会受到很大影响

6.3.3 MIXED - MBR

在 NDB Cluster 中，默认值为MIXED; NDB Cluster 不支持基于语句的复制。

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

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

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

MySQL 会在一些特定的情况下自动从 statement 格式切换到 row格式，默认采用statement格式。在以下情况下MySQL将会采用row格式：

  • a. 表的存储引擎为NDB，这时对表的DML操作都会以row格式记录 ；
  • b. 使用了uuid()、user()、current_user()、found_rows()、row_count()等不确定函数 ；
  • c. 使用了insert delay语句 ；
  • d. 使用了用户自定义函数UDF ；
  • e. 使用了临时表