mysql日志

java搬运工

已于 2024-05-04 15:34:46 修改

阅读量579

点赞数 17

分类专栏： Mysql 文章标签： mysql

于 2024-05-03 15:53:06 首次发布

本文链接：https://blog.csdn.net/Hkdg1651648029/article/details/138417305

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

日志种类

1、事务日志（redo log与undo log）

2、二进制日志（binlog）

3、中继日志（relay log）

4、慢查询日志（slow query log）

5、一般查询日志（general log）

6、错误日志（errorlog）

A原⼦性由undo log⽇志保证，它记录了需要回滚的⽇志信息，事务回滚时撤销已经执⾏成功的sql

C⼀致性⼀般由代码层⾯来保证

I隔离性由MVCC来保证

D持久性由内存+redo log来保证， mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

redo log 与 binlog

MySQL整体来看，其实就有两块：一块是Server层，它主要做的是MySQL功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。

redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog（归档日志）。

这两种日志有以下三点不同。

1. redo log是InnoDB引擎特有的； binlog是MySQL的Server层实现的，所有引擎都可以使用。

2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”； binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。

3. redo log是循环写的，空间固定会用完； binlog是可以追加写入的。 “追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

4.redo log 和 binlog有一个共同的数据字段，叫XID，通过该字段关联。

5.redo log通常是物理日志，记录的是数据页的物理修改，即事务执行后的状态。redo log就是为了恢复更新了内存但是由于宕机等原因没有刷入磁盘中的那部分数据。

6.undo log一般是逻辑日志，记录的是修改之前的数据。提供版本回滚和多个行版本控制(MVCC)，当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取（当前读）。

7.redo log是事务持久性的保证，undo log是事务原子性的保证。

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB。日志从头开始写，写到末尾就又回到开头循环写。

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。如果write pos追上checkpoint，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

InnoDB引擎执行SQL语句时的内部流程

update语句

1. 执行器先找引擎取ID=2这一行。 ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。

4. 执行器生成这个操作的binlog，并把binlog写入磁盘。

5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

insert语句

数据日志一致性保证

redo log两阶段提交

由于redo log和binlog是两个独立的逻辑，如果不用两阶段提交，要么就是先写完redo log再写binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

1. 先写redo log后写binlog。假设在redo log写完， binlog还没有写完的时候， MySQL进程异常重启。由于我们前面说过的， redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。

但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。

然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。

2. 先写binlog后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。

但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

两阶段提交就是为了保证redo log和bing log一致性。

1. 一种是， redo log buffer占用的空间即将达到 innodb_log_buffer_size一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是write，而没有调用fsync，也就是只留在了文件系统的page cache。

2. 另一种是，并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘。假设一个事务A执行到一半，已经写了一些redo log到buffer中，这时候有另外一个线程的事务B提交，如果innodb_flush_log_at_trx_commit设置的是1，那么按照这个参数的逻辑，事务B要把redo log buffer里的日志全部持久化到磁盘。这时候，就会带上事务A在redolog buffer里的日志一起持久化到磁盘。

redo log 与 binlog相关配置

sync_binlog

取值为0：mysql 自己不主动同步，依赖操作系统本身不定期把文件内容刷新到磁盘。性能最佳

取值为1：每次事务提交后将 binlog_cache 中的数据强制写入磁盘 bin log日志中，是最慢的，但是最安全

取值 >1：当进行n次事务提交后，mysql 将 binlog_cache 中的数据强制写入磁盘中。

当sync_binlog=1，还会存在另外问题。当使用InnoDB存储引擎时，在一个事务发出commit动作之前，由于sync_binlog设为1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在Mysql数据库下次启动时，由于commit操作并没有发生，所以这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。

这个问题，可以将innodb_support_xa设为1来解决，确保二进制日志和InnoDB存储引擎数据文件的同步。

从官方解释来看，innodb_support_xa的作用是分两类：

- 支持多实例分布式事务（外部xa事务），这个一般在分布式数据库环境中用得较多。

- 支持内部xa事务，说白了也就是说支持binlog与innodb redo log之间数据一致性。

innodb_flush_log_at_trx_commit

设置为0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作;

设置为1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去;

设置为2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

注意：由于进程调度策略问题,这个"每秒执行一次 flush(刷到磁盘)操作"并不是保证100%的"每秒"。

binlog

statement

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

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

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

mysql> delete from t where a>=4 and t_modified<='2018-11-10' limit 1;

mysql> show binlog events in 'master.000001';

分析图上的结果。

第一行 SET @@SESSION.GTID_NEXT='ANONYMOUS’你可以先忽略，后面文章我们会在介绍主备切换的时候再提到；
第二行是一个 BEGIN，跟第四行的 commit 对应，表示中间是一个事务；
第三行就是真实执行的语句了。可以看到，在真实执行的 delete 命令之前，还有一个“use ‘test’”命令。这条命令不是我们主动执行的，而是 MySQL 根据当前要操作的表所在的数据库
，自行添加的。这样做可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确地更新到 test 库的表 t。use 'test’命令之后的 delete 语句，就是我们输入的 SQL 原文了。可以看到，binlog“忠实”地记录了 SQL 命令，甚至连注释也一并记录了。
最后一行是一个 COMMIT。你可以看到里面写着 xid=61。

xid是binlog与redo log共同的数据字段，崩溃恢复的时候，会按顺序扫描redo log

如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

binlog设置的是statement格式的，并且delete带有limit，很可能会出现主从库数据不一致的情况。比如上面这个例子。

如果 delete 语句使用的是索引 a，那么会根据索引 a 找到第一个满足条件的行，也就是说删除的是 a=4 这一行；
但如果使用的是索引 t_modified，那么删除的就是 t_modified='2018-11-09’也就是 a=5 这一行。

由于 statement 格式下，记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。因此，MySQL 认为这样写是有风险的。

row

不记录sql语句上下文相关信息，仅保存哪条记录被修改。

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

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

mysql> delete from t where a>=4 and t_modified<='2018-11-10' limit 1;

mysql> show binlog events in 'master.000001';

与 statement 格式的 binlog 相比，前后的 BEGIN 和 COMMIT 是一样的。但是，row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows。

Table_map event，用于说明接下来要操作的表是 test 库的表 t;
Delete_rows event，用于定义删除的行为。

把格式改成row的话，我们是看不到详细信息的。还需要借助mysqlbinlog工具，用下面这个命令解析和查看binlog中的内容。从上图可以得知，这个事务的binlog是从8900这个位置开始的。所以可以用 start-position 参数来指定从这个位置的日志开始解析。

mysqlbinlog -vv data/master.000001 --start-position=8900;

server id 1，表示这个事务是在 server_id=1 的这个库上执行的。
每个 event 都有 CRC32 的值，这是因为我把参数 binlog_checksum 设置成了 CRC32。
Table_map event 跟在图 5 中看到的相同，显示了接下来要打开的表，map 到数字 226。现在我们这条 SQL 语句只操作了一张表，如果要操作多张表呢？每个表都有一个对应的 Table_map event、都会 map 到一个单独的数字，用于区分对不同表的操作。
我们在 mysqlbinlog 的命令中，使用了 -vv 参数是为了把内容都解析出来，所以从结果里面可以看到各个字段的值（比如，@1=4、 @2=4 这些值）。
binlog_row_image 的默认配置是 FULL，因此 Delete_event 里面，包含了删掉的行的所有字段的值。如果把 binlog_row_image 设置为 MINIMAL，则只会记录必要的信息，在这个例子里，就是只会记录 id=4 这个信息。
最后的 Xid event，用于表示事务被正确地提交了。

你可以看到，当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除 id=4 的行，不会有主备删除不同行的问题。

mixed

是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。也就是说， mixed格式可以利用statment格式的优点，同时又避免了数据不一致的风险。

比如我们这个例子，设置为 mixed 后，就会记录为 row 格式；而如果执行的语句去掉 limit 1，就会记录为 statement 格式。