mysql日志
2022-06-12 20:30:17 0 举报AI智能生成
mysql日志
作者其他创作
大纲/内容
error log
记录mysql启动、运行、关闭情况，用于定位问题
show variables like 'log_error'\G 查看错误日志位置
慢查询日志
mysql将慢查询语句收集到表 mysql.slow_log
配置
log_slow_queries
是否收集慢sql开关
long_query_time 默认10S
运行时间大于该配置的sql会被收集到slow_log中
log_queries_not_using_indexes
如果sql没有走索引也会被收集到slow_log中
一般查询日志
用于跟踪记录查询日志
中继日志 relay log
用于主从同步
表空间文件.ibd
按表空间存储数据库数据
配置
innodb_file_per_table
配置每个表数据到单独的一个表空间下
binlog
记录update或者insert语句，不会记录select和show语句
追加写入binlog日志文件，当量大的时候会进行拆分
仅在事务提交前，一次写入记录binlog
当一个线程开启一个事务后，会自动分配一个大小为binlog_cache_size的缓存，默认32K
用途
数据恢复
作为数据库的备份文件，一旦出问题可以通过binlog基于时间点来进行恢复
主从复制
从节点使用主节点的binlog进行同步复制
审计
根据二进制日志判断是否受到攻击
格式
statement
默认模式
记录的是客户端执行的sql
binlog日志量小，节省磁盘IO，提高同步效率
raw
记录的不再是单纯的sql，而是每行更改情况，更新前的行数据和更新后的行数据
单条记录的更新sql更加可靠和稳定
导致binlog体积变大，主从复制网络开销大
同步一般适用raw模式
mixed
默认采用statement格式，特殊情况下会使用raw
因为有些statement格式的binlog可能会导致主备不一致，所以要使用row格式
但row格式的缺点是，很占空间。
mixed格式的意思是，mysql自己会判断这条sql语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。
配置
max_binlog_size 单个二进制文件最大值1G
binlog_cache_size 缓冲区大小
默认大小32K，当一个线程开启一个事务后，mysql会自动为该事务分配一个大小为binlog_cache_size的缓冲，如果事务记录大一缓冲，mysql会将缓冲中的内容写到一个文件中，性能会下降。
sync_binlog
0
事务提交后，调用write()将二进制日志写入到os缓冲区，但是不进行刷新fsync(),由主线程定时将os缓冲区同步到磁盘
1
事务提交后，将二进制文件写入磁盘并立即执行刷新fsync()操作
2
表示每次提交事务都write，但是累积2个事务后才fsync
innodb_support_xa
expire_logs_days
指定保留binlog的天数，0代表全保留不清理
redo log
用于宕机后恢复缓冲区脏页的数据，实现事务的原子性和持久性
redolog是物理日志，记录的是关于每个页更改的物理情况
顺序IO写入，性能高，将事务提交时对磁盘的随机写转换为顺序写
在事务执行过程中，不断写入redolog，多个事务会交叉顺序写入
事务执行时先写入缓冲池脏页，在写入redolog缓冲，然后提交事务
在写redolog文件前先写如redolog缓冲，实现批量写
redolog缓冲写入到redolog文件规则
事务提交时
根据innodb_flush_log_at_trx_commit配置决定
master thread每秒将日志缓冲刷新到文件中，无论事务是否提交
当redolog buffer中一半内存已被使用时
log checkpoint时
在磁盘上至少有两个文件 ib_logfile0 ib_logfile1
redolog采用了循环写机制，ib_logfile0写满了就写ib_logfile1，ib_logfile1写满了就重写ib_logfile0
基于循环写，一个文件写满之后，会写另外一个文件，然后覆盖，覆盖前会将缓冲池脏页数据刷新到磁盘
checkpoint
当前要覆盖的位置，覆盖前要确保缓存中脏页数据刷盘
wriet pos
当前写日志的位置，一边写一边后移
checkpoint机制
解决的问题
innodb缓冲区不能无限大
redolog文件也不能无线追加
解决数据库恢复慢的问题
让redolog对应的脏页少一点
分类
sharp checkpoint
数据库关闭时将所有的脏页刷新到磁盘中去
fuzzy checkpoint
根据条件刷新脏页
checkpoint机制其实就是什么时间将innodb缓存中的脏页刷新到磁盘中去，其隐含的语义就是：保持redolog中的日志信息和磁盘中的数据一致
当缓冲区中脏页数据太多(75%)的时候就触发checkpoint，将其中的脏页数据刷新到磁盘中去
write pos追上checkpoint时触发刷新脏页，然后checkpoint向前走
数据库恢复的时候只需要重做checkpoint之后的日志，checkpoint表示已经完整刷到磁盘上data page上的LSN，因此恢复时对比数据也的LSN和redolog的LSN，仅需要恢复从checkpoint开始的日志部分，缩短恢复时间
redolog缓冲和磁盘都是一块(512字节)存储，往磁盘写入的时候时一个扇区大小512字节整个写入，可以保证写入的成功，不需要double write
redolog主要节省的时随机写磁盘的IO消耗(转成顺序写)，而change buffer主要节省的则是随机读磁盘的IO消耗
事务日志具有幂等性，所以多次操作得到同一结果的行为在日志中只记录一次。二进制日志不具有幂等性，多次操作会全部记录下来，在恢复的时候多次执行二进制日志中的记录，速度就慢的多
配置
innodb_log_file_size
配置重做日志文件大小，默认为5M
innodb_log_buffer_size
log buffer的大小，默认是8M
innodb_log_files_in_group
配置重做日志组中重做日志文件个数
innodb_flush_log_at_trx_conmmit
0
事务提交时不会将log buffer中日志写入到os buffer，依赖主线程定时每秒写入os buffer并调用fsync()写入到log file on disk中
1
事务提交时将redolog缓冲哦同步到磁盘，并执行fsync写入磁盘文件
2
每次提交都仅写入到os buffer，依赖操作系统调用fsync()将os buffer中的日志写入到log file on dick
undo log
结构
mysql只会记录一个行记录，行记录中有事务ID trx_id和回滚指针roll_ptr
undolog通过回滚指针链接在一起
提供回滚和多个行版本控制(MVCC)
用于保证事务的原子性、一致性，用于事务回滚
事务开始之前，将当前版本的记录写入到ubdolog，undolog也会产生redolog来保证undolog的可靠性
当事务提交之后，undolog并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定时候可以清理undolog的日志空间
undolog时逻辑日志，根据每行数据进行记录
insert undolog：记录对应的insert语句的delete语句
事务提交后可以直接删除purge
update undolog：记录update语句或deldete语句的逆向update语句
需要提供MVCC支持，放入undolog链表，等待purge线程最后删除
如果不是主键列，在undolog中直接反向记录时如何update的，即update时直接进行的，如果时主键列，update分两步执行：先删除该行，在插入一行目标行
随机写入
日志储存在undo段中，undolog存放在共享表空间中
事务提交后并不能立即删除undolog，因为其他事务可能通过undolog得到之前的版本，需要通过多个版本计算
但是在事务提交的时候，会将事务对应的undolog放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undlog分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免每个独立的事务分配独立的undolog页而良妃储存空间和性能
undolog时采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undolog segment
当执行rollback时，就可以从undolog中的逻辑记录读取到相应的内容并进行回滚，有时候应用到行版本控制的时候，也是通过undolog来实现的：当读取的某一行被其他事务锁定时，他可以冲undolog中分析出该行记录以前的数据时什么，从而提供该行版本信息，让用户实现非锁定一致性读取
0 条评论
下一页
