mysql的日志类型

发表于 | 分类于
字数统计: 3.7k | 阅读时长 ≈ 13

MySQL 日志的类型

原文链接：https://www.cnblogs.com/xiaocen/p/3709114.html

https://dev.mysql.com/doc/refman/5.6/en/server-logs.html

官方文档：https://dev.mysql.com/doc/refman/5.7/en/server-logs.html

https://dev.mysql.com/doc/refman/8.0/en/server-logs.html

MySQL Server has several logs that can help you find out what activity is taking place.

Log Type Information Written to Log
Error log Problems encountered starting, running, or stopping mysqld
General query log Established client connections and statements received from clients
Binary log Statements that change data (also used for replication)
Relay log Data changes received from a replication source server
Slow query log Queries that took more than long_query_time seconds to execute
DDL log (metadata log) Metadata operations performed by DDL statements

MySQL日志类别：
一般查询日志：log，general_log，log_output
慢查询日志：查询执行的时长超过指定的查询，即为慢查询；
错误日志：通常时指错误日志的相关信息,通常用服务器关闭和启动的日志信息，服务器运行过程中的错误信息，还可以记录警告信息。
二进制日志：只是跟修改相关的操作，可以理解为一个重做日志，用于复制的基本凭据；
中继日志：它其实跟复制相关的，与二进制日志几乎相同；

事物日志：随机I/O转换为顺序I/O，一般在两个文件存储，一个存满了就换另外一个存

默认情况下，除了windows平台下的错误日志外，其他所有日志均未启用。DDL日志在需要的时候会自动创建，用户无法自主配置DDL日志。默认情况下，某种日志开启后，会记录到对应默认的日志文件中，用户可以强制修改记录的目标日志文件路径；

查询日志

1
2
3
4
5
6
log={ON|OFF} # 是否记录所有语句的日志信息于一般查询日志文件（general_log）中；
log_output={TABLE | FILE | NONE} # 定义log保存形式，none表示不保存日志；table和file可以同时出现，用逗号分隔即可；
general_log={ON|OFF} # 是否启动查询日志；
general_log_file # 定义一般查询日志保存的文件；
mysql > SET GOLBAL general_log ={OFF|ON};启用或关闭一般查询日志
mysql > SET GLOGAL log_output=’TABLE’;选项可以控制log的存放方法,table表示以表格的方式存放。

注：

  • 若想开启查询日志，则log和general_log参数都必须处于ON状态；
  • 对于繁忙的MySQL，不建议开启此日志；

慢查询日志：查询执行时长超过指定时长的查询，即为慢查询

1
2
3
long_query_time # 定义慢查询的时长；
slow_query_log {ON|OFF} # 全局参数，设定是否启用慢查询日志；它的输出位置也取决于log_output的值；
slow_query_log_file # 定义日志文件路径及名称；

启用慢速查询日志后，服务器会将输出写入log_output系统变量指定的任何目标 。如果启用日志，服务器将打开日志文件并向其中写入启动消息。但是，除非FILE选择了日志目标，否则不会对文件进行进一步的查询日志记录。如果目标是 NONE，则即使启用了慢查询日志，服务器也不会写入任何查询。如果FILE未选择日志文件名作为输出目标，则设置日志文件名对日志记录无效。

如果启用了慢查询日志并将FILE其选择为输出目标，则写入日志的每个语句都以一行开头，该行以#字符开头， 并具有以下字段（所有字段都位于一行中）：

  • Query_time: *duration*

    语句执行时间，以秒为单位。

  • Lock_time: *duration*

    获取锁的时间（以秒为单位）。

  • Rows_sent: *N*

    发送给客户端的行数。

  • Rows_examined:*``*

    服务器层检查的行数（不计算存储引擎内部的任何处理）。

写入慢速查询日志文件的每个语句之前都有一个SET 包含时间戳的语句，该时间戳记指示慢速语句的记录时间（该语句在执行完成后发生）。

服务器将重写写入慢速查询日志的语句中的密码，以使之不会以纯文本形式出现。

错误日志

主要记录内容：

  • 服务器启动和关闭过程中的信息；
  • 服务器运行过程中的错误信息；
  • 事件调度器运行一个事件时产生的信息；
  • 在复制架构中从服务器上启动从服务器线程时产生的信息；

参数设定：

1
2
log_error = /path/to/error_log_file # 定义错误日志文件
log_warnings = {1|0} # 是否记录警告信息于错误日志中，Version: '5.7.32'测试出错，服务器无法启动

二进制日志：

二进制日志包含描述数据库更改（例如表创建操作或表数据更改）的“事件”。它还包含针对可能进行了更改的语句的事件（例如， DELETE不匹配任何行的a），除非使用基于行的日志记录。二进制日志还包含有关每个语句花费该更新数据多长时间的信息。二进制日志有两个重要目的：

  • 对于复制，复制源服务器上的二进制日志提供了要发送到副本的数据更改的记录。源将其二进制日志中包含的事件发送到其副本，副本将执行这些事件以对源进行相同的数据更改。请参见 第16.2节“复制实现”。
  • 某些数据恢复操作需要使用二进制日志。还原备份后，将重新执行在执行备份后记录的二进制日志中的事件。这些事件使数据库从备份开始就保持最新状态。请参见 第7.5节“时间点（增量）恢复”。

二进制日志不用于诸如SELECT或 SHOW不修改数据的语句 。要记录所有语句（例如，确定问题查询），请使用常规查询日志。请参见第5.4.3节“常规查询日志”。

运行启用了二进制日志记录的服务器会使性能稍微降低。但是，二进制日志在使您能够设置复制和进行还原操作方面的优势通常超过了这种较小的性能下降。

二进制日志通常可以抵御意外的暂停，因为仅记录或读取完整的事务。有关 更多信息，请参见 第16.3.2节“处理副本的意外中断”。

服务器将重写写在二进制日志中的语句中的密码，以使它们不会以纯文本形式出现。

。mysql使用以下规则将影响数据库表的语句写入二进制日志：

CREATE TABLE ... SELECT是数据定义和数据操作的组合。该CREATE TABLE 部分是利用语句格式记录，且 SELECT部分是根据的值记录binlog_format。

记录了对MySQL数据库进行的修改操作，影响数据潜在的内容的信息，select的是不会被记录到二进制日志的，二进制日志也叫复制日志，默认在数据目录下，专门查看日志的命令是：mysqlbinlog

功能：1、做时间点恢复，要想从哪个时间点恢复我们可以手动调的，所以这是一个备份恢复的重要工具

　　　 2、 mysql主从复制架构中使用；

　　　因为我们的MySQL数据是单进程多线程的工作机制，所以他可以同时发起很多们修改的语句，但是我们服务器使用的日志只有一个，那如果同时进行的多个线程 的请求都同时往日志文件中写数据，日志文件就成了资源热点，也叫资源征用点，那就会混乱不堪，为了解决这种问题，这里，当我们的线程要往二进制日志文件中 写数据的时候，它不是直接写到日志文件中去，还是统一写到日志缓冲区中，由日志缓冲区逐一写入日志文件中去，然而我们的日志文件对于一个很繁忙的服务器来 讲，每天都会产生很大的数据量，如果所有的二进制日志数据都写在一个日志文件中，那管理起来就特点的不方便了，也很不合理，如是日志文件丢失，那所有的日 志数据信息都丢失了，不应该这么做，那我们就有日志滚动的机制了，二进制日志我们可以自己定义，有两种定义日志滚致力的方法，一种是按大小来定义的，比如 说我们定义一个日志文件的大小为1G，那他存储一了大概1G左右就会滚动，使用下一个日志来存放二进制日志数据；另一种可以按时间来定义的，比如说一周滚 动一次，或者说一个月，一天滚动一次都可以；或者每次重启服务时或者执行FLUSH LOGS命令时都会滚动一次日志。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [(none)]> SHOW MASTER STATUS;可以查看当前服务器正在使用的二进制日志文件以及下一下个事件开始时基于的位置Position，如果当前使用的是00004，那么00001、2、3将不会再被使用，因为已经被滚动过去了。
MariaDB [hellodb]> SHOW BINARY LOGS;查看当前系统上所有的的二进制日志文件，其实就是数据目录下mysql-bin.index文件中的信息，这个文件就是保存已经滚动过的日志文件的条目。
MariaDB [hellodb]> flush logs;
清除日志文件的命令：PURGE
MariaDB [hellodb]> show binlog events in ‘log_file’;
# mysqlbinlog
--start-time
--stop-time
--start-
# mysqlbinlog --start-protion=1139 mysql-bin.000001：在命令行中查看某个日志文件的某个位置点往后所记录的数据信息。可以用输出重定向保存到某个文件中去将来用于执行的。
server-id：服务器的身份标识，
MariaDB [hellodb]> SELECT  VERSION();查看当前数据库的版本
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 1139;查看某个二进制日志文件中从哪个位置往后发生的事件信息。
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000006'\G查看某个二进制文件中在所有时间点和结束点等相关的信息。

MySQL记录二进制日志的格式：

您可以通过使用启动MySQL服务器来显式选择二进制日志记录格式 。支持的值为： --binlog-format=*type*type

  • STATEMENT 使日志记录基于语句。
  • ROW 使日志记录基于行。
  • MIXED 使日志记录使用混合格式。

statement：基于语句

  • 缺陷：执行带时间函数的语句时，会导致主从服务器执行结果不一致；
  • 如 insert into tb1 value (current_date());

row：基于行

  • 缺陷：批量更新操作将导致从服务器进行更多的操作 update tb2 set salary=salary+1000;
  • mixed：混合模式，由MySQL自行判断选择适合的方式基于语句或基于行记录日志；

二进制日志文件的内容格式

  • 事件发生的日期和时间
  • 服务器id：server_id
  • 事件的结束位置：end_log_pos
  • 事件的类型：如Query，GTID等
  • 原服务器生成此事件时的线程id：thread_id
  • 语句的时间戳和写入二进制日志文件的时间差，exec_time
  • 错误代码：error_code
  • 事件位置：at #，相当于下一事件的开始位置

设定参数：

1
2
3
4
5
6
7
8
log_bin = {ON|OFF|FileName} # 全局变量，控制二进制文件位置
log_bin_trust_function_creators # 全局变量，仅在启用二进制日志时有效，用于控制创建存储函数时如果会导致不安全的事件是否禁止创建存储函数
sql_log_bin = {ON|OFF} # 会话级别，用于控制二进制日志信息是否记录进日志文件
sync_binlog # 设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次；
binlog_format={statement|row|mixed} # 指定二进制日志的类型
max_binlog_cache_size= # 二进制日志缓冲空间大小，仅用于缓冲事务类的语句, 其上限由max_binlog_stmt_cache_size决定
max_binlog_size= # 二进制日志文件大小上限，单位字节，默认大小1G
# 注：切勿将二进制日志与数据文件放在同一磁盘上，减少磁盘I/O

中继日志：它其实跟复制相关的，与二进制日志几乎相同，只不过它不是用于记录事件的，而是作为读取数据的源并且在本地执行的，当然中继日志是在从服务器上。

设定参数：

1
2
3
4
5
6
7
8
  
 MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE ``'%relay%'``;查看中继日志的相关参数信息
relay_log=file_name # 设定中继日志的文件名称
relay_log_index=file_name # 设定中继日志的索引文件名，默认为为数据目录中的host_name-relay-bin.index
relay_log_purge={ON|OFF} # 是否自动清理不再需要的中继日志
relay_log_space_limit= # 设定用于存储所有中继日志文件的可用空间大小，0表示不限制
relay_log_recovery：跟中继日志自动恢复相关的
relay_log={空}：是否启用中继日志的

事务日志：

  • 暂存事务提交的数据，实现将随机I/O转换成顺序I/O；
  • 事务数据提交流程：innodb_buffer–>事务日志–>数据文件；
  • 事务日志文件组，至少应该有2个日志文件，一般保存在数据目录下，为ib_logfile0和ib_logfile1；

注意事项：

  • 尽可能使用小事务以提升事务引擎的性能；因为事务越大，回滚开销越大；
  • 崩溃恢复：将提交的事务从事务日志中同步至数据文件，将未提交的事务执行回滚操作，以保证数据一致性；
  • 避免磁盘故障导致事务文件丢失：对事务日志所在的磁盘做数据镜像；
  • 事务日志文件和数据文件应分离存放：防止对同一磁盘过于频繁的I/O操作；

设定参数：

-------------本文结束感谢您的阅读-------------
0%