InnoDB底层原理之MySQL的日志机制

架构

Server

• 连接器：认证登录，连接管理；
• 缓存(静态表：系统配置表，字典表，建议使用)（Mysql8.0移除使用）；

在my.cnf中#query_cache_type有3个值0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存 query_cache_type=2

• 分析器：分析语法与字段；
• 优化器： 拆解SQL语句，选择执行路径的最优解；
• 执行器：调用存储引擎进行执行

存储引擎

Server层的执行器调用

语句执行过程

Server层对数据的操作都是在缓存里进行，不与磁盘直接交互

updatetableNamesetname="xxx"whereid=1;

1. 加载旧值

ibd磁盘中加载id=1的整页数据到bufferPool缓存中

2. 修改缓存中的旧值，并存储旧值

bufferPool更新name为新值，将旧值写入undo Log(如果是新增，redoLog为commit状态后，就删除；如果是修改，不能立即删除，需要mvcc中无事务用到才会删除("快照"))中；

3. 记录redo Log

将磁盘修改的大概位置记录在redo Log中，准备提交事务，为prepare阶段

4. 记录binLog

将sql语句的逻辑修改写入binLog

5. 修改redo Log为commit状态
6. 新值写入磁盘

系统空闲时，将buffer Pool中的数据刷新至磁盘中

• redoLog写入机制

innodb_flush_log_at_trx_commit

0:每次事务提交只写入buffer Pool；
1(默认)：每次事务提交写入磁盘，性能差；
2：每次事务提交写入操作系统的PageCache(匹配内存与磁盘数据交互的速度)；

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

binLog二进制归档日志

只保存修改语句，不保存查询语句

• 主从DB同步数据用到

binlog_format

0：基于SQL语句的复制(例如DATA()函数在从库上回放时，与主库不一样)
1：基于行的复制
MIXED：函数以行的形式记录，其他情况选择SQL语句形式

• 数据丢失时用到

回放binLog中的sql记录

找到两条插入数据的sql，每条sql的上下都有BEGIN和COMMIT，我们找到第一条sql BEGIN前面的文件位置标识 at 219(这是文件的位置标识)，再找到第二条sql COMMIT后面的文件位置标识 at 701

mysqlbinlog ‐‐no‐defaults ‐‐start‐position=219‐‐stop‐position=701‐‐database=test D:/dev/mysql5.7.25‐winx64/data/mysql‐binlog.000009|mysql ‐uroot ‐p123456 ‐v test

或者基于时间戳回放

mysqlbinlog ‐‐no‐defaults ‐‐start‐datetime="2023‐1‐27 23:32:24"‐‐stop‐datetime="2023‐1‐27 23:34:>23"‐‐database=test D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000009|mysql ‐uroot ‐p123456 ‐v test

若DB无了，需要备份全量数据库+binLog

mysqldump ‐u root 数据库名>备份文件名;#备份整个数据库

• 数据审计时用到

错误日志

记录MySQL启动，停止，和执行过程中出现的重大错误事件信息；

1# 查看错误日志存放位置2showvariableslike'%log_error%';

通用查询日志

记录用户使用MySQL的所有操作记录，包括连接登录，执行的所有正确或不正确的SQL语句，包括查询，更新等；无论SQL执行成功或者失败。

一般不建议开启，在调试问题时开启

1showvariableslike'%general_log%';2# 打开通用查询日志3SETGLOBALgeneral_log=on;