mysql如何保证数据不丢失_MySQL如何在非“双一”时保证数据不丢失？

一般情况下，MySQL作为OLTP数据库，为了保证事务提交后数据不丢失，需将sync_binlog和innodb_flush_log_at_trx_commit均设置为1(即“双一”设置)。本文分析在MySQL复制场景下是否可以突破“双一”限制并提供可行性方案。

背景

MySQL“双一”设置，指的是MySQL Server层参数sync_binlog设置为1，引擎层参数innodb_flush_log_at_trx_commit也设置为1。

前者设置为1表示在MySQL每次进行事务组提交(group commit)时，需要将这些事务的binlog日志通过sync操作进行刷盘，确保这些binlog日志不会因为服务器宕机而丢失。后者设置为1表示，每个事务在InnoDB引擎中提交时，需要将其对应的redo日志刷盘，确保不会因服务器宕机而丢失。通过“双一”设置，可以实现MySQL高可用实例最大的数据持久性保障。

但每次事务提交都需要刷盘，对MySQL实例的性能会有较大影响，下面几个图是在云主机加SSD云盘环境下分别对各个参数及其组合配置进行性能对比测试的结果，分别进行简要说明：

上图是在相同innodb_flush_method下对比测试innodb_flush_log_at_trx_commit不同设置值的结果，可以看出，在O_DIRECT和O_DIRECT_NO_FSYNC模式下，设置为0或2均比设置为1有显著的性能提升。设置为2表示每隔innodb_flush_at_timeout秒刷一次redo，设置为0表示由Linux后台线程进行redo文件刷盘。

上图是设置不同sync_binlog值的性能对比测试结果，显然，设置为非1值对提升性能很有帮助。设置为0表示由Linux后台线程进行binlog文件刷盘，设置为大于1的值表示每n次事务组提交后进行一次binlog文件刷盘操作。

上图是2个参数叠加为非1设置时的性能对比，可以更加明显地看出性能提升。

据此，可以初步认为，在IO是明显瓶颈的情况下，事务提交时是否刷盘对MySQL性能影响非常大。

非双一的影响分析

既然调整这两个参数有这么明显的性能提升，当然需要想些办法落地使用。

首先分析不设置成“双一”具体的影响。我们假设sync_binlog设置为1000，innodb_flush_log_at_trx_commit设置为2，这种配置下会出现什么问题呢？

其实在这个配置下，事务提交时，binlog和redo均会写入到对应的文件中，只是不会刷盘，也就是说这部分新写入的数据或文件元数据没有进行持久化，而是会先缓存在Linux内核的Cache中(如page cache、vfs cache等)。考虑2种异常场景：- mysqld异常退出：比如mysqld因为bug导致crash，或mysqld因为oom被系统kill了，或mysqld被认为kill了。由于事务的数据已经写入到文件中，这种异常场景不会导致事务丢失；

- Linux服务器宕机：比如服务器掉电或出现Linux内核bug等，这种情况下设置为非“双一”会导致事务丢失。

所以，可以认为，在非服务器(或云主机，后面简化表述)宕机情况下，上述非“双一”配置是安全的。如果MySQL的管控系统，比如云计算上面的RDS服务。其设计方案是如果发生服务器宕机，会对其上的MySQL节点进行数据重建，那么这个方案下，数据的安全性也是可以保证的。

下面我们仅考虑在服务器宕机情况下，会等待服务器重启，然后重新拉起mysqld进程的处理方案。

优化方案可行性分析

在MySQL主从复制或MGR模式下，数据库实例的数据至少会有2个副本。如果能够保证在同一时间至少有一个数据副本是完整的，那么另一个副本的数据理论上就可以补回来。也就是说只要不是一个实例的所有mysqld所在服务器都宕机，那么数据就是安全的。

为了确保数据在所有节点的服务器宕机时仍然是安全的，可以做一折中的选择，那就是将MySQL主节点设置为“双一”模式，其他节点设置为非“双一模式”。通过调研发现，部分公有云厂商也采用了类似的方案：- 华为云MySQL 5.7 20190515版：“备库安全极速模式：在备库“sync_binlog”和“innodb_flush_log_at_trx_commit”为非1配置下，保证备库crash safe数据安全”；

- 阿里云MySQL 8.0 20191225版：“恢复不一致性检查：您可以设置参数sync_binlog = 0和innodb_flush_log_at_trx_commit = 2以获得更好性能。如果操作系统崩溃，Binlog和引擎之间可能会发生不一致，恢复不一致性检查会找出不一致并处理。”

综合来说，非“双一”设置有2种应用场景：- 对数据丢失有一定容忍性的业务场景：比如行为日志类数据，此时可以将主从节点均设置为非“双一”模式；

- 对数据可靠性要求很高的业务场景：可维持主库为“双一”设置，仅将从库设置为非“双一”模式；

对于一件事情，个人觉得是挺有价值而可行的，调研发现业界也在做或已经做了这件事情，那么当然是更加坚定去做的决心了。下面就来分析下来，从库非“双一”情况下，mysqld重启后如何通过主库或其他存活节点来补上缺失的数据。

优化方案具体实现

下面针对是否修改MySQL内核源码来分别分析下在非“双一”情况下的数据修补方案。

slave_exec_mode参数

2个方案均需用到一个MySQL复制参数slave_exec_mode。该参数默认值为STRICT，即Binlog复制严格模式，它还有另一个可选值为IDEMPOTENT，即幂等模式。该模式会忽略复制过程中遇到的记录不存在或重复主键等错误，(仅考虑ROW格式Binlog复制)如下说明：IDEMPOTENT mode causes suppression of duplicate-key and no-key-found errors

这里有些同学可能会先到slave_skip_errors参数，该参数可以跳过上述错误。两个参数的作用是否一样呢？答案是否定的，区别在于，从库回放事务Binlog过程中，如果遇到上述错误，slave_skip_errors会跳过整个出错事务，而slave_exec_mode只会跳过出错的Binlog事件。只有slave_exec_mode这样的处理模式才能做到幂等。

但将slave_exec_mode设置幂等模式有个局限，就是对DDL语句无效，比如建表操作，如果已经存在需创建的表，那么在该模式下仍然会报错，即使通过slave_skip_errors来跳过DDL错误，但DDL对应的事务gtid也无法加入到gtid_executed中。所以，DDL语句需要特殊处理。

实现方案一

本方案无需修改MySQL内核源码，只需调整MySQL实例运维方式。方案要点如下：- a.执行了DDL后，通过flush logs来切换binlog，确保最后一个Binlog文件中没有DDL语句；

- b.服务器宕机重启，拉起mysqld后，执行reset master，重新change master to;

- c.通过查询宕机前MySQL最后一个Binlog文件的Previous_gtids，将其设置为gtid_purged参数来初始化gtid_executed；

- d.在从库上将slave_exec_mode动态设置为IDEMPOTENT，获取此时主库的gtid_executed值；

- e.从库执行START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS = gtid_set，该gtid_set就是上一步获取的主库gtid_executed；

- f.在从库上通过WAIT_FOR_EXECUTED_GTID_SET(gtid_set)来等待从库完成这段binlog回放并停止复制；

- g.将slave_exec_mode调整为默认值，通过start slave正常进行复制。

Binlog文件rotate持久化行为

该方案有个非常重要的前提是认为非最后一个Binlog文件中的事务，都已经刷盘持久化了。经过源码层分析，这个前提是正确的。Binlog文件在进行rotate时会调用MYSQL_BIN_LOG::new_file_impl函数，如下所示：

/**

Start writing to a new log file or reopen the old file.

@param need_lock_log If true, this function acquires LOCK_log;

otherwise the caller should already have acquired it.

@param extra_description_event The master's FDE to be written by the I/O

thread while creating a new relay log file. This should be NULL for

binary log files.

@retval 0 success

@retval nonzero - error

@note The new file name is stored last in the index file

*/

int MYSQL_BIN_LOG::new_file_impl(

bool need_lock_log, Format_description_log_event *extra_description_event) {

...

if (DBUG_EVALUATE_IF("expire_logs_always", 0, 1) &&

(error = ha_flush_logs())) {

goto end;

}

if (!is_relay_log) {

/* Save set of GTIDs of the last binlog into table on binlog rotation */

if ((error = gtid_state->save_gtids_of_last_binlog_into_table())) {

if (error == ER_RPL_GTID_TABLE_CANNOT_OPEN) {

close_on_error =

m_binlog_file->get_real_file_size() >=

close(LOG_CLOSE_TO_BE_OPENED | LOG_CLOSE_INDEX, false /*need_lock_log=false*/,

false /*need_lock_index=false*/);

该函数会调用ha_flush_logs()来刷redo，并将该Binlog文件中的gtid集合写入到系统表中。ha_flush_logs()最终调用innobase_flush_logs()：

/** Flush InnoDB redo logs to the file system.

@param[in] hton InnoDB handlerton

@param[in] binlog_group_flush true if we got invoked by binlog

group commit during flush stage, false in other cases.

@return false */

static

bool

innobase_flush_logs(

handlerton* hton,

bool binlog_group_flush)

{

DBUG_ENTER("innobase_flush_logs");

DBUG_ASSERT(hton == innodb_hton_ptr);

if (srv_read_only_mode) {

DBUG_RETURN(false);

}

/* If !binlog_group_flush, we got invoked by FLUSH LOGS or similar.

Else, we got invoked by binlog group commit during flush stage. */

if (binlog_group_flush && srv_flush_log_at_trx_commit == 0) {

/* innodb_flush_log_at_trx_commit=0

(write and sync once per second).

Do not flush the redo log during binlog group commit. */

DBUG_RETURN(false);

}

/* Flush the redo log buffer to the redo log file.

Sync it to disc if we are in FLUSH LOGS, or if

innodb_flush_log_at_trx_commit=1

(write and sync at each commit). */

log_buffer_flush_to_disk(!binlog_group_flush

|| srv_flush_log_at_trx_commit == 1);

DBUG_RETURN(false);

}

可以看到，在binlog_group_flush默认为false的情况下，不受srv_flush_log_at_trx_commit=2的干扰，会强制刷盘，确保数据持久化。

我们再来看下rotate时如何处理Binlog文件的数据。经过层层代码调用后，会进入如下代码流程：

/*

LOCK_sync to guarantee that no thread is calling m_binlog_file

to sync data to disk when another thread is closing m_binlog_file.

*/

if (!is_relay_log) mysql_mutex_lock(&LOCK_sync);

m_binlog_file->close();

在MYSQL_BIN_LOG::close中，最终调用sync和close来刷盘和关闭文件：

/* this will cleanup IO_CACHE, sync and close the file */

if (log_state.atomic_get() == LOG_OPENED)

{

end_io_cache(&log_file);

if (mysql_file_sync(log_file.file, MYF(MY_WME)) && ! write_error)

{

char errbuf[MYSYS_STRERROR_SIZE];

write_error= 1;

sql_print_error(ER_DEFAULT(ER_ERROR_ON_WRITE), name, errno,

my_strerror(errbuf, sizeof(errbuf), errno));

}

if (mysql_file_close(log_file.file, MYF(MY_WME)) && ! write_error)

{

char errbuf[MYSYS_STRERROR_SIZE];

write_error= 1;

sql_print_error(ER_DEFAULT(ER_ERROR_ON_WRITE), name, errno,

my_strerror(errbuf, sizeof(errbuf), errno));

}

}

实现方案二

该方案通过修改内核代码来自动补齐数据，并完成复制模式切换。

具体方案后续再进行分享，先暂时不公开。

总结

通过以上分析，可以认为在非“双一”模式下，只要一个MySQL实例存在通过Binlog复制建立的多个MySQL节点，那么可以做到在不丢数据的情况下提升节点性能，降低从库的复制延迟。

欢迎大家指出方案中不成熟的地方，讨论改进。