mysql日志相关总结笔记

bangiao
190 阅读10分钟

如果mysql执行错误了怎么办?

可以查看错误日志, 通常是系统名字.err可以使用

SHOW VARIABLES LIKE 'log_error';

log_error   D:\programs\env\mysql-8.0.28-winx64\data\DESKTOP-XXXXXXX.err

所以我们可以去看看错误信息

image.png

比如原书中的例子:

image.png

image.png

比如上面的案例, 就希望用户提高log的大小

如果某个请求很慢怎么办?

问题可能出在各个方面, 比如网络问题, IO问题等

这里我们只讲mysql导致的慢查问题

可以查看mysql慢查日志, 不过通常慢查日志是被监控的, 如果查询速度较慢都会通过信息通知运维人员

比如我们可以监控mysql慢查日志, 每天定时通过钉钉机器人通知企业人员昨天的慢查sql日志

show variables like 'long_query_time'

long_query_time 10.000000

(10, 无限大], 不包括 10 秒

查看mysql慢查阈值, 多少秒的sql才会进入慢查日志

show variables like 'slow_query_log%'

查看慢查日志功能是否启动和慢查日志目录

slow_query_log  OFF
slow_query_log_file D:\programs\env\mysql-8.0.28-winx64\data\DESKTOP-FOS9FAS-slow.log

show variables like 'log_queries_not%';

用来记录未使用索引的慢查sql

log_queries_not_using_indexes   OFF

show variables like 'log_throttle_queries_not%';

记录每分钟未使用索引慢查sql的几率频率, 10表示每分钟只能记录10条sql, 0则不限制

log_throttle_queries_not_using_indexes  0

当慢查日志特别多怎么办?

使用mysqldumpslow命令

mysqldumpslow + *-slow.log

记住它不是mysql的指令, 而是工具类似于mysqld, 请在控制台上执行该指令, 不要登录mysql咯

image.png

mysqldumpslow -s al -n 10 *.log

获得执行时间最长的10条sql

mysql.slow_log表

从mysql5开始, mysql将慢查日志存放到了一张表中

image.png

但是这张表的引擎是CSV效率可能不高, 可以改成myISAM

ALTER TABLE mysql.slow_log ENGINE=MyISAM;
-- 如果执行失败
set global slow_query_log=off;
ALTER TABLE mysql.slow_log ENGINE=MyISAM;

mysql二进制日志(binary log)

二进制日志是什么?

二进制日志记录DML的所有指令, 比如 insert update 和 delete对数据库可能有修改的指令 , 但是不记录 select show

可能有修改: 可能是指update t set a = 1 where a = 2但a=2是不存在的, 所以它并没有修改任何值, 但是它也会被记录到二进制日志中

如果用户需要看到select 和 show, 那么需要去看 查询日志

二进制日志解决了什么问题?

  • 恢复: 某些数据需要二进制日志恢复数据
  • 复制: 主从复制
  • 审计: 判断日志中的sql是否有注入攻击分险

既然有恢复和复制, 那肯定会对性能有所影响, 但这也是一种最优的选择

事务和二进制日志

每当事务未提交之前，原本应该记录在二进制日志中的SQL语句会被存储到缓存中。

当事务提交时，这些SQL语句会被同步到二进制日志中。

这个缓存的大小通常为32KB，可以进行修改，但不建议设置得过大，因为可能会有很多事务存在。

然而，这样做也会带来新的问题，即当MySQL宕机时，缓存数据可能会丢失。

我们可以通过设置sync_binlog=N来解决这个问题，默认值为0。如果将其设置为1，表示每次写入缓存时都立即同步到磁盘。然而，实际上不是这样的，如果设置为1，则表示不使用缓冲，而是直接将数据写入二进制日志中。

这种方案相对来说更加可靠，也就是所谓的“双1方案”中的其中一个1。

sync_binlog=1的问题

我们知道，默认情况下，当事务未提交时，数据会被写入缓存中，而在事务提交时，数据会被写入到二进制日志中。

现在，如果你将其改为1，则表示不使用缓存，直接将值写入到二进制日志中。

这就引发了一个问题：即使事务还未提交，但SQL语句已经被写入了二进制日志。如果此时事务回滚，二进制日志中的内容不会回滚，导致该文件中的内容与实际情况不符合。

解决方法

配置 innodb_support_xa=1来解决问题

redo log 在事务未提交前就已经开始记录sql执行的数据了, 在redo log被叫做准备阶段, 然后在事务提交之后redo log将变为 已提交阶段

redo log是针对页的记录, 将数据的变化保存在页中

redo log由两个部分组成的, 一个是 redo log buffer, 容易丢失, 另一个是 redo log文件, 是磁盘文件

既然都是磁盘文件, 为什么还要有 redo log文件?

因为redo log 是顺序读写的, 而mysql的磁盘存储是随机的

mysql的数据修改先强制记录日志再保存磁盘的

也就是说当事务提交时, 至少已经写入到redo log文件中了, 在遇到checkpoint之后才会保存到磁盘中

所以redo log保证了事务的持久性

redo log 什么时候刷盘？

主要有下面几个时机：

  • MySQL 正常关闭时；
  • 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
  • InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
  • 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制）。

image.png

innodb_flush_log_at_trx_commit 参数控制的是什么？

值有：0、1、2，默认值为 1，这三个值分别代表的策略如下：

  • 当设置该参数为 0 时，表示每次事务提交时 ，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。
  • 当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。
  • 当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，Page Cache 是专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存。

Page cache 的一个功能是会自动将写入到cache中的数据持久化到磁盘中, 即便mysql进程崩溃

  • 数据安全性：参数 1 > 参数 2 > 参数 0
  • 写入性能：参数 0 > 参数 2> 参数 1

Q: innodb_flush_log_at_trx_commit 为 0 和 2 的时候，什么时候才将 redo log 写入磁盘？

A:

  • 针对参数 0 ：会把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失;
  • 针对参数 2 ：调用 fsync，将缓存在操作系统中 Page Cache 里的 redo log 持久化到磁盘。所以参数为 2 的策略，较取值为 0 情况下更安全，因为 MySQL 进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

undo log

undo log 是一种用于撤销回退的日志。在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚

undo log是逻辑日志, 不是redo log的针对页的日志

因为一个页不一定只有一条记录, 而是多条记录, 事务操作的可能是页中的某几条记录, 所以不能像redo log一样对一个页进行回滚, 而是对sql执行的撤销

相当于你执行了delete, 那么undo log 就会生成一个 insert, 如果出现问题回滚, 就执行insert

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

  • 通过 trx_id 可以知道该记录是被哪个事务修改的；
  • 通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；

image.png

MVCC由undo log提供支持的

undo log通过 ReadView + undo log 实现 MVCC（多版本并发控制）

在事务的不同隔离级别中, ReadView的创建时机是不同的

  • 读已提交: 每次select 都会生成一个 ReadView, 在这种情况下, 如果有个事务提交了, 然后你再去select读取到的数据将是不同的
  • 可重复读: 在事务被创建时也将创建一个ReadView, 然后事务的所有操作都将在一个ReadView中进行

值得注意的是undo log也需要redo log进行持久化

redo log和undo log的区别

两个log的区别

  • redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
  • undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务

bin log

binlog是MySQL的逻辑日志，用于记录数据库执行的写入性操作（不包括查询）信息，以二进制的形式保存在磁盘中。binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

bin log用于主从同步或通过 mysqlbinlog 工具来恢复数据

image.png

  • MySQL的主服务器（主库）在接收到客户端提交事务的请求后，会先将变更记录写入binlog（二进制日志），然后再提交事务，更新存储引擎中的数据。一旦事务提交完成，主库会向客户端返回“操作成功”的响应。

    从服务器（从库）会创建一个专门的输入/输出（I/O）线程，与主库的日志传输（log dump）线程建立连接，用来接收主库的binlog日志。然后，从库会将接收到的binlog信息写入中继日志（relay log）。同时，从库会向主库返回“复制成功”的响应。

    从库还会创建一个用于回放binlog的线程，它会读取中继日志中的数据，并将其中的binlog逐个应用到存储引擎中，从而实现主从服务器之间数据的同步和一致性。

数据库为什么需要主从复制?

主要的目的还是读写分离, 单个mysql实例在大业务中单体系统的CPU资源承受不住, 导致响应时间变慢, 所以需要再来几个实例分担

而第一个想到的方法是读写分离, 将业务的读操作划分给从库, 将业务的写操作划分给主库

然后你才会想到分库分表, 分库分表比较麻烦, 带来的问题可能更加的多, 需要慎重考虑

合理分割微服务, 每一个微服务一个数据库, 这样会好些

至于微服务怎么分割合理, 我们可以使用微服务分割利器 DDD 的限界上下文

avatar
文章
阅读
粉丝