-
慢查询日志分析:记录mysql中响应时间超过阙值的语句
- 查询是否打开:show variables like '%slow_query_log’或修改配置
- 开启慢查询日志:set global slow_query_log=‘ON’;关闭set global slow_query_log=‘OFF’
- 查询限定时间:show variables like ‘%long_query_time%’
- 修改限定时间:set global long_query_time = 1;set long_query_time = 1
- 分析工具mysqldumpslow
- 查看SQL成本;set profiling = ‘ON’;show profiles
-
undo log、redo log、binlog日志:
-
undo log(回滚日志):是 Innodb 存储引擎层生成的日志,实现了事务中的原子性,主要用于事务回滚和 MVCC。发生回滚时,就读取 undo log 里的数据,然后做原先相反操作(当一个事务需要回滚时,本质上并不会以执行反
SQL
的模式还原数据,而是直接将roll_ptr
回滚指针指向的Undo
记录,从xx.ibdata
共享表数据文件中拷贝到xx.ibd
表数据文件,覆盖掉原本改动过的数据。)。mvcc读已提交和可重复度中快照读(普通 select 语句)是通过 Read View + undo log 来实现的 -
redo log(重做日志):是 Innodb 存储引擎层生成的日志,实现了事务中的持久性,是 Innodb 存储引擎实现的日志。主要用于掉电等故障恢复mysql之前内存状态;将写操作从「随机写」变成了「顺序写」;redo log是物理日志,记录的是在某个数据页做了什么修改,比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新。对 undo 页的修改也都会记录到 redo log。缓存在 redo log buffer 里的 redo log 还是在内存中,redo log 会每秒刷盘,提交事务时也会刷盘,数据页和 undo 页都是靠这个机制保证持久化的。 1 个重做日志文件组( redo log Group)包括ib_logfile0
和
ib_logfile1,redo log 是循环写的方式,相当于一个环形,InnoDB 用 write pos 表示 redo log 当前记录写到的位置,用 checkpoint 表示当前要擦除的位置,如下图:write pos 追上了 checkpoint,mysql会阻塞刷盘并删除再更新checkpoint。redo log只记录未被刷入磁盘的数据的物理日志,已经刷入磁盘的数据都会从 redo log 文件里擦除。 -
事务提交之前发生了崩溃,重启后会通过 undo log 回滚事务,事务提交之后发生了崩溃,重启后会通过 redo log 恢复未刷盘的事务
-
binlog (二进制归档日志):是 Server 层生成的日志,所有存储引擎都可以使用。主要用于mysql全量数据备份和主从复制;binlog 文件是记录了所有数据库表结构变更和表数据修改的日志,不会记录查询类的操作。bin log是追加写。binlog 文件保存的是全量的日志
Statment、Row、Mixed
三种格式Statment
:每一条会对数据库产生变更的SQL
语句都会记录到bin-log
中。Row
:这种模式就是为了解决Statment
模式的缺陷,Row
模式中不再记录每条造成变更的SQL
语句,而是记录具体哪一个分区中的、哪一个页中的、哪一行数据被修改了。Mixed
:这种被称为混合模式,即Statment、Row
的结合版,因为Statment
模式会导致数据出现不一致,而Row
模式数据量又会很大,因此Mixed
模式结合了两者的优劣势,对于可以复制的SQL
采用Statment
模式记录,对于无法复制的SQL
采用Row
记录。
-
使用bin log完成主从复制:写入bin log、同步bin log、回放bin log。完成主从复制之后,你就可以在写数据时只写主库,在读数据时只读从库
-
两阶段提交:redo log 和 binlog 都要持久化到磁盘,但是这两个是独立的逻辑,可能出现半成功的状态,这样就造成两份日志之间的逻辑不一致。采用两阶段提交解决。采用内部XA将 redo log 的写入拆成了两个步骤:prepare 和 commit,中间再穿插写入binlog,两阶段提交是以 binlog 写成功为事务提交成功的标识。 redo log 可以在事务没提交之前持久化到磁盘,但是 binlog 必须在事务提交之后,才可以持久化到磁盘。
- prepare 阶段:将 XID(内部 XA 事务的 ID) 写入到 redo log,同时将 redo log 对应的事务状态设置为 prepare,然后将 redo log 持久化到磁盘(innodb_flush_log_at_trx_commit = 1 的作用);
- commit 阶段:把 XID 写入到 binlog,然后将 binlog 持久化到磁盘(sync_binlog = 1 的作用),接着调用引擎的提交事务接口,将 redo log 状态设置为 commit,此时该状态并不需要持久化到磁盘,只需要 write 到文件系统的 page cache 中就够了,因为只要 binlog 写磁盘成功,就算 redo log 的状态还是 prepare 也没有关系,一样会被认为事务已经执行成功;
- MySQL 重启后会按顺序扫描 redo log 文件,碰到处于 prepare 状态的 redo log,就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID:
- 如果 binlog 中没有当前内部 XA 事务的 XID,说明 redolog 完成刷盘,但是 binlog 还没有刷盘,则回滚事务。对应时刻 A 崩溃恢复的情况。
- 如果 binlog 中有当前内部 XA 事务的 XID,说明 redolog 和 binlog 都已经完成了刷盘,则提交事务。
- 组提交:当有多个事务提交的时候,会将多个 binlog 刷盘操作合并成一个,从而减少磁盘 I/O 的次数,prepare 阶段不变,只针对 commit 阶段,将 commit 阶段拆分为三个过程:每个阶段都有一个队列,每个阶段有锁进行保护,因此保证了事务写入的顺序,第一个进入队列的事务会成为 leader,leader领导所在队列的所有事务,全权负责整队的操作,完成后通知队内其他事务操作结束。
- flush 阶段:多个事务按进入的顺序将 binlog 从 cache 写入文件(不刷盘);
- sync 阶段:对 binlog 文件做 fsync 操作(多个事务的 binlog 合并一次刷盘);
- commit 阶段:各个事务按顺序做 InnoDB commit 操作;
-
5.7 有 redo log 组提交,在 prepare 阶段不再让事务各自执行 redo log 刷盘操作,而是推迟到组提交的 flush 阶段。flush 阶段队列的作用是用于支撑 redo log 的组提交。可以知道 sync 阶段队列的作用是用于支持 binlog 的组提交。commit 阶段队列的作用是承接 sync 阶段的事务,完成最后的引擎提交,使得 sync 可以尽早的处理下一组事务,最大化组提交的效率。
-
redo-log(prepare)
:在写入准备状态的redo
记录时宕机,事务还未提交,不会影响一致性。 -
bin-log
:在写
bin记录时崩溃,重启后会根据
redo记录中的事务
ID,回滚前面已写入的数据。
-
redo-log(commit)
:在
bin-log写入成功后,写
redo(commit)记录时崩溃,因为
bin-log中已经写入成功了,所以从机也可以同步数据,因此重启时直接再次提交事务,写入一条
redo(commit)`记录即可。能够确保
redo-log、bin-log
两者的日志数据是相同的,bin-log
中有的主机再恢复,如果bin-log
没有则直接回滚主机上写入的数据,确保整个数据库系统的数据一致性。
-
-
-
error-log:错误日志,涵盖了
MySQL-Server
的启动、停止运行的时间,以及报错的诊断信息,也包括了错误、警告和提示等多个级别的日志详情。 -
Slow-log慢查询日志:慢查询日志在内存中是没有缓冲区的,也就意味着每次记录慢查询
SQL
,都必须触发磁盘IO
来完成,因此阈值设的太小,容易使得MySQL
性能下降;如果设的太大,又会导致无法检测到问题SQL
,因此该值一定要设置一个合理值。 -
general log
即查询日志: -
Relay-log中继日志:该类型的日志仅存在主从架构中的从机上,从主机复制过来的
bin-log
数据放在relay-log
日志,仅仅只是作为主从同步数据的“中转站”。
Mysql日志简记
最新推荐文章于 2024-09-08 16:18:33 发布