一篇文章搞定MySQL所有日志!!!

2023年 11月 29日 172.5k 0

1、binlog

概念

binlog是以二进制格式追加存储的归档日志,它属于server层,并且是逻辑日志,主要记录数据的变更操作,包括DDL和DML语言,但不包括数据查询(SELECT、SHOW)语句。

主要作用

数据备份、恢复、主从复制

三种格式

在内容存储的时候,有如下表的三种格式,可通过binlog_format参数进行设置

类型 说明
statement 主要记录的是SQL语句,主从同步时存在数据一致性问题
row(默认) 基于行的日志,主要记录的是数据的变化(变化前和变化后),能保证一致性
mixed statement和row的混合,默认是statement,某些情况下会转换为row

数据库常用SQL语句

#查看binlog开启状态
show variables like '%log_bin%';

#数据库查看binlog日志的格式
show variables like '%binlog_format%';

写入时机

graph LR
A[binlog cache]
B[page cache,即磁盘binlog 文件]
C[硬盘, 持久化]
A -->|write| B
B -->|fysnc| C

事务执行过程中,先把日志写到binlog cache中,事务提交时再把cache中的数据写到binlog文件中,提供sync_binlog参数控制写入,sync_binlog有三种取值,如下表所示:

类型 说明
0 每次提交事务都只写到page cache中,宕机会丢失page cache中的binlog
1 每次提交事务都会执行fsync,即把文件保存到磁盘
N(N>1) 每次事务提交都写到page cache中,累计N个事务才进行fsync,如果宕机,会丢失N个事务的日志
tip:binlog cache每个线程分配一个,可通过binlog_cache_size控制大小,如果存储内容超过设置的大小,就要暂存到磁盘(Swap)

2、redo log

概念

重做日志,即在数据库崩溃的时候,可通过redo log恢复数据(InnoDB引擎独有),环形数组的形式进行循环写

主要作用

保证数据持久性、完整性

刷盘时机

graph LR
A[mysql]
B[redo log buffer, 在mysql内存]
C[page cache, 磁盘中未持久化]
D[硬盘, 已持久化]
A --> B
B --> C
C --> D 
  • 提供三种刷盘策略,可通过innodb_flush_log_at_trx_commit参数设置,具体可见下表格;
  • 另外InnoDB每隔1秒会把redo log buffer中的内容写到文件系统缓存(page cache),然后调用fsync进行刷盘;
  • 还有一种情况,当redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候,后台线程也会主动刷盘。
类型 说明
0 每次提交事务只写到redo log buffer中,mysql崩溃会丢失1s的数据
1(默认) 每次提交事务都会进行刷盘操作,将redo log持久化到磁盘
2 每次事务提交都只把redo log buffer中的内容写到page cache中,OS宕机会丢失1s的数据

二阶段提交

  • 假如redolog先写,binlog后写,如果binlog在写的过程中发生故障,那么数据库备份恢复的时候就会缺失这一部分数据
  • 假设binlog先写,redo log后写,如果redo log写的过程中发生故障,那么数据库重启后redo log中的数据就丢失

所以采用二阶段提交方案,即使在commit发生故障,也能通过事务id找到对应的binlog,从而提交事务恢复数据。

graph LR
A[redo log prepared状态]
B[binlog写]
C[redo log commit]
A --> B
B --> C

3、undo log

概念

回滚日志

主要作用

保证事务原子性,形成版本链实现MVCC多版本并发控制

4、relay log

中继日志,主从同步过程中,从库将主库的binlog日志记录到relaylog中,然后通过sql线程去执行操作,从而保持主从同步。

5、错误日志

记录运行过程中的错误信息

#通过此系统变量查看错误日志文件的位置
show variables like '%log_error%';

6、查询日志

记录了所有操作的日志,包括增删改查,可通过general_log参数进行设置,默认是不开启的,且开启之后日志量增长迅速,所以不建议开启

#该选项用来开启查询日志,0代表关闭,1代表开启
general_log=1 

#设置日志的文件名,如果没有指定,默认的文件名为 host_name.log
general_log_file=mysql_query.log

7、慢查询日志

记录执行时间慢的DML语句,但不包括DDL和不走索引的查询,默认不开启,以下是常用参数设置:

#开启慢查询日志
slow_query_log=1;

#执行时间超过此参数的则记录在慢查询日志中(默认10s)
long_query_time=2

#扫描记录数超过此参数的语句记录在慢查询日志中
min_examined_row_limit=10000;

#默认情况下不开启对DDL语句和不走索引查询的记录,可通过以下参数开启
log_slow_admin_statements=1;
log_queries_not_using_indexes=1;

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论