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;