一文读懂MySQL7大日志（slow、redo、undo、bin、relay、general、error）

迪姆卡特阿诺

Slow Log

简介

用于记录执行时间超过指定值的 SQL 语句的详细信息，多用于调试和监控。
配置

因为开启会略微影响性能，所以默认没有开启，所以需要配置。

1. 查看是否开启
2. show variables like '%slow%';
3. +---------------------+-------------------------------------+
4. | Variable_name       | Value                               |
5. +---------------------+-------------------------------------+
6. | slow_launch_time    | 2                                   |
7. | slow_query_log      | OFF                                 |
8. | slow_query_log_file | .../slow.log                        |
9. +---------------------+-------------------------------------+
11. 若没有开启，在命令行执行，注意给足文件所在目录的权限。
12. set global slow_query_log = on;
13. set global slow_launch_time= 1;
14. set global slow_query_log_file = /var/log/mysql/slow.log;
16. 想要永久生效，可在/etc/my.cnf中写入，注意给足文件所在目录的权限。
17. slow_query_log = 1
18. slow_query_log_file = /var/log/mysql/slow.log
19. long_query_time = 2
23. 打开日志验证：
24. # Time: 2024-02-21T12:18:10.954969Z
25. # User@Host: root[root] @ localhost [::1]  Id:     3
26. # Query_time: 20.001907  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
27. SET timestamp=1708517890;
28. select sleep(20);

复制代码

Redo Log

简介

重做日志，记录了数据库中发生的每次修改，如增、删、改、对数据页的更改。这些修改被记录在 redo 日志中，以便在数据库崩溃或意外关闭时能够恢复到最近的一致状态。
解决了什么问题

在宕机或者断电等其它异常的情况下，确保数据的一致性，持久性，增加高可用性。
必须提前知道一些背景：读数据页时，需要把磁盘上的页缓存到内存中的buffer pool中，所有的变更，也是先更新缓冲池，此时并没有持久化到磁盘，称之为脏页，然后脏页通过checkpoint机制去刷盘。
如果实时刷盘，代价太大，用户改了1字节的数据，都需要整页刷盘，如果用户改了大量的数据，就需要立即同步到多个页上，随机io性能低下。所以采用了非实时刷盘的策略。
假定一秒刷一次盘，第0.9秒时机器断电或被强制停止，这0.9秒的进度在内存中就丢失了，为了保证高可用，所以MySQL采用redo log的方式来保证这0.9秒的进度不丢失。
InnoDB采用的是WAL技术，在事务提交时先写入日志（Redo Log）记录事务所做的修改操作，再写磁盘，如果期间断电，下次启动时，也可以使用redo log来恢复数据进度。
注意上面的事务提交，不仅仅是显式声明的事务，只要auto commit已打开，每个单独的DML语句（增、删、改）通常会被视为一个独立的事务。
写redo log的代价比同步脏页到磁盘的代价要小。
配置

查看内存上的重做缓冲大小（默认16MB，最大4096MB，最小1MB）：
select @@innodb_log_buffer_size; 或 show variables like 'innodb_log_buffer_size';
设置内存上的重做缓冲大小（低版本只读，不支持修改）：
set global innodb_log_buffer_size = 16777216 ;
也可在配置文件中
[mysqld]
innodb_log_buffer_size = 16777216
架构

组成：

redo log可以分为两部分，内存上的的重做日志缓冲，和磁盘上的重做日志文件。
磁盘上的重做日志在设置的datadir目录下，通常叫做ib_logfile0，ib_logfile1，两个大小一致的文件（默认48MB）。
执行流程，假设执行update语句：

• 先写redo log到redo log buffer。
  
• 更新内存中的数据页（改的是SQL，不是redo）。
  
• 事务提交时，用追加写的方式，将内存中的重做日志缓冲，写入重做日志（改的是redo，不是SQL）。
  
• 通过一些checkpoint策略，定期将内存中修改的update数据刷新到磁盘（改的是SQL，不是redo）。
  

从redo log buffer到 redo log file的刷盘策略

从redo log写入磁盘，MySQL提供了一个线程来处理redo log 的写入操作，可以减少对主线程的影响。
通过 innodb_flush_log_at_trx_commit配置来变更刷盘策略，默认为1，安全性最好，性能最差。
0 ：不管事务是否提交，每隔1秒将redo log buffer写入到 redo log file。
1 ：每次事务提交时，都将redo log buffer写入到 redo log file。
2 ：每次事务提交时，只把redo log buffer写入操作系统的page cache（page cache的控制权是操作系统，page cache也是内存，此举是让操作系统自主控制从内存刷入磁盘的动作），这个阶段MySQL的任务完成了，所以MySQL挂了不会丢数据，服务器系统挂了会丢失数据。
注意，修改这个值为0，会极大提高MySQL InnoDB引擎的写入速度，适合对对事务持久性要求较低的场景。
用插入数据实测（不想开虚拟机，用的windows），改成0的速度比改成2快2倍，比改成1快57倍。
Undo Log

简介

回滚日志，用于记录事务所做的更改，以便在事务回滚或发生回滚操作时能够撤销事务中的修改。
如果使用undo log回滚，这个回滚的动作，也会产生redo log，用于保证数据的高可用。
解决了什么问题

如果一个事务执行了一部分，突然SQL执行出错了，进程被强制干掉了，断电了，或者回滚，这种情况下没有undo log，则无法完成原子性回滚操作。
提供MVCC的支持。
架构

组成：

日志通常在ibdata1文件里。
相关配置：
innodb_undo_directory: undo日志文件的存储路径，默认为 ./，表示与数据文件存储在同一目录下。
innodb_undo_tablespaces: undo表空间的数量，默认为 2。每个undo表空间包含多个undo日志文件，可以根据需要增加或减少undo表空间的数量。
innodb_max_undo_log_size: 单个undo日志文件的最大大小，默认为 10MB。可以根据实际需求调整此参数，适当增大以减少 UNDO 日志文件的频繁创建和删除。
innodb_undo_logs: UNDO 日志的数量，默认为 128。可以根据系统负载和事务频率适当增加undo日志的数量，以提高并发处理能力。
innodb_undo_log_truncate: 控制是否开启 undo日志的截断功能，默认为 on。开启后，可以定期截断旧的undo日志，释放空间并提高性能。
执行流程，假设执行update语句：

• 定位要更新数据，将其加入到缓冲池中。
  
• 将旧的数据写入undo log，为数据回滚做准备。
  
• 更新缓冲池中的数据（SQL）。
  
• 随后记录redo log，走redo log那套逻辑。
  

Bin Log

简介：

二进制日志，记录所有DDL、DML的日志，用于主从服务器之间的数据同步。
也可用于MySQL意外停止情况下的数据恢复。
解决了什么问题

没它做不了主从复制。
binlog做数据备份与恢复
配置

1. 查看相关配置
2. show variables like 'log_bin%';
4. +---------------------------------+-------+
5. | Variable_name                   | Value |
6. +---------------------------------+-------+
7. | log_bin                         | OFF   |
8. | log_bin_basename                |       |
9. | log_bin_index                   |       |
10. | log_bin_trust_function_creators | OFF   |
11. | log_bin_use_v1_row_events       | OFF   |
12. +---------------------------------+-------+
14. 各个字段含义：
15. log_bin：是否启用binlog。
16. log_bin_basename：二进制日志文件目录。
17. log_bin_index：二进制日志索引文件的路径。
18. log_bin_trust_function_creators：是否信任二进制日志中的函数创建者，例如在主机上执行now();和从机上执行now();有一些时间误差。
19. log_bin_use_v1_row_events：值为0表示正在使用版本2的行事件格式，值为1表示正在使用版本1的行事件格式。版本2比版本1提供了更好的性能和更好的可读性。
21. 推荐直接改配置文件，以下简单的几行配置就可以，如果需更多的自定义配置，可以再添加
22. vim /etc/my.cnf
24. [mysqld]
25. server_id=1           设置 MySQL 服务器唯一 ID，每个服务器要求唯一的 ID
26. log_bin=mysql-bin     启用二进制日志并设置日志文件名
27. binlog_format=ROW     设置二进制日志格式
28. expire_logs_days=7    设置二进制日志过期时间，单位为天
30. 保存完后重启，然后在data目录就有mysql-bin.000001文件了。

复制代码

使用binlog恢复数据

• 根据时间恢复（用当前时区就好）：
  mysqlbinlog --start-datetime="开始时间年月日时分秒" --stop-datetime="结束时间年月日时分秒"  binlog文件 | mysql -u用户名 -p密码 -v 数据库名
  可加--database=数据库名，限制数据库
  
• 按偏移量恢复（不建议用，pos偏移量不容易定位）：
  查看偏移量：show binlog events in "binlog文件";
  mysqlbinlog --start-position=偏移量数字 --stop-position=偏移量数字 binlog文件 | mysql -u用户名 -p密码 -v 数据库名
  可加--database=数据库名，限制数据库。
  

日志操作

• 新增：每重启一次，就会新增一个binlog文件。
  
• 删除（按文件名）：purge master logs to 'binlog文件名' 删除的是小于此编号名称的binlog文件。
  
• 删除（按时间）：purge master logs before 'YYYY-MM-DD HH:II:SS' 删除的是小于此编号名称的binlog文件。
  
• 修改：直接运行DDL或DML语句吧。
  
• 查看：show binary logs;
  

两阶段提交

redo log事务执行时就会写入，binlog事务提交时才会写入，写入的时机不一样，小概率事件，会造成数据不一致的问题。假如写完redo log后bin log写入异常，这可能导致主机数据正常，但是从机数据不一致。
可以使用两阶段提交的方式，就是说写入bin log前后分别进行redo log写入，但是redo log明明只需写入一次就够了，两次又怎么处理呢？
prepare redo log阶段会写入日志，然后等bin log执行完毕后，在commit redo log阶段判断 bin log是否写入成功，如果没有写入成功，回滚redo log，重新走以上流程。
Relay Log

简介：

MySQL的主从复制，主服务器会把自己的binlog发送到从服务器。从服务器会将接收到的binlog后，会写入自己的中继日志中，然后再应用到自己的数据库中。
相当于快递送到了驿站。
中继日志只有从服务器有，默认在data目录下。
General log

简介：

普通日志，记录日常执行过的命令，默认关闭，不建议开启，因为每执行一个SQL就记录一条日志，占空间还降低性能。
若真想做SQL的调试，推荐使用编程框架的机制，自定义封装一个日志功能，因为MySQL日志属于运维层，框架日志属于开发层，更容易自定义和接触的到。
解决了什么问题

配合业务做数据库调试使用。
配置

1. 查看相关状态
2. show variables like 'general_log%'
4. +------------------+--------------------------------+
5. | Variable_name    | Value                          |
6. +------------------+--------------------------------+
7. | general_log      | OFF                            |
8. | general_log_file | .... /你的用户名.log           |
9. +------------------+--------------------------------+
11. 临时修改全局配置
12. set global general_log = on;
13. set global general_log_file = path;
15. 持久化配置
16. vim /etc/my.cnf
18. [mysqld]
19. general_log = 1
20. general_log_file = /var/log/mysql/mysql.log
22. 示例日志如下，格林尼治时间
23. 2024-03-04T18:07:35.824760Z            8 Query        select * from cs
24. 2024-03-04T18:08:27.707305Z            8 Query        select * from cs
25. 2024-03-04T18:08:28.136048Z            8 Query        select * from cs
26. 2024-03-04T18:08:28.477068Z            8 Query        select * from cs

复制代码

Error log

简介

错误日志就是出错的时候出现的日志，对于异常排查，十分有用。
解决了什么问题

运维，服务器管理人员，做排查使用。
配置

1. show variables like 'log_error%';
3. +---------------------+--------------------------------+
4. | Variable_name       | Value                          |
5. +---------------------+--------------------------------+
6. | log_error           | ... /你的用户名.err            |
7. | log_error_verbosity | 3                              |
8. +---------------------+--------------------------------+
9. log_error_verbosity是错误的等级
10. 1：记录错误信息，但不包含错误堆栈跟踪。
11. 2：记录错误信息，并包括简短的错误堆栈跟踪。
12. 3：记录错误信息，并包括完整的错误堆栈跟踪和源代码行号。
14. 内容如下：
15. 2023-01-02T06:57:05.481440Z 0 [Note] ...mysqld (mysqld 5.7.24) starting as process 11088 ...
16. 2023-02-18T15:51:49.645048Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
17. 2023-02-18T15:51:49.645900Z 0 [Note] ...mysqld (mysqld 5.7.24) starting as process 4924 ...

复制代码

补充：

Redo Log对比Undo Log

作用差不多，但是侧重点不同。
redo log：偏向物理操作的日志，比如针对页的操作，保证事务的持久性。
undo log：偏向逻辑操作的日志，记录的是每个增、删、改的逆向操作，以便于事务回滚，保证事务的原子性。
Redo/Undo Log对比Bin log

Redo log对比Bin log，都有持久化的功能，但是侧重点不一样，redo log偏向回滚的备份，bin log偏向主从赋值，DML、DDL操作的恢复。
redo log事务执行时就会写入，binlog事务提交时才会写入。
redo和undo，对比bin，前者是为了保证高可用，防止进度丢失，后者也是为了数据备份，但是也偏向主从复制。
Redo log，与DML语句结果刷盘顺序？

先写redo日志到磁盘，在把sql执行后要写入的数据写入到磁盘中。
如果Redo log准备写入磁盘时断电，会导致进度丢失吗？

这会导致当前执行的sql无效， 这个过程SQL 语句的修改结果，并未写入到磁盘。
所以为了保证高可用，推荐UPS不间断电源，通过硬件层面的加持，达到高可用。

来源:https://www.cnblogs.com/phpphp/p/18062505
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！