MySQL은 백업 데이터의 일관성을 어떻게 보장합니까?

머리말

데이터 보안을 위해 데이터베이스를 정기적으로 백업해야 한다는 사실은 누구나 알고 있습니다. 하지만 데이터베이스를 백업할 때 가장 두려운 것은 쓰기 작업이 데이터 불일치를 일으킬 가능성이 가장 높기 때문입니다. 모든 사람이 볼 수 있도록 간단한 예를 제공합니다.

사용자가 데이터베이스 백업 기간 동안 주문을 한다고 가정하면 다음과 같은 문제가 발생할 수 있습니다.

• 재고 테이블 공제 재고.

• 백업 인벤토리 테이블입니다.

• 주문 테이블 데이터를 백업하세요.

• 주문 양식에 주문을 추가하세요.

• 사용자 테이블은 계정 잔액을 공제합니다.

• 백업 사용자 테이블.

위 논리를 따르면 백업 파일의 주문 테이블에 기록이 누락됩니다. 이 백업 파일을 사용하여 데이터를 복원하면 하나의 기록이 누락되어 데이터 불일치가 발생합니다.

이 문제를 해결하기 위해 MySQL은 다양한 솔루션을 제공하고 있는데, 하나씩 설명하고 장점과 단점을 분석해 보겠습니다.

1. 전체 데이터베이스 읽기 전용

이 문제를 해결하기 위해 우리가 생각할 수 있는 가장 쉬운 방법은 데이터베이스 백업 중에 데이터베이스를 읽기 전용으로 설정하고 이런 식으로 쓸 수 없도록 설정하는 것입니다. 전체 데이터베이스를 읽기 전용으로 설정하는 방법도 매우 간단합니다. 먼저 다음 SQL을 실행하고 해당 변수의 값을 살펴봅니다.

show variables like 'read_only';

기본적으로 read_only는 닫힌 상태인 OFF입니다. 먼저 ON으로 변경해 보겠습니다. 다음 SQL을 실행합니다. read_only 是 OFF，即关闭状态，我们先把它改为 ON，执行如下 SQL：

set global read_only=1;

1 表示 ON，0 表示 OFF，执行结果如下：

这个 read_only 对 super 用户无效，所以设置完成后，接下来我们退出来这个会话，然后创建一个不包含 super 权限的用户，用新用户登录，登录成功之后，执行一个插入 SQL，结果如下：

可以看到，这个错误信息中说，现在的 MySQL 是只读的（只能查询），不能执行当前 SQL。

加了只读属性，就不用担心备份的时候发生数据不一致的问题了。

但是 read_only 我们通常用来标识一个 MySQL 实例是主库还是从库：

• read_only=0，表示该实例为主库。数据库管理员 DBA 可能每隔一段时间就会对该实例写入一些业务无关的数据来判断主库是否可写，是否可用，这就是常见的探测主库实例是否活着的。

• read_only=1，表示该实例为从库。通常，对从库进行定期探活时，只会执行一些读操作，例如执行 "select 1;" 这样的语句。

所以，read_only 这个属性其实并不适合用来做备份，而且如果使用了 read_only 属性将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险很高。

因此这种方案不合格。

2. 全局锁

全局锁，顾名思义，就是把整个库锁起来，锁起来的库就不能增删改了，只能读了。

那么我们看看怎么使用全局锁。MySQL 提供了一个加全局读锁的方法，命令是 flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的增删改等操作就会被阻塞。

从图中可以看到，使用 flush tables with read lock; 指令可以锁定表；使用 unlock tables; 指令则可以完成解锁操作（会话断开时也会自动解锁）。

和第一小节的方案相比，FTWRL 有一点进步，即：执行 FTWRL 命令之后如果客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态，而不会一直处于只读状态。

但是！！！

加了全局锁，就意味着整个数据库在备份期间都是只读状态，那么在数据库备份期间，业务就只能停摆了。

所以这种方式也不是最佳方案。

3. 事务

不知道小伙伴们是否还记得松哥之前和大家分享的数据库的隔离级别，四种隔离级别中有一个是可重复读（REPEATABLE READ）

mysqldump -h localhost -uroot -p123 test08 > test08.sql

换言之，在 InnoDB 这种支持事务的存储引擎中，那么我们就可以在备份数据库之前先开启事务，此时会先创建一致性视图，然后整个事务执行期间都在用这个一致性视图，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作，并且这些更新操作不会被当前事务看到。

在可重复读的隔离级别下，即使其他事务更新了表数据，也不会影响备份数据库的事务读取结果，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

具体操作也很简单，使用 mysqldump 备份数据库的时候，加上 -–single-transaction 参数即可。

为了看到 -–single-transaction 参数的作用，我们可以先开启 general_log，general_log 即 General Query Log，它记录了 MySQL 服务器的操作。当客户端连接、断开连接、接收到客户端的 SQL 语句时，会向 general_log 中写入日志，开启 general_log 会损失一定的性能，但是在开发、测试环境下开启日志，可以帮忙我们加快排查出现的问题。

通过如下查询我们可以看到，默认情况下 general_log 并没有开启：

我们可以通过修改配置文件 my.cnf(Linux)/my.ini（Windows），在 mysqld 下面增加或修改（如已存在配置项）general_log 的值为1，修改后重启 MySQL 服务即可生效。

也可以通过在 MySQL 终端执行 set global general_log = ON 来开启 general log，此方法可以不用重启 MySQL。

开启之后，默认日志的目录是 mysql 的 data 目录，文件名默认为 主机名.log。

接下来，我们先来执行一个不带 -–single-transaction 参数的备份，如下：

mysqldump -h localhost -uroot -p123 test08 > test08.sql

大家注意默认的 general_log 的位置。

接下来我们再来加上 -–single-transaction 参数看看：

mysqldump -h localhost -uroot -p123 --single-transaction test08 > test08.sql

大家看我蓝色选中的部分，可以看到，确实先开启了事务，然后才开始备份的，对比不加 -–single-transaction 参数的日志，多了开启事务这一部分。

위 내용은 MySQL은 백업 데이터의 일관성을 어떻게 보장합니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!