mysql 日志

mysql日志

MySQL它有不同类型的日志文件，各自存储了不同类型的日志。分析这些日志文件，除了可以了解 MySQL 数据库的运行情况，还可以为 MySQL 的管理和优化提供必要的信息。

日志管理是维护数据库的重要步骤，所以经常需要在 MySQL 中进行日志启动、查看、停止和删除等操作。这些操作是数据库管理中最基本、最重要的操作。

日志分类

在 MySQL 中，日志可以分为二进制日志、错误日志、通用查询日志和慢查询日志。对于 MySQL 的管理工作而言，这些日志文件是不可缺少的。分析这些日志，可以帮助我们了解 MySQL 数据库的运行情况、日常操作、错误信息和哪些地方需要进行优化。

MySQL 中日志分类及其作用：

重做日志：确保事务的持久性,记录事务执行后的状态。
回滚日志：保证数据的原子性，记录了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC）
二进制日志：该日志文件会以二进制的形式记录数据库的各种操作，但不记录查询语句。
错误日志：该日志文件会记录 MySQL 服务器的启动、关闭和运行错误等信息。
通用查询日志：该日志记录 MySQL 服务器的启动和关闭信息、客户端的连接信息、更新、查询数据记录的 SQL 语句等。【mysql默认关闭】
慢查询日志：记录执行事件超过指定时间的操作，通过工具分析慢查询日志可以定位 MySQL 服务器性能瓶颈所在。【优化sql的关键】
中继日志：MySQL进行主主复制或主从复制的时候会在home目录下面产生相应的relay log

注意：

在 MySQL 所支持的日志文件里，除了二进制日志文件外，其它日志文件都是文本文件。默认情况下，MySQL 只会启动错误日志文件，而其它日志则需要手动启动。

记录日志的缺点：

降低 MySQL 的执行速度【一个查询操作比较频繁的 MySQL 中，记录通用查询日志和慢查询日志要花费很多的时间】
占用大量的硬盘空间【对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间甚至比数据库文件需要的存储空间还要大】

Redo Log(重做日志)

我们都知道，事务的四大特性里面有一个是持久性，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。但是mysql的刷盘并不是实时的否则会有很大的消耗，是有后台线程轮询刷盘，当这个时候数据库宕机，就需要redo log，因为它记录了事务执行后的状态，数据库再次重启后，就会根据redo log进行重做，从而达到事务的持久性这一特性。

实时刷盘产生性能问题：

因为Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，就大大浪费了资源
一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

redo log记录事务对数据页做了哪些修改，既能解决性能问题而且文件更小并且是顺序IO

redo log工作原理图

redo log写入机制

redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，该部分的日志是容易丢失的，另一个是磁盘上的日志文件(redo log file)。

mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。

MySQL的log buffer处于用户空间的内存中，用户空间(user space)下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间（kernel space)的缓冲区（OS Buffer)。因此，redo log buffer写入redo log file实际上是先写入OS Buffer，然后再通过系统调用fsync()将其刷到redo log file中，过程如下：

mysql支持三种将redo log buffer写入redo log file的时机:

Redo数据持久策略	含义
0 [延时写入]	事务提交时不会将`redo log buffer`中日志写入到`os buffer`并调用`fsync()`写入`redo log file`中。也就是说，延时写入大约每秒刷新写入到磁盘中，当系统宕机崩溃时可能会丢失1秒数据
1 [实时写入，实时刷]	事务每次提交都会将`redo log buffer`中的日志写入`os buffer`并调用`fsync()`刷到`redo log file`中。这种方式即使系统崩溃也不会丢失任何数据，但是因为是实时刷写入磁盘，IO性能比较差
2 [实时写入，延时刷]	每次提交都仅写入到`os buffer`，然后是每秒调用`fsync()`将`os buffer`中的日志写入到`redo log file`中。
备注：	`innodb_flush_log_at_trx_commit`进行参数配置【只能控制commit动作是否刷新log buffer到磁盘】

注意：

innodb_flush_log_at_timeout 的值为1秒，该变量表示的是刷日志的频率

在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置为如下：

如果启用了二进制日志，则设置sync_binlog=1，即每提交一次事务同步写到磁盘中。
总是设置innodb_flush_log_at_trx_commit=1，即每提交一次事务都写到磁盘中。

上述两项变量的设置保证了：每次提交事务都写入二进制日志和事务日志，并在提交时将它们刷新到磁盘中。

redo log循环写

前面说过，redo log实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志

write point：这个指针记录当前位置，一边写，一边移动，写到最后一个文件末尾后就回到 0 号文件重新覆盖写
check point：这个指针记录当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件

redo log写满时候，指针回到原点，重新开始覆盖保存，如果 write pos 追上checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下节点

redo log参数配置

通过命令查看redo log参数:

show variables like '%innodb_log%';

参数含义：

innodb_log_group_home_dir：指定redo日志存放的路径，日志文件以ib_logfile[number]来命名
innodb_log_files_in_group：MySQL8.0已经放弃了日志组的概念，但参数名依旧保留了下来以兼容以前的配置。该参数的含义为有多少个log文件(最少为2个)
innodb_log_file_size：表示每个文件的大小
innodb_log_files_in_group * innodb_log_file_size：总的redo Log的大小

redo log 更多详细原理

详细分析MySQL事务日志(redo log和undo log)

undo log(回滚日志)

undo log 基本概念

两大作用：

提供回滚
多个版本控制MVCC

事务中数据的修改新增删除，不仅记录了redo log 还记录了相对应的undo，如果事务失败则通过undo log进行回滚。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment

undo log也会产生redo log，因为undo log也要实现持久性保护

undo log 存储方式

innodb存储引擎对undo的管理采用段的方式。rollback segment称为回滚段，每个回滚段中有1024个undo log segment

注意：

老版本Mysql只支持1个rollback segment，这样就只能记录1024个undo log segment
MySQL5.5可以支持128个rollback segment，即支持128*1024个undo操作，还可以通过变量 innodb_undo_logs (5.6版本以前该变量是 innodb_rollback_segments )自定义多少个rollback segment，默认值为128
undo log默认存放在共享表空间【innodb_file_per_table 】将放在每个表的.ibd文件中
MySQL5.6中，undo的存放位置还可以通过变量 innodb_undo_directory 来自定义存放目录，默认值为"."表示datadir
rollback segment全部写在一个文件中，通过设置变量 innodb_undo_tablespaces 平均分配到多少个文件中。该变量默认值为0，即全部写入一个表空间文件。该变量为静态变量，只能在数据库示例停止状态下修改，如写入配置文件或启动时带上对应参数。但是innodb存储引擎在启动过程中提示，不建议修改为非0的值，如下：

undo log工作原理图

注意：

undo日志属于逻辑日志，redo是物理日志，所谓逻辑日志是undo log是记录一个操作过程，不会物理删除undo log，sql执行delete或者update操作都会记录一条undo日志

undo log参数配置

show variables like "%undo%";

参数含义：

innodb_undo_directory ：定义存储的目录路径，默认值./，表示datadir
datadir参数可以通过设置my.ini配置文件：

[mysql]
# 设置mysql数据库的数据的存放目录
datadir="D:\mysql-8.0.13-winx64\data"

innodb_undo_log_truncate：参数设置为1，即开启在线回收（收缩）undo log日志文件，支持动态设置，默认是关闭的
innodb_max_undo_log_size ：控制最大undo tablespace文件的大小，当启动了innodb_undo_log_truncate 时，undo tablespace 超过阀值时才会去尝试truncate。该值默认大小为1G，truncate收缩后的大小默认为10M。
innodb_undo_tablespaces：undo独立表空间个数，范围为0-128，默认0，表示undo日志存储在ibdata文件中，可以设置这个变量，平均分配到多少个文件中【mysql实例已创建不能再修改】

什么时候需要来设置innodb_undo_tablespaces参数呢？

当DB写压力较大时，可以设置独立UNDO表空间，把UNDO LOG从ibdata文件中分离开来，指定 innodb_undo_directory目录存放，可以制定到高速磁盘上，加快UNDO LOG 的读写性能。

show global variables like '%truncate%';

参数含义：

innodb_undo_log_truncate【详细】

InnoDB的purge线程，根据innodb_undo_log_truncate设置开启或关闭、innodb_max_undo_log_size的参数值，以及truncate的频率来进行空间回收和 undo file 的重新初始化。

该参数生效的前提是，已设置独立表空间且独立表空间个数大于等于2个。

purge线程在truncate undo log file的过程中，需要检查该文件上是否还有活动事务，如果没有，需要把该undo log file标记为不可分配，这个时候，undo log 都会记录到其他文件上，所以至少需要2个独立表空间文件，才能进行truncate 操作，标注不可分配后，会创建一个独立的文件undo_<space_id>trunc.log，记录现在正在truncate 某个undo log文件，然后开始初始化undo log file到10M，操作结束后，删除表示truncate动作的 undo<space_id>_trunc.log 文件，这个文件保证了即使在truncate过程中发生了故障重启数据库服务，重启后，服务发现这个文件，也会继续完成truncate操作，删除文件结束后，标识该undo log file可分配。

innodb_purge_rseg_truncate_frequency

用于控制purge回滚段的频度，默认为128。假设设置为n，则说明，当Innodb Purge操作的协调线程 purge事务128次时，就会触发一次History purge，检查当前的undo log 表空间状态是否会触发truncate。

delete/update操作的内部机制

当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read可重复读时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。
但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。
通过undo log记录delete和update操作的结果发现：(insert操作无需分析，就是插入行而已)
1. delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
2. update分为两种情况：update的列是否是主键列。
  1. 如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
  2. 如果是主键列，update分两部执行：先删除该行，再插入一行目标行。

undo log 更多详细

binlog(二进制日志)

Binlog基本概念

二进制日志（Binary Log）也可叫作变更日志（Update Log）

binlog用于记录数据库执行的写入性[不包含查询]操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。

逻辑日志：可以简单理解为记录的就是sql语句【以二进制的方式进行记录】。
物理日志：因为mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

注意：

binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

如果 MySQL 数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。

默认情况下，二进制日志功能是关闭的

Binlog作用及使用场景

binlog的主要使用场景作用有两个：

主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
数据恢复：通过使用mysqlbinlog工具来恢复数据。

Binlog启动/查看/配置

1. 查看binlog日志参数：

# 查看 binlog 日志参数状态 默认未开启
show variables like 'log_bin';

2. 启动二进制日志:

通过配置文件，修改配置添加 log-bin 选项来开启二进制日志：

[mysqld]
log-bin=dir/[filename]

# 参数解释：
# dir	参数指定二进制文件的存储路径
# filename		指定二进制文件的文件名，其形式为 filename.number，number 的形式为 000001、000002 等

# 注意：
# 每次重启 MySQL 服务后，都会生成一个新的二进制日志文件，这些日志文件的文件名中 filename 部分不会改变，number 会不断递增。
# 如果没有 dir 和 filename 参数，二进制日志将默认存储在数据库的数据目录下，默认的文件名为 hostname-bin.number，其中 hostname 表示主机名。
# 默认名称为主机名,名称若带有小数点，则只取第一个小数点前的部分作为名称

示例：

【未配置 dir 和 filename 参数】

my.ini 文件的 [mysqld] 组中添加以下语句：

log-bin

重启 MySQL 服务器后，可以在 MySQL 数据库的数据目录下看到 LAPTOP-UHQ6V8KP-bin.000001 这个文件，同时还生成了 LAPTOP-UHQ6V8KP-bin.index 文件。此处，MySQL 服务器的主机名为 LAPTOP-UHQ6V8KP。

【配置 dir 和 filename 参数】

在 my.ini 文件的 [mysqld] 组中进行如下修改：

log-bin=C:log\mylog

重启 MySQL 服务后，可以在 C:log 文件夹下看到 mylog.000001 文件和 mylog.index 文件。

Binlog文件的扩展:【生成新log文件序号递增】

MySQL服务器停止或重启时，MySQL会在重启时生成一个新的日志文件；
使用flush logs命令；
当binlog文件大小超过max_binlog_size系统变量配置的上限时；

注意：binlog文件的最大值和默认值是1GB，该设置并不能严格控制binlog的大小，尤其是binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录到当前日志，直到事务结束。

3. 查看二进制日志

1. 查看二进制日志文件列表:

SHOW binary logs;

查看当前正在写入的二进制日志文件

show master status;

查看二进制日志文件内容

二进制日志使用二进制格式存储，不能直接打开查看。如果需要查看二进制日志，必须使用 mysqlbinlog 命令。

mysqlbinlog 命令的语法形式:

mysqlbinlog filename.number

mysqlbinlog 命令只在当前文件夹下查找指定的二进制日志，因此需要在二进制日志所在的目录下运行该命令，否则将会找不到指定的二进制日志文件。

示例一：

生成一个新的binlog日志文件：

# 生成一个新的binlog文件
flush logs;
#查看状态
show master status;

# 查看binlog的目录
show global variables like '%log_bin%';

创建一张测试表生成binlog日志

# 创建一张测试表
create table testBinlog(
    id int primary key  auto_increment,
    name varchar(50) not null default ''
)ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
  #查看状态
show master status;

进入binlog存储目录CMD,执行mysqlbinlog 查看二进制日志命令：

# 查看binlog日志内容
mysqlbinlog PC-202006062018-bin.000437;  
# 注意 如果是win mysql 需要先找到myslq 的bin目录，在执行mysqlbinlog 后跟完整目录
mysqlbinlog  binlog完整目录地址

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#211214 16:51:49 server id 1  end_log_pos 125 CRC32 0xf852a91b  Start: binlog v 4, server v 8.0.20 created 211214 16:51:49
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
JVu4YQ8BAAAAeQAAAH0AAAABAAQAOC4wLjIwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBG6lS+A==
'/*!*/;
# at 125
#211214 16:51:49 server id 1  end_log_pos 156 CRC32 0xc25ac0eb  Previous-GTIDs
# [empty]
# at 156
#211214 16:55:22 server id 1  end_log_pos 235 CRC32 0x49af0563  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no     original_committed_timestamp=1639472122788248   immediate_commit_timestamp=1639472122788248             transaction_length=372
# original_commit_timestamp=1639472122788248 (2021-12-14 16:55:22.788248 ?D1ú±ê×?ê±??)
# immediate_commit_timestamp=1639472122788248 (2021-12-14 16:55:22.788248 ?D1ú±ê×?ê±??)
/*!80001 SET @@session.original_commit_timestamp=1639472122788248*//*!*/;
/*!80014 SET @@session.original_server_version=80020*//*!*/;
/*!80014 SET @@session.immediate_server_version=80020*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 235
#211214 16:55:22 server id 1  end_log_pos 528 CRC32 0x9a14ae9c  Query   thread_id=8     exec_time=0     error_code=0            Xid = 346
use `bigdatabase`/*!*/;
SET TIMESTAMP=1639472122/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
/* ApplicationName=DataGrip 2021.1.3 */ create table testBinlog(
    id int primary key  auto_increment,
    name varchar(50) not null default ''
)ENGINE = InnoDB
  DEFAULT CHARSET = utf8
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

分析二进制日志文件

通过show master status;命令position字段的前后变化，我们截取156-528之间的字段

/* ApplicationName=DataGrip 2021.1.3 */ create table testBinlog(
    id int primary key  auto_increment,
    name varchar(50) not null default ''
)ENGINE = InnoDB
  DEFAULT CHARSET = utf8
/*!*/;
# 我们可以找到测试创建表的sql语句，另外mysql也做了很多的隐含的操作

mysql客户端查询二进制日志内容

# 查到当前正在使用binlog日志，以及日志状态
show master status;
# 查询二进制日志内容	show binlog events in binlog日志名称
show binlog events in 'PC-202006062018-bin.000438';

# 注意：本命令在myslq8.0上测试正常，其他版本暂未测试

二进制日志文件相关参数值含义:

position: 位于文件中的位置，即第一行的（# at 314）,说明该事件记录从文件第314个字节开始
timestamp: 事件发生的时间戳，即第二行的（#161020 11:07:29）
exec_time: 事件执行的花费时间
error_code: 错误码
server id: 服务器标识（2）
thread_id: 代理线程id （thread_id=162）
type:事件类型Query【下文有详细介绍】

使用 binlog 日志恢复数据

使用mysqlbinlog命令还可以进行数据库恢复，使用日志进行恢复时，需要依次进行，即最早生成的日志文件要最先恢复

mysqlbinlog mysql-bin.000001 | mysql -uroot -proot
mysqlbinlog mysql-bin.000002 | mysql -uroot -proot

Binlog删除/暂停

删除binlog日志

# 删除的所有二进制日志 将会重新创建，编号恢复起始值
RESET MASTER;

# 根据名称编号删除二进制日志
PURGE MASTER LOGS TO 'filename.number';

# 根据创建时间删除二进制日志
PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';

暂时停止binlog日志

# 暂停/开启二进制日志功能    0 表示暂停二进制日志功能，1 表示开启二进制功能
SET SQL_LOG_BIN=0/1;

MySQL主备

MySQL主备复制原理、实现及异常处理

Binlog 日志格式

记录在二进制日志中的事件的格式取决于二进制记录格式。支持三种格式类型：

STATEMENT：基于SQL语句的复制（statement-based replication, SBR）
ROW：基于行的复制（row-based replication, RBR）
MIXED：混合模式复制（mixed-based replication, MBR）

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默认值是 ROW。日志格式通过 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

1. Statement 二进制记录格式

每一条会修改数据的sql都会记录在binlog中

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能。

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外mysql的复制，像一些特定函数的功能，slave与master要保持一致会有很多相关问题。

2. Row 二进制记录格式

5.1.5版本的MySQL才开始支持 row level 的复制,它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.

缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

注意: 将二进制日志格式设置为ROW时，有些更改仍然使用基于语句的格式，包括所有DDL语句，例如CREATE TABLE， ALTER TABLE，或 DROP TABLE。

3. Mixed 二进制记录格式

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

Binlog 事件类型

binlog 事件的结构主要有3个版本:

v1: 在 MySQL 3.23 中使用
v3: 在 MySQL 4.0.2 到 4.1 中使用
v4: 在 MySQL 5.0 及以上版本中使用

现在一般不会使用MySQL5.0以下版本，所以下面仅介绍v4版本的binlog事件类型。binlog 的事件类型较多在此做一些简单的汇总:

事件类型	说明
UNKNOWN_EVENT	此事件从不会被触发，也不会被写入binlog中；发生在当读取binlog时，不能被识别其他任何事件，那被视为UNKNOWN_EVENT
START_EVENT_V3	每个binlog文件开始的时候写入的事件，此事件被用在MySQL3.23 – 4.1，MYSQL5.0以后已经被 FORMAT_DESCRIPTION_EVENT 取代
QUERY_EVENT	执行更新语句时会生成此事件，包括：create，insert，update，delete；
STOP_EVENT	当mysqld停止时生成此事件
ROTATE_EVENT	当mysqld切换到新的binlog文件生成此事件，切换到新的binlog文件可以通过执行flush logs命令或者binlog文件大于 `max_binlog_size` 参数配置的大小；
INTVAR_EVENT	当sql语句中使用了AUTO_INCREMENT的字段或者LAST_INSERT_ID()函数；此事件没有被用在binlog_format为ROW模式的情况下
LOAD_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL 3.23版本中使用
SLAVE_EVENT	未使用
CREATE_FILE_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0和4.1版本中使用
APPEND_BLOCK_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0版本中使用
EXEC_LOAD_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0和4.1版本中使用
DELETE_FILE_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0版本中使用
NEW_LOAD_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0和4.1版本中使用
RAND_EVENT	执行包含RAND()函数的语句产生此事件，此事件没有被用在binlog_format为ROW模式的情况下
USER_VAR_EVENT	执行包含了用户变量的语句产生此事件，此事件没有被用在binlog_format为ROW模式的情况下
FORMAT_DESCRIPTION_EVENT	描述事件，被写在每个binlog文件的开始位置，用在MySQL5.0以后的版本中，代替了START_EVENT_V3
XID_EVENT	支持XA的存储引擎才有，本地测试的数据库存储引擎是innodb，所有上面出现了XID_EVENT；innodb事务提交产生了QUERY_EVENT的BEGIN声明，QUERY_EVENT以及COMMIT声明，如果是myIsam存储引擎也会有BEGIN和COMMIT声明，只是COMMIT类型不是XID_EVENT
BEGIN_LOAD_QUERY_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL5.0版本中使用
EXECUTE_LOAD_QUERY_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL5.0版本中使用
TABLE_MAP_EVENT	用在binlog_format为ROW模式下，将表的定义映射到一个数字，在行操作事件之前记录（包括：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT）
PRE_GA_WRITE_ROWS_EVENT	已过期，被 WRITE_ROWS_EVENT 代替
PRE_GA_UPDATE_ROWS_EVENT	已过期，被 UPDATE_ROWS_EVENT 代替
PRE_GA_DELETE_ROWS_EVENT	已过期，被 DELETE_ROWS_EVENT 代替
WRITE_ROWS_EVENT	用在binlog_format为ROW模式下，对应 insert 操作
UPDATE_ROWS_EVENT	用在binlog_format为ROW模式下，对应 update 操作
DELETE_ROWS_EVENT	用在binlog_format为ROW模式下，对应 delete 操作
INCIDENT_EVENT	主服务器发生了不正常的事件，通知从服务器并告知可能会导致数据处于不一致的状态
HEARTBEAT_LOG_EVENT	主服务器告诉从服务器，主服务器还活着，不写入到日志文件中

Binlog 事件结构

一个事件对象分为事件头和事件体，事件的结构如下：

如果事件头的长度是 x 字节，那么事件体的长度为 (event_length - x) 字节；设事件体中 fixed part 的长度为 y 字节，那么 variable part 的长度为 (event_length - (x + y)) 字节

Binlog Event 简要分析

从一个最简单的实例来分析Event，包括创建表，插入数据，更新数据，删除数据；

# 生成一个新的binlog文件
flush logs;
# 创建一张测试表
create table testBinlog(
    id int primary key  auto_increment,
    name varchar(255) not null default ''
)ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
select * from testBinlog;
  # 插入数据
insert into testBinlog values(1,'测试binlog日志');
# 修改数据
update testBinlog set name='测试binlog日志2' where id=1;
# 删除
delete from testBinlog where id=1;

binlog日志格式的切换以及查询:

# 注意：binlog日志格式的切换以及查询
#查看与配置binlog格式
show variables like 'binlog_format';
# 直接修改binlog_format 修改完需要重新连接mysql
set global binlog_format ='STATEMENT';

日志格式为Row，查看所有的Event

show binlog events in 'PC-202006062018-bin.000443';

ROW格式下create、insert、update、delete操作产生的binlog事件

根据事件类型可以在上面事件类型表中查到对应的操作类型，create，update ，delete等

日志格式为STATEMENT，查看所有的Event

# 注意： 需要重新创建测试表，再次执行sql语句 生成新binlog文件
show binlog events in 'PC-202006062018-bin.000444';

STATEMENT格式下create、insert、update、delete操作产生的binlog事件

和row日志输出格式事件，大大不同。关于Event的分析，有需要可以查看参考文档进行推算。

业内目前推荐使用的是row模式，准确性高，虽然说文件大，但是现在有SSD和万兆光纤网络，这些磁盘IO和网络IO都是可以接受的。

Binlog持久化

对于InnoDB存储引擎而言，只有在事务提交时才会记录binlog，此时记录还在内存中，那么binlog是什么时候刷到磁盘中的呢？mysql通过sync_binlog参数控制binlog的刷盘时机，取值范围是0-N：

0：不去强制要求，由系统自行判断何时写入磁盘；
1：每次commit的时候都要将binlog写入磁盘；
N：每N个事务，才会将binlog写入磁盘。

注意：

sync_binlog设置0/1部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务
sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

Binlog操作常用命令集合

# 是否启用binlog日志
show variables like 'log_bin';

# 查看详细的日志配置信息
show global variables like '%log%';

# mysql数据存储目录
show variables like '%dir%';

# 查看binlog的目录
show global variables like "%log_bin%";

# 查看当前服务器使用的biglog文件及大小
show binary logs;

# 查看主服务器使用的biglog文件及大小

# 查看最新一个binlog日志文件名称和Position以及当前正在写入的binlog文件
show master status;

# 生成一个新的binlog文件
flush logs;

#查看与配置binlog输出格式
show variables like 'binlog_format';
# 直接修改binlog_format 修改完需要重新连接mysql
set global binlog_format ='STATEMENT';

# 事件查询命令
# IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
# FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
# LIMIT [offset,] ：偏移量(不指定就是0)
# row_count ：查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

# 查看 binlog 内容 默认当前正在写入log文件
show binlog events;

# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）
show binlog events in 'master.000003';

# 设置binlog文件保存事件，过期删除，单位天
set global expire_log_days=3; 

# 设置日志过期时间 一天 单位秒 【8.0使用】
set global binlog_expire_logs_seconds=60*60*24;

# 查日志过期时间
show global variables like '%binlog_expire_logs_seconds%';

# 查日志过期时间 8.0已经弃用expire_log_days
show global variables like '%expire_log_days%';

# 删除当前的binlog文件
reset master; 

# 删除slave的中继日志
reset slave;

# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';

# 删除指定日志文件
purge master logs to 'master.000003';

Error Log(错误日志)

错误日志（Error Log）是 MySQL 中最常用的一种日志，主要记录 MySQL 服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等。

启动和设置错误日志

在 MySQL 数据库中，默认开启错误日志功能。一般情况下，错误日志存储在 MySQL 数据库的数据文件夹下，通常名称为 hostname.err。其中，hostname 表示 MySQL 服务器的主机名。

在 MySQL 配置文件中，错误日志所记录的信息可以通过 log-error 和 log-warnings 来定义，其中，log-err 定义是否启用错误日志功能和错误日志的存储位置，log-warnings 定义是否将警告信息也记录到错误日志中。

查看错误日志

在 MySQL 中，通过 SHOW 命令可以查看错误日志文件所在的目录及文件名信息。

将 log_error 选项加入到 MySQL 配置文件的 [mysqld] 组中，形式如下：

[mysqld]
log-error=dir/{filename}
# dir 参数指定错误日志的存储路径
# filename 参数指定错误日志的文件名

注意：

错误日志中记录的并非全是错误信息，例如 MySQL 如何启动 InnoDB 的表空间文件、如何初始化自己的存储引擎等，这些也记录在错误日志文件中。
错误日志以文本文件的形式存储，直接使用普通文本工具就可以查看

删除错误日志

mysqladmin -uroot -p flush-logs

执行该命令后，MySQL 服务器首先会自动创建一个新的错误日志，然后将旧的错误日志更名为 filename.err-old。

MySQL 服务器发生异常时，管理员可以在错误日志中找到发生异常的时间、原因，然后根据这些信息来解决异常。对于很久之前的错误日志，查看的可能性不大，可以直接将这些错误日志删除。

General Query Log(通用查询日志)

通用查询日志（General Query Log）用来记录用户的所有操作，包括启动和关闭 MySQL 服务、更新语句和查询语句等。

1. 查看通用查询日志

默认情况下，通用查询日志功能是关闭的。可以通过以下命令查看通用查询日志是否开启，命令如下：

SHOW VARIABLES LIKE '%general%';

参数含义：

general_log：开启/关闭默认关闭OFF/NO
general_log_file : 指定了通用查询日志文件

2. 设置通用查询日志

mysql配置文件中，修改参数：

[mysqld]
log=dir/filename

3. 开启通用查询日志

# 开启通用日志 0/1 关闭/开启
set global general_log = 1;

# 任意执行 select 、update、create、delete 语句
select * from bank;
# 查询通用日志
# 默认地址在myslq的data目录下

4. 停止通用查询日志

#配置文件中 
[mysqld]
#log=dir\filename log参数注释 ，重启myslq
# 也可以通过命令设置
set global general_log = 0;
# 再次查询
SHOW VARIABLES LIKE '%general%';

Slow Query Log(慢查询日志)

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

查询/设置慢查询日志参数

1. 查看当前慢查询日志配置

show variables like "%slow%";

参数含义：

slow_query_log：是否启用慢查询日志，[1 | 0] 或者 [ON | OFF] 默认未开启
slow_query_log_file : MySQL数据库（5.6及以上版本）慢查询日志存储路径。
log_slow_slave_statements：主从复制时候不会将复制查询写入慢查询日志，默认未开启
slow_launch_time：如果建立线程花费了比这个值更长的时间，这参数slow_launch_threads计数器增加
log_output：日志的存储方式，默认FILE,设置为TABLE写入到mysql.slow_log表中，支持同时两种日志存储方式
log_slow_admin_statements：表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志,默认未开启

# 查询慢日志写入时间阈值
# 查看慢查询时间，默认10s，建议降到1s或以下
show variables like 'long_query_time';

2. 开启慢日志/查询慢日志

# 方式一： 改变全局变量
# 查询慢查询日志配置
show variables like '%slow%';
# 查看慢查询时间，默认10s，建议降到2s或以下
show variables like 'long_query_time';
# 开启 慢查询日志
set global  slow_query_log = 1;
# 大于2每秒的sql写入日志 需要重新连接mysql
set global long_query_time = 2;
# 慢查询日志文件路径
set global slow_query_log_file='/tmp/slow_querys.log';
#设置慢查询记录到表中  写入到mysql.slow_log表，支持同时两种日志存储方式‘FILE,TABLE’
set global log_output='TABLE';


# 方式二：改变配置文件
[mysqld]
slow_query_log = ON
#定义慢查询日志的路径
slow_query_log_file = /tmp/slow_querys.log
#定义查过多少秒的查询算是慢查询，我这里定义的是1秒，5.6之后允许设置少于1秒，例如0.1秒
long_query_time = 1
#用来设置是否记录没有使用索引的查询到慢查询记录,默认关闭,看需求开启,会产生很多日志,可动态修改
#log-queries-not-using-indexes
管理指令也会被记录到慢查询。比如OPTIMEZE TABLE, ALTER TABLE,默认关闭,看需求开启,会产生很多日志,可动态修改
#log-slow-admin-statements

查询慢查询日志：

1. 记录表中的慢sql，直接查表mysql.slow_log

# 执行sql 睡眠3秒
select sleep(3);
# 查慢sql记录表
select * from mysql.slow_log;

我们可以看到，慢sql记录表中，已经记录了我们执行的睡眠3秒的sql，已经执行的详细信息。

2. 慢日志输出到log日志中，查看

# 将慢日志输出到log中
set global log_output ='FILE';

# 查询慢日志的输出方式
show variables like '%log_output%'

默认地址在data中，win可以直接打开

TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 2021-12-15T05:29:31.753772Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    12
# Query_time: 4.999791  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
use bigdatabase;
SET timestamp=1639546166;
/* ApplicationName=DataGrip 2021.1.3 */ select  sleep(5);

linux服务器下使用mysql自带的mysqldumpslow工具查看分析慢日志，下面有详解日志的分析

慢查询日志参数说明如下：

log 记录的时间：# Time: 2021-12-15T05:29:31.753772Z
SQL 的执行主机：#User@Host: root[root] @ localhost [127.0.0.1] Id: 12
SQL 的执行信息（执行时间(单位：s)，锁时间，返回结果行数，查询总行数）：# Query_time: 4.999791 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SQL 执行发生的时间：SET timestamp=1639546166;
SQL 的执行内容：# select sleep(5);

慢查询日志分析

虽然慢查询日志已经够清晰，但是往往我们的日志记录到的不是只有一条sql，可能有很多很多条，如果不加以统计，估计要看到猴年马月，这个时候就需要做统计分析了。而这种慢查询日志分析的工具，也比较多，下面拿mysql自带的工具mysqldumpslow进行分析使用。

mysqldumpslow工具分析慢查询log

# 执行命令
mysqldumpslow -s c -t 10 /tmp/slow-log

# mysqldumpslow 参数解释
mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
 
Parse and summarize the MySQL slow query log. Options are
 
  --verbose    verbose
  --debug      debug
  --help       write this text to standard output
 
  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time


########说明###########
-s, 是表示按照何种方式排序，

    c: 访问计数

    l: 锁定时间

    r: 返回记录

    t: 查询时间

    al:平均锁定时间

    ar:平均返回记录数

    at:平均查询时间

-t, 是top n的意思，即为返回前面多少条的数据；

-g, 后边可以写一个正则匹配模式，大小写不敏感的；

#####例如####
# 得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

windows下使用慢查询日志分析工具之mysqldumpslow

比较的五款常用工具：mysqldumpslow, mysqlsla, myprofi, mysql-explain-slow-log, mysqllogfilter

Relay log(中继日志)

MySQL进行主主复制或主从复制的时候会在home目录下面产生相应的relay log

Relay log概念

The relay log, like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files.

The term “relay log file” generally denotes an individual numbered file containing database events. The term”relay log” collectively denotes the set of numbered relay log files plus the index file
解释理解：relay log很多方面都跟binary log差不多。

relaylog & binlog区别是：从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致

Relay log 参数配置

# 查看中继日志
show variables like ‘%relay%’

参数含义：

max_relay_log_size：标记relay log 允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小
relay_log：定义 relay_log 的位置和名称，如果值为空，则默认位置在数据文件的目录；
relay_log_index：定义 relay_log 索引的位置和名称，记录有几个 relay_log 文件，默认为2个
relay_log_info_file：定义 relay-log.info 的位置和名称
relay_log_purge：是否自动清空中继日志，默认值为1(启用)；
relay_log_recovery：当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启；
sync_relay_log：设置如何同步中继日志到中继日志文件。
- 当sync_relay_log = 0时，则MySQL服务不会对中继日志文件进行同步操作，依赖于操作系统来定期进行同步。
- 当sync_relay_log = N(N>0)，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，每N个sync_relay_log事件后对中继日志文件执行一次同步(调用fdatasync())。
sync_relay_log_info：用于设置如何将应用中继日志的位置信息同步到文件和表中，默认参数为10000
- 当sync_relay_log_info = FILE时：
  - 如果sync_relay_log_info=0，则MySQL服务不会对relay-log.info文件进行同步操作，依赖于操作系统来定期进行同步。
  - 如果sync_relay_log_info=N(N>0)，则每执行N个事务后将信息使用fdatasync()同步到relay-log.info文件。
- 当sync_relay_log_info = TABLE 且表mysql.slave_relay_log_info使用事务存储引擎如Innodb：
  - 在每次事务后都会更新mysql.slave_relay_log_info表的数据，忽略sync_relay_log_info的设置。
- 当sync_relay_log_info = TABLE 且表mysql.slave_relay_log_info不使用存储引擎如MyISAM：
  - 如果sync_relay_log_info=0,则不更新表mysql.slave_relay_log_info的数据。
  - 如果sync_relay_log_info=N(N>0)，则每执行N个事务后更新表mysql.slave_relay_log_info的数据。
    elay_log.info内容同步至磁盘

生产环境应用

#推荐从库线上环境使用以下配置
 #relay log
max_relay_log_size = 0；
relay_log=$datadir/relay-bin
relay_log_purge = 1;
relay_log_recovery = 1;
sync_relay_log =0；
sync_relay_log_info = 0；

# 如果是mha环境，则| relay_log_purge 不要开启，设置为0，可以使用 purge_relay_logs 来定期清除

更多：

中继日志【重】

复制应用中继日志解析

搬家啦，点击进入个人网站
学习时刻

mysql 日志

mysql日志

日志分类

Redo Log(重做日志)

redo log工作原理图

redo log写入机制

redo log循环写

redo log参数配置

undo log(回滚日志)

undo log 基本概念

undo log 存储方式

undo log工作原理图

undo log参数配置

delete/update操作的内部机制

binlog(二进制日志)

Binlog基本概念

Binlog作用及使用场景

Binlog删除/暂停

MySQL主备

Binlog 日志格式

Binlog 事件类型

Binlog 事件结构

Binlog Event 简要分析

Binlog持久化

Binlog操作常用命令集合

Error Log(错误日志)

启动和设置错误日志

查看错误日志

删除错误日志

General Query Log(通用查询日志)

Slow Query Log(慢查询日志)

查询/设置慢查询日志参数

慢查询日志分析

Relay log(中继日志)

Relay log概念

Relay log 参数配置

生产环境应用

相关Mysql文章系列

公告

搬家啦，点击进入个人网站学习时刻

mysql 日志

mysql日志

日志分类

Redo Log(重做日志)

redo log工作原理图

redo log写入机制

redo log循环写

redo log参数配置

undo log(回滚日志)

undo log 基本概念

undo log 存储方式

undo log工作原理图

undo log参数配置

delete/update操作的内部机制

binlog(二进制日志)

Binlog基本概念

Binlog作用及使用场景

Binlog删除/暂停

MySQL主备

Binlog 日志格式

Binlog 事件类型

Binlog 事件结构

Binlog Event 简要分析

Binlog持久化

Binlog操作常用命令集合

Error Log(错误日志)

启动和设置错误日志

查看错误日志

删除错误日志

General Query Log(通用查询日志)

Slow Query Log(慢查询日志)

查询/设置慢查询日志参数

慢查询日志分析

Relay log(中继日志)

Relay log概念

Relay log 参数配置

生产环境应用

相关Mysql文章系列

公告

搬家啦，点击进入个人网站
学习时刻