MySQL数据如何保证不丢失

MySQL数据如何保证不丢失

一、概述

MySQL关系型数据库，是日志先行策略（Write-Ahead Logging），只要binlog和redo log日志能保证持久化到磁盘，我们就能确保MySQL异常重启后，数据不丢失。

二、redo log日志

redo log重做日志文件，只记录事务对数据页做了哪些修改，它记录的是数据修改之后的值，支持崩溃恢复crash-safe。随便说下undo log日志，它记录的是数据修改之前的值，用于保证原子性（回滚时使用）。

2.1、redo log 刷盘的过程：

• 物理上是在MySQL进程内存中，存在redo log buffer中。
• 物理上在文件系统的page cache里，写到磁盘 (write)，但是还没有持久化（fsync)。
• 存在hard disk，已经持久化到磁盘。

为了控制redo log的写入策略，Innodb根据innodb_flush_log_at_trx_commit参数不同的取值采用不同的策略，它有三种不同的取值：

• 0：称为延迟写，表示每次事务提交时都只是把redo log留在redo log buffer 中，不会将redo log buffer中日志写入到OS buffer，而是每秒写入OS buffer并调用写入到redo log file中。
• 1：称为实时写，实时刷”，表示每次事务提交时都将 redo log 直接持久化到磁盘，事务每次提交都会将redo log buffer中的日志写入OS buffer并保存到redo log file中。
• 2：称为实时写，延迟刷。表示每次事务提交时都只是把redo log 写到page cache，然后是每秒将日志写入到redo log file

2.2、总结：

三种模式下，0的性能最好，但是不安全，MySQL进程一旦崩溃会导致丢失一秒的数据。1的安全性最高，但是对性能影响最大，2的话主要由操作系统自行控制刷磁盘的时间，如果仅仅是MySQL宕机，对数据不会产生影响，如果是主机异常宕机了，同样会丢失数据。

三、binlog日志

binlog二进制日志，它会记录数据库执行更改的所有操作。跟redo log不一样，它的文件内容是可以追加的，没有固定大小限制。可以实现主从复制和数据恢复两个作用。

binlog有三种格式：

• Statement：基于SQL语句的复制((statement-based replication,SBR))

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
缺点：由于记录的只是执行语句，为了这些语句能在备库上正确运行，还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在备库得到和在主库端执行时候相同的结果。

• Row:基于行的复制。(row-based replication,RBR)

优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
缺点:可能会产生大量的日志内容

• Mixed:混合模式复制。(mixed-based replication,MBR)

实际上就是Statement与Row的结合。一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式

3.1、binlog写文件分write和fsync两个过程

• write：指把日志写到文件系统的page cache，并没有把数据持久化到磁盘，因此速度较快。
• fsync，实际的写盘操作，即把数据持久化到磁盘

binlog刷盘机制（write和fsync的写入时机，是由变量sync_binlog控制的）

• 当sync_binlog为0时，表示MySQL不控制binlog的刷新，而是由系统自行判断何时写入磁盘。选这种策略，一旦操作系统宕机，缓存中的binlog就会丢失（每次事务提交只write，不fsync）。

• 当sync_binlog为1时，则表示每次commit，都将binlog 写入磁盘。

• sync_binlog为N时，每次事务提交都write，到N个事务才fsync，即会将binlog写入磁盘。

3.2、总结：

如果IO出现性能瓶颈，可以将sync_binlog设置成一个较大的值。比如设置为（100）。但是，会存在数据丢失的风险，当主机异常重启时，会丢失N个最近提交的事务binlog

四、redo log 两阶段提交

两阶段提交就是为了保证redo log和binlog数据的安全一致性。只有在这两个日志文件逻辑上高度一致了。你才能放心的使用redo log帮你将数据库中的状态恢复成crash之前的状态，使用binlog实现数据备份、恢复、以及主从复制。

4.1、在崩溃恢复时，判断事务是否需要提交：
1、 binlog无记录，redolog无记录：在redolog写之前crash,恢复操作：回滚事务
2、 binlog无记录，redolog状态prepare:在binlog写完之前的crash,恢复操 作：回滚事务
3、 binlog有记录，redolog状态prepare:在binlog写完提交事务之前的crash, 恢复操作：提交事务
4、 binlog有记录，redolog状态commit:正常完成的事务，不需要恢复

具体的可以看下我之前写的Mysql的更新流程。