MySQL-日志

Innodb底层原理与Mysql日志机制图1MySQL InnoDB存储引擎架构图- 展示了InnoDB存储引擎的核心组件及其相互关系包括内存结构Buffer Pool、Log Buffer等和磁盘结构数据文件、日志文件等帮助理解MySQL如何通过多级缓存和日志机制保证数据的一致性与持久性。执行流程可分为三个阶段阶段一Server 层预处理1. 客户端Client动作用户通过 MySQL 客户端执行 SQLUPDATE t SET name zhuge666 WHERE id 1; 数据状态id1 的 name 原值为 zhuge2. 连接器动作1.校验用户名/密码 2检查用户是否有对表 t 的 UPDATE 权限 3管理连接长连接/短连接 通过条件权限通过继续执行3. 查询缓存动作1 对于 UPDATE 语句会使表 t 相关的查询缓存全部失效2 2 标记该表的所有缓存条目为无效 原因避免后续查询读到旧数据4. 分析器Analyzer动作 词法分析识别 SQL 中的关键字、表名、列名、条件值 语法分析检查 SQL 是否符合 MySQL 语法规则 输出语法树5. 优化器Optimizer动作 判断 id1 是否存在索引 选择最优执行计划如使用主键索引 决定索引访问路径 输出执行计划6. 执行器Executor动作 调用 InnoDB 存储引擎的 handler 接口开始执行更新操作阶段二InnoDB 引擎层执行 日志写入7. Buffer Pool缓存池默认大小机器内存的 60%-70% 执行过程 7.1 加载数据页 触发条件id1 的数据页不在 Buffer Pool 中 动作从磁盘 t.ibd 文件加载包含 namezhuge 的数据页到 Buffer Pool 加载单位Page默认 16KB 7.2 写入旧值到 Undo Log 动作在修改内存数据之前先将 namezhuge 的旧值写入 Undo Log 用途 事务回滚时恢复数据其他事务的 MVCC 快照读 7.3 修改内存数据 动作在 Buffer Pool 中直接修改数据页 变更namezhuge → namezhuge666 此时状态内存已改磁盘未变产生脏页8. Undo Log回滚日志文件类型InnoDB 引擎特有 写入内容namezhuge旧值 作用如果事务提交失败要回滚数据可以用 undo 日志里的数据恢复 存储位置Undo 表空间9. Redo Log重做日志文件—— Prepare 阶段类型InnoDB 引擎特有 写入方式顺序写在文件末尾追加性能极高 写入内容物理修改记录 —— 在哪页做什么修改 具体记录namezhuge666 阶段标记Prepare准备提交 redo日志顺序写是一个或几个预先分配好磁盘空间的文件写入永远都是在文件末尾追加10. Binlog归档日志文件类型Server 层日志 写入内容逻辑修改记录 —— namezhuge666 写入时机Redo Log Prepare 之后 主要用途主从复制数据库磁盘里的数据恢复阶段三提交与落盘11. 写入 Commit 标记到 Redo Log动作将 Redo Log 标记从 Prepare 改为 Commit 作用标志事务提交完成 功能为了保证事务提交后redo与binlog数据一致两阶段提交2PC的核心Prepare Redo Log → 写 Binlog → Commit Redo Log12. 事务提交完成此时数据状态: -Redo Log 已落盘Commit 状态 -Binlog 已落盘 -Buffer Pool 中数据已修改脏页 -磁盘 .ibd 文件中 name 仍然是 zhuge13. IO 线程后台刷新数据触发时机系统空闲时 动作将 Buffer Pool 里的缓存数据以 Page 为单位写入磁盘 写入方式随机写 写入目标磁盘文件 t.ibd14. 磁盘文件.ibd描述InnoDB 数据表文件 最终状态namezhuge666 写入完成后内存和磁盘数据一致SQL执行过程与日志写入顺序解析与优化Server层解析SQL生成执行计划InnoDB引擎处理在Buffer Pool中查找id1的数据页若不在内存中从磁盘加载到Buffer Pool修改内存中的数据页Undo Log记录生成回滚日志记录修改前的数据Redo Log记录将修改操作写入Redo Log BufferBinlog记录Server层将逻辑操作写入Binlog Cache两阶段提交2PCPrepare阶段Redo Log刷盘根据innodb_flush_log_at_trx_commit设置Commit阶段Binlog刷盘根据sync_binlog设置完成提交Redo Log标记事务提交完成后台线程异步刷脏将脏页刷回磁盘redolog重做日志redolog日志文件相关参数设置redolog buffer大小参数 innodblog_buffer_size 设置redolog文件存储位置innodblog_group_home_dir 设置redolog文件的个数innodblog_files_in_group 默认2个最大100个 设置单个redolog文件大小innodblog_file_size默认值为48M。最大值为512G最大值指的是整个 redolog系列文件之和。redo log 文件写入磁盘过程redo log循环写一个文件一个文件从头开始写数据所有文件写完从第一个文件开头开始写数据。作用:通过固定大小文件实现高效可循环的文件写入.(文件可多个).write pos 是redolog要写入的位置日志写入时会不断后移。checkpoint 是当前要擦除的位置一边擦除一边后移。write pos与checkpoint 之间为可写数据区域。checkpoint 位置之前的日志已经写入磁盘ibd 文件。checkpoint 与 write_pos 之间黄色区域的日志没有写入磁盘.ibd 文件数据库崩溃时使用这些日志恢复数据。当write pos追上checkpoint表示redolog文件写满了所有用户事务会被阻塞需要先擦除数据释放空间。为什么循环写效率高1:将随机写文件变成顺序写文件,顺序写文件磁盘i/o比随机写磁盘i/o效率指数级增加.2:如果直接写ibd文件数据存储位置未知,如果事务中存在多个update,必须通过主键确定数据位置,无法预计下一个update修改哪一页数据.redo log写入策略# 查看redo log写入策略 innodb_flush_log_at_trx_commit参数值showvariableslikeinnodb_flush_log_at_trx_commit;# 设置innodb_flush_log_at_trx_commit参数值(也可以在my.ini或my.cnf文件里配置)setglobalinnodb_flush_log_at_trx_commit1;设置为0事务提交时只把 redo log 写入 redo log buffer 中就提交事务。 特点数据库宕机可能会丢失最近1秒数据、事务提交极快、后台线程负责 write fsync。 持久化过程后台线程每过一秒调用os函数write将redologBuffer写入pageCache,然后调用fsync将数据持久化到磁盘 设置为1事务提交时把 redo log 直接持久化到磁盘就提交事务。数据最安全但是效率差 特点每个事务提交都要等待 fsync持久化到磁盘性能瓶颈在磁盘 持久化过程事务提交前直接调用os函数write将redologBuffer写入pageCache,然后调用fsync将数据持久化到磁盘然后提交事务。 设置为2事务提交时只把 redo log 写入os缓存pageCache中就提交事务。如果os宕机pageCache未写入磁盘就会丢失数据。 特点操作系统宕机可能会丢失最近1秒数据、事务提交很快只做内存拷贝、由后台线程统一持久化到磁盘。 持久化过程后台线程每秒调用os函数write和fsync将数据持久化到磁盘调用write可能无新数据。binlog二进制归档日志作用:记录事务对数据库的增删改操作不保存查询。文件追加写MySQL5.7 版本binlog默认是关闭的8.0版本默认是打开的Binlog相关配置开启binlog功能需要增加如下配置在MySQL 配置文件如 my.cnf配置 log‐binmysql‐binlog # log‐bin设置binlog的存放位置 server‐id 1 # 配置Server Id是数据库服务器id集群环境中id要唯一 binlog_format row # 日志文件格式 expire_logs_days 15 # 自动清理15天前的日志 默认为0 表示不自动删除 max_binlog_size 200M # 控制单个文件大小默认为 1GB sync_binlog 100; # 每100个事务刷盘一次binlog 的日志格式参数binlog_format statement直接记录执行的 SQL 语句 优点日志量小节约IO开销提高性能 缺点存在主从结构数据不一致问题。如: NOW()、UUID()、RAND()、CURRENT_TIMESTAMP()等。 row:每行数据修改的前后过程 优点解决主从结构数据不一致问题。 缺点日志量较大性能不如Statement。假设update语句更新10行数据Statement方式就记录这条update语句Row方式会记录被修改的10行数据 mixed: 混合模式由mysql根据sql自动判断使用日志的形式。行为不易预测。现在基本不用binlog写入磁盘机制参数 sync_binlog 默认值是 0。 sync_binlog 0事务提交时只 write 到page cache由os自行判断什么时候执行 fsync 刷盘。 特点不等待刷盘事务提交极快操作系统崩溃时可能丢失已提交的事务 sync_binlog 1事务提交时直接执行 fsync 刷盘。 特点数据最安全性能瓶颈在磁盘 I/O。 sync_binlog N(N1)事务提交时write 到page cache累积N个事务后才 fsync 刷盘。 特点减少 fsync 次数操作系统崩溃时可能丢失N个事务binlog日志文件重新生成-服务器启动或重新启动 -服务器刷新日志执行命令flush logs -日志文件大小达到 max_binlog_size 值默认值为 1GB删除 binlog 日志文件# 删除当前的binlog文件reset master;# 删除指定日志文件之前的所有日志文件下面这个是删除6之前的所有日志文件当前这个文件不删除purgemaster logstomysql‐binlog.000006;# 删除指定日期前的日志索引中binlog日志文件purgemaster logs before2023‐01‐21 14:00:00;查看 binlog 日志文件#查看bin‐log二进制文件命令行方式不用登录mysqlmysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐outputdecode‐rows D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000007binlog日志文件恢复数据每日全量备份 每小时增量备份恢复数据前提条件确认Binlog已开启必须要有最近一次完整数据全量备份1恢复全量备份 找到误操作前最近的一次完整备份文件如backup.sql将其恢复到一台临时数据库中 mysqlbinlog binlog-file-1 binlog-file-2 | mysql -u root -p 2增量日志回放 2.1查找备份点与误操作点 备份点(start-position)mysqldump生成的备份文件头部找到MASTER_LOG_POS对应的值这是记录备份开始时刻binlog文件位置。 误操作点(stop-position)使用mysqlbinlog将相关binlog文件解析为文本并搜索误操作的SQL语句记录下其# at位置 mysqlbinlog --base64-outputdecode-rows -v binlog.000001 | less 2.2通过备份点与误造作点使用mysqlbinlog命令进行恢复数据。 mysqlbinlog --start-position1969 --stop-position902120 binlog.000001 | mysql -u root -p全量备份# 备份单个数据库mysqldump-uroot-p--master-data2testtest_backup.sql# 备份所有数据库mysqldump-uroot-p--master-data2--all-databasesall_backup.sql# 备份指定表mysqldump-uroot-ptestusersordersusers_orders_backup.sqlmaster-data2 参数代表“以注释方式记录此刻binlog文件坐标。注备份文件中 MASTER_LOG_POS 字段 记录开始时刻的二进制日志位置增量恢复时从该位置开始应用 binlog。 如-- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS245; MASTER_LOG_POS245 就是备份文件在binlog文件中备份位置为什么会有redo log和binlog两份日志呢binlog时Server层日志redoLog是存储引擎innodb私有的日志系统。undo log回滚日志InnoDB使用 回滚段rollback segment方式存储和管理 Undo Log每个回滚段有 1024 个undo log segment 每个事务会使用一undo log segment。在MySQL 5.6开始InnoDB支持最大 128个回滚段故其支持同时在线的事务限制提高到了 128*1024 。 设置undo log文件所在的路径 innodb_undo_directory 设置undo log文件内部回滚段的个数默认值为128 innodb_undo_logs: 设置undo log文件的数量 innodb_undo_tablespaces:undo log日志什么时候删除新增类型的在事务提交之后就可以清除掉了。 修改类型的事务提交之后不能立即清除掉这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。优化建议1. 避免长事务长事务会阻止 Undo Log 清理导致 Undo 表空间膨胀 2. 合理设置隔离级别RC比 RR 能更早清理 Undo Log 3. 配置合适的 Undo文件大下为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了磁盘文件随机读写性能差导致并发低通过更新内存bufferpool在顺序写日志文件保证数据的一致性。同时内存性能极高顺序写性能也远超随机写。错误日志记录启动、关闭、崩溃、连接、权限、SQL 错误等「服务级」事件。 具体为数据库启动和停止以及运行过程中发生任何严重错误时的相关信息当数据库出现任何故障导致无法正常使用时建议首先查看此日志通用查询日志记录所有客户端连接 执行的所有 SQL不论引擎 具体为用户的所有操作包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给MySQL 数据库服务器的所有 SQL 指令等如select、show等无论SQL的语法正确还是错误、也无论SQL执行成功还是失败MySQL都会将其记录下来补充双写缓冲作用保证数据页完整性 将Buffer Pool中的脏页写入磁盘数据文件必须经过双写缓冲。 刷新数据页前先顺序写入双写缓冲区磁盘再写入实际位置。双写缓冲过程1、从Buffer Pool拷贝数据页将脏页16KB/个拷贝到内存中的Doublewrite Buffer 2、将Doublewrite Buffer的内容一次性 顺序写入共享表空间ibdata1 的双写区域磁盘 3、再将数据页分别写入各自的实际表空间文件.ibd 4、写入成功后标记Doublewrite Buffer中的页为可覆盖为什么要使用双写缓冲InnoDB的数据页大小默认为16KB。而操作系统写入磁盘的最小单位是4KB。一个16KB的数据页写入磁盘需要分4次操作每次4KB。四次操作在写入过程中如果发生宕机、断电或系统崩溃时会出现不完整的数据页。为了防止数据页部分写入失败导致的页损坏使用双写缓冲。 而redolog只有对这一页数据的增量修改并没有记录完整页数据。所以此时也无法使用redolog恢复数据。