MySQL的日志类型

原创

wx63647ca229299 2022-11-04 11:12:59 博主文章分类：MySQL ©著作权

文章标签 MySQL的日志类型 redo log undo log slow log bing log 文章分类 虚拟化云计算

©著作权归作者所有：来自51CTO博客作者wx63647ca229299的原创作品，请联系作者获取转载授权，否则将追究法律责任

重做日志（redo log）

作用

重做日志用来实现事务的持久性，即事务ACID中的D。其由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是易失的；二是重做日志文件（redo log file），其是持久的。

原理

InnoDB是事务的存储引擎，其通过Force Log at Commit机制实现事务的持久性，即当事务提交（COMMIT）时，必须先将该事务的所有日志写入到日志文件进行持久化，待事务的COMMIT操作完成才算完成。这里的日志是指redo log和undo log。redo log用来保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能。redo log基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作。而undo log是需要进行随机读写的。

参数innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略：

0：表示每次事务提交时不进行fsync操作，这个操作仅在master thread中完成，而在master thread中每1秒会进行一次重做日志文件的fsync操作，当系统挂掉会丢失一秒的数据。
1：表示每次事务提交时必须调用一次fsync操作，默认值是1，不会丢失数据，性能最差。
2：表示事务提交时仅写入文件系统的缓存中，不进行fsync操作，也就是只将文件从用户态转换成系统态，这样只要操作系统不挂掉，数据就不会丢失。

插入50万行记录，不同日志刷新策略的速度如下：

innodb_flush_log_at_trx_commit	执行时间
0	13.9秒
1	113.11秒
2	23.37秒

我们现在线上，金融类的服务设置的是1，其他服务设置的是2。

内容

是物理格式日志，其记录的是对于每个页的修改。

存储方式

在InnoDB存储引擎中，重做日志都是以512字节进行存储的。这意味着重做日志缓存、重做日志文件都是以块（block）的方式进行保存的，称之为重做日志块（redo log block），每块的大小为512字节。

重做日志格式：

redo_log_type	space	page_no	redo log body

redo_log_type：重做日志的类型。
space：表空间的ID。
page_no：页的偏移量。
redo log body：文件类容

文件位置

通过命令show variables like '%innodb_log_group_home_dir%';我们可以查询到文件位置：

mysql> show variables like '%innodb_log_group_home_dir%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_group_home_dir | .\    |
+---------------------------+-------+
1 row in set

.\表示数据库Data目录。

什么时候产生

在事务进行过程中不断的被写入，一个事务可能会有多条日志。

什么时候释放

当对应事务的脏页写入到磁盘之后，redo log的就会被清除，重做日志占用的空间就可以被重用。

重做日志是幂等的。

回滚日志（undo log）

作用

回滚日志主要用来解决事务的回滚和MVCC。

原理

undo也需要Force Log at Commit机制来保证undo log刷入磁盘。

内容

undo是逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

存储方式

InnoDB存储引擎对undo的管理同样采用段的方式，首先InnoDB存储引擎有rollback segment，每个回滚段种记录了1024个undo log segment，而在每个undo log segment段中进行undo页的申请。

文件位置

通过命令show variables like '%innodb_undo_directory%';我们可以查询到文件位置：

mysql> show variables like '%innodb_undo_directory%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_undo_directory | .\    |
+-----------------------+-------+
1 row in set

.\表示数据库Data目录，这个参数还可以修改。

什么时候产生

在数据被修改之前，undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。

什么时候释放

事务提交后并不能马上删除undo log及undo log所在的页，而只是将undo log 放入一个链表中，最终是否删除由purge线程来判断。

purge用于最终完成delete和update操作。这样设计是因为InnoDB存储引擎支持MVCC，所以记录不能在事务提交时立即进行处理。这时其他事物可能正在引用这行，故InnoDB存储引擎需要保存记录之前的版本。而是否可以删除该条记录通过purge来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真正的delete操作。

二进制日志（binlog）

作用

二进制日志主要用来进行POINT-IN-TIME（PIT 基于时间点的还原）的恢复及主从复制（Replication）环境的建立。

原理

重做日志是在InnoDB存储引擎层产生，而二进制日志是在MySQL数据库的上层产生的，并且二进制日志不仅仅针对于InnoDB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。

内容

二进制日志是一种逻辑日志，有三种格式，分别为：

Statement：基于 SQL语句级别的Binlog，每条修改数据的SQL都会保存到Binlog里。
Row：基于行级别，将每行数据的变化都记录到 Binlog 里面，并不记录原始 SQL; 在复制的时候, 并不会因为存储过程或触发器造成主从库数据不一致的问通，但是记录的日志量比Statement格式要大得多。
Mixed：混合Statement和Row模式，默认情况下采用Statement模式记录，某些情况下会切换到Row模式。

文件位置

通过命令show variables like '%log_bin_basename%';我们可以查询到文件位置：

mysql> show variables like '%log_bin_basename%';
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| log_bin_basename | /data2/mysql/xxx/mysql-bin |
+------------------+-------------------------------+
1 row in set

什么时候产生

二进制日志只在事务提交完成后释放锁之前，进行一次写入，一个事务只有一条日志，如果事务比较大，提交事务可能较慢。

什么时候释放

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除：

mysql> show variables like '%expire_logs_days%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 5     |
+------------------+-------+
1 row in set

binlog和redo log的区别

binlog和redo log本质上是不一样的：

redo log是存储引擎层产生，仅支持Innodb；binlog是mysql数据上层产生，支持所有存储引擎。
redo log是记录物理日志，记录的是页的物理修改；binlog记录的是逻辑日志，记录的是SQL语句。
redo log是在事务进过程中产生，日志内容并不是随着事务提交顺序写入；binlog是在事务提交完成后进行一次性的写入。
redo log主要所用是保证事务持久性；binlog主要用来所恢复和复制。

中继日志（relay log）

作用

relay log主要用来实现MySQL的复制功能。

复制原理

MySQL的日志类型_MySQL的日志类型

主服务器（master）把数据更改记录到二进制日志（binlog）中，然后通过binary log dump线程将二进制文件推送到从服务器。
从服务器（slave）通过I/O线程，把主服务器的二进制日志复制到自己的中继日志（relay log）中，中继日志通常会位于os缓存中，所以中继日志的开销很小。
从服务器通过SQL线程重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

从服务器有2个线程，一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL线程，复制执行中继日志。这里需要特别注意的是，复制是一个异步过程，从服务器数据存在延迟。

查看复制状态

查看当前的延迟，可以通过命令SHOW SLAVE STATUS和SHOW MASTER STATUS得知。

SHOW SLAVE STATUS:

mysql>SHOW SLAVE STATUS;
*************************** 1. row ***************************
      Slave_IO_State: Waiting for master to send event
        Master_Host: 192.168.190.10
        Master_User: rep
        Master_Port: 3306
      Connect_Retry: 60
      Master_Log_File: mysql-bin.000007
    Read_Master_Log_Pos: 555176471
      Relay_Log_File: gamedb-relay-bin.000048
      Relay_Log_Pos: 224355889
    Relay_Master_Log_File: mysql-bin.000007
      Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
  Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,DBA.%
        Last_Errno: 0
        Last_Error:
      Skip_Counter: 0
    Exec_Master_Log_Pos: 555176471
      Relay_Log_Space: 224356045
      Until_Condition: None
      Until_Log_File:
      Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
      Master_SSL_Cert:
    Master_SSL_Cipher:
      Master_SSL_Key:
    Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
        Last_IO_Errno: 0
        Last_IO_Error:
      Last_SQL_Errno: 0
      Last_SQL_Error:
1 row in set (0.00 sec)

主要参数说明：

MySQL的日志类型_slow log_02

SHOW MASTER STATUS:

mysql>SHOW MASTER STATUS;
*************************** 1. row ***************************
      File: mysql-bin.000007
    Position: 606181078
  Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec)

可以看到，当前二进制日志记录了偏移量606181078的位置，该值减去这一时间点时从服务器上的Read_Master_Log_Pos，就可以得知I/O线程的延时。

在考虑监控的时候，我们因该考虑从服务器的I/O线程和SQL线程监控；同时也需要考虑主从服务器之间的延迟。

错误日志（errorlog）

MySQL的错误日志用于记录MySQL服务在启动、关闭和运行过程中遇到的错误信息。可以直接在my.cnf中配置，也可以通过show variables like 'log_error%';命令查看。

mysql> show variables like 'log_error%';
+---------------------+------------------+
| Variable_name       | Value            |
+---------------------+------------------+
| log_error           | .\CD-HZTK5H2.err |
| log_error_verbosity | 3                |
+---------------------+------------------+
2 rows in set

文件内容：

2018-04-24T05:53:02.093532Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-04-24T05:53:02.093532Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2018-04-24T05:53:02.095758Z 0 [Note] C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe (mysqld 5.7.17-log) starting as process 7196 ...
2018-04-24T05:53:02.099268Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2018-04-24T05:53:02.099268Z 0 [Note] InnoDB: Uses event mutexes
2018-04-24T05:53:02.099268Z 0 [Note] InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier
2018-04-24T05:53:02.099268Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-04-24T05:53:02.099268Z 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 8 to 1 since innodb_buffer_pool_size is less than 1024 MiB
2018-04-24T05:53:02.099268Z 0 [Note] InnoDB: Number of pools: 1
2018-04-24T05:53:02.099268Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2018-04-24T05:53:02.099268Z 0 [Note] InnoDB: Initializing buffer pool, total size = 8M, instances = 1, chunk size = 8M
2018-04-24T05:53:02.099268Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-04-24T05:53:02.130523Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-04-24T05:53:02.199317Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-04-24T05:53:02.199317Z 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-04-24T05:53:02.230607Z 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2018-04-24T05:53:02.261912Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-04-24T05:53:02.261912Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-04-24T05:53:02.261912Z 0 [Note] InnoDB: Waiting for purge to start
2018-04-24T05:53:02.330562Z 0 [Note] InnoDB: 5.7.17 started; log sequence number 2535181
2018-04-24T05:53:02.330562Z 0 [Note] InnoDB: Loading buffer pool(s) from C:\ProgramData\MySQL\MySQL Server 5.7\Data\ib_buffer_pool
2018-04-24T05:53:02.330562Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-04-24T05:53:02.345812Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180424 13:53:02
2018-04-24T05:53:02.345812Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-04-24T05:53:02.345812Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2018-04-24T05:53:02.345812Z 0 [Note] IPv6 is available.
2018-04-24T05:53:02.361434Z 0 [Note]   - '::' resolves to '::';
2018-04-24T05:53:02.361434Z 0 [Note] Server socket created on IP: '::'.
2018-04-24T05:53:02.398707Z 0 [Note] Event Scheduler: Loaded 0 events
2018-04-24T05:53:02.398707Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 
2018-04-24T05:53:02.398707Z 0 [Note] Beginning of list of non-natively partitioned tables
2018-04-24T05:53:02.430064Z 0 [Note] End of list of non-natively partitioned tables
2018-04-24T05:53:02.430064Z 0 [Note] C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe: ready for connections.
Version: '5.7.17-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
2018-04-24T05:53:03.246194Z 3 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2018-04-24T12:03:13.235530Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6142555ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2018-04-24T12:19:42.998307Z 0 [Note] C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe: Normal shutdown
...

慢查询日志（slow query log）

SQL语句在数据库中执行超时时，会产生该日志，通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。

重要参数

注意：修改以下参数，需要重新启动数据库服务才会生效。
slow_query_log=off|on：是否开启慢查询日志。
slow_query_log_file=filename：指定保存路径及文件名，默认为数据文件目录。
long_query_time=2：指定多少秒返回查询的结果为慢查询。
long-queries-not-using-indexes：记录所有没有使用到索引的查询语句。
min_examined_row_limit=1000：记录那些由于查找了多余1000次而引发的慢查询。
long-slow-admin-statements：记录那些慢的optimize table，analyze table和alter table语句。
log-slow-Slave-statements：记录由Slave所产生的慢查询。

通过show variables like '%slow_query_log%'可以查看参数值。

实战：

show variables like '%slow_query_log%'; #  查看慢日志参数
SET GLOBAL  slow_query_log = 1; # 打开慢日志
SET long_query_time=0.000001; # 设置超时时间
SELECT * FROM `user` WHERE `name` = 'wyf'; #执行查询

慢日志文件：

# Time: 2020-05-06T09:36:02.242452Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.001961  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1588757762;
SELECT * FROM `user` WHERE `name` = 'wyf';

# Time: 2020-05-06T09:36:02.257462Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.001041  Lock_time: 0.000000 Rows_sent: 16  Rows_examined: 315
SET timestamp=1588757762;
SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/0.002464*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=301 GROUP BY STATE ORDER BY SEQ;

通过慢日志工具 mysqldumpslow可以对慢日志进行分析：

常用慢查询分析工具

mysqldumpslow（结果简陋不方便阅读分析）
mysqlsla
percona-toolkit中的pt-query-digest

一般查询日志（general log）

当客户端连接数据库执行SQL语句时会产生该日志。

DDL 日志（metadata log）

执行DDL语句时会产生该日志。

总结

日志类型	说明	默认名称	文件选项	开启选项	产生时间	释放时间
General query log	记录从客户端获取到的连接和语句	host_name.log	general_log_file=file_name	general_log	在服务器接收到客户端发来的命令时就写入到日志中
Slow query log	记录超过long_query_time时间的查询	host_name-slow.log	low_query_log_file=file_name	slow_query_log	在语句执行完毕，释放锁之后写入到日志中
Binary log	记录修改数据的语句	pif-file-bin	log_bin=base_name	log-bin	在语句执行完毕，释放锁之前写入到日志中	到期自动删除
Error log	记录在mysqld服务启动、运行或停止时遇到的问题	host_name.err	log_error=file_name	log-error
Relay log	在复制结构中，从库从主库获取到的数据修改	hostname-relay-bin.nnnnnn	relay-log=file_name	不需要额外开启	I/O线程写入	SQL线程重做后删除
DDL log	DD语句执行的元数据操作	ddl_log.log	无法修改	不需要额外开启	执行DDL语句时
redo log	重做日志	.\			不需要额外开启	事务进行时
undo log	回滚日志	.\			不需要额外开启	修改数据之前