binlog
特性
- binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用
- binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1”
- binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
用处
- 可以归档日志,因为redo log是循环写,所以会覆盖掉老的日志
- MySQL很多内部系统依赖于binlog,比如主从同步
- 很多异构系统也依赖于binlog,比如数据分析系统
- 数据库异常重启后,redo log处于prepare状态的时候,要确定数据是否存在,就是确定binlog是否完整,如果binlog完整,即使redo log没有commit,也认为数据是存在的
redo log
特性
- redo log 是 InnoDB 引擎特有的
- redo log 是物理日志,记录的是“在某个数据页上做了什么修改”
- redo log 是循环写的,空间固定会用完
用处
- 记录数据页操作的细节,能支持崩溃恢复
- 由于数据库的写操作,其实很大部分是更新的buffer pool中的数据页或change buffer。所以这一部分数据是没有直接落盘的,当数据恢复的时候其实是直接从数据库中把redo log中记录了的数据页拉入内存再执行redo log上的操作,使MySQL恢复到崩溃前的状态。这也就是为什么redo log满了后要刷脏页的原因
通过两种日志保证MySQL数据不丢失
语句执行流程
UPDATE TABLE_TEST SET c=c+1 WHERE ID=2;
- 上图的write可以理解为从数据库内存写到操作系统的内存中
- 上图的fsync可以理解为一次磁盘IO
是怎么保证数据不丢失
由"binlog的用处4"和"redo log用处2"可以确定,只要 redo log 和 binlog 保证持久化到磁盘,就能确保 MySQL 异常重启后,数据可以恢复。而控制binlog和redo log的参数分别是sync_binlog,innodb_flush_log_at_trx_commit。
-
sync_binlog
- sync_binlog=0 的时候,表示每次提交事务都只 write,不 fsync;
- sync_binlog=1 的时候,表示每次提交事务都会执行 fsync;
- sync_binlog=N(N>1) 的时候,表示每次提交事务都 write,但累积 N 个事务后才 fsync。
-
innodb_flush_log_at_trx_commit
- 设置为 0 的时候,表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 (MySQL内存)
- 设置为 1 的时候,表示每次事务提交时都将 redo log 直接持久化到磁盘(磁盘)
- 设置为 2 的时候,表示每次事务提交时都只是把 redo log 写到 page cache(系统内存)
如果sync_binlog和innodb_flush_log_at_trx_commit都设置成1,那么证明所有的数据库操作都会经历上面的"redo log prepare: write",“binlog: write”,“redo log prepare: fsync”,“binlog:fsync”,“redo log commit:write”。
- 在"redo log prepare: fsync"之前宕机,那就很自然的不算这条记录,数据没有保存,但给客户端返回了报错,可以保证数据不丢失。
- 在"redo log prepare: fsync"和"binlog: fsync"之间主机宕机,那么检查binlog的完整性,如果binlog不完整则不算这条记录,否则则算这条记录,数据有保存,也给客户端返回了报错,数据可能会冗余,但是也保证了数据不丢失。
- 在"binlog: fsync"之后,那么则肯定算这条记录,数据有保存,且不一定给客户端返回了报错,保证了数据不丢失。
如果sync_binlog和innodb_flush_log_at_trx_commit其中有一个值设置成非1,比如把innodb_flush_log_at_trx_commit设置成2,那么"redo log prepare: fsync"有可能就不会执行,这时主机在"redo log commit: write"后掉电,服务端不会给客户端返回报错,且服务端没有保存,主库宕机后数据不能从redo log中恢复,造成了数据丢失。且从库通过binlog读取到了改动,也会造成主从的数据不一致。
怎么降低数据库的IOPS
在我的实际工作中有因为把sync_binlog和innodb_flush_log_at_trx_commit都设置成1造成IOPS过高的问题,那有没有什么办法可以降低MySQL的IOPS。
- 最直接也是提升最明显的方法,修改sync_binlog和innodb_flush_at_trx_commit的值,让日志存储在内存而不实时刷盘,缺点:机器宕机可能会出现数据丢失和主从数据不一致的情况
- 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count,这两个值可以在"binlog:fsync"中等待几次或几毫秒,连同其他事务的binlog进行一次写盘,降低IOPS。
- binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小(默认32k),就要暂存到磁盘,所以如果你的事务内容过多,或者写的某个字段过大,那么可以调高binlog_cache_size的大小,防止不必要的额外写盘。