Mysql日志详解

1、Binlog

Binlog属于Mysql server层的维护的一种二进制日志，主要记录对于对Mysql数据更新的SQL语句，以“event”的形式保存在磁盘中

Binlog的写入时机是SQL transaction 执行完，但是任何相关的Locks还没释放或者事务还没最终commit前，这样就保证了Binlog记录的操作时序与数据库实际的数据变更顺序一致

1.1、Binlog的主要作用

• 数据恢复：通过mysqlbinlog工具恢复数据
• 主从复制：MySQL在Master端开启binlog，Master把它的二进制日志传递给slaves并回放(中继日志)来达到master-slave数据一致的目的

1.2、Binlog文件类型

• 二进制日志索引文件(.index)：记录所有的二进制文件
• 二进制日志文件(.00000*)：记录所有 DDL 和 DML 语句事件

1.3、相关命令

-- 检查 Binlog 文件是否已开启
show variables like '%log_bin%';

-- 查看binlog列表
show binary logs;

-- 查看最新的binlog
show master status;

-- 查看 Binlog 日志
mysqlbinlog mysql-bin.00000* | more

-- 查看当前 Binlog 文件对应的详细事件信息
show binlog events in 'mysql-bin.00000*';

-- 查询当前 Binlog 日志使用格式 Statement|Row|Mixed
show global variables like '%binlog_format%';

--mysqlbinlog 命令手动恢复数据 
mysqlbinlog --start-position=0  --stop-position=500  mysql-bin.00000* > /root/back.sql;

1.4、相关参数

my.cnf文件中对于binlog的配置参数：

• log-bin=/home/mysql/binlog/ 设置此参数表示启用binlog功能，并制定二进制日志的存储目录
• max_binlog_size=104857600 日志文件最大字节（单位：字节） Binlog 最大和默认值是 1G
• expire_logs_days = 7 设置了只保留7天BINLOG（单位：天）
• binlog-do-db=db_name binlog日志只记录指定库的更新
• binlog-ignore-db=db_name binlog日志不记录指定库的更新
• sync_binlog=0 写缓冲多少次，刷一次磁盘，默认0

---

Binlog的过期删除不是服务定时执行，而是由事件触发的，能够触发的事件包括

• 服务器重启

• 服务器被更新

• 日志达到了最大长度

• 日志被刷新

---

Binlog的刷新频率：sync_binlog默认为0 ，代表由操作系统根据自身负载自行决定多久写一次磁盘，如果修改为1或者n，那么代表执行每1次或者n次事务提交时会立刻写入磁盘

---

1.5、日志格式

Binlog提供了三种模式来记录日志：

• Statement 模式：基于 SQL 语句的复制(statement-based replication-SBR) 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能

  缺点：特定函数功能不能进行复制，如sleep()、UUID()

• Row 模式：基于行的复制(row-based replication-RBR) 优点：非常清楚的记录下每一行数据修改的细节 缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容

• Mixed 模式：混合模式复制(mixed-based replication-MBR) 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式

2、redo log

redo log属于Mysql Innodb引擎层维护的日志数据，记录的数据是事务执行过程中的执行情况，主要用来保证事务的持久性

先回顾一下为什么需要redo log，为了解决什么问题，这样才能更好的理解和学习

数据库的主要功能就是数据存储，存储的地方就是操作系统的磁盘空间，这就涉及到了磁盘IO，如果每次更改数据库都需要把数据写入磁盘文件，那么文件IO就会成为数据库的性能瓶颈！

如何解决：性能不够，内存来凑。数据库在数据改变的时候先把改变数据放入内存进行修改，然后等待时机把内存数据同步到磁盘文件进行持久化。内存分为用户空间和内核空间，数据库层面只有先把数据写入用户空间，然后切换到内核空间，执行fsync()进行刷盘操作，把内核空间的数据写入数据库磁盘文件（随机写入），到了这一步还存在什么问题？

问题有两个：

1、写数据库文件属于随机IO，性能不高

2、以什么样的频率进行刷盘操作，如果存在延迟，那么如何保证系统崩溃的时候，数据不丢失？也就是事务已经提交了（数据进入内存），但是没有写入磁盘文件，这时候系统崩溃了，也就无法保证持久性

3、Innodb是以页为单位进行磁盘交互，如果一个事务只修改了一个数据页中的几个字段，那也需要把整个数据页刷到磁盘，太浪费了；如果一个事务跨多个数据页，那么在刷盘的时候随机IO性能差

所以有问题就需要有解决方案：redo log，解决了什么问题？

1、redo log 文件大小固定，可以使用顺序写（顺序磁盘IO是随机IO性能的3-5倍）

2、数据改变的时候，先写入redo log磁盘文件进行持久化，然后再写入内存，等待刷盘进行持久化，如果数据库数据持久化之前系统崩溃，那么就可以使用redo log进行数据恢复（崩溃回复crash-safe），这种先写磁盘然后写入内存等待刷盘的操作也称之为WAL(write ahead log)

3、redo log只记录修改的数据，减少IO数量

---

接下来整体再总结一下：

1、数据库事务开始的时候进行逐步写盘（先写入重做日志缓冲区Innodb_log_buffer，默认大小为8M）

2、redo log持久化成功之后，把数据的改变写入log buffer内存中

3、内存中的数据根据策略进行刷盘操作，进行数据库数据真正的持久化到磁盘

4、如果持久化之前数据崩溃，重启时使用redo log的数据进行数据恢复

引用《MySQL技术内幕 Innodb 存储引擎》（page37）上的原话

即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。　　 这一点是必须要知道的，因为这可以很好地解释再大的事务的提交（commit）的时间也是很短暂的。

Mysql提供了三种redo log持久化策略：

• 0 ：只写redo log buffer（延迟刷），每秒落地一次，性能最高，但是一致性最差，有可能丢失1秒的数据，也就是每次事务提交，只把数据写入内存，每隔一秒进行一次OS flush的刷盘操作。
• 1 ：写redo log buffer同时调用OS flush，性能最差，一致性最高
• 2 ：写redo log buffer同时写os buffer，之后每隔一秒自动调用flush，性能高，一致性也好，这样即使mysql服务崩溃也不会丢失数据，但是如果操作系统崩溃，会丢失1秒的数据

如何查看目前使用的哪种策略：

show variables like '%innodb_flush_log_at_trx_commit%';

如何修改redo log持久化策略：

-- 修改mysql.ini文件中的属性
innodb_flush_log_at_trx_commit=1

---

redo log 和bin log之间的区别

	redo log	binlog
文件大小	大小固定	参数max_binlog_size设置
实现方式	Innodb引擎专属实现	server层实现，所有引擎都用
记录方式	循环写，写到结尾会回头覆盖最开始的日志	追加写，超过大小之后重开新文件进行记录
适用场景	崩溃回复crash-safe	复制和数据恢复
文件内容	是数据页修改之后的物理记录	简单认为就是记录sql，属于逻辑日志

3、undo log

undo log属于Mysql Innodb引擎层维护的日志数据，记录的数据是事务执行过程中数据的逻辑变化，主要用来保证事务的原子性

undo log主要有两个作用：

• 事务回滚
• MVCC

本篇文章主要记录关于事务回滚的作用，关于MVCC下次专门开一篇文章进行记录

先回顾一下为什么需要undo log？

事务原子性代表着事务内的所有操作，要么全部成功，要么全部失败，如果执行到中间的语句，执行失败了，需要把之前执行的数据全部恢复到执行之前的状态，这种机制就需要undo log来完成。undo log记录的是对应当前事务操作的相反操作，比如，事务插入一条数据，那么undo log就需要记录一条根据主键删除数据的日志，方便事务回滚的时候进行数据恢复。

---

那么什么时候会进行undo log的写入？

进行数据修改或者删除的时候会记录undo log，每一种操作对应的undo log的类型是不一样的，如图所示：

• insert操作：undo log的类型是 TRX_UNDO_INSERT_REC
  • start,end：指向记录开始和结束的位置
  • undo type：undo log的类型
  • undo no：在当前事务中的undo log的编号
  • table id：表空间id
  • 主键列信息：记录主键或者唯一列信息
• delete操作：delete并不会直接删除数据，而是先将数据记录头中的delete_mark标记为1（因为其它并发的事务可能还需要读取此数据），提交事务后，后台有一个purge线程会将数据真正的删除。对应的undo log的类型是TRX_UNDO_DEL_MARK_REC，存储的信息比insert多出来三列
  • old trx_id：这个属性会保存记录中的隐藏列trx_id，这个属性在MVCC并发读的时候会起作用
  • old roll_pointer：这个属性保存了记录中的隐藏列roll_point，可以通过这个属性找到之前的undo log
  • 索引列信息：这部分主要是用来在第二阶段事务提交后真是删除数据的
• update操作：update操作分为两种情况，一种是没有更新主键的，会产生类型为 TRX_UNDO_UPD_EXIST_REC的undo log；一种是更新主键的，会产生两种undo log，删除旧主键、插入新主键，也就是TRX_UNDO_INSERT_REC和TRX_UNDO_DEL_MARK_REC。 TRX_UNDO_UPD_EXIST_REC相比于TRX_UNDO_DEL_MARK_REC主要是多了更新列的信息
  • 更新列信息：更新了哪一些列的原有信息

---

undo log链

在一个事务中，对数据的连续操作会生成一个又一个的undo log，这些undo log通过old roll_pointer进行链式的连接，组成一个调用链，在事务回滚的时候，通过undo no从大到小的顺序进行数据恢复

比如：存在一张user表，id、name、age三个字段，做如下操作

begin;
insert into user (id,name,age) values(1,'张三',18);
update user set age = 20 where id = 1;
update user set id = 2 where id = 1;
commit;

这三个操作会产生4个undo log，如下图所示

4、慢查询日志

快和慢都是相对来说的，对于SQL来说，执行时间多久算的上慢？这种没有统一的标准，都是根据业务场景来具体判断，Mysql可以进行参数配置来定义超过多久时间的SQL算是慢SQL

-- 慢查询日志状态
show variables like "slow_query_log";

-- 开启慢查询日志
set global slow_query_log = "ON";

-- 查看慢查询日志存储位置
show variables like "slow_query_log_file";

-- 修改日志存储位置和日志名称
set global slow_query_log_file = ${path}/${filename}.log;

--查看慢查询日志规定时间
show variables like "long_query_time";

--设置慢查询时间 单位 秒
set global long_query_time = 0.5;