MySQL日志

标签：binlog log 文件数据库 MySQL 日志 redo

一条update的执行流程

执行流程分为在server层和存储引擎层；server是MySQL都有的，其日志文件是binlog；存储引擎是不同的，undolog，redolog是innodb特有的。

首先是客户端创建请求，然后去服务层请求；

然后是server的连接器，连接器的作用是校验用户是否有权限进行查询等等。

（在8.0版本之前有查询缓存，主要是针对select语句，但是8.0已经拿掉了这个功能，作用不大）

然后是分析器，分析器对词法语法进行分析，先词法分析判断是什么请求，要执行的操作是什么；然后是语法分析，看是否符合该操作的语法；

然后是优化器，优化器针对SQL进行优化，看是否要走索引等等；

然后就是执行器，执行器再次校验权限，权限满足就开始去调用innodb引擎提供的接口进行后续一系列的操作；

存储引擎中的操作：

先去存储引擎的缓存buffer pool中查询是否有这条数据；如果没有就从数据库磁盘中加载到缓存中，是在内存中的；加载是整页加载，根据就近性原则加载多条数据；
然后将这条数据的旧址写入到undo log日志版本链中，便于后续的回滚提交等操作，为了保证原子性；
然后在缓存buffer中将数据修改，此时可以认为数据修改完成；然后将数据写入到page cache中；
然后就是写redo log，这个redo log是分两阶段提交的，刚开始提交到redo log buffer（缓存中），还要写入到操作系统缓存page cache中，然后才能刷盘。完整的刷盘是三步。
此时redo log写入到buffer中，这里是顺序写，效率比较高，此时的提交是半事务，prepare阶段；
然后就是执行器写biglog文件，biglog文件没有buffer，就直接写入到操作系统的page cache，然后是刷盘；
biglog刷盘成功，然后执行器触发执行一个commit标记到redo log中，事务完全提交；也会令undo log中的状态变成commit；
然后就是操作系统根据配置信息将page cache中的数据库真正要修改的值刷新到磁盘中。

redo和binlog都是为了保证数据的持久性；redo在数据库宕机，buffer pool中的数据还没有写入到磁盘中进行数据恢复的；

binlog，主要是为了数据库的主从复制；还有就是误删了数据，可以根据binlog的备份日志将数据恢复。所以binlog也成为备份日志。

redo的写是顺序写的，因为默认设置好的一个或多个文件，都是从尾部追加写的，有两个指针，一个是追加写，另外一个是用来标记是否被刷到磁盘，两个标记中间的就是可用空间，当追加写指针赶上标记指针时，先暂停，将redo 中的部分数据刷盘，清理一下文件，然后继续写入。

为什么要搞的这么复杂？

因为修改可能是多张表，分别在不同的ibd文件中，这些在磁盘中的存储位置可能是不连续的，如果直接写，那就是随机写，要先找到位置，然后才能修改，这样比较耗时；使用redo的顺序写可用大幅的提高效率，这个也是异步的经典体现。

redo log重做日志关键参数

innodb_log_buffer_size：设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。

 show variables like '%innodb_log_buffer_size%';

innodb_log_group_home_dir：设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件。

show variables like '%innodb_log_group_home_dir%';

innodb_log_files_in_group：设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1... iblogfileN。默认2个，最大100个。是交替写的，先file0，写满之后是file1，file1满了之后重新写file0.

show variables like '%innodb_log_files_in_group%';

innodb_log_file_size：设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redolog系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。

show variables like '%innodb_log_file_size%';

redo log 写入磁盘过程分析：

redo log 从头开始写，写完一个文件继续写另一个文件，写到最后一个文件末尾就又回到第一个文件开头循环写，如下面这个图所示。

默认redo在写的时候分4块，从0开始写，一直写到3，然后再从0开始继续写。一直循环往复。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。 checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件里。 write pos 和 checkpoint 之间的部分就是空着的可写部分，可以用来记录新的操作。如果 write pos 追上checkpoint，表示redo log写满了，这时候不能再执行新的更新，这时要暂停写redo log，先擦掉（将数据刷盘，这样就可用清空这些数据）一些记录，把 checkpoint 推进一下。让两者之间有空间可用。 innodb_flush_log_at_trx_commit：这个参数控制 redo log 的写入策略，它有三种可能取值：

设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，之后就立刻commit事务，效率最高，但是数据库宕机可能会丢失数据。
设置为1(默认值)：表示每次事务提交时都将 redo log 直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。
设置为2：表示每次事务提交时都只是把 redo log 先写到buffer中，然后写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。如果对数据一致性要求不是很高，就可以认为写入到page cache就是写入到磁盘了。

redo log，redo log buffer这两个都是MySQL管理的，page cache是操作系统管理的内存空间。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用操作系统函数 write 写到文件系统的page cache，然后调用操作系统函数 fsync 持久化到磁盘文件。

从redo log buffer到操作系统缓存page cache，再从page cache到真正的磁盘文件；这是经过了两个步骤，这也是异步的经典体现。

page cache也是内存的一部分，其作用就是为了解决内存和磁盘交互速度瓶颈的，类似CPU的高速缓存。

binlog二进制归档日志

binlog二进制日志记录保存了所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。启动binlog记录功能，会影响服务器性能，但如果需要恢复数据或主从复制功能，则好处则大于对服务器的影响。

# 查看binlog相关参数
show variables like '%log_bin%';

log_bin：binlog日志是否打开状态
log_bin_basename：是binlog日志的基本文件名，后面会追加标识来表示每一个文件，binlog日志文件会滚动增加
log_bin_index：指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
sql_log_bin：sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。

MySQL5.7 版本中，binlog默认是关闭的，8.0版本默认是打开的。上图中log_bin的值是ON就代表binlog是打开状态，打开binlog功能，需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。

第一个就是binlog日志文件，第二个index是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。

一些重要的配置项：

binlog_format = row # 日志文件格式，下面会详细解释 expire_logs_days = 15 # 执行自动删除binlog日志文件的天数，默认为0，表示不自动删除。 max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

当然也可以执行命令查看有多少binlog文件

show binary logs;

binlog 的日志格式

用参数 binlog_format 可以设置binlog日志的记录格式，mysql支持三种格式类型：

STATEMENT：基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小，节约IO开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样。这种方式只是记录了一条SQL，具体SQL的改动量并没有记录。类似逻辑记录
ROW：基于行的复制，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，但这种方式日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。
MIXED：混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

binlog写入磁盘机制

binlog写入磁盘机制主要通过 sync_binlog 参数控制，默认值是 0。

为0的时候，表示每次提交事务都只 write 到page cache，由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。
也可以设置为1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。
还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后才 fsync 写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

发生以下任何事件时, binlog日志文件会重新生成：

服务器启动或重新启动
服务器刷新日志，执行命令flush logs
日志文件大小达到 max_binlog_size 值，默认值为 1GB

删除 binlog 日志文件

# 删除当前的binlog文件
reset master;
# 删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除
purge master logs to 'mysql‐binlog.000006';
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2023‐01‐21 14:00:00';

可用通过binlog进行数据的恢复。

注意：如果要恢复大量数据，比如程序员经常说的删库跑路的话题，假设我们把数据库所有数据都删除了要怎么恢复了，如果数据库之前没有备份，所有的binlog日志都在的话，就从binlog第一个文件开始逐个恢复每个binlog文件里的数据，这种一般不太可能，因为binlog日志比较大，早期的binlog文件会定期删除的，所以一般不可能用binlog文件恢复整个数据库的。一般我们推荐的是每天(在凌晨后)需要做一次全量数据库备份，那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的binlog来恢复数据。注意，备份的频次要始终，太少的情况下，可能因为备份失败而导致丢失数据。最好是每天凌晨备份一份数据。备份数据库一般可以用mysqldump 命令工具。

mysqldump ‐u root 数据库名>备份文件名; #备份整个数据库
mysqldump ‐u root 数据库名 表名字>备份文件名; #备份整个表
mysql ‐u root test < 备份文件名 #恢复整个数据库，test为数据库名称，需要自己先建一个数据库test

为什么会有redo log和binlog两份日志呢？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有crash-safe 能力的，所以InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crashsafe。

undo log回滚日志

InnoDB对undo log文件的管理采用段的方式，也就是回滚段（rollback segment）。每个回滚段记录了 1024 个undo log segment ，每个事务只会使用一个undo log segment。在MySQL5.5的时候，只有一个回滚段，那么最大同时支持的事务数量为1024个。在MySQL 5.6开始，InnoDB支持最大 128个回滚段，故其支持同时在线的事务限制提高到了 128*1024 。

innodb_undo_directory：设置undo log文件所在的路径。该参数的默认值为"./"，即innodb数据文件存储位置，目录下ibdata1文件就是undo log存储的位置。
innodb_undo_logs: 设置undo log文件内部回滚段的个数，默认值为128。
innodb_undo_tablespaces: 设置undo log文件的数量，这样回滚段可以较为平均地分布在多个文件中。设置该参数后，会在路径innodb_undo_directory看到undo为前缀的文件。

undo log日志什么时候删除

新增类型的，在事务提交之后就可以清除掉了。修改类型的，事务提交之后不能立即清除掉，这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。

为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。 Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。

错误日志

Mysql还有一个比较重要的日志是错误日志，它记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。在MySQL数据库中，错误日志功能是默认开启的，而且无法被关闭。

# 查看错误日志存放位置
show variables like '%log_error%';

通用查询日志

通用查询日志记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给MySQL 数据库服务器的所有 SQL 指令等，如select、show等，无论SQL的语法正确还是错误、也无论SQL执行成功还是失败，MySQL都会将其记录下来。通用查询日志用来还原操作时的具体场景，可以帮助我们准确定位一些疑难问题，比如重复支付等问题。 general_log：是否开启日志参数，默认为OFF，处于关闭状态，因为开启会消耗系统资源并且占用磁盘空间。一般不建议开启，只在需要调试查询问题时开启。 general_log_file：通用查询日志记录的位置参数。

show variables like '%general_log%';
# 打开通用查询日志
SET GLOBAL general_log=on;

标签：binlog,log,文件,数据库,MySQL,日志,redo
From： https://www.cnblogs.com/0630sun/p/18167837