MySQL数据安全的"双1模式"简介

保障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可以设置的稍微大一点，根据实际的业务情况) 且为了防止系统断电异常，可以使用带蓄电池后备电源的缓存。