保障MySQL数据安全的"双1模式"简介
今天简单写写MySQL中跟数据安全相关的两个关键参数吧,一个是innodb_flush_log_at_trx_commit,另外一个是sync_binlog,首先我们来看看这两个参数分别是什么意思吧:
1
innodb_flush_log_at_trx_commit参数
这个参数控制了提交事务的时候是否保持高度的ACID属性,也就是说数据库的安全性。当然我们需要知道,安全性和高性能是一组对立面,想要MySQL实现高性能,就得舍弃一部分安全性,承担一定数据丢失的风险;相反,想要MySQL保证安全性,就要接受一部分性能损失。
这个参数有3个指定的值,分别是0,1,2,它的默认值是1,这3个值的意思如下:
a、如果设置为0,那么MySQL数据库的log buffer将会每秒一次的写入log file中,并且log file的flush操作,也就是刷新到磁盘操作将会同时进行。也就是每秒一次写log,每秒一次刷盘,事务提交的时候不会有任何动作。
b、如果设置为1,每次事务提交的时候log buffer都会将数据写入log file,并且flush到磁盘中去。也就是说每次事务提交的时候,一次写log,一次刷盘
c、如果设置为2,每次事务提交的时候log buffer将数据写入log file,但是不会进行flush 磁盘操作,而是每秒进行一次flush 磁盘操作。也就是事务提交的时候一次写log,每秒一次刷盘
说是这么说,但是mysql官方文档中还给出了这么一句话:
For settings 0 and 2, once-per-second flushing is not 100% guaranteed.
Flushing may occur more frequently due to DDL changes and other
internal InnoDBactivities that cause logs to be flushed independently
of the innodb_flush_log_at_trx_commit setting, and sometimes less
frequently due to scheduling issues. If logs are flushed once
per second, up to one second of transactions can be lost in a crash.
翻译过来就是:
对于设置0和2,每秒一次冲洗不是100%保证。由于DDL更改和InnoDB
导致日志独立于innodb_flush_log_at_trx_commit
设置而刷新的其他内部活动可能更频繁地发生刷新,并且有时由于调度问题而不经常频繁地刷新 。如果每秒刷新一次日志,则出现系统崩溃的情况下最多可能丢失一秒的事务。也就是说,这个1s是不能保证的。一般的线上服务器中,1秒中可能会提交很多事务,也就是TPS会很高,如果将这个值设置为0,很有可能出现服务崩溃或者系统崩溃的时候,丢失上1s内的所有事务。这是我们不能接受的。 但是需要注意的是,如果设置成1的话,则每个事务提交的时候缓存都会和磁盘进行交互,这样频繁的交互必定会影响MySQL数据库的性能。如果设置为2的话,则在MySQL服务崩溃的时候,可以通过redo log来进行恢复,只有出现系统崩溃,那么上1s中所有的事务才会丢失。
2
sync_binlog参数
这个参数的默认值是0,我们看看官方文档的解释吧:
sync_binlog=0:
禁用MySQL服务器将二进制日志同步到磁盘。
相反,MySQL服务器依赖于操作系统来不时
地将二进制日志刷新到磁盘,就像对任何
其他文件一样。此设置提供最佳性能,
但在电源故障或操作系统崩溃的情况下,
服务器可能已提交尚未同步到二进制日志的事务。
sync_binlog=1:
在提交事务之前,允许将二进制日志同步
到磁盘。这是最安全的设置,但由于磁盘
写入次数增加,可能会对性能产生负面影响。
如果发生电源故障或操作系统崩溃,
二进制日志中缺少的事务仅处于准备状态。
这允许自动恢复例程回滚事务,
保证了二进制日志中没有丢失任何事务。
sync_binlog=N,其中N是0或1以外的值:
MySQL 在每写 N次 二进制日志binary log时,
会使用fdatasync()函数将它的写二进制日志
binary log同步到磁盘中去。如果发生操作系统崩溃,
服务器可能已提交尚未刷新到二进制日志的事务。
由于磁盘写入次数增加,此设置可能会对性能产生
负面影响。较高的值可提高性能,但会增加数据
丢失的风险。
3
"双1模式"
当innodb_flush_log_at_trx_commit和sync_binlog这两个参数都设置为1的时候,我们称之为"双1模式"。
"双1设置"适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如订单,交易,充值,支付消费系统。在mysql 服务崩溃或者服务器主机掉电的情况下,binary log 只有可能丢失最多一个语句或者一个事务,安全性能大大增加,但是我们说过,安全性和高性能是一把双刃剑,双1模式会导致频繁的io操作,因此该模式也是最慢的一种方式,影响数据库性能。
双1模式下,当磁盘IO无法满足业务需求时 比如"双11" 活动的压力。推荐的做法是我们设置 innodb_flush_log_at_trx_commit=2 ,sync_binlog=N (N可以设置的稍微大一点,根据实际的业务情况) 且为了防止系统断电异常,可以使用带蓄电池后备电源的缓存。