MySQL内部的日志类型及作用分析

代码魔法师Sunny

墨衍会员 · AI 创作全网分发码龄10年

关注

原创 2024-10-31 16:11:21 769 阅读 AI 写同款· GEO 优化 ›

log_error_verbosity参数详解 log_error_verbosity控制每条日志信息的详细程度阅读详情

MySQL常用的日志有下面几个

一、错误日志

MySQL错误日志是记录MySQL 运行过程中较为严重的警告和错误信息，以及MySQL每次启动和关闭的详细信息。错误日志的命名通常为hostname.err。其中，hostname表示服务器主机名。MYSQL的错误日志是默认开启的，并且，错误日志无法被禁止。

1.1.错误日志配置

可以用–log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值，mysqld使用错误日志名host_name.err 并在数据目录中写入日志件。如果你执行FLUSH LOGS，错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件。(如果未给出–log-error选项，则不会重新命名）。

[mysqld]
#参数为错误日志存放路径
log_error=/var/log/mysqld.log

1. 2.MySQL错误日志查询

mysql> show variables like 'log_error%';

1.3.MYSQL错误日志主要参数

1.3.1 log_warnings(MySQL 8.0.3移除)

此参数定义是否将告警信息（warning messages）也写入错误日志

log_warnings 为0，表示不记录告警信息。

log_warnings 为1，表示告警信息写入错误日志。

log_warnings 大于1，表示各类告警信息，例如有关网络故障的信息和重新连接信息写入错误日志。

此参数在不同版本略有差别，在MySQL 5.6中，log_warnings的默认值为1

在MySQL 5.7中，有些版本默认值为2，有些版本默认值为1

1.3.2 log_error_verbosity

此参数与log_warnings参数作用大致相同，从MySQL 5.7.2开始，首选log_error_verbosity,

log_error_verbosity有三个可选值，

1 错误信息；

2 错误信息和告警信息；

3 错误信息、告警信息和通知信息

二、通用查询日志

查询日志会将所有数据库的操作都会记录（general log 通用日志），无论对错。如果你想要知道mysqld内部发生了什么，你应该用–log[=file_name]或-l [file_name]选项启动它。如果没有给定file_name的值，默认名是host_name.log。mysqld按照它接收的顺序记录语句到查询日志。当你怀疑在客户端发生了错误并想确切地知道该客户端发送给mysqld的语句时，该日志可能非常有用。因为非常消耗I/O，所以默认不打开。

show variables like '%general_log%';

在这里插入图片描述

存放方式有三种

日志 general_log_file /var/lib/mysql/MySQL.log
将查询日志存入mysql.general_log表中
同时存放

默认值是'FILE'

开启MySQL通用查询日志以及设置要输出的通用日志输出格式

开启通用日志查询： set global general_log=on;
关闭通用日志查询： set globalgeneral_log=off;
设置通用日志输出为表方式： set globallog_output=’TABLE’;
设置通用日志输出为文件方式： set globallog_output=’FILE’;
设置通用日志输出为表和文件方式：set global log_output=’FILE,TABLE’;

（注意：上述命令只对当前生效，当MySQL重启失效，如果要永久生效，需要配置my.cnf）

记录到mysql.general_log表中的数据如下：

记录到本地中的.log中的格式如下：

my.cnf文件的配置如下：

general_log=1  #为1表示开启通用日志查询，值为0表示关闭通用日志查询
log_output=FILE,TABLE#设置通用日志的输出格式为文件和表

三、慢查询日志

	MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

相关参数

slow_query_log  ：是否开启慢查询日志，1表示开启，0表示关闭。
log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

慢查询日志配置

命令方式：

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------+
| Variable_name    | Value                  |
+---------------------+------------------------------------------+
| slow_query_log   | OFF                   |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------+
| Variable_name    | Value                  |
+---------------------+------------------------------------------+
| slow_query_log   | ON                    |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)

本地保存

[mysqld]
slow_query_log=ON
slow_launch_time=3
slow_query_log_file=/usr/local/slow.log

# 目录授权
chown -R mysql:mysql /usr/local/

使用慢查询

select sleep(4),username from ad_user;

直接查看慢查询日志

[root@localhost local]# cat slow.log
/usr/sbin/mysqld, Version: 5.6.48-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 200622  5:06:27
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 12.001145  Lock_time: 0.000190 Rows_sent: 3  Rows_examined: 3
use icoding_admin;
SET timestamp=1592816787;
select sleep(4),username from ad_user;

可以使用mysql提供的慢查询命令来查看

mysqldumpslow使用

-s排序规则，降序，-t 2显示结果前两行
c ：执行次数
l ：锁定时间
r ：返回记录
t ：执行时间
al、ar、at

# 根据时间降序
mysqldumpslow -s -t /var/lib/mysql/DB213-slow.log
# 根据记录数降序
mysqldumpslow -s -r /var/lib/mysql/DB213-slow.log
# 根据执行次数降序
mysqldumpslow -s -C /var/lib/mysql/DB213-slow.log
# 帮助手册
mysqldumpslow --help

内容

Reading mysql slow query log from /var/lib/mysql/DB213-slow.log
Count: 1  Time=12.00s (12s)  Lock=0.00s (0s)  Rows=3.0 (3), root[root]@localhost
  select sleep(N),id from ad_user

Count: 1  Time=12.00s (12s)  Lock=0.00s (0s)  Rows=3.0 (3), root[root]@localhost
  select sleep(N),username from ad_user

四、Binlog日志

MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。默认是关闭的，需要通过配置进行开启。

配置：

# statement
# MySQL会自动生成一个mysql-bin-00001.log
# chown -R mysql:mysql /usr/local/binlog/
log_bin=/usr/local/binlog/mysql-bin
binlog_format=statement
# binlog日志切割的大小
max_binlog_size=500m
# binlog过期清理时间
expire_logs_days=3
binlog_cache_size=32768 DML操作不不频繁 <=1m, DML频繁且事务⼤大 2-4m
max_binlog_cache_size 32位4G， 64位16P

修改数据：

update ad_user set username='gavin.huang' where id=1;

查看日志文件

mysql> show binlog events in 'mysql-bin.000001';

在这里插入图片描述

Binlog有三种记录模式

statement（语句模式）

纯粹的记录DML的语句,我们从上面的图中就可以看到。mysql5.6默认的是statement模式。

优点： 日志的记录量少，节省了I/O以及存储资源，提高性能。
缺点： 必须记录每条语句在执行时的上下文信息，以保证所有语句在slave端被执行时数据一致。不少情况下会造成mysql复制数据混乱

row（行模式）

就是将数据被修改的每一行的情况记录为一条语句。

# row模式的日志查询
mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000002

查询的结果

### DELETE FROM `icoding_admin`.`ad_user`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='coding' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
###   @3='123456' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

### UPDATE `icoding_admin`.`ad_user`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gavin' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
###   @3='1234567' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gavin.huang' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
###   @3='1234567' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

怎么快速找到误操作的语句

row模式的定位

mysqlbinlog --base64-output=decode-rows --start-datetime='2020-06-17 22:10' --stop-datetime='2020-06-17 22:25' -vv mysql-bin.000002

优点： 在行级模式下，binlog中可以不记录执行的SQL语句的上下文相关信息，仅仅记录哪一条记录被修改了，修改成什么样了即可，所以row level的日志内容会非常清楚地记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题。
缺点： 行级模式下，所有的执行语句都将根据修改的行来记录，而这就可能会产生大量的日志内容，例如一条语句修改了100万行，语句模式就用一条语句即可搞定，而行级模式执行之后，日志中记录的就是100万行的修改记录，binlog日志的量可能会大得惊人。

mixed（混合模式）

混合（mixed-based）模式默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行级模式记录，这些特殊情况包含但不限于以下情况。
·当函数中包含UUID()时。
·当表中有自增列（AUTO_INCREMENT）被更新时。
·当执行触发器（trigger）或者存储过程（stored function）等特殊功能时。
·当FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等执行时。

企业场景如何选择binlog的模式

1、如果生产中使用MySQL的特殊功能相对较少（储存过程，触发器，函数），选择默认的语句模式即Statement Level。
2、如果生产中使用MySQL的特殊功能比较多的，可以选用Mixed模式。
3、如果生产中使用MySQL的特殊功能较多，有些昂数据最大化一致，此时最好Row level模式，但要注意该模式的日志量将非常大。

收录于