1. MySQL有六种日志:
重做日志(redo log)
回滚日志 (undo log)
二进制日志 (binlog)
错位日志 (errolog)
慢查询日志 (slow query log)
一般查询日志 (general log)
中继日志 (relay log)
我们需要特别关注的是二进制日志(binlog) 和事务日志(redo log、undo log)
2. 二进制日志 binlog
基本内容:
- binlog 是sql server 层维护的一种二进制日志。
- binlog 是用于记录数据库执行的写入性操作(不包括查询)信息,并以二进制的形式存在磁盘中。
- binlog 是MySQL的逻辑日志,并由Sql Server层进行记录,任何存储引擎的MySQL数据库都会记录这种日志。
- binlog 是通过追加的方式写入的,可以通过max_binlog_size参数设置每个binlog文件的大小,当大小达到定值之后,会生成新的文件来保存日志。
逻辑日志:可以简单理解为 记录的就是sql语句。
物理日志:因为MySQL数据最终是保存在数据页的,物理日志记录的是数据页的变更。
使用场景:
主从复制:在master端开启binlog,然后将binlog发送到各个slave端,slave端重放binlog从而达到主从数据一致。
数据恢复:用mysqlbinlog工具来恢复数据
日志的刷盘时机:
当进行事务提交时才会记录binlog,此时记录在内存中
MySQL通过sync_binlog 参数控制binlog的刷盘时机,取值范围是0~N
0:不去强制要求,有计算机系统自行判断何时写入磁盘。
1:每次commit的时候都会将binlog写入磁盘。
N:每N个事务,就会把binlog写入磁盘中。
其他知识点:
- binlog不会记录不修改数据的语句,比如select show等
- binlog会重写日志中的密码,保证密码不以纯文本的形式出现
- MySQL 8之后可以选择对binlog进行加密
- 删除时间:保持时间由参数expire_logs_days配置,也就是说对于非活动的日志文件,在生成一定时间超过expire_logs_days配置的天数之后,会被自动删除
常用命令:
- binlog的配置信息:show variables like '%log_bin%'
- binlog的格式: show variables like 'binlog_format'
- 日志的文件列表: show binary logs
- 当前日志的写入状态: show master status
- 清空binlog日志:reset master
binlog 的格式:ROW、 Statement、 Mixed
ROW格式:
仅保存记录被修改的细节,不记录sql 语句上下文相关信息。
新版本的MySQL默认格式是ROW
优点:能非常清晰的记录下每行数据的修改细节,不需要记录上下文相关信息,因此不会发生某些特定情况下的存储过程、函数或者触发器的调用触发无法被正确复制的问题,任何情况都可以被复制,且能加快从库重放日志的效率,保证从库数据的一致性
缺点: 由于所有的执行语句在日志中都将以每行记录的修改细节来记录,因此可能产生大量的日志内容,干扰内容比较多。
Statement
基于SQL语句的复制(statement-base replication SBR)
每一条会修改数据的SQL语句都会记录到binlog中
优点:不需要记录每一行的变化,减少了binlog的日志量,节约了IO,从而提升了性能。
缺点:在某写情况下回导致主从数据不一致,比如执行sysdate()、slepp()等。为了保证sql语句能在slave上正确执行,必须记录上下文信息,以保证语句能在slave得到和在master端执行时候相同的结果。
Mixed
以上两种格式的混合
一般的复制使用statement模式保存,对于statement模式无法复制的操作使用ROW模式保存binlog
主从复制
- master将数据改变记录到二进制文件binlog中
- slave上面的IO进程连接上master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容。
- slave接收到来自slave IO 进程的请求后,负责复制的IO进程会根据请求信息读取日志指定位置后的日志信息,返回给slave的IO进程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息已经到master端的binlog文件的名称以及binlog的位置。
- Slave 的 IO 进程接收到信息后,将接收到的日志内容依次添加到 Slave 端的 relaylog 文件的最末端,并将读取到的 Master 端的 binlog 的文件名和位置记录到 masterinfo 文件中,以便在下一次读取的时候能够清楚的告诉 Master 从某个 binlog 的哪个位置开始往后的日志内容
- Slave 的 SQL 进程检测到 relaylog 中新增加了内容后,会马上解析 relaylog 的内容成为在 Master 端真实执行时候的那些可执行的内容,并在自身执行。
redo log
- 保证事务的持久性
- dedo log包含两部分:一是内存中的日志缓存(redo log buffer)另一个是磁盘上的日志文件(redo log file)。
- MySQL每次执行MDL语句,先将记录写入redo log buffer,后续在某个时间点再一次性将多个操作记录写入到redo log file。这就是WAL(Write-Ahead Logging)技术。
- redo log buffer 写入redo log file 实际上是先写入OS buffer ,再通过系统调用fsync()将其刷到redo log file中。innodb_flush_log_at_trx_commit参数可以设置写入时机,0(延迟写,1秒调用一次fsync())、1(实时写、实施刷)、2(实时写、延迟刷)。
- redo log 是记录数据页的变更,采用了大小固定、循环写入的方式,即当写到结尾时,会回到开头循坏写日志。checkpoint 和 LSN(Log Sequence Number)
逻辑序列号(LSN log sequence number)
checkpoint:就是把脏页刷到磁盘的时间点,这个时间点之前的数据都已经保存到了持久存储。
启动innodb的时候,不管上次是正常关闭还是异常关闭,总是会进行恢复操作。因为redo log记录的是数据页的物理变化,因此恢复的时候速度比逻辑日志(如binlog)要快很多。
重启innodb时,首先会检查磁盘中数据页的LSN,如果数据页的LSN小于日志中的LSN,则会从checkpoint开始恢复。
还有一种情况,在宕机前正处于checkpoint的刷盘过程,且数据页的刷盘进度超过了日志页的刷盘进度,此时会出现数据页中记录的LSN大于日志中的LSN,这时超出日志进度的部分将不会重做,因为这本身就表示已经做过的事情,无需再重做。
undo log
- 提供回滚和多版本并发控制下的读(MVCC),即非锁定读
- 在数据修改的时候,不仅记录了redo,还记录了相对应的 undo,如果因为某些原因导致事务失败或回滚了,可以借助该 undo 进行回滚。
- undo log 和 redo log 记录物理日志不一样,它是逻辑日志。可以认为当 delete 一条记录时,undo log 中会记录一条对应的 insert 记录,反之亦然,当 update 一条记录时,它记录一条对应相反的 update 记录。
- undo log 是采用段(segment)的方式来记录的,每个 undo 操作在记录的时候占用一个 undo log segment。
- 当事务提交的时候,InnoDB 不会立即删除 undo log,因为后续还可能会用到 undo log,如隔离级别为 repeatable read 时,事务读取的都是开启事务时的最新提交行版本,只要该事务不结束,该行版本就不能删除,即 undo log 不能删除。
- 当事务提交之后,undo log 并不能立马被删除,而是放入待清理的链表,由 purge 线程判断是否有其他事务在使用 undo 段中表的上一个事务之前的版本信息,决定是否可以清理 undo log 的日志空间。