重做日志（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的复制功能。

复制原理

image.png

主服务器（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)

主要参数说明：

image.png

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	回滚日志	.\		不需要额外开启	修改数据之前	由purge线程决定

MySQL的日志类型

MySQL的日志类型

重做日志（redo log）

作用

原理

内容

存储方式

文件位置

什么时候产生

什么时候释放

回滚日志（undo log）

作用

原理

内容

存储方式

文件位置

什么时候产生

什么时候释放

二进制日志（binlog）

作用

原理

内容

文件位置

什么时候产生

什么时候释放

binlog和redo log的区别

中继日志（relay log）

作用

复制原理

查看复制状态

错误日志（errorlog）

慢查询日志（slow query log）

重要参数

常用慢查询分析工具

一般查询日志（general log）

DDL 日志（metadata log）

总结