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

1은 ON을 의미하고, 0은 OFF를 의미하며, 실행 결과는 다음과 같습니다.

🎜🎜이 read_only는 슈퍼 유저에게는 유효하지 않으므로 설정이 완료된 후 세션을 종료하고 새 세션을 생성합니다. 슈퍼 권한을 가진 사용자는 성공 후 로그인합니다. 로그인하고 insert SQL을 실행하면 결과는 다음과 같습니다. 🎜🎜🎜🎜보시다시피 이 오류 메시지는 현재 MySQL이 읽기 전용(쿼리만 가능)이며 현재 SQL을 실행할 수 없음을 나타냅니다. 🎜🎜읽기 전용 속성을 추가하면 백업 중 데이터 불일치에 대해 걱정할 필요가 없습니다. 🎜🎜그러나 read_only는 일반적으로 MySQL 인스턴스가 기본 라이브러리인지 슬레이브 라이브러리인지 식별하는 데 사용됩니다. 🎜🎜🎜🎜read_only=0은 인스턴스가 기본 라이브러리임을 나타냅니다. 데이터베이스 관리자 DBA는 가끔 기본 라이브러리가 쓰기 가능하고 사용 가능한지 확인하기 위해 일부 비업무 데이터를 인스턴스에 쓸 수 있습니다. 이는 기본 라이브러리 인스턴스가 활성 상태인지 감지하는 일반적인 방법입니다. 🎜🎜🎜🎜read_only=1, 인스턴스가 슬레이브 라이브러리임을 나타냅니다. 일반적으로 슬레이브 라이브러리를 정기적으로 탐색할 때 "select 1;"과 같은 문을 실행하는 등 일부 읽기 작업만 수행됩니다. 🎜🎜🎜🎜그래서 read_only 속성은 백업에 적합하지 않으며 read_only 속성을 ​​사용하여 전체 라이브러리를 읽기 전용으로 설정하는 경우 클라이언트가 실패하는 경우 예외적으로 데이터베이스는 읽기 전용 상태로 유지되므로 전체 라이브러리가 오랫동안 쓸 수 없는 상태가 되며 위험이 매우 높습니다. 🎜🎜그래서 이 솔루션은 자격이 없습니다. 🎜🎜2. 전역 잠금🎜🎜전역 잠금은 이름에서 알 수 있듯이 전체 라이브러리를 잠그며, 잠긴 라이브러리를 추가, 삭제, 수정할 수 없습니다. 🎜🎜그럼 전역 잠금 사용법을 살펴보겠습니다. MySQL은 전역 읽기 잠금을 늘리는 방법을 제공합니다. 명령은 읽기 잠금이 있는 테이블 플러시(FTWRL)입니다. 전체 라이브러리를 읽기 전용 상태로 만들어야 하는 경우 이 명령을 사용하면 다른 스레드에 의한 추가, 삭제, 수정 등의 작업이 차단됩니다. 🎜🎜🎜🎜From 그림 보시다시피 flush tables with read lock; 명령을 사용하여 테이블을 잠글 수 있습니다. 잠금 해제 작업은 unlock tables; 명령을 사용하여 완료할 수 있습니다. 세션 연결이 끊어지면 자동으로 잠금이 해제됩니다. 🎜🎜첫 번째 섹션의 솔루션과 비교하여 FTWRL은 약간 개선되었습니다. 즉, FTWRL 명령을 실행한 후 클라이언트의 연결이 비정상적으로 끊어지면 MySQL은 자동으로 전역 잠금을 해제하고 전체 라이브러리가 해당 상태로 돌아갑니다. 정상적으로 업데이트될 수 있으며 읽기 전용으로 유지되지 않습니다. 🎜🎜하지만! ! ! 🎜🎜글로벌 잠금을 추가한다는 것은 백업 기간 동안 전체 데이터베이스가 읽기 전용 상태가 되어 데이터베이스 백업 기간 동안에만 업무 중단이 가능하다는 의미입니다. 🎜🎜그래서 이 방법은 최선의 해결책은 아닙니다. 🎜🎜3. 트랜잭션🎜🎜송 형제가 이전에 공유한 데이터베이스의 격리 수준을 아직도 기억하는지 모르겠습니다. 4가지 격리 수준 중 하나가 REPEATABLE READ입니다. 또한 MySQL의 기본 격리 수준입니다. 🎜🎜이 격리 수준의 다른 트랜잭션에서 사용자가 동일한 SELECT 문을 여러 번 실행하면 결과는 항상 동일합니다. (실행 중인 트랜잭션으로 인한 데이터 변경 사항은 외부에서 볼 수 없기 때문입니다.) 🎜

换言之，在 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!