Mysql之日志

一、日志类型

MySQL中的日志主要分为两种类型：Server层的日志和引擎层的日志（引擎层日志本文主要介绍InnoDB引擎日志）

Server层日志

Error Log （错误日志）：记录MySQL Server运行时出现的错误和警告。
Binary Log （二进制日志）：记录了MySQL Server层执行的所有修改操作。
Relay Log （中继日志）：主从复制过程中使用的一种日志类型，在从服务器上记录主服务器上所有的二进制日志（Binary log）的信息。
DDL Log ：记录DDL操作的一种日志类型。
General Log（查询日志）：记录MySQL Server层的所有查询语句。
Slow Query Log （慢查询日志）：记录执行时间过长的查询语句。

InnoDB引擎日志（事务日志）

Redo Log：记录事务过程中的修改操作，以保证事务的安全性。
Undo Log：用于撤销与事务相关的修改操作，以保证事务的原子性。

比较重要是二进制日志binlog（二进制日志）和事务日志redo log（重做日志）和undo log（回滚日志）。

二、binlog

binlog是数据库server层实现的，所以任何存储引擎都能使用binlog。不管用什么存储引擎，只要发生了表数据更新，都会产生binlog日志。
binlog是追加写入的，可以一直增加
binlog是没有故障恢复能力的

刷盘机制

binlog在内存中会对应一个binlog cache的区域（每个线程对应一个），当sql语句（修改表内容的语句，查询类语句是不记录的）执行时就会把语句逻辑写入到binlog cache中，当事务提交的时候MySQL会把binlog cache中的内容写入磁盘，一个事务的binlog是不能被拆分的，不论事务多大也要确保事务一次性写入磁盘，这意味着binlog中的语句是按一个个事务排好的，不存在事务语句相互穿插的情况。

在事务提交时进行日志刷盘的操作其实分为两个步骤，

第一步叫做write是把MySQL的binlog cache的内容写到操作系统的文件缓冲区，这个过程实际上是内存操作。
第二步叫做fsync，这才是真正把日志内容持久化到磁盘的操作，涉及到磁盘IO，执行速度较慢。

使用sync_binlog参数可以控制write和fsync的时机：

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；（不建议，断电后丢失日志）

sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；（最多丢失一个事务日志，每次提交都持久化速度慢）

sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。（主机断电会丢失最近N个事务的binlog日志，需要在风险和速度之间平衡）

我们还需要注意，每个线程的binlog cache是有限的，当事务很大缓存放不下时，会将放不下的部分存在临时文件中，临时文件也是每个线程一个。binlog文件是所有线程共享的。

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

三、redo log
redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

Buffer Pool、redo log buffer

MySQL中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。
后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。
更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。
然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

什么时候刷盘

redo log在innoDB的内存空间里也有一个redo log buffer的区域先行记录日志内容，当有数据更新时，直接在内存数据页上进行更新，并在redo log buffer中写下日志信息。
再在合适的时候把redo log buffer中的内容写入磁盘。这个写磁盘的操作和binlog一样也分为两步，write和fsync，具体过程见上一节。

我们接着说这个合适的时候，什么时候是什么时候呢？实际上有这么几种情况

一个叫做Master Thread的线程每秒会进行一次写盘操作

每次事务提交的时候可能会执行一次写盘操作（innodb_flush_log_at_trx_commit参数决定)；
设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;（MySQL异常重启会丢失）
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；（最稳妥）
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到操作系统缓存。（主机断电会丢失）

内存中redo log buffer剩余空间小于一半的时候会进行一次写盘操作。
redo log就没有binlog中那样每个事务排列在一起不存在事务之间交叉的性质，redo log只关注脏页不关注事务，所以刷新的时候可能是不同事务更新的数据相互穿插。

四、undo log

undo log（回滚日志）

用于记录数据被修改前的信息，作用包含两个:提供回滚和 MVCC(多版本并发控制)。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo og中会记录一条对应的inset记录，反亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rolback时，就可以从undo og中的逻辑记录读取到相应的内容并进行回滚。
Undo log销毁: undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
Undo log存储: undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含1024个undo l0segment。

五、两阶段提交

redo log和binlog分别在什么时候提交

redo log（重做日志）让InnoDB存储引擎拥有了崩溃恢复能力。

binlog（归档日志）保证了MySQL集群架构的数据一致性。

虽然它们都属于持久化的保证，但是则重点不同。

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

面临的问题
假设执行过程中写完redo log日志后，binlog日志写期间发生了异常，会出现什么情况呢？

由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。因此，之后用binlog日志恢复数据时，就会少这一次更新，最终数据不一致。
为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。

六、慢日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

long_query_time的默认值为10，意思是运行10秒以上的语句
由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前explain进行全面分析
默认情况下，MySQL数据库没有开启慢查询日志。

查看慢查询日志是否开以及如何开启

查看慢查询日志是否开启：
SHOW VARIABLES LIKE '%slow_query_log%';。
开启慢查询日志：
SET GLOBAL slow_query_log = 1;
使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。

-- 指定数据库
mysql> use advanced_mysql_learning;
Database changed

--  查看慢查询日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+---------------------------------------------------------------------------+
| Variable_name       | Value                                                                     |
+---------------------+---------------------------------------------------------------------------+
| slow_query_log      | OFF                                                                       |
| slow_query_log_file | D:\Development\Sql\Mysql\mysql8\exe\mysql-8.0.27-winx64\data\dam-slow.log |
+---------------------+---------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

--  开启慢查询日志
mysql> SET GLOBAL slow_query_log = 1;
Query OK, 0 rows affected (0.01 sec)

如果要使慢查询日志永久开启（不推荐，浪费性能），需要修改my.cnf文件，在[mysqld]下增加修改参数。

# my.cnf
[mysqld]
# 开启慢查询
slow_query_log=ON  
# 指定存储慢查询日志的文件。如果这个文件不存在，会自动创建
slow_query_log_file=/var/lib/mysql/slow.log

设置慢SQL的时间阈值

时间阈值是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。

MySQL中查看long_query_time的时间：
SHOW VARIABLES LIKE 'long_query_time%';。

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

--  设置阈值
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

--  可以发现设置没有成功
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

查询慢查询日志文件中的总记录条数

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

日志分析工具mysqldumpslow

# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log
# 另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more

s：是表示按照何种方式排序:
c：访问次数
l：锁定时间
r：是表示按照何种方式排序:
c：访问次数
1：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：即为返回前面多少条的数据:
g：后边搭配一个正则匹配模式，大小写不敏感的:是表示按照何种方式排序:

原文链接

https://zhuanlan.zhihu.com/p/609972086
https://blog.csdn.net/qq_40687433/article/details/112540401
https://blog.csdn.net/laodanqiu/article/details/131423834

posted @ 2024-03-19 15:51 *一炁化三清* 阅读(15) 评论(0) 编辑收藏举报

刷新页面返回顶部

OpenSource

Mysql之日志

公告