漫谈MySQL中的日志文件

最新推荐文章于 2024-07-12 14:57:26 发布

迪迪迦

最新推荐文章于 2024-07-12 14:57:26 发布

阅读量299

点赞数

分类专栏：数据库文章标签： mysql 运维数据库

本文链接：https://blog.csdn.net/qq_31426247/article/details/104204557

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

操作系统：windows
mysql版本：mysql 8

MySQL中日志的分类：
1.错误日志（error log）
2.查询日志（log）
2.慢查询日志（slow query log）
3.二进制日志（binlog）

错误日志：
错误日志文件对mysql的启动，运行，关闭过程进行了记录。比如在mysql启动时加载配置文件出现的问题信息记录等。一般在数据库不能够正常启动的时候，首先查看错误日志排除问题，查看错误日志存放的位置，通过 show variables like “log_error”; 查看错误日志存放的位置。

mysql> show variables like "log_error";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    14
Current database: test

+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| log_error     | .\LAPTOP-05DEDJUJ.err |
+---------------+-----------------------+
1 row in set, 1 warning (0.01 sec)

一般mysql的日志文件都存放于Data目录中。这边LAPTOP-05DEDJUJ是主机名（可忽略）

C:\ProgramData\MySQL\MySQL Server 8.0\Data

LAPTOP-05DEDJUJ.err中

2020-02-06T16:25:54.590098Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
2020-02-06T16:25:56.962409Z 0 [Warning] [MY-000076] [Server] option 'general_log': boolean value '2' was not recognized. Set to OFF.
2020-02-06T16:25:56.962497Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2020-02-06T16:25:56.965272Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.18) starting as process 22460
2020-02-06T16:25:57.921906Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2020-02-06T16:25:57.933529Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2020-02-06T16:25:58.146686Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

仔细查看第二行，option ‘general_log’: boolean value ‘2’ was not recognized. Set to OFF.这意味着在mysql启动时，读取配置文件my.ini中general_log的值出现了警告说明值存在问题。

2020-02-06T17:29:56.419891Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
2020-02-06T17:31:39.244854Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2020-02-06T17:31:39.247503Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.18) starting as process 14076
mysqld: File '.\LAPTOP-05DEDJUJ-bin.000022' not found (OS errno 2 - No such file or directory)
2020-02-06T17:31:40.053546Z 0 [ERROR] [MY-010958] [Server] Could not open log file.
2020-02-06T17:31:40.054234Z 0 [ERROR] [MY-010041] [Server] Can't init tc log
2020-02-06T17:31:40.055076Z 0 [ERROR] [MY-010119] [Server] Aborting

此处代表丢失了相应的二进制日志文件。

查询日志：
查询日志记录了所有对mysql数据库的请求信息，无论这些请求是否正确。默认文件名为：主机名.log 在这里插入图片描述

2020-02-06T16:28:18.968213Z	    9 Query	SET autocommit=0
2020-02-06T16:28:18.969164Z	    9 Query	SELECT @@session.transaction_read_only
2020-02-06T16:28:18.969738Z	    9 Query	INSERT into `user`(user_name) value('TEST5')
2020-02-06T16:28:18.970512Z	    9 Query	commit

2020-02-06T16:29:01.474854Z	   13 Query	set autocommit=1
2020-02-06T16:29:45.585592Z	   13 Query	update user set user_name='12' where id like "%21%"

以上两段代码记录了数据库新增，修改数据的详细操作。

慢查询日志：
慢查询日志可以定位代码中可能存在问题的sql语句，从而进行sql语句层面的优化。比如设置一个阈值long_query_time=1,则慢查询日志中会记录超过1秒的所有sql语句（小于等于1秒的sql则不会被记录）。
通过 show variables like "long_query_time";查看mysql服务器配置的最大查询时间。

mysql> show variables like "long_query_time";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set, 1 warning (0.00 sec)

另外还提供了一个参数`log_queries_not_using_indexes`,如果为OFF，则不会在慢查询日志中记录没有使用索引查询的sql语句。反之...

mysql> show variables like "log_queries_not_using_indexes";
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

慢查询日志一般以主机名-slow.log命名。
在这里插入图片描述

Time                 Id Command    Argument
# Time: 2020-02-06T15:36:19.086834Z
# User@Host: root[root] @ localhost [::1]  Id:     8
# Query_time: 1.456285  Lock_time: 0.000085 Rows_sent: 0  Rows_examined: 98955
use test;
SET timestamp=1581003377;
update user set user_name='12' where id >12;

二进制日志：
二进制日志（binary log）记录了对mysql数据库执行更改的所有操作，但是不包括select语句，因为这类语句并没有对数据本身进行修改。如果需要查看所有的sql语句可以使用查询日志。
在这里插入图片描述
首先二进制文件提供部分参数值：

max_binlog_size:指定了单个二进制日志文件的最大值，如果超过该阈值，则新生成一个二进制日志文件，后缀名+1。

binlog_cache_size:当使用事务引擎时，对于没有提交的二进制文件会暂时写入缓存中，等待事务提交后，将这部分二进制文件直接写入二进制日志文件中。此参数设置缓冲区的大小。默认大小为32K，此外binlog_cache_size是基于会话的。也就是说mysql会自动为每一个线程分配该大小的缓冲区。

sync_binlog:二进制日志并不是每次写的时候同步到磁盘，因此当数据库发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中。这将导致部分数据丢失的问题。因为宕机后，缓冲区的文件已经丢失，此时sync_binlog=[N] 代表每写缓冲区N次就会同步到磁盘，如果N=1，表示已同步的方式写二进制日志文件。此时会增加对数据库的IO系统的影响。

innodb_support_xa:当sync_binlog=1时存在一种问题，当未提交的数据发生回滚时，此时二进制日志记录了未提交的数据，可以使用innodb_support_xa=1来解决这个问题。

当我们打开二进制日志文件时，发现文件“不可读”。接下来解决二进制日志文件的正确读取。这里涉及到一个参数：binlog_format，该参数可以设置的值有：STATEMENT，ROW和MIXED。

1.STATEMENT：二进制日志文件记录的是逻辑SQL语句。
2.ROW：除了简单的SQL语句还记录了表的更改情况。
3.MIXED：在该格式下，MYSQL默认采用STATEMENT格式进行二进制日志文件的记录，但有些情况会采用ROW格式。

我们将隔离级别设置为REPEATABLE-READ，binlog_format设置为MIXED，使用如下方法mysqlbinlog -vv --base64-output=decode-rows LAPTOP-05DEDJUJ-bin.000023看下二进制日志文件的具体内容：

BEGIN
/*!*/;
# at 325
#200207  1:34:04 server id 1  end_log_pos 457 CRC32 0x193db0d8  Query   thread_id=8     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1581010444/*!*/;
update user set user_name='12' where id = "12"
/*!*/;
# at 457
#200207  1:34:04 server id 1  end_log_pos 549 CRC32 0xa111c1de  Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1581010444/*!*/;
COMMIT