MySQL三种日志之binlog/redo log/undo log

   0. MySql的3种日志

 

    MySQL数据库为什么可以实现主从复制,持久化,回滚的呢?

其实关键在于MySQL里的三种log,分别是:

- binlog
- redo log
- undo log

1. 什么是binlog

binlog是用于记录数据库表结构和表数据变更的二进制日志

eg:
insert、update、delete、create、truncate等等操作。
不会记录select、show操作,因为没有对数据本身发生变更。
-----------------------------------------------------------------------------------
binlog会记录下每条变更的sql语句,还有执行开始时间,结束时间,事务id等等信息。

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

    - 查看binlog是否打开

     使用命令show variables like ‘%log_bin%‘

   - 开启binlog

    找到my.cnf配置文件,增加下面配置(mysql版本5.7.31):
    

# 打开binlog
log-bin=mysql-bin
# 选择ROW(行)模式
binlog-format=ROW

 

修改后,重启mysql,配置生效。

执行SHOW MASTER STATUS;可以查看当前写入的binlog文件名。

 

- binlog使用场景

主从复制和数据恢复。

1. 主从复制:
在Master端开启binlog, 从机订阅binlog日志的信息。
然后将binlog发送到各个Slave端,Slave端重放binlog从而达到主从数据一致。
-----------------------------------------------------------------------------------
2. 数据恢复:
通过使用mysqlbinlog工具来恢复数据。
指定--start-position和--stop-position,或者指定--start-datetime和--stop-datetime,那么就可以恢复指定区间的数据。

 

binlog主从复制(见下图)

MySQL三种日志之binlog/redo log/undo log

 

- binlog刷盘时机

对于InnoDB存储引擎而言,只有在事务提交时才会记录biglog,此时记录还在内存中。
mysql通过sync_binlog参数控制biglog的刷盘时机,
取值范围是0-N:

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

从上面可以看出,sync_binlog最安全的是设置是1,这也是MySQL 5.7.7之后版本的默认值。
但是设置一个大一些的值可以提升数据库性能,

因此实际情况下也可以将值适当调大,牺牲一定的一致性来获取更好的性能。

- binlog日志格式

binlog日志有三种格式,分别为STATMENT、ROW和MIXED。

在 MySQL 5.7.7之前,默认的格式是STATEMENT,
MySQL 5.7.7之后,默认值是ROW。
-----------------------------------------------------------------------------------
日志格式通过binlog-format指定。

STATMENT 
基于SQL语句的复制(statement-based replication, SBR)。

每一条会修改数据的sql语句会记录到binlog中。
优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO, 从而提高了性能;
缺点:在某些情况下会导致主从数据不一致,比如执行sysdate()、slepp()等。
-----------------------------------------------------------------------------------
ROW 
基于行的复制(row-based replication, RBR)。

不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了。
优点:不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题;
缺点:会产生大量的日志,尤其是alter table的时候会让日志暴涨
-----------------------------------------------------------------------------------
MIXED 
基于STATMENT和ROW两种模式的混合复制(mixed-based replication, MBR)。

一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog。

 

3. redo log

为什么需要redo log?

事务的四大特性里面有一个是持久性,具体来说就是只要事务提交成功,那么对数据库做的修改就被永久保存下来了,
不可能因为任何原因再回到原来的状态。
那么mysql是如何保证一致性的呢?最简单的做法是在每次事务提交的时候,
将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题,

主要体现在两个方面:
1. 因为Innodb是以页为单位进行磁盘交互的,而一个事务很可能只修改一个数据页里面的几个字节,
   这个时候将完整的数据页刷到磁盘的话,太浪费资源了!

2. 一个事务可能涉及修改多个数据页,并且这些数据页在物理上并不连续,使用随机IO写入性能太差!

因此mysql设计了redo log,
具体来说就是只记录事务对数据页做了哪些修改,
这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

 

redo log结构(见下图)

MySQL三种日志之binlog/redo log/undo log

 

redo log与binlog区别

 

MySQL三种日志之binlog/redo log/undo log

 

 

4. undo log

undo log的作用主要用于回滚,mysql数据库的事务的原子性就是通过undo log实现的。

 

我们都知道原子性是指对数据库的一系列操作,要么全部成功,要么全部失败。
undo log主要存储的是数据的逻辑变化日志,比如说我们要insert一条数据,
那么undo log就会生成一条对应的delete日志。
简单点说,undo log记录的是数据修改之前的数据,因为需要支持回滚。
那么当需要回滚时,只需要利用undo log的日志就可以恢复到修改前的数据。
undo log另一个作用是实现多版本控制(MVCC),undo记录中包含了记录更改前的镜像,
如果更改数据的事务未提交,对于隔离级别大于等于read commit的事务而言,
不应该返回更改后数据,而应该返回老版本的数据。

MySQL三种日志之binlog/redo log/undo log

上一篇:统计tomcat的access日志


下一篇:【linux】CentOS7 升级sqlite3