Mysql学习（八）-- mysql日志

leo_messi94

已于 2023-04-16 18:00:15 修改

阅读量766

点赞数

分类专栏： mysql 文章标签： mysql 数据库学习

于 2023-04-14 18:10:21 首次发布

本文链接：https://blog.csdn.net/weixin_39724194/article/details/130159318

版权

mysql 专栏收录该内容

25 篇文章 3 订阅

订阅专栏

1. Mysql日志类型：

错误日志（error log）
- error log主要记录MySQL在启动、关闭或者运行过程中的错误信息，在MySQL的配置文件my.cnf中，可以通过log-error=/var/log/mysqld.log 执行mysql错误日志的位置。
慢查询日志（slow query log）
- MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
- long_query_time的默认值为10，意思是运行10秒以上的语句。
- 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。
- 默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
- 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
- 在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
一般查询日志（general log）
- general log 记录了客户端连接信息以及执行的SQL语句信息，通过MySQL的命令
重写日志（redo log）
回滚日志（undo log）
二进制日志（bin log）

1.2 bin log作用：

MySQL的bin log日志是用来记录MySQL中增删改时的记录日志。
当你的一条sql操作对数据库中的内容进行了更新，就会增加一条bin log日志。查询操作不会记录到bin log中。
bin log最大的用处就是进行主从复制，以及数据库的恢复。

1.3 redo log作用：

redo log是一种基于磁盘的数据结构，用来在MySQL宕机情况下将不完整的事务执行数据纠正，redo日志记录事务执行后的状态。
当事务开始后，redo log就开始产生，并且随着事务的执行不断写入redo log file中。redo log file中记录了xxx页做了xx修改的信息，我们都知道数据库的更新操作会在内存中先执行，最后刷入磁盘。
redo log就是为了恢复更新了内存但是由于宕机等原因没有刷入磁盘中的那部分数据。

1.4 undo log作用：

undo log主要用来回滚到某一个版本，是一种逻辑日志。
undo log记录的是修改之前的数据，比如：当delete一条记录时，undolog中会记录一条对应的insert记录，从而保证能恢复到数据修改之前。在执行事务回滚的时候，就可以通过undo log中的记录内容并以此进行回滚。
undo log还可以提供多版本并发控制下的读取（MVCC）。

2. Mysql日志写入策略：

磁盘写入固然是比较慢的。

2.1 bin log的写入：

参数：sync_binlog

sync_binlog=0 的时候，表示每次提交事务binlog不会马上写入到磁盘，而是先写到page cache,相对于磁盘写入来说写page cache要快得多,不过在Mysql 崩溃的时候会有丢失日志的风险。
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync 写入到磁盘；
sync_binlog的值大于1 的时候，表示每次提交事务都先写到page cach，只有等到积累了N个事务之后才fsync 写入到磁盘，同样在此设置下Mysql 崩溃的时候会有丢失N个事务日志的风险。

很显然三种模式下，sync_binlog=1 是强一致的选择，选择0或者N的情况下在极端情况下就会有丢失日志的风险，具体选择什么模式还是得看系统对于一致性的要求。

在这里插入图片描述

2.2 redo log的写入

innodb_flush_log_at_trx_commit：

取值0：每秒（一秒钟内提交的事务）写入磁盘每秒触发一次缓存日志回写磁盘操作，并调用操作系统fsync刷新IO缓存。
取值1：有事务提交就立即刷盘每次提交事务都立即调用操作系统fsync刷新IO缓存。
取值2：每次事务提交都写给操作系统由系统接管什么时候写入磁盘每次都把redo log写到系统的page cache中，由系统接管什么时候写入磁盘

2.3 undo log的写入

时机顺序：

1 开启事务
2 查询数据库中需要更新的字段，加载到内存中形成数据脏页
3 记录undo log到内存缓冲区（用于回滚和mvcc）并关联redo log -> 可刷盘
4 记录 redo log到内存缓冲区（用于失败重放）准备提交事务 -> 可刷盘
5 修改内存中的脏页数据
6 提交事务触发redolog刷盘
7 undo log 和脏页刷盘
8 事务成功

3. redo log 与 binlog 的两阶段提交

redo log 的写入拆成了两个步骤：prepare 和 commit

prepare：redolog写入log buffer，并fsync持久化到磁盘，在redolog事务中记录2PC的XID，在redolog事务打上prepare标识
commit：binlog写入log buffer，并fsync持久化到磁盘，在binlog事务中记录2PC的XID，同时在redolog事务打上commit标识

4. MySQL的binlog录入格式：

binlog_format=STATEMENT（默认）：数据操作的时间，同步时不一致每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致 master-slave 中的数据不一致( 如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
binlog_format=ROW：批量数据操作时，效率低不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
binlog_format=MIXED：是以上两种level的混合使用，有函数用ROW，没函数用STATEMENT，但是无法识别系统变量

5. Mysql集群同步时为什么使用binlog？优缺点是什么？

binlog是mysql提供的日志，所有存储引擎都可用。
支持增量同步
binlog还可以供其他中间件读取，比如同步到hdfs中
如果复制表数据：
- 不支持某个阶段回放
- 直接复制数据过程中一旦中断复制（比如断网），很难确定复制的offset

6. 慢查询日志监控步骤及分析：

开启慢查询日志，收集sql；

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

6.1 查看及开启

6.1.1 查看：

SHOW VARIABLES LIKE '%slow_query_log%';

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，

6.1.2 开启：

set global slow_query_log=1; 只对窗口生效，重启服务失效

6.1.3 慢查询日志记录long_query_time时间

假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，
在mysql源码里是判断大于long_query_time，而非大于等于。

SHOW VARIABLES LIKE '%long_query_time%';

SHOW GLOBAL VARIABLES LIKE 'long_query_time';

全局变量设置，对所有客户端有效。但，必须是设置后进行登录的客户端。

SET GLOBAL long_query_time=0.1;

对当前会话连接立即生效，对其他客户端无效。

SET SESSION long_query_time=0.1; #session可省略

永久生效：
- 修改配置文件my.cnf（其它系统变量也是如此）
- [mysqld]下增加或修改参数：slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。
  - 关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，如果不设置，系统默认文件：[host_name]-slow.log
- 也即将如下两行配置进my.cnf文件：

slow_query_log =1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
long_query_time=3
log_output=FILE

6.2 分析：

记录慢SQL并后续分析

SELECT * FROM emp;
SELECT * FROM emp WHERE deptid > 1;

查询当前系统中有多少条慢查询记录或者直接看慢查询日志

/var/lib/mysql/localhost-slow.log
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

6.3 日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

查看mysqldumpslow的帮助信息

mysqldumpslow --help

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

例：

得到返回记录集最多的10个SQL

mysqldumpslow  -s r -t 10 /var/lib/mysql/localhost-slow.log

得到访问次数最多的10个SQL

mysqldumpslow  -s c -t 10 /var/lib/mysql/localhost-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow  -s t -t 10 -g  "left join"  /var/lib/mysql/localhost-slow.log

另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现爆屏情况

mysqldumpslow  -s r -t 10 /var/lib/mysql/localhost-slow.log | more

leo_messi94

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql学习（八）-- mysql日志

error log主要记录MySQL在启动、关闭或者运行过程中的错误信息，在MySQL的配置文件my.cnf中，可以通过log-error=/var/log/mysqld.log 执行mysql错误日志的位置。在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
复制链接

扫一扫