MySQL数据备份与恢复的那些事（一）-CSDN博客

本文链接：https://blog.csdn.net/lvxinchun/article/details/115096179

MySQL数据备份与恢复的那些事（一）

- 一、MySQL主要日志的介绍（InnoDB）

一、MySQL主要日志的介绍（InnoDB）

MySQL最主要的日志有两个，binlog、redo log，除此之外，还有error.log、slow-query-log等。下面我们对这几个日志做一个简单的说明。

1.1 binlog

1.1.1.binlog的概念

binlog是用于记录数据库的修改以及潜在的修改行为的二进制文件。它也称为归档日志，在MySQL的server层实现的，即不论采用何种存储引擎，只要配置了binlog,那么就都会起作用。

官方文档：https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_log_bin

1.1.2.binlog的作用

主从复制：binlog日志在主服务器上发送语句的记录到从服务器上，从服务器执行这些语句，以保证数据库能和主服务器数据库进行相同的更新（DDL和DML）;
数据恢复：恢复误删除的数据或者恢复到任意一个时刻的数据。

1.1.3.binlog的格式

binlog主要有三种格式：

statement：记录SQL语句，包括DDL和DML语句。
- 优点：性能好，日志量少；
- 缺点：主从复制下会出现数据不一致。比如某些函数（sleep()、存储过程中使用了last_insert_id()函数）等
row:记录的是每行数据的变化，即记录每条数据被改成了什么样子。
- 优点：主从复制时的一致性高；
- 缺点：日志量较大。
- mixed:混合模式，这也是目前官方推荐的。由mysql自动判断是采用记录行还是记录语句。

1.1.4.binog的刷盘方式sync_binlog

MySQL通过sync_binlog参数来控制binlog刷到磁盘的频率，在5.7.7及以上版本，这个值默认为1。我们可以通过这个语句来查看我们MySQL中的刷盘配置：

show variables like 'sync_binlog';

结果如下：

在这里插入图片描述

sync_binlog=0：禁用 MySQL 服务器将二进制日志同步到磁盘。MySQL 服务器依靠操作系统不时地将二进制日志冲洗到磁盘中，就像对任何其他文件一样。此设置提供了最佳性能，但在发生电源故障或操作系统崩溃时，服务器可能会丢失已经提交的数据变更。
sync_binlog =1:每次事务提交，MySQL同步将二进制日期同步到磁盘。最安全的设置，但由于磁盘写入数量增加，可能会对性能产生负面影响。
sync_binlog =N,其中N是大于0的整数。这时候它是以二进制日志提交组的形式，将二进制日志同步到磁盘。在发生电源故障或操作系统崩溃时，服务器可能已执行未冲洗到二进制日志的事务。由于磁盘编写次数的增加，此设置可能会对性能产生负面影响。较高的值可提高性能，但数据丢失的风险会增加。

注意：许多操作系统和一些磁盘硬件愚弄了冲洗到磁盘的操作。他们可能会告诉mysqld，冲洗已经发生，即使它没有。在这种情况下，即使使用推荐的设置，交易的持久性也得不到保证，在最坏的情况下，停电可能会损坏数据。使用 SCSI 磁盘控制器中的电池支持磁盘缓存或磁盘本身可加快文件冲洗速度，并使操作更安全。您还可以尝试禁用软硬件缓存中刻入的磁盘缓存。InnoDB

官方文档：https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog

1.1.5.sync_binlog的生产建议设置

要在用于事务中的复制设置中最大限度地保持持久性和一致性，建议设置为：

sync_binlog =1

1.2 redo log

1.2.1 redo log的概念

redo log,顾名思义，指重做日志，它是一个基于磁盘的数据结构，用于保证数据库在crash的时候，也能使之前提交的记录不会丢失。也就是常说的crash-safe。它是InnoDB引擎自己实现的一种日志形式，并不是基于MySQL的server端生成的，比如Myisam就没有redo log。它是有种实实在在的物理文件。

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-redo-log.html

1.2.2 redo log的出现原因

为什么InnoDB出来一个redo log呢？为什什么MySQL不统一一下呢？我觉得主要有以下两点：

因为MySQL支持的存储引擎有很多，况且每一种存储引擎的实现方式都不一样，所以保证crash出现的状况也不尽相同，况且未来可能还会出现新的存储引擎，那么统一的日志就可能会比较复杂。
这与InnoDB的数据写入磁盘的的方式不同有关。InnoDB数据的更新是采用WAL(Write-Ahead-Logging),即先写日志，再写磁盘，这样降低IO成本。你可能会疑惑，写日志也是写磁盘啊，同样会有IO啊，但是这只是往一个已知的文件追加数据，不需要进行数据的查找，就减少了IO的次数。下面我们简要说一下InnoDB引擎是如何更新数据的。

1.2.3 InnoDB引擎下数据更新过程

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，所以这样就提高了写入性能。

1.2.4 redo log的刷盘频率—innodb_flush_log_at_trx_commit的设置

redo log用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。所以我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。innodb_flush_log_at_trx_commit 共有下面三个配置：

innodb_flush_log_at_trx_commit=0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。
innodb_flush_log_at_trx_commit=1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去；
innodb_flush_log_at_trx_commit=2：每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。**但是：**由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。

1.3 MySQL数据的更新过程（InnoDB存储引擎）

通过上面的分析，我们知道,binlog属于server层，redo log属于存储引擎，那么必然就会涉及的两者之前的数据一致性问题，特别是事务的一致性。这时候大名鼎鼎的"两阶段提交法"就产生了。这里我们先不详细的叙述这个理论，只结合数据的更新过程，简要描述一下这个过程。

我们以下面这条语句来分析下：

表结构：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

更新语句：

upodate student set age =age+1 where id =100;

执行过程如下：

执行器调取存储引擎，找到id=100的这行数据（id是主键，存储引擎直接在主键索引上找就行），如果id=100这行数据所在的内存页就在内存中，就直接返回给了执行器；否则，需要先从磁盘中读取到内存，然后再返回给执行器；
执行器拿到存储引擎给的行数据，age加上1，就得到的一行新数据，然后执行器再调用存储引，来写入这条数据；
存储引擎接收到这条数据，直接更新到内存中，同时将这个更新操作以日志的形式写入到redo log中。这个时候redo log的状态处于prepare状态，然后再通知执行器，我存储引擎处理好了，处理准备阶段，你随时可以提交事务；
执行器接收到存储引擎返回的处理完成的结果，生成这个操作的binlog,并且把binlog写入磁盘;
然后执行器再调用存储引擎的事务提交接口，存储引擎把刚刚写入的redo log的状态改为提交（commit）,更新完成。

这样就将整个更新过程分为两个阶段来执行了。

下图为具体的执行过程：

在这里插入图片描述

1.4 为什么两阶段提交法能够在保证数据的一致性

假设我们不用两阶段提交法，那么就有两种提交方法：先写redo log再写binlog、先写binlog再写redo log。那么我们来分析下这两种情况下会出现什么问题。

1.4.1先写redo log再写binlog

这时候如果redo log已经写完而binlog没有写完，MySQL的进行出现异常，服务重启了，由于redo log的crash-safe的特性，我们能够把刚才已经写入的数据恢复，但是，由于binlog没有写完就crash了，那么显然binlog中就没有这个日志记录，那么后续如果用这个binlog来恢复数据库化，由于binlog里面少了一次更新记录，所以回复出来的数据就会与真实的数据不一致了。

1.4.1先写binlog再写redo log

这时候如果binlog已经写完二redo log没有写完，MySQL服务终止，由于redo log没有写完，那么数据库恢复后，显然提交的事务无效，那么这个数据就没有被更改。，那么后续如果用这个binlog来恢复数据库化，由于binlog里面多了一次更新记录，所以回复出来的数据就会与真实的数据不一致了。

所以，如果不使用“两阶段提交法”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

即：redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。